Strange behaviour of mysqld after dropping a column

Description:
A time ago I reported next bug:
=09
"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.=20
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."

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=20
dropping a column the problem still exists !!!
>=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;
> +-------+-----------+-----+------+
> | objid | tablename | oid | test |
> +-------+-----------+-----+------+
> | 1 | metaoid | 4 | NULL |
> | 2 | metatable | 1 | NULL |
> | 3 | metaindex | 1 | NULL |
> +-------+-----------+-----+------+
>=20
> So this seems to be ok now, the problem starts when executing the
next steps.
>=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 |
> +-------+-----------+-----+
> | 5 | test | 1 |
> +-------+-----------+-----+
=09
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 !!!!!
>=20
> Fix:
> No fix found for this problem.
>=20
> Synopsis: Strange behaviour of MySQL after dropping a 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.52.
>=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
To unsubscribe, e-mail

Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

Strange behaviour of mysqld after dropping a column

Hi!

>>>>> "Cams" == Cams Ismael writes:

Cams> Description:
Cams> A time ago I reported next bug:



Cams> This problem should be solved in mysql-3.23.52. I have checked this
Cams> and came
Cams> to the conclusion the problem is indeed solved when adding a column,
Cams> but after
Cams> dropping a column the problem still exists !!!

Could you try to do a full example of this so that we can repeat the
problem and fix it? (If you can, please email the test case to
bugs@lists.mysql.com)

I don't see how this behavour would be possible.

When doing an ALTER TABLE, MYSQL works this way:

- Create a new copy of the table under a temporary name.
- Close all instanses of the old table
- Delete the original table
- Rename the new table as the old one
- Open the new table

You can easily see this happening if you look in the MySQL data
directory before and after you have done an ALTER TABLE.

We would be thankful for any more information you can give us
regarding this problem.

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ 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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Strange behaviour of mysqld after dropping a column

Cams Ismael writes:
> Description:
> =09A time ago I reported next bug:
> =09
> =09"The ALTER TABLE command behaves very strange when stopping and
> =09restarting the MySQL server afterwards. I noticed this after havi=
ng
> =09rebooted my PC (which =09means that the MySql server is stopped a=
nd
> =09restarted). Before the reboot I dropped a column in one of my
> tables.
> =09After I had done this all the data was available in my =09table.
> =09However after have been rebooting my PC all the data was gone !!!=
It
> even
> =09becomes crazier. After the second reboot the data was visisble
> again.=20
> =09The tests I have executed showed that this problem shows up for b=
oth
> =09adding and deleting a column of a table. Also I noticed that when=

> you add
> =09data after have been =09stopping the MySQL server the first time
> this data
> =09is dissapeared after a second stop of the MySQL server."
>=20
> =09This problem should be solved in mysql-3.23.52. I have checked thi=
s
> and came
> =09to the conclusion the problem is indeed solved when adding a colum=
n,
> but after=20
> =09dropping a column the problem still exists !!!
> >=20
> > Kind regards,
> > Isma=EBl


Thank you for your test case. It helped us fix a bug in ALTER TABLE
with BDB handler.

Fix will come up in 3.23.53 and 4.0.4. This is a patch that fixes it:

=====3D sql/sql_table.cc 1.103 vs edited =====3D
*** /tmp/sql_table.cc-1.103-25819=09Mon Aug 5 18:50:34 2002
--- edited/sql/sql_table.cc=09Wed Aug 28 15:10:52 2002
***************
*** 1665,1675 ****
VOID(pthread_cond_broadcast(&COND_refresh));
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())
--- 1665,1670 ----
***************
*** 1679,1684 ****
--- 1674,1687 ----
}
VOID(pthread_cond_broadcast(&COND_refresh));
VOID(pthread_mutex_unlock(&LOCK_open));
+ #ifdef HAVE_BERKELEY_DB
+ extern bool berkeley_flush_logs(void);
+ if (old_db_type == DB_TYPE_BERKELEY_DB)
+ {
+ (void)berkeley_flush_logs();
+ table=3Dopen_ltable(thd,table_list,TL_READ);
+ }
+ #endif
=20
end_temporary:
sprintf(tmp_name,ER(ER_INSERT_INFO),(ulong) (copied+deleted),


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

Re: Strange behaviour of mysqld after dropping a column

Cams Ismael writes:
> Description:
> =09A time ago I reported next bug:
> =09
> =09"The ALTER TABLE command behaves very strange when stopping and
> =09restarting the MySQL server afterwards. I noticed this after havi=
ng
> =09rebooted my PC (which =09means that the MySql server is stopped a=
nd
> =09restarted). Before the reboot I dropped a column in one of my
> tables.
> =09After I had done this all the data was available in my =09table.
> =09However after have been rebooting my PC all the data was gone !!!=
It
> even
> =09becomes crazier. After the second reboot the data was visisble
> again.=20
> =09The tests I have executed showed that this problem shows up for b=
oth
> =09adding and deleting a column of a table. Also I noticed that when=

> you add
> =09data after have been =09stopping the MySQL server the first time
> this data
> =09is dissapeared after a second stop of the MySQL server."
>=20
> =09This problem should be solved in mysql-3.23.52. I have checked thi=
s
> and came
> =09to the conclusion the problem is indeed solved when adding a colum=
n,
> but after=20
> =09dropping a column the problem still exists !!!
> >=20
> > Kind regards,
> > Isma=EBl


Thank you for your test case. It helped us fix a bug in ALTER TABLE
with BDB handler.

Fix will come up in 3.23.53 and 4.0.4. This is a patch that fixes it:

=====3D sql/sql_table.cc 1.103 vs edited =====3D
*** /tmp/sql_table.cc-1.103-25819=09Mon Aug 5 18:50:34 2002
--- edited/sql/sql_table.cc=09Wed Aug 28 15:10:52 2002
***************
*** 1665,1675 ****
VOID(pthread_cond_broadcast(&COND_refresh));
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())
--- 1665,1670 ----
***************
*** 1679,1684 ****
--- 1674,1687 ----
}
VOID(pthread_cond_broadcast(&COND_refresh));
VOID(pthread_mutex_unlock(&LOCK_open));
+ #ifdef HAVE_BERKELEY_DB
+ extern bool berkeley_flush_logs(void);
+ if (old_db_type == DB_TYPE_BERKELEY_DB)
+ {
+ (void)berkeley_flush_logs();
+ table=3Dopen_ltable(thd,table_list,TL_READ);
+ }
+ #endif
=20
end_temporary:
sprintf(tmp_name,ER(ER_INSERT_INFO),(ulong) (copied+deleted),


--=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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php