Strange behaviour of mysqld after dropping a column
Description: A time ago I reported next bug: "The ALTER TABLE command behaves very strange when stopping and restarting the MySQL server afterwards. I noticed this after having rebooted my PC (which means that the MySql server is stopped and restarted). Before the reboot I dropped a column in one of my tables. After I had done this all the data was available in my table. However after have been rebooting my PC all the data was gone !!! It even becomes crazier. After the second reboot the data was visisble again. The tests I have executed showed that this problem shows up for both adding and deleting a column of a table. Also I noticed that when you add data after have beenstopping the MySQL server the first time this data is dissapeared after a second stop of the MySQL server." This problem should be solved in mysql-3.23.52. I have checked this and came to the conclusion the problem is indeed solved when adding a column, but after dropping a column the problem still exists !!! > > How-To-Repeat: > mysql > create database findBug; > mysql > use findBug > mysql > create table metatable(objid BIGINT not null, tablename > varchar(64), field varchar(64), type varchar(50), PRIMARY KEY (objid)) > type=BDB; > mysql > create table metaindex(objid BIGINT not null, indexname > varchar(64), ref_metatable DOUBLE not null, PRIMARY KEY (objid)) type=BDB; > mysql > create table metaoid(objid BIGINT not null, tablename > varchar(64), oid BIGINT not null, PRIMARY KEY (objid), UNIQUE(tablename)) > type=BDB; > mysql > insert into metaoid values(1, "metaoid",4); > mysql > insert into metaoid values(2, "metatable",1); > mysql > insert into metaoid values(3, "metaindex",1); > mysql > select * from metaoid; > +---+---+-+ > | objid | tablename | oid | > +---+---+-+ > | 1 | metaoid| 4 | > | 2 | metatable | 1 | > | 3 | metaindex | 1 | > +---+---+-+ > > mysql > alter table metaoid add column test varchar(255); > mysql > select * from metaoid; > +---+---+-+--+ > | objid | tablename | oid | test | > +---+---+-+--+ > | 1 | metaoid | 4 | NULL | > | 2 | metatable | 1 | NULL | > | 3 | metaindex | 1 | NULL | > +---+---+-+--+ > > mysql > quit; > C:\>net stop mysql > C:\>net start mysql > > mysql > select * from metaoid; > +---+---+-+--+ > | objid | tablename | oid | test | > +---+---+-+--+ > | 1 | metaoid | 4 | NULL | > | 2 | metatable | 1 | NULL | > | 3 | metaindex | 1 | NULL | > +---+---+-+--+ > > So this seems to be ok now, the problem starts when executing the next steps. > > mysql > alter table metaoid drop column test; > mysql > quit; > C:\>net stop mysql > C:\>net start mysql > mysql > select * from metaoid; > Empty set (0.01 sec) > > mysql > insert into metaoid values (5,"test",1); > mysql > select * from metaoid; > +---+---+-+ > | objid | tablename | oid | > +---+---+-+ > | 5 | test | 1 | > +---+---+-+ > > mysql > quit; > C:\>net stop mysql > C:\>net start mysql > > mysql > select * from metaoid; > +---+---+-+ > | objid | tablename | oid | > +---+---+-+ > | 5 | test| 1 | > +---+---+-+ When I restarted mysql in mysql-3.23.51 my old data was visible again and the new data was lost. Now it seems to be the other way round ! > > Fix: > No fix found for this problem. > > Synopsis: Strange behaviour of MySQL after dropping a column. > Submitter-Id: > Originator: Ismaël Cams > Organization: Siemens Atea > MySQL support: license > Severity: critical > Priority: high > Category: mysqld > Class: sw-bug > Release: mysql-3.23.52. > > Executable: mysqld > Environment: 512 MB RAM, PII > System: Win2000 and NT > Compiler: VC++ 6.0 > Architecture: i > > Kind regards, > Ismaël > > > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check:
RE: Strange behaviour of mysqld after adding/dropping a column
Hello Sinisa, the problem I had was that after I restarted mysqld (after have dropped/added a column) my data was dissapeared. So if you meant this problem I am indeed the right person. How do I apply this patch ? Kind regards, Ismaël -Original Message- From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 09, 2002 3:02 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Strange behaviour of mysqld after adding/dropping a column Cams Ismael writes: > Hello Sinisa, > > have you already found the origin of the problem ? How does the bugs get > fixed -> are they solved in patch releases or are they solved in a next > release of the MySQL server ? > > Kind regards, > Ismaël > Sorry to have lost your e-mail. If that is about BDB , thanks for the test case !! A fix will come in the next 3.23 and 4.0 releases. This is a patch: = sql_table.cc 1.101 vs edited = *** /tmp/sql_table.cc-1.101-460 Mon Feb 11 12:56:48 2002 --- edited/sql_table.cc Mon Jul 8 22:05:06 2002 *** *** 1652,1658 VOID(pthread_mutex_unlock(&LOCK_open)); goto err; } ! thd->proc_info="end"; mysql_update_log.write(thd, thd->query,thd->query_length); if (mysql_bin_log.is_open()) --- 1652,1662 VOID(pthread_mutex_unlock(&LOCK_open)); goto err; } ! #ifdef HAVE_BERKELEY_DB ! extern bool berkeley_flush_logs(void); ! if (old_db_type == DB_TYPE_BERKELEY_DB && berkeley_flush_logs()) ! goto err; ! #endif thd->proc_info="end"; mysql_update_log.write(thd, thd->query,thd->query_length); if (mysql_bin_log.is_open()) -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Strange behaviour of mysqld after adding/dropping a column
Hello Sinisa, have you already found the origin of the problem ? How does the bugs get fixed -> are they solved in patch releases or are they solved in a next release of the MySQL server ? Kind regards, Ismaël -Original Message- From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 02, 2002 1:43 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Strange behaviour of mysqld after adding/dropping a column Cams Ismael writes: > Hello Sinisa, > > this has nothing to do with services. I have also tried this without > creating a service and the result is the same. > Normally the services are also stopped automatically when logging of, so I > don't understand why I should stop it manually. Also I have tested this > constantly by stopping and restarting the service without rebooting the PC > (as I described in my bug report), with the same result. So this has nothing > to do with services or with rebooting your PC (this was only how I found the > bug). In my opinion this is a heavily bug, keeping in mind that restarting > mysql after dropping/adding a column messes up your database ! Can you > please check this ? > > Kind regards, > Ismaël Yes, it looks like a bug. We shall inspect that. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Strange behaviour of mysqld after adding/dropping a column
Hello Sinisa, this has nothing to do with services. I have also tried this without creating a service and the result is the same. Normally the services are also stopped automatically when logging of, so I don't understand why I should stop it manually. Also I have tested this constantly by stopping and restarting the service without rebooting the PC (as I described in my bug report), with the same result. So this has nothing to do with services or with rebooting your PC (this was only how I found the bug). In my opinion this is a heavily bug, keeping in mind that restarting mysql after dropping/adding a column messes up your database ! Can you please check this ? Kind regards, Ismaël -Original Message- From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]] Sent: Monday, July 01, 2002 4:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Strange behaviour of mysqld after adding/dropping a column Cams Ismael writes: > * I have indeed installed MySQL as a service. That's why I use 'net stop > mysql' and 'net start mysql' to start and stop the MySQL server. > > * I have done nothing special before rebooting my PC. The problem doesn't > seem to lay in rebooting the PC, but in stopping andstarting the MySQL > service. Rebooting the PC causes this service to stop and to start. > > Kind regards, > Ismaël Sorry, but this is not reliable enough on any Windows, including W2K. You have to stop a service before re-booting. Only then you will have no problems with MySQL. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange behaviour of mysqld after adding/dropping a column
> Description: > The ALTER TABLE command behaves very strange when stopping and > restarting the MySQL server afterwards. I noticed this after having > rebooted my PC (which means that the MySql server is stopped and > restarted). Before the reboot I dropped a column in one of my tables. > After I had done this all the data was available in mytable. > However after have been rebooting my PC all the data was gone !!! It even > becomes crazier. After the second reboot the data was visisble again. > The tests I have executed showed that this problem shows up for both > adding and deleting a column of a table. Also I noticed that when you add > data after have been stopping the MySQL server the first time this data > is dissapeared after a second stop of the MySQL server. > > How-To-Repeat: > mysql > create database findBug; > mysql > use findBug > mysql > create table metatable(objid BIGINT not null, tablename > varchar(64), field varchar(64), type varchar(50), PRIMARY KEY (objid)) > type=BDB; > mysql > create table metaindex(objid BIGINT not null, indexname > varchar(64), ref_metatable DOUBLE not null, PRIMARY KEY (objid)) type=BDB; > mysql > create table metaoid(objid BIGINT not null, tablename > varchar(64), oid BIGINT not null, PRIMARY KEY (objid), UNIQUE(tablename)) > type=BDB; > mysql > insert into metaoid values(1, "metaoid",4); > mysql > insert into metaoid values(2, "metatable",1); > mysql > insert into metaoid values(3, "metaindex",1); > mysql > select * from metaoid; > +---+---+-+ > | objid | tablename | oid | > +---+---+-+ > | 1 | metaoid| 4 | > | 2 | metatable | 1 | > | 3 | metaindex | 1 | > +---+---+-+ > > mysql > alter table metaoid add column test varchar(255); > mysql > select * from metaoid; > +---+---+-+--+ > | objid | tablename | oid | test | > +---+---+-+--+ > | 1 | metaoid | 4 | NULL | > | 2 | metatable | 1 | NULL | > | 3 | metaindex | 1 | NULL | > +---+---+-+--+ > > mysql > quit; > C:\>net stop mysql > C:\>net start mysql > > mysql > select * from metaoid; > Empty set (0.01 sec) > > mysql > quit; > C:\>net stop mysql > C:\>net start mysql > > mysql > select * from metaoid; > +---+---+-+--+ > | objid | tablename | oid | test | > +---+---+-+--+ > | 1 | metaoid | 4 | NULL | > | 2 | metatable | 1 | NULL | > | 3 | metaindex | 1 | NULL | > +---+---+-+--+ > > mysql > alter table metaoid drop column test; > mysql > quit; > C:\>net stop mysql > C:\>net start mysql > mysql > select * from metaoid; > Empty set (0.01 sec) > > mysql > insert into metaoid values (5,"test",1); > mysql > select * from metaoid; > +---+---+-+ > | objid | tablename | oid | > +---+---+-+ > | 5 | test | 1 | > +---+---+-+ > > mysql > quit; > C:\>net stop mysql > C:\>net start mysql > > mysql > select * from metaoid; > +---+---+-+ > | objid | tablename | oid | > +---+---+-+ > | 1 | metaoid | 4 | > | 2 | metatable | 1 | > | 3 | metaindex | 1 | > +---+---+-+ > > Fix: > Restarting the server twice after have dropped/added a column is the > only "solution" I have found for this problem. > > Synopsis: Strange behaviour of MySQL after dropping/adding column. > Submitter-Id: > Originator: Ismaël Cams > Organization: Siemens Atea > MySQL support: license > Severity: critical > Priority: high > Category: mysqld > Class: sw-bug > Release: mysql-3.23.51. > > Executable: mysqld > Environment: 512 MB RAM, PII > System: Win2000 and NT > Compiler: VC++ 6.0 > Architecture: i > > Kind regards, > Ismaël > > > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Got error 127 after using the alter table command
Hello, I am using MySQL 3.23.39 (on Windows NT and Windows 2000) and the = mm_mysql 2.0.4 JDBC driver. I encountered following problem: 1. I first create some tables via JDBC 2. then I fill in some data via the command line 3. I execute an alter table command (adding a column) on the table with Java 4. When I now try to execute a statement on this table I get error 127 I do not have this problem when: 1. I don't fill in data via the command line 2. I fill in the data via JDBC I have also tried this with a dump, and this also gave the same error.=20 Could this be a bug ? Kind regards, Ismaël - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
