RE: Umstieg von msql --> mysql
Archiv Mailingliste mysql-de

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

RE: Umstieg von msql --> mysql

es gibt auch in mysql die programme wie mysqldump, mysqlshow, mysqladmin
(wie bei msql nur eben mit einem "y"). somit kannst du ganz einfach

	mysql Datenbank < msql_dump.txt

verwenden und schon stehen die daten in der mysql-db. wichtig ist allerdings
dass mysql einen zugriffsschutz bietet, man kann bestimmten usern von
bestimmten rechnern gezielt zugriff auf bestimmte dbs (und aktionen wie
select, insert, update, create etc) geben (ziemlich clever...)

w3-msql duerfte nicht mehr klappen (kann aber nichts dazu sagen, da ich es
nie verwendet habe),
ich setze mysql mit perl ein bzw. denke ueber den einsatz von php nach...
die migration von msql zu mysql hatte bei mir eigentlich ziemlich
reibungslos geklappt (wenn wir nur die msql/mysql-seite nehmen).
ich musste dazu nur fuer perl die module DBI::mysql installieren.

ein paar hinweise aus der doku von mysql:

19.1 How MySQL compares to mSQL
This section has been written by the MySQL developers, so it should be read
with that in mind. But there are NO factual errors that we know of.

For a list of all supported limits, functions and types, see the crash-me
web page.

