Query Cache slow downs?

[Dylan Neild]

Hi everyone,

I've been looking everywhere for an answer to this, so please excuse me 
if it should have been obvious.

I have a very big MySQL server, under load, serving in the 1500 QPS 
range. Under times of high concurrenncy (many threads connected issuing 
queries), I start to see a lot of the threads with "NULL" as their 
state (rather then Sending data, sorting, etc). These threads seem to 
be blocked for some reason, as they eventually do get executed.

Am I running into a locking issue?

MySQL seems like it -should- scale to 12 CPU's... so I must be missing 
something.

Up until this point, it's just grown and grown along with us (using 
InnoDB table system), so I'm hoping to continue. :)

Thanks in advance,

Dylan

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

Re: help regarding mysql

[Nils Valentin]

2003年 8月 2日 土曜日 15:11、あなたは書きました:
> Hi frnd.
>
> I am also using mysql and facing problems regarding access to
> the
> db using odbc from visual basic.
>
> i have made a table under mysql default db and
>
> used ms ado to connect to the db.
>
> what will be the server name in such a case(that will be used to
> connect to the db) using ado.
>
> well when i go to the gui i see that on my system's name &
> ipaddress  i have 2 databases registered.
>
> mysql and
>
> test
>
>
> i have made a table under mysql.
>
> and used ado to get to the db using server name = system
> name(OTAP)
>
> ado_pre.Open "DRIVER={MySQL ODBC for 3.51
> Driver};UID=root;PWD=root;SERVER=OTAP;"
>
>
>
> it says root dosent have permissions to open using
> [EMAIL PROTECTED]
>
> password(YES)
>
> Please help in this regard.
>
> what should be the server name or what must be the method to
> access the db.
>
>
> Thanking you.
>
> Alok.
>
> ___
> Download the hottest & happening ringtones here!
> OR SMS: Top tone to 7333
> Click here now:
> http://sms.rediff.com/cgi-bin/ringtone/ringhome.pl

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


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

Re: retrieving rows by insertion order

[Matthew McNicol]

You should use what you referred to as an insertion counter field. I'd call
it a "id" field that uses auto_increment. It's very useful to use this and
by definition it enables you to retrieve the data in the order that it was
inserted.





> - Original Message -
> From: "Murad Nayal" <[EMAIL PROTECTED]>
> To: "MySQL List" <[EMAIL PROTECTED]>
> Sent: Saturday, August 02, 2003 2:40 AM
> Subject: retrieving rows by insertion order
>
>
> >
> >
> > Hello,
> >
> > I vaguely remember reading in the manual that the order of the retrieved
> > rows in a response to a select statement is unpredictable (unless you
> > use an order by clause). this possibly depends on the indices set up for
> > the table and/or used in constructing the result etc.  is this accurate?
> > if so is there any way to insure that rows retrieved are returning in
> > the order by were inserted in, say other than ordering by some
> > 'insertion counter' (such a counter is of no use otherwise in my
> > application!).
> >
> > thanks for the help.
> >
> > --
> > Murad Nayal M.D. Ph.D.
> > Department of Biochemistry and Molecular Biophysics
> > College of Physicians and Surgeons of Columbia University
> > 630 West 168th Street. New York, NY 10032
> > Tel: 212-305-6884 Fax: 212-305-6926
> >
> > --
> > 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]

retrieving rows by insertion order

[Murad Nayal]

Hello,

I vaguely remember reading in the manual that the order of the retrieved
rows in a response to a select statement is unpredictable (unless you
use an order by clause). this possibly depends on the indices set up for
the table and/or used in constructing the result etc.  is this accurate?
if so is there any way to insure that rows retrieved are returning in
the order by were inserted in, say other than ordering by some
'insertion counter' (such a counter is of no use otherwise in my
application!).

thanks for the help.

-- 
Murad Nayal M.D. Ph.D.
Department of Biochemistry and Molecular Biophysics
College of Physicians and Surgeons of Columbia University
630 West 168th Street. New York, NY 10032
Tel: 212-305-6884   Fax: 212-305-6926

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

Changing error message

[Mysql]

now when using the
C:\mysql\bin\mysqld --standalone
the error message changes to:
Can't find messagefile 'c:mysql hare\english\errmsg.sys'
NO that is not typed incorrectly! mysql is has no idea where is wants to 
find that file.

I have downloaded the file twice now, with same result

below is a copy of C:\windows\my.ini

# This will be passed to all mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL
# The MySQL server

