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 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 been stopping the MySQL server the first time this =
data
> is dissapeared after a second stop of the MySQL server.=20
>=20
> 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=3DBDB;
> mysql > create table metaindex(objid BIGINT not null, indexname
> varchar(64), ref_metatable DOUBLE not null, PRIMARY KEY (objid)) =
type=3DBDB;
> mysql > create table metaoid(objid BIGINT not null, tablename
> varchar(64), oid BIGINT not null, PRIMARY KEY (objid), =
UNIQUE(tablename))
> type=3DBDB;
> 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 |
> +-------+-----------+-----+
> =09
> 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 |
> +-------+-----------+-----+------+
> =09
> mysql > quit;
> C:\>net stop mysql
> C:\>net start mysql
>=20
> mysql > select * from metaoid;
> Empty set (0.01 sec)
>=20
> mysql > quit;
> C:\>net stop mysql
> C:\>net start mysql
>=20
> mysql > select * from metaoid;
> +-------+-----------+-----+------+
> | objid | tablename | oid | test |
> +-------+-----------+-----+------+
> | 1 | metaoid | 4 | NULL |
> | 2 | metatable | 1 | NULL |
> | 3 | metaindex | 1 | NULL |
> +-------+-----------+-----+------+
>=20
> 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)
>=20
> mysql > insert into metaoid values (5,"test",1);
> mysql > select * from metaoid;
> +-------+-----------+-----+
> | objid | tablename | oid |
> +-------+-----------+-----+
> | 5 | test | 1 |
> +-------+-----------+-----+
>=20
> mysql > quit;
> C:\>net stop mysql
> C:\>net start mysql
>=20
> mysql > select * from metaoid;
> +-------+-----------+-----+
> | objid | tablename | oid |
> +-------+-----------+-----+
> | 1 | metaoid | 4 |
> | 2 | metatable | 1 |
> | 3 | metaindex | 1 |
> +-------+-----------+-----+
>=20
> Fix:
> Restarting the server twice after have dropped/added a column is the
> only "solution" I have found for this problem.
>=20
> Synopsis: Strange behaviour of MySQL after dropping/adding column.
> Submitter-Id:
> Originator: Isma=EBl Cams
> Organization: Siemens Atea
> MySQL support: license
> Severity: critical
> Priority: high
> Category: mysqld
> Class: sw-bug
> Release: mysql-3.23.51.
>=20
> Executable: mysqld
> Environment: 512 MB RAM, PII=20
> System: Win2000 and NT
> Compiler: VC++ 6.0
> Architecture: i
>=20
> Kind regards,
> Isma=EBl
>=20
>=20
>=20
>=20
>=20
>=20
>=20

------------------------------------------------------------ ---------
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 bugs-thread12155@lists.mysql.com
To unsubscribe, e-mail

Re: Strange behaviour of mysqld after adding/dropping a column

Two questions :

* have you installed MySQL as a service ??

* have you stopeed a service prior to rebooting ??

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ 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 bugs-thread12157@lists.mysql.com
To unsubscribe, e-mail

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 s=
top
> mysql' and 'net start mysql' to start and stop the MySQL server.
>=20
> * I have done nothing special before rebooting my PC. The problem doe=
sn't
> seem to lay in rebooting the PC, but in stopping and starting the =
MySQL
> service. Rebooting the PC causes this service to stop and to start.
>=20
> Kind regards,
> Isma=EBl


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.

--=20
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic >
/ /|_/ / // /\ \/ /_/ / /__ 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 bugs-thread12158@lists.mysql.com
To unsubscribe, e-mail

RE: Strange behaviour of mysqld after adding/dropping a column

Cams Ismael writes:
> Hello Sinisa,
>=20
> this has nothing to do with services. I have also tried this without
> creating a service and the result is the same.=20
> 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 th=
is
> constantly by stopping and restarting the service without rebooting t=
he 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 fo=
und the
> bug). In my opinion this is a heavily bug, keeping in mind that resta=
rting
> mysql after dropping/adding a column messes up your database ! Can yo=
u
> please check this ?
>=20
> Kind regards,
> Isma=EBl

Yes, it looks like a bug.

We shall inspect that.

--=20
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic >
/ /|_/ / // /\ \/ /_/ / /__ 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 bugs-thread12161@lists.mysql.com
To unsubscribe, e-mail

RE: Strange behaviour of mysqld after adding/dropping a column

Cams Ismael writes:
> Hello Sinisa,
>=20
> 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 ne=
xt
> release of the MySQL server ?
>=20
> Kind regards,
> Isma=EBl=20
>=20

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:


=====3D sql_table.cc 1.101 vs edited =====3D
*** /tmp/sql_table.cc-1.101-460=09Mon Feb 11 12:56:48 2002
--- edited/sql_table.cc=09Mon Jul 8 22:05:06 2002
***************
*** 1652,1658 ****
VOID(pthread_mutex_unlock(&LOCK_open));
goto err;
}
!=20
thd->proc_info=3D"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=3D"end";
mysql_update_log.write(thd, thd->query,thd->query_length);
if (mysql_bin_log.is_open())



--=20
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic >
/ /|_/ / // /\ \/ /_/ / /__ 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 bugs-thread12168@lists.mysql.com
To unsubscribe, e-mail