I noticed with one of my tables I can edit the cell values in the Result tab (after de-selecting "ReadOnly" in the combo box) from a simple query:
select * from Table
and I can press the Save Changes icon to save the changes or move to another row and do more changes. But if I re-execute the query, the new changes I've made in the grid are not there. Why not? I can edit other tables fine this way, just not this table or tables with the same structure. The problem table has 154 columns and 134 rows.

So I don't know if it's just me or a bug.

DbForever

SQLyog ComEd v6.56

Hi,
Can you please provide table dump with few rows so that we can reproduce at our end?

Regards,
Navya

One idea: are you sure that you are not updating a table in another database than the one you think you do.

In 6.56 this is possible (if the UPDATE statement is valid with the other database, MySQL will execute). If your SELECT uses full syntax (`database`. `table`) the database where the statement is executed (the last one selected from GUI) may be another one than the database selected.

7.0 RESULT tab will prevent you to update tables from other databases than the ones from where the SELECT was executed.


Alos try an update and check the statement in HISTORY and check MESSAGES as well.

One more idea!

Do you have floating numerical types (float, double) and is it used for defining the PK?

See: http://dev.mysql.com/doc/refman/5.0/en/pro...with-float.html

The WHERE clause generated by SQLyog GUI may specify a empty set. ("WHERE pkcolumn = 33.8" will fail simply if the value is stored internally as 33.8000000000000000000000007). We cannot support operations on FLOAT-PKs in SQLyog GUI for that reason! And also not tables with FLOATING types that do not have a PK.

(RESULT tab will use the PK-column(s) in the WHERE, provided that all PK-columns were specified in the SELECT. If they were not all, or if there is no PK, the WHERE will match all columns in the result with AND) .

I just published this FAQ:
http://webyog.com/faq/content/29/162/en/ta...-to-update.html

Peter,
I read your FAQ and it looks like this is the problem. The table does not have a primary key, but it does have 2 unique keys:
product_code Char(10);Inv_Date Date
Inv_Date Date;product_code char(10)

So it looks like the Date column is causing the problem. Is date also a Float data type? I'm thinking it is because it is related to datetime but without the fraction.
I modified the table and created a primary key using an Integer that was auto increment, and I was able to edit the grid and save the results without a problem.

Would my table joins or queries be faster if I switched the Date columns to an Integer? If so, should the data be stored as 20080722 so it is at least readable, or convert it to days elapsed since 1900 so it looks like 34521 etc.? Does anyone have an opinion? TIA

DbForever

1) all date/time datatypes in MySQL are stored as strings. No FLOAT problem here!


2) we do not consider UNIQUE KEYS when updating from the GRIDS. One reason is that a UNIQUE KEY may be defined on NULLABLE columns and for those columns where the value of such column IS NULL a UNIQUE KEY is actually non-unique!


3) I think the native date/time datatypes will be fastest and also most convenient in most situations. Only for data where it could happen that a DATE is TOO OLD for the MySQL DATE etc. type (like the year where Zarathustra was born!) types I would consider storing year, month, day etc. as seperate INTEGER values. However storing a UNIX_TIMESTAMP (as integer or as string) is also always a possibility. This is widely used by standard web applications designed to work on different RDBMS as UNIX_TIMESTAMP type is the same everywhere as opposite to DATE, TIME, DATETIME, TIMESTAMP etc. being implemented differently in different RDBMS. An example is our own FAQ system (phpMyFaq) that runs on many different RDBMS (MySQL, MS SQL, Oracle, Sybase, DB-2, SQLite and probably more)

Functions to work with UNIX_TIMESTAMPS are described here: http://dev.mysql.com/doc/refman/5.0/en/dat...-functions.html


4) I am not able to understand why updation failed for you as there are no FLOAT types. Could you provide an example (just a small table with a few rows or at least a structure-only DUMP) where this is reproducable? Maybe some detail that we should fix/consider/document. Also: did you check in HISTORY what SQL was sent?

[quote name='peterlaursen' date='Jul 24 2008, 07:10 PM' post='17516']
1) all date/time datatypes in MySQL are stored as strings. No FLOAT problem here!

Ok, good.


2) we do not consider UNIQUE KEYS when updating from the GRIDS. One reason is that a UNIQUE KEY may be defined on NULLABLE columns and for those columns where the value of such column IS NULL a UNIQUE KEY is actually non-unique!


3) I think the native date/time datatypes will be fastest and also most convenient in most situations.

Good because I really didn't want to change my application to handle a different date format.


Only for data where it could happen that a DATE is TOO OLD for the MySQL DATE etc. type (like the year where Zarathustra was born!) types I would consider storing year, month, day etc. as seperate INTEGER values. However storing a UNIX_TIMESTAMP (as integer or as string) is also always a possibility. This is widely used by standard web applications designed to work on different RDBMS as UNIX_TIMESTAMP type is the same everywhere as opposite to DATE, TIME, DATETIME, TIMESTAMP etc. being implemented differently in different RDBMS. An example is our own FAQ system (phpMyFaq) that runs on many different RDBMS (MySQL, MS SQL, Oracle, Sybase, DB-2, SQLite and probably more)

Functions to work with UNIX_TIMESTAMPS are described here: http://dev.mysql.com/doc/refman/5.0/en/dat...-functions.html


4) I am not able to understand why updation failed for you as there are no FLOAT types. Could you provide an example (just a small table with a few rows or at least a structure-only DUMP) where this is reproducable?

I tried to replicate the problem in a sample table and wasn't able to get it to fail. I'll work on it some more.

Maybe some detail that we should fix/consider/document. Also: did you check in HISTORY what SQL was sent?

Strange, but there was no Update SQL sent in the History. But when I added the primary key and edited a row, the History had the Update SQL. I'm not sure why the update sql was suppressed but I'll keep working on it.

dbForever