[mysqld]
basedir=C:\mysql\bin
datadir=C:\mysql\data
language=c:\mysql\share\english\
port=3306
#socket=MySQL
skip-locking
set-variable= key_buffer=256M
set-variable= max_allowed_packet=1M
set-variable= table_cache=256
set-variable= sort_buffer=1M
set-variable= record_buffer=1M
set-variable= myisam_sort_buffer_size=64M
set-variable= thread_cache=8
# Try number of CPU's*2 for thread_concurrency
# set-variable  = thread_concurrency=8
log-bin
server-id   = 1
[mysqldump]
quick
set-variable= max_allowed_packet=16M
[mysql]
no-auto-rehash
safe-updates
[isamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[myisamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[mysqlhotcopy]
interactive-timeout


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

Re: Backup conventions

[Simon Windsor]

Hi

Backups/recovery procedures for MySQL are very simple, and efficient.

The approach I take, is to

- Implement a replicated dual master

and

- Flush mysql db logs
- Use mysqldump  | gzip -c > .sql.gz at a quiet time
- Copy mysql logs

This is essentially, the same process one would use with oracle.

Simon

- Original Message - 
From: "Jean Hagen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 31, 2003 10:24 PM
Subject: Backup conventions


> Hi, I'm a new user of MySQL on Linux.
>
> I have been an Oracle DBA; and know that anyone familiar with Oracle takes
> backup procedures very seriously
>
> Anyone have recommendations for nightly backup procedures for MySQL
> (transactional) databases?  The literature seems a little ambiguous on
this.
>
> I did 'turn on' the general, binary and error logs.  Does anyone shut down
> the MySQL daemon regularly?  Anyone use mysqldump, or do folks simply use
a
> file system backup?  We have tons of databases and tons of tables, so I'm
> not really considering the lock/flush procedure on tables...
>
> Any suggestions?
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> -- 
> The information in this e-mail and any attached files is confidential.
> It is intended solely for the use of the addressee.
> Any unauthorised disclosure or use is prohibited.
>
> If you are not the intended recipient of the message,
> please notify the sender immediately and do not disclose the contents
> to any other person, use it for any purpose, or store or copy the
> information in any medium.
>
> This message has been scanned for viruses and dangerous content by
> MailScanner, http://www.mailscanner.info, and is believed to be clean.
>
>
>



-- 

This message has been scanned for viruses and dangerous content by
MailScanner, http://www.mailscanner.info, and is believed to be clean.


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

mysql crashes

[Miguel Perez]

Hi Guys:

Does anyone know how come I'm getting a lot of mysql processes when I start 
mysql server for the first time.

When I type top it showed the following:

 5:33pm  up 15 min,  4 users,  load average: 0.16, 0.19, 0.10
80 processes: 79 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states:  0.1% user,  0.2% system,  0.0% nice, 99.2% idle
CPU1 states:  0.1% user,  2.4% system,  0.0% nice, 96.4% idle
CPU2 states:  5.3% user,  0.1% system,  0.0% nice, 94.0% idle
CPU3 states: 28.3% user,  3.4% system,  0.0% nice, 67.2% idle
Mem:  2065160K av, 1028956K used, 1036204K free,   0K shrd,   12964K 
buff
Swap: 2096440K av,   0K used, 2096440K free  226252K 
cached

 PID USER PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
 715 mysql 15   0  627M 627M  2328 S 0.1 31.1   0:02 mysqld-max
 722 mysql 16   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 723 mysql 20   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 724 mysql 16   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 725 mysql 16   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 726 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 746 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 747 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 752 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 753 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 774 mysql 16   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 784 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 788 mysql 16   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 789 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 795 mysql 16   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 858 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 862 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 867 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 889 mysql 16   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 899 mysql 16   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 908 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
 914 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:04 mysqld-max
 967 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
1652 mysql 15   0  627M 627M  2328 S 0.0 31.1   0:00 mysqld-max
Thanks in advanced and I hope someone can help me.

Greetings

_
MSN Fotos: la forma más fácil de compartir e imprimir fotos.  
http://photos.msn.es/support/worldwide.aspx

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

MySQL List Stats July, 2003

[Bill Doerrfeld]

Be sure to visit the MySQL list archives at 
.

Here are stats for last month.

Enjoy!

Bill


MySQL List Stats
July, 2003

Note: Up/Down % as compared with June, 2003

Posts:   2556 (Down 6%)
Authors:  733 (Down 2%)
Threads:  882 (Down 2%)
Top 20 Contributors by Number of Posts

Nils Valentin100
Paul DuBois   95
Victoria Reznichenko  80
Egor Egorov   67
Rudy Metzger  66
Heikki Tuuri  57
gerald_clark  32
Jeff McKeon   26
Jeremy Zawodny25
Jim McAtee21
Sergei Golubchik  21
colbey21
Dathan Vance Pattishall   20
Dan Nelson20
Andrew Braithwaite19
Primaria Falticeni19
Info  17
Ola Ogunneye  17
Michael Satterwhite   16
Asif Iqbal16
Top 20 Threads by Number of Posts

What is a good benchmark? 26
Error 1044 & Error 2003 on 4.0.13 and RH9 20
What's up with this GATOR crap?   17
Can  run two versions of MySQL in Windows 2000?   16
Faster reindexing 15
MySQL vs. PostgreSQL -- speed test15
Working with the text datatype in MySQL   14
how to limit COUNT(*) 14
mysql stops processing14
CSV Formated output   13
Can mysql handle this load?   13
MySQL Password12
Newbie SELECT problem 12
Frequent Table Corruption - Please Help   12
Books advice  12
ERROR 2013:Lost connection to MySQL server11
mysqldump 11
recursive sql statement   11
does mySQL support a boolean data type?   11
Huge Server configuration 11
Top 20 Search Terms by Number of Requests

mysql 27
database  18
utf   13
table 13
key   12
query 11
innodb11
delete11
column11
set   10
to10
php   10
status 9
lasso  9
date   9
server 9
sql9
replication9
OSX8
where  8
--
-
Bill Doerrfeld[EMAIL PROTECTED]
Blue World Communications, Inc.   http://www.blueworld.com/
-
 Build and serve powerful data-driven Web sites
  with Lasso Studio and Lasso Professional.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql_info not very informative?

[Ray Kiddy]

I am trying to insert data using the C API.

Particulars:
OS: Mac OS X 10.3 (7A179)
MySQL: MySQL 4.1.0-alpha
table type: tried both MyISAM and InnoDB
I have a table:

mysql> describe testtable;
++-+---+--+-+-+---+
| Field  | Type| Collation | Null | Key | Default | Extra |
++-+---+--+-+-+---+
| _PK| int(11) | binary|  | PRI | 0   |   |
| first  | char(3) | latin1_swedish_ci | YES  | | NULL|   |
| second | char(3) | latin1_swedish_ci | YES  | | NULL|   |
| third  | char(3) | latin1_swedish_ci | YES  | | NULL|   |
++-+---+--+-+-+---+
4 rows in set (0.00 sec)
Note that the first, second, and third columns have 3 characters of 
space.

When I do:

printf("insert: %s\n", insert);
int result = mysql_query(one, insert);
printf("info: %s\n", mysql_info(one));
I get:

insert: INSERT INTO testtable (_PK,first,second,third) VALUES 
(1,'AAAXXX','BBB','CCC')
info: (null)
insert: INSERT INTO testtable (_PK,first,second,third) VALUES 
(2,'DDD','EEE','FFF')
info: (null)
insert: INSERT INTO testtable (_PK,first,second,third) VALUES 
(3,'GGG','HHHXXX','III')
info: (null)

Note that the value in the first insert, 'AAAXXX', is too long to fit. 
As is 'HHHXXX' in the third insert. And indeed, I see:

mysql> select * from testtable;
+-+---++---+
| _PK | first | second | third |
+-+---++---+
|   1 | AAA   | BBB| CCC   |
|   2 | DDD   | EEE| FFF   |
|   3 | GGG   | HHH| III   |
+-+---++---+
3 rows in set (0.00 sec)
So, why is mysql_info not giving me any information about the data loss 
that is going on here? Is that not information that might be of 
interest?

Is there some other call I have to make that will "prepare" for the 
mysql_info call? The doc does not seem to indicate this, but one never 
knows.

thanx - ray

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

error message: C:mysqin\share\english\errmsg.sys

[S Johnson]

Trying to start first time using command:
C:\mysql\bin\mysqld --standalone
Yields this error message:  Can't find 
messagefile'C:mysqin\share\english\errmsg.sys'aborting

Now in C:\mysql\share\english\ there is an errmsg.sys


Do you have any language=C:/mysqin/share/english/ entry in the my.ini?
--
Thanks for the Reply ...
It sounds like I need to add:
language=c:\mysql\share\english\

BUT, here is a copy (On XP Pro) of the current my.ini
C:\windows\my.ini
# This will be passed to all mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL
# The MySQL server

[mysqld]
basedir=C:\mysql\bin
datadir=C:\mysql\data
port=3306
#socket=MySQL
skip-locking
set-variable= key_buffer=256M
set-variable= max_allowed_packet=1M
set-variable= table_cache=256
set-variable= sort_buffer=1M
set-variable= record_buffer=1M
set-variable= myisam_sort_buffer_size=64M
set-variable= thread_cache=8
# Try number of CPU's*2 for thread_concurrency
# set-variable= thread_concurrency=8
log-bin
server-id= 1
[mysqldump]
quick
set-variable= max_allowed_packet=16M
[mysql]
no-auto-rehash
safe-updates
[isamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[myisamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[mysqlhotcopy]
interactive-timeout
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: RE: Newbie question...memo field

[Brent Elison]

Ok, let me explain my logic and please correct me if I'm wrong...

I gather from my limited understanding of MySQL and rdbms that it is not
advisable to store multiple values in one field on a db (such as the color
values in Table-A below).  I was thinking that I needed to have a separate
table for these color values, then do a lookup using the ID field to
retrieve the colors when needed.  The color field on Table-A would not be
needed after creating and populating Table-B.  Table-A will contain around
1,000 records.

Am I off track here?  What would be the correct way to design this db?

Thanks for the help.

Brent

-Original Message-
From: Adam Fortuno KOVICK [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 2:13 PM
To: [EMAIL PROTECTED]
Cc: MySQL List
Subject: Re: RE: Newbie question...memo field


Brent,

Maybe but give us more information. I know you moving data from one
table into a new one, but not much else. Whats the logic to the changes
you're making?

A$

- Original Message -
From: Brent Elison <[EMAIL PROTECTED]>
Date: Friday, August 1, 2003 3:25 pm
Subject: RE: Newbie question...memo field

> I appreciate the suggestion.  However, this solution will require
> me to run
> step (3) for each record I want updated into Table-B.  That would
> be 1000+
> times.
>
> Any other way to get this done without having to run all those
> updates?
> Thanks,
>
> Brent
> -Original Message-
> From: Adam Fortuno KOVICK [EMAIL PROTECTED]
> Sent: Friday, August 01, 2003 11:31 AM
> To: [EMAIL PROTECTED]
> Cc: MySQL List
> Subject: Re: Newbie question...memo field
>
>
> Normally I'd say do a select...into...from, but I don't think MySQL
> supports that. In lew of that, try this:
>
> (1) Create the new table.
>
> CREATE TABLE tbl_table_b (
> record SMALLINT NULL,
> id SMALLINT NULL,
> color VARCHAR(10)
> );
>
> (2) Insert the values from the source table to the destination.
>
> INSERT INTO tbl_b SELECT record, id, color FROM tbl_a;
>
>
> (3) Update the values you want changed.
>
> UPDATE tbl_b SET record = 2 id = 001 WHERE color = 'Pink';
> 
>
> Not sure if this is what you were after.
>
> Regards,
> A$
>
> - Original Message -
> From: Brent Elison <[EMAIL PROTECTED]>
> Date: Friday, August 1, 2003 11:19 am
> Subject: Newbie question...memo field
>
> > Hi all,
> >
> > I'm fairly new to MySQL and have the following scenario:
> >
> > The Table-A below was converted from a Filemaker Pro database.
> > The color
> > field was a value list in the FMP database that converted over as
> > a text
> > field.  The color field contains all the selected values in the
> > value list
> > (from the FMP database) and looks like the data below (Table-A)
> > when viewed
> > in the BLOB-editor (MySQL-Front).  So, how do I extract the color
> > valuesfrom the color field in Table-A and put them into a separate
> > table looking
> > like Table-B?
> >
> > Sorry for the totally newbie question.  Thanks for any suggestion.
> >
> > Brent
> >
> >
> > Table-A
> > Record  ID  Color
> > 
> > 1   001 Black
> > Pink
> > White
> > 2   002 Black
> > 3   003 Green
> > 4   AAA Black
> > White
> > 
> >
> >
> > Table-B
> > Record  ID  Color
> > 
> > 1   001 Black
> > 2   001 Pink
> > 3   001 White
> > 4   002 Black
> > 5   003 Green
> > 6   AAA Black
> > 7   AAA White
> > -
> >
> >
> >
> > --
> > 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]
>


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

Re: Order by umm OR?

[Roger Baklund]

* Yoed
> Lets say I have a query similar to:
>
> SELECT * FROM company WHERE id='3' OR id='1' OR id='7' ;
>
> I want to be able to get the results to come out ordered by the order
> the ids were searched (the sequence in the WHERE OR; 3 first then 1,
> then 7...), so the results would be like:
>
> Row   | id| name
> ---
> 0 | 3 | Joe's Co
> 1 | 1 | Buymart
> 2 | 7 | The big evil
>
> As it is the results returns it like id 1, then 3, and 7.

Try using the FIELD() function (PHP example):

$idlist = "3,1,7";
mysql_query("SELECT * FROM company
  WHERE id IN ($id_list)
  ORDER BY FIELD(id,$id_list)");

... which expands to:

SELECT * FROM company
  WHERE id IN (3,1,7)
  ORDER BY FIELD(id,3,1,7);

The FIELD() function is documented here:

http://www.mysql.com/doc/en/String_functions.html >

I guess your 'id' field is an integer, and this is a string function, but it
should work anyway. If your 'id' is a string type column, you must quote
each id like you did in your example.

--
Roger


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

Re: RE: Newbie question...memo field

[Adam Fortuno KOVICK]

Brent,

Maybe but give us more information. I know you moving data from one 
table into a new one, but not much else. Whats the logic to the changes 
you're making?

A$

- Original Message -
From: Brent Elison <[EMAIL PROTECTED]>
Date: Friday, August 1, 2003 3:25 pm
Subject: RE: Newbie question...memo field

> I appreciate the suggestion.  However, this solution will require 
> me to run
> step (3) for each record I want updated into Table-B.  That would 
> be 1000+
> times.
> 
> Any other way to get this done without having to run all those 
> updates?
> Thanks,
> 
> Brent
> -Original Message-
> From: Adam Fortuno KOVICK [EMAIL PROTECTED]
> Sent: Friday, August 01, 2003 11:31 AM
> To: [EMAIL PROTECTED]
> Cc: MySQL List
> Subject: Re: Newbie question...memo field
> 
> 
> Normally I'd say do a select...into...from, but I don't think MySQL
> supports that. In lew of that, try this:
> 
> (1) Create the new table.
> 
> CREATE TABLE tbl_table_b (
> record SMALLINT NULL,
> id SMALLINT NULL,
> color VARCHAR(10)
> );
> 
> (2) Insert the values from the source table to the destination.
> 
> INSERT INTO tbl_b SELECT record, id, color FROM tbl_a;
> 
> 
> (3) Update the values you want changed.
> 
> UPDATE tbl_b SET record = 2 id = 001 WHERE color = 'Pink';
> 
> 
> Not sure if this is what you were after.
> 
> Regards,
> A$
> 
> - Original Message -
> From: Brent Elison <[EMAIL PROTECTED]>
> Date: Friday, August 1, 2003 11:19 am
> Subject: Newbie question...memo field
> 
> > Hi all,
> >
> > I'm fairly new to MySQL and have the following scenario:
> >
> > The Table-A below was converted from a Filemaker Pro database.
> > The color
> > field was a value list in the FMP database that converted over as
> > a text
> > field.  The color field contains all the selected values in the
> > value list
> > (from the FMP database) and looks like the data below (Table-A)
> > when viewed
> > in the BLOB-editor (MySQL-Front).  So, how do I extract the color
> > valuesfrom the color field in Table-A and put them into a separate
> > table looking
> > like Table-B?
> >
> > Sorry for the totally newbie question.  Thanks for any suggestion.
> >
> > Brent
> >
> >
> > Table-A
> > Record  ID  Color
> > 
> > 1   001 Black
> > Pink
> > White
> > 2   002 Black
> > 3   003 Green
> > 4   AAA Black
> > White
> > 
> >
> >
> > Table-B
> > Record  ID  Color
> > 
> > 1   001 Black
> > 2   001 Pink
> > 3   001 White
> > 4   002 Black
> > 5   003 Green
> > 6   AAA Black
> > 7   AAA White
> > -
> >
> >
> >
> > --
> > 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]
> 

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

RE: Order by umm OR?

[Kevin Fries]

Not surprising, the dbms has no built-in support for ordering rows by
the filters in the where clause.
But with a bit of programming you should be able to construct a useful
ORDER BY clause to do what you want...

SELECT * FROM company WHERE id='3' OR id='1' OR id='7'
ORDER BY CASE id when '3' then 1 when '1' then 2 when '7' then 3 else 4
end;

So you'll have to construct the ORDER BY to  relate each successive OR
item into a 'when ... then' subclause.
It's not pretty, but it works.

Kevin

PS:  consider using WHERE id in ( '3', '1', '7') instead for brevity.



> -Original Message-
> From: Yoed [mailto:[EMAIL PROTECTED] 
> Sent: Friday, August 01, 2003 12:34 PM
> To: [EMAIL PROTECTED]
> Subject: Order by umm OR?
> 
> 
> Hi,
> 
> Lets say I have a query similar to:
> 
> SELECT * FROM company WHERE id='3' OR id='1' OR id='7' ;
> 
> I want to be able to get the results to come out ordered by 
> the order the ids were searched (the sequence in the WHERE 
> OR; 3 first then 1, then 7...), so the results would be like:
> 
> Row   | id| name
> ---
> 0 | 3 | Joe's Co
> 1 | 1 | Buymart
> 2 | 7 | The big evil
> 
> As it is the results returns it like id 1, then 3, and 7. 
> 
> Thanks for your help guys,
> Best,
> Yoed
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 


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

mysqlcheck fail in my.ini

[Luis Lozano]

I have this my.ini
--
[WinMySQLAdmin]
Server=C:/MYSQL/bin/mysqld-max.exe
QueryInterval=10
[mysqlcheck]
host=localhost
user=root
force
fast 
all-databases
silent
debug=c:\mychk.log
---
but do not check the BD at startup and do not write the log file. Some idea?

Thanks

luis

Order by umm OR?

[Yoed]

Hi,

Lets say I have a query similar to:

SELECT * FROM company WHERE id='3' OR id='1' OR id='7' ;

I want to be able to get the results to come out ordered by the order
the ids were searched (the sequence in the WHERE OR; 3 first then 1,
then 7...), so the results would be like:

Row | id| name
---
0   | 3 | Joe's Co
1   | 1 | Buymart
2   | 7 | The big evil

As it is the results returns it like id 1, then 3, and 7. 

Thanks for your help guys,
Best,
Yoed


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

RE: Newbie question...memo field

[Brent Elison]

I appreciate the suggestion.  However, this solution will require me to run
step (3) for each record I want updated into Table-B.  That would be 1000+
times.

Any other way to get this done without having to run all those updates?

Thanks,

Brent
-Original Message-
From: Adam Fortuno KOVICK [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 11:31 AM
To: [EMAIL PROTECTED]
Cc: MySQL List
Subject: Re: Newbie question...memo field


Normally I'd say do a select...into...from, but I don't think MySQL
supports that. In lew of that, try this:

(1) Create the new table.

CREATE TABLE tbl_table_b (
 record SMALLINT NULL,
 id SMALLINT NULL,
 color VARCHAR(10)
);

(2) Insert the values from the source table to the destination.

INSERT INTO tbl_b SELECT record, id, color FROM tbl_a;


(3) Update the values you want changed.

UPDATE tbl_b SET record = 2 id = 001 WHERE color = 'Pink';


Not sure if this is what you were after.

Regards,
A$

- Original Message -
From: Brent Elison <[EMAIL PROTECTED]>
Date: Friday, August 1, 2003 11:19 am
Subject: Newbie question...memo field

> Hi all,
>
> I'm fairly new to MySQL and have the following scenario:
>
> The Table-A below was converted from a Filemaker Pro database.
> The color
> field was a value list in the FMP database that converted over as
> a text
> field.  The color field contains all the selected values in the
> value list
> (from the FMP database) and looks like the data below (Table-A)
> when viewed
> in the BLOB-editor (MySQL-Front).  So, how do I extract the color
> valuesfrom the color field in Table-A and put them into a separate
> table looking
> like Table-B?
>
> Sorry for the totally newbie question.  Thanks for any suggestion.
>
> Brent
>
>
>   Table-A
> RecordID  Color
> 
> 1 001 Black
>   Pink
>   White
> 2 002 Black
> 3 003 Green
> 4 AAA Black
>   White
> 
>
>
>   Table-B
> RecordID  Color
> 
> 1 001 Black
> 2 001 Pink
> 3 001 White
> 4 002 Black
> 5 003 Green
> 6 AAA Black
> 7 AAA White
> -
>
>
>
> --
> 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: Speeding up Queries

[Scott Helms]

1.  Optimize your database calls:
http://www.mysql.com/doc/en/MySQL_Optimisation.html

2.  Buy bigger servers.

3.  Optimize your application.

4.  Upgrade to ver 4.x to take advantage of query caching.


I apologize for the general answer, but optimization is _very_ specific
and without detailed information on your set up thats about all anyone
can provide.  If you are reason

Scott Helms

On Fri, 2003-08-01 at 15:17, Daniel Negron/KBE wrote:
> I have an osCommerce Site along with another Dating site on a completely
> different server.  on the oscommerce site  I have 1200 products and the
> server queries the records very slowwly.  I have enabled gzip compression
> to try and speed this up.  Only very slightly has the oscommerce site been
> sped up.  The dating site has 11000 records in it and the same thing has
> happened and I am wondering what I can do to speed up the queries that are
> made to each site.
> 
> Any suggestions ?
> 
> TIA


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

Speeding up Queries

[Daniel Negron/KBE]

I have an osCommerce Site along with another Dating site on a completely
different server.  on the oscommerce site  I have 1200 products and the
server queries the records very slowwly.  I have enabled gzip compression
to try and speed this up.  Only very slightly has the oscommerce site been
sped up.  The dating site has 11000 records in it and the same thing has
happened and I am wondering what I can do to speed up the queries that are
made to each site.

Any suggestions ?

TIA


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

OT: Congratulation

[Andreas Karl Wittwer]

Hi,

Congratulation to MySQL for a two pages article published by the leading
business journal in Germany (Wirtschaftswoche).

Sorry, no online resource available only a headline
http://www.wiwo.de (German language)

sql, mysql

Andreas Karl Wittwer
I'm not related to wiwo in any case, just a reader ...



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

Re: Need help in querying two tables

[Roger Baklund]

* Jaime Teng
[...]
> Now, I have two of these tables (archivetable,currenttable).
>
> My problem is how do I perform a single query such that I get
> results from these two tables:
>
> mysql> select * from archivetable,currenttable;
> +++-+-+
> | sessionid  | username   | logon   | logoff  |
> +++-+-+
> | 03 | dangco77   | 1996-09-25 20:51:59 | 1996-09-25 21:07:00 |
> | 06 | mccarthy   | 1996-09-26 06:15:35 | 1996-09-26 06:20:00 |
> | 07 | sigmaph| 1996-09-26 06:25:48 | 1996-09-26 06:28:00 |
> | 09 | sigmaph| 1996-09-26 08:31:53 | 1996-09-26 08:51:00 |
> | 1000265891 | okame  | 2003-08-01 13:38:24 | 2003-08-01 13:43:42 |
> | 1000265893 | kbs| 2003-08-01 13:38:30 | 2003-08-01 13:38:48 |
> | 1000265897 | bdo-albaro | 2003-08-01 13:38:54 | 2003-08-01 14:07:06 |
> +++-+-+
>
> of course that last query isnt correct but thats the result I want.
>
> any suggestion? I read about using JOIN but I have no idea how to\
> make it work for my need.

Like Fred said, UNION if you use 4.x, but if you use 3.23.x, you could use
the MERGE tables option:

http://www.mysql.com/doc/en/MERGE.html >

You basically create a 'wrapper' table around your existing two (or more)
identically strucured tables, and can query them all in a single SELECT like
if it was a single table.

--
Roger


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

RE: max clients

[Brian Austin]

http://www.mysql.com/doc/en/Linux.html

If you are using Linux, you need to read this section.  You can get up to
4096 connections minus a few for the server admin threads.  Did this a while
back and works well.  I don't know if any of these issues have been resolved
in the Kernel or LinuxThreads yet.  However, you can see from some MS SQL
Server, and Oracle benchmarks, that you can have 20,000+ connections which
sounds cool, till you read the fine print and see each query takes over 5
minutes!  I don't remember where I read that, but it was posted on slashdot
a while back.

Hope that helps.

Brian




-Original Message-
From: NEWMEDIAPLAN [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 1:46 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: max clients


Hi Jeremy

I'm planning to use a dual xeon 2.8Ghz and 6/8 gb ram
on the database server.
Redhat 9 is the o.s.
Applications (perl dbi) will reside on other boxes.

What hw/o.s. are you using?
Can you send me your variables (max connections etc.) ... just for a check
;)

Thanks!!

|-Original Message-
|From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
|Sent: Thursday, July 31, 2003 11:36 PM
|To: NEWMEDIAPLAN
|Cc: [EMAIL PROTECTED]
|Subject: Re: max clients
|
|
|On Thu, Jul 31, 2003 at 08:36:02PM +0200, NEWMEDIAPLAN wrote:
|> Can mysql handle 5000 concurrent webusers sending queries to the
|db through
|> a web search engine.
|> Is it possible (with a very big server/hw) ?
|
|What OS are you  using?  And how much hardare?
|--
|Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
|<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
|
|MySQL 4.0.13: up 21 days, processed 679,998,992 queries (359/sec. avg)
|



--
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: my.cnf is not available under windows 2000

[Andy Eastham]

Morten ,

The file used is my.cnf on unix and my.ini on windows.

You should only have one file.

Andy

> -Original Message-
> From: Morten Gulbrandsen [mailto:[EMAIL PROTECTED]
> Sent: 01 August 2003 14:23
> To: [EMAIL PROTECTED]
> Subject: my.cnf is not available under windows 2000
> 
> 
> Hi programmers,
> 
> according to the manual,
> 
> There are two option files with the same function: 
> `C:\my.cnf', and the `my.ini' file in the Windows directory.
> 
> Is it sufficient with only one of the files ?
> 
> I have only my.ini  
> 
> For which purpose is my.cnf , please?
> 
> Yours Sincerely
> 
> Morten Gulbrandsen
> 
> 
> 
> -- 
> 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: help to structure query without using subselects

[Roger Baklund]

* Petre Agenbag
> The distinct clause will return only one occurrance of the
> implicated field.
> distinctrow would do the same, but for an entire row. what I am
> looking for
> is something like distinctset(field1,field2,field7) , is this what the
> MAX-CONCAT "trick" attempts to do?

Not exactly, but similarly. You should not use 'distinct' in this case.
GROUP BY is used to create only one row for each name, 'distinct' is not
needed.

> You see, what I'm getting at is that I am afraid that even with the
> MAX-CONCAT trick it will not "know" to include the
> "anything_else" from the
> distinct row with the highest id, but rather the first occurrance thereof.

No, that won't happen.

> And by the way, as *i think* I mentioned, "anything_else" would most
> definately NOT be an integer or anything that I would think can
> be "MAX"'ed.

This is nothing to worry about, because the CONCAT() function makes
everything a string, and you _can_ MAX() a string. The CONCAT() will make a
string out of every id/anything_else combination, and MAX() is used on the
created string value to find the highest combination for each name. However,
"2|anything_else" is bigger than "11|anything_else", so you may need to
prefix 'id' with zeroes. (See the use of LPAD() in the example in the
manual.)

> However, I'm already discrediting/doubting a solution without even testing
> it... Geez, I'm a prick hey? ;)

Skepticism is fine, but I think you should take a closer look at the example
in the manual. :)

> Maybe you can also help me to think about the following:
>
> idnamesomething   else
> 1 joe testtest2
> 2 joe testtest3
> 3 jacktesta   test
> 4 jacktestb   test1
> 5 jacktestb   test2
>
>
>
> Now I want to query with the following pseudo string:
>
> I first need to get hold of the distinct set of name,something with the
> highest row id, and basically remove everything else from the equation.
> For instance, should I do
>
> select id,name, something from this_table where else = 'test2'
>
> it should NOT return a value. I know this doesn't make sense, because this
> query is valid, and *should* by rights return
>
> 1 joe test
> 5 jacktestb
>
> BUT, that is my dilemma, for what I'm lookinmg for, only
>
> 5 jacktestb
>
> should return because that row did in fact match the criteria for
> the "else
> = test2", BUT, it also was indeed the "last" row with the distinct set of
> jack and testb.

Problems like this can sometimes be solved with a self join, but be aware
when you have big amounts of data, this could be very heavy for the server
if there are many rows with the same name. This works on your test data:

select t1.id, t1.name, t1.something
  from this_table t1
  left join this_table t2 on
t2.id>t1.id and t2.name=t1.name
  where
t1.`else` = 'test2' and
t2.id IS NULL;

The idea here is to find any row (t2) with the same name as the current one
(t1), but with a higher id. If we find it, we _ignore_ the row in the result
set, because this is not the highest id. Or, to put it the other way around,
we only want the rows where we did not find any t2 (t2.id IS NULL).

> My problem is having that last condition added to the string that
> says 
> where else = 'test2' BUT only if distinct(name, something) is the row with
> the highest id.

Both/any of the methods mentioned above could be usefull for you, I think.

[...]

> Thanks in any event for your time and interest.

happy to help, :)

--
Roger


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

Re: Newbie question...memo field

[Adam Fortuno KOVICK]

Normally I'd say do a select...into...from, but I don't think MySQL 
supports that. In lew of that, try this:

(1) Create the new table.

CREATE TABLE tbl_table_b (
 record SMALLINT NULL,
 id SMALLINT NULL,
 color VARCHAR(10)
);

(2) Insert the values from the source table to the destination.

INSERT INTO tbl_b SELECT record, id, color FROM tbl_a; 


(3) Update the values you want changed.

UPDATE tbl_b SET record = 2 id = 001 WHERE color = 'Pink';


Not sure if this is what you were after.

Regards,
A$

- Original Message -
From: Brent Elison <[EMAIL PROTECTED]>
Date: Friday, August 1, 2003 11:19 am
Subject: Newbie question...memo field

> Hi all,
> 
> I'm fairly new to MySQL and have the following scenario:
> 
> The Table-A below was converted from a Filemaker Pro database.  
> The color
> field was a value list in the FMP database that converted over as 
> a text
> field.  The color field contains all the selected values in the 
> value list
> (from the FMP database) and looks like the data below (Table-A) 
> when viewed
> in the BLOB-editor (MySQL-Front).  So, how do I extract the color 
> valuesfrom the color field in Table-A and put them into a separate 
> table looking
> like Table-B?
> 
> Sorry for the totally newbie question.  Thanks for any suggestion.
> 
> Brent
> 
> 
>   Table-A
> RecordID  Color
> 
> 1 001 Black
>   Pink
>   White
> 2 002 Black
> 3 003 Green
> 4 AAA Black
>   White
> 
> 
> 
>   Table-B
> RecordID  Color
> 
> 1 001 Black
> 2 001 Pink
> 3 001 White
> 4 002 Black
> 5 003 Green
> 6 AAA Black
> 7 AAA White
> -
> 
> 
> 
> -- 
> 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: UNION or not?

[Andy Jackman]

Gary,
The if() function often allows you to replace a union query. For example
this may work for you - watch the LIMIT it operates AFTER the sort so
your sort needs to put the records which you want at the front:

SELECT user_id, username, last_login, if(last_team = 380, 1, 2) as
SortOrder FROM users
 
  WHERE unix_timestamp(last_access) > unix_timestamp()-1440

  AND user_id <> '9' ORDER BY SortOrder, last_login LIMIT 20);

Regards,
Andy

Gary Broughton wrote:
> 
> Hi all
> 
> 
> 
> I want to provide a list of up to 20 online users on our network of
> football forums, but would like to list those live on the current team
> first, before "filling" any remainder with those online using a
> different team.  I couldn't see any way of getting it all into one
> select (which in English 'speak' would be like "order by team 380, then
> get the rest" I suppose?), and saw only the UNION function as the
> possible solution.
> 
> 
> 
> All I'm after, if possible, is to know if I'm using the most efficient
> method of retrieving the data, and also whether putting the extra "LIMIT
> 20" outside the UNION would indeed pick up the first 20 records only,
> even though there's a potential for 40.
> 
> 
> 
> (SELECT user_id, username, last_login FROM users
> 
>  WHERE unix_timestamp(last_access) > unix_timestamp()-1440
> 
>  AND user_id <> '9' AND last_team = '380' ORDER BY last_login LIMIT 20)
> 
> UNION
> 
> (SELECT user_id, username, last_login FROM users
> 
>  WHERE unix_timestamp(last_access) > unix_timestamp()-1440
> 
>  AND last_team <> '380' ORDER BY last_login LIMIT 20)
> 
> LIMIT 20
> 
> 
> 
> Incidentally, the "user_id <> '25'" is only there to prevent display of
> the name of the current online user in the list.
> 
> 
> 
> Many thanks as always
> 
> Gary

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

Native Arabic speakers wanted

[Stefan Hinz]

Dear all,

the MySQL Documentation Team is looking for native Arabic speakers on
this list who are interested in reviewing an Arabic translation of the
MySQL reference manual. Reviewers will receive our gratitude, and
will of course be mentioned in the credits section of the manual.

If you're interested, please send a mail to [EMAIL PROTECTED] providing
some background on who you are, where you are located, and how much
experience you have with both MySQL and translations.

Regards,
--
  Stefan Hinz
  I am MySQL certified - are you?
  http://www.mysql.com/certification


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

Performance questions

[Jack Coxen]

I've gone through the mailing list archives, read the appropriate parts of
the manual and searched the internet and still have questions.  What I'm
running into is a serious performance problem with a fairly large (by my
standards) database.  My system setup is as follows:

Dual processor Sun E250 w/1.5GB RAM running Solaris 8 (with all current
patches)
18GB dedicated drive/17GB data partition for the database

I'm running MySQL 4.0.12 standard with the Large my.cnf in place.  I'm not
running the Huge my.cnf because I'm running other applications on the
server.

Other software running on the server consists of:
Apache
120 iterations of MRTG - one for each of the devices I have to monitor.  The
MRTG RRD databases are on a separate drive from MySQL and MRTG doesn't
contribute seriously to the system load.
Various shell scripts and Perl scripts triggered by cron to automate damn
near everything. :)

My db application is a program called RTG - it's a replacement for MRTG that
uses MySQL on the backend - http://rtg.sourceforge.net.  BTW, both of these
applications (if you're not familiar with them) are designed to monitor
traffic through routers and switches.  MRTG uses a program called RRD (Round
Robin Database) on the backend that does data averaging for any data over 24
hour old.  RTG scales better for large networks and doesn't do data
averaging.  It keeps live data to the limit of your drive space.

Since starting up RTG not quite 3 months ago, my database has grown to over
10GB in size.  If I can get the drive space to keep the 2 years worth of
data I want to, my database will be edging upward toward 100GB.  There are
currently 682 tables ranging in size from 2 KB/1 record to around 310
MB/7570511 records depending on the number of ports on a router and how much
traffic flows through it.  

I'm running around 2500 queries/minute - 99+% of them inserts.  My cpu load
is minimal - around .40 nominally but it may go as high as 1.80 or so when
handling multiple large retrievals.  If I look at the iostats output for my
server, the drive controller bandwidth utilization is around 30-40% during
normal operation but immediately jumps to 100% utilization during retrieves.
Retrieval is done via Perl scripts or from Perl/PHP WebPages.

I'm trying to get a RAID array loaded with striped drives to hang off the
server but until I can talk someone into signing the check, I need to do
anything I can to improve performance.

Does anyone have any suggestions?  Preferably ones that don't involve money?
If it would help, I _might_ be able to coerce the IT group into giving me
more RAM but an E250 will only hold 2GB RAM and I'm already at 1.5GMB.  I've
modified the RTG table structure to use different indexes and modified the
Perl/PHP scripts to suit those changes and gotten significant performance
boosts from that.  What I really need are system tweaks and configuration
changes to improve performance.  

Jack

Jack Coxen
IP Network Engineer
TelCove
712 North Main Street
Coudersport, PA 16915
814-260-2705

RE: calender table - time column?

[Andrew]

Thanks Keith here it is :)

The booking system will take the format of a form. Customer or travel agent can
select a date of travel. They will also input how many places are needed.

The system will then check that the selected dates are available. At this point
there are two possible responses.

1)

Places are not available: System will bring user back to 1st page and ask them
to choose another date.

2)

Places are available.

System will look at the date of the holiday and check to see if that day is a
Mon, Tue, Wed, etc. Depending on the result a list of possible flights and costs
will be displayed (pulled from the data base)

User will choose their flight.



At this point the system will move the user to a payment area. Where all details
are filled out and will be sent for payment and also e-mail sent to Admin with
booking details. The booking system will also now be updated so that no one else
can book those dates (up to 24 persons rotating).

To deal with the travel agent commission a reference input field for the travel
agent to fill in on the e-mail/payment form. With a description similar to;

If you have a reference code for this holiday, please type it here. (Help)
The help will be linked to a pop up with more advice ect.

The system will take the given date and check the number of places is available
and if so will move to the next day and do it all again. When bookings are
written to the table there will be 2 entries for each person each day. Entry one
will be a reference number into one of the spare places to identify the user who
is in that slot. The second entry will be amendment to the number of places
available. This will be based on the itinerary and keep up to date available
places and avoid booking where no places are left.  Checks will be in place for
bogus bookings (payment).

This is based on the present itinerary and tour booking operation and may need
further amendments to fit in with the live working model.
-
Ideally what I would like is a DB structure that would work or an idea of the
table holding the Time/Date

Thank you

Andrew

>


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

RE: calender table - time column?

[Keith C. Ivey]

On 1 Aug 2003 at 17:08, Andrew wrote:

> Hi did anyone reply to this?

It doesn't look like it.  Pretend you don't know anything at all 
about your project and try reading your message.  Would you have any 
idea what sort of answer was wanted?  If you want help, you have to 
explain exactly what's needed and provide details.  Otherwise your 
message is likely to be ignored.

> >I am currently in the process of building a calender/date MySQL table
> >that will be searched for available dates for holiday tours?
> >
> >I want to use a time column as there will be a restriction on places
> >for up to 3 days so this will have to be taken into account.
> >
> >Has anyone done something like this?
> >
> >Cheers
> >Andrew

-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org


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

Re: Need help in querying two tables

[Fred van Engen]

Hi,

On Fri, Aug 01, 2003 at 11:55:36PM +0800, Jaime Teng wrote:
> Now, I have two of these tables (archivetable,currenttable).
> 
> My problem is how do I perform a single query such that I get
> results from these two tables:
> 
> mysql> select * from archivetable,currenttable;
> +++-+-+
> | sessionid  | username   | logon   | logoff  |
> +++-+-+
> | 03 | dangco77   | 1996-09-25 20:51:59 | 1996-09-25 21:07:00 |
> | 06 | mccarthy   | 1996-09-26 06:15:35 | 1996-09-26 06:20:00 |
> | 07 | sigmaph| 1996-09-26 06:25:48 | 1996-09-26 06:28:00 |
> | 09 | sigmaph| 1996-09-26 08:31:53 | 1996-09-26 08:51:00 |
> | 1000265891 | okame  | 2003-08-01 13:38:24 | 2003-08-01 13:43:42 |
> | 1000265893 | kbs| 2003-08-01 13:38:30 | 2003-08-01 13:38:48 |
> | 1000265897 | bdo-albaro | 2003-08-01 13:38:54 | 2003-08-01 14:07:06 |
> +++-+-+
> 
> of course that last query isnt correct but thats the result I want.
> 

You can use a UNION to do this, but you need MySQL 4.x. It won't work in
3.23.x or before. Look in the manual for details.


> any suggestion? I read about using JOIN but I have no idea how to\
> make it work for my need.
> 

Joins are used for combining records from multiple tables, which is not
what you seem to want to do.


Regards,

Fred.

-- 
Fred van Engen  XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC  Almere
fax: +31 36 5462424 The Netherlands

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

RE: calender table - time column?

[Andrew]

Hi did anyone reply to this?

Andrew


>I am currently in the process of building a calender/date MySQL table that will
>be searched for available dates for holiday tours?
>
>I want to use a time column as there will be a restriction on places
>for up to 3
>days so this will have to be taken into account.
>
>Has anyone done something like this?
>
>Cheers
>Andrew
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.504 / Virus Database: 302 - Release Date: 24/07/2003
>


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

re: mysqlhotcopy

[Seth Redmond]

I'm running mySql 3.23 on OS X 10.2 and I've reached a bit of an 
impassse with mysqlhotcopy, after having to re=install dbd and dbi, I 
keep getting the error:
	DBD::mysql::db do failed: You have an error in your SQL syntax near '` 
READ, `trypanofan`.``construct`` READ, `trypanofan`.``dapi`` READ, 
`trypanofan' at line 1 at /usr/bin/mysqlhotcopy line 438.

there appears to be a workaround in the manual 
(http://www.mysql.com/doc/en/mysqlhotcopy.html) which indicates it's 
the method of quotation and suggests modifying 'quote_names' - 
unfortunately I have no idea where 'quote_names' is or where it's 
supposed to be.

any suggestions would be lovely...

thanks

-s

--
__
Seth Redmond
DNA resource and Database Curator
Wellcome Trust Laboratories for Molecular Parasitology
Department of Biological Sciences
Imperial College
London
SW7 2AZ
[EMAIL PROTECTED]
__
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need help in querying two tables

[Jaime Teng]

Hi,

I have a table:

mysql> describe archivetable;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| sessionid | char(10) |  | PRI | |   |
| username  | char(25) |  | MUL | |   |
| logon | datetime |  | MUL | -00-00 00:00:00 |   |
| logoff| datetime |  | MUL | -00-00 00:00:00 |   |
+---+--+--+-+-+---+

Whenever I do a query, I get results in this format:

mysql> select * from archivetable;
+++-+-+
| sessionid  | username   | logon   | logoff  |
+++-+-+
| 03 | dangco77   | 1996-09-25 20:51:59 | 1996-09-25 21:07:00 |
| 06 | mccarthy   | 1996-09-26 06:15:35 | 1996-09-26 06:20:00 |
| 07 | sigmaph| 1996-09-26 06:25:48 | 1996-09-26 06:28:00 |
| 09 | sigmaph| 1996-09-26 08:31:53 | 1996-09-26 08:51:00 |
+++-+-+

Now, I have two of these tables (archivetable,currenttable).

My problem is how do I perform a single query such that I get
results from these two tables:

mysql> select * from archivetable,currenttable;
+++-+-+
| sessionid  | username   | logon   | logoff  |
+++-+-+
| 03 | dangco77   | 1996-09-25 20:51:59 | 1996-09-25 21:07:00 |
| 06 | mccarthy   | 1996-09-26 06:15:35 | 1996-09-26 06:20:00 |
| 07 | sigmaph| 1996-09-26 06:25:48 | 1996-09-26 06:28:00 |
| 09 | sigmaph| 1996-09-26 08:31:53 | 1996-09-26 08:51:00 |
| 1000265891 | okame  | 2003-08-01 13:38:24 | 2003-08-01 13:43:42 |
| 1000265893 | kbs| 2003-08-01 13:38:30 | 2003-08-01 13:38:48 |
| 1000265897 | bdo-albaro | 2003-08-01 13:38:54 | 2003-08-01 14:07:06 |
+++-+-+

of course that last query isnt correct but thats the result I want.

any suggestion? I read about using JOIN but I have no idea how to\
make it work for my need.

hoping for your help.

Jaime


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

Re: Mysqld is keep crashing

[Asif Iqbal]

Is there a pdf format of this ibman page ? OR is there a good book on
Mysql+innodb out there ?

Thanks

On Fri, 1 Aug 2003, Heikki Tuuri wrote:

> Asif,
>
> - Original Message -
> From: "Asif Iqbal" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Thursday, July 31, 2003 10:41 PM
> Subject: Mysqld is keep crashing
>
>
> >
> >
> > This is what I bumped into now
> >
> > 030731 15:39:50  mysqld started
> > 030731 15:39:52  InnoDB: Database was not shut down normally.
> > InnoDB: Starting recovery from log files...
> > InnoDB: Starting log scan based on checkpoint at
> > InnoDB: log sequence number 0 3491794745
> > InnoDB: Doing recovery: scanned up to log sequence number 0 3491800490
> > InnoDB: 1 transaction(s) which must be rolled back or cleaned up
> > InnoDB: in total 32 row operations to undo
> > InnoDB: Trx id counter is 0 3532544
> > 030731 15:39:53  InnoDB: Starting an apply batch of log records to the
> > database...
> > InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
> 23 24
> > 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
> 50 51
> > 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
> 77 78
> > 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
> > InnoDB: Apply batch completed
> > InnoDB: Starting rollback of uncommitted transactions
> > InnoDB: Rolling back trx with id 0 3532191, 32 rows to undoInnoDB: Error:
> trying
> > to access page number 2650767236 in space 0
> > InnoDB: which is outside the tablespace bounds.
> > InnoDB: Byte offset 0, len 16384, i/o type 10
> > 030731 15:39:59  InnoDB: Assertion failure in thread 1 in file fil0fil.c
> line
> > 1176
> > 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=402653184
> > read_buffer_size=2093056
> > 030731 15:39:59  mysqld ended
> >
> >
> > Anyone would know how I can fix it
>
> your InnoDB database is now corrupt. That may be a result of mixing ibdata
> files or ib_logfiles. I recall you were trying to modify my.cnf 2 days ago
> and had some problems.
>
> You can try starting with http://www.innodb.com/ibman.html#Forcing_recovery:
>
> set-variable = innodb_force_recovery = 4
>
> and dump your tables and recreate your ibdata files and ib_logfiles.
>
> > I am using my-huge.cnf . I have 4 450Mhz with 4 gig ram
> > --
> > Asif Iqbal
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> http://www.innodb.com
> Transactions, foreign keys, and a hot backup tool for MySQL
> Order MySQL technical support from https://order.mysql.com/
>
>
>
>

-- 
Asif Iqbal
http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08
There's no place like 127.0.0.1


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

Newbie question...memo field

[Brent Elison]

Hi all,

I'm fairly new to MySQL and have the following scenario:

The Table-A below was converted from a Filemaker Pro database.  The color
field was a value list in the FMP database that converted over as a text
field.  The color field contains all the selected values in the value list
(from the FMP database) and looks like the data below (Table-A) when viewed
in the BLOB-editor (MySQL-Front).  So, how do I extract the color values
from the color field in Table-A and put them into a separate table looking
like Table-B?

Sorry for the totally newbie question.  Thanks for any suggestion.

Brent


Table-A
Record  ID  Color

1   001 Black
Pink
White
2   002 Black
3   003 Green
4   AAA Black
White



Table-B
Record  ID  Color

1   001 Black
2   001 Pink
3   001 White
4   002 Black
5   003 Green
6   AAA Black
7   AAA White
-



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

Re: Using query file from console

[Lorenzo Rossi]

Hi,
my help command displays not so many options :(
Thanx a lot, bye.
Lorenzo

Fred van Engen wrote:

Hi,

On Fri, Aug 01, 2003 at 04:55:09PM +0200, Lorenzo Rossi wrote:
 

I need to run a query written in a file from inside mysql console, not 
shell prompt.
Anyone can help me?

   

mysql> help

MySQL commands:
Note that all text commands must be first on line and end with ';'
help(\h)Display this help.
?   (\?)Synonym for `help'.
clear   (\c)Clear command.
connect (\r)Reconnect to the server. Optional arguments are db and host.
edit(\e)Edit command with $EDITOR.
ego (\G)Send command to mysql server, display result vertically.
exit(\q)Exit mysql. Same as quit.
go  (\g)Send command to mysql server.
nopager (\n)Disable pager, print to stdout.
notee   (\t)Don't write into outfile.
pager   (\P)Set PAGER [to_pager]. Print the query results via PAGER.
print   (\p)Print current command.
quit(\q)Quit mysql.
rehash  (\#)Rebuild completion hash.
source  (\.)Execute a SQL script file. Takes a file name as an argument.
status  (\s)Get status information from the server.
tee (\T)Set outfile [to_outfile]. Append everything into given outfile.
use (\u)Use another database. Takes database name as argument.
Connection id: 171359  (Can be used with mysqladmin kill)

mysql>

So it looks like you could try the 'source' command. Copying the query
through your clipboard is another option.
Regards,

Fred.

 

Re: RE: RE: standardized naming system ?

[Adam Fortuno KOVICK]

Jim,

Habit. I look at so many objects I don't know what I would do if they 
weren't prefixed with a type.

I still didn't answer your question on "why should you use them", but 
this is why I do.

Regards,
A$

- Original Message -
From: Jim Smith <[EMAIL PROTECTED]>
Date: Friday, August 1, 2003 10:39 am
Subject: RE: RE: standardized naming system ?

> Wasn't my question.
> 
> Why do you need to label a table as a table?  There is some merit 
> in using
> type prefixes in VB because of its loose typing, but it makes no 
> sense in a
> database context where the types of objects are quite distinct.
> 
> > -Original Message-
> > From: Adam Fortuno KOVICK [EMAIL PROTECTED]
> > Sent: 01 August 2003 15:24
> > To: [EMAIL PROTECTED]
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: RE: standardized naming system ?
> >
> >
> > Jim,
> >
> > Great question!
> >
> > I use the ol'Reddick VBA naming conventions.
> >
> > tbl - table
> > idx - index
> > fld - field
> >
> > You can search them in google, but I'd like to know if MySQL has its
> > own established conventions too.
> >
> > Regards,
> > A$
> >
> > - Original Message -
> > From: Jim Smith <[EMAIL PROTECTED]>
> > Date: Friday, August 1, 2003 10:03 am
> > Subject: RE: standardized naming system ?
> >
> > > >
> > > > Hello list,
> > > >
> > > > is there a common naming system for db objects ?
> > >
> > > Thousands.
> > >
> > > > Like:
> > > >
> > > > 1) Tables: mytable, tblmytable, tbl_mytable
> > > >
> > > > 2) Indices: idx_anindex
> > > >
> > > > 3) Columns: int_somenumber, date_lastupdate
> > > >
> > > > 4) id   for the numerical primary key e.g. table  customers.id
> > > >  and then for referencing foreign keys
> > > > table addresses :  addresses.customer_id  or
> > > > addresses.customer_fk
> > > >
> > > > OK, I know I could name them the way I want but perhaps 
> there is
> > > some> kind of common sense in this regard ?
> > >
> > > Common sense will do, but here is my take on it.
> > >
> > > There are three main objectives - portability, maintainability and
> > > consistency.
> > >
> > > General.
> > >   Use long names. Don't abbreviate unnecessarily, but don't go to
> > > far that
> > > you have
> > >   to rename all your tables if you move to a different DBMS. A
> > max
> > > of 30
> > > chars should fit most DBMSs.
> > >
> > >   Use lower case names, with words separated by underscores '_'.
> > > Some DBMSs
> > > are case
> > >   sensitive, others aren't and some convert all names to upper
> > case
> > > ( this is
> > > an ANSI
> > >   standard feature, I believe). If you use camel case (
> > > studentClassScores),this could become
> > >   STUDENTCLASSSCORES which isn't very readable, whereas
> > > STUDENT_CLASS_SCORESis much better.
> > >
> > >   Don't use reserved words. Most DBMSs allow you to use reserved
> > > words with
> > >   various degrees of effort, but why bother. Also try to avoid
> > > simple names
> > > which
> > >   might be a reserved word in another DBMS.
> > >
> > > Tables.
> > >   Give tables a clear simple name which represents the content.
> > If
> > > it holds
> > > student records, call
> > >   it "students"; course details, "courses" etc.
> > >   Also assign each table a unique 2-4 letter prefix for use in
> > > naming objects
> > > which belong to that table.
> > >
> > > Columns
> > >   Again say what it is. I use the prefix referred to above in all
> > > columnnames, but some people think that
> > >   is a waste of name space.
> > >   e.g std_id, std_surname, std_forename, student_birthdate,
> > crs_name,
> > > crs_tutor_id, etc
> > >
> > > Constraints
> > >   Use the prefix
> > >   Primary Key   std_pk
> > >   Foreign Keys  std_fk_col   ( i.e.
> > _FK_
> > >   Unique  Keys   std_uk_nnwhere nn is a sequence number. Some
> > > people like
> > > std_uk_, but
> > >   if you have a composite
> > > key, that doesn't work.
> > >
> > > Indexes
> > >   Where an index is used to enforce (or instead of) a unique or
> > > primary key
> > > constraint, same name as the constraint.
> > >   Primary Key index   std_pk
> > >   Unique Index  std_uk_nn
> > >   Non-unique indexstd_nu_nn
> > >
> > >   If indexes share the same namespace as constraints, stick an i_
> > > on the
> > > front of the index name.
> > >
> > >
> > >
> > > --
> > > 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]
> 

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

