[mythtv-users] mythtv schema upgrade, from v1227 -> v1264, FAILs @ v1262: "Data truncated for column 'data' at row 1"

[dch8758 at eml.cc]

hi,

On Mon, Jan 9, 2012, at 12:59 PM, jk90090 wrote:
> > how can I workaround the broken schema upgrade step?
> >
> Your error is caused by NULL values in the data column.
> 
> mysql -u<username> -p<password> -Dmythconverg
> # select * from settings where Data is Null;
> 
> On my system the result is 0 records.

checking right after the @v1261 fail, here i've,

mysql> use mythtv_db;
	Database changed
mysql> select * from settings where Data is Null;
	+-------------------------------+------+----------+
	| value                         | data | hostname |
	+-------------------------------+------+----------+
	| mythfilldatabaseLastRunStart  | NULL | NULL     |
	| mythfilldatabaseLastRunEnd    | NULL | NULL     |
	| mythfilldatabaseLastRunStatus | NULL | NULL     |
	| DataDirectMessage             | NULL | NULL     |
	+-------------------------------+------+----------+
	4 rows in set (0.00 sec)
mysql> select value from settings where data='';                         
	Empty set (0.00 sec)    

> I'm not sure what the upgrade is doing, but it seems to alter the table 
> to have that column default with '' for values.  If that is indeed the 
> case, then perhaps you can change all of the NULL values to '':
> 
> # update settings set data = '' where data is null;

mysql> update settings set data = '' where data is null;
	Query OK, 4 rows affected (0.00 sec)
	Rows matched: 4  Changed: 4  Warnings: 0
mysql> select value from settings where data='';
	+-------------------------------+
	| value                         |
	+-------------------------------+
	| mythfilldatabaseLastRunStart  |
	| mythfilldatabaseLastRunEnd    |
	| mythfilldatabaseLastRunStatus |
	| DataDirectMessage             |
	+-------------------------------+
	4 rows in set (0.00 sec)

and now, an immediately subsequent

mythtv-setup
	2012-01-09 13:43:41.613 Using runtime prefix = /usr
	2012-01-09 13:43:41.613 Using configuration directory =
	/home/dch/.mythtv
	...
	2012-01-09 13:43:43.173 Current MythTV Schema Version
	(DBSchemaVer): 1261
	2012-01-09 13:43:43.173 MythTV database schema is old. Waiting
	to see if DB is being upgraded.
	2012-01-09 13:43:44.181 Current MythTV Schema Version
	(DBSchemaVer): 1261
	2012-01-09 13:43:45.188 Current MythTV Schema Version
	(DBSchemaVer): 1261
	2012-01-09 13:43:46.195 Current MythTV Schema Version
	(DBSchemaVer): 1261
	2012-01-09 13:43:47.202 Current MythTV Schema Version
	(DBSchemaVer): 1261
	2012-01-09 13:43:48.210 Current MythTV Schema Version
	(DBSchemaVer): 1261
	2012-01-09 13:43:48.210 Timed out waiting.
	2012-01-09 13:43:48.233 SG(DB Backups) Error: Unable to find any
	Storage Group Directories.  Using hardcoded default value of
	'/mnt/store'
	2012-01-09 13:43:48.233 SG(DB Backups) Error:
	FindNextDirMostFree: '/mnt/store' does not exist!
	2012-01-09 13:43:48.236 Backing up database with script:
	'/usr/share/mythtv/mythconverg_backup.pl'
	2012-01-09 13:43:48.733 Database Backup complete.
	2012-01-09 13:43:48.736 Backed up database to file:
	'/tmp/mythtv_db-1261-20120109134348.sql.gz'
	2012-01-09 13:43:53.939 Newest MythTV Schema Version : 1264
	2012-01-09 13:43:53.948 Upgrading to MythTV schema version 1262
	2012-01-09 13:43:55.658 New DB connection, total: 3
	2012-01-09 13:43:55.660 Connected to database 'mythtv_db' at
	host: mediasvr.home.loc
	2012-01-09 13:43:55.664 Upgrading to MythTV schema version 1263
	2012-01-09 13:43:55.668 Upgrading to MythTV schema version 1264
	2012-01-09 13:43:55.673 Database Schema upgrade complete,
	unlocking.

seems to finish the rest of the way to v1264.

so you've identified what the problem is, thanks!

is this something that I can fix, rather than just workaround, on my
end?  or is it a bug that needs fixing?

dch