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:
None 
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
Description:
I cannot create InnoDB table.

060708 21:27:38  InnoDB: Error: table `classifigratis/A` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
060708 21:28:15InnoDB: Assertion failure in thread 12209072 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=14
max_connections=600
threads_connected=7
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=0x8f1d7d0
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=0xb9dd5c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8178922
0xd20888
0x4
0x821f544
0x821fde4
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 0x8e47c90 = CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB
thd->thread_id=351
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
060708 21:28:15  mysqld restarted
InnoDB: !!!!!!!!!!!!!! UNIV_DEBUG switched on !!!!!!!!!!!!!!!
060708 21:28:16  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...
060708 21:28:16  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1935247.
InnoDB: Doing recovery: scanned up to log sequence number 0 1935247
InnoDB: Last MySQL binlog file position 0 79, file name ./servidor01-bin.000050
060708 21:28:17  InnoDB: Flushing modified pages from the buffer pool...
060708 21:28:20  InnoDB: Started; log sequence number 0 1935247
/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)

How to repeat:
[root@servidor01 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 351 to server version: 4.1.20-max-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 = InnoDB;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[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".