Re: Script question

[Gerald Taylor]

Philippe Poelvoorde wrote:

mysql> system echo hi there;
mysql> system ls -ls;
mysql> system uname-a;
all work on the 2.4.22-10mdk kernel and
the semi-colon is optional.
mysql> system echo "hi there"
should be also working, the first parameters 'echo' is recognized as the 
  command to execute, and the folowings strings the parameters of this 
command. so since 'echo hi there' is not a command it wasn't working 
(try, doing `$echo\ hi\ there` at your prompt :)
Since some commands require quotes, it could be useful.
(system doesn't work with MySQL 3.23, does it ?)

4.0.18, doesnt seem to work 4 me.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help with ALTER TABLE error

[John Stile]

On Tue, 2004-10-19 at 21:42, John Stile wrote:
> I have Mysql Cluster (version 4.1.16-gama) running, and now I need to
Sorry, I meant version mysql-max-4.1.6-gamma-pc-linux-i686.


signature.asc
Description: This is a digitally signed message part

Help with ALTER TABLE error

[John Stile]

I have Mysql Cluster (version 4.1.16-gama) running, and now I need to
convert database tables from engine MyISAM to NDBCLUSTER, but ALTER
TABLE fails on some tables.

Does anyone know what the error means or how to get around it?

mysql> use database1;
mysql> alter table attr engine=NDB;
ERROR 1005: Can't create table './database1/#sql-4627_3a.frm' (errno: 4009)

-- 
._.
|   \0/John Stile |
| UniX Administration |
|   / \  510-305-3800 | 
| [EMAIL PROTECTED] |
.-.



signature.asc
Description: This is a digitally signed message part

Re: counting rows

[Eric Bergen]

When you get the results of the query use mysql_num_rows(res) to get
the number of groups. Or if you only want to return the number of
groups in a query use something like. select count(distinct field)
from table;

-Eric


On Wed, 20 Oct 2004 03:03:47 +0200, Marco <[EMAIL PROTECTED]> wrote:
> I would like to count all of the resulting rows of my query.
> But my query contains a "group by", so
> 
> select count(*) from table group by field
> 
> will select an individual group count for each group. Instead, I would
> like to count the number of groups.
> 
> Thanks,
> Marco
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Eric Bergen
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: what is wrong woth this statement?

[Leo]

i didnt fully catch you...
is this the kind of query statement you want?

INSERT INTO some_other_table
SELECT
some_field_list
FROM z_mail_systems
HAVING COUNT(any_field)>0


On Tue, 19 Oct 2004 12:45:30 -0400, Josh Howe <[EMAIL PROTECTED]> wrote:
> if (select count(*) from z_mail_systems > 0) then [insert statement]
> endif;
> 
> How do I do this kind of conditional insert? Thanks.
> 
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: B-tree index question

[mos]

At 04:15 PM 10/23/2004, you wrote:
Hello,
We want to be able to insert records into a table containing a billion 
records in a timely fashion.
The table has one primary key, which I understand is implemented using 
B-trees, causing insertion to slow by log N.
The key field is an auto_increment field.
The table is never joined to other tables.
Is there any way we could implement the index ourselves, by modifying the 
MyISAM table handler perhaps? Or writing our own?
In our setup record n is always the nth record that was inserted in the 
table, it would be nice to just skip n * recordsize to get to the record.

Also, could someone shed some light on how B-tree indexes work. Do they 
behave well when values passed in are sequential (1, 2, 3, ...) rather 
than random values?

Thanks in advance,
-Phil
Phil,
The fastest method to load data into a table is to use "Load Data 
Infile". If the table is empty when the command is executed, then the index 
is not updated until after the command completes. Otherwise if you are 
loading a lot of data, you may want to drop the index and rebuild it later. 
Unfortunately "Alter Table table_name disable keys" won't work on unique 
indexes (primary).

Mike 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

counting rows

[Marco]

I would like to count all of the resulting rows of my query.
But my query contains a "group by", so
select count(*) from table group by field
will select an individual group count for each group. Instead, I would 
like to count the number of groups.

Thanks,
Marco
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tables and performance

[Yves Arsenault]

Thanks for the reply.

Yves

On Tue, 19 Oct 2004 14:22:01 +0300, Egor Egorov <[EMAIL PROTECTED]> wrote:
> Yves Arsenault <[EMAIL PROTECTED]> wrote:
> 
> > Does a very large number of tables in a database affect MySQL's performance?
> 
> Strictly saying, yes. But the difference won't really matter. So read - no, it
> won't affect performance.
> 
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
><___/   www.mysql.com
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Yves Arsenault

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trying to match on something that is not there

[Brian]

select s.userid from surveyanswers s where s.userid not in (select
distinct u.id from users u)


On Tue, 19 Oct 2004 16:30:29 -0600, Jonathan Duncan <[EMAIL PROTECTED]> wrote:
> I have a user who is using the following query to try and delete rows
> from one table based on the lack of a user id in another table:
> 
> SELECT s.questionid, s.userid, s.questionanswer
> FROM Users u, SurveyAnswers s
> WHERE u.id != s.userid
> 
> The corresponding user rows have already be deleted from the table
> Users.  Thus, this of course seems to match on just about everything
> since there is no actualy record in the Users table to match on.
> 
> Is there some way to match on a lack of information?
> 
> Thanks,
> Jonathan Duncan
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Trying to match on something that is not there

[Jonathan Duncan]

I have a user who is using the following query to try and delete rows
from one table based on the lack of a user id in another table:

SELECT s.questionid, s.userid, s.questionanswer
FROM Users u, SurveyAnswers s
WHERE u.id != s.userid

The corresponding user rows have already be deleted from the table
Users.  Thus, this of course seems to match on just about everything
since there is no actualy record in the Users table to match on.

Is there some way to match on a lack of information?

Thanks,
Jonathan Duncan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

B-tree index question

[Phil Bitis]

Hello,

We want to be able to insert records into a table containing a billion records in a 
timely fashion.
The table has one primary key, which I understand is implemented using B-trees, 
causing insertion to slow by log N.
The key field is an auto_increment field.
The table is never joined to other tables.
Is there any way we could implement the index ourselves, by modifying the MyISAM table 
handler perhaps? Or writing our own?
In our setup record n is always the nth record that was inserted in the table, it 
would be nice to just skip n * recordsize to get to the record.

Also, could someone shed some light on how B-tree indexes work. Do they behave well 
when values passed in are sequential (1, 2, 3, ...) rather than random values?

Thanks in advance,
-Phil

Re: Strange results from a query

[David Griffiths]

Sorry - removed some data to make it clearer.
insert into master (col1) values (1), (2);
is correct.
David
Michael Stassen wrote:
Before I think about this, which is it?
insert into master (col1) values (1), (2);
or
insert into master (col1) values (1), (2), (3);

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Strange results from a query

[Michael Stassen]

Before I think about this, which is it?
insert into master (col1) values (1), (2);
or
insert into master (col1) values (1), (2), (3);
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: incredible performance difference

[Elim Qiu]

Cool! Thanks a lot Shawn.

> No, but you can. Modify your scripts so that the word EXPLAIN is the first
> thing in each one  then re-execute them. This will product the optimizer's
> execution plan for each query. The results of all of those EXPLAIN
> SELECT statements will give us the most information to work from.
>
> Thanks.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "YL" <[EMAIL PROTECTED]> wrote on 10/19/2004 02:58:35 PM:
>
> > The following are the real tests but not the real logic i'll apply:-)
> >
> > i have 4 very simple script files below and like to show you the
> > performance differece
> >
> > tst0.sql:
> >
> >  select t1.participation_id id, t1.owner_id from participation t1 where
> >  (t1.participation_id in (24,469)) and
> >  (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *314346');
> >
> > tst1.sql:
> >
> > select t2.participation_id from participation t2 where
> > t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *222224';
> >
> > tst2.sql:
> >
> >  select t1.participation_id id, t1.owner_id from participation t1 where
> >  (t1.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *222224')
> >  and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb
> > =[^;]*314346');
> >
> > and finally
> >
> > tst.sql
> >
> >  select t1.participation_id id, t1.owner_id from participation t1 where
> >  (t1.participation_id in (select t2.participation_id from
> > participation t2 where
> >  t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *222224'))
> >  and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb
> > =[^;]*314346');
> >
> > Now the performance comparison:
> >
> > mysql> source tst0.sql
> > +-+--+
> > | id  | owner_id |
> > +-+--+
> > |  24 |1 |
> > | 469 |4 |
> > +-+--+
> > 2 rows in set (0.02 sec)
> >
> > mysql> source tst1.sql
> > +--+
> > | participation_id |
> > +--+
> > |   24 |
> > |  469 |
> > +--+
> > 2 rows in set (0.02 sec)
> >
> > mysql> source tst2.sql
> > +-+--+
> > | id  | owner_id |
> > +-+--+
> > |  24 |1 |
> > | 469 |4 |
> > +-+--+
> > 2 rows in set (0.03 sec)
> >
> > mysql> source tst.sql
> > +-+--+
> > | id  | owner_id |
> > +-+--+
> > |  24 |1 |
> > | 469 |4 |
> > +-+--+
> > 2 rows in set (30.45 sec)
> >
> > Basically this seems to me that the sql composite tst.sql is
> > terribly slow than the time needed for separate executions of
> > tst1.sql and tst0.sql. And best of all is tst2.sql.
> >
> > Can someone explain my results?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: From .txt to MySQL

[Amer Neely]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
| Hi..
| I have 30 text file that contains of sort of data about
| wheather...Each text file contains the information about weather
| in one day. So, i have 30 text file, which contains all the info
| about weather in 30 days(1 month). All the attributes in all the
| text files are same (date, date, rain scalar, celcius), but the
| the data / value in there are absolutely different. So, how i
| can dump all this data into mysql?...I have create a table named
| weather in Mysql, and the attributes in this table are same with
| my text files...
| My question is, how can I dump all the data from text file into
| mySQL without specifies the names of the text file..It's mean
| that, we only specifies the extention of text file (*.txt) then
| by that way i can dump all into mysql?...
You could also append all your text files into 1 large one, assuming they all have the 
same kind of
data in their respective columns. Make sure your field names in your table match up 
with the number
and type of data you are going to import, and are in the same order.
Get the text into some format recognized by MySQL (CSV for example).
Then you can use the 'load data local' MySQL construct to populate your weather table.
- --
/* All outgoing email scanned by AVG Antivirus */
Amer Neely, Softouch Information Services
Home of Spam Catcher & North Bay Information Technology Networking Group
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | PHP | MySQL | CGI programming for all data entry forms.
"We make web sites work!"
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.6 (MingW32)
Comment: For info see http://www.gnupg.org
iEYEARECAAYFAkF1dUcACgkQ3RxspxLYVsVs4QCgoK8lLTFFQ0slS0ES8OBI/AyB
rb4AniUGorNminphvenJ58ZntzcnA+Q+
=wHIK
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: incredible performance difference

[SGreen]

No, but you can. Modify your scripts so that the word EXPLAIN is the first 
thing in each one  then re-execute them. This will product the optimizer's 
execution plan for each query. The results of all of those EXPLAIN 
SELECT statements will give us the most information to work from.

Thanks.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"YL" <[EMAIL PROTECTED]> wrote on 10/19/2004 02:58:35 PM:

> The following are the real tests but not the real logic i'll apply:-)
> 
> i have 4 very simple script files below and like to show you the 
> performance differece
> 
> tst0.sql:
> 
>  select t1.participation_id id, t1.owner_id from participation t1 where
>  (t1.participation_id in (24,469)) and 
>  (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb =[^;]
> *314346');
> 
> tst1.sql:
> 
> select t2.participation_id from participation t2 where 
> t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> *222224';
> 
> tst2.sql:
> 
>  select t1.participation_id id, t1.owner_id from participation t1 where
>  (t1.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> *222224')
>  and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb 
> =[^;]*314346');
> 
> and finally
> 
> tst.sql
> 
>  select t1.participation_id id, t1.owner_id from participation t1 where
>  (t1.participation_id in (select t2.participation_id from 
> participation t2 where
>  t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> *222224'))
>  and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb 
> =[^;]*314346');
> 
> Now the performance comparison:
> 
> mysql> source tst0.sql
> +-+--+
> | id  | owner_id |
> +-+--+
> |  24 |1 |
> | 469 |4 |
> +-+--+
> 2 rows in set (0.02 sec)
> 
> mysql> source tst1.sql
> +--+
> | participation_id |
> +--+
> |   24 |
> |  469 |
> +--+
> 2 rows in set (0.02 sec)
> 
> mysql> source tst2.sql
> +-+--+
> | id  | owner_id |
> +-+--+
> |  24 |1 |
> | 469 |4 |
> +-+--+
> 2 rows in set (0.03 sec)
> 
> mysql> source tst.sql
> +-+--+
> | id  | owner_id |
> +-+--+
> |  24 |1 |
> | 469 |4 |
> +-+--+
> 2 rows in set (30.45 sec)
> 
> Basically this seems to me that the sql composite tst.sql is 
> terribly slow than the time needed for separate executions of
> tst1.sql and tst0.sql. And best of all is tst2.sql.
> 
> Can someone explain my results?

Configure error

[Marek Gimza]

Description:

The linker does not seem to be correctly set !!!  I am not sure what these 
error messages are really saying 

ld: fatal: library -ldir: not found
ld: fatal: File processing errors. No output written to conftest
collect2: ld returned 1 exit status
configure: failed program was:
#line 3958 "configure"
#include "confdefs.h"
/* Override any gcc2 internal prototype to avoid an error.  */
/* We use char because int might match the return type of a gcc2
builtin and then its argument prototype would still apply.  */
char opendir();

int main() {
opendir()
; return 0; }

-
ld: fatal: Symbol referencing errors. No output written to conftest
collect2: ld returned 1 exit status
configure: failed program was:
#line 4330 "configure"
#include "confdefs.h"
/* System header to define __stub macros and hopefully few prototypes,
which can conflict with char gethostbyname_r(); below.  */
#include 
/* Override any gcc2 internal prototype to avoid an error.  */
/* We use char because int might match the return type of a gcc2
builtin and then its argument prototype would still apply.  */
char gethostbyname_r();

int main() {

/* The GNU C library defines this for functions which it implements
to always fail with ENOSYS.  Some functions are actually named
something starting with __ and the normal name is an alias.  */
#if defined (__stub_gethostbyname_r) || defined (__stub___gethostbyname_r)
choke me
#else
gethostbyname_r();
#endif

; return 0; }
-

...etc

 
How-To-Repeat:
#!/bin/sh

CFLAGS="-O3"
CXX=gcc 
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti"
export CFLAGS CXX CXXFLAGS 
./configure --prefix=/usr/mysql --enable-assembler 
--with-mysqld-ldflags=-all-static



Fix:
YOU TELL ME ... I DO NOT KNOW !! 


>Submitter-Id:  
>Originator: 
>Organization:
 
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:  
>Severity:  <[ non-critical | serious | critical ] (one line)>
>Priority:  <[ low | medium | high ] (one line)>
>Category:  mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one 
line)>
>Release:   mysql-3.23.39 (Source distribution)

>Environment:

System: SunOS holy 5.7 Generic_106541-16 sun4u sparc 
SUNW,UltraSPARC-IIi-cEngine
Architecture: sun4

Some paths:  /usr/local/bin/perl /home/sw/buildadm/bin/make 
/usr/local/bin/gmake /home/sw/buildadm/bin/gcc /usr/ucb/cc
GCC: Reading specs from 
/usr/local/lib/gcc-lib/sparc-sun-solaris2.7/3.2.2/specs
Configured with: ../configure --disable-nls --with-ld=/usr/ccs/bin/ld 
--with-as=/usr/ccs/bin/as
Thread model: posix
gcc version 3.2.2
Compilation info: CC='gcc'  CFLAGS=''  CXX='gcc'  CXXFLAGS=''  LDFLAGS=''
LIBC: 
-rw-r--r--   1 bin  bin  1707752 May 29  2001 /lib/libc.a
lrwxrwxrwx   1 root root  11 Jan  8  2001 /lib/libc.so -> 
./libc.so.1
-rwxr-xr-x   1 bin  bin  1125056 May 29  2001 /lib/libc.so.1
-rw-r--r--   1 bin  bin  1707752 May 29  2001 /usr/lib/libc.a
lrwxrwxrwx   1 root root  11 Jan  8  2001 /usr/lib/libc.so -> 
./libc.so.1
-rwxr-xr-x   1 bin  bin  1125056 May 29  2001 /usr/lib/libc.so.1
Configure command: ./configure --with-unix-socket-path=/var/tmp/mysql.sock 
--with-low-memory --with-mit-threads=yes --without-perl 
--enable-thread-safe-client --with-berkeley-db --with-innodb
Perl: This is perl, version 5.005_03 built for sun4-solaris



Kind Regards,
Marek Gimza 

Arris International
3871 Lakefield Drive,
Suwanee, GA, 30024
(770-622-8541,  mobile: 678-662-2844)

incredible performance difference

[YL]

The following are the real tests but not the real logic i'll apply:-)

i have 4 very simple script files below and like to show you the performance differece

tst0.sql:

 select t1.participation_id id, t1.owner_id from participation t1 where
 (t1.participation_id in (24,469)) and 
 (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb =[^;]*314346');

tst1.sql:

select t2.participation_id from participation t2 where 
t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]*222224';

tst2.sql:

 select t1.participation_id id, t1.owner_id from participation t1 where
 (t1.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]*222224')
 and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb =[^;]*314346');

and finally

tst.sql

 select t1.participation_id id, t1.owner_id from participation t1 where
 (t1.participation_id in (select t2.participation_id from participation t2 where
 t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]*222224'))
 and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb =[^;]*314346');

Now the performance comparison:

mysql> source tst0.sql
+-+--+
| id  | owner_id |
+-+--+
|  24 |1 |
| 469 |4 |
+-+--+
2 rows in set (0.02 sec)

mysql> source tst1.sql
+--+
| participation_id |
+--+
|   24 |
|  469 |
+--+
2 rows in set (0.02 sec)

mysql> source tst2.sql
+-+--+
| id  | owner_id |
+-+--+
|  24 |1 |
| 469 |4 |
+-+--+
2 rows in set (0.03 sec)

mysql> source tst.sql
+-+--+
| id  | owner_id |
+-+--+
|  24 |1 |
| 469 |4 |
+-+--+
2 rows in set (30.45 sec)

Basically this seems to me that the sql composite tst.sql is terribly slow than the 
time needed for separate executions of
tst1.sql and tst0.sql. And best of all is tst2.sql.

Can someone explain my results?

Strange results from a query

[David Griffiths]

One of our developers came to me yesterday with strange results from a 
query. I've created a simple version of the example. I've pasted the 
table definitions at the bottom if someone really needs to see them. 
This is on mysql 4.0.18.

insert into master (col1) values (1), (2);
insert into sub (col1, a, b, c) values
(1, 'a', null, '2'),
(1, 'a', null, '2'),
(2, null, 'b', '3'),
(2, null, 'b', '3');
mysql> select m.col1,
   -> sum(s1.c) as 'A-count',
   -> sum(s2.c) as 'B-count'
   -> FROM master m
   -> left join sub s1 on (m.col1 = s1.col1 AND s1.a is not null)
   -> left join sub s2 on (m.col1 = s2.col1 and s2.b is not null)
   -> group by m.col1;
+--+-+-+
| col1 | A-count | B-count |
+--+-+-+
|1 |   8 |NULL |
|2 |NULL |  12 |
+--+-+-+
2 rows in set (0.00 sec)
In case it's not obvious, the count for the column marked "A" should be 
4, not 8. And for "B", it should be 6, not 12. The database seems to be 
iterating through the table twice.

If one of the outer-joins is removed, the results are correct. I would 
hazard a guess that if a third column existed in master/sub, and a third 
left-join was added, "A" would go to 12, and "B" would go to 16. Each 
outer join seems to spawn a new iteration through the data.

My question is "why", and what would be the strategy to avoid this?
Here are the table defs:
create table master (col1 int not null);
create table sub (col1 int not null, a char(1) null, b char(1) null, c 
smallint);

insert into master (col1) values (1), (2), (3);
Thanks, David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup

[David Griffiths]

No worries about the late reply.
We took down the master, took a hot backup from the slave (I still need 
to convert that 30-day license into a permanent one), moved it to the 
master, started the master, and then took a hot backup and 
re-initialized the slave. Took all of a few hours, and things are good.

We did have some weird crashing issues with this machine while using an 
LSI RAID card (RAID 5) - ie creating an index killed mysql. We switched 
to a 3ware SATA card (almost as fast in RAID 0+1, and much cheaper even 
with wasting more disk space for mirroring) and the problems disappeared.

Unfort, this corruption occurred about 4 months into setting up 
MySQL/Innodb - I hope we don't have to go through this every few months. 
Taking an additional backup from the slave should give us extra redundancy.

Corruption and weird crashes could be the result of specific 
drivers/hardware and/or specific versions of Linux.

Do you have any suggestions for tracking these issues, so that any 
platform/distro issues can be avoided (and hopefully addressed by OEMs 
and developers)??

David

Heikki Tuuri wrote:
David,
I am sorry for a late reply.
The corruption clearly is in the ibdata file of the production database.
InnoDB Hot Backup checks the page checksums when it copies the ibdata files.
Since CHECK TABLE fails, the corruption probably is in that table. You can
try to repair the corruption by dump + DROP + reimport of that table.
innodb_force_recovery cannot fix any kind of corruption.
 

InnoDB: Page lsn 3 1070601164, low 4 bytes of lsn at page end 1070609127
   

The corruption has almost certainly happened in the OS or the hardware,
because InnoDB checks page checksums before writing them to the ibdata
files. Since the lsn stored at the page start differs from what is stamped
at the page end, there is corruption at either end of the page. We have
received quite a few reports of strange crashes in Opteron/Linux boxes. That
suggests there are still OS bugs or hardware flaws in that platform.
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
Order MySQL support from http://www.mysql.com/support/index.html
..
From: David Griffiths ([EMAIL PROTECTED])
Subject: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2004-09-30 12:23:37 PST
I went to do some work on our database last night (dropping large
indexes, which can be time consuming). I checked to ensure that the
backup of that evening had run, but noticed that the size of the backup
was too small compared to previous days (I'm kicking myself for not
emailing the results of the backup to myself every night - I just have a
job that verifies that the backup actually ran).
So I ran the backup by hand. We have 8 data files, the first 7 being 4
gig in size, and the last being a 10-meg autoextend. This is MySQL
4.0.20 64bit, running on a dual Opteron machine running SuSE 8
Enterprise (64-bit). We are using ibbackup 2.0 beta (which is 64-bit for
the Opteron).
ibbackup (the Innodb backup utility) complains on the first file.
ibbackup: Re-reading page at offset 0 3272818688 in
/usr/local/mysql/var/ywdata1
this repeats a few hundred times
Then it dumps some ascii:
040930 11:44:14  InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex 55c3ee4d00030c4d00030c4c000374.
And at the bottom,
040930 11:44:14  InnoDB: Page checksum 1522485550, prior-to-4.0.14-form
checksum 1015768137
InnoDB: stored checksum 1438903885, prior-to-4.0.14-form stored checksum
4028531590
InnoDB: Page lsn 3 1070601164, low 4 bytes of lsn at page end 1070609127
InnoDB: Page number (if stored to page already) 199757,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 680
ibbackup: Error: page at offset 0 3272818688 in
/usr/local/mysql/var/ywdata1 seems corrupt!
While we no longer seem to have a backup, we do have a slave (not sure
if the corruption propigated to the slave; I know it can happen in Oracle).
I have a few questions:
1) Is InnoDB backup correct? This might be a false positive (doubt it
though).
2) What are the risks of stopping and starting the database? There is a
force-recovery option in inndb, which might fix the corruption. Note
that I answered this myself. I ran a "check table" on one of our larger
tables (600,000 rows) which killed the database. It came back up fine. I
re-ran the backup - same issue, with the same page checksums, etc.
3) Anyone have any experience with this? Keep in mind that this might be
an Opteron/MySQL-64bit issue. Or it might be a general issue in MySQL.
Thanks,
David
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://

Char to Varchar on Innodb

[Gary Richardson]

Hey,

>From http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html:


If any column in a table has a variable length, the entire row becomes
variable-length as a result. Therefore, if a table contains any
variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns
longer than three characters are changed to VARCHAR columns. This
doesn't affect how you use the columns in any way; in MySQL, VARCHAR
is just a different way to store characters. MySQL performs this
conversion because it saves space and makes table operations faster.
See section 15 MySQL Storage Engines and Table Types.


Does this affect all table types? I'm curious if this is happening on
my InnoDB tables as well. No problems, just curiosity..

Thanks.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Text field how to handle aliases

[Eldo Skaria]

Hi,

For this only RDBMS came into being.
You keep your master data in a table with relevent descriptions,
codify the item. Use the code in other location. In qa web page always
search against/show the full desc of what others require. Add a
category like school/university/pre-matric etc. this could simplify
the visitor to make logical and correct naming methods.
In a nutshell when accepting info from  users, show them the full
desc/ask them write the full desc, add a category. I hope this could
solve ur problem.


On Tue, 19 Oct 2004 12:38:49 -0500, Lewick, Taylor <[EMAIL PROTECTED]> wrote:
> I need help on the best way to handle a field that could have many
> different ways of naming something.
> 
> For instance, school name
> Let's take Saint Joseph's University
> 
> This could be Saint Joseph's Univeristy, U. of St. Joe, SJU, Univ. St.
> Joe, etc...
> 
> In this case, I don't think I can always get what I want by doing a
> select from table where name like "something".  Is there a
> practical/better way to handle this kind of thing.  This will come up a
> lot.  I'm thinking right now I will have to maintain my own mapping file
> for each school...
> 
> Something like
> Kansas, KU, Kansas University, University of Kansas, etc...
> 
> Any ideas?
> 
> Thanks,
> Taylor
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Thanks & Regards,
Eldo Skaria

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Text field how to handle aliases

[SGreen]

Don't think "file" think "table"! ;-)

CREATE TABLE universitysynonyms (
ID int auto_increment primary key,
synonym varchar(40) not null, 
university_id int not null,
UNIQUE(Synonym, university_id)
)

Each time you run into something you don't have in your "university" 
table, add it to this table and relate it back to the university it 
belongs to. After a while you will have all of the alternate spellings 
listed here. Problem is with some synonyms like "OSU": That's either Ohio 
State University, Oklahoma State University, or Oregon State University. 
You will have 3 synonym records for "OSU" each one having a different 
university_id. I guess in that case, the user has to pick which one to 
use...

The database can't do it all but it can make it easier to keep up with it.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine





"Lewick, Taylor" <[EMAIL PROTECTED]> wrote on 10/19/2004 01:38:49 PM:

> I need help on the best way to handle a field that could have many
> different ways of naming something.
> 
> For instance, school name
> Let's take Saint Joseph's University 
> 
> This could be Saint Joseph's Univeristy, U. of St. Joe, SJU, Univ. St.
> Joe, etc...
> 
> In this case, I don't think I can always get what I want by doing a 
> select from table where name like "something".  Is there a
> practical/better way to handle this kind of thing.  This will come up a
> lot.  I'm thinking right now I will have to maintain my own mapping file
> for each school...
> 
> Something like
> Kansas, KU, Kansas University, University of Kansas, etc...
> 
> Any ideas?
> 
> Thanks,
> Taylor
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Text field how to handle aliases

[Lewick, Taylor]

I need help on the best way to handle a field that could have many
different ways of naming something.

For instance, school name
Let's take Saint Joseph's University 

This could be Saint Joseph's Univeristy, U. of St. Joe, SJU, Univ. St.
Joe, etc...

In this case, I don't think I can always get what I want by doing a 
select from table where name like "something".  Is there a
practical/better way to handle this kind of thing.  This will come up a
lot.  I'm thinking right now I will have to maintain my own mapping file
for each school...

Something like
Kansas, KU, Kansas University, University of Kansas, etc...

Any ideas?

Thanks,
Taylor

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Chinese

[Heikki Tuuri]

Elim,

- Alkuperäinen viesti - 
Lähettäjä: "Elim Qiu" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Lähetetty: Tuesday, October 19, 2004 8:07 PM
Aihe: Re: Chinese


> > you can use MySQL-4.1.6 and the UTF-8 character >set for both Chinese
and
> European languages.
>
> What's the default character set for MySQL 5.0.1?

I have not heard that the default character set in a plain MySQL server
would change from latin1_swedish_ci.

You can put to my.cnf, in the [mysqld] section:

default-character-set=utf8

to make UTF-8 the default character set in your server.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Chinese

[Elim Qiu]

> you can use MySQL-4.1.6 and the UTF-8 character >set for both Chinese and
European languages.

What's the default character set for MySQL 5.0.1?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: what is wrong woth this statement?

[SGreen]

First, assume you want to insert records, then only insert the records you 
want to add to the destination table.

INSERT destinationtablename ()
SELECT 
FROM sourcetablename
WHERE 

Basically if you can build a query to return the records you want to 
INSERT, you can stick an INSERT clause to the front of it to make those 
records end up in some table.

http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Josh Howe" <[EMAIL PROTECTED]> wrote on 10/19/2004 12:45:30 PM:

> if (select count(*) from z_mail_systems > 0) then [insert statement]
> endif;
> 
> 
> 
> How do I do this kind of conditional insert? Thanks. 
> 

what is wrong woth this statement?

[Josh Howe]

if (select count(*) from z_mail_systems > 0) then [insert statement]
endif;

 

How do I do this kind of conditional insert? Thanks. 

Re: many fields or many tables? (Understanding DB design)

[SGreen]

Timothy Luoma <[EMAIL PROTECTED]> wrote on 10/19/2004 11:11:12 AM:

> 
> On Oct 19, 2004, at 10:17 AM, [EMAIL PROTECTED] wrote:
> 
> > You have already gone a long way to describing your table structure by 

> > describing your data elements and their relationships to each other. 
> >  Let me try to summarize you descriptions and see if I can show you 
> > how to translate your text descriptions into table descriptions.
> 
> Ok, I'm going to just go through and make sure I'm following your 
> translation (I've never been good at foreign languages, and SQL is 
> apparently no different ;-)
> 
> > 1. There are things called "projects".
> 
> Yes... (FPP and WW) although of course all of the projects fall under 
> one meta-project (TiM), but I guess that's taken care of by the fact 
> that this DB will contain only information about that one meta-project.
> 
> > 2. Some projects have "sub-projects." (I will assume that there is at 
> > most 1 parent project per sub-project)
> 
> Yes. (I am thinking here of FPP1 and FPP2 and FPP3).  So far WW has 
> only one "sub-project" (WW1) but we expect there will be more 
> eventually.


But in reality, the only difference in a project and a sub-project is that 
there is a "parent" to a sub-project. That's why I didn't differentiate 
between them later on.

> 
> > 3. Some projects contain groups.
> 
> I would have said (to use your wording) some "sub-projects" contain 
> "groups"... otherwise I think I'm confused what the difference is 
> between a sub-project and a group.
> 
> I'm thinking of it this way:
> 
> fpp --> fpp1--> fpp11
> --> fpp12
> --> fpp13
> --> fpp14
>--> fpp2   --> fpp21
> --> fpp22
> --> fpp23
> 
> are you saying that I ought to be thinking of it this way
> 
> fpp --> fpp1
>--> fpp11
>--> fpp12
>--> fpp13
>--> fpp14
>--> fpp2
>--> fpp21
>--> fpp22
>--> fpp23
> 

No, you had it right the first time. Remember, the only difference between 
a project and a sub-project is whether or not it has a parent project. It 
has nothing to do with how it's stored in the database.

> 
> > 4. All projects contain people.
> 
> Yes.
> 
> > 5. Some people assigned to projects also belong to one or more groups.
> 
> Here's where it starts to get fuzzy.
> 
> You have to be in one of FPP1 or FPP2 or FPP3 or WW1 (we'll call it WW1 
> even though there isn't a WW2 yet).
> 
> You can be in FPP1 (or FPP2 or FPP3) and WW1.
> 
> If you are in FPP11 you can't be in FPP12 or FPP13 or FPP14 or FPP2 or 
> FPP21 or FPP22 or FPP23.  Those final groups are exclusive and required 
> (if you are in FPP1 then you must be in one and only one of FPP11 or 
> FPP12 or FPP13 or FPP14).

This type of mutual exclusion is currently beyond the realm of MySQL DDL. 
You will have to enforce that business rule in your application code, not 
with the database design.

> 
> I've been thinking about it like students at a university (DB) which 
> has several colleges (projects), and each college has graduates of a 
> particular "class" (sub-project).

I agree but I see it more as a school(university) containing other schools 
(colleges) with each school having zero or more classes (freshman, 
sophomore, etc). 

In this design the top-most school is the university, it has no parent 
schools. The next tier schools are colleges, each of them is the child to 
a university. 

A university-type school has no child "classes". Each college-type school 
as 4 "classes": freshman, sophomore, junior, senior.

You have one "master" project, FPP. There are "child" projects: FPP1 and 
FPP2. Both FPP1 and FPP2 are children to FPP.  FPP1 is the parent to the 
following groups: FPP11,FPP12,FPP13,FPP14. FPP2 is the parent to the 
following groups: FPP21,FPP22,FPP23

However, a "project" is a "project" and a "group" is still a "group". The 
only difference between those and sub-projects and sub-groups are whether 
or not they get "parent"s.

> 
> The university has no current students or alumni who aren't from a 
> particular college and a particular year.
> 
> Some classes (sub-projects) are broken down into further groups...
> 
> > We need to look at #4 before we get to #3. Because a project must have 

> > people, that implies that there is a "person" thing in your system. 
> > Create a table to store information about a "person"
> >
> > CREATE TABLE person (
> > id int auto_increment primary key,
> > First Name varchar(20) not null,
> > Last Name varchar(20) not null,
> > ... more person-related fields and indexes ...
> > )
> 
> Now should that table have all the information about persons (name, 
> address, email.) ?
>

This is the "tip" of the "person" iceberg. You can create as complex a 
structure as you need in order to completely document a "person" in your 
system. Analyze your need for "person" information by describing them just 
as you described your project planning needs. However, all 

Re: Ask for help on a mysql problem

[Martijn Tonies]

> > That is ONE way to store a tree structure :-)
>
> > Another would be:
>
> > ITEMS
> > (ItemID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
> > other stuff)
>
> > ITEM_PARENT
> > (ItemID int,
> >  ParentID int
> > primary key (ItemID, ParentID)
> > )
>
>
> > I prefer the latter.
>
> The latter is not a tree, but a directed graph.

A rooted tree is a special kind of directed graph.

Besides, I wonder if you can fully store a graph in these tables
(with the PK and all)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ask for help on a mysql problem

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
"Martijn Tonies" <[EMAIL PROTECTED]> writes:

> That is ONE way to store a tree structure :-)

> Another would be:

> ITEMS
> (ItemID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
> other stuff)

> ITEM_PARENT
> (ItemID int,
>  ParentID int
> primary key (ItemID, ParentID)
> )


> I prefer the latter.

The latter is not a tree, but a directed graph.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB crash issue

[Heikki Tuuri]

Ian,

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 120,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
504 OS file reads, 167984 OS file writes, 116386 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-

hmm... InnoDB has posted 120 pages to be written to the ibdata files. That
is because it wants to get replaceable blocks in the buffer pool, InnoDB
uses mostly the LRU replacement policy.

The question is why the write thread has not waken up to process these
writes.

I added the following patch to 4.0.22 to trace this problem:

D 1.76 04/09/11 09:37:03+03:00 [EMAIL PROTECTED] 95 94 16/1/3132
P innobase/os/os0file.c
C Add more precise diagnostics about the state of the I/O threads of InnoDB;
pri
nt in SHOW INNODB STATUS if the event wait semaphore of each I/O thread is
set


It will print whether the 'event' is set to wake up the thread.

for (i = 0; i < srv_n_file_io_threads; i++) {
fprintf(file, "I/O thread %lu state: %s (%s)", i,
srv_io_thread_op_info[i],
srv_io_thread_function[i]);

#ifndef __WIN__
if (os_aio_segment_wait_events[i]->is_set) {
fprintf(file, " ev set");
}
#endif

fprintf(file, "\n");
}

There are a few similar reports lately. It might even be that some recent
Linux kernel or glibc version has a bug in threads and condition variables.
Events use condition variables. But we will know more when this problem is
repeated with 4.0.22.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html

...
From: Ian Gulliver ([EMAIL PROTECTED])
Subject: InnoDB crash issue
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2004-09-29 07:21:24 PST

--54328a363d28c325cc36d4d54176940d
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="kORqDWCi7qDJ0mEj"


--kORqDWCi7qDJ0mEj
Content-Type: multipart/mixed; boundary="PNTmBPCT7hxwcZjr"
Content-Disposition: inline


--PNTmBPCT7hxwcZjr
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

This isn't a repeatable bug, but it is certainly a repeating one.  We
have issues on multiple machines running 4.0.20-Max-log with different
data sets (in highly similar table structures) with InnoDB hanging and
eventually crashing itself to get out of deadlock.  Log is attached.
There's only one InnoDB table in the database (rest are MyISAM).  Its
structure is:

CREATE TABLE session_data (
  sid varchar(32) NOT NULL default '',
  session_data mediumtext NOT NULL,
  http_host varchar(255) NOT NULL default '',
  user varchar(32) NOT NULL default '',
  stamp datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (sid),
  KEY http_host (http_host),
  KEY user (user)
) TYPE=3DInnoDB;

--=20
Ian Gulliver
Penguin Hosting
"Failure is not an option; it comes bundled with your Microsoft products."

--PNTmBPCT7hxwcZjr
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="downeast_innodb_crash.txt"
Content-Transfer-Encoding: quoted-printable

MySQL thread id 806227, query id 7467614 localhost downeast statistics
SELECT session_data FROM session_data WHERE sid=3D'f697dfe1ccb2fddf0892d144=
a86d58bf'
---TRANSACTION 0 0, not started, process no 29469, OS thread id 1418817088 =
waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 806226, query id 7467612 localhost downeast statistics
SELECT session_data FROM session_data WHERE sid=3D'9200380a42dfd85e035865a8=
45b61db2'
---TRANSACTION 0 0, not started, process no 29469, OS thread id 1422344512 =
waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 806217, query id 7467599 localhost downeast statistics
SELECT session_data FROM session_data WHERE sid=3D'4ee95161699670b944f62ff1=
9a646270'
---TRANSACTION 0 0, not started, process no 29469, OS thread id 1420576192 =
waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 806212, query id 7467587 localhost downeast statistics
SELECT session_data FROM session_data WHERE sid=3D'1c60932c3eb0ef237397a295=
c6fd7b5d'
---TRANSACTION 0 0, not started, process no 29469, OS thread

Re: many fields or many tables? (Understanding DB design)

[Timothy Luoma]

On Oct 19, 2004, at 10:17 AM, [EMAIL PROTECTED] wrote:
You have already gone a long way to describing your table structure by 
describing your data elements and their relationships to each other. 
 Let me try to summarize you descriptions and see if I can show you 
how to translate your text descriptions into table descriptions.
Ok, I'm going to just go through and make sure I'm following your 
translation (I've never been good at foreign languages, and SQL is 
apparently no different ;-)

1. There are things called "projects".
Yes... (FPP and WW) although of course all of the projects fall under 
one meta-project (TiM), but I guess that's taken care of by the fact 
that this DB will contain only information about that one meta-project.

2. Some projects have "sub-projects." (I will assume that there is at 
most 1 parent project per sub-project)
Yes. (I am thinking here of FPP1 and FPP2 and FPP3).  So far WW has 
only one "sub-project" (WW1) but we expect there will be more 
eventually.

3. Some projects contain groups.
I would have said (to use your wording) some "sub-projects" contain 
"groups"... otherwise I think I'm confused what the difference is 
between a sub-project and a group.

I'm thinking of it this way:
fpp --> fpp1 --> fpp11
--> fpp12
--> fpp13
--> fpp14
--> fpp2 --> fpp21
--> fpp22
--> fpp23
are you saying that I ought to be thinking of it this way
fpp --> fpp1
--> fpp11
--> fpp12
--> fpp13
--> fpp14
--> fpp2
--> fpp21
--> fpp22
--> fpp23

4. All projects contain people.
Yes.
5. Some people assigned to projects also belong to one or more groups.
Here's where it starts to get fuzzy.
You have to be in one of FPP1 or FPP2 or FPP3 or WW1 (we'll call it WW1 
even though there isn't a WW2 yet).

You can be in FPP1 (or FPP2 or FPP3) and WW1.
If you are in FPP11 you can't be in FPP12 or FPP13 or FPP14 or FPP2 or 
FPP21 or FPP22 or FPP23.  Those final groups are exclusive and required 
(if you are in FPP1 then you must be in one and only one of FPP11 or 
FPP12 or FPP13 or FPP14).

I've been thinking about it like students at a university (DB) which 
has several colleges (projects), and each college has graduates of a 
particular "class" (sub-project).

The university has no current students or alumni who aren't from a 
particular college and a particular year.

Some classes (sub-projects) are broken down into further groups...
We need to look at #4 before we get to #3. Because a project must have 
people, that implies that there is a "person" thing in your system. 
Create a table to store information about a "person"

CREATE TABLE person (
        id int auto_increment primary key,
        First Name varchar(20) not null,
        Last Name varchar(20) not null,
... more person-related fields and indexes ...
)
Now should that table have all the information about persons (name, 
address, email.) ?

That was what I was originally thinking needed to be spread out into 
separate tables.


Now, #4 also states that each project can have 0 or more people 
assigned to it. What it didn't say, but should have, was whether each 
person can be assigned to more than one project. I know that most 
people do work on more than one project at a time or will be assigned 
to a new project after the old one is over. You have a many-to-many 
relationship between your people and your projects
It is possible that someone could be in only one project (ever) or that 
they might be involved in more than one.  We want to leave the door 
open for the 2nd option, although it will probably be more rare that 
someone is in more than 1 (however, we already have some and will no 
doubt have others).


By declaring that the COMBINATION of the values person_id and 
project_id must be UNIQUE, you guarantee that nobody is assigned to 
the same project more than once.
Ah, so I would assume I could do the same for sub-sub-projects (FPP11, 
FPP12, etc)?


Looking at #3...I get the impression from the description that each 
group is specific to a single project and that each project can have 
zero or more groups (a project could just have people that aren't in 
any groups or no groups at all).
The projects (FPP and WW) will always have sub-projects.  People have 
to belong to at least one project (FPP) and only one sub-project of 
that project (FPP1 or FPP2 or FPP3).  Further division beyond that is 
possible *and* if possible it is mandatory that everyone is in one and 
only one.


You may have noticed that while we have related a "person assigned to 
a project" to a "project group" however there is nothing in our data 
definitions that will prevent you from assigning a person assigned to 
one project to a group assigned to a different project. That bit if 
business rule enforcement must come fro

Re: first day of week/month

[Eamon Daly]

You can use DATE_FORMAT to recreate the first of the
month:
mysql> select d, DATE_FORMAT(d, '%Y-%m-01') FROM date_val;
+++
| d  | DATE_FORMAT(d, '%Y-%m-01') |
+++
| 1864-02-28 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 |
| 1987-03-05 | 1987-03-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
| 2004-01-01 | 2004-01-01 |
+++
DATE_SUB and DATE_FORMAT will generate the first day of the
week, assuming Sunday starts your week:
mysql> select d, DATE_SUB(d, INTERVAL DATE_FORMAT(d, '%w') DAY) FROM 
date_val;
+++
| d  | DATE_SUB(d, INTERVAL DATE_FORMAT(d, '%w') DAY) |
+++
| 1864-02-28 | 1864-02-28 |
| 1900-01-15 | 1900-01-14 |
| 1987-03-05 | 1987-03-01 |
| 1999-12-31 | 1999-12-26 |
| 2000-06-04 | 2000-06-04 |
| 2004-01-01 | 2003-12-28 |
+++

If your week starts on Monday, you can simply use WEEKDAY:
mysql> select d, DATE_SUB(d, INTERVAL WEEKDAY(d) DAY) FROM date_val;
++--+
| d  | DATE_SUB(d, INTERVAL WEEKDAY(d) DAY) |
++--+
| 1864-02-28 | 1864-02-22   |
| 1900-01-15 | 1900-01-15   |
| 1987-03-05 | 1987-03-02   |
| 1999-12-31 | 1999-12-27   |
| 2000-06-04 | 2000-05-29   |
| 2004-01-01 | 2003-12-29   |
++--+
As a side note, Paul DuBois lists several useful date
calculations such as last day of the month on pages 265-267
of the MySQL Cookbook (O'Reilly). He uses a DATE_SUB routine
for generating the first of the month, so maybe his way is
faster.

Eamon Daly

- Original Message - 
From: "Chris Knipe" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 18, 2004 9:07 PM
Subject: first day of week/month


Hi,
I know this might be a little silly, but can anyone give me a example on 
how to get the date of the first day of a week and month?

--
Chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB backup + replication problem?

[Heikki Tuuri]

Hi!

Guilhem has now fixed this bug to 4.0.22.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html

..
From: Don MacAskill ([EMAIL PROTECTED])
Subject: InnoDB backup + replication problem?
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2004-09-29 09:58:02 PST

I've got an interesting (well, I think so anyway) problem with my
replication.

The slave chugs along just fine, then spits out:


Query caused different errors on master and slave. Error on master:
'Can't execute the query because you have a conflicting read lock'
(1223), Error on slave: 'no error' (0). Default database: 'mysql'.
Query: 'BEGIN'


I check the master binlog position, and discover this:


/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 35294588
#040929  2:25:51 server id 1  log_pos 35294588  Query   thread_id=7830089
exec_time=0 error_code=1223
use mysql;
SET TIMESTAMP=1096449951;
BEGIN;
# at 35294629
#040929  2:25:44 server id 1  log_pos 35282293  Query   thread_id=7830089
exec_time=0 error_code=0
SET TIMESTAMP=1096449944;
INSERT INTO ibbackup_binlog_marker VALUES (1);
# at 35294710
#040929  2:25:51 server id 1  log_pos 35294710  Query   thread_id=7830089
exec_time=0 error_code=1223
SET TIMESTAMP=1096449951;
COMMIT;


I didn't see this prior to 4.0.21 (I was on 4.0.20), but it may or may
not be related.

This has happened a few times now, and always around the time that I
finish an InnoDB backup.

Anyone else seen this?  Any ideas?

Thanks,

Don


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup

[Heikki Tuuri]

David,

I am sorry for a late reply.

The corruption clearly is in the ibdata file of the production database.
InnoDB Hot Backup checks the page checksums when it copies the ibdata files.

Since CHECK TABLE fails, the corruption probably is in that table. You can
try to repair the corruption by dump + DROP + reimport of that table.

innodb_force_recovery cannot fix any kind of corruption.

>InnoDB: Page lsn 3 1070601164, low 4 bytes of lsn at page end 1070609127

The corruption has almost certainly happened in the OS or the hardware,
because InnoDB checks page checksums before writing them to the ibdata
files. Since the lsn stored at the page start differs from what is stamped
at the page end, there is corruption at either end of the page. We have
received quite a few reports of strange crashes in Opteron/Linux boxes. That
suggests there are still OS bugs or hardware flaws in that platform.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html


..
From: David Griffiths ([EMAIL PROTECTED])
Subject: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2004-09-30 12:23:37 PST

I went to do some work on our database last night (dropping large
indexes, which can be time consuming). I checked to ensure that the
backup of that evening had run, but noticed that the size of the backup
was too small compared to previous days (I'm kicking myself for not
emailing the results of the backup to myself every night - I just have a
job that verifies that the backup actually ran).

So I ran the backup by hand. We have 8 data files, the first 7 being 4
gig in size, and the last being a 10-meg autoextend. This is MySQL
4.0.20 64bit, running on a dual Opteron machine running SuSE 8
Enterprise (64-bit). We are using ibbackup 2.0 beta (which is 64-bit for
the Opteron).

ibbackup (the Innodb backup utility) complains on the first file.

ibbackup: Re-reading page at offset 0 3272818688 in
/usr/local/mysql/var/ywdata1

this repeats a few hundred times

Then it dumps some ascii:

040930 11:44:14  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 55c3ee4d00030c4d00030c4c000374.

And at the bottom,

040930 11:44:14  InnoDB: Page checksum 1522485550, prior-to-4.0.14-form
checksum 1015768137
InnoDB: stored checksum 1438903885, prior-to-4.0.14-form stored checksum
4028531590
InnoDB: Page lsn 3 1070601164, low 4 bytes of lsn at page end 1070609127
InnoDB: Page number (if stored to page already) 199757,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 680
ibbackup: Error: page at offset 0 3272818688 in
/usr/local/mysql/var/ywdata1 seems corrupt!

While we no longer seem to have a backup, we do have a slave (not sure
if the corruption propigated to the slave; I know it can happen in Oracle).

I have a few questions:

1) Is InnoDB backup correct? This might be a false positive (doubt it
though).

2) What are the risks of stopping and starting the database? There is a
force-recovery option in inndb, which might fix the corruption. Note
that I answered this myself. I ran a "check table" on one of our larger
tables (600,000 rows) which killed the database. It came back up fine. I
re-ran the backup - same issue, with the same page checksums, etc.

3) Anyone have any experience with this? Keep in mind that this might be
an Opteron/MySQL-64bit issue. Or it might be a general issue in MySQL.

Thanks,
David


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: many fields or many tables? (Understanding DB design)

[SGreen]

You have already gone a long way to describing your table structure by 
describing your data elements and their relationships to each other.  Let 
me try to summarize you descriptions and see if I can show you how to 
translate your text descriptions into table descriptions.

1. There are things called "projects". 
2. Some projects have "sub-projects." (I will assume that there is at most 
1 parent project per sub-project)
3. Some projects contain groups.
4. All projects contain people.
5. Some people assigned to projects also belong to one or more groups.


Let's do the easy one now:

CREATE TABLE project (
id int auto_increment primary key,
Name varchar(20) not null,
...other project related fields and indexes...
)

There are three ways to define tables for sub-projects. One is 
self-referential. This supports only one parent per sub-project but can 
extend into many layers of sub-sub projects.

CREATE TABLE project (
id int auto_increment primary key,
parentproject_id int not null default(0),
Name varchar(20) not null,
...other project related fields and indexes...
)

One is explicitly parent-child. The disadvantage to this method is if you 
need to reference a project/sub-project you need to check or decide 
between two different values that reside on two different table. 

CREATE TABLE project (
id int auto_increment primary key,
Name varchar(20) not null,
...other project related fields and indexes...
)

CREATE TABLE subproject (
id int auto_increment primary key,
Name varchar(20) not null,
...other project related fields and indexes...
)

The third is semi-self-referential in that all of the projects and 
subprojects are listed in the same table but their relationship (parent to 
child) is maintained in a third table. This method supports sub-projects 
that can be children of multiple projects. The danger here is you can 
possibly create a circular reference (A is a parent of B. B is a parent of 
C. C is a parent of A)

CREATE TABLE project (
id int auto_increment primary key,
Name varchar(20) not null,
...other project related fields and indexes...
)
CREATE TABLE subprojects (
project_id int not null,
subproject_id int not null,
... declare keys and indexes here...
)

In the table subprojects, both columns get the ID values from two 
different project records.

We need to look at #4 before we get to #3. Because a project must have 
people, that implies that there is a "person" thing in your system. Create 
a table to store information about a "person"

CREATE TABLE person (
id int auto_increment primary key,
First Name varchar(20) not null,
Last Name varchar(20) not null,
... more person-related fields and indexes ...
)

Now, #4 also states that each project can have 0 or more people assigned 
to it. What it didn't say, but should have, was whether each person can be 
assigned to more than one project. I know that most people do work on more 
than one project at a time or will be assigned to a new project after the 
old one is over. You have a many-to-many relationship between your people 
and your projects

create table people_projects (
id int auto_increment primary key,
person_id int not null,
project_id int not null,
UNIQUE(person_id, project_id)
...other indexes as needed...
)

By declaring that the COMBINATION of the values person_id and project_id 
must be UNIQUE, you guarantee that nobody is assigned to the same project 
more than once.

Looking at #3...I get the impression from the description that each group 
is specific to a single project and that each project can have zero or 
more groups (a project could just have people that aren't in any groups or 
no groups at all).

CREATE TABLE projectgroup (
id int auto_increment primary key,
project_id int not null,
name varchar(20) not null,
...other fields and keys as necessary...
)

Number 5 is an interesting relationship. It is declaring a relationonship 
on a relationship. The "people assigned to a project"  objects are on the 
"people_projects" table, not the person table. We need to equate those 
people to one or more groups. Again we are in a many-to-many situation and 
model it this way:

CREATE TABLE peopleproject_projectgroups (
id int auto_increment primary key,
peopleproject_id int not null,
projectgroup_id int not null,
UNIQUE (peopleproject_id, group_id)
... other indexes...
)

You may have noticed that while we have related a "person assigned to a 
project" to a "project group" however there is nothing in our data 
definitions that will prevent you from assigning a person assigned to one 
project to a group assigned to a different project. That bit if business 
rule enforcement must come from your application. The database can do a 
lot but it won't do everything.

Does this help you get started?

Shawn Green
Dat

Re: innodb monitoring

[Heikki Tuuri]

Boyd,

sorry, only the output of innodb_monitor can be accessed through an SQL
statement. The other monitors contain information mostly for special error
situations.

But it is in the TODO to implement

SHOW LOCKS ...

That would be useful for application developers.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html

..
From: "Boyd E. Hemphill" ([EMAIL PROTECTED])
Subject: innodb monitoring
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2004-10-05 11:29:24 PST

I notice the following special tables innodb_monitor, =
innodb_lock_monitor,
innodb_tablespace_monitor, innodb_table_monitor, and innodb_validate.

The information from the first can be accessed from the MySQL client =
prompt
with show innodb status without creating the table and watch standard =
output
by issuing "show innodb status."=20

Is it possible to access the others in a similar way?

Thanks for your time!


Best Regards,
Boyd E. Hemphill
MySQL Certified Professional
[EMAIL PROTECTED]
Triand, Inc.
www.triand.com
O:  (512) 248-2278


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Weirdness (or bug) with DROP TABLE

[Heikki Tuuri]

Adolfo,

the following patch in the 4.0 tree may fix the misleading error message:


D 1.222 04/10/12 18:11:50+03:00 [EMAIL PROTECTED] 357 356 1/1/5101
P sql/ha_innodb.cc
C Change error code to HA_ERR_ROW_IS_REFERENCED if we cannot DROP a parent
table
 referenced by a FOREIGN KEY constraint; this error number is less
misleading th
an the previous value HA_ERR_CANNOT_ADD_FOREIGN, but misleading still; we
should
 introduce to 5.0 a proper MySQL error code

The patch will be merged into 4.1.7.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html


...
From: Adolfo Bello ([EMAIL PROTECTED])
Subject: Weirdness (or bug) with DROP TABLE
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2004-10-06 13:03:59 PST

I am using Mandrake 10, MySQL 4.1.5 from RPM downloaded from
dev.mysql.com.

Look at this session:



$ mysql -u root -p permarn
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.5-gamma-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from tblinstructivos;
Empty set (0.00 sec)

mysql> drop table tblinstructivos;
ERROR 1051 (42S02): Unknown table 'tblinstructivos'
mysql> select * from `tblinstructivos`;
Empty set (0.00 sec)

mysql> drop table `tblinstructivos`;
ERROR 1051 (42S02): Unknown table 'tblinstructivos'
mysql>

=

Why I can not drop this particular table? No problem with any other
table.

Every table in this database is InnoDB. I started mysql with the
"--user=root" flag.


Adolfo


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: how to use mysql client source command through DBI/DBD

[gerald_clark]

Sanjeev Sagar wrote:
Hello All,
I am trying to create a perl DBI/DBD script for creating a database initial build. My input is a extract file, which is a mysqldump result file with --opt and -B option. 

I am using DBIx::DWIW. I am able to open a successful database handler. I am having 
code like
my $dropsql="DROP DATABASE $ARGV[1]";
my $loadsql="source /tmp/extract-file-name";
Source is a command built into the mysql client program, not the server.
You will have to write a perl subroutine that accomplishes the same task.
You may use 'system' to run  the mysql client.
print "Running database load...\n";
$conn->Execute($dropsql);
RC=$?
die "Error running in $dropsql...\n" if ( RC != 0 );
$conn->Execute($loadsql);
RC=$?
die "Error in running $loadsql...\n" if (RC != 0);
It appears that $loadsql is not going through. It is able to drop database but not running 
source command at all. I have tried by using "\. extract-file-name", still no luck.
Looks to me that I am not doing it right way. Any help will be highly appreciated.
Regards,

 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Innodb foreign keys names

[Heikki Tuuri]

Martijn,

List:   mysql
Subject:Re: Innodb foreign keys names
From:   "Martijn Tonies" 
Date:   2004-10-19 9:53:28
Message-ID: <01ad01c4b5c1$7c1e69a0$0a02a8c0 () martijn>
[Download message RAW]

>Heikki,
>
>> you have to use a fairly recent 4.0 or 4.1 version of MySQL.
>
>Does a newer version allow you to change/set the names yourself?

you can give the name yourself in new versions. Please look at the InnoDB
manual section.

>With regards,
>
>Martijn Tonies
>Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
>Server.
>Upscene Productions
>http://www.upscene.com

Regards,

Heikki


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB deadlock problem

[Heikki Tuuri]

David,

next-key locks are purely inhibitive. Even though transaction (2) has an
X-lock on the 'supremum' of the index, it cannot insert because also
transaction (1) has an X-lock on the 'supremum'.

Why is it allowed that two transactions can both have an X-lock on a 'gap'
in the index (the supremum is a special case of a gap)? We have to allow it
because purge may remove records from the index, and two gaps can merge.

In this specific case, the cursor of transaction (1) has already passed the
place where transaction (2) is trying to insert. If we would allow (2) to do
the insert, then the cursor of (1) should be more intelligent than it is
now. It should look back at the inserted record, and check if it is in the
result set the cursor is trying to read.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html


...
From: David Edwards ([EMAIL PROTECTED])
Subject: Re: InnoDB deadlock problem
View: Complete Thread (3 articles)
Original Format
Newsgroups: mailing.database.myodbc
Date: 2004-10-07 04:11:35 PST

--0-1154760343-1097147469=:85242
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi Tobias,

Thanks for your reply. Unfortunately I couldn't see from the manual why I
was getting the deadlock - transaction 2 already has a lock on the index it
is waiting for. The difference seems to be 'insert intention' - I'm not sure
what different types of exclusive lock there are and how they relate to each
other. Is there any way I can get both types of lock in one go, in the first
statement I execute?

Thanks,
David

Tobias Asplund <[EMAIL PROTECTED]> wrote:
On Wed, 6 Oct 2004, David Edwards wrote:

> I've got a deadlock problem using InnoDB tables

(...)

> Transaction 1:
> START TRANSACTION;
> DELETE FROM results WHERE id_job = 25920;
> INSERT INTO results(result,id_job) VALUES (31.461937,25920);
> COMMIT;
>
> Transaction 2:
> START TRANSACTION;
> DELETE FROM results WHERE id_job = 25919;
> INSERT INTO results(result,id_job) VALUES
(25.388607,25919),(22.650234,25919);
> COMMIT;

I think this manual page might explain what's happening:

http://dev.mysql.com/doc/mysql/en/InnoDB_Next-key_locking.html


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ask for help on a mysql problem

[Martijn Tonies]

Hello,

> The only difference is that you have moved parent outside main table.
> No benefits at all. You have to create two records I two tables instead of
> one. You have to make joins to see what is the parent of particular child.
> I am strongly against this.

Why?

1) relational theory clearly states you should store what is TRUE
(this means: no NULLs). Obviously, people are used to NULLs,
but this doesn't make them right.

2) there's nothing wrong with joins

3) there's nothing wrong with multiple inserts

4) you avoid self-joins, which can be tedious to write.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


