Bug: Cannot Update Merge Table Using Results Grid

[DbForever]

I noticed if I try and update a row value from a Merge table, the value gets changed in the grid, but not in the table. No error is reported.

Example:

select * from MergeTable;

and I then change the drop down from “Read-Only” to “MergeTable”, then edit one of the cell values, and save the results, everything appears to have worked. But doing another

Select * from MergeTable shows that the change did not get written back to the table. To get this to work, I have to do a select on the underlying table and not the Merge table.

Using v8.2 RC2 Community Edition.

[Khushboo]

Hi,

Its not a bug in SQLyog.

We tried with a test case:

CREATE TABLE t1 (

a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

message CHAR(20)) ENGINE=MyISAM;

CREATE TABLE t2 (

a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

message CHAR(20)) ENGINE=MyISAM;

INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

CREATE TABLE table_merge (

a INT NOT NULL AUTO_INCREMENT,

message CHAR(20), INDEX(a))

ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

SELECT * FROM table_merge;

And tried to edit from result tab, it worked fine at our end.

So, can you please check the following cases:

1. Check that “You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table”.

2. Check your create table syntax, as there are some limitations for merge tables documented here:

http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html

3. Are you able to perform edit and save in table data tab?

Thanks & Regards,

Khushboo

[peterlaursen]

Also you may try to write the UPDATE statement yourself.  Does it make any difference?

[DbForever]

'Khushboo' wrote on '22:

Hi,

Its not a bug in SQLyog.

It could be a MySQL limitation or how SqlYog identifies the row being updated.

Your table structure is similar to mine, with an AutoInc as a primary key. So what happens when there are thousands of rows in each table and each table has the SAME set of AutoInc values in each table, say 1..10000. How does MySQL know which table to update if it is using the primary key to identify the row? In my case, it will have to use the second index which is a compound key of 4 values which uniquely defines the row in all tables.

BTW, my Merge table is comprised of around 30 tables where each table has a year's data, from 1980 to 2010. So it is very easy for me to determine where the row came from in the merge table because of the date column (each table is named with the year as in T1980 or T2009 etc. and the Merge table is built from those).

So my question is, does SqlYog use ALL unique keys to identify the row in the merge table, or just the primary key? If it uses only the primary key then it could be updating the row in the wrong table since each of the tables will have similar sequence of AutoInc values.

TIA

dbForever

Quote:

So, can you please check the following cases:

1. Check that “You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table”.

Yes, I'm running as Administrator (I'm the only user)

Quote:

2. Check your create table syntax, as there are some limitations for merge tables documented here:

http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html

Can't see anything that would affect me

Quote:

3. Are you able to perform edit and save in table data tab?

If the range is small enough (0..10) on the Merge table, then it displays rows from the first table in the Merge table and I can edit and save the values fine using the Table Data, as expected. I then chose a new range from 5000000 for 10 rows and edited the value. This selected rows from a middle table in the Merge table. It appeared to save the value back to the table (no error) but doing a query on the merge table for that row shows the row was NOT changed.

So SqlYog (or MySQL) is updating the wrong row probably because it is using the wrong index to identify the row.

Quote:

Thanks & Regards,

Khushboo

[DbForever]

'peterlaursen' wrote on '22:

Also you may try to write the UPDATE statement yourself.  Does it make any difference?

The Update statement works fine if I use all unique index values to identify the row. I can't just rely on the primary key (AutoInc) because it is not unique among all tables of the Merge.

dbForever

[DbForever]

I did a Select on the merge table to find out which row it did update. Sure enough it updated the WRONG row. SqlYog will update the first table in the Merge table that has the same AutoInc value as the row I am editing. This can of course produce very wrong results because the AutoInc values are repeated in each table making up the Merge table. It looks like I will have to define a starting Inc for each table with the hopes that they don't overlap.

To get around this problem SqlYog could use all unique key values to identify the row. It is very unlikely that 2 rows will have the same AutoInc value and unique index values.

dbForever.

[peterlaursen]

“SqlYog will update the first table in the Merge table that has the same AutoInc value as the row I am editing.” Well .. It is the MySQL server that does.  To continue Khusboo's example:

The MERGE table has no Primary Key and thus SQLyog will generate an update statement like 

Code:

