[peterlaursen]

How do I create the batchfiles?

>> in Notepad for example. Enter the two lines and save as myjob.bat (NOT myjob.bat.txt !). The .bat file can be executed from commandline o from windows scheduler

What is “truncate” and how do I use it?

>>in SQL to truncate a table could look like:

Code:

truncate table `dbname`.`tablename`

It deletes all rows DATA in the table but not the columns-definition

How do I insert my own SQL statements like the TIMESTAMP thing…?

>>in SQL:

Code:

alter table `dbname`.`tablename`, add column `ts` timestamp  NOT NULL default CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP  after `somefield`;

But check that your MySQL version supports this.

But unfortunately here is a known big with SQLyog since it writes this SQL when a row is updated:

Code:

update `dbname`.`tablename` set  `id`='2',  `ts`='CURRENT_TIMESTAMP' where `id`='2'

Thus the server is 'overruled'. CURRENT_TIMESTAMP is not the same as 'CURRENT_TIMESTAMP' . The first is a time value – the latter is a literal string!

SQLyog should only write

Code:

update `dbname`.`tablename` set  `id`='2',  `ts`= CURRENT_TIMESTAMP where `id`='2'

or simply omit the ts-field

Code:

update `dbname`.`tablename` set  `id`='2' where `id`='2'

I think fixing this is on the TODO of SQLyog 4.3. Until then you should not update data with a TIMESTAMP defined this way with SQLyog. Do you need the TIMESTAMP thing ?

[peterlaursen]

Well .. yes!

You then do a one-way sync with webhost as 'source' and localhost as 'target' with the option 'NEVER DELETE' set.

After that you truncate the database/tables at the webhost.

If you want it all done in one operation you create a for the sync and a for truncation of the database/tables. You can then create a .bat file like

Code:

sja syncjob.xml
sja notifyjob.xml

There is one nag here: If the webiste is open for user/visitor input data may be added to the database from the time of sync to the time of truncation. These data will be lost. So it would be more safe to put a TIMESTAMP-column in the table and instead of complete truncation only delete rows with a timestamp smaller the time-of-last midnight for example.

How to use the TIMESTAMP depends on the MySQL version. With a more recent version you can define it as

Code:

default NOT NULL on UPDATE CURRENT_TIMESTAMP

Now the server will automatically update the timestamp whenever there is any change. Check the MySQL docs for your MySQL version for more detailed description (look in … column types .. data and time variables)

And I repeat: don't forget to set 'NEVER DELETE' option! 😀

[peterlaursen]

EDIT: no need that I should repeat myself 😀

[peterlaursen]

And how many columns have we here ?

1) Arequipa Arequipa Cesar Acurio

2) Arequipa Arequipa Guillermo Viñas Luisa Balboa

[peterlaursen]

Food for thought:

http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

http://bugs.mysql.com/bug.php?id=11203

😮

[peterlaursen]

Here is the (little) information about what is new in MySQL 5.1

http://dev.mysql.com/doc/refman/5.1/en/roadmap.html

[peterlaursen]

Sorry Call .. did not see you.

Time for cowboys to go to bed! 😀

[peterlaursen]

That's impossible as of now!

The MySQL server can't operate Foreign Keys with MyISAM tables.

So SQLyog can't possible save such thing to the database.

However I believe I read at the mySQL webiste that MySQL version 5.1 will allow for Foreing Keys (of some sort). The storage engines for 5.1 are being totally rewritten and a lot of code is moved from the server binaries to the storage engine code. Undoubtedly SQLyog will support it when it becomes possible.

The first pre-alpha version of 5.1 in out in a source distribution for Linux/Unix only and I have not had a chance to look at it. But I don't think it is there yet. The main 'trick' with mySQL 5.1 is database partitions. The other details come later …

It typically takes about two years for a new 'main versions' toreach its final stage from the time of the first alpha (and we have only seen a pre-alpha!)

[peterlaursen]

Quote:

After the tables were created, I tried using the wizard “Copy database…” by right-clicking on that database, clicking on the item “Copy database…” and Windows said it crashed and requested me to send a report.

