Fwd: Re: Basic Question

[Rhino]

 Original Message 
Subject:Re: Basic Question
Date:   Mon, 17 Dec 2012 15:39:39 -0500
From:   Rhino rhi...@sympatico.ca
To: Reindl Harald h.rei...@thelounge.net



On 2012-12-17 14:34, Reindl Harald wrote:


Am 17.12.2012 19:09, schrieb Rhino:

Is the version of SQL statements that I write for MySQL based on the
server version or the client version?

clearly for the server
mysql is a database-SERVER

I know that. I thought that if the client is at a lower level, as in
this case, I would need to write SQL that satisfies the client. In other
words, features that emerged after 4.1 would have to be omitted since
the client wouldn't know how to handle them.

But I'm not clear what the MySQL Client even is let alone what it does
so I'm prepared to accept the good news that I can write SQL at the 5.1
level and not have it be a problem for the client.



According to phpMyAdmin, the version of the MySQL Client is 4.1.22 but the 
MySQL Server version is 5.1.65. When I
write SQL, do I need to ensure that it follows the rules as laid out in the 
Version 4.1 manual or the version
Version 5.1 manual?

5.1

but a server using 5.1.x with 4.1.x client libraries is misconfigured
these days and with PHP = 5.3x mysqlnd should be sued


I can try sending a note to that effect to the hosting company but I
have my doubts about whether they will care.

Still, this may explain why the script generated by the Export of my
table failed to work when I tried to use it to reimport my table. The
second (non-comment) line it had generated was:
SET time_zone = '+00:00;

As you can see, the string begins with an apostrophe but ends with
quotation marks, causing a mismatch.

Or maybe that's just a completely different error that has nothing to do
with the client/server levels.

--
Rhino




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

Downloading MySQL

[Rhino]

What's the deal with downloads of MySQL?

I've been away from MySQL for a few years and have not really been 
keeping close tabs on things, although I have kept my mysql mailing list 
subscription and am aware of a major player planning to purchase MySQL. 
Did that actually go ahead?


The reason I ask is that I want to download a free copy of MySQL to use 
for development purposes and found that I couldn't simply download it 
any more the way I did several years ago. It gives you a form to 
complete where you have to supply all kinds of contact information and 
then assures you that someone will be in touch within 48 hours. What the 
heck is all that about? Is this some sort of marketing offensive where 
some sales guy is going to try to push me into purchasing MySQL and a 
service contract??


--
Rhino

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: When to use Stored Procedures

[Rhino]

Shawn Green wrote:

Tompkins Neil wrote:

Hi,

I've used mainly of the older versions of MySQL.  However am looking 
to port
a application across to MySQL 5.  My question is when would one 
decide to

use a Stored Procedure over a query written at the application level ?



The decision to encapsulate a particular process or query within a 
stored procedure is usually based on your business needs.


* Common queries that only change by parameters are good candidates

* Complex multi-step queries are good candidates

* If you need an unprivileged user to make a change to a sensitive 
table, you can avoid some security problems by wrapping that process 
in a stored procedure.  For example, maybe part of your internal HR 
processes is an application that allows people to update  their phone 
numbers but can't be allowed to see the private information in an 
employee's database record. You could create a privileged routine 
called change_phone_number() that could do that without giving the 
application (or another user) the rights to manipulate that table 
directly.


Does this help?

The other situation that strongly justifies a stored procedure in place 
of an application level query is one that involves a lot of churning 
through the database to return only a small amount of data.


For example, suppose you had to determine the median grade for a test. 
Let's say there were a million separate people taking this test (perhaps 
something like college admission tests). To find the median, it's 
probably best to sort the rows in ascending order by final grade, then 
count down from the top until you are halfway through the list. Then you 
can find and return the median grade. That involves a lot of I/O: a 
million rows to read, then the sorting, then reading down a half million 
rows to find the midpoint. But all you're returning is the median. If a 
client program has to do that work, all of those I/Os are going to be 
sent over the network and cost a lot. But if you write a stored 
procedure, it will do all the heavy lifting LOCALLY on the database 
server, which will reduce costs dramatically. Only the median gets sent 
across the network back to the client program. Obviously, this will save 
on both I/O costs and network utilization.


--
Rhino

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: sql to duplicate records with modified value

[Rhino]

Voytek Eymont wrote:

I have Postfix virtual mailboxes in MySQL table like below:

I'd like to duplicate all records whilst MODIFYING two fields like so:

current record has format like:
user 'usern...@domain.tld'
maildir 'domain.tld/usern...@domain.tld/'

add new record that has:
user 'username+s...@domain.tld'
maildir 'domain.tld/usern...@domain.tld/.spam/'

so that I'll end up with two record, existing, plus new one

field 'user' - insert '+spam' ahead of '@'
field 'maildir' append '.spam/'

what's the best way
mysql  Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3
Server version: 4.1.22-standard

mysql show tables;
+---+
| Tables_in_postfix |
+---+
| admin |
| alias |
| config|
| domain|
| domain_admins |
| fetchmail |
| log   |
| mailbox   |
| vacation  |
| vacation_notification |
+---+
10 rows in set (0.00 sec)


  
Are you  hoping to do all that you want - copy rows, update rows and 
create new rows - in a single SQL statement? Because if that's what you 
want, I don't think it's possible. Unless someone has come up with some 
new tricks, you can't insert a new record and update an existing one 
within the same SQL statement.


If you want to do it via several SQL statements, each part of what you 
want should be possible via different statements in a script where you 
can use the script itself to help with the update logic. If you want to 
do the updates from the command line only and won't consider a program, 
I don't know how to do it.


I should point out that I'm quite fluent in SQL and have been writing it 
for a long time. I haven't used MySQL in a few years now but I'm still 
on the mailing list and your question caught my eye.



--
Rhino

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Birthday Calendar

[Rhino]

Apparently not.


Noel Butler wrote:

does this list not have a dickhead filter?

On Tue, 2010-03-23 at 15:17 +, Ganeswar Mishra wrote:

  

Hi

