Guest - Testers are needed for the reworked CDateTime core component. See... https://forum.kodi.tv/showthread.php?tid=378981 (September 29) x
  • 1
  • 13
  • 14
  • 15(current)
  • 16
  • 17
  • 26
[MYSQL] HOW-TO: 5 User XBMC
What does the "TRIGGER FIX FOR MULT USERS" part actually do?
Reply
@john_es

Yes you could use Main and X numbers, It's just how I like it with names and if I need to pull data from the DB I don't need to remember who is who Smile
If I get to it I might do up a little script to sub in the names.

"This has only been Tested on a New Setup"
This means that using Kodi to upgrade the DB will prob fail as there are new tables and columns, and files is a view now not a table as its been renamed to globalfiles

PHP Code:
/* TRIGGER FIX FOR MULT USERS */
  
DROP TRIGGER IF EXISTS `Kodi_Test_Matt_Video_99`.`delete_file`;
  
DELIMITER $$
  
/* REPLACE ---> KODI <--- WITH YOUR MYSQL LOGIN UID FROM THE ADVANCEDSETTINGS.XML */
  
CREATE DEFINER=`KODI`@`%TRIGGER `Kodi_Test_Matt_Video_99`.`delete_fileAFTER DELETE
  ON 
`Kodi_Test_Matt_Video_99`.`globalfiles`
  FOR 
EACH ROW
  BEGIN
    DELETE FROM 
`Kodi_Test_Matt_Video_99`.bookmark WHERE idfile old.idfile;
    
DELETE FROM `Kodi_Test_Mace_Video_99`.bookmark WHERE idfile old.idfile;
    
DELETE FROM `Kodi_Test_Kiyana_Video_99`.bookmark WHERE idfile old.idfile;
    
DELETE FROM `Kodi_Test_Jaide_Video_99`.bookmark WHERE idfile old.idfile;
    
DELETE FROM settings WHERE idfile old.idfile;
    
DELETE FROM stacktimes WHERE idfile old.idfile;
    
DELETE FROM streamdetails WHERE idfile old.idfile;
  
END$$
  
DELIMITER 

This is a clean-up Trigger the default Kodi install has it, This will remove any bookmarks, settings, stacktimes, and streamdetails info when you remove a file from the globalfiles.
  • bookmarks [Where you leave an unfinished video]
  • settings [Subtitles On, Zoom, Audio Stream data etc (If you turn Subtitles off and another user goes to watch it its off for them too)]
  • stacktimes [Stack times for Stacked files (The ones that are like CD1, CD2, etc)]
  • streamdetails [Video info Codec, Width, Hight, Video Duration, etc]

If you are like me and keep everything then this isn't really needed but I know some people delete stuff and this will keep the DB tidy.

Hope that answers your questions
Reply
I attempted to use the 99 instructions, didnt get any errors when using the sql. But kodi seems to fail at inserting data into the views.
i get errors like this when i attempt to scan files

