Tools and other software [How To] [User Exp] Update MySQL (3 Viewers)

[azzuro]

i have tested to replace an fake Mysql (5.5) in deploytool , but during install, error appear ! maybe the last install are broken for deploytool.

 

[megahorst]

I had a deeper look to the database.

All the tables where stored as MyISAM tables.

I have converted them with the SQL-workbench.

alter table `mptvdb`.`<tablename>` engine=innodb;

That worked at most tables.But recording and schedule
gave me the following error:

0	2	19:48:42	alter table `mptvdb`.`recording` engine=innodb	Error Code: 1071. Specified key was too long; max key length is 767 bytes	0.031 sec

All the others gave my the following warning:

1	4	19:58:37	alter table `mptvdb`.`card` engine=innodb	3 row(s) affected, 2 warning(s):
1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
1478 InnoDB: assuming ROW_FORMAT=COMPACT.
Records: 3  Duplicates: 0  Warnings: 2	0.109 sec

Does anybody have an idea what has to modified within the db?

 

[azzuro]

i have found this : https://forum.team-mediaportal.com/...-mysql-to-switch-to-innodb-from-myisam.88493/

ALTER TABLE canceledschedule		ENGINE=InnoDB;
ALTER TABLE card				ENGINE=InnoDB;
ALTER TABLE cardgroup		  ENGINE=InnoDB;
ALTER TABLE cardgroupmap		ENGINE=InnoDB;
ALTER TABLE channel			ENGINE=InnoDB;
ALTER TABLE channelgroup			ENGINE=InnoDB;
ALTER TABLE channellinkagemap	  ENGINE=InnoDB;
ALTER TABLE channelmap		  ENGINE=InnoDB;
ALTER TABLE conflict				ENGINE=InnoDB;
ALTER TABLE diseqcmotor		ENGINE=InnoDB;
ALTER TABLE favorite				ENGINE=InnoDB;
ALTER TABLE groupmap			ENGINE=InnoDB;
ALTER TABLE history			ENGINE=InnoDB;
ALTER TABLE keyword		ENGINE=InnoDB;
ALTER TABLE keywordmap		  ENGINE=InnoDB;
ALTER TABLE personaltvguidemap  ENGINE=InnoDB;
ALTER TABLE program		ENGINE=InnoDB;
ALTER TABLE radiochannelgroup	  ENGINE=InnoDB;
ALTER TABLE radiogroupmap	  ENGINE=InnoDB;
ALTER TABLE recording		  ENGINE=InnoDB;
ALTER TABLE satellite			  ENGINE=InnoDB;
ALTER TABLE schedule			ENGINE=InnoDB;
ALTER TABLE server			  ENGINE=InnoDB;
ALTER TABLE setting			ENGINE=InnoDB;
ALTER TABLE timespan			ENGINE=InnoDB;
ALTER TABLE tuningdetail			ENGINE=InnoDB;
ALTER TABLE tvmoviemapping	  ENGINE=InnoDB;
ALTER TABLE version			ENGINE=InnoDB;

repair table canceledschedule;	  ALTER TABLE canceledschedule		ENGINE=InnoDB;
repair table card;			  ALTER TABLE card			ENGINE=InnoDB;
repair table cardgroup;		  ALTER TABLE cardgroup			ENGINE=InnoDB;
repair table cardgroupmap;		  ALTER TABLE cardgroupmap		ENGINE=InnoDB;
repair table channel;			  ALTER TABLE channel			ENGINE=InnoDB;
repair table channelgroup;		  ALTER TABLE channelgroup		ENGINE=InnoDB;
repair table channellinkagemap;	  ALTER TABLE channellinkagemap		ENGINE=InnoDB;
repair table channelmap;		  ALTER TABLE channelmap			ENGINE=InnoDB;
repair table conflict;		  ALTER TABLE conflict			ENGINE=InnoDB;
repair table diseqcmotor;		  ALTER TABLE diseqcmotor			ENGINE=InnoDB;
repair table favorite;		  ALTER TABLE favorite			ENGINE=InnoDB;
repair table groupmap;		  ALTER TABLE groupmap			ENGINE=InnoDB;
repair table history;			  ALTER TABLE history			ENGINE=InnoDB;
repair table keyword;			  ALTER TABLE keyword			ENGINE=InnoDB;
repair table keywordmap;		  ALTER TABLE keywordmap			ENGINE=InnoDB;
repair table pendingdeletion;		  ALTER TABLE pendingdeletion		ENGINE=InnoDB;
repair table personaltvguidemap;	  ALTER TABLE personaltvguidemap		ENGINE=InnoDB;
repair table program;			  ALTER TABLE program			ENGINE=InnoDB;
repair table radiochannelgroup;	  ALTER TABLE radiochannelgroup		ENGINE=InnoDB;
repair table radiogroupmap;		  ALTER TABLE radiogroupmap		ENGINE=InnoDB;
repair table recording;		  ALTER TABLE recording			ENGINE=InnoDB;
repair table satellite;		  ALTER TABLE satellite			ENGINE=InnoDB;
repair table schedule;		  ALTER TABLE schedule			ENGINE=InnoDB;
repair table server;			  ALTER TABLE server			ENGINE=InnoDB;
repair table setting;			  ALTER TABLE setting			ENGINE=InnoDB;
repair table softwareencoder;		  ALTER TABLE softwareencoder		ENGINE=InnoDB;
repair table timespan;		  ALTER TABLE timespan			ENGINE=InnoDB;
repair table tuningdetail;		  ALTER TABLE tuningdetail		ENGINE=InnoDB;
repair table tvmoviemapping;		  ALTER TABLE tvmoviemapping		ENGINE=InnoDB;
repair table version;			  ALTER TABLE version			ENGINE=InnoDB;

