Tools and other software [How To] [User Exp] Update MySQL (1 Viewer)

[Sebastiii]

Hi

In fact, in this branch i didn't do migration of DB in InnoDB, i only backup current 5.1 and restore to 5.6, so the DB stay in MyISAM.
But on fresh installation (read no db existing) the new TV DB is configured as InnoDB.

 

[megahorst]

Thx for your response.

But on fresh installation (read no db existing) the new TV DB is configured as InnoDB.

I can't confirm this.

I did the following:

-do a backup with huhas Backup plugin
-remove 1.30 beta + TV-server completely
-uninstall MySQL 5.1
-install MySQL 5.6
-install 1.30RC and choose the option "I do already have a database-server"
Here I had some trouble.
During installation I was asked for the database connection attributes. The installer did not accept 127.0.0.1 or localhost as address of the server that hosts the database. It always said "give propper IP or hostname"
At this point I had to go back to MySQL workbench and had to add a user/hostname combination "root / <full hostname of htpc>" and to give DBA rights to this user.
After this the MePo installer created all MePo tables in the MySQL 5.6 DB.
-restore my backup
I had a little problem here also. After the restore all TV-channels now have channel number 10000

First I thought, that everything was ok. The database default engine was set to InnoDB. But the I have mentioned that all the tables where created as MyISAM tables.

That I wanted to alter the tables with no luck with recording and schedule table ...

When you have a look to your 5.6 DB what does the describe of the tables say? The same as mine said?

 

[Sebastiii]

Thanks i will look when go to home later this evening.
So if i understand correctly, you test the branch and then on clean installation db was set to MySIAM ? (that weird) in my.ini the default db engine is InnoDB but maybe the code in TVServer to write to db change that.

 

[megahorst]

So if i understand correctly, you test the branch and then on clean installation db was set to MySIAM ? (that weird) in my.ini the default db engine is

I did not test the branch.
I have installed MySQL 5.6 by my own and than I have chosen it as backend for the TV-Server during the clean installation.

The default engine of MySQL was set to InnoDB, but the installer has created all tables as MyISAM tables. Now I'm trying to alter the tables to InnoDB...

 

[Sebastiii]

Ok thanks for confirming
Can you try with the branch then ?

Remove Link

 

[megahorst]

Can you try with the branch then ?

Ok, I will try to manage this within the next evenings.
What do I have to test? is this a new TV-Server installation?

 

[Sebastiii]

Yes, it's the new TV-Server installation :
If you have MySQL 5.1 : it should backup/restore MySQL db (all db) from 5.1 to 5.6.
It's it's a full clean installation (no MySQL installed) it should download MySQL 5.6 and setup it for TV-Server with MyTvDb set as InnoDB.

The best testing will be to do a clean installation after do full cleanup desinstallation.
About MySQL, after full cleanup, it could be nice to remove both folder :
C:\Program Files\MySQL and C:\ProgramData\MySQL
Like this it will do a proper installation
Thanks

 

[Sebastiii]

I just tested but i must go
Effectively new db was always in MyISAM so i just push a commit in branch that should fix it.
Can you try ? (for now only new db will be ok).

 

[megahorst]

I have tested ....

What I have done:
removed mePo and the TV-Server
uninstalled my own mysql 5.6 installation
installed the downloaded version
table 'server' and tvmovieprogramm were not propperly created
the tables 'recording' and 'schedule' are MyISAM.
rest of the tables are ok!

my try to repair the not propperly created tables gave me the following output:

Repairing tables
mptvdb.server
Warning  : InnoDB: Tablespace is missing for table 'mptvdb/server'
Error	: Table 'mptvdb.server' doesn't exist
status  : Operation failed
mptvdb.tvmovieprogram
Warning  : InnoDB: Tablespace is missing for table 'mptvdb/tvmovieprogram'
Error	: Table 'mptvdb.tvmovieprogram' doesn't exist
status  : Operation failed

just another thing:
the mysql-service was not running after the installation. I had to start it mannually

 

[Sebastiii]

Maybe the builded version of installer was not the latest : (i just push the change but in between start to generate installer so maybe the source was not yet updated)
On MySQLWorkbench, i execute this command : SHOW TABLE STATUS FROM `mptvdb`; and it show all db as InnoDB, i have the Table 'mptvdb.server' but not Table 'mptvdb.tvmovieprogram'.

