XF\Db\Exception: Truncated incorrect DOUBLE value

[Joshua]

Can someone help me generate queries to help me through a failed XMG upgrade?

XF\Db\Exception: MySQL query error [1292]: Truncated incorrect DOUBLE value: 'a:0:{}' in src/XF/Db/AbstractStatement.php at line 228
XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 196
XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 77
XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 322
XF\Db\AbstractAdapter->update() in src/XF/Install/InstallHelperTrait.php at line 85
XF\AddOn\AbstractSetup->tableColumnsToJson() in src/XF/Install/InstallHelperTrait.php at line 43
XF\AddOn\AbstractSetup->entityColumnsToJson() in src/addons/XFMG/Setup.php at line 2434
XFMG\Setup->upgrade902010010Step4() in src/XF/AddOn/StepRunnerUpgradeTrait.php at line 122
XFMG\Setup->upgradeStepRunner() in src/XF/AddOn/StepRunnerUpgradeTrait.php at line 71
XFMG\Setup->upgrade() in src/XF/Admin/Controller/AddOn.php at line 576
XF\Admin\Controller\AddOn->actionUpgrade() in src/XF/Mvc/Dispatcher.php at line 350
XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 257
XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 113
XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 55
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2184
XF\App->run() in src/XF.php at line 391
XF::runApp() in admin.php at line 13

 

[Joshua]

Or maybe just confirm for me whether or not I can use the queries posted above? Pretty please.

 

[Chris D]

I'm not exactly seeing the correlation between the error you posted and this thread. The errors are totally different.

Is the error in your first post the only one you are seeing or are there others related to watermarks?

 

[Joshua]

My bad I assumed they were related as failed XMG upgrades but I see now that that wasn't a smart assumption. The error I posted in #11 is the only error currently.

 

[Chris D]

Ok.

Can you run this query on your database and let me know what it returns?

SHOW CREATE TABLE xf_mg_media_item

Or show a screenshot of the "Structure" page for that table in PhpMyAdmin?

 

[Joshua]

+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xf_mg_media_item | CREATE TABLE `xf_mg_media_item` (
  `media_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `media_hash` varchar(32) DEFAULT NULL,
  `title` text NOT NULL,
  `description` text NOT NULL,
  `media_date` int(10) unsigned NOT NULL DEFAULT '0',
  `last_edit_date` int(10) NOT NULL DEFAULT '0',
  `last_comment_date` int(10) unsigned NOT NULL DEFAULT '0',
  `last_comment_id` int(10) unsigned NOT NULL DEFAULT '0',
  `last_comment_user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `last_comment_username` varchar(50) NOT NULL DEFAULT '',
  `media_type` varbinary(25) NOT NULL,
  `media_tag` text,
  `media_embed_url` text,
  `media_state` enum('visible','moderated','deleted') NOT NULL DEFAULT 'visible',
  `album_id` int(10) NOT NULL DEFAULT '0',
  `category_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(50) NOT NULL,
  `ip_id` int(10) unsigned NOT NULL DEFAULT '0',
  `likes` int(10) unsigned NOT NULL DEFAULT '0',
  `like_users` blob,
  `comment_count` int(10) unsigned NOT NULL DEFAULT '0',
  `rating_count` int(10) unsigned NOT NULL DEFAULT '0',
  `view_count` int(10) unsigned NOT NULL DEFAULT '0',
  `rating_sum` int(10) unsigned NOT NULL DEFAULT '0',
  `rating_avg` float unsigned NOT NULL DEFAULT '0',
  `custom_fields` mediumblob NOT NULL,
  `media_content_tag_cache` blob,
  `exif_data` mediumblob NOT NULL,
  `watermarked` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `warning_id` int(10) unsigned NOT NULL DEFAULT '0',
  `warning_message` varchar(255) NOT NULL DEFAULT '',
  `position` int(10) unsigned NOT NULL DEFAULT '0',
  `imported` int(10) unsigned NOT NULL DEFAULT '0',
  `thumbnail_date` int(10) unsigned NOT NULL DEFAULT '0',
  `custom_thumbnail_date` int(10) unsigned NOT NULL DEFAULT '0',
  `tags` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`media_id`),
  UNIQUE KEY `media_hash` (`media_hash`),
  KEY `position` (`position`),
  KEY `media_date_media_id` (`media_date`,`media_id`),
  KEY `media_date` (`media_date`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=380 DEFAULT CHARSET=utf8 |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [apistogrammadb]>

 

[Chris D]

There's two issues here.

1. Do you know why the table is in MyISAM rather than InnoDB?
2. The tags field is an integer rather than a blob. Do you know why that might be?

To fix the first one (unless there is a specific reason you changed it) you need to execute:

 ALTER TABLE xf_mg_media_item ENGINE=InnoDB;

To fix the second one I guess it depends why that field is in that format. I don't want to drop it exactly so the best thing to do might be:

ALTER TABLE xf_mg_media_item CHANGE tags tags_bak INT(10) UNSIGNED;

Then add the correct column:

ALTER TABLE xf_mg_media_item ADD COLUMN tags MEDIUMBLOB;

If whatever data was in tags is somehow significant, at least the data is still there.

 

[Joshua]

I guess MyISAM from a previous xf instance?
No idea on #2. Your queries fixed it for me though. Thank you kindly!

 

[Chris D]

Nope. InnoDB would have always been default.

It might be worth checking to see if your other tables are MyISAM. There are only a few that shouldn’t be InnoDB.