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

jk90090 jk90090 at gmail.com
Mon Jan 9 20:59:26 UTC 2012


On 1/9/12 12:22 PM, dch8758 at eml.cc wrote:
> i'm setting up a standalone mythbackend on a remote server.
>
> on the server,
>
> 	uname -rmo
> 		3.1.0-1.2-xen x86_64 GNU/Linux
> 	lsb_release -dir
> 		Distributor ID: SUSE LINUX
> 		Description:    openSUSE 12.1 (x86_64)
> 		Release:        12.1
> 	rpm -qa | grep -i myth
> 		libmyth-0_24-0.24.1-6.2.x86_64
> 		mythtv-0_24-backend-0.24.1-6.2.x86_64
> 		mythtv-0_24-common-0.24.1-6.2.x86_64
>
> i've setup the DB, and granted permissions.
>
> @ my desktop,
>
> 	uname -rmo
> 		3.1.0-1.2-desktop x86_64 GNU/Linux
> 	lsb_release -dir
> 		Distributor ID: SUSE LINUX
> 		Description:    openSUSE 12.1 (x86_64)
> 		Release:        12.1
> 	rpm -qa | grep -i myth-setup
> 		mythtv-0_24-setup-0.24.1-6.2.x86_64
>
> i exec myth-setup.  it connects as expected to the remote backend.
>
> i'm notified that the schema's outdated, and init the offered upgrade
> from v1227 ->  v1264.  the process gets as far as schema v1261 then dies:
>
> 	mythtv-setup
> 		2012-01-09 08:11:38.113 Using runtime prefix = /usr
> 		2012-01-09 08:11:38.113 Using configuration directory =
> 		/home/dch/.mythtv
> 		2012-01-09 08:11:38.114 Using localhost value of
> 		dchDesk.home.loc
> 		2012-01-09 08:11:38.114 Testing network connectivity to
> 		'mediascr.home.loc'
> 		2012-01-09 08:11:38.267 New DB connection, total: 1
> 		2012-01-09 08:11:38.271 Connected to database
> 		'mythtv_db' at host: mediascr.home.loc
> 		2012-01-09 08:11:38.277 Closing DB connection named
> 		'DBManager0'
> 		2012-01-09 08:11:38.278 Connected to database
> 		'mythtv_db' at host: mediascr.home.loc
> 		2012-01-09 08:11:38.281 Current locale en_US
> 		2012-01-09 08:11:38.281 Reading locale defaults from
> 		/usr/share/mythtv//locales/en_us.xml
> 		2012-01-09 08:11:38.518 ScreenSaverX11Private:
> 		XScreenSaver support enabled
> 		2012-01-09 08:11:38.519 ScreenSaverX11Private: Gnome
> 		screen saver support enabled
> 		2012-01-09 08:11:38.520 DPMS is active.
> 		2012-01-09 08:11:38.774 Desktop video mode: 1920x1080
> 		60.000 Hz
> 		2012-01-09 08:11:38.829 Loading en_us translation for
> 		module mythfrontend
> 		2012-01-09 08:11:38.921 Using Frameless Window
> 		2012-01-09 08:11:38.921 Using Full Screen Window
> 		2012-01-09 08:11:38.929 Using the Qt painter
> 		2012-01-09 08:11:39.669 Current MythTV Schema Version
> 		(DBSchemaVer): 1226
> 		2012-01-09 08:11:39.669 MythTV database schema is old.
> 		Waiting to see if DB is being upgraded.
> 		2012-01-09 08:11:40.669 New DB connection, total: 2
> 		2012-01-09 08:11:40.671 Connected to database
> 		'mythtv_db' at host: mediascr.home.loc
> 		2012-01-09 08:11:40.677 Waiting for Database Backup to
> 		complete.
> 		2012-01-09 08:11:41.681 Waiting for Database Backup to
> 		complete.
> 		2012-01-09 08:11:42.686 Waiting for Database Backup to
> 		complete.
> 		2012-01-09 08:11:43.690 Waiting for Database Backup to
> 		complete.
> 		2012-01-09 08:11:44.694 Waiting for Database Backup to
> 		complete.
> 		2012-01-09 08:11:45.699 Waiting for Database Backup to
> 		complete.
> 		2012-01-09 08:11:45.699 Timed out waiting.
> 		2012-01-09 08:11:45.723 SG(DB Backups) Error: Unable to
> 		find any Storage Group Directories.  Using hardcoded
> 		default value of '/mnt/store'
> 		2012-01-09 08:11:45.723 SG(DB Backups) Error:
> 		FindNextDirMostFree: '/mnt/store' does not exist!
> 		2012-01-09 08:11:45.727 Backing up database with script:
> 		'/usr/share/mythtv/mythconverg_backup.pl'
> 		2012-01-09 08:11:46.138 Database Backup complete.
> 		2012-01-09 08:11:46.140 Backed up database to file:
> 		'/tmp/mythtv_db-1226-20120109081145.sql.gz'
> 		2012-01-09 08:11:57.572 Newest MythTV Schema Version :
> 		1264
> 		2012-01-09 08:11:57.581 Upgrading to MythTV schema
> 		version 1227
> 		2012-01-09 08:11:58.054 New DB connection, total: 3
> 		2012-01-09 08:11:58.056 Connected to database
> 		'mythtv_db' at host: mediascr.home.loc
> 		2012-01-09 08:11:58.059 Upgrading to MythTV schema
> 		version 1228
> 		...
> 		2012-01-09 08:12:02.799 Upgrading to MythTV schema
> 		version 1236
> 		2012-01-09 08:12:02.799 New DB connection, total: 4
> 		2012-01-09 08:12:02.805 Connected to database
> 		'mythtv_db' at host: mediascr.home.loc
> 		2012-01-09 08:12:02.808 Upgrading to MythTV schema
> 		version 1237
> 		...
> 		2012-01-09 08:12:11.267 Upgrading to MythTV schema
> 		version 1261
> 		2012-01-09 08:12:11.271 Upgrading to MythTV schema
> 		version 1262
> 		2012-01-09 08:12:15.150 DB Error (Performing database
> 		upgrade):
> 		Query was: ALTER TABLE settings MODIFY COLUMN data
> 		VARCHAR(16000)     NOT NULL default '';
> 		Error was: Driver error was [2/1265]:
> 		QMYSQL: Unable to execute query
> 		Database error was:
> 		Data truncated for column 'data' at row 1
>
> 		new version: 1262
> 		2012-01-09 08:12:15.150 Database Schema upgrade FAILED,
> 		unlocking.
> 		2012-01-09 08:12:15.151 Couldn't upgrade database to new
> 		schema.
>
> how can I workaround the broken schema upgrade step?
>
> dch
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users
>
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.

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;