For a true comparison of speed, consult the growing MySQL benchmark suite.
See section 11 The MySQL benchmark suite. Because there is no thread
creation overhead, a small parser, few features and simple security, mSQL
should be quicker at:
Tests that perform repeated connects and disconnects, running a very simple
query during each connection.
INSERT operations into very simple tables with few columns and keys.
SELECT on something that isn't an index. (A table scan is very easy.)
Since these operations are so simple, it is hard to be better at them when
you have a higher startup overhead. After the connection is established,
MySQL should perform much better. On the other hand, MySQL is much faster
than mSQL (and most other SQL implementions) on the following:
Complex SELECT operations.
Retrieving large results (MySQL has a better, faster and safer protocol).
Tables with variable-length strings, since MySQL has more efficent handling
and can have indexes on VARCHAR columns.
Handling tables with many columns.
Handling tables with large record lengths.
SELECT with many expressions.
SELECT on large tables.
Handling many connections at the same time. MySQL is fully multi-threaded.
Each connection has its own thread, which means that no thread has to wait
for another (unless a thread is modifying a table another thread wants to
access.) In mSQL, once one connection is established, all others must wait
until the first has finished, regardless of whether the connection is
running a query that is short or long. When the first connection terminates,
the next can be served, while all the others wait again, etc.
Joins. mSQL can become pathologically slow if you change the order of tables
in a SELECT. In the benchmark suite, a time more than 15000 times slower
than MySQL was seen. This is due to mSQL's lack of a join optimizer to order
tables in the optimal order. However, if you put the tables in exactly the
right order in mSQL2 and the WHERE is simple and uses index columns, the
join will be relatively fast! See section 11 The MySQL benchmark suite.
Using TEXT or BLOB columns.
SQL Features
GROUP BY and HAVING. mSQL does not support GROUP BY at all. MySQL supports a
full GROUP BY with both HAVING and the following functions: COUNT(), AVG(),
MIN(), MAX(), SUM() and STD(). COUNT(*) is optimized to return very quickly
if the SELECT retrieves from one table, no other columns are retrieved and
there is no WHERE clause. MIN() and MAX() may take string arguments.
INSERT and UPDATE with calculations. MySQL can do calculations in an INSERT
or UPDATE. For example:
mysql> UPDATE SET x=x*10+y WHERE x<20;
Aliasing. MySQL has column aliasing.
Qualifying column names. In MySQL, if a column name is unique among the
tables used in a query, you do not have to use the full qualifier.
SELECT with functions. MySQL has many functions (too many to list here; see
section 7.3 Functions for use in SELECT and WHERE clauses).
Disk space efficiency
That is, how small can you make your tables? MySQL has very precise types,
so you can create tables that take very little space. An example of a useful
MySQL datatype is the MEDIUMINT that is 3 bytes long. If you have
100,000,000 records, saving even one byte per record is very important.
mSQL2 has a more limited set of column types, so it is more difficult to get
small tables.
This is harder to judge objectively. For a discussion of MySQL stability,
see section 1.5 How stable is MySQL?. We have no experience with mSQL
stability, so we cannot say anything about that.
Another important issue is the license. MySQL has a more flexible license
than mSQL, and is also less expensive than mSQL. Whichever product you
choose to use, remember to at least consider paying for a license or email
support. (You are required to get a license if you include MySQL with a
product that you sell, of course.)
Perl interfaces
MySQL has basically the same interfaces to Perl as mSQL with some added
JDBC (Java)
MySQL currently has 4 JDBC drivers:
The gwe driver: A Java interface by GWE technologies (not supported
The jms driver: An improved gwe driver by Xiaokun Kelvin ZHU
The twz driver: A type 4 JDBC driver by Terrence W. Zellers and educational
The mm driver: A type 4 JDBC driver by Mark Matthews
The recommended drivers are the twz or mm driver. Both are reported to work
excellently. We know that mSQL has a JDBC driver, but we have too little
experience with it to compare.
Rate of development
MySQL has a very small team of developers, but we are quite used to coding C
and C++ very rapidly. Since threads, functions, GROUP BY and so on are still
not implemented in mSQL, it has a lot of catching up to do. To get some
perspective on this, you can view the mSQL `HISTORY' file for the last year
and compare it with the News section of the MySQL Reference Manual (see
section D MySQL change history). It should be pretty obvious which one has
developed most rapidly.
Utility programs
Both mSQL and MySQL have many interesting third-party tools. Since it is
very easy to port upward (from mSQL to MySQL), almost all the interesting
applications that are available for mSQL are also available for MySQL. MySQL
comes with a simple msql2mysql program that fixes differences in spelling
between mSQL and MySQL for the most-used C API functions. For example, it
changes instances of msqlConnect() to mysql_connect(). Converting a client
program from mSQL to MySQL usually takes a couple of minutes.
19.1.1 How to convert mSQL tools for MySQL
According to our experience, it would just take a few hours to convert tools
such as msql-tcl and msqljava that use the mSQL C API so that they work with
the MySQL C API.

The conversion procedure is:

Run the shell script msql2mysql on the source. This requires the replace
program, which is distributed with MySQL.
Fix all compiler errors.
Differences between the mSQL C API and the MySQL C API are:

MySQL uses a MYSQL structure as a connection type (mSQL uses an int).
mysql_connect() takes a pointer to a MYSQL structure as a parameter. It is
easy to define one globally or to use malloc() to get one. mysql_connect()
also takes 2 parameters for specifying the user and password. You may set
these to NULL, NULL for default use.
mysql_error() takes the MYSQL structure as a parameter. Just add the
parameter to your old msql_error() code if you are porting old code.
MySQL returns an error number and a text error message for all errors. mSQL
returns only a text error message.
Some incompatibilities exist as a result of MySQL supporting multiple
connections to the server from the same process.
19.1.2 How mSQL and MySQL client/server communications protocols differ
There are enough differences that it is impossible (or at least not easy) to
support both.

The most significant ways in which the MySQL protocol differs from the mSQL
protocol are listed below:

A message buffer may contain many result rows.
The message buffers are dynamically enlarged if the query or the result is
bigger than the current buffer, up to a configurable server and client
All packets are numbered to catch duplicated or missing packets.
All column values are sent in ASCII. The lengths of columns and rows are
sent in packed binary coding (1, 2 or 3 bytes).
MySQL can read in the result unbuffered (without having to store the full
set in the client).
If a single write/read takes more than 30 seconds, the server closes the
If a connection is idle for 8 hours, the server closes the connection.
19.1.3 How mSQL 2.0 SQL syntax differs from MySQL
Column types

Has the following additional types (among others; see section 7.6 CREATE
TABLE syntax):
ENUM type for one of a set of strings.
SET type for many of a set of strings.
BIGINT type for 64-bit integers.
MySQL also supports the following additional type attributes:
UNSIGNED option for integer columns.
ZEROFILL option for integer columns.
AUTO_INCREMENT option for integer columns that are a PRIMARY KEY. See
section 18.4.27 mysql_insert_id().
DEFAULT value for all columns.
mSQL column types correspond to the MySQL types shown below: mSQL type
Corresponding MySQL type
CHAR(len)  CHAR(len)
TEXT(len)  TEXT(len). len is the maximal length. And LIKE works.
INT  INT. With many more options!
REAL  REAL. Or FLOAT. Both 4- and 8-byte versions are available.
DATE  DATE. Uses ANSI SQL format rather than mSQL's own.
MONEY  DECIMAL(12,2). A fixed-point value with two decimals.

Index creation

Indexes may be specified at table creation time with the CREATE TABLE
Indexes must be created after the table has been created, with separate
CREATE INDEX statements.
To insert a unique identifier into a table

Use AUTO_INCREMENT as a column type specifier. See section 18.4.27
Create a SEQUENCE on a table and select the _seq column.
To obtain a unique identifier for a row

Add a PRIMARY KEY or UNIQUE key to the table.
Use the _rowid column. Observe that _rowid may change over time depending on
many factors.
To get the time a column was last modified

Add a TIMESTAMP column to the table. This column is automatically set to the
current time for INSERT or UPDATE statements if you don't give the column a
value or if you give it a NULL value.
Use the _timestamp column.
NULL value comparisons

MySQL follows ANSI SQL and a comparison with NULL is always NULL.
In mSQL, NULL = NULL is TRUE. You must change =NULL to IS NULL and <>NULL to
IS NOT NULL when porting old code from mSQL to MySQL.
String comparisons

Normally, string comparisons are performed in case-independent fashion with
the sort order determined by the current character set (ISO-8859-1 Latin1 by
default). If you don't like this, declare your columns with the BINARY
attribute, which causes comparisons to be done according to the ASCII order
used on the MySQL server host.
All string comparisons are performed in case-sensitive fashion with sorting
in ASCII order.
Case-insensitive searching

LIKE is a case-insensitive or case-sensitive operator, depending on the
columns involved. If possible, MySQL uses indexes if the LIKE argument
doesn't start with a wildcard character.
Handling of trailing spaces

Strips all spaces at the end of CHAR and VARCHAR columns. Use a TEXT column
if this behavior is not desired.
Retains trailing space.
WHERE clauses

MySQL correctly prioritizes everything (AND is evaluated before OR). To get
mSQL behavior in MySQL, use parentheses (as shown below).
Evaluates everything from left to right. This means that some logical
calculations with more than three arguments cannot be expressed in any way.
It also means you must change some queries when you upgrade to MySQL. You do
this easily by adding parentheses. Suppose you have the following mSQL
mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4;
To make MySQL evaluate this the way that mSQL would, you must add
mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4))));
Access control

Has tables to store grant (permission) options per user, host and database.
See section 6.4 How the privilege system works.
Has a file `mSQL.acl' in which you can grant read/write privileges for


Markus Rietzler
* Wuppertal-Forum | Wuppertal-Navigator
* eMail: markus.rietzler_(at)

Ronsdorfer Strasse 14	Fon: 0202.42 08 30
42119 Wuppertal		Fax: 0202.242 24 66
				Mobil: 0171.480 77 82

> -----Original Message-----
> From: renevojtech_(at) [mailto:renevojtech_(at)]
> Sent: Friday, July 09, 1999 1:06 PM
> To: mysql-de_(at)
> Subject: Umstieg von msql --> mysql
> hi,
> ich möchte meine Datenbanken von msql auf mysql umstellen. Einen Dump
> der
> msql-Datenbanken habe ich schon gemacht - aber wie bekomme ich die
> Daten in die mysql-Datenbank - gibts da ein Programm für den Datenimport
> ?
> Was passiert mit meinen Programmen (z. Zt. Zugriff über w3-msql) muß ich
> die komplett umschreiben ?
> Sind bei der Umstellung Probleme zu erwarten ?
> cu
> rene

Home | Main Index | Thread Index

php::bar PHP Wiki   -   Listenarchive