> > It's really clear what you want. Please specify.
> >
> > If what you basicaly want is just a tree structure, then it's done like
> that:
> >
> >
> > CREATE TABLE something (
> > id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
> > parent INT NOT NULL,
> > data1 CHAR(255),
> > data2 CHAR(255),
> > ...
> >
> > );
> >
> > Then you can specify the parent node id for each record in database.
> > This is how tree-like structures are stored in SQL. Hope that helps.
>
> That is ONE way to store a tree structure :-)
>
> Another would be:
>
> ITEMS
> (ItemID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
> other stuff)
>
> ITEM_PARENT
> (ItemID int,
>  ParentID int
> primary key (ItemID, ParentID)
> )
>
>
> I prefer the latter.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tables and performance

[Egor Egorov]

Yves Arsenault <[EMAIL PROTECTED]> wrote:

> Does a very large number of tables in a database affect MySQL's performance?

Strictly saying, yes. But the difference won't really matter. So read - no, it
won't affect performance. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: From .txt to MySQL

[Egor Egorov]

"roime puniran" <[EMAIL PROTECTED]> wrote:

> My question is, how can I dump all the data from text file into
> mySQL without specifies the names of the text file..It's mean
> that, we only specifies the extention of text file (*.txt) then
> by that way i can dump all into mysql?...

