History Shows Sql-error With Export

[peterlaursen]

With the new CHUNKED feature of SQLyog 5.1 HISTORY shows queries like

Code:

USE `phpmyfaq`
/*[23:50:21][0 ms]*/ show create table `phpmyfaq`.`faq_faqadminlog`
/*[23:50:22][0 ms]*/ select count(*) from `phpmyfaq`.`faq_faqadminlog`
/*[23:50:23][0 ms]*/ show keys from `phpmyfaq`.`faq_faqadminlog`
/*[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
/*[23:50:28][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 200,100
/*[23:50:30][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 300,100
/*[23:50:31][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 400,100
/*[23:50:33][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 500,100
/*[23:50:34][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 600,100
/*[23:50:36][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 700,100

Why must only first SELECT be ORDERed BY id (PK)??

I think there is a risk that that you miss some rows and get others twice.

If PK is not an autoincrement but anything else that most certainly will happen!

I also would like to comment on this CHUNCKed feature. I think it was a very bad idea from the start. Remove it or make it configurable! With EXPORT it does not make sense at all. With import it could make some sense sometimes. But basically I would like to get rid of it for myself! It takes the controls out of my hands. BTW: Is this the reason for the slow export? I believe it is!

You are AGAIN trying to hard-code what only users can know: their server settings and the size of each row. With some BLOBs even 100 rows per CHUNK may be far too much. And you cannot know WHAT THEY WANT so you cannot code what other people want! And basically a user might want to import to ANOTHER server so the BULK size of max-allowed-packet (as of now) in no good idea either.

You have been wasting your time with this, and should instead have implemented the configurable BULK size. It is just a constant to be read from configuration.

Most professional programs that I know of have 10 times as many settings as SQLyog! You shall not decide for me. I want to decide for myself! Please understand that NOW! <_< Pleas also read: http://www.webyog.com/forums/index.php?showtopic=1967

I edited quite a lot!

BTW: 2 questions:

1)

What happens if job is aborted (by user or as a result of a HTTP-error) after issuing a LOCK TABLES … READ and before UNLOCK TABLES? Will that have effect on the database for other users? Could be the reason why MySQL invented the LOCK_TABLE privilege.

2)

Is it safe to have more HTTP-connections using the same tunnelling file? I had quite a lot of HTTP-errors when I tried!

EDIT: now I studied how it works with BULK enabled. The CHUNK overrides the BULK! Total confusion and mismatch in my humble opinion!

[Ritesh]

peterlaursen wrote on Mar 4 2006, 11:23 PM:

With the new CHUNKED feature of SQLyog 5.1 HISTORY shows queries like

Code:

USE `phpmyfaq`
/*[23:50:21][0 ms]*/ show create table `phpmyfaq`.`faq_faqadminlog`
/*[23:50:22][0 ms]*/ select count(*) from `phpmyfaq`.`faq_faqadminlog`
/*[23:50:23][0 ms]*/ show keys from `phpmyfaq`.`faq_faqadminlog`
/*[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
/*[23:50:28][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 200,100
/*[23:50:30][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 300,100
/*[23:50:31][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 400,100
/*[23:50:33][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 500,100
/*[23:50:34][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 600,100
/*[23:50:36][0 ms]*/ select * from `phpmyfaq`.`faq_faqadminlog`limit 700,100

Why must only first SELECT be ORDERed BY id (PK)??

We do an ORDER by only on PK columns. This insures that we dont get the same row two times in the dump. limit 100,100 is not guaranteed to return you the same row everytime when done like select * from table limit 100,100.

Quote:

I think there is a risk that that you miss some rows and get others twice.

If PK is not an autoincrement but anything else that most certainly will happen!

Not with the order by clause.

Quote:

I also would like to comment on this CHUNCKed feature.  I think it was a very bad idea from the start.  Remove it or make it configurable! With EXPORT it does not make sense at all.  With import it could make some sense sometimes.

It indeed makes sense in Export. Chunk based export is only done in HTTP Tunneling and not in direct connection. Since HTTP Tunneling is stateless, we need to get the whole table data in one shot. This creates problem in shared environment where there is a limit on how much memory PHP should use. Loading a big table completely in the PHP environment will certainly result in error. Thus we get data in chunks of 1000 and write the dump.

Quote:

But basically I would like to get rid of it for myself!  It takes the controls out of my hands.  BTW: Is this the reason for the slow export? I believe it is!

As of now it looks like the reason of the slow export. I will work on it tomorrow.

Quote:

You are AGAIN trying to hard-code what only users can know: their server settings and the size of each row.  With some BLOBs even 100 rows per CHUNK may be far too much.  And you cannot know WHAT THEY WANT so you cannot code what other people want!  And basically a user might want to import to ANOTHER server so the BULK size of max-allowed-packet (as of now) in no good idea either.

We already have plans to make it configurable before v5.1 FINAL.

Quote:

You have been wasting your time with this, and should instead have implemented the configurable BULK size.  It is just a constant to be read from configuration.

Most professional programs that I know of have 10 times as many settings as SQLyog!  You shall not decide for me.  I want to decide for myself!  Please understand that NOW!  <_<

Agreed but we are slowly and slowly moving every option to Tools -> Preferences to make it 100% configurable.

Quote:

1)

What happens if job is aborted (by user or as a result of a HTTP-error) after issuing a LOCK TABLES … READ and before UNLOCK TABLES? Will that have effect on the database for other users?  Could be the reason why MySQL invented the LOCK_TABLE privilege.

The LOCK TABLE will automatically go off when the connection is dropped.

Quote:

2)

Is it safe to have more HTTP-connections using the same tunnelling file?  I had quite a lot of HTTP-errors when I tried!

EDIT: now I studied how it works with BULK enabled.  The CHUNK overrides the BULK!  Total confusion and mismatch in my humble opinion!

[post=”9014″]<{POST_SNAPBACK}>[/post]

This is a BUG. I will ask my engg. to work on it.

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

@ritesh

I think you might have missed reading my last post post here because of Forums Software upgrade.

So I just put this reminder …

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 must 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 getting CHUNKS.

[Ritesh]

peterlaursen wrote on Mar 5 2006, 07:27 PM:

@ritesh

I think you might have missed reading my last post post here because of Forums Software upgrade.

So I just put this reminder …

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 must 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 getting CHUNKS.

I saw that and indeed looks like a bug. I will check upon it today.

[Author]

Posts