Re: indexing text

[John Hicks]

On Friday 11 June 2004 07:00 am, Fagyal, Csongor wrote:
> Hi,
>
> I have a table that has a few short text fields
> [text(4000), text(1000)] I would like to index. Do
> you think it is a good idea to index them "simply",
> or is it better if I create auxilary fields which
> hold the MD5 for the text fields and index those?
> Would that be faster?
>
> Thank you,
> - Csongor

How are you going to be retrieving them? md5ing them 
means you could only retrieve on an exact match of the 
entire field (i.e. you couldn't use wildcards such as 
"where myfield like 'cson%' ")

Regards,
John

---
John Hicks
Gulfbridge, Inc.
"Putting the Web to work for your business."
http://gulfbridge.com
561-586-8116

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

"not unique table/alias"

[Jack Tanner]

I have two complex subqueries that I need to join. I suspect this
problem is due to using aliases instead of table names, but I don't
know how to work around it (temporary tables?). Please help.
SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
JOIN t1 ON t2.col = t1.col;
Not unique table/alias: 't1'.
SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
WHERE t2.col = t1.col;
Works fine! (But this query is different, because it doesn't get
the rows that are NULL in one of the tables.)
Using MySQL 4.1.2. Thanks in advance for your help.
_
FREE pop-up blocking with the new MSN Toolbar – get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/

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

Access Denied for CREATE TEMPORARY TABLE

[Robert Paulsen]

I must be missing something about "create temporary table". Here are two sql
commands. The first works the second fails:

  CREATE   TABLE mytable  (id int(10) NOT NULL auto_increment, data 
varchar(255), PRIMARY KEY (id) );
  CREATE TEMPORARY TABLE mytable2 (id int(10) NOT NULL auto_increment, data 
varchar(255), PRIMARY KEY (id) );

Prior to issuing the above commands I used the following grant command:

  GRANT ALL ON MYDB.* TO [EMAIL PROTECTED] identified by 'password'

I also tried the following:

  GRANT CREATE TEMPORARY TABLE ON MYDB.* TO [EMAIL PROTECTED] identified by 'password'

but it didn't help.

What am I missing?

-- 
Robert C. Paulsen, Jr.
[EMAIL PROTECTED]

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

RE: Server optimization issue

[Misao]

MySQL is the only process that runs on these boxes. We dedicate the servers
to MySQL since the DBs are so large. One of the possible problems is that
these servers used to be MyISAM DBs, but we upgraded to InnoDB once we moved
from 3.23.33 to 4.0.16. It is hard to tell the MyISAM variables from the
InnoDB ones, so that I can optimize for InnoDB. I know that those prefixed
with InnoDB are for such, but not which others may affect it as well. We
only use MyISAM for the mysql system DB. The build is 4.0.16 from a RedHat
RPM binary. These settings used to work great on the older MySQL, older
hardware, same 4GB RAM, and much higher user access.(It was for the old
MP3.com DBs)

Here is the whole mysqld section in the my.cnf:

[mysqld]
user= mysql
port= 3306
socket  = /home/mysql/mysql.sock
tmpdir  = /usr2/tmp
skip-locking
server-id   = 13
set-variable= back_log=10
set-variable= max_connections=800
set-variable= key_buffer=256M
set-variable= max_allowed_packet=8M
max_allowed_packet  =8M
set-variable= thread_stack=128K
set-variable= record_buffer=4M
set-variable= sort_buffer=64M
#set-variable= wait_timeout=120
skip-slave-start

# Start logging
log-slow-queries = slow.log
#log = query_log
log-slave-updates
log-bin

# This MySQL options file was generated by innobackup.

innodb_data_home_dir=/home/mysql
innodb_data_file_path=ibdata1:2M:autoextend
innodb_log_group_home_dir=/home/mysql/
innodb_log_files_in_group=2
innodb_log_file_size=512M
innodb_buffer_pool_size = 1512M
innodb_additional_mem_pool_size = 20M

And a note: /home is actually on a large filesystem separate from /, so
there is not OS/DB access issues.

-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 11, 2004 7:45 AM
To: [EMAIL PROTECTED]
Subject: Re: Server optimization issue

"Misao" <[EMAIL PROTECTED]> wrote:

Take a deep look at
http://dev.mysql.com/doc/mysql/en/Server_system_variables.html

But ensure that this is MySQL who takes so much memory. Watch other
variables
like key_buffer. MySQL probably should not take that much memory if these
are
the only variables specified: 

> Here are my InnoDB settings in my.cnf:
> innodb_log_files_in_group=2
> innodb_log_file_size=512M
> innodb_buffer_pool_size = 1512M
> innodb_additional_mem_pool_size = 20M

What are the OS and MySQL build? 





-- 
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: RE - Order By Problem

[Michael Stassen]

andy thomas wrote:

Well, this was fixed in the end by this query:
   select substring_index(surname,' ',-1) as r from advisers order by r
which produced the desired result. But we have since had complaints from
individuals wanting their surnames sorted differently! People from Germany
with surnames such as 'von Neumann' like to have this sorted with the V's
and not the N's while people from the Netherlands with 'van den Berg' want
it to be with the B's and not the V's.
We are now redesigning the table to allow records to be displayed in a
specific order chosen by the administrator, rather than trying to do this
automatically by a SELECT statement.
Wow, that sounds like a headache.  Are you adding a sort order column, then? 
 You'll have to renumber everytime you add a row.  That will work, but I'd 
like to point out that my suggested solution easily handles this in either 
of two ways, depending on your sorting philosopphy:

#1: Each user's name is always sorted where he/she expects it.  In this 
case, Max von Neumann expects to be sorted with the Vs, so 'von Neumann' 
goes in the surname column while surname_prefix is set to NULL.  Meanwhile, 
Marije van den Berg expects to show up in the Bs, so 'van den' goes in 
surname_prefix and 'Berg' goes in surname.  In your queries, ORDER BY surname.

#2: Since Max von Neumann expects to be sorted with the Vs, he probably 
expects Marije van den Berg to show up in the Vs, as well.  Meanwhile, as 
Marije van den Berg expects to be in the Bs, she probably expects to find 
Max von Neumann in the Ns.  To accomodate both, always put the prefix in the 
surname_prefix column and the rest in the surname column.  Then create two 
listings:  Max von Neumann sees, based on his preference, a listing with 
"ORDER BY surname_prefix, surname".  Marije van den Berg, on the other hand, 
sees a listing, based on her preference, with "ORDER BY surname.  In other 
words, each list *viewer* chooses whether surname_prefix will be significant 
in the ordering or not.

I'd probably go with option 2, as it enables each user to see the list 
sorted the way he/she expects.

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

RE: Unable to connect to DB

[Bartis, Robert M (Bob)]

Thanks for the pointers. We can connect from the Linux box to my PC so we're convinced 
the network is not an issue, but still cannot connect from my PC to the new MySQL 
server installed on the Linux box. When we monitor the packets coming in we can see 
the request to connect and to MySQL at port 3306 followed by the request for an ICMP 
ping instead of the expected ACK. The ports are enabled in the etc/services file so 
we're at a loss. Is there a setting to allow remote connections in Linux? 

Still lost:-|
Bob

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, June 11, 2004 3:30 PM
To: Bartis, Robert M (Bob)
Cc: [EMAIL PROTECTED]
Subject: Re: Unable to connect to DB



Check to make sure the linux box has its port open (do a MySQL "ping").
>From the windows box, telnet to your linux box on port 3306 (or whatever
you set your linux server to listen on in your my.cnf file) you should see
the version# of the server and a bunch of non-text information.  If that
fails, your linux box is not listening. Change your server's config file to
open a port.

Another issue about mixing platforms if you are trying to access your
MySQL server through the ODBC driver (the most current version I can find
is 3.x) and your server is version 4.1 or better, you will have to
downgrade your password.

Log into the MySQL with admin permissions (root) and run this command:

update mysql.user set password=old_password('') where
user = '';

Also, make sure you have GRANT-ed the appropriate permission for your user
acct to the new tables.

HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   
   
  "Bartis, Robert M
   
  (Bob)"   To:   [EMAIL PROTECTED] 
   
  <[EMAIL PROTECTED]cc:

  m>   Fax to: 
   
   Subject:  Unable to connect to DB   
   
  06/11/2004 03:19 
   
  PM   
   
   
   
   
   




I have been using a local copy of the current production version of MySQL
in a windows environment while we evaluate porting a MS Access front-end to
make use of MySQL. We are ready to go prime-time within my team and as part
of this exercise installed a LINIX version of MySQL on another machine. We
can create a DB, import files etc, etc while on the local LINIX box, but
are unable to connect via a remote machine. Any suggestions on how to debug
this issue? Networking is not an issue as we can ping the machine.

Thanks in advance for you help
Bob

--
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: Possible problem with outer join in 4.1.2

[SGreen]

Rick,

You are most welcome. So sorry for the function confusion, I must be
getting tired if I am confusing ISNULL() with IFNULL(). I sure am glad it's
FRIDAY!!!

About your NOT EXISTS() vs correlated subqueries.  I tried to simulate
acting as the query engine by creating an intermediate results table based
on the JOIN then applying a second query to it using your NOT EXISTS
construct. I couldn't get to your missing records with either EXISTS or NOT
EXISTS. Until I rewrote your query, I  couldn't see any of the records
where the rateplan was 4 (except in the intermediate table) Maybe I will
hit it again on Monday when my mind is fresher.

On the JOINS...  I know exactly how you feel. It was strange for me when I
used Horrical that it DIDN'T have joins. I had the hardest time adjusting
to the (+) notation of partial joining.  Just remember that whichever table
is "in the direction" of the join should return all of its records (unless
you get rid of them in the WHERE clause). Tablea LEFT JOIN Tableb  means
that you will be building an internal intermediate table that contains all
of the columns of both tables that will have all of the rows of Tablea
matched up to all rows of Tableb that satisfy the ON clause. Any rows where
the ON clause is not satisfied will have NULL in every column from the
"other" table.   INNER join is just an "equi-join", if I remember my
terminology correctly (it's been several years since I used Horrical).

The advantage of the JOIN...ON... syntax versus making all of your
comparisons in the WHERE clause is that the engine can pre-select certain
rows from your source tables (using the available indices) based on the
restrictions in the ON clause which will make your temporary internal
working dataset much smaller. This form

SELECT a.*, b.*, c.*
FROM a,b,c
Where b.a_ID = a.ID
and c.somedate > b.somedate
and a.user = 'joe'

could cause  the SQL engine to combine all rows of a with all rows of b and
all rows of c THEN apply the WHERE clause (depending on the optimizer, of
course). If a has 100 rows and b has 100 rows and c has 1000 rows you get
potentially 100x100x1000 or 10,000,000 rows in memory to filter through
while this form

SELECT a.*, b.*, c.*
FROM a
INNER JOIN b
  on a.user = 'joe'
  and b.a_id = a.id
LEFT JOIN c
  on c.somedate > b.somedate

will result in an internal set much smaller than 100x100 joined to a set
potentially smaller than 1000 for a much faster result (notice I didn't
need a WHERE pass through the data?) and much smaller memory footprint. To
me, it's all about clock cycles. Smaller query footprints -> less data
pumped through the system bus (and maybe less swap file usage) -> faster
execution -> happier users.

Have a good weekend!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


   
   
  "Rick Robinson"  
   
  <[EMAIL PROTECTED]>To:   <[EMAIL PROTECTED]> 
 
   cc:   "'Mysql'" <[EMAIL PROTECTED]> 
   
  06/11/2004 04:01 Fax to: 
   
  PM   Subject:  RE: Possible problem with 
outer join in 4.1.2
  Please respond to
   
  rick 
   
   
   
   
   




Hi Shawn-
First, thanks for responding.  You're re-written query works as I would
expect, even using IFNULL instead of COASLESCE (see PS:).

I'm not sure I explained my issue well enough.  Basically, I feel that
outer
joins with correlated sub-queries using not exists are broken in MySQL.

The NOT EXISTS clause is filtering out records that have effective dates
that are 1) later than the transaction date, and 2) have matching records
with earlier effective dates.  (Note: In Oracle, the EXPLAIN shows that
this
is what happens and that the query was not internally rewritten by
Horracle.)  Basically, it's picking out the record that was effective at
the
time of the transaction.

