1054, "Unknown column in 'field list'... Help me

[sujin...@gmail.com]

I had made MXPDEVICE table, and I added MXPPCPLATFORM table yesterday.

Then definition of MXPDEVICE changed as below (Red color part).

-------------------------------------------------------------------------------------------------------------------------

db.define_table('MXPDEVICE',

    Field('NAME','string', notnull=True, label='Device'),

    Field('MODEL_NO','string', required=True),

    Field('PLATFORM_ID', db.MXPPLATFORM, ondelete='NO ACTION', required=True, label='Platform'),

    Field('PLATFORM_VERSION',required=True),

    Field('PC_PLATFORM_ID', db.MXPPCPLATFORM, ondelete='NO ACTION', required=True, label='PC Platform'),

    Field('PC_PLATFORM_VERSION',required=True),

    Field('DEVICE_INFO', 'text', required=True),

    Field('DEFAULT_TESTER_ID', db.MXPTESTER, required=True, ondelete='NO ACTION', label = 'Tester'),

    Field('DEFAULT_TEST_DEVICE_YN', 'boolean', default=True),

    Field('USE_YN', 'boolean', default=True, writable=False, readable = False ),

    Field('CREATED_ON',  'datetime', default=request.now,  writable=False ),

    Field('UPDATED_ON',  'datetime', update=request.now,  writable=False  ))

-------------------------------------------------------------------------------------------------------------------------

and then I got Error......

=======================================================================

Database db select

Traceback

    Traceback (most recent call last):

  File "/home/mxptest/web2py/applications/mxpTest/controllers/appadmin.py", line 270, in select

    *fields, limitby=(start, stop))

  File "/home/mxptest/web2py/gluon/dal.py", line 10335, in select

    return adapter.select(self.query,fields,attributes)

  File "/home/mxptest/web2py/gluon/dal.py", line 1831, in select

    return self._select_aux(sql,fields,attributes)

  File "/home/mxptest/web2py/gluon/dal.py", line 1796, in _select_aux

    self.execute(sql)

  File "/home/mxptest/web2py/gluon/dal.py", line 1916, in execute

    return self.log_execute(*a, **b)

  File "/home/mxptest/web2py/gluon/dal.py", line 1910, in log_execute

    ret = self.cursor.execute(command, *a[1:], **b)

  File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 166, in execute

    self.errorhandler(self, exc, value)

  File "/usr/lib/pymodules/python2.7/MySQLdb/connections.py", line 35, in defaulterrorhandler

    raise errorclass, errorvalue

OperationalError: (1054, "Unknown column 'MXPDEVICE.PC_PLATFORM_ID' in 'field list'")

=======================================================================

I thought it's because of migration.

but even though I changed the option, It wasn't solved.....

Thank you for your answer!!

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hi.

I tried adding new columns, but I got that error 1054, "Unknown column in 'field list'.............

As web2py document

(set fake_migrate=True and after the metadata has been rebuilt, setfake_migrate=False and migrate the table again).--- I'm using MySQL), I did it but the problem was not solved.

I searched and tried many things... change migration option, delete .table file etc...  But It didn't work.

Is anybody who knows solution? Help meeeeeeeeeeee

Thank you so much!!! :)

[Anthony]

Generally helps to show the code and full traceback.

[sujin...@gmail.com]

I had made MXPDEVICE table, and I added MXPPCPLATFORM table yesterday.

Then definition of MXPDEVICE changed as below (Red part).

2014년 3월 10일 월요일 오후 3시 58분 29초 UTC+9, sujin...@gmail.com 님의 말:

[sujin...@gmail.com]

In sql.log and  database/******.MXPDEVICE.table file

has that column info.

but when I search in MySQL, MXPDEVICE table doesn't have that column......

What should I do???

Should I drop the table?????? T T......................................... 

2014년 3월 10일 월요일 오후 3시 58분 29초 UTC+9, sujin...@gmail.com 님의 말:

I had made MXPDEVICE table, and I added MXPPCPLATFORM table yesterday.

Then definition of MXPDEVICE changed as below (Red color part).

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[Anthony]

Looks like the new fields didn't get added to the database. Do you have migrations turned off?

[Johann Spies]

I do not use mysql but postgresql.  I would try the following:

* using the database backend export the table to a csv-file

* drop the table in the database backend.

* remove the table's entry in applications/yourapp/databases (be careful to delete the correct file).  Maybe move it to another temporary location to be save.

* do a 'python web2py.py -M -S application/[your controller] on the commandline or run appadmin.

* check whether the table creation was successful

* adapt the csv-file to your new format. The easiest way for me is to export the new empty table to get the correct headings. Then, if it is not too large for a program like localc/oocalc/gnumeric/excel you can use a spreadsheet program to combine the two csv-files. Otherwise you can do it using sed and awk.

* import the adapted csv-file.

Regards
Johann

--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[Mark Li]

I've had this happen several times, and the solution provided in this link have worked for me.

http://comments.gmane.org/gmane.comp.python.web2py/42344

On Sunday, March 9, 2014 11:58:29 PM UTC-7, sujin...@gmail.com wrote:

I had made MXPDEVICE table, and I added MXPPCPLATFORM table yesterday.

Then definition of MXPDEVICE changed as below (Red color part).

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[pa...@cancamusa.net]

Hello:

I'm getting this problem and, like the author, deleting the database folder and fake_migrating did not help.

The link to the "the solution provided in this link" above is broken: could anyone please copy and paste it, or link to newer documentation, since that comment from 2014 may not work in 2021 with python 3 :-/

Have a nice day

[pa...@cancamusa.net]

My problem came from importing a sql database backup. I had to checkout the version which was used to make that backup, then update the code and trust the web2py migrations, which worked, then make a new backup for the test data.

Regards

[pa...@cancamusa.net]

It's also important to use fake_migrate_all=True, besides fake_migrate=True and migrate=True.