Tables are lost for DDL and different behaviors for alter table failed situation between innobase and innodb_plugin
*summary:*
Recently we hit lost tables during DDL for online products, and after some
observersion, we found some interesting hehaviors if fil_rename_tablespace
failed as retry 25.
That might be another issue that not discussed here.
THERE MUST BE SINGLE TABLESPACE
Here is the steps to show the behavior:
*1. compile 5.1.48 source code.*
CFLAGS=-O0 -g CXX=gcc CXXFLAGS=-O0 -g -felide-constructors \
-fno-exceptions -fno-rtti
export CFLAGS CXXFLAGS
./configure --with-plugins=innodb_plugin
make -j32
*2. debug with plugin innodb*
2.1 # attach a running pid
(gdb) b fil_rename_tablespace
Breakpoint 1 at 0x2abfed30: file fil/fil0fil.c, line 2435.
(gdb) c
Continuing.
[Switching to Thread 0x4944a940 (LWP 7069)]
Breakpoint 1, fil_rename_tablespace (old_name=0xeacdc20 sbtest/sb1, id=51,
new_name=0xea96fa0 sbtest/#sql2-1b8b-1) at fil/fil0fil.c:2435
2435{
(gdb) c
Continuing.
Breakpoint 1, fil_rename_tablespace (old_name=0xeabb3f0
sbtest/#sql-1b8b_1, id=52, new_name=0xea96fa0 sbtest/sb1) at
fil/fil0fil.c:2435
2435{
(gdb) return 0
Make fil_rename_tablespace return now? (y or n) y
#0 0x2abea8c4 in dict_table_rename_in_cache (table=0xeabb288,
new_name=0xea96fa0 sbtest/sb1,
rename_also_foreigns=1) at dict/dict0dict.c:951
951 } else if (!fil_rename_tablespace(old_name,
table-space,
(gdb)
(gdb) l
946 fputs( (, stderr);
947 ut_print_filename(stderr,
948
table-dir_path_of_temp_table);
949 fputs( )\n, stderr);
950 return(FALSE);
951 } else if (!fil_rename_tablespace(old_name,
table-space,
952 new_name)) {
953 return(FALSE);
954 }
955 }
(gdb) finish
Run till exit from #0 0x2abea8c4 in dict_table_rename_in_cache
(table=0xeabb288, new_name=0xea96fa0 sbtest/sb1, rename_also_foreigns=1)
at dict/dict0dict.c:951
0x2ac6527e in row_rename_table_for_mysql (old_name=0xea97000
sbtest/#sql-1b8b_1, new_name=0xea96fa0 sbtest/sb1, trx=0xeacf728,
commit=1)
at row/row0mysql.c:3905
3905if (!dict_table_rename_in_cache(table, new_name,
Value returned is $1 = 0
(gdb) l
3900trx-error_state = DB_SUCCESS;
3901} else {
3902/* The following call will also rename the .ibd data
file if
3903the table is stored in a single-table tablespace */
3904
3905if (!dict_table_rename_in_cache(table, new_name,
3906!new_is_tmp)) {
3907trx-error_state = DB_SUCCESS;
3908trx_general_rollback_for_mysql(trx, NULL);
3909trx-error_state = DB_SUCCESS;
(gdb) l
3910goto funct_exit;
3911}
3912
3913/* We only want to switch off some of the type
checking in
3914an ALTER, not in a RENAME. */
3915
3916err = dict_load_foreigns(
3917new_name, !old_is_tmp ||
trx-check_foreigns);
3918
3919if (err != DB_SUCCESS) {
(gdb) finish
Run till exit from #0 0x2ac6527e in row_rename_table_for_mysql
(old_name=0xea97000 sbtest/#sql-1b8b_1, new_name=0xea96fa0 sbtest/sb1,
trx=0xeacf728, commit=1) at row/row0mysql.c:3905
0x2ac14e0b in ha_innodb::rename_table () from
/u01/mysql-5.1.48/storage/innodb_plugin/.libs/ha_innodb_plugin.so
Value returned is $2 = 10
2.2 here is the key routines:
storage/innodb_plugin/row/row0mysql.c: row_rename_table_for_mysql
3905 if (!dict_table_rename_in_cache(table, new_name,
3906 !new_is_tmp)) {
3907 trx-error_state = DB_SUCCESS;
3908 trx_general_rollback_for_mysql(trx, NULL);
3909 trx-error_state = DB_SUCCESS;
3910 goto funct_exit;
3911 }
2.3 client's output:
root@sbtest 05:00:24alter table sb1 add column d2 int;
Query OK, 0 rows affected (9 min 47.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@sbtest 05:11:26desc sb1;
ERROR 1146 (42S02): Table 'sbtest.sb1' doesn't exist
*3. different code between innodb and plugin*
3.1 innodb add err = DB_ERROR; before goto funct_exit; when
dict_table_rename_in_cache failed.
3.2 test innodb work and result could be refered to modified innodb plugin
just like innobase behavior, add err = DB_ERROR; before exit
3905 if (!dict_table_rename_in_cache(table, new_name,
3906 !new_is_tmp)) {
3907 trx-error_state = DB_SUCCESS;
3908 trx_general_rollback_for_mysql(trx, NULL);
3909
mysql tables are lost for DDL of alter table .. add column ...
mysql tables are lost for DDL of alter table .. add column ... *1. mysqld's error.log* 110803 3:39:16 InnoDB: Warning: problems renaming 'feel_22/#sql-2635_23d3a8' to 'feel_22/feed_send_1451', 25000 iterations （first 25000，fil0fil.c:: fil_rename_tablespace） InnoDB: Warning: tablespace './feel_22/#sql-2635_23d3a8.ibd' has i/o ops stopped for a long time 24999 （fil0fil.c:: fil_mutex_enter_and_prepare_for_io） 110803 3:39:16 InnoDB: Warning: problems renaming 'feel_22/#sql-2635_23d3a8' to 'feel_22/feed_send_1451', 25001 iterations （over 25000, return FALSE） 110803 3:39:16 [ERROR] Cannot find or open table feel_22/feed_send_1451 from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn't support. there is another interesting error info: Error 1005: Can't create table 'feel_01.#sql-57f0_25a510' (errno: -1) *2. rename tablespace can be successful only if:* if (node-n_pending 0 || node-n_pending_flushes 0) if (node-modification_counter node-flush_counter) *3. we failed to repeat this bug and failed to locate the real reason. * Here is the information we got: * it's single table space with innodb plugin 1.0.9 and mysql-5.1.48. we failed to repeat this issue. * DDL is done at mid-night, and workload is very very slow(both master and slave suffered with issue but with very very low probability). * for 100G ibd file, fsync is so quickly that retry number is 0 for rename condition waiting, so we don't doult the n_pending_flushes. * all the sql are blocked when 2 retry first hit, but unfornantely no other stack/core info saved. * we suspect the io handlers are out-of-order,io_handler_threads and srv_master_thread maybe all os_event_wait_low. We can not prove the deadlock situation, but if the deadlock is exist in such situation, the patch maybe help us to suffering occasional table losting for DDL --- /tmp/mysql-5.1.48/storage/innodb_plugin/fil/fil0fil.c 2010-06-03 23:50:08.0 +0800 +++ storage/innodb_plugin/fil/fil0fil.c 2011-08-11 00:23:31.0 +++ +0800 @@ -938,8 +938,24 @@ mutex_exit(fil_system-mutex); + +#ifndef UNIV_HOTBACKUP +/* Wake the i/o-handler threads to make sure pending i/o's are + performed */ +os_aio_simulated_wake_handler_threads(); + os_thread_sleep(2); + /* Flush tablespaces so that we can close modified files in the LRU + list */ + +fil_flush_file_spaces(FIL_TABLESPACE); +#else + + os_thread_sleep(2); +#endif + + count2++; goto retry; @@ -2457,6 +2473,11 @@ fputs( to , stderr); ut_print_filename(stderr, new_name); fprintf(stderr, , %lu iterations\n, (ulong) count); + if (node) + fprintf(stderr, node info: n_pending=%lu, n_pending_flushes=%lu +modification_counter=%lu, flush_counter=%lu\n, +node-n_pending, node-n_pending_flushes, node-modification_counter, +node-flush_counter); } mutex_enter(fil_system-mutex); *4. we need yours help to solve the table lost issue*
error log rotation problem
Hey everyone, I have run across something that has me stumped. I have some systems that have very large error logs because we haven't moved from statement-based to mixed-based replication yet so they get a lot of warnings logged. I need to rotate the error logs and have started looking at it doing so. The problem is that on one system a normal course of action works perfectly, but on anther it does not. And these systems were installed from the same RPM packages (5.1.50 -- downloaded from the MySQL website). Here is what I do: log in with mysql client and 'flush logs' OR mysqladmin --flush-log It should rename the old log file to mysqld.log-old and start a new mysqld.log file. On one system it works perfectly On the other...nothing. I tried moving the error log (mv /var/log/mysqld/mysqld.log /var/log/mysqld.log.old) and then issuing the flush logs command...it stays writing to the old file and never makes a new one. If I were to restart mysqld it would solve the problem but this is a production system and that isn't very practical. These systems are very similar. my.cnfs have been checked for differences. I searched the interwebs and specifically bugs.mysql.com for something similar. Not finding anything. I would appreciate any ideas! thanks, Keith
Import from Quicken 2004 Mac?
I'm looking for ways to import QuickBooks 2010 Mac. I've only just started researching this, so feel free to RTFM me -- with a proper reference, of course! I'll be wanting to set up a process to do this periodically (and hopefully, automagically) for new transactions. QB 2010 Mac appears to only export .IIF format, which appears to be a variant of the older .QIF format, and Google didn't turn up really anything for getting IIF/QIF files into MySQL. The best I could find would be importing them into Excel first, then CSV out of Excel into MySQL, which sounds like a lot of bother and not readily scriptable for routine use. I find it hard to believe I'm the first one to ever attempt this! IIF/QIF seems to be a rather unusual format. Lacking a one-step MySQL import tool, does anyone know of good parsers and translators for IIF/QIF that may be useful? Thanks in advance for any advice offered! Science uses mathematics to predict the future; economics uses statistics to predict the past. -- Jeff Barton Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Import from Quicken 2004 Mac?
The QIF file includes a lot of data aside from basic transactions, what exactly are you trying to end up with at the end of the day? Simply a copy of your QB data in Mysql? -Original Message- From: Jan Steinman [mailto:j...@bytesmiths.com] Sent: Thursday, August 11, 2011 2:15 PM To: mysql@lists.mysql.com Subject: Import from Quicken 2004 Mac? I'm looking for ways to import QuickBooks 2010 Mac. I've only just started researching this, so feel free to RTFM me -- with a proper reference, of course! I'll be wanting to set up a process to do this periodically (and hopefully, automagically) for new transactions. QB 2010 Mac appears to only export .IIF format, which appears to be a variant of the older .QIF format, and Google didn't turn up really anything for getting IIF/QIF files into MySQL. The best I could find would be importing them into Excel first, then CSV out of Excel into MySQL, which sounds like a lot of bother and not readily scriptable for routine use. I find it hard to believe I'm the first one to ever attempt this! IIF/QIF seems to be a rather unusual format. Lacking a one-step MySQL import tool, does anyone know of good parsers and translators for IIF/QIF that may be useful? Thanks in advance for any advice offered! Science uses mathematics to predict the future; economics uses statistics to predict the past. -- Jeff Barton Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Import from Quicken 2004 Mac?
On 11 Aug 11, at 14:17, David Brian Chait wrote: The QIF file includes a lot of data aside from basic transactions, what exactly are you trying to end up with at the end of the day? Simply a copy of your QB data in Mysql? That would be a good start. We don't need a complete duplicate, but in my experience, it's easier to get it all and winnow out the bits you don't want than to selectively import. That said, we really only need the basic transaction info: date, payee, amount, memo, category, account from, account to. This is to reconcile the chart of accounts (in Quick Books) with project management (in MySQL). From: Jan Steinman [mailto:j...@bytesmiths.com] Sent: Thursday, August 11, 2011 2:15 PM To: mysql@lists.mysql.com Subject: Import from Quicken 2004 Mac? I'm looking for ways to import QuickBooks 2010 Mac. I've only just started researching this, so feel free to RTFM me -- with a proper reference, of course! I'll be wanting to set up a process to do this periodically (and hopefully, automagically) for new transactions. QB 2010 Mac appears to only export .IIF format, which appears to be a variant of the older .QIF format, and Google didn't turn up really anything for getting IIF/QIF files into MySQL. The best I could find would be importing them into Excel first, then CSV out of Excel into MySQL, which sounds like a lot of bother and not readily scriptable for routine use. I find it hard to believe I'm the first one to ever attempt this! IIF/QIF seems to be a rather unusual format. Lacking a one-step MySQL import tool, does anyone know of good parsers and translators for IIF/QIF that may be useful? Thanks in advance for any advice offered! Science uses mathematics to predict the future; economics uses statistics to predict the past. -- Jeff Barton Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.com Always do right. This will surprise some people and astonish the rest. -- Mark Twain Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Import from Quicken 2004 Mac?
I don't think I have ever heard of anyone directly importing a QIF into any relational database, you would have to translate the resulting data into a delimited txt file and then import. You may want to check to see if Quickbooks has an API that you can use to access the data natively rather than trying to move it around from platform to platform. -Original Message- From: Jan Steinman [mailto:j...@bytesmiths.com] Sent: Thursday, August 11, 2011 2:37 PM To: David Brian Chait Cc: mysql@lists.mysql.com Subject: Re: Import from Quicken 2004 Mac? On 11 Aug 11, at 14:17, David Brian Chait wrote: The QIF file includes a lot of data aside from basic transactions, what exactly are you trying to end up with at the end of the day? Simply a copy of your QB data in Mysql? That would be a good start. We don't need a complete duplicate, but in my experience, it's easier to get it all and winnow out the bits you don't want than to selectively import. That said, we really only need the basic transaction info: date, payee, amount, memo, category, account from, account to. This is to reconcile the chart of accounts (in Quick Books) with project management (in MySQL). From: Jan Steinman [mailto:j...@bytesmiths.com] Sent: Thursday, August 11, 2011 2:15 PM To: mysql@lists.mysql.com Subject: Import from Quicken 2004 Mac? I'm looking for ways to import QuickBooks 2010 Mac. I've only just started researching this, so feel free to RTFM me -- with a proper reference, of course! I'll be wanting to set up a process to do this periodically (and hopefully, automagically) for new transactions. QB 2010 Mac appears to only export .IIF format, which appears to be a variant of the older .QIF format, and Google didn't turn up really anything for getting IIF/QIF files into MySQL. The best I could find would be importing them into Excel first, then CSV out of Excel into MySQL, which sounds like a lot of bother and not readily scriptable for routine use. I find it hard to believe I'm the first one to ever attempt this! IIF/QIF seems to be a rather unusual format. Lacking a one-step MySQL import tool, does anyone know of good parsers and translators for IIF/QIF that may be useful? Thanks in advance for any advice offered! Science uses mathematics to predict the future; economics uses statistics to predict the past. -- Jeff Barton Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.com Always do right. This will surprise some people and astonish the rest. -- Mark Twain Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
different size under the two OS
Hello DBAs, Though this is not exactly a mysql problem, but I think this list may be helpful for my question. I have dumped a mysql data file, and scp it to another host. The current host is ubuntu-8.04, the remote host is ubuntu-9.10. As you can see below: The current host: $ md5sum fcm.0812.sql.gz ea08ec505c1b1724213538fed7483975 fcm.0812.sql.gz $ lsb_release -r Release:8.04 $ du -k fcm.0812.sql.gz 418080 fcm.0812.sql.gz The remote host: $ md5sum fcm.0812.sql.gz ea08ec505c1b1724213538fed7483975 fcm.0812.sql.gz $ lsb_release -r Release:9.10 $ du -k fcm.0812.sql.gz 417672 fcm.0812.sql.gz Though the files in two hosts have the same md5sum, but why they have different size with 'du -k' showed? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: different size under the two OS
du reports how much space the file takes on the disk. This # depends on the block size of each file system. On Aug 11, 2011 9:13 PM, Feng He short...@gmail.com wrote: Hello DBAs, Though this is not exactly a mysql problem, but I think this list may be helpful for my question. I have dumped a mysql data file, and scp it to another host. The current host is ubuntu-8.04, the remote host is ubuntu-9.10. As you can see below: The current host: $ md5sum fcm.0812.sql.gz ea08ec505c1b1724213538fed7483975 fcm.0812.sql.gz $ lsb_release -r Release:8.04 $ du -k fcm.0812.sql.gz 418080 fcm.0812.sql.gz The remote host: $ md5sum fcm.0812.sql.gz ea08ec505c1b1724213538fed7483975 fcm.0812.sql.gz $ lsb_release -r Release:9.10 $ du -k fcm.0812.sql.gz 417672 fcm.0812.sql.gz Though the files in two hosts have the same md5sum, but why they have different size with 'du -k' showed? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
