[peterlaursen]

hmmm ….

The first issue in the above post I cannot reproduce right now ( 🙁 )

But the second is easily reproducable! I filed this bug report:

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

BTW: I think the easiest 'workaround' is to 'search and replace' in an editor. With MySQL >= 5.1.14 simply replace

Code:

/*!50114 IF EXISTS */

with

Code:

IF EXISTS

[peterlaursen]

1) ok .. this one is dead!

2) avoid programs with word processing capabilities (like Wordpad) when working with code!

Save the file .. open in Notepad (rightclick .. open with (Danish: højreklik .. åbn med)). I can only repeat that the extension does not change the content of the file. Do you doubleclick on the file? Then of course the associated program will open it. Try open from right-click menu or the program file menu.

Go to control panel … folder setting (Danish: kontrolpanel .. mappeindstillinger). What program is associated with .sql files on your system?

i am pretty sure taht this is definitely an issue with your system or the way you use it!

3)

This is nonsense: “In SQLyog the field errorTimestamp can have a NULL value .. On the SQL server the same field can´t have a NULL value”. There is nothing in SQLyog! SQLyog is a database client – it has no data of its own, everything is in the MySQL database!

What does “SHOW CREATE TABLE ;” return for the table? This is how you do: ask the server with SHOW statements! You seem to believe phpMyAdmin more than you believe SQLyog! Why? They are both clients only! phpMyAdmin is not more 'natural' for MySQL than SQLyog is! The only client that has a special 'status' is the 'mysql' command line client!

Always when you are in doubt: ask the server: “SHOW …” ! (or click on OBJECTS tab in SQLyog)

The 'quotes' from phpMyAdmin and SQlyog are not readable if you never saw the original from where it was copied. You may attach screenshots instead.

BTW: PLEASE … no more phpMyAdmin in Danish! 🙂 Translation is horrible!

And also consider: if by chance I was not Danish, no one would have been able to understand much meaning of your latest post!

[peterlaursen]

It may be necassary to delete the line with the DROP TRIGGER statement in the SQL and drop manually untill MySQL AB fixes this 'bug-complex'

[peterlaursen]

Our analysis tells that all that needs to be fixed is allready fixed in 6.06 beta2. However 6.06 RC will be released today .. but with no change in this respect.

The confusing thing was that with recent MySQL 5.1.x (tested with 5.1.14, 5.1.18 and 5.1.20) only full syntax like

Code:

drop trigger database_name.trigger_name;

works. Even

Code:

use database_name;
drop trigger_name;

.. does not work. This is a server bug we think and this we will report to bugs.mysql.com. Like this it is simply not possible to import a trigger to another database than where it was created!

There seems to be more server bugs too, introduced when MySQL introduced support for DROP TRIGGER IF EXISTS in 5.1.14.

For instance nested conditional comments like

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `zxcv` */;

does not work. On 5.0.45 it does!

[peterlaursen]

If you sync first time to an empty database, there is no real sync going on – only a copy process. Syncing to empty target and target with data is not comparable.

To me it simply looks as if connection was lost 2nd time you sync'ed! After that “SHOW FULL FIELDS FROM …” retrives nothing for every table that follows and the sync for every table will then have to be skipped.

[peterlaursen]

We will release 6.1 beta1 including a 'Visual Schema Designer' in (hopefully) 10 days.

We will start focussing on TABLES, next VIEWS.

Visualisation of SPs, Functions, Triggers, Events etc. is problematic, because there is no 'rule' for the structure of those! But Ideas is always welcome!

[peterlaursen]

currently you can export a structure only dump and search with an ordinary editor.

[peterlaursen]

There is no 'columns selection' for export, only 'rows selection'.

The best option now is ,I think, to open a new query tab, copy the SELECT statement from HISTORY, paste it in the new tab, edit the columns list and execute.

We also had a report from a user who did like this:

