Upgraded From Mysql Community Edition 5.21 To 5.22a

[wchiquito]

Hi,

Upgraded From SQLyog Community Edition 5.21 to 5.22a and I have problems with the option “Backup Database As SQL Dump”.

it generates script with procedures, functions, triggers and other options different from the tables to commentary way.

example:

/*


SQLyog Community Edition- MySQL GUI v5.22a


Host - 5.0.33-community-max : Database - mybasededatos


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


Server version : 5.0.33-community-max


*/





/*!40101 SET NAMES utf8 */;





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





create database if not exists `mybasededatos`;





USE `mybasededatos`;





/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;


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








/*Table structure for table `A` */





DROP TABLE IF EXISTS `A`;





CREATE TABLE `A` (


`B` int(10) NOT NULL auto_increment


) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;





/*Trigger structure for table `A` */





DELIMITER $$





/*!50003 DROP TRIGGER `trgA` */$$





/*!50003 CREATE TRIGGER `trgA` BEFORE INSERT ON `A` FOR EACH ROW BEGIN IF (NEW.B = 1) THEN


SET NEW.B = 2;


END IF;


END */$$








DELIMITER ;








/* Procedure structure for procedure `UN` */





/*!50003 DROP PROCEDURE IF EXISTS  `UN` */;





DELIMITER $$





/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `UN`()


SQL SECURITY INVOKER


BEGIN UPDATE A SET B = 2;


END */$$


DELIMITER ;








/* Function  structure for function  `DATETIMEtoINT` */





/*!50003 DROP FUNCTION IF EXISTS `DATETIMEtoINT` */;


DELIMITER $$





/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION `DATETIMEtoINT`(Fecha DATETIME) RETURNS int(11)


SQL SECURITY INVOKER


BEGIN DECLARE FechaBase DATETIME;


SET FechaBase = CONVERT('1900-01-01', DATE);


RETURN DATEDIFF(Fecha, FechaBase);


END */$$


DELIMITER ;

thanks.

[adarsh]

“I have problems …” Please explain the problems! the script imports OK here if we!

1)

change this

CREATE TABLE `A` (

`B` int(10) NOT NULL auto_increment

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

to

1) change this

CREATE TABLE `A` (

`B` int(10) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

first thing you cannot create a table with autoincrement without key.

and

2) delete this line

/*!50003 DROP TRIGGER `trgA` */$$

The comments are not a comments really! /*!50003 DROP TRIGGER `trgA` */$$ for instance means that this will be executed on MySQL 5.0.3 and higher. Before that the line is skipped. Every backup program will have to do like this is backup/restore across MySQL versions shall be possible. Also all programs from MySQL themselves do. most of the MySQL server does not support this “Drop Trigger statement”.

[wchiquito]

Perfect! thanks.

I already understood:

SQLyog Backups are now 'backwards compatible' (they import on earlier MySQL versions due to the use of 'version-dependent conditional comments' (like /*!40101 Engine …*/)).

[peterlaursen]

However it is still a mystery to me how

Code:

CREATE TABLE `A` (
`B` int(10) NOT NULL auto_increment
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

(auto_increment and no key)

… comes into existence.  When exporting we do a “SHOW CREATE TABLE” statement only.

So this is what MySQL sends – unless you have edited the table?

[Author]

Posts