As a note, I have 35 rows with data=''

mysql> select value from settings where data='';
+-------------------------+
| value                   |
+-------------------------+
| startupCommand          |
| SetWakeuptimeCommand    |
| preSDWUCheckCommand     |
| WOLbackendCommand       |
| WOLslaveBackendsCommand |
| UserJob1                |
| UserJob2                |
| UserJob3                |
| UserJob4                |
| CustomFilters           |
| LircKeyPressedApp       |
| MythFillDatabaseArgs    |
| MythFillDatabaseLog     |
| SlideshowBackground     |
| PostCDRipScript         |
| CustomFilters           |
| Style                   |
| LircKeyPressedApp       |
| MythArchiveTempDir      |
| SlideshowBackground     |
| PostCDRipScript         |
| CustomFilters           |
| LircKeyPressedApp       |
| MythArchiveTempDir      |
| SlideshowBackground     |
| PostCDRipScript         |
| SipProxyAuthName        |
| SipProxyAuthPassword    |
| CDDevice                |
| IgnoreDevices           |
| ThemeUpdateStatus       |
| IgnoreDevices           |
| MiscStatusScript        |
| web_video_imdb_path     |
| ThemeUpdateStatus       |
+-------------------------+
35 rows in set (0.00 sec)

-JK


More information about the mythtv-users mailing list