Error with database admin and backsticks in table/field names

[Kirill Shatalaev]

Hello.

Dont know why, but I see tables and fields surrounded by backticks on appadmin.

For example:

`component`.`id`	`component`.`name`

So, when I try to sort table clicking by field name, I get error:

Traceback (most recent call last):
  File "/home/ksh/PycharmProjects/barlist/web2py/applications/barlist/controllers/appadmin.py", line 266, in select
    orderby=eval_in_global_env(orderby))
  File "/home/ksh/PycharmProjects/barlist/web2py/applications/barlist/controllers/appadmin.py", line 95, in eval_in_global_env
    exec ('_ret=%s' % text, {}, global_env)
  File "<string>", line 1
    _ret=db.`component`.`id`
            ^
SyntaxError: invalid syntax

How can be this fixed? I use no mysql reserved keywords on table's/field's names, as far as I know.

[Dave S]

Where do you see this?  In the appadmin/select page, where queries are entered?  (the default query is db.tablename.id > 0)

(path is https:...server/appadmin/select/db?query=db.logtable.id%3E0, where %3E encodes '>')

/dps

[Kirill Shatalaev]

Where do you see this?  In the appadmin/select page, where queries are entered?  (the default query is db.tablename.id > 0)

(path is https:...server/appadmin/select/db?query=db.logtable.id%3E0, where %3E encodes '>')

/dps

Yes, exactly.

Table header links to:

http://127.0.0.1:8000/barlist/appadmin/select/db?orderby=`component`.`id`

If I remove '`' from url, sort query works as expected.

 

[Dave S]

What version of web2py is this happening on?

In the case I'm looking at, it's db?orderby.logtable.status with no tick marks.

I'm using 2.14.6 (with Rocket 1.2.6 for development)

Does this happen just with your app, or with the welcome app as well?

/dps
 

[Kirill Shatalaev]

 

What version of web2py is this happening on?

web2py Web Framework

Created by Massimo Di Pierro, Copyright 2007-2016

Version 2.14.6-stable+timestamp.2016.05.10.00.21.47

 

Does this happen just with your app, or with the welcome app as well?

