Encoding and access issues
Hi. I love the CONNECT engine and I have a perfect use case for it. Actually FederatedX would work as well, but I really need the pushdown.
I have found two issues though. First off, I can not connect with my low perm user. Access has been given like this: GRANT ALL ON `mydb`.* to myuser@'192.168.8.%' identified by 'mypw'; I then get access denied. But if I change the ON part to *.* it works. Is this by design? It would be preferable to not have to give access to the mysql database for example.
The bigger issue though is that I am getting encoding issues. My main database has a table that has this column: `region` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
I have then created a connect-table like this: CREATE TABLE mydb.mytable ( `region` varchar(64) CHARACTER SET utf8 DEFAULT NULL ) ENGINE="CONNECT" TABLE_TYPE=MYSQL DEFAULT CHARSET=utf8 COLLATE=utf8_bin CONNECTION = 'pgc-db-01';
The connection itself has been created like this: CREATE SERVER 'pgc-db-01' foreign data wrapper 'mysql' options (HOST '192.168.8.6', DATABASE 'mydb', USER 'myuser', password 'mypw', PORT 3306, SOCKET '', OWNER 'myuser');
In the main database the region column has the value "Jönköping". But on the server using CONNECT it has the value J. These warnings are raised:
| Warning | 1366 | Incorrect string value: '\xF6nk\xF6pi...' for column 'region' at row 1 |
| Warning | 1105 | Out of range value J�nk�ping for column 'region' at row 1 |
I can not figure out why this would be. Both servers has the same output of show variables like '%charac%';
The encoding issue is seen both with the mysql cli client and my php implementation.
Now, there is one important fact. The server using connect is of this version: 10.0.12-MariaDB-1wheezy-log and the "master" is of 5.5.38-MariaDB-1wheezy-log
Could this be incompatibility between version 5.5 and 10.0?
Answer Answered by Olivier Bertrand in this comment.
About your issues:
The access issue is not related to the CONNECT engine. I had the same when trying to access a table with the mysql client. It was fixed by issuing:
FLUSH PRIVILEGES;
After the GRANT command.
The UTF8 issue comes from the fact that when reading the remote table the strings are automatically converted. To fix this issue, just remove the CHARACTER SET UTF8 and DEFAULT CHARSET=utf8 COLLATE=utf8_bin from the declaration of the CONNECT table.
