Linux - Mysql with special characters issue

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
morfeus Offline
Member
Posts: 74
Joined: Aug 2009
Reputation: 1
Question  Mysql with special characters issue
Post: #1
I'm having trouble getting movies with danish letters (æ ø å), scrapped when using mysql database.
The same movies, when using standard sqlite, is scrapped correct, and inserted into the database.

On the mysql-server, only the bind-address was changed, and the mysql database was created by xbmc.
English titles, with no special characters, are scrapped fine, and put in the mysql database.

Can someone shed some light on this? Smile

Here's debug logs, with mysql and sqlite, testet on 3 movies. (2 with special characters, 1 without).

XBMC-mysql-log

XBMC-sqlite-log

XBMC 11.0 Eden (Ubuntu 12.04) - My i3 Build + guide/tips
Sony 46" HX723 - NAD T 744 AV. B&W DM602 S3....and the coolest air guitar in town!
find quote
dnixx Offline
Junior Member
Posts: 39
Joined: Dec 2011
Reputation: 5
Post: #2
Error code 1062 is for duplicate entry; the movie path already exists in the database. XBMC probably reads the title differently than the way it's stored in the DB.

For example, if XBMC reads "Det.Fors?For?1993" and stores "Det Forsømte Forår" in the DB, XBMC will try to add it anew everytime you run the scraper.

This is what I can gather from the logs. I don't use MySQL with XBMC myself, though, so I'm not sure on how to solve your problem. But you could do the following in order to obtain some information that might help the other viewers of this thread:

Type (in a terminal window):

Code:
mysql -uxbmc -p
(enter password "xbmc" without quotes)
Code:
use xbmc_video;
explain path;
show FULL columns from path;
find quote
morfeus Offline
Member
Posts: 74
Joined: Aug 2009
Reputation: 1
Post: #3
That makes sense, dnixx.
I started with fresh databases, both mysql and sqlite, while creating thoose debug logs.
And after scraping with mysql enabled, there's only one entry. The movie without special characters.

I've put the output together, of what you suggested, with a select from the "movie" table also:

http://pastebin.com/raw.php?i=mg3MJ5y9

XBMC 11.0 Eden (Ubuntu 12.04) - My i3 Build + guide/tips
Sony 46" HX723 - NAD T 744 AV. B&W DM602 S3....and the coolest air guitar in town!
find quote
dnixx Offline
Junior Member
Posts: 39
Joined: Dec 2011
Reputation: 5
Post: #4
But I assume that the paths have been added to the "path" table, even if the movie files aren't, right? Your log says that XBMC tries to add the paths twice.

Quote: DEBUG: Mysql execute: insert into path (idPath, strPath, strContent, strScraper) values (NULL,'/home/xbmc/share/mediacenter/movies/test/De.Gr�Slagtere.2003/','','')
DEBUG: VideoInfoScanner: Adding new item to movies:/home/xbmc/share/mediacenter/movies/test/De.Gr�Slagtere.2003/De.Gr�Slagtere.2003.iso
...
DEBUG: Mysql execute: insert into path (idPath, strPath, strContent, strScraper) values (NULL,'/home/xbmc/share/mediacenter/movies/test/De.Gr�Slagtere.2003/','','')
ERROR: SQL: Undefined MySQL error: Code (1062)
Query: insert into path (idPath, strPath, strContent, strScraper) values (NULL,'/home/xbmc/share/mediacenter/movies/test/De.Gr�Slagtere.2003/','','')
ERROR: AddPath unable to addpath (insert into path (idPath, strPath, strContent, strScraper) values (NULL,'/home/xbmc/share/mediacenter/movies/test/De.Gr�Slagtere.2003/','',''))

Could you dump the "path" table as well? (select * from path)
(This post was last modified: 2012-05-01 21:00 by dnixx.)
find quote
morfeus Offline
Member
Posts: 74
Joined: Aug 2009
Reputation: 1
Post: #5
dnixx, you're spot on Smile