You can write a script to import all text files in php or MySQL.
See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html - this
may help.





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transactions dilemma

[Stuart Felenstein]

Egor, 

Thank you , I wasn't sure anyone would ever respond to
this post :)

What I wound up doing is , from the application level,
running an if / else.  The if checks to see if each
$query has succeeded. If any of them failed, I do a
rollback.  If they all have succeeded, I then do a
committ.

Now I'm not totally sure how a rollback would effect
the one Myisam query. I mean it wouldn't , since
rollback is alien to myisam, so I probably need to put
a line in there to (whatever a rollback is in myisam
language) as well.

Stuart
--- Egor Egorov <[EMAIL PROTECTED]> wrote:

> Stuart Felenstein <[EMAIL PROTECTED]> wrote:
> 
> > I have a slight dilemma.  I am using transactions
> to
> > insert data into multiple tables.  All but one
> table
> > is Innodb.  That one is Myisam and it's left as
> such
> > because its one text column, so I want the
> benefits of
> > full text search.
> > 
> > Still I need this transaction to somehow include
> this
> > entry. 
> > Two thoughts :
> > 1- I created a temp innodb table and then after
> > transaction move the data over to the myisam.
> > 
> > 2-Figure out what the text search options are in
> > innodb and maybe if there is a way to improve on
> them.
> > 
> > Any suggestions ?
> 
> Consider LOCK TABLES: 
> http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html
> 
> 
> 
> 
> 
> -- 
> For technical support contracts, goto
> https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net
> http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
>  / /|_/ / // /\ \/ /_/ / /__  
> [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
><___/   www.mysql.com
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fatal Error on db

[Egor Egorov]

John <[EMAIL PROTECTED]> wrote:


Not a MySQL error. it's a bug in the application.

> 
>  I am using php/mysql for a program and everything
> installed fine but when I try to open it up I get this
> error:
> 
> Fatal Error : Couldn't find local config file.
> File Name : /program/admin/index.php
> Error Code : err01
> Time :18 Oct 2004, 07:19:44 pm
> 
> PHP Fatal error:  Call to undefined function: 
> make_last_processes() in
> /home/main/public_html/app/program/admin/index.php on
> line 16
> 
> 
> Line 16 is : make_last_processes(false);
> 
> Help please. :)
> 
> 
>
> ___
> Do you Yahoo!?
> Express yourself with Y! Messenger! Free. Download now. 
> http://messenger.yahoo.com
> 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL TMPDIR.

