+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: Tips, tricks, and step by step guides (https://forum.kodi.tv/forumdisplay.php?fid=110)
+--- Thread: [MYSQL] HOW-TO: 5 User XBMC (/showthread.php?tid=196821)
RE: [MYSQL] HOW-TO: 5 User XBMC - OtheA - 2019-08-25
Hello again. I moved some files around and tried to clean the database, but now I'm getting two references for a few videos. This error in the logs.
I did run the delete_file trigger SQL query when I set it up. Is there something else I need to do? Is this error to be expected because I'm only using 5 of the 9 databases? I think I noticed a typo in the delete_file trigger. You have Kodi_U03_Video_116 in there twice and skipped U04, so I changed that when I ran it. Was that not correct?sql:2019-08-24 18:54:57.485 T:4088872976 NOTICE: CleanDatabase: Starting videodatabase cleanup ..
2019-08-24 18:55:14.167 T:4088872976 ERROR: SQL: [Kodi_U01_Video_116] The table does not exist
Query: DELETE FROM files WHERE idFile IN (334,424,487,493,497,674,738,833,853,863,1033,1137,1297,1482,3,3,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,176,177,178,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,243,244,245,246,247,248,249,249,250,251,252,253,254,255,256,257,258,259,260,261,261,262,263,264)
2019-08-24 18:55:14.168 T:4088872976 ERROR: CleanDatabase failed
RE: [MYSQL] HOW-TO: 5 User XBMC - BigMong - 2019-08-25
(2019-08-25, 06:19)OtheA Wrote: Hello again. I moved some files around and tried to clean the database, but now I'm getting two references for a few videos. This error in the logs.Wow I did have a typo haha Thank you I have updated it
I did run the delete_file trigger SQL query when I set it up. Is there something else I need to do? Is this error to be expected because I'm only using 5 of the 9 databases? I think I noticed a typo in the delete_file trigger. You have Kodi_U03_Video_116 in there twice and skipped U04, so I changed that when I ran it. Was that not correct?sql:2019-08-24 18:54:57.485 T:4088872976 NOTICE: CleanDatabase: Starting videodatabase cleanup ..
2019-08-24 18:55:14.167 T:4088872976 ERROR: SQL: [Kodi_U01_Video_116] The table does not exist
Query: DELETE FROM files WHERE idFile IN (334,424,487,493,497,674,738,833,853,863,1033,1137,1297,1482,3,3,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,176,177,178,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,243,244,245,246,247,248,249,249,250,251,252,253,254,255,256,257,258,259,260,261,261,262,263,264)
2019-08-24 18:55:14.168 T:4088872976 ERROR: CleanDatabase failed
This just removes bookmarks of deleted files no biggie just keeping things tidy.
That's fine if your only using 5 of the 9 or increased it to 20 or whatever.
you will just need to remove lines from the tiggers and not run the user setup of the other. can leave the other cols within the "global_files" table or drop them it wont change anything.
within the database "Kodi_U01_Video_116" do you have a view called "files", this must be a view and not a table. You should also have a table "global_files" but if your missing "files" run the following
sql:/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
CREATE ALGORITHM=MERGE DEFINER=`KODI_18`@`%` SQL SECURITY DEFINER VIEW `files`
AS SELECT
`global_files`.`idFile` AS `idFile`,
`global_files`.`idPath` AS `idPath`,
`global_files`.`strFilename` AS `strFilename`,
`global_files`.`playCount_U01` AS `playCount`,
`global_files`.`lastPlayed_U01` AS `lastPlayed`,
`global_files`.`dateAdded` AS `dateAdded`
FROM `global_files`;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
RE: [MYSQL] HOW-TO: 5 User XBMC - OtheA - 2019-08-25
(2019-08-25, 21:08)BigMong Wrote:(2019-08-25, 06:19)OtheA Wrote: Hello again. I moved some files around and tried to clean the database, but now I'm getting two references for a few videos. This error in the logs.Wow I did have a typo haha Thank you I have updated it
I did run the delete_file trigger SQL query when I set it up. Is there something else I need to do? Is this error to be expected because I'm only using 5 of the 9 databases? I think I noticed a typo in the delete_file trigger. You have Kodi_U03_Video_116 in there twice and skipped U04, so I changed that when I ran it. Was that not correct?sql:2019-08-24 18:54:57.485 T:4088872976 NOTICE: CleanDatabase: Starting videodatabase cleanup ..
2019-08-24 18:55:14.167 T:4088872976 ERROR: SQL: [Kodi_U01_Video_116] The table does not exist
Query: DELETE FROM files WHERE idFile IN (334,424,487,493,497,674,738,833,853,863,1033,1137,1297,1482,3,3,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,176,177,178,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,243,244,245,246,247,248,249,249,250,251,252,253,254,255,256,257,258,259,260,261,261,262,263,264)
2019-08-24 18:55:14.168 T:4088872976 ERROR: CleanDatabase failed
This just removes bookmarks of deleted files no biggie just keeping things tidy.
That's fine if your only using 5 of the 9 or increased it to 20 or whatever.
you will just need to remove lines from the tiggers and not run the user setup of the other. can leave the other cols within the "global_files" table or drop them it wont change anything.
within the database "Kodi_U01_Video_116" do you have a view called "files", this must be a view and not a table. You should also have a table "global_files" but if your missing "files" run the following
sql:/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
CREATE ALGORITHM=MERGE DEFINER=`KODI_18`@`%` SQL SECURITY DEFINER VIEW `files`
AS SELECT
`global_files`.`idFile` AS `idFile`,
`global_files`.`idPath` AS `idPath`,
`global_files`.`strFilename` AS `strFilename`,
`global_files`.`playCount_U01` AS `playCount`,
`global_files`.`lastPlayed_U01` AS `lastPlayed`,
`global_files`.`dateAdded` AS `dateAdded`
FROM `global_files`;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
I have a view called "files" and a table called "global_files" in U01. I did a search for one of my movies and it is indeed listed twice in the database. I'll run the above query tonight and report back. Thanks again!
Yup. SQL responded with #1050 - Table 'files' already exists.
RE: [MYSQL] HOW-TO: 5 User XBMC - BigMong - 2019-08-27
(2019-08-25, 22:03)OtheA Wrote:(2019-08-25, 21:08)BigMong Wrote:(2019-08-25, 06:19)OtheA Wrote: Hello again. I moved some files around and tried to clean the database, but now I'm getting two references for a few videos. This error in the logs.Wow I did have a typo haha Thank you I have updated it
I did run the delete_file trigger SQL query when I set it up. Is there something else I need to do? Is this error to be expected because I'm only using 5 of the 9 databases? I think I noticed a typo in the delete_file trigger. You have Kodi_U03_Video_116 in there twice and skipped U04, so I changed that when I ran it. Was that not correct?sql:2019-08-24 18:54:57.485 T:4088872976 NOTICE: CleanDatabase: Starting videodatabase cleanup ..
2019-08-24 18:55:14.167 T:4088872976 ERROR: SQL: [Kodi_U01_Video_116] The table does not exist
Query: DELETE FROM files WHERE idFile IN (334,424,487,493,497,674,738,833,853,863,1033,1137,1297,1482,3,3,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,176,177,178,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,243,244,245,246,247,248,249,249,250,251,252,253,254,255,256,257,258,259,260,261,261,262,263,264)
2019-08-24 18:55:14.168 T:4088872976 ERROR: CleanDatabase failed
This just removes bookmarks of deleted files no biggie just keeping things tidy.
That's fine if your only using 5 of the 9 or increased it to 20 or whatever.
you will just need to remove lines from the tiggers and not run the user setup of the other. can leave the other cols within the "global_files" table or drop them it wont change anything.
within the database "Kodi_U01_Video_116" do you have a view called "files", this must be a view and not a table. You should also have a table "global_files" but if your missing "files" run the following
sql:/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
CREATE ALGORITHM=MERGE DEFINER=`KODI_18`@`%` SQL SECURITY DEFINER VIEW `files`
AS SELECT
`global_files`.`idFile` AS `idFile`,
`global_files`.`idPath` AS `idPath`,
`global_files`.`strFilename` AS `strFilename`,
`global_files`.`playCount_U01` AS `playCount`,
`global_files`.`lastPlayed_U01` AS `lastPlayed`,
`global_files`.`dateAdded` AS `dateAdded`
FROM `global_files`;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
I have a view called "files" and a table called "global_files" in U01. I did a search for one of my movies and it is indeed listed twice in the database. I'll run the above query tonight and report back. Thanks again!
Yup. SQL responded with #1050 - Table 'files' already exists.
Sorry for the delay,
Two other thoughts about this
check the permissions of the user "KODI_18"
then try a cleanup again.sql:GRANT ALL PRIVILEGES ON `Kodi\_U01\_Video\_116`.* TO 'KODI_18'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
and/or try and do the task manually for one of the id's if able log into the database as the KODI_18 user.
just to confirm the table is workingsql:USE `Kodi_U01_Video_116`;
DELETE FROM files WHERE idFile IN (334);
RE: [MYSQL] HOW-TO: 5 User XBMC - OtheA - 2019-08-28
(2019-08-27, 21:35)BigMong Wrote:(2019-08-25, 22:03)OtheA Wrote:(2019-08-25, 21:08)BigMong Wrote: Wow I did have a typo haha Thank you I have updated it
This just removes bookmarks of deleted files no biggie just keeping things tidy.
That's fine if your only using 5 of the 9 or increased it to 20 or whatever.
you will just need to remove lines from the tiggers and not run the user setup of the other. can leave the other cols within the "global_files" table or drop them it wont change anything.
within the database "Kodi_U01_Video_116" do you have a view called "files", this must be a view and not a table. You should also have a table "global_files" but if your missing "files" run the following
sql:/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
CREATE ALGORITHM=MERGE DEFINER=`KODI_18`@`%` SQL SECURITY DEFINER VIEW `files`
AS SELECT
`global_files`.`idFile` AS `idFile`,
`global_files`.`idPath` AS `idPath`,
`global_files`.`strFilename` AS `strFilename`,
`global_files`.`playCount_U01` AS `playCount`,
`global_files`.`lastPlayed_U01` AS `lastPlayed`,
`global_files`.`dateAdded` AS `dateAdded`
FROM `global_files`;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
I have a view called "files" and a table called "global_files" in U01. I did a search for one of my movies and it is indeed listed twice in the database. I'll run the above query tonight and report back. Thanks again!
Yup. SQL responded with #1050 - Table 'files' already exists.
Sorry for the delay,
Two other thoughts about this
check the permissions of the user "KODI_18"
then try a cleanup again.sql:GRANT ALL PRIVILEGES ON `Kodi\_U01\_Video\_116`.* TO 'KODI_18'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
and/or try and do the task manually for one of the id's if able log into the database as the KODI_18 user.
just to confirm the table is workingsql:USE `Kodi_U01_Video_116`;
DELETE FROM files WHERE idFile IN (334);
Thanks BigMong. No need to apologize. Still not working for me. I ran the query from SQL and got the following:
#1146 - Table 'Kodi_U06_Video_116.bookmark' doesn't exist
Which is correct, because I didn't create anything past U05, so I expect this would be a normal error.
Okay, that fixed it. I didn't realize I needed to remove the users I didn't create from the cleanup. The query was halting at the first user that didn't exist so it couldn't complete. I went back and removed the users I didn't configure and cleaned the library and that got rid of the extras. Thanks again BigMong. I appreciate you again.
RE: [MYSQL] HOW-TO: 5 User XBMC - BigMong - 2019-08-28
(2019-08-28, 05:36)OtheA Wrote:(2019-08-27, 21:35)BigMong Wrote:(2019-08-25, 22:03)OtheA Wrote: I have a view called "files" and a table called "global_files" in U01. I did a search for one of my movies and it is indeed listed twice in the database. I'll run the above query tonight and report back. Thanks again!
Yup. SQL responded with #1050 - Table 'files' already exists.
Sorry for the delay,
Two other thoughts about this
check the permissions of the user "KODI_18"
then try a cleanup again.sql:GRANT ALL PRIVILEGES ON `Kodi\_U01\_Video\_116`.* TO 'KODI_18'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
and/or try and do the task manually for one of the id's if able log into the database as the KODI_18 user.
just to confirm the table is workingsql:USE `Kodi_U01_Video_116`;
DELETE FROM files WHERE idFile IN (334);
Thanks BigMong. No need to apologize. Still not working for me. I ran the query from SQL and got the following:
#1146 - Table 'Kodi_U06_Video_116.bookmark' doesn't exist
Which is correct, because I didn't create anything past U05, so I expect this would be a normal error.
Okay, that fixed it. I didn't realize I needed to remove the users I didn't create from the cleanup. The query was halting at the first user that didn't exist so it couldn't complete. I went back and removed the users I didn't configure and cleaned the library and that got rid of the extras. Thanks again BigMong. I appreciate you again.
Awesome glad its working
RE: [MYSQL] HOW-TO: 5 User XBMC - LikeTearsInTheRain - 2019-09-12
Hey BigMong,
Thanks for updating the SQL for V116, I am moving from a solo user setup to hopefully allowing another user to have seperate watchlists maintained on the database as per your tutorial.
I have databases U01-U09 setup, the master db has 42 tables/views and the others 37, and I also did the delete trigger update.
My question (and this may come across as stupid but I'm a kodi novice), when do I add profiles to my kodi setup (currently just the master user running off the U01 DB), and if I was to just add a new user, how will kodi know whether 'Marcus' or 'Susan' is U03 or U07 etc, and also what settings should I use, shared readonly, seperate, etc.
Apologies for the beginner level questioning, I'm familiar with SQL, XML etc, just not Kodi
RE: [MYSQL] HOW-TO: 5 User XBMC - OtheA - 2019-09-12
I just recently went through the same process. You'll copy your advancedsettings.xml to the profile directory and change the DB reference there. The Wiki has info on how to create Profiles here.
Example:
Create Marcus Profile in Kodi. Kodi creates Marcus directory under profiles.
Copy advancedsettings.xml from main profile to Marcus directory.
Edit advancedsettings.xml in Marcus' directory and change Kodi_U01_Video_116 to Kodi_U03_Video_116 per your example above.
Now when Marcus is logged in, it will access his U03 DB.
Hope this helps.
RE: [MYSQL] HOW-TO: 5 User XBMC - LikeTearsInTheRain - 2019-09-14
Perfectly explained thank you! Everything is working as expected
RE: [MYSQL] HOW-TO: 5 User XBMC - jmgibson1981 - 2019-09-26
I didn't read the whole thread, maybe someone asked. What benefit is there to this > just having a separate video db for each user. They don't eat up much space / compute power to access them all at the same time. I currently just run 2, main for the wife and i in the living room / master bedroom, and a guest room video db. We all share the same music db. Both video dbs pull from the same nfs share. This is done by the video db name in advancedsettings.xml. They all access kodi with the same sql user.
Just seems I'm missing the point? Or misread and I'm already doing this?
Code:
lib_watch() {
inotifywait -m -r "$POOLLOC"/"$1"/ -e create | while read FILE ; do
KODICLIENTS=$(grep kodi /etc/dhcp/ltsp.dhcpd.conf | awk '{print $2}')
kodi-send --action "UpdateLibrary(${1})"
sleep 30
for client in $KODICLIENTS ; do
if ping -c 1 "$client" ; then
ssh "$client" "kodi-send --action \"UpdateLibrary(${1})\""
fi
done
done
}
for library in video music ; do
lib_watch "$library" &
doneScans are done automatically as stuff is added to the libraries.
RE: [MYSQL] HOW-TO: 5 User XBMC - ponkotsu - 2019-09-26
For me, the primary benefit is that I only have to scrape media once into a single database, and my brother (user2) will have it available when he uses his Kodi installation. I don't use any auto-library-update stuff because I'm a bit particular about the poster / fanart for each item. But if your living room + bedroom use the same database, while the guest room uses a separate one, and you don't need to share watched / resume status between the two, and you do use an automatic library updating mechanism, then I don't think you'd really gain much from doing this.
Edit: Missed your edit while I was replying. Yep, it looks like the way you have your setup arranged, this wouldn't really be of any benefit for you.
RE: [MYSQL] HOW-TO: 5 User XBMC - BigMong - 2019-09-27
@jmgibson1981
The main reason is the have different info (watched status, resume points, etc) and to only the scan the storage once.
So I have about 9 PI's and one NUC (main unit) and about 8 active users in my SQL.
About 46,000 Episodes (790 Shows) and 2,800 Movies totaling around 26 TB
If all the devices was scanning when I added a new item that be X devices all on mass scanning the NAS and reading over all the folders on avg it takes a PI around 1 - 1.5 hours per scan
This will add more load to the Network, PtP Links, PI's, NAS
So with this setup I scan once from the NUC when I add stuff (or I can auto it but I do it manually) its done in 3-4 mins and everyone has the data.
Bottom line was for me why do something more times then its needed and in this case it only needs to be done once.
Now how you do it looks to work for your setup and that's fine.
If you wanted to have your own watched status and the wife to have her own and only scan the shares once and both can login on any device then this setup "[MYSQL] HOW-TO: 5 User XBMC" might be better.
RE: [MYSQL] HOW-TO: 5 User XBMC - jmgibson1981 - 2019-09-27
Guess it's about scale. For all purposes I only have 2 users and 3 clients at any given time so it's a non issue I suppose. That being said I see the advantages here for scaling out. Also my library isn't even close to that big yet. Barely pushing 1400 episodes of tv shows, 880 movies, 3k mp3s give or take. This uses profiles as well, something I've never had a need for so far. For my guest room I have a script I run that toggles whether to use the guest video db, or our family one so when the room is empty we can use the room just like any other.
RE: [MYSQL] HOW-TO: 5 User XBMC - sloth85 - 2019-10-12
(2019-08-28, 05:36)OtheA Wrote:Having exact same issue at the moment. Can you explain exact steps you took to fix this? Cheers in advance.(2019-08-27, 21:35)BigMong Wrote:(2019-08-25, 22:03)OtheA Wrote: I have a view called "files" and a table called "global_files" in U01. I did a search for one of my movies and it is indeed listed twice in the database. I'll run the above query tonight and report back. Thanks again!
Yup. SQL responded with #1050 - Table 'files' already exists.
Sorry for the delay,
Two other thoughts about this
check the permissions of the user "KODI_18"
then try a cleanup again.sql:GRANT ALL PRIVILEGES ON `Kodi\_U01\_Video\_116`.* TO 'KODI_18'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
and/or try and do the task manually for one of the id's if able log into the database as the KODI_18 user.
just to confirm the table is workingsql:USE `Kodi_U01_Video_116`;
DELETE FROM files WHERE idFile IN (334);
Thanks BigMong. No need to apologize. Still not working for me. I ran the query from SQL and got the following:
#1146 - Table 'Kodi_U06_Video_116.bookmark' doesn't exist
Which is correct, because I didn't create anything past U05, so I expect this would be a normal error.
Okay, that fixed it. I didn't realize I needed to remove the users I didn't create from the cleanup. The query was halting at the first user that didn't exist so it couldn't complete. I went back and removed the users I didn't configure and cleaned the library and that got rid of the extras. Thanks again BigMong. I appreciate you again.
RE: [MYSQL] HOW-TO: 5 User XBMC - OtheA - 2019-11-01
(2019-10-12, 13:08)sloth85 Wrote:(2019-08-28, 05:36)OtheA Wrote:Having exact same issue at the moment. Can you explain exact steps you took to fix this? Cheers in advance.(2019-08-27, 21:35)BigMong Wrote: Sorry for the delay,
Two other thoughts about this
check the permissions of the user "KODI_18"
then try a cleanup again.sql:GRANT ALL PRIVILEGES ON `Kodi\_U01\_Video\_116`.* TO 'KODI_18'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
and/or try and do the task manually for one of the id's if able log into the database as the KODI_18 user.
just to confirm the table is workingsql:USE `Kodi_U01_Video_116`;
DELETE FROM files WHERE idFile IN (334);
Thanks BigMong. No need to apologize. Still not working for me. I ran the query from SQL and got the following:
#1146 - Table 'Kodi_U06_Video_116.bookmark' doesn't exist
Which is correct, because I didn't create anything past U05, so I expect this would be a normal error.
Okay, that fixed it. I didn't realize I needed to remove the users I didn't create from the cleanup. The query was halting at the first user that didn't exist so it couldn't complete. I went back and removed the users I didn't configure and cleaned the library and that got rid of the extras. Thanks again BigMong. I appreciate you again.
Sure thing! On step 4, just remove any users from the housekeeping that you didn't configure in step 3. Let's say if you only have two users, U1 and U2 created in step 3, you'd only reference U1 and U2 in step 4. Example:
sql:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
-- TRIGGER FIX FOR MULT USERS --
DROP TRIGGER IF EXISTS `Kodi_U01_Video_116`.`delete_file`;
DELIMITER $$
CREATE DEFINER=`KODI_18`@`%` TRIGGER `Kodi_U01_Video_116`.`delete_file` AFTER DELETE
ON `Kodi_U01_Video_116`.`global_files`
FOR EACH ROW
BEGIN
DELETE FROM `Kodi_U01_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U02_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U01_Video_116`.settings WHERE idfile = old.idfile;
DELETE FROM `Kodi_U01_Video_116`.stacktimes WHERE idfile = old.idfile;
DELETE FROM `Kodi_U01_Video_116`.streamdetails WHERE idfile = old.idfile;
END$$
DELIMITER ;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;