Result of the command :

'canceledschedule', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '16384', '0', '1', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'card', 'InnoDB', '10', 'Compact', '3', '5461', '16384', '0', '16384', '0', '4', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'cardgroup', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '0', '0', '1', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'cardgroupmap', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '32768', '0', '1', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'channel', 'InnoDB', '10', 'Compact', '3338', '78', '262144', '0', '163840', '0', '3339', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'channelgroup', 'InnoDB', '10', 'Compact', '3', '5461', '16384', '0', '16384', '0', '4', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'channellinkagemap', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '0', '0', '1', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'channelmap', 'InnoDB', '10', 'Compact', '3338', '44', '147456', '0', '163840', '0', '3339', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'conflict', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '49152', '0', '1', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'diseqcmotor', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '32768', '0', '1', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'favorite', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '16384', '0', '1', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'groupmap', 'InnoDB', '10', 'Compact', '5628', '40', '229376', '0', '212992', '0', '5629', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'history', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '16384', '0', '1', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'keyword', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '0', '0', '1', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'keywordmap', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '0', '0', '1', '2013-02-27 19:04:23', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'pendingdeletion', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '0', '0', '1', '2013-02-27 19:04:27', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'personaltvguidemap', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '0', '0', '1', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'program', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '16384', '0', '1', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'radiochannelgroup', 'InnoDB', '10', 'Compact', '3', '5461', '16384', '0', '16384', '0', '4', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'radiogroupmap', 'InnoDB', '10', 'Compact', '1048', '62', '65536', '0', '49152', '0', '1049', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'recording', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '49152', '0', '1', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'satellite', 'InnoDB', '10', 'Compact', '152', '323', '49152', '0', '0', '0', '153', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'schedule', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '81920', '0', '1', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'server', 'InnoDB', '10', 'Compact', '1', '16384', '16384', '0', '0', '0', '2', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'setting', 'InnoDB', '10', 'Compact', '104', '157', '16384', '0', '16384', '0', '105', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'softwareencoder', 'InnoDB', '10', 'Compact', '2', '8192', '16384', '0', '0', '0', '24', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'timespan', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '0', '0', '1', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'tuningdetail', 'InnoDB', '10', 'Compact', '3339', '475', '1589248', '0', '245760', '4194304', '3339', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'tvmoviemapping', 'InnoDB', '10', 'Compact', '0', '0', '16384', '0', '16384', '0', '1', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''
'version', 'InnoDB', '10', 'Compact', '1', '16384', '16384', '0', '0', '0', '2', '2013-02-27 19:04:24', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=DYNAMIC', ''

I need to look if Table 'mptvdb.tvmovieprogram'. was created on MySQL 5.1 too.

BTW, if i execute : mysqlcheck mptvdb --auto-repair -e -u root -p, i get that :

Enter password: ***********
mptvdb.canceledschedule							OK
mptvdb.card										OK
mptvdb.cardgroup								  OK
mptvdb.cardgroupmap								OK
mptvdb.channel									OK
mptvdb.channelgroup								OK
mptvdb.channellinkagemap						  OK
mptvdb.channelmap								  OK
mptvdb.conflict									OK
mptvdb.diseqcmotor								OK
mptvdb.favorite									OK
mptvdb.groupmap									OK
mptvdb.history									OK
mptvdb.keyword									OK
mptvdb.keywordmap								  OK
mptvdb.pendingdeletion							OK
mptvdb.personaltvguidemap						  OK
mptvdb.program									OK
mptvdb.radiochannelgroup						  OK
mptvdb.radiogroupmap							  OK
mptvdb.recording								  OK
mptvdb.satellite								  OK
mptvdb.schedule									OK
mptvdb.server									  OK
mptvdb.setting									OK
mptvdb.softwareencoder							OK
mptvdb.timespan									OK
mptvdb.tuningdetail								OK
mptvdb.tvmoviemapping							  OK
mptvdb.version									OK
 
C:\Program Files\MySQL\MySQL Server 5.6\bin>

TvService surely failed to start because db was not ok.

So I will generate a new installer and you can try full upgrade or just delete the db + execute NSIS installer from : '%temp%\MediaPortal Installation\deploy\package-tvengine.exe' after after try to start installer and quit it

Thanks