I've always had a fun time with NOT EXISTS...kind of mind-bending at times.
I swear it works like a dream in Horracle (both 8.x and 9.x, haven't tried
it in 10.x).

Thanks again for looking at this.  The left join syntax in MySQL is new to
me and a little chall

using a column value in IN() in a join condition.

[Ken Easson]

Hello,

I am trying to retrieve a cross join of two tables. Table one contains an id column, 
table two contains a column that can list up to three id's from table one.

SELECT dix_ondemand_genre.id, dix_ondemand_shows.genre,
id IN(dix_ondemand_shows.genre) as test
   FROM dix_ondemand_genre CROSS JOIN dix_ondemand_shows ON id 
IN(dix_ondemand_shows.genre);

This select finds only the first value of the IN() column, however i want to return 
every possible result for all genres (Cartesian product):
to illustrate - here is a sample output of what i do get:

id | genre
1 | 1,2,6
3 | 3,6
4 | 4,6

But this s what i want is to have:
1 | 1,2,6
2 | 1,2,6
3 | 3,6
4 | 4,6
5 | NULL
6 | 1,2,6
6 | 3,6
6 | 4,6

actually - what i really want is to be able to group the id column to count how many 
genres are returned for each id, however the group by isn't the problem... I've tried 
quite a number of Join types, all to no avail.

any help would be great.

ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.  


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

Re: Date/Time Difference Calculations

[Dirk Bremer \(NISC\)]

Bingo, Shawn wins the virtual beer(s). I never looked at the unix_timestamp
function. Thanks to everyone else and wishing you virtual beers as well!

Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

[EMAIL PROTECTED]
www.nisc.cc
- Original Message - 
From: <[EMAIL PROTECTED]>
To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, June 11, 2004 14:54
Subject: Re: Date/Time Difference Calculations


>
> I didn't see where these were 4.1+ function so I think it will work. I
> refer you to:
> http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
> http://dev.mysql.com/doc/mysql/en/Cast_Functions.html (if you ever run
into
> 'negative' time differences)
>
> SELECT sec_to_time(unix_timestamp(transfer_end) -
> unix_timestamp(transfer_start)) from queue;
>
> I know it will work for values up to 24 hours different. If sec_to_time is
> using an internal TIME data type value you get just under 840 hours of
> differential.
>
> HTH,
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine


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

RE: Possible problem with outer join in 4.1.2

[Rick Robinson]

Hi Shawn-
First, thanks for responding.  You're re-written query works as I would
expect, even using IFNULL instead of COASLESCE (see PS:).

I'm not sure I explained my issue well enough.  Basically, I feel that outer
joins with correlated sub-queries using not exists are broken in MySQL.

The NOT EXISTS clause is filtering out records that have effective dates
that are 1) later than the transaction date, and 2) have matching records
with earlier effective dates.  (Note: In Oracle, the EXPLAIN shows that this
is what happens and that the query was not internally rewritten by
Horracle.)  Basically, it's picking out the record that was effective at the
time of the transaction.

I've always had a fun time with NOT EXISTS...kind of mind-bending at times.
I swear it works like a dream in Horracle (both 8.x and 9.x, haven't tried
it in 10.x).

Thanks again for looking at this.  The left join syntax in MySQL is new to
me and a little challenging at times - your example helps a lot.  Although I
still feel that there's some sort of problem with the way MySQL is handling
NOT EXISTS for this case.

Best regards,
Rick
PS:
There's no IsNULL() function in MySQL that I know of.  I'm using IFNULL() -
according to the doc:
"IFNULL(expr1,expr2) : If expr1 is not NULL, IFNULL() returns expr1, else it
returns expr2."

Rewritten query (with ifnull):
select
a.id id,
a.trandate trandate,
a.acct acct,
a.rateplan rateplan,
ifnull(a.rangeid, 9) rangeid,
case ifnull(a.rangeid, 9) when 9 then 'null rangeid' else
ifnull(b.descr, 'null descr') end descr
from
transactions a
left join ref_info b
on  a.rateplan = b.rateplan
and a.rangeid = b.rangeid
and a.trandate > b.effdate
left join ref_info c
on  c.rateplan = b.rateplan
and c.rangeid  = b.rangeid
and c.effdate < a.trandate
and c.effdate > b.effdate
where
a.acct = 123
and c.rateplan is null
order by 2 desc, 1 desc
;



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 11, 2004 3:05 PM
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: Re: Possible problem with outer join in 4.1.2


Hi Rick,

First, the MySQL IsNULL() function does not operate like the ORACLE or MS
SQL version. It is merely a test and returns either 1 or 0. You will need to
use COALESCE() to provide a non-null replacement for a null value.

I am not sure what you are trying to accomplish with your EXISTS() clause
but that was what was killing you. I tried it both as EXISTS and as NOT
EXISTS and I didn't get *any* records where rangeid=4.  I can't fully
explain the behavior but I think its failure is linked to the fact that
there are zero b table rows for rangeid of 4.

I rewrote your exclusion as another LEFT JOIN and I have the right number of
rows but I need you to verify the results:

select a.id id,
  a.trandate trandate,
  a.acct acct,
  a.rateplan rateplan,
  a.rangeid rangeid,
  case coalesce(a.rangeid, 9)
  when 9 then 'null rangeid'
  else coalesce(b.descr, 'null descr') end descr from transactions a
left outer join ref_info b
  on a.rateplan = b.rateplan
  and a.rangeid = b.rangeid
  and a.trandate > b.effdate
left join ref_info c
  on c.rateplan = b.rateplan
  and   c.rangeid  = b.rangeid
  and   c.effdate < a.trandate
  and   c.effdate > b.effdate
where a.acct =123
  and c.rateplan is null;

Let me know if this worked as you wanted. I will keep trying to figure out
why that EXISTS clause failed
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




 

  "Rick Robinson"

  <[EMAIL PROTECTED]>To:   "Mysql"
<[EMAIL PROTECTED]>  
   cc:

  06/11/2004 12:48 Fax to:

  PM   Subject:  Possible problem
with outer join in 4.1.2
  Please respond to

  rick

 

 





Hi all-
I'm not certain if this is a bug in MySQL, a bug in Oracle, or a possible
miscoding of my outer join, but I have a scenario in which I've replicated a
table set up and query from an Oracle application and I'm not getting the
same result set.  The following script sets up the representative tables and
data and has the query.

drop table if exists ref_info;
drop table if exists transactions;

create table ref_info
(
rateplan   char(3)  not null,
rangeidint  not null,
effdatedatetime not null,
descr  char(20) not null,
primary key(rateplan, rangeid, effdate)
)
type=myisam
;

create table transactions
(
id int  not null auto_increment,
acct   int  not null,
rateplan   char(3)  not null,
trandate   datetime not null,
rangeidint  null,
primary key(id),
key(acct, rateplan, trandate)
)

Re: Date/Time Difference Calculations

[SGreen]

I didn't see where these were 4.1+ function so I think it will work. I
refer you to:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
http://dev.mysql.com/doc/mysql/en/Cast_Functions.html (if you ever run into
'negative' time differences)

SELECT sec_to_time(unix_timestamp(transfer_end) -
unix_timestamp(transfer_start)) from queue;

I know it will work for values up to 24 hours different. If sec_to_time is
using an internal TIME data type value you get just under 840 hours of
differential.

HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




   
   
  "Dirk Bremer 
   
  \(NISC\)"To:   <[EMAIL PROTECTED]>   
   
  <[EMAIL PROTECTED]cc:

  .cc> Fax to: 
   
   Subject:  Re: Date/Time Difference 
Calculations
  06/11/2004 03:37 
   
  PM   
   
   
   
   
   




Looking at the results further reveals that a numeric subtraction is being
performed on the two datetime fields rather than a date-type subtraction.
Any thoughts on how to perform a date subtraction in version 4.0.18?

Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

[EMAIL PROTECTED]
www.nisc.cc
- Original Message -
From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 11, 2004 14:29
Subject: Re: Date/Time Difference Calculations


> Here is an example using sec_to_time. Note that the results are
inconsistent
> and sometimes inaccurate. It seems that when the difference is less than
one
> minute, the result is correct, when it is over one minute, the result is
> incorrect.
>
> select ident,
> transfer_start,
> transfer_end,
>sec_to_time(transfer_end - transfer_start) as 'Transfer Time1',
>(transfer_end - transfer_start) as 'Transfer Time2'
> from queue
> where ident > 1300
> order by queue_time
> --
>
>
+---+-+-++--

> --+
> | ident | transfer_start  | transfer_end| Transfer Time1 |
> Transfer Time2 |
>
+---+-+-++--

> --+
> |  1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02   |
> 2 |
> |  1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03   |
> 3 |
> |  1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56   |
> 22916 |
> |  1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16   |
> 196 |
> |  1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02   |
> 2 |
> |  1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03   |
> 3 |
> |  1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50   |
> 950 |
> |  1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15   |
> 15 |
> |  1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53  |
> 768893 |
> |  1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32   |
> 92 |
> |  1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00   |
> 0 |
> |  1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14   |
> 6074 |
> |  1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15   |
> 75 |
> |  1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34   |
> 2014 |
> |  1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13   |
> 13 |
>
+---+-+-++--

> --+
> 15 rows in set (0.00 sec)
>
> Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
> USA Central Time Zone
> 636-922-9158 ext. 8652 fax 636-447-4471
>
> [EMAIL PROTECTED]
> www.nisc.cc
>
> > You probably want SEC_TO_TIME:
> >
> > http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
> >
> > SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table
> >
> > 
> > Eamon Daly
> >
> >
> >
> > - Original Message --

Re: Date/Time Difference Calculations

[Dirk Bremer \(NISC\)]

Close, but time_to_sec requires a time argument, not a datetime argument. My
next iteration is:

select ident,
   transfer_start,
   transfer_end,
   sec_to_time(time_to_sec(substring(transfer_end,12,8)) -
time_to_sec(substring(tra
   cast(transfer_end - transfer_start as signed) as 'Transfer Time2'
from queue
where ident > 1300
order by queue_time
--

+---+-+-++--
--+
| ident | transfer_start  | transfer_end| Transfer Time1 |
Transfer Time2 |
+---+-+-++--
--+
|  1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02   |
2 |
|  1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03   |
3 |
|  1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 02:29:16   |
22916 |
|  1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:01:56   |
196 |
|  1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02   |
2 |
|  1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03   |
3 |
|  1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:09:10   |
950 |
|  1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15   |
15 |
|  1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | -23:11:07  |
768893 |
|  1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:00:52   |
92 |
|  1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00   |
0 |
|  1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 00:20:34   |
6074 |
|  1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:00:35   |
75 |
|  1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:20:14   |
2014 |
|  1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13   |
13 |
+---+-+-++--
--+
15 rows in set (0.01 sec)

Which appears to be working correctly (Transfer Time1) except for ident =
1309, which spans a date boundary. So, back to the drawing board to figure
out the date boundary issue.

Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

[EMAIL PROTECTED]
www.nisc.cc
- Original Message - 
From: "gerald_clark" <[EMAIL PROTECTED]>
To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, June 11, 2004 14:50
Subject: Re: Date/Time Difference Calculations


> select
sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start));
>


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

Re: Date/Time Difference Calculations

[gerald_clark]

select  sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start));
Dirk Bremer (NISC) wrote:
Looking at the results further reveals that a numeric subtraction is being
performed on the two datetime fields rather than a date-type subtraction.
Any thoughts on how to perform a date subtraction in version 4.0.18?
Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471
[EMAIL PROTECTED]
www.nisc.cc
- Original Message - 
From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 11, 2004 14:29
Subject: Re: Date/Time Difference Calculations

 

Here is an example using sec_to_time. Note that the results are
   

inconsistent
 

and sometimes inaccurate. It seems that when the difference is less than
   

one
 

