[zephura]

Hello all,

This post to give the information how I manage the totalcount issue with mariaDB galera Cluster.

If you know mariaDB galera cluster, you probably know that it hardly handle nullif(count(...) ,0) (in fact it doesn't work well).
It seems with the version 10.1.11, this issue was fixed, but with version 10.1.12, this issue came back.

I NEEDED the version 10.1.12, otherwise all the view of mysql using nullif were corrupted (it was a well known issue of mariaDB).

howether with the version 10.1.12, every number of episods of a tv show was counted twice
(for example, if you have a tvshow with 7 episods, the totalcounts of episods was 7*2-1 = 13).

2 solutions :
1/ wait for a new update of mariaDB
2/ find a tweak to fix this until something better.

I am not fan of solution 1,
here is my solution 2

I modified the view `tvshowcounts` to display the right number of total episods.

Here is the original code not displaying the correct number of episods

Code:

VIEW `tvshowcounts` AS select `tvshow`.`idShow` AS `idShow`,max(`files`.`lastPlayed`) AS `lastPlayed`,nullif(count(`episode`.`c12`),0) AS `totalCount`,count(`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `episode`.`c12`),0) AS `totalSeasons`,max(`files`.`dateAdded`) AS `dateAdded` from ((`tvshow` left join `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`

I tweaked the view by adding 0 to the count of episods : in the part nullif(count(`episode`.`c12`),0) AS `totalCount` like this nullif(count(`episode`.`c12`) + 0,0) AS `totalCount`

To be true, I don't know what is happening, but this way, the VIEW display the right number of totalepisods

Here is the tweakedcode displaying the correct number of episods

Code:

VIEW `tvshowcounts` AS select `tvshow`.`idShow` AS `idShow`,max(`files`.`lastPlayed`) AS `lastPlayed`,nullif((count(`episode`.`c12`) + 0),0) AS `totalCount`,count(`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `episode`.`c12`),0) AS `totalSeasons`,max(`files`.`dateAdded`) AS `dateAdded` from ((`tvshow` left join `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`

Now how to use this tweak....
If you have understood what I am speaking about, you probably already know.
If you don't know, wait a moment.....

If you have NOT understood what I am speaking about, I will describe how to modify the SQL database "MyVideos99",
I know how to backup database, I know 1 or 2 thing in SQL, and you??Are you sure you want to do this
Well then, DO THIS AT YOUR OWN RISK
If something goes wrong, you'll need to recreate your database!!!

To be honnest, the process is easy:
0- test the view
1- drop the view
2- create the view
3- test the view

I suppose :
- your MariaDB server is on the machine 192.168.10.10 on port 3306
- you've defined a powerful user named 'kodi' (a user that can at least manipulate the table structure including the views)
- this user kodi is connecting using the password 'kodi'
- the name of the video database is MyVideos99

If not, this means you are clever enough to tweak advancedsettings.xml, so update the following code as needed

0- Test the view

Code:

# mysql -ukodi -pkodi -h 192.168.10.10 MyVideos99 -e "select * from tvshowcounts limit 5;"
+--------+------------+------------+--------------+--------------+---------------------+
| idShow | lastPlayed | totalCount | watchedcount | totalSeasons | dateAdded           |
+--------+------------+------------+--------------+--------------+---------------------+
|      3 | NULL       |        355 |            0 |            8 | 2015-02-26 00:04:44 |
|      6 | NULL       |         59 |            0 |            5 | 2013-09-09 23:24:02 |
|      9 | NULL       |         43 |            0 |            2 | 2015-03-26 14:33:46 |
|     12 | NULL       |         11 |            0 |            1 | 2014-11-01 18:24:01 |
|     15 | NULL       |        137 |            0 |            3 | 2015-09-12 16:10:43 |
+--------+------------+------------+--------------+--------------+---------------------+

Of course, those are from MY database..... yours will be different...
Well the totalCount is false

1- drop the view

Code:

# mysql -ukodi -pkodi -h 192.168.10.10 MyVideos99 -e "drop view tvshowcounts;"

That was the easy part

2- create the view
The interesting part....
Create a file containing the tweak (for example the file tvshowcounts-tweaked.sql)

Code:

# cat tvshowcounts-tweaked.sql
CREATE VIEW `tvshowcounts` AS select `tvshow`.`idShow` AS `idShow`,max(`files`.`lastPlayed`) AS `lastPlayed`,nullif(count(`episode`.`c12`)+0,0) AS `totalCount`,count(`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `episode`.`c12`),0) AS `totalSeasons`,max(`files`.`dateAdded`) AS `dateAdded` from ((`tvshow` left join `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

DON'T FORGET ";" at the end of the command line!!!
Insert the tweak

Code:

# mysql -ukodi -pkodi -h 192.168.10.10 MyVideos99 < tvshowcounts-tweaked.sql

3- test the view

Code:

# mysql -ukodi -pkodi -h 192.168.10.10 MyVideos99 -e "select * from tvshowcounts limit 5;"
+--------+------------+------------+--------------+--------------+---------------------+
| idShow | lastPlayed | totalCount | watchedcount | totalSeasons | dateAdded           |
+--------+------------+------------+--------------+--------------+---------------------+
|      3 | NULL       |        178 |            0 |            8 | 2015-02-26 00:04:44 |
|      6 | NULL       |         30 |            0 |            5 | 2013-09-09 23:24:02 |
|      9 | NULL       |         22 |            0 |            2 | 2015-03-26 14:33:46 |
|     12 | NULL       |          6 |            0 |            1 | 2014-11-01 18:24:01 |
|     15 | NULL       |         69 |            0 |            3 | 2015-09-12 16:10:43 |
+--------+------------+------------+--------------+--------------+---------------------+

NOW, the number of total episods is correct.


nota bene of course, checking if it works is not mandatory,
of course, you can do this inside mysql.
of course you can do this with 1 script (drop, then create)
this post was to display the more information as possible to understand step by step how to do this as safe as possible.

[zerocool_ie]

thanks!

[marantz]

For anyone Who read this its fixed in 10.1.13