[Egor Egorov]

You can be sure that MySQL won't loose any data in this case. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: just testing, sorry.

[Egor Egorov]

tibyke <[EMAIL PROTECTED]> wrote:

> pls delete it

Done, deleted from my computer. :-)) 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Blob question

[Egor Egorov]

Steve Grosz <[EMAIL PROTECTED]> wrote:

> I'm just getting into the whole MySql (was using access).  Is it better 
> to create a Blob type and insert a image into it, or to create a char 
> file type and have a directory structure to the specific file?

It's FAQ. One of the most FA Q. 

Store image in file and store file name in table. 

> How big of files to the different Blob's hold?

As much as you want, size limited by the int type. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Which Filesystem to choose?

[Egor Egorov]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:

> I'm trying to set up a new Opteron-Based MySQL-Server.
> The only thing I'm unsure about is which filesystem to choose..
> 
> ext3? ReiserFS? XFS?
> What's your experience?

ext3 is the safest. For both others I'd suggest you to have UPS for your server. 
XFS is sensitive to power outages. 

For InnoDB consider using raw partitions or raw disks as a storage. This will give you
more performance/reliability and this will let InnoDB have full control over
the storage space, bypassing the filesystem layer. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problems with mysqldump

[Egor Egorov]

See http://dev.mysql.com/doc/mysql/en/GRANT.html





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ask for help on a mysql problem