http://pastebin.com/raw.php?i=k0jQV8nk

But i wonder why it fails to add info from the movies into the other tables, ex. movie?
Is there such af huge difference between mysql and sqlite, in this scenario?

Ahh, and i cut's off the movie title, when the special character appears.
That's not right Sad

XBMC 11.0 Eden (Ubuntu 12.04) - My i3 Build + guide/tips
Sony 46" HX723 - NAD T 744 AV. B&W DM602 S3....and the coolest air guitar in town!
(This post was last modified: 2012-05-01 21:14 by morfeus.)
find quote
dnixx Offline
Junior Member
Posts: 39
Joined: Dec 2011
Reputation: 5
Post: #6
Okay, how about
Code:
use xbmc_video60;
show create table path;
show variables like "character_set_database";
show variables like "collation_database";

I'm wondering about the character set, and I assume there's not an issue with XBMC's connection link to MySQL.
find quote
morfeus Offline
Member
Posts: 74
Joined: Aug 2009
Reputation: 1
Post: #7
You could be right. I haven't touched the database. It was created by XBMC.
Only thing I've changed, is my locale, when installing Ubuntu.

This is the output:

http://pastebin.com/raw.php?i=iMRuKY5s

Also, my local is:
$ echo $LANG
en_US.ISO-8859-1

XBMC 11.0 Eden (Ubuntu 12.04) - My i3 Build + guide/tips
Sony 46" HX723 - NAD T 744 AV. B&W DM602 S3....and the coolest air guitar in town!
find quote
dnixx Offline
Junior Member
Posts: 39
Joined: Dec 2011
Reputation: 5
Post: #8
Did XBMC create the database, or only the tables?

Clean the database and change the character set:
Code:
alter table path character set latin1 collate latin1_general_ci;

Or, if you're going to drop and recreate the database:
Code:
CREATE DATABASE xbmc_video CHARACTER SET latin1 COLLATE latin1_general_ci;
(The DB name is different ... xbmc_video60?)

Edit: If you *did* create the database yourself, it might be easier to set it up from scratch again with the correct character sets as described above. There will probably be traces of the old setup that might cause problems.
(This post was last modified: 2012-05-02 13:42 by dnixx.)
find quote
morfeus Offline
Member
Posts: 74
Joined: Aug 2009
Reputation: 1
Post: #9
Really appreciate your help dnixx Smile
Only thing I've created, was the mysql 'xbmc' user.
In advancedsettings.xml I've put in this:

Code:
<videodatabase>
    <type>mysql</type>
    <host>192.168.1.55</host>
    <port>3306</port>
    <user>xbmc</user>
    <pass>xbmc</pass>
    <name>xbmc_video</name>
  </videodatabase>

And when XBMC starts up, it creates both the database and tables.

I tried dropping the old database, started XBMC, a new xbmc_video60 database was created. Then i did:
Code:
alter table path character set latin1 collate latin1_general_ci;
But the special characters movies, didn't get scanned. Still only the one movie, without special characters.

I also tried:
Code:
CREATE DATABASE xbmc_video60 CHARACTER SET latin1 COLLATE latin1_general_ci;
But then XBMC just freezes at startup, and doesn't create tables in that empty database. The "60" is the xbmc-database-version-number i think.

Really hope this can be solved, because, the setup with centralized database works great and fast...but I'm missing alot of movies in the database, because of this problem with the special characters. It's gonna be such a sweet setup...i hope Smile

XBMC 11.0 Eden (Ubuntu 12.04) - My i3 Build + guide/tips
Sony 46" HX723 - NAD T 744 AV. B&W DM602 S3....and the coolest air guitar in town!
(This post was last modified: 2012-05-02 14:36 by morfeus.)
find quote
dnixx Offline
Junior Member
Posts: 39
Joined: Dec 2011
Reputation: 5
Post: #10
I tried to reproduce the problem, but everything works for me. XBMCbuntu 11.0, fresh install.