minute, the result is correct, when it is over one minute, the result is
incorrect.
select ident,
   transfer_start,
   transfer_end,
  sec_to_time(transfer_end - transfer_start) as 'Transfer Time1',
  (transfer_end - transfer_start) as 'Transfer Time2'
from queue
where ident > 1300
order by queue_time
--
   

+---+-+-++--
 

--+
| ident | transfer_start  | transfer_end| Transfer Time1 |
Transfer Time2 |
   

+---+-+-++--
 

--+
|  1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02   |
2 |
|  1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03   |
3 |
|  1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56   |
22916 |
|  1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16   |
196 |
|  1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02   |
2 |
|  1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03   |
3 |
|  1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50   |
950 |
|  1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15   |
15 |
|  1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53  |
768893 |
|  1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32   |
92 |
|  1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00   |
0 |
|  1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14   |
6074 |
|  1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15   |
75 |
|  1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34   |
2014 |
|  1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13   |
13 |
   

+---+-+-++--
 

--+
15 rows in set (0.00 sec)
Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471
[EMAIL PROTECTED]
www.nisc.cc
   

You probably want SEC_TO_TIME:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table

Eamon Daly

- Original Message - 
From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 11, 2004 1:57 PM
Subject: Date/Time Difference Calculations

 

I'm using MySQL version 4.0.18. I have two datetime columns in the
   

same
 

table, one that represents a start time and the other that represents
   

an
 

end
 

time. I would like to write a query that will show the difference
   

between
   

these two columns in a HH:MM:SS format. The values of the two columns
   

as
 

inserted into the table are never be separated by more than a few
   

hours,
 

but
 

could span a day boundary, i.e the start time could be late one day
   

end
 

the
 

end time early the next day, so the date will have to be taken into
consideration for the calculation. Looking at the docs, it appears
   

that
 

version 4.1.x has a lot more date/time functions, but I'm wondering if
something similar can be arrived at under the version that I am using.
   

 

--
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: Date/Time Difference Calculations

[Dirk Bremer \(NISC\)]

Looking at the results further reveals that a numeric subtraction is being
performed on the two datetime fields rather than a date-type subtraction.
Any thoughts on how to perform a date subtraction in version 4.0.18?

Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

[EMAIL PROTECTED]
www.nisc.cc
- Original Message - 
From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 11, 2004 14:29
Subject: Re: Date/Time Difference Calculations


> Here is an example using sec_to_time. Note that the results are
inconsistent
> and sometimes inaccurate. It seems that when the difference is less than
one
> minute, the result is correct, when it is over one minute, the result is
> incorrect.
>
> select ident,
> transfer_start,
> transfer_end,
>sec_to_time(transfer_end - transfer_start) as 'Transfer Time1',
>(transfer_end - transfer_start) as 'Transfer Time2'
> from queue
> where ident > 1300
> order by queue_time
> --
>
>
+---+-+-++--
> --+
> | ident | transfer_start  | transfer_end| Transfer Time1 |
> Transfer Time2 |
>
+---+-+-++--
> --+
> |  1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02   |
> 2 |
> |  1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03   |
> 3 |
> |  1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56   |
> 22916 |
> |  1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16   |
> 196 |
> |  1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02   |
> 2 |
> |  1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03   |
> 3 |
> |  1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50   |
> 950 |
> |  1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15   |
> 15 |
> |  1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53  |
> 768893 |
> |  1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32   |
> 92 |
> |  1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00   |
> 0 |
> |  1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14   |
> 6074 |
> |  1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15   |
> 75 |
> |  1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34   |
> 2014 |
> |  1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13   |
> 13 |
>
+---+-+-++--
> --+
> 15 rows in set (0.00 sec)
>
> Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
> USA Central Time Zone
> 636-922-9158 ext. 8652 fax 636-447-4471
>
> [EMAIL PROTECTED]
> www.nisc.cc
>
> > You probably want SEC_TO_TIME:
> >
> > http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
> >
> > SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table
> >
> > 
> > Eamon Daly
> >
> >
> >
> > - Original Message - 
> > From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Friday, June 11, 2004 1:57 PM
> > Subject: Date/Time Difference Calculations
> >
> >
> > > I'm using MySQL version 4.0.18. I have two datetime columns in the
same
> > > table, one that represents a start time and the other that represents
an
> > end
> > > time. I would like to write a query that will show the difference
> between
> > > these two columns in a HH:MM:SS format. The values of the two columns
as
> > > inserted into the table are never be separated by more than a few
hours,
> > but
> > > could span a day boundary, i.e the start time could be late one day
end
> > the
> > > end time early the next day, so the date will have to be taken into
> > > consideration for the calculation. Looking at the docs, it appears
that
> > > version 4.1.x has a lot more date/time functions, but I'm wondering if
> > > something similar can be arrived at under the version that I am using.
> >
> >
> >
>
>
> -- 
> 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: Unable to connect to DB

[SGreen]

Check to make sure the linux box has its port open (do a MySQL "ping").
>From the windows box, telnet to your linux box on port 3306 (or whatever
you set your linux server to listen on in your my.cnf file) you should see
the version# of the server and a bunch of non-text information.  If that
fails, your linux box is not listening. Change your server's config file to
open a port.

Another issue about mixing platforms if you are trying to access your
MySQL server through the ODBC driver (the most current version I can find
is 3.x) and your server is version 4.1 or better, you will have to
downgrade your password.

Log into the MySQL with admin permissions (root) and run this command:

update mysql.user set password=old_password('') where
user = '';

Also, make sure you have GRANT-ed the appropriate permission for your user
acct to the new tables.

HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   
   
  "Bartis, Robert M
   
  (Bob)"   To:   [EMAIL PROTECTED] 
   
  <[EMAIL PROTECTED]cc:

  m>   Fax to: 
   
   Subject:  Unable to connect to DB   
   
  06/11/2004 03:19 
   
  PM   
   
   
   
   
   




I have been using a local copy of the current production version of MySQL
in a windows environment while we evaluate porting a MS Access front-end to
make use of MySQL. We are ready to go prime-time within my team and as part
of this exercise installed a LINIX version of MySQL on another machine. We
can create a DB, import files etc, etc while on the local LINIX box, but
are unable to connect via a remote machine. Any suggestions on how to debug
this issue? Networking is not an issue as we can ping the machine.

Thanks in advance for you help
Bob

--
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: Date/Time Difference Calculations

[Dirk Bremer \(NISC\)]

Here is an example using sec_to_time. Note that the results are inconsistent
and sometimes inaccurate. It seems that when the difference is less than one
minute, the result is correct, when it is over one minute, the result is
incorrect.

select ident,
transfer_start,
transfer_end,
   sec_to_time(transfer_end - transfer_start) as 'Transfer Time1',
   (transfer_end - transfer_start) as 'Transfer Time2'
from queue
where ident > 1300
order by queue_time
--

+---+-+-++--
--+
| ident | transfer_start  | transfer_end| Transfer Time1 |
Transfer Time2 |
+---+-+-++--
--+
|  1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02   |
2 |
|  1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03   |
3 |
|  1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56   |
22916 |
|  1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16   |
196 |
|  1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02   |
2 |
|  1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03   |
3 |
|  1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50   |
950 |
|  1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15   |
15 |
|  1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53  |
768893 |
|  1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32   |
92 |
|  1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00   |
0 |
|  1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14   |
6074 |
|  1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15   |
75 |
|  1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34   |
2014 |
|  1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13   |
13 |
+---+-+-++--
--+
15 rows in set (0.00 sec)

Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

[EMAIL PROTECTED]
www.nisc.cc

> You probably want SEC_TO_TIME:
>
> http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
>
> SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table
>
> 
> Eamon Daly
>
>
>
> - Original Message - 
> From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, June 11, 2004 1:57 PM
> Subject: Date/Time Difference Calculations
>
>
> > I'm using MySQL version 4.0.18. I have two datetime columns in the same
> > table, one that represents a start time and the other that represents an
> end
> > time. I would like to write a query that will show the difference
between
> > these two columns in a HH:MM:SS format. The values of the two columns as
> > inserted into the table are never be separated by more than a few hours,
> but
> > could span a day boundary, i.e the start time could be late one day end
> the
> > end time early the next day, so the date will have to be taken into
> > consideration for the calculation. Looking at the docs, it appears that
> > version 4.1.x has a lot more date/time functions, but I'm wondering if
> > something similar can be arrived at under the version that I am using.
>
>
>


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

Re: Date/Time Difference Calculations

[Eamon Daly]

You probably want SEC_TO_TIME:

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

SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table


Eamon Daly



- Original Message - 
From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 11, 2004 1:57 PM
Subject: Date/Time Difference Calculations


> I'm using MySQL version 4.0.18. I have two datetime columns in the same
> table, one that represents a start time and the other that represents an
end
> time. I would like to write a query that will show the difference between
> these two columns in a HH:MM:SS format. The values of the two columns as
> inserted into the table are never be separated by more than a few hours,
but
> could span a day boundary, i.e the start time could be late one day end
the
> end time early the next day, so the date will have to be taken into
> consideration for the calculation. Looking at the docs, it appears that
> version 4.1.x has a lot more date/time functions, but I'm wondering if
> something similar can be arrived at under the version that I am using.


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

Re: Possible problem with outer join in 4.1.2

[SGreen]

Hi Rick,

First, the MySQL IsNULL() function does not operate like the ORACLE or MS
SQL version. It is merely a test and returns either 1 or 0. You will need
to use COALESCE() to provide a non-null replacement for a null value.

I am not sure what you are trying to accomplish with your EXISTS() clause
but that was what was killing you. I tried it both as EXISTS and as NOT
EXISTS and I didn't get *any* records where rangeid=4.  I can't fully
explain the behavior but I think its failure is linked to the fact that
there are zero b table rows for rangeid of 4.

I rewrote your exclusion as another LEFT JOIN and I have the right number
of rows but I need you to verify the results:

select a.id id,
  a.trandate trandate,
  a.acct acct,
  a.rateplan rateplan,
  a.rangeid rangeid,
  case coalesce(a.rangeid, 9)
  when 9 then 'null rangeid'
  else coalesce(b.descr, 'null descr') end descr
from transactions a
left outer join ref_info b
  on a.rateplan = b.rateplan
  and a.rangeid = b.rangeid
  and a.trandate > b.effdate
left join ref_info c
  on c.rateplan = b.rateplan
  and   c.rangeid  = b.rangeid
  and   c.effdate < a.trandate
  and   c.effdate > b.effdate
where a.acct =123
  and c.rateplan is null;

Let me know if this worked as you wanted. I will keep trying to figure out
why that EXISTS clause failed
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




   
   
  "Rick Robinson"  
   
  <[EMAIL PROTECTED]>To:   "Mysql" <[EMAIL PROTECTED]> 
 
   cc: 
   
  06/11/2004 12:48 Fax to: 
   
  PM   Subject:  Possible problem with outer 
join in 4.1.2
  Please respond to
   
  rick 
   
   
   
   
   




Hi all-
I'm not certain if this is a bug in MySQL, a bug in Oracle, or a possible
miscoding of my outer join, but I have a scenario in which I've replicated
a
table set up and query from an Oracle application and I'm not getting the
same result set.  The following script sets up the representative tables
and
data and has the query.

drop table if exists ref_info;
drop table if exists transactions;

create table ref_info
(
rateplan   char(3)  not null,
rangeidint  not null,
effdatedatetime not null,
descr  char(20) not null,
primary key(rateplan, rangeid, effdate)
)
type=myisam
;

create table transactions
(
id int  not null auto_increment,
acct   int  not null,
rateplan   char(3)  not null,
trandate   datetime not null,
rangeidint  null,
primary key(id),
key(acct, rateplan, trandate)
)
type=myisam
;

