Fixed XF\Db\Exception: MySQL statement prepare error [1054]: Unknown column 'watermark_id' in 'where clause' src/XF/Db/AbstractStatement.php:212

[John71]

Affected version

XenForo Media Gallery 1.1.7

I'm getting this MYSQL error and a 500 Internal Server Error when trying to upgrade my XFMG to XFMG 2.0

Server error log

• XF\Db\Exception: MySQL statement prepare error [1054]: Unknown column 'watermark_id' in 'where clause'
• src/XF/Db/AbstractStatement.php:212
• Generated by: John
• Dec 10, 2017 at 2:01 AM

Stack trace
UPDATE xf_mg_media_item
SET watermark_id = 1
WHERE watermark_id > 0

------------

#0 src/XF/Db/Mysqli/Statement.php(174): XF\Db\AbstractStatement->getException('MySQL statement...', 1054, '42S22')
#1 src/XF/Db/Mysqli/Statement.php(36): XF\Db\Mysqli\Statement->getException('MySQL statement...', 1054, '42S22')
#2 src/XF/Db/Mysqli/Statement.php(46): XF\Db\Mysqli\Statement->prepare()
#3 src/XF/Db/AbstractAdapter.php(69): XF\Db\Mysqli\Statement->execute()
#4 src/XF/AddOn/AbstractSetup.php(190): XF\Db\AbstractAdapter->query('\n\t\t\tUPDATE xf_m...', Array)
#5 src/addons/XFMG/Setup.php(855): XF\AddOn\AbstractSetup->query('\n\t\t\tUPDATE xf_m...')
#6 src/XF/AddOn/StepRunnerUpgradeTrait.php(117): XFMG\Setup->upgrade902000010Step6(Array)
#7 src/XF/AddOn/StepRunnerUpgradeTrait.php(74): XFMG\Setup->upgradeStepRunner(902000010, 6, Array, 902000032)
#8 src/XF/Admin/Controller/AddOn.php(437): XFMG\Setup->upgrade(Array)
#9 src/XF/Mvc/Dispatcher.php(249): XF\Admin\Controller\AddOn->actionUpgrade(Object(XF\Mvc\ParameterBag))
#10 src/XF/Mvc/Dispatcher.php(89): XF\Mvc\Dispatcher->dispatchClass('XF:AddOn', 'upgrade', 'html', Object(XF\Mvc\ParameterBag), 'listAddOns', Object(XF\Admin\Controller\AddOn), NULL)
#11 src/XF/Mvc/Dispatcher.php(41): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#12 src/XF/App.php(1879): XF\Mvc\Dispatcher->run()
#13 src/XF.php(328): XF\App->run()
#14 admin.php(13): XF::runApp('XF\\Admin\\App')
#15 {main}

Not sure what else to do at this point. The Forums seem to be working well but, this upgrade fails and I'm seeing this with the Server Logs.

 

[Chris D]

Hopefully everything else otherwise worked fine, so running the following query manually on your server should allow the upgrade process to proceed:

ALTER TABLE `xf_mg_media_item`
ADD `watermark_id` INT UNSIGNED NOT NULL DEFAULT '0'

 

[John71]

Thank you. I will try this.

 

[John71]

Ok, @Chris D When I tried to run the upgrade again it gave me another Internal Server Error and now lists this error on the forums admin.

Server error log

• XF\Db\Exception: MySQL statement prepare error [1146]: Table 'jshallo_xenforo.xengallery_watermark' doesn't exist
• src/XF/Db/AbstractStatement.php:212
• Generated by:
• Dec 11, 2017 at 5:06 PM

Stack trace
SELECT watermark_id
FROM xengallery_watermark
WHERE is_site = 1
ORDER BY watermark_date DESC
LIMIT 1

------------

