Re:.37 INNO Strange behaviour after deleting a table
Andre, the strange behavior may have been due to the same bug Alex Baumeister reported, and for which I posted a fix on the mailing list a few minutes ago. After you had reloaded the table, did you insert, update, or delete any rows in the table? Or did you just do selects? Did you have any other transactions open when you did the selects, i.e., clients in the non-autocommit mode? Regards, Heikki >Hi, > >I accidently delete all my records from a table, but I always make backups!!! >Okay, I deleted the (empty) table with drop table. >Than I recreate the table and all the data mith a mysql batch job (from >mysqldump) .No problem. Than I started the mysql UI and make some test queries. >select * from passwd;All records are displayed >if I query only for one column I get an empty set back >select pw_name from passwd where pw_name='pbs013'; >Empty set (0.00 sec) >If I query for the whole record all works fine: >mysql> select * from passwd where pw_name='pbs013'; >+-++-+---+++--- + >--+--+ >| name_nr | nr | pw_name | pw_passwd | pw_uid | pw_gid | pw_gecos | >pw_dir | shell_nr | >+-++-+---+++--- + >--+--+ >| 50 | 50 | pbs013 | deleted | 10013 | 1 | andre konopka | >/home/pbs013 |1 | >+-++-+---+++--- + >--+--+1 row in set (0.00 sec) >After restarting mysqld all works as expected??? > mysql> select pw_name from passwd where pw_name='pbs013';+-+| pw_name | >+-+| pbs013 |+-+1 row in set (0.01 sec)Andre >--E-Mail: [EMAIL PROTECTED] >Date: 08-May-01Time: 16:28:44 - 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:.37 INNO Strange behaviour after deleting a table
Heikki, here are more details ... I' am running mysql-3.23.37 on a hp-ux11 box (with actual patches) I used the following configure options to compile MySQL: CXX=gcc CFLAGS="-fomit-frame-pointer -O6 -fpic" CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti -O6" ./configure --with-low-memory --with-berkeley-db --with-innodb --prefix=/opt/mysql --disable-shared My backup comes from mysqldump (MySQL dump 8.8), here is the create statement from the ascii file CREATE TABLE passwd ( name_nr int(11) DEFAULT '0' NOT NULL, nr int(11) NOT NULL auto_increment, pw_name varchar(64) DEFAULT '' NOT NULL, pw_passwd varchar(32) DEFAULT '', pw_uid int(11) DEFAULT '0' NOT NULL, pw_gid int(11) DEFAULT '0' NOT NULL, pw_gecos varchar(127) DEFAULT '' NOT NULL, pw_dir varchar(127) DEFAULT '' NOT NULL, shell_nr int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (nr), KEY name_nr (name_nr), UNIQUE pw_name (pw_name), UNIQUE pw_uid (pw_uid) ) type=innobase; # # Dumping data for table 'passwd' # . . . With cat db_backup | /opt/mysql/bin/mysql -r -B -N -u my_db_adm -pvery_secret_password mail I re-create the table structure and recover my data It's absolut no problem to reproduce the behaviour, it's a DB with roundabout 1000 rows. Hope that helps Greetings fro Germany Andre On 08-May-01 Heikki Tuuri wrote: > Andre, > > it might be a bug in the multiversioning code in InnoDB. > Can you describe in detail the situation: > > - What is the CREATE TABLE statement? > - How many rows there are in the table? > - How did you import the table? > - Are you able to repeat the bug if you delete and drop the table, > and create and import it again? > > Regards, > > Heikki > > .. >>Hi, >> >>I accidently delete all my records from a table, but I always make backups!!! >>Okay, I deleted the (empty) table with drop table. >>Than I recreate the table and all the data mith a mysql batch job (from >>mysqldump) .No problem. Than I started the mysql UI and make some test >>queries. >>select * from passwd;All records are displayed >>if I query only for one column I get an empty set back >>select pw_name from passwd where pw_name='pbs013';Empty set (0.00 sec) >>If I query for the whole record all works fine: >>mysql> select * from passwd where pw_name='pbs013'; >>+-++-+---+++--- > + >>--+--+ >>| name_nr | nr | pw_name | pw_passwd | pw_uid | pw_gid | pw_gecos | >>pw_dir | shell_nr | >>+-++-+---+++--- > + >>--+--+ >>| 50 | 50 | pbs013 | deleted | 10013 | 1 | andre konopka | >>/home/pbs013 |1 | >>+-++-+---+++--- > + >>--+--+1 row in set (0.00 sec) >>After restarting mysqld all works as expected??? >>mysql> select pw_name from passwd where pw_name='pbs013';+-+| > pw_name | >>+-+| pbs013 |+-+1 row in set (0.01 sec)Andre >>--E-Mail: [EMAIL PROTECTED] >>Date: 08-May-01Time: 16:28:44 > > > - > 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 -- E-Mail: [EMAIL PROTECTED] Date: 09-May-01 Time: 08:29:32 () Join the ASCII ribbon campaign against html email /\ and Microsoft attachments. -- - 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:.37 INNO Strange behaviour after deleting a table
Andre, it might be a bug in the multiversioning code in InnoDB. Can you describe in detail the situation: - What is the CREATE TABLE statement? - How many rows there are in the table? - How did you import the table? - Are you able to repeat the bug if you delete and drop the table, and create and import it again? Regards, Heikki .. >Hi, > >I accidently delete all my records from a table, but I always make backups!!! >Okay, I deleted the (empty) table with drop table. >Than I recreate the table and all the data mith a mysql batch job (from >mysqldump) .No problem. Than I started the mysql UI and make some test queries. >select * from passwd;All records are displayed >if I query only for one column I get an empty set back >select pw_name from passwd where pw_name='pbs013';Empty set (0.00 sec) >If I query for the whole record all works fine: >mysql> select * from passwd where pw_name='pbs013'; >+-++-+---+++--- + >--+--+ >| name_nr | nr | pw_name | pw_passwd | pw_uid | pw_gid | pw_gecos | >pw_dir | shell_nr | >+-++-+---+++--- + >--+--+ >| 50 | 50 | pbs013 | deleted | 10013 | 1 | andre konopka | >/home/pbs013 |1 | >+-++-+---+++--- + >--+--+1 row in set (0.00 sec) >After restarting mysqld all works as expected??? >mysql> select pw_name from passwd where pw_name='pbs013';+-+| pw_name | >+-+| pbs013 |+-+1 row in set (0.01 sec)Andre >--E-Mail: [EMAIL PROTECTED] >Date: 08-May-01Time: 16:28:44 - 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
