[MDEV-10215] SQL which works with MySQL hangs the system with MariaDB Created: 2016-06-12  Updated: 2016-07-15  Resolved: 2016-07-15

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 5.5.47-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Bob McMeeking Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback


 Description   

A simple SQL query which works fine for MySQL (5.0.95) hangs the system for MariaDB. No error message appear and Maria server does not appear to be using CPU cycles.

Here is an example of a problem SQL which uses a single table:

USE cworks_classicplus1;
 
SELECT DISTINCT author_tab1.author , author_tab2.author ,
 
author_tab1.num , author_tab2.num
 
FROM author AS author_tab1   , author AS author_tab2
 
 WHERE       author_tab1.author ='SMITH'    AND author_tab2.author ='JONES' 
 AND author_tab1.recnum = author_tab2.recnum ;

The table was originally created using:

CREATE TABLE author (
 
        num     INTEGER(8) PRIMARY KEY,
 
        recnum     INTEGER(8) NOT NULL,
 
        author     VARCHAR(50) NOT NULL,
 
        finitial    CHAR(1),
 
        rest       VARCHAR(70)
 
);

The sort of output expected (as from MySQL) is:

.
 
.
 
Smith   Jones   7836054 7836053
 
Smith   Jones   7843689 7843688
 
Smith   Jones   8028018 8028016
 
Smith   Jones   8028023 8028021



 Comments   
Comment by Elena Stepanova [ 2016-06-12 ]

BOBRFM,

Please attach your cnf file(s) and paste the output of 

SHOW TABLE STATUS LIKE 'author';
 
 
 
EXPLAIN EXTENDED
 
SELECT DISTINCT author_tab1.author , author_tab2.author ,
 
author_tab1.num , author_tab2.num
 
FROM author AS author_tab1   , author AS author_tab2
 
 WHERE       author_tab1.author ='SMITH'    AND author_tab2.author ='JONES' 
 AND author_tab1.recnum = author_tab2.recnum ;
 
 
 
SHOW WARNINGS;

If you can provide the full data dump, it will be even better. You can upload it to ftp.askmonty.org/private, only MariaDB developers will have access to it.

Comment by Elena Stepanova [ 2016-06-12 ]

Reply received by email:

Hello Elena

I assume the .cnf file in question is /etc/my.cnf. As far as I can see
this is the default (i.e. no customisation)

[mysqld]
 
datadir=/var/lib/mysql
 
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
 
symbolic-links=0
 
# Settings user and group are ignored when systemd is used.
 
# If you need to run mysqld under a different user or group,
 
# customize your systemd unit file for mariadb according to the
 
# instructions in http://fedoraproject.org/wiki/Systemd
 
 
 
[mysqld_safe]
 
log-error=/var/log/mariadb/mariadb.log
 
pid-file=/var/run/mariadb/mariadb.pid
 
 
 
#
 
# include all files from the config directory
 
#
 
!includedir /etc/my.cnf.d

Further information will follow.

Please bear with me. I am not a database expert.

I will try to dig out the script that I use to load the table. To
perform the search I simply use:

cat question | mysql -u dba -pPWD

This then hangs with no evidence of any CPU activity.

If I edit question to:

USE cworks_classicplus1;
 
SELECT DISTINCT
 
author_tab1.author , author_tab2.author ,
 
author_tab1.num , author_tab2.num
 
FROM author AS author_tab1   , author AS author_tab2
 
 WHERE       author_tab1.author ='SMITH'    AND author_tab2.author ='JONES'
 
 AND author_tab1.num = author_tab2.recnum ;

I get:

cat question | mysql -u dba -psql
 
author  author  num     num
 
Smith   Jones   171742  954326
 
Smith   Jones   173652  965395
 
Smith   Jones   208202  1162893
 
Smith   Jones   245543  1379436
 
Smith   Jones   252778  1420036
 
Smith   Jones   268445  1516025
 
Smith   Jones   279024  1580211
 
