Unable to upgrade MySQL Database after update to v14

Hello,

This is being investigated as we speak, we will post shortly with our findings.

Best regards,

Could you please send us you database schema. File > Data Sources > Upgrade (tab) > Email schema to support. Please add: Attn: Stefane to the subject.

Hi,

I've emailed the schema using the RDM 'Email schema to support' button as requested - there was no input box for subject, so I included "Attn: Stefane" in the message body along with a reference to this forum post

I'm leaving the office for the day now so will be unable to respond until 09:00 GMT+1


Many thanks

You might have tables in your MySQL/MariaDB schema that are using the MyISAM engine.

Run the following SQL statement (make sure you are on the proper schema):SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' ENGINE = InnoDB;')
FROM information_schema.TABLES
WHERE ENGINE='MyISAM' AND TABLE_SCHEMA = database();If the statement outputs any data (see attached image) then copy that output and execute it. It will convert all your tables to the innodb engine.

Before continuing the upgrade process, you might want to test the servers default engineselect @@default_storage_engine;If the output is MyISAM then you should probably change the engine to innodb. Note this could cause issues with other schemas on this server, if you think this server is being used by other systems I would suggest you not perform this task.

To change the default engine see: https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_default-storage-engine

Now try to open RDM and let it perform the upgrade, all should be good now.

P.S. We will modify RDM to perform these task automatically in the future

Best regards,

Hi,

I checked the schema - the default is set correctly (RDMGR).

When I run the query:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' ENGINE = InnoDB;')
FROM information_schema.TABLES
WHERE ENGINE='MyISAM' AND TABLE_SCHEMA = database();
I get no results.

When I check the default storage engine using:



select @@default_storage_engine;

It returns InnoDB, so it seems that's already set. I've also updated to 14.0.1.0 but the upgrade is still not working and returning


MySql.Data.MySqlClient.MySqlException (0x80004005): Can't create table 'RDMGR.ConnectionState' (errno: 150)
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at MySql.Data.MySqlClient.MySqlScript.Execute()
at Devolutions.RemoteDesktopManager.Business.DataSources.MySQLConnectionDataSource.ExecuteScript(String script, IDbConnection dbConnection)
at Devolutions.RemoteDesktopManager.Business.DataSources.MySQLConnectionDataSource.ExecuteScript(String script, IDbTransaction dbTransaction)
at Devolutions.RemoteDesktopManager.Business.DatabaseUpgradeAction.Execute(DatabaseConnectionDataSource dataSource)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.Upgrade(Boolean silent, LogMessageDelegate logMessage)



Regards
Niels Jensen

Hello,

We have the exact same symptom, and

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' ENGINE = InnoDB;')
FROM information_schema.TABLES
WHERE ENGINE='MyISAM' AND TABLE_SCHEMA = database();
Does not yield any result.

Could you both please install v14.0.1.0 and send me you schema for analysis.

File > Data Source > Upgrade (tab) > Email Schema to Support

I think there might be data type discrepancy between the two tables and the foreign keys can't be created.


Best regards,

Email sent. I added "Attn: Stefane" into the body.

Thanks
Niels

Email sent as well, with "Attn: Stefane" in the body.

@all,

Cloud you please try to run the following manually (one statement at a time) and let me know which of last 3 statements fail?CREATE TABLE IF NOT EXISTS `ConnectionState` (
`ID` VARCHAR(36) NOT NULL,
`ConnectionID` VARCHAR(36) NULL,
`RepositoryID` VARCHAR(36) NULL,
`UserID` VARCHAR(36) NULL,
`State` INT NOT NULL,
`Comment` LONGTEXT NULL,
`ExpirationDate` DATETIME NULL,
`MachineName` VARCHAR(256) NULL,
`CreationDate` DATETIME NULL,
`CreationUsername` VARCHAR(256) NULL,
`CreationLoggedUserName` VARCHAR(256) NULL,
constraint PK_ConnectionState PRIMARY KEY clustered (`ID`),
INDEX `CI_ConnectionState` (`RepositoryID` ASC, `State` ASC),
INDEX `IX_ConnectionState_UserID` (`UserID` ASC),
INDEX `IX_ConnectionState_ConnectionID` (`ConnectionID` ASC)
);

alter table `ConnectionState` add
CONSTRAINT `FK_ConnectionState_ConnectionID`
FOREIGN KEY (`ConnectionID`)
REFERENCES `Connections` (`ID`)
ON DELETE CASCADE
ON UPDATE NO ACTION;


alter table `ConnectionState` add
CONSTRAINT `FK_ConnectionState_RepositoryID`
FOREIGN KEY (`RepositoryID`)
REFERENCES `Repository` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;


alter table `ConnectionState` add
CONSTRAINT `FK_ConnectionState_UserID`
FOREIGN KEY (`UserID`)
REFERENCES `UserAccount` (`ID`)
ON DELETE CASCADE
ON UPDATE NO ACTION;

Hi,

The first of the 3 alter tables gives me the following error:
/* SQL Error (1005): Can't create table 'RDMGR.#sql-57e_194b' (errno: 150) Foreign key constraint is incorrectly formed */

The last two run without issue


For clarity, this is the alter table statement that fails:

alter table `ConnectionState` add
CONSTRAINT `FK_ConnectionState_ConnectionID`
FOREIGN KEY (`ConnectionID`)
REFERENCES `Connections` (`ID`)
ON DELETE CASCADE
ON UPDATE NO ACTION;


Regards
Niels Jensen

@Niels Jensen

Perfect, now please try to complete the upgrade with RDM, no need to worry about the failure of that particular constraint it gets removed later on in the upgrade script.

If all succeeds you might want to send me your schema one last time for validation.

Best regards,