[Martijn Tonies]

Egor, others,

> It's really clear what you want. Please specify.
>
> If what you basicaly want is just a tree structure, then it's done like
that:
>
>
> CREATE TABLE something (
> id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
> parent INT NOT NULL,
> data1 CHAR(255),
> data2 CHAR(255),
> ...
>
> );
>
> Then you can specify the parent node id for each record in database.
> This is how tree-like structures are stored in SQL. Hope that helps.

That is ONE way to store a tree structure :-)

Another would be:

ITEMS
(ItemID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
other stuff)

ITEM_PARENT
(ItemID int,
 ParentID int
primary key (ItemID, ParentID)
)


I prefer the latter.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: strange user permissions error

[Egor Egorov]

Check tables in mysql database with myisamchk. This might me some kind of table 
corruptions.





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transactions dilemma

[Egor Egorov]

Stuart Felenstein <[EMAIL PROTECTED]> wrote:

> I have a slight dilemma.  I am using transactions to
> insert data into multiple tables.  All but one table
> is Innodb.  That one is Myisam and it's left as such
> because its one text column, so I want the benefits of
> full text search.
> 
> Still I need this transaction to somehow include this
> entry. 
> Two thoughts :
> 1- I created a temp innodb table and then after
> transaction move the data over to the myisam.
> 
> 2-Figure out what the text search options are in
> innodb and maybe if there is a way to improve on them.
> 
> Any suggestions ?