Re: Using query file from console

[Fred van Engen]

Hi,

On Fri, Aug 01, 2003 at 04:55:09PM +0200, Lorenzo Rossi wrote:
> I need to run a query written in a file from inside mysql console, not 
> shell prompt.
> Anyone can help me?
> 

mysql> help

MySQL commands:
Note that all text commands must be first on line and end with ';'
help(\h)Display this help.
?   (\?)Synonym for `help'.
clear   (\c)Clear command.
connect (\r)Reconnect to the server. Optional arguments are db and host.
edit(\e)Edit command with $EDITOR.
ego (\G)Send command to mysql server, display result vertically.
exit(\q)Exit mysql. Same as quit.
go  (\g)Send command to mysql server.
nopager (\n)Disable pager, print to stdout.
notee   (\t)Don't write into outfile.
pager   (\P)Set PAGER [to_pager]. Print the query results via PAGER.
print   (\p)Print current command.
quit(\q)Quit mysql.
rehash  (\#)Rebuild completion hash.
source  (\.)Execute a SQL script file. Takes a file name as an argument.
status  (\s)Get status information from the server.
tee (\T)Set outfile [to_outfile]. Append everything into given outfile.
use (\u)Use another database. Takes database name as argument.

Connection id: 171359  (Can be used with mysqladmin kill)

mysql>


So it looks like you could try the 'source' command. Copying the query
through your clipboard is another option.


Regards,

Fred.

-- 
Fred van Engen  XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC  Almere
fax: +31 36 5462424 The Netherlands

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

Using query file from console

[Lorenzo Rossi]

Hello.
I need to run a query written in a file from inside mysql console, not 
shell prompt.
Anyone can help me?

Thanx

Lorenzo

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

RE: RE: standardized naming system ?

[Jim Smith]

Wasn't my question.

Why do you need to label a table as a table?  There is some merit in using
type prefixes in VB because of its loose typing, but it makes no sense in a
database context where the types of objects are quite distinct.

> -Original Message-
> From: Adam Fortuno KOVICK [mailto:[EMAIL PROTECTED]
> Sent: 01 August 2003 15:24
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: RE: standardized naming system ?
>
>
> Jim,
>
> Great question!
>
> I use the ol'Reddick VBA naming conventions.
>
> tbl - table
> idx - index
> fld - field
>
> You can search them in google, but I'd like to know if MySQL has its
> own established conventions too.
>
> Regards,
> A$
>
> - Original Message -
> From: Jim Smith <[EMAIL PROTECTED]>
> Date: Friday, August 1, 2003 10:03 am
> Subject: RE: standardized naming system ?
>
> > >
> > > Hello list,
> > >
> > > is there a common naming system for db objects ?
> >
> > Thousands.
> >
> > > Like:
> > >
> > > 1) Tables: mytable, tblmytable, tbl_mytable
> > >
> > > 2) Indices: idx_anindex
> > >
> > > 3) Columns: int_somenumber, date_lastupdate
> > >
> > > 4) id   for the numerical primary key e.g. table  customers.id
> > >  and then for referencing foreign keys
> > > table addresses :  addresses.customer_id  or
> > > addresses.customer_fk
> > >
> > > OK, I know I could name them the way I want but perhaps there is
> > some> kind of common sense in this regard ?
> >
> > Common sense will do, but here is my take on it.
> >
> > There are three main objectives - portability, maintainability and
> > consistency.
> >
> > General.
> > Use long names. Don't abbreviate unnecessarily, but don't go to
> > far that
> > you have
> > to rename all your tables if you move to a different DBMS. A
> max
> > of 30
> > chars should fit most DBMSs.
> >
> > Use lower case names, with words separated by underscores '_'.
> > Some DBMSs
> > are case
> > sensitive, others aren't and some convert all names to upper
> case
> > ( this is
> > an ANSI
> > standard feature, I believe). If you use camel case (
> > studentClassScores),this could become
> > STUDENTCLASSSCORES which isn't very readable, whereas
> > STUDENT_CLASS_SCORESis much better.
> >
> > Don't use reserved words. Most DBMSs allow you to use reserved
> > words with
> > various degrees of effort, but why bother. Also try to avoid
> > simple names
> > which
> > might be a reserved word in another DBMS.
> >
> > Tables.
> > Give tables a clear simple name which represents the content.
> If
> > it holds
> > student records, call
> > it "students"; course details, "courses" etc.
> > Also assign each table a unique 2-4 letter prefix for use in
> > naming objects
> > which belong to that table.
> >
> > Columns
> > Again say what it is. I use the prefix referred to above in all
> > columnnames, but some people think that
> > is a waste of name space.
> > e.g std_id, std_surname, std_forename, student_birthdate,
> crs_name,
> > crs_tutor_id, etc
> >
> > Constraints
> > Use the prefix
> > Primary Key   std_pk
> > Foreign Keys  std_fk_col   ( i.e.
> _FK_
> > Unique  Keys   std_uk_nnwhere nn is a sequence number. Some
> > people like
> > std_uk_, but
> >   if you have a composite
> > key, that doesn't work.
> >
> > Indexes
> > Where an index is used to enforce (or instead of) a unique or
> > primary key
> > constraint, same name as the constraint.
> > Primary Key index   std_pk
> > Unique Index  std_uk_nn
> > Non-unique indexstd_nu_nn
> >
> > If indexes share the same namespace as constraints, stick an i_
> > on the
> > front of the index name.
> >
> >
> >
> > --
> > 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: MySQL LOAD zipped DATA from File

[Sergei Golubchik]

Hi!

On Aug 01, Dan Muey wrote:
> 
> > Dear Ladies and Sirs,
> > 
> > can anyone give me a hint please, if it is possible toimport 
> > data data from a zipped File, without unzipping it before. 
> > I'm using MySQL 3.23 on a LINUX System. I guess it is 
> > possible using a 'named pipe' and 'funzip' , but I don't lnow how.

Manual:

`LOAD DATA INFILE' Syntax
...

