Schema comparison

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
molino
Posts: 33
Joined: Mon 06 Jul 2015 12:07

Schema comparison

Post by molino » Tue 28 Feb 2017 19:01

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

.jp
Devart Team
Posts: 345
Joined: Wed 09 Sep 2009 06:55
Location: devart

Re: Schema comparison

Post by .jp » Wed 01 Mar 2017 14:40

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:
------ Build started: Project: NewProjectName, Configuration: Default ------
...
------- Build failed: Project: NewProjectName, - 12 errors -------
Also would you please tell us what version of MariaDB you are using.

Best Regards.

molino
Posts: 33
Joined: Mon 06 Jul 2015 12:07

Re: Schema comparison

Post by molino » Tue 07 Mar 2017 13:58

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

molino
Posts: 33
Joined: Mon 06 Jul 2015 12:07

Re: Schema comparison

Post by molino » Mon 20 Mar 2017 13:14

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

alexa

Re: Schema comparison

Post by alexa » Mon 20 Mar 2017 17:26

You can upload the video to our FTP server ftp://ftp.devart.com@anonymous or e-mail it to alexaATdevartDOTcom .

molino
Posts: 33
Joined: Mon 06 Jul 2015 12:07

Re: Schema comparison

Post by molino » Mon 03 Apr 2017 16:22

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

alexa

Re: Schema comparison

Post by alexa » Tue 04 Apr 2017 08:27

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.

Richard_Stracke
Posts: 1
Joined: Wed 12 Apr 2017 05:39

Re: Schema comparison

Post by Richard_Stracke » Wed 12 Apr 2017 05:50

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

alexa

Re: Schema comparison

Post by alexa » Wed 12 Apr 2017 09:58

We will investigate this issue when developing the next product versions.

alexa

Re: Schema comparison

Post by alexa » Mon 15 May 2017 16:01

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.

molino
Posts: 33
Joined: Mon 06 Jul 2015 12:07

Re: Schema comparison

Post by molino » Thu 18 May 2017 10:10

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

alexa

Re: Schema comparison

Post by alexa » Fri 19 May 2017 14:51

We will investigate this issue and will answer you as soon as possible.

Sorry for the inconvenience.

molino
Posts: 33
Joined: Mon 06 Jul 2015 12:07

Re: Schema comparison

Post by molino » Sat 10 Jun 2017 13:35

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.

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;
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

alexa

Re: Schema comparison

Post by alexa » Mon 12 Jun 2017 10:18

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.

molino
Posts: 33
Joined: Mon 06 Jul 2015 12:07

Re: Schema comparison

Post by molino » Mon 11 Dec 2017 14:56

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?

Post Reply