Mariadb unicode issues

Help and Support
1

Hey, I’ve got a long-existing mariadb database for my handful of osmc devices, and for the most part things are fine, except for files with unicode characters in them.

Sample error when playing a file with a special character:

2024-01-17 21:47:04.935 T:2968     info <general>: VideoPlayer::OpenFile: /tbla/video/tv/The 100/season02/The 100 - S02E11 - Coup de Grâce.mkv
2024-01-17 21:47:04.946 T:3149     info <general>: Creating InputStream
2024-01-17 21:47:05.068 T:3149    error <general>: SQL: [xbmc_video121] Undefined MySQL error: Code (1267)
                                                   Query: SELECT allAudio FROM path WHERE allAudio IS NOT NULL AND instr('/tbla/video/tv/The 100/season02/The 100 - S02E11 - Coup de Grâce.mkv', strPath) = 1 ORDER BY length(strPath) DESC LIMIT 1

Titles for a bunch of movies with special characters don’t show, and when trying to refresh to add, I get a similar

2024-01-15 10:05:52.218 T:2957    error <general>: SQL: [xbmc_video121] Undefined MySQL error: Code (1366)

I’ve tried a few things to fix this, mostly centred around googling and trying to set the character/colation on the tables in question.

The server is MariaDB version 10.6.16

But yes, I tried

alter table movie character set = utf8mb4 collate = utf8mb4_unicode_ci;

And also tried at the server level with

collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

I know it’s not osmc specific, but I’d love any help to be able to get unicode characters working

Thanks!

2

I’m running MariaDB on the backend and haven’t ever had an issue with unicode characters in file names. Here’s my ini file.

[mysqld]
port=3306

key_buffer_size=16k
skip_name_resolve=1
query_cache_size=32M
query_cache_limit=2M
optimizer_search_depth=1
character-set-server=utf8
innodb_compression_default=OFF
innodb_log_buffer_size=32M

Maybe try just the character-set-server and use plain utf8? Because you altered a table, it’s possible that won’t work though. If it doesn’t, you might have to export the data, delete the Kodi databases, let Kodi create new empty ones, and reimport everything.

3

I haven’t had an issue either and that includes the file name in question from the first post. I am also using that utf8 setting…

[mysqld]
datadir=C:/Program Files/MariaDB 10.4/data
port=3306
innodb_buffer_pool_size=2G
character-set-server=utf8
skip_name_resolve=1
optimizer_search_depth=0
#key_buffer_size=16k
#host_cache_size=16

# Query Cache Configuration
query_cache_limit=2M
query_cache_size=32M

# InnoDb optimizations
innodb_buffer_pool_chunk_size=1G
innodb_buffer_pool_instances=2
#innodb_adaptive_hash_index=OFF
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.4/lib/plugin

The issue is on Kodi’s radar it would seem for Kodi v21…

4

Thanks, I’ve updated my config to use this, and want to try the suggestion of resetting back to vanilla schema. I’ve dumped my data without the create commands, but now can’t seem to figure out how to get kodi to recreate.

If I drop the db and create a blank new one, I just get

2024-01-18 12:07:30.144 T:3047    error <general>: SQL: [xbmc_video121] The table does not exist
                                                   Query: SELECT idVersion FROM version

I’d have hoped it’d detect the missing tables and recreate them, but I guess not?

5

Ahh, I was too clever in creating the database for it.

OK things look good here. I had to re-import and re-do the dump with --skip-triggers flag which I’d forgotten about, but I was eventually able to get a nice fresh schema and re-import the data.

A quick test of playing that file worked fine.
Now just need to try updating all the movies/tv with special characters and see if they get updated in the database correctly.

Is there a good way to trigger that for a reasonable number of files (100 or so) rather than one by one refreshing in the gui?

6

If they didn’t scrape in the first place doesn’t just updating the library add them?

7

They scraped, just have an empty c00 field

8

Can you formulate at smart playlist, widget, or library filter to match them? If so then maybe use that to find them and delete them from the library (making sure you have the file deletion option disabled so you don’t have to deal with the second dialog box). It might be kind of tedious but it you keymapped a button to delete them it could go pretty quick. Something like this…

<?xml version="1.0" encoding="UTF-8"?>
<keymap>
	<global>
		<keyboard>
			<delete>Delete</delete>
		</keyboard>
	</global>
	<yesnodialog>
		<keyboard>
			<delete>SendClick(11)</delete>
		</keyboard>
	</yesnodialog>
