I finally upgraded to 2.3 last week and it went pretty smoothly. The only thing that isn't working properly is the Collection Tree plugin. The collections all show up on the main website (http://www.jacksoncountyhistory.org) but as soon as you try to open one it gives you an SQL error. Debug is turned on right now so you can see it, but I'll quote it anyway:
exception 'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id DESC LIMIT 15' at line 3' in /var/www/html/application/libraries/Zend/Db/Statement/Mysqli.php:77
Stack trace:
#0 /var/www/html/application/libraries/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('SELECT <code>items</code>....')
#1 /var/www/html/application/libraries/Zend/Db/Adapter/Mysqli.php(388): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'SELECT <code>items</code>....')
#2 /var/www/html/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Adapter_Mysqli->prepare('SELECT <code>items</code>....')
#3 [internal function]: Zend_Db_Adapter_Abstract->query(Object(Omeka_Db_Select), Array)
#4 /var/www/html/application/libraries/Omeka/Db.php(79): call_user_func_array(Array, Array)
#5 /var/www/html/application/libraries/Omeka/Db/Table.php(648): Omeka_Db->__call('query', Array)
#6 /var/www/html/application/libraries/Omeka/Db/Table.php(648): Omeka_Db->query(Object(Omeka_Db_Select), Array)
#7 /var/www/html/application/libraries/Omeka/Db/Table.php(281): Omeka_Db_Table->fetchObjects(Object(Omeka_Db_Select))
#8 /var/www/html/application/controllers/CollectionsController.php(47): Omeka_Db_Table->findBy(Array, 15)
#9 /var/www/html/application/libraries/Zend/Controller/Action.php(516): CollectionsController->showAction()
#10 /var/www/html/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('showAction')
#11 /var/www/html/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#12 /var/www/html/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#13 /var/www/html/application/libraries/Zend/Application.php(382): Zend_Application_Bootstrap_Bootstrap->run()
#14 /var/www/html/application/libraries/Omeka/Application.php(79): Zend_Application->run()
#15 /var/www/html/index.php(23): Omeka_Application->run()
#16 {main}
Looking through the forum I didn't see this very specific error, but found problems like this regarding plugins usually involved uninstalling and reinstalling them. If we can avoid that it would be great, if not then I understand.
So far, I've moved the physical files out of the plugins directory and unzipped the new files in, which gave me an "Upgrade" button in the plugins page. After clicking that the error remained the same.
Thanks for your help!
You can try turning on SQL logging in application/config/config.ini (log.sql). That will output all the queries Omeka runs into your error log at application/logs/errors.log.
It's a very large amount of logging (so you'll want to turn it off after viewing the page causing the error and getting the query logged). The query you're looking for should be the last thing that gets logged, and it should start with
SELECT `items`
Alright, after those settings the errors seem a little different:
2015-06-01T08:10:39-10:00 ERR (3): exception 'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.<code>id</code> DESC LIMIT 1' at line 4' in /var/www/html/application/libraries/Zend/Db/Statement/Mysqli.php:77
Stack trace:
#0 /var/www/html/application/libraries/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('SELECT <code>items</code>....')
#1 /var/www/html/application/libraries/Zend/Db/Adapter/Mysqli.php(388): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'SELECT <code>items</code>....')
#2 /var/www/html/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Adapter_Mysqli->prepare('SELECT <code>items</code>....')
#3 [internal function]: Zend_Db_Adapter_Abstract->query(Object(Omeka_Db_Select), Array)
#4 /var/www/html/application/libraries/Omeka/Db.php(79): call_user_func_array(Array, Array)
#5 /var/www/html/application/libraries/Omeka/Db/Table.php(648): Omeka_Db->__call('query', Array)
#6 /var/www/html/application/libraries/Omeka/Db/Table.php(648): Omeka_Db->query(Object(Omeka_Db_Select), Array)
#7 /var/www/html/application/libraries/Omeka/Db/Table.php(281): Omeka_Db_Table->fetchObjects(Object(Omeka_Db_Select))
#8 /var/www/html/application/models/Collection.php(228): Omeka_Db_Table->findBy(Array, 1)
#9 /var/www/html/application/views/helpers/FileMarkup.php(802): Collection->getFile()
#10 /var/www/html/application/libraries/globals.php(2094): Omeka_View_Helper_FileMarkup->image_tag(Object(Collection), Array, 'square_thumbnai...')
#11 /var/www/html/admin/themes/default/collections/browse.php(36): record_image('collection', 'square_thumbnai...')
#12 /var/www/html/application/libraries/Omeka/View.php(117): include('/var/www/html/a...')
#13 /var/www/html/application/libraries/Zend/View/Abstract.php(888): Omeka_View->_run('/var/www/html/a...')
#14 /var/www/html/application/libraries/Zend/Controller/Action/Helper/ViewRenderer.php(905): Zend_View_Abstract->render('collections/bro...')
#15 /var/www/html/application/libraries/Zend/Controller/Action/Helper/ViewRenderer.php(926): Zend_Controller_Action_Helper_ViewRenderer->renderScript('collections/bro...', NULL)
#16 /var/www/html/application/libraries/Zend/Controller/Action/Helper/ViewRenderer.php(965): Zend_Controller_Action_Helper_ViewRenderer->render()
#17 /var/www/html/application/libraries/Zend/Controller/Action/HelperBroker.php(277): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#18 /var/www/html/application/libraries/Zend/Controller/Action.php(527): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#19 /var/www/html/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('browseAction')
#20 /var/www/html/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#21 /var/www/html/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#22 /var/www/html/application/libraries/Zend/Application.php(382): Zend_Application_Bootstrap_Bootstrap->run()
#23 /var/www/html/application/libraries/Omeka/Application.php(79): Zend_Application->run()
#24 /var/www/html/admin/index.php(28): Omeka_Application->run()
#25 {main}
Does this contain the SQL info you're expecting? I expected a vastly larger amount of logging, where this seemed to change what was going into the log with just a little extra info.
Ah, I believe I forgot one other required step: you need to set the logging priority in application/config/config.ini to actually print the logged queries:
log.priority = Zend_Log::DEBUG
The lines logging the SQL will start with (after the date) DEBUG (7): and then immediately show the query, without any error message.
Alright - with that set, I definitely got a huge amount of logging and SQL debug lines. The last one recorded before I turned off debugging was a tags request (How collection tree works?) and was this:
2015-06-01T08:29:07-10:00 DEBUG (7): SELECT <code>tags</code>.*, COUNT(tags.id) AS <code>tagCount</code> FROM <code>omeka_tags</code> AS <code>tags</code>
INNER JOIN <code>omeka_records_tags</code> AS <code>records_tags</code> ON records_tags.tag_id = tags.id WHERE (records_tags.record_id = 292) AND (records_tags.record_type = 'Item') GROUP BY <code>tags</code>.<code>id</code>,
<code>tags</code>.<code>id</code> ORDER BY <code>tags</code>.<code>name</code> ASC, <code>tags</code>.<code>id</code> ASC
The last Select Items query is here:
2015-06-01T08:29:07-10:00 DEBUG (7): SELECT <code>items</code>.* FROM <code>omeka_items</code> AS <code>items</code> WHERE (items.public = 1) AND (items.id IN (SELECT <code>records_tags</code>.<code>record_id</code> AS <code>items.id</code> FROM <code>omeka_records_tags</code> AS <code>records_tags</code>
INNER JOIN <code>omeka_tags</code> AS <code>tags</code> ON tags.id = records_tags.tag_id WHERE (tags.name = 'Tampico' AND records_tags.<code>record_type</code> = "Item"))) GROUP BY <code>items</code>.<code>id</code> ORDER BY <code>items</code>.<code>added</code> DESC, <code>items</code>.<code>id</code> DESC LIMIT 15
And naturally the code tags are messed up - I'm trying to get the relevant log sections out as text that I can host and share, if need be.
Are these the queries that are logged on the page that shows the error? That's the page you'd need to go to when you have the logging enabled, and then you're looking for a query that's right above the ERR exception line.
The lines you posted look like they're related to searching/displaying items with a particular tag, rather than involving a collection at all.
Ah hah! That I can do - here's a redo of the error with the specific entry:
2015-06-01T09:35:07-10:00 DEBUG (7): SELECT <code>items</code>.* FROM <code>omeka_items</code> AS <code>items</code>
INNER JOIN <code>omeka_collections</code> AS <code>collections</code> ON items.collection_id = collections.id
LEFT JOIN <code>omeka_files</code> AS <code>files</code> ON files.item_id = items.id
LEFT JOIN <code>omeka_item_order_item_orders</code> AS <code>item_order_item_orders</code> ON items.id = item_order_item_orders.item_id WHERE (collections.id = 30) AND (files.has_derivative_image = '1') GROUP BY <code>items</code>.<code>id</code> ORDER BY <code>ISNULL(item_order_item_orders</code>.<code>order), item_order_item_orders</code>.<code>order ASC, items</code>.<code>id</code> DESC LIMIT 1
This looks like it's actually a problem with the Item Order plugin, not the Collection Tree.
Upgrading to the latest version of Item Order should fix this problem.
That was it John, thank you so much. Everything is loading properly and all my collections are still there.