| Bug #11178 | Alter table corrupts tables with fulltext index | ||
|---|---|---|---|
| Submitted: | 8 Jun 2005 16:30 | Modified: | 29 Jun 2005 16:47 |
| Reporter: | Marcus Beranek | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | mysql-standard-4.0.20-pc-linux-i686-icc | OS: | Linux (linux 2.4.28) |
| Assigned to: | CPU Architecture: | Any | |
[8 Jun 2005 16:30]
Marcus Beranek
[10 Jun 2005 14:28]
MySQL Verification Team
Hello, Thank you for the report, but I wasn't able reproduce it with my test table/data. Can you create a repeatable test case?
[21 Jun 2005 11:41]
Marcus Beranek
Hello, sorry for my late awnser, but I was away for a few days. First, I give you an overview about my systems, which are running mysql: System-1 Debian Woody 3.0 (running on a P4 CPU 2.00GHz) uname -a Linux gonzo 2.4.28 #1 SMP Don Nov 25 11:31:48 CET 2004 i686 unknown mysqld -v mysqld Ver 4.0.20-standard for pc-linux on i686 (Official MySQL-standard binary) glibc 2.2.5 System-2 Debian Woody 3.0 (running on a dual-Xeon 2.40GHz, kernel compiled with openwall-patch) uname -a Linux jupp 2.4.22-ow1 #1 SMP Sun Oct 5 15:36:18 CEST 2003 i686 unknown mysqld -v mysqld Ver 4.0.20-standard for pc-linux on i686 (Official MySQL-standard binary) glibc 2.2.5 Both machines are having the same databases (in structure but not in data). MY TESTCASE: ================================================================== The table-structure is very large (lots of rows, cause the table has grown over time; every few days a new column was aded with an ALTER-statement). Table-structure from mysqldump: -- MySQL dump 9.11 -- -- Host: localhost Database: jobmatik -- ------------------------------------------------------ -- Server version 4.0.20-standard-log -- -- Table structure for table `db_anzeigen` -- CREATE TABLE db_anzeigen ( anz_id int(11) NOT NULL auto_increment, anz_referrer int(11) NOT NULL default '0', anz_status int(11) NOT NULL default '0', anz_art int(11) NOT NULL default '0', anz_chiffre tinyint(1) NOT NULL default '0', anz_chiffre_nummer varchar(20) NOT NULL default '', anz_bundesweit tinyint(1) NOT NULL default '0', anz_stamp1 timestamp(14) NOT NULL, anz_stamp2 timestamp(14) NOT NULL default '00000000000000', anz_stamp3 timestamp(14) NOT NULL default '00000000000000', anz_stamp4 timestamp(14) NOT NULL default '00000000000000', anz_stamp5 timestamp(14) NOT NULL default '00000000000000', anz_jobtitel varchar(255) NOT NULL default '', anz_dauer tinyint(4) NOT NULL default '0', anz_rechnungsnummer varchar(255) NOT NULL default '', anz_firma varchar(160) NOT NULL default '', anz_strasse varchar(160) NOT NULL default '', anz_plz varchar(20) NOT NULL default '', anz_ort varchar(80) NOT NULL default '', anz_staat int(11) NOT NULL default '0', anz_staat_weitere varchar(160) NOT NULL default '', anz_telefon varchar(80) NOT NULL default '', anz_telefax varchar(80) NOT NULL default '', anz_mobil varchar(80) NOT NULL default '', anz_anrede_01 tinyint(1) NOT NULL default '1', anz_vorname_01 varchar(80) NOT NULL default '', anz_nachname_01 varchar(80) NOT NULL default '', anz_email_01 varchar(160) NOT NULL default '', anz_anrede_02 tinyint(1) NOT NULL default '1', anz_vorname_02 varchar(80) NOT NULL default '', anz_nachname_02 varchar(80) NOT NULL default '', anz_email_02 varchar(160) NOT NULL default '', anz_www varchar(160) NOT NULL default '', anz_firmenprofil text, anz_anrede_vorname_nachname_anzeigen tinyint(1) NOT NULL default '0', anz_strasse_anzeigen tinyint(1) NOT NULL default '0', anz_plz_anzeigen tinyint(1) NOT NULL default '0', anz_ort_anzeigen tinyint(1) NOT NULL default '0', anz_staat_anzeigen tinyint(1) NOT NULL default '0', anz_telefon_anzeigen tinyint(1) NOT NULL default '0', anz_telefax_anzeigen tinyint(1) NOT NULL default '0', anz_mobil_anzeigen tinyint(1) NOT NULL default '0', anz_email_anzeigen tinyint(1) NOT NULL default '0', anz_www_anzeigen tinyint(1) NOT NULL default '0', anz_logo varchar(100) NOT NULL default '', anz_job_beginn varchar(50) NOT NULL default '', anz_job_ende varchar(50) NOT NULL default '', anz_honorar_job varchar(50) NOT NULL default '', anz_honorar_job_pro tinyint(1) NOT NULL default '0', anz_honorar_job_art tinyint(1) NOT NULL default '0', anz_job_beschreibung text, anz_job_anforderung text, anz_job_einsatzort text, anz_job_plzbereich varchar(255) NOT NULL default '', anz_job_bundesland varchar(255) NOT NULL default '', anz_job_region varchar(255) NOT NULL default '', anz_job_plzbereich_int smallint(6) NOT NULL default '0', anz_job_bundesland_int smallint(6) NOT NULL default '0', anz_job_region_int smallint(6) NOT NULL default '0', anz_bank varchar(255) NOT NULL default '', anz_kontoinhaber varchar(255) NOT NULL default '', anz_kontonummer varchar(255) NOT NULL default '', anz_blz varchar(255) NOT NULL default '', anz_kreditkarte varchar(255) NOT NULL default '', anz_kreditnummer varchar(255) NOT NULL default '', anz_kreditinhaber varchar(255) NOT NULL default '', anz_kreditkontroll varchar(255) NOT NULL default '', anz_bezahlung_inserat tinyint(1) NOT NULL default '0', anz_rechnung_inserat tinyint(1) NOT NULL default '0', anz_preis_original int(11) NOT NULL default '0', anz_preis_rabattiert int(11) NOT NULL default '0', anz_hits int(11) NOT NULL default '0', anz_hits_bew int(11) NOT NULL default '0', anz_rid int(11) NOT NULL default '0', anz_rabatt varchar(255) NOT NULL default '', anz_hasFilter tinyint(1) NOT NULL default '0', anz_zufall_01 varchar(100) NOT NULL default '', anz_zufall_02 varchar(100) NOT NULL default '', anz_zufall_03 varchar(100) NOT NULL default '', anz_zufall_04 varchar(100) NOT NULL default '', anz_zufall_05 varchar(100) NOT NULL default '', anz_position smallint(6) NOT NULL default '9999', anz_remind tinyint(1) NOT NULL default '0', anz_rem_ip varchar(100) NOT NULL default '', anz_jls tinyint(1) NOT NULL default '0', anz_opt1 text NOT NULL, anz_opt2 text NOT NULL, anz_opt3 text NOT NULL, anz_opt4 text NOT NULL, anz_opt5 text NOT NULL, anz_f1 tinyint(1) NOT NULL default '0', anz_f2 tinyint(1) NOT NULL default '0', anz_f3 tinyint(1) NOT NULL default '0', anz_f4 tinyint(1) NOT NULL default '0', anz_f5 tinyint(1) NOT NULL default '0', anz_mitteilung text NOT NULL, anz_inserent int(11) NOT NULL default '0', anz_ausland tinyint(1) NOT NULL default '0', anz_kurzbeschreibung varchar(255) NOT NULL default '', anz_arbeitszeit varchar(255) NOT NULL default '', anz_honorar_zahlungsweise smallint(6) NOT NULL default '0', anz_stellenanzahl varchar(255) NOT NULL default '', anz_honorar_waehrung tinyint(3) NOT NULL default '0', anz_job_einsatzort_art tinyint(3) NOT NULL default '0', anz_firma_anzeigen tinyint(1) NOT NULL default '0', anz_behindertengerecht tinyint(1) NOT NULL default '0', anz_art_original int(11) NOT NULL default '0', anz_dauer_original tinyint(4) NOT NULL default '0', anz_id_original int(11) NOT NULL default '0', anz_rem_ip_schaltung varchar(100) NOT NULL default '', anz_vorz_expire date NOT NULL default '0000-00-00', anz_firma_02 varchar(200) NOT NULL default '', anz_telefon_02 varchar(80) NOT NULL default '', anz_www_02 varchar(160) NOT NULL default '', anz_branche smallint(6) NOT NULL default '0', anz_unternehmensgroesse tinyint(4) NOT NULL default '0', anz_bl smallint(6) NOT NULL default '0', anz_telefon_02_anzeigen tinyint(1) NOT NULL default '0', anz_www_02_anzeigen tinyint(1) NOT NULL default '0', anz_branche_anzeigen tinyint(1) NOT NULL default '0', anz_groesse_anzeigen tinyint(1) NOT NULL default '0', anz_firma_02_anzeigen tinyint(1) NOT NULL default '0', anz_bl_anzeigen tinyint(1) NOT NULL default '0', anz_anrede_vorname_nachname_02_anzeigen tinyint(1) NOT NULL default '0', anz_firma_staat smallint(6) NOT NULL default '0', anz_firma_staat_weitere varchar(160) NOT NULL default '', anz_markiert tinyint(1) NOT NULL default '0', anz_partner_id int(11) NOT NULL default '0', anz_telefon_lokal varchar(80) NOT NULL default '', anz_telefax_lokal varchar(80) NOT NULL default '', anz_mobil_lokal varchar(80) NOT NULL default '', anz_telefon_02_lokal varchar(80) NOT NULL default '', anz_filter_zuschlag int(11) NOT NULL default '0', anz_extern tinyint(1) NOT NULL default '0', anz_sfp_used tinyint(1) NOT NULL default '0', anz_sfp_confirmed tinyint(1) NOT NULL default '0', anz_sfp_completed tinyint(1) NOT NULL default '0', anz_sfp_tid varchar(255) NOT NULL default '', anz_sfp_data text NOT NULL, anz_sfp_sig text NOT NULL, anz_preis_jetzt int(11) NOT NULL default '0', anz_signatur varchar(60) NOT NULL default '', anz_rabatt_prozent tinyint(3) NOT NULL default '0', anz_partner_provision int(11) NOT NULL default '0', anz_bewerbungsmail tinyint(1) NOT NULL default '1', anz_jobmatik_shared tinyint(1) NOT NULL default '1', anz_partner_shared tinyint(1) NOT NULL default '1', anz_partner_schaltung int(11) NOT NULL default '0', anz_partner_schaltung_original int(11) NOT NULL default '0', PRIMARY KEY (anz_id), KEY plz_k (anz_job_plzbereich), KEY bnd_k (anz_job_bundesland), KEY reg_k (anz_job_region), FULLTEXT KEY full01 (anz_jobtitel), FULLTEXT KEY full02 (anz_jobtitel,anz_job_beschreibung,anz_job_einsatzort) ) TYPE=MyISAM; I'm not sure, if this is important, but the fulltext-Index was added after table-creation. When adding the fulltext-index the table got corrupted for the first time. And every ALTER-statement, which was added after the fulltext-index-creation caused table-corruption... So, actually I used "CREATE FULLTEXT INDEX full01 ON db_anzeigen (anz_jobtitel);" and "CREATE FULLTEXT INDEX full02 ON db_anzeigen (anz_jobtitel,anz_job_beschreibung,anz_job_einsatzort);" instead of the "FULLTEXT KEY full01 (anz_jobtitel)" and "FULLTEXT KEY full02 (anz_jobtitel,anz_job_beschreibung,anz_job_einsatzort)" Now, when altering the table, I get the follwing errors: mysql> ALTER TABLE db_anzeigen ADD COLUMN anz_test tinyint(1) DEFAULT '0' NOT NULL ; Query OK, 1203 rows affected (0.25 sec) Records: 1203 Duplicates: 0 Warnings: 0 mysql> select count(*) from db_anzeigen; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql stop myisamchk -o -r db_anzeigen.MYI - recovering (with keycache) MyISAM-table 'db_anzeigen.MYI' Data records: 0 Data records: 1203 mysql start mysql> select count(*) from db_anzeigen; +----------+ | count(*) | +----------+ | 1203 | +----------+ 1 row in set (0.00 sec) mysql> ALTER TABLE db_anzeigen DROP COLUMN anz_test; Query OK, 1203 rows affected (0.25 sec) Records: 1203 Duplicates: 0 Warnings: 0 mysql> select count(*) from db_anzeigen; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql stop myisamchk -o -r db_anzeigen.MYI - recovering (with keycache) MyISAM-table 'db_anzeigen.MYI' Data records: 0 Data records: 1203 mysql start mysql> select count(*) from db_anzeigen; +----------+ | count(*) | +----------+ | 1203 | +----------+ 1 row in set (0.00 sec) I wasn't able to reproduce this behaviour with other tables I created for testing. But the tables I have tested were much smaller than the mentioned table above. Best regards, Marcus
[23 Jun 2005 12:42]
MySQL Verification Team
I can't reproduce it with my test data:
mysql> select count(*) from db_anzeigen;
+----------+
| count(*) |
+----------+
| 1500 |
+----------+
1 row in set (0.01 sec)
mysql> ALTER TABLE db_anzeigen ADD COLUMN anz_test tinyint(1) DEFAULT '0' NOT
-> NULL ;
Query OK, 1500 rows affected (0.56 sec)
Records: 1500 Duplicates: 0 Warnings: 0
mysql> select count(*) from db_anzeigen;
+----------+
| count(*) |
+----------+
| 1500 |
+----------+
1 row in set (0.00 sec)
Is it possible to upload the whole table to our ftp?
ftp://ftp.mysql.com/pub/mysql/upload/
[23 Jun 2005 13:56]
Marcus Beranek
I have uploaded the file "db_anzeigen.tgz", which contains "db_anzeigen.frm", "db_anzeigen.MYD" and "db_anzeigen.MYI".
[29 Jun 2005 16:47]
MySQL Verification Team
I downloaded your table and I was unable to repeat it with latest
BK source server:
miguel@hegel:~/dbs/4.0$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.25-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> ALTER TABLE db_anzeigen ADD COLUMN anz_test tinyint(1) DEFAULT '0' NOT
-> NULL;
Query OK, 1208 rows affected (1.33 sec)
Records: 1208 Duplicates: 0 Warnings: 0
mysql> select count(*) from db_anzeigen;
+----------+
| count(*) |
+----------+
| 1208 |
+----------+
1 row in set (0.03 sec)
