Error Contents: 1060 (42S21): Duplicate column name 'lastplayed'
AakashC2020 opened this issue · 61 comments
Hi, I'm getting the below error while running the MySQL version of this add-on.
Please advise.
2020-10-27 19:38:10.096 T:16832 ERROR: Traceback (most recent call last):
2020-10-27 19:38:10.096 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.seren\resources\lib\modules\trakt_sync\activities.py", line 57, in sync_activities
2020-10-27 19:38:10.096 T:16832 ERROR: success = self._remove_old_meta_items('shows')
2020-10-27 19:38:10.096 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.seren\resources\lib\modules\trakt_sync\activities.py", line 606, in _remove_old_meta_items
2020-10-27 19:38:10.097 T:16832 ERROR: for i in range(2, int(trakt_api.response_headers['X-Pagination-Page-Count']) + 1):
2020-10-27 19:38:10.097 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.requests\lib\requests\structures.py", line 52, in getitem
2020-10-27 19:38:10.097 T:16832 ERROR: return self._store[key.lower()][1]
2020-10-27 19:38:10.097 T:16832 ERROR: KeyError: 'x-pagination-page-count'
2020-10-27 19:38:10.199 T:16832 ERROR: Traceback (most recent call last):
2020-10-27 19:38:10.199 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.seren\resources\lib\modules\trakt_sync\activities.py", line 75, in sync_activities
2020-10-27 19:38:10.199 T:16832 ERROR: success = self._remove_old_meta_items('movies')
2020-10-27 19:38:10.199 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.seren\resources\lib\modules\trakt_sync\activities.py", line 606, in _remove_old_meta_items
2020-10-27 19:38:10.199 T:16832 ERROR: for i in range(2, int(trakt_api.response_headers['X-Pagination-Page-Count']) + 1):
2020-10-27 19:38:10.199 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.requests\lib\requests\structures.py", line 52, in getitem
2020-10-27 19:38:10.200 T:16832 ERROR: return self._store[key.lower()][1]
2020-10-27 19:38:10.200 T:16832 ERROR: KeyError: 'x-pagination-page-count'
2020-10-27 19:38:43.169 T:16276 WARNING: CGUIWindowManager - CGUIWindowManager::HandleAction - ignoring action 107, because topmost modal dialog closing animation is running
2020-10-27 19:38:46.454 T:16276 WARNING: Previous line repeats 2 times.
2020-10-27 19:38:46.454 T:16276 ERROR: Control 55 in window 10025 has been asked to focus, but it can't
2020-10-27 19:38:47.044 T:3388 ERROR: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--
- NOTE: IGNORING THIS CAN LEAD TO MEMORY LEAKS!
Error Type: <class 'mysql.connector.errors.ProgrammingError'>
Error Contents: 1060 (42S21): Duplicate column name 'lastplayed'
Traceback (most recent call last):
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\main.py", line 187, in
plugin.run()
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\routing.py", line 115, in run
self._dispatch(path)
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\routing.py", line 126, in _dispatch
view_func(**kwargs)
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\main.py", line 53, in run_addon
Main()
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\main.py", line 102, in init
cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed, tvshowcounts.lastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;")
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.myconnpy\lib\mysql\connector\cursor.py", line 491, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.myconnpy\lib\mysql\connector\connection.py", line 635, in cmd_query
statement))
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.myconnpy\lib\mysql\connector\connection.py", line 553, in _handle_result
raise errors.get_exception(packet)
ProgrammingError: 1060 (42S21): Duplicate column name 'lastplayed'
-->End of Python script error report<--
2020-10-27 19:38:47.123 T:20220 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://plugin.video.nextup-mysql/
2020-10-27 19:38:47.218 T:16276 ERROR: CGUIMediaWindow::GetDirectory(plugin://plugin.video.nextup-mysql/) failed
2020-10-27 19:38:57.715 T:16276 WARNING: CGUIWindowManager - CGUIWindowManager::HandleAction - ignoring action 107, because topmost modal dialog closing animation is running
2020-10-27 19:39:00.432 T:16276 WARNING: Previous line repeats 8 times.
2020-10-27 19:39:00.432 T:16276 ERROR: Control 55 in window 10025 has been asked to focus, but it can't
2020-10-27 19:39:00.824 T:4336 ERROR: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--
- NOTE: IGNORING THIS CAN LEAD TO MEMORY LEAKS!
Error Type: <class 'mysql.connector.errors.ProgrammingError'>
Error Contents: 1060 (42S21): Duplicate column name 'lastplayed'
Traceback (most recent call last):
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\main.py", line 187, in
plugin.run()
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\routing.py", line 115, in run
self._dispatch(path)
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\routing.py", line 126, in _dispatch
view_func(**kwargs)
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\main.py", line 53, in run_addon
Main()
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\main.py", line 102, in init
cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed, tvshowcounts.lastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;")
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.myconnpy\lib\mysql\connector\cursor.py", line 491, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.myconnpy\lib\mysql\connector\connection.py", line 635, in cmd_query
statement))
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.myconnpy\lib\mysql\connector\connection.py", line 553, in _handle_result
raise errors.get_exception(packet)
ProgrammingError: 1060 (42S21): Duplicate column name 'lastplayed'
-->End of Python script error report<--
2020-10-27 19:39:00.908 T:21120 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://plugin.video.nextup-mysql/
2020-10-27 19:39:00.922 T:16276 ERROR: CGUIMediaWindow::GetDirectory(plugin://plugin.video.nextup-mysql/) failed
Ok well I dont actually use a MYSQL database so it not something I can test but I suspect that its complaining about these two columns in the query "files.lastplayed, tvshowcounts.lastplayed".
When I was developing this I had issues with the MySQL query needing to have aliases set for parts of the query which worked without problems under sqlite. Therefore I think possibly the columns just need to have two different names set (ie "files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed") to prevent this error.
So can you test the following query in a python terminal or script:
import sqlite3
con = sqlite3.connect('/home/osmc/.kodi/userdata/Database/MyVideos116.db')
cur = con.cursor()
sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall()
print sql_result
I tested it at my end and it still works, but the original worked for me too.
Hi, I'm a novice in Python scripting. I tried to run the above query, but I'm getting this error:
Sqlite3, OperationalError: unable to open database file
Please advise.
Yeah you would need to point it to the correct database file. Modify this line:
con = sqlite3.connect('/home/osmc/.kodi/userdata/Database/MyVideos116.db')
To point to wherever your MySQL database is stored and then try it again.
The MySQL database in my Windows 10 PC is created like this:
C:\ProgramData\MySQL\MySQL Server 5.6\data\myvideos116
Instead of a .db file it's an entire folder containing multiple files.
How do I run the above query in this case?
Please advise.
Right looking at the addon code the MySQL part connects like this:
import mysql.connector
sql_username = addon.getSetting('username')
sql_password = addon.getSetting('password')
sql_host = addon.getSetting('host')
sql_port = addon.getSetting('port')
sql_db_name = addon.getSetting('db_name')
con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)
So you would need to replace these lines:
import sqlite3
con = sqlite3.connect('/home/osmc/.kodi/userdata/Database/MyVideos116.db')
With the correct connection details for your mysql database as above, with the username, password, host etc.
You could look in the settings for the addon if you arent sure what details you have used as it appears as if it is connecting to your mysql database.
Hi I tried running this query:
import sqlite3
con = sqlite3.connect('C:\ProgramData\MySQL\MySQL Server 5.6\data\myvideos116')
However I received the below error:
SyntaxError: EOL while scanning string literal
Please advise.
For MySQL "con = sqlite3.connect('C:\ProgramData\MySQL\MySQL Server 5.6\data\myvideos116')" isnt going to work.
You need to setup the connection like:
import mysql.connector
sql_username = 'Username'
sql_password = 'Password'
sql_host = '192.168.0.99'
sql_port = '1234'
sql_db_name = 'database_name'
con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)
And whatever the real values of username,password,etc are
Hi, I ran the below query:
import mysql.connector
sql_username = 'kodi'
sql_password = '****'
sql_host = '192.168.0.116'
sql_port = '3306'
sql_db_name = 'myvideos116'
con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)
cur = con.cursor()
sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall()
I received the below error:
Traceback (most recent call last):
File "", line 1, in
File "C:\Users\SHIBAJI\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\cursor_cext.py", line 234, in execute
self._cnx.handle_unread_result()
File "C:\Users\SHIBAJI\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 746, in handle_unread_result
raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found
Please advise.
Also I ran the below query and the results were as follows:
import mysql.connector
sql_username = 'kodi'
sql_password = '****'
sql_host = '192.168.0.116'
sql_port = '3306'
sql_db_name = 'myvideos116'
con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)
cur = con.cursor()
sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall()
Traceback (most recent call last):
File "", line 1, in
AttributeError: 'NoneType' object has no attribute 'fetchall'
print sql_result
File "", line 1
print sql_result
^
SyntaxError: Missing parentheses in call to 'print'. Did you mean print(sql_result)?
Please advise.
Ok possibly you need to close the connection, i think that error may be cause as it hasnt been getting closed properly.
Try running this on its own.
cur.close()
Then rerun the query:
import mysql.connector
sql_username = 'kodi'
sql_password = '****'
sql_host = '192.168.0.116'
sql_port = '3306'
sql_db_name = 'myvideos116'
con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)
cur = con.cursor()
sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall()
cur.close()
And let me know what happens?
Hi, see below please:
cur.close()
Traceback (most recent call last):
File "", line 1, in
File "C:\Users\SHIBAJI\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\cursor_cext.py", line 402, in close
self._cnx.handle_unread_result()
File "C:\Users\SHIBAJI\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 746, in handle_unread_result
raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found
I think both those issue are due to an open cursor pointing at a non existent database. So you need to close it and recreate the cursor where the database query will be sent.
Thats how i read the "AttributeError: 'NoneType' object has no attribute 'fetchall'" error (attempting to fetch results on a query which didnt work).
The "mysql.connector.errors.InternalError: Unread result found" error also seems to have a connection to the results returned by the query. If it wasnt closed properly first im assuming that could cause this issue.
But i dont know much about mysql and was only able to get this part of the addon working with much trial and error with a user with a mysql database over discord. So it might take a bit of back and forth to figure out why its not working for you.
Hi,
Fine. I'll again check back on this later and let you know what happens.
Thanks a lot for your help! :)
Some googling and it suggests that the cursor needs setup differently?
Also rebooting the machine you are running this on and the machine the database is on may help?
If it has anything to do with open connections that would be a way to ensure they close and everything is starting from fresh?
So the cursor option which may have some bearing:
cursor(buffered=True)
So your query would be:
import mysql.connector
sql_username = 'kodi'
sql_password = '****'
sql_host = '192.168.0.116'
sql_port = '3306'
sql_db_name = 'myvideos116'
con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)
cur = con.cursor(buffered=True)
sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall()
cur.close()
Hi, please see below:
import mysql.connector
sql_username = 'kodi'
sql_password = '****'
sql_host = '192.168.0.116'
sql_port = '3306'
sql_db_name = 'myvideos116'
con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)
cur = con.cursor(buffered=True)
sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall()
Traceback (most recent call last):
File "", line 1, in
AttributeError: 'NoneType' object has no attribute 'fetchall'
cur.close()
True
Hmmm, that error isnt really helping me too much "AttributeError: 'NoneType' object has no attribute 'fetchall'".
I've done a bit of a google and i've looked at the version of the sql query from my github and I dont think its the same as the version you posted.
So try the following, which is from here v0.0.12 in my repo:
I see you have this version installed "plugin.video.nextup-mysql", I think thats your problem, I'm pretty sure I stopped updating that version and incorporated the features into the main addon. It should be in my repo.
Anyway try the following, i think the previous setup with the ".fetchall()" attached to the query was tripping it up, but the following sql query is the current iteration and its not setup like that and I believe it has been tested and works on mysql.
import mysql.connector
sql_username = 'kodi'
sql_password = '****'
sql_host = '192.168.0.116'
sql_port = '3306'
sql_db_name = 'myvideos116'
con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)
cur = con.cursor()
cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, episode1 from (select idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, lastplayed from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and files.idfile in (select idfile from (select min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null group by tvshow.c00) as a)) as b where (idshow in (select idshow from (select files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, lastplayed from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and files.idfile in (select idfile from (select max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 group by tvshow.c00) as c))as d)) and c05 < current_date order by c05 desc;")
sql_result = cur.fetchall()
print sql_result
cur.close()
My repo is here:
Finally, this worked:
import mysql.connector
sql_username = 'kodi'
sql_password = '****'
sql_host = '192.168.0.116'
sql_port = '3306'
sql_db_name = 'myvideos116'
con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)
cur = con.cursor()
cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, episode1 from (select idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, lastplayed from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and files.idfile in (select idfile from (select min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null group by tvshow.c00) as a)) as b where (idshow in (select idshow from (select files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, lastplayed from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and files.idfile in (select idfile from (select max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 group by tvshow.c00) as c))as d)) and c05 < current_date order by c05 desc;")
sql_result = cur.fetchall()
print(sql_result)
[(391, 'smb://192.168.0.116/TV Shows/TV Shows (From DVD)/English/RoboCop - Prime Directives (2001)/Season 01/S01E02 - Meltdown.avi', '2', 'Robocop: Prime Directives', 'Action & Adventure / Sci-Fi & Fantasy', 17, 54, 'Meltdown'), (396, 'smb://192.168.0.116/TV Shows/TV Shows (From DVD)/English/RoboCop - The Series (1994)/Season 01/S01E03 - Trouble in Delta City.avi', '3', 'RoboCop: The Series', 'Sci-Fi & Fantasy / Action & Adventure / Comedy / Crime', 18, 58, 'Trouble in Delta City')]
cur.close()
True
However, even though the SQL query worked, but the addon is not working. I entered my MySQL database configuration in the addon but every time I open it the settings box is popping up and after that nothing is happening. Please advise.
This add-on is different in that it runs as a service and creates a smart playlist with the entries from the SQL result.
So you can then point a widget at that or open it from playlists.
If you really need the addon page I think I could get that working but it's basically the same list and I personally think the service add-on is better.
It's created in the playlists folder in userdata which should show up in Kodi and also smart shortcuts in skins.
I'm getting this error in Kodi after running the addon:
2020-10-28 03:33:51.883 T:2816 NOTICE: load skin from: C:\Program Files\Kodi\addons\skin.estuary (version: 2.0.27)
2020-10-28 03:33:53.355 T:2816 NOTICE: VideoPlayer::OpenFile: update_playlist_path
2020-10-28 03:33:53.357 T:4580 NOTICE: Creating InputStream
2020-10-28 03:33:53.357 T:4580 ERROR: CVideoPlayer::OpenInputStream - error opening [update_playlist_path]
2020-10-28 03:33:53.357 T:4580 NOTICE: CVideoPlayer::OnExit()
2020-10-28 03:33:53.562 T:2816 NOTICE: CVideoPlayer::CloseFile()
2020-10-28 03:33:53.562 T:2816 NOTICE: VideoPlayer: waiting for threads to exit
2020-10-28 03:33:53.562 T:2816 NOTICE: VideoPlayer: finished waiting
2020-10-28 03:33:53.568 T:8828 ERROR: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--
- NOTE: IGNORING THIS CAN LEAD TO MEMORY LEAKS!
Error Type: <type 'exceptions.ImportError'>
Error Contents: No module named mysql.connector
Traceback (most recent call last):
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\service.next_playlist\service.py", line 298, in onPlayBackStopped
kodi_playlist_generate()
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\service.next_playlist\service.py", line 323, in kodi_playlist_generate
import mysql.connector
ImportError: No module named mysql.connector
-->End of Python script error report<--
2020-10-28 03:33:57.286 T:2816 ERROR: Control 55 in window 10025 has been asked to focus, but it can't
2020-10-28 03:34:53.269 T:11108 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://service.next_playlist/
2020-10-28 03:34:53.283 T:2816 ERROR: CGUIMediaWindow::GetDirectory(plugin://service.next_playlist/) failed
Also the service add-on does some additional stuff, like marking episodes watched and a couple of other things.
I'd have to review the code to see what I actually added. My own version has a number of additions which work for me but have never really been tested enough that I'd be confident releasing it.
I think that means that you need to install the Kodi add-on
Mysql connector/python
I should probably have added that as a dependancy but I obviously forgot.
Hi,
It seems we can't install a dependency addon separately. It needs to be embedded to your addon.
Please check the below and advise.
Kindly see this link:
https://forum.kodi.tv/showthread.php?tid=298428
"it's not possible to manually install script modules through the repo though,
you need to depend on it in the addon.xml file of your addon.
it will then be auto-installed when someone installs your addon."
The connector IS available in the official repo. You have to include it as a dependency of your add-on. When your add-on is installed, it will get pulled in and installed automatically.
This is part of the 'addon.xml' file for Video Database Cleaner, which installs it as a dependency.
all
Ok well im pretty sure i installed it manually at some point when i was building the addon so can you try installing it from here:
script.module.mysql.connector-master.zip
Which is just the following cloned as a zip from github:
Actually this is the version i have if that doesnt work:
After installing these, the new error is as below:
2020-10-28 04:23:26.280 T:2996 ERROR: Control 55 in window 10025 has been asked to focus, but it can't
2020-10-28 04:23:30.893 T:12180 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://service.next_playlist/
2020-10-28 04:23:30.967 T:2996 ERROR: CGUIMediaWindow::GetDirectory(plugin://service.next_playlist/) failed
2020-10-28 04:23:14.953 T:2996 WARNING: CGUIMediaWindow::OnMessage - updating in progress
2020-10-28 04:23:19.833 T:2996 ERROR: Control 55 in window 10025 has been asked to focus, but it can't
2020-10-28 04:23:23.101 T:12772 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://service.next_playlist/
2020-10-28 04:23:23.188 T:2996 ERROR: CGUIMediaWindow::GetDirectory(plugin://service.next_playlist/) failed
2020-10-28 04:23:26.280 T:2996 ERROR: Control 55 in window 10025 has been asked to focus, but it can't
2020-10-28 04:23:30.893 T:12180 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://service.next_playlist/
2020-10-28 04:23:30.967 T:2996 ERROR: CGUIMediaWindow::GetDirectory(plugin://service.next_playlist/) failed
2020-10-28 04:26:06.508 T:2996 ERROR: Control 55 in window 10025 has been asked to focus, but it can't
2020-10-28 04:26:11.036 T:2996 NOTICE: VideoPlayer::OpenFile: update_playlist_path
2020-10-28 04:26:11.037 T:8988 NOTICE: Creating InputStream
2020-10-28 04:26:11.037 T:8988 ERROR: CVideoPlayer::OpenInputStream - error opening [update_playlist_path]
2020-10-28 04:26:11.037 T:8988 NOTICE: CVideoPlayer::OnExit()
2020-10-28 04:26:11.071 T:2996 NOTICE: CVideoPlayer::CloseFile()
2020-10-28 04:26:11.072 T:2996 NOTICE: VideoPlayer: waiting for threads to exit
2020-10-28 04:26:11.072 T:2996 NOTICE: VideoPlayer: finished waiting
2020-10-28 04:26:11.167 T:4204 ERROR: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--
- NOTE: IGNORING THIS CAN LEAD TO MEMORY LEAKS!
Error Type: <type 'exceptions.ImportError'>
Error Contents: No module named mysql.connector
Traceback (most recent call last):
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\service.next_playlist\service.py", line 298, in onPlayBackStopped
kodi_playlist_generate()
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\service.next_playlist\service.py", line 323, in kodi_playlist_generate
import mysql.connector
ImportError: No module named mysql.connector
-->End of Python script error report<--
2020-10-28 04:26:11.580 T:4724 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://service.next_playlist/
2020-10-28 04:26:11.648 T:2996 ERROR: CGUIMediaWindow::GetDirectory(plugin://service.next_playlist/) failed
2020-10-28 04:26:12.543 T:2996 WARNING: CGUITextureManager::Cleanup: Having to cleanup texture lists/focus.png
2020-10-28 04:26:12.568 T:2996 NOTICE: load skin from: C:\Program Files\Kodi\addons\skin.estuary (version: 2.0.27)
2020-10-28 04:26:14.048 T:2996 NOTICE: VideoPlayer::OpenFile: update_playlist_path
2020-10-28 04:26:14.049 T:8040 NOTICE: Creating InputStream
2020-10-28 04:26:14.049 T:8040 ERROR: CVideoPlayer::OpenInputStream - error opening [update_playlist_path]
2020-10-28 04:26:14.049 T:8040 NOTICE: CVideoPlayer::OnExit()
2020-10-28 04:26:14.158 T:2996 NOTICE: CVideoPlayer::CloseFile()
2020-10-28 04:26:14.158 T:2996 NOTICE: VideoPlayer: waiting for threads to exit
2020-10-28 04:26:14.158 T:2996 NOTICE: VideoPlayer: finished waiting
2020-10-28 04:26:14.169 T:4204 ERROR: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--
- NOTE: IGNORING THIS CAN LEAD TO MEMORY LEAKS!
Error Type: <type 'exceptions.ImportError'>
Error Contents: No module named mysql.connector
Traceback (most recent call last):
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\service.next_playlist\service.py", line 298, in onPlayBackStopped
kodi_playlist_generate()
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\service.next_playlist\service.py", line 323, in kodi_playlist_generate
import mysql.connector
ImportError: No module named mysql.connector
-->End of Python script error report<--
Please advise.
I guess it is not working separately. I think it needs to be added as a dependency.
Did you try manually installing it from the zip:
https://github.com/twinther/script.module.myconnpy
https://github.com/henryjfry/fryhenryj/files/5448479/script.module.myconnpy-master.zip
Yes, I manually installed it from the Zip. Still the same issue.
If not you could install that add-on mentioned in the message as a work around.
Otherwise I can do you a version with the dependancy but that would have to wait until tomorrow.
Have you checked it exists under the dependencies in settings/system/add-ons?
And that it's enabled?
Fine. I'll wait for a version with the dependency. Please let me know when it's updated. Thanks for all your help!
It'll just be a zip version with a quick change to add-on.xml
I don't have git currently setup and I can never remember how to properly commit and push stuff to my repo
Hence me not having updated it in months
Fine. I'll wait for the updated zip file. Thanks!
If you want you can download the zip and edit add-on.xml yourself with this in the requires section:
<import addon="script.module.myconnpy" version="1.1.7"/>
And reinstall it from the modified zip
I can try but it'll be really helpful if you could do it from your end as I'm a novice in Python scripting. Thanks!
Ok service.next_playlist-0.0.12a.zip has the following dependancy:
<import addon="script.module.mysql.connector" version="8.0.17"/>
service.next_playlist-0.0.12a.zip
And service.next_playlist-0.0.12b.zip:
<import addon="script.module.myconnpy" version="1.1.7"/>
service.next_playlist-0.0.12b.zip
As im not sure which version is in the official repo.
If that fails you can manually install an addon by extracting the addon from the zip file, and copying and pasting it into "/home/.kodi/addons" while kodi is not running, when you start kodi you can then go into the manage dependencies page in settings/system/addons and enable the addon, which will initially show up as disabled.
If that still doesnt work then my suggestion would be dont use a mysql database and im probably unable to help you any further without hands on access to your machine.
Buts its not actually a problem with the addon at this stage, but with a dependency which i was able to install without problems manually. If you aren't able to do that then there isn't too much help I can provide from my end.
Hi, No problem. I can understand. Actually I need to use MySQL database as there are 3 instances of Kodi running in my home having access to the shared database. Nevertheless thanks a lot for your help. I learnt a lot of new things from you. Keep up the good work! :)
So did either of those version 12's install the dependancy then?
Failing that you could try the addon "Video Database Cleaner" as per your message before from the kodi forum. Its in the Black Eagle repo (not sure where the kodi explorer repo path is but the zip file is below)
https://github.com/the-black-eagle/repository.blackeagle/blob/master/repository.blackeagle.zip
With the dependancy issue directly installing the addon from the zip might not work properly without the repo installed first but the direct link to the Video Database cleaner is here:
Also possibly if the addon isnt going to work installing the python "mysql.connector" module on the kodi machine on which it needs to run might work?
However I think kodi has its own version of python, which is why addons like myconnpy are required to provide the required modules when the python scripts are launched as addons rather than being launched from the command line with a fully working python environment.
Hi, I tried installing both the versions of MySQL connector with the addon v12 from your repo. However, none of them worked. At present it's not possible for me to clean my database as it's quite large in size. I shall look into this issue later and let you know if I find any solution. Thanks!
You wouldn't need to actually clean your database, just install the addon to get the dependancy installed. Im pretty sure if you install the "Video Database Cleaner" addon you would still need to add the correct login to your mysql database before any of the cleaning actions would work, so just installing that addon shouldn't cause any problems or perform any database cleaning.
However i'm just trying to figure out how it is that this mysql connector addon wont install.
Can you take a screenshot of the kodi system settings/addons/manage dependencies at the "M" section in the list?
Like the attached?
I want to see if its installed but disabled.
The two versions of the addon are called "MySQL Connector/Python" and "mysql-connector"
With the version I have installed which you can see on the screen:
https://github.com/henryjfry/fryhenryj/files/5448479/script.module.myconnpy-master.zip
I checked "plugin.video.nextup-mysql.0.0.18.zip" and i did add the line:
<import addon="script.module.myconnpy" version="1.1.7"/>
To that addon, so maybe reinstalling that addon may help? (if you uninstalled it)
Hi, I'll try that but I think you got me all wrong. The MySQL connector for python has installed and it's showing up there under my addon dependencies. However, still I'm getting the error from the next episode addon even after installing that. Thanks!
Yeah im not sure what the problem is then. If its installed and enabled then it should just work with "import msql.connector" in the addon.
Can you please provide the screen shot? I want to see that its definetly enabled, its all too easy to think its installed but the addon is not actually enabled.
Also I want to see which version of the addon is installed.
And are you on Kodi 18 or Kodi 19?
Kodi 19 has a different version of Python and this addon isnt developed for that version.
Your initial errors were about the actual SQL query, which would have been using "import msql.connector" under "plugin.video.nextup-mysql", so im pretty certain it was working at one point.
If you uninstalled that addon it may have disabled the orphaned dependancy, thats all I can think that is happening.
Also it might be worth completely removing "plugin.video.nextup-mysql" and "service.next_playlist" and/or "script.module.mysql.connector" and "script.module.myconnpy"
And then reinstall just "service.next_playlist-0.0.12a.zip" or "service.next_playlist-0.0.12b.zip" with the dependancy added (use 12b which links to the exact same dependancy as "plugin.video.nextup-mysql").
With a reboot of kodi in between and after to make sure everything is running once it starts up again.
Yeah im not sure what the problem is then. If its installed and enabled then it should just work with "import msql.connector" in the addon.
Can you please provide the screen shot? I want to see that its definetly enabled, its all too easy to think its installed but the addon is not actually enabled.
Also I want to see which version of the addon is installed.
And are you on Kodi 18 or Kodi 19?
Kodi 19 has a different version of Python and this addon isnt developed for that version.
Hi, I'm using Kodi v18.9. The MySQL connector is installed and enabled. It's installed as a dependency for the TMDB scrapped so can't be installed or disabled. Thanks!
Ive just looked and "script.module.myconnpy" is a dependancy of "script.module.metahandler", so it should probably be installed by defualt.
Why its not working then, I'm not sure. What version number is it?
Ive just looked and "script.module.myconnpy" is a dependancy of "script.module.metahandler", so it should probably be installed by defualt.
Why its not working then, I'm not sure. What version number is it?
Hi, I've MySQL Connector/Python version 1.1.7 installed on my Kodi. Thanks!
Yeah thats the correct version, same as the one I have installed. As far as I know it should be working.
Does this happen on the other kodi boxes?
Can you post your full log?
it should have a line:
2020-10-27 08:33:03.423 T:4067434512 NOTICE: ADDON: script.module.myconnpy v1.1.7 installed
At this point I'm kind of at a loss as to why its not working.
Did you reinstall plugin.video.nextup-mysql.0.0.18.zip?
If so what happens when you open it?
If it gives the same error with mysql.connector then I think a re-install of kodi may be required, although I know it can be painful so i'd get if thats not something you want to do.
Thats why i want to know what happens on another box as its a relatively simple addon and while the sql may cause an issue, it should still run.
By the way you can rename your kodi directory and reboot to get kodi from a fresh state. That way if you want to go back you just need to quit kodi and rename the directory back and it should be back to how it was setup before.
I checked "plugin.video.nextup-mysql.0.0.18.zip" and i did add the line:
<import addon="script.module.myconnpy" version="1.1.7"/>To that addon, so maybe reinstalling that addon may help? (if you uninstalled it)
Hi, finally this version worked:
https://github.com/henryjfry/fryhenryj/files/5451659/plugin.video.nextup-mysql.0.0.18.zip
Thanks a lot for taking all the pain!
Really awesome work!
Thanks again!
Hi, I noticed a bug in plugin.video.nextup-mysql.0.0.18.zip
The option: "3.In progress ordered by the last played or airdate if newer" is not working. I'm getting the below MySQL error:
2020-10-29 11:40:09.173 T:11436 ERROR: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--
- NOTE: IGNORING THIS CAN LEAD TO MEMORY LEAKS!
Error Type: <class 'mysql.connector.errors.ProgrammingError'>
Error Contents: 1060 (42S21): Duplicate column name 'lastplayed'
Traceback (most recent call last):
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\main.py", line 187, in
plugin.run()
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\routing.py", line 115, in run
self._dispatch(path)
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\routing.py", line 126, in _dispatch
view_func(**kwargs)
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\main.py", line 53, in run_addon
Main()
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.nextup-mysql\main.py", line 102, in init
cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed, tvshowcounts.lastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;")
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.myconnpy\lib\mysql\connector\cursor.py", line 491, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.myconnpy\lib\mysql\connector\connection.py", line 635, in cmd_query
statement))
File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.myconnpy\lib\mysql\connector\connection.py", line 553, in _handle_result
raise errors.get_exception(packet)
ProgrammingError: 1060 (42S21): Duplicate column name 'lastplayed'
-->End of Python script error report<--
2020-10-29 11:40:09.253 T:9260 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://plugin.video.nextup-mysql/
2020-10-29 11:40:09.361 T:11696 ERROR: CGUIMediaWindow::GetDirectory(plugin://plugin.video.nextup-mysql/) failed
2020-10-29 11:40:22.339 T:11696 WARNING: CGUIWindowManager - CGUIWindowManager::HandleAction - ignoring action 107, because topmost modal dialog closing animation is running
2020-10-29 11:40:25.373 T:11696 WARNING: Previous line repeats 1 times.
2020-10-29 11:40:25.373 T:11696 ERROR: Control 55 in window 10025 has been asked to focus, but it can't
2020-10-29 11:47:00.698 T:11696 WARNING: CGUIWindowManager - CGUIWindowManager::HandleAction - ignoring action 92, because topmost modal dialog closing animation is running
2020-10-29 11:47:00.882 T:11696 WARNING: CGUIWindowManager - CGUIWindowManager::HandleAction - ignoring action 107, because topmost modal dialog closing animation is running
However, option 1, 2 and 4 are running fine.
I guess there is some error in the code for option 3 only.
Also, the "Update list now" button is asking to install the addon "Next up episodes", and even after installing that addon nothing is happening on clicking the button.
Also the below line is present in my Kodi log:
2020-10-29 11:21:53.152 T:11696 NOTICE: ADDON: script.module.myconnpy v1.1.7 installed
Please advise.
Ok so the "Update Now" option is becasue this line is in the settings:
<setting label="Update List Now" type="action" action="RunPlugin(plugin://plugin.video.nextup/update_widget)" option="close"/>
When it should be this line:
<setting label="Update List Now" type="action" action="RunPlugin(plugin://plugin.video.nextup-mysql/update_widget)" option="close"/>
And for the query in option three I think it should be this:
cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;")
Ive made those couple of changes in this version:
plugin.video.nextup-mysql.0.0.18b.zip
You might want to test the SQL before you install 0.0.18b and let me know if it works?
Also I was wondering do you still have the service plugin installed? This version 12c has a couple of changes which I noticed werent setup properly for mysql in 0.0.12b.
service.next_playlist-0.0.12c.zip
Does it work now, it should be working if the mysql.connector is now working?
If and when both addons work, you would only need one.
The service addon creates the playlist "/HOME/PATH/.kodi/userdata/playlists/video/NEXT_EPISODES_PLAYLIST.xsp"
While the video plugin will open as an addon page with the sql results loaded into it.
The service addon does some additional stuff like marking episodes as watched, it also has a next up style popup (which is what the additional change to the sql for mysql was needed for).
So if you are using an addon which populates a playlist (like seren) it wont use the built in next up popup, but if there is no populated playlist it gets the next episode from your library and pops up with an option to play the next episode (via tmdbhelper)
Hi, all the options including option 3 in below version is working fine:
plugin.video.nextup-mysql.0.0.18b.zip
However, after changing the option again when I'm clicking on the "Update Now" button nothing is happening.
It seems that the button is still not working.
The following addon is not working at all:
service.next_playlist-0.0.12c.zip
Please advise. Thanks!
The update now button is mostly just for widgets. When it runs it triggers a dummy playlist to play which will cause widgets to reload, which for the video plugin will cause the addon page to reload and feed through to the widget.
However when you reopen the addon after having been on a different screen it will reload anyway. Im not sure if you exit back to the home screen and run the "update now" and then go back into the addon if it will reload, i don't think kodi addons work like that (im pretty sure it will remember the last addon or directory until a different one is loaded, which is partly why further versions were created as a service addon).
If the service addon isnt working then I would just stick with the one which does work.
But id be interested to know what you mean by not working?
The service addon doesn't do much visible, just creates the smart playlist. So it may actually be doing what its supposed to be but if you haven't looked at the output playlist you might not notice?
It'll just be in kodi video library playlists folder.
Unless its throwing an actual error?
I just realised there were still some references to "plugin://plugin.video.nextup" in the addon
plugin.video.nextup-mysql.0.0.18c.zip
There are a couple of context menu items available in the addon which wouldnt work as they were pointing to the wrong addon but ive changed that in v0.0.18c
So the menu options "Refresh Directory" and "Addon Settings" that appear when you are in the addon should work now.
So within the addon the context menu option "Refresh Directory" is what you need to run to force an update. If used as a widget, skins which support widgets trigger whatever they are linked to to reload after something has played.
However addons cant do that, you need to trigger a container refresh, which is what "Refresh Directory" does. To trigger that after something has played you would need to know you are in the correct container and the only way to do that is to have a service running which can tell when you are back to the correct container.
I think I have an idea about how to do that but it would require further development.
Hi,
In the below version everything is working perfectly.
plugin.video.nextup-mysql.0.0.18c.zip
"Refresh Directory" option is very useful. Awesome work!
Thanks a lot!
Cheers. Yeah when I first built this I was using it as an addon and found the "Refresh Directory" to be pretty useful.
Glad thats its working now.
Cheers to you too! Thanks for all the help!