Please click on the link below and enter your birthday for me.  I am creating a 
birthday calendar for myself.  Don't worry, it'll take less than a minute (and 
you don't have to enter your year of birth).

http://www.birthdayalarm.com/bd2/86622257a687264490b1506094186c258984146d1386

Ganeswar




  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SQL syntax

[Rhino]

- Original Message - 
From: Scott Yamahata [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Friday, December 29, 2006 1:29 AM
Subject: SQL syntax



Hi, I'm getting the following error message:

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 ' enabled = 
'1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', 
countryid = , enabled = '1'


and can't quite figure out what it means.  Any help is greatly 
appreciated.




Someone may have replied to you by now so I apologize if this is old news 
but I think the problem _precedes_


enabled = '1'

Take a look at the SQL just BEFORE that where you wrote:

   countryid = ,

That is surely not valid SQL in any dialect of SQL I have ever seen. You 
need an appropriate value before the comma, such as:


   countryid = 7,

or

   countryid = '7',


If you don't want to assign a value to countryid, just omit the entire 
'countryid =,' altogether.


--
Rhino


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

Re: what this error was

[Rhino]

- Original Message - 
From: VenuGopal Papasani [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, September 05, 2006 12:28 AM
Subject: what this error was



Hi,
I was trying to import some of the data into the 'datavalue' table of my
database i was getting th following error

Error Code : 1452
Cannot add or update a child row: a foreign key constraint fails
(`gtest1/datavalue`, CONSTRAINT `FKEB92DC077A7A2FFA` FOREIGN KEY
(`dataElement`) REFERENCES `dataelement` (`id`))
(5428 ms taken)


My table structures:

dataelement  CREATE TABLE `dataelement`
(
  `id` int(11) NOT NULL
auto_increment,
  `name` varchar(255) NOT
NULL,
  `shortName` varchar(255) default
NULL,
  `description` varchar(255) default
NULL,
  `active` bit(1) default
NULL,
  `type` varchar(255) default
NULL,
  `aggregationOperator` int(11) default
NULL,
  `parent` int(11) default
NULL,
  `alternativeName` varchar(255) default
NULL,
  `code` varchar(255) default
NULL,
  PRIMARY KEY
(`id`),
  UNIQUE KEY `name`
(`name`),
  UNIQUE KEY `shortName`
(`shortName`),
  UNIQUE KEY `alternativeName`
(`alternativeName`),
  UNIQUE KEY `code`
(`code`),
  KEY `FKE1611C125DC41C92`
(`parent`),
  CONSTRAINT `FKE1611C125DC41C92` FOREIGN KEY (`parent`)
REFERENCES `dataelement` (`id`)
) ENGINE=InnoDB DEFAULT
CHARSET=utf8

--

datavalue  CREATE TABLE `datavalue`
(
`dataElement` int(11) NOT
NULL,
`period` int(11) NOT
NULL,
`source` varchar(255) NOT
NULL,
`flag` varchar(255) default
NULL,
`value` varchar(255) default
NULL,
`storedBy` varchar(255) default
NULL,
`timestamp` datetime default
NULL,
`comment` varchar(255) default
NULL,
PRIMARY KEY
(`dataElement`,`period`,`source`),

KEY `FKEB92DC077F9CE9CC`
(`period`),
KEY `FKEB92DC077A7A2FFA`
(`dataElement`),
CONSTRAINT `FKEB92DC077A7A2FFA` FOREIGN KEY (`dataElement`)
REFERENCES `dataelement` (`id`),
CONSTRAINT `FKEB92DC077F9CE9CC` FOREIGN KEY (`period`)
REFERENCES `period` (`id`)
  ) ENGINE=InnoDB DEFAULT
CHARSET=utf8



The message is pretty clear; a dataelement value on a row failed to satisfy 
the foreign key constraint during either an insert or an update.


Let's say that the dataelement value had a foreign constraint that limited 
the values to 'M' or 'F'. If you added a new row to the table and the value 
in the new dataelement column was anything but 'M' or 'F', you would have 
violated the foreign constraint and gotten that error. Or, if you had 
updated an existing row and tried to change the dataelement value to 
anything but 'M' or 'F', you would get that same message.


You need to look at the values that are allowable in this dataelement column 
and then see what value is actually being inserted or what the existing 
value is being changed to.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.7/437 - Release Date: 2006-09-04


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

Re: Chemistry search

[Rhino]

- Original Message - 
From: Dave Shariff Yadallee - System Administrator a.k.a. The Root of the 
Problem [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Wednesday, August 23, 2006 10:15 PM
Subject: Chemistry search



Has anyone come up with searching a chemicals based database using
mols or smilies?


Huh?

What do you mean by mols - molar weights?

And why would anyone search for anything to do with chemicals based on 
smilies? How would :-) or symbols like that help? Or is this some other 
sense of the word smilies than the customary Internet one?


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.5/425 - Release Date: 2006-08-22


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

Re: ON DELETE CASCADE question

[Rhino]

If I understand your question correctly - and I'm not sure I do - Table 2 is 
the parent and Table 1 is the child. In other words, there is a one-to-many 
relationship between the parent, Table 2, and the child, Table 1.


Therefore, if the parent table, Table 2, contains one row for each 
department of a company and the child table, Table 1, contains one row for 
each employee, we would have a situation like this:


Table 2 - DEPARTMENT
===
DeptnoDeptname

A00Administration
B01Sales
C01Manufacturing

Table 1 - EMPLOYEE
=
EmpnoDeptnoName

100A00Smith
101B01Brown
102C01Wong
103A00Green
104A00White
105C01Ferguson

If I'm following you so far, you're asking what happens in the DEPARTMENT 
table if you delete an employee from the EMPLOYEE table? For example, if you 
delete employee 105 or even employee 101 from the EMPLOYEE table, will there 
be any impact on the DEPARTMENT table?


The answer is _NO_. The DELETE rule between a parent table and its child 
table controls what happens in the _child_ table if a row is deleted from 
the _parent_ table; deleting a row in the child table has no effect on the 
parent table.


Therefore, if you delete employee 105 from the EMPLOYEE table, the only 
effect is that employee 105 is removed from the EMPLOYEE table; there is no 
effect of any kind on the DEPARTMENT table. Ditto for employee 101 who is 
the only employee in department B01: only the employee 101 row is removed 
and there is no impact on the DEPARTMENT table at all.


If you want to see the DELETE rule between the tables take effect, you need 
to delete a row from the _DEPARTMENT_ table. If the DELETE rule in effect is 
CASCADE, as you have proposed, then deleting the B01 row from DEPARTMENT 
will also cause the deletion of all B01 rows in the EMPLOYEE table. By the 
same token, deleting the C01 row from the DEPARTMENT table will cause the 
deletes of _all_ of the C01 rows in the EMPLOYEE table.


--
Rhino


- Original Message - 
From: James Sherwood [EMAIL PROTECTED]

To: mysqllist mysql@lists.mysql.com
Sent: Monday, July 17, 2006 8:44 AM
Subject: ON DELETE CASCADE question


Hello.

I have a question about on delete cascade.

If i have 2 tables such as this:

Table1Table2

PrikeyPrikey
Table2foreinkey   name
name description
description

Now if I delete a row from table1 that has a foreign key from table 2 should 
it delete the row in table 2?



Thanks,
James





No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 2006-07-11



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 2006-07-11


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

Re: How to look for balanced parenthesis?

[Rhino]

- Original Message - 
From: Miles Thompson [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Sunday, July 09, 2006 8:10 PM
Subject: Re: How to look for balanced parenthesis?



At 03:48 PM 7/9/2006, Rhino wrote:



- Original Message - From: mos [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, July 09, 2006 1:35 PM
Subject: How to look for balanced parenthesis?


I have a complicated SQL statement with around a dozen 
if(this,val1,val2) embedded in it and there are even nested If clauses. 
I'm getting syntax errors because I'm not balancing the ( ) properly. 
Is there any free software out there for Windows that I can copy and 
paste the SQL statement into that will show me where the parenthesis are 
unbalanced? It doesn't have to know about SQL, I just to either highlight 
the parenthesis range or find out when the parenthesis become out of 
sync.


I'm going to give you one answer that you almost certainly won't like: 
Eclipse. Eclipse is an IDE for developing programs, especially Java, and 
it has a parenthesis matcher which also handles braces and square 
brackets. Installing Eclipse solely for the bracket matcher is a bit like 
using atomic weapons to kill mosquitos but if you were going to develop 
applications anyway and wanted a great IDE, it might be the answer to your 
problem. It's free by the way. You can get it at http://eclipse.org.


Another editor that can also match brackets is PFE, Programmer's File 
Editor. It's also free and is a good editor. You can find it many places, 
including http://www.lancs.ac.uk/staff/steveb/cpaap/pfe/pfefiles.htm. It 
only runs on Windows though.


Another decent little editor that has the feature is TextPad. It's also 
free and can be found at http://www.textpad.com/.


There are probably more basic editors out there that have bracket matchers 
but I can't name any for you. I'm not even sure what the feature you want 
is supposed to be called: bracket matcher, parenthesis balancer, or 
whatever. It's getting to be a pretty standard feature in editors in 
recent years, although it doesn't seem to be in NotePad or WordPad.


--
Rhino


Rhino,

Eclipse can't edit files on the server, can it?

I used to use UltraEdit, but then switched to EditPlus because it can edit 
remote files almost transparently. (Opening a file FTP's it down, you edit 
local copy, Saving FTP's it back.)




I really don't know if Eclipse can edit files on a server. My workspace is 
on my local PC so I've never tried editing a file on a server.


I can certainly see that it would be convenient to be able to edit a file on 
a server without having to manually transfer the file back and forth! But 
Eclipse is pretty smart so I'd be a little surprised if they forced you to 
transfer the file manually.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.9/382 - Release Date: 2006-07-04


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

Re: How to look for balanced parenthesis?

[Rhino]

- Original Message - 
From: mos [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Sunday, July 09, 2006 1:35 PM
Subject: How to look for balanced parenthesis?


I have a complicated SQL statement with around a dozen if(this,val1,val2) 
embedded in it and there are even nested If clauses. I'm getting syntax 
errors because I'm not balancing the ( ) properly. Is there any free 
software out there for Windows that I can copy and paste the SQL statement 
into that will show me where the parenthesis are unbalanced? It doesn't 
have to know about SQL, I just to either highlight the parenthesis range or 
find out when the parenthesis become out of sync.




I'm going to give you one answer that you almost certainly won't like: 
Eclipse. Eclipse is an IDE for developing programs, especially Java, and it 
has a parenthesis matcher which also handles braces and square brackets. 
Installing Eclipse solely for the bracket matcher is a bit like using atomic 
weapons to kill mosquitos but if you were going to develop applications 
anyway and wanted a great IDE, it might be the answer to your problem. It's 
free by the way. You can get it at http://eclipse.org.


Another editor that can also match brackets is PFE, Programmer's File 
Editor. It's also free and is a good editor. You can find it many places, 
including http://www.lancs.ac.uk/staff/steveb/cpaap/pfe/pfefiles.htm. It 
only runs on Windows though.


Another decent little editor that has the feature is TextPad. It's also free 
and can be found at http://www.textpad.com/.


There are probably more basic editors out there that have bracket matchers 
but I can't name any for you. I'm not even sure what the feature you want is 
supposed to be called: bracket matcher, parenthesis balancer, or 
whatever. It's getting to be a pretty standard feature in editors in recent 
years, although it doesn't seem to be in NotePad or WordPad.


--
Rhino




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.9/382 - Release Date: 2006-07-04


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

Re: Sql optimization

[Rhino]

You're hard-coding ten thousand discrete 
values between the two IN clauses?? I'm not sure how MySQL processes a query 
like that but I can imagine that the performance would not be good. Frankly, I 
am: 
a)suprised that you had the patience 
to type in all of those values (andverify that you typed them 
correctly)
b) amazed that MySQL actually executes a 
statement that long without complaining that the statement is too 
long

Are the discrete values really sequential 
like the example you show? Because if they are you might find your life a lot 
easier if you simply write your INs as BETWEENs. For example, 


delete from modnaptrrecord_zone 

where 
modnaptrrecord_zone.modnaptrrecord_idbetween 593536 and 789123and 
modnaptrrecord_zone.modnaptrrecord_id notbetween 593536 and 
789123

Even if there were a few ranges, the 
query would still be a lot shorter if written with BETWEENs. For 
example,


delete from modnaptrrecord_zone 

where 
(modnaptrrecord_zone.modnaptrrecord_id (between 100 and 500)
or 
(modnaptrecord_zone.modnaptrrecord_id between 1 and 11000)) 
and 
(modnaptrrecord_zone.modnaptrrecord_id not (between 100 and 500)
or 
(modnaptrecord_zone.modnaptrrecord_id between 1 and 11000)) 

I've probably got at least one of 
those conditions backward but you probably get the idea. It's a LOT easier to 
write ranges than long lists of sequential values!

Another way that might improve 
performance a lot is if you use a subqueries with your IN clauses. For 
example:


delete from modnaptrrecord_zone 

where 
modnaptrrecord_zone.modnaptrrecord_id in 
(select id from other_table where 
customer_country = 'USA') and 
modnaptrrecord_zone.modnaptrrecord_id 
not in 
(select id from other_table where 
customer_country = 'CANADA')

In other words, if the values that 
need to go into the IN list can be obtained by reading a table with a SELECT, 
put the SELECT statement within the brackets instead of listing the hundreds of 
discrete values. Of course, this assumes that you are using a version of MySQL 
that supports subqueries!!

By the way, I'm assuming that the 
lists needed for your two IN clauses are different from one another in some 
way.Your query makes no sense if the two lists are the same. For example, 
if your lists were the same - I'll just use a few values for each list to make 
my point clear - you might have a query like this:


delete from modnaptrrecord_zone 

where 
modnaptrrecord_zone.modnaptrrecord_id in (1, 2, 3) and 
modnaptrrecord_zone.modnaptrrecord_id 
not in (1, 2, 3)

In other words, you want to delete 
rows in table modnaprrecord_zone where the modnaptrrecord_id IS 1, 2 or 3 AND 
where the mondaptrrecord_id IS NOT 1, 2, or 3 at the same time. That doesn't 
make sense.

Now, if the values in the lists are 
not in ranges and can't easily be obtained via SELECT queries, you may want to 
consider modifying your tables to simplify this query. Let's say that those long 
IN lists are just trying to specify customers who are in good standing with you, 
i.e. customers who don't owe you any money and are prompt to pay their accounts. 
You might find it a lot easier to add a column to your customer table that 
indicates the customer's status and then simply search on the value of that 
status flag. For example, let's say that a customer could have three statuses: 
"Excellent", "Good" and "Poor". You could have a process that determines the 
proper status for that customer; that might be a batch job that runs once a day 
or once a week. Then, your query could simply search for the status like 
this:


delete from modnaptrrecord_zone where 
modnaptrrecord_zone.modnaptrrecord_id in 
(select id from customer where status 
= 'Poor') and 
modnaptrrecord_zone.modnaptrrecord_id 
not in 
(select id from customer where status 
= 'Good')

--
Rhino

  - Original Message - 
  From: 
  [EMAIL PROTECTED] 
  To: mysql@lists.mysql.com 
  Sent: Tuesday, June 20, 2006 11:22 
  AM
  Subject: Sql optimization
  
  Hi All,
  
  I am usingINNODB.I have a delete quetry something like this :
  
  delete from modnaptrrecord_zone 
  where modnaptrrecord_zone.modnaptrrecord_id in (593536 ,593537 ,593538 ,593539 
  ,593540 ) and modnaptrrecord_zone.modnaptrrecord_id not in (593536 
  )
  
  This is taking a verylong 
  time to execute..somewhere around 15mins. The values in the "id 
  in"and "not in" of the where clause are around 1. I am sending 
  the "SHOW INNODB STATUS also. Can somebody let me know, why this is taking 
  such a long time ? And how to optimize this query ?
  
  Thanks
  Prasad
  


  The information contained in this electronic message 
and any attachments to this message are intended for the exclusive use 
of the addressee(s) and may contain proprietary, confidential or 
privileged information. If you are not the intended recipient, you 
should 

Re: Query problem

[Rhino]

- Original Message - 
From: John Meyer [EMAIL PROTECTED]

To: List: MySQL mysql@lists.mysql.com
Sent: Tuesday, May 30, 2006 5:09 PM
Subject: Query problem



Setup

TITLES:
TITLE_ID


AUTHORS:
AUTHOR_ID


TITLE_AUTHOR:
(TITLE_ID,AUTHOR_ID)


Problem:
Given a title, I need to find all the authors who aren't connected with 
that particular book.




That's a pretty odd requirement, I must say. If your database has thousands 
or millions of books, you'd have to assume that virtually ALL of the authors 
in the database are NOT connected with a particular book.


I'm trying to think of a situation where that list of people who were not 
connected with the book was actually useful Okay, maybe if you were 
looking for authors who could review the book, the query you want could be 
useful for identifying potential reviewers. Even if that was thousands of 
authors, it's still a smaller list than the list of all human beings on 
Earth :-)


In any case, the query is pretty simple assuming you are using a version of 
MySQL which supports  subqueries:


select AUTHOR_ID
from AUTHORS
where AUTHOR_ID not in
   (select AUTHOR_ID
   from TITLE_AUTHOR
   where TITLE_ID = 123)

You simply plug in the title_id of the book in question in place of 123 and 
you're all set.


If I were creating the database, I would have an id _and_ an author name in 
the AUTHORS table and I'd have an id _and_ a title in the TITLES table. Then 
I'd modify the query above to do joins so that the result showed me the 
author names and searched so that I was looking for the book title, not the 
book id. But I'm guessing that you already have that in mind and just 
simplified the question to get the bare essence of it. Or maybe you only 
have a few dozen books and will quickly memorize the author names and titles 
that go with each author id and title id.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 29/05/2006


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

Re: Baffled by error

[Rhino]

- Original Message - 
From: Mike Blezien [EMAIL PROTECTED]

To: MySQL List mysql@lists.mysql.com
Sent: Tuesday, May 16, 2006 1:29 PM
Subject: Baffled by error



Hello,

MySQL 4.1.12

trying to figure out why I keep getting this error with the following 
query:


SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins 
FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate 
 = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9

AND SUM(c.agent_product_time) = '500' GROUP BY a.account_id
ORDER BY mins

ERROR: # - Invalid use of group function
Any help appreciated...

I mostly use DB2, not MySQL, and I know that MySQL is a bit looser about 
what it will allow in its SQL. However, if this were happening in DB2, I'd 
say that it's because you needed to change the GROUP BY  to say:


   GROUP BY a.account_id, a.company

In DB2, the GROUP BY normally needs to include EVERY column/expression from 
the SELECT clause that is not in a column function. In your case, you are 
doing a SUM on c.agent_product_time so that means DB2 would expect you to 
put BOTH of the other two columns from the SELECT in your GROUP BY.


Now, if each account_id is associated with a single company, this should 
give you the result you probably envisioned. But if a given account_id can 
be associated with more than one company, then your revised query will give 
you one sum for each COMBINATION of account_id and company. For example:


ACCOUNT_IDCOMPANYMINS
001  ABC Co.45
001   DEF Ltd.   90


But maybe that's what you wanted all along?

--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006


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

Re: Baffled by error

[Rhino]

- Original Message - 
From: Rhino [EMAIL PROTECTED]
To: Mike Blezien [EMAIL PROTECTED]; MySQL List 
mysql@lists.mysql.com

Sent: Tuesday, May 16, 2006 1:56 PM
Subject: Re: Baffled by error




- Original Message - 
From: Mike Blezien [EMAIL PROTECTED]

To: MySQL List mysql@lists.mysql.com
Sent: Tuesday, May 16, 2006 1:29 PM
Subject: Baffled by error



Hello,

MySQL 4.1.12

trying to figure out why I keep getting this error with the following 
query:


SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins 
FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate 
 = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9

AND SUM(c.agent_product_time) = '500' GROUP BY a.account_id
ORDER BY mins

ERROR: # - Invalid use of group function
Any help appreciated...

I mostly use DB2, not MySQL, and I know that MySQL is a bit looser about 
what it will allow in its SQL. However, if this were happening in DB2, I'd 
say that it's because you needed to change the GROUP BY  to say:


   GROUP BY a.account_id, a.company


Sorry, I didn't notice the name in the SELECT. Therefore, the GROUP BY 
should be:


   GROUP BY a.account_id, a.name, a.company


In DB2, the GROUP BY normally needs to include EVERY column/expression 
from the SELECT clause that is not in a column function. In your case, you 
are doing a SUM on c.agent_product_time so that means DB2 would expect you 
to put BOTH of the other two columns from the SELECT in your GROUP BY.


Oops, that should be:

DB2 would expect you to put ALL of the other THREE columns from the select 
in your GROUP BY.




Now, if each account_id is associated with a single company, this should 
give you the result you probably envisioned. But if a given account_id can 
be associated with more than one company, then your revised query will 
give you one sum for each COMBINATION of account_id and company. For 
example:


ACCOUNT_IDCOMPANYMINS
001  ABC Co.45
001   DEF Ltd.   90


Oops, that should be:

Now, if each account_id is associated with a single name and company, this 
should

give you the result you probably envisioned. But if a given account_id can
be associated with more than one company and the company with more than one 
name, then your revised query will give you one sum for each COMBINATION of 
account_id, name and company. For example:


ACCOUNT_IDNAME COMPANYMINS
001  Bob ABC Co.45
001  DaveABC Co.20
001  Mary DEF Ltd.   90
001  Jane  DEF Ltd.  190



But maybe that's what you wanted all along?



--
Rhino


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006


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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006


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

Re: Returning records in a circle

[Rhino]

- Original Message - 
From: Steffan A. Cline [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Friday, May 12, 2006 1:38 PM
Subject: Returning records in a circle



Is there a way I can get a set of records incrementally such as to get 2
then the next query get the next 2 then at the end of all records to get 
the

2 from the beginning? I need to keep going incrementally by 2 in a circle.



Are you trying to get these rows purely via SQL at the command line or in an 
SQL script? Or would an application be an option for you?


If you are not willing to consider application code to grab the rows you 
want, the answer to your question is maybe. SQL has always been intended 
to return ALL of the rows that satisfy a query with a single invocation of 
the query, no matter how many rows that is. So if your query says:


   select * from mytab;

you will normally get all of the rows that satisfy that query in one go, 
whether there are 0 rows, 100 rows, or a 100 million rows in the result.


You _might_ be able to get the results you want by using the LIMIT clause. 
I'm not sure what version of MySQL you are using but the LIMIT clause is 
described in the MySQL 3.23/4.0/4.1 manual on this page: 
http://dev.mysql.com/doc/refman/4.1/en/select.html. The problem is that 
you'll still pretty much need some sort of script in order to keep executing 
the query to get the next two records and you may need to change the 
parameters of the LIMIT clause at the same time.


If you are willing to write application code, things get a lot easier. For 
instance, a Java program could easily grab rows from a result set for you 
two at a time, let you process them, then grab two more, etc. I expect that 
it would similarly easy to do the same thing in Perl and PHP and C.


In short, a program gives you a lot more ability to do what you want to do 
with your database data. But some shops have very little programming 
expertise and prefer to do everything via SQL. If you work for one of those 
shops, you might not be able to get your records two at a time with SQL 
alone, unless you can write a script that takes advantage of the LIMIT 
clause.


I don't pretend to know MySQL exhaustively so someone else may have another 
suggestion for you but the only two approaches I can think of that might 
meet your needs are to use the LIMIT clause or to write an application.


--
Rhino




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/337 - Release Date: 11/05/2006


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

Re: Returning records in a circle

[Rhino]

I'm sorry but I think you're all making this more complicated than it needs 
to be.


I could easily write a Java program that obtained a result set, then 
processed that set in almost any way you wanted, including two records at a 
time. I could probably do the same in Perl or any other language supported 
by MySQL, although it might take a bit of time to learn the necessary parts 
of the language. Processing a result set is a very well-understood process 
and has been widely done millions of times in dozens of languages. You 
should not need to add any columns to the table to do it either.


I might be able to offer more detail - and maybe even an example! - if a few 
things were explained to me. I'm still not clear on why two records are 
being processed at a time and what the relationship is between the records. 
About the only time I see twinned records are when someone is doing an 
accounting application where each debit is matched by an offsetting credit. 
But this doesn't seem to the case here. Perhaps you don't need to process 
two records at once?


Also, am I right in assuming that you are allowing new records to be written 
to the table - and allowing existing records to be updated - while you run 
your query? If yes, is it possible that the keys of the new records will be 
interspersed with the existing keys? Or will new records always have a key 
value that is higher than the highest previous key value? If inserts and 
updates are happening, do they absolutely HAVE to happen simultaneously with 
your query? Or could you store them off to the side briefly or even just 
suspend the insert and update applications while the query runs?


If there are no inserts and updates to worry about, you should be able to 
avoid any updates of duplicates by simply sorting all of the desired rows 
into order based on the key and then processing them one (or two) at a time. 
Things get a bit trickier if the table is being updated/inserted while your 
new program is running.


I don't want to say much more until you've clarified what it is you are 
doing



--
Rhino





- Original Message - 
From: George Law [EMAIL PROTECTED]

To: Steffan A. Cline [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, May 12, 2006 3:33 PM
Subject: RE: Returning records in a circle


I ran into this same type of question the other evening at a local
linux group.

I think that once you reach the end of the results set the only way to
start back at the beginning of that results set is to do the query
again.

Once option - do your query and grab all the rows
load them into a data structure - ie - a list of associative arrays


Then all you need to do is incrementally go through the array.
when you hit the end, just reset the index back to 0.


-Original Message-
From: Steffan A. Cline [mailto:[EMAIL PROTECTED]
Sent: Friday, May 12, 2006 2:52 PM
To: mysql@lists.mysql.com
Subject: Re: Returning records in a circle

Well, basically it can be done to an extent in some kind of code.
Basically
I am drawing from a table 2 records at a time. I want to make sure that
all
records are pulled at least once. If at all possible not to have 2 from
the
same vendor.

So, in this case.

I have columns id, html, vendor

So I can add columns as needed. I tried adding a flag and after
returning to
the client the 2 records I'd mark it as flag = 1 then search like this
Select id, html from urls order by flag, id desc limit 3
Then after I have those I would then set the last of the 3 to flag = 1
so
that on the next search I get the 2 after. In theory it worked fine but
when
multiple people hit the page simultaneously I had flags in different
places
and not in order. Maybe just mark them as flag = 1 after returned and
then
on search if found is 0 then set all to flag = 0 so they can be seen
again?
This doesn't seem so bad but them I guess I'd use distinct? If I cant
use
distinct with other parameters...
ex: select id,html distinct(vendor) from urls where flag=0 limit 2;
Would it be like :
Select id,html from urls where flag = 0 group by distinct(vendor) limit
2

?

Thanks

Steffan

---
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net  USA
AIM : SteffanC  ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
 Lasso Partner Alliance Member
---




From: Rhino [EMAIL PROTECTED]
Date: Fri, 12 May 2006 14:20:10 -0400
To: Steffan A. Cline [EMAIL PROTECTED], mysql@lists.mysql.com
Subject: Re: Returning records in a circle


- Original Message -
From: Steffan A. Cline [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, May 12, 2006 1:38 PM
Subject: Returning records in a circle



Is there a way I can

Re: PRINT statement?

[Rhino]

 
is to always use the first SELECT to set the column names for the result set 
AND to control the width of the result set columns.


--

Okay then, aside from the issue of string expressions, which I'm not sure 
about yet, I think we can see that SELECT can do everything else that the 
PRINT command supports.


--
Rhino

- Original Message - 
From: Quentin Bennett [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]; Stephen Cook [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Wednesday, May 10, 2006 11:59 PM
Subject: RE: PRINT statement?



From Transact-SQL Help file:


PRINT

Returns a user-defined message to the client.

Syntax
PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr

Arguments
'any ASCII text'

Is a string of text.

@local_variable

Is a variable of any valid character data type. @local_variable must be char 
or varchar, or be able to be implicitly converted to those data types.


@@FUNCTION

Is a function that returns string results. @@FUNCTION must be char or 
varchar, or be able to be implicitly converted to those data types.


string_expr

Is an expression that returns a string. Can include concatenated literal 
values and variables. The message string can be up to 8,000 characters long; 
any characters after 8,000 are truncated.



-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Thursday, 11 May 2006 3:51 p.m.
To: Stephen Cook
Cc: MySQL List
Subject: Re: PRINT statement?


I am not familiar with the PRINT command so I don't know what it does. I
played with MS SQL Server once for a couple of days a few years back and
that is the only contact I've ever had with SQL Server.

If you can tell me what PRINT does, in detail, maybe I can suggest another
alternative.

--
Rhino

- Original Message - 
From: Stephen Cook [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Wednesday, May 10, 2006 8:09 PM
Subject: Re: PRINT statement?



I've started using the SELECT with no other clauses but I am still curious
about a PRINT-like command.  It is for SQL scripts.

Rhino wrote:


- Original Message - From: Stephen Cook [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Sunday, May 07, 2006 3:53 AM
Subject: PRINT statement?



Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)?

It would be handy to debug some scripts.


If you're talking about a script that is running SQL, you can simply use
the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or
HAVING clauses. For example:

   select Creating Foo table as Action;

will produce the following output:

   +--+
   | Action   |
   +--+
   | Creating Foo table |
   +--+
   1 row in set (0.00 sec)

If you're talking about an OS script, you can use OS commands to display
things. For example, I have some BASH scripts on our Linux server so I
can use the BASH echo command, like this:

   #!/bin/bash
   report_date=`/bin/date`
   echo Report Date: $report_date;

to produce this output:

   Report Date: Sun May 7 09:42:57 EDT 2006


--
Rhino







--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006


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

The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/336 - Release Date: 10/05/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/336 - Release Date: 10/05/2006


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

Re: Sum of counts

[Rhino]

Hi Chris, Joerg, and everyone else following this discussion,

Joerg, you are correct; the best way to sum the tables is individually and 
then add the sums together with program logic of some kind, such as might be 
found in a script or application program or stored procedure.


I'm afraid I jumped in and gave correct but irrelevant information. I saw 
that Chris's query lacked joining conditions so I explained why they were 
needed and how to write them. Unfortunately, this was premature: I should 
have thought about the basic problem more carefully first. Joerg, you are 
absolutely right: if one table contains students and another contains 
teachers, you don't count the number of people in the school by JOINING the 
tables together. You count the people in each table separately and add the 
two sums together. I don't know why that didn't come to me when I read 
Chris's question but it didn't.


My apologies to all for wasting your time with an inappropriate solution. 
I'll try not to do that again!


--
Rhino

- Original Message - 
From: Joerg Bruehe [EMAIL PROTECTED]

To: Chris Sansom [EMAIL PROTECTED]
Cc: Rhino [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com
Sent: Wednesday, May 10, 2006 12:04 PM
Subject: Re: Sum of counts



Hi Chris, all,


Re-inserting Chris' original question:
| I want to get a total of entries from four tables which all match a
| particular id. The result for the id I'm testing (21) should be 233.
| In my naivety, I thought something like this would work:
|
| select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id)
| from table_a as a, table_b as b, table_c as c, table_d as d
| where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21


IMO, this is simply no task for a join,
probably not a task for any single SQL statement.

The easiest way is to have four separate SELECT count(*) FROM table_? 
with the '?' replaced by 'a' .. 'd'.



More explanations below:


Chris Sansom wrote:

At 13:28 -0400 9/5/06, Rhino wrote:
The reason you are getting so many rows has nothing to do with the way 
you are using the count(*) function and adding the different count() 
results together. The problem is that you are doing your joins 
incorrectly... In your case, I think you need to change the original 
query to this:


   select count(a.id) + count(b.id) + count(c.id) + count(d.id)
   from table_a as a, table_b as b, table_c as c, table_d as d
   where a.id = b.id
   and b.id = c.id
   and c.id = d.id
   and a.id = 21
   and b.id = 21
   and c.id = 21
   and d.id = 21


First, the transitive equality on the 4 id columns (first 3 conditions) 
together with one restriction to 21 (say, on a.id)

has no different effect than the 4 conditions = 21,
in mathematical view 3 of these 7 conditions can be dropped.
(Not any 3, but several different combinations.)

But that is not the cause of the problem - this is the join approach:
Remember that a join does a cartesian product, this is in no way helpful 
to the solution of your task!



Let us construct a minimized example: Just two tables, each with three 
rows, all having that magic value 21:


Table aTable b
id  cntid  cnt
21   1 21   4
21   2 21   5
21   3 21   6

Doing a natural join on the id column will yield 9 rows:

a.id  a.cnt  b.id  b.cnt
211  214
211  215
211  216
212  214
212  215
212  216
213  214
213  215
213  216

Summing a.id and b.id results in 18, where the correct value is 6.




Hi Rhino

Many thanks for the very full and frank response, but sadly it didn't 
work. I do understand exactly what you said, and I even took it further, 
adding in:


and a.id = c.id
and a.id = d.id
and b.id = d.id

...so that every table is thus related to every other one, but I'm 
/still/ getting that damned eight and a half million instead of the 233 I 
expect!


See above -
demanding all 4 columns to be equal to 21
is equivalent to demanding one is 21, and all are equal,
and also equivalent to some other combinations of conditions.

If you have a mathematical education, apply your knowledge of 
transitivity to the problem.




I'm baffled by this, though the version I did with subqueries works very 
nicely (and it's simple enough to do four separate queries and add them 
together in the script for the older MySQL).


Frankly spoken: This is the way to go!


From your problem description, there seems to be no connection between the 
tables that would warrant joining them.


For a very coarse analogy:
If you have separate tables for the teachers and the pupils of a school, 
and want to know the number of all peoples going there daily,

you will not join these two tables,
you will rather count them individually and then add these two values.


HTH,
Jörg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com


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

Re: PRINT statement?

[Rhino]

I am not familiar with the PRINT command so I don't know what it does. I 
played with MS SQL Server once for a couple of days a few years back and 
that is the only contact I've ever had with SQL Server.


If you can tell me what PRINT does, in detail, maybe I can suggest another 
alternative.


--
Rhino

- Original Message - 
From: Stephen Cook [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Wednesday, May 10, 2006 8:09 PM
Subject: Re: PRINT statement?


I've started using the SELECT with no other clauses but I am still curious 
about a PRINT-like command.  It is for SQL scripts.


Rhino wrote:


- Original Message - From: Stephen Cook [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Sunday, May 07, 2006 3:53 AM
Subject: PRINT statement?



Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)?

It would be handy to debug some scripts.

If you're talking about a script that is running SQL, you can simply use 
the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or 
HAVING clauses. For example:


   select Creating Foo table as Action;

will produce the following output:

   +--+
   | Action   |
   +--+
   | Creating Foo table |
   +--+
   1 row in set (0.00 sec)

If you're talking about an OS script, you can use OS commands to display 
things. For example, I have some BASH scripts on our Linux server so I 
can use the BASH echo command, like this:


   #!/bin/bash
   report_date=`/bin/date`
   echo Report Date: $report_date;

to produce this output:

   Report Date: Sun May 7 09:42:57 EDT 2006


--
Rhino







--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006


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

Re: How to convert strings to 'proper case' ?

[Rhino]

I'd be surprised if things actually turned out to be quite as simple as you 
describe. For example, let's say that your column actually contained book 
titles or names of people. Would you really want to see any of the following 
in your column:
- A Diplomatic History Of The Un? (more likely: A Diplomatic History of the 
UN)

- A.b. Mcdonald? (more likely: A. B. McDonald)
- The Life And Times Of King George Iii? (more likely: The Life and Times of 
King George III)


In any case, I don't think a simple SQL UPDATE will do what you want to do, 
at least not very easily. You'll almost certainly want some real programming 
statements to do the string manipulation that you need.


You haven't said whether your data is already in tables or whether you are 
planning to load the data into new tables. You also haven't said whether the 
data is in all upper case, all lower case or in some form of mixed case.


If the data is not already in tables, I'd be inclined to change the case of 
the data with a scripting language that was appropriate for your operating 
system and then load the corrected data into the tables. For instance, on 
Linux, I might write a bash shell script to reformat the data, which is 
presumably sitting in a flat file somewhere, then load the reformatted data 
into the tables. This gives you the option of choosing from several 
different scripting languages, some of which you may already know fluently. 
That could save you a lot of time.


If the data is already in tables, you could unload it to a flat file, fix it 
with a shell script, and then reload it to the database. Or, you could write 
a User Defined Function (UDF) or Stored Procedure (SP) in order to update 
the existing values. Then you could call the procedure or function to do the 
necessary work at any time you found data with the wrong case.


If you write an SP, you could pass the table name and column name to the 
procedure. Then, the procedure could do a loop that operated on every value 
in that column of the table. For each row, it could read the existing value, 
create a revised value using string manipulation techniques, then update the 
current value with the revised value. Creating the revised value would 
likely be the only tricky part and even that might not be very hard if it 
really were only necessary to convert the first letter of each word to a 
capital. If the code actually had to handle more complex cases like the ones 
I put at the beginning of this note, the code would be more complicated; it 
might even be impossible if the language you were using for the SP or UDF 
didn't have many string manipulation techniques. In that case, you might 
need to choose a different language or you could go back to unloading the 
data from the database, manipulating it outside MySQL, and then reloading 
it.


That's all I'm going to say for the moment but if you decide to try a UDF or 
SP and can state what programming languages you are willing to use for the 
code - and whether the conversion is really as simple as capitalizing just 
the first letters of the words - I might be able to give you more specific 
suggestions.


Unfortunately, I don't have a current version of MySQL and can't really 
install one so I can only talk hypothetically, based on UDFs and SPs that 
I've written in DB2 and on what I've seen in the MySQL manuals. I can't 
actually write you a simple UDF or SP for MySQL that would do at least the 
basic parts of the conversion you want. Maybe someone else on this mailing 
list has an example that you could have which is actually known to work in 
MySQL. Otherwise, you might only have hypothetical guidelines and manual 
articles to guide you as you try to write your UDF or SP. That can be 
time-consuming if you've never done any coding like that before. But it 
could be fun too if you are in the right frame of mind!


--
Rhino



- Original Message - 
From: C.R.Vegelin [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, May 09, 2006 1:41 AM
Subject: How to convert strings to 'proper case' ?


Hi List,

I want to convert strings to proper-case,
where only the 1st char of each word is uppercase,
such as: This Is An Example.
Any idea how to do this with MySQL 5.0.15 ?

Thanks, Cor





No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006


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

Re: Order by leads to an empty set.

[Rhino]

- Original Message - 
From: Mohammed Sameer [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, May 09, 2006 9:56 AM
Subject: Order by leads to an empty set.



Hi all,

I have a strange problem and I can't really understand what's going on!

mysql SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' 
AND (n.uid = 1 OR n.status = 1) ORDER BY n.created desc;

Empty set (0.00 sec)

mysql SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' 
AND (n.uid = 1 OR n.status = 1);

+-+++
| nid | sticky | created|
+-+++
|  73 |  0 | 1141048224 |
|  75 |  0 | 1141736038 |
.
|  93 |  0 | 1145039899 |
|  97 |  0 | 1145189131 |
+-+++
51 rows in set (0.00 sec)


I'm using a standar drupal installation:
mysql desc node;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra  |
+--+--+--+-+-++
| nid  | int(10) unsigned |  | PRI | NULL| auto_increment |
| type | varchar(32)  |  | MUL | ||
| title| varchar(128) |  | MUL | ||
| uid  | int(10)  |  | MUL | 0   ||
| status   | int(4)   |  | MUL | 1   ||
| created  | int(11)  |  | MUL | 0   ||
| changed  | int(11)  |  | MUL | 0   ||
| comment  | int(2)   |  | | 0   ||
| promote  | int(2)   |  | MUL | 0   ||
| moderate | int(2)   |  | MUL | 0   ||
| sticky   | int(2)   |  | | 0   ||
| vid  | int(10) unsigned |  | | 0   ||
+--+--+--+-+-++
12 rows in set (0.00 sec)

| version | 4.1.12
CentOS release 4.2 (Final)

Any idea ? Am I doing something wrong ? Did I hit a bug ?

If the two queries really are identical except that one has an ORDER BY 
added to it, this would appear to be a bug, although it would be a VERY 
strange one! But a bug isn't the _only_ possibility; in fact, I can think of 
three things that are probably much more likely.


1. Is there any possibility that a DELETE took place between the first query 
and the second? If the query without the ORDER BY returned 51 rows, then a 
DELETE executed by you - or someone unknown to you - removed all the rows, 
then the query WITH the ORDER BY executed, this would explain the behaviour 
you saw without any bug being involved.


2. Is there any possibility that the two queries took place against 
different tables or databases or systems? If the query without the ORDER BY 
ran against a version of the table that had 51 rows in it (perhaps the 
production version of the table) and the query with the ORDER BY ran against 
a different version of the table, maybe one that was empty (perhaps a test 
version of the table), this would explain the behaviour you saw without any 
bug being involved. This kind of thing could happen if you had two command 
prompts open and each was pointed at a different version of the table. If 
you were juggling several things at once, you might forget that the two 
prompts pointed at different systems and not realize that the queries had 
been done against different tables.


3. Is there any possibility that the query with the ORDER BY which you have 
given us in your email is not the one which returned 0 rows and that it is 
not identical to the other query that lacks the ORDER BY? The best approach 
for reporting query problems is to copy and paste the query from your MySQL 
environment into your email but some people simply type the query directly 
into the email. That opens the possibility that you typed the query 
inaccurately and may explain the problem.



Frankly, I find it quite unlikely that ORDER BY would fail so I would 
strongly recommend that you consider the alternate scenarios I have 
suggested and rule those out first. If you can rule them out, then you may 
have encountered a real bug. Naturally, you should report that bug if you 
can satisfy yourself that it really IS a bug.


--
Rhino

Another 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006


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

Re: Sum of counts

[Rhino]

- Original Message - 
From: Chris Sansom [EMAIL PROTECTED]

To: MySQL List mysql@lists.mysql.com
Sent: Tuesday, May 09, 2006 11:47 AM
Subject: Sum of counts



Here comes a newbie question...

I want to get a total of entries from four tables which all match a 
particular id. The result for the id I'm testing (21) should be 233. In my 
naivety, I thought something like this would work:


select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id)
from table_a as a, table_b as b, table_c as c, table_d as d
where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21

...but no - I get about eight and a half million! I tried fiddling about 
with joins but got no better luck. In fact if I replace those +'s with 
commas I get four equal values of about 2.12 million.


In the end I got the correct result like this:

select
(select count(*) from table_a where id = 21) +
(select count(*) from table_b where id = 21) +
(select count(*) from table_c where id = 21) +
(select count(*) from table_d where id = 21)
as total

Two questions:

1  Is this the best way to do it? If not, what is?

2  This is fine in MySQL 5 (on my development platform), but 3.23 (on the 
live platform until the host upgrades us) doesn't support subqueries, so 
as an interim measure is there any better way than doing four separate 
queries and adding up the total in the PHP script?




The reason you are getting so many rows has nothing to do with the way you 
are using the count(*) function and adding the different count() results 
together. The problem is that you are doing your joins incorrectly. Whenever 
you join tables, you need to specify what the tables have in common by 
writing joining predicates; the number of joining predicates you usually 
write is the number of tables being joined minus one. Therefore, since you 
are joining four tables, you need three joining predicates: one to join the 
first table to the second, one to join the second table to the third, and 
one to join the third table to the fourth.


A joining predicate looks like this:

   table1.col4 = table2.col3

In other words, there are two column names with an equal sign in the middle. 
I think you are attempting to get the same result by saying a.id. = 21 and 
b_id = 21 (etc.) but this is not having the effect that you want. In your 
case, I think you need to change the original query to this:


   select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id)
   from table_a as a, table_b as b, table_c as c, table_d as d
   where a.id = b.id
   and b.id = c.id
   and c.id = d.id
   and a.id = 21
   and b.id = 21
   and c.id = 21
   and d.id = 21

This says that:
-  a row in table_a is joined to a row in table_b when the 'id' value in 
table_a is identical (and non-null) to the 'id' value in table_b
-  a row in table_b is joined to a row in table_c when the 'id' value in 
table_b is identical (and non-null) to the 'id' value in table_c
-  a row in table_c is joined to a row in table_d when the 'id' value in 
table_c is identical (and non-null) to the 'id' value in table_d
- the final result should only have rows where the id columns in each of the 
four tables contain 21.


The conditions you wrote, namely 'a.id = 21', 'b.id = 21', 'c.id = 21' and 
'd.id = 21' are called local predicates, i.e. conditions that affect only 
one table. In your case, you've said that out of all the rows in the result 
set after the joins have been done, you only want rows where a.id, b.id, 
c.id, and d.id are 21.


Unfortunately, you don't have any joining clauses so MySQL joins EVERY ROW 
of the first table to EVERY row of the second table and EVERY ROW of the 
second table gets joined to EVERY row of the third table and EVERY row of 
the third table gets joined to EVERY row of the fourth table. This is called 
a Cartesian product and is usually considered very bad news because it gives 
you huge result sets in which most of the rows are joined to rows to which 
they shouldn't be joined. I think you wrote this query on the assumption 
that your local predicates would ensure that only the correct rows were 
joined but, as you can see, that isn't the case.


If you try the query I gave you, you should see that it only joins rows when 
the ids are equal; then, the local predicates ('a.id = 21', etc.) ensure 
that you only get the joined rows you want, namely the ones that contain 
21.)


This is a very common beginner mistake. Unfortunately, the MySQL manual 
doesn't yet explain how to do joins very well; this is something that is 
badly needed, in my opinion.


--
Rhino




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006


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

Re: comparing postgis with mysql

[Rhino]

There used to be a page at the MySQL site which showed comparison between 
MySQL and its competitors. For example, one page contrasted MySQL with DB2. 
These pages were lengthy and compared the product feature for feature.


I just had a look but I'm having trouble finding the comparison pages. I'm 
not sure if they have been removed or whether the site has been redesigned 
so that you can find things more easily - which usually results in it being 
_harder_ to find things, in my experience :-)


Have a look for yourself; maybe you can find those comparisons. I'm not sure 
if postgis was one of the databases compared to MySQL though.


--
Rhino


- Original Message - 
From: Parang Saraf [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, May 09, 2006 4:06 PM
Subject: comparing postgis with mysql


hello,

I am relatively new in this field. I am designing a database to store the
events extracted from the oceans. This project later demands of publishing
data on web. I am not able to decide which database to use. Mysql with
spatial extension or the postgis one. I would prefer to use windows 
platform.



Can someone suggest or can provide me with some links that compare the two
databases. Do you think Mysql has a better spatial elements handling
capacity in comparison to postgis.?

please reply soon.

Thanks and Regards
Parang Saraf
[EMAIL PROTECTED]






No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006


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

Re: PRINT statement?

[Rhino]

- Original Message - 
From: Stephen Cook [EMAIL PROTECTED]

To: MySQL List mysql@lists.mysql.com
Sent: Sunday, May 07, 2006 3:53 AM
Subject: PRINT statement?



Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)?

It would be handy to debug some scripts.

If you're talking about a script that is running SQL, you can simply use the 
SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or HAVING 
clauses. For example:


   select Creating Foo table as Action;

will produce the following output:

   +--+
   | Action   |
   +--+
   | Creating Foo table |
   +--+
   1 row in set (0.00 sec)

If you're talking about an OS script, you can use OS commands to display 
things. For example, I have some BASH scripts on our Linux server so I can 
use the BASH echo command, like this:


   #!/bin/bash
   report_date=`/bin/date`
   echo Report Date: $report_date;

to produce this output:

   Report Date: Sun May 7 09:42:57 EDT 2006


--
Rhino




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 05/05/2006


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

Re: How to synchronize two databases

[Rhino]

You may want to consider automating the synchronization of your databases 
via replication. Check out the Replication chapter in the MySQL manual 
(chapter 6 in the MySQL 5.0 manual).


I don't know if Replication can cope with changes to the table structure - 
I've never played with Replication - but the manual should tell you whether 
this is a problem or is handled well by MySQL.


--
Rhino

- Original Message - 
From: abhishek jain [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Sunday, May 07, 2006 11:41 AM
Subject: How to synchronize two databases


Dear Friends,
I have two databases with the same name and table structure but the content
is different, I need to synchronize them i mean the data inside one database
need to be updated with the other one leaving the etries which are similar.
Let me give a single example:

DB1:table1
Rows like : userid  name
Ist row   1   abhishek
IInd row  2   jain
III row 3   rahul



DB2:table1
Rows like : userid  name
Ist row   1   abhishek
IInd row  2   amitabh
III row 3   vijay

Now i want  like :
Rows like : userid  name
Ist row   1   abhishek
IInd row  2   jain
III row 3   rahul
IV row 4   amitabh
V row   5  vijay

NOTE: Here DB1 = database names , table1 = table name .
How can i do that via mysql or that i need to prepare a script for this can
anyone help.
Regards,
Abhishek Jain






No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 05/05/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 05/05/2006


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

Re: Output to a file

[Rhino]

- Original Message - 
From: Payne [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Friday, May 05, 2006 12:09 AM
Subject: Output to a file



Hey,

been trying to output a select statment to a file, all the books I have 
only show how to input from a file, what is the correct way


I thought I could do select * from my_toy  `/tmp/my_toys`

But I get an error.



Here is a snippet from some documentation about MySQL which I wrote for 
myself. It shows a  different technique for capturing output from a batch 
file into an output file; if the batch file contains 'select * from my_toy', 
it will capture the output in a file. It's not exactly what you want but 
maybe it will be close enough.


Running a script from OS prompt

If you are connected to the database and are at an OS prompt, use this 
pattern:


mysql  batch-file  output-file

For example, if I want to run a script or batch file named my_batch_file.sql 
and write the output of the script to a file named my_batch_file.out, I'd 
need to do this:


mysql  my_batch_file.sql  my_batch_file.out

If you are NOT connected to the database, use this pattern:

mysql -u username -p  batch_file  output_file

For example, if your user name is 'fred' and your password is 'dino' and you 
want to run a script or batch file named my_batch_file.sql against database 
'barf' and write the output to a file called my_batch_file.out, you'll need 
to do this:


mysql barf -u fred -p  my_batch_file.sql  my_batch_file.out

[Be sure to supply the password when prompted.]

--

Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.4/332 - Release Date: 04/05/2006


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

Re: Effective-dating

[Rhino]

I don't think I noticed the original post for this discussion when it first 
arrived - or maybe I did and mistook it for spam having to do with social 
dating ;-) - but I just saw Sheeri's reply and want to jump in if I may.


Most professional databases in corporate environments that I have seen over 
the years use the approach of having begin and end dates on each row. Those 
dates indicate when the row is valid. For instance, if the row is recording 
interest rates, the table would get a new row whenever the current interest 
rate changed. Assuming no one knew when the interest rate would change 
again, the new row would typically set the current date as the begin date 
and the end date would be set to null. The row for the previous interest 
rate would be updated at the same time and have its end date set to the 
current date (or the day before in some cases).


Queries could easily determine the current interest rate by just finding the 
only row in the table whose end date was null. Older interest rates could be 
find by searching for the row whose begin date was on or before the search 
date and whose end date was on or after the search date. Eventually, as 
older interest rates were no longer desired for queries very often, you 
might move them to archive tables with names like IntRates_2005 and remove 
them from the main interest rates table altogether.


I don't know enough about performance in MySQL to venture an opinion of how 
this would perform. You'll need to research that yourself.


--
Rhino


- Original Message - 
From: sheeri kritzer [EMAIL PROTECTED]

To: Douglas Sims [EMAIL PROTECTED]
Cc: mysql List mysql@lists.mysql.com
Sent: Thursday, May 04, 2006 3:15 PM
Subject: Re: Effective-dating


Coming to the table SOOO late.  But this has special relevance as I'm
working on an application that stores event dates and therefore will
also need to solve this problem.

The biggest question I have is What will this be used for?  My first
thought is to have at least 2 tables -- one table with the rows that
are expired and another with non-expired rows.

In an events database, for example probaby 75% of the queries will be
current and future events.  Folks will be interested in past events,
but usually as a part of a separate logical flow.  Users may want past
events for research, but probably won't need to compare previous
events to current/future ones.  (they might want to compare events all
in the past, for instance how many people attended each meeting,
average rating, etc; or they might want to compare current/future
events for conflicts. etc.  But rarely both in the same query).

If you have reporting to do that might include both tables, you could
replicate them to MyISAM tables and make a MERGE table for your
reporting purposes.

Sorry this is so late; usually I get to MySQL list mail about once a
week, but the Users Conference took up a lot of my time!  I hope this
helps

-Sheeri

On 4/10/06, Douglas Sims [EMAIL PROTECTED] wrote:


Does anyone know of a thorough discussion of effective dating on the
web, particularly with respect to MySQL, or have any opinions you
could share?

I've worked with effective-dated tables in MS SQL Server and never
been particularly awe-struck by how well it works.  I can think of
three ways of doing it:

1) Store a Begin date and an End date for each row and then
reference the row with 'WHERE {transaction date} BETWEEN {begin} AND
END.  This inevitably winds up with overlapping rows that shouldn't
be or gaps where you don't want them, and also requires an extra date
column, but the select queries are simplest.  Also, what about
indexing the dates?

2) Store an Expires date with each row, but then to find the actual
row you have to do a subselect or some messy joins and I'm not at all
confident this will be optimized reasonably.

3) Store an Effective as of date with each row but this has
essentially the same problem as 2.

None of the SQL books on my shelf even mentions this, including
Jeremy Zawodny's Hi-Performance MySQL and the MySQL Reference
Manual.

This page is interesting but doesn't explain the different options
nor try to analyze which is best and under what circumstances: http://
llamasery.com/forums/showthread.php?p=34945

Strangely enough, most of what I find by googling the topic
effective dating has to do with meeting girls efficiently - which
is also interesting, but outside the scope of this list and not
immediately relevant to the system I'm working on.


Douglas Sims
[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]


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.3/331 - Release Date: 03/05/2006



--
No virus found

Re: How to find size of my database

[Rhino]

Thank you VERY much, Mark! Your reply is EXCELLENT and gives us all a lot of 
very useful information.


This is the kind of information that should be in the MySQL manual. Paul 
DuBois, if you're reading this, please consider adding all of Mark's 
information to the manual!


I think this reply also points to a definite need within the MySQL 
community, namely monitoring tools. After all, any decent administrator is 
going to want to know the size of his databases at some point. I don't 
follow the development of tools for MySQL but if there are no tools to 
monitor database size, I would imagine there is a definite market for such 
tools. After all, why should each of us independently re-invent the wheel? 
This seems like an opportunity for an entrepreneurial type to make some 
money serving a market. Or for people who have already developed monitoring 
tools to contribute them freely to the MySQL community.


Thanks again, Mark! I know I will revisit your reply when I get around to 
doing proper monitoring of my MySQL databases when they finally go into 
production.


--
Rhino

- Original Message - 
From: Mark Leith [EMAIL PROTECTED]

To: Shivaji S [EMAIL PROTECTED]
Cc: Rhino [EMAIL PROTECTED]; [EMAIL PROTECTED]; 
mysql@lists.mysql.com

Sent: Wednesday, May 03, 2006 9:20 AM
Subject: Re: How to find size of my database



On Wed, 03 May 2006 Rhino wrote :
Daniel de Veiga has already answered you on how to determine the size of 
your database by using the file system and simply looking at the size of 
the physical files in your database.


Another possibility is that you could use the SHOW TABLE STATUS command 
in MySQL. If you go to your MySQL prompt and select a database, then use 
the SHOW TABLE STATUS command, like this:


   set tmp;
   show table status like '%';

you'll find a column called Data_length which tells you the length of 
the data file for each table. If you simply add the size of each table 
in the database together, you should have the size of the whole 
database.


Please note that I'm not sure how accurate my suggestion is; you might 
find that Daniel's approach gives you a better answer. I'm not sure if 
the Data_length column considers all the overhead that you might have 
with a table, such as index sizes, pointer sizes, unusable space, etc. 
Perhaps someone else can jump in with a better approach; I'm inclined to 
think it can't be quite as easy as I suggested.



I'll jump in for you..

Indeed,  you are right that Data_length that does not cover space 
allocated but unused (i.e space freed up by DELETE or UPDATE statements 
that is not released back to the filesystem, before an OPTIMIZE TABLE for 
example). There are other columns within the output however - Data_free 
and Index_length.


Therefore to get the total allocated space to a database:

SUM(data_length) +  SUM(index_length)

Total of actual data:

(SUM(data_length) - SUM(data_free)) + SUM(index_length)

Allocated but unused:

SUM(data_free)
Also, even if the Data_length column gives an accurate answer for the 
size of a table, it is rather tedious to have to execute the SHOW TABLE 
STATUS command and then manually sum up the various sizes. I don't think 
you can simply execute an SQL query that does all the work for you, 
which is very unfortunate.


Unfortunately within 4.0 there is no way to do this with a SQL query. You 
can do this on 5.0 however. For example:


SELECT s.schema_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) 
/1024/1024,2),0.00),Mb) total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),Mb) 
data_used,

CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),Mb) data_free,
IFNULL(ROUNDSUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free)) 
/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,

