Segmentation Fault: Core dumped..;-)

MySQL Vs. Oracle: Some more observations…

Posted : November 22, 2004 at 7:09 pm [America/Los_Angeles]

Almost 6 months back, I had written an entry on some of the differences that I found between Oracle and MySQL as I was getting to know more MySQL. This afternoon, while dabbling with some more MySQL, I found a few more things that I thought I would point out, especially pertaining to using MySQL’s mysql vs. Oracle’s sqlplus

Let’s take a simple Oracle SQL script (save it as run.sql):

set echo on;

drop table customers;

create table customers
(
 customerid number not null primary key,
 name varchar(11) not null,
 address varchar(100) not null,
 city varchar(30) not null

);

drop table orders;

create table orders
(
 orderid number not null primary key,
 customerid number not null,
 amount number(6,2),
 orderdate date not null

);

drop table books;

create table books
(
 isbn varchar(13) not null primary key,
 author varchar(50)
 title varchar(100),
 price number(6,2)
);

Upon execution, it gives the following result:

SQL> @E:run.sql;
SQL>
SQL> drop table customers;
drop table customers
           *
ERROR at line 1:
ORA-00942: table or view does not exist 

SQL>
SQL> create table customers
  2  (
  3   customerid number not null primary key,
  4   name varchar(11) not null,
  5   address varchar(100) not null,
  6   city varchar(30) not null
  7  );

Table created.

SQL>
SQL> drop table orders;
drop table orders
           *
ERROR at line 1:
ORA-00942: table or view does not exist 

SQL>
SQL> create table orders
  2  (
  3   orderid number not null primary key,
  4   customerid number not null,
  5   amount number(6,2),
  6   orderdate date not null
  7  );

Table created.

SQL>
SQL> drop table books;
drop table books
           *
ERROR at line 1:
ORA-00942: table or view does not exist 

SQL>
SQL> create table books
  2  (
  3   isbn varchar(13) not null primary key,
  4   author varchar(50),
  5   title varchar(100),
  6   price number(6,2)
  7  );

Table created.

SQL> 

Anyone who knows a thing or two about SQL can look at this interaction and come to a conclusion about whether the script went well or failed.

A quick-and-dirty translation of the SQL above into MySQL’s flavor resulted in this (save it as run1.sql):

drop table customers;

create table customers
(
 customerid int unsigned not null auto_increment primary key,
 name varchar(11) not null,
 address varchar(100) not null,
 city varchar(30) not null

);

drop table orders;

create table orders
(
 orderid int unsigned not null auto_increment primary key,
 customerid int unsigned not null,
 amount float(6,2),
 orderdate date not null

);

drop table books;

create table books
(
 isbn varchar(13) not null primary key,
 author varchar(50),
 title varchar(100),
 price float(6,2)
);

So, what’s different? Well, other than replacing all instances of number and number(x,y) from Oracle’s SQL script with MySQL’s int and float respectively and removing sqlplus-centric command like set echo on, it was really not much. So far so good.

However, if you try running this script using mysql (assuming you already have a MySQL user account and database with relevant privileges), your interaction will be perfunctory at best:

E:>mysql -D books -u anand -p < "E:run1.sql"
Enter password: ****************
ERROR 1051 (42S02) at line 1: Unknown table 'customers'

E:>

Well, that was dumb. Ofcourse there isin’t a ‘customers’ table. Can’t you just skip that error and just move on? Turns out, by default, mysql won’t. However, it has an option –force, -f which will solve our problem. Here’s what our interaction looks like with -f added:

E:>mysql -D books -f -u anand -p < "E:run1.sql"
Enter password: ********
ERROR 1051 (42S02) at line 1: Unknown table 'customers'
ERROR 1051 (42S02) at line 11: Unknown table 'orders'
ERROR 1051 (42S02) at line 21: Unknown table 'books'

E:>

While it worked, notice that the output is not too user-friendly. How do I know if the script worked or failed? After running my script, I had to login and run show tables to confirm that it had indeed worked. It was far cleaner in Oracle where the transcript of the session (thanks to set echo on) was pretty obvious.

So, after some more monkeying around with mysql’s command-line options, I was finally able to come up with an interaction which was almost like Oracle’s:

E:>mysql -D books -f -v -v -u anand -p < "E:run1.sql"
Enter password: ***********
--------------
drop table customers
--------------

ERROR 1051 (42S02) at line 1: Unknown table 'customers'
--------------
create table customers
(
 customerid int unsigned not null auto_increment primary key,
 name varchar(11) not null,
 address varchar(100) not null,
 city varchar(30) not null
)
--------------

Query OK, 0 rows affected

--------------
drop table orders
--------------

ERROR 1051 (42S02) at line 11: Unknown table 'orders'
--------------
create table orders
(
 orderid int unsigned not null auto_increment primary key,
 customerid int unsigned not null,
 amount float(6,2),
 orderdate date not null
)
--------------

Query OK, 0 rows affected

--------------
drop table books
--------------

ERROR 1051 (42S02) at line 21: Unknown table 'books'
--------------
create table books
(
 isbn varchar(13) not null primary key,
 author varchar(50),
 title varchar(100),
 price float(6,2)
)
--------------

Query OK, 0 rows affected

Bye

E:>

Much better. Definitely far more informative and user-friendly output.

Now, let’s zero-in on the various command-line options and what they mean:

E:>mysql -D books -f -v -v -u anand -p < “E:run1.sql”

The -D books specifies the database that the script will work on

The -f forces mysql to continue executing the SQL script despite errors

The -v -v (double -v) is for “more” verbose output. Try the command with just one -v and you will know why we need two -v

The -u anand specifies the userid that mysql will attempt logging in as

The -p tells mysql that it should prompt for a password in order to authenticate the user

On a side note, mysql sorta supports sqlplus’s spool command. During an interactive mysql session, you can use tee <filename> and notee options and it will work very similar to spool <filename> and spool off respectively. However, this does not seem to work when you run a batch SQL file

Bottomline, if you’re a frequent user of MySQL’s mysql client tool, hope these options taught you something new today. It sure was new to me

- Anand

Category: Services and Software