Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database update from 3.3.10 to 3.4.0 fails
12-17-2012, 02:22 AM,
#1
Database update from 3.3.10 to 3.4.0 fails
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=3minor=4subminor=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' (errno150

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
Reply
12-17-2012, 01:33 PM,
#2
RE: Database update from 3.3.10 to 3.4.0 fails
(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=3minor=4subminor=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' (errno150

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
Reply
12-17-2012, 02:04 PM, (This post was last modified: 12-17-2012, 02:11 PM by Daniel.)
#3
RE: Database update from 3.3.10 to 3.4.0 fails
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` ( `idint(11NOT NULL auto_increment, `userIDint(11NOT NULL default '0', `pwdvarchar(50) default NULL, `datedatetime NOT NULL default '0000-00-00 00:00:00'PRIMARY KEY (`id`), CONSTRAINT `tblUserPasswordHistory_userFOREIGN KEY (`userID`) REFERENCES `tblUsers` (`id`) ON DELETE CASCADE ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE tblUsers ADD COLUMN 
`pwdExpirationdatetime NOT NULL default '0000-00-00 00:00:00'
ALTER TABLE tblUsers ADD COLUMN `loginfailurestinyint(4NOT NULL default '0'
ALTER TABLE tblUsers ADD COLUMN `disabledsmallint(4NOT NULL default '0'
ALTER TABLE tblUsers ADD UNIQUE(`login`)
UPDATE tblVersion set major=3minor=4subminor=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
Reply
12-17-2012, 11:48 PM,
#4
RE: Database update from 3.3.10 to 3.4.0 fails
(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` ( `idint(11NOT NULL auto_increment, `userIDint(11NOT NULL default '0', `pwdvarchar(50) default NULL, `datedatetime NOT NULL default '0000-00-00 00:00:00'PRIMARY KEY (`id`), CONSTRAINT `tblUserPasswordHistory_userFOREIGN KEY (`userID`) REFERENCES `tblUsers` (`id`) ON DELETE CASCADE ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE tblUsers ADD COLUMN 
`pwdExpirationdatetime NOT NULL default '0000-00-00 00:00:00'
ALTER TABLE tblUsers ADD COLUMN `loginfailurestinyint(4NOT NULL default '0'
ALTER TABLE tblUsers ADD COLUMN `disabledsmallint(4NOT NULL default '0'
ALTER TABLE tblUsers ADD UNIQUE(`login`)
UPDATE tblVersion set major=3minor=4subminor=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
Reply
12-18-2012, 01:36 AM, (This post was last modified: 12-18-2012, 01:58 AM by Daniel.)
#5
RE: Database update from 3.3.10 to 3.4.0 fails
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=3minor=4subminor=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_nameTHEN
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
Reply
12-18-2012, 04:15 PM,
#6
RE: Database update from 3.3.10 to 3.4.0 fails
(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
Reply
12-18-2012, 05:32 PM,
#7
RE: Database update from 3.3.10 to 3.4.0 fails
(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
Reply
12-19-2012, 10:44 PM,
#8
RE: Database update from 3.3.10 to 3.4.0 fails
Hi Uwe,

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

Daniel
Reply
12-19-2012, 11:00 PM,
#9
RE: Database update from 3.3.10 to 3.4.0 fails
(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
Reply
12-20-2012, 07:32 PM,
#10
RE: Database update from 3.3.10 to 3.4.0 fails
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
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)

Contact Us | LetoDMS Community Forum | Return to Top | | Lite (Archive) Mode | RSS Syndication