Beta 13.9.0.0 - Unable to upgrade MySQL database

Hello,

Improvements have been made to the MySQL data source however this is still in beta.

I can confirm myself that I have issues when connecting to a MySQL database with RDM 14 Beta.


The issues you're encountering today will be addressed in the next beta releases.

In the meantime, if you revert back to 13.6.7.0 and restore a backup of your database, does it work?


Best regards,

I uninstalled and put back 13.6.7.0, didn't restore the database, and all is well again.

Hello,

As mentioned in this thread, indeed, RDM Beta 13.9 is not compatible with MySQL data source
https://forum.devolutions.net/topic30381-rdm--beta.aspx

Best regards,

@Taomyn,

Could you please send us your MySQL for analysis. File > Data Sources > Upgrade > Email Schema to Support.

Best regards,

I tried but it won't allow me to, see screen shot

Also, does this file include my credentials?

Schema dump only includes the structure of the database (schema) and other stats (server version, schema version).

Open a command line at "C:\Program Files\MySQL\MySQL Workbench 6.3 CE" your Workbench version might be different.
Run the following : mysqldump -h SERVER_NAME -u root -p --no-data DATABASE_NAME > c:\temp\schema.sql

You will be prompted for your password. Find the file on disk and send it to me please.

Attached are a sample schema dumps: one via mysqldump & one via RDM as a reference.

Best regards,

PM'd the schema

Got it thanks, I will investigate and get back to you soon.

Best regards,

Could you please execute the following and return me the number?

select DatabaseVersion from DatabaseInfo;


Thank you,

124

Exactly what I suspected. The fix has been done and will be in a future beta release.

To get you going you can execute the following SQL statement:

ALTER TABLE `Repository` DROP COLUMN `Repository`;

Thanks, though unless the beta is weeks away I'm happy to wait for the fixed beta so I can test it for you - just be sure to flag it in the change-log that appears when the application detects a newer version (unlike not mentioning the MySQL issue in the first place, at least I never saw it until I visited the forums).

13.9.6 beta is now available

https://remotedesktopmanager.com/home/download#Beta

Best regards,

Does 13.9.6 contain fixed support for MySQL as the release notes make no mention of it?

Yes, v13.9.6 does in fact contain the fix. You are correct it is missing from the release notes.

Best regards,

I'm afraid you have some more fixing to do

doh!



Can you please send me your schema and database version. Try the built-in schema send button to see if it succeeds if not you will need to perform the same steps as before.

Sorry for the inconvenience.

Best regards,

My schema and version haven't changed since the first time, I've been waiting for the fix and skipped all updates until now, plus the screen shots includes once again that the built-in tool won't export my schema.

I have an idea what the issue is. I will try another fix.

As for the schema dump crash, can you have a look in the application log and send me the error message.

Thanks,

Also you might have a more detail error of the failed upgrade. If you don't see it, go to File > Options > Advanced > Debug Level (top of form) and set the value to 1. Try the upgrade, it will fail and the detail of the errors will be logged. Please send me your log file.

Best regards,

It looks like your table names could all be lowercase, instead of CamelCase. Can you please confirm.
SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = database() order by 1;

I'm unable to connect to the PC running RDM, but the database I can and you're correct, they are all lowercase:

[rdm]> SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = database() order by 1;
+---------------------------+
| TABLE_NAME |
+---------------------------+
| appsettings |
| attachment |
| backupjob |
| backupjobuser |
| backuplog |
| connectionhandbook |
| connectionhandbookhistory |
| connectionhistory |
| connectionlog |
| connections |
| connectionstate |
| customimage |
| databaseinfo |
| datasourcesettinghistory |
| groupchanged |
| groupinfo |
| groupinfohistory |
| inventory |
| loginattempt |
| loginhistory |
| logmessage |
| monitoring |
| report |
| repository |
| repositoryhistory |
| secureattachment |
| securemessage |
| sessionrecording |
| subscription |
| subscriptionevent |
| todo |
| todohistory |
| todousers |
| useraccount |
| usergroupinfo |
| userinfo |
| userinfohistory |
| userprofile |
| userroamingsettings |
| userrole |
| usersecurity |
| versioning |
+---------------------------+
42 rows in set (0.061 sec)

Odd, we create our tables with CamelCase, it looks like the server forced lowercase in your case. We could try renaming the tables but before we do this can you run the following SQL statements.

The first will fail, the second will pass. This would explain why RDM send schema fails in your case. It tries to query the Connections table but in your case it is called connections.select * from Connections;
select * from connections;
If my above assumptions are correct you can execute the following to rename your tables.

