[jrossiter]

Bump.

It looks like 5.2 is going out without this. I'd like, again, to stress that this is a very important improvement for tunneling users.

[jrossiter]

PHP and Apache settings are fine. The servers are developed for and running commercial PHP apps, and I configured them myself. We have administrative pages that can easily run for 5 minutes or 2 hours with no interruptions.

There's no proxy between myself and the server in question. There is a NAT, but again, I've personally built and configured that and there are definitely no timeouts present.

This is the entire scenario:

My machine connects through permanently-attached-VPN via web to the administrative machine using SQLyogTunnel.php

The administrative machine connects to a third machine which houses the DB. The only reason I do this with the tunnel instead of a direct connect is due to security considerations that were taken in the network routing. External machines are not allowed to talk directly to the internal network. (Network routing tables, not firewall, so again there's no timeout potential here.)

I have done SSH tunnels, and from what I remember this was not a problem. I continue to use SSH tunnels to access my home server when I'm in the office. The reason I opted not to use SSH for the work machines is that I often want to connect multiple sessions, and to do that with tunneling requires creating multiple profiles (which is kind of a pain). (Now, if SQLYog were smart enough to automatically use a random local port when creating its SSH tunnels, this would be really easy.)

The queries that have problems are always ones that actually take more than 30 seconds to execute (Generally speaking, DELETEs on large data sets) where no data is being returned at all.

That I know of, I've had no issues with situations where the data transfer takes more than 30 seconds, but it's hard to estimate that given my bandwidth to the server. It would have to be a very large resultset for that to occur in the first place.

[jrossiter]

As I suspected, no change.

It's not set_time_limit(), it's not apache aborting the connection…

[jrossiter]

peterlaursen wrote on Aug 11 2006, 05:22 AM:

I am not sure I understand this.

Are you sure that it is SQLyog that initiates this disconnect?

To me it looks like it must a settting at your ISP that does that?

First try to edit the tunnelling file and replace the mysql_connct() with mysql_pconnect() connection method.

http://webyog.com/faq/21_103_en.html (paragraph 3)

Definitely not my ISP – they're my company servers, and I admin them.

I'll try pconnect, though I doubt it will make any change. connect has no time limits associated with it.

The query itself does not stop running at 30 seconds – the SQLYog connection aborts with the 'failed to connect'. To me it seems like SQLYog doesn't even know that it connected in the first place.

[jrossiter]

Thanks about the menu – I swear I looked.

About the delay, it's not something that I have mentioned personally, but I have seen other people mention it.

It may be tied to not having tags built, but if I select a block of text and delete it, the UI pauses for 2-3 seconds before it responds while it tries to parse/autocomplete.

I see the same behavior if I am just keyboard-navigating the query window (arrow keys) when I move between lines or statements.

[jrossiter]

peterlaursen wrote on Mar 24 2006, 10:50 AM:

If I kill the thread I don't get this error!

Sure, if you kill a thread *that exists*.

Try killing one that doesn't exist.

As I stated, in the event of a server crash or other malfunction, connections are not terminated. SQLYog still thinks it's connected to the server, and when the server is functioning again, it sends a kill command for a processid that doesn't exist.

[jrossiter]

I'm aware of all of those things, however as I stated, a server crash doesn't explicitly kill connections.

I am running on MySQL 4 and 5, using both direct connections and SSH tunnelling.

The issue doesn't have to be reproducable to fix, however. Receiving an error message from the server stating that the thread doesn't exist should simply terminate the UI-lock.

[jrossiter]

peterlaursen wrote on Mar 22 2006, 10:32 PM:

Cool down!

I'd have to be hot to cool down. I can make a statement without being upset.

[jrossiter]

Specifically I was meaning 'hidden' by being listed on the Database(s) portion of the connection dialog. Hidden by the app, not by SQL permissions.

[jrossiter]

My issue isn't with the actual data handling or input (which is the only place that my OS character set and keyboard would come in), it's with the database structure syncronization itself.

Syncing the character set is as simple as adding “DEFAULT CHARSET=” to the end of the table definition, or “character set ” to column definitions.

Here's an example:

The MySQL default is latin1.

Code:

CREATE TABLE `testTable` (
`hewbrewCol` CHAR(10) character set hebrew,
`asciiCol` CHAR(10) character set ascii,
`cp1252Col` CHAR(10) character set cp1252
) DEFAULT CHARSET=utf8;

This is a completely legal table, and highly specified for very important reasons. The problem is that if I were to use Structure Sync to copy this table structure to a separate DB, it would lose all of that character set information. It would end up being:

Code:

CREATE TABLE `testTable` (
`hewbrewCol` CHAR(10),
`asciiCol` CHAR(10),
`cp1252Col` CHAR(10),
);

Which means that all of those columns, as well as the table itself, would be latin1 because that's the server's default. That's bad. That's not syncrhonization. If I inserted data into those columns expecting them to be the same as they were on the other server, I'd end up with potentially corrupted data.

Would you consider “rsync” (Remote Sync) a useful application if it took all of your Japanese text files and transferred them as ASCII instead of Binary, thereby destroying all of the data?

The dictionary definitions are all specific towards time, however they all have the same implication – things happening simultaneously, e.g. the same. If it isn't the same on both ends, it isn't synchronized.

[jrossiter]

UI:

1. Small issue, but it is a usability and UI-standards issue. It makes it difficult to do a “top down” visual comparison, which is how every language that I know of reads. (RTL or LTR, it's always top to bottom)

3. See below. The issue seems to only occur if the table structures are identical *except* for the FK definition. The FK entry for testDB2.testTable2 will be corrupted.

4. Using the example below, in testDB2.testTable1 try and select *only* the extra column. It's not possible. You can select the entire table, or nothing. You can't just check one box. It's not a serious issue by any means, and the UI has “always” acted like this, but my question is simply “why bother going to the effort of having checkboxes for the sub-items if you can't select them”.

SQL:

1. It's not serious, but it's unnecessary duplication.

2. I have a feeling this discussion could get long.

3. The sample below demonstrates this as well. Again, the issue only occurs when there are no column changes necessary. It's building the alter command because of the FK, but because there are no column changes, it doesn't write any further data or end the statement with a ;.

Code:

CREATE DATABASE testDB1;
USE testDB1;
CREATE TABLE testTable1 (
`primaryColumn` CHAR(32) NOT NULL,
PRIMARY KEY (`primaryColumn`)
) ENGINE=InnoDB;
CREATE TABLE testTable2 (
`fkColumn` CHAR(32) NOT NULL,
PRIMARY KEY (`fkColumn`),
FOREIGN KEY `fk_test` (`fkColumn`)
REFERENCES `testTable1` (`primaryColumn`)
ON DELETE CASCADE
ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE DATABASE testDB2;
USE testDB2;
CREATE TABLE testTable1 (
`primaryColumn` CHAR(32) NOT NULL,
`secondColumn` CHAR(32) NOT NULL,
PRIMARY KEY (`primaryColumn`)
) ENGINE=InnoDB;
CREATE TABLE testTable2 (
`fkColumn` CHAR(32) NOT NULL,
PRIMARY KEY (`fkColumn`)
) TYPE=InnoDB;

[jrossiter]

peterlaursen wrote on Mar 22 2006, 10:52 PM:

1) This is unclear to me. I would expect “on the target server they ended up being latin1..”. A typo? :huh:

Yes, typo.

peterlaursen wrote on Mar 22 2006, 10:52 PM:

I think most people would not bring the charset from source to target when syncing between two databases having different charsets. However that could be an option. Or a warning/a message could be printed.

I disagree. If you are synchronizing the databases that means they should end up identical. That includes character sets. Also, it's not really a matter of the database character set, it's the MySQL server default. If one server defaults to latin1 and the other defaults to utf8 that doesn't mean that the tables should differ. For instance, if someone were in the same situation I was in during this situation:

Two databases were created on different servers using the same SQL, thus ending up with the same character sets.

One of the databases has new tables added to it.

I use Structure Sync to copy those tables to the second database.

Now I create a new foreign key on the first system and everything works great.

I create the foreign key on the second system and it fails obtusely and takes me quite a while to figure out.

The reason? Because when SQLYog created the new tables on the second DB, it failed to make them identical. That means that when I went to create the foreign key the column collation didn't match between the new table and one of the old tables.

In a situation like this Structure Sync is actually a hinderance because it didn't actually synchronize them. They had the same columns, but they were not structured the same way. If I had simply done the 'SHOW CREATE TABLE' and copied the SQL by hand, I wouldn't have had any implementation problems and saved time in the long run.

I would also argue that character set can be very important in other situations. What if, for instance, I had one table that was used for storing mutlilanguage data. I set the collation in one column to be latin1, another to be geostd8, yet another to be hebrew. Those columns were set with those character sets for a real reason. The rest table itself could be utf8, and the server's default could be latin1, but the character set information is not something that can be trivially ignored.

peterlaursen wrote on Mar 22 2006, 10:52 PM:

But it is always possible to edit the CREATE and ALTER statements.

Sure… if you know that it's something you need to watch for. I'm fairly experienced with SQL and it took me a couple hours to figure out because I'd never seen the problem before, and SQL sure as hell wasn't giving me any worthwhile hints.

peterlaursen wrote on Mar 22 2006, 10:52 PM:

2) I don't understand!? It generates a quite normal CREATE SQL statement herre ?

It's an effort and accuracy issue. If SQLYog simply returned the results from 'SHOW CREATE TABLE', it wouldn't have to go to the effort of rebuilding the SQL, and also things like character sets wouldn't be lost, because 'SHOW CREATE TABLE' automatically includes those creation flags.

[jrossiter]

I have to say that I'm disappointed to see utf8 missing since that's quickly becoming the universal default.

[jrossiter]

peterlaursen wrote on Feb 20 2006, 04:25 AM:

aahhh .. MAJOR ??? excuse me  major ???  😀

It is just a silly bug!

When it comes to using structure or data sync on DBs where this exists, it's a major issue. It's a complete failure of the feature. In my world as a former QA engineer, that's known as “blocking”. It isn't as if there were a typo in the dialog or something – the feature fails and in bad cases who knows what it has the possibility of doing?

What happens if one DB has “testTable” and the other has “testtable” and “testTable” – does it sync into the wrong location and erase data? I haven't bothered to test those scenarios explicitly, but given the nature of the bug and its behavior it would be a valid assumption.

[Author]

Posts