MySQL Bugs: #39810: `#mysql50#raw_data#P#p20` already exists in InnoDB ...

Bug #39810	`#mysql50#raw_data#P#p20` already exists in InnoDB ...
Submitted:	2 Oct 2008 13:55	Modified:	24 Nov 2008 17:31
Reporter:	SeungEun Choi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	mysql-5.1.26-rc-linux-x86_64-glibc23	OS:	Linux (Linux tiara.log-11 2.6.9-67.ELsmp #1 SMP Wed Nov 7 13:56:44 EST 2007 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	drop and add partition error

Description:

081002 22:24:29  InnoDB: Error: table `tiara`.081002 22:24:29 [ERROR] Invalid (old?) table or database name 'raw_data#P#p20'
/daum/program/mysql/bin/mysqld(print_stacktrace+0x24)[0x6cb7e4]
/daum/program/mysql/bin/mysqld(handle_segfault+0x1fe)[0x5b458e]
/lib64/tls/libpthread.so.0[0x36d5f0c5b0]
/daum/program/mysql/bin/mysqld(row_get_mysql_key_number_for_index+0x9)[0x795639]
/daum/program/mysql/bin/mysqld(_ZN11ha_innobase4infoEj+0x1d4)[0x718dc4]
/daum/program/mysql/bin/mysqld(_ZN7handler11print_errorEii+0x113)[0x681183]
/daum/program/mysql/bin/mysqld(_ZN12ha_partition11print_errorEii+0x24)[0x68aaf4]
/daum/program/mysql/bin/mysqld[0x515136]
/daum/program/mysql/bin/mysqld(_Z26fast_alter_partition_tableP3THDP8st_tableP10Alter_infoP24st_ha_create_informationP10TABLE_LISTPcPKcj+0x4fe)[0x51298e]
/daum/program/mysql/bin/mysqld(_Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP10TABLE_LISTP10Alter_infojP8st_orderb+0xa9e)[0x6927ae]
/daum/program/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x962)[0x5c0b12]
/daum/program/mysql/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x141)[0x5c5fe1]
/daum/program/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x3db)[0x5bee6b]
/daum/program/mysql/bin/mysqld(_Z10do_commandP3THD+0xb8)[0x5bea48]
/daum/program/mysql/bin/mysqld(handle_one_connection+0x11d)[0x5bce5d]
/lib64/tls/libpthread.so.0[0x36d5f06137]
/lib64/tls/libc.so.6(__clone+0x73)[0x36d54c7533]
`#mysql50#raw_data#P#p20` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
081002 22:24:29 - 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=2097152
max_used_connections=52
max_threads=1000
threads_connected=31
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 10381150 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x2b2c62fed0
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...
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2b72364d00  is invalid pointer
thd->thread_id=83701
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
081002 22:24:30 mysqld_safe Number of processes running now: 0
081002 22:24:30 mysqld_safe mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
081002 22:24:31  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...
081002 22:24:32  InnoDB: Started; log sequence number 139 4124041861
081002 22:24:32 [Note] Event Scheduler: Loaded 0 events
081002 22:24:32 [Note] /daum/program/mysql/bin/mysqld: ready for connections.
Version: '5.1.26-rc-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
081002 22:24:32 [Note] Event Scheduler: scheduler thread started with id 1

How to repeat:

        (SELECT '*' FROM information_schema.PARTITIONS P WHERE P.TABLE_SCHEMA='tiara' AND PARTITION_NAME=@PNAME)

 return exists partitons..

Thank you for a problem report. Please, provide exact steps to repeat the problem.

ALTER TABLE raw_data ADD PARTITION (PARTITION p00 VALUES IN (0, 1, 2, 3, 4))

printed priv message

Thank you for the feedback.

Please provide output of SHOW CREATE TABLE raw_data. Also please try with current version 5.1.28.

DROP TABLE IF EXISTS `tiara`.`raw_data`;
CREATE TABLE  `tiara`.`raw_data` (
  `ip` char(15) DEFAULT NULL,
  `access_time` datetime NOT NULL,
  `partkey` tinyint(3) unsigned DEFAULT '0',
  `nil_profile` char(20) DEFAULT NULL,
  `nil_id` char(20) DEFAULT NULL,
  `nil_tag` char(20) DEFAULT NULL,
  `nil_tag_no` char(20) DEFAULT NULL,
  `user_agent` char(100) DEFAULT NULL,
  `screen_size` char(10) DEFAULT NULL,
  `load_time` int(10) unsigned DEFAULT NULL,
  `window_size` char(10) DEFAULT NULL,
  `click_position` char(10) DEFAULT NULL,
  `url` varchar(1024) DEFAULT NULL,
  `ref` varchar(1024) DEFAULT NULL,
  `biryr` int(10) unsigned DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `userid` char(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `ishome` char(1) DEFAULT NULL,
  `uuid` bigint(20) DEFAULT NULL,
  `login` char(1) DEFAULT NULL,
  `suuid` bigint(20) DEFAULT NULL,
  `suuid_seq` bigint(20) DEFAULT NULL,
  `nil_no` varchar(128) DEFAULT NULL,
  KEY `Index_raw_log_access_time_partkey` (`access_time`),
  KEY `index_nilTagTagNo` (`nil_tag`,`nil_tag_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED /*!50100 PARTITION BY LIST ( partkey) (PARTITION p55 VALUES IN (55,56,57,58,59) ENGINE = InnoDB, PARTITION p00 VALUES IN (0,1,2,3,4) ENGINE = InnoDB, PARTITION p05 VALUES IN (5,6,7,8,9) ENGINE = InnoDB, PARTITION p10 VALUES IN (10,11,12,13,14) ENGINE = InnoDB, PARTITION p15 VALUES IN (15,16,17,18,19) ENGINE = InnoDB, PARTITION p20 VALUES IN (20,21,22,23,24) ENGINE = InnoDB, PARTITION p25 VALUES IN (25,26,27,28,29) ENGINE = InnoDB, PARTITION p30 VALUES IN (30,31,32,33,34) ENGINE = InnoDB, PARTITION p35 VALUES IN (35,36,37,38,39) ENGINE = InnoDB, PARTITION p40 VALUES IN (40,41,42,43,44) ENGINE = InnoDB, PARTITION p45 VALUES IN (45,46,47,48,49) ENGINE = InnoDB, PARTITION p50 VALUES IN (50,51,52,53,54) ENGINE = InnoDB) */;

Please send the entire .err log of the database server! The first error is the most interesting.

Assigning this partition bug to Inaam.

--Heikki

In addition to Heikki's question above, what are the EXACT steps to repeat the problem. Assuming we already have the table as in the above create statement, what should we do: issue ALTER TABLE as above, issue SELECT from information_schema tables as above, or both. I tried bot as well as some other variations of ALTER TABLE but didn't see any crash

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".