A text copy of this FAQ can be obtained via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/faq.txt
or an HTML copy can be obtained via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/faq.html
It may take a couple of days for the new versions of the FAQ to be
moved into the mSQL area. If you're desperate for the latest FAQ and
you missed it on the mSQL mailing list, try
looking for:
ftp://bond.edu.au/pub/Minerva/msql/Incoming/faq.txt
or
ftp://bond.edu.au/pub/Minerva/msql/Incoming/faq.html
This FAQ is maintained by Peter Samuel
<Peter.Samuel@uniq.com.au>. While every attempt is made to ensure that the information contained in this FAQ is accurate, no guarantees of accuracy can or will be made.
Third party applications mentioned in this FAQ may not be compatible with the current release of mSQL - by necessity their development will lag that of mSQL. If you have any questions concerning their status please contact the mSQL mailing list or the author of the application in question.
New questions in the FAQ are marked with (=). Questions that have been modified since the last release of the FAQ are marked with (-).
NOTE: BEFORE POSTING A QUESTION TO THE mSQL MAILING LIST, PLEASE READ THE SECTION "How do I post a question to the mSQL mailing list".
"mSQL has been released in the past under terms known as 'conscience-ware', the basic concept of which was that companies that used the software could contribute a small amount to the continued development of the software without any strict rules being placed upon such 'donations'. Although the concept sounds fair, it failed badly with only 3 contributions being made from over 3,600 copies of mSQL-1.0.5 that were ftp'ed from my machine alone. Over 1,000 of those copies went to commercial organisations and I receive many questions a day from companies using mSQL behind their WWW servers etc who are looking for free support.
In an attempt to balance this out and allow me to devote some time to mSQL (rather than other pursuits that I do to generate an income), mSQL is now shareware. I still believe in free software over the Internet and cooperation in research so the new license is designed not to hurt Universities, research groups and other people that _should_ have free access to software. Commercial organisations that are using this so that they don't have to buy an Oracle or an Ingres will now have to buy mSQL (at a minute fraction of the cost of one of the commercial offerings).
Please read the doc/License file to see if you are required to register your copy. An invoice is included in both Postscript and ASCII format to ease the generation of payments."
As of release 1.0.10, the cost of mSQL is:
Commercial Installation - AUD $225
Private Installation - AUD $65
Exchange rates vary wildly, but at the time of preparing this FAQ, the
Australian dollar was trading at about 0.75 US dollars. This
information is provided as an indication only. You MUST check your
local exchange rates before preparing to purchase mSQL.
ftp://bond.edu.au/pub/Minerva/msql/
It is available via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/msql-1.0.10.tar.gz
(198076 bytes)
mSQL development is NOT David's primary role. He does this work in whatever spare time he has available so PLEASE don't pester him with requests about when we can expect version 2. Take the zen approach and just let it happen :)
(See http://www.khoros.unm.edu/staff/neilb/weblint.html for more details on weblint).
The above notwithstanding, there are three unofficial recommended patches that should be applied to mSQL version 1.0.10.
The first patch, contributed by Rasmus Lerdorf <rasmus@io.org> fixes a problem where the mSQL database server will not notice that a client has died.
Rasmus writes:
A second change is actually a bug fix in my opinion. If you have a
look at the writePkt() function in net.c you will notice that it is
a void function. The fact that writing a packet to a client socket
is never checked for errors means that if a client goes away for
whatever reason, msqld will continue sending the data to an invalid
file descriptor. This is especially noticeable in www environments
where people are likely to hit the Stop button or their back
buttons in their browsers on any screen which takes a little while
to load. If enough people do this, msqld is sitting around spinning
its wheels feeding data to bogus file descriptors. The fix here is
obvious and simple. Make writePkt() return an error status and
check it. If the receiving socket has disappeared, stop sending
data.
And in a followup David Hughes writes:
Now, I'll admit that not checking the return from write isn't
"correct" in the strictest terms of the system call semantics, but
it's only a bug if it causes things to break. The only thing that
writePkt() could be writing down is something that is a reliable
data stream such as a TCP SOCK_STREAM or a pipe. Therefore, the
only real situation that would cause an error is if the other end
bailed out on the server.
In such a case, the first thing that will happen is that msqld will
be sent a SIGPIPE to indicate that there's nothing on the other end
of the connection anymore. The signal handler cleans up all
details of the connection, closes the FD and does all the right
stuff. The write() then returns with an error which causes
writePkt() to return and bail out. If there's more data to write
then writePkt() may be called again with an instant return because
the FD isn't valid anymore. This isn't going to hurt anyone and
certainly isn't going to cause any data errors or server problems.
You may burn a few cycles on this but what the hell.
So, I don't think of it as a bug - it's actually part of the design
as it simplifies other aspects of the connection management.
And Rasmus replies:
I'll grant that no data errors will occur, but at least on my
Solaris box these few extra cycles you talk about are very
significant. msqld sits and writes to an invalid FD for a very long
time. With the simple fix, it exits instantly when a client goes
away.
*** ./src/msql/net.c.orig Tue Oct 17 11:24:03 1995
--- ./src/msql/net.c Tue Dec 12 15:24:11 1995
***************
*** 66,72 ****
! void writePkt(fd)
int fd;
{
u_char *cp;
--- 66,72 ----
! int writePkt(fd)
int fd;
{
u_char *cp;
***************
*** 84,94 ****
numBytes = write(fd,packetBuf + offset, remain);
if (numBytes == -1)
{
! return;
}
offset += numBytes;
remain -= numBytes;
}
}
--- 84,95 ----
numBytes = write(fd,packetBuf + offset, remain);
if (numBytes == -1)
{
! return(-1);
}
offset += numBytes;
remain -= numBytes;
}
+ return(0);
}
*** ./src/msql/msql_priv.h.orig Tue Oct 17 11:06:55 1995
--- ./src/msql/msql_priv.h Tue Dec 12 15:24:14 1995
***************
*** 298,304 ****
! void writePkt();
int readPkt();
--- 298,304 ----
! int writePkt();
int readPkt();
To avoid conflicts with mismatched whitespace,
apply this patch using the
"-l" option of patch. The second patch comes from David Hughes <bambi@hughes.com.au> and fixes a problem where the database server would freeze.
David writes:
OK, problem solved.
The problem is a 1 liner in bSort() where it recurses once too far
and ends up calling itself with an upper bound of MAX_UINT (i.e.
unsigned 0 - 1). The problem is solved by the following patch.
*** ./src/msql/msqldb.c.orig Mon Nov 13 14:37:24 1995
--- ./src/msql/msqldb.c Tue Dec 12 15:24:39 1995
***************
*** 5170,5176 ****
else
bSwap(entry,high,newHigh);
}
! bSort(entry, order, olist, low+1, high-1);
}
--- 5170,5179 ----
else
bSwap(entry,high,newHigh);
}
! if (high != 0)
! {
! bSort(entry, order, olist, low+1, high-1);
! }
}
To avoid conflicts with mismatched whitespace,
apply this patch using the
"-l" option of patch. The third recommended patch comes from Dr Andreas F Muller <afm@mathi.uni-heidelberg.de> and fixes a problem with msqldump.
Andreas writes:
... to get a well defined exit status from msqldump (to tell whether
the dump has succeeded or not), you need to add an exit(0) at the end
of the main function in msqldump.c. It was bad in 1.0.8 and is still
bad in 1.0.10. I met this problem on Solaris 2.5, Sparc, version
1.0.10 of msql.
*** src/msql/msqldump.c.orig Tue Oct 3 10:34:51 1995
--- src/msql/msqldump.c Mon Jan 8 09:54:32 1996
***************
*** 387,392 ****
--- 387,394 ----
}
dbDisconnect(host);
printf("\n");
+
+ exit(0);
}
To avoid conflicts with mismatched whitespace,
apply this patch using the
"-l" option of patch.
The easiest way to apply these patches is to use Larry Wall's patch program:
Patch will take a patch file containing any of the four forms of
difference listing produced by the diff program and apply those
differences to an original file, producing a patched version. By
default, the patched version is put in place of the original, with
the original file backed up to the same name with the extension
".orig".
Patch is available from a number of anonymous ftp sites
worldwide. Latest versions of patch are being distributed by
the Free Software Foundation as part of the GNU suite of products. If you're having difficulty finding the latest version of patch, you can download version 2.1 via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/Misc/patch-2.1.tar.gz
(74856 bytes)
While some patches are fairly simple - often involving minor changes
to a single line of code - others are quite complex and attempting to
apply these patches by hand is definitely NOT recommended. Use the
patch program whenever you need to apply a patch. To apply the patches listed in this FAQ, use this procedure:
You should be left with a file containing a number of sections similar to:
*** ./src/msql/net.c.orig Tue Oct 17 11:24:03 1995
--- ./src/msql/net.c Tue Dec 12 15:24:11 1995
***************
*** 66,72 ****
! void writePkt(fd)
int fd;
{
u_char *cp;
--- 66,72 ----
! int writePkt(fd)
int fd;
{
u_char *cp;
cd /usr/local/src/db/Minerva/msql/msql-1.0.10
patch -l < /tmp/msql-patch1
The "-l" option is used to tell patch
to ignore any whitespace mismatches between lines in the patch file
and lines in the mSQL source file. Patch will respond with output similar to:
Hmm... Looks like a new-style context diff to me...
The text leading up to this was:
--------------------------
|*** ./src/msql/net.c.orig Tue Oct 17 11:24:03 1995
|--- ./src/msql/net.c Tue Dec 12 15:24:11 1995
--------------------------
Patching file ./src/msql/net.c using Plan A...
Hunk #1 succeeded at 66.
Hunk #2 succeeded at 84.
done
http://AusWeb.com.au/computer/Hughes/
To subscribe to the list, send the word "subscribe" in the body of your message to msql-list-request@bunyip.com.
To unsubscribe from the list send the word "unsubscribe" in the body of your message to msql-list-request@bunyip.com.
Postings should be addressed to msql-list@bunyip.com.
Archives of the mailing list are available via anonymous ftp from:
ftp://ftp.bunyip.com/pub/mailing-lists/msql-list.archive/
Each month's archive is stored in a file:
msql-list.archive.YYMM
where YYMM represents the year and month. So the archive for
October 1995 would be in the file:
msql-list.archive.9510
These files are also available from the majordomo mailing list server
at bunyip.com. To receive a list
of the archive files available as well as the majordomo help file send
a message to
majordomo@bunyip.com with the
text:
index msql-list
help
END
in the body of the message. To reach a human for help send a note to:
owner-msql-list@bunyip.com
or
listmaster@bunyip.com
The mailing list discusses issues that arise from the use of mSQL and
w3-msql (both products developed by David Hughes). Often discussions on
contributed software arise but it is probably best to take these
discussions off line and summarise solutions back to the list. Traffic on the list is moderate. There were approximately 2400 postings between February 1 1995 and October 18 1995 which gives an average of around 280 messages per month. (If you think this is high, try subscribing to the firewalls list - this has an average of around 1000 postings per month!)
Turn around times for postings can sometimes be a little slow. It is not unusual for messages sent from Australia to take a few hours to appear on the list. List subscribers from other countries have also reported similar turn around times. Please be patient.
Is there any way I can find the answer to this question myself?
If you can figure out a way to simply find the answer, then it will
probably be quicker than asking the list. If you think your answer
would be helpful to others then post a summary to the mailing list.
Postings should be addressed to msql-list@bunyip.com.
IF YOU POST A QUESTION TO THE LIST ASKING FOR HELP, YOU
MUST INCLUDE THE FOLLOWING INFORMATION!
Failure to include these details makes it almost impossible to pinpoint
the cause of your problem.
uname -a
msqladmin version
http://AusWeb.com.au/computer/Hughes/msql/history.htm
If you want to report a bug, send a report to the mSQL mailing list at msql-list@bunyip.com. Please include the following:
msqld.c
Save the original file as follows:
cp ./src/msql/msqld.c ./src/msql/msqld.c.orig
Make your changes to the file:
./src/msql/msqld.c
diff -c ./src/msql/msqld.c.orig ./src/msql/msqld.c
ftp://bond.edu.au/pub/Minerva/msql/Incoming
then notify David at
<bambi@hughes.com.au>
and he will place it in the mSQL
contributions
directory:
ftp://bond.edu.au/pub/Minerva/msql/Contrib
You may like to discuss your proposed code with others on the
mSQL mailing list. The subscribers to this
list may be able to help you with improvements or modifications to your
code or advise you of work already available in your area.
| msqld | the mSQL database server. |
| msqladmin | handles administrative details such as creation and deletion of databases, server shutdown etc. |
| msql | the mSQL monitor. It provides a user interface for making SQL queries on databases. |
| msqldump | Dumps a database in ASCII format. The dump takes the form of SQL commands so that the entire database can be re-created from the dump file. |
| relshow | The mSQL schema viewer.
Shows table details for a given database. |
For more details see the documentation that comes with mSQL.
char
int (4 bytes)
real (8 bytes)
The internal storage for types int and real is held
at 4 bytes and 8 bytes respectively regardless of the system
architecture you're using. So even on 64 bit Crays a real will be 8
bytes. For more details see the documentation that comes with mSQL.
mSQL version 2 will support more data types.
CREATE TABLE table_name (
col_name col_type [ not null | primary key ]
[, col_name col_type [ not null | primary key ] ]**
)
|
DROP TABLE table_name |
INSERT INTO table_name [ ( column [ , column ]** ) ]
VALUES (value [, value]** )
|
DELETE FROM table_name
WHERE column OPERATOR value
[ AND | OR column OPERATOR value ]**
OPERATOR can be <, >, =, <=, >=, <>, or LIKE
|
SELECT [table.]column [ , [table.]column ]**
FROM table [ = alias] [ , table [ = alias] ]**
[ WHERE [table.]column OPERATOR VALUE
[ AND | OR [table.]column OPERATOR VALUE]** ]
[ ORDER BY [table.]column [DESC] [, [table.]column [DESC] ]
OPERATOR can be <, >, =, <=, >=, <>, or LIKE
VALUE can be a literal value or a column name
|
UPDATE table_name SET column=value [ , column=value ]**
WHERE column OPERATOR value
[ AND | OR column OPERATOR value ]**
OPERATOR can be <, >, =, <=, >=, <>, or LIKE
|
For more details see the documentation that comes with mSQL.
mSQL version 2 will support more SQL commands.
For more details see the documentation that comes with mSQL.
| int | msqlConnect(char *host); | FUNCTION |
| int | msqlSelectDB(int sock, char *dbname); | FUNCTION |
| int | msqlQuery(int sock, char *query); | FUNCTION |
| m_result * | msqlStoreResult(); | FUNCTION |
| void | msqlFreeResult(m_result *result); | FUNCTION |
| m_row | msqlFetchRow(m_result *result); | FUNCTION |
| void | msqlDataSeek(m_result *result, int pos); | FUNCTION |
| int | msqlNumRows(m_result *result); | MACRO |
| m_field * | msqlFetchField(m_result *result); | FUNCTION |
| void | msqlFieldSeek(m_result *result, int pos); | FUNCTION |
| int | msqlNumFields(m_result *result); | MACRO |
| m_result * | msqlListDBs(int sock); | FUNCTION |
| m_result * | msqlListTables(int sock); | FUNCTION |
| m_result * | msqlListFields(int sock, char *tableName); | FUNCTION |
| void | msqlClose(int sock); | FUNCTION |
For more details see the documentation that comes with mSQL.
Note: The PostScript documentation that comes with mSQL lists the function msqlClose() as type int. This is incorrect. It is actually type void.
This feature may be included in mSQL version 2.
As an example, consider this method to find the list of grandparents from a child/parent tuple:
SELECT t1.parent, t2.child from parent_data=t1,parent_data=t2
WHERE t1.child=t2.parent
mSQL also supports the SQL standard method of table aliasing which uses
either a space character or the keyword AS instead of an = character.
So the above example can also be written as either:
SELECT t1.parent, t2.child from parent_data t1,parent_data t2
WHERE t1.child=t2.parent
or
SELECT t1.parent, t2.child from parent_data AS t1,parent_data AS t2
WHERE t1.child=t2.parent
mSQL has an access control file which allows the database administrator to control access on a user and host level.
For more details see the documentation that comes with mSQL.
mSQL does not support access control from within SQL commands.
The current release of mSQL has NO direct support for BLOBs. However, you can always store the path name of a file that points to the BLOB in one of the fields of your table. Then your application can deal with the file name appropriately.
If you're dealing with large blocks of text, you may also wish to consider this approach from Pascal Forget <pascal@wsc.com>:
Another possible hack would be to have the "block_text"
record contain a pointer to a "text_lines" table. This
table would contain a linked list of text lines like this:
CREATE TABLE text_lines (
line_id int primary key,
string char(80),
next_line int
)
Later versions of mSQL may have support for BLOBs.
The mSQL server handles requests serially - that is only one user's request is handled at a time. Therefore there is no danger of a user reading from a table that is currently being updated by another user.
However, there is the possibility that the same read operations may straddle another user's write operation so that different data will be returned from each read.
mSQL version 2 will provide client initiated locking.
19 ----- \ \ i 52 * / 63 = 813621925049196536663393538834956800 / ----- i = 0Though in practise, many of these combinations will probably remain unused.
While not recommended, the default maximum name length value of 20 can be changed by editing the mSQL source code. However, if you change it AFTER you have already created ANY databases, the old databases will be unreadable. To avoid this problem, follow this procedure:
./src/msql/msql_priv.h
Change the line reading
#define NAME_LEN 20 /* Field/table name length */
to suit your needs.
./src/msql/msql_priv.h
contains the definitions of the internal mSQL limits:
#define MAX_FIELDS 75 /* Max fields per query */
#define MAX_CON 24 /* Max connections */
#define BUF_SIZE (256*1024) /* Read buf size if no mmap() */
#define NAME_LEN 20 /* Field/table name length */
#define PKT_LEN (32*1024) /* Max size of client/server packet */
#define CACHE_SIZE 8 /* Size of table cache */
If you want to increase them you can just edit this file and recompile.
Don't change MAX_CON or CACHE_SIZE without understanding why these
limits are set (maximum file descriptors per process etc). Changing any of these parameters will almost certainly make any existing databases unreadable. To avoid this problem, follow this procedure:
./src/msql/msql_priv.h
changing the definitions to suit your needs.
Consider the SQL query:
SELECT something from somewhere WHERE
name='jan' or country='italy' and sex='female' or title='ms'
Under the current release of mSQL, the parser will scan the condition
from left to right. So in this example the condition reads:
((name='jan' or country='italy') and sex='female') or title='ms'
The current release of mSQL does NOT support parentheses in logical
expressions, so there is NO way to change this parsing. Future versions of mSQL may support user defined levels of associativity.
In C, for example, see the manual pages on atoi().
Does SELECT return the rows always in order 'first inserted first',
if there is no ORDER statement given, and the rows are selected
from one table only, and there has been no DELETEs on that table?
It seems be so, but is it guaranteed?
David Hughes replied:
This is guaranteed. The only time the rows will be returned in
another order is if you have deleted a row and it's then filled by
a later insert.
I am new at mSQL, and have a beginner question: Is it possible to
create a table "normally", and to have the fields of one
of the column being[sic] another table?
David Hughes replied:
You can't nest tables in mSQL (don't think you can in ANSI SQL
either). What you can do is to use a common value as a key to join
the contents of two tables (eg. a part number or a user ID).
/usr/local/Minerva/
then the databases will be created in the directory:
/usr/local/Minerva/msqldb/
Note that this can be overridden by specifying the MSQL_HOME
environment variable when starting
msqld. Each table in the database is stored as a number of files:
For each field in a table, mSQL will also store an additional flag byte. mSQL also stores an additional flag byte for each row of the table.
Consider the following table:
CREATE TABLE test (
f0 char(13),
f1 int,
f2 real,
f3 real,
f4 real,
f5 real,
f6 int
)
Storage space for each row of this table would be:
(13 * char) + (2 * int) + (4 * double) + (7 * fields) + (1 * rows)
= (13 * 1) + (2 * 4) + (4 * 8) + (7 * 1) + 1
= 61 bytes
So if this table had 1000 records, it would occupy 61000 bytes of
disk space. (In reality it may occupy slightly more real disk space
because of the way the underlying file system behaves. This is
operating system specific and not really an issue to worry about.
If you do an 'ls -l' on the file it will show 61000
bytes).
The size of this file will be the size of the key plus one flag byte times the number of rows in the table. In the above example, if the table was defined as:
CREATE TABLE test (
f0 char(13) primary key,
f1 int,
f2 real,
f3 real,
f4 real,
f5 real,
f6 int
)
and the table had 1000 rows, the size of the data file would still
be 61000 bytes and the size of the key file would be:
((13 * char) + 1) * 1000
= ((13 * 1) + 1) * 1000
= 14 * 1000
= 14000 bytes
Each field in the table has a 64 byte definition. Using the example above, the table has 7 fields so the size of the definition file will be:
7 * 64 = 448 bytes
For every hole in the table, this file will contain a 4 byte integer indicating the row number of the hole. It is accessed like a stack. When a row is deleted, it's index is appended to the file. When an insert is done, the last 4 bytes are "popped" off the file and the file is truncated back 4 bytes.
If the table contains 20 holes, the size of the stack file will be:
20 * 4 = 80 bytes
If the table contains no holes then this file will have zero
length.
table_storage_requirements
= expected_max_rows *
(
number_of_fields + 1 + total_chars +
(4 * total_ints) + (8 * total_reals) +
(size_of_key + 1) +
(4 * expected_deletion_ratio)
) +
(total_fields * 64)
table_storage_requirements
= expected_max_rows *
(
number_of_fields + 1 + total_chars +
(4 * total_ints) + (8 * total_reals) +
(4 * expected_deletion_ratio)
) +
(total_fields * 64)
10000 *
(
7 + 1 + 13 +
(4 * 2) + (8 * 4) +
(13 + 1) +
(4 * 0.10)
) +
(7 * 64)
= 10000 * ( 21 + 8 + 32 + 14 + 0.4) + 448
= 754448 bytes
plus a handful of bytes to store file names in directories. Note that this is the maximum storage allocation. Unlike some other database systems, mSQL only uses disk space when it has data to add to a table - it does NOT allocate a large block of empty disk space and then proceed to fill it. If our example only had 1000 rows the storage requirements would only be 75848 bytes.
does msqld allocate more ram to itself as new db's are added? i.e.
is any part of the database held in ram or does it just access the
database files directly from disc? I need to do some planning, and
want to know if I need to plan to get more simms...
David Hughes replies:
If your OS supports mmap() (e.g. Solaris, SunOS, *BSD, BSDI, Linux
1.3.x, HP-UX >9.x) then the more memory you throw at it the
better things will get if you are using big databases. The data is
left on disk but is accessed via the virtual memory subsystem so it
will be in memory some of the time.
If you are not using mmap() then data is just read from disk as it
is needed. There's a small buffer in the read code to make things
faster but that's about it. It doesn't matter how many databases
you have defined it only uses 1 buffer.
Does performance degrade at all as the number of databases
increases? That is, say a query from database A took n seconds when
database A was the only one served by msqld. After adding databases
B, C, D and E, should the database A query take any longer? It
seems like 'no' from my experience, but...
David Hughes replies:
No. It will degrade if people are hitting the new databases at the
same time as they are hitting database A though. msqld only handles
1 query at a time so if 2 queries come in they are queued and
processed in order.
> To browse the database, I want mSQL to return me the first row
> in the database, and keep a pointer to it. Then sometime later
> I can ask it for the second row, and so on.
mSQL does not provide support for cursors.
You'll have to issue a SELECT query each time you want the next
row. mSQL has no provisions for modifying a result set once it has
been created. I suggest you add a field containing a unique
identifier for each row, then fetch the next row using:
SELECT ... FROM mytable where unique_field > last_id LIMIT 1
> How do I express this in sql?
If you find a way to express it, it most certainly won't be in
standard SQL, as the language has no support for cursors.
> I see that I could add an explicit field that was an arbitrary
> row number, and query for the current row number +/- 1, but over
> time with insertions and deletions there would be gaps and the
> query would break. How is this problem usually solved?
The select statement I gave you won't break even if there are gaps
in the unique identifiers. You can periodically "compact" the
numbers if you want.
The solution to the problem with using the socket and then nsl
libraries with NIS in Irix 5.2 is:
1. Do not link them if they are not needed :) This is the case for
mSQL.
or
2. link libc BEFORE the socket and the nsl libraries.
For those who didn't know, the problem is that if you use NIS and
link socket or nsl, the getpwuid() function doesn't work.
Looks like the same thing that happens under HP-UX with background
processes in rc scripts. They are killed off on exit of the ksh
functions.
Create yourself a wrapper for msqld. In there you do a fork and
exit the parent process in the child process you do a call to
setsid() to get rid of the controlling terminal followed by a call
to execl() to launch msqld. You might also want to close all open
file descriptors before calling exec.
DEC Alphas running OSF/1 (Digital Unix): The original mSQL docs
recommended using cc rather than gcc on this platform. In my
experience this is still good advice. If you have gcc on the
machine, however, autoconf will find it and default to it. After
running 'setup' edit site.mm and change 'gcc' to 'cc' and
'bison -y' to yacc.
If you have an earlier version of Linux you can either upgrade or ensure that mmap() support in mSQL is disabled by running the 'setup' program and then editing
./targets/your-architecture/site.mm
and ensuring the mmap() directive reads:
MMAP=
and then recompile the mSQL package.
I just built msql-1.0.10 on hpux 9.05. It appears that you have
slain the mmap bug. Good job. The only mods I are in site.mm CC= cc
-Ae +O3 +Z. Remove -g flags also or you'll get a lot of annoying
messages about opt not compatible with debug. The test suites for
msql and msqlperl ran flawlessly.
Ensure the EXTRA_CFLAGS option in the file:
./targets/your-architecture/site.mm
reads:
EXTRA_CFLAGS= -Ae +O2 +Z
and recompile mSQL. The +Z option ensures that "position independent code" is used when creating object files. For more information see the manual pages on your compiler.
The following is a summary of the efforts required by Andrew Cash <cash_a@sls.co.uk> to install mSQL version 1.0.8 on a SCO Unix system. It should work perfectly well for version 1.0.9 as well.
gunzip -c msql-1.0.9.tar.gz | tar xvf -
cd msql-1.0.9
make target
cd targets/your-architecture
./setup
answer questions
./common/config.h
ensure the lines referring to the sys/select.h include file are
commented out as follows:
/* Defined if you have sys/select.h */
/* #define HAVE_SYS_SELECT_H 1 */
./msql/msql_yacc.c
so run:
make
until it completes (or fails). Ensure that the msql_yacc.c
file has been created. If it has, apply the following patch to the
file. (Use the "-l" option of patch to
avoid any problems with mismatched whitespace). This patch ensures that <malloc.h> is explicitly included and that all references to alloca() are changed to malloc().
*** msql/msql_yacc.c.orig Fri Jan 5 13:07:02 1996
--- msql/msql_yacc.c Fri Jan 5 13:09:34 1996
***************
*** 329,362 ****
Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. */
- #ifndef alloca
- #ifdef __GNUC__
- #define alloca __builtin_alloca
- #else /* not GNU C. */
- #if (!defined (__STDC__) && defined (sparc)) || defined (__sparc__) || defined (__sparc) || defined (__sgi)
- #include <alloca.h>
- #else /* not sparc */
- #if defined (MSDOS) && !defined (__TURBOC__)
- #include <malloc.h>
- #else /* not MSDOS, or __TURBOC__ */
- #if defined(_AIX)
#include <malloc.h>
- #pragma alloca
- #else /* not MSDOS, __TURBOC__, or _AIX */
- #ifdef __hpux
- #ifdef __cplusplus
- extern "C" {
- void *alloca (unsigned int);
- };
- #else /* not __cplusplus */
- void *alloca ();
- #endif /* not __cplusplus */
- #endif /* __hpux */
- #endif /* not _AIX */
- #endif /* not MSDOS, or __TURBOC__ */
- #endif /* not sparc. */
- #endif /* not GNU C. */
- #endif /* alloca not defined. */
/* This is the parser code that is written into each bison parser
when the %semantic_parser declaration is not specified in the grammar.
--- 329,335 ----
***************
*** 607,618 ****
yystacksize *= 2;
if (yystacksize > YYMAXDEPTH)
yystacksize = YYMAXDEPTH;
! yyss = (short *) alloca (yystacksize * sizeof (*yyssp));
__yy_bcopy ((char *)yyss1, (char *)yyss, size * sizeof (*yyssp));
! yyvs = (YYSTYPE *) alloca (yystacksize * sizeof (*yyvsp));
__yy_bcopy ((char *)yyvs1, (char *)yyvs, size * sizeof (*yyvsp));
#ifdef YYLSP_NEEDED
! yyls = (YYLTYPE *) alloca (yystacksize * sizeof (*yylsp));
__yy_bcopy ((char *)yyls1, (char *)yyls, size * sizeof (*yylsp));
#endif
#endif /* no yyoverflow */
--- 580,591 ----
yystacksize *= 2;
if (yystacksize > YYMAXDEPTH)
yystacksize = YYMAXDEPTH;
! yyss = (short *) malloc (yystacksize * sizeof (*yyssp));
__yy_bcopy ((char *)yyss1, (char *)yyss, size * sizeof (*yyssp));
! yyvs = (YYSTYPE *) malloc (yystacksize * sizeof (*yyvsp));
__yy_bcopy ((char *)yyvs1, (char *)yyvs, size * sizeof (*yyvsp));
#ifdef YYLSP_NEEDED
! yyls = (YYLTYPE *) malloc (yystacksize * sizeof (*yylsp));
__yy_bcopy ((char *)yyls1, (char *)yyls, size * sizeof (*yylsp));
#endif
#endif /* no yyoverflow */
make
./msql/Makefile.full
and comment out the mode change line for msqld as
follows:
# chmod 4750 $(INST_DIR)/bin/msqld
Failure to make this change will result in an error during the
install phase.
make install
If you installed Slackware and didn't get the K series disks then you will be missing a lot of C header files that you need to compile mSQL. Go back and grab the kernel sources disks and install them on your box.
If you indicated that it would not be running as root, you would have then nominated a username for the "root user". In that case, you must be logged in as the user you nominated before you can perform admin functions like creation of databases.
The manual also states that you can only perform the admin functions of msqladmin (i.e. any function other than 'version') from the local host. For security reasons you cannot perform administrative functions in a client/server manner of a network.
A sample ACL file is installed in the installation directory. You could copy this file to msql.acl and edit it to reflect the access you want to offer to your databases.
If you are seeing an error regarding the PID file, then one of the following could be the cause:
Top of install tree ? [/usr/local/Minerva]
Will this installation be running as root ? [y] n
What username will it run under ? peters
Directory for pid file ? [/var/adm]
You must ensure that this directory exists. The mSQL installation
procedure will NOT create this directory for you. If you did not specify root as the mSQL administration user when you answered the questions:
Will this installation be running as root ? [y] n
What username will it run under ? peters
you must ensure that the user you specified has write permissions in
the directory in which msqld will store its PID file. Under Irix 5.3 the /var/adm directory can only be written to by the root user, so if your mSQL administration user is NOT root then you'll have to choose some other location such as /var/tmp or /var/share.
If you need to change the location of this directory, you can either rerun the setup program, or edit the file:
./targets/your_architecture/site.mm
and change the line:
PID_DIR= /var/adm
to suit your needs.
Failure to do this will almost certainly guarantee that your applications will fail at some stage while talking to the new mSQL database server. You may also miss out on some new feature provided by the new mSQL API.
-rwsr-xr-x 1 peters db 24576 Nov 13 1995 db_app
Access to database denied
mSQL version 2 will have radically different security mechanisms.
msqladmin shutdown
The TCP/IP port will remain bound for about 90 seconds or so. After
this time the port should be available and msqld can be
started successfully. Another possibility to consider is that something is already using the TCP/IP port that msqld is trying to use. For a default installation these port numbers are 1112 for a root user or 4333 for an ordinary user. In this case user means the name of the user you entered when answering the setup question(s):
Will this installation be running as root ?
What username will it run under ?
There are a number of ways you can check for something using the TCP/IP
port:
If the mSQL monitor program msql can connect to the mSQL database server msqld then you KNOW that the database server is already running.
Telnet to the database server and specify the mSQL TCP/IP port number using one of the following commands:
telnet dbhost 1112
or
telnet dbhost 4333
You'll see the following types of messages:
Trying 127.0.0.1 ...
telnet: connect: Connection refused
telnet>
Trying 127.0.0.1 ...
Connected to localhost.
Escape character is '^]'.
0:6:1.0.10
Trying 127.0.0.1 ...
Connected to localhost.
Escape character is '^]'.
If your operating system has the netstat command, you can use it to display the contents of various network related data structures in various formats, depending on the options you select. Some of the options that may be helpful are:
netstat -a | grep 4333
If you see output similar to:
*.4333 *.* 0 0 0 0 LISTEN
then something is using that port.
netstat -f unix
Output similar to the following will indicate that msqld
is already running:
Active UNIX domain sockets
Address Type Vnode Conn Addr
fcf8bca8 stream-ord 231 0 /tmp/msql.sock
This may not work for all operating systems. - the above examples
were taken from a Solaris 2.4 system. Variations on this command
include:
netstat -f inet
or
netstat -f local
msqladmin shutdown
If you don't have a running msqld process then something else
may be using the port that msqld is trying to use. Examine
/etc/inetd.conf and /etc/services (or the services
NIS map if you're running NIS) to see if anything else is using the
port. The output from one of the netstat commands listed
above may be helpful. If you find such a program you have two options:
/*
** TCP port for the MSQL daemon
*/
#ifdef ROOT_EXEC
#define MSQL_PORT 1112
#else
#define MSQL_PORT 4333
#endif
to suit your needs. Then recompile and reinstall mSQL.
'Bambi's'
would be entered as
'Bambi\'s'.
Note: This applies when using
msql - the database monitor
program. If you're developing your own application you may have to
escape other characters that are special to the language you're using -
for example perl, C or tcl.
c:\windows\system\
would be entered as
'c:\\windows\\system\\'
When using regular expressions in queries of the form:
SELECT table.column FROM table WHERE table.column LIKE 'regexp'
the following rules apply:
|
To search for this character |
Use this string |
or this string |
To search for this character |
Use this string |
or this string |
|---|---|---|---|---|---|
| $ | \\\\$ | [$] | [ | \\\\[ | [[] |
| % | \\% | [%] | \ | \\\\\\\\ | [\\\\] |
| ' | \' | ^ | \\\\^ | [^] | |
| ( | \\\\( | [(] | _ | \\_ | |
| ) | \\\\) | [)] | | | \\\\| | [|] |
| ? | \\\\? | [?] | |||
Note: This applies when using msql - the database monitor program. If you're developing your own application you may have to escape other characters that are special to the language you're using - for example perl, C or tcl.
For example
insert into foo values ( NULL, 1, 2, 'some text' )
SELECT * FROM my_table WHERE my_field LIKE '[Ss][Oo][Mm][Ee]
[Vv][Aa][Ll][Uu][Ee]'
Sol Katz's <skatz@blm.gov>
Object Oriented HTML API includes a C routine
that converts a string into its case insensitive form. You may wish to
use this in any C code that you write. See the section below on
"Contributed Code and Third Party
Applications" Alternatively, you can create an additional field in each table that will hold a single case version of the information you are likely to be searching for.
For perl users, Michael Cowden <cowden@leithaus.leitess.com> has contributed this code example:
The following statement turns mSQL into [mM][sS][qQ][lL]
$string = "mSQL";
$string =~ s/(.)/\[\L$1\E\U$1\E\]/g;
Vivek Khera
<khera@kci.kciLink.com>
suggests a simpler method for perl users:
Personally, I use this in Perl, as there is no need to complicate
the regular expression with non-alpha characters.
$string =~ s/([A-Za-z])/\[\L$1\U$1\]/gi;
Version 2 of mSQL will support functions similar to upper()
and lower() which will obviate the need for the above.
One possible solution is to use msqldump to create an ASCII dump of the entire database. Then edit this dump file by hand and add the extra field to the CREATE clause. You'll also need to edit each INSERT clause to ensure that the new field is referenced. Once you've modified the dump file, drop and recreate the database using msqladmin and repopulate the new database using the dump file and msql.
This procedure could be automated by a shell or perl script.
As an example consider this output from msqldump
#
# mSQL Dump (requires mSQL-1.0.6 or better)
#
# Host: localhost Database: test
#--------------------------------------------------------
#
# Table structure for table 'test'
#
CREATE TABLE test (
name CHAR(40),
num INT
) \g
#
# Dumping data for table 'test'
#
INSERT INTO test VALUES ('item 999',999)\g
...
INSERT INTO test VALUES ('item 0',0)\g
If you wish to insert a field, say "discount", then you will
need to modify the dump file as follows:
#
# mSQL Dump (requires mSQL-1.0.6 or better)
#
# Host: localhost Database: test
#--------------------------------------------------------
#
# Table structure for table 'test'
#
CREATE TABLE test (
name CHAR(40),
num INT,
discount REAL
) \g
#
# Dumping data for table 'test'
#
INSERT INTO test VALUES ('item 999',999,0.0)\g
...
INSERT INTO test VALUES ('item 0',0,0.0)\g
Notice that every insert clause MUST be changed as well as the
table definition.
| < | >= | by | distinct | integer | not | real | update |
| <= | all | char | drop | into | null | select | values |
| <> | and | create | from | key | or | set | where |
| = | as | delete | insert | like | order | smallint | |
| > | asc | desc | int | limit | primary | table |
Remember that mSQL reserved words are case insensitive so UPPER case or MiXeD cAsE reserved words are also forbidden in table or field names.
SELECT number FROM table ORDER BY number DESC LIMIT 1
To obtain the minimum value use:
SELECT number FROM table ORDER BY number LIMIT 1
This will only work with mSQL 1.0.9 and above unless you have applied
the unofficial LIMIT patch to earlier versions. See the
mSQL mailing list archives for details on
this patch. (Before searching for this unofficial patch, you should
seriously consider upgrading to the
latest version of mSQL). Note: Rasmus Lerdorf writes:
The LIMIT statement limits the number of records actually
transferred from the server to the client. It doesn't limit the
scope of the search at all in any way. That means that if you are
looking for the maximum value in a table with 30,000 entries, the
query will first build the entire sorted result in memory in the
server, but when it comes time to transferring the result to the
client, it only sends the first item.
In many cases, especially when you have a lot of fields, or long
fields, the time it takes to transfer the data from the server to
the client is actually many times that of the actual search. And
the msqld daemon is tied up and not available to other clients
while it is wasting time sending result records that will never be
used. So, if you do queries and you know you will only be looking
at the first couple of them, you should use the limit clause and
cut down on the amount of useless records being sent across the
socket.
relshow
or
relshow -h host
This will return output similar to:
+-----------------+ | Databases | +-----------------+ | test | | photos | | patches | +-----------------+
relshow dbname
or
relshow -h host dbname
This will return output similar to:
Database = test
+---------------------+
| Table |
+---------------------+
| test_table |
| addresses |
| telephone |
+---------------------+
relshow dbname tablename
or
relshow -h host dbname tablename
This will return output similar to:
Database = test
Table = test_table
+-----------------+----------+--------+----------+-----+
| Field | Type | Length | Not Null | Key |
+-----------------+----------+--------+----------+-----+
| name | char | 40 | N | N |
| num | int | 4 | N | N |
+-----------------+----------+--------+----------+-----+
Programs that were connected to the mSQL database server should be either restarted or have some internal mechanism whereby they notice the server has died and attempt a reconnection.
One possible method for checking the status of the database server would be to examine the return status of the msqlSelectDB() call.
Note: Please consult the documentation that comes with each of these applications to determine the licensing obligations that may be involved in their use.
ftp://bond.edu.au/pub/Minerva/esl/esl-0.3.tar.gz
(407046 bytes)
ftp://bond.edu.au/pub/Minerva/msql/w3-msql/w3-msql-1.0.tar.gz
(30203 bytes)
At the time of compiling this FAQ, version 1.1 was being prepared.
ftp://ftp.ceo.org/pub/ewse-mSQL-apache-demos/apache-msql-demo.1.0.1.tar.gz
(12723 bytes)
or
ftp://ftp.apache.org/pub/apache/incoming
ftp://gopher.library.tudelft.nl/pub/misc/dbf2msql-0.3.tar.gz
(7568 bytes)
http://www.hermetica.com
ftp://ftp.mcqueen.com/pub/dbperl
It is also available from any CPAN (Comprehensive Perl Archive
Network) site in the "modules" directory. For more
information about CPAN see:
ftp://ftp.funet.fi/pub/languages/perl/CPAN/CPAN
The latest blurb describing Alligator's work can be obtained from:
http://www.hermetica.com/technologia/DBI
Digger is a Distributed Directory Service for the Internet based on Whois++ technology. For more information about digger send mail to <digger-info@bunyip.com> or have a look at Bunyip's web pages:
http://www.bunyip.com/products/digger
ftp://bond.edu.au/pub/Minerva/msql/Contrib/sql-mode.tar.gz
(6883 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/msql-import-0.0.6.tar.gz
(7516 bytes)
Rasmus writes:
For anybody using my FI form interpreter or David's w3-msql
package, this PHP package should be of interest to you. It is an
html embedded script language cgi wrapper with built-in web page
access logging, access restriction, as well as support for ndbm,
gdbm and mSQL databases through a powerful C-like scripting
language. It is based on the original FI concept to which the
functionality of the earlier PHP package has been added to form a
single tool.
The mSQL support is just a small set of functions supported by the
package. A full set of string manipulation, regular expression,
directory and file routines complement the script language.
The source distribution as well as more information is available
at:
http://www.io.org/~rasmus.
ftp://bond.edu.au/pub/Minerva/msql/Contrib/MsqlJava-1.0.1.tar.Z
(13392 bytes)
For more details see:
http://www.minmet.uq.oz.au/msqljava
ftp://bond.edu.au/pub/Minerva/msql/Contrib/winapi.zip
(87211 bytes)
Not included in winapi.zip is an msql.ini file.
Its contents should resemble
[Server]
IP=your.server.host.name
Port=1112
Username=YourUsername
There also appears to be a later version of Dean's work which
includes compiled executables in
ftp://bond.edu.au/pub/Minerva/msql/Contrib/winmsql7.zip
(306827 bytes)
An Enterprise Object Framework (EOF) is an object framework that allows object oriented access to relational databases, where each row is considered an object. Besides a few limitations, it basically makes a relational database look like an OO database to the developer. By means of an adaptor, EOF can be used with virtually any database. The adaptor is responsible to transform the generic OO messages in database specific queries by subclassing a generic adaptor and modifying its behaviour.
It is available via anonymous ftp from:
ftp://ftp.blm.gov/pub/gis/msql_api.tar.gz
(10317) bytes
An example can be found at
http://www.blm.gov/gis/msql/dbs6.html
ftp://ftp.comed.com/pub/odbc
For more information on Dean's work see:
http://alfred.niehs.nih.gov
Onyx consists of a transaction manager, a shell like 4GL and a Simple Database Transaction Protocol engine.
"Onyx is designed by the Model-View-Controller paradigm, so tables are the model, masks are the views and transactions are the controllers which can be bound to an input field, a menu, function keys or the change of the current record in a cursor."
It is available via anonymous ftp from:
ftp://ftp.uni-bremen.de/pub/unix/database/Onyx/Onyx.2.45.src.tar.gz
(195872) bytes
ftp://bond.edu.au/pub/Minerva/msql/Contrib/MsqlPerl-1.03.tar.gz
(15037 bytes)
It is also available from any CPAN (Comprehensive Perl Archive
Network) site in the "modules" directory. For more
information about CPAN see:
ftp://ftp.funet.fi/pub/languages/perl/CPAN/CPAN
ftp://bond.edu.au/pub/Minerva/msql/Contrib/PymSQL.tar.gz
(7581 bytes)
ftp://ftp.qut.edu.au/src/REXXSQL/
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLBase-1.00.tgz
which is a symbolic link to
ftp://bond.edu.au/pub/Minerva/msql/Contrib/msqlb-1.00.tgz
(38136 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/msqltcl-1.50.tar.gz
(58929 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/tcl_msql.tar.gz
(7998 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/time_library.tar.gz
(7989 bytes)
tkmSQL requires:
Note: This is a perl Tk module and is NOT to be confused with Tk itself. It can be obtain via anonymous ftp from:
ftp://ftp.wpi.edu/perl5
There is also a FAQ available from:
http://w4.lns.cornell.edu/~pvhp/ptk/ptkFAQ.html
You may obtain tkmSQL via anonymous ftp from:
ftp://ftp.mcqueen.com/pub/databases/dbatools/tkmSQL
http://www.synthcom.com/cgi-bin/gear
and the source code can be obtained via anonymous ftp from:
ftp://ftp.synthcom.com/pub/stuff
ftp://bond.edu.au/pub/Minerva/msql/Contrib/sgs1.0.0.tar.gz
(24216 bytes)
WDB is a software tool set that tremendously simplifies the integration of SQL based databases into the World Wide Web. WDB lets you provide WWW access to the contents of databases without writing a single line of code!
At the moment WDB supports Sybase, Informx and mSQL. However it is relatively easy to port it to other SQL based databases.
For more details on WDB see:
http://arch-http.hq.eso.org/bfrasmus/wdb
"This is a C web CGI script to examine and modify rows in tables of an mSQL database. You should use Netscape or another browser which supports HTML 3.0 tables."
More details and sample output are available from:
http://www.ua.com/websql
Source code is available from:
http://www.ua.com/websql/websql.tar.gz
(24225 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/zmsql-2.1.tar
(40960 bytes)