insert into ref_info values
('aaa',1,'1970-01-01','aaa:1:1970-01-01'),
('aaa',1,'2004-06-01','aaa:1:2004-06-01'),
('aaa',1,'2004-06-03','aaa:1:2004-06-03'),
('aaa',1,'2004-06-05','aaa:1:2004-06-05'),
('aaa',2,'1970-01-01','aaa:2:1970-01-01'),
('aaa',2,'2004-06-01','aaa:2:2004-06-01'),
('aaa',2,'2004-06-03','aaa:2:2004-06-03'),
('aaa',2,'2004-06-05','aaa:2:2004-06-05'),
('aaa',3,'1970-01-01','aaa:3:1970-01-01'),
('aaa',3,'2004-06-01','aaa:3:2004-06-01'),
('aaa',3,'2004-06-03','aaa:3:2004-06-03'),
('aaa',3,'2004-06-05','aaa:3:2004-06-05'),
('aaa',5,'1970-01-01','aaa:5:1970-01-01'),
('aaa',5,'2004-06-01','aaa:5:2004-06-01'),
('aaa',5,'2004-06-03','aaa:5:2004-06-03'),
('aaa',5,'2004-06-05','aaa:5:2004-06-05')
;

insert into transactions values
(null,123,'aaa','2004-05-30',1),
(null,123,'aaa','2004-06-02',1),
(null,123,'aaa','2004-06-04',1),
(null,123,'aaa','2004-06-06',1),
(null,123,'aaa','2004-05-30',2),
(null,123,'aaa','2004-06-02',2),
(null,123,'aaa','2004-06-04',2),
(null,123,'aaa','2004-06-06',2),
(null,123,'aaa','2004-05-30',3),
(null,123,'aaa','2004-06-02',3),
(null,123,'aaa','2004-06-04',3),
(null,123,'aaa','2004-06-06',3),
(null,123,'aaa','2004-05-30',4),
(null,123,'aaa','2004-06-02',4),
(null,123,'aaa','2004-06-04',4),
(null,123,'aaa','2004-06-06',4),
(null,123,'aaa','2004-05-30',5),
(null,123,'aaa','2004-06-02',5),
(null,123,'aaa','2004-06-04',5),
(null,123,'aaa','2004-06-06',5),
(null,123,'aaa','2004-0

Unable to connect to DB

[Bartis, Robert M (Bob)]

I have been using a local copy of the current production version of MySQL in a windows 
environment while we evaluate porting a MS Access front-end to make use of MySQL. We 
are ready to go prime-time within my team and as part of this exercise installed a 
LINIX version of MySQL on another machine. We can create a DB, import files etc, etc 
while on the local LINIX box, but are unable to connect via a remote machine. Any 
suggestions on how to debug this issue? Networking is not an issue as we can ping the 
machine.

Thanks in advance for you help
Bob

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

Date/Time Difference Calculations

[Dirk Bremer \(NISC\)]

I'm using MySQL version 4.0.18. I have two datetime columns in the same
table, one that represents a start time and the other that represents an end
time. I would like to write a query that will show the difference between
these two columns in a HH:MM:SS format. The values of the two columns as
inserted into the table are never be separated by more than a few hours, but
could span a day boundary, i.e the start time could be late one day end the
end time early the next day, so the date will have to be taken into
consideration for the calculation. Looking at the docs, it appears that
version 4.1.x has a lot more date/time functions, but I'm wondering if
something similar can be arrived at under the version that I am using.

Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

[EMAIL PROTECTED]
www.nisc.cc


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

Re: COPY row?

[John Mistler]

Great!  It works.  I did have to eliminate the parentheses in the SELECT
part:

INSERT PRIVILEGES (login, Permission_ID)
SELECT ('newuser', Permission_ID)
FROM PRIVILEGES
WHERE login='user1'

had to be

INSERT PRIVILEGES (login, Permission_ID)
SELECT 'newuser', Permission_ID
FROM PRIVILEGES
WHERE login='user1'

Otherwise I got a "Mistake in you SQL syntax error."

Thanks!

-John

on 6/11/04 6:36 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:

> 
> ABSOLUTELY! If you can create a SELECT statement that contains the NEW data
> for the rows you want (regardless of data types. I only used integer
> columns as an example) you can use that in your INSERT statement.
> 
> Longer example , smaller tables:
> 
> CREATE TABLE USERS (
> login char(8),
> name varchar(20),
> ... (other user information columns)
> )
> 
> CREATE TABLE PERMISSION(
> ID int auto_increment,
> name varchar(20) primary key
> )
> 
> CREATE TABLE PRIVILEGES (
> login char(8),
> Permission_ID int
> )
> 
> I have used a structure like this to provide granular access to various
> applications in the past. The PRIVILEGES table contains one row for each
> type of permission someone has. Examples would be "read public", "read
> confidential", "read secret", "edit public", "edit confidential", edit
> secret", etc.  These were the entries in the PERMISSION TABLE. To grant
> someone permission to do certain things an entry in the PRIVILEGES table
> would look like this
> 
> INSERT PRIVILEGES (login, Permission_ID) VALUES ('user1', 1)
> 
> That would let 'user1' read public documents, get it? One row for each
> level of permission they hold.  OK, now I have been asked to duplicate a
> set of permissions (multiple rows in the PRIVILEGES table) because someone
> new was just appointed as the backup to 'user1'.  I can write a SELECT
> statement that looks like the rows I want to see like this:
> 
> SELECT ('newuser', Permission_ID)
> FROM PRIVILEGES
> WHERE login='user1'
> 
> That shows me all of the privileges that 'user1' had but I substituted the
> name of 'new user' as a constant. In reality I could have used any formula
> or a column from another table (by JOIN-ing that table to my FROM clause)
> or any combination of data to create what ever new value I wanted to see in
> that first column. Understand? You can build your results any way you want.
> 
> Adding the results of that query to my privileges table is as simple as
> 
> INSERT PRIVILEGES (login, Permission_ID)
> SELECT ('newuser', Permission_ID)
> FROM PRIVILEGES
> WHERE login='user1'
> 
> The secret to making this work right and NOT screwing up your tables is to
> get the SELECT statement correct *first* then prepend the INSERT clause to
> it so that those result rows end up as new rows in your table. Your
> destination table has an auto-incrementing ID column. You should not insert
> values to that column (yes, you can under certain circumstances but this is
> not one of them) so DO NOT include it in either the INSERT clause or the
> SELECT clause.
> 
> Did this help or make it worse?
> Respectfully,
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> 
> 
> John Mistler 
> <[EMAIL PROTECTED]To:   <[EMAIL PROTECTED]>
> phia.net>cc:
> Fax to:  
> 06/10/2004 11:12 Subject:  Re: COPY row?
> PM   
> 
> 
> 
> 
> 
> 
> This ALMOST does it for me, except for the update part.  In your example,
> you simply add a number to the value of the column i.e. SELECT (col1 + 1 .
> .
> .) for the update.  In my situation, the column to be updated is a string.
> So I need to replace the string value in the VARCHAR column with a new
> string.  Is there way to do this?
> 
> Thanks,
> 
> John
> 
> on 6/10/04 12:57 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
> 
>> 
>> Yes! but you will have to do some typing
>> 
>> Just use the INSERT ... SELECT command. You do NOT want list the PK
> column
>> in your statements (so that it will autoincrement) so you will have to
> type
>> out the rest of the column names. With a table that looks like:
>> 
>> CREATE TABLE testme (
>> id int auto_increment primary key,
>> col1 int,
>> col2 int,
>> ...
>> col37 int
>> )
>> 
>> You would use a statement like:
>> INSERT testme (col1, col2, ..., col37)
>> SELECT col1, col2, ... , col37
>> FROM testme
>> WHERE 
>> 
>> Whatever rows the WHERE clause matched would be added to the table
> creating
>> your duplicate rows. Because you DID NOT list the autoincrement column,
> all
>> of those new rows end up with new numbers.
>> 
>> Now, if you know what changes to you want to make at the time of the
>> copying, you can define those changed in the SELECT statement and do it
> all
>> at once.
>> 
>> SELECT (col1 + 1, col2 +1, col3, ...
>> 
>> That would give you incremented numbers for col1 and col 2 but the rest
> of
>> the fields would be the same, get it?
>> 
>> HTH,
>> Shawn Green
>> Database Administrator
>> Unimin Corporation - Spruce Pine
>> 
>>

Possible problem with outer join in 4.1.2

[Rick Robinson]

Hi all-
I'm not certain if this is a bug in MySQL, a bug in Oracle, or a possible
miscoding of my outer join, but I have a scenario in which I've replicated a
table set up and query from an Oracle application and I'm not getting the
same result set.  The following script sets up the representative tables and
data and has the query.

drop table if exists ref_info;
drop table if exists transactions;

create table ref_info
(
rateplan   char(3)  not null,
rangeidint  not null,
effdatedatetime not null,
descr  char(20) not null,
primary key(rateplan, rangeid, effdate)
)
type=myisam
;

create table transactions
(
id int  not null auto_increment,
acct   int  not null,
rateplan   char(3)  not null,
trandate   datetime not null,
rangeidint  null,
primary key(id),
key(acct, rateplan, trandate)
)
type=myisam
;

insert into ref_info values
('aaa',1,'1970-01-01','aaa:1:1970-01-01'),
('aaa',1,'2004-06-01','aaa:1:2004-06-01'),
('aaa',1,'2004-06-03','aaa:1:2004-06-03'),
('aaa',1,'2004-06-05','aaa:1:2004-06-05'),
('aaa',2,'1970-01-01','aaa:2:1970-01-01'),
('aaa',2,'2004-06-01','aaa:2:2004-06-01'),
('aaa',2,'2004-06-03','aaa:2:2004-06-03'),
('aaa',2,'2004-06-05','aaa:2:2004-06-05'),
('aaa',3,'1970-01-01','aaa:3:1970-01-01'),
('aaa',3,'2004-06-01','aaa:3:2004-06-01'),
('aaa',3,'2004-06-03','aaa:3:2004-06-03'),
('aaa',3,'2004-06-05','aaa:3:2004-06-05'),
('aaa',5,'1970-01-01','aaa:5:1970-01-01'),
('aaa',5,'2004-06-01','aaa:5:2004-06-01'),
('aaa',5,'2004-06-03','aaa:5:2004-06-03'),
('aaa',5,'2004-06-05','aaa:5:2004-06-05')
;

insert into transactions values
(null,123,'aaa','2004-05-30',1),
(null,123,'aaa','2004-06-02',1),
(null,123,'aaa','2004-06-04',1),
(null,123,'aaa','2004-06-06',1),
(null,123,'aaa','2004-05-30',2),
(null,123,'aaa','2004-06-02',2),
(null,123,'aaa','2004-06-04',2),
(null,123,'aaa','2004-06-06',2),
(null,123,'aaa','2004-05-30',3),
(null,123,'aaa','2004-06-02',3),
(null,123,'aaa','2004-06-04',3),
(null,123,'aaa','2004-06-06',3),
(null,123,'aaa','2004-05-30',4),
(null,123,'aaa','2004-06-02',4),
(null,123,'aaa','2004-06-04',4),
(null,123,'aaa','2004-06-06',4),
(null,123,'aaa','2004-05-30',5),
(null,123,'aaa','2004-06-02',5),
(null,123,'aaa','2004-06-04',5),
(null,123,'aaa','2004-06-06',5),
(null,123,'aaa','2004-05-30',null),
(null,123,'aaa','2004-06-02',null),
(null,123,'aaa','2004-06-04',null),
(null,123,'aaa','2004-06-06',null)
;

select count(*) from transactions
;

select
a.id id,
a.trandate trandate,
a.acct acct,
a.rateplan rateplan,
ifnull(a.rangeid, 9) rangeid,
case ifnull(a.rangeid, 9) when 9 then 'null rangeid' else
ifnull(b.descr, 'null descr') end descr
from
transactions a left outer join ref_info b
on a.rateplan = b.rateplan and a.rangeid = b.rangeid and a.trandate >
b.effdate
where
a.acct = 123
and not exists
(select 1 from ref_info c
 where c.rateplan = b.rateplan
 and   c.rangeid  = b.rangeid
 and   c.effdate < a.trandate
 and   c.effdate > b.effdate
)
order by 2 desc, 1 desc
;


On the nifty outer join query, I expect to get 24 rows back, but only get
16; all rows with either a null a.rangeid or a.rangeid=4 are missing.  This
(effectively) same query works in Oracle and returns all data.  (as FYI, the
query is trying to get back descriptions that are effective dated - however,
some descriptions may not be there for various reasons).

So, am I writing this query incorrectly for MySQL?  Is this a possible bug?
Thanks for any help.  But please skip suggestions on trying to make changes
to the schema definition.

Running MySQL 4.1.2 binary on both Solaris 2.9 and WinXP.

Best regards,
Rick

Plugable Authentication Module

[Aysun Alay]

Hello,

I'm trying to find out if mysql 4.0.20 support PAM interface?  I find a
pam_mysql utility but there is nothing in the documentation regarding to
pam.

Does anybody has any experince use plugin to replace authentication in
mysql?

Thanks
Aysun

Re: Help with apostrophe and FTS

[Pete Harlan]

On Fri, Jun 11, 2004 at 03:34:14PM +0300, Egor Egorov wrote:
> "Andrea Gangini" <[EMAIL PROTECTED]> wrote:
> 
> > 
> > Well yes, it's an option. I really need this functionality.  But
> > on mysql site, under source downloads, there's this warning: " For
> > maximum stability and performance, we recommend that you use the
> > binaries we provide. "
> > 
> > Is it really true?
> 
> Absolutely. 
> 
> There are too many ways to build MySQL so that it will not work well... buggy
> compilers, thread stack size, etc.  Especially this applies to Linux, because
> MySQL build is statically linked with a bit patched glibc version. And glibc
> compilation by itself is tricky. 

While I'm sure this is true, for the last 6+ years we've essentially
always compiled our own MySQLs under Debian and never had a problem
that I wasn't able to reproduce with the precompiled version.

[I say "essentially always" because we did use a precompiled version
for a while once, but it had a bug that went away when we compiled it
ourselves.  That was years ago and was a rare case where the
precompiled version did have a problem with its static libs that was
disovered by MySQL and fixed shortly thereafter.]

I'm not saying this to contradict the good folks at MySQL or to
disparage their fine binary builds, only to say that their (and
Debian's) software is so good that in my experience you shouldn't be
*that* worried about compiling it yourself.  (Of course, if you have
problems and expect any help with it, paying for support sounds like a
good idea.)

--Pete

> So if you need rock stability on Linux and still need to have a
> custom built binary you can subscribe to "Primary support" or "Login
> installation" packages.  Both include custom binaries build. Click
> on the link below to learn more.
> 
> 
> 
> 
> -- 
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
><___/   www.mysql.com
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

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

Re: Server optimization issue

[Ben Ricker]

A few pointers:

Almost every time, the issue is queries that need optimization. Figure out
which queries are happening at the slow times and look those over
carefully. I cannot count how many times I was asked to throw hardware at
an issue related to MySQL which was really related to bad queries or full
table scans that an index fixed immediately.

I would not worry about swap; remember ALL apps and the System use swap.
You are not having RAM issues, so do not worry about the RAM. Note the
buffer number: that is data that can be cleared out of memory if the system
requires it.  Let your system worry about the swap and RAM allocation.
Looks like you are using a Unix type OS, try "top", a great little tool
that will show you listings of processes real time with the heap info. But
I would worry more about what the queries are doing.

Good luck.

Ben Ricker


--

Ben Ricker
Web Administrator
Mastercard International, Inc.
904 North Third
(636) 722-4697


   
 
  "Misao"  
 
  <[EMAIL PROTECTED]To:   <[EMAIL PROTECTED]>  
  
  akeasy.net>  cc:   (bcc: Ben 
Ricker/STL/MASTERCARD)   
   Subject:  Server optimization issue 
 
  06/10/2004 12:27 
 
  PM   
 
   
 
   
 




I have 4 MySQL 4.0.16 servers, all with Dual 3ghz Xeons and 4GB of RAM.
They use InnoDB for all tables, and the ibdata file is 70GB.
The DBs seem to be a little slow, and the darn thing is always using a huge
chunk of swap. I've tried increasing and decreasing what it's allowed to
use, but this seems to be the best working settings.

Here are my InnoDB settings in my.cnf:
innodb_log_files_in_group=2
innodb_log_file_size=512M
innodb_buffer_pool_size = 1512M
innodb_additional_mem_pool_size = 20M

Can any optimizations be done to this, or do I just need to get more RAM
for
these servers? I believe I can go up to 6GB of RAM, but it requires an OS
upgrade to handle anything over 4GB. I know that the biggest DB in that
chunk is about 20GB itself, with around 5-10GB in it's biggest table.


Memory status:

Mem:  3943852K av, 3938184K used,5668K free,   0K shrd,  209456K
buff
Swap: 2096440K av, 1895456K used,  200984K free 1921172K
cached

Any help would be appreciated, this seemed easier to tweak when it was a
MyISAM server.


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






-
CONFIDENTIALITY NOTICE
This e-mail message and any attachments are only for the use of the intended recipient 
and may contain information that is privileged, confidential or exempt from disclosure 
under applicable law.  If you are not the intended recipient, any disclosure, 
distribution or other use of this e-mail message or attachments is prohibited.  If you 
have received this e-mail message in error, please delete and notify the sender 
immediately. Thank you.


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

"MERGE Table Problem"

[Michael Arndt]

Hello *

reproducable Problem:

Content of UNION: 
logs_20040608,logs_20040609,logs_20040611,logs_20040612,logs_20040613,logs_20040614, 
logs_20040615
results: 0E0
DBD::mysql::db do failed: Can't open file: '#sql-13c1_12.MRG'. (errno: 144) at 
/usr/local/sbin/new_MERGE_table.pl line 276.
Unable to ALTER TABLE logs  UNION=( 
logs_20040608,logs_20040609,logs_20040611,logs_20040612,logs_20040613,logs_20040614, 
logs_20040615 ) INSERT_METHOD=LAST: !Can't open file: '#sql-13c1_12.MRG'. (errno: 144)

Can anyone see a conceptional misunderstandig, i make ?

in pseudocode  do the following using perl dbi:

1. FLUSH TABLES
# take out all "old tables from merge in order to compress the most recent
2. ALTER TABLE logs UNION (log_"today) INSERT_METHOD=LAST;
3. FLUSH TABLES; # make mysql use the new merge table
3. mysiampack / mysiamcheck (log_yesterday)
4. FLUSH TABLES # just in case
5. ALTER TABLE logs UNION ( last_tenlogs, log_today) INSERT_METHOD=LAST;
<- here the problem arises, i assume triggered by "yesterdays table" in the list of 
last_tenlogs

>@commands  = ( "FLUSH TABLES", "ALTER TABLE $LOGTABLE  UNION=( $COMMA_TABLE_SET 
>$newTABLE ) INSERT_METHOD=LAST");
>for ( @commands){
>$results=$dbh->do($_) or die "Unable to $_: !" . $dbh->errstr . "\n";

(COMMA_TABLE_SET is a list of commaseparated table names, ending with a comma)


TIA
Micha

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

OS X Version < 4.1 Multithreaded?

[John May]

I've heard some recent rumblings that MySQL before 4.1 wasn't 
multithreaded on OS X.  However, in actual usage I see every 
indication that is IS.

Does anyone have a definitive answer either way?  Thanks!
- John
--
---
John May : President  
Point In Space Internet Solutions [EMAIL PROTECTED]
 LPA Corporate Partner / FSA Associate / ACN Member
  Professional Lasso / PHP / MySQL / FileMaker Pro Hosting
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Adding replica server in a mixed MyISAM/InnoDB multi-master env?

[Tabor J. Wells]

On Fri, Jun 11, 2004 at 03:38:05PM +0300,
Egor Egorov <[EMAIL PROTECTED]> is thought to have said:

> "Tabor J. Wells" <[EMAIL PROTECTED]> wrote:
> 
> 
> > Is it safe to just shutdown ServerB, copy all of the mysql data dir (including
> > the ibdata files) to ServerC, restart ServerB, and then change the my.cnf of
> > ServerC to set a new server-id, master-host, replicate-do-db entries and start 
> > ServerC?
> 
> You better dump data using mysqldump.  Don't forget the --opt option.

Ugh. Well that leads to more questions:

1) How long will it take to restore this data (60ish gb) then?
2) If I use mysqldump -opt how do I handle the case where by the time I'm
dumping db 20 of 23, db #1-20 may have changed? If they've changed then how
can I be sure that that when replication starts those changes are done? Or
will mysqldump -opt --all-databases actually lock all of them at once and
unlock them all at the end?
3) Are you absolutely sure I can't just copy the files if mysql isn't
running on ServerB? :)

-- 

Tabor J. Wells [EMAIL PROTECTED]
Fsck It! Just another victim of the ambient morality

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

Re: Using IF

[SGreen]

You can do this as a UNION statement or the long way. This is a UNION
example:

select rac.name as race, ch.*
from characters as ch
inner join races as rac
  on rac.raceID = ch.raceID
inner join entityLocation el
  on el.entityID = ch.characterID
  and el.visibility <=60
  and el.entityType =1
  AND el.containerType =6
  AND el.containerID =75402
where ch.handle<>'Khan'
UNION
select rac.name as race, npc.*
from npc as npc
inner join races as rac
  on rac.raceID = npc.raceID
inner join entityLocation el
  on el.entityID = npc.characterID
  and el.visibility <=60
  and el.entityType=10
  AND el.containerType =6
  AND el.containerID =75402

The long way is to do an individual IF( , , ) for each and every column you
need to return and not use the * notation.

One other problem with your IF statement was that you aliased *ALL* of
those columns being returned as 'charData'. That's not good under any
circumstance and would have failed you even if the * format were
acceptable.

Your,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


   
   
  "Keith"  
   
  <[EMAIL PROTECTED]To:   <[EMAIL PROTECTED]>  

  om>  cc: 
   
   Fax to: 
   
  06/11/2004 01:56 Subject:  Using IF  
   
  AM   
   
   
   
   
   




g'day,

Am having a bit of a problem with using IF. This is the error message I
get:

-
SELECT rac.name AS race,
IF (

el.entityType =1, ch. * , npc. *
)charData
FROM characters AS ch, entityLocation AS el, races AS rac, npc AS npc
WHERE el.entityType
IN ( 1, 10 ) AND el.containerType =6 AND el.containerID =75402 AND
IF (
el.entityType =1, el.entityID = ch.characterID AND el.visibility <=60 AND
ch.raceID = rac.raceID AND ch.handle <> 'Khan', el.entityID = npc.npcID AND
el.visibility <=60 AND npc.raceID = rac.raceID
)
LIMIT 0 , 30

MySQL said:


#1064 - You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'*, npc. *  )charData
FROM characters AS ch, entityLocation AS
-

What I want to do is select everything from characters if the el.entityType
is 1, else I want to select everything from npc table.



Cheers,
Keith






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

Query on Projects Information Grouped by Month

[shaun thornburgh]

Hi,
I have two tables in my database. One that holds information on bookings on 
varous projects for a scheduling system, and the other holds project 
informtaion.

Is it possible to produce a report that lists total bookings by project a 
month with one query i.e.

Jan  Feb  Mar  Apr  May  Jun  Jul
Project 1   6 7 3 0  3 43
Project 2   5 4 5 7 1251
Project 3   8 7 1 9  214   1
Thanks for your help
_
Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query Help

[Andrew Dixon]

Excellent, thanks for that. I have used the first way as the second way gave
me different results (lower record count) from what I was getting with it
via a server side script. The first way provide me with the same record
count. Thanks.

Best regards

>>> Andrew Dixon

-Original Message-
From: Dobromir Velev [mailto:[EMAIL PROTECTED] 
Sent: 11 June 2004 12:58
To: Andrew Dixon - MSO.net; [EMAIL PROTECTED]
Subject: Re: Query Help

Hi,

You could use either something like this
SELECT  gallery_id, gallery_name
FROM galleries g 
WHERE   keywords rlike '(^|,)$keyword_id(,|$)';

or 

SELECT  gallery_id, gallery_name
FROM galleries g 
WHERE   $keyword_id in (keywords);


and replace the $keyword_id with the id of the keyword you are looking for. 

Personally I would prefer the first option and put an index on the keywords
field but you should check for yourself which query will work faster.


-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

On Friday 11 June 2004 14:22, Andrew Dixon - MSO.net wrote:
> Hi Everyone.
>
> I have the following a table with a varchar column that contains a comma
> delimited list of id's from another table that relates the item keywords
in
> the other table.
>
> The table keywords contains
>
> keyword_id (int/auto increment/primary key)
> Keyword (varchar/normal key)
>
> The galleries table contains:
>
> gallery_id (int/auto increment/primary key)
> gallery_name (varchar)
> keywords (varchar)
>
> I didn't design the database and I know it is not a good design, but I'm
> stuck with it and I need a query to get the gallery_id when I have a
> certain keyword_id
>
> For example:
>
> gallery_id | gallery_name | keywords
> 1  | test | 1,2,3,4
> 2  | test2| 3,4,5,6
>
> And I won't to get all the galleries with where the have the keywords 2,
> which in this case would be record 1 or keyword 4 which would be both
> record.
>
> SELECTgallery_id, gallery_name
> FROM  galleries
> WHERE keywords 
>
> Hope that makes sense, thanks in advanced.
>
> Best Regards,
>
> Andrew Dixon.





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

Re: how mysql sends its data to clients.

[Craig Harding]

I do have ssl compiled in but I just want to compare the ssl connection 
with the regular connection to make sure that I'm actually encrypting 
the data. call me paranoid.

thanks,
craig.
Egor Egorov wrote:
Craig Harding <[EMAIL PROTECTED]> wrote:
MySQL protocol is a binary protocol. Still it's not encrypted and 
data could be sniffed. 

Use MySQL-Max and SSL connections to encrypt data in protocol.
 

I'm wondering how mysql actually sends its data to a mysql client? Is it 
binary data or plain text or encrypted with some general function? When 
I use ethereal to capture the packets all I can see is anything but 
plain text.
   


 

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

Re: Maximum number of simulatneous connections

[Egor Egorov]

venkata ramana <[EMAIL PROTECTED]> wrote:

>   What is the maximum number of simulataneous connections
> that can exist for MySQL. Can we change this limit? If yes please tell
> me how to do this. I am using MySQL4.1 in Linux.

You can tweak max_connections variable, but on Linux the limit is around 1000
connection due to some system limitations. To achieve more connections it 
should be rebuilt. 





-- 
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: Bug in 4.0.20

[Egor Egorov]

Wendell Dingus <[EMAIL PROTECTED]> wrote:

> RedHat Enterprise 3WS, fully up2date. MySQL binary RPMs for AMD64 won't even
> start for me, what were they built on?. I installed the .src.rpm and built one
> myself (-bb --target amd64). Installed that and all seems well.

This seems strange. Can you provide me with the details (mailto:[EMAIL PROTECTED], 
please)?
We will investigate and possibly fix the AMD build. 

And, of course, to eliminate bugs, it's better to run MySQL prebuilt binaries on Linux.





-- 
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: Column's DataType -- TEXT vs BLOB...

[Egor Egorov]

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

> I also have another table that use 4 columns of 800 characters along
> with 5 columns that use 250 characters.  I'm thinking of using TEXT for
> 9 of those columns.

If you don't plan to store pure binary data in these fields, choose the TEXT
type. 





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




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

Re: Server optimization issue

[Egor Egorov]

"Misao" <[EMAIL PROTECTED]> wrote:

Take a deep look at http://dev.mysql.com/doc/mysql/en/Server_system_variables.html

But ensure that this is MySQL who takes so much memory. Watch other variables
like key_buffer. MySQL probably should not take that much memory if these are
the only variables specified: 

> Here are my InnoDB settings in my.cnf:
> innodb_log_files_in_group=2
> innodb_log_file_size=512M
> innodb_buffer_pool_size = 1512M
> innodb_additional_mem_pool_size = 20M

What are the OS and MySQL build? 





-- 
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: Adding replica server in a mixed MyISAM/InnoDB multi-master env?

[Egor Egorov]

"Tabor J. Wells" <[EMAIL PROTECTED]> wrote:


> Is it safe to just shutdown ServerB, copy all of the mysql data dir (including
> the ibdata files) to ServerC, restart ServerB, and then change the my.cnf of
> ServerC to set a new server-id, master-host, replicate-do-db entries and start 
> ServerC?

You better dump data using mysqldump.  Don't forget the --opt 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: Corrupted .MYI file

[Egor Egorov]

There are too many reasons for that. Yes, users who press "reset" button, power 
outage, 
buggy hardward or OS, even corrupted MySQL builds may cause this to happen. 

Example: we often have this kind of troubles on FreeBSD 5.x on high load with big 
databases
(tens of gigs). This happens because of damaged threads in FreeBSD, not because of 
MySQL.





-- 
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 mysql sends its data to clients.

[Egor Egorov]

Craig Harding <[EMAIL PROTECTED]> wrote:

MySQL protocol is a binary protocol. Still it's not encrypted and 
data could be sniffed. 

Use MySQL-Max and SSL connections to encrypt data in protocol.

> I'm wondering how mysql actually sends its data to a mysql client? Is it 
> binary data or plain text or encrypted with some general function? When 
> I use ethereal to capture the packets all I can see is anything but 
> plain text.



-- 
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: Help with apostrophe and FTS

[Egor Egorov]

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

> Well yes, it's an option. I really need this functionality.
> But on mysql site, under source downloads, there's this warning: " For
> maximum stability and performance, we recommend that you use the binaries we
> provide. "
> 
> Is it really true?

Absolutely. 

There are too many ways to build MySQL so that it will not work well... buggy
compilers, thread stack size, etc.  Especially this applies to Linux, because
MySQL build is statically linked with a bit patched glibc version. And glibc
compilation by itself is tricky. 

So if you need rock stability on Linux and still need to have a custom built
binary you can subscribe to "Primary support" or "Login installation" packages.
Both include custom binaries build. Click on the link below to learn more. 




-- 
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: Last Modified

[Egor Egorov]

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

>> > > If your table contains a timestamp field, it will update each time the
>> row
>> > > is altered.  Otherwise I don't think it's possible.
>> >
>> > That's on a per ROW basis, not TABLE basis.
>>
>> But if it is on every row, you can MAX() it to get the latest row update,
>> which is the latest table update.
> 
> 
> How fast would this be on a largish table?

Build an index on this column and force it: 

SELECT MAX(timestamp) FROM table FORCE INDEX(timestamp_index)

In this case you'll be sure that it's fast enough. 

And, of course, as Ian Gibbons correcly mentioned, you can use MySQL-only way
to check the latest update time of a table: 

SHOW TABLE STATUS LIKE 'tablename';

and look for the Update_time field. 

> Martijn Tonies





-- 
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: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

[Egor Egorov]

You better download the binary release from mysql.com and install it. It's statically
linked so it should work fine on all Linuxes. 

Correctly installed MySQL binary release works fine and generally needs no tweaking to 
start and run. 





-- 
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: Query Help

[Dean Urmson]

> Already tried that, but is 2 appears at the end of the list 
> is doesn't get picked up because there is no comma at the end 
> of the list
 
Are there spaces between the commas???

If not then

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   
keywords = '2'-- 2 on it's own
OR
keywords LIKE '%,2,%'  -- 2 in any position but first and last
OR
keywords LIKE '2,%' -- 2 in first position
OR  
keywords LIKE '%,2' -- 2 in last position

If there are spaces between the commas and the keywords are single words or
numberic digits then you could collapse all spaces...

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   
REPLACE(keywords,' ','') = '2'-- 2 on it's own
OR
REPLACE(keywords,' ','') LIKE '%,2,%'  -- 2 in any position but
first and last
OR
REPLACE(keywords,' ','') LIKE '2,%' -- 2 in first position
OR  
REPLACE(keywords,' ','') LIKE '%,2' -- 2 in last position
 
Done very quickly so I may have missed something, I'm sure others will point
it out if I have!

Cheers

Dean
 
 


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

Re: indexing text

[Pete McNeil]

On Friday, June 11, 2004, 7:00:39 AM, Csongor wrote:

FC> Hi,

FC> I have a table that has a few short text fields [text(4000), text(1000)]
FC> I would like to index. Do you think it is a good idea to index them
FC> "simply", or is it better if I create auxilary fields which hold the MD5
FC> for the text fields and index those? Would that be faster?

The MD5 will be shorter - and so more of it will fit in RAM - so it
will be faster. Better still if you can store it as a BIG INT because
binary comparisons can be done (fewer cycles per comparison).

Hope this helps,
_M




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

Re: Maximum number of simulatneous connections

[venkata ramana]

On Fri, 11 Jun 2004 13:16:12 +0200, Alberto Mucignat <[EMAIL PROTECTED]> wrote:
> 
> 
> venkata ramana wrote:
> 
> >Hi,
> >   What is the maximum number of simulataneous connections
> >that can exist for MySQL. Can we change this limit? If yes please tell
> >me how to do this. I am using MySQL4.1 in Linux.
> >
> >Thanks,
> >ramana.
> >
> >
> 
> look at max_connections in my.cnf
> 
I couldn't find any entry with that name(max_connections) in
/etc/my.conf.  Is this the file you are talking about or is there any
other?

thanks,
ramana

> bye
> 
> a
> 
> --
> "Imagination is more important than knowledge"
> 
>

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

Re: Query Help

[Dobromir Velev]

Hi,

You could use either something like this
SELECT  gallery_id, gallery_name
FROM galleries g 
WHERE   keywords rlike '(^|,)$keyword_id(,|$)';

or 

SELECT  gallery_id, gallery_name
FROM galleries g 
WHERE   $keyword_id in (keywords);


and replace the $keyword_id with the id of the keyword you are looking for. 

Personally I would prefer the first option and put an index on the keywords 
field but you should check for yourself which query will work faster.


-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

On Friday 11 June 2004 14:22, Andrew Dixon - MSO.net wrote:
> Hi Everyone.
>
> I have the following a table with a varchar column that contains a comma
> delimited list of id's from another table that relates the item keywords in
> the other table.
>
> The table keywords contains
>
> keyword_id (int/auto increment/primary key)
> Keyword (varchar/normal key)
>
> The galleries table contains:
>
> gallery_id (int/auto increment/primary key)
> gallery_name (varchar)
> keywords (varchar)
>
> I didn't design the database and I know it is not a good design, but I'm
> stuck with it and I need a query to get the gallery_id when I have a
> certain keyword_id
>
> For example:
>
> gallery_id | gallery_name | keywords
> 1  | test | 1,2,3,4
> 2  | test2| 3,4,5,6
>
> And I won't to get all the galleries with where the have the keywords 2,
> which in this case would be record 1 or keyword 4 which would be both
> record.
>
> SELECTgallery_id, gallery_name
> FROM  galleries
> WHERE keywords 
>
> Hope that makes sense, thanks in advanced.
>
> Best Regards,
>
> Andrew Dixon.



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

RE: Query Help

[Andrew Dixon]

Already tried that, but is 2 appears at the end of the list is doesn't get
picked up because there is no comma at the end of the list

Best regards

>>> Andrew Dixon 

-Original Message-
From: Dean Urmson [mailto:[EMAIL PROTECTED] 
Sent: 11 June 2004 12:53
To: [EMAIL PROTECTED]
Subject: RE: Query Help

> For example:
> 
> gallery_id | gallery_name | keywords 
> 1  | test | 1,2,3,4
> 2  | test2| 3,4,5,6
> 
> And I won't to get all the galleries with where the have the keywords 
> 2, which in this case would be record 1 or keyword 4 which would be 
> both record.
> 
> SELECTgallery_id, gallery_name
> FROM  galleries
> WHERE keywords 
> 

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   keywords LIKE '%2% 

Or if you want to avoid the above picking up keywords 12, 20, 21, 22 etc...
And each keyword is seperated with a comma then

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   keywords LIKE '%2,% 

Cheers

Dean


--
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: Query Help

[Dean Urmson]

> For example:
> 
> gallery_id | gallery_name | keywords 
> 1  | test | 1,2,3,4
> 2  | test2| 3,4,5,6
> 
> And I won't to get all the galleries with where the have the 
> keywords 2, which in this case would be record 1 or keyword 4 
> which would be both record.
> 
> SELECTgallery_id, gallery_name
> FROM  galleries
> WHERE keywords 
> 

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   keywords LIKE '%2% 

Or if you want to avoid the above picking up keywords 12, 20, 21, 22 etc...
And each keyword is seperated with a comma then

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   keywords LIKE '%2,% 

Cheers

Dean


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

Re: RE - Order By Problem

[andy thomas]

On Tue, 8 Jun 2004, Michael Stassen wrote:

> The proposed solution to sort on a portion of the surname field will work,
> but it has a drawback.  If you sort on the result of a function applied to a
> column, you prevent the use of any index on that column.  If your data set
> and user base are both small, this may be a problem you can ignore, but it
> won't scale well.  Also, I expect you will want 'de la Tour' to come before
> 'du Tour', so you'll have to do a secondary sort on surname.

The table is quite small with only 33 records at present although it gets
accessed maybe 10K times a day.

> I'd like to suggest an alternate solution.  In your current scheme, you
> would put 'de la Tour' in your surname column, but you are saying that
> 'Tour' is the part to sort by, while 'de la' is not.  To my mind, that means
> 'de la' and 'Tour' are different kinds of data, which means they belong in
> different columns -- surname_prefix and surname, perhaps.  Then you can
> concatenate surname_prefix and surname for display purposes, but sort on
> just surname (or surname, surname_prefix, first_name), and an index on
> surname (or surname, surname_prefix, first_name) could be used.
>
> For example:
>
>SELECT * FROM advisers;
>
> ++++--+
> | id | first_name | surname_prefix | surname  |
> ++++--+
> |  1 | Michael| NULL   | Stassen  |
> |  2 | Max| van den| Berg |
> |  3 | Sylvia | du | Sautoy   |
> |  4 | Alicia | NULL   | Davidson |
> |  5 | Marco  | van| Basten   |
> |  6 | Andy   | NULL   | Thomas   |
> |  7 | Michelle   | de | Contes   |
> |  8 | Gabrielle  | de la  | Tour |
> |  9 | Joe| NULL   | McNeil   |
> | 10 | Chris  | NULL   | Brown|
> ++++--+
> 10 rows in set (0.30 sec)
>
>
>SELECT first_name, CONCAT_WS(' ', surname_prefix, surname) AS last_name
>FROM advisers
>ORDER BY surname;
>
> ++--+
> | first_name | last_name|
> ++--+
> | Marco  | van Basten   |
> | Max| van den Berg |
> | Chris  | Brown|
> | Michelle   | de Contes|
> | Alicia | Davidson |
> | Joe| McNeil   |
> | Sylvia | du Sautoy|
> | Michael| Stassen  |
> | Andy   | Thomas   |
> | Gabrielle  | de la Tour   |
> ++--+
>
>SELECT CONCAT_WS(' ', first_name, surname_prefix, surname) AS name
>FROM advisers
>ORDER BY surname, surname_prefix, first_name;
>
> +--+
> | name |
> +--+
> | Marco van Basten |
> | Max van den Berg |
> | Chris Brown  |
> | Michelle de Contes   |
> | Alicia Davidson  |
> | Joe McNeil   |
> | Sylvia du Sautoy |
> | Michael Stassen  |
> | Andy Thomas  |
> | Gabrielle de la Tour |
> +--+


Yes, this is one way of doing this. But having adopted an alternative
solution based on a suggestion from Andy Eastham, it now turns out that
the users of the database from different countries have different ideas
of how we should be ordering surnames! So to keep everyone happy, the
table is being redesigned to allow entries to be ordered as the users
want them ordered, rather than the way *we* think they should be ordered.
Complicated but that's life...

cheers,

Andy

> andy thomas wrote:
>
> > On Tue, 8 Jun 2004, Andy Eastham wrote:
> >
> >
> >>Look at using the Reverse() function, then take the substring up to the
> >>first space, then reverse the result.
> >
> >
> > Well, 'select substring_index(surname,' ',-1) from advisers' does the
> > trick as far as extracting the wanted parts of surnames at the end of
> > the surname filed but I'm not sure how to use this as an argument to
> > ORDER BY? Shouldn't something like:
> >
> > select substring_index(surname,' ',-1) as r from advisers, select * from
> > advisers order by r
> >
> > work?
> >
> > Thanks for your help,
> >
> > Andy
> >
> >
> >>>-Original Message-
> >>>From: Paul McNeil [mailto:[EMAIL PROTECTED]
> >>>Sent: 08 June 2004 14:04
> >>>To: [EMAIL PROTECTED]
> >>>Subject: RE - Order By Problem
> >>>
> >>>I have never done anything like this but after looking at the spec's I
> >>>have a possible direction for you
> >>>
> >>>In String functions there is LOCATE(substr,str,pos)
> >>>The first syntax returns the position of the first occurrence of substring
> >>>substr in string str. The second syntax returns the position of the first
> >>>occurrence of substring substr in string str, starting at position pos.
> >>>Returns 0 if substr is not in str.
> >>>
> >>>I think that if you create a function that uses this to strip the string
> >>>to the left of the last found space and that returns the string to the right
> >>>you could ca

Re: Server optimization issue

[Alberto Mucignat]

Misao wrote:
I have 4 MySQL 4.0.16 servers, all with Dual 3ghz Xeons and 4GB of RAM.
They use InnoDB for all tables, and the ibdata file is 70GB.
The DBs seem to be a little slow, and the darn thing is always using a huge
chunk of swap. I've tried increasing and decreasing what it's allowed to
use, but this seems to be the best working settings.
Here are my InnoDB settings in my.cnf:
innodb_log_files_in_group=2
innodb_log_file_size=512M
innodb_buffer_pool_size = 1512M
innodb_additional_mem_pool_size = 20M
Can any optimizations be done to this, or do I just need to get more RAM for
these servers? I believe I can go up to 6GB of RAM, but it requires an OS
upgrade to handle anything over 4GB. I know that the biggest DB in that
chunk is about 20GB itself, with around 5-10GB in it's biggest table.
Memory status:
Mem:  3943852K av, 3938184K used,5668K free,   0K shrd,  209456K
buff
Swap: 2096440K av, 1895456K used,  200984K free 1921172K
cached
Any help would be appreciated, this seemed easier to tweak when it was a
MyISAM server.
 

I had some of performance issue questions too, but it seems that this 
list miss them...

What I can suggest is to carefully read mysql manual, especially the 
tuning part, which mixes up some suggestion about how to set up global 
variables and how to check information from status variables and feel 
where problem is.

About your issue, I had it too several months ago, but after I tuned my 
server that way now I'm ok.

Bye
A.
--
"Imagination is more important than knowledge"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Binary logfiles eating all my disk space

[Dean Urmson]

Many Thanks to Dobromir Velev,


And for those without PERL but with PHP (or prefer PHP) here is a quick and
dirty port to PHP


 REMEMBER TO MAKE A BACKUP OF YOUR FILES BEFORE TESTING OR USING THIS
SCRIPT 
 IT IS RECOMMENDED YOU TEST THIS SCRIPT IN A NON PRODUCTION ENVIRONMENT
FIRST 
 USE AT YOUR OWN RISK, THERE IS NO WARRANTY EXPRESS OR IMPLIED WITH THIS
SOFTWARE 

Cheers

Dean

<--- Script Starts --->

#!/usr/bin/php -q

# PHP Port of Original PERL script by Dobromir Velev 

$db_host="";
$db_user="";
$db_pass="";
$query="show master logs" ;
$dbh=NULL ;

$to_email='';
$from_email='';
$email_subject='DB Clean error - '.date( 'l Y-m-d H:i:s T', time()) ;

$log = array() ;
// Leave the last x logs
$num_logs_to_leave = 7 ;

function controlled_die( $message = NULL ) {
global $dbh, $to_email, $from_email, $email_subject ;

if( is_null( $message ) ) {
$message = "MySQL ERROR: (".mysql_errno($dbh).")
".mysql_error($dbh);
}

$headers  = "From: $from_email\r\n" ;
$headers .= "Reply-to: $from_email\r\n" ;

mail( $to_email, $email_subject, $message, $headers ) ;
exit;
} // end function Controlled_die

$dbh = mysql_connect($db_host, $db_user, $db_pass) 
or controlled_die() ;

$result = mysql_query($query) 
or controlled_die();

$rows = mysql_num_rows($result) ;

while ($num_logs_to_leave <= $rows){
$currRow  = mysql_fetch_array($result,MYSQL_NUM) ;
$log[] = $currRow[0] ;
$num_logs_to_leave++;
}

mysql_free_result($result) ;

$message  = "MySQL Log Purge Started :: ".date( 'l Y-m-d H:i:s T', time()) ;
$message .= "\nPurging logs to ".$log[count($log) - 1]."\n";

mysql_query("purge master logs to '".$log[count($log) - 1]."'") or
controlled_die();

mysql_close($dbh) ;

$message .= "Logs purge end: ".date( 'l Y-m-d H:i:s T', time())."\n";
$email_subject='MySQL Log Purge Report' ;
controlled_die($message);
?>

<--- Script Ends --->


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

Query Help

[Andrew Dixon - MSO.net]

Hi Everyone.

I have the following a table with a varchar column that contains a comma
delimited list of id's from another table that relates the item keywords in
the other table.

The table keywords contains

keyword_id (int/auto increment/primary key)
Keyword (varchar/normal key)

The galleries table contains:

gallery_id (int/auto increment/primary key)
gallery_name (varchar)
keywords (varchar)

I didn't design the database and I know it is not a good design, but I'm
stuck with it and I need a query to get the gallery_id when I have a certain
keyword_id

For example:

gallery_id | gallery_name | keywords 
1  | test | 1,2,3,4
2  | test2| 3,4,5,6

And I won't to get all the galleries with where the have the keywords 2,
which in this case would be record 1 or keyword 4 which would be both
record.

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   keywords 

Hope that makes sense, thanks in advanced.

Best Regards,

Andrew Dixon.



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

Re: Maximum number of simulatneous connections

[Alberto Mucignat]

venkata ramana wrote:
Hi,
  What is the maximum number of simulataneous connections
that can exist for MySQL. Can we change this limit? If yes please tell
me how to do this. I am using MySQL4.1 in Linux.
Thanks,
ramana.
 

look at max_connections in my.cnf
bye
a
--
"Imagination is more important than knowledge"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

indexing text

[Fagyal, Csongor]

Hi,
I have a table that has a few short text fields [text(4000), text(1000)] 
I would like to index. Do you think it is a good idea to index them 
"simply", or is it better if I create auxilary fields which hold the MD5 
for the text fields and index those? Would that be faster?

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

Maximum number of simulatneous connections

[venkata ramana]

Hi,
   What is the maximum number of simulataneous connections
that can exist for MySQL. Can we change this limit? If yes please tell
me how to do this. I am using MySQL4.1 in Linux.

Thanks,
ramana.

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

RE: RE - Order By Problem

[andy thomas]

On Tue, 8 Jun 2004, Andy Eastham wrote:

> Andy,
>
> Just:
>
> select substring_index(surname,' ',-1) as r from advisers order by r;

Yes, that did the trick!

Thanks,

Andy

> > -Original Message-
> > From: andy thomas [mailto:[EMAIL PROTECTED]
> > Sent: 08 June 2004 15:57
> > To: Andy Eastham
> > Cc: Mysql List
> > Subject: RE: RE - Order By Problem
> >
> > On Tue, 8 Jun 2004, Andy Eastham wrote:
> >
> > > Look at using the Reverse() function, then take the substring up to the
> > > first space, then reverse the result.
> >
> > Well, 'select substring_index(surname,' ',-1) from advisers' does the
> > trick as far as extracting the wanted parts of surnames at the end of
> > the surname filed but I'm not sure how to use this as an argument to
> > ORDER BY? Shouldn't something like:
> >
> > select substring_index(surname,' ',-1) as r from advisers, select * from
> > advisers order by r
> >
> > work?
> >
> > Thanks for your help,
> >
> > Andy
> >
> > > > -Original Message-
> > > > From: Paul McNeil [mailto:[EMAIL PROTECTED]
> > > > Sent: 08 June 2004 14:04
> > > > To: [EMAIL PROTECTED]
> > > > Subject: RE - Order By Problem
> > > >
> > > > I have never done anything like this but after looking at the spec's I
> > > > have
> > > > a possible direction for you
> > > >
> > > > In String functions there is
> > > >
> > > > LOCATE(substr,str,pos)
> > > > The first syntax returns the position of the first occurrence of
> > substring
> > > > substr in string str. The second syntax returns the position of the
> > first
> > > > occurrence of substring substr in string str, starting at position
> > pos.
> > > > Returns 0 if substr is not in str.
> > > >
> > > > I think that if you create a function that uses this to strip the
> > string
> > > > to
> > > > the left of the last found space and that returns the string to the
> > right
> > > > you could call this in your query and use it in the order by
> > statement.
> > > >
> > > >
> > > >
> > > > --
> > > > 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]
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

# include 


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

Re: AW: ORDER BY problem

[andy thomas]

On Tue, 8 Jun 2004, Franz, Fa. PostDirekt MA wrote:

> Hi,
>
> it is not possible to handle all cases proper.
> You can just handle all cases you know with the REPLACE-function,
> so you simply delete the prefixes in the WHERE-clause.
> But that only works for all prefixes you know.
> If you do like
> ORDER BY REPLACE(REPLACE(surname,'du',''),'de','')
> you get all 'du Sautoy' and 'de Contes' exactly like you wanted it but
> 'de la Tour' will still apear at the wrong place.
> I don't think you can be sure to remove all prefixes like this, because
> you can't be sure to know all of them.
> A different trick would be to say allways take the last 'word' in the surname,
> which is much more efficent, but will unfortunally not work with double names like
> 'Schwarzenegger Schriver' (Maybe they are written with a '-' in it, it's just to
> show the principle).

This is what I did in the end, to alwyas use the last word that's
separated by a space.

> So, there is not lot the world can learn from germany, but we treat all
> these prefixes like they belong ti the name, which means 'von Hohenzollern'
> is correctly ordered among the the v's and not the h's.

Well, we have run into this problem already since I 'fixed' the ordering!
As there are people from all over the world using this database, we are
now about to redesign the table to allow individual people to decide where
they want their surname to appear in the listing.

Thanks for your suggestions,

Andy

> -Ursprüngliche Nachricht-
> Von: andy thomas [mailto:[EMAIL PROTECTED]
> Gesendet: Dienstag, 8. Juni 2004 13:51
> An: [EMAIL PROTECTED]
> Betreff: ORDER BY problem
>
>
> In a table called 'advisers' I have a column called 'surname' which contains the 
> surnames of a number of people. Using a query like: 'select * from advisers order by 
> surname' lists the people in the correct order but some people have surnames like 
> 'du Sautoy' and 'van den Berg' and these are listed in the order of the first 
> character that appears in their name, so that 'du Sautoy' appears surnames beginning 
> with 'D' rather than 'S', etc.
>
> Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in 
> a sort string, so that 'du' and 'van den' in the example above are effectively 
> ignored?
>
> Andy
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

# include 


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

Re: RES: ORDER BY problem

[andy thomas]

On Tue, 8 Jun 2004, Renato Cramer wrote:

> Hello Andy,
>
> I don't known if this is possible without handle string, what I don't guess
> recommended, because of performance and legibility of code.
>
> One suggestion will be store in column 'surname' (or other) the data already
> in format of sort.
> In other words, will be two columns in table, and, depending on approach,
> the second column will can be disabled for the users.
>
> Example:
>   Name: Marco van Basten
>   Archive: Basten, Marco van
>
> I hope that helps.

Well, this was fixed in the end by this query:

   select substring_index(surname,' ',-1) as r from advisers order by r

which produced the desired result. But we have since had complaints from
individuals wanting their surnames sorted differently! People from Germany
with surnames such as 'von Neumann' like to have this sorted with the V's
and not the N's while people from the Netherlands with 'van den Berg' want
it to be with the B's and not the V's.

We are now redesigning the table to allow records to be displayed in a
specific order chosen by the administrator, rather than trying to do this
automatically by a SELECT statement.

Thanks for your suggestion anyway.

Andy

> -Mensagem original-
> De: andy thomas [mailto:[EMAIL PROTECTED]
> Enviada em: terça-feira, 8 de junho de 2004 08:51
> Para: [EMAIL PROTECTED]
> Assunto: ORDER BY problem
>
> In a table called 'advisers' I have a column called 'surname' which
> contains the surnames of a number of people. Using a query like:
> 'select * from advisers order by surname' lists the people in the
> correct order but some people have surnames like 'du Sautoy' and
> 'van den Berg' and these are listed in the order of the first
> character that appears in their name, so that 'du Sautoy' appears
> surnames beginning with 'D' rather than 'S', etc.
>
> Does anyone know of a way of getting ORDER BY to sort on uppercase
> elements only in a sort string, so that 'du' and 'van den' in the
> example above are effectively ignored?
>
> Andy
>

# include 


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

Re: Using IF

[Johan Hook]

Hi Keith,
I think your problem comes down to the fact that IF() is a function
that must return one value, when you put in your '*' you might be
specifying more then one value to return.
/Johan
Keith wrote:
g'day,
Am having a bit of a problem with using IF. This is the error message I get:
-
SELECT rac.name AS race,
IF (
el.entityType =1, ch. * , npc. *
)charData
FROM characters AS ch, entityLocation AS el, races AS rac, npc AS npc
WHERE el.entityType
IN ( 1, 10 ) AND el.containerType =6 AND el.containerID =75402 AND
IF (
el.entityType =1, el.entityID = ch.characterID AND el.visibility <=60 AND
ch.raceID = rac.raceID AND ch.handle <> 'Khan', el.entityID = npc.npcID AND
el.visibility <=60 AND npc.raceID = rac.raceID
)
LIMIT 0 , 30
MySQL said:
#1064 - You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near '*
, npc. *  )charData
FROM characters AS ch, entityLocation AS
-
What I want to do is select everything from characters if the el.entityType
is 1, else I want to select everything from npc table.

Cheers,
Keith

--
Johan Höök, Pythagoras Engineering Group
- MailTo:[EMAIL PROTECTED]
- http://www.pythagoras.se
Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden
Phone: +46 8 760 00 10 Fax: +46 8 761 22 77

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

Re: Help with apostrophe and FTS

[Andrea Gangini]

> The ' isn't NOT a stopword, it's simply not a word-boundary character,
> which I think is what you want.

Yes, I expressed myself badly, but you have just greatly understood my
problem.

> change that in MySQL... unless you edit the source of course and
> compile it yourself. :-)  Is that an option for you?

Well yes, it's an option. I really need this functionality.
But on mysql site, under source downloads, there's this warning: " For
maximum stability and performance, we recommend that you use the binaries we
provide. "

Is it really true?

Andrea Gangini [EMAIL PROTECTED] Mimesi Srl


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

Re: Help with apostrophe and FTS

[Matt W]

Hi Andrea,

The ' isn't NOT a stopword, it's simply not a word-boundary character,
which I think is what you want.  And there is no way I know of to change
that in MySQL... unless you edit the source of course and compile it
yourself. :-)  Is that an option for you?  If so, I think you just need to
change 1 line in myisam/ftdefs.h:

#define misc_word_char(X)   ((X)=='\'')

change that to:

#define misc_word_char(X)   (0)

I HOPE that is correct! ;-)


Matt


- Original Message -
From: "Andrea Gangini"
Sent: Thursday, June 10, 2004 9:44 AM
Subject: Help with apostrophe and FTS


> Is there the possibility of making the apostrophe char ( ' ) a stopword
in
> mysql?
> Full text search queries in italian or other European language are
greatly
> affected by that; for example searching "amore" will not return
"dell'amore"
> as a match
> Any workaround suggested?
>
> Andrea Gangini [EMAIL PROTECTED] Mimesi Srl


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

Re: Binary logfiles eating all my disk space

[Dobromir Velev]

Here it is

#!/usr/bin/perl
use strict;
use DBI;
my $db_host="localhost";
my $db_user="username";
my $db_pass="password";
my $db_name="database";
my $mail_prog = '/usr/lib/sendmail';
my $email='[EMAIL PROTECTED]';
my $from_email='[EMAIL PROTECTED]';

sub mysql_die{
if ($_[0]){ print $_[0]."\n";}
else{ print "MySQL Error: ".$DBI::errstr."\n";}
open (MAIL, "|$mail_prog -t");
print MAIL "To: $email\n";
print MAIL "Reply-to: $from_email\n";
print MAIL "From: $from_email\n";
print MAIL "Content-type: text/plain;charset=windows-1251\n";
print MAIL "Subject :DB Clean error - ".localtime()."\n";
print MAIL "\n";
print MAIL "MySQL Error: ".$DBI::errstr."\n";
close (MAIL);
exit;
}

my @log;
my $dbh = 
DBI->connect("DBI:mysql:$db_name:$db_host",$db_user,$db_pass,{PrintError => 
0, AutoCommit => 1}) or  die $DBI::errstr;
my $cmycres=$dbh->prepare("show master logs");
$cmycres->execute() or mysql_die();
my $i=7; #this will leave the last seven logs intact
while ($i<=$cmycres->rows){
@log=$cmycres->fetchrow;
$i++;
}
$cmycres->finish();

print localtime()." purging logs to $log[0]\n";
$dbh->do("purge master logs to '$log[0]'") or mysql_die();
$dbh->disconnect;
print "Logs purge end: ".localtime()."\n";

-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

On Friday 11 June 2004 00:55, Jeff Smelser wrote:
> On Thursday 10 June 2004 08:03 am, Josh Trutwin wrote:
> > Would you be willing to share your perl script?  Perhaps offlist if you
> > are concerned about everyone seeing it?
>
> I would like to see it as well.. If at all possible
>
> --
> How can there be self-help groups?



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

Re: Binary logfiles eating all my disk space

[Dobromir Velev]

Hi,
There is a thing I forgot to mention in my previous email - if you are 
replicating your database please follow the steps described in 

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

or you might end with missing data on your slave servers. A binary log should 
not be deleted unless all slaves have already processed it. 

-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

On Friday 11 June 2004 00:55, Jeff Smelser wrote:
> On Thursday 10 June 2004 08:03 am, Josh Trutwin wrote:
> > Would you be willing to share your perl script?  Perhaps offlist if you
> > are concerned about everyone seeing it?
>
> I would like to see it as well.. If at all possible
>
> --
> How can there be self-help groups?



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

Sending multiple queries to MySQL (sorry, hit send by accident!)

[Andy Hall]

Hi,

I have a situation where, within a PHP loop, I am creating over one hundred SQL 
statement (INSERTS and UPDATES).

Would it cut any overhead to send 20 ";" seperated queries in one mysql_query() 
statement, and therefore only execute mysql_query() 5 times for 100 statements, as 
opposed to sending each statement individually?

So, 

mysql_query("INSERT 1", "UPDATE 1...", "INSERT 2...", "UPDATE 2..");

or:

mysql_query("INSERT 1");
mysql_query("UPDATE 1...");
mysql_query("INSERT 2...");
mysql_query("UPDATE 2..");

Do you save by not making as many connections?

Thanks

Andy.

Sending multiple queries to MySQL

[Andy Hall]

Hi,

I have a situation where, within a PHP loop, I am creating over one hundred SQL 
statement (INSERTS and UPDATES).

Would it cut any overhead to send 20 ";" seperated queries in one mysql_query() 
statement, and therefore only execute mysql_query() 5 times for 100 statements, as 
opposed to sending each statement individually?

So, 

mysql_query("INSERT...", "UPDATE ...", "INSERT ...", "UPDATE ..");