[peterlaursen]

What is the idea in creating 2 Forums post and 1 ticket? One contact option is enough!

I have replied in your ticket

[peterlaursen]

Also please tell if your mouse uses a non-Windows Standard/non-Microsoft driver (such as Logitech for instance).

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

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.

[peterlaursen]

about init_connect in MySQL:

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

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

We have no such plans.

[peterlaursen]

Thanks for contacting us here. I think you read my comment in our issue tracker (I guess it was your comment here: http://code.google.com/p/sqlyog/issues/detail?id=1119 (?) )

We will l reply, but due to Xmas/New Year/holiday season it could take a week or more.

[peterlaursen]

Also note that the default setting is not to sent an alert at once. The alert value must be exceed a number of times. You can set this number to “1” if you want to have alerts at once.

[peterlaursen]

It is very likely that you have some queries generating very large temporary tables. Those queries will then typically be slow as well. Such queries can be queries with JOIN of tables with indexes not optimal or queries with SUBQUERIES. Subquerires have poor performacne before MySQL 5.6/MariaDB 5.5. In particular the construction SELECT … WHERE IN (SELECT ..) can be a very bad performance killer.

So you should identify those queries, rewrite them or add indexes to tables to make the queries perform better.

To identify the queries I would suggest that you turn on the 'query sniffer' (The processlist based sniffer is OK for this and it is very simple to setup). Data collected can then be analyzed in both the 'query analyzer' page and the 'Way back machine' pages in MONyog.

[peterlaursen]

I have turned the EXPLAIN option OFF in preferences .. powertools .. query profiler settings. Please see my settings in attached image:

[attachment=1840:settings.jpg]

I have this simple table:

CREATE TABLE `s` (


`a` INT(11) DEFAULT NULL


) ENGINE=INNODB DEFAULT CHARSET=utf8

I execute from the editor:

INSERT INTO s VALUES (1);


SELECT * FROM s;


SELECT * FROM s LIMIT 100;

In HISTORY I have:

/*[14:43:23][2 ms]*/ SHOW VARIABLES LIKE 'profiling';

/*[14:43:23][0 ms]*/ SET PROFILING = 1;

/*[14:43:23][0 ms]*/ SET profiling_history_size = 0;

/*[14:43:23][0 ms]*/ SET profiling_history_size = 15;

/*[14:43:23][6 ms]*/ SHOW STATUS;

/*[14:43:23][3 ms]*/ SHOW STATUS;

/*[14:43:23][5 ms]*/ INSERT INTO s VALUES (1);

/*[14:43:23][3 ms]*/ SHOW STATUS;

/*[14:43:23][0 ms]*/ SHOW PROFILES;

/*[14:43:23][65 ms]*/ SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 5 GROUP BY state ORDER BY `duration (summed) in sec` DESC;

/*[14:43:23][0 ms]*/ SET PROFILING = 0;

/*[14:44:55][0 ms]*/ SET PROFILING = 1;

/*[14:44:55][1 ms]*/ SET profiling_history_size = 0;

/*[14:44:55][0 ms]*/ SET profiling_history_size = 15;

/*[14:44:55][4 ms]*/ SHOW STATUS;

/*[14:44:55][1 ms]*/ SELECT * FROM s;

/*[14:44:55][3 ms]*/ SHOW STATUS;

/*[14:44:55][0 ms]*/ SHOW PROFILES;

/*[14:44:55][1 ms]*/ SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 11 GROUP BY state ORDER BY `duration (summed) in sec` DESC;

/*[14:44:55][0 ms]*/ SET PROFILING = 0;

/*[14:45:53][0 ms]*/ SET PROFILING = 1;

/*[14:45:53][3 ms]*/ SET profiling_history_size = 0;

/*[14:45:53][0 ms]*/ SET profiling_history_size = 15;

/*[14:45:53][5 ms]*/ SHOW STATUS;

/*[14:45:53][1 ms]*/ SELECT * FROM s LIMIT 100;

/*[14:45:53][2 ms]*/ SHOW STATUS;

/*[14:45:53][0 ms]*/ SHOW PROFILES;

/*[14:45:53][1 ms]*/ SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 17 GROUP BY state ORDER BY `duration (summed) in sec` DESC;

/*[14:45:53][0 ms]*/ SET PROFILING = 0;

This is expected. There is no EXPLAIN statement executed. And I do not get such error then of course.

If you get a server syntax error complaining about an incorrect EXPLAIN statement, it must either mean that an EXPLAIN statement is executed – and one with an incorrect syntax – or it is a server bug.

Can you provide a detailed test case similar to above?

[peterlaursen]

Issue with 'custom filter' dialog ALT+TAB verified as described! Really strange! We will check this after the weekend.

[peterlaursen]

1) Please tell the MySQL server version you are using (execute “SELECT VERSION();“)

2) The EXPLAIN statement that SQLyog sends (if it does) can be found in HISTORY tab. Please try to execute this statement from the editor.

[peterlaursen]

On Linux use SJA for Linux available from http://code.google.com/p/sqlyog/downloads/list

[peterlaursen]

In the program help file .. version history page:

Quote:

SQLyog 10.5 (December 2012)

Features

— New major feature: “Visual Data Compare” (in short 'VDC'). It is a third option for Data Sync in addition to the two already existing ('one-way synchronization' and 'two-way synchronization'). A VDC-job is started from a modified version of the Data Sync wizard (from the 'powertools' menu). With VDC you will be able to compare differences visually and specify tables and rows that shall be sync'ed as well as to decide in what direction to sync. A SQL preview is available. VDC is an ULTIMATE feature.

— …

If you are considering to switch to Ultimate, please contact [email protected]. You will of course only need to pay the difference.

[Author]

Posts