Bug #15387 Table '.' doesn't exist
Submitted: 1 Dec 2005 15:37 Modified: 20 Jan 2006 17:57
Reporter: Mark Neri Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.13 OS:Linux (Linux 2.6)
Assigned to: CPU Architecture:Any

[1 Dec 2005 15:37] Mark Neri
Description:
From time to time, when we attempt a basic insert, we get back a SQLException with error code 1050.  The description is:
Table '.' doesn't exist
or 
Table '                             .     ' doesn't exist.

We are using the MySQL jdbc drivers.  Reconnecting to the database and running the same insert works fine.  We don't suspect that there is 
heavy load during this time.

How to repeat:
No sure.  We upgraded to mySQL 5 about three weeks ago.  We have seen the problem twice.  We do a few insert per second on average.  We are batching.
[1 Dec 2005 16:38] Nikolay Shestakov
I have this problem for 5.0.15 and 5.0.16 on linux (debian from packages) and windows (xp and 2003 server), but when i call SP.
if reconect to server this problem is free, but can repeat.
sometimes server is crash on this statement (SP), but after restart is work.

this SP like:

create procedure rm(CHAR ide)
begin
  declare lm date;

  select `LastMod` into lm from `t1` where `t1`.`id`= ide;

  if isnull(lm) = 0 then
    delete from `t2` where `t2`.`id` = ide and `t2`.`LastMod` < lm;
    delete from `t3` where `t3`.`id` = ide and `t3`.`LastMod` < lm;
    ...
  end if;
end;

but t2 and t3 have triggers on delete which update t2_1 and t3_1 accordingly like

creat trigger t2_update after update on t2 for each row
begin
  insert into t2_1 (`f1`, `f2`, ...)
  values (OLD.f1, OLD.f2,...)
  on duplicate key update 
    `t2_1`.`f2` = `t2_1`.`f2` + VALUES(`f2`),
     ...
end;

where f1 is primary key.
[1 Dec 2005 16:40] Nikolay Shestakov
in previous post is error - trigger on delete
[2 Dec 2005 8:18] Valeriy Kravchuk
Mark:

Please, inform, what version of jdbc drivers do you use and what version of MySQL server you are connecting to.

Nikolay:

Please, send all the SQL statements to repeat the problem you described.
[7 Dec 2005 13:42] Mark Neri
MySQL 5.0.13-rc-Debian_1

mysql-connector-java-3.1.11

The insert statement is as follows:
insert into trades
(id,valid,ts,order_time,fill_time,deal_date,value_date,instrument_id,quantity,rate,broker_id,broker_trade_id,broker_trade_time,trader,account,cpty_id,order_type,execution_mode,origin_time,user_req_id,alt_broker_trade_id)
values
(null,1,null,1132608956526,1132608956587,'2005-11-21','2005-11-23','EUR/USD','2000000.0000000000000000','1.17290000000000','HotSpot','3179224','16:35:56','RHAC','CSHARB026','HotSpot','IOC','API',1132608956525,'bertBlyleven:system3:24337|23954','3179223');

I don't think it matters, but id is an auto increment integer.  ts defaults to the current time.  Also, this problem is quite rare.  Less than once in 10,000 tries of similar inserts.  There is a trigger that deletes rows when the valid column becomes 0.  There is another trigger that moves rows to another table when they are deleted.  I don't expect that either one is triggering in this case.
[8 Dec 2005 18:23] Valeriy Kravchuk
Mark,

