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

[Sebastiii]

Thanks for the report

I still see an issue (about password update failed) but randomly so need to know why
Also left the question, if user has db (not related to TVService) then all db will be migred to new MySQL5.6, not sure if it's ok !!!

 

[azzuro]

hi, all
i try to make, wiki page for update/change mysql to innoDB
http://wiki.team-mediaportal.com/1_...Server/Update_MySQL_Database_MyISAM_to_InnoDB

 

[Miwer]

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?

Hi,

I was having the same issue, so I looked into this a bit.
I tried installing a crisp new MP1.6 on a VM, and there's a change in one of the columns in both recording and schedule tables.
In table recording, I had the column 'fileName' as varchar(260) - in a new database this is a varchar(255).
In table schedule, I had the column 'programName' as varchar(256) - in a new database this is also a varchar(255).

So after changing these columns, I could convert the table to InnoDB without errors (although I did get a warning on the column change)
Also row_format in a new database is COMPACT, so that should be included too.
I did not change any character sets.

This script worked for me (notice the two extra lines in there):

ALTER TABLE `mptvdb`.`canceledschedule` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`card` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`cardgroup` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`cardgroupmap` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`channel` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`channelgroup` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`channellinkagemap` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`channelmap` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`conflict` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`diseqcmotor` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`favorite` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`groupmap` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`history` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`keyword` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`keywordmap` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`pendingdeletion` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`personaltvguidemap` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`program` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`radiochannelgroup` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`radiogroupmap` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`recording` CHANGE COLUMN `fileName` `fileName` VARCHAR(255) NOT NULL;
ALTER TABLE `mptvdb`.`recording` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`satellite` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`schedule` CHANGE COLUMN `programName` `programName` VARCHAR(255) NOT NULL;
ALTER TABLE `mptvdb`.`schedule` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`server` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`setting` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`softwareencoder` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`timespan` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`tuningdetail` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`tvmoviemapping` ENGINE=InnoDB ROW_FORMAT=COMPACT;
ALTER TABLE `mptvdb`.`version` ENGINE=InnoDB ROW_FORMAT=COMPACT;

 

[Sebastiii]

We want to integrate the switch to innodb on MySQL upgrade and that a good finding because we run into 3 issues :
Recording / Schedule about 767 bytes limits (you just fixed that).

Now we need to be able to execute this above script if MySQL engine is higher then MySQL version 5.6 and only if current table is MyISAM.

Are you able to provider such modification ?

I have try something like that but i'm not SQL guru lol :

SELECT @@VERSION, IF(@@VERSION > '5.6',true, false) FROM information_schema.tables WHERE ENGINE = 'MyISAM';

 

[Miwer]

Are you able to provider such modification ?

Yeah I can give it a shot... but don't hold your breath, it might take some time. I'm no guru myself, but I have been tinkering with databases a couple of years ago.

 

[Sebastiii]

For now i have added a workaround on C# code (not the best way), i analyse MySQL version and authorize the script to be start or not but could be nice to have a good script directly

 

[Miwer]

Hi, I just discovered a little quirk about my script...
Well, to be honest, I kinda had a feeling about this, but wanted to test it first, and now I have verified it.

These two lines:

ALTER TABLE `mptvdb`.`recording` CHANGE COLUMN `fileName` `fileName` VARCHAR(255) NOT NULL;
ALTER TABLE `mptvdb`.`schedule` CHANGE COLUMN `programName` `programName` VARCHAR(255) NOT NULL;

... WILL fail with an "Error Code: 1265. Data truncated for column (xxx)" , if there's any data in the tables exceeding the newly set length of the fields (which are shorter than the original lengths of 260 and 256 respectively).

I order to circumvent the error, and force the change through, there need to be an IGNORE statement in the command ("ALTER IGNORE TABLE...")
However this will cause modification of user data in the database. Though It will only truncate the excessive characters in the column, not the entire record.

So, here's a choice for the developers: modify the users data, forcing the upgrade of the table, or skip it, if there's data that won't fit the new shorter limit.

Correct me if I'm wrong: I'm guessing that truncating data in the schedule.programName column shouldn't have any functional impact, however truncating the recording.fileName column could cause some recordings not working anymore. But how likely is anyone to have filenames up to the Windows API limit of 260 chars anyway?? (I know this limit has exceptions).

Have a nice day!

 

[Sebastiii]

Thanks for your input, but i'm not sure now what to do lol