| Bug #50310 | Text Fields are cut down to 256 Characters after ALTER TABLE | ||
|---|---|---|---|
| Submitted: | 13 Jan 2010 16:06 | Modified: | 13 Mar 2014 18:56 |
| Reporter: | Markus Mattzick | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | mysql-5.1-telco-6.3 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 256, ALTER TABLE, cluster, MySQL 5.1.30-ndb-6.3.20-cluster, mysql-5.1.39-ndb-7.0.10, TEXT fields | ||
[14 Jan 2010 9:25]
Hartmut Holzgraefe
As your table data doesn't seem too big: could you provide us with a dump of the unaltered tables create statement (the one in the bug report has errors in it) and data contents? And also the contents of your cluster configuration file? This would probably help reproducing this a lot ...
[18 Jan 2010 16:19]
Markus Mattzick
Hi, sorry for the mistakes in the CREATE statement. Running different tests it came down that I couldn't reproduce the bug with the MySQL Query Browser, but in the end it ist 100 % repeatable when I run a query within Navicat (Version 8.2.11). Thus it seems to be a Navicat Bug and I will close this report. Sorry for the fuss, Markus
[22 Jan 2010 8:04]
Markus Mattzick
Hello again,
after discussing that matter with the Navicat support I was able to reproduce the behaviour with the MySQL-Console as well.
Navicat(8.2.19) sends per default a "SET PROFILING=1;" with each query which is executed in a query-window. Thus the combination of "SET PROFILING=1; ALTER TABLE database_name.cut256_bug_test_copy_copy MODIFY COLUMN herstellungsjahr ENUM('Keine Angabe', '2002', '2003', '2004', '2005', '2006', '2007', '2008', '2009', '2010');" will corrupt the table data (check with "SELECT * FROM cut256_bug_test_copy_copy WHERE LENGTH(anmerkung)= 256;" before and after executing the ALTER-Query) and propably truncate at last one of the hidden tables (checking for "LENGTH(anmerkung) > 256" still returns records).
######################################
[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=2048M
IndexMemory=512M
NoOfFragmentLogFiles=12
RedoBuffer=32M
MaxNoOfAttributes=30000
MaxNoOfConcurrentOperations=100000
MaxNoOfTables=2000
MaxNoOfOrderedIndexes=4000
MaxNoOfUniqueHashIndexes=128
MaxNoOfTriggers=3000
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
#-- management server
[NDB_MGMD]
HostName=172.16.229.129
LogDestination=SYSLOG:facility=local7
#-- storage nodes
[NDBD]
HostName=172.16.229.130
DataDir=/var/lib/mysql-cluster
[NDBD]
HostName=172.16.229.131
DataDir=/var/lib/mysql-cluster
# app01
[MYSQLD]
HostName=172.16.229.128
# stage
[MYSQLD]
HostName=172.16.229.132
########################################
I'll upload a sample dump where I could reproduce the described behaviour with both, Navicat and the MySQL-Console.
Regards,
Markus Mattzick
[22 Jan 2010 14:22]
Markus Mattzick
P.S.: Error occured with Navicat 8.2.19 in the query window (not in the table design tool). Navicat runs a "SET PROFILING=1" with each query executed in a query window. This can be turned off with disableing the option "Show Profile and Status" in the "View"-Menu of the Query-window.
[28 Jan 2010 19:19]
Hartmut Holzgraefe
Thanks for providing the test dump, i was able to reproduce the problem with it.
[28 Jan 2010 19:25]
Hartmut Holzgraefe
Also effects ndb-7.0.10
[28 Jan 2010 19:34]
Hartmut Holzgraefe
When running a debug build the ALTER command in combination with PROFILING=1
crashes with the following backtrace:
#0 0x0000003e6000b9b2 in pthread_kill () from /lib64/libpthread.so.0
#1 0x0000000000b06ca1 in my_write_core (sig=6) at stacktrace.c:313
#2 0x00000000006ae4c3 in handle_segfault (sig=6) at mysqld.cc:2595
#3 <signal handler called>
#4 0x0000003e5f830265 in raise () from /lib64/libc.so.6
#5 0x0000003e5f831d10 in abort () from /lib64/libc.so.6
#6 0x0000003e5f8296e6 in __assert_fail () from /lib64/libc.so.6
#7 0x0000000000ab422e in NdbBlob::preCommit (this=0xfb62f70)
at NdbBlob.cpp:2975
#8 0x0000000000a57b66 in NdbTransaction::execute (this=0xfaadde0,
aTypeOfExec=NdbTransaction::Commit,
abortOption=NdbOperation::AbortOnError, forceSend=1)
at NdbTransaction.cpp:398
#9 0x00000000008f2ebd in execute_commit (thd_ndb=0xfaaca90, trans=0xfaadde0,
force_send=1, ignore_error=0, ignore_count=0x0) at ha_ndbcluster.cc:603
#10 0x00000000008ea5ea in ha_ndbcluster::flush_bulk_insert (this=0xfa95168,
allow_batch=false) at ha_ndbcluster.cc:5368
#11 0x00000000008f3f27 in ha_ndbcluster::fetch_next (this=0xfa95168,
cursor=0xfa9ee20) at ha_ndbcluster.cc:2728
#12 0x00000000008f403f in ha_ndbcluster::next_result (this=0xfa95168,
buf=0xfa97108 "�C\006\021") at ha_ndbcluster.cc:2760
#13 0x00000000008ec82e in ha_ndbcluster::rnd_next (this=0xfa95168,
buf=0xfa97108 "�C\006\021") at ha_ndbcluster.cc:4956
#14 0x00000000007ec881 in rr_sequential (info=0x41c184c0) at records.cc:385
#15 0x00000000008106bf in copy_data_between_tables (from=0xfaaa1d8,
to=0xfaa1598, create=@0x41c1a068, ignore=false, order_num=0, order=0x0,
copied=0x41c193e0, deleted=0x41c193d8, keys_onoff=LEAVE_AS_IS,
error_if_not_empty=false) at sql_table.cc:7925
#16 0x000000000081c81e in mysql_alter_table (thd=0xfa90a18,
new_db=0xfa64738 "test", new_name=0xfa643a0 "cut256_bug_test_copy_copy",
create_info=0x41c19720, table_list=0xfa64408, alter_info=0x41c1a020,
order_num=0, order=0x0, ignore=false) at sql_table.cc:7544
#17 0x00000000006c1211 in mysql_execute_command (thd=0xfa90a18)
at sql_parse.cc:2864
#18 0x00000000006c8246 in mysql_parse (thd=0xfa90a18,
inBuf=0xfa641e8 "ALTER TABLE cut256_bug_test_copy_copy MODIFY COLUMN herstellungsjahr ENUM('Keine Angabe', '2002', '2003', '2004', '2005', '2006', '2007', '2008', '2009', '2010')", length=161, found_semicolon=0x41c1aef0)
at sql_parse.cc:5937
#19 0x00000000006c9916 in dispatch_command (command=COM_QUERY, thd=0xfa90a18,
packet=0xfa788c9 "ALTER TABLE cut256_bug_test_copy_copy MODIFY COLUMN herstellungsjahr ENUM('Keine Angabe', '2002', '2003', '2004', '2005', '2006', '2007', '2008', '2009', '2010')", packet_length=161) at sql_parse.cc:1216
#20 0x00000000006cacc5 in do_command (thd=0xfa90a18) at sql_parse.cc:857
#21 0x00000000006b7827 in handle_one_connection (arg=0xfa90a18)
at sql_connect.cc:1133
#22 0x0000003e600064a7 in start_thread () from /lib64/libpthread.so.0
#23 0x0000003e5f8d3c2d in clone () from /lib64/libc.so.6
Description: After executing an ALTER TABLE query, some TEXT-fields are cut down to 256 chars (while others still have all text inside). Query executed: ALTER TABLE `table` MODIFY COLUMN `enum_field_3` ENUM('Keine Angabe','2002','2003','2004','2005','2006','2007','2008','2009','2010'); Table design: CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `foreign_key_1` int(11) DEFAULT NULL, `foreign_key_2` int(11) DEFAULT NULL, `foreign_key_3` int(11) DEFAULT NULL, `enum_field_1` enum('option_1','option_2') CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT 'option_1', `enum_field_2` enum('option_1','option_2') CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `enum_field_3` enum('Keine Angabe','2002','2003','2004','2005', '2006','2007','2008','2009','2010', '2011') DEFAULT NULL, `enum_field_4` enum('option_1','option_2','option_3','option_4','option_5','option_6', 'option_7','option_8','option_9','option_10','option_11','option_12','option_13') DEFAULT NULL, `varchar_field_1` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `varchar_field_2` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `varchar_field_3` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `varchar_field_4` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `varchar_field_5` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `varchar_field_6` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `text_field_1` text CHARACTER SET latin1 COLLATE latin1_german1_ci, `varchar_field_7` varchar(255) DEFAULT NULL, `varchar_field_8` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `varchar_field_9` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `varchar_field_10` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `varchar_field_11` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `set_field_1` set('option_1','option_2','option_3','option_4') CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `set_field_1` set('option_1','option_2','option_3','option_4') DEFAULT NULL, `text_field_2` text CHARACTER SET latin1 COLLATE latin1_german1_ci, `varchar_field_12` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `varchar_field_13` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `set_field_1` set('option_1','option_2') CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `set_field_2` set('option_1','option_2','option_3','option_4') CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `int_field_1` tinyint(4) DEFAULT NULL, `set_field_3` set('option_1','option_2','option_3','option_4','option_5','option_6') CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `set_field_4` set('option_1','option_2') CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL, `text_field_3` text CHARACTER SET latin1 COLLATE latin1_german1_ci, `int_field_1` tinyint(4) DEFAULT NULL, `int_field_2` tinyint(4) DEFAULT NULL, `int_field_3` tinyint(4) DEFAULT NULL, `int_field_4` tinyint(4) DEFAULT NULL, `int_field_5` tinyint(4) DEFAULT NULL, `int_field_6` tinyint(4) DEFAULT NULL, `int_field_7` tinyint(4) DEFAULT NULL, `int_field_8` tinyint(4) DEFAULT NULL, `int_field_9` tinyint(4) DEFAULT NULL, `int_field_10` tinyint(4) DEFAULT NULL, `int_field_11` tinyint(4) DEFAULT NULL, `int_field_12` tinyint(4) DEFAULT NULL, `varchar_field_14` varchar(255) DEFAULT NULL, `status` tinyint(4) DEFAULT NULL, `created` datetime DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `modified` datetime DEFAULT NULL, `modified_by` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uidx_varchar_field_14` (`varchar_field_14`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 Data length of table: 1216 KB, Rows: 1513 How to repeat: I tried several times, the bug appeared only in about 50 % of the tries. Tried also with "DEFAULT NULL" within the ALTER statement, same results: in 50 % some textfields where cut down to 256 characters.