• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

run sql in MySql query browser using generated DDL from MyEclipse tool.

Initially I use MySQL query browser(1.1.14) to create schema, then I using MYEclipse DDL generation to generate following DDL:

    create table `auctiontest`.`user`(
        `uid` bigint unsigned not null auto_increment comment 'auto_increment',
       `name` varchar(45) default '' not null,
       `password` varchar(45) default '' not null,
        primary key (`uid`)
    );

    create unique index `PRIMARY` on `auctiontest`.`user`(`uid`);

2) I create the table in another schema. So I replace auctiontest to testone2many.

    create table `testone2many`.`user`(
        `uid` bigint unsigned not null auto_increment comment 'auto_increment',
       `name` varchar(45) default '' not null,
       `password` varchar(45) default '' not null,
        primary key (`uid`)
    );

    create unique index `PRIMARY` on `testone2many`.`user`(`uid`);

But it failes. The error message is "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ';
    create unique index `PRIMARY` on `testone2many`.`user`"

It does work if I only run part of it say:
    create table `testone2many`.`user`(
        `uid` bigint unsigned not null auto_increment comment 'auto_increment',
       `name` varchar(45) default '' not null,
       `password` varchar(45) default '' not null,
        primary key (`uid`)
    );
MyEclipse version is 5.1.1 and MySQL version is 4.0.9.


0
JianJunShen
Asked:
JianJunShen
  • 7
  • 6
6 Solutions
 
Aleksandar BradarićSoftware DeveloperCommented:
You're duplicating the code. This:
---
     primary key (`uid`)
---

and:
---
create unique index `PRIMARY` on `testone2many`.`user`(`uid`);
---

are synonyms. You need one or the other - not both.
0
 
JianJunShenAuthor Commented:
create table `testone2many`.`category`(
        `cid` bigint unsigned not null auto_increment comment 'auto_increment',
       `name` varchar(45) default '' not null,
       `parent_category_id` bigint unsigned,
        primary key (`cid`)
    );

    alter table `testone2many`.`category`  
        add index `FK_category_1`(`parent_category_id`),
        add constraint `FK_category_1`
        foreign key (`parent_category_id`)
        references `testone2many`.`category`(`cid`);
    create unique index `PRIMARY` on `testone2many`.`category`(`cid`);
    create index `Index_2` on `testone2many`.`category`(`parent_category_id`,`cid`);
0
 
JianJunShenAuthor Commented:
How about above DDL then? I remove two create unique index and create index lines, problem still exists?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Aleksandar BradarićSoftware DeveloperCommented:
You can either make it like this:
---
    create table `testone2many`.`user`(
        `uid` bigint unsigned not null auto_increment comment 'auto_increment',
       `name` varchar(45) default '' not null,
       `password` varchar(45) default '' not null,
        primary key (`uid`)
    );
---

or like this:
---
    create table `testone2many`.`user`(
        `uid` bigint unsigned not null PRIMARY KEY auto_increment comment 'auto_increment' ,
       `name` varchar(45) default '' not null,
       `password` varchar(45) default '' not null
    );
---

If you define a column as `auto_increment`, you need to designate it a `primary key` right away. You can not do it afterwards.

As for the second table (`category`), again you do not need to issue the "create unique index `PRIMARY`..." line. The rest is fine.
0
 
JianJunShenAuthor Commented:
I follow your suggestions, but it does not work.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Could you tell me what exactly doesn't work. I've just executed all your queries  on my local server and it seems to be working fine...

If you could post here the query that did not work along with the error message it displayed, that would be great.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Here's the test:
---
mysql> CREATE TABLE `testone2many`.`category`(
    ->   `cid` bigint unsigned not null auto_increment comment 'auto_increment',
    ->   `name` varchar(45) default '' not null,
    ->   `parent_category_id` bigint unsigned,
    ->   primary key (`cid`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> ALTER TABLE `testone2many`.`category`
    ->   ADD INDEX `FK_category_1`(`parent_category_id`),
    ->   ADD CONSTRAINT `FK_category_1`
    ->   FOREIGN KEY (`parent_category_id`) REFERENCES `testone2many`.`category`(`cid`);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE INDEX `Index_2` on `testone2many`.`category`(`parent_category_id`,`cid`);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
---

And here's what it created:
---
mysql> EXPLAIN category;
+--------------------+---------------------+------+-----+---------+----------------+
| Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| cid                | bigint(20) unsigned |      | PRI | NULL    | auto_increment |
| name               | varchar(45)         |      |     |         |                |
| parent_category_id | bigint(20) unsigned | YES  | MUL | NULL    |                |
+--------------------+---------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> SHOW INDEX FROM category;
+----------+------------+---------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name      | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+---------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| category |          0 | PRIMARY       |            1 | cid                | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| category |          1 | FK_category_1 |            1 | parent_category_id | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| category |          1 | Index_2       |            1 | parent_category_id | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| category |          1 | Index_2       |            2 | cid                | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+----------+------------+---------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)
---
0
 
JianJunShenAuthor Commented:
I run following DDL. And error message is "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ';
 alter table `testone2many`.`category`
        add ind ";
What kind of MySQL server version do you have? I have old one, 4.0.9, not 5.0. Do you know how could I use command window to run these DDL?


create table `testone2many`.`category`(
        `cid` bigint unsigned not null auto_increment comment 'auto_increment',
       `name` varchar(45) default '' not null,
       `parent_category_id` bigint unsigned,
        primary key (`cid`)
    );

    alter table `testone2many`.`category`
        add index `FK_category_1`(`parent_category_id`),
        add constraint `FK_category_1`
        foreign key (`parent_category_id`)
        references `testone2many`.`category`(`cid`);
    create index `Index_2` on `testone2many`.`category`(`parent_category_id`,`cid`);
0
 
Aleksandar BradarićSoftware DeveloperCommented:
> Do you know how could I use command window to run these DDL?

You can execute any file (e.g. file.sql) by supplying it to the `mysql` client. Go to command line client and execute the following command:
---
mysql -u your_username -p < /path/to/file.sql
---

It will prompt you for your password and will continue to process the file. You may also provide the password right away, but it's not recommended for security reasons:
---
mysql -u your_username -pyour_password < /path/to/file.sql
---

As for the MySQL Query Browser, are you using `Open Query` or `Open Script` to open the DDL file?
0
 
Aleksandar BradarićSoftware DeveloperCommented:
> Go to command line client

I'm sorry - this should say: `Go to command prompt`.
0
 
JianJunShenAuthor Commented:
Yes, using command window it works.
0
 
JianJunShenAuthor Commented:
But I am wondering why MySQL query browser does not work.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
> But I am wondering why MySQL query browser does not work.

You might want to try using a newer version (I'm running 1.2.9). I think I remember having problems with executing multiple queries from the same file (separated by `;`) in some of the earlier versions of MySQL Query Browser. It may have been a bug which was fixed later on.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now