phpmyadmin Question posted in
The official documentation can be found here.

MySQL Undefined index: column_info when creating a view – Phpmyadmin

I have defined this query that works fine :

    SELECT `a`.`id`,
        `a`.`fld_tribunal` AS `fld_tribunal`,
        `a`.`fld_date` AS `fld_date`,
        `a`.`fld_juge` AS `fld_juge`,
        `a`.`fld_representant_prefecture` AS `fld_representant_prefecture`,
        `a`.`fld_interpretes` AS `fld_interpretes`,
        `a`.`fld_nombre_policiers` AS `fld_nombre_policiers`,
        `a`.`fld_retenus_menottes` AS `fld_retenus_menottes`,
        `a`.`fld_representants_cdv` AS `fld_representants_cdv`,
        `a`.`fld_public` AS `fld_public`,
        `a`.`fld_duree_audience` AS `fld_duree_audience`,
        `a`.`fld_duree_delibere` AS `fld_duree_delibere`
    FROM `cr_tribunaux` `a`
    INNER JOIN `list_records` `r`
        ON ((`r`.`form_id` = 8) AND (`r`.`record_id` = `a`.`id`))
    INNER JOIN `list_states` `s`
        ON ((`s`.`id` = `r`.`state_id`) AND (`s`.`form_id` = `r`.`form_id`) )
    WHERE `s`.`id`=74 OR `s`.`id`=75
    ORDER BY `a`.`fld_date` ASC

but when I try to create a view out of it using phpMyadmin it gives the error:

Notice in ./libraries/SystemDatabase.php#52
Undefined index: column_info

Backtrace

./view_create.php#140: PMAlibrariesSystemDatabase->getExistingTransformationData(string 'maindb')

When I remove the INNER JOIN statements the view is created fine so there must be something either in the syntax I’m using or (I hope not) with the tables targeted by the JOINs.
I’ve tried different combinations of parenthesis but nothing helps.

The table creation code for the three related tables is :

CREATE TABLE `cr_tribunaux` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `storage_id` int(11) NOT NULL DEFAULT '4',
 `user_id` int(11) NOT NULL DEFAULT '0',
 `created` datetime NOT NULL DEFAULT '2018-07-10 09:51:32',
 `created_by` varchar(255) NOT NULL DEFAULT '',
 `modified_user_id` int(11) NOT NULL DEFAULT '0',
 `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `modified_by` varchar(255) NOT NULL DEFAULT '',
 `fld_tribunal` text NOT NULL,
 `fld_juge` text NOT NULL,
 `fld_date` text NOT NULL,
 `fld_representant_prefecture` text NOT NULL,
 `fld_nombre_policiers` text NOT NULL,
 `fld_retenus_menottes` text,
 `fld_public` text NOT NULL,
 `fld_representants_cdv` text NOT NULL,
 `fld_duree_delibere` text,
 `fld_compte_rendu` text NOT NULL,
 `fld_remarques` text NOT NULL,
 `fld_interpretes` text NOT NULL,
 `fld_duree_audience` text NOT NULL,
 `fld_retenus_menottes_detail` text NOT NULL,
 PRIMARY KEY (`id`),
 KEY `storage_id` (`storage_id`),
 KEY `user_id` (`user_id`),
 KEY `created` (`created`),
 KEY `modified_user_id` (`modified_user_id`),
 KEY `modified` (`modified`)
) ENGINE=MyISAM AUTO_INCREMENT=747 DEFAULT CHARSET=utf8```

CREATE TABLE `list_states` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `form_id` int(11) NOT NULL DEFAULT '0',
 `title` varchar(255) NOT NULL DEFAULT '',
 `color` varchar(255) NOT NULL DEFAULT '',
 `action` varchar(255) NOT NULL DEFAULT '',
 `published` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=221 DEFAULT CHARSET=utf8


CREATE TABLE `list_records` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `form_id` int(11) NOT NULL DEFAULT '0',
 `record_id` bigint(20) NOT NULL DEFAULT '0',
 `state_id` int(11) NOT NULL DEFAULT '0',
 `reference_id` int(11) NOT NULL DEFAULT '0',
 `published` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `form_id` (`form_id`,`record_id`,`state_id`)
) ENGINE=MyISAM AUTO_INCREMENT=811 DEFAULT CHARSET=utf8
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found a way around the problem that is a good solution for my need. The view I was trying to create selects "application valid" records from the cr_tribunaux table. That view was later to be joined to a secondary table resulting in a list of "valid" records with all the columns I needed. I then created a simpler view without the JOIN with the "list_records" and "list_states" tables, and in a second view I introduce the record validation functionality.

    Oddly enough, Mysql didn't complain about that new view, so now I've a final composite view that gives me the result I was looking for :-)

    I guess that Mysql needed a little "help" to understand how to do what I wanted.

    While this solves my problem it doesn't give an answer to the underlying issue that caused the error.

    Thanks for your help.


  2. 0

    For creating view you can use below code 

    CREATE VIEW sample_test AS
SELECT `a`.`id`,
        `a`.`fld_tribunal` AS `fld_tribunal`,
        `a`.`fld_date` AS `fld_date`,
        `a`.`fld_juge` AS `fld_juge`,
        `a`.`fld_representant_prefecture` AS `fld_representant_prefecture`,
        `a`.`fld_interpretes` AS `fld_interpretes`,
        `a`.`fld_nombre_policiers` AS `fld_nombre_policiers`,
        `a`.`fld_retenus_menottes` AS `fld_retenus_menottes`,
        `a`.`fld_representants_cdv` AS `fld_representants_cdv`,
        `a`.`fld_public` AS `fld_public`,
        `a`.`fld_duree_audience` AS `fld_duree_audience`,
        `a`.`fld_duree_delibere` AS `fld_duree_delibere`
    FROM `cr_tribunaux` `a`
    INNER JOIN `list_records` `r`
        ON ((`r`.`form_id` = 8) AND (`r`.`record_id` = `a`.`id`))
    INNER JOIN `list_states` `s`
        ON ((`s`.`id` = `r`.`state_id`) AND (`s`.`form_id` = `r`.`form_id`) )
    WHERE `s`.`id`=74 OR `s`.`id`=75
    ORDER BY `a`.`fld_date` ASC;

    Even after trying with this if you get an error then it must be an issue with your PHPMyadmin. You can try with any other client(like mysqlworkbench, sqlyog, HeidiSql) depending on your operating system to create this view.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search