Mysql data source error

I'm assuming your are on latest version of RDM. Could you please send us your DB Schema for analysis.

File -> Data Sources -> Upgrade (tab) -> Send Schema to Support

Best regards,

Would like to do so, but i get:

Schema Dump failed with error:

Unknown Collumn 'Data_Type' in 'field list'

We've seen the "Unknown Collumn 'Data_Type' in 'field list'" error before but can't reproduce it.

If you could run the following SQL script (one will fail with the same error but should help me diagnose it) and send me the results I should be able to diagnose both the issues. This is the exact same code that runs when you hit the "send schema to support" button.


select
(select DatabaseVersion from DatabaseInfo limit 1)
,database()
,@@hostname;

-- Tables & Columns
SELECT table_name as 'Table', column_name as 'Name', COLUMN_DEFAULT as 'Default', IS_NULLABLE as 'Nullable', DATA_TYPE as 'Type', case when CHARACTER_MAXIMUM_LENGTH >= 65535 then null else CHARACTER_MAXIMUM_LENGTH END as 'Length'
FROM information_schema.columns
WHERE table_schema = database()
order by 1, 2;

-- triggers
select EVENT_OBJECT_TABLE as 'Table', trigger_name as 'Name', ACTION_TIMING as 'Timing', EVENT_MANIPULATION as 'Event', ACTION_ORIENTATION as 'Orientation', action_statement as 'Code'
from information_schema.triggers
WHERE trigger_schema = database()
order by 1;

-- procedures/functions
select ROUTINE_NAME as 'Name', ROUTINE_TYPE as 'Type', DATA_TYPE as 'DataType', ROUTINE_DEFINITION as 'Code'
from information_schema.routines
WHERE routine_schema = database()
order by 1;

-- views
select table_NAME as 'Name', view_DEFINITION as 'Code'
from information_schema.views
WHERE table_schema = database()
order by 1;

-- constraints
select constraint_name as 'Name', TABLE_NAME as 'Table', CONSTRAINT_TYPE as 'Type'
from information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = database()
order by 2, 1;

-- constraint columns
select Table_NAME as 'Table', CONSTRAINT_NAME as 'Name', COLUMN_NAME as 'Column', Ordinal_Position as 'Position', Referenced_table_name as 'ReferencedTable', Referenced_Column_Name as 'ReferencedColumn'
from information_schema.KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = database()
order by CONSTRAINT_NAME, ORDINAL_POSITION;

Not really much :(

What version of mysql are you running?
SHOW VARIABLES LIKE "%version%"
Has the schema been created? Is ha_test the schema? What happens when you click create schema?

Variable_nameValueprotocol_version10version5.1.73version_commentSource distributionversion_compile_machinex86_64version_compile_osredhat-linux-gnu
[/table]
now i get "already exists" when i try to create a schema. Before i got the same error Message as in the beginning...


Btw: Thanks for your Help: Much appreciated!

Could you try creating a new schema? Change the schema name to something like "RDM2" and hit "Create Schema" button to see if it works. If that doesn't work, we might need to connect remotely to diagnose the issue at hand.

Regards,

First of all a user in our mysql enviroment has no root rights so cannot create a schema. But i did a workaround, i gave the user the all rights on a non existing database rdm and then did a "create schema". Result is the same error as before. It looks like a lock is set in one transaction and this lock is not released before the next statement. Only the table connections was created, nothing more


MySqlException - Can't execute the given command because you have active locked tables or an active transaction

at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& 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 connection)
at Devolutions.RemoteDesktopManager.Business.DatabaseUpgradeAction.Execute(DatabaseConnectionDataSource dataSource)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.Upgrade(Boolean silent, LogMessageDelegate logMessage)

I can't reproduce this issue here so we are going at it blindly. We can try to kick start the DB creation with the following script.

Select the new RDM schema, make sure it's empty (no tables) and run the script below. Then, using RDM initiate the DB upgrade process. If you still get the error run this query and send me the result (number) select DatabaseVersion from DatabaseInfo
Initiate MySQL DB Script
delimiter $$

ALTER DATABASE `RDM` charset=utf8;
$$

CREATE TABLE IF NOT EXISTS `Connections`
(
`ID` varchar(36) CHARACTER SET utf8 NOT NULL,
`Data` longtext CHARACTER SET utf8 NOT NULL,
CONSTRAINT `PK_Connections` PRIMARY KEY (`ID`)
);
$$

ALTER TABLE `Connections` CONVERT TO CHARACTER SET utf8;
$$

CREATE TABLE IF NOT EXISTS `DatabaseInfo` (
`DatabaseVersion` int,
`Settings` longtext CHARACTER SET utf8 NULL,
`ID` varchar(36) CHARACTER SET utf8 NOT NULL Default '00000000-0000-0000-0000-000000000000',
`ConnectionCacheID` varchar(36) CHARACTER SET utf8 NULL,
CONSTRAINT `PK_DatabaseInfo` PRIMARY KEY (`DatabaseVersion`)
);
$$

INSERT INTO `DatabaseInfo` (`DatabaseVersion`, `ConnectionCacheID`) VALUES (1, UUID());
$$

delimiter ;