select text-mode for results, copy the complete result into an editor that supports 'area selection'(he used PSPad).

We have added a feature similar to this to our TODO, but priority is low!

[peterlaursen]

And just in case I did not make it clear enough:

The error occurs when importing. Not when exporting!

[peterlaursen]

Sorry for the confuison!

We find a bug it the parser used when executing external files.

(this parser 'isolates' statements from the file)

Please:

1) upgrade to 6.06 latest beta.

2) copy the file into the editor.

.. that should work. but we will fix the bug very soon too as well!

It is not pseudo-comments that is a problem. They work fine!

You should notice however that 'nested comments' are only supported from version 6.06.

[peterlaursen]

It also fails on 5.0.45

Removing pseudocomments does not help.

BTW (but not the issue here):

I think it should be

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `tablename1`.`qwe` /* $$

and not only

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `qwe` /* $$

edit:

NO .. it is “DROP .. databasename.triggername”

[peterlaursen]

test script/example:

/*

SQLyog Enterprise – MySQL GUI v6.1 Test Build

Host – 5.1.20-beta-community-nt-debug : Database – asdf

*********************************************************************

Server version : 5.1.20-beta-community-nt-debug

*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

create database if not exists `asdf`;

USE `asdf`;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*Table structure for table `tablename1` */

DROP TABLE IF EXISTS `tablename1`;

CREATE TABLE `tablename1` (

`ab` bigint(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `tablename1` */

/*Trigger structure for table `tablename1` */

DELIMITER $$

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `qwe` /*$$

/*!50003 CREATE TRIGGER `qwe` BEFORE DELETE ON `tablename1` FOR EACH ROW BEGIN

— nothing;

END */$$

DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

[peterlaursen]

This is reproduced on 5.1.20

Can you tell positively that rewoving the pseudo-comments fixes this or is it another issue?

We will need to research. But it may very well be a server bug!

[peterlaursen]

1)

is a known bug that is fixed in 6.06 beta. Please refer to the release notes! (news section of this Forum)

2)

your file is readable for me, except that a lot a SPACES have been inserted. See attachment. What text editor or other program do you paste into before saving? I think it is an issue with that program! Please try Notepad ('Notesblok' in Danish). But of course there are hundreds of text editors where it works. I cannot reproduce the additional SPACES. I also notice that your file is ANSI-encoded. If a file is saved directly from structure sync GUI it is saved as windows 'unicode' (utf16) on my system. That is why i am almost sure that you do not use the 'save' button, or you may have opened and saved the file in some prgram. Maybe you use a program that does not support (utf16) 'unicode'.

But that also surprises me. For portability I think we should save the file as utf8 as we do with all other files since version 6.0! But a 'decent' editor on Windows supports all three: ANSI, UTF8 and UTF16!

3)

please also try if 6.06 beta fixes this (an issue with TRIGGERS was fixed). If not best would be if you attached a structure-only dump (no data required for this) of the database from BOTH servers, zip and attach here (or you can create a ticket if you do not want to expose it in public).

Why should I rename the file to .sql? That does not change the content of the file! If the .txt or .sql extension makes any difference it is an (association) issue with your system only!

BTW: please always tell the MySQL server versions involved! Are you syncing between different MySQL versions? And do you use features supported by one of the servers only?

[peterlaursen]

They are not commented out!

This

/*!50003 … */

is a MySQL extension to standard SQL comment syntax. ” ! ” means “NOT” in C-type computer languages.

So it means 'IF NOT MySQL version is 5.0.3 or higher then treat as a comment, ELSE (if MySQL version is 5.0.3 or higher) execute'.

For your information the 'mysqldump' program does exactly the same! This syntax is implemented in the MySQL server itself!

What is the server version you import to? Before 5.03 MySQL does not support triggers. We use the 'version dependent conditional comment' like this to avoid that the job should abort as a result of the error occurring on lower server versions.

[Author]

Posts