Smith   Jones   281243  1593270
 
Smith   Jones   303943  1722688
 
Smith   Jones   336862  1916338
 
Smith   Jones   485391  2792408
 
Smith   Jones   489451  2814834
 
Smith   Jones   528489  3038731
 
Smith   Jones   555214  3196035
 
Smith   Jones   599558  3460277
 
Smith   Jones   721411  4179707
 
Smith   Jones   730170  4235682
 
Smith   Jones   745905  4327540
 
Smith   Jones   754740  4382713
 
Smith   Jones   779612  4529710
 
Smith   Jones   781592  4541512
 
Smith   Jones   845433  4824604
 
Smith   Jones   862833  4889782
 
Smith   Jones   865549  4898014
 
Smith   Jones   886533  4972801
 
Smith   Jones   1021052 5565128
 
Smith   Jones   1036442 5627430
 
Smith   Jones   1173311 6333102
 
Smith   Jones   1222779 6805285
 
Smith   Jones   1253070 6958537
 
Smith   Jones   1285190 7142160
 
Smith   Jones   1472848 7946723

These results are pretty meaningless, but show things can me fudged to work...

Here

AND author_tab1.recnum = author_tab2.recnum

has been changed to

AND author_tab1.num = author_tab2.recnum

"num" is a primary key. Is that significant?.

Regards

Bob

Comment by Elena Stepanova [ 2016-06-12 ]

BOBRFM,

For config files, please paste or attach not only /etc/my.cnf, but everything it refers to – namely, the contents of /etc/my.cnf.d folder.
Alternatively, you can paste the output of SHOW VARIABLES. To do it, run mysql -u dba -pPWD, and in the command-line client prompt write SHOW VARIABLES;, press enter, copy the entire result set and paste it or attach as a file (you can edit it to obfuscate whatever you consider to be private info there).

Please do the same with the queries I asked about above (SHOW TABLE STATUS... etc.).

Finally, if you can provide the contents of the table but just don't know how, you can simply run 

SELECT * FROM author INTO OUTFILE '/tmp/author.sql';

and then attach or upload the file /tmp/author.sql.

Comment by Bob McMeeking [ 2016-06-12 ]

I hope to be in a position to provide the full contents of the author table sometime tomorrow.

In the meantime I have performed further test. It would appear that the search runs very slowly - even when all columns used are indexed (or I believe so!).

The problem is size related.JONES has ~16K instances and SMITH ~15K. A search with say INOUE & NASU runs through very fast. SMITH & INOUE is very slow and SMITH & JONES is glacial. But it does get there in the end!

Bob

Comment by Bob McMeeking [ 2016-06-13 ]

The file MDEV-10215-author.sql.gz should now been uploaded to the FTP server.

Please confirm that it has arrived safely!

Comment by Bob McMeeking [ 2016-06-13 ]

I have uploaded the "SHOW VARIABLES" and "SHOW TABLES" file to the FTP area.

These were previous sent as email attachments, but may have been lost due to my use of an inappropriate email address.

Comment by Elena Stepanova [ 2016-06-16 ]

BOBRFM, thanks, but there is still no output of 

EXPLAIN EXTENDED
 
SELECT DISTINCT author_tab1.author , author_tab2.author ,
 
author_tab1.num , author_tab2.num
 
FROM author AS author_tab1   , author AS author_tab2
 
 WHERE       author_tab1.author ='SMITH'    AND author_tab2.author ='JONES' 
 AND author_tab1.recnum = author_tab2.recnum ;
 
 
SHOW WARNINGS;

Please paste it.

Please also specify how long you had waited till you decided that the query hangs. If you can, run the SELECT which you suspect of hanging in one MySQL session, then connect through another one, wait for a bit (say, a few minutes) and run SHOW PROCESSLIST.
Thanks.

Comment by Elena Stepanova [ 2016-07-15 ]

Please comment to re-open if you have further information on this issue.

Generated at Thu Feb 08 07:40:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.