I think we understood that now! My point is that this is not a GENERAL problem with SQLyog. It could be a defect systems file on your system OR a rare issue with SQLyog that only has effect in very special situations (conflicts with drivers and certain running dll's etc. When we get to details not two systems are identical on this planet!). It is hard to tell. Maybe Ritesh has some idea?

Quote:

I just formated and fdisked my hard disk yesterday. My hard disk has expanded some bad sectors. Do you think that it it the cause that leads to Sqlyog crashs.

I don't believe it. I think it is software related. But you COULD try to uninstall SQLyog and reinstall. It is 99.9999999 % that it won't be written to the same sectors then. But if it is some read failure from the harddisk it could be MySQL or the database files that has somme error.

Did you run a chkdsk with the /r parameter (both checkboxes checked in the WinXP user interface for “control of disk”)). The very best is to boot on 'recovery console and run “checkdisk /r”. This is the one that checks for most, since no system files are locked when booting on recovery console.

A question: Can you open that database from SQLyog? and can you export (as a .sql-file for instance) or read the data from ODBC/JDBC .. etc. ???

[peterlaursen]

No Ritesh!

This is not a problem with the delimiter setting at all. The problem is that Excel does not allow for use of escape characters with CSV-export.

The CSV-file as it is now (with comma as delimiter as well as in the data) can't be imported correctly at all! No setting can help! You must find a delimiter that is not is use by the data and that Excel can use (the 'tab' would often work). But it is much simpler to open the .xls-file from Access and use ODBC!

[peterlaursen]

Yes … that is the problem!

The escape character is for solving that!

If you export to a CSV with SQLyog you can choose an escape character.

For instance if you have the string

Code:

India, China, Denmark

and choose , (comma) as the delimiter character you can choose for instance ^ as esacpe character. Then the CSV file would look like

Code:

row1data,row2data,India^, China^, Denmark

and SQLyog can handle that. The escape character means that the following charcter should be written

But unfortunalety you don't have the chance to use an escape character when exporting form Excel.

There is an option in my Excel 2000 to use ; (semicolon) as seperator when saving to a CSV. It would then look like

Code:

row1data;row2data;India, China, Denmark

and that will work if there are no ;'s in the data! You will need to find a delimiter that is not in use by the data if you shall avoid manual editing!

You can also export as a tab-seperated text-file, and rename the file to csv. The delimiter setting is then t (Platform-independent C-syntax for 'tab') with SQLyog.

If you still have the Excel-file I propose you experiment with the settings for export!

Personally I prefer quite another way of transferring data from Excel to MySQL and NOT to use CSV at all.

Open the Excel-file from MS-Access (if you have it) as a 'datalink' and either

1) export from Access to MySQL using ODBC (the MySQL ODBC-driver must be installed and configured with an instance pointing to the MySQL database where you want it)

or 2) Import into MySQL with the SQLyog Enterprise migration tool (this uses the MS ODBC-driver for Access)

[peterlaursen]

I have no problems with MySQL 5.0.15.

[peterlaursen]

hmmm…

Quite a lot of syntax errors in that … missing ;'s

It would be easier it you used SQLyog for the export.

How did you manage to put this together ?

And what are the select statements good for ?

I can't reproduce the error.

My humble opinion is that you should try to deactivate(deinstall all that yahoo software that you have. I never had some software form Yahoo that did not give me problems!

I am pretty serious with that. It is a problem with your system I believe (nobody else reported it) and not a SQLyog problem.

[peterlaursen]

Well it is the settings. The CSV-tool in SQLyog can be somewhat confusing because it has so many possibilities. And it does not escape automatically either.

Could you show us just how one row of data looks like?

And the 'settings' dialogue (the picture to the one that I attach here)

[peterlaursen]

1) We need the create statement for the tables!

The windows report is not worth much!

2) Do you have Views, triggers or Stored Procedures in the database ?

3) What is your SQLyog version ?

4) Do you connect to the MySQL server on prot 3307 or do you use port 3307 for SSH-tunnelling? Is the WinXP machine the client or server machine or both? What sort of connections are open when it happens ?

You really will have to EXPLAIN so that we can reproduce exactly the same situation!

and BTW: I have just copied a HUGE database with InnoDB tables using both connections on port 3307 and SSH port forwarding on port 3307.

[Author]

Posts