Bug #20923 | Error creating InnoDB table / Lost connection to MySQL server during query | ||
---|---|---|---|
Submitted: | 9 Jul 2006 0:32 | Modified: | 9 Sep 2006 15:02 |
Reporter: | Luiz Boaretto | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 4.1.20 | OS: | Linux (RHEL 4/kernel 2.6.9-34.0.1.EL) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[9 Jul 2006 0:32]
Luiz Boaretto
[9 Jul 2006 0:44]
Luiz Boaretto
mysql> use classifigratis; Database changed mysql> CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE A ENGINE = InnoDB; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> quit Bye
[9 Jul 2006 1:15]
MySQL Verification Team
Thank you for the bug report. I was unable to repeat with current source server, please upgrade for the latest version and try again. Thanks in advance. miguel@hegel:~/dbs/4.0> bin/mysqladmin -uroot create classifigratis miguel@hegel:~/dbs/4.0> bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.27-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use classifigratis; Database changed mysql> CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE A ENGINE = InnoDB; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table A\G *************************** 1. row *************************** Table: A Create Table: CREATE TABLE `A` ( `A` int(11) NOT NULL default '0', `B` int(11) default NULL ) TYPE=InnoDB 1 row in set (0.00 sec) mysql>
[9 Jul 2006 1:55]
Luiz Boaretto
I don't have any reference to 'innodb' configuration in my.cnf. If I change settings in reference, MySQL don't start.
[9 Jul 2006 3:41]
Luiz Boaretto
My version is the latest of 4.1.x!
[9 Jul 2006 5:09]
MySQL Verification Team
Sorry I missed the correct version. Could you please verify the output of the below command: miguel@hegel:~/dbs/4.1> bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.21-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show variables like "have_innodb"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_innodb | YES | +---------------+-------+ 1 row in set (0.00 sec) mysql> I was not able to repeat with 4.1 latest source server too. Also please provide your my.cnf and the message errors when the server doesn't start as you mentioned before. Thanks in advance.
[9 Jul 2006 5:19]
Luiz Boaretto
[root@servidor01 SPECS]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4683 to server version: 4.1.20-max-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show variables like "have_innodb"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_innodb | YES | +---------------+-------+ 1 row in set (0.01 sec) my my.cnf: [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking query_cache_type=1 query_cache_limit=1M query_cache_size=32M max_connections=600 interactive_timeout=100 wait_timeout=15 connect_timeout=10 table_cache=512 thread_cache=32 key_buffer=128M thread_concurrency=2 log_slow_queries=/var/log/mysql/slow.log long_query_time=2 log=/var/log/mysql/mysqld.log log-bin server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout [safe_mysqld] err-log=/var/log/mysql/mysqld-err.log --------------------------------- after, with innodb: innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 I get in mysqld-err.log: 060709 2:15:36 [Note] /usr/sbin/mysqld-max: Normal shutdown 060709 2:15:36 InnoDB: Starting shutdown... 060709 2:15:39 InnoDB: Shutdown completed; log sequence number 0 1935445 060709 2:15:40 [Note] /usr/sbin/mysqld-max: Shutdown complete 060709 02:15:40 mysqld ended 060709 02:15:40 mysqld started InnoDB: !!!!!!!!!!!!!! UNIV_DEBUG switched on !!!!!!!!!!!!!!! InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size InnoDB: 1152 pages (rounded down to MB) InnoDB: than specified in the .cnf file 128000 pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 060709 2:15:41 [ERROR] Can't init databases 060709 2:15:41 [ERROR] Aborting 060709 2:15:41 [Note] /usr/sbin/mysqld-max: Shutdown complete 060709 02:15:41 mysqld ended whith another configuration: innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:100M:autoextend set-variable = innodb_buffer_pool_size=100M set-variable = innodb_additional_mem_pool_size=10M innodb_flush_log_at_trx_commit=1 I get: 060709 02:18:32 mysqld started InnoDB: !!!!!!!!!!!!!! UNIV_DEBUG switched on !!!!!!!!!!!!!!! InnoDB: Error: auto-extending data file /var/lib/mysql/ibdata1 is of a different size InnoDB: 1152 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 6400 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 060709 2:18:32 [ERROR] Can't init databases 060709 2:18:32 [ERROR] Aborting 060709 2:18:32 [Note] /usr/sbin/mysqld-max: Shutdown complete 060709 02:18:32 mysqld ended Thank you.
[12 Jul 2006 1:06]
Luiz Boaretto
You need more info? Thank you!
[12 Jul 2006 1:27]
MySQL Verification Team
Olá, Notice the error messages you got, when you tried to setup the InnoDB variables in your my.cnf you typed the sizes with different values than actual sizes you have already on your data directory, I guess you have the default sizes (when the server is started at first time without to use the variables in the my.cnf) so just take a look in that directory i.e: miguel@hegel:~/dbs/5.0> ls var/ib* -la -rw-rw---- 1 miguel users 10485760 2006-07-11 11:53 var/ibdata1 -rw-rw---- 1 miguel users 5242880 2006-07-11 11:53 var/ib_logfile0 -rw-rw---- 1 miguel users 5242880 2006-07-07 12:32 var/ib_logfile1 also when you did the failed setup, see the recommendation done by the InnoDB engine: InnoDB: Error: auto-extending data file /var/lib/mysql/ibdata1 is of a different size InnoDB: 1152 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 6400 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! Before that issue did you create and filled InnoDB tables?
[12 Jul 2006 1:52]
Luiz Boaretto
Hi, My ibdata1 have 19MB and I changed to my.cnf to: innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend set-variable = innodb_buffer_pool_size=100M set-variable = innodb_additional_mem_pool_size=10M innodb_flush_log_at_trx_commit=1 mysql start normally but, when I try to create the table... mysql> use test; Database changed mysql> CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> quit Bye mysql-err.log: 060711 22:42:13 mysqld started InnoDB: !!!!!!!!!!!!!! UNIV_DEBUG switched on !!!!!!!!!!!!!!! InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060711 22:42:14 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060711 22:42:14 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 060711 22:42:14 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060711 22:42:21 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.20-max-debug-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Experimental (GPL) 060711 22:47:05InnoDB: Assertion failure in thread 57150384 in file ./../include/dict0dict.ic line 96 InnoDB: Failing assertion: table->cached InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=13 max_connections=600 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1436650 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8f2a1e8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x3679d5c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8178922 0xd20888 0x4 0x821f544 0x821fde4 0x820c726 0x81fb99a 0x823c3bf 0x8192fb8 0x819355b 0x81940f2 Stack trace seems successful - bottom reached Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8e8f118 = CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB thd->thread_id=491 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 060711 22:47:05 mysqld restarted InnoDB: !!!!!!!!!!!!!! UNIV_DEBUG switched on !!!!!!!!!!!!!!! 060711 22:47:05 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... 060711 22:47:06 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43682. InnoDB: Doing recovery: scanned up to log sequence number 0 43682 InnoDB: Last MySQL binlog file position 0 79, file name ./servidor01-bin.000062 060711 22:47:06 InnoDB: Flushing modified pages from the buffer pool... 060711 22:47:09 InnoDB: Started; log sequence number 0 43682 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.20-max-debug-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Experimental (GPL)
[12 Jul 2006 5:43]
Luiz Boaretto
For more info: I'm running mysql on Pentium 4 2.6Ghz, 1GB RAM, 2x 80G HDD. and: - Apache with php (sites with "hard" access) - SMTP Server, pop/imap using mysql (with "hard" spam traffic hehehe); - FTP Server using mysql If you need more information... Thank you!
[13 Jul 2006 4:02]
Luiz Boaretto
Now, I downgrade to 4.1.19 and InnoDB table has created with success... Probably bug in 4.1.20.
[22 Jul 2006 2:43]
MySQL Verification Team
Thank you for the feedback. Have you enabled SELinux?
[22 Jul 2006 2:51]
Luiz Boaretto
No. [root@servidor01 ~]# sestatus SELinux status: disabled [root@servidor01 ~]# Thank you
[26 Jul 2006 20:09]
Sveta Smirnova
Please, describe your INNODB tables more in detail: your ibdata files were created by earlier version of MySQL? If so, please, change data directory location, create default tables there and try to create InnoDB table in such configuration. Also, please, name distribution you use.
[27 Jul 2006 3:23]
Luiz Boaretto
In this server, I installed version 4.1.19, the 4.1.20 is not released at this time. I still installed and I created the InnoDB tables in version 4.1.19, and when released version 4.1.20, I upgraded and in the attempt to create other InnoDB tables, he gave problem. When I came back toward version 4.1.19, seems to be all OK. All the procedure is registered here. I cannot place version 4.1.20 therefore already I had serious problems with my customers... If I will be able to help you, please speak to me what I must make. More information: processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Pentium(R) 4 CPU 2.60GHz stepping : 9 cpu MHz : 2600.929 cache size : 512 KB fdiv_bug : no hlt_bug : no f00f_bug : no coma_bug : no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr bogomips : 5205.20 ------- MemTotal: 1027668 kB MemFree: 679640 kB Buffers: 61452 kB Cached: 40764 kB SwapCached: 46804 kB Active: 247064 kB Inactive: 58772 kB HighTotal: 122816 kB HighFree: 3948 kB LowTotal: 904852 kB LowFree: 675692 kB SwapTotal: 2048276 kB SwapFree: 1890844 kB Dirty: 380 kB Writeback: 0 kB Mapped: 216984 kB Slab: 31320 kB Committed_AS: 1544688 kB PageTables: 3864 kB VmallocTotal: 106488 kB VmallocUsed: 3100 kB VmallocChunk: 103232 kB HugePages_Total: 0 HugePages_Free: 0 Hugepagesize: 4096 kB ------- Red Hat Enterprise Linux ES release 4 (Nahant Update 3) Kernel 2.6.9-34.0.1.EL glibc 2.3.4-2.19 ------- You need some another information specify? Thank you
[27 Jul 2006 6:08]
Sveta Smirnova
>You need some another information specify? Yes. We have some packages available for RHEL. Please, exact specify which 4.1.19 and 4.1.20 packages you use (name of downloaded files). I ask because nobody did not repeat the problem using 4.1.20 sources. Thank you.
[27 Jul 2006 15:38]
Luiz Boaretto
Hi, I compiled RPM sourcr packages [root@servidor01 ~]# rpm -qa|grep MySQL MySQL-Max-4.1.19-0.glibc23 MySQL-debuginfo-4.1.19-0.glibc23 MySQL-server-4.1.19-0.glibc23 MySQL-shared-4.1.19-0.glibc23 MySQL-client-4.1.19-0.glibc23 MySQL-devel-4.1.19-0.glibc23 MySQL-embedded-4.1.19-0.glibc23 for 4.1.20 too.. Thank you
[28 Jul 2006 18:17]
Sveta Smirnova
Today has been released MySQL 4.1.21. Could you please try to repeat the problem using this new version?
[7 Aug 2006 16:23]
Luiz Boaretto
It forgives the delay, I was travelling. I will be providing upgrade for the new version. Thank you.
[9 Aug 2006 15:02]
Sveta Smirnova
I changed status of the bug, because we still wait results of your upgrade.
[9 Sep 2006 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".