Description:
I am running Server 5.5.54 on Ubuntu, and have a problem.
I am using Perl and the MySQL Workbench to develop a tool. I have a table with an auto-incremented ID, and I create a hash for the ID that I use for various purposes. I create an entry in the table using a simple insert:
INSERT INTO myDB.myTable ( cpName, cpHash, cpText, cpCreated) VALUES ( "TEST_01" , "DUMMY" , "test text", "2017-05-12 15:49:54" )
This works fine. NOTE - The key field in the table is cpID... So, when I then run:
SELECT LAST_INSERT_ID();
I *do* get the correct cpID value for the new record.
for this example, lets say the returned value was 10 for cpID.
So, if I execute:
SELECT cpID, cpHash from myDB.myTable where cpID = 10;
I get back the correct values:
cpID = 10
cpHash = DUMMY
Unfortunately, everything goes south from there, and I am getting an error that does not make sense when I try to update the cpHash value.
If I execute the following:
update myDB.myTable SET cpHash='3W4' where cpID=10
I get the error message:
UNEXPECTED DBMS ERROR:: 0 / 1146 (42S02)
DBI::errstr:=> Table 'myDB.inserted' doesn't exist
- PLEASE NOTE - The error reports an incorrect name (e.g. 'inserted') for my table.
This occurs whether I run all commands from Workbench or Perl, and weather I run the INSERT from one tool and the UPDATE from the other.
I have tried working with a 'USE myDB' statement and eliminating the 'myDB.' prefix, but the problem remains AND the error does not change (e.g. it reports the full db.table syntax in the error and says:'myDB.inserted' does not exist).
I have also tried this sequence using transaction locks with various combinations of placement for COMMIT and ROLLBACK calls, and have the same results (except when I ROLLBACK the initial INSERT, in which case the new record never gets into the table).
Can anyone help me figure out what is causing this problem? Why is the MySQL engine changing the name of the table in my UPDATE command to myDB.inserted? I have a huge amount of data already in the table, and do not want to have to rebuild/reload it.
How to repeat:
Not certain how to cause a repeat on another server. For me, repeating the insert/update sequence continuously results in the error.