If you need `LOAD DATA' to read from a pipe, you can use the following
trick:

 mkfifo /mysql/db/x/x
 chmod 666 /mysql/db/x/x
 cat < /dev/tcp/10.1.1.12/4711 > /mysql/db/x/x
 mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x
...

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

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

MySQL startup scripts

[Enrique Sanchez Vela]

Hi,

Would anyone explain me why the startup scripts check
for command line arguments and the contents of
/etc/my.cnf right after setting the env variables such
as basedir, pid-file and so?

if the MySQL code and scripts are expected to be on a
specific hardcoded location (yeah, right we could
specify this at build time) why bother supporting
those variables at all?

shouldn't they run parse the command line and defaults
file first before setting defaults??

btw, I am just courious.

thanks,
enrique.

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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

MYSQL Scalability on SMPs

[Khaled D Elmeleegy]

I am studying the scalability of MYSQL on SMPs on Linux.  I am wondering 
if
any one has performed scalability studies.  If so, I would be interested 
in
a pointer to the results; if not, I am curious if there is interest in
MYSQL's scalability.  Pointers to benchmarks used to study MYSQL would 
also
be appreciated.  Another thing I was wondering is if anyone had experience
or recommendations in choosing the server parameters of MYSQL to tune for
SMP performance.

Khaled

Re: show variables

[Victoria Reznichenko]

"Primaria Falticeni" <[EMAIL PROTECTED]> wrote:
> 
> 1. Is it a way to grow the speed of the replication out of the
> master-connect-retry variable?

What do you mean "speed of replication"? 

> 
> 2. I backup the databases. Assuming that the last relay log file is
> sql-relay-bin.005 and the last bin log file is sql-bin.007, can I simply
> delete the files from sql-relay-bin.001 to sql-relay-bin.004 and from
> sql-bin.001 to sql-bin.006? If not, how can I escape by these logs in an
> elegant way?

You don't need to delete relay logs, SQL thread deletes these log file.
As to binary logs, you can delete them using PURGE MASTER LOGS command:
http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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: RE: standardized naming system ?

[Adam Fortuno KOVICK]

Jim,

Great question!

I use the ol'Reddick VBA naming conventions.

tbl - table
idx - index
fld - field

You can search them in google, but I'd like to know if MySQL has its 
own established conventions too.

Regards,
A$

- Original Message -
From: Jim Smith <[EMAIL PROTECTED]>
Date: Friday, August 1, 2003 10:03 am
Subject: RE: standardized naming system ?

> >
> > Hello list,
> >
> > is there a common naming system for db objects ?
> 
> Thousands.
> 
> > Like:
> >
> > 1) Tables: mytable, tblmytable, tbl_mytable
> >
> > 2) Indices: idx_anindex
> >
> > 3) Columns: int_somenumber, date_lastupdate
> >
> > 4) id   for the numerical primary key e.g. table  customers.id
> >  and then for referencing foreign keys
> > table addresses :  addresses.customer_id  or
> > addresses.customer_fk
> >
> > OK, I know I could name them the way I want but perhaps there is 
> some> kind of common sense in this regard ?
> 
> Common sense will do, but here is my take on it.
> 
> There are three main objectives - portability, maintainability and
> consistency.
> 
> General.
>   Use long names. Don't abbreviate unnecessarily, but don't go to 
> far that
> you have
>   to rename all your tables if you move to a different DBMS. A 
max 
> of 30
> chars should fit most DBMSs.
> 
>   Use lower case names, with words separated by underscores '_'. 
> Some DBMSs
> are case
>   sensitive, others aren't and some convert all names to upper 
case 
> ( this is
> an ANSI
>   standard feature, I believe). If you use camel case ( 
> studentClassScores),this could become
>   STUDENTCLASSSCORES which isn't very readable, whereas 
> STUDENT_CLASS_SCORESis much better.
> 
>   Don't use reserved words. Most DBMSs allow you to use reserved 
> words with
>   various degrees of effort, but why bother. Also try to avoid 
> simple names
> which
>   might be a reserved word in another DBMS.
> 
> Tables.
>   Give tables a clear simple name which represents the content. 
If 
> it holds
> student records, call
>   it "students"; course details, "courses" etc.
>   Also assign each table a unique 2-4 letter prefix for use in 
> naming objects
> which belong to that table.
> 
> Columns
>   Again say what it is. I use the prefix referred to above in all 
> columnnames, but some people think that
>   is a waste of name space.
>   e.g std_id, std_surname, std_forename, student_birthdate, 
crs_name,
> crs_tutor_id, etc
> 
> Constraints
>   Use the prefix
>   Primary Key   std_pk
>   Foreign Keys  std_fk_col   ( i.e. 
_FK_
>   Unique  Keys   std_uk_nnwhere nn is a sequence number. Some 
> people like
> std_uk_, but
>   if you have a composite 
> key, that doesn't work.
> 
> Indexes
>   Where an index is used to enforce (or instead of) a unique or 
> primary key
> constraint, same name as the constraint.
>   Primary Key index   std_pk
>   Unique Index  std_uk_nn
>   Non-unique indexstd_nu_nn
> 
>   If indexes share the same namespace as constraints, stick an i_ 
> on the
> front of the index name.
> 
> 
> 
> -- 
> 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: Newbie question for MySQL

[Adam Fortuno KOVICK]

Its doesn't look like you are starting the process as root. Turn into 
root then start the process. Then exit turn back into you're user 
account and use mysql.

Regards,
A$

- Original Message -
From: Jbo <[EMAIL PROTECTED]>
Date: Thursday, July 31, 2003 10:26 pm
Subject: Newbie question for MySQL

> I installed MySQL 4.0.4-beta from a cdrom that came with the book 
> "Teach yourself PHP, MySQL and Apache"  (a SAMs publication) on a 
> linux 7.2 OS.  I installed the binary distribution and attempted 
> to follow the directions from the included manual.
> 
> When I execute the command:
> .bin/safe_mysqld --user=mysql &
> I see displayed:
> starting mysqld daemon with databases from /usr/local/mysql/data
> mysqld ended
> 
> then I hit a carriage return and see
> [1]+ donebin/safe_mysqld --user=mysql
> 
> And I grep for the process mysql using ps -ef and it is not there.
> 
> I found a file called $hostname.err and it said "Can't find 
> messagefile 'mysql-4.0.4-beta-pc-linux-gnu-
> i686/share/mysql/englishy/errmsg.sys'".  Does this mean I don't 
> have some environment variables set up correctly?  So how do I 
> start debugging this problem?
> 
> thanks
> Joel

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

Re: my.cnf is not available under windows 2000

[Scott Pippin]

>>> "Morten Gulbrandsen" <[EMAIL PROTECTED]> 08/01/03 07:22AM >>>
Hi programmers,

>according to the manual,
>There are two option files with the same function: 
>`C:\my.cnf', and the `my.ini' file in the Windows directory.
>Is it sufficient with only one of the files ?
>I have only my.ini  
>For which purpose is my.cnf , please?
 
