MySQL as data source, another topic :)

Update:

I copied upgrade script to phpMyAdmin, change $DATABASENAME$ to real database name and run the query
All tables are created !

Issues:
1. "Updata Schema" button still works incorrectly
2. This datasource addon should use only root mysql credentials ?!? These lines from SQL logs..
523 Init DB rdm
523 Query select count(*) from mysql.user where Super_priv = 'Y' and user = 'rdm'

The add-on is no longer supported. It's now built-in. How did you install the add-on exactly?

No, I'm not install it

So you don't have the mysql add-on dll installed in your application folder?

I tried to search "*mysql*" in this folder and found only MySql.Data.dll. Size=377.856, Last modified - 29.10.2012

What is the file version for this one?

6.5.4.0

That's the good one. Do you still have an error?

sure
If you prefer, I can make short video and share with you

I would like to see the error message first. Could you verify your application logs in Help->Application Logs

Latest error, that concerns privileges issue, very important for me:

[11/16/2012 4:31 PM]ERROR MySql.Data.MySqlClient.MySqlException: SELECT command denied to user 'rdm'@'this_is_my_ip' for table 'user'
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
...

I try to use non-privileged user, which have full privileges to necessary database. Is this possible?

From SQL logs:
>>Query select count(*) from mysql.user where Super_priv = 'Y' and user = 'rdm'
sure, my user doesn't have the privileges to use "mysql" database..
edited by Alex-KS02 on 11/16/2012

Have you tried to create this user in RDM (Administration->Users)?

no.. :) sorry..

Now I encountered with another issue
I created new user rdm with restricted rights, successfully edited my datasource and restarted Remote Desktop Manager

After I typed the password, error window appears:
The corrent datasource is unavailable

Application logs:
[11/16/2012 4:46 PM]ERROR MySql.Data.MySqlClient.MySqlException: Table 'rdm.UserGroupinfo' doesn't exist
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
..

Sure, this table doesn't exist.. only "UserGroupInfo" (please note, Info - with capital letter) was created during database preparation..

Is it possible that mySQL could installed not case sensitive? This could explains the problem you have. Stef did a lot of testing and he nevers has this problem.

This is default MySQL behavior, which installed on any *nix system, and trying to change it could affect other MySQL databases..

Does your mySQL is installed on Unix or Windows? We will fix the upgrade script for sure but I would like to be able to reproduce it.

Debian Linux

We will setup a mySQL on Linux and test it. We did all our test on Windows.

I've fixed a few issues with the MySQL data source

- fixed issue with case sensitive table names
- fixed issue with the selecting rows from mysql.user table when the user doesn't have read privileges
- upgrade database issue where the upgrade would fail with message like "Can't create table 'rdm.#somename' (errno: 150)"

all fixes are available in the latest release 8.0.4.0 download it here

Thanks a lot! These issues are really fixed! But there are some another issues..

1. I tried to create new schema

2. Click "Create Schema"

3. MySQL logs:
121120 13:04:52 510 Connect root@my_ip on

510 Query SHOW VARIABLES
510 Query SHOW COLLATION
510 Query SET character_set_results=NULL
121120 13:05:20 510 Query SELECT count(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'rdmtest'
510 Query SELECT count(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'rdmtest'
510 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
510 Query BEGIN
510 Query CREATE DATABASE `rdmtest`
510 Query COMMIT
511 Connect root@my_ip on rdmtest
511 Query SHOW VARIABLES
511 Query SHOW COLLATION
511 Query SET character_set_results=NULL
511 Init DB rdmtest
511 Query CREATE TABLE IF NOT EXISTS `Connections`
(
`ID` varchar(36) NOT NULL,
`Data` longtext NOT NULL,
CONSTRAINT `PK_Connections` PRIMARY KEY (`ID`)
)
121120 13:05:21 512 Connect root@my_VPS_ip on rdmtest
my comment: another tables isn't created!
512 Query SHOW VARIABLES
512 Query SHOW COLLATION
512 Query SET character_set_results=NULL
512 Init DB rdmtest
512 Query select COALESCE(DatabaseVersion, 0) FROM `DatabaseInfo`

4. Click "Yes"

5. MySQL logs:

121120 13:14:34 520 Connect root@my_ip on rdmtest
520 Query SHOW VARIABLES
520 Query SHOW COLLATION
520 Query SET character_set_results=NULL
520 Init DB rdmtest
520 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
520 Query BEGIN
121120 13:14:35 520 Query CREATE TABLE IF NOT EXISTS `DatabaseInfo` (
`DatabaseVersion` int,
`Settings` longtext NULL,
`ID` varchar(36) NOT NULL Default '00000000-0000-0000-0000-000000000000',
`ConnectionCacheID` varchar(36) NULL,
CONSTRAINT `PK_DatabaseInfo` PRIMARY KEY (`DatabaseVersion`)
)
520 Query UPDATE DatabaseInfo SET DatabaseVersion=1
520 Query COMMIT
521 Connect root@my_ip on rdmtest
521 Query SHOW VARIABLES
521 Query SHOW COLLATION
521 Query SET character_set_results=NULL
521 Init DB rdmtest
521 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
521 Query BEGIN
521 Query ALTER DATABASE rdmtest charset=utf8
521 Query ROLLBACK
521 Init DB rdmtest
521 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
121120 13:14:36 521 Query BEGIN
521 Query ALTER DATABASE rdmtest charset=utf8
521 Query ROLLBACK


If you prefer I can share you my.cnf config file - it doesn't have any fine tuning options..

Interesting, yes please send me (via private message if you like) your my.cnf file.

The issue we are seeing here is with the command that changes the default charset of the database to utf8. The command:

ALTER DATABASE rdmtest charset=utf8;

From the error message images you attached it looks like it’s failing on a table lock.

“Can't execute the given command because you have active locked tables or an active transaction.”

The log file explained:
- (510) create the schema/database, success
- (511) create the basic “Connections” table, this is to match the AddOn we had prior to the full fledge datasource, success
- (512) Get the database version information from the ‘DatabaseInfo’ table. Fails, as expected, since we haven’t yet created the table, we are only just getting started. On future upgrades this will return the db version that will allow us to continue the upgrade at a given step.
- At this point we’ve realised that we need an upgrade therefore we prompt you.
- (520) Create ‘DatabaseInfo’ table & register the step. (set DatabaseVersion = 1), success
- (521) change database charset, FAIL

Could you please try running this query in MySQL Workbench?

ALTER DATABASE rdmtest charset=utf8;

If it was successful, please run the following:

UPDATE DatabaseInfo SET DatabaseVersion=2;

Now try to run the datasource upgrade again. Success? Failed?

Thank you for your help on the subject, we will get to the bottom of this.

I'm sorry, there is too few time - work hard, play hard.. :) I'll investigate this issue today or tomorrow and write you