MySQL Bugs: #44385: Forward Engineer SQL Script does not generate INSERT Statements for Tables

Bug #44385	Forward Engineer SQL Script does not generate INSERT Statements for Tables
Submitted:	21 Apr 2009 16:12	Modified:	10 Jun 2009 9:39
Reporter:	Ivan Wolf	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1.10 OSS Beta Revision 5309	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	CHECKED, export, generate inserts, workbench

Description:
When exporting a schema, defined INSERTs for each table don't get included in the forward engineer sql create script export.

How to repeat:
define a table with inserts, export it via file/export/forward engineer sql create script, put a tick at 'generate insert statements for tables', etc.

Thank you for the problem report. Verified just as described also on Mac OS X.

Tested the exact same on 5.0 where it worked flawlessly. Taking the same, small, scheme to 5.1.10a and it fails to include the inserts.

Still broken on 5.1.12 beta

Tested on archlinux.

Also broken on 5.2. 12 OSS Beta Revision 5778

I've had a look at the source code for MySQL Workbench OSS 5.2.1, and found a solution to the problem.

File: modules/db.mysql/src/module_db_mysql.cpp
Class: GenerateTable
Method:	perform

It appears as though a change is in progress, such that the inserts are appended in one block, after all the other SQL statements - see comment on line 1565.  However, this change appears to be incomplete.  My solution causes the inserts to appear immediately after the SQL statements for each table, rather than a group at the end.  It is not the ideal solution, but if you need the inserts functionality, and don't mind compiling from source, it's a quick and easy solution.

The inserts are generated in an if block, starting at line 1676:

---

if (!table_inserts_sql.empty())
{
  inserts_sql
    .append("\n-- -----------------------------------------------------\n-- Data for table ")
    .append(get_qualified_schema_object_name(table))
    .append("\n-- -----------------------------------------------------\nSET AUTOCOMMIT=0;\n")
    .append(table_inserts_sql)
    .append("\nCOMMIT;\n");
}

---

The SQL statements are stored in a variables called 'sql', while the inserts are stored in 'inserts_sql'.  The contents of 'inserts_sql' are not appended to 'sql'.  To fix the problem, add the following after the inserts are appended to inserts_sql:

sql.append(inserts_sql)

The inserts block should now look like this:

---

if (!table_inserts_sql.empty())
{
  inserts_sql
    .append("\n-- -----------------------------------------------------\n-- Data for table ")
    .append(get_qualified_schema_object_name(table))
    .append("\n-- -----------------------------------------------------\nSET AUTOCOMMIT=0;\n")
    .append(table_inserts_sql)
    .append("\nCOMMIT;\n");

  sql.append(inserts_sql);
}

---

Once done, compile the source, and the inserts should work.  I'm running Ubuntu, and the build instructions in the README file that comes with the source worked perfectly.

The inserts editor in 5.2.1 does not work properly, but the above fix is exactly the same for 5.1.12-1, in which the editor does work.

I was able to repeat with 5.1.12 in Linux, but not with latest revision.

fix confirmed. will be included in 5.1.13

An entry was added to the 5.1.13 changelog:

The Forward Engineer SQL Script wizard did not emit table INSERT statements in the generated script when requested to do so.