It is my understanding that it looks for either file.  As long as you have one file 
you should be ok.

Scott Pippin
[EMAIL PROTECTED] 

Re: mysql_history file?

[Egor Egorov]

[EMAIL PROTECTED] wrote:
> it's like bash_history.. command history (used with up/back key mostly)
> 
> You want to get rid of it for good:  ln -sf /dev/null .mysql_history

or run mysql client with -q option.

> 



-- 
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: How does one get off of this list

[Dan Muey]

> I have tried to remove myself from this list, but the unsubscribe 
> function does not function. How does one get off of this list?

At the risk of sounding condescending

> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]

Is at the bottom of every message.

Also if that doesn't work take a look at the headers.

There should be a bunch of List-*** headers and one will gie you an address to send 
unsubscribe requests to.

HTH

Dan

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

RE: standardized naming system ?

[Jim Smith]

>
> Hello list,
>
> is there a common naming system for db objects ?

Thousands.

> Like:
>
> 1) Tables: mytable, tblmytable, tbl_mytable
>
> 2) Indices: idx_anindex
>
> 3) Columns: int_somenumber, date_lastupdate
>
> 4) id   for the numerical primary key e.g. table  customers.id
>  and then for referencing foreign keys
> table addresses :  addresses.customer_id  or
> addresses.customer_fk
>
> OK, I know I could name them the way I want but perhaps there is some
> kind of common sense in this regard ?

