Error inserting new field in table

[lapega]

Describe the bug When trying to insert a new field in the table configuration, I get an error message at save.

Error: Unknown column [previous_field] in [table]

To Reproduce

1. Right click on table
2. Click on 'Configuration'
3. Use the 'Add' button to add a new field
4. Complete the data
5. Click on 'Save'

Expected behavior New field get inserted on table

Screenshots (the field campusDeshab is the new one)

Application (please complete the following information):

• App version 0.5.9
• Installation source: Snap Store

Environment (please complete the following information):

• OS name: Ubuntu 22.04
• DB name: MySql
• DB version: 8.0.29

[Fabio286]

Hi @lapega, I did some tries but I'm unable to reproduce. Can you give me more informations? It happens on every table, or just that one? Have you done some other operations on that table before ti insert a new row?

[lapega]

I've tried insert new fields in three different databases, several tables per each database (but always MySql), but the problem occurs every time.

It tried also when the app is just started, after a while using it, and after editing some fields. Also tested with different kind of fields, and different configurations of the new field.

If started from command line, didn't show any error when it happens.

[Fabio286]

To avoid any misunderstanding, can you confirm that this is what generates the error in your case?

I'm going crazy but I just can't replicate the conditions that generate the error. I have tried from both Windows 11 and Ubuntu 22.04 on MySQL. Are you connecting in SSL and / or via SSH? Can you write me the permissions that your database user has (SHOW GRANTS FOR your_user)?

[lapega]

Yes, I was trying the same process that is on your video. When clicking at 'Save', I got the error.

Today I tried it on another machine, same db connection, and didn't have any problems. In the computer where I have the issue, I can edit the table by using Mysql-Workbench, so it isn't a permissions problem.

Both computers have the same configuration (Ubuntu 22.04, Xorg, fully updated, Antares installed from Snap, same connection access to db).

The only difference is that the one with the problem has a nvidia card, but nothing more.

There's anything else I can try?

[Fabio286]

I begin to suspect that the problem is something like the corruption of your Antares installation, perhaps due to an update attempt that went wrong. Have you tried uninstalling and reinstalling?

[lapega]

Just tried that, same problem. When removed, it deleted saved connections, so I guess is like a fresh install.

[Fabio286]

Thanks! Can you try if with AppImage distribution you have same problems?

[lapega]

Tried the appimage instance, same problem.

Grabación de pantalla desde 13-07-22 11:14:31.webm

[Fabio286]

Could you try to start Antares in debug mode to send me the queries it executes in your case? If so, I'll explain how to do it.

[lapega]

Yes, of course!

[Fabio286]

Sorry for delay but i just implemented in v0.5.10 a console that shows queries that Antares do under the hood so that is more easy to find bugs. You can open this console from the app footer or with CTRL+` / CTRL+F12.

[lapega]

Woow, the new console is very useful. Thanks for that!

This is the query that fail: (I use an image because Github deletes some chars)

The error displayed is:

Error: Unknown column 'accion' in 'logs'

And this is the table structure:

[lapega]

Also, I find that the problem depends on the mysql server, so the same query is ok in a server, and gives error on another.

Here's the query in Server A (error given):

Same table modification on Server B (no error):

Maybe a problem with table encoding? (utf8_general_ci vs latin1_swedish_ci)

[Fabio286]

(I use an image because Github deletes some chars)

To avoid problems you can wrap the code in:

Thanks for collaboration. Now I have some other clues, I will investigate this strange behavior.

[Fabio286]

Also, I find that the problem depends on the mysql server, so the same query is ok in a server, and gives error on another.

Have those servers the same MySQL version?

Can you show me the output of following query from both no error server that server with error? (From Antares query tab select "No schema").

SHOW GLOBAL VARIABLES LIKE '%sql_mode%'

[lapega]

On both databases I got the same result: sql_mode = ""

[lapega]

Now that I have access to the executed query, I can paste it here.

ALTER TABLE `intranet`.`cs_eac` ADD COLUMN `Field_1` TINYINT(4) NOT NULL COLLATE latin1_swedish_ci AFTER `eac`, CHANGE COLUMN `id_eac` `id_eac` INT(3) NOT NULL FIRST, CHANGE COLUMN `eac` `eac` VARCHAR(200) NOT NULL COLLATE utf8_general_ci AFTER `id_eac`

The workaround I found is deleting everything after "CHANGE COL...":

ALTER TABLE `intranet`.`cs_eac` ADD COLUMN `Field_1` TINYINT(4) NOT NULL COLLATE latin1_swedish_ci AFTER `eac`

[Fabio286]

Hi @lapega, In the case you reported above would you be able to test the following query?

ALTER TABLE `intranet`.`cs_eac`
ADD COLUMN `Field_1` TINYINT(4) NOT NULL COLLATE latin1_swedish_ci AFTER `eac`,
MODIFY COLUMN `id_eac` INT(3) NOT NULL FIRST,
MODIFY COLUMN `eac` VARCHAR(200) NOT NULL COLLATE utf8_general_ci AFTER `id_eac`

[lapega]

Hi!

Tested, same problem:

[Fabio286]

I still could not replicate the error conditions. I suspect it is something related to the server configuration. I was hoping to get a few more clues but in a year no one has reported a similar problem to me except you.

Can you please try to put the ADD statement after the CHANGEs?

Example:

ALTER TABLE `intranet`.`table_A`
CHANGE COLUMN `field_1` `field_1` TINYINT(4) NOT NULL FIRST,
CHANGE COLUMN `field_2` `field_2` TINYINT(4) NOT NULL AFTER `field_1`,
ADD COLUMN `field_3` TINYINT(4) NOT NULL COLLATE latin1_swedish_ci AFTER `field_2`

[lapega]

Yes, that order worked!

[Fabio286]

Yes, that order worked!

Nice! I don't know for what reason but if this solves I will test to implement it in the next release.