Consider LOCK TABLES: 
http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: hotcopy - segfault

[Egor Egorov]

James Green <[EMAIL PROTECTED]> wrote:

> Trying to run mysqlhotcopy on multiple Debian stable systems, am getting 
> segfaults on some of them. We're using mysql-4.0.20 from the binary 
> release off mysql.com. strace says:
> 
[]
> I'm no expert - can anyone identify the fault? Running as root user.

mysqlhotcopy is a perl script. Please check the perl version, the DBI
versions and also how is the DBD::MySQL linked with libmysqlclient. 

I suggest you to install MySQL-devel and MySQL-shared and then 
recompile and reinstall DBD::MySQL to get sure that it's linked with
properly-compiled MySQL libraries. 

Also, as a clue, take a look at the end of the dmesg output. If there's 
a kernel panic - then you have something wrong with your hardware or 
less possibly, with your kernel. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: create table....select * from table anomaly..

[Egor Egorov]

It's rather a different behaviour. We suggest to upgrade to 4.0.21 in this
case. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Unable to Start MySQL on FreeBSD4.10 box

[Egor Egorov]

"Lynette Tillner" <[EMAIL PROTECTED]> wrote:
> I'm setting up a development box with FreeBSD 4.10 and installed MySQL =
> 4.0.12 on it.  Everything in the install appeared to work smoothly.=20
> 
> However, when I go to start MySQL I get an error that says:=20
> 
> database list could not be retrieved
> 
> So, how do I fix this?  I've been unable to find any documentation that =
> sheds any light on what I need to change.=20