Common sense will do, but here is my take on it.

There are three main objectives - portability, maintainability and
consistency.

General.
Use long names. Don't abbreviate unnecessarily, but don't go to far that
you have
to rename all your tables if you move to a different DBMS. A max of 30
chars should fit most DBMSs.

Use lower case names, with words separated by underscores '_'. Some DBMSs
are case
sensitive, others aren't and some convert all names to upper case ( this is
an ANSI
standard feature, I believe). If you use camel case ( studentClassScores),
this could become
STUDENTCLASSSCORES which isn't very readable, whereas STUDENT_CLASS_SCORES
is much better.

Don't use reserved words. Most DBMSs allow you to use reserved words with
various degrees of effort, but why bother. Also try to avoid simple names
which
might be a reserved word in another DBMS.

Tables.
Give tables a clear simple name which represents the content. If it holds
student records, call
it "students"; course details, "courses" etc.
Also assign each table a unique 2-4 letter prefix for use in naming objects
which belong to that table.

Columns
Again say what it is. I use the prefix referred to above in all column
names, but some people think that
is a waste of name space.
e.g std_id, std_surname, std_forename, student_birthdate, crs_name,
crs_tutor_id, etc

Constraints
Use the prefix
Primary Key   std_pk
Foreign Keys  std_fk_col   ( i.e. _FK_
Unique  Keys   std_uk_nnwhere nn is a sequence number. Some people like
std_uk_, but
   if you have a composite key, that doesn't 
work.

Indexes
Where an index is used to enforce (or instead of) a unique or primary key
constraint, same name as the constraint.
Primary Key index   std_pk
Unique Index  std_uk_nn
Non-unique indexstd_nu_nn

If indexes share the same namespace as constraints, stick an i_ on the
front of the index name.



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

Re: to many connections

[Victoria Reznichenko]

"tuncay bas" <[EMAIL PROTECTED]> wrote:
> reason to "to many connections"
> 

Look at:
http://www.mysql.com/doc/en/Too_many_connections.html


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

Newbie question for MySQL

[Jbo]

I installed MySQL 4.0.4-beta from a cdrom that came with the book "Teach yourself PHP, 
MySQL and Apache"  (a SAMs publication) on a linux 7.2 OS.  I installed the binary 
distribution and attempted to follow the directions from the included manual.

When I execute the command:
.bin/safe_mysqld --user=mysql &
I see displayed:
starting mysqld daemon with databases from /usr/local/mysql/data
mysqld ended

then I hit a carriage return and see
[1]+ donebin/safe_mysqld --user=mysql

And I grep for the process mysql using ps -ef and it is not there.

I found a file called $hostname.err and it said "Can't find messagefile 
'mysql-4.0.4-beta-pc-linux-gnu-i686/share/mysql/englishy/errmsg.sys'".  Does this mean 
I don't have some environment variables set up correctly?  So how do I start debugging 
this problem?

thanks
Joel

my.cnf is not available under windows 2000

[Morten Gulbrandsen]

Hi programmers,

according to the manual,

There are two option files with the same function: 
`C:\my.cnf', and the `my.ini' file in the Windows directory.

Is it sufficient with only one of the files ?

I have only my.ini  

For which purpose is my.cnf , please?

Yours Sincerely

Morten Gulbrandsen



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

Re: ERROR 1005 at line 3: Can't create table '.\company\employee.frm' (errno: 150)

[Victoria Reznichenko]

"Morten Gulbrandsen" <[EMAIL PROTECTED]> wrote:
> USE company;
> DROP TABLE IF EXISTS EMPLOYEE;
> CREATE TABLE  EMPLOYEE
> ( 
> # PK 
> SSN CHAR(9) NOT NULL,
> # FK 
> SUPERSSNCHAR(9),
> DNO INT NOT NULL DEFAULT 1,
> CONSTRAINT EMPPK
>PRIMARY KEY (SSN),
> FOREIGN KEY (SUPERSSN)  REFERENCES EMPLOYEE(SSN),  # THIS runs 
> FOREIGN KEY (DNO)  REFERENCES DEPARTMENT(DNUMBER));  # TYPE=InnoDB
> 
> 
> SHOW CREATE TABLE EMPLOYEE;
> SHOW TABLE STATUS FROM COMPANY LIKE "EMPLOYEE";
> 
> 
> 
> 
> USE company;
> DROP TABLE IF EXISTS EMPLOYEE;
> CREATE TABLE  EMPLOYEE
> ( 
> # PK 
> SSN CHAR(9) NOT NULL,
> # FK 
> SUPERSSNCHAR(9),
> DNO INT NOT NULL DEFAULT 1,
> CONSTRAINT EMPPK
>PRIMARY KEY (SSN),
> FOREIGN KEY (SUPERSSN)  REFERENCES EMPLOYEE(SSN),  # THIS woun't DO !!! 
> FOREIGN KEY (DNO)  REFERENCES DEPARTMENT(DNUMBER))TYPE=InnoDB;  # 
> 
> 
> SHOW CREATE TABLE EMPLOYEE;
> SHOW TABLE STATUS FROM COMPANY LIKE "EMPLOYEE";
> 
> 
> 
> C:\mysql\bin>mysql < Company_00.sql > out.txt
> ERROR 1005 at line 3: Can't create table '.\company\employee.frm'
> (errno: 150)
> 
> 
> What can I DO ?
> 
> 
> 
> mysql> SHOW VARIABLES LIKE "have_innodb";
> +---+---+
> | Variable_name | Value |
> +---+---+
> | have_innodb   | YES   |
> +---+---+
> 1 row in set (0.00 sec)
> 
> mysql>
> 

Error 150 means that you have wrong foreign key definitions. You must create index on 
SUPERSSN, DNO columns:
http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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 LOAD zipped DATA from File

[Dan Muey]

> Dear Ladies and Sirs,
> 
> can anyone give me a hint please, if it is possible toimport 
> data data from a zipped File, without unzipping it before. 
> I'm using MySQL 3.23 on a LINUX System. I guess it is 
> possible using a 'named pipe' and 'funzip' , but I don't lnow how.

I've used Perl and the Archive::Zip module to grab a text file form 
a zip file and use it to populate a database.

I write mine to a temp file but there might be a way to do grab it 
into a variable and process it straght up instead of wiritng to a file but
If it's very big youmay not want to do that.

I'd check our search.cpan.org for Archive::Zip and do it in Perl!
Check out [EMAIL PROTECTED]

HTH
DMuey

> 
> Thank you 
>   Klaus 

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

to many connections

[tuncay bas]

reason to "to many connections"

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

Re: problems with mysql thru command line

[Egor Egorov]

"karl james" <[EMAIL PROTECTED]> wrote:
> Im kinda new at creating databases thru the command line
> When I try to login using.
> Mysql -h hostname -username -p
> 
> I get this message on command line.
> 
> C:\Documents and Settings\Karl James>mysql -h hostname -u username -p
> -p;
> 'mysql' is not a recognized as a internal or exteranl command,
> operable program or batch file.
> 
> Am I in the wrong section on my computer, what am I doing wrong
> Im trying to follow examples in book, but its not working right with me.

Run it from MySQL /bin directory.



-- 
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: Books advice

[Stephen Fromm]

It depends on what you want to know.

I used _Fundamentals of Database Systems_ (Elmasri and Navathe) when I took
a DB course.  It was pretty good, though my impression is that there might
be a "classic" which is better.

The problem with the more MySQL-specific books is that you might not learn
the more abstract aspects of database design.  E.g. some MySQL literature
seems to imply that keys and indexes are the same thing, which is not true.
Also, a book like the one above will strongly emphasize what an "ideal" RDMS
will adhere to, most importantly data integrity, especially referential
integrity.  Earlier editions of MySQL (including the one I'm using) don't
actually enforce foreign key references.  And judging from some things I see
posted in this list, your DB design will be well-served by learning the
fundamentals.

I haven't read Celko's books, but my guess is that they're advanced, not
foundational, and that you'd be better served by first looking at a
foundational book.

-S

- Original Message - 
From: "Fawad Siddiqui" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 31, 2003 8:05 PM
Subject: Books advice


Hi,

I would like to learn about RDBMS, namely mysql of course, but know really
nothing in this area, so have to learn about; RDBMS, SQL and mysql from
scratch.

In this regard, if anyone knows of any books they think would start me off
on the right foot, I would be very grateful.

I have done some searching on Amazon, with the following results.

1.Beginning Databases with MySQL
   by Richard Stones, Neil Matthew

2.MySQL Cookbook
   by Paul DuBois

3.Managing and Using MySQL
  by George Reese, et al

4.Inside Relational Databases
   by Mark Whitehorn, Bill Marklyn

5.Database Design
   by Ryan K. Stephens, Ronald R. Plew

6.The Practical SQL Handbook: Using SQL Variants
   by Judith S. Bowman, et al


Many thanks in advance for all your help.


Fawad


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

RE: standardized naming system ?

[Rob A. Brahier]

Andreas,
I don't know of a standard for naming such things.  My advice is to group
your table names with a prefix if they are related to one another.  Name
fields in such a way that the field's *purpose* is clear to you;
e.g.-"checked_out_by", "checked_out_date", "is_checked_out", etc.  Make sure
foreign keys are named in such a way that 6 months down the road you will
clearly be able to figure out what table they reference.  I've never felt
the need to append the type of the field onto its name; after all, that is
what the describe statement is for.  ;)

-Rob

-Original Message-
From: Andreas [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 31, 2003 10:34 AM
To: [EMAIL PROTECTED]
Subject: standardized naming system ?


Hello list,

is there a common naming system for db objects ?

Like:

1) Tables: mytable, tblmytable, tbl_mytable

2) Indices: idx_anindex