RENAME TABLE appsettings TO AppSettings;
RENAME TABLE attachment TO Attachment;
RENAME TABLE backupjob TO BackupJob;
RENAME TABLE backupjobuser TO BackupJobUser;
RENAME TABLE backuplog TO BackupLog;
RENAME TABLE connectionhandbook TO ConnectionHandbook;
RENAME TABLE connectionhandbookhistory TO ConnectionHandbookHistory;
RENAME TABLE connectionhistory TO ConnectionHistory;
RENAME TABLE connectionlog TO ConnectionLog;
RENAME TABLE connections TO Connections;
RENAME TABLE connectionstate TO ConnectionState;
RENAME TABLE customimage TO CustomImage;
RENAME TABLE databaseinfo TO DatabaseInfo;
RENAME TABLE datasourcesettinghistory TO DataSourceSettingHistory;
RENAME TABLE groupchanged TO GroupChanged;
RENAME TABLE groupinfo TO GroupInfo;
RENAME TABLE groupinfohistory TO GroupInfoHistory;
RENAME TABLE inventory TO Inventory;
RENAME TABLE loginattempt TO LoginAttempt;
RENAME TABLE loginhistory TO LoginHistory;
RENAME TABLE logmessage TO LogMessage;
RENAME TABLE monitoring TO Monitoring;
RENAME TABLE report TO Report;
RENAME TABLE repository TO Repository;
RENAME TABLE repositoryhistory TO RepositoryHistory;
RENAME TABLE secureattachment TO SecureAttachment;
RENAME TABLE securemessage TO SecureMessage;
RENAME TABLE sessionrecording TO SessionRecording;
RENAME TABLE subscription TO Subscription;
RENAME TABLE subscriptionevent TO SubscriptionEvent;
RENAME TABLE todo TO Todo;
RENAME TABLE todohistory TO TodoHistory;
RENAME TABLE todousers TO TodoUsers;
RENAME TABLE useraccount TO UserAccount;
RENAME TABLE usergroupinfo TO UserGroupInfo;
RENAME TABLE userinfo TO UserInfo;
RENAME TABLE userinfohistory TO UserInfoHistory;
RENAME TABLE userprofile TO UserProfile;
RENAME TABLE userroamingsettings TO UserRoamingSettings;
RENAME TABLE userrole TO UserRole;
RENAME TABLE usersecurity TO UserSecurity;

Sorry, but both work - I'd paste the results but they are quite long, re-running from Heidi-SQL GUI, I can get you:

select * from Connections;
select * from connections;
/* Affected rows: 0 Found rows: 30 Warnings: 0 Duration for 2 queries: 0.000 sec. */

I've never know these to ever be case-sensitive in MySQL (I've coded for PHP in the past), so not sure why it would be a problem.

There are as many ways to configure MySQL/MariaDB then people using it.

We have a Unix based install that is case sensitive in regards to table name. Usually controlled by the lower_case_table_names system variable.

In your case, table names are lowercase but both lowercase & CamelCase queries succeed.



I noticed in your table name export, from earlier today, tables that don't belong to v124. It looks like some steps might have been executed but not registered. Could you please re-export your schema using the command line. If you have no private data please export with data so that I can look at the versioning information.

Data"C:\Program Files\MySQL\MySQL Workbench 6.3 CE\mysqldump.exe" -h SERVER_NAME -u root -p DATABASE_NAME > c:\temp\schemadata.sqlNo-Data"C:\Program Files\MySQL\MySQL Workbench 6.3 CE\mysqldump.exe" -h SERVER_NAME -u root -p[color=rgb(73, 73, 73)][font="Open Sans", sans-serif] [/font][/color][color=rgb(73, 73, 73)][font="Open Sans", sans-serif]--no-data[/font][/color] DATABASE_NAME > c:\temp\schemanodata.sql

Also the RDM logs will help figure out what is truly going on.

Best regards,

Right, so I went back to my previous backup before this latest update and re-ran RDM after first enabling debugging as requested. Attached are screen shots of everything that happened, and the two log files.

Hope they help.

Perfect, exactly what I needed.

I will resolve your issue and try to get a new beta build out in a day or so (with change log).

Best regards,

13.9.7 is out, it should solve the last error you had and I'm hoping no other errors are present. It should also fix the send schema issue.

https://remotedesktopmanager.com/home/download#Beta

https://remotedesktopmanager.com/release-notes/beta

Please send me your schema for validation after you upgrade.

Best regards,

Thanks, I will test it tonight and get back to you

Hi again, finally success, though not everything is perfect, screen shots as follows:

Also attached the logs and the current upgraded schema

Thank you for the logs.

I've fixed, what I hope is, your last ever MySQL data source related error.

The next beta build the Send Schema action should succeed along with all other MySQL data source actions.

Best regards,