UPDATE `merge`.`table_merge` SET `message`='blah' WHERE `a`='1' AND `message`='Testing';

next this message will appear

[attachment=1339:more rows.jpg] .. and that is also what happens: 2 rwos will update

Please always paste you *exact* table definitions. Nothing more and nothing less!  

But I think you can forget about any idea that we should consider another table when updating one table. Then every user will have some special situation that we will have to consider and everything will soon be total mess.

General advice on how to use MERGE tables is and always was to INSERT, UPDATE and DELETE from the underlying tables and SELECT from the MERGE table.

Additionally if you encounter failure to update please read:

http://www.webyog.com/faq/content/29/162/en/tables-with-floating-point-datatypes-fail-to-update.html

[DbForever]

'peterlaursen' wrote on '23:

“SqlYog will update the first table in the Merge table that has the same AutoInc value as the row I am editing.” Well .. It is the MySQL server that does. 

It is really the Where clause that SqlYog uses in the Update that does that. This can easily be changed which is what I'm recommending.

Quote:

To continue Khusboo's example:

The MERGE table has no Primary Key and thus SQLyog will generate an update statement like 

Code:

UPDATE `merge`.`table_merge` SET `message`='blah' WHERE `a`='1' AND `message`='Testing';

If the MERGE table has no Primary Key, then why is SqlYog relying on that key and that key alone to update the edited row?

Quote:

next this message will appear

[attachment=1339:more rows.jpg] .. and that is also what happens: 2 rwos will update

I have that option checked and I don't see that message. Only 1 row gets updated in my Merge table.

Quote:

Please always paste you *exact* table definitions. Nothing more and nothing less!

The table has over 100 columns in it and my non-disclosure agreement prevents me from publishing it.  

Quote:

But I think you can forget about any idea that we should consider another table when updating one table. Then every user will have some special situation that we will have to consider and everything will soon be total mess.

Huh? All I'm saying is to include all unique index values in the Where clause of the update for that row. If the table had another index like “ix_Trans (Cust_Id, Trans_Date)” then the where clause should have been

Code:

UPDATE `merge`.`table_merge` SET `message`='blah' WHERE `a`='1' AND `message`='Testing' and `Cust_Id`=123 and `Trans_Date`=”2009-02-13″;

This will help considerably to reduce the chance of updating the wrong record. SqlYog shouldn't depend on the primary key alone when updating the row. Use as many of the unique index fields as possible in the Where clause. That's what I was trying to say.

Quote:

General advice on how to use MERGE tables is and always was to INSERT, UPDATE and DELETE from the underlying tables and SELECT from the MERGE table.

And that is how I do it in my application. However for a simple one time update of a value in one row, I thought SqlYog should be able to handle it accurately by editing the cell value in its grid.

dbForever

[peterlaursen]

 1) “If the MERGE table has no Primary Key, then why is SqlYog relying on that key and that key alone to update the edited row?”  It is not as there is no PK in the MERGE table.  The WHERE lists all columns for same reason.

2) “Only 1 row gets updated in my Merge table”.  And what is the UPDATE statement from HISTORY?

3) “my non-disclosure agreement prevents me from publishing it”.  Then please try to create a 'dummy' reproducible test case.

4) “to include all unique index values”.  The problem is that the MERGE table has no information that this column is unique in another (MyISAM) table. Did I miss something?

5) “SqlYog shouldn't depend on the primary key alone”.  Primary Key (or unique key on non-NULLable columns) is a unique identification of records.  That is the purpose of it really. But besides the MERGE table does not have a PK, so there is no such to use.

6) To manage INSERTS MySQL provides a mean: (refer: http://dev.mysql.com…e-engine.html) 

“You can optionally specify an INSERT_METHOD option to control how inserts into the MERGE table take place. Use a value of FIRST or LAST to cause inserts to be made in the first or last underlying table, respectively. If you specify no INSERT_METHOD option or if you specify it with a value of NO, inserts into the MERGE table are disallowed and attempts to do so result in an error.  But no such server side functionality exists for update”

I think the best solution would be to ALTER the underlying tables.  Instead of having a PK on an auto-increment column only consider creating a PK based on two columns: the existing one + one more column that is constant in each table but different across tables. That will ensure that even all rows in the MERGE table are unique.

[Author]

Posts