3) Columns: int_somenumber, date_lastupdate

4) id   for the numerical primary key e.g. table  customers.id
 and then for referencing foreign keys
table addresses :  addresses.customer_id  or   addresses.customer_fk

OK, I know I could name them the way I want but perhaps there is some
kind of common sense in this regard ?


... A.




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

RE: Really stupid Question...

[Hayes, Scott]

There is absolutely no difference. Shell prompt is used in *nix because of
the different "shells" you can work in but it all comes down to the same
thing.

Just type what is asked at the DOS prompt and it will work. The only part
that won't work is the file in the mysql/scripts directory as they are
scripts written to be used in a BASH shell in *nix. The perl programs will
work if you have perl installed on you PC.

Hope this helps,

Scott

 

-Original Message-
From: Ola Ogunneye [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 8:28 AM
To: [EMAIL PROTECTED]
Subject: Really stupid Question...


I am a Windows User and very used to the Dos Prompt. Can someone please
tell me what the Shell Prompt is?

What I am asking is the difference between a shell and a dos prompt
vis-a-vis mysql. 

Thank you.

Ola

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

Re: copying a

[Alejandro C. Garrammone]

Hey Enserink, your mail have virus!, please before sending mail please
check your machine!!!.

- Original Message -
From: "W. Enserink" <[EMAIL PROTECTED]>
Sent: Friday, August 01, 2003 8:43 AM
Subject: copying a


> Hi all,
>
>
> Im in need of some tps.
>
> I want to copy a row in a table to a new row in the same table except
> for
> the unique ID. Is there some mysql statement for this?
>
> re


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

Retrieving a BMP image from a binary field in the DB

[Franz Klein]

Hi

Thanks to everybody who told me how to store a BMP image as a BLOB using the
LOAD_FILE.

Now I would like to now if there is a function that does the opposite to
LOAD_FILE?

I have tried using SQLFetch after using a SQLBindCol, but all I get returned
is the first 4 bytes even if the size of the buffer I use is 1024 bytes big
and this size has been passed as a parameter to the SQLBindCol function. I
am using the ODBC API of Microsoft Visual Studio 6 and MyODBC Driver version
3.51. The MySQL version is 4.0.12-nt.

Thanks
Franz Klein

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

Re: Really stupid Question...

[Kevin J Citron]

It's conceptually the same. Both allow you to enter in commands to either
execute code in batch or enter program names to be excuted. I'm not sure
about Win platforms. But, in the *.nix environments you have a choice
of different shells. And, also you can run things in the background and
a host of other capabilties. For your purposes, typing in mysql.
In theory it should be the same. But, I have not used mysql on a Win 
platform.

Ola Ogunneye wrote:

I am a Windows User and very used to the Dos Prompt. Can someone please
tell me what the Shell Prompt is?
What I am asking is the difference between a shell and a dos prompt
vis-a-vis mysql. 

Thank you.

Ola

 

--

Kevin J Citron
Sr. Object Imagineer
Optimized Objects, Inc.
EL Paso, Texas 79930
(915) 565-5777/566-2403


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

RE: Really stupid Question...

[Jack Coxen]

A shell prompt is the Unix/Linux equivalent of the DOS prompt.  Since you
have the choice of several different operating systems shells to work in
(Bourne shell - sh, Korn shell - ksh, C shell - csh, Bourne Again shell -
bash (my favorite), etc) the command line prompts are generically referred
to as shell prompts.

Jack

-Original Message-
From: Ola Ogunneye [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 8:28 AM
To: [EMAIL PROTECTED]
Subject: Really stupid Question...


I am a Windows User and very used to the Dos Prompt. Can someone please
tell me what the Shell Prompt is?

What I am asking is the difference between a shell and a dos prompt
vis-a-vis mysql. 

Thank you.

Ola

InnoDB data file is growing

[Asif Iqbal]

My ibdata1 file is growing. Do I need to worry about that ? I am guessing it
will automatically delete old data to fit the size, correct ? Also how do I
limit the growth size. I tried to put max:2000M , but since my Innodb is
crashing and can't restart mysql I removed the whole my.cnf file all together to
fix the crash. So now I am not sure how I can prevent the growth of InnoDB.

I looked through the InnoDB website but I don't see it addressing InnoDB crash
issue

Thanks

-- 
Asif Iqbal
http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08
There's no place like 127.0.0.1


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

ERROR 1005 at line 3: Can't create table '.\company\employee.frm' (errno: 150)

[Morten Gulbrandsen]

USE company;
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE  EMPLOYEE
( 
# PK 
SSN CHAR(9) NOT NULL,
# FK 
SUPERSSNCHAR(9),
DNO INT NOT NULL DEFAULT 1,
CONSTRAINT EMPPK
PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN)  REFERENCES EMPLOYEE(SSN),  # THIS runs 
FOREIGN KEY (DNO)  REFERENCES DEPARTMENT(DNUMBER));  # TYPE=InnoDB


SHOW CREATE TABLE EMPLOYEE;
SHOW TABLE STATUS FROM COMPANY LIKE "EMPLOYEE";




USE company;
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE  EMPLOYEE
( 
# PK 
SSN CHAR(9) NOT NULL,
# FK 
SUPERSSNCHAR(9),
DNO INT NOT NULL DEFAULT 1,
CONSTRAINT EMPPK
PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN)  REFERENCES EMPLOYEE(SSN),  # THIS woun't DO !!! 
FOREIGN KEY (DNO)  REFERENCES DEPARTMENT(DNUMBER))TYPE=InnoDB;  # 


SHOW CREATE TABLE EMPLOYEE;
SHOW TABLE STATUS FROM COMPANY LIKE "EMPLOYEE";



C:\mysql\bin>mysql < Company_00.sql > out.txt
ERROR 1005 at line 3: Can't create table '.\company\employee.frm'
(errno: 150)


What can I DO ?



mysql> SHOW VARIABLES LIKE "have_innodb";
+---+---+
| Variable_name | Value |
+---+---+
| have_innodb   | YES   |
+---+---+
1 row in set (0.00 sec)

mysql>

Yours Sincerely

Morten Gulbrandsen



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

Really stupid Question...

[Ola Ogunneye]

I am a Windows User and very used to the Dos Prompt. Can someone please
tell me what the Shell Prompt is?

What I am asking is the difference between a shell and a dos prompt
vis-a-vis mysql. 

Thank you.

Ola

recovery from log file

[john]

Hi group,

I have two mysql-Innodb server(4.0.13) with replication enabled in that.

server A > Server B

I have the database dump of server B. In server B, I have enabled the log-
bin, log-slave-update in server B.
In case of database crash, How can I restore the data from log file. I know
that I can restore the major portion from the database dump, but I am
asking about the data update after the dump. How can I restore that from
log files.

thanks



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

Re: Recreating MySQL server gone away Error

[Anubrata Chakrabarti]

Thanks!
I did that and started mysql as : safe_mysqld --interactive_timeout=10
But it DOESN't go away after 10 secs.

On Fri, 1 Aug 2003, Victoria Reznichenko wrote:

> Anubrata Chakrabarti <[EMAIL PROTECTED]> wrote:
> > According to the manual mysql server goes away automatically after 8 hrs
> > (by default). I changed wait_timeout and tried to recreate the problem but
> > no luck! I can access the server even after 8 inactive hrs.
>
> If you use mysql command line client hich runs in the interactive mode, you should 
> change value of interactive_timeout variable.
>
>
>

-- 
-
Anubrata Chakrabarti
Alumnus Software Ltd.
Infinity Tower 2, 2nd Floor
Plot A3, Block GP, Sector 5
Kolkata 700091, India
Phone: +91 33 2357 5626/27/28
Fax: +91 33 2357 5268



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

Re: mysqldump from the 4.1.0-alpha

[Maciej Bobrowski]

>   --opt Same as --add-drop-table --add-locks --all --quick
> --extended-insert --lock-tables --disable-keys.  Enabled
> by default, disable with --skip-opt.

Fine, --skip-opt works great.

Tkanks a lot,
regards,

Maciej

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

Re: mysqldump from the 4.1.0-alpha

[Sergei Golubchik]

Hi!