Hey there, thanks for all the fixes. Now I have another issue which is kind of related. I want to move the database to my MariaDB server running on Fedora. I've moved several other DBs without issue (phpBB and ownCloud for example), but with RDM it won't load and it seems to be related to the "lower case" difference between Windows and Linux.

When I move the database (export then import) and point RDM to it, it complains that tables are missing, and they are clearly there just not in the same case e.g. it was complaining it could not find "ConnectionHistory" which is called "connectionhistory" in my database.

How can I sort this?

So my guess is that the Fedora MySQL instance is case sensitive vs the other MySQL forces lower case table names on disk yet can query using CamelCase.

On the Fedora MySQL instance run the following:

select * from Connections;
select * from connections;Did they both work? First? Second?

In the case both or only the first worked then all should have been ok with RDM, would need to investigate. Let me know if this is the case.

The last case is only the second one works. In this case we will need to rename all tabes to CamelCase. Run the following script and try running RDM again.

RENAME TABLE appsettings TO AppSettings;
RENAME TABLE attachment TO Attachment;
RENAME TABLE backupjob TO BackupJob;
RENAME TABLE backupjobuser TO BackupJobUser;
RENAME TABLE backuplog TO BackupLog;
RENAME TABLE connectionhandbook TO ConnectionHandbook;
RENAME TABLE connectionhandbookhistory TO ConnectionHandbookHistory;
RENAME TABLE connectionhistory TO ConnectionHistory;
RENAME TABLE connectionlog TO ConnectionLog;
RENAME TABLE connections TO Connections;
RENAME TABLE connectionstate TO ConnectionState;
RENAME TABLE customimage TO CustomImage;
RENAME TABLE databaseinfo TO DatabaseInfo;
RENAME TABLE datasourcesettinghistory TO DataSourceSettingHistory;
RENAME TABLE groupchanged TO GroupChanged;
RENAME TABLE groupinfo TO GroupInfo;
RENAME TABLE groupinfohistory TO GroupInfoHistory;
RENAME TABLE inventory TO Inventory;
RENAME TABLE loginattempt TO LoginAttempt;
RENAME TABLE loginhistory TO LoginHistory;
RENAME TABLE logmessage TO LogMessage;
RENAME TABLE monitoring TO Monitoring;
RENAME TABLE report TO Report;
RENAME TABLE repository TO Repository;
RENAME TABLE repositoryhistory TO RepositoryHistory;
RENAME TABLE secureattachment TO SecureAttachment;
RENAME TABLE securemessage TO SecureMessage;
RENAME TABLE sessionrecording TO SessionRecording;
RENAME TABLE subscription TO Subscription;
RENAME TABLE subscriptionevent TO SubscriptionEvent;
RENAME TABLE todo TO Todo;
RENAME TABLE todohistory TO TodoHistory;
RENAME TABLE todousers TO TodoUsers;
RENAME TABLE useraccount TO UserAccount;
RENAME TABLE usergroupinfo TO UserGroupInfo;
RENAME TABLE userinfo TO UserInfo;
RENAME TABLE userinfohistory TO UserInfoHistory;
RENAME TABLE userprofile TO UserProfile;
RENAME TABLE userroamingsettings TO UserRoamingSettings;
RENAME TABLE userrole TO UserRole;
RENAME TABLE usersecurity TO UserSecurity;

Yes, as we suspected the case is now sensitive, so I ran your rename commands and now all is well. However a couple of things cropped up.

And I had to rename another table "versioning" to "Versioning".

The two errors you go are perfectly normal. Those tables no longer exist in v13.9.x

As for the versioning table, please rename it to lower case versioning, not sure if it will cause any issues but it was actually created to be lowercase (by design) and we always access it lowercase. My bad, the only exception we have, older code that we must now live with.

Best regards,

That'll teach me to be pro-active ;) I've renamed it back, though I didn't see any difference at the time.

Seems you've broken my RDM again with the latest beta, and "Send schema" is far from fixed.

v13.9.10.0 (October 8th 2018) Beta has a fix for this issue.

This should be the last regression issue with MySQL. We've but a process in place over the weekend to make sure we don't regress MySQL when fixing a bug in SQL Server data source and vise versa. With v14 (v13.9 beta) we've brought MySQL data source on par, functionality-wise, with the SQL Server data source. In doing so we are reusing code as much as possible, this has the advantage of fixing a bug once for both data sources but also has the disadvantage of possible regression. With this new process in place the likely hood is greatly reduced.

Thank you for your patience and understanding during the v13.9 beta and your help has been greatly appreciated.


Best regards,

I almost forgot to report that the latest beta allowed me to run RDM again, thanks again for that.

BTW, I still can't export the schema using the "Send schema via email" button, it always crashes with the same error previously reported.