#0 src/XF/Db/Mysqli/Statement.php(174): XF\Db\AbstractStatement->getException('MySQL statement...', 1146, '42S02')
#1 src/XF/Db/Mysqli/Statement.php(36): XF\Db\Mysqli\Statement->getException('MySQL statement...', 1146, '42S02')
#2 src/XF/Db/Mysqli/Statement.php(46): XF\Db\Mysqli\Statement->prepare()
#3 src/XF/Db/AbstractAdapter.php(69): XF\Db\Mysqli\Statement->execute()
#4 src/XF/Db/AbstractAdapter.php(81): XF\Db\AbstractAdapter->query('\n\t\t\tSELECT wate...', Array)
#5 src/addons/XFMG/Setup.php(864): XF\Db\AbstractAdapter->fetchOne('\n\t\t\tSELECT wate...')
#6 src/XF/AddOn/StepRunnerUpgradeTrait.php(117): XFMG\Setup->upgrade902000010Step6(Array)
#7 src/XF/AddOn/StepRunnerUpgradeTrait.php(74): XFMG\Setup->upgradeStepRunner(902000010, 6, Array, 902000032)
#8 src/XF/Admin/Controller/AddOn.php(437): XFMG\Setup->upgrade(Array)
#9 src/XF/Mvc/Dispatcher.php(249): XF\Admin\Controller\AddOn->actionUpgrade(Object(XF\Mvc\ParameterBag))
#10 src/XF/Mvc/Dispatcher.php(89): XF\Mvc\Dispatcher->dispatchClass('XF:AddOn', 'upgrade', 'html', Object(XF\Mvc\ParameterBag), 'listAddOns', Object(XF\Admin\Controller\AddOn), NULL)
#11 src/XF/Mvc/Dispatcher.php(41): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#12 src/XF/App.php(1879): XF\Mvc\Dispatcher->run()
#13 src/XF.php(328): XF\App->run()
#14 admin.php(13): XF::runApp('XF\\Admin\\App')
#15 {main}
Request state
array(4) {
["url"] => string(38) "/forums/admin.php?add-ons/XFMG/upgrade"
["referrer"] => string(67) "http://www.cruise-addicts.com/forums/admin.php?add-ons/XFMG/upgrade"
["_GET"] => array(1) {
["add-ons/XFMG/upgrade"] => string(0) ""
}
["_POST"] => array(7) {
["_xfProcessing"] => string(1) "1"
["continue"] => string(1) "1"
["confirm"] => string(1) "1"
["params"] => string(2) "[]"
["count"] => string(1) "1"
["finished"] => string(1) "0"
["_xfToken"] => string(8) "********"
}
}



I'm also gettings allot of the "Oops Something Went Wrong! Messages and trying to look at the web console to figure out what the cause is.

One thing I'm seeing is this: full Failed to load resource: the server responded with a status of 404 (Not Found) for this:

http://www.cruise-addicts.com/forums/media/x-logo.51327/full

 

[John71]

I also just got this from the console.

admin.php Failed to load resource: the server responded with a status of 500 (Internal Server Error)

 

[Chris D]

The upgrade isn't finished yet so XFMG will be disabled until it is.

I think this relates to upgrades from a particularly old version of XFMG but these issues have been fixed for the next release. For now, it's just a case of re-adding a few queries manually.

This query should allow it to move on:

CREATE TABLE IF NOT EXISTS `xengallery_watermark` (
  `watermark_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `watermark_user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `watermark_date` int(10) unsigned NOT NULL DEFAULT '0',
  `is_site` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`watermark_id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci

 

[John71]

Tried again, new error. @Chris D

XF\Db\DuplicateKeyException: MySQL query error [1062]: Duplicate entry 'xfmgWatermarking' for key 'PRIMARY' in src/XF/Db/AbstractStatement.php at line 212

1. XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 174
2. XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 70
3. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 69
4. XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 146
5. XF\Db\AbstractAdapter->insert() in src/addons/XFMG/Setup.php at line 903
6. XFMG\Setup->upgrade902000010Step6() in src/XF/AddOn/StepRunnerUpgradeTrait.php at line 117
7. XFMG\Setup->upgradeStepRunner() in src/XF/AddOn/StepRunnerUpgradeTrait.php at line 74
8. XFMG\Setup->upgrade() in src/XF/Admin/Controller/AddOn.php at line 437
9. XF\Admin\Controller\AddOn->actionUpgrade() in src/XF/Mvc/Dispatcher.php at line 249
10. XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 89
11. XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 41
12. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 1879
13. XF\App->run() in src/XF.php at line 328
14. XF::runApp() in admin.php at line 13

 

[Chris D]

One last query, hopefully.

DELETE FROM xf_option
WHERE option_id = 'xfmgWatermarking'

 

[John71]

Thanks @Chris D , That did it. It gave me an almost constant Internal Server error repeatedly where I had to press the back arrow a zillion times until it filled up three lines of dots. It finally finished.

I'm not sure what else is wrong with my setup. I'm getting allot of 500 Internal Server Errors. Its kinda driving me nuts. Whats odd is the functions work and even if its throws me a 500 Error when posting or something the message is either already posted on the page, so all functions are working but, its throwing these errors.

 

[Chris D]

Troubleshooting those can be challenging, but generally they are demonstrating issues very early in the request, so before XF is even running properly. We've seen them happen on some lower end hosts during/after higher load (which would fit with the upgrade process).

If the issues continue, you (or your host) will need to inspect the web server / PHP error logs to see if that can shed any light.