[peterlaursen]

I have some comments

– a knob or right click save browse setting from each table: you can save the width from the colom and the sorting

>>> A request for saving column widths has been requested before. Could be implemented in SQLite. Sorting (and filtering) can be saved as a 'Favorite' SQL-scripts

– What i also miss is a knop copy of a whole row and paste the row with a new id

>>>> hmmm. I this it is a pretty special request for a database client.

– ctrl-c doesn't work on numeric or text fields only if you double click.

>>> First click selects the CELL itself, second click selects the CONTENT of the cell. Selecting of CELL is used for SET NULL, SET EMPTY and SET DEFAULT. I do not favour any change here!

– And why are the text field the width always so big. Can you measure the chars and end the colom with that.

>>>> check tools … preferences … Truncate Column Headers ..

– Is it possible to turn of the popup screen from the text field? so i can type text right into field

>>>> You are talking about the BLOB viewer. You should use CHAR/VARCHAR types and not TEXTs whenever possible. It is more efficient. And it works as you want. BTW: Have you been using Navicat before? They never understood what a MySQL TEXT is …

– Alternating color and a select color in the grid

Well, this information is not DATABASE INFORMATION! SQLyog is a DATABASE CLIENT – not a spreadsheet program! SQLyog shows what is inside MySQL, it does not store data of its own and should not! So that information will be lost when you close the program! Still want it then?

BTW .. In case you do not know –

This is the official 'plans for the future' … for – my best guess – the next 9-12 months to come.

http://webyog.com/faq/33_20_en.html

But don't be frightened! Your opinion is of course always welcome and let us see what others have to say!

[peterlaursen]

No Ritesh!

Check my 'test case'. There is no “on update”. (There was not in upeters' example either).

4.0

==

create database if not exists `test`;

USE `test`;

/*Table structure for table `t1` */

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (

`id` bigint(20) NOT NULL auto_increment,

`t` varchar(20) default NULL,

`ts` timestamp(14) NOT NULL,

PRIMARY KEY (`id`)

) TYPE=MyISAM;

/*Data for the table `t1` */

insert into `t1` (`id`,`t`,`ts`) values (1,'a',20060101010101);

4.1

==

SET NAMES utf8;

SET SQL_MODE='';

create database if not exists `test`;

USE `test`;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

/*Table structure for table `t1` */

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (

`id` bigint(20) NOT NULL auto_increment,

`t` varchar(20) default NULL,

`ts` timestamp NULL default NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `t1` */

insert into `t1` (`id`,`t`,`ts`) values (1,'b','2006-01-01 01:01:01');

SET SQL_MODE=@OLD_SQL_MODE;

===================================

4.0 is 'NOT NULL' 4.1 is 'NULL default NULL'

but there is no NULL in the data!

I wonder if the 20060101010101 (number) and '2006-01-01 01:01:01' (formatted string) fail to compare?

That could be why it deletes in the first run.

In the second run target is empty and nothing can fail to compare, as there is NOTHING to compare !???

[peterlaursen]

Quote:

In RESULT TAB, duplicate copy of the data is made and kept in the memory from the beginning itself.

I think you mean “In DATA tab …..”

[peterlaursen]

I do not understand! Could you explain more in detail?

Do you mean that SQLyog crashes when typing ')'. If so I cannot reproduce!

Please give a VERY DETAILED description or (maybe better) paste in a screenshot of the program window before it happens. As attached …

Also ALWAYS tell the program version when reporting a bug/problem/issue!

[peterlaursen]

Actually I have a correction …

The datafile itself (.MYD) is only around 300 MB. The Index file for the table (.MYI) is around 700 MB.

But loading all rows into DATA tab exceeds memory.

A “select * from that_table” uses about 850 MB of memory as long as 'Read-only” is displayed in the drop-down. And now the 'slider' of the RESULT tab is very unresponsive. But no crash! But when I select the table here memory starts increasing from the 850 to around 1100 MB. And still the 'slider' is very inresponsive. But still no crash.

Also I think that a 'SELECT * ….' into RESULT tab is faster than 'show all' from DATA tab.

[peterlaursen]

I just tested with 5.02 and 5.12 myself.

On this PC I have 512 MB physical RAM and a max. swapfile setting of 1.8 GB. That is 2.3 GB total available memory. I tried to load all rows of a 4 GB table. Both 5.02 and 5.12 also crashed silently when memory ran full.

Now it it happens with your installation BEFORE memory is full, that is another situation. Just watch 'Virtual Memory' in Task Manager .. (you may need to configure Task Manager to show the Virtual memory -column from 'settings')

So this seems to have been the situation with all (at least 5.x) versions. You are the first that complain (I do no recall any other reports here?). Except for me who did too Monday night (I think).

[peterlaursen]

shit … you found out! 🙁

We did too ourselves a few days ago. It simply happens when system runs out of memory (physical memory + swapfile). We are working on it.

You can verify yourself by checking taskmanager.

Ideally it should display a message like

Quote:

Not enough memory to show all rows.

This message occurred after loading x# of rows.

..or even better it should dynamically load 'portions of' very big tables as the scrollbar is moved. But that is not so easy to implement in this environment, I have been told.

Now: can you tell a version that did not do this?

What happened then when memory 'ran full' ?

[peterlaursen]

a note on DATETIME vs. TIMESTAMP types.

The trick with 'setting NULL' only works with TIMESTAMPs (and only the first of a table) and not DATETIMEs.

However DATETIMES do accept constants as defaults.

Another difference is that DATETIMEs go 'longer back in time' than TIMESTAMPs do. DATETIMES basically support the time of the Gregorian Calendar. Timestamps start (as far as I remember) around 1970 … But don't 'hang me' on that .. check the docs yourself!

In MySQL 3.23 and 4.0 a TIMESTAMP can have different lenghts. TIMESTAMP(14) is default and is a YYYYMMDDHHMMSS -format. It is simply stored as a 14 character string like that. Just as a DATETIME is.

[peterlaursen]

3.23 does not support TIMESTAMP-defaults like CURRENT_TIMESTAMP (or the synonyms NOW() or localtimestamp). You cannot even use constants as defaults like '20060615210000'.

Also a functionality like 4.1 and 5.. '… on update CURRENT_TIMESTAMP' is not possible.

With 3.23 (and 4.0) your application(s) will have to handle that. An application can INSERT or UPDATE a TIMESTAMP with NOW() – the server itself has no functionality to do so! Not as 'default' and not as 'on update ..' .

Another (and common) way to set CURRENT_TIME with MySQL before 4.1 is to define the TIMESTAMP as NOT NULL and explicitly set it NULL from the application(s). That works with THE FIRST TIMESTAMP column of a table (when declared NOT NULL).

MySQL documentation at:

http://dev.mysql.com/doc/refman/4.1/en/tim…mp-pre-4-1.html (3.23 and 4.0)

http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html (4.1 – 5.x is basically the same but not quite!)

So basically you got yourself a nice coding job with doing that from your application(s) 😀

… there is SOME reason to upgrade software from time to time!

As far as SQLyog 5.1.x is concerned it supports the 4.1++ 'default' and 'on update' CURRENT_TIMESTAMP 'automatically' (that is it does not 'override' the server). With MySQL 3.23 and 4.0 you can right-click a cell and 'set NULL'. That is the fastest way I guess.

[peterlaursen]

@ritesh – BTW:

As far as remember it was the same symptoms we had (deletion .. recreation) before we fixed the issue with the INTEGER type being too short to hold changes in the SECONDS-range. Wasn't it?

[peterlaursen]

@vygi

Any idea on how to get further from here?

[peterlaursen]

The simple test case comes here:

Attached a small MySQL 4.0 and a similar 4.1 example.

Structure sync:

==========

It is very co0mplicated actually due to the different meaning of NULL/NOT NULL with the different versions!

Data sync:

=======

verified!

running DATA sync to times yields:

SQLyog Job Agent Version 5.13

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Wed Jun 14 10:28:24 2006

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`t1` 1 1 0 0 1

Total time taken – 0 sec(s)

SQLyog Job Agent Version 5.13

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Wed Jun 14 10:29:18 2006

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`t1` 1 0 1 0 0

Total time taken – 0 sec(s)

[peterlaursen]

I am sorry .. maybe it was too late yesterday! But I was confused by you talking about SJA and providing an output from the structure sync tool.

So I think we have two problems

1) DATA SYNC deletes and recreates data every second time (as the log file tells)

2) STRUCTURE SYNC finds differences. After running the sync script STRUCTURE SYNC keep finding the same diferences forever …

