Reply to thread

Message

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:
[CODE]ALTER TABLE `mptvdb`.`recording` CHANGE COLUMN `fileName` `fileName` VARCHAR(255) NOT NULL;
ALTER TABLE `mptvdb`.`schedule` CHANGE COLUMN `programName` `programName` VARCHAR(255) NOT NULL;[/CODE]

... 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!

<blockquote data-quote="Miwer" data-source="post: 1068290" data-attributes="member: 132832">Hi, I just discovered a little quirk about my script...&nbsp; 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:[CODE]ALTER TABLE `mptvdb`.`recording` CHANGE COLUMN `fileName` `fileName` VARCHAR(255) NOT NULL;ALTER TABLE `mptvdb`.`schedule` CHANGE COLUMN `programName` `programName` VARCHAR(255) NOT NULL;[/CODE]... WILL fail with an &quot;Error Code: 1265. Data truncated for column (xxx)&quot; , 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 (&quot;ALTER IGNORE TABLE...&quot;)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. <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite14" alt=":cautious:" title="Cautious&nbsp; &nbsp; :cautious:" loading="lazy" data-shortname=":cautious:" />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). <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite23" alt=":sneaky:" title="Sneaky&nbsp; &nbsp; :sneaky:" loading="lazy" data-shortname=":sneaky:" /><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite27" alt=":whistle:" title="Whistling&nbsp; &nbsp; :whistle:" loading="lazy" data-shortname=":whistle:" />Have a nice day! <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite1" alt=":)" title="Smile&nbsp; &nbsp; :)" loading="lazy" data-shortname=":)" /></blockquote>

[QUOTE="Miwer, post: 1068290, member: 132832"] 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: [CODE]ALTER TABLE `mptvdb`.`recording` CHANGE COLUMN `fileName` `fileName` VARCHAR(255) NOT NULL; ALTER TABLE `mptvdb`.`schedule` CHANGE COLUMN `programName` `programName` VARCHAR(255) NOT NULL;[/CODE] ... [B]WILL fail[/B] 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. :cautious: 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). :sneaky::whistle: Have a nice day! :) [/QUOTE]