With my app. Welcome is not affected, but it uses sqlite, and my app uses mysql. Backticks is mysql special chars, you can use  it if you want to use mysql reserved words in your table name of field name (i.e., "select", but I do not have such names on my model.

So there is a setting on scaffolding db.py:

db = DAL(myconf.get('db.uri'),
         pool_size=myconf.get('db.pool_size'),
         migrate_enabled=myconf.get('db.migrate'),
         check_reserved=['all'])

check_reserved = all, not sure if this trouble related with it? Maybe my table or field names intersect with reserved words in other databases?

This is a part of my sql.log, so 2016-10-06 everything is OK.

2016-10-12, I defined some additional tables in model, nothing more changed. And now I'm in this tick troubles.

timestamp: 2016-10-06T22:50:05.440741
CREATE TABLE component_manufacturer(
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(30),
    PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;
success!
timestamp: 2016-10-06T22:54:05.131029
CREATE TABLE component(
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(50),
    alcoholic CHAR(1),
    alcohol_by_vol INT,
    component_group INT  , INDEX component_group__idx (component_group), FOREIGN KEY (component_group) REFERENCES component_group (id) ON DELETE CASCADE,
    component_manufacturer INT  , INDEX component_manufacturer__idx (component_manufacturer), FOREIGN KEY (component_manufacturer) REFERENCES component_manufacturer (id) ON DELETE CASCADE,
    PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;
success!
timestamp: 2016-10-12T00:08:19.416900
ALTER TABLE `auth_permission` ADD `record_id__tmp` INTEGER;
success!
UPDATE `auth_permission` SET `record_id__tmp`=`record_id`;
success!
ALTER TABLE `auth_permission` DROP COLUMN `record_id`;
success!
ALTER TABLE `auth_permission` ADD `record_id` INTEGER;
success!
UPDATE `auth_permission` SET `record_id`=`record_id__tmp`;
success!
ALTER TABLE `auth_permission` DROP COLUMN `record_id__tmp`;
success!
success!
timestamp: 2016-10-12T00:08:22.863067
CREATE TABLE `component_state`(
    `id` INT AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(10),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET utf8;
success!
timestamp: 2016-10-12T00:08:22.874621
ALTER TABLE `component` ADD `component_state` INT  , ADD INDEX `component_state__idx` (`component_state`), ADD FOREIGN KEY (`component_state`) REFERENCES `component_state` (`id`) ON DELETE CASCADE;
success!
timestamp: 2016-10-12T00:08:24.065036
ALTER TABLE `component` ADD `alcohol_by_vol__tmp` INTEGER;
success!
UPDATE `component` SET `alcohol_by_vol__tmp`=`alcohol_by_vol`;
success!
ALTER TABLE `component` DROP COLUMN `alcohol_by_vol`;
success!
ALTER TABLE `component` ADD `alcohol_by_vol` INTEGER;
success!
UPDATE `component` SET `alcohol_by_vol`=`alcohol_by_vol__tmp`;
success!
ALTER TABLE `component` DROP COLUMN `alcohol_by_vol__tmp`;
success!
success!
timestamp: 2016-10-12T00:08:28.920658
CREATE TABLE `component_measurements`(
    `id` INT AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(15),
    `component_state` INT  , INDEX `component_state__idx` (`component_state`), FOREIGN KEY (`component_state`) REFERENCES `component_state` (`id`) ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET utf8;
success!
timestamp: 2016-10-12T00:08:28.932209
CREATE TABLE `measurement_plurals`(
    `id` INT AUTO_INCREMENT NOT NULL,
    `plural_form` VARCHAR(15),
    `component_measurement` INT  , INDEX `component_measurement__idx` (`component_measurement`), FOREIGN KEY (`component_measurement`) REFERENCES `component_measurements` (`id`) ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET utf8;
success!
timestamp: 2016-10-12T01:13:15.007933
CREATE TABLE `plural_case`(
    `id` INT AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(15),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET utf8;
success!
timestamp: 2016-10-12T01:13:15.053358
ALTER TABLE `measurement_plurals` ADD `plural_case` INT  , ADD INDEX `plural_case__idx` (`plural_case`), ADD FOREIGN KEY (`plural_case`) REFERENCES `plural_case` (`id`) ON DELETE CASCADE;
success!

[Dave S]

On Tuesday, October 11, 2016 at 9:22:39 PM UTC-7, Kirill Shatalaev wrote:

 

What version of web2py is this happening on?

web2py Web Framework

Created by Massimo Di Pierro, Copyright 2007-2016

Version 2.14.6-stable+timestamp.2016.05.10.00.21.47

 

Does this happen just with your app, or with the welcome app as well?

With my app. Welcome is not affected, but it uses sqlite, and my app uses mysql. Backticks is mysql special chars, you can use  it if you want to use mysql reserved words in your table name of field name (i.e., "select", but I do not have such names on my model.

So there is a setting on scaffolding db.py:

db = DAL(myconf.get('db.uri'),
         pool_size=myconf.get('db.pool_size'),
         migrate_enabled=myconf.get('db.migrate'),
         check_reserved=['all'])

check_reserved = all, not sure if this trouble related with it? Maybe my table or field names intersect with reserved words in other databases?

This is a part of my sql.log, so 2016-10-06 everything is OK.

2016-10-12, I defined some additional tables in model, nothing more changed. And now I'm in this tick troubles.

Can you post your table declarations?  Taking a quick look at the sql.log (and I'm not a mysql user), but the index declarations look different between Oct 06 and Oct  10:

 

timestamp: 2016-10-06T22:50:05.440741

 

[...] 

CREATE TABLE component(
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(50),
    alcoholic CHAR(1),
    alcohol_by_vol INT,
    component_group INT  , INDEX component_group__idx (component_group), FOREIGN KEY (component_group) REFERENCES component_group (id) ON DELETE CASCADE,
    component_manufacturer INT  , INDEX component_manufacturer__idx (component_manufacturer), FOREIGN KEY (component_manufacturer) REFERENCES component_manufacturer (id) ON DELETE CASCADE,
    PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;
success!

[...] 

timestamp: 2016-10-12T00:08:22.874621
ALTER TABLE `component` ADD `component_state` INT  , ADD INDEX `component_state__idx` (`component_state`), ADD FOREIGN KEY (`component_state`) REFERENCES `component_state` (`id`) ON DELETE CASCADE;
success!

[...] 

/dps
 

[Kirill Shatalaev]

Can you post your table declarations?  Taking a quick look at the sql.log (and I'm not a mysql user), but the index declarations look different between Oct 06 and Oct  10:

# -*- coding: utf-8 -*-

db.define_table('component_group',
                Field('name', 'string', length=30, required=True),
                format='%(name)s')


db.define_table('component_manufacturer',
                Field('name', 'string', length=30, required=True),
                format='%(name)s')


db.define_table('component_state',
                Field('name', 'string', length=10, required=True),
                format='%(name)s')


db.define_table('component',
                Field('name', 'string', length=50, required=True),
                Field('alcoholic', 'boolean', default=False),
                Field('alcohol_by_vol', 'integer', default=0),
                Field('component_group', db.component_group, required=True),
                Field('component_manufacturer', db.component_manufacturer, required=True, default=1),
                Field('component_state', db.component_state),
                format='%(name)s')


db.define_table('component_measurements',
                Field('name', 'string', length=15),
                Field('component_state', db.component_state),
                format='%(name)s')


db.define_table('plural_case',
                Field('name', 'string', length=15, required=True),
                format='%(name)s')


db.define_table('measurement_plurals',
                Field('plural_form', 'string', length=15),
                Field('plural_case', db.plural_case),
                Field('component_measurement', db.component_measurements),

format='%(plural_form)s')