Doesn't look like a MySQL error message. Please read
http://dev.mysql.com/doc/mysql/en/Unix_post-installation.html and
http://dev.mysql.com/doc/mysql/en/FreeBSD.html





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ask for help on a mysql problem

[Egor Egorov]

Teng Wang <[EMAIL PROTECTED]> wrote:

It's really clear what you want. Please specify. 

If what you basicaly want is just a tree structure, then it's done like that: 


CREATE TABLE something ( 
id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
parent INT NOT NULL,
data1 CHAR(255),
data2 CHAR(255),
...

);

Then you can specify the parent node id for each record in database. 
This is how tree-like structures are stored in SQL. Hope that helps.

> I wanna setup a tree structure. Each node in this tree is a
> table. Each table has a "link" field. For each record, the
> data in this field is a pointer to another table or null.
> 
> I read mysql manual but don't find any clues that SQL
> supports such a "link" field. Does anyone has an idea about
> that?
> 
> Thanks a lot!
> 
> 
> 
>eruisi
>10/14/2004
>23:26:58
> 
> 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Modify type

[Egor Egorov]

Jerry Swanson <[EMAIL PROTECTED]> wrote:

> I have field date type of "datetime". I need to modify to "timestamp".
> If I alter the table and mofiy the field will this crash the data in
> the field.

MySQL server will crash? Please show us the error message. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Sequences and Synomyms

[lakshmi.narasimharao]

Hi,
I am new to mysql. I want to create sequences in mysql 4.0 which
should be equivalent to oracle sequences. I gone through the mysql
manual, Auto_Increment(), C API mysql_insert_id() and LAST_INSERT_ID()
are there for sequences. Last_insert_id() gives the last value.
I want actual migration to mysql for the following oracle statements

create sequence msdba.msuser_sequence maxvalue 1 cycle order;
msuser_sequence.currval
select msuser_sequence.nextval from dual;
grant all on msuser_sequence to msuser;

Could you please suggest how to do it.

And also I want to create synonym in mysql for the following oracle
statement

create synonym msuser.ms_sequence for msdba.msuser_sequence;

Please help me in this.

Thanks,
Narasimha





Confidentiality Notice

The information contained in this electronic message and any attachments to this 
message are intended
for the exclusive use of the addressee(s) and may contain confidential or privileged 
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Sequences and Synomyms

[Martijn Tonies]

Hello,

> I am new to mysql. I want to create sequences in mysql 4.0 which
> should be equivalent to oracle sequences. I gone through the mysql
> manual, Auto_Increment(), C API mysql_insert_id() and LAST_INSERT_ID()
> are there for sequences. Last_insert_id() gives the last value.
> I want actual migration to mysql for the following oracle statement
> create sequence msdba.ms_sequence maxvalue 1 cycle order;
>
> Could you please suggest how to do it.

MySQL doesn't support sequences. It supports auto-inc fields only.

> And also I want to create synonym in mysql for the following oracle
> statement
>
> create synonym msuser.ms_sequence for msdba.msuser_sequence;

Not supported in MySQL. When using MyISAM, you can probably
create a symbolic link of some sorts (in the file system, not MySQL
itself), but nothing like a real synonym.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Sequences and Synomyms

[lakshmi.narasimharao]

Hi,
I am new to mysql. I want to create sequences in mysql 4.0 which
should be equivalent to oracle sequences. I gone through the mysql
manual, Auto_Increment(), C API mysql_insert_id() and LAST_INSERT_ID()
are there for sequences. Last_insert_id() gives the last value.
I want actual migration to mysql for the following oracle statement
create sequence msdba.ms_sequence maxvalue 1 cycle order;

Could you please suggest how to do it.

And also I want to create synonym in mysql for the following oracle
statement

create synonym msuser.ms_sequence for msdba.msuser_sequence;

Please help me in this.

Thanks,
Narasimha





Confidentiality Notice

The information contained in this electronic message and any attachments to this 
message are intended
for the exclusive use of the addressee(s) and may contain confidential or privileged 
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Innodb foreign keys names

[Martijn Tonies]

Heikki,

> you have to use a fairly recent 4.0 or 4.1 version of MySQL.

Does a newer version allow you to change/set the names yourself?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


> Hi everybody,
>
> Still have a problem with naming "foreign key" constraints on innodb
tables.
> Can't retrieve any of the name that was given to the constraints.
>
> "Internally generated" IDs are always given to the foreign keys... no way
to
> apply a "drop foreign key 0_" on a replicated server or gererate
> automatic update scripts.
>
> This is from mySql documentation :
>
> ALTER TABLE yourtablename
> ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES
anothertablename(...)
>
> What the "symbol" value is use for ? if it is not handled : is this a bug
?
>
> Is there any planned release of InnoDB that could handle the constraint
> "foreign key" names ?
>
> thanks in advance
> regards
>
>
> --
> Richard FURIC
> CEDRICOM
> Tel : 02 99 55 07 55
> Fax : 02 99 55 08 64
> E-mail : [EMAIL PROTECTED]
> site vitrine : www.cedricom.com
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Preserving backslashes in DML

[Wolfram Kraus]

Tom Kirkman wrote:
What are the options available for inserting\updating a MySQL table
VARCHAR with a string containing backslash characters so that the
backslash characters are preserved as is?  For example, the UNC string
'\\MyServer\MyDir  ' would be changed on the
way in to the VARCHAR to become '\MyServerMyDir'.  What options are
there for specifying that this changing on the way in should NOT be
done?
Just mask every backslash with another backslash:
'MyServer\\MyDir'
http://dev.mysql.com/doc/mysql/en/String_syntax.html
HTH,
Wolfram
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: corruption after database restore

[Heikki Tuuri]

Baba,
- Original Message - 
From: "Baba Buehler" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, October 07, 2004 12:34 AM
Subject: corruption after database restore


I'm having a corruption problem after doing a backup and then a restore
with ibbackup (v1.40).
After restoring from the backup, when mysqld starts I get:
041006 07:46:53  mysqld started
InnoDB: Warning: an inconsistent page in the doublewrite buffer
InnoDB: space id 0 page number 5877102, 7'th page in dblwr buf.
InnoDB: Warning: an inconsistent page in the doublewrite buffer
The sum of data file sizes is 5767168 pages
How is it possible that the doublewrite buffer contains page number 5877102?
InnoDB: space id 0 page number 5877103, 8'th page in dblwr buf.
InnoDB: Warning: an inconsistent page in the doublewrite buffer
InnoDB: space id 0 page number 5877104, 9'th page in dblwr buf.
InnoDB: Warning: an inconsistent page in the doublewrite buffer
InnoDB: space id 0 page number 5877105, 10'th page in dblwr buf.
InnoDB: Warning: an inconsistent page in the doublewrite buffer
InnoDB: space id 0 page number 5877106, 11'th page in dblwr buf.
InnoDB: Error: tablespace size stored in header is 6029312 pages, but
InnoDB: the sum of data file sizes is 5767168 pages
Are you sure you did not forget some ibdata file from the my.cnf you are 
using? Looks like the tablespace data files are smaller than they should be.

Please show the my.cnf that you used in taking the backup, as well as in 
restoring the backup.

041006  7:46:54  InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-standard'  socket: '/tmp/mysql4.sock'  port: 3306

Then when queries start hitting the database, I start getting a lot of
errors like (full backtrace included below):
 InnoDB: Assertion failure in thread 36874 in file fil0fil.c line 1204
These assertions happen because the data files are too small.
I've restored from this backup multiple times with the same results, so
I'm presuming its the backup itself that is corrupt.  Does anyone have
any ideas on what might cause ibbackup to corrupt files, as the backup
appeared to complete successfully?
Please show the printout of the backup run, as well as the --restore run.
InnoDB: Error: trying to access page number 141623 in space 0
InnoDB: which is outside the tablespace bounds.
Look, it says that page number 142 000 is outside the tablespace data files, 
though above you had over 5 million pages in data files! You have probably 
forgotten some data files when you restarted mysqld.

Thanks,
baba
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php


InnoDB: Error: trying to access page number 141623 in space 0
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10
041006  7:49:38  InnoDB: Assertion failure in thread 36874 in file
fil0fil.c line 1204
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly 
built,
or misconfigured. This error can also be caused by malfunctioning 
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.

key_buffer_size=1048576
read_buffer_size=4190208
max_used_connections=1
max_connections=35
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 287603 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x87b2120
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfe7df28, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8071f44 handle_segfault + 420
0x82a0e38 pthread_sighandler + 184
0x81edc77 fil_io + 1287
0x81b416b buf_read_page_low + 203
0x81b45c9 buf_read_page + 41
0x81a6fb8 buf_page_get_gen + 888
0x8167771 btr_cur_open_at_rnd_pos + 897
0x8171a4e btr_estimate_number_of_different_key_vals + 670
0x8101c99 dict_update_statistics_low + 89
0x8101d04 dict_update_statistics + 20
0x80f78f8 dict_table_get_and_increment_handle_count + 552
0x80ccc5b open__11ha_innobasePCciUi + 203
0x80c6b54 ha_open__7handlerPCcii + 36
0x8094595 openfrm__FPCcT0UiUiUiP8st_table + 5317
0x8090d27 open_unireg_entry__FP3THDP8st_tablePCcN22 + 87
0x8090178 open_table__FP3THDPCcN21Pb + 888
0x809102b open_tables__FP3THDP13st_table_list + 75
0x8091308 open_and_lock_tables__FP3THDP13st_table_list + 24
0x807ccb3 mysql_execute_command__Fv + 947

From .txt to MySQL

[roime puniran]

Hi..
I have 30 text file that contains of sort of data about
wheather...Each text file contains the information about weather
in one day. So, i have 30 text file, which contains all the info
about weather in 30 days(1 month). All the attributes in all the
text files are same (date, date, rain scalar, celcius), but the
the data / value in there are absolutely different. So, how i
can dump all this data into mysql?...I have create a table named
weather in Mysql, and the attributes in this table are same with
my text files...
My question is, how can I dump all the data from text file into
mySQL without specifies the names of the text file..It's mean
that, we only specifies the extention of text file (*.txt) then
by that way i can dump all into mysql?...
-
Free POP3 Email from www.Gawab.com 
Sign up NOW and get your account @gawab.com!!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Innodb foreign keys names

[Heikki Tuuri]

Richard,
you have to use a fairly recent 4.0 or 4.1 version of MySQL.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/
.
From: "Richard - CEDRICOM" ([EMAIL PROTECTED])
Subject: Innodb foreign keys names
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2004-10-08 07:03:42 PST
Hi everybody,
Still have a problem with naming "foreign key" constraints on innodb tables.
Can't retrieve any of the name that was given to the constraints.
"Internally generated" IDs are always given to the foreign keys... no way to
apply a "drop foreign key 0_" on a replicated server or gererate
automatic update scripts.
This is from mySql documentation :
ALTER TABLE yourtablename
ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES anothertablename(...)
What the "symbol" value is use for ? if it is not handled : is this a bug ?
Is there any planned release of InnoDB that could handle the constraint
"foreign key" names ?
thanks in advance
regards
--
Richard FURIC
CEDRICOM
Tel : 02 99 55 07 55
Fax : 02 99 55 08 64
E-mail : [EMAIL PROTECTED]
site vitrine : www.cedricom.com 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Chinese

