[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

[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.

[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]

'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]

navyashree.r wrote on Jun 18 2009, 05:54 AM:

Hi,

Thank You for your request. We have added this feature in our list,

http://code.google.com/p/sqlyog/issues/detail?id=1093

Priority is not yet set.

Regards,

Navya

Ok, great.

One other minor improvement (wasn't worth starting a new topic) is when I press Ctrl-L the SQL output is text which is really great. I can then do a Ctrl-F to find the output text I'm looking for, also great. But when it can't find the text it pops up the usual dialog “”End of the document is reached. Do you want to continue searching from the beginning?”. The buttons are “Yes” and “No”. All of this works as expected. But I'd like to be able to press ESCAPE to cancel this dialog so it returns to the Find dialog and here I can also press ESCAPE to cancel this dialog (this dialog already detects Escape and closes properly). I find it easier to press Escape twice rather than having to press the “No” button (or Alt-N) then return to the Find dialog which doesn't have a short cut key for the Cancel button so now I have to use the mouse to hit the cancel button. I find it easier if I can use the keyboard to close these dialogs, and escape works best because I don't have to use the Alt key. Anyways, that's it.

dbForever

[DbForever]

DAE51D wrote on May 15 2009, 07:41 PM:

You just made my day. Can't wait! 🙂

You forgot to say “Amen” 😆

dbForever

[DbForever]

peterlaursen wrote on May 13 2009, 02:58 PM:

I only want to add that the reason why the RESULT tab opens in read-only mode is that with queries using JOIN (or UNION) the same column-name may exist in more than one table. The result set has no info about from what table every row in the result set! That is why user will have to decide what table to work on. SQLyog has no information from the server that can be used.

Sure, that makes sense. But I'm only selecting one table at a time and SqlYog should be able to determine that. Adding a simple edit checkbox in the memo dialog would help a lot for this single table queries. Thanks.

DbForever

[DbForever]

peterlaursen wrote on Mar 13 2009, 06:49 PM:

Please detail this request!

Quote:

1)

If I have

select 1;select 2;select 3; (I hope you will understand that I try to describe 3 different cursor positions!)

.. should it apply to all 3 caret positions or only ?

If the cursor is at caret1, then it should execute Select 2.

If the cursor is at caret2, then it should execute Select 3.

If the cursor is at caret3, then it shouldn't execute anything because it is outside of all the sql statements.

Quote:

2)

And what about these to

select 1;select 2; — no space between statements here

select 1; select 2; — at least one space (or tab or linebreak) after 1st semicolon

.. should those be treated differently?

No, they are treated the same.

Now sorry for the confusion but when I first posted the message, a simple query like this:

select * from table

would fail to execute unless I moved the caret to:

select * from table

So I would always have to move back into the sql text in order to execute it with F9 even though there was nothing after the single sql statement.

But now today I see it is working ok with v8.02. I took a SqlYog update but it is still displaying v8.02. Maybe it got fixed by itself or when I rebooted? Maybe I should wait a day before reporting a problem (or go lie down for a while).

So it is working ok now.

dbForever

[DbForever]

'peterlaursen' wrote on 'Jul:

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

[DbForever]

peterlaursen wrote on Jul 24 2008, 12:00 PM:

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

[DbForever]

peterlaursen wrote on Jul 18 2008, 09:24 PM:

We have started coding resizability for dialogues where it makes sense.

'manage indexes' will be among the first, probably to be completed next week. You can expect it in next release.

Can you also make the Insert/Update Blob window resizeable? I store a lot of text and it is difficult to try and view it in the small window. Also a Find ^F would be ideal too. Thanks for a great product. 🙄

DbForever

[DbForever]

peterlaursen wrote on Dec 17 2007, 01:13 AM:

They are of course both good and reasonable requests, but I also think you do not yourself fully understand the 'depth' of them.

Quote:

#1:

This may seem rather simple when you type from an end but when you copy blocks of code into the editor it is not, because then everything will have to be recalculated. Also as long as there are matching “(” and “)” it is also far more simple than if they do not match! I think you would expect SQLyog to tell which one should be removed or where one should be added is case there are 16 “(” and 17 “)”. Basically the SQL-editor does not understand SQL (it does not even try) .. it lets the server decide what is right and what is wrong! SQLyog is not a 'stand-alone' program – it is a database client that is only thought to be operational when connected to a mySQL server. The MySQL server has no such integrated IDE functionality that we can query. Anyway this is something that we would like to do. The problem is not so much to identify matching “(” and “)” – but to find out what to do when they do not match!

I think you are reading more into my request than necessary. I don't want SQLyog to try and interpret SQL, that would be silly. All I was suggesting that if the caret is on a “(“, highlight the matching “)”. This is trivial for a text editor. All it has to do is sum +1 for each “(” and -1 for “)” starting from the current “(” and when the sum reaches 0, highlight that “)”. Same if the caret is on a “)”, you would go in reverse order. If the sum does not go to zero (missing parenthesis), then no closing parenthesis is highlighted. This would help immensely in balancing parentheses.

Quote:

2:

Here you first make a simple mistake. This “I have a dual core machine so I should be able to go to another query table” is basically wrong. It does not matter if you have one or 16 cpu's because no matter have many cpu's or cores you have it is the OS that gives cpu time to processes and threads. Hardware simply does not matter here – it is a software issue only!

Also again I think I will have to remind you that SQLyog is a client program. You cannot expect anything from SQLyog that the MySQL communication protocol does not support! With one connection you cannot send another query to the server before it has returned the result of the first query! What could be done was to create a connection for every query tab. But then you may equally well start 'a new connection using current connetion settings' (or another program instance for that sake). Note that this option is in the file menu allready (and you can use it with a single single core cpu – windows can handle it!) – it just will open connections in different windows and not different tabs! But an option to (transparently) start a new connection for a new query tab is worth considering! We have several requests for a 'tabbed connections' interface and this request may unite with it. And will will probably be easier to navigate than this:

[attachment=803:connections.gif]

Basically MySQL client-server communication is 'ping-pong-ping-pong' and cannot be 'ping-ping-ping-pong-pong-ping-pong-pong'. After one 'ping' is sent over a connection it is not possible to 'ping' again until after the 'pong' corresponding the first 'ping' has been received.

But you are right that there is no compelling need that you should not be able to start typing in another tab – that is lack of 'multithreadedness' in SQLyog alone that prevents it. But you will have to wait to execute it or establish another connection to the server!

Ok, that makes sense. I thought the SQLyog tabbed windows were multi-threaded and could establish a new connection automatically.

[Author]

Posts