| Bug #12847 | SELECT w/ ORDER does not return records while the same SELECT w/o ORDER does | ||
|---|---|---|---|
| Submitted: | 29 Aug 2005 17:00 | Modified: | 1 Nov 2005 7:09 |
| Reporter: | Alex Zhilyakov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.0.25, 4.1.14 | OS: | Solaris (Solaris 10 SPARC) |
| Assigned to: | CPU Architecture: | Any | |
[29 Aug 2005 17:00]
Alex Zhilyakov
[29 Aug 2005 17:04]
MySQL Verification Team
You can upload the tables into a zip file at: ftp://ftp.mysql.com:/pub/mysql/upload with a name identifying this number report and let us know when done. Thanks.
[29 Aug 2005 17:30]
Alex Zhilyakov
Dear Sir, I apologize but I'm not able to upload the tables because they contain valuable commercial data. I can provide access to our test server (let me know which access level you would need - sql queries, unix shell, root, etc). -Alex
[1 Sep 2005 17:36]
MySQL Verification Team
You don't need to send the original table structure and data, but is possible for you to create a test case with table structure and some insert statements which permit to reproduce the issue with that or similar query ?
[1 Sep 2005 17:47]
Alex Zhilyakov
Dear Sir, As I indicated in the bug report, I don't know how to reproduce the problem on a different table set. I noticed the problem in our production enviroment, and this is the only scenario I can demonstrate the bug on. The tables' size is over 2Gb, it's commercial data. Please let me know if you'd like to investigate the bug in a way other than uploading the tables to you. -Alex
[1 Sep 2005 18:06]
Valeriy Kravchuk
Please, send the results of EXPLAIN (execution plans) for the problem selects, like the following: mysql> explain select * from i6072; +----+-------------+-------+------+---------------+------+---------+------+----- -+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+----- -+-------+ | 1 | SIMPLE | i6072 | ALL | NULL | NULL | NULL | NULL | 4 | | +----+-------------+-------+------+---------------+------+---------+------+----- -+-------+ 1 row in set (0.02 sec) So, as you can see, no real data displayed, just the information about the execution plan needed to try to create a test case.
[1 Sep 2005 18:15]
Alex Zhilyakov
mysql> explain SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' ORDER BY disconnect_time; +----+-------------+------------------------+------+-----------------------------------------+---------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+------+-----------------------------------------+---------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | reptmp0969325804707722 | ref | ix_uniq,ix_disconnect_time,ix_client_id | ix_uniq | 21 | const | 9 | Using where; Using filesort | +----+-------------+------------------------+------+-----------------------------------------+---------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec) mysql>
[2 Sep 2005 13:23]
Valeriy Kravchuk
And explain results for the other 2 queries (thos with correct results), please: explain SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' ; explain SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND (called_number = '867505365400' OR called_number = '11111') ORDER BY disconnect_time; We really need some more information to try to create a simple test case.
[2 Sep 2005 14:00]
Alex Zhilyakov
mysql> explain SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' ; +----+-------------+------------------------+------+-----------------------------------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+------+-----------------------------------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | reptmp0969325804707722 | ref | ix_uniq,ix_disconnect_time,ix_client_id | ix_uniq | 21 | const | 9 | Using where | +----+-------------+------------------------+------+-----------------------------------------+---------+---------+-------+------+-------------+ 1 row in set (0.01 sec) mysql> mysql> mysql> explain SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND (called_number = '867505365400' OR called_number = '11111') ORDER BY disconnect_time; +----+-------------+------------------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------------+ | 1 | SIMPLE | reptmp0969325804707722 | range | ix_uniq,ix_disconnect_time,ix_client_id | ix_uniq | 21 | NULL | 12 | Using where; Using filesort | +----+-------------+------------------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql>
[5 Sep 2005 15:24]
Mark Smithson
I am experiencing a problem which seems similar. Environment is MySQL 4.1.11 on Debian 3.1 on Dual Xeon hardware. We are using MyISAM tables with a table size of around 6 Gig. The query which is causing issues is: SELECT documentNo,documentType,documentDate,documentCode,pages,description FROM document WHERE companyNo='01111111' order by documentDate DESC Remove the order by and it returns results. Does not return results with the order by present. Also if I remove the DESC from the order by it returns results.
[5 Sep 2005 18:55]
Mark Smithson
This may be an issue with corrupted indexes. I have resolved this by running; alter table document disable keys; alter table document enable keys;
[5 Sep 2005 19:57]
Alex Zhilyakov
Hello, Just found another thing. I reported the problem on MERGE table. When I query MyISAM table containing the target record, it shows it fine: mysql> SELECT called_number, disconnect_time FROM c20050817 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; +---------------+---------------------+ | called_number | disconnect_time | +---------------+---------------------+ | 867505365400 | 2005-08-17 22:36:38 | +---------------+---------------------+ 1 row in set (0.01 sec) However, when I query MERGE table which merges c20050817 and other daily tables, it does not show the result: mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; Empty set (0.01 sec) mysql> show create table reptmp0969325804707722; [...] ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`c20050812`,`c20050813`,`c20050814`,`c20050815`,`c20050816`,`c20050817`,`c20050818`) -Alex
[6 Sep 2005 12:59]
Valeriy Kravchuk
Good advice by Mark... Would you try to recreate indexes on base tables you MERGEd, as he proposed, or, even better: 1. cd to the data/database directory. 2. Use myisamchk: myisamchk <table name> for each of the tables or mysqlcheck <db> <table name> as appropriate (See http://dev.mysql.com/doc/mysql/en/mysqlcheck.html for details). myisamchk should give results similar to the following: Checking MyISAM file: _master Data records: 11 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 Please, send the results you get while performing any of this procedures you selected, as well as the results of your test after these procedures.
[6 Sep 2005 14:07]
Alex Zhilyakov
Hello, As I stated in original problem submission, all tables were myisamchk'ed before performing queries and reporting the bug. Now I performed 'myisamchk -e' (extended check, took a few hours) on all the tables, and the problem is still there. -Alex
[6 Sep 2005 14:12]
Alex Zhilyakov
Can it be that table indexes are corrupt, my even 'myisamchk -e' can not find it ?
[6 Sep 2005 15:04]
Valeriy Kravchuk
> Can it be that table indexes are corrupt, my even 'myisamchk -e' can not find it? Yes, looks like it is possible. We are coming back to the advice from Mark. Perform: alter table <table> disable keys; for each of the MERGEd table. (You may try your query after that. If our idea about the corrupted indexes is right, it should work for ages but give you the correct results) Then alter table <table> enable keys; for each of the MERGEd table. Indexes will be recreated. See http://dev.mysql.com/doc/mysql/en/alter-table.html for the details: "As of MySQL 4.0, this feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes." Please, try it if you have time and inform me about the results of the queries.
[6 Sep 2005 17:46]
Alex Zhilyakov
Hello, Things are getting totally weird. I did 'alter table XXX enable/disable keys' for all tables. After I did that, SELECT with ORDER shows result. However (!) after I restarted mysqld, it stopped showing result again !!! See below -- mysql> alter table c20050812 disable keys; Query OK, 0 rows affected (0.02 sec) mysql> alter table c20050812 enable keys; Query OK, 0 rows affected (3 min 55.85 sec) mysql> alter table c20050813 disable keys; Query OK, 0 rows affected (0.00 sec) mysql> alter table c20050813 enable keys; Query OK, 0 rows affected (4 min 21.27 sec) mysql> alter table c20050814 disable keys; Query OK, 0 rows affected (0.00 sec) mysql> alter table c20050814 enable keys; Query OK, 0 rows affected (5 min 21.92 sec) mysql> alter table c20050815 disable keys; Query OK, 0 rows affected (0.00 sec) mysql> alter table c20050815 enable keys; Query OK, 0 rows affected (4 min 17.21 sec) mysql> alter table c20050816 disable keys; Query OK, 0 rows affected (0.00 sec) mysql> alter table c20050816 enable keys; Query OK, 0 rows affected (2 min 35.34 sec) mysql> alter table c20050817 disable keys; Query OK, 0 rows affected (0.00 sec) mysql> alter table c20050817 enable keys; Query OK, 0 rows affected (2 min 42.85 sec) mysql> alter table c20050818 disable keys; Query OK, 0 rows affected (0.00 sec) mysql> alter table c20050818 enable keys; Query OK, 0 rows affected (1 min 45.54 sec) mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; +---------------+---------------------+ | called_number | disconnect_time | +---------------+---------------------+ | 867505365400 | 2005-08-17 22:36:38 | +---------------+---------------------+ 1 row in set (0.02 sec) [---- now I restart mysqld ----] bash-3.00# mysqladmin -u root shutdown STOPPING server from pid file /usr/local/mysql/data/justme.pid 050906 17:33:17 mysqld ended bash-3.00# ( cd /usr/local/mysql/bin; ./mysqld_safe& ) bash-3.00# Starting mysqld daemon with databases from /usr/local/mysql/data mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test Empty set (0.06 sec) mysql> mysql> mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; Empty set (0.01 sec) mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' ; +---------------+---------------------+ | called_number | disconnect_time | +---------------+---------------------+ | 867505365400 | 2005-08-17 22:36:38 | +---------------+---------------------+ 1 row in set (0.01 sec) -Alex
[6 Sep 2005 17:56]
Alex Zhilyakov
Hi, I did a little more experimenting and figured out, that if i do "disable/enable keys" on a SINGLE table within MERGE, SELECT..ORDER shows the result. But after restart it stops showing result again. mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; Empty set (0.01 sec) mysql> alter table c20050815 disable keys; Query OK, 0 rows affected (0.00 sec) mysql> alter table c20050815 enable keys; Query OK, 0 rows affected (3 min 56.63 sec) mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; +---------------+---------------------+ | called_number | disconnect_time | +---------------+---------------------+ | 867505365400 | 2005-08-17 22:36:38 | +---------------+---------------------+ 1 row in set (0.01 sec) [---- RESTART ----] mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test Empty set (0.04 sec) mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; Empty set (0.01 sec) And advice to Mark (if he's still watching) -- restart your mysqld and check the query again... -Alex
[7 Sep 2005 12:34]
Valeriy Kravchuk
Looks like we are moving to the right direction... Please, try to perform alter table reptmp0969325804707722 disable keys; alter table reptmp0969325804707722 enable keys; as a last step (after recreating indexes for base tables), then try your queries, restart mysqld, and try them once more. Inform me about the results.
[7 Sep 2005 14:39]
Alex Zhilyakov
Hello, It does not look like "enable/disable keys" on a MERGE table really does anything: mysql> alter table reptmp0969325804707722 disable keys; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> alter table reptmp0969325804707722 enable keys; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' order by disconnect_time ; Empty set (0.01 sec) -Alex
[7 Sep 2005 16:19]
Alex Zhilyakov
The warning for "alter table MERGE_table disable/enable keys" was "Table storage engine doesn't have this option".
[8 Sep 2005 8:27]
Valeriy Kravchuk
And what will happen when you recreate table (or execute ALTER TABLE reptmp0969325804707722 UNION=(...)) just after rebuilding indexes? Does restart change the results of your query after that? I am trying to repeat this strange change of MERGE table behaviour, but still with no luck... Would you, please, post the complete results of the following statements: show create table reptmp0969325804707722; show index from reptmp0969325804707722; show table status like <each of your base tables + reptmp0969325804707722>
[8 Sep 2005 14:09]
Alex Zhilyakov
mysql> show create table reptmp0969325804707722; | reptmp0969325804707722 |CREATE TABLE `reptmp0969325804707722` ( `cdrid` int(10) unsigned NOT NULL auto_increment, `called_number` varchar(20) default NULL, `duration` int(10) unsigned default NULL, `start_time` datetime default NULL, `connect_time` datetime default NULL, `disconnect_time` datetime default NULL, `cause_code` tinyint(3) unsigned default NULL, `client_ip` varchar(15) default NULL, `carrier_ip` varchar(15) default NULL, `call_type` char(1) default NULL, `release_originator` char(1) default NULL, `in_cic` int(10) unsigned default NULL, `out_cic` int(10) unsigned default NULL, `caller_id` varchar(20) default NULL, `destination_id` int(10) unsigned default NULL, `mid_client` int(10) unsigned default NULL, `mid_carrier` int(10) unsigned default NULL, `buy_rate_id` int(10) unsigned default NULL, `sell_rate_id` int(10) unsigned default NULL, `billdur_client` double(8,4) default NULL, `billdur_carrier` double(8,4) default NULL, `client_id` int(10) unsigned default NULL, `carrier_id` int(10) unsigned default NULL, `call_rnd` int(10) unsigned default NULL, `client_regid` varchar(32) default NULL, `carrier_regid` varchar(32) default NULL, PRIMARY KEY (`cdrid`), UNIQUE KEY `ix_uniq` (`called_number`,`client_ip`,`disconnect_time`,`call_rnd`), KEY `ix_disconnect_time` (`disconnect_time`), KEY `ix_client_ip` (`client_ip`), KEY `ix_carrier_ip` (`carrier_ip`), KEY `ix_cause_code` (`cause_code`), KEY `ix_destination_id` (`destination_id`), KEY `ix_mid_client` (`mid_client`), KEY `ix_mid_carrier` (`mid_carrier`), KEY `ix_client_id` (`client_id`), KEY `ix_carrier_id` (`carrier_id`), KEY `ix_client_regid` (`client_regid`), KEY `ix_carrier_regid` (`carrier_regid`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`c20050812`,`c20050813`,`c20050814`,`c20050815`,`c20050816`,`c20050817`,`c20050818`) | mysql> show index from reptmp0969325804707722; +------------------------+------------+--------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------------------+------------+--------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | reptmp0969325804707722 | 0 | PRIMARY | 1 | cdrid | A | NULL | NULL | NULL | | BTREE | | | reptmp0969325804707722 | 0 | ix_uniq | 1 | called_number | A | 829937 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 0 | ix_uniq | 2 | client_ip | A | NULL | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 0 | ix_uniq | 3 | disconnect_time | A | NULL | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 0 | ix_uniq | 4 | call_rnd | A | NULL | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_disconnect_time | 1 | disconnect_time | A | 829937 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_client_ip | 1 | client_ip | A | 475 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_carrier_ip | 1 | carrier_ip | A | 925 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_cause_code | 1 | cause_code | A | 191 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_destination_id | 1 | destination_id | A | 2058 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_mid_client | 1 | mid_client | A | 1359 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_mid_carrier | 1 | mid_carrier | A | 1365 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_client_id | 1 | client_id | A | 167 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_carrier_id | 1 | carrier_id | A | 173 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_client_regid | 1 | client_regid | A | 325 | NULL | NULL | YES | BTREE | | | reptmp0969325804707722 | 1 | ix_carrier_regid | 1 | carrier_regid | A | 676 | NULL | NULL | YES | BTREE | | +------------------------+------------+--------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ 16 rows in set (0.00 sec)
[8 Sep 2005 14:10]
Alex Zhilyakov
mysql> show table status like 'reptmp0969325804707722'; +------------------------+------------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------------------+------------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+ | reptmp0969325804707722 | MRG_MyISAM | 7 | Dynamic | 4979623 | 229 | 620483732 | NULL | 0 | 0 | 968841 | NULL | NULL | NULL | latin1_swedish_ci | NULL | | | +------------------------+------------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.04 sec) mysql> show table status like 'c20050812'; +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | c20050812 | MyISAM | 7 | Dynamic | 747432 | 126 | 94227016 | 4294967295 | 229086208 | 0 | 747433 | 2005-08-12 00:02:10 | 2005-09-06 15:19:28 | 2005-09-06 15:26:30 | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) mysql> show table status like 'c20050813'; +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | c20050813 | MyISAM | 7 | Dynamic | 834787 | 123 | 102979780 | 4294967295 | 255131648 | 0 | 834788 | 2005-08-13 00:02:09 | 2005-09-06 15:19:44 | 2005-09-06 15:39:21 | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) mysql> show table status like 'c20050814'; +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | c20050814 | MyISAM | 7 | Dynamic | 968840 | 122 | 118700008 | 4294967295 | 296453120 | 0 | 968841 | 2005-08-14 00:02:14 | 2005-09-06 15:20:05 | 2005-09-06 16:26:38 | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec)
[8 Sep 2005 14:10]
Alex Zhilyakov
mysql> show table status like 'c20050815'; +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | c20050815 | MyISAM | 7 | Dynamic | 777886 | 124 | 96540596 | 4294967295 | 304774144 | 0 | 777887 | 2005-08-15 00:00:22 | 2005-09-06 15:20:26 | 2005-09-06 17:52:36 | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) mysql> show table status like 'c20050816'; +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | c20050816 | MyISAM | 7 | Dynamic | 625783 | 124 | 78122476 | 4294967295 | 190982144 | 0 | 625784 | 2005-08-16 00:00:13 | 2005-09-06 15:20:42 | 2005-09-06 17:17:22 | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) mysql> show table status like 'c20050817'; +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | c20050817 | MyISAM | 7 | Dynamic | 582175 | 125 | 72980916 | 4294967295 | 178192384 | 0 | 582176 | 2005-08-17 00:00:21 | 2005-09-06 15:20:56 | 2005-09-06 17:27:58 | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) mysql> show table status like 'c20050818'; +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | c20050818 | MyISAM | 7 | Dynamic | 442720 | 128 | 56932940 | 4294967295 | 173841408 | 0 | 442721 | 2005-08-18 00:00:26 | 2005-09-06 15:21:09 | 2005-09-06 17:48:14 | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) mysql>
[8 Sep 2005 14:50]
Alex Zhilyakov
Okey, here is new experimental info. So, as we know, the target row is located in table c20050817. Now I do: 1. I restart mysqld so it's fresh. 2. CREATE TABLE a1617 (...) UNION=(c20050816, c20050817); 3. CREATE TABLE a1517 (...) UNION=(c20050815, c20050817); 4. SELECT from a1617 - shows empty 5. SELECT from a1517 - shows empty 6. I restart mysqld again 7. SELECT from a1517 - shows empty 8. SELECT from a1617 - shows correct result I repeated this experiment [1..8] three times and the outcome is stable as described. Any ideas?
[14 Sep 2005 23:45]
Matisse Enzer
We saw a possibly similar problem where having an extra KEY in a MERGE table definition caused strange results. We had a MERGE table defined like this: CREATE TABLE `tag_stats` ( `bid` bigint(20) NOT NULL default '0', `date_hour` datetime NOT NULL default '0000-00-00 00:00:00', `count` bigint(20) NOT NULL default '0', KEY `date_hour` (`date_hour`), KEY `bid_time_uniq` (`bid`,`date_hour`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`tag_stats_20050913`,`tag_stats_20050914`) where the underlying tables do not have the KEY `date_hour` (`date_hour`), caused strange results from SELECTS.
[22 Sep 2005 7:18]
Valeriy Kravchuk
Good idea from the last comment. You have already shown the complete create table statement for the merge table (with 10 keys), but not for the underlying ones... So, coming back to your last test, please, send the results of SHOW CREATE TABLE a1617; SHOW CREATE TABLE a1517; and (!) SHOW CREATE TABLE c20050815; SHOW CREATE TABLE c20050816; SHOW CREATE TABLE c20050817;
[22 Sep 2005 11:56]
Alex Zhilyakov
Hello, All table definitions a _precisely_ the same, because they were created using the same text template (or the same perl statement) with only difference in table name itself. Below are a1517 and c20050815. Other tables, again, are exactly the same... | a1517 |CREATE TABLE `a1517` ( `cdrid` int(10) unsigned NOT NULL auto_increment, `called_number` varchar(20) default NULL, `duration` int(10) unsigned default NULL, `start_time` datetime default NULL, `connect_time` datetime default NULL, `disconnect_time` datetime default NULL, `cause_code` tinyint(3) unsigned default NULL, `client_ip` varchar(15) default NULL, `carrier_ip` varchar(15) default NULL, `call_type` char(1) default NULL, `release_originator` char(1) default NULL, `in_cic` int(10) unsigned default NULL, `out_cic` int(10) unsigned default NULL, `caller_id` varchar(20) default NULL, `destination_id` int(10) unsigned default NULL, `mid_client` int(10) unsigned default NULL, `mid_carrier` int(10) unsigned default NULL, `buy_rate_id` int(10) unsigned default NULL, `sell_rate_id` int(10) unsigned default NULL, `billdur_client` double(8,4) default NULL, `billdur_carrier` double(8,4) default NULL, `client_id` int(10) unsigned default NULL, `carrier_id` int(10) unsigned default NULL, `call_rnd` int(10) unsigned default NULL, `client_regid` varchar(32) default NULL, `carrier_regid` varchar(32) default NULL, PRIMARY KEY (`cdrid`), UNIQUE KEY `ix_uniq` (`called_number`,`client_ip`,`disconnect_time`,`call_rnd`), KEY `ix_disconnect_time` (`disconnect_time`), KEY `ix_client_ip` (`client_ip`), KEY `ix_carrier_ip` (`carrier_ip`), KEY `ix_cause_code` (`cause_code`), KEY `ix_destination_id` (`destination_id`), KEY `ix_mid_client` (`mid_client`), KEY `ix_mid_carrier` (`mid_carrier`), KEY `ix_client_id` (`client_id`), KEY `ix_carrier_id` (`carrier_id`), KEY `ix_client_regid` (`client_regid`), KEY `ix_carrier_regid` (`carrier_regid`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`c20050815`,`c20050817`) | | c20050815 |CREATE TABLE `c20050815` ( `cdrid` int(10) unsigned NOT NULL auto_increment, `called_number` varchar(20) default NULL, `duration` int(10) unsigned default NULL, `start_time` datetime default NULL, `connect_time` datetime default NULL, `disconnect_time` datetime default NULL, `cause_code` tinyint(3) unsigned default NULL, `client_ip` varchar(15) default NULL, `carrier_ip` varchar(15) default NULL, `call_type` char(1) default NULL, `release_originator` char(1) default NULL, `in_cic` int(10) unsigned default NULL, `out_cic` int(10) unsigned default NULL, `caller_id` varchar(20) default NULL, `destination_id` int(10) unsigned default NULL, `mid_client` int(10) unsigned default NULL, `mid_carrier` int(10) unsigned default NULL, `buy_rate_id` int(10) unsigned default NULL, `sell_rate_id` int(10) unsigned default NULL, `billdur_client` double(8,4) default NULL, `billdur_carrier` double(8,4) default NULL, `client_id` int(10) unsigned default NULL, `carrier_id` int(10) unsigned default NULL, `call_rnd` int(10) unsigned default NULL, `client_regid` varchar(32) default NULL, `carrier_regid` varchar(32) default NULL, PRIMARY KEY (`cdrid`), UNIQUE KEY `ix_uniq` (`called_number`,`client_ip`,`disconnect_time`,`call_rnd`), KEY `ix_disconnect_time` (`disconnect_time`), KEY `ix_client_ip` (`client_ip`), KEY `ix_carrier_ip` (`carrier_ip`), KEY `ix_cause_code` (`cause_code`), KEY `ix_destination_id` (`destination_id`), KEY `ix_mid_client` (`mid_client`), KEY `ix_mid_carrier` (`mid_carrier`), KEY `ix_client_id` (`client_id`), KEY `ix_carrier_id` (`carrier_id`), KEY `ix_client_regid` (`client_regid`), KEY `ix_carrier_regid` (`carrier_regid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
[29 Sep 2005 12:21]
Valeriy Kravchuk
Still got nothing similar to your results and no information about well-known MERGE problems (in case they are used correctly)... Just wonder (based on the last experimetn result, with restarting), may be you have not enough memory or temporary space in some of these cases. Please, send your my.cnf settings, as well as df -k and swap -a commands results while getting the empty results.
[29 Sep 2005 12:44]
Valeriy Kravchuk
Looks like i've got it finally! There is another, similar bug report: http://bugs.mysql.com/bug.php?id=9112. Your queries are using ix_uniq composite index, so this bug may cause the behaviour you described! I am ready to mark your report as duplicate of that one, but, please, check with the 4.0.27 or 4.1.15 (or 5.0.14) verisons as soon as they will be available and infor me about the results.
[29 Sep 2005 13:26]
Alex Zhilyakov
Hello, [~]$df -k -F ufs Filesystem kbytes used avail capacity Mounted on /dev/dsk/c1t1d0s0 33875895 27555178 5981959 83% / [~]$swap -l swapfile dev swaplo blocks free /dev/dsk/c1t1d0s1 118,9 16 2097392 2097392 I don't have my.cnf file, so mysqld works with all default settings. I have 2G ram, which is plenty for such a setup. Okey, let's wait for 4.1.15 to be published and test it.
[29 Sep 2005 13:32]
Valeriy Kravchuk
I'll set it as waiting for your feedback on testing of future 4.1.15 version (do not want to mark it as duplicate before that).
[29 Oct 2005 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[31 Oct 2005 16:04]
Alex Zhilyakov
I tested the issue on mysql 4.1.15 and it seems as resolved: mysql> SELECT called_number, disconnect_time FROM reptmp0969325804707722 WHERE client_id = 64 AND disconnect_time >= '2005-08-17 00:00:00' AND called_number = '867505365400' ORDER BY disconnect_time; +---------------+---------------------+ | called_number | disconnect_time | +---------------+---------------------+ | 867505365400 | 2005-08-17 22:36:38 | +---------------+---------------------+ 1 row in set (0.00 sec) This query was yielding no rows in 4.1.14 but yields correct 1 row in 4.1.15 -Alex
[1 Nov 2005 7:09]
Valeriy Kravchuk
Thank you for the information. I'll mark this bug report as closed. Please, send a comment if you'll encounter similar bug again on 4.1.15. (Looks like somehow fixed in 4.1.15 as informed by reporter).