COUNT(table_name) total_tables
FROM INFORMATION_SCHEMA.SCHEMATA s
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema
WHERE s.schema_name = sakila
GROUP BY s.schema_name
ORDER BY pct_used DESC\G
*** 1. row ***
schema_name: sakila
 total_size: 6.62Mb
  data_used: 6.62Mb
  data_free: 0.01Mb
   pct_used: 99.91
total_tables: 22
1 row in set (0.08 sec)
It is entirely possible that there is a MySQL command that gives you the 
actual size of each database directly, although I didn't find it when I 
searched the manual. Again, perhaps someone with more administrative 
experience with MySQL can suggest a better approach. If not, perhaps we 
need to make a feature request of the MySQL people :-) This would appear 
to be a very useful command to create if it doesn't already exist!


I have a little administrative experience ;)

I'm going to confuse the matter now, as the above reports freespace 
correctly for storage engines such as MyISAM, however, it does not report 
the freespace properly within Data_free column for InnoDB tables - the 
freespace

Re: How to find size of my database

[Rhino]

It would be easier to help if you specified what you meant by size.

Do you mean the amount of space the database is using on your hard drive? Or 
the number of tables in the database? Or the number of rows in the tables? 
Or somethng else altogether?


You should also mention which version of MySQL you are using; later versions 
have features and commands not found in earlier versions. Also, the size of 
a give database may differ from version to version of MySQL.


--
Rhino

