[peterlaursen]

I thin you missed the point her:

Code:

/*[23:50:24][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog` order by id limit 0,100
/*[23:50:26][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 100,100

either you mist ORDER BY PK with each select or not at all. The two select above may fetch identical rows!

The ORDER BY clause must be the same for all SELECTs on the same table with CHUNKS.

[peterlaursen]

This CHUNK thing simply is against the very nature of the Internet (at least the HTTP part of it) I believe.

[peterlaursen]

I just checked the Backup 'powertool'. Looks like this has been messed up the same way. Fortunately we have phpMyAdmin 😮

[peterlaursen]

deep silence!

I asked if there was any need that I test this on more different computers?

If you have verified there is no need – if not I might do!

added: well I did test on both my computers. Problem is the same. And it is the same on my personal ISP too.

And I believe the new CHUNK feature with HTTP-connections is the reason. With 100.000 rows in a table you will now issue 1.000 SELECTS. Each of them must create a new connection SQLyog <> tunnelling script <> webserver <> MySQL. Establishing conncetion 1.000 times is 1.000 times slower than doing it only once! And establishing this SQLyog-PHP-webserver-MySQL connection is what takes most of the time. SQLyog client must sit idle and wait each time this is happening. Network software, hardware and webserver is in control. SQLyog is out-of-control and has to wait.

Try exporting a big table yourself with HTTP and watch HISTORY while job is running. You will see that each time a connection is established several seconds pass. That is how slow the type of connection is established. Selecting 100 rows and 100.000 rows in one SELECT are both very fast. It only depends on MySQL performance – nothing else once webserver <> MySQL connection is established. Do as few queries as possible. Do one SELECT only when it is possible – not 1.000 SELECTs. This is particularly important with HTTP-connections. Beta 5.1 multiplies the performance issue with HTTP-tunnel by iterating the 'weak link in the chain' multible times. 1.000 SELECTS and 4 seconds for connections to establish in average is more than one hour. One hour idle time for SQLyog! And not unrealistic at all!

BULKs are created at the client, and performance is not affected by the connection type at all. CHUNKs are each generated with one query to the server and takes a new connection for each. First/BULK is right. Second/CHUNK is wrong!

Changing the mysql_connect() method to mysql_pconnect() in tunnelling file does not make much difference. What I believe indicates that it is not webserver <> MySQL connection that is a problem, but establishing connection over the Internet. Also when running with a local server the difference is not that big.

[peterlaursen]

@ ritesh: whose problem are you now talking about?

Lacylester's problems is the empty columns names. Simply. no base 64 thing.

I just wanted to be sure that there was no issue with a rare sql_mode too.

I'll repeat:

We have four issues:

1) SQLyog beta 1 generates empty column names with HTTP-tunnelling. You toul me that was fixed in BETA2 tree.

2) SQLyougtunnel.php did not call the function doing the set_names

3) With recent PHP versions there is an problem when SQL is generated from the GRIDs.

4) The base64 issue that I shal not comment

Those four issues may occur individually or at the same same. Hence the confusion. Do you agree? Did you verify 3) on my server?

To me it seems like you are completely confused abot this! Lets have BETA 2 ASAP so we can see what is left of it!

[peterlaursen]

reproduced on my local with HTTP. Same data.

Export is about 3 times faster then with the remote server, but the ratio 5.03/5.1 is the same. More than 10 time slower with 5.1 Beta1. Same with max_allowed_packet from 1 to 100 MB, same on MySQL 5.0 and 5.1.

ritesh: All tests are done on my 'mini' (server+client). Did you reproduce with a more 'standard' machine or should I do?

[peterlaursen]

I have lived with this for 3 years so I might just survive 3 months more.

But basically I agree: 5.1 + 5.2 as fast as possible and then a lot of 'polishing' + user management and SQLyog is complete as of MySQL 5.0.

Next comes GUI builder, MySQL 5.1 GUI functionalities etc …

[peterlaursen]

I am sorry 😀

But I think I know how to 'wake you up'. I just wait till after BETA2 to concentrate on this then. Maybe even then MySQL would reply to my requests at bugs.mysql.com so this can be taken into consideration

You know DEFAULTs are my personal pets! But the character/SQL issues and autocomplete finished with Beta 2 isn't bad either.

[peterlaursen]

@ritesh

I wrote this earlier

Quote:

… Because if you save a literal 'NULL' (using backquote = `NULL) and open it again it displays just like a NULL, and next you save it would become NULL.

Are you PERFECTLY SURE, that you read and understood this. You did not comment on it. (Too) often that implies that you were not concentrated when you read it!

Now I ask you again: The solution that you have come up with, will it secure that a literal-keywords (ALL of them!) do not become a functional-keywords after multible reads and saves (and the other way around of course too)?

[peterlaursen]

That was what I expected (would normally be so if not server is running in ANSI-mode).

So with you server there is no need to/idea in setting sql_mode = '' – becuase it is already. So this is not what your are missing!

You can do this to understand your issue: Simply try to insert a row using HTTP-tunnel. Yo get the error message and nothin is inserted. However the query is logged to HISTORY. Look at it and you'll se that column names are empty strings.

[peterlaursen]

And if it could be done then I shall request that CREATE TABLE, ALTER TABLE, STRUTURE SYNC etc. panes in a similar way are prevented from 'stealing focus'. If you are in th proces of building a complex DB structure with CREATE TABLE, and just need to do something else (could be checking the table structure of another table in OBJECTS pane for instance), then you must close down those panes.

But of course here too, you can start another program instance.

But it has been an annoyance to me with all SQLyog till now that these popup-panes steal focus the way they do. I don't believe it has to be like that. Most programs don't do like this. An option with the library that you overlooked/never used?

[peterlaursen]

While the export is running, the 'popup' completely steals focus, and I am not able to work in the main window!

Maybe the export-code is running in its own thread, but the GUI pane for selecting parameters for the export is not then!

What I request is to be able to work with SQLyog while export/import is running.

[peterlaursen]

And let me add that I also believe that I found the reason for the 'Turkish' issue. I have mailed details to Ritesh. It would take to much space here to explain, and would have to be documented with a lot of examples. But Ritesh has all this now.

But basically it is an issue with the GRIDs of DATA and RESULT panes. It was pretty tricky since PHP up to 4.3 (and possibly 5.0) accepted and executed the SQL anyway. But PHP 4.4 and 5.1 don't.

I believe all significant issues with SQLyog 5 (5.1 BETA and previous) – except the slow export that I just reported – are now fully understood. It can't be long till we'll see BETA 2. I also know about significant improvements in auto-complete, so it is going to be a terrific release, I think!

[peterlaursen]

There are two issues here.

1)

One is fixed by Ritesh in BETA 2 code tree. It simply generated empty column names with INSERTs and UPDATES and HTTP connections. This issue/bug affects 5.1 BETA1 only

2)

With MySQL version 5 running in some STRICT MODE as server default, SQLyog must issue the command set sql_mode = ''. It does! Actually the code is in the Tunnelling file allready, but this function in the PHP-code is not invoked! This is a bug! I believe this is fixed allredy too, though ritesh has not confirmed this to me. But it is pretty simple to see this, once you understand the structure of the tunnelling script! It simply must call the function in the script immediately after (or before) calling the function that does the SET NAMES. This issue affects all SQLyog versions when connecting with HTTP to a MySQL version 5.x server running STRICT MODE. However this is a bug with the tunnelling script only – not the SQLyog or SJA executables – and anyone who knows how to use NOTEPAD can easily fix it herself!

Your issue is the first one! set sql_mode = '' has only effect with MySQL 5. With 4.1 sql_mode must be specified with the connection string, and can't be changed in the midst of a connection. Read about sql_modes: http://www.webyog.com/faq/28_72_en.html.

Conclusion is that you will have to wait for BETA2 to INSERT and UPDATE using HTTP-tunnel.

What does your MySQL return if you execute:

Code:

SELECT @@sql_mode;

??

[peterlaursen]

I think you are right about the focus. Actually the 'STOP' button clicks, when pressing .

And this is not fixed with SQLyog 5.1 BETA1.

[Author]

Posts