Same here for the failure, it's the first one that fails.

@Jean Cardona - after further analysis of your schema it looks like you have a few issues. You have 15 tables that are using the MyISAM engine (doesn't allow for foreign keys).

Example:<Table>
<Name>ConnectionHistory</Name>
<Engine>MyISAM</Engine>
We will need to convert all tables and then created the missing foreign keys (we will do this after the tables).

Make sure when you run on the proper schemaSELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' ENGINE = InnoDB;')
[color=rgb(73, 73, 73)][font="Open Sans", sans-serif]FROM information_schema.TABLES[/font][/color]
[color=rgb(73, 73, 73)][font="Open Sans", sans-serif]WHERE ENGINE='MyISAM' AND TABLE_SCHEMA = database();[/font][/color]Not sure you have the correct shcema select [/font][/color]database();

I now have this error:

I've sent the schema for analysis

Hi,

Tried the update again. Still failed but this time on a different table:

MySql.Data.MySqlClient.MySqlException (0x80004005): Can't create table 'RDMGR.SessionRecording' (errno: 150)
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at MySql.Data.MySqlClient.MySqlScript.Execute()
at Devolutions.RemoteDesktopManager.Business.DataSources.MySQLConnectionDataSource.ExecuteScript(String script, IDbConnection dbConnection)
at Devolutions.RemoteDesktopManager.Business.DataSources.MySQLConnectionDataSource.ExecuteScript(String script, IDbTransaction dbTransaction)
at Devolutions.RemoteDesktopManager.Business.DatabaseUpgradeAction.Execute(DatabaseConnectionDataSource dataSource)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.Upgrade(Boolean silent, LogMessageDelegate logMessage)

I've emailed the schema again

Regards
Niels

After running the alter tables from the CONCAT command, the upgrade went well, thank you!

@Jean Cardona,

Please send me you schema to make sure all is good so that we don't have any errors with future upgrades.

Best regards,

Ok, done

@Neils Jensen

Ok, so we've fixed the "engine: innodb vs MyISAM", I've checked the field types and they are identical. The only thing I can figure out is the collation of the fields are different. Can you please execute the following and send me the result?
select * from INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = database();
It gives me the raw information of all columns & tables in your schema, if I can't find the issue with this then...

Thanks,

What's the best way for me to get this info to you? The forum will butcher the data, and the send schema to support text field in RDM isnt big enough for all the data to be posted

Regards
Niels

Hello,

You can send an email at support@devolutions.net

I will foward it to Stefane from there.

Best regards,

Email sent - thanks

You have a few options:

#1 using the code [ code ][ /code ] tags (perseveres any formatting & white space)
#2 attachment (.txt, .csv...)
#3 email slavergne-at-devolutions.net

Best regards,

got it thanks

OK I'm 99.99% sure we've found the issue. We have a table with CHARACTER_SET_NAME = utf8 and the other table CHARACTER_SET_NAME = latin1.

I know with MS SQL Server you can't create foreign keys between to different CHARACTER_SET_NAME columns so my guess is MySQL/MariaDB is the same way.

Now I need to figure out how to modify the CHARACTER_SET_NAME and make them all match.

@Jean Cardona

You have missing foreign keys, please execute the following script. Your are now 100% correct.

ALTER TABLE `Attachment` ADD CONSTRAINT `FK_Attachment_TodoID` FOREIGN KEY (`TodoID`) REFERENCES `Todo`(`ID`);

ALTER TABLE `Connections` ADD CONSTRAINT `FK_Connections_GroupInfo` FOREIGN KEY (`SecurityGroup`) REFERENCES `GroupInfo`(`ID`);

ALTER TABLE `Inventory` ADD CONSTRAINT `FK_Inventory_Connections` FOREIGN KEY (`ConnectionID`) REFERENCES `Connections`(`ID`);

ALTER TABLE `Monitoring` ADD CONSTRAINT `FK_Monitoring_Connections` FOREIGN KEY (`ConnectionID`) REFERENCES `Connections`(`ID`);

ALTER TABLE `Todo` ADD CONSTRAINT `FK_Todo_GroupInfo` FOREIGN KEY (`SecurityGroup`) REFERENCES `GroupInfo`(`ID`);

ALTER TABLE `UserGroupInfo` ADD CONSTRAINT `FK_UserGroupInfo_GroupInfo` FOREIGN KEY (`GroupInfoID`) REFERENCES `GroupInfo`(`ID`);

@Neils Jensen
Please execute the following & try again. I will need to change the upgrade script to deal with "CHARACTER SET" and all but I would like to get you unblocked and working first.

CREATE TABLE IF NOT EXISTS `SessionRecording` (
`ID` VARCHAR(36) NOT NULL,
`ConnectionLogID` VARCHAR(36) NOT NULL,
`CreationDate` DATETIME NOT NULL,
`RecordingType` INT NOT NULL,
`FileSize` INT NOT NULL,
`ItemIndex` INT NOT NULL,
`Folder` VARCHAR(256) NULL,
`StartTime` INT NOT NULL,
`Duration` INT NULL,
`HashValue` VARBINARY(64) NOT NULL,
`Data` LONGTEXT NULL,
`Flags` INT NOT NULL DEFAULT 0,
constraint PK_SessionRecording PRIMARY KEY clustered (`ID`),
CONSTRAINT `FK_SessionRecording_ConnectionLogID`
FOREIGN KEY (`ConnectionLogID`)
REFERENCES `ConnectionLog` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) CHARACTER SET utf8;

Hi,

I ran the above query successfully and then tried the update again - it worked! I've sent the working schema version for your inspection if you need it

Many thanks for the great support

Niels

Ok, I've done that. I've just sent the schema for verification.

Thank you for the great support!