agreed?

about 1)

Please read this about DATA SYNC: http://webyog.com/faq/11_68_en.html

Note that data sync was not originally designed to work across versions. The 'workaround' described in the FAQ will not work with TIMESTAMPS. But of course it should not do like this – first deleting and next recreating data. It should instead tell that the databases cannot be sync'ed, if it cannot. (however converting to strings .. syncing .. converting to timestamp again should work)

about 2)

Structure sync is designed to work across versions.

I think MySQL 4.1 only operates TIMESTAMPS in YYYYMMDDHHMMSS -format. That is the same as a TIMESTAMP(14) of MySQL 4.0. The tool should see that. Specifying a length of a TIMESTAMP with 4.1 has no effect. It is simply ignored. So it does no harm. But confusing that the tools keeps telling that structures are not in sync, when they are!

Ritesh/Manoj/Sarat .. I am uncertain who does what from here .. ??

I think I'll create a simpler test case?

[peterlaursen]

OK .. thanks

1) pretty stange that the .zip extension gets lost when downloading your files. But after renaming manually I can unzip and read the files.

2) sja.log does not tell anything about the STRUCTURE SYNC tool! I still do not understand what that file should tell? I see that all rows get INSERTED on target. Was that the point?

3) But there is an issue with sync'ing of TIMESTAMPs form 4.0. to 4.1. On MySQL it is a TIMESTAMP(14) and on MySQL 4.1 it is a TIMESTAMP only. It is undoubtly a server issue, but the SYNC tool should 'spot' this and see that there is nothing to sync.

… Now it is late here. We will look into it tomorrow!

[peterlaursen]

OK ..

1) Structure sync has nothing to do with the SJA. Structure sync is implemented in SQLyogEnt.exe itself and not sja.exe.

2) I do not understand the attachment. It looks like some binary format ??

3) I also do not think the platform matters. But could be TIMESTAMP implementations with MySQL 4.0 and 4.1 respectively

4 Please copy the 'create statements for the table' for the table on both databases. We will then easily be able to spot the issue. (be carefull to tell which statement belongs to which version)

5) In what direction are you sync'ing? 4.0 >> 4.1 or 4.1 >> 4.0?

I think that if the length of a TIMESTAMP then TIMESTAMP(14) is default. So when “but I keep having timestamp fields, not timestamp(14) fields” I am not sure that it really is a problem ?! A TIMESTAMP(14) is a YYYYMMDDHHMMSS timestamp just like a TIMESTAMP with-no-length-specification is (with MySQL 5.x the TIMESTAMP specification is different- but no issue here!). So what is the PROBLEM, except for differing MySQL terminilogy across versions ?? What does not work?

“forcing me to always have to run the script twice. The first time my records are erased, then at the following run they are put back again” .. Please do 4) and we will check this!

[Author]

Posts