MySQL Bugs: #12119: "Didn't find any fields in table 't2'" error

Bug #12119	"Didn't find any fields in table 't2'" error
Submitted:	22 Jul 2005 16:21	Modified:	8 Aug 2005 20:54
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Any (any)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
1. run perl script that drops and creates table t2  like t1 within an infinite loop.
2. connect to the MySQL server (another session) and you get an error: "Didn't find any fields in table 't2'"

How to repeat:
1. create table t1:

create table t1(id int)type=innodb;

2. run attached perl script
3. connect to the MySQL server

perl script

Attachment: test.pl (application/octet-stream, text), 407 bytes.

Victoria,

does mysqld print anything to the .err log?

I guess this is not an InnoDB bug, but a MySQL server bug. InnoDB is not involved in getting the column information from the .frm file.

Regards,

Heikki

No, there is nothing in the error log.

Is this script meant to have intentionaly erroneous SQL syntax in the first $dbh->do() call: 
"$dbh->do("drop table if exists t2 like t1") or die "Couldn't drop table t2" . DBI->errstr;"

mysql> show tables;
Empty set (0.11 sec)

mysql> create table t1 (id int, name varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id int, name varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> drop table if exists t2 like t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'like t1' at line 1

It's my opinion that this is not a bug, and my reasoning for this is as follows:

The test script is broken - the first query fails because the syntax is incorrect, which the 'die' happens hence the following 'create table' call never occurs, resulting in the behaviour noted in the original bug report.

while (true)
{
  #$dbh->do("drop table if exists t2 like t1") or die "Couldn't drop table t2" . DBI->errstr;
  $dbh->do("drop table if exists t2") or die "Couldn't drop table t2" . DBI->errstr;
  sleep 1;
  $dbh->do("create table if not exists t2 (id int)type=InnoDB") or die "Couldn't create table t2" . DBI->errstr;
}

The sleep is necessary because drop/create happens too fast to ever see a the table created.

Then in a client:

| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.04 sec)

mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.04 sec)

mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.11 sec)

mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

Oops,

I'm sorry, I pasted wrong lines to test.pl script :(
These rows are correct one:

while (true)
{
  $Master->do("drop table if exists t2") or die "Couldn't drop table t2" . DBI->errstr;
  $Master->do("create table if not exists t2 like t1") or die "Couldn't create table t2" . DBI->errstr;
}

You can put sleep to check that table is created, though bug isn't reproducable for me with this sleep.

Ok, so I do get this error  now while while running the script (without a sleep, as well as trying innodb and myisam). The problem is that the server should never report anything about a table if there are no fields. It's either there or not, right? So, we could conclude that the server is being allowed to report the table while it's not fully created...?

radha:~/mysql-build/mysql-5.0.clean patg$ uname -a
Darwin radha.local 7.9.0 Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC  Power Macintosh powerpc

radha:~ patg$ /usr/local/mysql/bin/mysql -u root test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Didn't find any fields in table 't2'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 304 to server version: 4.1.14-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \q
Bye
radha:~ patg$ /usr/local/mysql/bin/mysql -u root test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 305 to server version: 4.1.14-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \q
Bye
radha:~ patg$ /usr/local/mysql/bin/mysql -u root test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Didn't find any fields in table 't2'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 306 to server version: 4.1.14-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \q
Bye
radha:~ patg$ /usr/local/mysql/bin/mysql -u root test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 307 to server version: 4.1.14-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \q

radha:~ patg$ /usr/local/mysql/bin/mysql -u root test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Didn't find any fields in table 't2'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 308 to server version: 4.1.14-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  select version();
+------------------+
| version()        |
+------------------+
| 4.1.14-debug-log |
+------------------+
1 row in set (0.00 sec)

Victoria, I tested this with 4.0 and 5.0, because looking at sql_parse.cc in 4.1, I noticed (and Mikael pointed this out) that there is no lock occuring during create, and that is suspect. I have tested this with both 4.0 and 5.0 also, and couldn't achieve the same failure. Could you perhaps test it out for me on your platform for 4.0 and 5.0? This way, it'll to show that it's a 4.1-specific problem.

