Adding a SQL column to a table duplicates the table

user_15384725 0
Viewed 139

On a mySQL database, a strange phenomenon appeared. When I try to add a column to a table, the column is added, but the table is duplicated afterwards. It keeps duplicating tables if we try to add other columns and I do not know how to fix it or where it could come from. The SQL DB is hosted on Azure

Here is what the situation is like before trying to add a column :

SHOW tables;

('annotations',)
('dicoms',)
('image',)
('patient',)
('procedure_biopsy',)
('series',)

DESCRIBE annotations;

('nb_lesions', 'int(11)', 'YES', '', None, '')
('id_annotation', 'int(11)', 'NO', 'PRI', None, 'auto_increment')
('id_annotator', 'varchar(50)', 'YES', '', None, '')
('id_annotation_request', 'int(11)', 'YES', '', None, '')
('comments_to_annotator', 'text', 'YES', '', None, '')
('status', 'varchar(50)', 'YES', '', None, '')
('location_output', 'varchar(50)', 'YES', '', None, '')
('date_saved', 'date', 'YES', '', None, '')
('version', 'varchar(50)', 'YES', '', None, '')
('nb_bounding_boxes', 'int(11)', 'YES', '', None, '')
('purpose', 'varchar(50)', 'YES', '', None, '')
('id_image', 'int(11)', 'YES', '', None, '')
('type_annotation', 'varchar(50)', 'YES', '', None, '')

Now, if I send this request :

ALTER TABLE annotations ADD test_column varchar(50);

the requests performs successfully, and with "DESCRIBE annotations;", we get :

('nb_lesions', 'int(11)', 'YES', '', None, '')
('id_annotation', 'int(11)', 'NO', 'PRI', None, 'auto_increment')
('id_annotator', 'varchar(50)', 'YES', '', None, '')
('id_annotation_request', 'int(11)', 'YES', '', None, '')
('comments_to_annotator', 'text', 'YES', '', None, '')
('status', 'varchar(50)', 'YES', '', None, '')
('location_output', 'varchar(50)', 'YES', '', None, '')
('date_saved', 'date', 'YES', '', None, '')
('version', 'varchar(50)', 'YES', '', None, '')
('nb_bounding_boxes', 'int(11)', 'YES', '', None, '')
('purpose', 'varchar(50)', 'YES', '', None, '')
('id_image', 'int(11)', 'YES', '', None, '')
('type_annotation', 'varchar(50)', 'YES', '', None, '')
('test_column', 'varchar(50)', 'YES', '', None, '')

But the problem comes when we try to run "SHOW tables;" as it returns :

('annotations',)
('annotations',)
('dicoms',)
('image',)
('patient',)
('procedure_biopsy',)
('series',)

The table annotation is duplicated.

I also tried to check with dgForge and dataGrip how the data looks like, and I get 2 completely different results from those database explorers.

  • With dgForge studio 2020 for mySQL, I see duplicated tables and other columns of the table are all duplicated. (but if we try to look at the data itself, columns does not seem duplicated)
  • With dataGrip, I do not see any duplicated tables or duplicated columns anywhere

I really do not understand what's happening here. Any idea of were this type of issue could come from or what could be done to fix this strange behavior ? Thank you very much for your help anyways !

mysql
2 Answers

Quick workaround for the problem: Restart your MySQL Azure DB and the duplicates are gone. InnoDB has a healing mechanism during restart.

It is not solving the problem, but at least the duplicates are gone, and you can work with the DB.

For the problem itself, after doing a lot of research with my technical guy, we think it is a Microsoft Azure issue and reported it to Microsoft. I will post the answer as soon as I have one.

user_65267660

According to Microsoft this issue seems to be a known issue due to last March deployment. In order to solve the issue, Microsoft has changed the server parameter dir_cache_fine_grained_enabled = OFF, which is not exposed to customers

This change will only take effect after you reboot the server.

After restarting my Azure DB servers, the issue is not present anymore!

user_65267660
Related