ERROR unknown column 'airline_cd' in field list

[Anonymous]

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

[Anonymous]

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

Regards
Shong

[Anonymous]

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).

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

I checked the table structure and found the id column

However the columns were all highlighted in red

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[Elison1]

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.