Thanks!

Patrick,

I don't think it's a server bug.  Look in client/mysql.cc, at the build_completion_hash() function.  It does:

1) SHOW TABLES

2) FOR (tables from #1) { mysql_list_fields(MYSQL*, <table_name>, NULL); }

Imagine that the table is created at step #0, so it is  listed at step #1, but is dropped before step #2.  In that case, the mysql_list_fields() call will fail, and mysql.cc will print the message about not finding any fields in the table.

Note that the error message is printed by mysql.cc, not by the server.....

This *could* be considered a bug in mysql.cc.  But the fix would be to LOCK TABLES before running a SHOW TABLES, and that's not good.  It's not important that this error happens, and I think it's best to just leave the behavior as is.

If you want to reproduce it, you can run mysql in gdb, break after the SHOW TABLES is executed, drop a table, and then continue.

Timothy

Timothy,

That's a good thing to point out, and it would prevent the error message from occuring, though not fix the real problem, which is that the server should do this. The client shouldn't have to work around this issue by explicitely locking the table. If the server is to properly lock the table when creating, then we shouldn't see this message anyway. 

I also ran this issue by a couple of devs, and they felt the issue was in the server.

I'm curretly testing adding a lock, and will see what happens!

Thanks!

Patrick,

I tested this bug report with 5.0 and 4.0 and wasn't able reproduce it.
Seems it's only 4.1 behavior.

Victoria - thanks for testing this on other versions!

The problem turns out, as Tim was thinking, in the client. However, no types of locks are desired neither in the server nor in the client. What was needed was to remove the error message in mysql.cc and let it silently not print out anything about that dropped table. Thanks to Monty for help on this (who happened to be visiting her in Seattle)!

radha:~/mysql-build/mysql-4.1 patg$ client/mysql -S /tmp/mysqld-4.1-5552.sock
test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.14-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.02 sec)

mysql> \q
Bye
radha:~/mysql-build/mysql-4.1 patg$ client/mysql -S /tmp/mysqld-4.1-5552.sock
test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.14-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \q
Bye
radha:~/mysql-build/mysql-4.1 patg$ client/mysql -S /tmp/mysqld-4.1-5552.sock
test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.14-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.15 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.07 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.03 sec)

mysql>

i'm using mysql 4.1.12 version on linux redhat 4

====================================================
when connecting from remote clinent to Mysql server show error messages as like
 ====================================================
[root@admin manger]# mysql -u dbadmin -p -hldb -Dka_test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Didn't find any fields in table 'addr_group'
Didn't find any fields in table 'addr_group_user'
Didn't find any fields in table 'addr_main'

====================================================
but connection from local clien to server then clearly show 
===================================================
[root@ldb mysql]# mysql -udbadmin -p -Dka_test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2346 to server version: 4.1.12-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use ka_test;
Database changed

=================================

show tables from remote clent server 
mysql> show tables;
+--------------------------+
| Tables_in_kaatal_test    |
+--------------------------+
| addr_group               |
| addr_group_user          |
| addr_main                |
| arreo_sms                |
| bulletin_attach          |
| bulletin_category_item   |
| bulletin_category_master |
| bulletin_contents        |
| bulletin_master          |
| bulletin_pool            |
| c3p0testtable            |
| cart_master              |
| cart_product_detail      |

show tables from local database 
mysql> show tables;
+-----------------------+
| Tables_in_kaatal_test |
+-----------------------+
| store_groups          |
| store_users           |
+-----------------------+
2 rows in set (0.00 sec)

i'm delete all tables except   store_groups , store_users  
but Server information dosn't applied they are deleted. 
   (what about Sared memory err  ? )

so client  and server envirment dosen't synced.

what is problem ?

who can fixed it.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28035

patch approved and pushed