Schema comparison
Schema comparison
Hello,
I want to report an issues I'm having with dbForge for Mysql (I'm using MariaDB).
Schema comparison:
I have just created a comparison script to adapt a live DB to the new structure (DB compared to project). I noticed that the script drops a lot of foreign keys just to recreate the identical keys. Even in the view before creating the script the keys are shown as identical.
Regards,
Raul
I want to report an issues I'm having with dbForge for Mysql (I'm using MariaDB).
Schema comparison:
I have just created a comparison script to adapt a live DB to the new structure (DB compared to project). I noticed that the script drops a lot of foreign keys just to recreate the identical keys. Even in the view before creating the script the keys are shown as identical.
Regards,
Raul
Re: Schema comparison
Hello,
Could you please send text of error for us.
Please perform the following:
1. Open project.
2. Choose project in Project Explorer.
3. Select popup menu Build <Project Name>.
4. Select in main menu View\Output.
5. Send an error. For example:
Best Regards.
Could you please send text of error for us.
Please perform the following:
1. Open project.
2. Choose project in Project Explorer.
3. Select popup menu Build <Project Name>.
4. Select in main menu View\Output.
5. Send an error. For example:
Also would you please tell us what version of MariaDB you are using.------ Build started: Project: NewProjectName, Configuration: Default ------
...
------- Build failed: Project: NewProjectName, - 12 errors -------
Best Regards.
Re: Schema comparison
Hello,
I didn't get an error. The issue is that the script containing the differences between db and project (schema comparison) listed that a lot of foreign keys were modified, resulting in dropping them. Because they were not modified (correctly shown in the gui that compares both sides before creating the script) they were also recreated by the script.
I don't like unnecessary modifications on production databases - like droping and recreating the same FK.
Because the production db is now on the new state I can not repeat the operation, maybe on the next db adaption.
I'm using MariaDB 10.0.21
Regards,
Raul
I didn't get an error. The issue is that the script containing the differences between db and project (schema comparison) listed that a lot of foreign keys were modified, resulting in dropping them. Because they were not modified (correctly shown in the gui that compares both sides before creating the script) they were also recreated by the script.
I don't like unnecessary modifications on production databases - like droping and recreating the same FK.
Because the production db is now on the new state I can not repeat the operation, maybe on the next db adaption.
I'm using MariaDB 10.0.21
Regards,
Raul
Re: Schema comparison
Hello,
I just wanted to upload some files from the next comparison where it happened again. But this forum doesn't support file upload (or at least I don't see it) ...
I just wanted to upload some files from the next comparison where it happened again. But this forum doesn't support file upload (or at least I don't see it) ...
Re: Schema comparison
You can upload the video to our FTP server ftp://ftp.devart.com@anonymous or e-mail it to alexaATdevartDOTcom .
Re: Schema comparison
Hello Alexa,
any news on this issue?
I just sent you a second mail regarding false reported differences because of "on update no action / restrict".
I use the schema comparison quite often and this two issues are generating a lot of extra work on my side because I have to manually correct a lot of entries on the generated script.
Regards,
Raul
any news on this issue?
I just sent you a second mail regarding false reported differences because of "on update no action / restrict".
I use the schema comparison quite often and this two issues are generating a lot of extra work on my side because I have to manually correct a lot of entries on the generated script.
Regards,
Raul
Re: Schema comparison
Hello Raul,
Thank you for the reply.
We were able to reproduce the first issue and are currently working on another one.
We will get back to you as soon as possible.
Thank you for the reply.
We were able to reproduce the first issue and are currently working on another one.
We will get back to you as soon as possible.
-
- Posts: 1
- Joined: Wed 12 Apr 2017 05:39
Re: Schema comparison
With general_log actrivated I can see, that DBforge use this statement:
SELECT c.table_name, column_name, column_key, column_default
FROM information_schema.columns c INNER JOIN
(SELECT table_name, row_format FROM information_schema.tables t
WHERE LOWER(t.table_schema) = LOWER('mnode_cm_data') AND ('' = '' OR LOWER(t.table_name) IN ('')) AND (t.table_type IN ('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL) ) t2
ON t2.table_name
COLLATE utf8_bin = c.table_name COLLATE utf8_bin
WHERE LOWER(c.table_schema) = LOWER(<tablename>)
ORDER BY t2.table_name
It looks , that DBforge not use the original "SHOW CREATE" output,
it recreates a fake "SHOW CREATE" statement based on the output of the query above.
The query not take into account the ordinal_position, so the output from the query above can another order of columns compared with the real table.
fix suggestion:
Ad ordinal_position in the order by clause.
Regards,
Richard Stracle
SELECT c.table_name, column_name, column_key, column_default
FROM information_schema.columns c INNER JOIN
(SELECT table_name, row_format FROM information_schema.tables t
WHERE LOWER(t.table_schema) = LOWER('mnode_cm_data') AND ('' = '' OR LOWER(t.table_name) IN ('')) AND (t.table_type IN ('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL) ) t2
ON t2.table_name
COLLATE utf8_bin = c.table_name COLLATE utf8_bin
WHERE LOWER(c.table_schema) = LOWER(<tablename>)
ORDER BY t2.table_name
It looks , that DBforge not use the original "SHOW CREATE" output,
it recreates a fake "SHOW CREATE" statement based on the output of the query above.
The query not take into account the ordinal_position, so the output from the query above can another order of columns compared with the real table.
fix suggestion:
Ad ordinal_position in the order by clause.
Regards,
Richard Stracle
Re: Schema comparison
We will investigate this issue when developing the next product versions.
Re: Schema comparison
We would like to let you know that we have released dbForge Studio for MySQL, v7.2.63 where the issue you reported is fixed https://www.devart.com/dbforge/mysql/st ... nload.html
Thank you for your help in improving dbForge Studio for MySQL.
Thank you for your help in improving dbForge Studio for MySQL.
Re: Schema comparison
Hello,
I updated yesterday to the new version. Today I made a new comparison and I'm getting again false FK drops with identical recreations.
The comparison preview shows correct info but the generated script does not.
Regards,
Raul
I updated yesterday to the new version. Today I made a new comparison and I'm getting again false FK drops with identical recreations.
The comparison preview shows correct info but the generated script does not.
Regards,
Raul
Re: Schema comparison
We will investigate this issue and will answer you as soon as possible.
Sorry for the inconvenience.
Sorry for the inconvenience.
Re: Schema comparison
Hello,
Any news on this issue?
By the way, it's not only happening on FK. On my last comparison I deleted and renamed some columns on a table.
The script of the comparison also generates a Change Column on the column BasisBezeichnung. But this column wasn't changed. Also an unnecessary db modification.
Best Regards,
Raul
Any news on this issue?
By the way, it's not only happening on FK. On my last comparison I deleted and renamed some columns on a table.
Code: Select all
ALTER TABLE artikelbasis
CHANGE COLUMN MatchCode BasisMatchCode VARCHAR(255) DEFAULT NULL AFTER IstLagerArtikel,
CHANGE COLUMN BasisBezeichnung BasisBezeichnung VARCHAR(255) NOT NULL AFTER BasisMatchCode;
Best Regards,
Raul
Re: Schema comparison
Sorry for the delay on this. We will fix this issue in one of the next product versions and will notify you once it's available for downloading.
Re: Schema comparison
still not fixed? It is still dropping and recreating a lot of foreign keys. In my opinion this is an absolute no go in a database and you don't fix it?