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

MattW

Well-known member
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

Well-known member
Code:
> 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:
Code:
> 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

Well-known member
Think I've fixed it running the below
Code:
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

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

Mike

XenForo developer
Staff member
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.
 
Top