Fixed Mysqli prepare error: Key 'media_date' doesn't exist in table 'media'

[MattW]

Just upgraded XFMG to 1.1.11, and XMFG is now causing the below errors:

Mysqli prepare error: Key 'media_date' doesn't exist in table 'media'

1. Zend_Db_Statement_Mysqli->_prepare() in Zend/Db/Statement.php at line 115
2. Zend_Db_Statement->__construct() in Zend/Db/Adapter/Mysqli.php at line 381
3. Zend_Db_Adapter_Mysqli->prepare() in Zend/Db/Adapter/Abstract.php at line 478
4. Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 791
5. Zend_Db_Adapter_Abstract->fetchCol() in XenGallery/Model/Media.php at line 475
6. XenGallery_Model_Media->getUnviewedMediaIds() in XenGallery/Listener.php at line 192
7. XenGallery_Listener::controllerPreDispatch()
8. call_user_func_array() in XenForo/CodeEvent.php at line 58
9. XenForo_CodeEvent::fire() in XenForo/Controller.php at line 313
10. XenForo_Controller->preDispatch() in XenForo/FrontController.php at line 350
11. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
12. XenForo_FrontController->run() in /home/hearth/public_html/talk/index.php at line 13

I've been through and tried to manually run the SQL queries in the upgrade PHP file, and all of them report "Duplicate key name 'XXXXXX'" when it's executing, so I'm assuming the queries have ran during the update.

I've had to disable the gallery to get the site back online.

 

[MattW]

> SHOW INDEXES FROM xengallery_media;
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xengallery_media |          0 | PRIMARY                |            1 | media_id    | A         |        2171 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | position               |            1 | position    | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | user_id_media_date     |            1 | user_id     | A         |         723 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | user_id_media_date     |            2 | media_date  | A         |        1085 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | album_id_media_date    |            1 | album_id    | A         |         310 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | album_id_media_date    |            2 | media_date  | A         |        1085 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | category_id_media_date |            1 | category_id | A         |          22 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | category_id_media_date |            2 | media_date  | A         |        1085 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

When compared to a different site:

> SHOW INDEXES FROM xengallery_media;
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xengallery_media |          0 | PRIMARY                |            1 | media_id    | A         |       11577 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | position               |            1 | position    | A         |         399 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | media_date             |            1 | media_date  | A         |       11577 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | user_id_media_date     |            1 | user_id     | A         |         964 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | user_id_media_date     |            2 | media_date  | A         |       11577 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | album_id_media_date    |            1 | album_id    | A         |        1929 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | album_id_media_date    |            2 | media_date  | A         |       11577 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | category_id_media_date |            1 | category_id | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| xengallery_media |          1 | category_id_media_date |            2 | media_date  | A         |       11577 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

 

[MattW]

Think I've fixed it running the below

ALTER TABLE xengallery_media ADD INDEX media_date (media_date);

But looking through the Install.php file, I can't see where there is an $alter to add this, and the key is only present in the CREATE table statement. Not sure if / why this was missing on here?

 

[CTXMedia]

I've just checked and I also have an index for - media_date_media_id (media_date + media_id) - in that table.

 

[Mike]

I'm going to move this to bugs. I think this error might apply if you upgraded from an old version of XMG (< 2.1.3 I think) directly to XFMG. It looks like a couple queries got wiped out at some point.

 

[Chris D]

We're re-adding this if it doesn't already exist in the next release. Thanks.