Sqlyog Stale Data

[mick]

Happy New Year!

I am using sqlyog ultimate- gui 10.3

Many times when I run a query, it returns me stale data. but when I create a new connection with same settings, I see the updated data.

and this is very annoying and unproductive. I am not completely sure (but positive) that this problem surfaced after upgrading sqlyog a few months back.

I searched about this problem without any solution. When I run,

SHOW VARIABLES LIKE '%timeout%'; it returns wait_timeout = 28800.

SELECT 1; Under history tab I see,

/*[10:56:20 PM][54 ms]*/ SET PROFILING = 1;

/*[10:56:20 PM][55 ms]*/ SET profiling_history_size = 0;

/*[10:56:20 PM][104 ms]*/ SET profiling_history_size = 15;

/*[10:56:20 PM][79 ms]*/ SHOW STATUS;

/*[10:56:20 PM][60 ms]*/ SELECT 1;

/*[10:56:20 PM][68 ms]*/ SHOW STATUS;

/*[10:56:20 PM][75 ms]*/ SHOW PROFILES;

/*[10:56:20 PM][58 ms]*/ SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 183 GROUP BY state ORDER BY `duration (summed) in sec` DESC;

/*[10:56:20 PM][61 ms]*/ SET PROFILING = 0;

/*[10:56:20 PM][57 ms]*/ EXPLAIN SELECT 1;

This stale data problem is driving me nuts 🙂

Thank you.

[Jan.S]

Hi Mick,

Wishing you a very happy new year!

We are unable to understand what the problem is. Could you explain what you expect as against what you are getting?

From what we can see, the output seems to be fine.

Also, when you say stale data, do you mean the output in result tab or the statements in history?

Regards,

Janani

[peterlaursen]

Please execute “SHOW VARIABLES LIKE 'autocommit';”. Does it return ON of OFF?

If it returns OFF you may use the option in the Connections Manager .. advanced tab as described in 9.63 release notes:

“Added an option to specify INIT_COMMAND(s) for a connection. INIT_COMMAND(s) are executed when SQLyog connects and reconnects. The primary purpose is to override settings specified with ‘init_connect’ setting in server configuration for the SQLyog session. For instance if ‘init_connect’ server setting specifies “SET AUTOCOMMIT = 0″ you may enter “SET AUTOCOMMIT = 1″ in the SQLyog connection manager if you want a non-transactional behavior for the SQLyog session.”

(If it is OFF all queries are running in a single transaction and if also transaction isolation (as specified by the server variable 'tx_isolation') is set to “REPEATABLE READ” (default in MySQL unlike most other databases) you will get the same snapshot of (InnoDB-)data every time.)

[peterlaursen]

about init_connect in MySQL:

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_init_connect

[mick]

'peterlaursen' wrote:

Please execute “SHOW VARIABLES LIKE 'autocommit';”. Does it return ON of OFF?

It is OFF. I have set it OFF under SQLyog connection manager > Advanced > Init commands as SET AUTOCOMMIT = 0; to avoid commit when query is incorrect.

Also, SHOW VARIABLES LIKE 'tx_isolation'; returns 'REPEATABLE-READ'.

I did not follow your post completely, above settings are my current settings. I will explain it again, we have couple developers using SQLyog.

If my collegue modifes something in his sqlyog and I am also connected to database, and I run the query I won't see that change unless I create a new connection and run the query again. And yes, I do execute COMMIT command whenever I really need to save data.

Surprisingly, our web app always fetches fresh data. Our db server and app server are in local network.

Thank you

[peterlaursen]

Please try to use the option added in 9.63 for this connection. Please see image for details.

[attachment=1843:init.jpg]

Your web application probably does not have this problems as it reconnects for every query (PHP does, JAVA (most often) does). SQLyog reuses the same connection unless it needs to reconnect for some reason and with REPEATABLE READ + AUTOCOMMIT = OFF|0 and when using InnoDB you will always get the same snapshot of data.

[mick]

'peterlaursen' wrote:

Please try to use the option added in 9.63 for this connection. Please see image for details.

The problem with SET AUTOCOMMIT = 1; is, if I write an update query (which happned to be incorrect because I forgot to add some important where caluse andit modified 1000 rows as opposed to modifying only 10 rows), it saves directly to database. (Oh god that was wrong query) I don't want to do that.

I want to write and run a query and see if it modifies/deletes *expected* number of columns and then execute commit command. so there is no need of rollback.

I am sure this is common expcted feature , people want to commit after making sure the query is correct.

I hope I have explained my situation clearly.English is my second language.

[peterlaursen]

Well .. then try instead to

SET [SESSION] tx_isolation = 'READ-COMMITTED'

(or alternative syntax described here: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html)

.. in the same place of the SQLyog Connection Manager.

I never tried this, but according to http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_tx_isolation this variable can be set on a SESSION basis as well.

[peterlaursen]

Actually MySQL/Oracle just published a Blog about this:

https://blogs.oracle.com/mysqlinnodb/entry/repeatable_read_isolation_level_in

[Author]

Posts