After converting an attribute from decimal to integer at synchronizing the database, the databases content is corrupted

0
After converting an attribute from decimal to integer: at synchronizing the database, the database's content is corrupted. More precise: the attribute's values get thrown away and the attribute-values to the right, all shift one column to the left, even getting casted to the type of the column where the value happens to get moved to! This happens on a MariaDb. So I also tried this on the built-in database and there it is slightly different: the changed attribute is stripped from it's values and is moved to the end of the table. Wtf? Is this a known error? What is going on here?
asked
3 answers
4

Tested it using the build-in Db. All values were set to 0. Which appears to me pretty normal, since decimal numbers != to Integers. 

What you describe regarding the MariaDb, not sure what is happening. 

But in general, changing the type of attribute, related to the situation with existing (production) data => I never change the attribute type, but favour a new attribute and populate it (after start up?) with the values of the original attribute, allowing me to be in control of the conversion.

answered
1

I have just checked decimal to integer conversion for MariaDB 10.0. All decimal values were correctly converted to 0 as expected: 

Furthermore, no messy renaming of the attributes detected in Mendix 7.8.0. Bool attributed remained of type Boolean, as well as text attributed remained of type String:

Tim, can you verify the types of the columns of the table's metadata like in the screenshot above ?

answered
1

Ah, the strange behaviour slowly becomes clear.

This is after creation in step 3 and synchronization in step 4:

and this is after synchronization in step7:

Like you said, fields 'bool' and 'text' kept their DataType. I do notice now that in the table-definition field 'number' has  moved to the last place. The database-management tool (HeidiSql 9.4) is slow in refreshing the column-titles in the datagrid. Just refreshing did not do the trick the first time, but now the columns have the correct titles.

Hm... AH! Reading the details of the synchronization step just before clicking on 'Synchronize database' helps!! One more test: I changed the type of field 'bool' to string, length 2 and the details say this:

ALTER TABLE `zipcodehousenumber$mendixtest` DROP COLUMN `bool`;

ALTER TABLE `zipcodehousenumber$mendixtest` ADD `bool` NVARCHAR(2) NULL;

UPDATE `mendixsystem$attribute`
 SET `entity_id` = 'd11dcbe6-18b7-4538-83c5-03dc1a29192d', 
`attribute_name` = 'bool', 
`column_name` = 'bool', 
`type` = 30, 
`length` = 2, 
`default_value` = '', 
`is_auto_number` = FALSE
 WHERE `id` = 'c478611b-b047-4103-91fa-d8e92eee3eed';

UPDATE `mendixsystem$version`
 SET `versionnumber` = '4.2', 
`lastsyncdate` = '20171121 17:14:53';

Now it's all clear: Mendix just DROPs and ADDs, whereas databases do this via :

ALTER TABLE `zipcodehousenumber$mendixtest`
    CHANGE COLUMN `bool` `bool` VARCHAR(2) NULL DEFAULT NULL AFTER `id`;

 Thanks guys!

answered