18:19:34 T:944 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into files (idFile, idPath, strFileName) values(NULL, 8, lots-tfoftr.mkv')
18:19:34 T:944 ERROR: CVideoDatabase::AddFile unable to addfile ()

and

18:19:17 T:5900 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/')
18:19:17 T:5900 ERROR: CVideoDatabase::AddPath unable to addpath (insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/'))

when attempting to change content type of a source this works when i use the master database in the advancedsettingsfile scanning for content does not
Reply
Big thanks to BigMong.

I have been stuck on Kodi 14 multi-user setup, postponing the upgrade because of all the work to figure out the new database schema and views. You made my life a lot easier, thanks!

Next step is to upgrade to Kodi 16.1, but since all my devices run on OpenELEC, I'll wait another month or so as it is still in beta (OpenELEC 7.0 beta 3).

BTW, I too use trakt.tv and can recommend it to anyone. The free version is sufficient for most any user, but I decided to become a VIP ($2.50/month).
Reply
@npbhdk
(2016-05-04, 18:23)npbhdk Wrote: I attempted to use the 99 instructions, didnt get any errors when using the sql. But kodi seems to fail at inserting data into the views.
i get errors like this when i attempt to scan files

18:19:34 T:944 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into files (idFile, idPath, strFileName) values(NULL, 8, lots-tfoftr.mkv')
18:19:34 T:944 ERROR: CVideoDatabase::AddFile unable to addfile ()

and

18:19:17 T:5900 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/')
18:19:17 T:5900 ERROR: CVideoDatabase::AddPath unable to addpath (insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/'))

when attempting to change content type of a source this works when i use the master database in the advancedsettingsfile scanning for content does not
If I understand right, you are using all systems Master and Slaves to scan for media? (I have found its best to use only the one system Master or Slave to import, as when you do a scan on a different user it will redo the whole TV Shows as the Hash changes (Haven't looked too far into why that is))

By the looks of the errors its trying to INSERT into a non insertable view, I would check that you can view data in that view "files" that is linked to "globalfiles" does the Master and Slave connect to the DB with the same Login? Does the login being used have All Privileges to the view?

@RonM

Thank You, Glad you are loving it, OpenELEC on some RPI2 or 3's work awesome around the house, and now a few streets over, over a PTP WiFi network Smile
Reply
(2016-05-05, 16:06)BigMong Wrote: @npbhdk
(2016-05-04, 18:23)npbhdk Wrote: I attempted to use the 99 instructions, didnt get any errors when using the sql. But kodi seems to fail at inserting data into the views.
i get errors like this when i attempt to scan files

18:19:34 T:944 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into files (idFile, idPath, strFileName) values(NULL, 8, lots-tfoftr.mkv')
18:19:34 T:944 ERROR: CVideoDatabase::AddFile unable to addfile ()

and

18:19:17 T:5900 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/')
18:19:17 T:5900 ERROR: CVideoDatabase::AddPath unable to addpath (insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/'))

when attempting to change content type of a source this works when i use the master database in the advancedsettingsfile scanning for content does not
If I understand right, you are using all systems Master and Slaves to scan for media? (I have found its best to use only the one system Master or Slave to import, as when you do a scan on a different user it will redo the whole TV Shows as the Hash changes (Haven't looked too far into why that is))

By the looks of the errors its trying to INSERT into a non insertable view, I would check that you can view data in that view "files" that is linked to "globalfiles" does the Master and Slave connect to the DB with the same Login? Does the login being used have All Privileges to the view?

@RonM

Thank You, Glad you are loving it, OpenELEC on some RPI2 or 3's work awesome around the house, and now a few streets over, over a PTP WiFi network Smile

Ive tried both scanning on the master and the slave, neither works and yes they use same database login, ive tried logging into the database with a gui client and i can insert data into the view with that, and i can see it aswell. Not really sure why it fails with kodi Sad The mysql user has these priviliges (GRANT ALL PRIVILEGES ON *.* TO 'xbmc'@'%' WITH GRANT OPTION) so if got it right(havent used mysql much) it should be able to do everything like root can tried to use root login didnt work either same error.
Reply
Edit: "question was dump^^"
Reply
Got it. Thanks anyways for the awesome script Smile
Reply
@BigMong

I use the default Kodi Watch Status.
pressing W or Context menu Mark as Watched.

Once I have my "Matt" DB created I open Kodi and test the Watch Status work.

After I implement the code:
Code:
RENAME TABLE `Sushi99`.`files` TO `Sushi99`.`globalfiles`;
  ALTER TABLE `Sushi99`.`globalfiles` CHANGE playCount playCountSushi INT(11);
  ALTER TABLE `Sushi99`.`globalfiles` CHANGE lastPlayed lastPlayedSushi TEXT;
  ALTER TABLE `Sushi99`.`globalfiles` ADD playCountChloe INT(11) AFTER lastPlayedSushi;
  ALTER TABLE `Sushi99`.`globalfiles` ADD lastPlayedChloe TEXT AFTER playCountChloe;
  ALTER TABLE `Sushi99`.`globalfiles` ADD playCountFelicia INT(11) AFTER lastPlayedChloe;
  ALTER TABLE `Sushi99`.`globalfiles` ADD lastPlayedFelicia TEXT AFTER playCountFelicia;

  CREATE VIEW `Sushi99`.`files` AS SELECT
   `Sushi99`.`globalfiles`.`idFile` AS `idFile`,
   `Sushi99`.`globalfiles`.`idPath` AS `idPath`,
   `Sushi99`.`globalfiles`.`strFilename` AS `strFilename`,
   `Sushi99`.`globalfiles`.`playCountSushi` AS `playCount`,
   `Sushi99`.`globalfiles`.`lastPlayedSushi` AS `lastPlayed`,
   `Sushi99`.`globalfiles`.`dateAdded` AS `dateAdded`
  FROM `Sushi99`.`globalfiles`;

The watched status does not function anymore.

From the Log:
Code:
16:48:27 T:9872   DEBUG: CInputManager::OnKey: w (0xf057) pressed, action is ToggleWatched
16:48:27 T:2948   DEBUG: Mysql Start transaction
16:48:27 T:2948   DEBUG: Mysql execute: update files set playCount=NULL,lastPlayed=NULL where idFile=2
16:48:27 T:2948   ERROR: SQL: [sushi99] Undefined MySQL error: Code (1288)
                                            Query: update files set playCount=NULL,lastPlayed=NULL where idFile=2
16:48:27 T:2948   ERROR: CVideoDatabase::SetPlayCount failed
16:48:27 T:2948   DEBUG: Mysql commit transaction

I have narrowed it down to this code, and this is the first and only SQL Queries I have run.
I believe Kodi 16.1 Jarvis has made some changes that after we ALTER the Tables/Views this breaks.

This was working on 15 Isengrad
Reply
@Sushi7

Is this only when you are marking as un-watched?
does it work marking as watch or watching the video?

I will do some testing

also what version of MySQL are you running?

Mine is running on an older one "Server version: 5.5.40-0ubuntu1 - (Ubuntu)"
Reply
MySQL 5.7 Windows
Kodi 16.1 Jarvis
Windows 10 Pro

Trying to mark or unmark Watched does not work but watching the Movie all the way through automatically marks as watched

MySQL states error 1288:
Error: 1288 SQLSTATE: HY000 (ER_NON_UPDATABLE_TABLE)
Message: The target table %s of the %s is not updatable

https://dev.mysql.com/doc/refman/5.5/en/...erver.html

I have also checked the IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table and it is set properly by default.
http://dev.mysql.com/doc/refman/5.7/en/v...ility.html

If I run the query manually directly in SQL on the View Files it works
Code:
update files set playCount=NULL,lastPlayed=NULL where idFile=2

Just not from within Kodi
Reply
@Sushi7

I spotted that error and I have tested the manually running the SQL
it's strange that it works when your watch an episode but that is calling a different SQL string

After a few mins looking at the source on GitHub https://github.com/xbmc/xbmc

In the /xbmc/video/VideoDatabase.cpp file, line 5155 I believe is the SQL that is Prepared
Code:
if (!date.IsValid())
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed=NULL where idFile=%i", id);
      else
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed='%s' where idFile=%i", date.GetAsDBDateTime().c_str(), id);
    }

I will setup my 16.1 Kodi again and do some testing over the weekend
Reply
(2016-06-28, 14:43)BigMong Wrote: @Sushi7

I spotted that error and I have tested the manually running the SQL
it's strange that it works when your watch an episode but that is calling a different SQL string

After a few mins looking at the source on GitHub https://github.com/xbmc/xbmc

In the /xbmc/video/VideoDatabase.cpp file, line 5155 I believe is the SQL that is Prepared
Code:
if (!date.IsValid())
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed=NULL where idFile=%i", id);
      else
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed='%s' where idFile=%i", date.GetAsDBDateTime().c_str(), id);
    }

I will setup my 16.1 Kodi again and do some testing over the weekend

Have you made any progress??
Reply
Tongue 
(2016-07-28, 11:28)gaxander Wrote:
(2016-06-28, 14:43)BigMong Wrote: @Sushi7

I spotted that error and I have tested the manually running the SQL
it's strange that it works when your watch an episode but that is calling a different SQL string

After a few mins looking at the source on GitHub https://github.com/xbmc/xbmc

In the /xbmc/video/VideoDatabase.cpp file, line 5155 I believe is the SQL that is Prepared
Code:
if (!date.IsValid())
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed=NULL where idFile=%i", id);
      else
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed='%s' where idFile=%i", date.GetAsDBDateTime().c_str(), id);
    }

I will setup my 16.1 Kodi again and do some testing over the weekend

Have you made any progress??

Yep I'm getting the same issue and still can't figure it out after spending most of the night on it!
Reply
I have decided to use Isengrad 15 to be able to use the Watch status correctly.
Reply
  • 1
  • 13
  • 14
  • 15(current)
  • 16
  • 17
  • 26

Logout Mark Read Team Forum Stats Members Help
[MYSQL] HOW-TO: 5 User XBMC5
This forum uses Lukasz Tkacz MyBB addons.