Description:
Given the data in "How to repeat" the following query returns NULL for itemCity when we expect it to return "San Diego."
mysql> SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51;
+--------+-----------------------+----------+
| itemID | itemName | itemCity |
+--------+-----------------------+----------+
| 51 | My super special item | NULL |
+--------+-----------------------+----------+
1 row in set (0.00 sec)
I think this is isolated to InnoDB because if you change `extractedItems` to a MyISAM table, itemCity correctly returns "San Diego" each time.
Additionally, if you rebuild the InnoDB table after import:
ALTER TABLE extractedItems ENGINE=InnoDB;
The query then DOES return the proper answer:
mysql> SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51;
+--------+-----------------------+-----------+
| itemID | itemName | itemCity |
+--------+-----------------------+-----------+
| 51 | My super special item | San Diego |
+--------+-----------------------+-----------+
1 row in set (0.00 sec)
--
Even further, if you remove the ENABLE/DISABLE KEYS, running the query before a table rebuild seems to work but then after a rebuild, the second one works only sometimes.
--
I'm not sure what's going on, but it sure seems like unexpected behaviour.
If you need more information, please let me know. Thanks!
-- mjh
How to repeat:
-- MySQL dump 10.10
--
-- Host: localhost Database: hg
-- ------------------------------------------------------
-- Server version 5.0.18-standard-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `extractedItems`
--
DROP TABLE IF EXISTS `extractedItems`;
CREATE TABLE `extractedItems` (
`siteID` mediumint(8) unsigned NOT NULL default '0',
`itemTypeID` mediumint(8) unsigned NOT NULL default '0',
`geoNodeID` mediumint(8) unsigned NOT NULL default '0',
`itemID` mediumint(8) unsigned NOT NULL default '0',
KEY `siteID` (`siteID`,`itemTypeID`,`geoNodeID`),
KEY `siteID_2` (`siteID`,`itemTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `extractedItems`
--
/*!40000 ALTER TABLE `extractedItems` DISABLE KEYS */;
LOCK TABLES `extractedItems` WRITE;
INSERT INTO `extractedItems` VALUES (156,1,53097,51);
UNLOCK TABLES;
/*!40000 ALTER TABLE `extractedItems` ENABLE KEYS */;
--
-- Table structure for table `itemMetaData`
--
DROP TABLE IF EXISTS `itemMetaData`;
CREATE TABLE `itemMetaData` (
`itemCity` varchar(255) NOT NULL default '',
`itemID` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (`itemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `itemMetaData`
--
/*!40000 ALTER TABLE `itemMetaData` DISABLE KEYS */;
LOCK TABLES `itemMetaData` WRITE;
INSERT INTO `itemMetaData` VALUES ('San Diego',51);
UNLOCK TABLES;
/*!40000 ALTER TABLE `itemMetaData` ENABLE KEYS */;
--
-- Table structure for table `items`
--
DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
`itemName` varchar(255) NOT NULL default '',
`itemID` mediumint(8) unsigned NOT NULL auto_increment,
PRIMARY KEY (`itemID`),
UNIQUE KEY `itemName_2` (`itemName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `items`
--
/*!40000 ALTER TABLE `items` DISABLE KEYS */;
LOCK TABLES `items` WRITE;
INSERT INTO `items` VALUES ('My super special item',51);
UNLOCK TABLES;
/*!40000 ALTER TABLE `items` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*
The query fails:
mysql> SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51;
+--------+-----------------------+----------+
| itemID | itemName | itemCity |
+--------+-----------------------+----------+
| 51 | My super special item | NULL |
+--------+-----------------------+----------+
1 row in set (0.00 sec)
*/
SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51;
/* **** REBUILD THE TABLE **** */
ALTER TABLE extractedItems ENGINE=InnoDB;
/*
The query works:
mysql> SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51;
+--------+-----------------------+-----------+
| itemID | itemName | itemCity |
+--------+-----------------------+-----------+
| 51 | My super special item | San Diego |
+--------+-----------------------+-----------+
1 row in set (0.00 sec)
*/
SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51;
---
/etc/my.cnf for server:
# Example mysql config file for large systems.
#
# This is for large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
#old-passwords
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
user = mysqld
port = 3306
socket = /tmp/mysql.sock
old-passwords
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
set-variable = max_connections=500
sync-binlog=1
server-id = 1
#nice = -20
log-bin=binlog
relay-log=relaylog
log-error=errors.log
query_cache_type=1
query_cache_size=52428800
log-slow-queries
log-queries-not-using-indexes
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=64M
#set-variable = bdb_max_lock=100000
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /db/mysql/data/
innodb_data_file_path = innodb:20M:autoextend
innodb_log_group_home_dir = /db/mysql/data/
innodb_log_arch_dir = /db/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
set-variable = innodb_buffer_pool_size=128M
set-variable = innodb_additional_mem_pool_size=20M
# Set .._log_file_size to 25 % of buffer pool size
set-variable = innodb_log_file_size=64M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
#no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
------------
uname -a for server:
Linux dev1.main.aresdirect.com 2.4.20-8 #1 Thu Mar 13 17:54:28 EST 2003 i686 i686 i386 GNU/Linux
Description: Given the data in "How to repeat" the following query returns NULL for itemCity when we expect it to return "San Diego." mysql> SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51; +--------+-----------------------+----------+ | itemID | itemName | itemCity | +--------+-----------------------+----------+ | 51 | My super special item | NULL | +--------+-----------------------+----------+ 1 row in set (0.00 sec) I think this is isolated to InnoDB because if you change `extractedItems` to a MyISAM table, itemCity correctly returns "San Diego" each time. Additionally, if you rebuild the InnoDB table after import: ALTER TABLE extractedItems ENGINE=InnoDB; The query then DOES return the proper answer: mysql> SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51; +--------+-----------------------+-----------+ | itemID | itemName | itemCity | +--------+-----------------------+-----------+ | 51 | My super special item | San Diego | +--------+-----------------------+-----------+ 1 row in set (0.00 sec) -- Even further, if you remove the ENABLE/DISABLE KEYS, running the query before a table rebuild seems to work but then after a rebuild, the second one works only sometimes. -- I'm not sure what's going on, but it sure seems like unexpected behaviour. If you need more information, please let me know. Thanks! -- mjh How to repeat: -- MySQL dump 10.10 -- -- Host: localhost Database: hg -- ------------------------------------------------------ -- Server version 5.0.18-standard-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `extractedItems` -- DROP TABLE IF EXISTS `extractedItems`; CREATE TABLE `extractedItems` ( `siteID` mediumint(8) unsigned NOT NULL default '0', `itemTypeID` mediumint(8) unsigned NOT NULL default '0', `geoNodeID` mediumint(8) unsigned NOT NULL default '0', `itemID` mediumint(8) unsigned NOT NULL default '0', KEY `siteID` (`siteID`,`itemTypeID`,`geoNodeID`), KEY `siteID_2` (`siteID`,`itemTypeID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `extractedItems` -- /*!40000 ALTER TABLE `extractedItems` DISABLE KEYS */; LOCK TABLES `extractedItems` WRITE; INSERT INTO `extractedItems` VALUES (156,1,53097,51); UNLOCK TABLES; /*!40000 ALTER TABLE `extractedItems` ENABLE KEYS */; -- -- Table structure for table `itemMetaData` -- DROP TABLE IF EXISTS `itemMetaData`; CREATE TABLE `itemMetaData` ( `itemCity` varchar(255) NOT NULL default '', `itemID` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`itemID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `itemMetaData` -- /*!40000 ALTER TABLE `itemMetaData` DISABLE KEYS */; LOCK TABLES `itemMetaData` WRITE; INSERT INTO `itemMetaData` VALUES ('San Diego',51); UNLOCK TABLES; /*!40000 ALTER TABLE `itemMetaData` ENABLE KEYS */; -- -- Table structure for table `items` -- DROP TABLE IF EXISTS `items`; CREATE TABLE `items` ( `itemName` varchar(255) NOT NULL default '', `itemID` mediumint(8) unsigned NOT NULL auto_increment, PRIMARY KEY (`itemID`), UNIQUE KEY `itemName_2` (`itemName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `items` -- /*!40000 ALTER TABLE `items` DISABLE KEYS */; LOCK TABLES `items` WRITE; INSERT INTO `items` VALUES ('My super special item',51); UNLOCK TABLES; /*!40000 ALTER TABLE `items` ENABLE KEYS */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /* The query fails: mysql> SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51; +--------+-----------------------+----------+ | itemID | itemName | itemCity | +--------+-----------------------+----------+ | 51 | My super special item | NULL | +--------+-----------------------+----------+ 1 row in set (0.00 sec) */ SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51; /* **** REBUILD THE TABLE **** */ ALTER TABLE extractedItems ENGINE=InnoDB; /* The query works: mysql> SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51; +--------+-----------------------+-----------+ | itemID | itemName | itemCity | +--------+-----------------------+-----------+ | 51 | My super special item | San Diego | +--------+-----------------------+-----------+ 1 row in set (0.00 sec) */ SELECT items.itemID, items.itemName, itemMetaData.itemCity FROM items LEFT JOIN itemMetaData ON itemMetaData.itemID = items.itemID INNER JOIN extractedItems ON extractedItems.siteID = 156 AND extractedItems.itemTypeID = 1 AND extractedItems.itemID = items.itemID WHERE extractedItems.itemID = 51; --- /etc/my.cnf for server: # Example mysql config file for large systems. # # This is for large system with memory = 512M where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /usr/local/mysql/var) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /tmp/mysql.sock #old-passwords # Here follows entries for some specific programs # The MySQL server [mysqld] user = mysqld port = 3306 socket = /tmp/mysql.sock old-passwords skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=4 set-variable = max_connections=500 sync-binlog=1 server-id = 1 #nice = -20 log-bin=binlog relay-log=relaylog log-error=errors.log query_cache_type=1 query_cache_size=52428800 log-slow-queries log-queries-not-using-indexes # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=64M #set-variable = bdb_max_lock=100000 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /db/mysql/data/ innodb_data_file_path = innodb:20M:autoextend innodb_log_group_home_dir = /db/mysql/data/ innodb_log_arch_dir = /db/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high set-variable = innodb_buffer_pool_size=128M set-variable = innodb_additional_mem_pool_size=20M # Set .._log_file_size to 25 % of buffer pool size set-variable = innodb_log_file_size=64M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] #no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout ------------ uname -a for server: Linux dev1.main.aresdirect.com 2.4.20-8 #1 Thu Mar 13 17:54:28 EST 2003 i686 i686 i386 GNU/Linux