- discuss - lists.mariadb.org

[Maria-discuss] Triangular FKs - Cascade delete leaves DB in state with broken referential integrity
by Oliver Schonrock 05 Feb '18

05 Feb '18

I have come across a very strange symptom with triangular FKs (see below) where a cascade delete of the central (member) record leaves the DB in a state with broken referential integrity. The FK structure is a little bit demanding and somewhat "triangular/circular", but has been in operation in our application for many years without problems. After recent upgrade from 10.1 to 10.2.12, we promptly got broken FKs. (I have not yet retested with 10.1). How to reproduce: (example reduced to the bare essentials with 3 tables with 1 record each). SET FOREIGN_KEY_CHECKS=0; CREATE TABLE member ( id int(11) NOT NULL AUTO_INCREMENT, default_address_id int(11) DEFAULT NULL, default_card_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY member_FI_2 (default_address_id), KEY member_FI_3 (default_card_id), CONSTRAINT member_FK_2 FOREIGN KEY (default_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT member_FK_3 FOREIGN KEY (default_card_id) REFERENCES payment_method (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO member VALUES (1,2,3); CREATE TABLE address ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, PRIMARY KEY (id), KEY address_FI_1 (member_id), CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO address VALUES (2,1); CREATE TABLE payment_method ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, cardholder_address_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY payment_method_FI_1 (member_id), KEY payment_method_FI_2 (cardholder_address_id), CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO payment_method VALUES (3,1,2); SET FOREIGN_KEY_CHECKS=1; SELECT * FROM member; +----+--------------------+-----------------+ | id | default_address_id | default_card_id | +----+--------------------+-----------------+ | 1 | 2 | 3 | +----+--------------------+-----------------+ SELECT * FROM address; +----+-----------+ | id | member_id | +----+-----------+ | 2 | 1 | +----+-----------+ SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | 2 | +----+-----------+-----------------------+ DELETE FROM member WHERE id = 1; SELECT * FROM member; [ empty ] SELECT * FROM address; [ empty ] SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | NULL | +----+-----------+-----------------------+ ------------ DB is now in an inconsistent state: payment_method.member_id=1 references a non-existent member record. Am I missing something, or should this never happen? Thanks -- Oliver Schönrock

1 2

[Maria-discuss] Aurora 'rds.amazonaws.com' string detection
by Evan Burke 31 Jan '18

31 Jan '18

Hi, I recently resolved an issue with an application using the MariaDB JDBC driver being unable to connect to an Amazon Aurora MySQL-compatible database (single instance, not cluster). This turned out to be caused by using 'dblink.[examplehostname].com' for my Aurora connection string, rather than the ...rds.amazonaws.com string checked for in UrlParser.java - https://github.com/MariaDB/mariadb-connector-j/blob/386819936ffad08d0ac2134… I see the code comment indicating that there are problems with detecting Aurora via other methods, as well as compatibility issues preventing the ability to connect to Aurora without a special case, so to some extent I can understand the decision to use hostname or haMode detection. But I wanted to point out that this approach comes with downsides - particularly when it is not documented anywhere that I can see. We had to review the code itself on Github in order to identify the solution, and that's a problem. While I don't expect my experience to be the same as most users, I can't be the only one using an alternate hostname or an IP address to connect, or the only one making the reasonable assumption that the driver is hostname-agnostic. The other consideration - which I am admittedly making somewhat naively - is that the MySQL JDBC driver is able to connect to this Aurora instance without any problems, regardless of hostname. From the perspective of an end user who doesn't care too much about implementation details, I'm probably going to prefer the option that doesn't exhibit unexpected behavior. In any case - despite the frustrating experience I had - I am grateful to everyone who's contributed to the MariaDB project, and I hope my feedback is helpful. -Evan

1 0

[Maria-discuss] Fwd: mysql_insert_id() for INSERT...SELECT statement
by Yuki . 30 Jan '18

30 Jan '18

Hi, I made a post at https://stackoverflow.com/questions/48306962/mysql- insert-id-for-insert-select-statement, but have not received any helpful answer, may be you could help me. I am using MaridDB but I read MySQL documentation because it has much more elaborated documentation than the same page for MariaDB. I am doing similar INSERT...SELECT query to this INSERT INTO table (value1, value2) SELECT 'stuff for value1', 'stuff for value2' FROM DUAL WHERE NOT EXISTS (SELECT * FROM table WHERE value1='stuff for value1' AND value2='stuff for value2') LIMIT 1 , where table has auto-genrated id. And I would like to know if it was inserted or not, of course. I assume the way to do that is to use `mysql_insert_id()`. It returns 0 if no insertions happen and 1 if insertions happen. Looking more details [here]( https://dev.mysql.com/doc/refman/5.7/en/mysql-insert-id.html). > If an INSERT ... SELECT statement is executed, and **NO** automatically > generated value is successfully inserted, mysql_insert_id() **RETURNS** > the ID of the last inserted row. What does it return if no auto-generated ID was successfully inserted? Is this a doc typo? So far I did testing in C and `mysql_insert_id()` returns always 0 if insertion did not happen even if the last insertion succeeded and `mysql_insert_id()` returned non-zero result. A paragraphs in the same manual, mentioned above, confirms this behavior by saying: > mysql_insert_id() returns 0 if the previous statement does not use an AUTO_INCREMENT value. .... > > The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients. > > The LAST_INSERT_ID() SQL function will contain the value of the first automatically generated value that was successfully inserted. LAST_INSERT_ID() is not reset between statements because the value of that function is maintained in the server. .... And that feels kind of logical otherwise `INSERT...SELECT` would be useless in many cases, if you cannot know within the code if your insertion worked or not. But it totally contradicts to the statement above. Did anyone have experience with this? >From MariaDB manual, also suggests that the value should be zero in case of insertion did not happen: > The mysql_insert_id() function returns the ID generated by a query on > a table with a column having the AUTO_INCREMENT attribute or the value > for the last usage of LAST_INSERT_ID(expr). If the last query wasn't > an INSERT or UPDATE statement or if the modified table does not have a > column with the AUTO_INCREMENT attribute and LAST_INSERT_ID was not > used, this function will return zero. Does `mysql_insert_id()` of MariaDB returns 0 for `INSERT...SELECT` if an insertion did not happen, or it might return some other non-zero value? Do you think it is a typo at MySQL documentation? Best, Yuki

2 3

[Maria-discuss] Replication set up to ignore Delete statements
by Furlong, John 25 Jan '18

25 Jan '18

I am new to MariaDB and am wondering if it's possible to set up replication to only replicate INSERTS & UPDATES? Can Deletes be ignored? The purpose is to keep the primary DB small and the secondary DB would be a data warehouse. Does --binlog-format=STATEMENT mean all writes? I've been reading the documention but haven't been able to find a definitive answer. If Delete statements are executed by a different user is it possible to ignore them that way such as through the CURRENT_USER()<https://mariadb.com/kb/en/current_user/> function? Thanks. John

1 0

[Maria-discuss] ALTER TABLE ENIGNE=InnoDB performance
by Conor Murphy 25 Jan '18

25 Jan '18

Hi, We're looking at changing an existing database from MyISAM to InnoDB. The existing system is using MariaDB 5.5.56 on RHEL 7.4 The plan is to - create a replication slave on the same host - use ALTER TABLE .. ENGINE=InnoDB on the slave, with 4 ALTERs running concurrently - replace the master with the "coverted" slave Doing this with a small database (~ 3.5GB) works okay with the largest table taking ~ 7 minutes to convert. However, while trying to figure out how to replicate from the new master, we found that we need "FLUSH TABLES .. FOR EXPORT" which isn't available in MariaDB 5.5. So we updated to MariaDB 10.2.12 and started again. Now when we run the "ALTER TABLE .. ENIGNE=InnoDB", it takes hours from the ALTERs to complete and we're getting the following log entries 2018-01-09 12:36:03 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160101` /* Partition `P20160101` */: Lock wait timeout 2018-01-09 12:36:54 140132036515584 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_13#P#P20160101` /* Partition `P20160101` */: Lock wait timeout 2018-01-09 12:37:45 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160601` /* Partition `P20160601` */: Lock wait timeout 2018-01-09 12:38:36 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160701` /* Partition `P20160701` */: Lock wait timeout 2018-01-09 12:39:27 140132037728000 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_11#P#P20160701` /* Partition `P20160701` */: Lock wait timeout ... Any ideas on what's wrong here? Thanks, Conor

3 8

[Maria-discuss] MariaDB 10.3.4 consumes extremely much CPU on Windows.
by Peter Laursen 21 Jan '18

21 Jan '18

On a 4-core (4 physical, 8 logical cores), 3.5 Ghz box, MariaDB 10.3.4 consumes typically 35-40% of available CPU even when completely idle according to Task Manager. Please see image. I cannot tell if previous 10.3 versions also did, as I don't have 10.3 running constantly in the backgorund. I just observed this now. This does not happen with MariaDB 10.1, MariaDB 10.2, MySQL 5.6, MySQL 5.7 and MySQL 8.0 (all those I have starting with Windows on this box), where the CPU-load is neglible unless the server is "doing real work". As a result, the CPU clock frequency is always highest possible 3.5 Ghz (this CPU model is a 4th generation Intel with the option to scale down CPU frequency to around 40-50% of max. when there is no need for more) when MariaDB 10.3.4 is running. -- Peter -- Webyog

2 7

[Maria-discuss] OpenLDAP & PAM authentication
by Adam Balgach 19 Jan '18

19 Jan '18

Hello – I am having an issue configuring openLDAP to authenticate users on an instance in RHEL (CentOS 7) MariaDB server configured with plugin-load=auth_pam.so in my /etc/pam.d/mysql: auth required pam_ldap.so account required pam_ldap.so when I try and access the server from either the localhost, or any server inside my internal network, I see the LDAP authentication happen without issue and login is fine. When I try and access the server from outside the network, on the mariadb server in /var/log/secure I see the following: Jan 19 08:32:35 mysqld: pam_ldap(mysql:auth): unexpected response from failed conversation function Jan 19 08:32:35 mysqld: pam_ldap(mysql:auth): conversation failed Jan 19 08:32:35 mysqld: pam_ldap(mysql:auth): failed to get password: Authentication token manipulation error And in my client (Cygwin) I get the following error: ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: No such file or directory However I see this plugin in the /usr/lib64/mysql/plugin directory Any idea what is going wrong with remote logins? Thanks-

2 1

[Maria-discuss] MariaDB 10.3.4 installation blocked ny MS "Windows Defender" malware protection
by Peter Laursen 19 Jan '18

19 Jan '18

Please see image. (you can clik "more info" adn the option to "install anyway" will appear. Did you forget to sign the installer this time or has some certificate expired? -- Peter -- Webog

2 5

Re: [Maria-discuss] Parallel Databases and network security
by Reindl Harald 16 Jan '18

16 Jan '18

Am 16.01.2018 um 05:10 schrieb Jan Steinman: >> On 2018-01-15, at 17:15, Reindl Harald <h.reindl(a)thelounge.net> wrote: >> >> Am 16.01.2018 um 01:58 schrieb Jan Steinman: >>>> On 2018-01-15, at 16:37, Ruben Safir <ruben(a)mrbrklyn.com> wrote: >>>> >>>> Because that is how I want it. The longer explanation is, because that >>>> is how I want to do it, period. >>> You have the freedom to be foolish. And I have the freedom to not help you be foolish >> >> forget it - that fool obviously never wants answers to his questions nor tries to understand what people tell him > > He doesn’t understand how TCP/IP and routed works, either: “I'd like it to only listen on a specific interface altogether.” > > That’s okay. It’s been cheap entertainment for me! :-) :-) however, it's heartwarming that someone who don't want anserws from humans types his questions not in a search engine like https://www.google.at/search?q=mysql+listen gicen that the first hit is not too bad... https://www.howtogeek.com/howto/mysql/switch-mysql-to-listen-on-tcp/

3 8

[Maria-discuss] Parallel Databases and network security
by Ruben Safir 16 Jan '18

16 Jan '18

Is there a way to hot copy a database from one machine to another and restrict network access to only between those two machines without using a firewall? Ruben -- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive http://www.coinhangout.com - coins! http://www.brooklyn-living.com Being so tracked is for FARM ANIMALS and and extermination camps, but incompatible with living as a free human being. -RI Safir 2013

3 6