[zephura]

Hi all (and merry Xmas )

Please don't ask why, I wanted to use a replicated MySQL cluster to feed Kodi and it seems there are some replication issue.
I had 2 main ideas behind this : 1st high-availability 2nd I wanted to perform some backup of my databases (not through the MariDB replication of course, but from one node of the cluster-dedicated to backup)

Here are some useful informations:

• KODI 15.2 is run on an updated OSMC for Raspberry
  
• GALERA cluster is run on MariaDB 10.1
  Code:

  # cat /etc/mysql/conf.d/galera.cnf
[galera]
#mysql settings
.....

#galera settings
wsrep_on = ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="mariadb_cluster"
wsrep_cluster_address="gcomm://192.168.0.14,192.168.0.13,192.168.0.4"
wsrep_sst_method=rsync
wsrep_sync_wait=7
  I cutted the mandatory options to keep the useful info : the replication is performed using rsync (maybbe the point...)
  I even make additional check before any new query (wsrep_sync_wait)
  
• MariaDB 10.1 are run on 3 updated Debian8.0 (jessie) (nodes : 192.168.0.4, 192.168.0.13, 192.168.0.14)
  
• my advanvancedsettings.xml file is tuned with
  Code:

  <?xml version="1.0" encoding="utf-8"?>
<advancedsettings>
        <videodatabase>
                <name>MyVideos</name>
                <host>192.168.0.4</host>
                <user>kodi</user>
                <pass>kodi</pass>
                <type>mysql</type>
                <port>3306</port>
        </videodatabase>
.....
</advancedsettings>

1st : even after some tests, be assured I don't know where the problem relies
2nd : I don't think GALERA has to be blamed (replication works fine)
3rd : I don't think that Raspberry is the major point of this thread
4th : I don't know how kodi or OSMC do manage the sql queries,

1/ tests for replications

Code:

# mysql -ukodi -pkodi -h 192.168.0.4 -e "create table MyVideos93.foobar (foo numeric, bar numeric);"
# mysql -ukodi -pkodi -h 192.168.0.4 -e "insert into MyVideos93.foobar (foo, bar) values (1,1), (2,4), (3,9), (4,16), (5,25);"
# mysql -ukodi -pkodi -h 192.168.0.13 -e "select * from MyVideos93.foobar;"
+------+------+
| foo  | bar  |
+------+------+
|    1 |    1 |
|    2 |    4 |
|    3 |    9 |
|    4 |   16 |
|    5 |   25 |
+------+------+

• On the node 1 (192.168.0.4), using the user kodi, I create the table foobar in database MyVideos93,
  
• I fill it with some data
  
• On the node 2 (192.168.0.13), using the user kodi, I check the data

replication seems to work, some more tests:

Code:

# mysql -ukodi -pkodi -h 192.168.0.4 -e "delete from MyVideos93.foobar where foo=2;"
# mysql -ukodi -pkodi -h 192.168.0.13 -e "select * from MyVideos93.foobar;"
+------+------+
| foo  | bar  |
+------+------+
|    1 |    1 |
|    3 |    9 |
|    4 |   16 |
|    5 |   25 |
+------+------+

• On the node 1 (192.168.0.4), using the user kodi, I delete some data in the table foobar in database MyVideos93
  
• On the node 2 (192.168.0.13), using the user kodi, I check the data

replication seems to work really fine.
One more test

Code:

# mysql -ukodi -pkodi -h 192.168.0.4 -e "drop table MyVideos93.foobar;"
# mysql -ukodi -pkodi -h 192.168.0.13 -e "show tables from MyVideos93;"
+----------------------+
| Tables_in_MyVideos93 |
+----------------------+
.....
| episode_view         |
| files                |
| genre                |
...
+----------------------+

I cutted the knowned table, it's it show around table foobar...

• On the node 1 (192.168.0.4), using the user kodi, I drop table foobar in database MyVideos93
  
• On the node 2 (192.168.0.13), using the user kodi, I check the table in database MyVideos93

Now I am sure the replication is perfectly working with direct queries.

2/ tests with online KODI

Code:

# mysql -ukodi -pkodi -h 192.168.0.4 -e "select * from MyVideos93.files where dateAdded>='2015-12-24';"
+--------+--------+--------------------------+-----------+------------+---------------------+
| idFile | idPath | strFilename              | playCount | lastPlayed | dateAdded           |
+--------+--------+--------------------------+-----------+------------+---------------------+
|  41099 |   1506 | Save.00x12..avi           |      NULL | NULL       | 2015-12-24 02:57:06 |
|  41108 |   1508 | Save.02x17..avi           |      NULL | NULL       | 2015-12-24 00:14:24 |
|  41111 |   1508 | Save.02x18..avi           |      NULL | NULL       | 2015-12-24 00:34:10 |
|  41114 |   1508 | Save.02x19..avi           |      NULL | NULL       | 2015-12-24 00:53:55 |
|  41117 |   1508 | Save.02x20..avi           |      NULL | NULL       | 2015-12-24 01:14:57 |
|  41120 |   1508 | Save.02x21..avi           |      NULL | NULL       | 2015-12-24 01:36:19 |
|  41123 |   1508 | Save.02x22..avi           |      NULL | NULL       | 2015-12-24 01:54:36 |
|  41126 |   1508 | Save.02x23..avi           |      NULL | NULL       | 2015-12-24 02:14:36 |
|  41129 |   1508 | Save.02x24..avi           |      NULL | NULL       | 2015-12-24 02:36:57 |
+--------+--------+--------------------------+-----------+------------+---------------------+
# mysql -ukodi -pkodi -h 192.168.0.13 -e "select * from MyVideos93.files where dateAdded>='2015-12-24';"

• I just added some file before posting this thread, using kodi (update Video Database), the values are perfectly inserted into my reference node (the node described in advancedsettings.xml - 192.168.0.4)
  
• but are not replicated into the backup node (192.168.0.13)

If someone can help at least to identify the problem...

[zephura]

Now it works..... and I don't know exactly why


In an other thread, I've perform a complete update of my Library (read complete update from scratch).

I don't know if it is linked, but now it works... (I didn't make any update of MariaDB)
Any updates performed by kodi on the "master" node (192.168.0.4) is replicated on my backup node (192.168.0.13).

For fun, I will play with heartbeat and Loadbalancing (haProxy).