InnoDB crashes joining three tables in a select statement

[Strobel Oliver]

Hi all,

I'm using MySQL Server 4.1.11 on a WindowsXP SP2 PC.

The client application is written in VB6 using the MyODBC-3.51.11-2 connector.

Following SQL command, started from a client pc, hits the MySQL server so hard, that it stopps:

SELECT t1.*, t2.*, t3.Cus_Art_Name
FROM Customer_Order t1, Ehv_Production_Order t2, Customer_Article t3
WHERE (t1.Sel_Sta_Ord_ID = 2 OR t1.Sel_Sta_Ord_ID = 3)
AND t1.Cus_Ord_No = t2.Cus_Ord_No
AND t1.Cus_Art_ID = t3.Cus_Art_ID
ORDER BY t1.Cus_Ord_No;

All three tables are InnoDB tables.

The error occure when there is more than one datarow in the table "production_order".

When I start the SQL-command with the MySQL QueryBrowser the server crashes also, so it is not an application problem.

After changing the tables to MyISAM the SQL statement works well, so it looks for an InnoDB problem.


The errolog-file from the MySQL server, after a InnoDB crash, tells the following:

050623 17:43:11 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050623 17:43:11 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 82784476.
InnoDB: Doing recovery: scanned up to log sequence number 0 82784476
InnoDB: Last MySQL binlog file position 0 79, file name C:\Programme\MySQL\MySQL_V41\logs\Log_Bin.000037
050623 17:43:12 InnoDB: Flushing modified pages from the buffer pool...
050623 17:43:12 InnoDB: Started; log sequence number 0 82784476
050623 17:43:12 [Warning] 'db' entry 'mysql application@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
050623 17:43:12 [Warning] 'db' entry 'em_cam application@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.


I changed to MySQL 4.1.12a without any result.

Maybe it is a problem with the my.ini???

[WinMySQLadmin]
Server =C:/Programme/MySQL/MySQL_V41/bin/mysqld-nt.exe
QueryInterval =10

[client]
;host = localhost
;password = my_password
port = 3306
;default-character-set = ucs2

[mysqld]
bind-address = Elektro14
port = 3306
;default-character-set = ucs2
log_bin = D:/M_PROG/_Test_EM_Server/Data/RDBMS/_Logs/Log_Bin.txt
server-id = 1
basedir = C:/Programme/MySQL/MySQL_V41
datadir = D:/M_PROG/_Test_EM_Server/Data/RDBMS
language = C:/Programme/MySQL/MySQL_V41/share/german
log-error = D:/M_PROG/_Test_EM_Server/Data/RDBMS/_Logs/Log_Error.txt
log = D:/M_PROG/_Test_EM_Server/Data/RDBMS/_Logs/Log_Query.txt
log-update = D:/M_PROG/_Test_EM_Server/Data/RDBMS/_Logs/Log_Update.txt
log-bin = D:/M_PROG/_Test_EM_Server/Data/RDBMS/_Logs/Log_Bin.txt
log-slow-queries = D:/M_PROG/_Test_EM_Server/Data/RDBMS/_Logs/Log_Slow_Query.txt
safe_user_create
default_storage_engine=innodb
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
innodb_data_home_dir = D:/M_PROG/_Test_EM_Server/Data/RDBMS/
innodb_data_file_path = ibdata1:25M
innodb_log_group_home_dir = D:/M_PROG/_Test_EM_Server/Data/RDBMS/_Logs/
innodb_log_arch_dir = D:/M_PROG/_Test_EM_Server/Data/RDBMS/_Logs/
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet =16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
;safe-updates

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout




But even it is a problem with the my.ini the server should keep on working.




best regards