On Aug 01, Maciej Bobrowski wrote:
> 
> Hi,
> 
> I want to backup some data from a database. In previous releases when I
> backuped a database to a file I got inserts each in one line, separated
> with semicolon. It was very comfortable, because I could very easy edit
> the file, change something and give it to the database. Now after the
> backup the file is very weakly readable. It works of course but it is not
> as convenient as before (in previous releases). Now INSERTS to tables
> looks like:
> 
> INSERT INTO table VALUES (values1), (values2), ... (valuesN);
> 
> I want something like that:
> 
> INSERT INTO table VALUES (values1);
> INSERT INTO table VALUES (values2);
> ...
> INSERT INTO table VALUES (valuesN);
> 
> Of course I can write a script which will do it, but meybe there is an
> option to the mysqldump? I tried --complete-insert  and  omitted the
> --extended-insert option, but it doesn't work.
> 
> How can I do this?

% mysqldump --help
...
  --opt Same as --add-drop-table --add-locks --all --quick
--extended-insert --lock-tables --disable-keys.  Enabled
by default, disable with --skip-opt.

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

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

Re: Recreating MySQL server gone away Error

[Victoria Reznichenko]

Anubrata Chakrabarti <[EMAIL PROTECTED]> wrote:
> According to the manual mysql server goes away automatically after 8 hrs
> (by default). I changed wait_timeout and tried to recreate the problem but
> no luck! I can access the server even after 8 inactive hrs.

If you use mysql command line client hich runs in the interactive mode, you should 
change value of interactive_timeout variable.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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 Getting XP mysql server going

[Egor Egorov]

Mysql <[EMAIL PROTECTED]> wrote:
> Mysql is in default directory
> my.ini - sure looks correct accourding to manual
> 
> Trying to start frist time using command:
> C:\mysql\bin\mysqld --standalone
> 
> Yields this error message:  Can't fine 
> messagefile'C:mysqin\share\english\errmsg.sys'aborting
> 
> Now in C:\mysql\share\english\ there is an errmsg.sys
> I must be missing something: do I create mysqin and put the errmsg.sys 
> in the directory or how do I change the server start up process to 
> lookin in mysql and not mysqin ?

Do you have any
language=C:/mysqin/share/english/
entry in the my.ini?



-- 
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: Error 1005...

[Egor Egorov]

Karam Chand <[EMAIL PROTECTED]> wrote:
> Greetings
> 
> I have MySQL 4.0.14 running on WinXP. 
> 
> Whenever I create a temporary table from an existing
> table that has multiple keys ( the temporary table has
> columns reordered from the original table ) with a
> query like -
> 
> create temporary table if not exists
> `mn`.`sqlyog_23796` ( `entryid` int(6) unsigned NOT
> NULL auto_increment ,
> `caseid` int(6) unsigned NOT NULL DEFAULT '0'  ,
> `litigantid` int(6)
> unsigned NULL  , `litiganttype` char(1) NULL  ,
> `counselid` int(6) unsigned
> NULL  ,  primary key ( entryid ) ,KEY `caseid` ( 
> `caseid` ), FULLTEXT KEY
> `counselid` (  `counselid` ), FULLTEXT KEY
> `litigantid` (  `litigantid` ));
> 
> I get a error - 
> 
> Error No. 1005
> Can't creat table 'C:\WINDOWS\TEMP\#sql794_37_2.frm'
> (errno: 140)
> 
> Why the above query is not working? Is there anything
> wrong with the SQL?

You can't create FULLTEXT index on the INT column.



-- 
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: FULLTEXT Searching

[Victoria Reznichenko]

"Ralph Guzman" <[EMAIL PROTECTED]> wrote:
> I have to do a catalog search through multiple tables and columns for
> product model number, description, and name. I realize that doing
> pattern matching with multiple LIKE statements is slow so I found that
> FULLTEXT searches is a better alternative. 
> 
> I have added a FULLTEXT index to the tables I'm searching, but I get an
> unkown error when I run my query: 
> 
> SELECT p2c.categories_id, p.products_id, pd.products_name,
> p.products_quantity, p.products_image, p.products_bimage,
> p.products_price, p.products_date_added, p.products_last_modified,
> p.products_date_available, p.products_status 
> FROM products p, products_description pd, products_to_categories p2c
> WHERE MATCH(p.products_model,pd.products_name,pd.products_description)
> AGAINST('pumps') 
> AND p.products_id = pd.products_id 
> AND p.products_id = p2c.products_id 
> ORDER BY pd.products_name;
> 

In the MATCH() function you can use one or more columns included in the FULLTEXT 
index, but you doesn't have FULLTEXT index on these three columns from different 
tables.

You can mix columns from different tables if you use full-text search in BOOLEAN MODE 
without full-text index.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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

[Stephan Lukits]

Thank you for pointing that out Kaarel you saved me some time.
I wonder, Patric, if you ever read the source code of the OS you are 
developing for or if you ever read the source of the compilers you use 
because it is most likely that there are bugs and following your 
argumentation: "a feature is there but you have to reimplement it every 
time you write an app because you are resposible as developer for the 
propper working of the app" you'd have to check all this. But you don't 
because you expect these things to work as documentet. If I use a RDBMS 
which supports foreign keys I expect the developers that they have red 
the theory and standards about it and having them implemented. Because 
adding a feature to an app and saying - if any problems occure - "was 
just a trail you have to reimplement it" is imho absurd.

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

UNION or not?

[Gary Broughton]

Hi all

 

I want to provide a list of up to 20 online users on our network of
football forums, but would like to list those live on the current team
first, before "filling" any remainder with those online using a
different team.  I couldn't see any way of getting it all into one
select (which in English 'speak' would be like "order by team 380, then
get the rest" I suppose?), and saw only the UNION function as the
possible solution.

 

All I'm after, if possible, is to know if I'm using the most efficient
method of retrieving the data, and also whether putting the extra "LIMIT
20" outside the UNION would indeed pick up the first 20 records only,
even though there's a potential for 40.

 

(SELECT user_id, username, last_login FROM users

 WHERE unix_timestamp(last_access) > unix_timestamp()-1440

 AND user_id <> '9' AND last_team = '380' ORDER BY last_login LIMIT 20)

UNION

(SELECT user_id, username, last_login FROM users

 WHERE unix_timestamp(last_access) > unix_timestamp()-1440

 AND last_team <> '380' ORDER BY last_login LIMIT 20) 

LIMIT 20

 

Incidentally, the "user_id <> '25'" is only there to prevent display of
the name of the current online user in the list.

 

Many thanks as always

Gary

Re: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?

[Victoria Reznichenko]

"Morten Gulbrandsen" <[EMAIL PROTECTED]> wrote:
> mysql> SHOW VARIABLES LIKE "have_innodb";
> +---+--+
> | Variable_name | Value|
> +---+--+
> | have_innodb   | DISABLED |
> +---+--+
> 1 row in set (0.02 sec)
> 
> mysql>
> 
> ===
> 
> Goodygood, Victoria !!!
> Thank you Sir, 
> 
> This saves my keyboard, 
> 
> now please, how can I enable this fundamental Value have_innodb?

In 3.23 you must specify innodb_data_file_path:
http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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 LOAD zipped DATA from File

[Franz, Fa. PostDirekt MA]

Dear Ladies and Sirs,

can anyone give me a hint please, if it is possible toimport data data from
a zipped File,
without unzipping it before.
I'm using MySQL 3.23 on a LINUX System.
I guess it is possible using a 'named pipe' and 'funzip' , but I don't lnow
how.

Thank you 
Klaus 

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

Re: FULLTEXT Searching

[Cybot]

Ralph Guzman wrote:

I have to do a catalog search through multiple tables and columns for
product model number, description, and name. I realize that doing
pattern matching with multiple LIKE statements is slow so I found that
FULLTEXT searches is a better alternative. 

I have added a FULLTEXT index to the tables I'm searching, but I get an
unkown error when I run my query: 
what means 'unknown error' ? did MySQL tell you 'unknown error'?
or do you get no results? or a timeout? or whatever?
--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update Table By ID Ranges

[Cybot]

How do i use the UPDATE statement to update a range of Primary Key id
numbers. (let say 100 through 1000).
Example,

UPDATE testTable set testField='' WHERE
test_id in (100:1000);
> how about
>
> UPDATE testTable set testField='' WHERE
> test_id >= 100 and test_id <= 1000;
or just:
expr BETWEEN min AND max
http://www.mysql.com/doc/en/Comparison_Operators.html#IDX1156
--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MERGE tables still gamma?

[Victoria Reznichenko]

[EMAIL PROTECTED] wrote:
> The manual page for MERGE tables states the code is in gamma since 3.23.25.
> But it also says you can only SELECT, DELETE, and UPDATE, which isn't true
> since version 4.0.something. 
> 
> Can anyone verify if the MERGE tables is still in gamma? We occassionally
> have diskspace issues on our system, and I'd love to split our invoice table
> into years and use pack on the old years and replace the original table with
> a MERGE.

MERGE tables are stable. Thanks for point, it should be documented better.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

mysqldump from the 4.1.0-alpha

[Maciej Bobrowski]

Hi,

I want to backup some data from a database. In previous releases when I
backuped a database to a file I got inserts each in one line, separated
with semicolon. It was very comfortable, because I could very easy edit
the file, change something and give it to the database. Now after the
backup the file is very weakly readable. It works of course but it is not
as convenient as before (in previous releases). Now INSERTS to tables
looks like:

INSERT INTO table VALUES (values1), (values2), ... (valuesN);

I want something like that:

INSERT INTO table VALUES (values1);
INSERT INTO table VALUES (values2);
...
INSERT INTO table VALUES (valuesN);

Of course I can write a script which will do it, but meybe there is an
option to the mysqldump? I tried --complete-insert  and  omitted the
--extended-insert option, but it doesn't work.

How can I do this?

Regards,
Maciej Bobrowski

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

Recreating MySQL server gone away Error

[Anubrata Chakrabarti]

According to the manual mysql server goes away automatically after 8 hrs
(by default). I changed wait_timeout and tried to recreate the problem but
no luck! I can access the server even after 8 inactive hrs.
Please help.

Anubrata




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

RE: help to structure query without using subselects

[Petre Agenbag]

Thanks Roger, I will give it a bash.

PS, just to get my head around things.

The distinct clause will return only one occurrance of the implicated field.
distinctrow would do the same, but for an entire row. what I am looking for
is something like distinctset(field1,field2,field7) , is this what the
MAX-CONCAT "trick" attempts to do?
You see, what I'm getting at is that I am afraid that even with the
MAX-CONCAT trick it will not "know" to include the "anything_else" from the
distinct row with the highest id, but rather the first occurrance thereof.
And by the way, as *i think* I mentioned, "anything_else" would most
definately NOT be an integer or anything that I would think can be "MAX"'ed.
However, I'm already discrediting/doubting a solution without even testing
it... Geez, I'm a prick hey? ;)

Maybe you can also help me to think about the following:

id  namesomething   else
1   joe testtest2
2   joe testtest3
3   jacktesta   test
4   jacktestb   test1
5   jacktestb   test2



Now I want to query with the following pseudo string:

I first need to get hold of the distinct set of name,something with the
highest row id, and basically remove everything else from the equation.
For instance, should I do

select id,name, something from this_table where else = 'test2'

it should NOT return a value. I know this doesn't make sense, because this
query is valid, and *should* by rights return

1   joe test
5   jacktestb

BUT, that is my dilemma, for what I'm lookinmg for, only

5   jacktestb

should return because that row did in fact match the criteria for the "else
= test2", BUT, it also was indeed the "last" row with the distinct set of
jack and testb.

My problem is having that last condition added to the string that says 
where else = 'test2' BUT only if distinct(name, something) is the row with
the highest id.


When I think of this, it seems that the only way to do this is to first
create a temporary table that only contains the "last" row for that
particular name/something combination, and only once you have that table, to
query it again with a "where else ='test2'", cause then you KNOW,  there can
only be 1 or 0 rows in the result set.

However, to create this temporary table that INCLUDES the else, I am back at
square one.

It's easy enough to get a temp table with only one occurrance of say "name"
OR "something", but to get one with a COMBINATION of "name" AND "something"
is my achiles heal...

Again, sorry if your proposed solution does exactly this ( touch wood, and a
BIG HUGE thank you then), I just want to make sure that you (or anyone else
interested in this thread) understands exactly what the question is (
believe me, after a while thinking of it myself, I sometimes lose the entire
question completely inbetween all the "if" "and" and "or"'s flying through
my head.).

Thanks in any event for your time and interest.




-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 2:49 AM
To: [EMAIL PROTECTED]
Cc: Petre Agenbag
Subject: Re: help to structure query without using subselects


* Petre Agenbag
[...]
> This works:
>
> select distinct name, max(id) as mid from table group by name
>
> but it only returns the name and the highest id for such a name.
>
> if I try this:
>
> select distinct name, max(id) as mid, anything_else  from table group by
> name
>
> it returns the FIRST "anything_else", and not the value in the last row
> for that name ( as I would like it to)

Try the MAX-CONCAT trick:

select distinct name, max(concat(id,'|',anything_else,'|',even_more)) as
name_info
  from table group by name

Then you must programatically split the 'name_info' column back to the
individual fields, or use SUBSTRING, like in the example in the manual:

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html >

HTH,

--
Roger




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