- Original Message - 
From: Shivaji S [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 3:53 PM
Subject: How to find size of my database



Hi,

is there any command to find the total size of mysql db.

Regards,
Shivaji.





No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006


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

Re: Re: How to find size of my database

[Rhino]

You still haven't said what you mean by size!

Let me put it to you this way: what units would be used in the answer you 
want? In other words, would the answer look like:

1. 234,000 KB?
2. 2.75 million rows?
3. 321 tables?
Or something else altogether?

--
Rhino

- Original Message - 
From: Shivaji S [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 4:33 PM
Subject: Re: Re: How to find size of my database



Hi Rhino,

Thanks for the input.

I mean to say the total Database Size ,that is to see my current Database 
Size.I am using 4.0.20 version


Regards,
Shivaji

On Wed, 03 May 2006 Rhino wrote :

It would be easier to help if you specified what you meant by size.

Do you mean the amount of space the database is using on your hard drive? 
Or the number of tables in the database? Or the number of rows in the 
tables? Or somethng else altogether?


You should also mention which version of MySQL you are using; later 
versions have features and commands not found in earlier versions. Also, 
the size of a give database may differ from version to version of MySQL.


--
Rhino

- Original Message - From: Shivaji S [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 3:53 PM
Subject: How to find size of my database



Hi,

is there any command to find the total size of mysql db.

Regards,
Shivaji.





No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006



-- No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006








No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006


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

Re: Re: Re: How to find size of my database

[Rhino]

Daniel de Veiga has already answered you on how to determine the size of 
your database by using the file system and simply looking at the size of the 
physical files in your database.


Another possibility is that you could use the SHOW TABLE STATUS command in 
MySQL. If you go to your MySQL prompt and select a database, then use the 
SHOW TABLE STATUS command, like this:


   set tmp;
   show table status like '%';

you'll find a column called Data_length which tells you the length of the 
data file for each table. If you simply add the size of each table in the 
database together, you should have the size of the whole database.


Please note that I'm not sure how accurate my suggestion is; you might find 
that Daniel's approach gives you a better answer. I'm not sure if the 
Data_length column considers all the overhead that you might have with a 
table, such as index sizes, pointer sizes, unusable space, etc. Perhaps 
someone else can jump in with a better approach; I'm inclined to think it 
can't be quite as easy as I suggested.


Also, even if the Data_length column gives an accurate answer for the size 
of a table, it is rather tedious to have to execute the SHOW TABLE STATUS 
command and then manually sum up the various sizes. I don't think you can 
simply execute an SQL query that does all the work for you, which is very 
unfortunate.


It is entirely possible that there is a MySQL command that gives you the 
actual size of each database directly, although I didn't find it when I 
searched the manual. Again, perhaps someone with more administrative 
experience with MySQL can suggest a better approach. If not, perhaps we need 
to make a feature request of the MySQL people :-) This would appear to be a 
very useful command to create if it doesn't already exist!


--
Rhino



- Original Message - 
From: Shivaji S [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 5:12 PM
Subject: Re: Re: Re: How to find size of my database



Hi Rhino,

sorry ,for my unclear reply.

the size i meant is in KB or MB..

Regards,
Shivaji


On Wed, 03 May 2006 Rhino wrote :

You still haven't said what you mean by size!

Let me put it to you this way: what units would be used in the answer you 
want? In other words, would the answer look like:

1. 234,000 KB?
2. 2.75 million rows?
3. 321 tables?
Or something else altogether?

--
Rhino

- Original Message - From: Shivaji S [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 4:33 PM
Subject: Re: Re: How to find size of my database



Hi Rhino,

Thanks for the input.

I mean to say the total Database Size ,that is to see my current Database 
Size.I am using 4.0.20 version


Regards,
Shivaji

On Wed, 03 May 2006 Rhino wrote :

It would be easier to help if you specified what you meant by size.

Do you mean the amount of space the database is using on your hard drive? 
Or the number of tables in the database? Or the number of rows in the 
tables? Or somethng else altogether?


You should also mention which version of MySQL you are using; later 
versions have features and commands not found in earlier versions. Also, 
the size of a give database may differ from version to version of MySQL.


--
Rhino

- Original Message - From: Shivaji S [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 3:53 PM
Subject: How to find size of my database



Hi,

is there any command to find the total size of mysql db.

Regards,
Shivaji.





No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006



-- No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006








No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006



-- No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006








No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006


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

Re: need help for my jointure

[Rhino]

First of all, I'm going to guess that English is not your first language and 
tell you that jointure is not the word normally to describe the process of 
combining two tables in a database: the word you want is joining.


Second, there are many kinds of joins and you haven't specified which kind 
you want to do. If you look in the MySQL manual, you will see that there are 
cross joins, inner joins, straight joins, natural joins, left joins, right 
joins, etc. You need to figure out which kind of join you want because your 
decision will affect the way you need to write your SQL.


Third, the manual gives some information and examples on how to do joins. 
You haven't specified which version of MySQL you are using but if it is 
Version 5.0, the topic you want is 
http://dev.mysql.com/doc/refman/5.0/en/join.html. If you are using a 
different version, you can find the various editions on this page 
http://dev.mysql.com/doc/.


Fourth, the manual does _not_ do a very good job of explaining the 
differences between the types of joins. This has been a known deficiency for 
some time and I am disappointed that this has (apparently) not been 
addressed yet. I wish I could suggest a good place to see a clear 
description of how the join types differ but I can't. Maybe someone else 
here has seen a decent tutorial on the differences between the types of 
joins


However, if you plan to do an inner join, which is the kind most people do 
most of the time, your syntax will look like this:


select id, conf
from confs as c inner join conf_id as i on c.id = i.id
where id != '101.33.55.123'

If you need to do a different kind of join, please specify which kind you 
want to do and perhaps someone here can suggest the right syntax.



--
Rhino

- Original Message - 
From: Patrick Aljord [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, April 25, 2006 8:48 PM
Subject: need help for my jointure


I have a table confs like this:
id int 5 auto_increment primary key;
conf text;

and another table conf_ip like this:
id int 5 auto_increment primary key;
conf_id int 5; ==foreing key of confs
ip varchar 150;

I would like to
select id, conf from confs where ip!='some val';

how can I do this?

thanx in advance

Pat

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 24/04/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 24/04/2006


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

Re: WHERE doesn'r works

[Rhino]

- Original Message - 
From: Tom Lobato [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, April 22, 2006 4:03 AM
Subject: Re: WHERE doesn'r works





From: [EMAIL PROTECTED]
you really need to show your php code and the output from an echo of
the update line so that people can try to spot what you're doing wrong.
from what i've seen, people are basically just guessing at the issue
with you saying tried that, didn't work.

updating mysql records via php code, with a where, works just fine.
i.e., this isn't a php/mysql bug. rather there's something in your code
that's not quite right.

so, show your code, the table (definition and data) that you're trying
to update, and output from appropriate echoing of statements and we can
probably help you figure out your problem.


   Perfectly, I didnt show it before just for dont bore you with so many
code =)

   The echo ouput:
UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24' WHERE id =
'5'



Most of the time, when I see a column named 'id', it is defined as an 
integer. If _your_ 'id' column is defined as an integer, then the reason 
your WHERE clause is failing is very simple: Your WHERE clause is looking 
for all the rows where the 'id' value is a character-string containing '5', 
not the integer value 5. In other words, remove the apostrophes on either 
side of the 5 in the WHERE clause so that it says:


   WHERE id = 5

_not_

   WHERE id = '5'

and your WHERE clause will probably start working just fine.

If your 'id' column is defined as a CHAR, VARCHAR or similar data type, then 
the apostrophes around the 5 are fine and there is some other problem.



   The php and db structure and data are attacheds.


I don't see them in my copy of the email


Two infos: 1) Im not a
expert 2) The code is in the beggining of the development, so are too many
uglyness yet =)




Also, just a small note about English grammar since English doesn't appear 
to be your first language: contractions like 'didnt' and 'dont' should 
_always_ be spelled with apostrophes. In other words: use don't, not dont; 
use didn't, not didnt.


Unfortunately, even some people who know only English are starting to spell 
contractions without the apostrophes but this is always wrong and makes the 
writer look illiterate. Obviously, we make allowances for those who are 
relatively new to English but I wanted you to know the right way to handle 
contractions. I assume you want to write English as well as you can so 
please don't copy the bad habits of English-speakers who don't have enough 
education or self-respect to spell their own language correctly.


--
Rhino




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006


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

Re: please help, can not delete database

[Rhino]

What makes you think the delete of the database failed? It looks like the 
message from the DROP command indicates that the database was dropped 
successfully.


--
Rhino

- Original Message - 
From: Randy Paries [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, April 22, 2006 9:22 AM
Subject: please help, can not delete database


Hello,
Not sure what is going on
i have mysql  Ver 12.22 Distrib 4.0.17,

I have a database that i can not delete.

i do this
#mysqladmin drop billmax -u admin --password
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'billmax' database [y/N] y
Database billmax dropped

But it does not do anything.

I have tried restarting the DB and restarting the box as well

Please help

Randy

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006


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

Re: How to Find Most Recent Autoincrement Index Assigned???

[Rhino]

- Original Message - 
From: David T. Ashley [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, April 22, 2006 12:53 AM
Subject: How to Find Most Recent Autoincrement Index Assigned???


I'm using PHP, and I sometimes INSERT new records in a table.  MySQL 
assigns

a new autoincrement int field on each INSERT ... nothing surprising there.
It goes 1, 2, 3, etc.

What query can I use to find out what value this int autoincrement 
assigned

field was?  I could of course SELECT based on what was just inserted, but
that seems inefficient.

Thanks for any help,



RTFM?

If you search the MySQL manual on increment, you'll get several hits, one 
of which is 3.6.9 Using AUTO_INCREMENT. Here is the link:

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

If you read that page, you should find your answer

--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006


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

Fw: please help, can not delete database

[Rhino]

I'm putting this back on the list where it belongs; that enables everyone to 
help and to learn from the discussion, either now or in the future via the 
list archive.


--

Ahh, so you've tried to re-create the database after it appeared to be 
safely dropped! You didn't say that in your note so I wanted to be sure you 
had done that much before writing the note.


Have you looked in the MySQL log to see if it is reporting any problems with 
the DROP? If you are using INNODB, have you tried a SHOW STATUS INNODB to 
see if it reported any problems?


--
Rhino

- Original Message - 
From: Randy Paries [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Sent: Saturday, April 22, 2006 11:04 AM
Subject: Re: please help, can not delete database


when i go back and try to create it , it says it already exisit?

Randy

On 4/22/06, Rhino [EMAIL PROTECTED] wrote:

What makes you think the delete of the database failed? It looks like the
message from the DROP command indicates that the database was dropped
successfully.

--
Rhino

- Original Message -
From: Randy Paries [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, April 22, 2006 9:22 AM
Subject: please help, can not delete database


Hello,
Not sure what is going on
i have mysql  Ver 12.22 Distrib 4.0.17,

I have a database that i can not delete.

i do this
#mysqladmin drop billmax -u admin --password
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'billmax' database [y/N] y
Database billmax dropped

But it does not do anything.

I have tried restarting the DB and restarting the box as well

Please help

Randy

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006





--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006


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

Re: WHERE doesn'r works

[Rhino]

- Original Message - 
From: Tom Lobato [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, April 22, 2006 4:18 PM
Subject: Re: WHERE doesn'r works




From: Rhino [EMAIL PROTECTED]

Most of the time, when I see a column named 'id', it is defined as an
integer. If _your_ 'id' column is defined as an integer, then the reason
your WHERE clause is failing is very simple: Your WHERE clause is looking
for all the rows where the 'id' value is a character-string containing
'5', not the integer value 5. In other words, remove the apostrophes on
either side of the 5 in the WHERE clause so that it says:

   WHERE id = 5

_not_

   WHERE id = '5'


   I tried this, but didn't work yet. I tried double, simple and no 
quotes,

same problem: WHERE only works when executed directly in the mysql client,
no from mysql api of the php.

   See the table creation:

CREATE TABLE `clientes` (
 `id` int(5) NOT NULL auto_increment,
 `tipo` char(1) default NULL,
 `razao_social` varchar(30) default NULL,



Since your table definition says that 'id' is definitely an int,

   WHERE id = 5

_should_ work. But obviously, it doesn't.

I'm not sure what to try next. It's possible that php is messing you up 
somehow but I don't know how to be sure; I don't know php so I don't know 
the problems that you can encounter with it.


There is one thing slightly odd about your table definition: you have 
defined 'id' as int(5). Normally, I define a column like 'id' as int, not 
int(5). According to the manual, it is okay for you to have int(5) but I 
wonder if your problem is caused by the int(5)? Perhaps you could try 
changing the column definition from int(5) to int and see if the php code 
works after that? It shouldn't make any difference but you never know: 
perhaps this will solve the problem.


If that doesn't work, you could try searching for bug reports involving int 
(or int(5)) columns; perhaps this is a known bug?


If you don't find anything in the bug reports, perhaps you are the first to 
find this problem; in that case, you could create a new bug report. Maybe 
someone will be able to suggest a workaround.


I'd be surprised if this is a bug though; it seems like very basic 
functionality that should have been debugged a long time ago.





and your WHERE clause will probably start working just fine.

If your 'id' column is defined as a CHAR, VARCHAR or similar data type,
then the apostrophes around the 5 are fine and there is some other
problem.


   The php and db structure and data are attacheds.


I don't see them in my copy of the email


Also, you can see the codes in...
http://www.spalha.com.br/spalha/DB_code.html
http://www.spalha.com.br/spalha/insert_client_code.html

(thanks to GESHI project, http://qbnz.com/highlighter/index.php =)


Two infos: 1) Im not a
expert 2) The code is in the beggining of the development, so are too
many
uglyness yet =)




Also, just a small note about English grammar since English doesn't 
appear

to be your first language: contractions like 'didnt' and 'dont' should
_always_ be spelled with apostrophes. In other words: use don't, not 
dont;

use didn't, not didnt.

Unfortunately, even some people who know only English are starting to
spell contractions without the apostrophes but this is always wrong and
makes the writer look illiterate. Obviously, we make allowances for those
who are relatively new to English but I wanted you to know the right way
to handle contractions. I assume you want to write English as well as you
can so please don't copy the bad habits of English-speakers who don't 
have

enough education or self-respect to spell their own language correctly.


   So I will have begin to pay the list =) Beyond mysql I learn English
too?
   Well, thank you by the hint, I'll stay alive about this.

No charge for the English pointers :-) I just want you to know the correct 
way to write things. I hope you'd do the same for me if I was trying to 
write Portuguese and made a consistent mistake :-)


--
Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006


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

Re: arrg need help summing Colum's

[Rhino]

- Original Message - 
From: Brian E Boothe [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Friday, April 14, 2006 5:09 AM
Subject: arrg need help summing Colum's



why cant i get this to sum  ???
?
$link = mysql_connect(localhost,root,goobers) or 
die(mysql_error());

mysql_select_db(workorder, $link);
 $result = mysql_query(SELECT SUM(`ElecRem`) AS total 
FROM orders, $link);

//$total = mysql_fetch_row($result);
   echo mysql_result($result); // outputs 
total

 //return $total[0];
  echo mysql_error();  ?

It would REALLY REALLY help if you gave us some idea why you think there is 
anything wrong with this code. You haven't indicated how the code is 
misbehaving or what statements, if any, are working correctly and which are 
failing.


Are you successfully getting connected to the server? If yes, how do you 
know?


Are you successfully connecting the database ('workorder')? If yes, how do 
you know?


What happens when you run the query? You haven't given us any clue at all. 
Does the statement work but return an incorrect answer? Does it fail with an 
error message? If so, what is the error message?


Or is it the statement that computes 'total' that is failing in some way? If 
so, what is wrong with it?


All you've done is given us a fragment of code without clearly identifying 
the failing code or giving us any real symptoms, other than a remark about a 
summing problem.


Without more information it is VERY hard to guess what might be wrong.

--
Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.1/310 - Release Date: 12/04/2006


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

Fw: Select a value between dates.

[Rhino]

I'm putting your question back on the mailing list where it belongs. That 
enables others to help and to learn from the discussion, either now or in 
the future via the archives.


--
Rhino

- Original Message - 
From: H L [EMAIL PROTECTED]

To: [EMAIL PROTECTED]
Sent: Wednesday, April 12, 2006 2:00 PM
Subject: Re: Select a value between dates.



From: Rhino [EMAIL PROTECTED]

To: H L [EMAIL PROTECTED],mysql@lists.mysql.com
Subject: Re: Select a value between dates. Date: Tue, 11 Apr 2006 
16:35:15 -0400



- Original Message - From: H L [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, April 11, 2006 4:05 PM
Subject: Select a value between dates.


I am far from an mysql expert... but is there a way to select between 
dates in a table and check if a value exists in all fields between dates. 
If one day between those dates cannot be booked i dont want to get it in 
my SUM resultset.


I want to calculate Sum between those dates and i one idea i have is to 
use the count to determine if i can use the result but it does not feels 
right. ..


anyway the query looks like this

SELECT
`companyobjects`.`objectid`,
`companyobjects`.`objectname`,
Sum(`completereservationcredentials`.`price`),
Count(`completereservationcredentials`.`price`)
FROM
`completereservationcredentials`
Inner Join `companyobjects` ON 
`completereservationcredentials`.`objectid` = `companyobjects`.`objectid`

WHERE
`completereservationcredentials`.`avaliable` = 1 AND
`completereservationcredentials`.`reservationid` = 0 AND
`completereservationcredentials`.`thedate` = '2006-08-01' AND
`completereservationcredentials`.`thedate`  '2006-08-10' AND
`companyobjects`.`companyid` = 'somecompanyid'
GROUP BY
`completereservationcredentials`.`objectid`
ORDER BY
`companyobjects`.`objectname` ASC


Your question and your SQL seem to be contradicting one another.

You seem to be asking whether it is possible to determine if a given date 
from a table lies between two arbitrary dates. Of course it can, as you 
demonstrate in your SQL:



`completereservationcredentials`.`thedate` = '2006-08-01' AND
`completereservationcredentials`.`thedate`  '2006-08-10' AND


By the way, you might find it more concise to say:

`completereservationcredentials`.`thedate` between '2006-08-01' AND 
'2006-08-09'


(I subtracted one day from the original end date of the range to make sure 
that the August 10 was not chosen; your original query wanted the date 
only if was LESS THAN August 10. The between keyword always gets dates 
that in a given range and INCLUDES both ends of the range.)


You also talk about using sum() on days or dates but your query is 
actually summing prices, which is quite a different matter.


I think you need to clarify just what you want to know and ask a clearer 
question.


--
Rhino


Hi thanks!

Well in this case i want to query on the keys objectid and date to check 
if object can be booked or not.
If one of days between is booked i do not want to calculate price for any 
day which it does now. I want it to discard that object as it cannot be 
booked that period. I do not want a false sum result of price for 13 days 
instead of 14 days..


So, basically, you only want to return data if there are rows for each and 
every date in the date range?


If that is what you mean, I think you'll need to modify your WHERE to return 
rows only when every single day in the range is available for booking; if 
one or more days in the range are not available for booking, don't return 
any rows at all. An EXISTS might be handy for that problem.


Something like this might do the job, although I've never tried a query with 
multiple exists in it before and don't know if it will work the way I'm 
picturing:


your existing SELECT and FROM
WHERE
EXISTS (select * from completereservationcredentials where 
completereservationcredentials.thedate = '2006-08-01') AND
EXISTS (select * from completereservationcredentials where 
completereservationcredentials.thedate = '2006-08-02') AND

...
EXISTS (select * from completereservationcredentials where 
completereservationcredentials.thedate = '2006-08-10')


In other words, write a separate EXISTS subquery for each individual date 
within the range and be sure to connect each of the EXISTS with AND. The 
EXISTS query doesn't actually return any data: in an EXISTS query, the 
'SELECT *' really means return a true/false flag.


I can't try this myself since I don't have a recent enough copy of MySQL - 
no subquery support in my version! - but it's worth a try.


It's pretty ugly though, especially if you have a date range of more than a 
very few days, since you'd have to write one subquery for each date in the 
range. There may well be a much easier way to do it but that's all I can 
think of offhand. Maybe someone else on the list can suggest a better 
approach.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.1/309

Re: Select a value between dates.

[Rhino]

- Original Message - 
From: H L [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, April 11, 2006 4:05 PM
Subject: Select a value between dates.


I am far from an mysql expert... but is there a way to select between dates 
in a table and check if a value exists in all fields between dates. If one 
day between those dates cannot be booked i dont want to get it in my SUM 
resultset.


I want to calculate Sum between those dates and i one idea i have is to 
use the count to determine if i can use the result but it does not feels 
right. ..


anyway the query looks like this

SELECT
`companyobjects`.`objectid`,
`companyobjects`.`objectname`,
Sum(`completereservationcredentials`.`price`),
Count(`completereservationcredentials`.`price`)
FROM
`completereservationcredentials`
Inner Join `companyobjects` ON `completereservationcredentials`.`objectid` 
= `companyobjects`.`objectid`

WHERE
`completereservationcredentials`.`avaliable` = 1 AND
`completereservationcredentials`.`reservationid` = 0 AND
`completereservationcredentials`.`thedate` = '2006-08-01' AND
`completereservationcredentials`.`thedate`  '2006-08-10' AND
`companyobjects`.`companyid` = 'somecompanyid'
GROUP BY
`completereservationcredentials`.`objectid`
ORDER BY
`companyobjects`.`objectname` ASC


Your question and your SQL seem to be contradicting one another.

You seem to be asking whether it is possible to determine if a given date 
from a table lies between two arbitrary dates. Of course it can, as you 
demonstrate in your SQL:



`completereservationcredentials`.`thedate` = '2006-08-01' AND
`completereservationcredentials`.`thedate`  '2006-08-10' AND


By the way, you might find it more concise to say:

`completereservationcredentials`.`thedate` between '2006-08-01' AND 
'2006-08-09'


(I subtracted one day from the original end date of the range to make sure 
that the August 10 was not chosen; your original query wanted the date only 
if was LESS THAN August 10. The between keyword always gets dates that in a 
given range and INCLUDES both ends of the range.)


You also talk about using sum() on days or dates but your query is actually 
summing prices, which is quite a different matter.


I think you need to clarify just what you want to know and ask a clearer 
question.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.1/307 - Release Date: 10/04/2006


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

Re: COUNT() Efficiency

[Rhino]

- Original Message - 
From: Martin Gallagher [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, April 08, 2006 6:34 PM
Subject: COUNT() Efficiency



Hi,

If I did a query like:

SELECT COUNT(id) AS count1, COUNT(id) AS count2 FROM table WHERE id100

Would MySQL run the COUNT() calculation once or twice?



I don't know the answer to your question but why would you want to count in 
the same column of the same table twice in the same query?


I'm not very knowledgeable about MySQL performance - I'm mostly a DB2 guy - 
but doesn't MySQL have an Explain command that will tell you what access 
path you are getting? If it does, your best bet would be to try your query 
and do an Explain to see what it actually does; it should be pretty clear 
whether the count() is being done once or twice.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006


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

Fw: COUNT() Efficiency

[Rhino]

I'm sending this back to the mailing list where it belongs; that way, other 
people can jump in to help and others can learn from the discussion, either 
now or via the list archive.


- Original Message - 
From: Martin Gallagher [EMAIL PROTECTED]

To: 'Rhino' [EMAIL PROTECTED]
Sent: Saturday, April 08, 2006 6:57 PM
Subject: RE: COUNT() Efficiency



Sorry about the previous empty message.

This is the actual query:

SELECT messages.id, subject, IF(COUNT(messages.id)=1,
CONCAT(members.surname, ', ', members.forename), 
CONCAT(COUNT(messages.id),

' Recipients')) AS `to` FROM messages LEFT JOIN members ON
members.id=messages.recipient WHERE author='1' AND messages.flag IN(0, 2, 
4)

GROUP BY checksum ORDER BY messages.timestamp DESC LIMIT 0,10

The problem is I can seem to assign COUNT(messages.id) to a MySQL variable
like so:

Do you mean 'can' or 'cannot'? It isn't usually a problem if you _can_ 
assign a COUNT() expression to a variable



SELECT messages.id, subject, @count:=COUNT(messages.id), IF(@count =1,
CONCAT(members.surname, ', ', members.forename), CONCAT(@count, '
Recipients')) AS `to` FROM messages LEFT JOIN members ON
members.id=messages.recipient WHERE author='1' AND messages.flag IN(0, 2, 
4)

GROUP BY checksum ORDER BY messages.timestamp DESC LIMIT 0,10

This results in NULL for `to`

It might have something to do with the GROUP clause?

How does any of this have anything to do with the efficiency of COUNT()? 
You've explained why you need to have the same COUNT() expression in the 
SELECT twice and I accept that this looks like it might be reasonable in 
this case. But your real problem seems to be the null in the 'to' column, 
which has nothing to do with efficiency.


As for your GROUP BY clause, something looks rather odd there; I don't see 
checksum in the SELECT at all yet you're grouping by it. Normally, a GROUP 
BY names one or more columns from the SELECT that aren't in column functions 
so that you can get summarization of the rows returned by the query. This 
query doesn't seem to be operating that way. That is not to say that it is 
invalid in some way - it might be exactly the right solution to your 
problem - but it makes me question what you're doing.


Unfortunately, I'm behind with my own work and don't have the time I would 
need to wheedle out enough information to figure out if you are doing the 
right thing or the wrong thing. I just jumped in because it appeared that 
you had a short simple question; apparently, that is not the case. I'll 
leave the others on the list to help you. Be patient, there isn't much 
activity on the weekend but things start to pick up once the work week 
starts again.


Sorry I can't be more help.

--
Rhino




-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: 08 April 2006 15:50
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: COUNT() Efficiency


- Original Message - 
From: Martin Gallagher [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, April 08, 2006 6:34 PM
Subject: COUNT() Efficiency



Hi,

If I did a query like:

SELECT COUNT(id) AS count1, COUNT(id) AS count2 FROM table WHERE id100

Would MySQL run the COUNT() calculation once or twice?



I don't know the answer to your question but why would you want to count 
in

the same column of the same table twice in the same query?

I'm not very knowledgeable about MySQL performance - I'm mostly a DB2 
guy -

but doesn't MySQL have an Explain command that will tell you what access
path you are getting? If it does, your best bet would be to try your query
and do an Explain to see what it actually does; it should be pretty clear
whether the count() is being done once or twice.

--
Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006


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




--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006


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

Re: A doubt in SELECT query

[Rhino]

If the sequence in which the result set is displayed is important to you, 
you will need to use SQL clauses to force the sequence you want. ORDER BY is 
the main way of accomplishing this although other clauses, like GROUP BY and 
DISTINCT, can also affect the sequence. But ORDER BY is the normal method of 
forcing the output to be in a specific order.


--
Rhino

- Original Message - 
From: subramani [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Friday, April 07, 2006 10:22 AM
Subject: A doubt in SELECT query


hello all,

In which order the datas are displayed, when the SELECT quey is used ?
Is it random or the order in which the datas are inserted?

-- r.subramani
My log file: http://subramanitce.blogspot.com

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/303 - Release Date: 06/04/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/303 - Release Date: 06/04/2006


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

Re: Getting number days between 2 dates

[Rhino]

- Original Message - 
From: Mike Blezien [EMAIL PROTECTED]

To: Jorrit Kronjee [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Saturday, April 01, 2006 9:00 AM
Subject: Re: Getting number days between 2 dates



Jorrit,

- Original Message - 
From: Jorrit Kronjee [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, April 01, 2006 7:46 AM
Subject: Re: Getting number days between 2 dates



Mike Blezien wrote:

Hello,

I'm sure this is a simple query but haven't come up with a good 
approach. Need to get the number of days between two dates. IE: today's 
date: (2006-04-01 - 2006-03-05)
need to calculate the number of days between these dates.. what is the 
best query statement to accomplish this?


TIA,



Mike,
You probably want to use something like this:

SELECT TO_DAYS('2006-03-15') - TO_DAYS('2006-03-01');


Thanks, that works, also using the DAYOFYEAR produces the same results as 
I just found :)


appreciate the help


I'd be careful with DAYOFYEAR() if I were you.

DAYOFYEAR() only tells you which day it is within a given year. If you try 
to use DAYOFYEAR to tell the difference in days between dates that are in 
different years, you are certainly going to get the wrong answer. For 
instance, DAYOFYEAR(2006-04-01) - DAYOFYEAR(2005-04-01) gives an answer of 0 
days when the correct answer is 365.


A better choice for getting the difference between two dates in days is 
probably DATEDIFF() or TO_DAYS().


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.4/299 - Release Date: 31/03/2006


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

Re: Update multiple tables

[Rhino]

- Original Message - 
From: Mike Blezien [EMAIL PROTECTED]

To: MySQL List mysql@lists.mysql.com
Sent: Monday, March 27, 2006 3:39 PM
Subject: Update multiple tables



Hello,

I'm alittle unclear on how too update multiple tables. We have two tables 
with the same column name:

account.state
account_service.state

when we update the account table, we also need to update the 
account_service table with the same value for the 'state' column. Can this 
be done with MySQL 4.1.12. and what is the correct syntax?



I think you're asking if you can update both tables within the same SQL 
statement.


Unless MySQL works much differently than DB2, the main relational database I 
use, you have to write a separate UPDATE statement for each table. 
Relational databases (or at least DB2!) normally require that an UPDATE 
statement can only affect a single table; you can't put multiple table names 
in the FROM clause of an UPDATE statement.


Assuming you are using an engine that supports transactions, I would 
definitely make a point of enclosing both updates within a single 
transaction. That way, if one of the updates fails, they will both be rolled 
back to maintain consistency. Otherwise, if the first update succeeds and 
then you hit a problem, like a power failure, the column that is common to 
both tables will have one value in one table and a different value in the 
other table.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.2/293 - Release Date: 26/03/2006


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

Re: Comparing x.x.x.x strings

[Rhino]

I don't know the answer to your question but I know that this issue has been 
discussed in this mailing list before. Look in the MySQL archives and I'm 
sure you will find an answer on the best way to compare IP addresses.


--
Rhino

- Original Message - 
From: Lucas Vendramin [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Friday, March 24, 2006 3:44 PM
Subject: Comparing x.x.x.x strings


Hi all.
I have a problem:
When I compare two strings in mask (x.x.x.x) the comparation is not true 
(for my question :D)... look:

select '3.2.0.13'  '3.2.0.2'- FALSE
I want this command return TRUE, but it is returning FALSE.
But, if I put a space first into the smaller string the camparation will 
returns what I want:

select '3.2.0.13'  ' 3.2.0.2'- TRUE

How can I check it? There is a way to correct my problem? The string is like 
a IP-ADDRESS where 192.168.0.13 is better than 192.168.0.2, but it is not a 
IPADDRESS column, it is a Version of file (version 3.2.0, version 3.2.1, 
etc...)


Thanks for all.





No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 24/03/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 24/03/2006


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

Re: How to shutdown mysql from Java

[Rhino]

- Original Message - 
From: Zsolt [EMAIL PROTECTED]

To: MySql Mailing List mysql@lists.mysql.com
Sent: Tuesday, March 21, 2006 2:58 AM
Subject: How to shutdown mysql from Java



Hi,

my application starts mysqld (via Runtime.getRuntime().exec) and I would
like to stop it also from Java (because of technical reasons I cannot use
mysqladmin).

What is the best way stop shutdown mysqld?

1. FLUSH TABLES

2. Process.destroy()

3. kill PID auf Unix

What do you think?

Most database management systems, like MySQL and DB2, are designed to run 24 
X 7 with occasional breaks for maintenance like taking backups. Why do you 
want start and start MySQL from an application? Isn't this going to preclude 
most of the users from using it? After all, databases usually have large 
numbers of users, not just one.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006


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

Re: RIGHT JOIN better than INNER JOIN?

[Rhino]

- Original Message - 
From: Jochem van Dieten [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, March 21, 2006 3:53 AM
Subject: Re: RIGHT JOIN better than INNER JOIN?



On 3/21/06, Robert DiFalco wrote:

I apologize if this is a naive question but it appears through my
testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
where they would produce identical result sets. i.e. there are no keys
in the left table that do not exist in the right table.

Is this true?


If your benchmark shows it it must be true :) But is it a significant
difference? Over how many test runs? And is it worth the risk that for
some join it is actually much slower.



If so, it this peculiar to MySQL or would this be true
with almost all database servers?


Hard to tell. If you want a generic answer the way to go wouls be to
start digging in the MySQL sourcecode to find out why it is faster.
Once you know that, you can extrapolate to other databases.

No, you can't. Other databases use different optimizers, i.e. different 
algorithms to choose the access path. Even if you inspected 100 or 1000 or 
1,000,000 Fords and found every one of them to have rear-wheel drive, it 
wouldn't guarantee that BMWs or Hondas had rear-wheel drive, they might have 
front-wheel drive or all-wheel drive. The specific things you learn about 
MySQL's optimization technique cannot be extrapolated to other databases 
unless they are using the same optimization techniques.


The only mechanism I can imagine is that you are constraining the
planner so for a join between N tables you go from N! plans to (N-1)!
plans which will save you a miniscule amount on the planning time for
a small join, and somwhat more on a big join. (Does the MySQL planner
do an exhaustive search?) The downside is that the plan might be
significantly worse so you can loose big time in the executor.
Especially considering the semantic difference between both syntaxes I
would never use this as a way to constrain the planner.

Jochem



--

Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006


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

Re: RIGHT JOIN better than INNER JOIN?

[Rhino]

Robert,

Your restatement of your original question uses FULL JOIN as if it means 
the same things as INNER JOIN: that's simply not correct. A full join 
contains the results of an inner join PLUS the orphan rows from the 
right-hand table in the join PLUS the orphan rows from the left-hand table 
in the join. Furthermore, the last time I checked, which was probably at 
least a year ago now, MySQL didn't support a full join.


--
Rhino

- Original Message - 
From: Robert DiFalco [EMAIL PROTECTED]

To: Martijn Tonies [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Tuesday, March 21, 2006 2:04 PM
Subject: RE: RIGHT JOIN better than INNER JOIN?


For me the argument is a little pedantic. The contract of the descriptor
table is that it must reference a name; there is code and constraints to
enforce this. I am happy to have the query return nulls to indicate a
programming error that can be quickly addressed. _If_ (after buffer
tuning et al) a RIGHT JOIN still provides a substantial performance
improvement over a FULL JOIN in this case, my customers would want me to
provide that rather than have me tell them it is an inappropriate join
or that I am asking the database server developers to improve their
query optimizer.

I wasn't really looking to get into a philosophical debate on
correctness so let me restate my question a little better.

Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially
out perform a FULL JOIN in those cases where the results would be
identical? It is a little difficult to test query performance
empirically since performance will change as different indices are
swapped in and out of memory buffers and such (although I have turned
query caching off), but it appears that for a table with 1-2 million
rows a query similar to what I posted here was faster with a RIGHT JOIN.

R.

-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 21, 2006 9:43 AM
To: mysql@lists.mysql.com
Subject: Re: RIGHT JOIN better than INNER JOIN?

Robert,


Of course one should use the right JOIN for the job. But let me ask
you, which join would you use here?

You have a table called Descriptors, it has a field called nameID
which is a unique key that relates to a Names table made up of a
unique identity and a VARCHAR name. I think most people would write a
simple query like this:

   SELECT desc.fields, names.name
   FROM desc JOIN names ON desc.nameId = names.Id
   ORDER BY names.name

However, it this really correct? Every descriptor has a record in
names, so it could be equivalently written as:

   SELECT desc.fields, names.name
   FROM desc RIGHT JOIN names ON desc.nameId = names.Id
   ORDER BY names.name

My guess is that most people conventionally write the first query.


Gee, I wonder why ... This happens to be the query that returns the rows
as it should.

What happens, if two years from now you didn't document WHY you wrote a
right join query instead of an inner join and someone figures out that
this could return nulls for a result column?


If you start using the wrong joins, you will make things harder on
yourself and others.


As I said: if performance isn't satisfactory (which sounds a bit strange
for this situation), then try to solve that. Either by using different
index/buffer/caching strategies or by complaining to the people who
created the database system in the first place.



Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.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]


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006


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

Re: RIGHT JOIN better than INNER JOIN?

[Rhino]

The only relational databases I've ever used to any significant extent are 
MySQL and DB2. I've used DB2 for a lot longer than MySQL and on most of the 
platforms on which it runs over various versions. As far as I'm concerned, 
the answer to your questions, at least as far as DB2 goes, is: it depends.


It depends on a host of factors. In no particular order, these factors 
include:

- which version of DB2 you are using
- what hardware you are running on
- how you write your SQL
- whether the data is properly clustered
- whether the tables and indexes have been reorganized in a timely fashion
- etc. etc.

You simply can't make a categorical statement that a right join will perform 
better than an inner join - or vice versa - in every case in DB2. All 
versions of DB2 use a cost-based optimizer that makes great efforts to give 
the optimum access path (and therefore optimum performance) for each query. 
A lot of very smart people have worked on the design of that optimizer over 
the years - I've met some of them - but, as good as the DB2 optimizer is, it 
can still make inappropriate decisions. This happens when you don't do 
routine maintenance like reorganizing tables and the RUNSTATS utility but 
the way you write (or mis-write) your SQL can also affect your access path 
and therefore your performance.


This unpredictability may sound like a bad thing but it is often a very good 
thing since the optimizer has many tricks and shortcuts. It will often 
rewrite a poorly-written query to improve its performance.


You may be able to find more categorical answers for the other major 
databases, like Oracle, since they tend to use different optimizer designs.


The only way to be really sure though is to do a proper benchmark for all 
the platforms and configurations that interest you.


--
Rhino

- Original Message - 
From: Robert DiFalco [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Monday, March 20, 2006 7:11 PM
Subject: RIGHT JOIN better than INNER JOIN?


I apologize if this is a naive question but it appears through my
testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
where they would produce identical result sets. i.e. there are no keys
in the left table that do not exist in the right table.

Is this true? If so, it this peculiar to MySQL or would this be true
with almost all database servers? i.e. Oracle, DB2, MSSQL, etc.

TIA,

R.


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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006


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

Re: Counting Multiple Fields

[Rhino]

- Original Message - 
From: Zack Vernon [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Sunday, March 19, 2006 12:11 PM
Subject: Counting Multiple Fields



Hello,



Would be grateful for any help!



I have a table like (in reality few more fields!!!):



Id  Site Code  Type of Job



What I want to do is make reports like:



Site Code  Type of JobNumber of Jobs for this type and 
site

Number of jobs for this site Percent of the type of jobs for this
site

This is very unclear. I know that wrapping makes it harder to communicate 
what you are trying to say but I'm not sure if you have listed three column 
headings on the first line and then elaborated what they mean on the second 
line or if you are listing six different columns that you want with 
different contents. That makes the rest of your question very unclear too.



Guess it would be something like:



SELECT SiteCode, TypeOfJob, count(TypeOfJob) As countJobType FROM jobs 
GROUP

BY SiteCode



But I cannot work out how to work out the number of jobs for the site.



Please help!

It sounds to me like you need to use count distinct. That can be used to 
determine the number of different values in a column. For example, given 
this table:


SiteJob

LondonPlumber
LondonEngineer
LondonElectrician
London   Electrician
Paris   Drywall Installer
ParisArchitect
ParisEngineer
Venice  Engineer

This query:

   Select distinct site from mytable;

returns the different locations from the Site column:

   Site
   -
   London
   Paris
   Venice

This query:

   select count(distinct site) from mytable;

returns the NUMBER of different sites in the table:

   3

This query:

   select distinct site, job from mytable;

returns the number of different combinations of sites and jobs. Note that 
although there are two rows for electricians in London, the result contains 
only one such row; the distinct eliminates the duplicate

combination of London and electrician.

   SiteJob
   
   LondonPlumber
   LondonEngineer
   LondonElectrician
   Paris   Drywall Installer
   ParisArchitect
   ParisEngineer
   Venice  Engineer

If this information doesn't answer your question, please clarify what you 
want.


Also, it is always a good idea to state which version of MySQL you are 
using; older versions, which are still in widespread use, don't have all the 
functionality of newer versions. That means that a solution that works fine 
in a newer version may not work at all in an older version or may have to be 
adapted to work.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006


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

Re: Update Multiple Records

[Rhino]

- Original Message - 
From: Rich [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, March 18, 2006 6:28 PM
Subject: Update Multiple Records



Hi folks.

I want to set the status of 5 records to 'completed'.  how do I go about 
that without having to prepare 5 different instructions?


update myTable set status = 'completed' where id=10 OR id=20 OR id=30 OR 
id=40 OR id=50



That should work. So should:

   update myTable set status = 'completed' where id in (10, 20, 30, 40, 50)

and (assuming id 10 through 50 are the ONLY numbers in that range!), so 
should this:


   update myTable set status = 'completed' where id between 10 and 50

A properly written program would probably use one or more variables to 
control which rows got deleted; for example, something like this pseudocode:


   update myTable set status = 'completed' where id between :lowValue and 
:highValue


where lowValue and highValue are variables. Your program would then set the 
values of the two variables before executing the UPDATE statement. But you 
didn't say if you were using a programming language to do some of the work 
or if you were trying to do everything purely in MySQL.



I'm trying to reduce the number of instructions.

The best way to accomplish this is to reason out the circumstances under 
which you will do updates. What will you know about the row(s) being updated 
when it is time to update them? Be careful to make sure you are updating 
ONLY the rows that should be updated. If you specify your WHERE conditions 
incorrectly, you can update every row of the table or none at all.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006


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

Re: Accountability with MySQL

[Rhino]

 to do...

Database systems are designed to accomodate large quantities of tables but 
any designer with any real world experience will advise you not to create 
tables simply for the sake of creating tables: that is an unnecessary 
proliferation of tables. Look at normalization. As you probably know, each 
phase of normalization tends to increase the number of tables in the 
database. I know for a fact that the theorists have identified at least up 
to 17NF (17th Normal Form) but how many levels of normalization do 
businesses typically use in their normalizations? Answer: Three. 
(Occasionally four). And I think you'll find that the main reason is that 
the benefits of normalizing beyond 3NF are outweighed by the increasing 
number of tables to manage.



The key point is that NULLs _do_ work and are a legitimate design

decision.

You don't _have_ to use them but they can save you some work and reduce

the

number of tables you need. (They can also be a bit more work on the
programming side.)

I took your remarks to mean that NULLs were always a bad idea and were a
symptom of bad design and I strongly disagree with that.


So I've noticed.

I'm trying to be gracious here and accept that I may just have misunderstood 
the emphasis in what you are saying.



The message I'm trying to get across is that:
1 - according to (proper) design literature, you should not use NULLs
 if you don't have to


Perhaps you can cite some of this proper literature. I'm interested in 
seeing their definition of what an appropriate time to use nulls is.



2 - NULLs can cause you more problems than you can think of


I'd like to hear what these problems are. I've never had any big problems 
because of nulls.



3 - its sometimes much easier to avoid storing NULLs AND to be
 able to refactor your database because of it


I agree that nulls can frequently be avoided and that avoiding them will 
simplify _some_ situations.



4 - the meaning of NULL can change, so why store it in the first place

Simple: because it happens to be true when it is stored. In the case of the 
hypothetical employee, I store a null termination date when I hire him 
because I don't know when he is going to leave. If he laters gives his 
notice, then I know when he is leaving and can store that date for his 
termination date instead of a null; then his row of the table is true again, 
based on the new facts.



If you are simply saying that you don't like them and prefer to use
different designs to avoid them, then I don't have any problem with that.


I have seen that when I avoid storing NULLs, my applications
became more clear and easier to understand.

Beauty is in the eye of the beholder as we all know. I have no problem with 
having nulls in my tables and consider that a better design than a separate 
table for special cases most of the time.


Again, if you are saying that you don't like nulls and prefer to avoid them 
in your designs, I have no problem with that; that's just your personal 
preference. It's the same as if we sat down to eat a meal and you asked for 
chocolate ice cream for dessert and I had vanilla; neither choice is 
wrong, they are just personal preference.


But if you are stating categorically that nulls are always bad or wrong, 
I disagree strongly. That's like saying only chocolate ice cream is 
acceptable and that all other flavours are evil. That's just wrong.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.3/281 - Release Date: 14/03/2006


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

Re: Accountability with MySQL

[Rhino]

 big and small problems with NULLs stored in
the database. That made me appreciate more how easy it is to avoid
them and how to handle situations differently.

I have no problem with that point of view. This is just a difference of 
opinion or emphasis that two reasonable people can have.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.3/281 - Release Date: 14/03/2006


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

Re: Accountability with MySQL

[Rhino]

- Original Message - 
From: Bruno B B Magalháes [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, March 14, 2006 12:41 PM
Subject: Accountability with MySQL



I am designing a simple accountability system so all the partners can have
direct access to it by intranet.

I was designing the data model, and came up with this:

CREATE TABLE `moviments` (
 `moviment_id` int(20) NOT NULL auto_increment,
 `moviment_date` date NOT NULL default '-00-00',
 `moviment_description` char(200) NOT NULL default '',
 `moviment_assignor` char(80) NOT NULL default '',
 `moviment_drawee` char(80) NOT NULL default '',
 `moviment_amount` int(20) NOT NULL default '0',
 PRIMARY KEY  (`moviment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

One small observation: if your column names are meant to be in English - as 
suggested by your use of amount, drawee, etc. I think you should also 
use an English word where you are currently using moviment. Moviment is 
not an English word. There _is_ an English word movement that might be 
what you want but I suspect that transaction would be the term most 
English-speakers would use in the table you describe. Your email address 
indicates that you are from Brazil so I'll assume you are a 
Portuguese-speaker, not a native English-speaker. This suggestion is only 
meant to be a friendly suggestion, not an insult to your English which is 
very very good. :-)



But I am a little bit stuck with this:

Should I use a column to mark if the moviment has been executed?


That depends. You could decide to only add the row to the table if it has 
been executed; then, you would know implicitly that every entry has been 
executed. On the other hand, if you want to show transactions that have not 
yet been executed, a column that indicates whether the transaction has been 
executed would probably be a good idea.


It might be even better to display details about the transaction that refer 
to its successful execution rather than just showing a yes/no flag. Perhaps 
you could store the timestamp that shows when the transaction was completed 
and maybe the identity of the person or program that completed the 
transaction or even a copy of the document generated by the transaction (or 
a link to this document). For instance, if a receipt was issued for this 
transaction, you might want to store an image of the receipt (or a link to 
the image) in the table.



About income and outcome, should I use a column called moviment_type or
just put a negative value when is an outcome for exampe?

Either approach should work. It would probably be easier to simply store the 
sign of the transaction with the amount.



Does anyone ever made something like that, any other idea that could
improve my little system?

I think the question you need to ask yourself is What do I want to be able 
to find out about a transaction after it has taken place?. If you decide 
you want to know what currency was involved in the transaction, you need to 
record that in your table. If you decide you want to know the serial numbers 
of the banknotes used in the transaction, you need to record that. And so 
on. It might be wise to talk to someone in the bank and ask them what things 
they need to find out while a transaction is taking place and afterwards.


You may want to try asking your users (or their managers) what sorts of 
things people ask for but can't get from the old system; these are prime 
candidates for things that you could add to your new system.


I'm a little surprised that your amount column is an integer. Does the 
Brazilian currency not have a fractional component? Here in Canada, monetary 
amounts are always decimal numbers, like $123.45, meaning one hundred and 
twenty three dollars and forty five cents. Or is your currency like Japanese 
yen, which have no fractional part?


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/03/2006


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

Re: Accountability with MySQL

[Rhino]

- Original Message - 
From: Martijn Tonies [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, March 14, 2006 1:16 PM
Subject: Re: Accountability with MySQL



Hello Bruno,

well about the date default value being invalid, well it´s working here 
in

my system (MySQL 4.1.16, Mac OS X 10.4.5), and all my systens work with
this...


Yes, it's a valid value in MySQL, but it's an invalid date,
that's what I'm trying to say. Why have an invalid date
as the default?



I think there may be a little bit of language confusion here.

Bruno, I think Martijn is suggesting that you use a date that can actually 
exist as the default transaction date; '000-00-00' is accepted by MySQL as a 
date that can be stored in a table but it is not a date that ever really 
existed. There was never a Year 0 in the history of the world.


I think Martijn is right; it is probably a better choice to use a real 
date in your table. The obvious choice would be to store the current date in 
that column. For example, if you add a row to the table, the new row should 
normally contain the date that the transaction was executed; that might be 
yesterday or a week ago or maybe even longer. But if no transaction date is 
known, it would probably be reasonable to store today's date. But sometimes 
that _wouldn't_ be reasonable.


You should probably talk to the people in the bank about this and ask if it 
is ever possible that the transaction date would be unknown; if the 
transaction data _can_ be unknown, you should ask what date they usually 
store in their existing system in that case. Then your new table should 
probably do the exact same thing as the old system does, assuming everyone 
agrees that this is the right thing to do. (The users may say that it is the 
wrong thing to do and suggest a better value to choose for the transaction 
date.) But if the transaction date can never be unknown, it doesn't make 
much difference what you choose as the default date since it will never be 
used.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/03/2006


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

Re: Accountability with MySQL

[Rhino]

- Original Message - 
From: Martijn Tonies [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, March 14, 2006 1:28 PM
Subject: Re: Accountability with MySQL



Should you have a flag for the status movement complete ? I would say

yes but instead of a simple checkbox, you could store a date value. That
gives you two pieces of information

a) if the date is null then the movement is not complete.
b) if the date is NOT null then the movement is complete and you know when
it finished.


There, Shawn and I disagree :-)

In case of a very simple database, one could use NULL
as a flag or signal. But in general, I would avoid
storing NULLs.

Basics of database design: store what you know.

Given that NULLs basically means the absence of a value
(eg: unknown), you shouldn't be storing NULLs.


Nonsense!!

That's simply wrong. A null means unknown or not applicable and is a 
perfectly valid value to use in many, many situations. Two classic examples:


1. You have a table containing test scores. Some students get every question 
on a given test wrong. Some students don't write the test at all, perhaps 
because they were sick. In this scenario, I think the correct thing to store 
is 0 for the students who got every question wrong and NULL for the students 
who didn't write the test.
2. You have a table containing employee records. One of the columns is 
termination date. What value do you store for a new employee's termination 
date? Well, if they are a contractor on a fixed length contract, you could 
calculate the date the contract ends; fair enough. If they are a permanent 
employee and your area has mandatory retirement, you could calculate the 
date they turn 65 (or whatever) and use that. But what if they are a 
permanent employee and you don't have mandatory retirement? I would store a 
NULL to mean I don't know right now. Then, if and when they gave notice 
that they were leaving, I would change the termination date from NULL to 
their last day of work.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/03/2006


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

Re: Accountability with MySQL

[Rhino]

 payments have been made, if all clients have payed... As I am the one
who make every transaction I simple would input it into DB (direct) and
they would access a simple page with a transactions list and graphics of
the current month as default, so they could choose what period they want.

Okay, that's fine but it's a little more general than I had in mind. I was 
trying to suggest that you think of specific problems that you need to be 
able to solve with the table :-)



I'm a little surprised that your amount column is an integer. Does the

Brazilian currency not have a fractional component? Here in Canada,
monetary

amounts are always decimal numbers, like $123.45, meaning one hundred

and twenty three dollars and forty five cents. Or is your currency like
Japanese

yen, which have no fractional part?


Yes we have decimal, but, we use the dot for thousand, and the comma for
decimal values... Is it possible to make MySQL accept this? :)

Normally, when you define a number as a decimal number in a database, no 
actual decimal symbol is stored in that column. The decimal symbol, whether 
it is a dot or a comma, is usually 'virtual': it is not stored. When you 
display the number later in a query, there is usually some way that you can 
specify the decimal symbol you want and you can often choose what separator 
you want to represent 1000. The exact method for specifying your decimal 
symbol and thousands separator is usually different for each database and 
programming language. (You can often use SQL to format the number the way 
you like but you could give this job to the programming language.)


I haven't played with decimal symbols in MySQL - I mostly use DB2 - but I 
just took a quick look at the manual to see how you could control the 
decimal symbol and thousands separator in MySQL. Unfortunately, I didn't 
find it but maybe if you do a more thorough search you will find the right 
answer. There may be a built-in function that controls the formatting. Or 
maybe you'll have to write your own function for this purpose. Or maybe 
choosing the right character set will handle this for you automatically. I'm 
really not sure. If you want to do this formatting via Java, I can tell you 
how to accomplish it but if you are using Perl or php, or other languages, I 
don't know.



--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/03/2006


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

Re: Accountability with MySQL

[Rhino]

- Original Message - 
From: Martijn Tonies [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Tuesday, March 14, 2006 2:16 PM
Subject: Re: Accountability with MySQL




 Should you have a flag for the status movement complete ? I would 
 say
yes but instead of a simple checkbox, you could store a date value. 
That

gives you two pieces of information

a) if the date is null then the movement is not complete.
b) if the date is NOT null then the movement is complete and you know

when

it finished.

 There, Shawn and I disagree :-)

 In case of a very simple database, one could use NULL
 as a flag or signal. But in general, I would avoid
 storing NULLs.

 Basics of database design: store what you know.

 Given that NULLs basically means the absence of a value
 (eg: unknown), you shouldn't be storing NULLs.

Nonsense!!


That's a bold statement ...


That's simply wrong. A null means unknown or not applicable and is a


Ah, so now things become clear, a NULL actually can mean
two things? So much for clarity then...

Come on; that's not fair. Unknown and not applicable are more like 
different senses of the same thing, not two opposite things.



perfectly valid value to use in many, many situations. Two classic

examples:

Let me first state that there's a difference between storing NULLs
and handling NULLs in your result.

Yes, of  course there is: so what? You said NULLs should never be _stored_; 
that's what I'm responding to. Using NULLs is not very _difficult_ anyway, 
just a bit tedious because it is another case to handle.



So, let's debunk these two classic examples...

Please note that I did not say that you MUST use NULLs, just that they were 
perfectly valid to use in a design.



1. You have a table containing test scores. Some students get every

question

on a given test wrong. Some students don't write the test at all, perhaps
because they were sick. In this scenario, I think the correct thing to

store

is 0 for the students who got every question wrong and NULL for the

students

who didn't write the test.


IMO, the better thing to do in this particular case is to NOT store a test
result for the students that did not make the test.

Okay, that might be acceptable, if it doesn't cause you to lose track of the 
student altogether. But if this table was the only one that even recorded 
the _existence_ of the student, you'd have a problem; if someone tried to 
verify that the student had attended this school, you wouldn't know that 
they had. Or in a more probable case, if that was the only test for that 
course and the student missed it and then had no row in the table, you might 
not have any way of knowing that they took the course! And if they later 
wanted to write the exam, having recovered from their illness, your query 
might have the effect of keeping them from taking the second exam: your 
query would report that they had never taken the first exam so an 
adminstrator might refuse to let him/her take the second exam because they 
(apparently) had never been scheduled to take the first one.


Now, you could have a second table to record people who were scheduled to 
take tests but failed to take them to cover that situation but I think it 
would be easier to record all students in one table and then simply store a 
null for any test that they fail to take and a zero for every case where a 
student got every answer wrong. When you compute the class average, the 
avg() function would ensure that the students who got every question wrong 
would pull down the class average but that students who failed to write the 
test at all would NOT skew the average because the avg() function ignores 
nulls.



So, an example table with test scores:

TEST_SCORES
StudentID Int,
TestID Int,
Score TinyInt Unsigned

Now, students who didn't make the test won't have a record in here.

Perfectly valid design AND you avoid storing NULLs.


2. You have a table containing employee records. One of the columns is
termination date. What value do you store for a new employee's

termination
date? Well, if they are a contractor on a fixed length contract, you 
could
calculate the date the contract ends; fair enough. If they are a 
permanent

employee and your area has mandatory retirement, you could calculate the
date they turn 65 (or whatever) and use that. But what if they are a
permanent employee and you don't have mandatory retirement? I would store

a

NULL to mean I don't know right now. Then, if and when they gave notice
that they were leaving, I would change the termination date from NULL to
their last day of work.


Why store a date column if you don't know?

Why not use:

EMPLOYEES
EmployeeID int,
StartingDate Date,
...

TERMINATED_EMPLOYEES (albeit a bit agressive ;) )
EmployeeID
TerminationDate

Once more: perfectly valid design.

Yes, that is also a valid design but it means you have to have yet another 
table that you could have avoided simply by permitting a null

Re: Table with multiple primary keys - How

[Rhino]

- Original Message - 
From: fbsd_user [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, March 07, 2006 11:40 PM
Subject: Table with multiple primary keys - How




What I am trying to do here is have 3 separate primary keys.
Creating a mysql select on either of the Logon_id, email_addr,
or last_name fields will do a single read to the matching value.
Like having 3 different indexes into the same table.
I don't want those 3 field concatenated together as a single key.

Is this table definition correct?

By definition, a table cannot have more than one primary key. Period. Your 
definition will probably execute successfully - I haven't tried it so I 
can't be sure - but 'successful' only means that it will (probably) not fail 
on a syntax error. Your definition does NOT create three primary keys, it 
creates one key on the combination of three values. In other words, your 
definition concatenates the three keys together, which you say you don't 
want to do.


Your basic goal of having three separate primary keys is not possible. 
However, you _could_ create a primary key and two unique keys or three 
unique keys. But before you start doing that, I suggest you take a step back 
and rethink your design.


The proper way to design databases is to do logical design FIRST, then 
consider physical design. In other words, make sure that your design hangs 
together logically first and meets all your business requirements. Then, and 
only then, should you consider physical design, i.e. redesigning tables to 
optimize performance. Logical design is always done with the assumption that 
you are working on a perfect processor that has outstanding performance no 
matter what query you run. Once the logical design is perfect, you can start 
being realistic and modify your design to optimize performance for real 
world non-perfect processors.


I think you're leaping into physical design before you've finished logical 
design. You're worried about query performance before you've satisfied 
yourself that the logical design even works. You need to consider what 
primary key will support your logical design. Is the login_id alone 
sufficient to uniquely identify rows of the members table? Or do you need 
other columns to accomplish this? If, in fact, you need all three columns 
(login_id, email_addr, and last_name) to uniquely identify rows in the 
members table, then all three of those need to be in your single, 
concatenated key, exactly as you have it in your code.


Is it possible for more than one person to have the login_id ABC123? If not, 
the login_id by itself should suffice to be your primary key. In that case, 
you may want to make email_addr and last_name separate unique keys - or not. 
If the login_id is sufficient to uniquely identify a member that's fine; 
make login_id your primary key. But you may not want to make email_addr or 
last_name unique keys. What if two of your members have separate login_ids 
but share an email address? Making email_addr unique will prevent one of 
your two members from being added to the table. This is an even bigger 
problem for the last_name; if you make it unique, and you already have one 
Smith in the table, you will never be allowed to have another Smith in the 
table!


You need to sort out the issue of the primary key FIRST. Once that is 
resolved, you can start to think about making other keys unique or not. But 
right now, I think you're getting badly ahead of yourself.



create table members (
   logon_idvarchar(15),
   email_addr  varchar(30),
  last_name   varchar(30),
  member_type char(1),
   email_verified  char(1),
  logon_pwvarchar(15),
  date_added  date,
   last_login  timestamp,
   first_name  varchar(30),
   addr1   varchar(30),
   addr2   varchar(30),
   cityvarchar(20),
   state   varchar(20),
   zip varchar(15),
  phone_home  varchar(15),
   phone_officevarchar(15),
   phone_cell  varchar(15),
   mothers_maiden_name varchar(30),
  ip_of_useratsignup  varchar(16),
  primary key(login_id, email_addr, last_name)
);


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 06/03/2006


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

Re: Help on sql statement (not MySQL specifik)

[Rhino]

- Original Message - 
From: Søren Merser [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Sunday, March 05, 2006 9:12 AM
Subject: Help on sql statement (not MySQL specifik)



Hi

Could someone please help me out here?

TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4-KEEP AS IS
3| 2|6- DROP
4| 3|NULL- KEEP AS IS
5| 4|NULL-KEEP AS IS
6| 5|1-NULL
7| 5|3-DROP
8| 6|7-NULL
9| 6|3-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 
4,

selects all the unique id's with TYPE = 4 when appropriate otherwise NULL

like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still not 
clear on what you want but maybe this will help until you can clarify the 
question.


You use WHERE to limit your result set to only specific rows of the original 
table. Therefore, if you want only rows where the type is 4, you say:


Select column-list
from table-name
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID column in 
the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the result 
set and you just want to know the unique values of ID that had a type of 4, 
you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. There 
is only one row in your sample table that has a type of 4 so DISTINCT isn't 
going to do anything for you; you'll get the same result with or without 
DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want to 
update the data in your table permanently so that a null isn't null any 
longer (or a non-null value is null)? Or do you want to _display_ a null 
where something isn't null? I don't understand what your DROP and LEAVE 
AS IS remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

Re: Help on sql statement (not MySQL specifik)

[Rhino]

I don't really understand _why_ you want to do this but here is a query that 
gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave it 
alone, otherwise display null.


--
Rhino


- Original Message - 
From: Søren Merser [EMAIL PROTECTED]

To: mysql@lists.mysql.com; Rhino [EMAIL PROTECTED]
Sent: Sunday, March 05, 2006 11:46 AM
Subject: Re: Help on sql statement (not MySQL specifik)



Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with 
TYPE set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one is 
preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren




TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4-KEEP AS IS
3| 2|6- DROP
4| 3|NULL- KEEP AS IS
5| 4|NULL-KEEP AS IS
6| 5|1-NULL
7| 5|3-DROP
8| 6|7-NULL
9| 6|3-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 
4,
selects all the unique id's with TYPE = 4 when appropriate otherwise 
NULL


like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still not 
clear on what you want but maybe this will help until you can clarify the 
question.


You use WHERE to limit your result set to only specific rows of the 
original table. Therefore, if you want only rows where the type is 4, you 
say:


Select column-list
from table-name
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID column 
in the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the 
result set and you just want to know the unique values of ID that had a 
type of 4, you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. 
There is only one row in your sample table that has a type of 4 so 
DISTINCT isn't going to do anything for you; you'll get the same result 
with or without DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want to 
update the data in your table permanently so that a null isn't null any 
longer (or a non-null value is null)? Or do you want to _display_ a null 
where something isn't null? I don't understand what your DROP and 
LEAVE AS IS remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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




--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

Re: Help on sql statement (not MySQL specifik)

[Rhino]

I agree with Michael. If your data is actually messed up, the right solution 
is to fix the data and prevent it from getting messed up again FIRST. 
Writing SQL that compensates for the messed up data is often possible but 
doesn't fix the problem; you'll still need to fix the data and the sooner 
you do that, the better.


Fixing the data has a major side-benefit too: it usually makes your queries 
a lot easier and more logical. But, as Michael has said, you haven't 
explained very much about the 'Big Picture' of your situation so maybe there 
is nothing wrong with your data at all. In that case, the query I just 
posted should meet your needs.


If you _do_ have a data problem, Michael's suggestions make a lot of sense 
but if you don't understand them or if your situation is actually different 
than Michael assumes, please post again and people will try to help you.


--
Rhino


- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]

To: Søren Merser [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Sunday, March 05, 2006 1:13 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Søren Merser wrote:

Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with 
TYPE set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one is 
preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren


You appear to have several, separate issues.  First, your table has 
duplicate entries, which you don't want.  If you do not want duplicate 
entries, you should not allow them.  That is, you need a UNIQUE constraint 
on the id column.  You won't be able to add one, however, until you remove 
the existing duplicates.


To remove duplicates, you first have to decide which to keep and which to 
toss. In your example, you always keep the row with the lowest recno, but 
your description implies that when one of the duplicates has type = 4, you 
want to keep that one, regardless of recno.  Assuming that to be true, you 
need something like:


  DELETE t1
  FROM yourtable t1 JOIN yourtable t2 ON t1.id = t2.id
  WHERE (t1.recno  t2.recno AND t1.type !=4)
 OR (t1.recno  t2.recno AND t2.type = 4);

(The exact syntax depends on your version of mysql.  See the manual for 
details http://dev.mysql.com/doc/refman/4.1/en/delete.html.)


Now add a UNIQUE constraint on id so this won't ever happen again:

  ALTER TABLE yourtable ADD UNIQUE (id);

In your example, you have renumbered recno.  This is almost always a bad 
idea, but you can do it with:


  SET @i = 0;
  UPDATE yourtable SET recno = (@i := @i + 1);

Finally, now that you've fixed the table, the requested update is simple:

  UPDATE yourtable SET type = NULL WHERE type != 4;

Having said all that, I'm skeptical this is the best solution.  Perhaps it 
is just lack of imagination on my part, but I'm having trouble seeing why 
you would want to do things this way.  Having a column which should be 
unique, but isn't, and wanting to renumber your primary key column are 
both red flags.  I also find it strange that you seem to want to find the 
unique ids with type = 4, but you are changing every other type to NULL in 
the process.  Why not just select what you want, as Rhino suggested?


  SELECT DISTINCT id FROM yourtable WHERE type = 4;

Perhaps this all makes sense given the context. (You haven't told us 
much).  On the other hand, if you carefully describe what you are trying 
to accomplish, one of the many experts on the list may well be able to 
supply you with a better way.


Michael

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

Re: Help on sql statement (not MySQL specifik)

[Rhino]

You're absolutely right that I'd need some good luck for this query to work 
for every possible data value that the table could continue.


I realized the combination of 'group by id' and 'select id, type' was not 
very good SQL - 'select id, column-function' would be a much more standard 
construction to go with 'group by id' - as I developed that query. But I was 
too lazy to dig through the manual to find out exactly what MySQL would do 
with that query; it worked fine for the data given. But you're right, I 
should have at least warned that this was dubious SQL before posting it. The 
original poster could easily have though that this was actually good SQL 
when it isn't.


Again, cleaning up the data (assuming it is messed up!) should be the first 
priority and any query would just be a bandaid until that is done. The query 
would probably be a lot easier if the data was clean to start with.


In any case, thanks for keeping me honest.

--
Rhino

- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: Søren Merser [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Sunday, March 05, 2006 2:26 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Rhino wrote:
I don't really understand _why_ you want to do this but here is a query 
that gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave 
it alone, otherwise display null.


--
Rhino


Unfortunately, that won't work unless you are very lucky.  You aren't 
grouping by type, and CASE is not an aggregate function.  Mysql will use 
the value for type from the first row it finds for each id in the CASE 
statement.  The following illustrate the problem:


  DROP TABLE nu;
  CREATE TABLE nu (recno INT, id INT, type INT);
  INSERT INTO nu VALUES
  (1,1,NULL), (2,2,4), (3,2,6), (4,3,5), (5,3,4), (6,3,3);

  SELECT * FROM nu;
+---+--+--+
| recno | id   | type |
+---+--+--+
| 1 |1 | NULL |
| 2 |2 |4 |
| 3 |2 |6 |
| 4 |3 |5 |
| 5 |3 |4 |
| 6 |3 |3 |
+---+--+--+

  SELECT id, CASE type WHEN 4 THEN 4 ELSE NULL END AS type
  FROM nu
  GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 | 4|
|3 | NULL |
+--+--+

As you can see, id=3 has a row with type=4, but it isn't found.

You could do this:

  SELECT id, IF(SUM(type=4)0, 4, NULL) AS type FROM nu GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 |4 |
|3 |4 |
+--+--+

but it's hard to see how that's better than your previous, simpler 
suggestion


  SELECT DISTINCT id FROM nu WHERE type = 4;

Michael


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

Re: Help on sql statement (not MySQL specifik)

[Rhino]

I'm glad to hear that your data isn't corrupt! That would have complicated 
your life a bit, at least in the short term


The additional information you have supplied helps me understand a bit 
better but I still don't really understand enough. I'll try to ask some 
specific questions that will help me understand the data and what you are 
trying to do better.


1. What kind of join are you doing to combine these tables? Is it an inner 
join or some kind of outer join? Are you satisfied that it is correctly 
joining the tables and giving you a true picture of the different events 
affecting the patients? I just want to be sure that the join is giving the 
right data before we go any farther; if it isn't, we should fix the join 
first.


2. Does the id belong to a specific patient? For example, does id 2 belong 
to Tony Blair while id 3 belongs to Jacques Chirac? I think this must be the 
case, but I want to be sure.


3. What do you mean by an 'event'? Is this a surgical procedure like remove 
appendix or just something like emptying a bedpan? What exactly is event 4? 
If it's secret, that's okay but it would help me understand the problem 
better to know what event 4 and a some of the other events are. Can a 
patient have more than one event 4? For example, if event 4 is empty 
patient's bedpan that can probably happen many times but  if event 4 is 
patient died, that can obviously only happen once.


4. How do you propose to determine the time difference between events when 
you aren't storing the times that the events took place??? You described the 
record number (recno) as a simple ascending integer earlier but now I wonder 
if you mean that it is actually a timestamp or datetime value? Otherwise, I 
don't see how an expression like 7 - 4 (for records 7 and 4) is going to 
give you a value like 2 hours and 10 minutes.


5. What do you mean when you described eType as nominal and not interval 
data?


--
Rhino

- Original Message - 
From: Søren Merser [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Sunday, March 05, 2006 4:37 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Hi
Thank You for all Your efforts
I'll try once again to clarify my problem

My tabel (t_temp) isn't corrupt but is the result form a join from two 
other tables

1) t_base, with id's and basic info of patients.
2) t_events. with id (of the patient in question) and the type of the 
event.


As a patient, or id can have more than one event t_temp will end up with 
one or more records accordingly

if no event has occured for id, etype=NULL

t_temp:

id  |   etype
-
1|NULL
2|4
2|6
3|NULL
4|NULL
5|1
5|3
6|7
6|3

Now I want to examine for the event of etype=4, in particular the time 
between the events.
I want to extract all the id's with etype=4 i.e. the event in question has 
happend othervise NULL.

Order must be preserved.
Result: the number of records will mirror the total number of operations 
and the actual record number mirrors the time of the event



id  |   etype
-
1|NULL
2|4
3|NULL
4|NULL
5|NULL
6|NULL

6 operations in all, second operation had event 4

Kind regards Soren
Ps
1)
As I make subsets from t_temp depending other columns, the id's do not 
exactly indicate time of events

2)
etype is nominal, not inteval data so You can't use  or  operator


- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: Michael Stassen [EMAIL PROTECTED]
Cc: Søren Merser [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Sunday, March 05, 2006 9:05 PM
Subject: Re: Help on sql statement (not MySQL specifik)


You're absolutely right that I'd need some good luck for this query to 
work for every possible data value that the table could continue.


I realized the combination of 'group by id' and 'select id, type' was not 
very good SQL - 'select id, column-function' would be a much more 
standard construction to go with 'group by id' - as I developed that 
query. But I was too lazy to dig through the manual to find out exactly 
what MySQL would do with that query; it worked fine for the data given. 
But you're right, I should have at least warned that this was dubious SQL 
before posting it. The original poster could easily have though that this 
was actually good SQL when it isn't.


Again, cleaning up the data (assuming it is messed up!) should be the 
first priority and any query would just be a bandaid until that is done. 
The query would probably be a lot easier if the data was clean to start 
with.


In any case, thanks for keeping me honest.

--
Rhino

- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: Søren Merser [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Sunday, March 05, 2006 2:26 PM
Subject: Re: Help on sql statement (not MySQL

Re: -help

[Rhino]

It would be easier to answer a specific question

--
Rhino

- Original Message - 
From: Terry Spencer [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Sunday, March 05, 2006 6:41 PM
Subject: -help







Terry Spencer
Haigh Consultancy Services

Tel:  +44 (0)116 262 3966

Fax:  +44 (0)116 262 3946 (Leciester Office)

Fax:  +44 (0)870 052 4572 (Terry)

Mob: +44 (0)7796108244
www.haigh-cs.co.uk http://www.haigh-cs.co.uk










No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

Re: help regarding writing a query

[Rhino]

I'm not sure but the situation you are describing sounds like the so-called 
Bill of Materials Problem, sometimes called BOM for short.


I'm certain that some databases provide SQL to handle BOM problems; for 
example, DB2 which I know quite well, provides for BOMs. However, I just did 
a search in the MySQL 5.0 manual and could not find a single hit on Bill of 
Materials or BOM so I suspect that MySQL does not support this, although 
it might some day. Someone once told me about another database that supports 
BOMs; it might have been Oracle but I can't be sure. He and I discussed BOMs 
and I showed him the SQL used by DB2 to handle them; he said the SQL for 
BOMs in the other database was quite different but didn't show it to me.


In short, I suspect that solving your problem in MySQL will be difficult or 
maybe impossible. Solving it in DB2 or some other database should be 
possible if using another database is an option for you.
But don't give up yet! I may have misunderstood your requirement and it 
really isn't a BOM at all: after all, a BOM usually proceeds downwards from 
parents to children to grandchildren but you seem to want to go upwards; 
that may require a somewhat different approach.


I just did a search on Bill of Materials in the MySQL archives and found 
some useful information, particularly this article by Peter Brawley:


http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#parts_explosion

It describes a way to do a BOM (also known as a Parts Explosion) in MySQL 
and also gives many links to articles about BOMs which might be helpful to 
you.


Also, if you do a Google search on Bill of Materials MySQL in Google the 
way I just did, you'll find over 200,000 hits, some of which will point to 
tools or techniques that might help you with your problem.


--
Rhino

- Original Message - 
From: VenuGopal Papasani [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Thursday, March 02, 2006 5:24 AM
Subject: help regarding writing a query


HI all,
  This is venugopal.Being a silent member of this group.I am having a doubt
regarding writing a query.I can explain it with an example

  Consider i have a table called GetParents

  The table consists of two fieds called num as Integer
 ParentNum as Integer

  The table consists of the following data
   num ParentNum
1 4
2 1
3 5
6 7
4 2

Now the output should be as follows if i have given input as 4
 then output should be as
   num ParentNum
1 4
2 1
4 2

Explaination of Output:
As the given input is 4.Input is compared with num and we get 4-
2.Now ParentNum is taken and checked in the table for 2 being the num and
compared and 2-1 is obtained and now again the comparision is taken and
1-2 is given as ouput

 NOw the problem.Can we get the result by writing only one query or we
have to write more no of queries.If it is possible to get this with one
query then can u please give me the query.

 Can u give me the query or tell me the source where i can get examples
of such queries.

  It will be very much helpful for me.

Thanks in advance,

Regards,
venu.






No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

Re: Want mysql to return tablename.fieldname format

[Rhino]

I don't understand what you want. If you have the original query, it should 
be apparent from it where each 'id' column originated. If you're not sure 
how to read the query, post it and we can help you figure out which table 
provided each 'id' column.


--
Rhino

- Original Message - 
From: Ryan Stille [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Thursday, March 02, 2006 12:42 PM
Subject: Want mysql to return tablename.fieldname format


I am working with an existing compilcated query someone wrote years ago.
When I dump the data from the query to try to figure out why I'm getting
unexpected data, I have three fields named id.  Is there anyway to
tell mysql to name the fields with the table name when they are
returned, so they show up as ads.id, track.id, etc?

-Ryan


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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

Re: TimeStamp issue

[Rhino]

If you need a broader range of dates, you could use DATETIME instead of 
TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through 
'-12-31 23:59:59'. The only big difference is that DATETIME does not 
store the fractional part of the seconds, e.g. 
milliseconds/microseconds/nanonseconds. If you have to keep the fractional 
part of the seconds, you could store them in a second column defined as some 
kind of integer.


--
Rhino

- Original Message - 
From: rtroiana [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Thursday, March 02, 2006 10:10 AM
Subject: TimeStamp issue



Hi All,



I have recently noticed in the MySQL 5.0 documentation in section 11.3.1.
The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that



TIMESTAMP values cannot be earlier than 1970 or later than 2037. This 
means

that a date such as '1968-01-01', while legal as a DATETIME or DATE value,
is not valid as a TIMESTAMP value and is converted to 0.



Is that a correct range for TimeStamp? It's not big enough to be used in a
real life application.



I plan to use DATETIME instead of TIMESTAMP. I used to use
CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my
TimeStamp column. Is there a way to assign default value to a DateTime
column, since I couldn't find that in the documentation?



Thanks,

Reema










No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

Fw: TimeStamp issue

[Rhino]

I'm putting this back on the list where it belongs; that enables everyone to 
benefit from the discussion, both now and in the future via the archives.


--

Sorry, you're right, I didn't read your entire question thoroughly. You set 
the default value for a DATETIME column (or any other type for that matter) 
in the CREATE TABLE statement. For example:


create table if not exists dates03
(id smallint not null default 99,
my_datetime datetime not null default '2006-03-02 12:34:56',
primary key(id));

Please note that a datetime value can be expressed in several different 
formats. The different formats are explained here: 
http://dev.mysql.com/doc/refman/5.0/en/datetime.html.


--
Rhino


- Original Message - 
From: rtroiana [EMAIL PROTECTED]

To: 'Rhino' [EMAIL PROTECTED]
Sent: Thursday, March 02, 2006 1:29 PM
Subject: RE: TimeStamp issue


Thanks for the reply. I'm using DATETIME instead of TIMESTAMP now. 
Although

all I wanted to know was if there's any way I can set default values to
DATETIME column.

++Reema

-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 02, 2006 1:26 PM
To: rtroiana; mysql@lists.mysql.com
Subject: Re: TimeStamp issue

If you need a broader range of dates, you could use DATETIME instead of
TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through
'-12-31 23:59:59'. The only big difference is that DATETIME does not
store the fractional part of the seconds, e.g.
milliseconds/microseconds/nanonseconds. If you have to keep the fractional
part of the seconds, you could store them in a second column defined as 
some


kind of integer.

--
Rhino

- Original Message - 
From: rtroiana [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Thursday, March 02, 2006 10:10 AM
Subject: TimeStamp issue



Hi All,



I have recently noticed in the MySQL 5.0 documentation in section 11.3.1.
The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that



TIMESTAMP values cannot be earlier than 1970 or later than 2037. This
means
that a date such as '1968-01-01', while legal as a DATETIME or DATE 
value,

is not valid as a TIMESTAMP value and is converted to 0.



Is that a correct range for TimeStamp? It's not big enough to be used in 
a

real life application.



I plan to use DATETIME instead of TIMESTAMP. I used to use
CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my
TimeStamp column. Is there a way to assign default value to a DateTime
column, since I couldn't find that in the documentation?



Thanks,

Reema











No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006





--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

Re: TimeStamp issue

[Rhino]

Thanks for keeping me honest! I'd 
forgotten that MySQL timestamps don't keep the fractional parts of seconds 
either; I mostly use DB2 which keeps the fractional parts (microseconds) and 
forgot about this quirk of MySQL.

--
Rhino

  - Original Message - 
  From: 
  [EMAIL PROTECTED] 
  To: Rhino 
  Cc: mysql@lists.mysql.com ; rtroiana 
  
  Sent: Thursday, March 02, 2006 1:42 
  PM
  Subject: Re: TimeStamp issue
  In fact, no time values in 
  MySQL are fractional (yet). All times are stored to the nearest second 
  regardless of which date-time-like storage type you use. They way Rhino 
  phrased his answer, it sounded as though TIMSTAMP would save fractional 
  seconds. It doesn't. He is spot on about needing a separate column to store 
  any values that represent fractions of seconds. Shawn GreenDatabase AdministratorUnimin 
  Corporation - Spruce Pine "Rhino" [EMAIL PROTECTED] wrote on 
  03/02/2006 01:25:36 PM: If you need a broader range of dates, you 
  could use DATETIME instead of  TIMESTAMP: DATETIME can handle the 
  range '1000-01-01 00:00:00' through  '-12-31 23:59:59'. The only 
  big difference is that DATETIME does not  store the fractional part of 
  the seconds, e.g.  milliseconds/microseconds/nanonseconds. If you have 
  to keep the fractional  part of the seconds, you could store them in a 
  second column defined as some  kind of integer.  
  -- Rhino  - Original Message -  From: 
  "rtroiana" [EMAIL PROTECTED] To: 
  mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 
  AM Subject: TimeStamp issueHi 
  All, I have recently 
  noticed in the MySQL 5.0 documentation in section 11.3.1.  The 
  DATETIME, DATE, and TIMESTAMP Types, it's mentioned that  
 "TIMESTAMP values cannot be earlier than 1970 
  or later than 2037. This   means  that a date such as 
  '1968-01-01', while legal as a DATETIME or DATE value,  is not 
  valid as a TIMESTAMP value and is converted to 0."  
 Is that a correct range for TimeStamp? It's not 
  big enough to be used in a  real life application. 
  I plan to use DATETIME instead of 
  TIMESTAMP. I used to use  "CURRENT_TIMESTAMP on update 
  CURRENT_TIMESTAMP" as default value for my  TimeStamp column. Is 
  there a way to assign default value to a DateTime  column, since I 
  couldn't find that in the documentation?   
Thanks,   Reema  
   
   
No virus found in this incoming message. Checked by 
  AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - 
  Release Date: 01/03/2006--  No 
  virus found in this outgoing message. Checked by AVG Free 
  Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release 
  Date: 01/03/2006   --  MySQL General Mailing 
  List For list archives: http://lists.mysql.com/mysql To 
  unsubscribe:  
  http://lists.mysql.com/[EMAIL PROTECTED] 
  
  
  

  No virus found in this incoming message.Checked by AVG Free 
  Edition.Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 
  01/03/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

Re: Problem with UNION

[Rhino]

Shaun [EMAIL PROTECTED] wrote in message 
news:[EMAIL PROTECTED]

Hi,

The following 3 queries on their own produce more rows than if I UNION 
them

together:

SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('W_', Work_Type_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Booking'

UNION

SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('T_', Task_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Task'

UNION

SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('U_', Unavailability_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Unavailability'

I am trying to get all types of bookings - unavailability, tasks and
bookings into one result but am confused as to why the query produces less
rows this way.

Any tips here would be greatly appreciated.


UNION removes duplicate rows from the result set; in other words, if two 
identical rows are produced by two or more of the queries that have been 
UNIONed together, the duplicate rows are removed from the final result set. 
If you don't want to remove the duplicates, use UNION ALL instead of UNION. 
(I'm not sure if UNION ALL is supported in MySQL but it certainly is in DB2, 
my main database.)


Perhaps that's why you have fewer rows in the UNION result than you do by 
summing up the row counts in the individual queries?


By the way, I'm not sure how you posted your question but my copy of Outlook 
Express thinks it's a newsgroup post, not an email so I couldn't reply in 
the normal way.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/271 - Release Date: 28/02/2006


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

Re: returning empty columns

[Rhino]

- Original Message - 
From: 2wsxdr5 [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, February 28, 2006 3:43 AM
Subject: returning empty columns


This is probably going to sound like an odd request, but is there a way to 
return empty columns in Mysql.  For example a roll call sheet I want to do 
a select of names from my table and then add a column for each  of the 
next 12 weeks.  I tried this.


SELECT `Call`, concat(FName, ' ', LName) as Name, 'Mar-6' ,'Mar-13', 
'Mar-20', 'Mar-27'

FROM table
Order BY LName, FName

The problem is it put that date on every row and I just want the names to 
show up in the column header  I know I could just write some php code to 
print out a table with the columns but I have a handy php function the 
prints the out put of a query in a table already so if I can find the 
right query I don't have to change that code any.


In 20+ years of writing and teaching SQL I can't remember anyone ever 
wanting to do this but you can easily get a blank column (or twelve) with 
just a slight modification of the technique you already tried. The values 
you put within apostrophes, like 'Mar-20', are just literals so, instead of 
putting text between the apostrophes, just write two consecutive 
apostrophes. Therefore:


SELECT `Call`, concat(FName, ' ', LName) as Name, '' ,'', '', ''
FROM table
Order BY LName, FName

will give you the same information you got before but each of the four extra 
columns should be empty. If you want those columns to have titles, use an AS 
expression, like this:


SELECT `Call`, concat(FName, ' ', LName) as Name, '' as Eenie ,'' as 
Meenie, '' as Miney, '' as Moe

FROM table
Order BY LName, FName

Be careful when typing my examples: to get a blank column, you need two 
consecutive apostrophes (sometimes called single quotes) but the AS 
expressions need to be within double quotes.


Wait! I was wrong! I just tried it using single quotes in the AS expressions 
and it still worked fine:


SELECT `Call`, concat(FName, ' ', LName) as Name, '' as 'Eenie' ,'' as 
'Meenie', '' as 'Miney', '' as 'Moe'

FROM table
Order BY LName, FName

It even worked when I used backtics (`):

SELECT `Call`, concat(FName, ' ', LName) as Name, '' as `Eenie` ,'' as 
`Meenie`, '' as `Miney`, '' as `Moe`

FROM table
Order BY LName, FName

You can also use pairs of double quotes to create the empty columns:

SELECT `Call`, concat(FName, ' ', LName) as Name,  as `Eenie` , as 
`Meenie`,  as `Miney`,  as `Moe`

FROM table
Order BY LName, FName

But you can't use pairs of backtics:

SELECT `Call`, concat(FName, ' ', LName) as Name, `` as `Eenie` , `` as 
`Meenie`, `` as `Miney`, `` as `Moe`

FROM table
Order BY LName, FName

So, MySQL is more tolerant than I realized.

--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/271 - Release Date: 28/02/2006


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

Re: Cleaning Bin-Log Files

[Rhino]

- Original Message - 
From: Shaun Adams [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, February 28, 2006 5:19 PM
Subject: Cleaning Bin-Log Files



Does anyone know of a method or script that will automatically delete
bin-log files after x days?  I've got a few customers, all utilizing a
master and slave server... my problem is that the harddrives often get
filled to capacity if they go unchecked over a few months.  Most of this 
is
attributed to the binlogs which fill to about 1GB and then starts a new 
log.

We're using MySQL 4.026.

Any thoughts?


This may be excessive for what you are trying to do but it's a starting 
point.


The following bash script is one that I run every day via a cron job. (You 
didn't say what OS your server is; mine is Linux Mandrake so I use cron for 
scheduling.) The script is fairly heavily commented so you'll probably 
understand it if you know bash but feel free to ask followup questions if 
things aren't clear. Basically, the script gets the names of each of the 
databases on the system, does a backup via mysqldump, then lists all backups 
older than a given number of days, then deletes those older backups. It 
writes a short report and emails it to me and the system administrator for 
the server. It's run without problems for several years now.


You're not worried about database backups so you can probably remove the 
do/done and simply list all the bin-logs that meet your criteria, then 
delete them. You may not want to bother sending that email but you could use 
cron to schedule this script to run as often as you like.



#!/bin/bash

#This script makes a separate database-level backup of each of the current 
MySQL databases and

#deletes backups older than a certain number of days.
#This script is normally invoked via a cron job so that it runs once per day 
in the middle of the night.

#The crontab entry looks like this:
#0 3 * * * sh /home/rhino/MySQL/backup2.bash  /home/rhino/MySQL/backup2.out 
21; cat /home/rhino/MySQL/backup2.out | sendEmail -f [EMAIL PROTECTED] -t 
[EMAIL PROTECTED] [EMAIL PROTECTED]

-u MySQL Backup Report

USERID=myuserid; #The userid to use for creating the backup
PASSWORD=mypasswd; #The password to use for creating the backup
BACKUP_TIMESTAMP=`/bin/date +%Y%m%d-%H%M%S`; #The timestamp 
(MMDD-HHMMSS) of the backup
BACKUP_PATH=/home/rhino/MySQL/backup; #The directory into which the backup 
will be written
NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups to 
keep


echo ** REPORT BEGINS **;
echo
echo Program Name: $0
report_date=`/bin/date`
echo Report Date: $report_date;
echo

#Display the non-secret values used in this run.
echo Backup Values:;
echo   Backup timestamp is $BACKUP_TIMESTAMP;
echo   Backup path is $BACKUP_PATH;
echo   Number of daily backups to keep = $NUMBER_OF_DAILY_BACKUPS_TO_KEEP;

#For each database currently in MySQL, take a database-level backup, then 
list any backups older than a certain number of days, then delete those old 
backups.

for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql

  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old 
backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';'; 
#delete old backups (if any)

done

echo
echo ** REPORT ENDS **;



--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/271 - Release Date: 28/02/2006


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

Re: using WHERE and OR in a query

[Rhino]

Define not work: do you mean you get a compile error? A runtime error? Or 
a result that differs from what you expected?


If you got an error message, please state the full message text. If the 
result simply differed from what you wanted, please give us a small sample 
of data, your expectations for what the result should have been, and 
information about what you actually got.


The CREATE TABLE statement could also be helpful in determining what went 
wrong.


--
Rhino

- Original Message - 
From: Bruce Therrien [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, February 25, 2006 4:28 PM
Subject: using WHERE and OR in a query



Why does this query not work using mysql 3.23.58?

$SQL =SQL;
UPDATE $tablename SET rank = '$qdj_rank_en', rank_icon = '$qdj_rank_icon', 
qdj = qdj+'$adata{qdj_credit}', gold = gold+'$adata{gold_credit}' WHERE 
subscribe != '' OR artist = '1' OR rank_change != '1'

SQL
$dbh-do($SQL) ||  cgierr(Reason: $!);



--
Bruce Therrien [EMAIL PROTECTED]

This is the end of the internet.
Please turn around and go back.


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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006


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

Fw: Re[2]: using WHERE and OR in a query

[Rhino]

I'm putting this discussion back in the mailing list where it belongs; that 
way other people can contribute suggestions and learn from the discussion, 
either now or in the future via the archives.


--

Is there any possibility the Update is doing exactly what your SQL is 
telling it to do? You're asking for a row to be updated if _any_ of the 
following are true:

- the subscribe column is not blank
- the artist column contains 1
- the rank_change column does not contain 1

If at least one of those conditions is true for each row in the table, then 
it is entirely reasonable that every row gets updated because every row 
qualifies for the update. That's why I suggested you provide sample data, 
expected results and actual results: if you had done that, I probably 
wouldn't have to ask this followup question.


What language is your code? It looks a bit like Perl to me but I sometimes 
confuse Perl and PHP.


Is '!=' a legitimate way to say 'not equal' in this version of MySQL and the 
language you are using? I normally use either the NOT keyword or the symbols 
'' to negate conditions, for example:

- WHERE NOT subscribe = ''
- WHERE subscribe  ''
Some dialects of SQL and some programming languages support '!=' but not all 
of them do. You should check that in the MySQL reference for 3.23 and the 
programming language, whatever it is.


Are you sure you are executing the statement against the table you _think_ 
you are updating? For example, Is it possible that you mean to execute the 
statement against a production table but are actually executing it against a 
test table that has very different data than the production table?


--
Rhino

- Original Message - 
From: Bruce Therrien [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Sent: Saturday, February 25, 2006 4:37 PM
Subject: Re[2]: using WHERE and OR in a query



the query is updating all records with no regards to the WHERE clauses.


On Sat, 25 Feb 2006 16:36:38 -0500
Rhino [EMAIL PROTECTED] wrote:

Define not work: do you mean you get a compile error? A runtime error? 
Or

a result that differs from what you expected?

If you got an error message, please state the full message text. If the
result simply differed from what you wanted, please give us a small 
sample

of data, your expectations for what the result should have been, and
information about what you actually got.

The CREATE TABLE statement could also be helpful in determining what went
wrong.

--
Rhino

- Original Message - 
From: Bruce Therrien [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, February 25, 2006 4:28 PM
Subject: using WHERE and OR in a query


 Why does this query not work using mysql 3.23.58?

 $SQL =SQL;
 UPDATE $tablename SET rank = '$qdj_rank_en', rank_icon = 
 '$qdj_rank_icon',

 qdj = qdj+'$adata{qdj_credit}', gold = gold+'$adata{gold_credit}' WHERE
 subscribe != '' OR artist = '1' OR rank_change != '1'
 SQL
 $dbh-do($SQL) ||  cgierr(Reason: $!);



 -- 
 Bruce Therrien [EMAIL PROTECTED]


 This is the end of the internet.
 Please turn around and go back.


 -- 
 MySQL General Mailing List

 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: 
 http://lists.mysql.com/[EMAIL PROTECTED]



 -- 
 No virus found in this incoming message.

 Checked by AVG Free Edition.
 Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 
 24/02/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006


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





--
Bruce Therrien [EMAIL PROTECTED]

This is the end of the internet.
Please turn around and go back.


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006


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

Re: Number Searches

[Rhino]

- Original Message - 
From: CodeHeads [EMAIL PROTECTED]

To: MySQL-List mysql@lists.mysql.com
Sent: Wednesday, February 22, 2006 4:52 PM
Subject: Number Searches



Hello all,
I have searched but cannot find what I am looking for.

I have a full index index on a table and on of the fields is a number
field (IP Address). Can MySQL search for numbers??



I don't know the answer to your question but I'm pretty sure it has come up 
before in this list. You might want to do a search on this topic in the 
MySQL archives at http://lists.mysql.com/. I think the answer depends on 
exactly how you store your IP addresses, i.e. datatype and number of columns 
used.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.12/266 - Release Date: 21/02/2006


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

Re: Database design help

[Rhino]

- Original Message - 
From: Mike Blezien [EMAIL PROTECTED]

To: MySQL List mysql@lists.mysql.com
Sent: Friday, February 17, 2006 7:49 AM
Subject: Database design help



Hello,

we currently have a small database setup for affilates and visitor/leads. 
I believe we have a one to many application, one affiliate can have 
several visitor/leads but each visitor can only be assigned to one 
affiliate.


What I need to know if this the best design for this setup. Basically a 
visitor fills out a form, and is assigned to one affiliate. So I was 
wondering is it better to create a  joining table between the 
`affiliates` table and the `visitors` table or will this design be 
efficent as it is. Below are the 2 tables in question


CREATE TABLE IF NOT EXISTS affiliates (
  affid int(6) unsigned NOT NULL auto_increment,
  affiliate_id int(10) unsigned NOT NULL default '',
  affiliate_email varchar(60) NOT NULL default '',
  PRIMARY KEY  (affid),
  KEY affiliate_id (affiliate_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS visitors (
  visitorid int(6) unsigned NOT NULL auto_increment,
  fname varchar(20) NOT NULL default '',
  lname varchar(20) NOT NULL default '',
  phone varchar(20) NOT NULL default '',
  email varchar(60) NOT NULL default '',
  state char(2) NOT NULL default '',
  ip varchar(20) NOT NULL default '',
  dtime datetime NOT NULL default '-00-00 00:00:00',
  exported varchar(10) default NULL,
  affid int(6) unsigned NOT NULL default '0',
  PRIMARY KEY  (visitorid),
  KEY email (email),
  KEY affid (affid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


When you say joining table, I assume you mean an intersection table or 
association table, which are the more common terms that describe something 
used to break a many-to-many relationship into two one-to-many 
relationships. I've never heard it described as a joining table but I 
_think_ we're talking about the same thing


In any case, I don't think you need anything but the two tables you have 
here. If there is only ever going to be a single affiliate assigned to a 
given visitor, then this is a one-to-many relationship and there is no need 
for an additional table. However, I would suggest one small amendment to 
your visitors table. Add the clause:


   FOREIGN KEY (affid) references affiliates(affid) on delete INSERT A 
DELETE RULE HERE


This will ensure that you never add an affid other than a value found in the 
Affiliates table to the affid column of the visitors table. It will also 
ensure the proper behaviour when deletes take place in the affiliates table. 
For example, if you use ON DELETE CASCADE as your delete rule, if one of the 
affliates is deleted from the affiliates table, all of the rows with his ID 
will also be deleted from the visitors table. If you use ON DELETE RESTRICT, 
you will not be able to delete an affiliate from the affiliates table unless 
all of the Visitors rows with his ID have had their affid changed to that of 
some other affiliate. If you use on DELETE SET NULL, you can freely delete 
affiliates even if they have rows in the Visitors table; the Visitors rows 
will just have their affids set to null, which effectively means that those 
Visitors have no assigned affiliate.


--
Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.10/263 - Release Date: 16/02/2006


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

Re: selecting min, max

[Rhino]

- Original Message - 
From: Octavian Rasnita [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Monday, February 13, 2006 9:07 AM
Subject: selecting min, max



Hi,

I have a table with the following fields:

symbol, date_time, price, volume

I need to extract a list which the following values from this table, for
each hour (in date_time field):

- symbol
- min(price)
- max(price)
- price where date_time is the earliest for that certain hour.
- price where the date_time is the last from that hour.
- The sum of volume from that hour.

I have tried to get the list of symbols, then get each hourly period and
calculate those 6 values for each period, but there are many symbols and
very many periods, and it takes very very much time.

Is there a more intelligent way of getting those values in another way 
than

symbol by symbol and period by period?

It's hard to answer your question since you haven't given us any examples of 
the SQL you've already tried. You haven't told us which version of MySQL you 
are using, either. That makes a big difference since newer versions offer 
many more SQL capabilities like views and subqueries that could really help 
you.


You certainly shouldn't have to write separate queries for each different 
symbol that you are using!


Have you looked at the GROUP BY clause? If you haven't, I think you'll find 
that it is _very_ helpful. A query like:


select symbol, max(price) as Maximum_Price, min(price) as Minimum_Price
from mytable
group by symbol

should show you a single row for each symbol that you have in your table. 
Each row will contain the maximum and minimum prices for that symbol.


Of course you will still need to add the time logic to that example so that 
rows for each hour are grouped together as well. But I can't do that without 
seeing a full definition of the table and a few sample rows so that I can 
really understand the data.


Something like that should get you everything you want in just one query. I 
don't know how it will perform but if it doesn't perform well, you should be 
able to improve the performance dramatically by creating appropriate indexes 
on the data. I can't advise you on the construction of indexes in MySQL - I 
don't know enough about how MySQL uses indexes - but others on this mailing 
list are very experienced in this area and should be able to guide you.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 13/02/2006


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

Re: selecting min, max

[Rhino]

I've just spent the last couple of hours trying various queries to get the 
result you want. I'm afraid I didn't find an answer for you. I think I 
understand exactly what you want but I couldn't figure out how to write the 
query you need.


Unfortunately, I'm using 4.0.15 and I don't even have subquery support in 
that version. I might be able to solve the problem in DB2 SQL, which I know 
better, but that won't help you because MySQL doesn't appear to support the 
techniques I would use in DB2, like nested table expressions and common 
table expressions.


Therefore, I think you should look very carefully at the two suggestions 
that Peter Brawley gave you earlier in the day. They look promising and I 
think you might be able to solve the problem that way.


Sorry I couldn't help more.

--
Rhino

- Original Message - 
From: Octavian Rasnita [EMAIL PROTECTED]
To: Octavian Rasnita [EMAIL PROTECTED]; Rhino [EMAIL PROTECTED]; 
mysql@lists.mysql.com

Sent: Monday, February 13, 2006 3:16 PM
Subject: Re: selecting min, max



PS, I have forgotten to tell that I am using MySQL 5.

Thank you.

Teddy

- Original Message - 
From: Octavian Rasnita [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Monday, February 13, 2006 10:00 PM
Subject: Re: selecting min, max



Hi,

From: Rhino [EMAIL PROTECTED]
...
  I need to extract a list which the following values from this table,

for

  each hour (in date_time field):
 
  - symbol
  - min(price)
  - max(price)
  - price where date_time is the earliest for that certain hour.
  - price where the date_time is the last from that hour.
  - The sum of volume from that hour.
 
  I have tried to get the list of symbols, then get each hourly period

and

  calculate those 6 values for each period, but there are many symbols

and

  very many periods, and it takes very very much time.
 
  Is there a more intelligent way of getting those values in another 
  way

  than
  symbol by symbol and period by period?
 
 It's hard to answer your question since you haven't given us any

examples

of
 the SQL you've already tried. You haven't told us which version of 
 MySQL

you
 are using, either. That makes a big difference since newer versions

offer

 many more SQL capabilities like views and subqueries that could really
help
 you.

 You certainly shouldn't have to write separate queries for each

different

 symbol that you are using!


Here is the table definition. The table is simple, but what I want is
complicated:

CREATE TABLE `tickers` (
`symbol` varchar(20) NOT NULL,
`last_volume` bigint(20) unsigned default NULL,
`last_price` decimal(20,4) unsigned default NULL,
`last_update` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I want to get a list of values for more periods of time, 5 minutes, 15
minutes, and hourly.

I need to get:

symbol
date_format(last_update, '%Y-%m-%d') as date
date_format(last_update, '%H:%i:%s') as time
min(last_price) as low  (The min value of last_price for that period)
max(last_price) as high  (the max price from that period)
last_price as open  (where last_update=min(last_update) from that period)
last_price as close  (where last_update=max(last_update) from that 
period)


The result data should look something like:

Symbol,data,time,low,high,open,close
simb1,2006-02-08,10:15:00,1000,1200,1050,1150
simb1,2006-02-08,10:30:00,1100,1150,1150,1150
simb1,2006-02-08,10:45:00,1000,1200,1050,1150
simb1,2006-02-08,11:00:00,1050,1200,1050,1150
simb1,2006-02-08,11:15:00,1000,1200,1050,1150

... then here follow the rest of records for simb1 and for other symbols.

You may see that the first time is 10:15:00, the next time is 10:30:00,
the next is 10:45, so the period of time is 15 minutes.

The first low is the lowest price between 10:15:00 and 10:30:00 and the
high is the highest price in that period.
The first open value is the last_price of the first trade from that
period and the close price is the last_price of the latest trade from

that

period.

I don't know if MySQL can create a query that can get those values fast
enough.

Thank you very much.


Teddy


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



--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006





--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 13/02/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 13/02/2006


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

Fw: Multiple many-to-many SELECT

[Rhino]

Oops, I meant to send this to the list so that all could benefit.

--
Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: Jessica Yazbek [EMAIL PROTECTED]
Sent: Sunday, February 12, 2006 4:19 PM
Subject: Re: Multiple many-to-many SELECT


Assuming you are only worried about getting the rows that match, you need 
to do this:


select m.title, d.director_last_name, p.producer_last_name
from movies m inner join director_movies dm on m.id = dm.movie_id
inner join director d on dm.director_id = d.id
inner join producer_movies pm on m.id = pm.movie_id
inner join producer p on pm.producer_id = p.id;

In this case, a row will only appear in the final result set if the movie 
has a corresponding row in director_movies and producer_movies and the 
director_id and producer_id in those tables have matching rows in director 
and producer. I don't know how familiar you are with database terminology 
but these are called 'inner' joins.


If you also want to pick up rows that don't match, e.g. a movie in the 
movies table has no corresponding row in the director_movies or an id in 
director_movies has no corresponding row in director, then you need to do 
'right' or 'left' joins. I won't attempt to explain these here but if you 
need an explanation, post again and I will try to explain the concept and 
give an example using your tables.


Or search the MySQL archives and you will probably find some examples and 
explanations. I *think* I wrote something like that in the past year or 
two since the MySQL manual is sadly deficient in describing joins.


--
Rhino

- Original Message - 
From: Jessica Yazbek [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Sent: Sunday, February 12, 2006 2:30 PM
Subject: Re: Multiple many-to-many SELECT



By request, here are the create statements for my tables:

movies | CREATE TABLE `movies` (
  `id` int(11) NOT NULL default '0',
  `catalog_description` text,
  `title` text,
  `website_url` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

director | CREATE TABLE `director` (
  `id` int(11) NOT NULL auto_increment,
  `director_first_name` text NOT NULL,
  `director_last_name` text NOT NULL,
  PRIMARY KEY  (`director_first_name`(100),`director_last_name`(100)),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

producer | CREATE TABLE `producer` (
  `id` int(11) NOT NULL auto_increment,
  `producer_first_name` text NOT NULL,
  `producer_last_name` text NOT NULL,
  PRIMARY KEY  (`producer_first_name`(100),`producer_last_name`(100)),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

director_movies | CREATE TABLE `director_movies` (
  `movie_id` int(11) NOT NULL default '0',
  `director_id` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

producer_movies | CREATE TABLE `producer_movies` (
  `movie_id` int(11) NOT NULL default '0',
  `producer_id` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Again, I'm trying to select from movies,director, and producer, using 
only one SELECT statement. Director and Producer are related to  movies 
via the tables director_movies and producer_movies.


Thanks again!
Jessica

On Feb 12, 2006, at 11:05 AM, Peter Brawley wrote:


Jessica,

To figure out what's causing your error, I for one would need the 
CREATE statements.


PB

Jessica Yazbek wrote:

Hello,

I apologize if this is a common question; I have been working with  it 
and googling for days, and can't seem to find anyone who has  been 
trying to do the same thing that I am. Maybe I'm using the  wrong 
keywords. In any event, I am desperate for help. Here is my  problem:


I have a database with several tables related on a many-to-many  basis. 
Here is a simplified description:


TABLE: movies
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| id  | int(11) |  | PRI | 0   |   |
| catalog_description | text| YES  | | NULL|   |
| title   | text| YES  | | NULL|   |
| website_url | text| YES  | | NULL|   |
+-+-+--+-+-+---+
TABLE: director
+-+-+--+-+- 
++

| Field   | Type| Null | Key | Default |  Extra |
+-+-+--+-+- 
++
| id  | int(11) |  | UNI | NULL| 
auto_increment |

| director_first_name | text|  | PRI |  | |
| director_last_name  | text|  | PRI |  | |
+-+-+--+-+- 
++

TABLE: producer
+-+-+--+-+- 
++

| Field   | Type| Null | Key | Default |  Extra

Fw: Insert and Update together

[Rhino]

Oops, I meant to send this to the list so that everyone could benefit, not 
just to Andre.


--
Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: Andre Matos [EMAIL PROTECTED]
Sent: Saturday, February 11, 2006 12:11 PM
Subject: Re: Insert and Update together




- Original Message - 
From: Andre Matos [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, February 11, 2006 10:54 AM
Subject: Insert and Update together



Hi List,

I would like to know if it is possible to combine Insert and Update in 
one

SQL instruction. This is what I want to do:

I have two tables: one where I will perform and Update replacing m0 by
scr. If MySQL find a m0, it will need to perform an insert into a log
table including the information updated.

I am trying to avoid writing a php4 program to do this. I am using MySQL 
4.1


Thanks for any help.



Have a look at the these two pages from the MySQL manual; I think one or 
the other of them will do what you want to do:


http://dev.mysql.com/doc/refman/4.1/en/replace.html
http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html

--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 10/02/2006


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

Re: Multiple many-to-many SELECT

[Rhino]

Jessica,

It think your first mistake is that you are trying to implement many-to-many 
relationships directly. Although it is theoretically possible to do so, I've 
never seen anyone do it myself. I believe the normal approach is to break 
each many-to-many relationship into two one-to-many relationships with an 
association table (sometimes called an intersection table) in the middle. 
Then, you join the tables together as needed, using inner, left or right 
joins as appropriate for your situation.


I spent several hours detailing most of this in emails on this list several 
months back in response to a similar question and I _really_ don't want to 
go through all that typing again :-) Instead, I'm going to refer you to the 
MySQL archives. If you go to this page - 
http://lists.mysql.com/mysql/171636 - you will see the beginning of a thread 
where I was one of the participants. We were discussing many-to-many 
designs. You probably don't need to read the whole thread but certainly read 
the first few posts in the thread, especially 
http://lists.mysql.com/mysql/171645, which is where I first describe how to 
implement a many-to-many relationship between members (of a video club) and 
the titles of the movies they rent.


That should get you started. You can read more in that thread to hear more 
of the pros and cons of the issue but you may find this thread wanders 
somewhat and is completely relevant to your concerns.


Another thing you could try, to see other discussions of many-to-many 
implementations, is to go to http://lists.mysql.com/ and fill in the search 
box as follows:

Search mailing lists for: intersection table
Within: MySQL General Discussion
Matching: all of the words
since: the beginning

You may find that other people explain the idea more clearly or convincingly 
than I do ;-)


I have to dash but if you have followup questions, post them in the list and 
I, or someone else, will likely be able to answer.


--
Rhino


- Original Message - 
From: Jessica Yazbek [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Thursday, February 09, 2006 5:28 PM
Subject: Multiple many-to-many SELECT


Hello,

I apologize if this is a common question; I have been working with it
and googling for days, and can't seem to find anyone who has been
trying to do the same thing that I am. Maybe I'm using the wrong
keywords. In any event, I am desperate for help. Here is my problem:

I have a database with several tables related on a many-to-many
basis. Here is a simplified description:

TABLE: movies
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| id  | int(11) |  | PRI | 0   |   |
| catalog_description | text| YES  | | NULL|   |
| title   | text| YES  | | NULL|   |
| website_url | text| YES  | | NULL|   |
+-+-+--+-+-+---+

TABLE: director
+-+-+--+-+- 
++

| Field   | Type| Null | Key | Default |
Extra  |
+-+-+--+-+- 
++

| id  | int(11) |  | UNI | NULL|
auto_increment |
| director_first_name | text|  | PRI |
||
| director_last_name  | text|  | PRI |
||
+-+-+--+-+- 
++


TABLE: producer
+-+-+--+-+- 
++

| Field   | Type| Null | Key | Default |
Extra  |
+-+-+--+-+- 
++

| id  | int(11) |  | UNI | NULL|
auto_increment |
| producer_first_name | text|  | PRI |
||
| producer_last_name  | text|  | PRI |
||
+-+-+--+-+- 
++


TABLE: director_movies
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| movie_id| int(11) |  | | 0   |   |
| director_id | int(11) |  | | 0   |   |
+-+-+--+-+-+---+

TABLE: producer_movies
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| movie_id| int(11) |  | | 0   |   |
| producer_id | int(11) |  | | 0   |   |
+-+-+--+-+-+---+

There are actually several more related tables and fields, but I
think this is enough to give an idea of what I have. What I am trying

QL 4.1 or greater.Re: MySQL says, Ich don't think so (Subquery woes)

[Rhino]

With a name like Rene Fournier, shouldn't the subject line be Je don't 
think so? ;-)


Okay, the first thing we need to know to help you is what version of MySQL 
you are using. If I am not mistaken, subqueries aren't supported until 
Version 4.1; if you are using 4.0 or earlier, your subquery won't work no 
matter how you change it.


If you are using Version 4.1 or later, I don't see any real problem with 
your subquery. However, since the subquery is getting a max(), you know that 
it can only possibly return one value so you don't need to introduce it with 
'in', an '=' will suffice, although both should work. However, it's possible 
that MySQL is a bit flakey in this regard so try it with =, i.e.


SELECT history.* FROM history
WHERE history.id =
(SELECT MAX(id) FROM history
WHERE account_id =  216
GROUP BY asset_id)

The 'history.*' shouldn't affect the ability of the query to run in any 
case; that expression is just shorthand for give me all of the columns in 
the History table.


I can't try any of this in MySQL myself because I am still running MySQL 
4.0.15. But your query should be fine as long as you have MySQL 4.1 or 
later. It would certainly work in DB2, my main database.


--
Rhino

- Original Message - 
From: René Fournier [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, February 04, 2006 5:38 PM
Subject: MySQL says, Ich don't think so (Subquery woes)



SELECT MAX(id) FROM history
WHERE account_id = 216 GROUP BY asset_id

...works. Returns, e.g.: 1234, 3456, 5483, 8382.


SELECT history.* FROM history
WHERE history.id IN (1234, 3456, 5483, 8382 )

...works too. But if I try to combine them using a subquery, a la...


SELECT history.* FROM history
WHERE history.id IN ( SELECT MAX(id) FROM history WHERE account_id =  216 
GROUP BY asset_id )


...it pretty much hangs MySQL. CPU goes to 100%, ten minutes later, I 
have to kill the connection. I can't figure out why. Any ideas? Any 
suggestions?


...Rene

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 03/02/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 03/02/2006


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

Re: Help Understanding Document Syntax

[Rhino]

First and foremost, thank you very much Michael for correcting my mistakes; 
I _was_ a bit sloppy in my reading of the syntax for the statements and that 
caused some unnecessary errors in my reply to Scott.


However, your corrections are not _quite_ right even now. See below where I 
explain this.


--
Rhino

- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: Scott Purcell [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Tuesday, January 31, 2006 1:18 AM
Subject: Re: Help Understanding Document Syntax



Rhino wrote:


The 'symbol' you are referring to, in the foreign key clause of the 
CREATE TABLE statement, is simply an opportunity for you to choose a name 
for the foreign key of the table; if you don't choose a name, MySQL will 
generate a default name for you.


Therefore, if you do this:

   CREATE TABLE Foo
   ...
   constraint (bar) foreign key(workdept) references Sample.department on 
delete cascade

   ...


That's not quite right.  There should be no parentheses around the symbol, 
but you do need parentheses around the referenced column. The syntax is


[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) [reference_definition]

reference_definition:
REFERENCES tbl_name [(index_col_name,...)]

so you should have

  CONSTRAINT bar FOREIGN KEY (workdept) REFERENCES Sample (department)
  ON DELETE CASCADE

snip
I _think_ you are saying that you want the combination of values in two 
of the columns of your table to be unique so that no two rows of the same 
table can have that same combination of values in those two columns. I 
know how to do this in DB2, my main database, so I looked up the syntax 
to do the same thing in MySQL and came up with this small example:


=
use tmp;

create table Purcell01
(empno smallint not null,
fname char(10) not null,
lname char(10) not null,
primary key(empno)
constraint(uk) unique index ukix btree (fname, lname)) Type=INNODB;


For the record, unique constraints don't require InnoDB.

Thanks for mentioning that. I didn't know one way or the other whether 
unique keys required INNODB; I know that _foreign_ keys are only supported 
in INNODB so I pretty much always use INNODB tables for everything I do in 
MySQL. It's useful to know that INNODB is not necessary to support unique 
keys.



snip
Unfortunately, I get a syntax error when I try this in my copy of MySQL, 
which is only 4.0.15. I'm guessing that the UNIQUE clause isn't 
recognized in MySQL 4.0.15 and that the statement will work in 5.1.x but 
there may be some problem with my syntax. I can't find an explicit 
example of a multicolumn unique constraint in the manual so maybe someone 
else reading this thread can identify any errors in the syntax if this 
doesn't work for you.


UNIQUE constraints have been in mysql a long time (at least since 3.23, I 
believe).  You have parentheses in the wrong place again. The syntax is


  [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)

so the correct definition would be

  CONSTRAINT uk UNIQUE INDEX ukix (fname, lname)

or simply

  UNIQUE ukix (fname, lname)


Strangely enough, both of those formulations of the UNIQUE clause fail for 
me with the same error as the mistaken version I first proposed in my note 
to Scott.


This is the current version of my DROP/CREATE:

drop table if exists Purcell01;
create table if not exists Purcell01
(empno smallint not null,
fname char(10) not null,
lname char(10) not null,
primary key(empno)
-- constraint uk unique index ukix (fname, lname)
-- unique ukix (fname, lname)
) Type=INNODB;

If I run it exactly as shown, with both versions of the UNIQUE clause 
commented, it works fine. But if I uncomment either version of the UNIQUE 
clause, it fails with the same error I mentioned in my previous note. I've 
also tried 'unique(fname, lname)' and that also fails on the same error.


Any idea why every formulation of the UNIQUE clause I try fails? If UNIQUE 
has been supported since Version 3.x, then I'm out of ideas


The other thing you wanted was for a bad row, like the last row in my 
Inserts, to simply be ignored if it violates the unique constraint. In 
DB2, that isn't an option: the insert simply fails due to the violation 
of the uniqueness. However, it _appears_ that MySQL has a different 
policy. Apparently, you can add an IGNORE clause to an INSERT or UPDATE 
statement to make it ignore a uniqueness violation. As I read the article 
on the INSERT statement, you would want an INSERT to look like this if 
you wanted a row that violated uniqueness to be ignored:


   INSERT IGNORE INTO PURCELL01 VALUES(5, 'Fred', 'Flintstone');

The UPDATE statement appears to be the same idea;

   UPDATE IGNORE
   set fname = 'Fred', lname = 'Flintstone'
   where empno = 4;


To be clear, attempting to insert a row which violates a unique 
constraint

Re: Help Understanding Document Syntax

[Rhino]

The 'symbol' you are referring to, in the foreign key clause of the CREATE 
TABLE statement, is simply an opportunity for you to choose a name for the 
foreign key of the table; if you don't choose a name, MySQL will generate a 
default name for you.


Therefore, if you do this:

   CREATE TABLE Foo
   ...
   constraint (bar) foreign key(workdept) references Sample.department on 
delete cascade

   ...


the foreign key you defined on the column workdept has the name 'bar'.


If you defined the table this way:

   CREATE TABLE Foo
   ...
   constraint foreign key(workdept) references Sample.department on delete 
cascade

   ...

the name of the foreign key would be generated by MySQL.

If memory serves, the foreign key name can be used to drop the foreign key 
in an ALTER TABLE statement and perhaps a few other places. The name of the 
foreign key does not help you with what you appear to want to do.


I _think_ you are saying that you want the combination of values in two of 
the columns of your table to be unique so that no two rows of the same table 
can have that same combination of values in those two columns. I know how to 
do this in DB2, my main database, so I looked up the syntax to do the same 
thing in MySQL and came up with this small example:


=
use tmp;

create table Purcell01
(empno smallint not null,
fname char(10) not null,
lname char(10) not null,
primary key(empno)
constraint(uk) unique index ukix btree (fname, lname)) Type=INNODB;

insert into Purcell01 values
(1, 'Fred', 'Flintstone'),
(2, 'Barney', 'Rubble'),
(3, 'Fred', 'Slate'),
(4, 'Wilma', 'Flintstone'),
(5, 'Fred', 'Flintstone');

select * from Purcell01;

=

If I'm reading the manual correctly, this should force the _COMBINATION_ of 
fname and lname to be different in each row of the table. Then, when you do 
the inserts, all but the last one should work. It's perfectly okay for other 
rows to have Flintstone in the lname column and it's perfectly okay for 
other rows to have Fred in the fname column but only one row in the column 
can have the COMBINATION of 'Fred' 'Flintstone' in the lname and fname 
columns. I _think_ that is what you want to do.


Unfortunately, I get a syntax error when I try this in my copy of MySQL, 
which is only 4.0.15. I'm guessing that the UNIQUE clause isn't recognized 
in MySQL 4.0.15 and that the statement will work in 5.1.x but there may be 
some problem with my syntax. I can't find an explicit example of a 
multicolumn unique constraint in the manual so maybe someone else reading 
this thread can identify any errors in the syntax if this doesn't work for 
you.


The other thing you wanted was for a bad row, like the last row in my 
Inserts, to simply be ignored if it violates the unique constraint. In DB2, 
that isn't an option: the insert simply fails due to the violation of the 
uniqueness. However, it _appears_ that MySQL has a different policy. 
Apparently, you can add an IGNORE clause to an INSERT or UPDATE statement 
to make it ignore a uniqueness violation. As I read the article on the 
INSERT statement, you would want an INSERT to look like this if you wanted a 
row that violated uniqueness to be ignored:


   INSERT IGNORE INTO PURCELL01 VALUES(5, 'Fred', 'Flintstone');

The UPDATE statement appears to be the same idea;

   UPDATE IGNORE
   set fname = 'Fred', lname = 'Flintstone'
   where empno = 4;

---
Rhino

- Original Message - 
From: Scott Purcell [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Monday, January 30, 2006 9:08 AM
Subject: Help Understanding Document Syntax


Hello,

I have created some tables a while back, and of course, and I am
learning, I have found problems with duplicate entries and other
problems.

So upon a fresh read of the 5.1 docs, I am trying to understand the word
symbol after the constraint.

I would like to be able to somehow combine two columns, and make them
unique? Or distinct?. I do not want the same two columns to ever occur
again. If someone tries to insert, just ignore and continue. So I will
use a MyISAM table type. But in order to understand how this is done,
could use an understanding of the symbol behind constraint.

create_definition:
   column_definition
 | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
 | KEY [index_name] [index_type] (index_col_name,...)
 | INDEX [index_name] [index_type] (index_col_name,...)
 | [CONSTRAINT [symbol]] UNIQUE [INDEX]
   [index_name] [index_type] (index_col_name,...)
 | FULLTEXT [INDEX] [index_name] (index_col_name,...)
 [WITH PARSER parser_name]
 | SPATIAL [INDEX] [index_name] (index_col_name,...)
 | [CONSTRAINT [symbol]] FOREIGN KEY
   [index_name] (index_col_name,...) [reference_definition]
 | CHECK (expr)









No virus found in this incoming message

Re: How to restrict this query... (need subquery?)

[Rhino]

Rene,

The count(*) function should always report the exact number of rows that 
satisfy the query. If the query has only a WHERE clause, count(*) should 
report the number of rows that satisfied the WHERE. If the query has a only 
a GROUP BY, count(*) should report the number of groups found by the query. 
If the query has WHERE _and_ GROUP BY, count(*) should report the number of 
groups that were found after the WHERE clause had been applied to the data 
in the table.


Would that help you?

Rhino

- Original Message - 
From: René Fournier [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Monday, January 30, 2006 5:01 PM
Subject: How to restrict this query... (need subquery?)


Hello,

I have two tables: Accounts and History. Basically, I want to see how
much activity each account has during a given period of time. Even if
an account has no activity, I still want to see it in the result
(naturally with zeros or null). In the history table, there is a
column called time_sec—it's a UNIX timestamp. That is the column
needed to restrict the counting to a particular day or month. My
problem is that either I get all the accounts (good) without
restricting to a day or month (bad)...

SELECT
accounts.id,
accounts.account_name,
accounts.company_name,
history.msg_src,
COUNT(history.msg_src) as msg_num
FROM accounts
LEFT JOIN history ON history.account_id = accounts.id
GROUP BY accounts.id, msg_src
ORDER BY accounts.id DESC, history.msg_src ASC

... or I get a result that is restricted (good), but without showing
all the accounts (bad)...

SELECT
accounts.id,
accounts.account_name,
accounts.company_name,
history.msg_src,
COUNT(history.msg_src) as msg_num
FROM accounts
LEFT JOIN history ON history.account_id = accounts.id
WHERE
history.time_sec  1138604400 AND history.time_sec  1138652381
GROUP BY accounts.id, msg_src
ORDER BY accounts.id DESC, history.msg_src ASC

What I need to do, somehow, is apply that WHERE clause to the COUNT
part of the SELECT. Any ideas?

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 27/01/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 27/01/2006


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

Re: UK Postcodes

[Rhino]

You'd think that the people who run the post office in the UK - British 
Telecom?? - would have had a number of enquiries from people who wanted to 
match postal codes with latitude and longitude. That would tend to give them 
a natural incentive to provide such information, all nicely integrated, 
possibly for a fairly affordable price.


Any idea what would prevent the post office from doing that?

Rhino

- Original Message - 
From: sheeri kritzer [EMAIL PROTECTED]

To: [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Tuesday, January 24, 2006 3:31 PM
Subject: Re: UK Postcodes


Hi Mike,

Sorry for the late reply.  The company I work for has this very same
problem -- we are a multi-national personal ad site, where members can
search for other members close to them.

The answer is, unfortunately, you have to acquire one database with
postcodes, and another with longitudes and latitudes, and merge them
together.  We spent a lot of time finding that answer, and when we
did, it wasn't cheap.

Sorry for the bad news.

-Sheeri Kritzer

On 1/7/06, Mike Blezien [EMAIL PROTECTED] wrote:

Hello,

we are working with a database that stores UK postcodes, which are 
different
then US zipcodes. I've found alot of information for working with 
zipcodes,
locating closed distances within a zipcode range, but haven't found 
anything

regarding working with UK type postcodes.

Was hoping someone on the list may have worked with UK postcodes and may 
have
some info on the best way to query these postcodes for locating closed 
location,

distances,.etc?

thx's

--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.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]


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 23/01/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 23/01/2006


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

Re: count(*) send a wrong value

[Rhino]

What you're describing definitely sounds like a bug to me, assuming that you 
are accurately reporting the query you've used and the data in your table.


In other words, if there really are 10 rows that have a cid value of 123 and 
you really are doing select * from table where cid = 123, then you should 
definitely be getting a result of 10, not 2.


But that is a VERY strange error to be having! I've been writing SQL for 
over 20 years on a variety of platforms and I can't remember EVER seeing a 
count(*) give the wrong result. Any time the result was not what I expected, 
it turned out that I'd written the query incorrectly or I was wrong about 
what data was in the table. I'd also expect that the MySQL testing team 
would have executed many tests to be sure that basic functionality like 
count(*) works before ever releasing the product.


Please, retest everything VERY carefully once more and make VERY sure that 
you aren't inadvertently writing the query incorrectly and that you really 
DO have 10 rows with cid = 123. If you still get 2 as the result of your 
query, I would recommend sending a bug report to MySQL.


Rhino

- Original Message - 
From: fabsk [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Monday, January 23, 2006 5:32 PM
Subject: Re: count(*) send a wrong value



Thank you for you answer, but I read many times and I did not found
something to answer my question (well, I did not know about the NULL).

In my case:
- there is one table
- htere is no distinct
- there is a WHERE clause, so there is no optimisation
- there is no other field and no group by

If I do count(cid), I still get 2.

Fabien

Le lundi 23 janvier 2006 à 20:54 +, [EMAIL PROTECTED] a écrit :

From the MySQL 4.1 manual

12.10.1. GROUP BY (Aggregate) Functions

COUNT(expr)

Returns a count of the number of non-NULL values in the rows
retrieved by a SELECT statement.


COUNT() returns 0 if there were no matching rows.

mysql SELECT student.student_name,COUNT(*)
-FROM student,course
-WHERE student.student_id=course.student_id
-GROUP BY student_name;


COUNT(*) is somewhat different in that it returns a count
of the number of rows retrieved, whether or not they contain
NULL values.


COUNT(*) is optimized to return very quickly if the SELECT
retrieves from one table, no other columns are retrieved,
and there is no WHERE clause. For example:

mysql SELECT COUNT(*) FROM student;


 This optimization applies only to MyISAM and ISAM tables
only, because an exact record count is stored for these
table types and can be accessed very quickly. For
transactional storage engines (InnoDB, BDB), storing an
exact row count is more problematic because multiple
transactions may be occurring, each of which may affect the
count.


COUNT(DISTINCT expr,[expr...])


Returns a count of the number of different non-NULL values.


COUNT(DISTINCT) returns 0 if there were no matching rows.

mysql SELECT COUNT(DISTINCT results) FROM student;


In MySQL, you can get the number of distinct expression
combinations that do not contain NULL by giving a list of
expressions. In standard SQL, you would have to do a
concatenation of all expressions inside COUNT(DISTINCT ...).

COUNT(DISTINCT ...) was added in MySQL 3.23.2.

Keith

In theory, theory and practice are the same;
In practice they are not.

On Mon, 23 Jan 2006, fabsk wrote:

 To: mysql@lists.mysql.com
 From: fabsk [EMAIL PROTECTED]
 Subject: count(*) send a wrong value

 Hi,

 I'm facing a strange problem. I am using a database at my Internet
 provider (Free, France). The type of table is MyISAM (no choice),

MySQL

 4.1.15. I can do my tests with my PHP code or phpMyAdmin.

 The definition of my table is:
 - uid, int
 - cid, int
 - response, text
 - points, int (can be null)

 keys:
 - uid, cid
 - cid, response(4)
 - cid

 When I do select * from my_table where cid=123, I get my 10

records.

 But when I do select count(*) from my_table where cid=123 I get

2. I

 also happens with many other values of cid and the bad result is
 always 2.

 I can't understand what's happen. It seems to simple, but there

should

 be something. Do you have an idea?

 Thank you for your attention
 Fabien


 -- 
 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]


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.21/236 - Release Date: 
20/01/2006







--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.21/236 - Release Date: 20/01/2006


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

Fw: Error from mysqldump - problem solved

[Rhino]

I've resolved my problem with mysqldump. I read the error message yet again 
and it suddenly dawned on me what the problem might be. I made a slight 
modification in my table name and, sure enough, the problem went away.


The problem lay in the fact that my table name was References. That's right, 
the same word that is a keyword in the Foreign Key clause, as in Foreign 
key (id) references tmp.foo(id) on delete restrict. Using References as a 
table name had initially caused me grief when creating the table and when 
defining foreign keys that used it as a primary table. I'd eventually gotten 
past all those problems by putting backtics around each use of References 
as a table name. It didn't occur to me until I finally reread the error 
message this morning that the table name was biting me again within the 
mysqldump command.


All I did was drop the References table then recreate the tables so that the 
former References was now called Reference and everything worked 
perfectly again. I can't believe I didn't see this right from the start. Oh 
well, live and learn


I just thought I should follow up so that anyone following this thread now 
or in the archives will know how it was resolved.


Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]; gerald_clark 
[EMAIL PROTECTED]

Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 11:15 AM
Subject: Re: Error from mysqldump


Just as a followup to my own remarks, I've tried running my backup script 
with the new syntax that Gerald suggested. I was going to wait for the 
normal daily backup but I was eager to see if the new version would work 
better so I just ran it from the command line.


Unfortunately, it came back with the same error. The new syntax is still 
cleaner and I'm going to keep it but I'm back to square one in determining 
why the mysqldump of this one database is giving me trouble.


Does anyone have any ideas?

Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: gerald_clark [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 10:53 AM
Subject: Re: Error from mysqldump




- Original Message - 
From: gerald_clark [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 9:30 AM
Subject: Re: Error from mysqldump



Rhino wrote:

I have an automated backup script that has been running daily for a 
couple of years now. It has never given me trouble until the last two 
days. For the last two days, I have been getting this message when 
backing up my newest database:


/usr/bin/mysqldump: Got error: 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 'References READ /*!32311 LOCAL */' at 
line 1 when using LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u 
$USERID -p$PASSWORD`

do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r 
${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


I would look here. This is a dangerous expansion. A space or ';' in any 
of these variables my generate unwanted commands.

Use quotes around the argument to -r.
Try.
 /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


or /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME}   ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql




Okay, fair enough, I've never claimed to be a bash expert ;-) I think 
your proposed change is an improvement: it is clearer and easier to read. 
I'll give this version a try for the next few days and see if it works 
any better.


But I'm still not sure why this version might solve my problem. Wouldn't 
an expansion issue cause problems for all of my databases, not just one? 
I'm trying to understand why only one database is affected and why only 
the newest one when the script has worked fine for many months with the 
older databases.



  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display 
old backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' 
';' #delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database 
when the exact same logic is applied for each of my databases and works 
fine for all the others.


I've tried doing the backup manually from the command line and found 
that I got the same error when I tried to backup the Maximal database 
that way; a manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is 
something internally

Re: question about CONTAINS SQL

[Rhino]

- Original Message - 
From: Paul DuBois [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]; wangxu [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Friday, January 20, 2006 2:20 AM
Subject: Re: question about CONTAINS SQL



At 8:29 -0500 1/19/06, Rhino wrote:
I am copying the rest of the list with this so that everyone may benefit 
from the discussion.


If your routine modifies data, in other words if it does SQL Update, 
Insert or Delete but your routine definition says only CONTAINS SQL, I 
would expect your routine to fail at runtime. I can't say this with 
certaintly because I don't have one of the newer versions of MySQL that 
supports these routines but I'm pretty sure that you will have a runtime 
failure. After all, CONTAINS SQL implies only that you are creating 
objects like tables within your routine; CONTAINS SQL does not permit the 
execution of Insert, Update, or Delete. Therefore, I expect that you will 
get a runtime error as soon as you do your first Insert, Update, or 
Delete. If you want to avoid the error, use MODIFIES SQL instead of 
CONTAINS SQL.


Of course the best way to be sure is to try this for yourself. Try the 
routine with CONTAINS SQL and see what happens at runtime. If it fails, as 
I strongly expect, change CONTAINS SQL to MODIFIES SQL DATA and your error 
will almost certainly go away.


No, these characteristics are merely advisory.  The server doesn't
impose any restraints based on them.


Oh? Really?

I use DB2 a lot more than I use MySQL - and I'm not currently using a 
version of MySQL that supports stored procedures and user-defined 
functions - so I assumed that the MODIFIES/READS/CONTAINS clauses actually 
_do_ something in MySQL. Since MySQL and DB2 both try to follow roughly the 
same SQL standards that seemed like a reasonable assumption.


Obviously, I was wrong and bow to your superior knowledge of what MySQL 
actually does with these clauses.


If the MODIFIES/READS/CONTAINS clauses are just checked for spelling and 
otherwise ignored, i.e. if they are not enforced, then why does Wangxu's 
procedure not work? Again, my version of MySQL doesn't support procedures so 
I can't try it for myself.


Rhino


- Original Message - From: wangxu [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]
Sent: Thursday, January 19, 2006 2:54 AM
Subject: Re: question about CONTAINS SQL

  If I create a routine with modification operation and not spectify 
characteristic in CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL 
DATA.
  In the maunal,the default value is CONTAINS SQL if i haven't 
spectify a values.
  If it's meaning that the routine with CONTAINS SQL  include 
modification operation?

  Should many problem happen?


- Original Message - From: Rhino [EMAIL PROTECTED]
To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 12:21 AM
Subject:Re: question about CONTAINS SQL

If you are writing something that does INSERT, UPDATE, or DELETE, you 
need

to use the MODIFIES SQL DATA option.

Rhino

- Original Message - From: wangxu [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, January 18, 2006 3:05 AM
Subject: Re: question about CONTAINS SQL






 But what is  INSERT OR UPDATE need?


 - Original Message -  From: Rhino [EMAIL PROTECTED]
 To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com
 Sent: Tuesday, January 17, 2006 9:49 PM
 Re: question about CONTAINS SQL


  - Original Message -   From: wangxu 
[EMAIL PROTECTED]

  To: mysql@lists.mysql.com
  Sent: Tuesday, January 17, 2006 12:35 AM
  Subject: question about CONTAINS SQL
 
 
  I notice there are one section in the manual:
  
   CONTAINS SQL indicates that the routine does not contain
statements

   that
   read or write data.
  
   And that the option is default.
  
   It's true?
  
   If i wouldn't do read or write in routine.What can i do yet?
  
 
  Commands like GRANT or REVOKE or CREATE TABLE don't read or write 
   data
  within tables but they involve SQL so commands like this need   
CONTAINS

  SQL,
  rather than the NO SQL, READS SQL DATA or MODIFIES SQL DATA 
options.

 
  Rhino
 
 
 
 
  --   No virus found in this outgoing message.
  Checked by AVG Free Edition.
  Version: 7.1.375 / Virus Database: 267.14.19/231 - Release Date:
  16/01/2006
 
 
  --   MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/[EMAIL PROTECTED]
 
 






No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 
18/01/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 
18/01/2006



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

Re: Database design help

[Rhino]

Ian,

If I'm not mistaken, you started this conversation yesterday. I've been 
watching the back-and-forth haphazardly and not really absorbing the full 
details so forgive me if someone has already asked this and you've answered 
it.


My concern, in hearing you state your problem, is that some of the stuff you 
want to track just doesn't seem that important or, to put it another way, 
they just don't seem like the kinds of things that a business will really 
care that much about.


For instance, this note mentions that the size or colour of a box has 
changed and you want to track that. Frankly, I'm having trouble believing 
that your management really _needs_ to track that kind of micro-change. Why 
would they care? Surely their major concerns must be things like sales of 
goods, profits, and inventories. What difference does the colour of the box 
make? Do you sell more widgets when they are in blue boxes than when they 
are in green boxes? Now, at some level, the packaging probably _does_ 
matter; I'm sure packaging experts will be able to trot out stories about 
how sales of widgets increased 14% when the box was changed in such-and-such 
a way. But do _you_ or your company really care about this enough to track 
the details about the packaging for every single item you stock? Or are you 
doing a detailed study to try to prove that the packaging really does make a 
difference of so many percent in sales? Otherwise, I'm at a loss to 
understand why you'd track that much detail.


I caught glimspses of other requirements in the other notes that had 
comparable requirements; some of them struck me as things that were just not 
typically tracked in computer systems.


I'm not saying you couldn't make a case for any of these requirements; maybe 
they are all essential for your project. But is it possible that you've 
taken a wouldn't it be nice if we could track XXX? remark that someone 
made and turned it into a do-or-die requirement? Is is possible that some of 
these requirements just aren't that important and could be omitted with no 
important loss of functionality?


If you give this due consideration, you may find that a lot of your problem 
evaporates and the rest gets simpler to handle.


Just a general observation made by a disinterested third party; ignore it if 
you like :-)


Rhino

- Original Message - 
From: Ian Klassen [EMAIL PROTECTED]

To: Marco Neves [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, January 20, 2006 3:49 PM
Subject: Re: Database design help


Marco,

Thanks for your help.  I created this example to try to simplify my real
world problem.  Clearly I didn't provide enough detail.  Keeping with my
example, essentially I'm looking at product details that change over
time.  Let's say I'm keeping track of boxes.  Over time, the color or size
of the box might change.  At any particular time I want to take a snapshot
for a box and see what color and size it is.

I could have a box table that holds data that doesn't change and another
that contains the changing data such as:

box_id | name
1 | Big Box

box_id | date | color | size
1 | 2006-01-01 | blue | 20  // start off with blue boxes that
are 20 in size
1 | 2006-02-01 | red | NULL // boxes are now red but same size
1 | 2006-03-01 | NULL | 30  // boxes are still red but are now
30 in size

Or I could break off each field that changes into it's own table.  Any
recommendations?

Thanks again.

Ian

At 12:35 AM 1/19/2006 +, Marco Neves wrote:

Ian,

I'ld like to help you, but a more specific db design would depend 
on more

specific description on your application needs.

What I can say is that you need to adapt your database to your 
reality.


What I got til now is that you need a product table, where you can 
store your

basic information on products.

You say you have other information, but I could understand several 
things.


1- That other information is related to the product, to 
the transaction, to

both, to stocks?

for example, color or size is relevant to determine stocks 
and is related to

the product, and so is relevant to the transactions also.

The sale rep is relevant to transaction, but not to the 
product.


sales rep comission is relevante to the sales rep, but not 
to the

transaction nor the product.

My point is, a database design can be a complex task, and the 
hability an
application will have to provide solutions to the real world depends, 
before

anyother thing in that database design.

The is the point where almost all analisys most be done, and 
almost no

programming (i think).

mpneves

On Wednesday 18 January 2006 22:55, you wrote:
 Thanks Ed.  That's another good idea.  The consensus I'm getting is to
 create one table that stores unchanging data about the product and 
 another

 that stores transaction details.  The
 problem I'm

Re: question about CONTAINS SQL

[Rhino]

I am copying the rest of the list with this so that everyone may benefit 
from the discussion.


If your routine modifies data, in other words if it does SQL Update, Insert 
or Delete but your routine definition says only CONTAINS SQL, I would 
expect your routine to fail at runtime. I can't say this with certaintly 
because I don't have one of the newer versions of MySQL that supports these 
routines but I'm pretty sure that you will have a runtime failure. After 
all, CONTAINS SQL implies only that you are creating objects like tables 
within your routine; CONTAINS SQL does not permit the execution of Insert, 
Update, or Delete. Therefore, I expect that you will get a runtime error as 
soon as you do your first Insert, Update, or Delete. If you want to avoid 
the error, use MODIFIES SQL instead of CONTAINS SQL.


Of course the best way to be sure is to try this for yourself. Try the 
routine with CONTAINS SQL and see what happens at runtime. If it fails, as I 
strongly expect, change CONTAINS SQL to MODIFIES SQL DATA and your error 
will almost certainly go away.


Rhino

- Original Message - 
From: wangxu [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Sent: Thursday, January 19, 2006 2:54 AM
Subject: Re: question about CONTAINS SQL


  If I create a routine with modification operation and not spectify 
characteristic in CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL 
DATA.
  In the maunal,the default value is CONTAINS SQL if i haven't spectify 
a values.
  If it's meaning that the routine with CONTAINS SQL  include 
modification operation?

  Should many problem happen?


- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 12:21 AM
Subject:Re: question about CONTAINS SQL


If you are writing something that does INSERT, UPDATE, or DELETE, you 
need

to use the MODIFIES SQL DATA option.

Rhino

- Original Message - 
From: wangxu [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, January 18, 2006 3:05 AM
Subject: Re: question about CONTAINS SQL





 But what is  INSERT OR UPDATE need?

 - Original Message - 
 From: Rhino [EMAIL PROTECTED]

 To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com
 Sent: Tuesday, January 17, 2006 9:49 PM
 Re: question about CONTAINS SQL


  - Original Message - 
  From: wangxu [EMAIL PROTECTED]

  To: mysql@lists.mysql.com
  Sent: Tuesday, January 17, 2006 12:35 AM
  Subject: question about CONTAINS SQL
 
 
  I notice there are one section in the manual:
  
   CONTAINS SQL indicates that the routine does not contain 
   statements

   that
   read or write data.
  
   And that the option is default.
  
   It's true?
  
   If i wouldn't do read or write in routine.What can i do yet?
  
 
  Commands like GRANT or REVOKE or CREATE TABLE don't read or write 
  data
  within tables but they involve SQL so commands like this need 
  CONTAINS

  SQL,
  rather than the NO SQL, READS SQL DATA or MODIFIES SQL DATA options.
 
  Rhino
 
 
 
 
  -- 
  No virus found in this outgoing message.

  Checked by AVG Free Edition.
  Version: 7.1.375 / Virus Database: 267.14.19/231 - Release Date:
  16/01/2006
 
 
  -- 
  MySQL General Mailing List

  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/[EMAIL PROTECTED]
 
 





No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 
18/01/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 
18/01/2006



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








No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006


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

Error from mysqldump

[Rhino]

I have an automated backup script that has been running daily for a couple 
of years now. It has never given me trouble until the last two days. For the 
last two days, I have been getting this message when backing up my newest 
database:


/usr/bin/mysqldump: Got error: 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 'References READ /*!32311 LOCAL */' at line 1 when using 
LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql

  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old 
backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';' 
#delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database when 
the exact same logic is applied for each of my databases and works fine for 
all the others.


I've tried doing the backup manually from the command line and found that I 
got the same error when I tried to backup the Maximal database that way; a 
manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is something 
internally wrong with my database but I'm darned if I know what the problem 
could be. When I do 'select *' against each of the five small tables in this 
database, each returns exactly the right data and there are no errors or 
warnings of any kind.


Can anyone suggest queries or commands that would reveal the status of my 
database and its tables to make sure something is not messed up?


Any suggestions on resolving this problem would be greatly appreciated.

---
Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006


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

Re: Error from mysqldump

[Rhino]

- Original Message - 
From: gerald_clark [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 9:30 AM
Subject: Re: Error from mysqldump



Rhino wrote:

I have an automated backup script that has been running daily for a 
couple of years now. It has never given me trouble until the last two 
days. For the last two days, I have been getting this message when 
backing up my newest database:


/usr/bin/mysqldump: Got error: 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 'References READ /*!32311 LOCAL */' at 
line 1 when using LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r 
${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


I would look here. This is a dangerous expansion. A space or ';' in any of 
these variables my generate unwanted commands.

Use quotes around the argument to -r.
Try.
 /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


or /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME}   ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql




Okay, fair enough, I've never claimed to be a bash expert ;-) I think your 
proposed change is an improvement: it is clearer and easier to read. I'll 
give this version a try for the next few days and see if it works any 
better.


But I'm still not sure why this version might solve my problem. Wouldn't an 
expansion issue cause problems for all of my databases, not just one? I'm 
trying to understand why only one database is affected and why only the 
newest one when the script has worked fine for many months with the older 
databases.



  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display 
old backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';' 
#delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database 
when the exact same logic is applied for each of my databases and works 
fine for all the others.


I've tried doing the backup manually from the command line and found that 
I got the same error when I tried to backup the Maximal database that 
way; a manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is 
something internally wrong with my database but I'm darned if I know what 
the problem could be. When I do 'select *' against each of the five small 
tables in this database, each returns exactly the right data and there 
are no errors or warnings of any kind.


Can anyone suggest queries or commands that would reveal the status of my 
database and its tables to make sure something is not messed up?


Any suggestions on resolving this problem would be greatly appreciated.

---
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006


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

Re: Error from mysqldump

[Rhino]

Just as a followup to my own remarks, I've tried running my backup script 
with the new syntax that Gerald suggested. I was going to wait for the 
normal daily backup but I was eager to see if the new version would work 
better so I just ran it from the command line.


Unfortunately, it came back with the same error. The new syntax is still 
cleaner and I'm going to keep it but I'm back to square one in determining 
why the mysqldump of this one database is giving me trouble.


Does anyone have any ideas?

Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: gerald_clark [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 10:53 AM
Subject: Re: Error from mysqldump




- Original Message - 
From: gerald_clark [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 9:30 AM
Subject: Re: Error from mysqldump



Rhino wrote:

I have an automated backup script that has been running daily for a 
couple of years now. It has never given me trouble until the last two 
days. For the last two days, I have been getting this message when 
backing up my newest database:


/usr/bin/mysqldump: Got error: 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 'References READ /*!32311 LOCAL */' at 
line 1 when using LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u 
$USERID -p$PASSWORD`

do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r 
${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


I would look here. This is a dangerous expansion. A space or ';' in any 
of these variables my generate unwanted commands.

Use quotes around the argument to -r.
Try.
 /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


or /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME}   ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql




Okay, fair enough, I've never claimed to be a bash expert ;-) I think your 
proposed change is an improvement: it is clearer and easier to read. I'll 
give this version a try for the next few days and see if it works any 
better.


But I'm still not sure why this version might solve my problem. Wouldn't 
an expansion issue cause problems for all of my databases, not just one? 
I'm trying to understand why only one database is affected and why only 
the newest one when the script has worked fine for many months with the 
older databases.



  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display 
old backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';' 
#delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database 
when the exact same logic is applied for each of my databases and works 
fine for all the others.


I've tried doing the backup manually from the command line and found 
that I got the same error when I tried to backup the Maximal database 
that way; a manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is 
something internally wrong with my database but I'm darned if I know 
what the problem could be. When I do 'select *' against each of the five 
small tables in this database, each returns exactly the right data and 
there are no errors or warnings of any kind.


Can anyone suggest queries or commands that would reveal the status of 
my database and its tables to make sure something is not messed up?


Any suggestions on resolving this problem would be greatly appreciated.

---

Rhino


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 
18/01/2006



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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 
18/01/2006







--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006


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

Re: question about CONTAINS SQL

[Rhino]

If you are writing something that does INSERT, UPDATE, or DELETE, you need 
to use the MODIFIES SQL DATA option.


Rhino

- Original Message - 
From: wangxu [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, January 18, 2006 3:05 AM
Subject: Re: question about CONTAINS SQL






But what is  INSERT OR UPDATE need?

- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Tuesday, January 17, 2006 9:49 PM
Re: question about CONTAINS SQL


 - Original Message - 
 From: wangxu [EMAIL PROTECTED]

 To: mysql@lists.mysql.com
 Sent: Tuesday, January 17, 2006 12:35 AM
 Subject: question about CONTAINS SQL


 I notice there are one section in the manual:
 
  CONTAINS SQL indicates that the routine does not contain statements 
  that

  read or write data.
 
  And that the option is default.
 
  It's true?
 
  If i wouldn't do read or write in routine.What can i do yet?
 

 Commands like GRANT or REVOKE or CREATE TABLE don't read or write data
 within tables but they involve SQL so commands like this need CONTAINS 
 SQL,

 rather than the NO SQL, READS SQL DATA or MODIFIES SQL DATA options.

 Rhino




 -- 
 No virus found in this outgoing message.

 Checked by AVG Free Edition.
 Version: 7.1.375 / Virus Database: 267.14.19/231 - Release Date: 
 16/01/2006



 -- 
 MySQL General Mailing List

 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: 
 http://lists.mysql.com/[EMAIL PROTECTED]









No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006


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

Re: Help in joining three tables

[Rhino]

- Original Message - 
From: Imran [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Wednesday, January 18, 2006 3:13 PM
Subject: Help in joining three tables



Hello All:

I need to join three tables but I am not sure how to structure the query.
I need to join table1 to table2 and then join table3 to this result set.

So like (table1 join table2) join table3.

Table1 and Table2 will be joined on ProdNo,CustNo and Branch. Table3 will 
be

joined to the result set by CustNo and Branch.

There are many types of joins: inner joins, left joins, right joins, cross 
joins, etc. I don't know which types you want to do so I'll just assume 
inner joins and let you modify my answer to suit your basic needs.


Select t1.col1, t1.col3, t3.col5, t2.col9
from table1 t1 join table t2 on t1.col1 = t2.col4
join table3 t3 on t3.col5 = t1.col8
where t2.country = 'USA'
and t3.planet = 'EARTH'
order by t1.col1, t2.col2;

Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006


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

Re: Help in joining three tables

[Rhino]

- Original Message - 
From: [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: Imran [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, January 18, 2006 5:07 PM
Subject: Re: Help in joining three tables



Rhino [EMAIL PROTECTED] wrote on 01/18/2006 03:30:44 PM:



- Original Message - 
From: Imran [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Wednesday, January 18, 2006 3:13 PM
Subject: Help in joining three tables


 Hello All:

 I need to join three tables but I am not sure how to structure the

query.

 I need to join table1 to table2 and then join table3 to this result

set.


 So like (table1 join table2) join table3.

 Table1 and Table2 will be joined on ProdNo,CustNo and Branch. Table3

will

 be
 joined to the result set by CustNo and Branch.

There are many types of joins: inner joins, left joins, right joins,

cross

joins, etc. I don't know which types you want to do so I'll just assume
inner joins and let you modify my answer to suit your basic needs.

Select t1.col1, t1.col3, t3.col5, t2.col9
from table1 t1 join table t2 on t1.col1 = t2.col4
join table3 t3 on t3.col5 = t1.col8
where t2.country = 'USA'
and t3.planet = 'EARTH'
order by t1.col1, t2.col2;

Rhino




Rhino?

What does your example have to do with the tables and columns or the
relationships between them provided for you in the original post?

Just curious



There's no real relationship at all, actually :-) I just conjured up an 
untested example. I suppose I should have worked Imran's column names into 
the table instead of using my own - or at least explained what I was doing 
more clearly.


Sorry for any confusion! I'm afraid I'm a bit distracted today so forgive me 
for a weak example.


Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006


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