Login

Daniel · 12-17-2012, 02:22 AM

Hi,

Just tried to update a 3.3.10 installation to 3.40. Replaced all the files and created the ENABLE_INSTALL_TOOL file to start the installer.

Installer starts up, prompts for database update, but upon execution of the updates failures are coming up, see below:

PHP Code:
ALTER TABLE tblUsers ADD UNIQUE(`login`)
UPDATE tblVersion set major=3, minor=4, subminor=0
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ DROP PROCEDURE IF EXISTS DROPFK $$ CREATE PROCEDURE DROPFK ( IN pa' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END $$ DELIMITER' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$ ALTER TABLE tblACLs ENGINE=InnoDB' at line 1
Table 'docmgmt.tblUserPasswordRequest' doesn't exist
Incorrect table definition; there can be only one auto column and it must be defined as a key
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
Incorrect table definition; there can be only one auto column and it must be defined as a key
Can't create table 'docmgmt.tblDocumentContentAttributes' (errno: 150) 

After that it displays a link to go back to the installation. I can't run this a 2nd time because tblVersion has already been written.

Any idea what is wrong here?

Daniel

steinm · 12-17-2012, 01:33 PM

(12-17-2012, 02:22 AM)Daniel Wrote: Hi,

Just tried to update a 3.3.10 installation to 3.40. Replaced all the files and created the ENABLE_INSTALL_TOOL file to start the installer.

Installer starts up, prompts for database update, but upon execution of the updates failures are coming up, see below:

PHP Code:
ALTER TABLE tblUsers ADD UNIQUE(`login`) UPDATE tblVersion set major=3, minor=4, subminor=0 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ DROP PROCEDURE IF EXISTS DROPFK $$ CREATE PROCEDURE DROPFK ( IN pa' at line 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END $$ DELIMITER' at line 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$ ALTER TABLE tblACLs ENGINE=InnoDB' at line 1 Table 'docmgmt.tblUserPasswordRequest' doesn't exist Incorrect table definition; there can be only one auto column and it must be defined as a key PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist Incorrect table definition; there can be only one auto column and it must be defined as a key Can't create table 'docmgmt.tblDocumentContentAttributes' (errno: 150)

After that it displays a link to go back to the installation. I can't run this a 2nd time because tblVersion has already been written.

I wonder why tblUserPasswordRequest does not exists. That should have been creaeted in 3.3.x already.

Uwe

Daniel · (This post was last modified: 12-17-2012, 02:11 PM by Daniel.)

I checked the SQL update and ran the part to create tblUserPasswordRequest from the SQL editor and ran the update of tblVersion.

I wonder why it doesn't create a new entry with the new version, it just updates all the entries.

date major minor subminor
23.03.11 18:56 3 3 7
23.03.11 18:57 3 3 7
23.03.11 18:57 3 3 7
24.03.11 17:41 3 3 7
03.09.11 21:09 3 3 7

Daniel

Just done, but the problem is not disappearing, there is now another table missing.

PHP Code:
CREATE TABLE `tblUserPasswordHistory` ( `id` int(11) NOT NULL auto_increment, `userID` int(11) NOT NULL default '0', `pwd` varchar(50) default NULL, `date` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`), CONSTRAINT `tblUserPasswordHistory_user` FOREIGN KEY (`userID`) REFERENCES `tblUsers` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE tblUsers ADD COLUMN `pwdExpiration` datetime NOT NULL default '0000-00-00 00:00:00'
ALTER TABLE tblUsers ADD COLUMN `loginfailures` tinyint(4) NOT NULL default '0'
ALTER TABLE tblUsers ADD COLUMN `disabled` smallint(4) NOT NULL default '0'
ALTER TABLE tblUsers ADD UNIQUE(`login`)
UPDATE tblVersion set major=3, minor=4, subminor=0
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ DROP PROCEDURE IF EXISTS DROPFK $$ CREATE PROCEDURE DROPFK ( IN pa' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END $$ DELIMITER' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$ ALTER TABLE tblACLs ENGINE=InnoDB' at line 1
Incorrect table definition; there can be only one auto column and it must be defined as a key
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
PROCEDURE docmgmt.DROPFK does not exist
Incorrect table definition; there can be only one auto column and it must be defined as a key
Can't create table 'docmgmt.tblDocumentContentAttributes' (errno: 150) 

I wonder why there is always a syntax error. It also shows the same error when i paste this statement into PHPMyAdmin.

Daniel

steinm · 12-17-2012, 11:48 PM

(12-17-2012, 02:04 PM)Daniel Wrote: I checked the SQL update and ran the part to create tblUserPasswordRequest from the SQL editor and ran the update of tblVersion.

I wonder why it doesn't create a new entry with the new version, it just updates all the entries.

date major minor subminor
23.03.11 18:56 3 3 7
23.03.11 18:57 3 3 7
23.03.11 18:57 3 3 7
24.03.11 17:41 3 3 7
03.09.11 21:09 3 3 7

Daniel
Just done, but the problem is not disappearing, there is now another table missing.

PHP Code:
CREATE TABLE `tblUserPasswordHistory` ( `id` int(11) NOT NULL auto_increment, `userID` int(11) NOT NULL default '0', `pwd` varchar(50) default NULL, `date` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`), CONSTRAINT `tblUserPasswordHistory_user` FOREIGN KEY (`userID`) REFERENCES `tblUsers` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ALTER TABLE tblUsers ADD COLUMN `pwdExpiration` datetime NOT NULL default '0000-00-00 00:00:00' ALTER TABLE tblUsers ADD COLUMN `loginfailures` tinyint(4) NOT NULL default '0' ALTER TABLE tblUsers ADD COLUMN `disabled` smallint(4) NOT NULL default '0' ALTER TABLE tblUsers ADD UNIQUE(`login`) UPDATE tblVersion set major=3, minor=4, subminor=0 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ DROP PROCEDURE IF EXISTS DROPFK $$ CREATE PROCEDURE DROPFK ( IN pa' at line 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END $$ DELIMITER' at line 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$ ALTER TABLE tblACLs ENGINE=InnoDB' at line 1 Incorrect table definition; there can be only one auto column and it must be defined as a key PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist PROCEDURE docmgmt.DROPFK does not exist Incorrect table definition; there can be only one auto column and it must be defined as a key Can't create table 'docmgmt.tblDocumentContentAttributes' (errno: 150)

I wonder why there is always a syntax error. It also shows the same error when i paste this statement into PHPMyAdmin.

I just did a simple test again. First I created a fresh database and imported the file create-tables_innodb.sql from version 3.3.11 and then import the update sql statements from 3.4.0.

Can you try that?

Uwe

Daniel · (This post was last modified: 12-18-2012, 01:58 AM by Daniel.)

Hi Uwe,

No success here. Created a new installation with a new database and a new user. Ran the setup and created the database tables. I wondered why it said the database version is 3.3.0 when installing 3.3.11, i meant the database version should be 3.3.7.

After a login test i deleted all the files and copied the 3.4.0 source in there, created the ENABLE_INSTALL_TOOL file and went through the setup again. Paths where OK, but the SQL statement was again not fine.

btw: i used the built in installation for both installations, i'm going to purge the database now and try to run the SQL files.

PHP Code:
UPDATE tblVersion set major=3, minor=4, subminor=0
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ DROP PROCEDURE IF EXISTS DROPFK $$ CREATE PROCEDURE DROPFK ( IN pa' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END $$ DELIMITER' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$ ALTER TABLE tblACLs ENGINE=InnoDB' at line 1
PROCEDURE letonew.DROPFK does not exist
Can't create table 'letonew.#sql-1b3f_227' (errno: 121)
PROCEDURE letonew.DROPFK does not exist
Can't create table 'letonew.#sql-1b3f_227' (errno: 121)
PROCEDURE letonew.DROPFK does not exist
Can't create table 'letonew.#sql-1b3f_227' (errno: 121)
PROCEDURE letonew.DROPFK does not exist
PROCEDURE letonew.DROPFK does not exist
Can't create table 'letonew.#sql-1b3f_227' (errno: 121)
PROCEDURE letonew.DROPFK does not exist
Can't create table 'letonew.#sql-1b3f_227' (errno: 121) 

Daniel

Even if i run the SQL file first and try to update then i stuck at the error with the SQL syntax.

Hi Uwe,

My SQL client creates an error when running this query:

PHP Code:
DELIMITER $$

DROP PROCEDURE IF EXISTS DROPFK $$
CREATE PROCEDURE DROPFK (
IN parm_table_name VARCHAR(100),
IN parm_key_name VARCHAR(100)
)
BEGIN
IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN
ALTER TABLE parm_table_name DROP FOREIGN KEY parm_key_name;
END IF;
END $$
DELIMITER ; $$ 

I needed to change the last line to the following:

PHP Code:
DELIMITER ; 

Then the stored procedure is created without any error. As far as i understood from the manuals the last command is just to set back the Delimiter from $$ to ;

Daniel

steinm · 12-18-2012, 04:15 PM

(12-18-2012, 01:36 AM)Daniel Wrote: Then the stored procedure is created without any error. As far as i understood from the manuals the last command is just to set back the Delimiter from $$ to ;

Indeed, the extra $$ shouldn't be there.

Uwe

Daniel · 12-18-2012, 05:32 PM

(12-18-2012, 04:15 PM)steinm Wrote:
(12-18-2012, 01:36 AM)Daniel Wrote: Then the stored procedure is created without any error. As far as i understood from the manuals the last command is just to set back the Delimiter from $$ to ;

Indeed, the extra $$ shouldn't be there.

Uwe

But this error will not go away, even if the extra $$ are removed.

PHP Code:
PROCEDURE letonew.DROPFK does not exist
Can't create table 'letonew.#sql-1b3f_227' (errno: 121) 

Daniel

Daniel · 12-19-2012, 10:44 PM

Hi Uwe,

Any idea what else i can try to get rid of above error?

Daniel

steinm · 12-19-2012, 11:00 PM

(12-19-2012, 10:44 PM)Daniel Wrote: Hi Uwe,

Any idea what else i can try to get rid of above error?

How about, if you make a schema dump of the database and send it privately. So, I could try to run the update on it.

Uwe

steinm · 12-20-2012, 07:32 PM

I found a solution. The procedure for droping the foreign key in the update-3.4.0/update.sql script didn't work.

It must be replaced by

Code:
DROP PROCEDURE IF EXISTS DROPFK;

DELIMITER $$

CREATE PROCEDURE DROPFK (

IN parm_table_name VARCHAR(100),

IN parm_key_name VARCHAR(100)

)

BEGIN

  SET @table_name = parm_table_name;

  SET @key_name = parm_key_name;

  SET @sql_text = concat('ALTER TABLE ',@table_name,' DROP FOREIGN KEY ',@key_name);

  IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS

    WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN

    PREPARE stmt FROM @sql_text;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

  END IF;

END $$

DELIMITER ;

Uwe

Login
Username:
Password:	Lost Password?
	Remember me