Code:
sudo apt-get install mysql-server

Code:
mysql -uroot -p
CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
GRANT ALL ON *.* TO 'xbmc';
exit;

(Creating the DB beforehand didn't work for me neither; I had to let XBMC create it.)

Added the following to advancedsettings.xml (inside <advancedsettings></advancedsettings> of course):
<videodatabase>
<type>mysql</type>
<host>localhost</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</videodatabase>

<musicdatabase>
<type>mysql</type>
<host>localhost</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</musicdatabase>

(localhost because the mysql server is running on the same machine as the xbmc client)

Restarted xbmc.

Code:
mysql -uxbmc -p
show databases;
+--------------------+
| Database           |
+--------------------+
| MyMusic18          |
| MyVideos60         |
+--------------------+

Notice that I didn't specify a DB name in advancedsettings.xml, so XBMC created "MyVideos60".

I didnt change any character sets. Many people have been talking about setting the character set to latin1 and collation to latin1_general_ci, but I didn't have to do anything in order to get it to work:

Code:
mysql> show variables like "character_set_database";
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+

mysql> show variables like "collation_database";
+--------------------+-------------------+
| Variable_name      | Value             |
+--------------------+-------------------+
| collation_database | latin1_swedish_ci |
+--------------------+-------------------+

mysql> show create table path;
| path  | CREATE TABLE `path` (
  `idPath` int(11) NOT NULL AUTO_INCREMENT,
  `strPath` text,
  `strContent` text,
  `strScraper` text,
  `strHash` text,
  `scanRecursive` int(11) DEFAULT NULL,
  `useFolderNames` tinyint(1) DEFAULT NULL,
  `strSettings` text,
  `noUpdate` tinyint(1) DEFAULT NULL,
  `exclude` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`idPath`),
  UNIQUE KEY `ix_path` (`strPath`(255))
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

use MyVideos60;
mysql> select idPath,strPath from path;
+--------+---------------------------------------+
| idPath | strPath                               |
+--------+---------------------------------------+
|      1 | /                                     |
|      2 | /home/xbmc/Movies/                    |
|      3 | /home/xbmc/Movies/American.History.X/ |
|      4 | /home/xbmc/Movies/Det.Fors�mte.For�r/ |
+--------+---------------------------------------+

mysql> select idFile, c00 from movie;
+--------+--------------------+
| idFile | c00                |
+--------+--------------------+
|      2 | American History X |
|      3 | Det Fors�mte For�r |
+--------+--------------------+

I've seen people on these boards talking about latin1 and latin1_general_ci multiple times, but I'm not really sure why. This was a fresh install and I followed the wiki which doesn't mention character sets.

If this post doesn't help you I'm afraid you'll have to hope for someone else to answer.
(This post was last modified: 2012-05-02 22:21 by dnixx.)
find quote
morfeus Offline
Member
Posts: 74
Joined: Aug 2009
Reputation: 1
Post: #11
Bingo! Problem solved Big Grin
Your succes with the fresh install, got me thinking, that the only difference, was probably locale.
I was using ISO-8859-1. And the movie-filnames was ISO-8859-1 too.
Somehow, this has worked great for years, with sqlite.
I've now switched to UTF-8 (like the rest of the world Blush ) , converted the filenames to UTF-8,
and movies now gets inserted correct in the mysql db.

Thank you so much for all your trouble and help dnixx, much appreciated! Smile

XBMC 11.0 Eden (Ubuntu 12.04) - My i3 Build + guide/tips
Sony 46" HX723 - NAD T 744 AV. B&W DM602 S3....and the coolest air guitar in town!
find quote
dnixx Offline
Junior Member
Posts: 39
Joined: Dec 2011
Reputation: 5
Post: #12
Great to hear, morfeus! Smile
find quote