Thank you for the additional information. Please, send the results of SHOW CREATE TABLE trades (the table you are inserting into). Can you try to upgrade to 5.0.15 at least? I'll have to check the behaviour you described on the last versdion, in any case.
[14 Dec 2005 14:28] Mark Neri
Results of SHOW CREATE TABLE trades:

 CREATE TABLE `trades` (\n  `id` int(10) unsigned NOT NULL auto_increment,\n  `valid` tinyint(4) default NULL,\n  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n  `order_time` bigint(20) default NULL,\n  `fill_time` bigint(20) default NULL,\n  `deal_date` date default NULL,\n  `value_date` date default NULL,\n  `instrument_id` varchar(16) default NULL,\n  `quantity` decimal(16,3) default NULL,\n  `rate` decimal(14,8) default NULL,\n  `broker_id` varchar(16) default NULL,\n  `broker_trade_id` varchar(24) default NULL,\n  `broker_trade_time` time default NULL,\n  `trader` varchar(8) default NULL,\n  `account` varchar(10) default NULL,\n  `cpty_id` varchar(32) default NULL,\n  `order_type` varchar(16) default NULL,\n  `execution_mode` varchar(10) default NULL,\n  `origin_time` bigint(20) default NULL,\n  `user_req_id` varchar(32) default NULL,\n  `alt_broker_trade_id` varchar(24) default NULL,\n  PRIMARY KEY  (`id`),\n  UNIQUE KEY `trade_broker_id_index` (`broker_trade_id`),\n  KEY `trade_ts_index` (`ts`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1
[14 Dec 2005 14:34] Mark Neri
Upgrades to 5.016.  The problem may have changed, but is at least similar, except that breaking the connection and reconnecting doesn't seem to fix it.

Upgraded mysql drivers to 3.1.12.  No luck.

Tried innodb.  No luck.

Adding a clear batch call between insert batches may have helped, but we're not sure.

Here are some packets that were sent/received:
0000  00 14 c2 54 e6 3e 00 14  c2 54 a8 66 08 00 45 00   ...T.>.. .T.f..E.
0010  01 24 58 5b 40 00 40 06  cb 58 0a 01 01 0f 0a 01   .$X[@.@. .X......
0020  01 10 82 60 0c ea 2b 5b  96 2b 86 ea 71 43 80 18   ...`..+[ .+..qC..
0030  19 80 37 48 00 00 01 01  08 0a 54 46 64 42 59 43   ..7H.... ..TFdBYC
0040  06 22 ec 00 00 00 17 09  00 00 00 00 01 00 00 00   ."...... ........
0050  00 00 00 01 08 00 08 00  0a 00 0a 00 fd 00 fd 00   ........ ........
0060  fd 00 fd 00 fd 00 0b 00  fd 00 fd 00 fd 00 fd 00   ........ ........
0070  fd 00 08 00 fd 00 fd 00  f6 c8 de 27 08 01 00 00   ........ ...'....
0080  8a cb de 27 08 01 00 00  07 d5 07 0c 0e 00 00 00   ...'.... ........
0090  07 d5 07 0c 10 00 00 00  07 45 55 52 2f 4a 50 59   ........ .EUR/JPY
00a0  18 2d 31 30 30 30 30 30  2e 30 30 30 30 30 30 30   .-100000 .0000000
00b0  30 30 30 30 30 30 30 30  30 12 31 34 32 2e 35 33   00000000 0.142.53
00c0  30 30 30 30 30 30 30 30  30 30 30 30 08 43 75 72   00000000 0000.Cur
00d0  72 65 6e 65 78 0f 41 32  30 30 35 33 34 38 30 31   renex.A2 00534801
00e0  39 4d 58 30 30 08 00 00  00 00 00 00 04 26 04 52   9MX00... .....&.R
00f0  4b 4f 43 09 43 53 48 41  52 42 30 32 36 04 42 6f   KOC.CSHA RB026.Bo
0100  66 41 03 49 4f 43 03 41  50 49 00 00 00 00 00 00   fA.IOC.A PI......
0110  00 00 1a 74 72 61 64 65  42 6f 78 3a 73 79 73 74   ...trade Box:syst
0120  65 6d 31 3a 36 39 32 35  7c 32 30 32 38 04 6e 75   em1:6925 |2028.nu
0130  6c 6c                                              ll

0000  00 14 c2 54 a8 66 00 14  c2 54 e6 3e 08 00 45 08   ...T.f.. .T.>..E.
0010  00 7c ad 8e 40 00 40 06  76 c5 0a 01 01 10 0a 01   .|..@.@. v.......
0020  01 0f 0c ea 82 60 86 ea  71 43 2b 5b 97 1b 80 18   .....`.. qC+[....
0030  09 d8 16 8f 00 00 01 01  08 0a 59 43 06 23 54 46   ........ ..YC.#TF
0040  64 42 44 00 00 01 ff 7a  04 23 34 32 53 30 32 54   dBD....z .#42S02T
0050  61 62 6c 65 20 27 65 6c  65 74 65 64 5f 63 61 73   able 'el eted_cas
0060  68 4e 6f 54 72 61 64 65  73 2e 66 72 6d 2e 68 4e   hNoTrade s.frm.hN
0070  6f 54 72 61 64 65 73 2e  66 72 6d 27 20 64 6f 65   oTrades. frm' doe
0080  73 6e 27 74 20 65 78 69  73 74                     sn't exi st

And here is the insert that we did
2005-12-14@00:04:38.669:insert:com.mysql.jdbc.ServerPreparedStatement[9]
- insert into trades
(id,valid,ts,order_time,fill_time,deal_date,value_date,instrument_id,quantity,rate,broker_id,broker_trade_id,broker_trade_time,trader,account,cpty_id,order_type,execution_mode,origin_time,user_req_id,alt_broker_trade_id)
values (null,1,null,1134540278006,1134540278666,'2005-12-14','2005-12-16','EUR/JPY','-100000.0000000000000000','142.53000000000000','Currenex','A2005348019MX00','00:04:38','RKOC','CSHARB026','BofA','IOC','API',0,'tradeBox:system1:6925|2028','null');

In addition, there is a trigger on this table that deletes a record when it a certain field takes on a certain value.  This should not be triggering, ever.

The is also a trigger that adds into another table when a record is deleted.  I don't think this is happening, but it might be.
[14 Dec 2005 14:42] Mark Neri
One of the error messages that came back on this insert was as follows:

Table 'd.rod_rank, push_rank=old.push_rank, cash_exchange=old.cash_exch., push_rank=old.push_rank, cash_exchange=old.cash_exchange, futu' doesn't exist   
(seen 6 times)

This  is the text of a trigger on another table in the database.  We do not expect that this table was being modified at the time.  We are quite sure that it was not being deleted in a way that would set of this trigger.
[16 Dec 2005 16:44] Robert Zeh
We have a trigger on the table as well:

CREATE TRIGGER `trades_delete` BEFORE DELETE ON `trades` FOR EACH ROW INSERT INTO deleted_trades SET id=old.id, order_time=old.order_time, fill_time=old.fill_time, deal_date=old.deal_date, value_date=old.value_date, instrument_id=old.instrument_id, quantity=old.quantity, rate=old.rate, broker_id=old.broker_id, broker_trade_id=old.broker_trade_id, broker_trade_time=old.broker_trade_time, trader=old.trader, account=old.account, cpty_id=old.cpty_id, order_type=old.order_type, execution_mode=old.execution_mode, origin_time=old.origin_time, user_req_id=old.user_req_id, alt_broker_trade_id=old.alt_broker_trade_id
[20 Dec 2005 17:57] Valeriy Kravchuk
Please, send the results of SHOW CREATE TABLE for your deleted_trades table also. Without trigger your insert works OK for me:

mysql> insert into trades
    -> (id,valid,ts,order_time,fill_time,deal_date,value_date,instrument_id,quantity,
    -> rate,broker_id,broker_trade_id,broker_trade_time,trader,account,cpty_id,order_type
    -> ,execution_mode,origin_time,user_req_id,alt_broker_trade_id)
    -> values
    -> (null,1,null,1132608956526,1132608956587,'2005-11-21','2005-11-23','EUR/USD',
    -> ''2000000.0000000000000000','1.17290000000000','HotSpot','3179224','16:35:56','RHAC'
    -> ,'CSHARB026','HotSpot','IOC','API',1132608956525,'bertBlyleven:system3:24337|23954','3179223');
Query OK, 1 row affected (0,02 sec)

So, I have to repeat with trigger.

Have you tried to upgrade, by the way? Please, try 5.0.17 that is officially released already.
[27 Dec 2005 18:11] Robert Zeh
Valeriy,

I'm not sure you understand the problem.  The failure doesn't happen on every insert --- it happens once in a great while, perhaps one insert out of 5,000, where the 5,000 inserts could be spread out over a week.

After digging through the source code, it feels like mysql is ending up with a bad pointer.  But that's just a guess.  And, after looking over the documentation, it looks like our triggers are violating the following constraint by inserting records into the deleted_trades:

 The trigger cannot use statements that explicitly or implicitly begin or end a transaction such as START TRANSACTION, COMMIT, or ROLLBACK.

I'm assuming that an insert into another table is an implicit start transaction and commit.  Please let me know if I'm wrong.

Anyway, we've removed the triggers and backed out to MySQL 4.1.15-Debian.
[27 Dec 2005 19:36] Dmitry Lenev
Hi, Mark, Nikolay and Robert!

I suspect that you are observing effects of the bug #13399  "Crash when executing PS/SP which should activate trigger which is now dropped" (note that this bug can be repeatable when one uses FLUSH TABLES instead of DROP TRIGGER statement) or may be you are affected by the bug #12329  "Bogus error msg when executing PS with stored procedure after SP was re-created". Both these bugs were fixed in MySQL 5.0.17.
So can you give a try to this version (or even to the upcoming 5.0.18) and inform us if upgrade solved your problem?

And thank you for your interest in MySQL and your patience!!!
[21 Jan 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".