[smine]

i am sorry, this is my mistake. i checked 8.22 and 8.3 beta 1 and there is consistent behavior

• start a long-running query –> Object Browser text is normal[*]query is running, shift focus to a different window –> Object Browser text dims (“greys out”)[*]query is running, return focus to SQLyog –> Object Browser text remains dimmed[*]query ends –> the Object Browser text is restored to its normal display (even if SQLyog does not have focus when the query ends)

i thought the dimming happened as soon as i began executing the query, but i see now that it happens only when a query is running and SQLyog loses focus.

i find the dimmed text is a good visual indicator of when the query ends and i can resume working in SQLyog. i like the dimming, can we make it a feature? (just kidding.)

[smine]

nithin wrote on Nov 11 2009, 06:20 AM:

There was an issue in calculating the size of INSERT. … It has been fixed now in this special build. … Could you please verify the fix in your environment and let's know the status?

it works! thank you!

i am curious how you determine how many value sets can fit on a bulk insert statement. using actual data size of export, the lines appear to be able to fit many more sets on each statement. but using max possible lengths of data fields, it seems like there are too many sets on each statement.

[smine]

nithin wrote on Nov 9 2009, 11:41 PM:

You meant to say sql dump with Single Insert is imported fine for 25 mins and you stopped? But we cant tell the 'failure point' is already passed or not, it may fail in any point.

in 25 mins it imported more rows of Single Insert than sets of values with Bulk Insert. it always fails on first Bulk Insert, but with Single Insert it inserted data past all the values in the first Bulk Insert.

nithin wrote on Nov 9 2009, 11:41 PM:

Please clarify this statement of your 1st post

this size you mentioned is size of single INSERT statement(for single row) or BULK INSERT statement? And How did you get this?

the size is for Bulk Insert (e.g. insert into (column_list) values (value_list_1),(value_list_2),(value_list_3),…). i opened the dump file in a text editor and moved the cursor to the end of the insert statement. the text editor told me the line length.

nithin wrote on Nov 9 2009, 11:41 PM:

And please do one more thing..We have an option to specify the BULK INSERT size in preference itself, give 500(K) in preference and do EXPORT(with BULK INSERT option checked) and Import the dump.

this was successful!

the old setting was “Get server default”. i changed to 500 and did Export with Bulk Insert, then Import. it took about 6 mins for about 350,000 rows (and 9 indexes) in 211 insert statements. the longest insert statement line length in the dump file is about 809000 bytes. total size of dump file about 156700KB.

i didn't know about all of the custom settings. very useful!

[smine]

good point about problems with DOUBLE. some of our values are monetary, others are conversion factors and ratio values which may benefit from greater precision.

these databases are rather old and pre-date MySQL 5.0.3, so even using DECIMAL back then still would have produced floating point errors.

i will pass along to our designers/architects the information you provided. it's unlikely we will make changes in existing tables, but it may be helpful for new tables.

thanks!

[smine]

nithin wrote on Nov 8 2009, 10:18 PM:

If you have checked the option “CREATE BULK INSERT STATEMENT” in EXPORT dialog, Can you please export that table by unchecking that Bulk-Insert option?

And please try to import this.

yes, this worked. (well, i think it worked. i don't want to wait 3 hours for the import to finish. it has been running for about 25 minutes with no errors and has passed the earlier point of failure.)

i don't think i deliberately turned on BULK INSERT, i think it was the default, but maybe i am wrong.

[smine]

peterlaursen wrote on Jul 29 2009, 12:54 AM:

line wrap setting is available from tools .. preferences .. 'general' tab .. check 'word wrap ..'

i'm embarrassed i did not find this setting. thank you!

[smine]

looks good. thanks!

[smine]

looks good. thanks!

[smine]

peterlaursen wrote on May 10 2009, 04:03 AM:

One more detail: If you have a lot of databases on the server that you will not need to access from SQLyog, you could define the connection using specific databases only. The TAGS folders are generated as *per connection* setting.

this is a very good tip! i didn't even know i could specify multiple databases.

[smine]

peterlaursen wrote on May 10 2009, 01:28 AM:

Is the server accessed over (W)LAN or Internet?

Are you using SSH- or HTTP-tunnelling?

Is your User Profile stored on a domain server (in that case you should notice that there is an option to specify a local folder for storing TAGS files).

1) the SQLyog connection dialog is using only the MySQL tab, so i don't think we are using SSH or HTTP

