#4856 Error with PMA_DisplayResults->getTable()

[Snake]

So I added new column in a table, then went to browse that table, and now I get like 1000 different errors. I traced them all back to a common point, which was a call to PMA_DisplayResults->getTable(). Removing the new column also removed the errors, while adding the column back reintroduced the errors. The new column was pretty simple:

ALTER TABLE `table` ADD `name_short` TINYTEXT NULL DEFAULT NULL AFTER `name`

Here's my table with the column already added. Somewhat anonymized.

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(4) unsigned NOT NULL,
  `name` tinytext NOT NULL,
  `name_short` tinytext,
  `type` enum('1','2','3','') NOT NULL,
  `a` decimal(12,4) NOT NULL,
  `b` decimal(12,4) DEFAULT NULL,
  `c` decimal(12,4) DEFAULT NULL,
  `d` decimal(12,4) DEFAULT NULL,
  `e` enum('1','2','3','4') NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here's what the function call data looked like. Again somewhat anonymized.

PMA_DisplayResults->getTable(
    $dt_result = {mysqli_result} [5]
        current_field = 0
        field_count = 9
        lengths = null
        num_rows = 25
        type = 0
    $the_disp_mode = "urdr111101",
    $analyzed_sql = {array} [1]
        0 = {array} [18]
            querytype = "SELECT"
            select_expr_clause = "* "
            position_of_first_select = 0
            from_clause = "`table` "
            group_by_clause = ""
            order_by_clause = ""
            having_clause = ""
            limit_clause = ""
            where_clause = ""
            where_clause_identifiers = {array} [0]
            unsorted_query = "SELECT * FROM ‘table`"
            queryflags = {array} [1]
                select_from = 1
            select_expr = {array} [0]
            table_ref = {array} [1]
                0 = {array} [5]
                    expr = "table"
                    db = ""
                    table_name = "table"
                    table_alias = ""
                    table_true_name = "table"
            foreign_keys = {array} [0]
            create_table_fields = {array} [0]
            section_before_limit = "SELECT * FROM `table`"
            section_after_limit = "",
    $is_limited_display = false
)

[Snake]

Oh, forgot my setup details.

MySQL: 5.5.34
Apache: 2.4.7
PHP: 5.4.22
phpMyAdmin: 4.4.2

I was using 4.3.13 of phpMyAdmin when I first noticed the error, then upgrading to the latest version didn't seem to help.

[Madhura Jayaratne]

It would be useful if you could paste here some of the errors you get

[Snake]

Attached as requested.

[Snake]

Noticed something else: the browse page is only showing up to 8 columns when this error occurs. Some of the errors mention an undefined offset of 8, so that would seem to correlate.

[Snake]

Another thing: if I SELECT all the columns manually by name instead of with a *, i get no errors, even with the same column order.

[Madhura Jayaratne]

Do you have phpmyadmin configuration storage (usually a set of pma prefixed tables in a database named phpmyadmin. More info http://docs.phpmyadmin.net/en/latest/setup.html#phpmyadmin-configuration-storage) configured? If so, what is the CREATE_TIME for this table in pmatable_uiprefs? Does it differ from the creation time of the table?

[Snake]

Value for prefs column: {"CREATE_TIME":"2014-04-16 02:09:09","col_order":["0","1","2","3","4","5","6","7"],"col_visib":["1","1","1","1","1","1","1","1"]}

I'm noticing that none of the other values for prefs in table_uiprefs contain a "CREATE_TIME". But the create time does seem accurate.

[Snake]

Thinking that the col_order and col_visib arrays looked suspicious with only 8 columns, I set this field to just []. But the errors persist.

[Madhura Jayaratne]

Old values are probably still in the session. Can you try login out and try again?

[Snake]

Ah, yes. Logging out fixed it. So it seems to have been the col_order and col_visib settings for that table.

And I figured out how to reproduce it. Reorder columns in browse (I don't even remember doing this / maybe accidentally), add column in structure, browse, get errors.

[Madhura Jayaratne]

There is an internal mechanism that compares table creation time stored in pmatable_uiprefs and the actual table creation time of the table. When you add a column actual table creation time is updated and col_order in pmatable_uiprefs is discarded if creation time of the table is newer. Of some reason this does not seem to work in you system.

Can you try the following:

Create a new table, add a couple of records
Change column order
Ensure that create_time stored in pma__table_uiprefs and actual creation time (seen in information section of table structure page) is same
Add a new column
See whether error appears and if so check for create_time in both places

[Snake]

Yes, did exactly as instructed and still got errors. uiprefs didn't change after adding a new column. Is it really supposed to, though? The table create time doesn't change when a column is added.

[Madhura Jayaratne]

Thanks.
uiprefs is not suppose to change but the actual table create time should (even though it sounds weird). It is the difference that invalidates the uiprefs and remove the value.

What is the storage engine of this table?

By adding
$cfg['ShowDbStructureCreation'] = true;
$cfg['ShowDbStructureLastUpdate'] = true;
to your config.inc.php you can view the creation and update times of the tables in database structure page. Anything interesting about the values you see for this table?

[Snake]

These are InnoDB tables. I added those lines to the config and see the new column showing last updated date, but none display anything. Even after I subsequently modify a table, the update date isn't showing.

The creation date does show and it's accurate.

[Madhura Jayaratne]

I also do not see update time for InnoDB tables in my local machine. However creation time is updated every time I add a column. That is why creation time has been used to invalidates the uiprefs.

[Snake]

I also have no update times when either checking information_schema.tables table or running show table status.