An other Topic :
link of this code : by @Vasilich

ALTER TABLE version			ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE tvmoviemapping	ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE tuningdetail	  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE timespan		  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE softwareencoder	ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE setting			ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE server			ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE schedule		  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE satellite		  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE recording		  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE radiogroupmap	  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE radiochannelgroup  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE program			ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE personaltvguidemap ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE pendingdeletion	ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE keywordmap		ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE keyword			ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE history			ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE groupmap		  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE favorite		  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE diseqcmotor		ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE conflict		  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE channelmap		ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE channellinkagemap  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE channelgroup	  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE channel			ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE cardgroupmap	  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE cardgroup		  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE card			  ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE canceledschedule  ENGINE=InnoDB ROW_FORMAT=COMPACT;

 

[megahorst]

ALTER TABLE mptvdb.<tablename>			ENGINE=InnoDB ROW_FORMAT=COMPACT;

worked without any warnings

Except these ones:
ALTER TABLE mptvdb.schedule ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE mptvdb.recording ENGINE=InnoDB ROW_FORMAT=COMPACT;

They gave the following error:

0	22	08:19:57	ALTER TABLE mptvdb.schedule		  ENGINE=InnoDB ROW_FORMAT=COMPACT	Error Code: 1071. Specified key was too long; max key length is 767 bytes	0.016 sec

The keyvalue is to long for the chosen character-set "utf8-default collation" .

So the key or the character-set has to be changed.
Does anyone has an idea which character-set should be used?

 

[azzuro]

From other forum source: http://stackoverflow.com/questions/...-key-was-too-long-max-key-length-is-767-bytes

For what it's worth, i just had the same problem and switching from utf8_general_ci to utf8_unicode_ci solved the problem for me. I do not know why though

 

[powermarcel10]

So MP is still including an old version of MySQL in the installation? May I ask why?

I've never looked into this as everything is working fine here, but I thought that we always included the newest version of MySQL in our installation..

Anyone?

 

[FreakyJ]

I think it needs changes to the deploy tool

 

[azzuro]

i have switch to InnoDB in Workbench directly !
For that

• Open Workbench
• Left Menu (select Edit tables)
• Open mptvdb scheme
• Right click on one table
• select "Alter Table"
• Switch Myisiam -> InnoDB
• Select "Apply" & Close Tables
• Make this for all tables (one by one)

Row_format : Dynamic (not changed)
Character_set : UTF8 default (not changed)
No error message in Workbench

For test : i have scanned DVB-S (2500 channel), i think configuration.exe freeze less compared to 5.1+Mysiam (my HTPC is really slow)
but many channel have 10000 as channel Number.

 

[megahorst]

That is strange ...
This did not work for me.

Did you get an ok message in the log?

Here are my table definitions. Are yours the same?

desc mptvdb.schedule

field type null key default
id_Schedule int(11) NO PRI auto_increment
idChannel int(11) NO MUL
scheduleType int(11) NO MUL
programName varchar(256) NO MUL
startTime datetime NO MUL
endTime datetime NO MUL
maxAirings int(11) NO
priority int(11) NO
directory varchar(1024) NO
quality int(11) NO
keepMethod int(11) NO
keepDate datetime NO
preRecordInterval int(11) NO
postRecordInterval int(11) NO
canceled datetime NO
recommendedCard int(11) NO
series bit(1) NO
idParentSchedule int(11) NO 0

desc mptvdb.recording

field type null key default
idRecording int(11) NO PRI auto_increment
idChannel int(11) NO MUL
startTime datetime NO
endTime datetime NO
title varchar(2000) NO
description varchar(8000) NO
genre varchar(200) NO
fileName varchar(260) NO MUL
keepUntil int(11) NO
keepUntilDate datetime NO
timesWatched int(11) NO
idServer int(11) NO MUL
stopTime int(11) NO
episodeName text NO
seriesNum varchar(200) NO
episodeNum varchar(200) NO
episodePart text NO
isRecording bit(1) NO b'0'
idSchedule int(11) NO 0

 

[megahorst]

@Sebastiii:
I have seen in Mantis that you have created an update procedure from MySQL 5.1 to 5.6 and InnoDB.
Can you tell me what's wrong with my update tries?
I have installed mySqL 5.6, created the MpTVDb, converted most tables to InnoDB. Only recording and schedule is still MyISAM.

The table definitions are in the post above.
I have described the error in post #34

Thx in advance