2) the SQLyog connection dialog has a MySQL host address like “xxx.xxx.[my_domain].com” so i guess that is Internet and not LAN

3) i'm not sure what User Profile is. if it is a SQLyog-specific profile, then i believe everything is local to my PC. currently, the Tags folder is located at C:Documents and Settings[user_name]Application DataSQLyog

[smine]

Things are working well.

I rebuilt the Tags database for my main server connection. It took 167 minutes and the final size is 26636 KB.

It was interesting to watch the build process. When I typed a database name followed by a period, autocomplete would not show the list of table names if the build had not reached that database yet (TAB just inserted a tab and CTRL-Space was ignored). So I could watch the progress of the build by typing the names of different databases to see how far it got.

While the build process is going on, if there are no tags for the database I am working in, then autocomplete does not do the 10-15 second pause after typing a few characters. That is good!

This is much better than current behavior in 8.05! Of course, it would be nice if it did not take almost 3 hours to build the tags database. 🙂 I am going to try to remove some obsolete databases from our server, which might help a bit. I still notice a bit of a lag when autocomplete is looking up its tags, but I suppose it cannot be instantaneous.

Thanks!

P.S. I have a couple of other questions about autocomplete but i will open other threads for them.

[smine]

MySQL 5.0.45, direct-connect, local servers. there are only a very few stored procedures, we do not use that capability much.

the Tags database finally finished building about 162 mins after it started. its final size is 27245 KB.

now that the build is finished, autocomplete is working. it is very responsive, though there is still a noticeable pause after typing a few chars of a word, including keywords like 'fr' in 'from' and the first 2-5 chars of table & column names. it is also noticeable when backspacing into a word, e.g 'from' to 'fro' then 'fr'. the performance is comparable to what i saw on 8.05 after the tags database finished building on 8.05, so i guess it is the build process that is the problem.

we have 146 databases on this server. 13 of them each have 312 tables, 13 of them each have 142 tables. the number of columns ranges from 3 up to 50. table and column names tend to be long with many common initial characters, e.g. this_is_our_long_table_name_1, this_is_our_long_table_name_2 (those are not real names, i made them up).

i think i definitely have to turn off “Always rebuild tags database on startup”!

i appreciate your attention to this issue, you guys work so hard!

[smine]

i renamed Tags directory to Tags_old and started SQLyog. the Tags directory was rebuilt. some observations:

1) it appears that there is a set of files for each connection? i connected to 3 servers and have 3 sets of files. it appears that all 3 are being rebuilt simultaneously, not just the connection i am currently using?

2) during the rebuild, autocomplete doesn't work (i guess you turned it off during rebuild?). but typing is very fast without the 10-15 sec delay as in 8.05!

3) the rebuild is very slow. i again renamed Tags directory to let SQLyog rebuild it. i connected to only one server. so far, it has taken about 5 mins to reach 1 MB and it is still growing (before this the .db was 25 M:cool:. … now it has been 37 mins and the .db file is 6964 KB (and autocomplete is still not working). … now it has been 55 mins and the .db file is 10,001 KB. now it has been 75 mins and the .db file is 13760 KB and still growing…

are you reading information_schema to populate tags? i have noticed when i query information_schema it takes a long time.

[smine]

hi. i'm having some problems.

i installed the beta to a new directory but did not uninstall 8.05 release. the performance was much better! but then i switched servers and autocomplete stopped working (TAB just inserts whitespace).

i thought maybe it was a conflict between 8.1n1 and 8.05 (because they both use c:documents and settings[user]Application DataSQLyog ?) so i uninstalled 8.1n1 then uninstalled 8.05 then re-installed only 8.1n1. autocomplete wasn't working. so i deleted all files in the Tags directory. i also clicked “Stop Rebuilding Tags”.

now autocomplete works on one server (and is very fast!!) but does not work on other servers! very strange.

perhaps i broke something?

UPDATE!!

i uninstalled and deleted everything and installed 8.05 and it worked as before. i then uninstalled 8.05 and installed 8.1n1 and it is now working. i think the problem is that i copied connections from an old .ini file to a new .ini file. that seems unlikely, but after this reinstall i created each connection manually. does each connection get its own set of TAG databases?

anyway, things are working so far and the performance of autocomplete in the beta is very much better! there is still sometimes a perceptible delay after typing a few chars, but it is very much better than 8.05.

[smine]

wow, you guys are fast! thanks!

[Author]

Posts