Skip to main content
  • EN
Question

MariaDB/MySQL writer not ignoring calculated columns

  • 25 July 2024
  • 5 replies
  • 33 views

Hi folks,

 

I’m trying to setup a writer to a MySQL database table with a calculated column. When importing the table from the data source, FME (correctly) ignores the calculated field, so when I look at the table’s attributes (screenshot below), the column is not listed. However, when I try to use the writer, the calculated field gets included in the generated SQL insert and so the write itself fails with an error that a calculated field can’t be written to:

Error executing SQL query ('INSERT INTO `StageDate` (`Date`,`Year`,`MonthNumber`,`MonthName`,`Day`,`Weekday`,`WeekdayName`,`IsWeekend`,`AcademicYearCode`,`AcademicYearDescription`,`FiscalYearDescription`,`Checksum`) VALUES ('19000103','1900','1','January','3','3','Wednesday','0','1899','1899-1900','1899-1900',NULL),...: 'The value specified for generated column 'Checksum' in table 'StageDate' is not allowed.'

 

Note the `Checksum` in the column list and the NULL at the end of the feature values.

 

We use this same technique with an Oracle database and it works well - the Oracle writer completely ignores the calculated column. Is there any way to adjust the MySQL writer parameters to do the same thing? (I see a similar issue with the PostgreSQL writer, but generating my own SQL statements seems a less than ideal solution to this.)

 

Thanks!

 

 

5 replies

virtualcitymatt
Rank
Userlevel 5
Badge +32

I’m not 100% sure on this but it could be related to Bulk Insert. Bulk insert is waaay faster but I did read that it invalidates foreign fey constraints. Can you try turning off Bulk insert? 

If not there could be a creative way to run some SQL before and after write to somehow disable/drop the column and then recalculate the values after the insert - This might not be a valid solution for you though. 

nielsgerrits
Rank
Badge +41

What happens if you remove the attribute Checksum from the features just before it enters the writer using an AttributeRemover? I think this is your issue.

Have had a simular issue writing to a postgres db where columns in my table got updated while they were not defined as attributes in the writer. I expected the writer to only update the columns defined in the writer, but this turned out to be different.

patlougheed
Rank
Badge +3

I’m not 100% sure on this but it could be related to Bulk Insert. Bulk insert is waaay faster but I did read that it invalidates foreign fey constraints. Can you try turning off Bulk insert? 

The MariaDB/MySQL writer doesn’t have an explicit bulk insert parameter like the SQL Server writer, so I tried setting the “operations per SQL statement” setting on the table to 1. Unfortunately it gets the same error, so I don’t think that’s it.

patlougheed
Rank
Badge +3

What happens if you remove the attribute Checksum from the features just before it enters the writer using an AttributeRemover? I think this is your issue.

Have had a simular issue writing to a postgres db where columns in my table got updated while they were not defined as attributes in the writer. I expected the writer to only update the columns defined in the writer, but this turned out to be different.

I’m not actually creating the Checksum in the workflow at all - the AttributeManager immediately before the writer doesn’t have any column similar to it coming out. It’s only defined in the database.

patlougheed
Rank
Badge +3

In case it’s helpful to others, the option I went with here was leaving the Checksum column off the table entirely, and instead creating a view in the database to layer on the Checksum. The view’s pretty simple, it’s basically:

SELECT s.*, UNHEX(SHA2(..., 512)) AS `Checksum` FROM <table> s

And that does the trick. The downside is another database object; the upside is FME is perfectly happy and the additional object is pretty simple.

Reply


Community Stats

29,620
Posts
112,572
Replies
37,269
Members
Terms & ConditionsCookie settings