[Heikki Tuuri]

Hassan,
- Original Message - 
From: "Hassan Shaikh" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Sunday, October 10, 2004 6:55 PM
Subject: Chinese


Hi,
How do I handle Chinese text (along with regular English) in my
database? I am using INNODB tables.
you can use MySQL-4.1.6 and the UTF-8 character set for both Chinese and 
European languages.

Thanks.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/ 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CORRECT WAY TO SQUEEZE INNODB 4.0.17

[Heikki Tuuri]

Massimo,
- Original Message - 
From: ""Massimo Petrini"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, October 11, 2004 7:57 PM
Subject: CORRECT WAY TO SQUEEZE INNODB 4.0.17


I my network I have 1 master and 4 slaves. I need to squeeze the innodb on
my master; which is the correct way to execute the squeeze action (now the
innodb files is around 1gb, while in a new db is around 300 mB)
thanks
unfortunately, ibdata files never shrink, and you cannot remove them either. 
The only way in 4.0 is to recreate the whole tablespace.

If you use 4.1.6 with the my.cnf option
innodb_file_per_table
then each table goes into its own .ibd file, and you can free unused space 
in individual tables with

OPTIMIZE TABLE t;
Beware that the table rebuild that OPTIMIZE does can last very long if the 
table is bigger than your buffer pool.

Massimo
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/

-
Massimo Petrini
c/o Omt spa
Via Ferrero 67/a
10090 Cascine Vica (TO)
Tel.+39 011 9505334
Fax +39 011 9575474
E-mail  [EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

offer

[lakshmi.narasimharao]

Hi,
 I need a person who worked on oracle and mysql for a PCMM Level
company in Bangalore, India on a contract basis for 4 months. If any one
is interested please forward the resume asap. Min 3 years exp is
required. It is a very urgent requirement.

Thanks,
Narasimha




Confidentiality Notice

The information contained in this electronic message and any attachments to this 
message are intended
for the exclusive use of the addressee(s) and may contain confidential or privileged 
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Table creation Time stamp

[Heikki Tuuri]

Hi!
- Original Message - 
From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, October 13, 2004 6:43 PM
Subject: RE: Table creation Time stamp


--=_alternative 005527E185256F2C_=
Content-Type: text/plain; charset="US-ASCII"
By Jove! you are right! Sorry!  I can't think of an easy way to find those
dates. Mr. Tuuri, any ideas?
I am sorry for a late reply :). We were busy fixing bugs for the 4.1.6 
release.

You can look with
ls -l
the creation time of the .frm file of the table. The .frm file is in the 
database directory under the datadir of MySQL. Actually, I do not know why 
MySQL does not fetch the creation date from there. We will look at this.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/

"Anil Doppalapudi" <[EMAIL PROTECTED]> wrote on 10/13/2004
11:04:53 AM:
i see the time stamp only for Myisam table type and not for InnoDB table
type. where can we find for Innodb Table type
Thanks
Anil
DBA
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 8:18 PM
To: Anil Doppalapudi
Cc: [EMAIL PROTECTED]
Subject: Re: Table creation Time stamp
use the command: SHOW TABLE STATUS
http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Anil Doppalapudi" <[EMAIL PROTECTED]> wrote on 10/13/2004
10:42:11 AM:
> Hi,
>
>
> where can we find the time stamp of table creation i.e when that table
was
> created
>
>
> Thanks
> Anil
> DBA
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--=_alternative 005527E185256F2C_=-- 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Which Filesystem to choose?

[Heikki Tuuri]

Chris,
- Original Message - 
From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, October 18, 2004 10:24 AM
Subject: Which Filesystem to choose?


Hi,
I'm trying to set up a new Opteron-Based MySQL-Server.
The only thing I'm unsure about is which filesystem to choose..
ext3? ReiserFS? XFS?
What's your experience?
my impression is that all three have the same performance and reliability.
Opteron + Linux as a platform, on the other hand, still seems to have quite 
a few unexplained hardware faults or OS bugs.

Thanks
chris
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/ 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Varchar and InnoDB

[Heikki Tuuri]

Filip,
- Original Message - 
From: "Filip Rachunek" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, October 18, 2004 10:33 AM
Subject: Varchar and InnoDB


Hello,
I have a big table in my InnoDB database (more than 10 million rows)
and it contains a column of varchar(40) type. For some reason I need
to extend this column to varchar(160) but I don't expect to use the
whole capacity of this column, except for several rare cases.
My question is, after I make this "alter table" action, can it have a
negative impact on MySQL performance or required disk space? It is
just an ordinary non-indexed column, not used in where or sort by
conditions.
the ALTER TABLE itself may take quite long if you have many secondary keys 
on the table.

Increasing the maximum length of a VARCHAR column in InnoDB does not consume 
more space at all. InnoDB strips the trailing spaces from a VARCHAR column 
before storing to the data files.

Thank you.
Filip Rachunek
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Script question

[Philippe Poelvoorde]

mysql> system echo hi there;
mysql> system ls -ls;
mysql> system uname-a;
all work on the 2.4.22-10mdk kernel and
the semi-colon is optional.
mysql> system echo "hi there"
should be also working, the first parameters 'echo' is recognized as the 
  command to execute, and the folowings strings the parameters of this 
command. so since 'echo hi there' is not a command it wasn't working 
(try, doing `$echo\ hi\ there` at your prompt :)
Since some commands require quotes, it could be useful.
(system doesn't work with MySQL 3.23, does it ?)

--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Date as Primary ID

[Philippe Poelvoorde]

Ferhat BINGOL wrote:
Hi Scoot,
I do my table structure like that
CREATE TABLE `test_table` (
  `timestamp` date NOT NULL default '-00-00',
  `data` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`timestamp`),
  KEY `timestamp` (`timestamp`)
) TYPE=MyISAM;
Correct me if I'm wrong, but I think the second index is absolutely 
useless. It will just use more space.

--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

OT sites mirroring the list

[martin fasani]

Just did a google search on my company email because I'm receiving a lot of
spam and discovered that a lot of sites are taking mysql list and publishing
our emails everywhere.
E.g
www.webservertalk.com
archives.neohapsis.com/archives/mysql/

Is there any way to hide our emails so they don't appear like this. Please
reply to my email and not to the list. Thanks,

MARTIN
Tel: 0034  971706090
Fax: 0034  971444323
[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqld_safe starts, but mysqld(API) node "not connected"

[John Stile]

I start the management node, then the ndbd node, followed by mysqld API
node.  mysqld (api node) starts, and I can log into it (mysql -u root
-p), but it is not listed as a "connected" mysqld(API) using ndb_mgm
show.

Is there some thing I have to clear?  It did connect once, but will not
reconnect now.  No config files were changed.
 
The following are details about my install and start/stop process:
Config Files:
#
# ON Postal1
#
-
 cat /usr/local/mysql/data/Ndb.cfg
-
nodeid=20;host=postal1:2200

-
 cat /usr/local/mysql/ndb/ndb1/Ndb.cfg
-
nodeid=1;host=postal1:2200

--
 cat /usr/local/mysql/ndb/mgmt/Ndb.cfg
--
nodeid=63;host=postal1:2200

--
 cat /usr/local/mysql/ndb/mgmt/config.ini
--
##
# First Machine:
#  /usr/local/mysql/
#  data/
#  Ndb.cfg
#  log/
#  ndb/
#  mgmt/
#   config.ini
#   Ndb.cfg
#  ndb1/
#   Ndb.cfg
# 550MB indexes, 500MB data
# 2 replicas
##
[DB DEFAULT]
NoOfReplicas: 2
DataMemory:500M
IndexMemory:550M
MaxNoOfConcurrentTransactions:75
MaxNoOfConcurrentOperations:12
TimeBetweenWatchDogCheck:2000
MaxNoOfOrderedIndexes:75000
MaxNoOfTables:9000
MaxNoOfAttributes:4
[COMPUTER]
Id: 1
HostName: postal1
[COMPUTER]
Id: 2
HostName: postal2
[MGM]
Id: 63
ExecuteOnComputer: 1
PortNumber: 2200
LogDestination: 
SYSLOG:facility=local0,;FILE:filename=/usr/local/mysql/ndb/mgmt/mgmd.log
[DB]
Id: 1
ExecuteOnComputer: 1
FileSystemPath: /usr/local/mysql/ndb/mgmt/
[DB]
Id: 2
ExecuteOnComputer: 2
FileSystemPath: /usr/local/mysql/ndb/ndb1/
[API]
Id: 20
ExecuteOnComputer: 1
[API]
Id: 21
ExecuteOnComputer: 2
postal1:/usr/local/mysql# cat /usr/local/mysql/data/Ndb.cfg
nodeid=20;host=postal1:2200
postal1:/usr/local/mysql# cat /usr/local/mysql/ndb/ndb1/Ndb.cfg
nodeid=1
host=postal1:2200
postal1:/usr/local/mysql# cat /usr/local/mysql/ndb/mgmt/Ndb.cfg
nodeid=63
host=postal1:2200
--

--
cat /etc/my.cnf
--
[client]
#password   = your_password
port= 3306
socket  = /var/run/mysqld/mysqld.sock
[mysqld]
port= 3306
socket  = /var/run/mysqld/mysqld.sock
skip-locking
# NDB Cluster
ndbcluster
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin
server-id   = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
--

#
# ON Postal2
#
-
cat /usr/local/mysql/data/Ndb.cfg
-
nodeid=21;host=postal1:2200

-
cat /usr/local/mysql/ndb/ndb1/Ndb.cfg
-
nodeid=2;postal1:2200

-
cat /usr/local/mysql/ndb/mgmt/Ndb.cfg
-
nodeid=63;host=postal1:2200

-
cat /usr/local/mysql/ndb/mgmt/config.ini
-
#
# First Machine:
#  /usr/local/mysql/ndb/
#  mgmt/
#   config.ini
#   Ndb.cfg
#  log/
#  ndb1/
#   Ndb.cfg
# 550MB indexes, 500MB data
# 2 replicas
[DB DEFAULT]
NoOfReplicas: 2
DataMemory:500M
IndexMemory:550M
MaxNoOfConcurrentTransactions:75
MaxNoOfConcurrentOperations:12
TimeBetweenWatchDogCheck:2000
MaxNoOfOrderedIndexes:75000
MaxNoOfTables:9000
MaxNoOfAttributes:4
#[TCP DEFAULT]
#PortNumber: 28002
[COMPUTER]
Id: 1
HostName: postal1
[COMPUTER]
Id: 2
HostName: postal2
[MGM]
Id: 63
ExecuteOnComputer: 1
PortNumber: 2200
LogDestination: 
SYSLOG:facility=local0,;FILE:filename=/usr/local/mysql/ndb/mgmt/mgmd.log
[DB]
Id: 1
ExecuteOnComputer: 1
FileSystemPath: /usr/local/mysql/ndb/mgmt/
[DB]
Id: 2
ExecuteOnComputer: 2
FileSystemPath: /usr/local/mysql/ndb/ndb1