[peterlaursen]

The problem is undoubtedly a variant of this one:

http://www.webyog.co…-in-sqlyog.html

.. and as such a problem with thow you store data in your database.

Now please tell:

1) How was the SQL-dump created? With SQLyog 5.x or 9.6x or another application? Does the import job create the tables of did they exist in advance?

2) How is the dump file encoded?If it is UTF8 please open the dump in Notepad or any utf8-enabled editor and check if the accented characters display correctly here?

You are welcome to create a private ticket by sending a mail to support@webyog,com. We wil need to see some sample data/scripts and you may not want to exchange them in public.

[peterlaursen]

REPAIR TABLE seems to be overkill. OPTIMIZE TABLE should do. I came across this: http://www.pythian.com/news/1114/mysql-back-to-basics-analyze-check-optimize-and-repair/

“OPTIMIZE TABLE is used to remove overhead, sort indexes for better access, and generally keep your tables in good order — but is often overrated and used more often than necessary. I’ve seen a lot of people using OPTIMIZE TABLE daily or even hourly, but that is generally not needed, unless you do massive amount of INSERT and/or DELETE on a few tables, and even then daily or weekly will be enough — just remember to run it only on the affected tables.”

But I have to admit that I am not really able to evaluate such information myself.  

[peterlaursen]

Another way to check if tablespace fragmentation is the basic problem here is to dump the database and import to another database/server. Next compare speed of statement on the two databases.

[peterlaursen]

OK .. that server should definitely be fine.

With MyISAM tabels I have seen recommendataion many tiems that a REPAIR TABLE should be scheduled regularly (some recommedation I've seen say weekly). But I cannot judge myself. If there are (almost) only INSERTs to the table (so that the tablespaces grow 'at the end' I don't think so), but if there are DELETES and INSERTS randomly I believe the tablespace could easily fragment. REPAIR TABLE will fix that – but if will also LOCK the table and it will effectively go offline for as long as the statement runs.

I think you should try (when it is possible) to execute SHOW TABLE STATUS LIKE `table` FROM `database` WHERE ENGINE IS NOT NULL; .. next REPAIR all tablles in the database, next run the SOW statement again and see if speed has improved. It could alos have good efffect on the applications.

But I agree that SQLyog should not depend on that. Hiding the INFO tab is one option. Optimizing queries could be another. We will discuss after the weekend

[peterlaursen]

It looks mostly like an 'over-burdened webserver' (“This has been going on for months now” indicates this, I think. Traffic to the server has increased lately and has exceeded what the server can reliableyhandle). It could also be a conenctivity error (but I doubt).  

Is it the same tables that fail to backup every time? I doubt so. To me it looks more relevant to complain to the hosting than to us.

Please try this simple tests: connect ot the tunneller from a browser: When successfull refresh. Are you able to refresh around 100 times without getting a 'Not Found' error or similar? (you may need to turn off the browser cache).

[peterlaursen]

“SHOW TABLE STATUS FROM `database` WHERE ENGINE IS NOT NULL;” is one of the queries that populate the INFO tab for a table

1) Please try to CHECK TABLE or REPAIR TABLE for tables where this slowness appears. The slowness is a server issue. SQLyog simply sends a few trivial SHOW queries.

2) What is the exact MySQL 5.1 version you are using? A Mysql version has three digits x.y.x (like 5.1.62 – the latest 5.1). Execute “SELECT VERSION();” if you are not sure. If it is not at least 5.1.60 or close to it please upgrade the server. Please refer: http://www.webyog.co…-supported.html (and the recommendation 5.1.35 or higher should probably be raised now!). You may also need to dump data and import if this is an issue with an early 5.1 server.

3) Are affected tables MyISAM tables or InnoDB tables? If InnodDB do you use the 'built-in' InnoDB or the 'InnoDB plugin'?

4) How many rows are in affected tables?

5) Are you sure the query executes when closing the INFO tab? It does not for me – so probably I don to understand what you are trying to tell here.

6) Do you see any change in behavior with 9.63 as compared to previous versions? If so what was your previous version?

If you close the INFO tab and next close SQLyog, the INFO tab will not open next time you open SQLyog.

[peterlaursen]

Please read the comment that I wrote to our blog as late as yesterday: http://www.webyog.co…eased/#comments

We do intend to make tabs moveable (both preferences settings and drag'n'drop are options). But we need to complete the 'revamp' of the code that has been in progress since version 9.5. This will result in DATA tab moving to upper position as well. Only at that time the mutual dependencies of the code behind every tab has been 'relaxed' so that we can start moving tabs around.

[peterlaursen]

Please refer: http://www.webyog.com/blog/2012/03/22/sqlyog-mysql-gui-9-63-released/

With a — skip-character-set-client-handshake setting in configuration you can now enter SET NAMES UTF8 as an INIT-COMMAND for the connection in SQLyog

[peterlaursen]

No. But you can double-click on anything in the Object Browser and it will insert to the editor.

[peterlaursen]

Yes .. click the first row and SHIFT+click the last row. SHIFT+click works exactly like in gmail for instance all over the program.

[peterlaursen]

or “INSERT INTO .. ON DUPLICATE KEY …”. Point is that if you have Primary/unique Keys in the tables you will need to handle the conflict somehow.

[peterlaursen]

I have to insist that it does not matter what client you use. It is the server that returns this error. All 4-digit error numbers starting with “1” are.

[peterlaursen]

I don't believe this is the problem! This works fine for me:

Code:

SELECT
DATE_FORMAT (`dttest`.`dt1`, '%Y%m%d') AS `CheckDate` — <– note the SPACE
FROM
`test`.`dttest`
ORDER BY `CheckDate` ASC;

(`test` is a database, `dttest` is a table and `dt1` is a DATETIME column)

I simply think you mismatched names of database, table and column! Actually it looks like you typed 'database' literally instead of the actual database name.

[peterlaursen]

@moijisi: what version are you using?

[peterlaursen]

Is it this problem: http://code.google.com/p/sqlyog/issues/detail?id=1709 ?

[Author]

Posts