</keymap>

which with a keyboard should allow you to press the delete key twice to delete a highlighted item and press yes in the confirmation dialog.

9

I actually tried to be clever and removed them from movie files and path tables with some SQL queries, thinking that then an updateLibrar(video) would re-add them, but that doesn’t seem to be happening :smiley:

10

Depending upon the type of cleanup you are trying tp do you might want to look at the Kodi Selective Cleaner addon I wrote. It may help

Jeff

11

Wow, reading that sounds just like what I need.
However when I tried to run it:

2024-01-19 10:24:56.620 T:6684    error <general>: EXCEPTION IN (/home/osmc/.kodi/addons/plugin.program.kscleaner/resources/lib/common.py, LINE 97 "tree = ET.fromstring((xml_txt.read().replace('&',' ').encode('utf-8')), ET.XMLParser(encoding='utf-8'))"): XML or text declaration not at start of entity: line 1, column 1
2024-01-19 10:24:56.620 T:6684     info <general>: There is a problem with your MySQL configuration

My only config was to tell it ‘mysql’ for video/audio and enter the db names

12

Well I assume it refers to your Kodi Mysql config.
share complete logs and people can tell.

13

Those … are the complete logs

2024-01-19 10:24:56.536 T:6684     info <general>: KS Cleaner logging database check successful.  Addon started.
2024-01-19 10:24:56.558 T:6684     info <general>: Kodi Selective Cleaner Python vesion is: 3.9.2
2024-01-19 10:24:56.584 T:6684     info <general>: KS Cleaner music DB trigger checking is disabled
2024-01-19 10:24:56.620 T:6684    error <general>: EXCEPTION IN (/home/osmc/.kodi/addons/plugin.program.kscleaner/resources/lib/common.py, LINE 97 "tree = ET.fromstring((xml_txt.read().replace('&',' ').encode('utf-8')), ET.XMLParser(encoding='utf-8'))"): XML or text declaration not at start of entity: line 1, column 1
2024-01-19 10:24:56.620 T:6684     info <general>: There is a problem with your MySQL configuration
14

Complete set via MyOSMC log uploader and share the URL then we see the Mysql config

15

I just had a little test with it on my PC. I found that it didn’t work in a profile and for the database name you had to (confusingly) supply the database version in the name so in my case my name tag in my advancedsettings.xml is “John” but because Kodi tags on the database version for the Major version of Kodi your running I needed to set the name in this add-on to “John121”.

16

https://paste.osmc.tv/uqeculisaj

My database name in the config is xbmc and I told the cleaner it was xbmc_video121

17

Sorry for the delay responding. I am on a cruise ship so I have other interests right now :slight_smile: Anyway, the issue appears to be my XML parsing code for your advancedsettings.xml file. I think these two lines are causing the issue:

                                                 <cputempcommand>/bin/sed -e &apos;s/\([0-9]*\)[0-9]\{3\}.*/\1 C/&apos; /sys/class/thermal/thermal_zone0/temp</cputempcommand>
                                                 <gputempcommand>/bin/sed -e &apos;s/\([0-9]*\)[0-9]\{3\}.*/\1 C/&apos; /sys/class/thermal/thermal_zone0/temp</gputempcommand>

You might try removing them when you run the cleaner and see if that fixes the issue. Someone once suggested I remove the XML parser and just let folks enter the config info manually into the addon. I tried to make it friendly but have run into other things in the XML file I hadn’t expected.

If removing these lines work, I’ll look to fix in the code when I return. I see one other error check I need to add.

Thanks,

Jeff

18

Those lines are not from the users advancedsettings.xml file but rather a system level one and they are responsible for feeding temperature info into Kodi.

19

Ok. Maybe I read the debug log wrong. I was trying to read from a cellphone screen.

Regardless the fault is an XML parsing error. I’ll likely need to get a copy of the advancedsettings.xml file and see why it isn’t parsing properly.

Thanks,

Jeff

20

You can get it from the log file he posted. At the start of Kodi.log it outputs both the system level and userdata advancedsettings.xml files one after the other. They are listed after…

Contents of special://xbmc/system/advancedsettings.xml are...
and
Contents of special://profile/advancedsettings.xml are...
respectively.