Bug #13671 Infinite errors during creation of a federated table

• Submitted: 30 Sep 2005 21:46
• Modified: 30 Sep 2005 22:26
• Reporter: Stephane PAQUOT
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.13 max
• OS: Windows (Windows XP)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[30 Sep 2005 21:46] Stephane PAQUOT

Description:
It is fully reproductible : during execution of a sql file containing a federated table creation, the table is created and then MySQL tries to create again many times the federated table (see errors below) generating errors.

Here is the sql source file :

CREATE TABLE `t_asi_users` (
  `id` int(3) NOT NULL default '0',
  `login` char(3) NOT NULL default '',
  `nom` varchar(30) NOT NULL default '',
  `prenom` varchar(30) NOT NULL default '',
  `pwd` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_login` (`login`)
) 
ENGINE=FEDERATED
 DEFAULT CHARSET=latin1
CONNECTION='mysql://qry_cgcam_r:faspas@CGC:40102/cgcam/t_asi_users';

Here are the errors generated :

mysql> \. C:\Projets\mysql\federated.sql
Query OK, 0 rows affected (0.09 sec)

ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
ERROR 1050 (42S01): Table 't_asi_users' already exists
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 'ERROR 1429 (HY000): Unable to connect to foreign data source - database ' databa' at line 1
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 'ERROR 1429 (HY000): Unable to connect to foreign data source - database ' databa' at line 1
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 'ERROR 1429 (HY000): Unable to connect to foreign data source - database ' databa' at line 1
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 'ERROR 1429 (HY000): Unable to connect to foreign data source - database ' databa' at line 1
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 'ERRO

How to repeat:
Always reproductible.
The same versions are used for the two MySQL Servers DBA_T1_MYS and DBA_T2_MYS :

mysql> show variables like 'version';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| version       | 5.0.13-rc-nt-max-log |
+---------------+----------------------+

[30 Sep 2005 22:12] MySQL Verification Team

On Linux:
miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create cgcam
miguel@hegel:~/dbs/5.0> bin/mysql -uroot cgcam
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.14-rc-debug

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

mysql> CREATE TABLE `t_asi_users` (
    ->   `id` int(3) NOT NULL default '0',
    ->   `login` char(3) NOT NULL default '',
    ->   `nom` varchar(30) NOT NULL default '',
    ->   `prenom` varchar(30) NOT NULL default '',
    ->   `pwd` varchar(32) NOT NULL default '',
    ->   PRIMARY KEY  (`id`),
    ->   UNIQUE KEY `idx_login` (`login`)
    -> ) 
    -> ENGINE MyISAM  DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql> grant all on cgcam.* to 'qry_cgcam_r'@'192.168.0.33' identified by 'faspas';
Query OK, 0 rows affected (0.01 sec)

mysql> 

On Windows:

c:\mysql\bin>mysql -uroot cgcam
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.13-rc-nt-max

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

mysql> CREATE TABLE `t_asi_users` (
    ->   `id` int(3) NOT NULL default '0',
    ->   `login` char(3) NOT NULL default '',
    ->   `nom` varchar(30) NOT NULL default '',
    ->   `prenom` varchar(30) NOT NULL default '',
    ->   `pwd` varchar(32) NOT NULL default '',
    ->   PRIMARY KEY  (`id`),
    ->   UNIQUE KEY `idx_login` (`login`)
    -> )
    -> ENGINE=FEDERATED
    ->  DEFAULT CHARSET=latin1
    -> CONNECTION='mysql://qry_cgcam_r:faspas@hegel:3306/cgcam/t_asi_users';
Query OK, 0 rows affected (0.22 sec)

mysql> show create table t_asi_users\G
*************************** 1. row ***************************
       Table: t_asi_users
Create Table: CREATE TABLE `t_asi_users` (
  `id` int(3) NOT NULL default '0',
  `login` char(3) NOT NULL default '',
  `nom` varchar(30) NOT NULL default '',
  `prenom` varchar(30) NOT NULL default '',
  `pwd` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_login` (`login`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

[30 Sep 2005 22:16] Stephane PAQUOT

Hi Miguel,

I'm really sorry for having wasted your time. I didn't think you would analyze this problem so quickly.
This problem is completely my fault : there was a special character at the end of the sql file.

I apologize.

Best regards.

[30 Sep 2005 22:26] MySQL Verification Team

Thank you for the feedback.