topic ERROR unknown column 'airline_cd' in field list in Talend Studio

ERROR unknown column 'airline_cd' in field list

Anonymous — Thu, 27 Feb 2020 02:46:04 GMT

I'm trying to migrate MSSQL table to Mysql, the following error occurs.

[statistics] connecting to socket on port 3999
[statistics] connected
Thu Feb 27 11:13:35 JST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Unknown column 'airline_cd' in 'field list'
[statistics] disconnected

Here's my schema

I have mapped everything accordingly, but still the errors occur.

Is there a way to fixed this?

iDBInput_1 ---row1(Main)--tMap_1--row(Main)--tDBOutput1

Re: ERROR unknown column 'airline_cd' in field list

Anonymous — Thu, 27 Feb 2020 02:51:40 GMT

Hi
Can you check if the field 'airline_cd' already exists in source table and target table?

Regards
Shong

Re: ERROR unknown column 'airline_cd' in field list

Anonymous — Thu, 27 Feb 2020 03:03:40 GMT

yes, it exist.
now I tried to change table action to 'drop' or 'drop if table exist) and data action to 'insert'/'replace'.

This time nothing happens.

[statistics] connecting to socket on port 3549
[statistics] connected
[statistics] disconnected

PS. This is a different question though, is it possible to migrate a DB (mssql) to a blank mysql DB?(without creating a schema/table in mysql in advance).

Re: ERROR unknown column 'airline_cd' in field list

Anonymous — Thu, 27 Feb 2020 04:00:51 GMT

The table should exist in target DB (Mysql), if it does not exist, it will throw the error 'the table does exist' during the execution.
In the action of table list, if you select:
drop table and create: it will drop the table and create a new table, if the table does not exist, it will an error.
create table if it does not exist: it will create the table if it does not exist.
drop table if exist and create: drop the table if the table already exists and create.

Regards
Shong

Re: ERROR unknown column 'airline_cd' in field list

Anonymous — Thu, 27 Feb 2020 04:25:17 GMT

i just realized that there seems to be problem with mysql table.

When I tried to tLogRow for mysqlInput, the column is unknown too.

Is there a way to fixed this? I think the problem roots from here.

tDBInput2 -- row2(Main) tLogRow_2

Exception in component tDBInput_2 (test3)
java.sql.SQLSyntaxErrorException: Unknown column 'agent_ftp_infos.id' in 'field list'

Here I realized the difference between mssql table(which is working fine).

Schema name is taken as database name here, so the schema is not included in the automatically generated query.

(i tried to insert a correct db and schema name, but syntax error appears)

traveljp is supposed to ba schema name, but it's recognize as CATALOG(database name?) here. FYI, the database is mysql localinstance

Re: ERROR unknown column 'airline_cd' in field list

Anonymous — Thu, 27 Feb 2020 05:09:17 GMT

Click '...' button to open the SQL builder where you can edit and run your query for testing, check the table structure if it contains id column.

Re: ERROR unknown column 'airline_cd' in field list

Anonymous — Thu, 27 Feb 2020 05:48:23 GMT

I checked the table structure and found the id column

However the columns were all highlighted in red

Re: ERROR unknown column 'airline_cd' in field list

Anonymous — Thu, 27 Feb 2020 06:33:58 GMT

It's weird, I have no idea why it is highlighted in red, maybe it is permissions issue? What about other tables?

Re: ERROR unknown column 'airline_cd' in field list

Anonymous — Fri, 28 Feb 2020 02:11:49 GMT

I think I found the reason.

After I execute the job, for some reason, some output tables are deleted

therefore, when I tried to execute the job again, the command could not be executed as the table didn't exist anymore

It took me a long time to realized as the table status is not automatically updated on Talend,

I went to check in mysql workbench.

Re: ERROR unknown column 'airline_cd' in field list

Elison1 — Mon, 30 Aug 2021 17:05:44 GMT

Olá,

Estava com esse mesmo problema(No caso com esse erro: unknown column "null" in "field list") e decidi testar de o próprio componente de saída do banco de dados criar a tabela. Verifiquei que uma das colunas foram criadas com o nome "null", conforme esperado. Depois que identifiquei a coluna no banco, retornei ao talend e vi que o componente estava com o schema das colunas sem preenchimento, em db column. Só preencher esse coluno e deve resolver. Espero ter ajudado!

Esse erro deve ser voltado a essa lógica, entre o componente do tdboutput e o banco de dados, bom estar ciente disso. Por isso, se for o caso, escolha a opção de "drop and create table" fica mais fácil de identificar o erro.