RE: upgrading mysql

[Joshua Gordon]

Also see http://dev.mysql.con/doc/refman/5.0/en/mysql-upgrade.html.
And make sure you make a backup before you do anything :)

-Original Message-
From: Tom Worster [mailto:f...@thefsb.org] 
Sent: Tuesday, January 12, 2010 10:47 AM
To: Lawrence Sorrillo; mysql@lists.mysql.com
Subject: Re: upgrading mysql

How about:

1 shut down the slave, upgrade it, restart it, let it catch up.

2 shut down the master, upgrade it, restart it, let the slave catch up.

?





On 1/12/10 12:34 PM, "Lawrence Sorrillo"  wrote:

> Hi:
> 
> I want to upgrade a master and slave server from mysql 4.1 to mysql
5.1.
> 
> I want to so something like follows:
> 
> 1. Stop all write access to the master server.
> 2. Ensure that replication on the slave is caught up to the last
change
> on the master.
> 3. stop binary logging on the master.
> 4. stop replication on the slave.
> 5. dump the master, stop old 4.1 server, start new 5.1 server and
reload
> master dump file under 5.1 server ( binary logging is turned off)
> 6. dump the slave, stop old 4.1 server, start new 5.1 server and
reload
> slave dump file under 5.1 server.
> 7. After loading is complete, test then start binary logging on master
> while still preventing updates to updates.
> 8. After loading slave, test then start slave (get configs in place
and
> restart server).
> 
> I am thinking that in this scenario I dont have to bother with
recording
> binlog file names and position etc etc.
> That both servers will have the same databases abd replication and
> binary logging will start on the two databases with no data loss and
> continue forward.
> 
> 
> Comments?
> 
> ~Lawrence
> 
> 
> 



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


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

FW: cache-panel DB issue

[Joshua Gordon]

I am trying to move my cache into ndb but when I use the ndb engine the
query time is 4 min versus myisam which is 7 seconds. This is due to the
fact it is doing a full table scan on ndb and an index on myisam. I was
hoping someone could help me fix this or at least explain why this is
happening. I have included the select statements , the select statement
and the explain select for both the ndb and the myisam.

 

Cache database (ndb)

  Select distinct t0.panelId from cache1 t0, cache2  t1 where
t0.panelid=t1.panelid; (282.54 s)

 

CREATE TABLE  `panel`.`cache1` (

  `panelId` int(11) DEFAULT NULL,

  KEY `panelid` (`panelId`)

) ENGINE=ndbcluster DEFAULT CHARSET=utf8;

 

CREATE TABLE  `panel`.`cache2` (

  `panelId` int(11) DEFAULT NULL,

  KEY `panelid` (`panelId`)

) ENGINE=ndbcluster DEFAULT CHARSET=utf8;

 

Explain select distinct t0.panelId from cache1 t0, cache2 t1 where
t0.panelid=t1.panelid; 

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

| id | select_type | table | type | possible_keys | key | key_len |
ref  | rows   | Extra   |

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

|  1 | SIMPLE  | t0| ALL  | panelid   | NULL| NULL|
NULL | 534063 | | 

|  1 | SIMPLE  | t1| ref  | panelid   | panelid | 5   |
cache.t0.panelId |  1 | Using where | 

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

 

Panel database (myisam)

select distinct t0.panelId from cache3 t0, cache4 t1 where
t0.panelid=t1.panelid;  (6.5 s)

 

CREATE TABLE  `panel`.`cache3` (

  `panelId` int(11) DEFAULT NULL,

  KEY `panelid` (`panelId`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

CREATE TABLE  `panel`.`cache4` (

  `panelId` int(11) DEFAULT NULL,

  KEY `panelid` (`panelId`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

explain select distinct t0.panelId from cache3 t0, cache4 t1 where
t0.panelid=t1.panelid; 

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

| id | select_type | table | type  | possible_keys | key | key_len |
ref  | rows   | Extra  |

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

|  1 | SIMPLE  | t0| index | panelid   | panelid | 5   |
NULL | 534053 | Using index; Using temporary   | 

|  1 | SIMPLE  | t1| ref   | panelid   | panelid | 5   |
panel.t0.panelId |  1 | Using where; Using index; Distinct | 

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

 

Thanks Joshua Gordon

Tools on http://www.severalnines.com do not work.

[Joshua Gordon]

Hi List,

I have been trying to use bencher and chkfrag from
http://www.severalnines.com. But I continue to receive the following
errors../chkfrag: error while loading shared libraries:
/usr/local/mysql/lib/libndbclient.so.4: cannot restore segment prot
after reloc: permission denied. 

I am running this with NDB 7.0.6 and the owner of the
library is mysql.mysql. 

Any ideas?

 

Thanks Joshua Gordon.

binlog questions

[Joshua Gordon]

We outputted the bin log using the following command:

mysqlbinlog -v --base64-output=DECODE-ROWS oo-mysql1-bin.87

We then looked in this file and found some odd things.  For example
there is the below insert statement:

### INSERT INTO panel.history
### SET
###   @1=-182667600 (4112299696)
###   @7=NULL


The table structure for history is:

CREATE TABLE `history` (
  `historyid` int(11) NOT NULL AUTO_INCREMENT,
  `panelid` int(11) DEFAULT NULL,
  `projectid` int(11) DEFAULT NULL,
  `resultid` int(11) DEFAULT NULL,
  `pulldate` datetime DEFAULT NULL,
  `resultdate` datetime DEFAULT NULL,
  `senddate` datetime DEFAULT NULL,
  `sendcount` int(11) DEFAULT NULL,
  `bouncecount` int(11) DEFAULT NULL,
  `link` char(128) DEFAULT NULL,
  `projectgroupcode` int(11) DEFAULT NULL,
  `vendorid` int(10) unsigned DEFAULT NULL,
  `ipaddress` char(15) DEFAULT NULL,
  `enddate` datetime DEFAULT NULL,
  `bloodhoundid` int(10) unsigned DEFAULT NULL,
  `incentive` int(4) unsigned DEFAULT NULL,
  PRIMARY KEY (`historyid`),
  KEY `RESULTDATE` (`resultdate`),
  KEY `PULLDATE` (`pulldate`),
  KEY `PANELID_PROJECTID` (`panelid`,`projectid`),
  KEY `PROJECTGROUPCODE` (`projectgroupcode`),
  KEY `projectid_vendorid` (`projectid`,`vendorid`),
  KEY `PROJECTIDb` (`projectid`) USING BTREE,
  KEY `bloodhoundid` (`bloodhoundid`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1


So it is trying to put a negative value in for the first column?

Then you have stuff like:

### INSERT INTO panel.history
### SET
###   @1=1169499418
###   @7=348123-41-35 05:64:02

That looks valid but there is no row in the history table with that
historyid so why isn't it there?  all in all it seems very inconsistent
with the columns it uses and what shows up etc..  any insight would be
appreacited thank you.

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

RE: Sun bought by Oracle

[Joshua Gordon]

I hope I start getting paid what Oracle DBA's make.

-Original Message-
From: Arthur Fuller [mailto:fuller.art...@gmail.com] 
Sent: Tuesday, April 21, 2009 12:04 PM
To: russbucket
Cc: mysql@lists.mysql.com
Subject: Re: Sun bought by Oracle

I too am a big entusiast of Sun's VirtualBox, and I hope that nothing
goes
sideways on this product.

A.

effects and that is VirtualBox. I've used MySQL for years and hope
Oracle
> does not stop it, but you never know with Ellison encharge!
>
> Just my 2 cents.
> --
>
>

--
> OpenSUSE 11.1 KDE 4.1.3,
> Intel DX48BT2 Core 2 Dual E7200. 4 GB DDR III
> GeForce 8400 GS, 320GB Disc (2)
>
> ---
> Russ
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com
>
> I know this is the MySQL list but there is another Sun product that
this

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

RE: Question!

[Joshua Gordon]

Read the online Manual.

-Original Message-
From: Jarikre Efemena [mailto:jefem...@yahoo.com] 
Sent: Monday, March 30, 2009 11:30 PM
To: mysql@lists.mysql.com
Subject: Question!

Dear sir,
 
I am young web developer using PHP Script in designing interactive website. I 
desire to include Mysql database on my websites. 
 
Please, how do I import, upload/export Mysql database to a website server after 
creating a Mysql user account and a particular database on my local machine?
 
I will be very grateful if comprehensive response is granted to my question.
 
Thank You.
 
Efe Jarikre
Nigeria 


  

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

used command isn not allowed

[Gordon]

I tried running this load data command on Server version: 5.0.27 and get the
1148 error. I'm not sure if it is referring tho the comand client or the
server.

I also do not understand why the command {or which part} it is complaining
about. Probably something obvious, but I just can't see it.

 

Any suggestion will bew greatly appreciated.

 

mysql> load data local infile "c:\\losalamos.asc" into table losalamos_new

->fields terminated by ',' optionally enclosed by '"' lines
terminated by '\r\n' ignore 1 lines

->(`FIRSTOWNER`, `OWNER2`, `NFUL_ADDR`, `NFUL_ADDR2`, `CITY`,
`STATE`, `ZIPCODE`, `FIRST_NAME`,

-> `LOT`, `BLCK`, `TRACT`, `SUBD`, `SECTION`, `TOWNSHIP`,
`RANGE`, `DESCRIPT`, `LAST_NAME`, `TAXAMT`,

-> `MAPCODE`, `NUMBER`, `STREET`, `YBLT`, `MAIN`, `SECOND`,
`THIRD`, `DOWNSTAIRS`, `TOTAL`, `DEAR`,

-> `TRUST`, `CTYSTZIP`, `ACCT`, `ACRE`, `DEEDBOOK`, `DEEDPAGE`,
`TAXDUE`, `TAXPAID`, `PUR_DATE`,

-> `PROP_ZIP`, `COMPANY`, `SALUTATION`, `CONTACT`, `BEDROOMS`,
`BATHS`, `TAXYEAR`, `UNITS`, `SALEDATE`,

-> `ACCTTYPE`, `LANDASD`, `IMPASD`, `cntyname`, `SALEMONTH`,
`SALEYR`);

ERROR 1148 (42000): The used command is not allowed with this MySQL version

RE: Order By and Ignore Punctuation

[Gordon]

Try something like this. If there are multiple punctuation values you
want to ignore you can nest multiple REPLACE functions.

mysql> create table names (name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into  names values
('Osbourn'),("O'shea"),("O'Malley"),('Olathe'),('Ottawa');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT name 
 > FROM   names 
 > ORDER BY REPLACE(name,"'",'');
+--+
| name |
+--+
| Olathe   |
| O'Malley |
| Osbourn  |
| O'shea   |
| Ottawa   |
+--+
5 rows in set (0.00 sec)

-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 03, 2007 7:48 PM
To: Bill Guion
Cc: mysql@lists.mysql.com
Subject: RE: Order By and Ignore Punctuation

I would suggest you order by something that includes a fulltext index on
the specific column.
Maybe check out the documentation on the MATCH()AGAINST() systax as well
as fulltext searches in general.

For example:
SELECT Col1, Col2, Score AS MATCH(TextCol) AGAINST ("") WHERE ... ORDER
BY Score;

Hope to help,
   -Andy

-Original Message-
From: Bill Guion [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 03, 2007 8:33 PM
To: mysql@lists.mysql.com
Subject: Order By and Ignore Punctuation

I would like to perform a query of a personnel database with an ORDER BY
clause that ignores punctuation. For example, O'shea would sort after
Osbourne, not to the beginning of the Os.

Is this doable in the query?

  -= Bill =-
-- 

You can tell a lot about a man by the way he handles these three
things: a rainy day, lost luggage, and tangled Christmas tree lights.



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




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


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

RE: Giving Back...Well, Maybe

[Gordon]

Just one suggestion re the behavior of special characters between file
input and command line input. Try using char(10) {I think that is new
line} instead of \n. That should work in both scenerios.

-Original Message-
From: John Kebbel [mailto:[EMAIL PROTECTED] 
Sent: Saturday, April 28, 2007 7:19 AM
To: mysql@lists.mysql.com
Subject: Giving Back...Well, Maybe

I don't have enough MySQL knowledge to contribute much to this
mailing
list, but as a 23 year veteran teacher, I have some expertise when it
comes to developing methods of instruction (especially
self-instruction). While studying for the MySQl CMDEV exam, I created a
method for MySQL skill-building that may help others trying to master
MySQL systematically.

I had been building my expertise with queries by redirecting
commands
from a text file into MySQL. When I discovered MySQL comments through
this mailing list, I realized I could store my learning by putting an
active command I was testing at the top line of my text document, and my
previously tested commands below them inside a commented out area. This
morning I went a step further when I realized I could embed XHTML coding
inside MySQL comments and have a dual-purpose page, a page that (1) lets
me test queries by redirecting them to MySQL on my local computer, and
then (2) stores the tested queries inside commented-out areas in a
format I can post on the web.

The web page format allows me to review what I've done and lets
me
continue my research at home or work by downloading, testing and
expanding, then uploading my new research.

I hope someone finds the concept useful. What I've accomplished
so far
is at

 http://scripting-solutions.com/certifications/mysql/dothis.html

Thanks to everyone who has been of help to me here now and in the
future.  


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


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

RE: ORDER BY question

[Gordon]

I think you can also do 
SELECT *, 
   DATE_FORMAT(deadline, '%d-%m-%Y') AS deadline_f,
   Status + 0 AS sorted_grade
FROM v_issue_project_task
ORDER BY sorted_grade

That way you do not have to change the code if you add a value to the
enum list via ALTER TABLE.
-Original Message-
From: Mike van Hoof [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 21, 2007 3:19 AM
To: Christophe Gregoir
Cc: mysql
Subject: Re: ORDER BY question

Thanks, that is also a solution.

Friend of mine pointed me to the following:

SELECT *, DATE_FORMAT(deadline, '%d-%m-%Y') as deadline_f,
CASE `status`
WHEN 'not yet started' then 1
WHEN 'in progress' then 4
WHEN 'finished' then 5
WHEN 'now hiring' then 3
WHEN 'waiting' then 2
WHEN 'closed' then 6
END AS sorted_grade
FROM v_issue_project_task
ORDER BY sorted_grade

- Mike

Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
 
Uw bedrijf voor Multimedia op Maat



Christophe Gregoir schreef:
> Hey Mike,
>
> Sounds like you would be better of with an ENUM of integers, e.g. 
> ENUM(-1,1,2,3) where -1 stands for to be started, 1 for started and so

> on.
> To answer your question:
> ORDER BY `status` = 'to be started', `status` = 'started', `status` = 
> 'finished', `status` = 'canceled'
>
> Mike van Hoof wrote:
>> Hello everybody,
>>
>> I got a small problem with ordering on en ENUM field. The values in 
>> this field are:
>> - to be started
>> - started
>> - finished
>> - canceled
>>
>> And i want to order on this field, but in the direction the are above

>> here (and not alpabetically).
>> Is that possible?
>>
>> - Mike
>>
>
>

-- 
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.5.446 / Virus Database: 268.18.15/728 - Release Date:
3/20/2007 8:07 AM
 

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.15/728 - Release Date:
3/20/2007 8:07 AM
 


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

RE: Baffled by error

[Gordon]

Try this 

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
GROUP BY a.account_id
HAVING   SUM(c.agent_product_time) >= '500'
ORDER BY mins

You have to do the SUM with the GROUP BY before you can test the
criteria

-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 16, 2006 10:30 AM
To: MySQL List
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...

Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.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]

Re: Multiple primary keys

[Gordon]

MySQL will not use the primary key unless you use the left most columns. For 
a 1 column primary key then it's easy. For a 2 column primary key you can 
either use the 1st column in the primary key or both columns. If you only 
reference the 2nd column the query will not use the primary key and will do 
a full table scan.


In your case you are referencing "classb" which is not the left most collumn 
in the primary key set. Put the word "explain" preceding  the statement and 
execute the query. it will show you what keys are used in the query and in 
your case it is none. You either need to define another KEY with classb as 
the 1st column in the definition or if all of your queries at least 
reference classb then you could rebuild the primary key and put classb as 
the 1st entry in the definition.
- Original Message - 
From: "nngau" <[EMAIL PROTECTED]>

To: "'Kishore Jalleda'" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, April 27, 2006 9:11 AM
Subject: RE: Multiple primary keys



Thanks all. The query I run is a subquery.

I noticed joined query run a lot faster than the sub.

This is the subquery:
select * from class_c where detail_id in (select classC from
item_classification where classb="216") order by detail;

This query takes nearly 3 minutes, before it did not take that long. I 
guess

I should use a primary key As an index.

I want to be able to add items that I can classify into different classa,
classb or classc.

Example:

Itemid 1025 ClassA: 101 classB: 218 classC: 356

Same item can be put into another class.

Itemid 105 ClassA: 101 classb: 218 classC: 357

So not having a primary key/index will slow my queries?


-Original Message-
From: Kishore Jalleda [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 27, 2006 10:49 AM
To: nngau
Cc: mysql@lists.mysql.com
Subject: Re: Multiple primary keys

On 4/27/06, nngau <[EMAIL PROTECTED]> wrote:


Can someone figure out what's going on. This is the only change I made
to this table. Basically I don't want any duplicate rows, so I setup 4
fields to be my primary key.

When I do a simple select query it takes nearly 30 seconds to complete.
This is affecting my websites and taking a very long time to query the
Products. Have I setup this table right? Thank You!!

+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| itemID  | int(6)  |  | PRI | 0   |   |
| classA  | int(3)  |  | PRI | 0   |   |
| classB  | int(3)  |  | PRI | 0   |   |
| classC  | int(3)  |  | PRI | 0   |   |
| picture | varchar(10) | YES  | | NULL|   |
| sex | char(2) | YES  | | NULL|   |
+-+-+--+-+-+---+


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




You can avoid duplicate rows with only one primary key, unless you have a
specific reason for having your primary key span on four columns.
Your query being slower depends on how you have indexed your columns
relating to your queries. Please give us a sample query which is running
slowly..

Kishore Jalleda
http://kjalleda.googlepages.com/projects


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






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

RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)

[Gordon]

REMOVE the semicolon ";" from " END;//"

SQLyog has a problem with all of the procedures, functions and triggers RE
the DELIMITER syntax.

> -Original Message-
> From: Daevid Vincent [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 11, 2006 10:00 PM
> To: mysql@lists.mysql.com
> Cc: 'Shawn Green'
> Subject: RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)
> 
> I was using SQLYog 5.03 RC1.
> 
> vmware ~ # mysql --version
> mysql  Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline
> 5.1
> 
> But just to sanity check. I ssh'd in and tried this at the mysql command
> line utility:
> 
> vmware ~ # mysql somedatabase
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
> 
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 415 to server version: 5.0.19-log
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> 
> mysql> delimiter //
> mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> -> FOR EACH ROW
> -> BEGIN
> -> IF NEW.skey < 1 THEN
> ->
> Display all 187 possibilities? (y or n)
> -> EW.skey = 1;
> -> ELSEIF NEW.skey > 9 THEN
> ->
> Display all 187 possibilities? (y or n)
> -> EW.skey = 9;
> -> END IF;
> -> END;//
> delimiter ;
> ERROR 1064 (42000): 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 '.skey = 1;
> ELSEIF NEW.skey > 9 THEN
> EW.skey = 9;
> END IF;
> END' at line 5
> mysql> delimiter ;
> mysql>
> 
> 
> > -Original Message-
> > From: Shawn Green [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, April 11, 2006 12:41 PM
> > To: Daevid Vincent; mysql@lists.mysql.com
> > Subject: Re: FW: New to TRIGGER and CALL. Example gives
> > errors. (repost)
> >
> >
> >
> > --- Daevid Vincent <[EMAIL PROTECTED]> wrote:
> >
> > > This may have been lost, so I'm reposting hoping for a clue
> > as to why
> > > the
> > > mySQL example onlie gives me errors...
> > > -Original Message-
> > > Sent: Sunday, April 09, 2006 7:41 PM
> > >
> > > I'm trying to follow the example in the manual to create a trigger:
> > > http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html
> > >
> > > #DROP TRIGGER upd_check;
> > > delimiter //
> > > CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> > > FOR EACH ROW
> > > BEGIN
> > >   IF NEW.skey < 1 THEN
> > >   SET NEW.skey = 1;
> > >   ELSEIF NEW.skey > 9 THEN
> > >   SET NEW.skey = 9;
> > >   END IF;
> > > END;//
> > > delimiter ;
> > >
> > > All I'm trying to do is enforce that my starkeys.skey column is
> > > always in
> > > the range of 1 through 9. I was planning to start with this example
> > > and work
> > > my way up. Ideally it should check on UPDATE or INSERT. The manual
> > > recommended:
> > >
> > > "It can be easier to define a stored procedure separately and then
> > > invoke it
> > > from the trigger using a simple CALL statement. This is also
> > > advantageous if
> > > you want to invoke the same routine from within several triggers."
> > >
> > > But I don't know how to do that yet.
> > >
> > > vmware public_html # mysql --version
> > > mysql  Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using
> > > readline 5.1
> > >
> > > But I just get these errors:
> > >
> > > Error Code : 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
> > 'delimiter
> > > //
> > > CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> > > FOR EACH ROW
> > > BEG' at line 2
> > > (0 ms taken)
> > >
> > > Error Code : 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 'ELSEIF
> > > NEW.skey
> > > > 9 THEN
> > >   SET NEW.skey = 9' at line 1
> > > (0 ms taken)
> > >
> > > Error Code : 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 'END IF'
> > > at line
> > > 1
> > > (0 ms taken)
> > >
> > > Error Code : 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 'END' at
> > > line 1
> > > (0 ms taken)
> > >
> > > Error Code : 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 '//
> > > delimiter' at line 1
> > > (0 ms taken)
> > >
> >
> > This looks suspiciously like an interface issue, not a coding issue.
> > How are you delivering these commands to your MySQL server and is it
> > v5.0 or newer?
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> 
> 
> --
> MySQL General Mailing List
> For list archives

RE: Reserevd Error -7776 -- Urgent

[Gordon]

I have been linking MySQL tables into access for versions 3.23 thorough
5.17. The only way I have been able to deal with insert/update tables with a
timestamp field is to build a query which includes all of the fields except
the timestamp field. You can then do your insert or update against the
query. ACCESS/ODBC does not know about the timestamp field so they are
happy. If you specify CURRENT TIMESTAMP on the timestamp field in the MySQL
definition, MySQL will populate the field every time the row is changed or a
new row added.

> -Original Message-
> From: C K [mailto:[EMAIL PROTECTED]
> Sent: Friday, April 07, 2006 1:50 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com; [EMAIL PROTECTED]
> Subject: Re: Reserevd Error -7776 -- Urgent
> 
> I also tried folloing and got results as below:
> 1) I updated the default for timestamp filed as CURRENT TIMESTAMP as
> mentioned in MySQL 5.1 manual.
> But this not worked.
> 2) I dropped timestamp fiield from that table and refreshed link, then
> I can ork properly. Now I can insert and update reocrds without any
> problem.  But this against the comment and responce from MySQL
> community that it is necessary to have a timestamp field to properly
> view and edit data in linked MysQL table. Else it will give he error
> as #deleted, But isn't it strange that now it is not giving me any
> such error. ? Why?
> Thanks.
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


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

RE: Stored procedures and views

[Gordon]

If the features available in stored procedures {i.e. looping etc.} are
required, have you tried having the procedure 1st write/edit the data in a
temporary table and at the end select the values you want from that table. 

I think I might write 2 procedures. One that does the data manipulation and
the other that selects the result. 
something like this  

DELIMITER //
DROP PROCEDURE IF EXITS manipulate//

CREATE PROCEDURE manipulate(
CREATE TEMPORARY TABLE data_result

...
...
END// 

DROP PROCEDURE IF EXITS result//

CREATE PROCEDURE result(
...
  CALL manipulate (
...
  SELECT ... FROM data_result
END//

DELIMITER ;
> -Original Message-
> From: Martijn Tonies [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 24, 2006 1:04 PM
> To: Chris Carrier; mysql@lists.mysql.com
> Subject: Re: Stored procedures and views
> 
> 
> 
> > I just mean is it possible to hook a set of stored procedures to a view
> as
> > opposed to a temporary table that we'd have to explicitly update?
> 
> "hook"?
> 
> If you mean: using the views in select statements inside a procedure:
> sure it. Did you try?
> 
> 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
> 
> > Chris
> >
> > -Original Message-
> > From: Martijn Tonies [mailto:[EMAIL PROTECTED]
> > Sent: Friday, March 24, 2006 10:26 AM
> > To: mysql@lists.mysql.com
> > Subject: Re: Stored procedures and views
> >
> > Chriss,
> >
> > > I'm trying to create a flatfile dump from our database which requires
> some
> > > functionality that's not possible with raw sql.  Up to now we've been
> > using
> > > PHP tied to mySql to do all the work and get the data in the correct
> > format.
> > > We would like to switch this functionality over to stored procedures
> in
> > > mySql.  My question is this: Is it possible to hook a series of stored
> > > procedures to a view so that the data is live and current?
> >
> > What do you mean by that?
> >
> > A view is always current.
> >
> > Can you explain it a bit better?
> >
> > >We could set it
> > > up to run our procedures on some regular interval and dump the result
> into
> > a
> > > temporary table but having up-to-date data would be ideal.
> >
> > 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]
> >
> >
> > --
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/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: 3/24/2006
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


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

InnDB disabbled on 5.1.7

[Gordon]

We are running 2.6.15-gentoo Linux and downloaded the max binaries for
5.1.7. With the following my.cnf I thought we should have InnoDB. All of the
InnoDB files got created but show variables like 'have%'; displays "
have_innodb  DISABLED". Exactly the same my.cnf {except the skip bdb is not
commented out} has InnoDB enabled.

Any ideas on what we have to do to enable InnoDB.

my.cnf

# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/var) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
 
# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port    = 3306
socket  = /tmp/mysql.sock
 
# Here follows entries for some specific programs
 
# The MySQL server
[mysqld]
port    = 3306
socket  = /tmp/mysql.sock
max_connections = 100
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
thread_cache_size = 8
query_cache_size= 2M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
 
 
 
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
 
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1
 
 
 
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
 
skip-bdb
 
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/var/
innodb_data_file_path = ibd1:2000M;ibd2:2000M;ibd3:2000;ibd4:10M:autoextend
#innodb_log_group_home_dir = /usr/local/var/
#innodb_log_arch_dir = /usr/local/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
 

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout

mysql> select version();
++
| version()  |
++
| 5.1.7-beta-max-log |
++
1 row in set (1.73 sec)

Linux zeus 2.6.15-gentoo-r1 #10 SMP PREEMPT Tue Mar 7 15:36:28 MST 2006 i686
Intel(R) Xeon(TM) CPU 3.80GHz GenuineIntel GNU/Linux

mysql> show variables like 'have%';
++--+
| Variable_name  | Value|
++--+
| have_archive   | YES  |
| have_bdb   | DISABLED |
| have_blackhole_engine  | YES  |
| have_compress  | YES  |
| have_crypt | YES  |
| have_csv   | YES  |
| have_example_engine| NO   |
| have_federated_engine  | YES  |
| have_geometry  | YES  |
| have_innodb| DISABLED |
| have_ndbcluster| DISABLED |
| have_openssl   | NO   |
| have_partitioning  | YES  |
| have_query_cache   | YES  |
| have_row_based_replication | YES  |
| have_rtree_keys| YES  |
| have_symlink   | YES  |
++--+
17 rows in set (0.10 sec)

mysql> show variables like 'inno%';
+-+-
+
| Variable_name   | Value
|
+-+-
+
| innodb_additional_mem_pool_size | 20971520
|
| innodb_autoextend_increment | 8
|
| innodb_buffer_pool_awe_mem_mb   | 0
|
| innodb_buffer_pool_size | 536870912
|
| innodb_checksums| ON
|
| innodb_commit_concurrency   | 0
|
| innodb_concurrency_tickets  | 500
|
| innodb_data_file_path   |
ibd1:2000M;ibd2:2000M;ibd3:2000;ibd4:10M:autoextend |
| innodb_data_home_dir|
|
| innodb_doublewrite  | ON
|
| innodb_fast_shutdown| 1
|
| innodb_file_io_threads  | 4
|
| innodb_file_per_table   | OFF
|
| innodb_flush_log_at_trx_commit  | 0
|
| innodb_flush_method |
|
| innodb_force_recovery   | 0
|
| innodb_lock_wait_timeout| 50
|
| innodb_locks_unsafe_for_binlog  | OFF
|
| innodb_log_arch_dir |
|
| innodb_log_

RE: Easy regex replace?

[Gordon]

If "%20" are the actual characters in the varchar column you shuld be able
to do 
UPDATE table 
SETcolumn_name =REPLACE(column_name,'%20',' ');

You might have to use REPLACE(column_name,'\%20',' '); 
to force MySQL to treat "%" as an actual value instead of a wild card.

-Original Message-
From: Pooly [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 20, 2006 5:11 AM
To: MySQL General
Subject: Re: Easy regex replace?

2006/3/19, Adam i Agnieszka Gąsiorowski FNORD <[EMAIL PROTECTED]>:
>
> On 2006-03-18, at 00:59, Yani Copas wrote:
>
> >
> > Is there a quick and dirty way to update such that I can only
> > affect the portion
> > of a string (varchar column) that matches a regexp?
> > (e.g. replace all '%20' with ' ' leaving the rest untouched?)
>
> You know that proverb - "For a man in possession of a hammer,
> everything looks like a nail".
>   Don't do that. MySQL is *really slow* with Regular Expressions. It
> will be much easier to SELECT
>   all records you want to change, storing their IDs in a list (or
> array) construct, then tell your favourite
>   script program to construct an REPLACE query out of these chosen
> few, after it does whatever you want it to do
>   with the records' data.


Yeah, but sometimes beoing able to do such things on the mysql 
command line would be very helpful ! (Instead of having a script for
such simple things which would be like having a jack hammer for a
nail.. )

--
Pooly
Webzine Rock : http://www.w-fenec.org/


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

RE: Some queries use 100% CPU after restore

[Gordon]

Have you tried Repair table or if InnoDB 
ALTER TABLE ENGINE=InnoDB;

Sometimes I've noticed after a restore or after adding lots of rows
performance is slow. REPAIR or the ALTER TABLE fixes it.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 17, 2006 2:37 PM
To: mysql@lists.mysql.com
Subject: Some queries use 100% CPU after restore
Importance: High

I'm moving a database to a new server.  I'm using MySQL v5.0.16 
on 'Windows.  I used the MySQL Administrator to backup on the old 
system and restore on the new one.  Everything is fine _except_ on the 
new server, some queries take 2-3 minutes with MySQL using 100% of the 
CPU.  I've dropped unneeded views, done maintenance, verfied that all 
the indices I expect are there, etc.  What's very, very strange is 
that it seems that the long queries are for old records and records 
created since the move are OK.  I realize this is a vague request but 
I've been poking at this for a long time without getting any good 
clues or making any real headway.  Any kind of brainstorming on things 
to check would be very welcome.  Thanks.

   Chris

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


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

RE: getting table metadata

[Gordon]

What you are looking for is the INFORMATION_SCHEMA views, but they are not
available before 5.0. Until then you have to parse the "show create table"
or "DESCRIBE tablename" may be easier to parse 

mysql> describe organizations;
+---+---
+--+-+-+---+
| Field | Type
| Null | Key | Default | Extra |
+---+---
+--+-+-+---+
| orgn_ID   | char(4)
| NO   | PRI | |   |
| orgn_Name | char(50)
| YES  | | NULL|   |
| orgn_Billing_Type | enum('Bank Transfer','Credit
Card','Invoice','none','Purchase Order') | YES  | | NULL
|   |
| orgn_Internal_ID  | char(15)
| YES  | | NULL|   |
| orgn_Active   | enum('Yes','No')
| NO   | | Yes |   |
| orgn_Who  | char(4)
| NO   | | |   |
| orgn_Timestamp| timestamp
| YES  | | CURRENT_TIMESTAMP   |   |
| orgn_Create   | datetime
| NO   | | 2000-01-01 00:00:00 |   |
+---+---
+--+-+-+---+
8 rows in set (0.27 sec) 

-Original Message-
From: Yves Glodt [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 17, 2006 9:22 AM
To: mysql@lists.mysql.com
Subject: Re: getting table metadata

On Friday 17 March 2006 15:52, Martijn Tonies wrote:
> Hello Yves,

Hello Martijn,

> > is it possible to get information about tables by doing queries on some
>
> system
>
> > tables? I am using mysql version 4.1.11 on debian sarge.
> >
> > In my case I need to know which columns (names and types) a table has,
> > and
>
> how
>
> > the primary key is defined.
> >
> > How can I get this information out of mysql by only using sql ?
>
> Have a look at the SHOW commands in the documentation.

I know about the "show create table ..." but it doesn't really satisfy my 
needs... Is there really no other way apart of parsing "create table" 
statements?

(I needed to this with firebird, and found all I could dream of in the RDB$ 
tables)

Best regards,
Yves

> As for system tables, MySQL 4.1 hardly has any.
>
> 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]

RE: Accountability with MySQL

[Gordon]

And now we are down to reality. This is a MySQL list. Views are a wonderful
thing for creating an isolation layer between the application and the
database. However, MySQL's current implementation makes it extremely
difficult in many cases to avoid full table scans when you define the
logical view. {Not poking at the development team I think 5.x is a huge step
forward}. 

So you can't hide the underlying structure currently in MySQL. Like all
other things we have to commingle best practices with pragmatism. In other
words do what makes sense while at the same time acquiring a good knowledge
of fundamentals. {Not just slap name address phone1  phone n in some
table with an auto-increment ID field and say your done with the design.}

By the way, Date and Codd {both way above me in math and theory} had this
discussion ~20 years ago over a 6 month period in pages and pages of
Database magazine. In the end I believe they agreed to disagree.

-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 16, 2006 5:33 AM
To: mysql@lists.mysql.com
Subject: Re: Accountability with MySQL

From: <[EMAIL PROTECTED]>
> "Martijn Tonies" <[EMAIL PROTECTED]> wrote on 16/03/2006 11:02:32:
>
> > Well, the question still is if you should store "unknown" at all ;)
> >
> > Not according to Date: you should store what is known. See the remarks
> > about the "true propositions", from which relational databases are
> derived
> > (but you probably know that).
>
> As someone totally unread in the theory of databases, that seems unduly
> puritanical. I assume that what Date would propose is that you have
> another table (related by master key) in which, if you do not know
> something, you do not enter it. But this means that if you have 10
> different pieces of potentially but not necessarily available information
> about a single master record (e.g. a person), you have to do a 10-way join
> in order to retrieve all the information about them. Replacing a
> theoretically ugly null flag with a 10 way join strikes me, as an engineer
> rather than a theoretician, the wrong side of the elegance/practicality
> trade-off.

Using NULLs as well as de-normalization brings the risk of
integrity problems to your storage, storing what is right is only
a good thing.

And when it comes to having to writing JOINs for all your queries,
lo and behold, I bring you the wonder of the VIEW.

;-)



Martijn Tonies
Upscene Productions
http://www.upscene.com


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


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

RE: replication

[Gordon Bruce]

There is one issue. MySQL changed the way it parsed SQL join statements
in 5.03. Specifically JOINS in the older syntax using implicit joins
{tables seperated by ","} i.e.

FROM table_a, table_b 

are parsed differently than they are in versions prior to 5.03. If you
have any statements that do any implicit joins the statements that ran
sucessfully on your 4.1 system may error out on your 5.0.?  system.

Full details on the change and it's impact on query design can be found
here:
http://dev.mysql.com/doc/refman/5.0/en/join.html


-Original Message-
From: Prasanna Raj [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 03, 2006 6:28 AM
To: Octavian Rasnita
Cc: mysql@lists.mysql.com
Subject: Re: replication


Yes ..u can use older version as master and 5.0 version as slave


http://dev.mysql.com/doc/refman/5.1/en/replication-compatibility.html


http://linux.com.hk/penguin/developer/mysql/manual_Replication.html#Repl
ication_Compatibility


Ciao
Praj


On Fri, 3 Mar 2006 12:32:24 +0200
"Octavian Rasnita" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I have 2 servers. On one of them I have MySQL 4.1 (the main server)
and on
> the second I have MySQL 5.0.
> I want to use the second server to replicate the first server. Is it
> possible or the servers should have the same version?
> 
> Or I will need to install one more MySQL 4.1 on the second server and
use it
> for replicating the first server?
> 
> Thank you.
> 
> Teddy
> 
> 

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


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

RE: MYSQL: Unlimited Forums, Scalablity Issues, advice please? - Bayesian Filter detected spam

[Gordon Bruce]

Let's try to characterize the load and sizing. If the posts are mostly
text.
100 forums X 100,000 posts X 2,000 bytes per post = 20GB large but not
huge

We have people reading the posts. Even speed readers will take minutes
to read the new posts. Maybe 1 in 10 - 100 readers will do a new post. A
person writing a new post will take at least 5 minutes to type the 2000
bytes.

So, while this site might have 100's to 1000's of concurrent users, the
load profile on the database is much lower than a dynamic database
driven web site where users are constantly searching/linking and the
time spent on a specific page is seconds. 

I suspect if you watch some "typical" forum activity and build a crude
database interaction model, you will find that even with 1000's of
connected users the database server will need to run less than 100
relatively simple selects per second and many of them will be served by
the query cache.  

This is a very rushed analysis with lots of assumptions, but if close
then I think you are looking at at most a pair of dual dual-core 4GB
systems running Master Slave replication.

-Original Message-
From: J. Pow [mailto:[EMAIL PROTECTED] On Behalf Of jay
Sent: Monday, February 27, 2006 6:06 PM
To: Philip Hallstrom
Cc: mysql@lists.mysql.com
Subject: [SPAM] - Re: MYSQL: Unlimited Forums, Scalablity Issues, advice
please? - Bayesian Filter detected spam

Hi Philip, thanks for the reply.

Single master + many read only slaves would only solve the problem of 
handling many many concurrent read accesses, by distributing the load 
across all slaves.

However, I guess the real problem, is that the writes would still need 
to be performed across ALL databases, and the DB would be HUGE, would it

not?

Lets say I host 100 forums, with 100k posts each, every write would need

to be replicated to as many slaves as I have.

Thanks!

Jay



Philip Hallstrom wrote:

>> Hi there,
>> I am in the midst of creating a forums hosting site.
>>
>> 1. It has to be highly scalable, so I doubt a single MYSQL db with 
>> TONS of "subforums that simulate full forums" would do.
>>
>> 2. Also regarding scalablity, I hope to "Add capacity" as and when 
>> its needed. So i'll have one server running initially, and when it 
>> gets too crowded, i'll get two servers etc.
>>
>> 3. I will be providing a user with a "dashboard" that allows him to 
>> view all his subscribed posts across ALL forums. So lets say a user 
>> is a member of 25 forums, this dashboard view will allow the user to 
>> view all his posts across all the forums.
>>
>> Does anyone have advice that could point me in the right direction?
>> I have solved the scalability issue WITHIN a forum (code can handle 
>> million + posts easy), but I havent solved the issue of scaling 
>> MULTIPLE separate forums.
>
>
> What about having a single write master with many read-only slaves?  
> Then modify your code so that posts go to the master and everything 
> else happens on the slaves?
>
>> Also, does there exist any php package that helps ease the process of

>> "deciding which Server/database to connect to"?
>> For example, someone accesses FORUM A, so the script would 
>> automatically know to direct all queries to the DB in SERVER 1 etc, 
>> and if i try to access FORUM J, it would connect to SERVER 2 etc. I 
>> could easily hard code this, but I was thinking "what if internal IP 
>> addresses change, or I decide to migrate a busy forum to a server of 
>> its own etc", so perhaps there is a better available packaged 
>> solution designed for this task.
>
>
> Create a table on a "central" server that contains this mapping.  This

> server could also hold the login tables as well...
>
> Just a thought.
>


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


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

RE: [SPAM] - Re: Inner join with left join - Bayesian Filter detected spam

[Gordon Bruce]

If you want to see all of the products {even those that have never been
ordered} then you need to SELECT ... FROM products ... LEFT JOIN orders 

I think you also have to do a LEFT JOIN on order_items
And pull prod_name from products {don't know what the column name in
products is}. 

SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p 
LEFT JOIN order_items as oi
ON (p.id = oi.product_id) 
LEFT JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY p.id
ORDER by qty ASC

-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 22, 2006 2:58 PM
To: MySql
Subject: [SPAM] - Re: Inner join with left join - Bayesian Filter
detected spam

> Is this what you mean?
> 
> SELECT
>   p.prod_name,
>   count(oi.product_id) AS mycount
> FROM ORDERS AS o
> INNER JOIN products ON o.id=p.id
> LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> GROUP BY oi.product_id
> ORDER BY mycount;

Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products
with
a zero count.  This tells me those products have not been ordered ever,
but
I would like to know what they are.

SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON (o.id = oi.order_id)
LEFT JOIN products as p
ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY oi.product_id
ORDER by qty ASC


-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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


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

RE: Byte Swapping (Re Post)

[Gordon Bruce]

If the order of the bytes is opposite between big-endian and
little-endian, then if you can get the bytes in a string REVERSE()
should flip the order.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 14, 2006 3:25 PM
To: gerald_clark
Cc: mysql@lists.mysql.com; David Godsey
Subject: Re: Byte Swapping (Re Post)

gerald_clark <[EMAIL PROTECTED]> wrote on 02/14/2006 
03:59:21 PM:

> [EMAIL PROTECTED] wrote:
> 
> >"David Godsey" <[EMAIL PROTECTED]> wrote on 02/14/2006 03:28:41 
PM:
> >
> > 
> >
> >>Well, just thought I'd try one more time because I didn't get an 
answer 
> >> 
> >>
> >to
> > 
> >
> >>my question last time.
> >>
> >>So what I have is a random data stream that is sent in raw form, and

> >> 
> >>
> >based
> > 
> >
> >>on some data definition, I can assemble with the correct data types 
and
> >>such.  One of my requirements is that I have to store the data in
raw
> >>form, and when I pull the data out, it displays based on the 
> >> 
> >>
> >configuration
> > 
> >
> >>(with the correct data types and such).  So floats and doubles are 
IEEE
> >>standards so I don't have to worry about those, however with integer
> >>types, I may need to do some byte swapping (because this data can
come
> >>from variouse systems that could be either big or little endian).
So 
I 
> >> 
> >>
> >am
> > 
> >
> >>singling out the data I need, but now I need to add the ability to 
byte
> >>swap the data.
> >>
> >>Keep in mind that it would be best if I can do this in SQL so that
it 
is
> >>portable.  I realize that it can easily be done in C, but that makes

my
> >>code less portable (which is also a requirement, to have it portable

> >> 
> >>
> >that
> > 
> >
> >>is).  So does anybody know of a MySQL function that is already 
> >> 
> >>
> >implemented
> > 
> >
> >>to do byte swapping? or know of a way to implement this in SQL?
> >>
> >>If not, is my only other option to write a UDF?
> >>
> >>Thanks for any help.
> >>
> >>Accomplishing the impossible means only that the boss will add it to

> >> 
> >>
> >your
> > 
> >
> >>regular duties.
> >>
> >>David Godsey
> >>
> >> 
> >>
> >
> >Native functions? No. Something you can cobble together? Yes.  There 
> >should be several ways you can deal with your data as a string of 
binary 
> >characters. Just re-sequence those and you should have your bytes 
swapped.
> >
> >One idea is to use the substring functions directly on your BINARY 
string. 
> >Another is to use the substring functions in combination with 
> >HEX()/UNHEX() to work on an escaped version of your BINARY string.
> > 
> >
> Would not the first zero value character terminate the substring, 
> rendering it invalid?
> 
> >Sorry or the lame ideas but usually things like this are not handled
at 

> >the database layer but rather in the application layer. Depending on 
which 
> >version of MySQL you are using you may be able to define a FUNCTION
(a 
> >different creature than a UDF) or a STORED PROCEDURE to do the 
swapping. 
> >Both will be pure SQL and should meet your compatibility needs.
Neither 

> >will be as fast as creating and registering a UDF, though.
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >

I don't know if it will or if it won't. The original poster (David
Godsey) 
seems to have no trouble extracting specific subsections of raw data
from 
his blob fields. I just assume that working with chunks of raw data that

contained zeroes in them was no problem for him.

His need is to somehow binarily invert sections of each number (the 
INET_... functions could also help) in order to convert big-endian to 
little-endian and vice versa. I was just trying to help point him to
some 
possible functions that may help him to do that. Hopefully he will post 
back with what works and what doesn't.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




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

RE: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in a result set - Bayesian Filter detected spam

[Gordon Bruce]

You can use a user variable [EMAIL PROTECTED] in the sample below} to number 
the rows in the result set.


mysql> set @row:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @row:[EMAIL PROTECTED], city_Name from citiesw limit 10;
+--+-+
| @row:[EMAIL PROTECTED] | city_Name   |
+--+-+
|1 | !fajji !fasan   |
|2 | 'aadeissa   |
|3 | 'abas   |
|4 | 'abas   |
|5 | 'abasabad   |
|6 | 'abd al qader   |
|7 | 'abdullah kalay |
|8 | 'abdullah kalay |
|9 | 'abruyeh|
|   10 | 'adel bagrou|
+--+-+
10 rows in set (0.00 sec)

-Original Message-
From: Jacques Brignon [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 30, 2006 9:19 AM
To: mysql@lists.mysql.com
Subject: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in a 
result set - Bayesian Filter detected spam

Oops! forgoten to include the list in the relply

--
Jacques Brignon

- Message transféré de Jacques Brignon <[EMAIL PROTECTED]> -
   Date : Mon, 30 Jan 2006 16:16:53 +0100
 De : Jacques Brignon <[EMAIL PROTECTED]>
Adresse de retour :Jacques Brignon <[EMAIL PROTECTED]>
  Sujet : Re: Finding the row number satisfying a conditon in a result set
  À : Jake Peavy <[EMAIL PROTECTED]>

Selon Jake Peavy <[EMAIL PROTECTED]>:

> On 1/30/06, Jacques Brignon <[EMAIL PROTECTED]> wrote:
> >
> > I would like some advice on the various and best ways of finding the rank
> > of the
> > row  which satisfies a given condition in a rsult set.
> >
> > Let's assume that the result set includes a field containing an identifier
> > from
> > one of the table used in the query and that not two rows have the same
> > value
> > for this identifier but that the result set does not contains all the
> > sequential values for this identifier and/or the values are not sorted in
> > any
> > predictable order.
> >
> > The brute force method is to loop through all the rows of the result set,
> > until
> > the number is found to get the rank of the row. That does not seem very
> > clever
> > and it can be very time consuming if the set has a lot of rows.
>
>
>
> use ORDER BY with a LIMIT of 1
>
> your subject line needs work though - a "row number" has no meaning in a
> relational database.
>
> -jp
>

Thanks for the tip, I am going to think to it as I do not see right away how
this solves the problem.

I agree with your comment, This is precisely because the result row number is
not in the database that I need to find it.

The problem I am trying to solve is the following:

A query returns a result set with a number of rows, lets say 15000 as an
example.

I have an application wich displays those 10 by 10 with arrows  based navigation
capabilities (first page, previous page, next page, last page).

I also have a search capability and I need to find in which set of 10 results
the row I search for will be diplayed in order to show directly the appropriate
page and to know what is the rank of this row in the result set or in the page
to show the searched result row "selected".

As an example the row having a customer id of 125, would have the row # 563 in
the result set (not orderd by customer id but by some other criterion like
name) and would therefore be displayed in the page showing result rows 561 to
570

When I say row I do not mean a row in any table but a row in the result set
produced by the query which can touch several tables.

None of the fields of the result set contains the row number, it is just  the
number of time I have to loop through the result set to get the row in the set
which matches my criterion.

I hope this makes my question clearer.

I am sure this is a pretty common problem, but I have not yet figured out the
clever way to tackle it!

--
Jacques Brignon
- Fin du message transféré -

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



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

RE: [SPAM] - convert help - Bayesian Filter detected spam

[Gordon Bruce]

I just added a user variable @fdata to get visabilility outside of the 
procedure and this is what I get.

mysql> delimiter //
mysql> create procedure test20 ()
->BEGIN
->   DECLARE fdata BLOB;
->   DECLARE foffset INT UNSIGNED;
->   DECLARE flength INT UNSIGNED;
-> DECLARE tmp_int BIGINT UNSIGNED;
->
-> SELECT 0xABCDEF0123456789 INTO fdata;
-> SELECT 14 INTO foffset;
-> SELECT 7 INTO flength;
->
-> SELECT SUBSTR(BINARY(fdata),
-> FLOOR(foffset/8)+1,
-> CEIL((flength + (foffset %8 ))%8))
-> INTO fdata;
->   set @fdata:=fdata;
-> END//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> call test20();
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select @fdata, hex(@fdata)
->
-> ;
++-+
| @fdata | hex(@fdata) |
++-+
| ═∩☺#E  | CDEF012345  |
++-+
1 row in set (0.00 sec)

-Original Message-
From: David Godsey [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 19, 2006 3:33 PM
To: mysql@lists.mysql.com
Subject: [SPAM] - convert help - Bayesian Filter detected spam


I am trying to convert binary data to a bigint so I can do bitwise
operations on the data, and I'm having trouble doing it.

I noticed that if I have binary data and I:
select data>>1; I get 0 (not what I'm expecting).

Here is a test procedure I wrote:

create procedure test20 ()
   BEGIN
DECLARE fdata BLOB;
  DECLARE foffset INT UNSIGNED;
  DECLARE flength INT UNSIGNED;
DECLARE tmp_int BIGINT UNSIGNED;

SELECT 0xABCDEF0123456789 INTO fdata;
SELECT 14 INTO foffset;
SELECT 7 INTO flength;

SELECT SUBSTR(BINARY(fdata),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))%8))
INTO fdata;

SELECT HEX(fdata);
SELECT CONVERT(fdata,BIGINT) INTO tmp_int;
SELECT HEX(tmp_int);
END
The last two selects are added to show what I would like to do, but have
not been able to get it to work.

Any help would be great.  Thanks in advance.

Accomplishing the impossible means only that the boss will add it to your
regular duties.

David Godsey

Accomplishing the impossible means only that the boss will add it to your
regular duties.

David Godsey


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

RE: Show Description options??

[Gordon Bruce]

If you are on 5.0.x you can use 

SELECT column_Name 
FROM   INFORMATION_SCHEMA.columns;


INFORMATION_SCHEMA is a set of VIEWS that lets you access the database
structure. See 

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

-Original Message-
From: Mike OK [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 19, 2006 10:34 AM
To: mysql@lists.mysql.com
Subject: Show Description options??

Hi

I was looking for a command that will list the names of my columns
only.
I have investigated show columns but there seems to be no way to return
just
the names.  Any suggestions??  Thanks Mike


-- 
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]

is UNION allowed in a MySQL stored procedure?

[Gordon Bruce]

I have a simple stored procedure which works as intended.

As soon as I add a UNION in the SELECT I get the error message 
ERROR 1064 (42000): 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 'select phon_Lvl INTO Lvl

Are UNION's currently not allowed in a stored procedure? 


mysql> delimiter //
mysql> create procedure ph()
-> BEGIN
->   DECLARE LVL Char(10);
->   select phon_Lvl INTO Lvl
->   FROM   phones
->
->   limit 1;
->   SET @Lvl:=Lvl;
-> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> call ph();
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> Select @Lvl;
+--+
| @Lvl |
+--+
| locn |
+--+
1 row in set (0.00 sec)

mysql>
mysql> drop procedure if exists ph;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> create procedure ph()
-> BEGIN
->   DECLARE LVL Char(10);
->   select phon_Lvl INTO Lvl
->   FROM   phones
->   UNION
->   Select "a" into LVL
->   limit 1;
->   SET @Lvl:=Lvl;
-> END//
ERROR 1064 (42000): 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 'select phon_Lvl INTO Lvl
  FROM   phones
  UNION
  Select "a" into LVL
  limi' at line 4
mysql> delimiter ;
mysql>


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

RE: [SPAM] - Adding data from one table to another - Bayesian Filter detected spam

[Gordon Bruce]

You probably want a multi table update assuming you are running at least
4.0.x.

Add the column(s) to A with an ALTER TABLE 

UPDATE A 
   INNER JOIN B 
   ON (...
SETA.col_name = B.col_name,
   A.col_name_2 = B.col
WHERE  ..


You can copy the join structure from the FROM  section of your SELECT
statement and paste it in the UPDATE section and move the WHERE section
of the SELECT to the UPDATE.

-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 11, 2006 11:30 AM
To: mysql@lists.mysql.com
Subject: [SPAM] - Adding data from one table to another - Bayesian
Filter detected spam

I have two tables with data on people in them.  Table A is a subset of 
table B,  However, there is data about these people in table B that is 
not in table A.  with a simple select I can do a join and get a result 
set with all the data I need to show, but what I would like to do is 
change table A so it also has one of the fields from table B.  Adding 
the field to table A is trivial , but how do I then populate that new 
field with data from the table B?

-- 
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want & 
give the gifts they want
One stop wish list for any gift, 
from anywhere, for any occasion!
http://thewishzone.com


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



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

RE: [SPAM] - Re: mysql 5 - disk bound - fixed - Email found in subject

[Gordon Bruce]

One huge problem with this approach. The new table doesn't have any of
the indexes that were present in the previous table. You may be better
off to build a script that renames the current tables followed with the
full CREATE TABLE statement(s).

Do a SHOW CREATE TABLE current table;

Then take that output and put it in a script 

RENAME TABLE current_table TO new_table_name;
CREATE TABLE .

ON 5.0 you can use PREPARED STATEMENTS if you want to control the new
table names. See 

"13.7. SQL Syntax for Prepared Statements

Beginning with MySQL 4.1.3, an alternative interface to prepared
statements is available: SQL syntax for prepared statements. This
interface is not as efficient as using the binary protocol through a
prepared statement API, but requires no programming because it is
available directly at the SQL level: "


-Original Message-
From: Pooly [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 11, 2006 9:47 AM
To: MySQL General
Subject: [SPAM] - Re: mysql 5 - disk bound - fixed - Email found in
subject

2006/1/11, George Law <[EMAIL PROTECTED]>:
> Hi All,
>
>
[snip]

>
> I have to work on an automatic way to rotate these tables every week.
> Is there an easy way with SQL to create a new table based on the
schema
> of an existing table?
>


I believe CREATE TABLE newtbl SELECT blah... is what you're after :
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
FTFM :
 You can create one table from another by adding a SELECT statement at
the end of the CREATE TABLE statement:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;


--
Pooly
Webzine Rock : http://www.w-fenec.org/

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



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

RE: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam

[Gordon Bruce]

Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's. 

Too much time looking at dump's.

-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam

If you really do mean decimal (base 10) you could use Gordon's solution 
like this:

SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;

Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+


Hope this helps.



Gordon Bruce wrote:

>If by Decimal you mesn HEXIDECIMAL you can use CONV 
>where the 1st arg is the HEX value, 
>2nd arg is From Base 
>and 3rd arg is To Base. 
>
>You will have to suround the aliases with "`'s" if you really want 
>the names to be numeric.
>
>
>mysql> select MID(CONV('A5',16,2),1,1) AS `7`,
>->MID(CONV('A5',16,2),2,1) AS `6`,
>->MID(CONV('A5',16,2),3,1) AS `5`,
>->MID(CONV('A5',16,2),4,1) AS `4`,
>->MID(CONV('A5',16,2),5,1) AS `3`,
>->MID(CONV('A5',16,2),6,1) AS `2`,
>->MID(CONV('A5',16,2),7,1) AS `1`,
>->MID(CONV('A5',16,2),8,1) AS `0` ;
>+---+---+---+---+---+---+---+---+
>| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
>+---+---+---+---+---+---+---+---+
>| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
>+---+---+---+---+---+---+---+---+
>1 row in set (0.00 sec)
>
>-Original Message-
>From: Ed Reed [mailto:[EMAIL PROTECTED] 
>Sent: Tuesday, January 10, 2006 12:16 PM
>To: mysql@lists.mysql.com
>Subject: Converting decimal to binary
>
>Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
>number to binary and have the result be returned as a separate field
for
>each bit? For example, what I'd like to do is,
>
>Select ConvertToBin(245);
>
>And have a result that looked like this
>+---+---+---+---+---+---+---+---+
>| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
>+---+---+---+---+---+---+---+---+
>| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
>+---+---+---+---+---+---+---+---+
>
>- Thanks
>
>
>
>  
>


-- 
Bill Dodson
Parkline, Inc. http://www.parkline.com
phone: 304-586-2113 x149
fax: 304-586-3842
email: [EMAIL PROTECTED]


Email Disclaimer

The information in any email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to the email
message by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or
any action or omission taken by you in reliance on it, is prohibited and
may be unlawful. If you have received an email message in error, please
notify the sender immediately by email, facsimile or telephone and
return and/or destroy the original message.

Thank you.


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



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

RE: Converting decimal to binary

[Gordon Bruce]

If by Decimal you mesn HEXIDECIMAL you can use CONV 
where the 1st arg is the HEX value, 
2nd arg is From Base 
and 3rd arg is To Base. 

You will have to suround the aliases with "`'s" if you really want 
the names to be numeric.


mysql> select MID(CONV('A5',16,2),1,1) AS `7`,
->MID(CONV('A5',16,2),2,1) AS `6`,
->MID(CONV('A5',16,2),3,1) AS `5`,
->MID(CONV('A5',16,2),4,1) AS `4`,
->MID(CONV('A5',16,2),5,1) AS `3`,
->MID(CONV('A5',16,2),6,1) AS `2`,
->MID(CONV('A5',16,2),7,1) AS `1`,
->MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)

-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary

Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field for
each bit? For example, what I'd like to do is,

Select ConvertToBin(245);

And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+

- Thanks



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




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

RE: [SPAM] - concat string and update question - Found word(s) remove list in the Text body

[Gordon Bruce]

Try this 

UPDATE people 
SETphone = CASE 
 WHEN LEFT(phone,4) = '405_' THEN MID(phone,5,20) 
 WHEN LEFT(phone,3) = '405' THEN MID(phone,4,20) 
 ELSE phone 
   END 
FROM   people 
WHERE  LEFT(phone,3) = '405'
   AND LENGTH(phone) > 7;

This way you don't accidentally replace '405' contained in the rest of
the phone number. Also, if the phone numbers contain punctuation you
will need to change the '7' in the LENGTH criteria. You will have to
replace 'people' and 'phone' with the appropriate table and column name
respectively.
-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 06, 2006 8:09 PM
To: mysql@lists.mysql.com
Subject: [SPAM] - concat string and update question - Found word(s)
remove list in the Text body

I have a table of people and their phone numbers, some have the area
code and others do not.  Everyone in this table lives in the same area
code, so I would like to remove the area code from the phone number
field.  Basically replace '(405)' or '405-' with '' is there an easy way
to do that in a query with out writing code?  I know how to do it with
code but would like an easier way if some one knows the SQL better than
I do.

-- 
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.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]

FW: Re: SQL Question

[Gordon Bruce]

Why not just use the ABS Function


update ev98nv_tm 
   set mome=ABS(b) 
where  tm.tr=tr and tm.ra=ra 
   and tm.ke=ke 
   and tm.moti=moti ;

12.4.2. Mathematical Functions
All mathematical functions return NULL in the event of an error. 

ABS(X) 

Returns the absolute value of X. 

mysql> SELECT ABS(2);
-> 2
mysql> SELECT ABS(-32);
-> 32

This function is safe to use with BIGINT values. 


-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 06, 2006 12:19 PM
To: Mester József; mysql
Subject: [SPAM] - Re: SQL Question - Bayesian Filter detected spam


- Original Message - 
From: "Mester József" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>; "mysql" 
Sent: Friday, January 06, 2006 12:07 PM
Subject: Re: SQL Question


> Hy
>
>> If you know which values are supposed to be negative, wouldn't it be 
>> easier to do updates to your data to change all of those values to 
>> negatives? That should only need to be done once. Then use the normal SQL 
>> sum() function to add all of the values together.
>
> Thank you. Actually my first thing was update but my SQL knowledge is weak 
> and I don't want to mess the database.
> I started a script which is update bad records on a copy of that database. 
> However I didn't solve the update problem.
>
> My script is in (PL/SQL):
>
> integer a;
> integer b;
> varchar tr;
> varchar ra;
> varchar ke;
> varchar moti;
>
> begin
>
> select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome
> into tr,ke,moti,a
> from ev98nv_tm tm
> where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -'
> and tm.EV like '2005'
>
> if (a >0) then
> a=b;
> b = 0- b;
> update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and 
> tm.moti=moti ;
>
> end;
>
> But it is not working. The Primary index is tr+ra+ke+moti
>
>> Rhino

I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's 
suggestion can help you do the summing as you originally wanted or perhaps 
someone else can jump in with suggestions.

Rhino 



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


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



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

RE: [SPAM] - Re: SQL Question - Bayesian Filter detected spam

[Gordon Bruce]

Why not just use the ABS Function


update ev98nv_tm 
   set mome=ABS(b) 
where  tm.tr=tr and tm.ra=ra 
   and tm.ke=ke 
   and tm.moti=moti ;

12.4.2. Mathematical Functions
All mathematical functions return NULL in the event of an error. 

ABS(X) 

Returns the absolute value of X. 

mysql> SELECT ABS(2);
-> 2
mysql> SELECT ABS(-32);
-> 32

This function is safe to use with BIGINT values. 


-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 06, 2006 12:19 PM
To: Mester József; mysql
Subject: [SPAM] - Re: SQL Question - Bayesian Filter detected spam


- Original Message - 
From: "Mester József" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>; "mysql" 
Sent: Friday, January 06, 2006 12:07 PM
Subject: Re: SQL Question


> Hy
>
>> If you know which values are supposed to be negative, wouldn't it be 
>> easier to do updates to your data to change all of those values to 
>> negatives? That should only need to be done once. Then use the normal SQL 
>> sum() function to add all of the values together.
>
> Thank you. Actually my first thing was update but my SQL knowledge is weak 
> and I don't want to mess the database.
> I started a script which is update bad records on a copy of that database. 
> However I didn't solve the update problem.
>
> My script is in (PL/SQL):
>
> integer a;
> integer b;
> varchar tr;
> varchar ra;
> varchar ke;
> varchar moti;
>
> begin
>
> select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome
> into tr,ke,moti,a
> from ev98nv_tm tm
> where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -'
> and tm.EV like '2005'
>
> if (a >0) then
> a=b;
> b = 0- b;
> update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and 
> tm.moti=moti ;
>
> end;
>
> But it is not working. The Primary index is tr+ra+ke+moti
>
>> Rhino

I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's 
suggestion can help you do the summing as you originally wanted or perhaps 
someone else can jump in with suggestions.

Rhino 



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


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



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

RE: Can this SELECT go any faster?

[Gordon Bruce]

Try this 

SELECT replace(left(history.time_sec,7),'-','') AS month 
FROM   history 
WHERE  history.account_id = 216 
GROUP BY month 
ORDER BY history.time_sec DESC;

This is what I get on 1 of my tables with no index on perm_user_ID , 80,000 
rows in the table and 7,000 rows where perm_user_ID = 'CSRB' on version 5.0.17.

mysql> SELECT replace(left(pord_Timestamp,7),'-','') AS month
-> FROM   product_order_main
-> WHERE  perm_user_ID = 'CSRB'
-> GROUP BY month
-> ORDER BY pord_Timestamp DESC;
++
| month  |
++
| 200511 |
| 200510 |
| 200509 |
| 200508 |
| 200507 |
| 200506 |
| 200505 |
| 200504 |
| 200503 |
| 200502 |
| 200501 |
| 200412 |
| 200411 |
| 200410 |
| 200409 |
| 200408 |
| 200407 |
| 200406 |
| 200405 |
| 200404 |
| 200403 |
| 200402 |
| 200401 |
| 200312 |
| 200311 |
++
25 rows in set (0.08 sec)

mysql> select count(*) from product_order_main WHERE  perm_user_ID = 'CSRB';
+--+
| count(*) |
+--+
| 7095 |
+--+
1 row in set (0.05 sec)

mysql> select count(*) from product_order_main WHERE  perm_user_ID = 'CSRB';
+--+
| count(*) |
+--+
| 7095 |
+--+
1 row in set (0.05 sec)

mysql> select count(*) from product_order_main;
+--+
| count(*) |
+--+
|80774 |
+--+
1 row in set (0.05 sec)

mysql> select version();
+---+
| version() |
+---+
| 5.0.17-nt |
+---+
1 row in set (0.00 sec)

-Original Message-
From: René Fournier [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 04, 2006 2:23 PM
To: mysql@lists.mysql.com
Subject: Can this SELECT go any faster?

Hello,

I have a table called (history) containing thousands of rows. Each  
row is UNIX time-stamped, and belong to a particular account.
I would like to know which months a particular account has been  
active. (For example, maybe one account has been active since June  
2004, so the SELECT should return every month since then.) Here's  
what I'm using:

SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month
FROM history
WHERE history.account_id = 216
GROUP BY month
ORDER BY history.time_sec DESC

Showing rows 0 - 7 (8 total, Query took 0.1975 sec)

month
200601
200512
200511
200510
200509
200508
200507
200506

This account (216) has about 8000 rows. There are Indexes for  
account_id and time_sec. I'm running MySQL 5.0.16.

When I run EXPLAIN, I am told:

id: 1
select_type: SIMPLE
table: history
type: ref
possible_keys: account_id
key: account_id
key_len: 4
ref: const  
rows: 6556
Extra: Using where; Using temporary; Using filesort


Any ideas how I can speed this up more? (I am just starting to learn  
how to improve MySQL performance but clearly have a ways to go.) Thanks.

...Rene


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




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

RE: Changing types on the fly in select queries?

[Gordon Bruce]

Generally MySQL does format conversion for you to match data types.

You can also force the CONCAT result to be integer by the following 

where concat(year,period,week) + 0 < 2007031
   ^^^

-Original Message-
From: Jay Paulson (CE CEN) [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 27, 2005 12:49 PM
To: mysql@lists.mysql.com
Subject: Changing types on the fly in select queries?

I have a strange question for you all.  I've inherated some code and the
way the code works is that I can only mess with the WHERE part of a
query.  Therefore, I was wondering if something like this would be
possible.

WHERE where concat(year,period,week) as type int < 2007031

Note that I'm trying to change the type of what the concat() is doing.
Is this even possible?  If so is it possible to do it in the WHERE?  

The reason why I think I need to do this is that 'period' is a char(2).
I have to have the leading zero for every entry into the database so I
can run my less than compare to it.  Is there a better way of doing this
than having the 'period' a char(2) type and trying to make whole
concat() a type of int() on the fly?

Thanks!



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

Is this a MySQL 5.0.x bug OR What am I missing?

[Gordon Bruce]

I have 2 almost identical SQL statements {copied except 1 is a LEFT join and 
the other is an INNER join}.
The INNER join gives me values for all of the fields. The LEFT join gives me 
NULL's for all of the prec_... {the LEFT join table} fields.
If the INNER JOIN gives me values and not an empty set then why does the LEFT 
join give me NULL's?

I have included the satements, SELECT * and CREATE TABLE for the 3 tables.
I have run this on 5.0.15 and 5.0.17.

mysql> SELECT user.user_ID, sesn.user_ID, sesn.orgn_ID, prec3.orgn_ID,
    ->    prec3.prec_Type,  prec3.prec_Level,prec3.prec_Value, 
prec3.prec_ID,
    ->    if(isnull(prec3.prec_Replace_Level),
    ->   'sesn',
    ->   prec3.prec_Replace_Level
    ->   ) as pl3
    -> FROM   users AS user
    ->    INNER JOIN sessions AS sesn
    ->    USING(user_ID
    ->  )
    ->    LEFT JOIN precedences AS prec3
    ->    ON (prec3.orgn_ID = sesn.orgn_ID
    ->    AND prec3.prec_Type = 'Phones'
    ->    AND prec3.prec_Level = 'user'
    ->    AND prec3.prec_Value = 'Primary'
    ->    AND prec3.prec_ID = 3
    ->    )
    -> where sesn.sesn_ID = 1;
+-+-+-+-+---+++-+--+
| user_ID | user_ID | orgn_ID | orgn_ID | prec_Type | prec_Level | prec_Value | 
prec_ID | pl3  |
+-+-+-+-+---+++-+--+
| AGB1    | AGB1    | AXIS    | NULL    | NULL  | NULL   | NULL   
|    NULL | sesn |
+-+-+-+-+---+++-+--+
1 row in set (0.02 sec)

mysql>
mysql>
mysql> SELECT user.user_ID, sesn.user_ID, sesn.orgn_ID, prec3.orgn_ID,
    ->    prec3.prec_Type,  prec3.prec_Level,prec3.prec_Value, 
prec3.prec_ID,
    ->    if(isnull(prec3.prec_Replace_Level),
    ->   'sesn',
    ->   prec3.prec_Replace_Level
    ->   ) as pl3
    -> FROM   users AS user
    ->    INNER JOIN sessions AS sesn
    ->    USING(user_ID
    ->  )
    ->    INNER JOIN precedences AS prec3
    ->    ON (prec3.orgn_ID = sesn.orgn_ID
    ->    AND prec3.prec_Type = 'Phones'
    ->    AND prec3.prec_Level = 'user'
    ->    AND prec3.prec_Value = 'Primary'
    ->    AND prec3.prec_ID = 3
    ->    )
    -> where sesn.sesn_ID = 1;
+-+-+-+-+---+++-+--+
| user_ID | user_ID | orgn_ID | orgn_ID | prec_Type | prec_Level | prec_Value | 
prec_ID | pl3  |
+-+-+-+-+---+++-+--+
| AGB1    | AGB1    | AXIS    | AXIS    | phones    | user   | Primary    
|   3 | locn |
+-+-+-+-+---+++-+--+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM sessions;
+-+--+--+-+-+-+-+-+-+-+
| sesn_ID | perm_user_ID | perm_usgp_ID | user_ID | usgp_ID | acct_ID | locn_ID 
| orgn_ID | sesn__Timestamp | sesn_Create |
+-+--+--+-+-+-+-+-+-+-+
|   1 | AGB1 | ADZZ | AGB1    | ADZZ    | | AXIS    
| AXIS    | 2005-12-23 08:32:26 | 2005-12-23 08:30:02 |
|   2 | AGB1 | ADZZ | AGB1    | ADZZ    | | AXIS    
| AXIS    | 2005-12-23 08:32:26 | 2005-12-23 08:30:07 |
+-+--+--+-+-+-+-+-+-+-+
2 rows in set (0.02 sec)

mysql>
mysql> SELECT * FROM users;
+-+-+-+-+--+--+---++++-+--+-+-+
| user_ID | orgn_ID | locn_ID | usgp_ID | prev_usgp_ID | user_Log_On_Name | 
user_Pass | user_FName | user_LName | user_PName | user_Active | user_Who | 
user_Timestamp  | user_Create |
+-+-+-+-+--+--+---+----+++-+--+-+-+
| AGB1    | AXIS    | AXIS    | ADZZ    | NULL | gbruce   | 
rgbjs1jc  | Ralph  | Bruce  | Gordon | Yes | AGB1 | 
2005-12-23 08:59:31 | NULL    |
+-+-+-+-

RE: [SPAM] - Re: locating ibdata1 and *.ibd files in different directories. - Bayesian Filter detected spam

[Gordon Bruce]

The following takes a little effort, but it should get you close to dump
file size.

On 5.0.x you can use Information_schema.columns to get average row
length for MyISAM tables. Take that plus the punctutation {~35 + 3* #
cols for insert per row if you enclose your columns in "'s} in the
insert statements generated by mysqldump times the number of rows and
that will give you the size of the MyISAM tables. 

For INNODB use 

mysql> select avg(length(concat( col1, col2,...))) AS Avg_Len, count(*)
-> from table ;
+--+--+
| Avg_Len  | count(*) |
+--+--+
| 107.5588 |  3514429 |
+--+--+
1 row in set (1 min 1.31 sec)

I would also use Information_schema.columns to get the column names so I
would not have to type them. 




-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 20, 2005 12:24 PM
To: mysql@lists.mysql.com
Subject: [SPAM] - Re: locating ibdata1 and *.ibd files in different
directories. - Bayesian Filter detected spam

Hello.



> symbolic links! Thats a neat solution. Question: when you say

> "symbolic links for databases" do you mean links to ibd files, ibdata1

> file, either, or something else?



I've meant symbolic links for databases. See:

  http://dev.mysql.com/doc/refman/5.0/en/symbolic-links.html



> Is mysql smart enough not to use my indices when importing until after

> the import, or should I de-activate my indices until after the import?



mysqldump from 5.0.17 distribution sets FOREIGN_KEY_CHECKS to 0; for

your version you can check this by yourself.



> Also, is there a formula of what I can expect the size of the dumped

> files to be?



For a pity, I don't know any formula, even approximate.









Nathan Gross wrote:

> On 12/20/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> 

>>Hello.

>>

>>

>>

>>Please, next time answer to the list as well.

> 

> Sorry. I didn't realize Gmail's 'reply' didn't go to the list. First

> time I noticed the 'reply to all' option in Gmail. Thanks.

> 

> 

>>As far as I know, you  can't specify the location of ibd files,
they're s=

> 

> tored in the

> 

>>database directory, however, you can use symbolic links for databases
to =

> 

> have 

> 

>>them in another place.

> 

> symbolic links! Thats a neat solution. Question: when you say

> "symbolic links for databases" do you mean links to ibd files, ibdata1

> file, either, or something else?

> 

> 

> 

>>>all databases? So the question is if I can locate the ibdata1 file
somew=

> 

> here else.

> 

>>

>>Have you tried just to change the value of innodb_home_dir to the new

>>location, and move there ibdata1 file?

> 

> And leave the original subdirs(databases) in the original place? I can

> try. This means though, that the absolute db paths are coded into the

> ibdata file.

> 

>>>ibd files. BUT, the ibdata1 file is still 7 gig and being

>>

>>

>>

>>If you want do decrease the size of ibdata1 file, you should dump all

>>your InnoDB tables, stop the server, remove all existing tablespace

>>files, configure a new tablespace, restart the server, import the dump

>>files. In such a way you'll move all your tables which are in ibdata1

>>tablespace to may ibd files in databases. See:

>>

>>  http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

> 

> Is mysql smart enough not to use my indices when importing until after

> the import, or should I de-activate my indices until after the import?

> Also, is there a formula of what I can expect the size of the dumped

> files to be?

> 

> Thank you much!

> -nat

> 



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




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



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

RE: copying data!!!

[Gordon Bruce]

Sorry the DROP TABLE Should be TEMP not table1

-Original Message-
From: Gordon Bruce [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 09, 2005 3:51 PM
To: Sachin Bhugra; mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: RE: copying data!!!

This is kind of ugly, but with the multitable limitations of 3.23 it
probably works.

CREATE TABLE TEMP 
SELECT table_1 columns except age, table_2 age
FROM   table_1 
   INNER JOIN table_2 
   USING (name);
TRUNCATE table_1; 

INSERT INTO table_1 
SELECT * 
FROM TEMP; 

DROP TABLE_1;



-Original Message-
From: Sachin Bhugra [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 09, 2005 3:00 PM
To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: Re: copying data!!!

Tnx for the reply Jimmy. I also sent another question( i know its a
very silly question for you all..but believe me i am tryin this for
past three days and not able to get it)

Pls hlp..(just give me hint in right direction, and i will try to do
the rest)

Tnx
Sachin

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




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




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

RE: copying data!!!

[Gordon Bruce]

This is kind of ugly, but with the multitable limitations of 3.23 it
probably works.

CREATE TABLE TEMP 
SELECT table_1 columns except age, table_2 age
FROM   table_1 
   INNER JOIN table_2 
   USING (name);
TRUNCATE table_1; 

INSERT INTO table_1 
SELECT * 
FROM TEMP; 

DROP TABLE_1;



-Original Message-
From: Sachin Bhugra [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 09, 2005 3:00 PM
To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: Re: copying data!!!

Tnx for the reply Jimmy. I also sent another question( i know its a
very silly question for you all..but believe me i am tryin this for
past three days and not able to get it)

Pls hlp..(just give me hint in right direction, and i will try to do
the rest)

Tnx
Sachin

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




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

RE: Format for saving date field.

[Gordon Bruce]

What is the source of the data that is displayed on the screen. If it is
a field in a MySQL table and the data type for that field is either DATE
or DATETIME then it will intsert/update without any manipulation. 

Try doing a 

SELECT datefield 
FROM   table 
Limit 15;

outside of your ASP.NET environment. {Command line, SQLYOG, Query
Browser etc.}

-Original Message-
From: Jesse Castleberry [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 10, 2005 1:49 PM
To: MySQL List
Subject: Format for saving date field.

When doing an update or insert into a database with a date field, the
format
for the data on the screen is m/d/.  However, I believe that MySQL
is
expecting it in the format of -mm-dd.  I'm using MySQL in an ASP.Net
application.  Is there an EASY way to convert to the data to a format
that
MySQL will allow either with some MySQL function, or with an ASP.NET
function?  I realize that I could "rip the data apart", and put it back
in
the same format that MySQL is looking for, but there's got to be some
easier
way.

Thanks,
Jesse


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




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

RE: Query producing default values

[Gordon Bruce]

Try this
{I wasn't sure whether cd_nature_ltr is in ligne_trans 
  or transaction. This assumes ligne_trans. If it is in 
  transaction thatn move "cd_nature_ltrsn  = 2" into the ON clause.}

SELECT CASE id_ltrsn 
 WHEN NULL THEN 0
 ELSE id_ltsrn
   END AS id_ltrsn, 
   CASE id_ltrsn
 WHEN NULL THEN NOW() 
 ELSE MAX(GREATEST(date_fin_ltrsn  + INTERVAL 1 MONTH,
CURDATE()))   
   END AS subs_start
FROM   ligne_trans  
   LEFT JOIN transaction 
   ON (transaction.id_trsn = ligne_trans.id_trans_ltrsn 
   AND transaction.id_pers_trsn = 278 
   )
WHERE  cd_nature_ltrsn  = 2 
   AND ligne_trans.id_cntxt_ltrsn = 1 
GROUP BY ligne_trans.id_cntxt_ltrsn
> >
> > When past subscipiton exixts it will produce as an example:
> >
> > id_ltrsn | subs_start
> > -
> > 79   | 2006-11-25
> >
> > When no past subscription exists I would like to get today's date as

> > a
> result
> > instead of nothing, example:
> >
> > id_ltrsn | subs_start
> > -
> > 0| 2005-11-09
-Original Message-
From: Jacques Brignon [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 09, 2005 9:19 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Query producing default values

Thanks, that makes a lot of sense.

My only problem is that I am using here a standard piece of code on the
application side and I would hate to modify it, the thing I have all
liberty to
change is the query! Reason for trying to ask the query itself to tell
me if
there is nothing in the DB!

--
Jacques Brignon

Selon [EMAIL PROTECTED]:

> Jacques Brignon <[EMAIL PROTECTED]> wrote on 11/09/2005 09:58:07
AM:
>
> > I have a query which scans a subscription databse to locte the most
> recent
> > expiration date of the subscription to a given periodical or
serviceto
> compute
> > the start date of a renewal.
> >
> > It works fine when for a given person such a subscription exists. If
> none
> > exists, as expected the query produces no results.
> >
> > Any suggestion on how to transform this query to produce a default
value
> set
> > when no past subscription exists?
> >
> > Here is the query:
> >
> > SELECT
> > id_ltrsn, MAX(GREATEST(date_fin_ltrsn  + INTERVAL 1 MONTH,
CURDATE()))
> AS
> > subs_start
> > FROM
> > ligne_trans, transaction
> > WHERE
> > transaction.id_trsn = ligne_trans.id_trans_ltrsn AND cd_nature_ltrsn
= 2
> > AND ligne_trans.id_cntxt_ltrsn = 1
> > AND transaction.id_pers_trsn = 278
> > GROUP BY
> > ligne_trans.id_cntxt_ltrsn
> >
> > When past subscipiton exixts it will produce as an example:
> >
> > id_ltrsn | subs_start
> > -
> > 79   | 2006-11-25
> >
> > When no past subscription exists I would like to get today's date as
a
> result
> > instead of nothing, example:
> >
> > id_ltrsn | subs_start
> > -
> > 0| 2005-11-09
> >
> >
> > Thanks for any help you can provide
> >
> > --
> > Jacques Brignon
> >
>
> You are asking the database to return with data it does not have. Can
you
> not detect the fact that you found no records and use that in your
> application code to supply a default date?  That would be much easier
to
> implement and maintain than any database-based solution.  The SQL can
> become quite convoluted when you start trying to simulate missing
values.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>

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




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

RE: Help optimize this simple find

[Gordon Bruce]

Is it possible to change the geocodes table to look like 

CREATE TABLE `geocodes` (
   `ip` int(10) unsigned zerofill NOT NULL default '00',
   `lat` double default NULL,
   `lon` double default NULL,
   PRIMARY KEY  (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

Then you could do 

SELECT lat,lon 
FROM   geocodes 
WHERE  ip BETWEEN 1173020467 AND 1173020467 ;


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 04, 2005 10:33 PM
To: Brian Dunning
Cc: mysql@lists.mysql.com
Subject: Re: Help optimize this simple find

Brian Dunning <[EMAIL PROTECTED]> wrote on 11/04/2005 10:36:00 PM:

> This simple find is taking 4 to 7 seconds. Way too long!! (This is a 
> geotargeting query using the database from IP2location.)
> 
> select lat,lon from geocodes where ipFROM<=1173020467 and 
> ipTO>=1173020467
> 
> The database looks like this (how IP2location recommends):
> 
> CREATE TABLE `geocodes` (
>`ipFROM` int(10) unsigned zerofill NOT NULL default '00',
>`ipTO` int(10) unsigned zerofill NOT NULL default '00',
>`lat` double default NULL,
>`lon` double default NULL,
>PRIMARY KEY  (`ipFROM`,`ipTO`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> 
> And there are 1.7 million records. Any suggestions?
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> 

I would bet that if you do an EXPLAIN on your query that you will see
that 
you wound up with a full table scan. It did this because it takes fewer 
read operations to just scan the table than if you do an indexed lookup 
for any more than about 30%  of the rows in any table. 

Can you not change the query to not use <= or =>  ??

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


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

RE: SQL Statement Conversion

[Gordon Bruce]

If your MySQL server is a *nix system than table names are case
sensitive.

SELECT A.*, 
   CASE CounselorOnly 
 WHEN 1 THEN 'Yes' 
 WHEN 0 THEN 'No' 
   END AS CO
FROM Activities A 
ORDER BY Activity 

I also just noticed, remove the "CO =" and add "AS CO" following the END
of the case statement.

-Original Message-
From: Jesse Castleberry [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 04, 2005 3:54 PM
To: MySQL List
Subject: SQL Statement Conversion

I'm converting an application from SQL Server to MySQL.  I have the
following query that I need to convert:

SELECT A.*, CO = CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No'
END
FROM Activities a ORDER BY Activity

What is the proper syntax for this in MySQL?

Thanks,
Jesse


-- 
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]

Sub Selects, Alias Names and stored procedures

[Gordon Bruce]

After reading one of the recent posts from Gobi [EMAIL PROTECTED] 
I took his successful query and modified it for one of my tables. It indeed 
produce the correct result, but in the process raised some questions.

1. Why do list_ID and acct_ID not have to be qualified with a table 
name or alias? 

mysql> SELECT list_ID, list_Name, acct_ID, list_Qty
    -> FROM   lists
    ->    INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
    ->    FROM   lists
    ->    GROUP BY acct_id
    ->    ) AS t
    ->    USING (acct_ID, list_ID)
    -> WHERE  list_Active = 'Yes'
    ->    AND cpny_ID = 'RER1'
    -> LIMIT  100,10;
+-++-+--+
| list_ID | list_Name  | acct_ID | list_Qty |
+-++-+--+
|   3 | Farm   | BA8M    |    0 |
|  10 | Woodbury   | BA8Y    |  100 |
|   2 | Brookview Heights 03-23-04 | BA9O    |  278 |
|   4 | Magnet Mailing | BABA    |  250 |
|   2 | Fall Back  | BABM    |  223 |
|   1 | Contact list   | BACF    |   71 |
|   4 | Friends/Family | BAE2    |   10 |
|   1 | St. Michael    | BAE7    |  139 |
|   2 | JS Prospects   | BAE8    |  196 |
|   1 | Home Focus     | BAE9    |   55 |
+-++-+--+
10 rows in set (0.03 sec)


2. While the subselect does work, it appears to generate a cartesian 
product. Initial guess with 5.0 and stored procedures would be that 

    CREATING TEMPORARY TABLE
    INSERT max values in temporary
    SELECT from main table joined with temporary

    would run faster and still allow this to be done with 1 statement.

However,  even though the explains would indicate that this was so {23508 * 
7354 rows for subselect VS 6060 rows for temporary table}
actual times are {0.03 for subselect VS 0.19 for temporary table} . After doing 
some playing, it is the INSERT into temporary that adds the 
time even though the table was memory resident. Trying a similar request on a 
table with 3.5M rows still favors the subselect 
{27.50 sec for subselect VS 1 min 13.91 sec for temporary table}. 

Has EXPLAIN just not caught up with SUBSELECT logic or is there something else 
going on?



mysql> EXPLAIN
    -> SELECT list_ID, list_Name, acct_ID, list_Qty
    -> FROM   lists
    ->    INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
    ->    FROM   lists
    ->    GROUP BY acct_id
    ->    ) AS t
    ->    USING (acct_ID, list_ID)
    -> WHERE  list_Active = 'Yes'
    ->    AND cpny_ID = 'RER1'
    -> LIMIT  100,10;
++-+++--+-+-+-+---+-+
| id | select_type | table  | type   | possible_keys    | key | 
key_len | ref | rows  | Extra   |
++-+++--+-+-+-+---+-+
|  1 | PRIMARY |  | ALL    | NULL | NULL    | 
NULL    | NULL    |  7354 | |
|  1 | PRIMARY | lists  | eq_ref | PRIMARY,acct_list_ID | PRIMARY | 
6   | t.acct_ID,t.list_ID | 1 | Using where |
|  2 | DERIVED | lists  | index  | NULL | PRIMARY | 
6   | NULL    | 23508 | Using index |
++-+++--+-+-+-+---+-+
3 rows in set (0.01 sec)

mysql> SELECT list_ID, list_Name, acct_ID, list_Qty
    -> FROM   lists
    ->    INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
    ->    FROM   lists
    ->    GROUP BY acct_id
    ->    ) AS t
    ->    USING (acct_ID, list_ID)
    -> WHERE  list_Active = 'Yes'
    ->    AND cpny_ID = 'RER1'
    -> LIMIT  100,10;
+-++-+--+
| list_ID | list_Name  | acct_ID | list_Qty |
+-++-+--+
|   3 | Farm   | BA8M    |    0 |
|  10 | Woodbury   | BA8Y    |  100 |
|   2 | Brookview Heights 03-23-04 | BA9O    |  278 |
|   4 | Magnet Mailing | BABA    |  250 |
|   2 | Fall Back  | BABM    |  223 |
|   1 | Contact list   | BACF    |   71 |
|   4 | Friends/Family | BAE2    |   10 |
|   1 | St. Michael    | BAE7    |  139 |
|   2 | JS Prospects   | BAE8    |  196 |
|   1 | Home Focus  

RE: [SPAM] - Query help - Bayesian Filter detected spam

[Gordon Bruce]

You might try UNION with the 1st statement pulling all products with
groupid = 0 and the 2nd pulling 1 product with groupid > 1.

-Original Message-
From: John Nichel [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 10, 2005 3:31 PM
To: MySQL List
Subject: [SPAM] - Query help - Bayesian Filter detected spam

Hi, I have this query below, and I have been pulling my hair out for the

past couple of hours trying to get it to do what I want.  As is, it 
works, but I need it to consider other conditions.  One of the columns 
in the products table is called groupid.  I need it to pull all products

with a groupid of 0 and only 1 product with a groupid > 0 (doesn't 
matter which one).  Any help will save my sanity. ;)

SELECT products.*, MIN(pricing.price) as price , products_lng.product as

product_lng, products_lng.descr as descr_lng, products_lng.full_descr as

fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant, 
IF(classes.classid IS NOT NULL,'Y','') as is_product_options, 
MIN(v_pricing.price) as v_price, products_lng.product as product_lng, 
products_lng.descr as descr_lng, products_lng.full_descr as 
fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant, 
IF(classes.classid IS NOT NULL,'Y','') as is_product_options, 
MIN(v_pricing.price) as v_price FROM products, pricing , 
products_categories, categories LEFT JOIN products_lng ON 
products_lng.productid = products.productid AND products_lng.code = 'US'

LEFT JOIN classes ON classes.productid = products.productid LEFT JOIN 
variants ON variants.productid = products.productid LEFT JOIN pricing as

v_pricing ON v_pricing.variantid = variants.variantid AND 
v_pricing.quantity = 1 AND v_pricing.membership IN ('','') WHERE 
pricing.productid=products.productid AND pricing.quantity=1 AND 
pricing.membership IN ('','') AND products.product_type <> 'C' AND 
products.product_type <> 'B' AND (pricing.variantid = 0 OR 
(variants.variantid = pricing.variantid AND variants.avail > 0)) AND 
products_categories.productid=products.productid AND 
products_categories.categoryid = categories.categoryid AND 
categories.membership IN ('','') AND 
products_categories.categoryid='412' AND (products_categories.main='Y' 
OR products_categories.main!='Y') AND products.forsale='Y' AND 
(products.avail>0 OR products.product_type NOT IN ('','N')) GROUP BY 
products.productid ORDER BY products_categories.orderby ASC, 
products.product ASC LIMIT 10, 10

-- 
John C. Nichel
KegWorks.com
716.856.9675
[EMAIL PROTECTED]

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



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

RE: how to list foreign keys

[Gordon Bruce]

IF you are on 5.0.x you can use INFORMATION_SCHEMA

 

21.1.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

 

http://dev.mysql.com/doc/mysql/en/key-column-usage-table.html

 

-Original Message-
From: Operator [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 05, 2005 3:02 PM
To: mysql@lists.mysql.com
Subject: how to list foreign keys

 

HI everybody 

 

I'm tryng to find a way to know if a field is a foreign key, by example
if I run this 

 

describe ; 

 

in the "Key" colum I got "PRI" for the primary key field, somebody know
a way to get the foreign keys ? 

 

 

Regards 

 

Daniel

 

RE: strange order by problem

[Gordon Bruce]

Try this

mysql> select distinct secname, date
-> from   optresult
-> where  secname like 'swap%'
->and date like '2005-09-2%'
-> order by if(secname like 'swap%',
-> (mid(secname,5,20)+0),
-> secname);
+--++
| secname  | date   |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5  | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10   | 2005-09-26 |
| SWAP10   | 2005-09-23 |
| SWAP10   | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-21 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
+--++
18 rows in set (0.00 sec)

-Original Message-
From: Claire Lee [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 27, 2005 2:48 PM
To: mysql@lists.mysql.com
Subject: strange order by problem

I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.

So I came up with the following query:

mysql> select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);

I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.

+--++
| secname  | date   |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5  | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10   | 2005-09-26 |
| SWAP10   | 2005-09-23 |
| SWAP10   | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++

However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.

mysql> select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname  | date   |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5  | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10   | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10   | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10   | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++


So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.

mysql> select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname  | date   |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5  | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10   | 2005-09-26 |
| SWAP10   | 2005-09-21 |
| SWAP10   | 2005-09-23 |
+--++

Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.

Claire






__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

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




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

RE: insert subquery

[Gordon Bruce]

What am I missing 

INSERT INTO table1 (column names.) 
SELECT VALUES..
FROM table2 
WHERE primary id = insert value

You will have to put in your real table name and column names.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 23, 2005 11:59 AM
To: DJ
Cc: mysql@lists.mysql.com
Subject: Re: insert subquery

DJ <[EMAIL PROTECTED]> wrote on 09/23/2005 12:49:35 PM:

> [EMAIL PROTECTED] wrote:
> 
> >DJ wrote on 09/23/2005 12:22:58 PM:
> > 
> >
> >>i want to insert a row into table1 only if the value being inserted
on 

> >>table1 exists on table2 primary id.
> >>
> >>can i do this with subquery?
> >>
> >>thanx.
> >>
> >> 
> >>
> >
> >Depending on what version MySQL you are using, probably not. A very 
robust 
> >method of doing what you propose is to allow MySQL to do it for you
by 
> >establishing a Foreign Key from table1 to table2. One drawback is
that 
> >both tables need to be InnoDB (which you may not want to support). 
> >
> >What version are you using and what is the possibility of using
InnoDB 
> >with your appliation?
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >PS: always CC the list on all responses (unless you intentionally
mean 
to 
> >take the conversation off-list)
> > 
> >
> i am using 4.1.x
> hmm.. maybe it's easier if i just check the id is in table2 before 
> inserting into table1.
> not really a big deal just looking to create shortcuts without running

> multiple queries..
> 
> 

With a foreign key defined, you only need to run one query. That's why I

mentioned it. ;-)  Your way works, too.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


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

RE: Union vs OR

[Gordon Bruce]

It's getting late on Friday, but couldn't you build a table with all of
the parameter combinations and then just join against that table?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 26, 2005 4:25 PM
To: mysql@lists.mysql.com
Subject: Union vs OR

I have a table that holds attributes for users. This is the structure:

TABLE properties (
  id int(11) NOT NULL,
  userid int(11) NOT NULL,
  attrType int(11) NOT NULL,
  attrValue text NOT NULL,
  FULLTEXT KEY propValue (propValue)
) TYPE=MyISAM;

The table is used to find people based on criteria.

A simple query:

select
userID, attrType, attrValue from properties
where
propType = 1
and
propValue= 'some value'

The problem I'm running into is that the number of attributes could be
over
50.

Would a query with many sets of

(propType = 1 and propValue= 'some value')
or
(propType = 2 and propValue= 'some other value')
or ...

work better than doing the same thing with unions?

Or does anyone have an alternate solution?

Thanks for any help!

-- Avi



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




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

RE: Compare two tables

[Gordon Bruce]

If you have the 5.0.x version of MySQL then INFROMATION SCHEMA can give
you what you want. i.e. 

SELECT a.*, b.* 
FROM   INFORMATION_SCHEMA.COLUMNS AS a 
   INNER JOIN _SCHEMA.COLUMNS AS b 
   ON (a.column_name = b.column_name) 
WHERE  a.TABLE_NAME = 'foo_1' 
   AND b.TABLE_NAME = 'foo_2'

If you look up INFORMATION SCHEMA in the documentation you will find the
table definitions to chose the columns you need for your comparison.

21. The INFORMATION_SCHEMA Information Database 
21.1. INFORMATION_SCHEMA Tables

-Original Message-
From: Alfredo Cole [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 26, 2005 9:08 AM
To: mysql@lists.mysql.com
Subject: Compare two tables

Hi:

I need to compare the structure of two tables (fields, field types,
field 
lengths, indices, etc.) to determine if they have the same schema, even
if 
the fields may be in a different order. Is there a command in mysql that
will 
do this? This will be used to determine if the tables are basically the
same, 
or if they need to be upgraded based on the table structures of a
central 
office.

Thank you.

-- 
Alfredo J. Cole
Grupo ACyC

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




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

RE: Treating Two Fields Like One

[Gordon Bruce]

I think you misunderstand how auto_increment works. Primary keys using
auto_increment are NOT row numbers.

If your table has a primary key that is an auto_increment field then
when you add a row to the table the value of the primary key of the new
row is 1 greater than the max(Value) before the row was added. 

Once added the value in the field does not change. Let's say you have 
ID  V1  V2
1   a   b
2   x   y 
3   x   u 
4   b   a 
Now you delete the row with ID = 2. The row where V1=x and V2=u still
has a value of 3 in the ID field. 

>From reading the post I think to need to look at some refernces on
handling tree/hierarchie structures in a relational table.
Here are 2 references out of many.


http://www.sitepoint.com/article/hierarchical-data-database

http://www.intelligententerprise.com/001020/celko1_1.jhtml

They should help you understand your 2nd question 
" So I need a way to distinguish one leo from the other."

-Original Message-
From: David Blomstrom [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 24, 2005 2:31 PM
To: mysql@lists.mysql.com
Subject: Re: Treating Two Fields Like One

--- Peter Brawley <[EMAIL PROTECTED]> wrote:

"As you note, the names [of animal taxons] aren't
guaranteed to be unique, or to stay the same . . .
 
> One way out is to give every table an
> auto-incrementing integer PK, and 
> use those keys, which will never change, to mark
> parent-child relationships.

I wanted to follow up on this. I can easily substitute
integers from my primary key for names, but how do I
substitute them for parents? For example:

ID | NAME | PARENT
10 | Canidae | Carnivora
11 | Canis | Canidae
12 | Vulpes |Canidae

I can easily replace Canis with 11, Vulpes with 12.
But they both have the same family - Canidae, which
translates as 10. I could create a new field and
manually, like this:

ID | NAME | PARENT | PARENTID
10 | Canidae | Carnivora | 9
11 | Canis | Canidae | 10
12 | Vulpes |Canidae | 10

But if I add or delete a row, the numerals in my
primary key will change, messing up the values in
PARENTID.

Along similar lines, I have another question...

Consider the database table code below, which displays
animal names (representing all taxonomic heirarchies)
in a child-parent relationship:

ID | NAME | PARENT
1 | Mammalia | (NULL)
2 | Carnivora | Mammalia
3 | Canidae | Carnivora
4 | Canis | Canidae
5 | leo | Canis
6 | Felidae | Carnivora
7 | Panthera | Felidae
8 | leo | Panthera

Rows 5 and 8 represent identical species names, leo.
If I type http://geozoo/stacks/leo/ into my browser,
it defaults to Mammalia > Carnivora > Canidae > Canis
> leo, rather than the lion, Mammalia > Carnivora >
Felidae > Panthera > leo

So I need a way to distinguish one leo from the other.

Would it be possible to somehow combine my
auto-incrementing primary key with the field Name,
converting leo / leo to 5leo / 8leo?

There are two things I'd have to deal with...

1. I'd need to weed the numerals out of the display,
which should look like this...

http://geozoo/stacks/leo/";>leo

not this...

http://geozoo/stacks/8leo/";>8leo

2. The numerals would have to be fluid, as I will be
adding and deleting rows. Thus, the lion could be 8leo
one day and 9leo the next.

I can take this to a PHP forum to learn how to
implement it. But I thought someone on this forum
might tell me if it can be done in the first place.

Thanks.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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




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

RE: Date arithmetic: 2005-08-31 - 1

[Gordon Bruce]

Do You know about INTERVAL?

 

 Use it in an exprecssion or funtion as 

 

 

..INTERVAL expr type 

where expr is any numerical value

 

*   The INTERVAL keyword and the type specifier are not case
sensitive. 

The following table shows how the type and expr arguments are related: 

type Value 

Expected expr Format 

MICROSECOND

MICROSECONDS 

SECOND

SECONDS 

MINUTE

MINUTES 

HOUR

HOURS 

DAY

DAYS 

WEEK

WEEKS 

MONTH

MONTHS 

QUARTER

QUARTERS 

YEAR

YEARS 

SECOND_MICROSECOND

'SECONDS.MICROSECONDS' 

MINUTE_MICROSECOND

'MINUTES.MICROSECONDS' 

MINUTE_SECOND

'MINUTES:SECONDS' 

HOUR_MICROSECOND

'HOURS.MICROSECONDS' 

HOUR_SECOND

'HOURS:MINUTES:SECONDS' 

HOUR_MINUTE

'HOURS:MINUTES' 

DAY_MICROSECOND

'DAYS.MICROSECONDS' 

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS' 

DAY_MINUTE

'DAYS HOURS:MINUTES' 

DAY_HOUR

'DAYS HOURS' 

YEAR_MONTH

'YEARS-MONTHS' 

 

 

 

mysql> select min(addr_id) from addresses;

+--+

| min(addr_id) |

+--+

|2 |

+--+

1 row in set (0.00 sec)

 

mysql> select now() + INTERVAL min(addr_ID) Day from addresses;

+---+

| now() + INTERVAL min(addr_ID) Day |

+---+

| 2005-08-25 15:38:15   |

+---+

1 row in set (0.00 sec)

 

mysql> select now()

-> ;

+-+

| now()   |

+-+

| 2005-08-23 15:38:31 |

+-+

1 row in set (0.00 sec)

 

-Original Message-
From: Barbara Deaton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 23, 2005 2:37 PM
To: mysql@lists.mysql.com
Subject: Date arithmetic: 2005-08-31 - 1

 

All,

 

I know MySQL comes with all sorts of wonderful functions to do date
arithmetic, the problem is the context that my application is being
called in I don't know if a user wants me to add or subtract days.  I'm
just given the number of days that need to be either added or subtracted
from the date given.

 

So for example, if your table was

 

mysql> select * from dtinterval;

+

| datecol  

+

2005-09-01

2005-08-30

2005-08-31 

+--

 

a user could enter:

 

select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;

 

Which is our applications SQL, my part of the product is only give the
value 1, I have to transform that into something MySQL will understand
as 1 day and then pass that back into the SQL statement to be passed
down to the MySQL database.  I transform our applications SQL into 

 select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) =
'1974-12-04'

 

I know that just doing the -1 is wrong, since "select '2005-08-31' - 1
and that just gives me a year

 

mysql> select '2005-08-31' - 1;

+--+

| '2005-08-31' - 1 |

+--+

| 2004 |

+--+

 

What do I need to translate the 1 into in order to get back the value
'2005-08-30' ?

 

Thanks for your help.

Barbara

 

-- 

MySQL General Mailing List

For list archives: http://lists.mysql.com/mysql

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

 

 

RE: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key

[Gordon Bruce]

If you want to have all values except the primary key be the same and
say your is foo_ID
You can simply do 
INSERT INTO foo 
  (foo_ID... {rest of columns list}) 
SELECT new primary key value,
   {rest of columns list}
FROM   foo 
WHERE  foo_ID = {primary key value of row you want to copy}

If your PRIMARY KEY is an auto_increment field, just omit foo_ID from
the columns list in both the INSERT and SELECT.

-Original Message-
From: suomi [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 19, 2005 7:08 AM
To: mysql@lists.mysql.com
Subject: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key

Hi listers
I once asked if there is an SQL syntax permitting to copy a row in the 
same table. I got no answer, so there is no such syntax.

now i meant to have found a work-around using (see subject).

problem is, that when i do a SELECT * ... INTO OUTFILE .. i will also 
catch the PRIMARY KEY column if there is one and the LOAD DATA INFILE 
... of this file will fail because of duplicate keys. i tried to use the
FOREIGN_KEY_CHECKS=0 but obiousely this works on foreign keys not on the

primary key.

certainly, i can very very clumsily construct a SELECT at1, ... atn INTO

  OUTFILE statement which selects all columns except the primary key.

the REPLACE and IGNORE constructs are not what i want either, because i 
want to add a row in any case, not replace an existing one nore ignore 
the action.

is there a more elegant way then the clumsy making of an attr list, 
which includes alle columns except the primary key column?

thanks very much for your interest and understanding.

suomi

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




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

RE: query

[Gordon Bruce]

You can use INTERVAL i.e.

Lets say you have a table 

mysql> CREATE TABLE foo (bar int(14), fdate date );
Query OK, 0 rows affected (0.27 sec)

mysql> INSERT INTO foo 
 > VALUES (1, now()),
  (25,now() - INTERVAL 1 DAY),
  (15,now() - INTERVAL 2 DAY);

mysql> SELECT f1.bar - f2.bar AS diff , f2.fdate
-> FROM   foo AS f1
->INNER JOIN foo AS f2
->ON (f1.fdate = f2.fdate + INTERVAL 1 DAY);
+--++
| diff | fdate  |
+--++
|  -24 | 2005-08-15 |
|   10 | 2005-08-14 |
+--++
2 rows in set (0.00 sec)

-Original Message-
From: Octavian Rasnita [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 16, 2005 7:51 AM
To: mysql@lists.mysql.com; Felix Geerinckx
Subject: Re: query

Hi,

I define the previous record by date. For each record corresponds a date
which is unique and the previous record is that that contains the
yesterday
date.

Thank you.

Teddy

- Original Message - 
From: "Felix Geerinckx" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, August 16, 2005 2:45 PM
Subject: Re: query


> On 16/08/2005, "Octavian Rasnita" wrote:
>
> > I want to create a query that selects the diference between the
value
> > of a field from the current record and the value of the same field
> > from the previous record.
>
> How do you define "current record" and "previous record"?
> (relational databases are not spreadsheets)
>
> -- 
> felix
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


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




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

RE: Index - max key length is 1024 bytes

[Gordon Bruce]

If you are on a version prior to 4.1.2 the max index size is 500 bytes 
{not sure why the error mentions 1024}

>From section 14.1 of documention

The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can
be changed by recompiling. For the case of a key longer than 250 bytes,
a larger key block size than the default of 1024 bytes is used.

-Original Message-
From: javabuddy [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 12, 2005 12:30 PM
To: mysql@lists.mysql.com
Subject: Index - max key length is 1024 bytes

I am trying to create an index with multiple fields. 

The sixe of each of the column is listed and thier sum is 560bytes. But
when I try to create an index with the colums, it complains on the size
exceeded 1024 bytes. Below is the query and the size of each..

create index selectTechnologyClubsThread_idx on content
(club_id, date_update, subject, message_id, id, date_published,
comment_count_d, display_usr_name_d, short_content)

COLUMN_NAME TYPESIZE
club_id Bigint  8
date_update Bigint  8
Subject Varchar(120)121
message_id  varchar(120)121
Id  Bigint  8
date_published  Datetime8
comment_count_d Int 4
short_content   Varchar(250)251
display_usr_name_d  Varchar(30) 31

TOTAL : 560

Any sort of help would be great

- javabuddy




People are conversing... without posting their email or filling up their
mail box. ~~1123867827435~~
roomity.com http://roomity.com/launch.jsp No sign up to read or search
this Rich Internet App




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




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

RE: JOIN QUERY -> UPDATE ... help?!

[Gordon Bruce]

Multi Table UPDATES are first supported in 4.0.x

-Original Message-
From: Brendan Gogarty [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 09, 2005 12:16 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: RE: JOIN QUERY -> UPDATE ... help?!


"Brendan Gogarty" <[EMAIL PROTECTED]> wrote on 08/09/2005
05:30:51 AM:

> Hi,
> We are running mysql  3.23.58 and I want to do a query with joins
> from two tables and then insert the
> results into the column of a third. This appears to be harder than I
> realised with this version of mysql and I am banging my head against a
> wall. Please Help!
> ok first query.
 [snip]
> any ideas?
> 
> 

Start from here: 
http://dev.mysql.com/doc/mysql/en/update.html 

Updates *are* allowed to use JOINED tables as the "thing to be updated".
Which means that an UPDATE statement can look VERY MUCH like a SELECT
statement turned on it's head. In your case, I think you are trying to
figure out how to flip this: 

select link_ID,of_group
FROM
page_elements
LEFT JOIN pages
   ON page_elements.link_to=pages.page_ID
LEFT JOIN links_DB_bk
   ON page_elements.link_ID=links_DB_bk.link_ID
WHERE content_type='text' 

into this (while adding the `links_db` table into the mix: 

UPDATE links_db 
INNER JOIN page_elements 
ON page_elements.link_ID=links_DB.link_ID
LEFT JOIN pages
   ON page_elements.link_to=pages.page_ID
LEFT JOIN links_DB_bk
   ON page_elements.link_ID=links_DB_bk.link_ID 
SET <*** see note***> 
WHERE content_type='text'; 


*** note:  your SET clause can reference ANY column from ANY table
defined in your UPDATE clause. You are not limited to just changing one
table at a time. Just make sure you properly identify the columns you
want to get data from and which ones you want to set. Now, you didn't
say exactly what you wanted to update with what or I would have filled
in more of the SET clause. 

If you want to flip a SELECT ... GROUP BY statement into an UPDATE
statement, you have to go through a temporary table first. That is
because the GROUP BY eliminates any one-to-one row-to-value mappings
99.9% of the time. There is no UPDATE ... GROUP BY  command for any
RDBMS that I know of. However, if you save the results of the
SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE
statement just like any other data. 

Let me know if you run into any more issues and I can help you work it
out. 

 
Hi Shaun,
 
I'm afraid after a few hours of testing various things it doesn't work.
I am pretty sure its a version issue as even the simplest query such as 
UPDATE links_DB
LEFT JOIN
page_elements
SET links_DB.in_group=0
 
brings up an error
' 

MySQL said: 


You have an error in your SQL syntax near 'LEFT  JOIN page_elements SET
links_DB.in_group = 0' at line 1
'
cheers,
brendan.
 
 




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

RE: top one row

[Gordon Bruce]

This is out of the MySQL class and is called the Max-Concat trick. 


What is the biggest country in each continent?
"The Rows Holding the Group-wise Maximum of a Certain Field"

MySQL> Select Continent,
->  SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS
Country,
-> 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS
Population
-> From Country
-> Group By Continent
-> Order By Population DESC;
+---+--+
+
| Continent | Country  |
Population |
+---+--+
+
| Asia  | China|
1277558000 |
| North America | United States|
278357000 |
| South America | Brazil   |
170115000 |
| Europe| Russian Federation   |
146934000 |
| Africa| Nigeria  |
111506000 |
| Oceania   | Australia|
18886000 |
| Antarctica| South Georgia and the South Sandwich Islands |
0 |
+---+--+



It looks ugly but what you have to do is tie the data you want together
and let the max work on the collection and then split it back out again
in the display. 

So in your case 

SELECT col1, 
   LEFT(MAX(CONCAT(LPAD(col2,10,'0'),col3)),10) + 0 AS col2, 
   MID((MAX(CONCAT(LPAD(col2,10,'0'),col3)),11,50) + 0 As col3
FROM sample 
GROUP BY col1


-Original Message-
From: Kemin Zhou [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 05, 2005 12:38 PM
To: mysql@lists.mysql.com
Subject: top one row

I have a simple table

col1  col2col3
A  2  3
A  100  70
A  100080
B20   90
B7080


To select the top one row for each unique value of col1

select distinct on (col1), col1, col2, col3
from table
order by col1, col2 desc, col3 desc;

What I want is
A  1000 80
B   70 80

How do you do it in mysql?

Kemin



-- 
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]

Number of SQL Queries curiosity

[Gordon Bruce]

We have a relatively small web site running 4.0.20 on a Dell 2850
running RedHat. 

We rarely see any SQL statements visible when we run SHOW PROCESSLIST
and typically use  30 - 40 concurrent connections.

 

The Number of SQL Queries graph in MySQL Administrator usually is in the
0 to 10 range with occaisional spikes to 40 - 50.

 

Lately I have been noticing the Max value in the Number of SQL Queries
setting at 2,000 +. This happens maybe once or twice a day and I have
only been looking at the display when it happened 1 time. There does not
seem to be any unusal difference in the rest of the graphs, page hits on
the web site, network traffic etc. Nothing seems to be impacted when
this activity occurs. I just don't understand what could cause this kind
of activity on the server with our web site usage profile. We do have
some people using access through ODBC, but I have not been able to
recreate the event.

 

Does anyone have any ideas on what could cause this?

Shouild I be concerned?

RE: advanced group by

[Gordon Bruce]

Something like this 

SELECT CompanyName, 
   WhatToShip, 
   SUM(IF(TrackingNumber = '', 
  IF(SerialNumber = '', 
 1,
 0),
  0)
   ) AS READY, 
   SUM(IF(TrackingNumber <> '', 
  IF(SerialNumber = '', 
 1,
 0),
  0)
   ) AS Almost, 
   SUM(IF(TrackingNumber <> '', 
  IF(SerialNumber <> '', 
 1,
 0),
  0)
   ) AS Done 
FROM   shipments 
GROUP BY 1,2

-Original Message-
From: James M. Gonzalez [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 03, 2005 5:59 AM
To: mysql@lists.mysql.com
Subject: advanced group by 

Hello list! little GROUP BY problem here:

 

Table 'shipments'

 

ID  int(10)

CompanyName  char(50)

WhatToShip  char(50)

TrackingNumber  char(50)

SerialNumber  char(50)

 

I would like to obtain the following results:

 

CompanyName - WhatToShip   -  Ready - Almost - Done

 Foo-  car   - 26   -  2-
23 

 Foo-elephant  - 43  -  0-   15

 Foo-acuarium - 12  -  6-   47

 Bar- mobile- 9-  0-
52

 Bar- fan - 15  -  4-
43

 

 

Ready: items with empty TrackingNumber and empty SerialNumber

Almost: items with popullated TrackingNumber AND empty SerialNumber

Done: items with popullated TrackingNumber and popullated SerialNumber

 

I have been reading around and trying lots of things. I believe the
answer lies on how to group by an empty field. This means, I believe I
can make this work if I find a way to group by a field's emptiness or
not, instead of the actual content. 

 

Google is tired of seeinf me search around for ' advanced "grouping by"
' and so on and on. 

 

Any help will be greatly apprecieted. (Im begging for help)

 

James




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

RE: Renaming a database

[Gordon Bruce]

A database in MySQL is simply a directory. 

 

So just rename the directory with appropriate tool for your platform. 

On my test box this becomes

 

mysql> show databases;

++

| Database  
|

++

| information_schema |

|
lois  
|

| mailprint  |

|
mysql 
|

|
test  
|

++

5 rows in set (0.22 sec)

 



 

mysql> show databases;

++

| Database  
|

++

| information_schema |

|
lois  
|

| mailprint  |

|
mysql 
|

|
test1 
|

++

5 rows in set (0.00 sec)

 

-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 18, 2005 11:02 AM
To: 'Mysql '
Subject: Renaming a database

 

How do I rename a database? The help online is pretty ambigous.

 

-- 

Power to people, Linux is here.

 

RE: Impossible join?

[Gordon Bruce]

Here is one way. 

Some time ago I set up a table named count with one field named count
and built 5000 rows of incrementing values . 


I think I originally populated it by originally createing it with a 2nd
field 

CREATE TABLE `count` (
  `count` int(10) unsigned NOT NULL auto_increment, 
  `addr_ID` int(10) NULL,  this field does not really
matter 
  PRIMARY KEY  (`count`)
) ENGINE=InnoDB DEFAULT CHARSET=;

and then doing something like 

INSERT INTO count(addr_ID) 
SELECT  addr_ID <<< any primary key out of any table with > 5000
entries
FROM   addresses
LIMIT 5000;

and then droping the 2nd field.

Just put an INSERT in front of the select and set the value = to the #
of dates you want to populate and the set value to 1 day pior to where
you want to start. 

mysql> set @d:='2004-12-31 00:00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select  @d:[EMAIL PROTECTED] + interval 1 day as date from count where 
count <=
10;
+-+
| date|
+-+
| 2005-01-01 00:00:00 |
| 2005-01-02 00:00:00 |
| 2005-01-03 00:00:00 |
| 2005-01-04 00:00:00 |
| 2005-01-05 00:00:00 |
| 2005-01-06 00:00:00 |
| 2005-01-07 00:00:00 |
| 2005-01-08 00:00:00 |
| 2005-01-09 00:00:00 |
| 2005-01-10 00:00:00 |
+-+
10 rows in set (0.00 sec)
-Original Message-
From: Jonathan Mangin [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 18, 2005 8:03 AM
To: Michael Stassen
Cc: mysql@lists.mysql.com
Subject: Re: Impossible join?


> Jonathan Mangin wrote:
>
>> Hello all,
>>
>> I'm storing data from a series of tests throughout each
>> 24-hour period.  I thought to create a table for each test.
>> (There are six tests, lots more cols per test, and many
>> users performing each test.)
>
> But each test is performed no more than once per day by a given user?

Correct.
>
>> select test1.date, test1.time, test2.date, test2.time from
>> test1 left join test2 on test2.date=test1.date where
>> test1.date between '2005-07-01' and '2005-07-16' and
>> uid='me';
>
> Something is strange here.  Doesn't uid exist in both tables?  I'll
assume 
> it does.

Oops. Also correct.
>
>> ++--++--+
>> | date   | time | date   | time |
>> ++--++--+
>> | 2005-07-13 | 6:30 | 2005-07-13 | 7:30 |
>> | 2005-07-14 | 6:32 | 2005-07-14 | 7:45 |
>> | 2005-07-15 | 6:30 | 2005-07-15 | 7:42 |
>> | 2005-07-16 | 6:35 | NULL   | NULL |
>> ++--++--+
>>
>> Is there a join, or some other technique, that would
>> return (nearly) these same results if test1 (or any test)
>> has not been performed?  Using 4.1.11.
>>
>> TIA,
>> Jon
>

[ SNIP! ]


> A better solution would be to add a table:
>
>   CREATE TABLE `testdates` (`date` date default NULL,
>  UNIQUE KEY `date_idx` (`date`)
>);
>
> Insert one row into testdates for each day.  Now you can use something

> like this:
>
>   SELECT testdates.date, test1.time AS 'Test 1', test2.time AS 'Test
2'
>   FROM testdates
>   LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me'
>   LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me'
>   WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16';
>
> ++--+--+
> | date   | Test 1   | Test 2   |
> ++--+--+
> | 2005-07-11 | NULL | NULL |
> | 2005-07-12 | NULL | 07:28:00 |
> | 2005-07-13 | 06:30:00 | 07:30:00 |
> | 2005-07-14 | 06:32:00 | 07:45:00 |
> | 2005-07-15 | 06:30:00 | 07:42:00 |
> | 2005-07-16 | 06:35:00 | NULL |
> ++--+--+
> 6 rows in set (0.01 sec)
>
> Much better, don't you think?  This generalizes pretty well, too.
>
>   SELECT testdates.date,
>  test1.time AS 'Test 1',
>  test2.time AS 'Test 2',
>  test3.time AS 'Test 3',
>  test4.time AS 'Test 4'
>   FROM testdates
>   LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me'
>   LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me'
>   LEFT JOIN test3 on testdates.date = test3.date AND test3.uid = 'me'
>   LEFT JOIN test4 on testdates.date = test4.date AND test4.uid = 'me'
>   WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16';
>
> ++--+--+--+--+
> | date   | Test 1   | Test 2   | Test 3   | Test 4   |
> ++--+--+--+--+
> | 2005-07-11 | NULL | NULL | NULL | 08:12:00 |
> | 2005-07-12 | NULL | 07:28:00 | 07:14:00 | 08:14:00 |
> | 2005-07-13 | 06:30:00 | 07:30:00 | 07:16:00 | 08:29:00 |
> | 2005-07-14 | 06:32:00 | 07:45:00 | 07:14:00 | 08:26:00 |
> | 2005-07-15 | 06:30:00 | 07:42:00 | 07:19:00 | NULL |
> | 2005-07-16 | 06:35:00 | NULL | NULL | NULL |
> ++--+--+--+--+
> 6 rows in set (0.00 sec)
>
> Michael
>
I'm guessing thi

RE: Count(*)

[Gordon Bruce]

select count(distinct ordr_ID) from store 

-Original Message-
From: Gana [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 07, 2005 3:01 PM
To: mysql@lists.mysql.com
Subject: Count(*)

select count(*) from store group by orederId.

For the above sql, I am not getting the count of unique order ids..

help!!

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




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

RE: question about field length for integer

[Gordon Bruce]

If you really need more than 20 digits of accuracy and can move to 5.0.3
+ 
you can use the Decimal data type without losing precision.

This is out of Chapter 23. Precision Math 

The maximum value of 64 for M means that calculations on DECIMAL values
are accurate up to 64 digits. This limit of 64 digits of precision also
applies to exact-value numeric literals, so the maximum range of such
literals is different from before. (Prior to MySQL 5.0.3, decimal values
could have up to 254 digits. However, calculations were done using
floating-point and thus were approximate, not exact.) This change in the
range of literal values is another possible source of incompatibility
for older applications. 

Values for DECIMAL columns no longer are represented as strings that
require one byte per digit or sign character. Instead, a binary format
is used that packs nine decimal digits into four bytes. This change to
DECIMAL storage format changes the storage requirements as well. Storage
for the integer and fractional parts of each value are determined
separately. Each multiple of nine digits requires four bytes, and the
"leftover" digits require some fraction of four bytes. For example, a
DECIMAL(18,9) column has nine digits on each side of the decimal point,
so the integer part and the fractional part each require four bytes. A
DECIMAL(20,10) column has 10 digits on each side of the decimal point.
Each part requires four bytes for nine of the digits, and one byte for
the remaining digit. 


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 27, 2005 10:34 AM
To: Eko Budiharto
Cc: mysql@lists.mysql.com
Subject: Re: question about field length for integer

Eko Budiharto <[EMAIL PROTECTED]> wrote on 06/26/2005 11:02:30 AM:

> Hi,
> is there anyway that I can have more than 20 digits for integer 
> (bigInt)? If not, what I can use for database index?

BIGINT UNSIGNED can range from 0 to 18446744073709551615
(http://dev.mysql.com/doc/mysql/en/numeric-types.html)

Are you actually saying that you have a database with more than 1.8e+19 
records in it? I don't think you do. I think you are combining several 
pieces of information into something that looks like a number and it's 
exceeding the storage limits of even BIGINT.

What you have is actually a "good idea" but you are physically limited
by 
the capacity of the column types available. In this case if you cannot 
create all of your key values so that they look like numbers smaller
than 
18446744073709551615, it can't fit into a BIGINT UNSIGNED column.

You do have some options:
a) change the way you create your server keys so that they fit in the 
value allowed
b) use a character-based column to store your server key values
c) use some other value to identify your servers (IP address, for
example)
d) create a table of server keys:

CREATE TABLE server (
ID int auto_increment
, name varchar(25) not null
, ip int unsigned
, ... (any other fields you could define to describe this
server)
, PRIMARY KEY (ID)
, UNIQUE(name)
)

Then, refer to your servers using server.id instead of your composited 
key.

e) ...? (I am sure there are more ideas from others on the list)

To answer your literal question: No, MySQL cannot store integer values 
that contain more than 20 digits. Sorry!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


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

RE: Intersting MySQL / Access Issue

[Gordon Bruce]

When I have seen this error it was caused by a field defined in the
MySQL database as NOT NULL in the Create table and the value in Access
is NULL or usually for us an empty field in EXCEL which is appears to be
intreped as NULL when you do a PASTE APPEND. 

Ours is often times a datetime field but I don't think its limited to
data time fields defined as NOT NULL. I have not really found an easy
wasy to find the offending field except by process of elimination.

Hope this helps.



-Original Message-
From: Edward Maas [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 21, 2005 10:31 PM
To: mysql@lists.mysql.com
Subject: Intersting MySQL / Access Issue

Dear Community,

My team and I have been experiencing an interesting mysql error 
during the past few weeks of testing.  Here is the scenario we are 
trying to accomplish.  We are essentially working to use MsAccess as a 
windows client for a linux based mysql databases.  We have installed 
myODBC 3.51 and are using that for communication.  We seem to be able to

create a linked table just find and select queries work great.

The problem arises when we try to update or insert data.  Updates 
yield the following error:

"You Tried to assign the Null value to a variable that is not a 
Variant data type." 

 From my searching, I was unable to find how to set fields to variant 
data types.  Secondly, I am not sure which field is causing the error.  
The second issue was with inserts.  On insert of just one field (none 
are required other than the primary key), ALL of the fields of type text

are set to NULL.  This is particularly odd and occurs also in the mysql 
command line utilty.

If anyone has any ideas or experience, please send emails to 
[EMAIL PROTECTED]  We will definitely summarize the solution for the 
educaitonal purposes of the list.  Again many thanks in advance.

Sincerely,,
Ed Maas


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




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

RE: Backup database with MyISAM and InnoDB tables together

[Gordon Bruce]

There are 3 things [that are exclusivly MyISAM}.

Full Text index
autoincrement column as the last column in a multi column primary key
MERGE tables

Tables  which don't require these features can be INNODB tables. We have
a few tables that use these, otherwise we are exclusively INNODB.

There is another way to do backups. It's what we use.

Capture the file names in your database
Extract the .frm files and build "select into outfile" and coresponding
"load data infile" statements for each file name {i.e.table} 

Sample
select * into outfile '/usr/data/mailprint/day/user.txt' from
mysql.user; 
load data infile 'user.txt' ignore into table user; 

put the select statements in a file with flush logs before and after the
set of select statements

run the file through a CRON as mysql -h ...< filename 

Now we have a text file for each table in a directory which we can
zip/tar, move to a different machine snd selectively restore the tables
via the load data commands, restore a single table in a "test" database
and recover/rebuild a specific tabel in the live database, etc.

Our 4GB database takes < 5 minutes to save every night. A full restore
takes <30 minutes including moving the data files to the right place. We
keep 1 month of the nightly copies and day 1 of each month for a year.
We have development, stage and live servers and started doing this to
give us better granularity for selectively synching tables or parts of
tables. {I need these 500 rows from this table to move the dev project
to staging}. It has also been invaluable in the time when a developer
was on the "wrong" server and inadvertantly corupted an entire column of
the user table. We did not want to take down the site and do a
restore/rollforward to right before the "stupid" command was executed.
We just needed to fix the data in this one column in one table.

Sorry I started to ramble.

Oh I almost forgot, we also periodically dump the structure with 

mysqldump --no-datato capture the structure. 

Our table defintitions are relatively stable so we don't do it every
night. You could put it in the cron job to do it with the backup.



-Original Message-
From: Scott Plumlee [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 17, 2005 12:36 PM
To: mysql@lists.mysql.com
Subject: Re: Backup database with MyISAM and InnoDB tables together

 >
 > -Original Message-
 > From: Scott Plumlee [mailto:[EMAIL PROTECTED]
 > Sent: Friday, June 17, 2005 10:21 AM
 > To: mysql@lists.mysql.com
 > Subject: Backup database with MyISAM and InnoDB tables together
 >
 > I'm not clear on best practice to use on a database containing both
 > MyISAM and InnoDB tables.  For the MyISAM tables, it seems better to
use
 >
 > mysqldump --opt, thus getting the --lock-tables option, but for the
 > InnoDB the --single-transaction is preferred.  Since they are
mutually
 > exclusive, is there a best practice to get consistent state of the
 > tables when the database dump is performed?
 >
 > Would `mysqldump --opt --skip-lock-tables --single-transaction` be
best
 > for a database that is mostly InnoDB tables, but does have a few
MyISAM
 > tables?
 >
 > WOuld I be better off locking the database from any updates/inserts,
and
 >
 > specifying particular commands for individual tables?
 >
 > Any advice appreciated, including RTFMs with links.
 >
 >
 > Gordon Bruce wrote:
> If you are runing binary log and do a 
> 
> FLUSH LOGS 
> mysqldump --opt --skip-lock-tables MyISAM table names
> FLUSH LOGS 
> mysqldump --opt --single-transaction INNODB table names
> 
> You have a recoverable state with the combination of the mysqldump
file
> and the binary log file that was started by the 1st FLUSH LOGS
command.
> The recovered database wil be restored to the date time that the 2nd
> FLUSH LOGS command was issued instead of the start time of the backup,
> but you won't have to lock all of your tables and it wil give you a
> consistent state across a mixed INNODB MyISAM environment. 
> 
> The downside is 
>   -you have 3 files to deal with
>   -you have to maintain the table names in the mysqldump commands
>   -you have a small risk of a change ocurring in the MyISAM 
>tables between time the 2nd FLUSH LOGS is executed and the 2nd 
>mysqldump command is executed

Thanks for the tip.  I haven't looked into binary logs too much, just 
learned about them the other day when I had to correct my own mistake 
and restore a table.

Is is best practice to go with tables of all one sort to allow for 
consistent state when doing backups like this, or are mixed tables the 
norm in most databases?  I went with the InnoDB in order to not have to 
do row level locking on the tables as transactions were perfo

RE: Backup database with MyISAM and InnoDB tables together

[Gordon Bruce]

If you are runing binary log and do a 

FLUSH LOGS 
mysqldump --opt --skip-lock-tables MyISAM table names
FLUSH LOGS 
mysqldump --opt --single-transaction INNODB table names

You have a recoverable state with the combination of the mysqldump file
and the binary log file that was started by the 1st FLUSH LOGS command.
The recovered database wil be restored to the date time that the 2nd
FLUSH LOGS command was issued instead of the start time of the backup,
but you won't have to lock all of your tables and it wil give you a
consistent state across a mixed INNODB MyISAM environment. 

The downside is 
-you have 3 files to deal with
-you have to maintain the table names in the mysqldump commands
-you have a small risk of a change ocurring in the MyISAM 
 tables between time the 2nd FLUSH LOGS is executed and the 2nd 
 mysqldump command is executed

-Original Message-
From: Scott Plumlee [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 17, 2005 10:21 AM
To: mysql@lists.mysql.com
Subject: Backup database with MyISAM and InnoDB tables together

I'm not clear on best practice to use on a database containing both 
MyISAM and InnoDB tables.  For the MyISAM tables, it seems better to use

mysqldump --opt, thus getting the --lock-tables option, but for the 
InnoDB the --single-transaction is preferred.  Since they are mutually 
exclusive, is there a best practice to get consistent state of the 
tables when the database dump is performed?

Would `mysqldump --opt --skip-lock-tables --single-transaction` be best 
for a database that is mostly InnoDB tables, but does have a few MyISAM 
tables?

WOuld I be better off locking the database from any updates/inserts, and

specifying particular commands for individual tables?

Any advice appreciated, including RTFMs with links.

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




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

RE: alter only an enum label

[Gordon Bruce]

The ALTER TABLE is going to "copy" the entire table when it executes the
ALTER TABLE so it will take some time. Depends on your server, diaks,
table type etc.. 

One alternative might be to do a 

SELECT a, enumcolumn INTO OUTFILE 'x' FROM tablename; 
TRUNCATE tablename; 
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c')
DEFAULT "a" NOT NULL;
LOAD DATA INFILE 'x' INTO TABLE tablename;

I know this seems obtuse, but load data infile and select into outfile
seem to run very fast and for what ever reason may just be faster than
the ALTER TABLE on the fully populated table.


-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 16, 2005 1:18 PM
To: mysql@lists.mysql.com
Subject: Re: alter only an enum label

hum... clever. i liked that solution.

but do have experience on how long it will take with milions of records?
all records havin only a int(11) as unique key and the enum field..
suposing now i have enum("a", "b") only, and did a  "ALTER TABLE
tablename CHANGE columnname columnname ENUM('a','b','c');"

thanks,
Gabriel

2005/6/16, Gordon Bruce <[EMAIL PROTECTED]>:
> If you have "c" values in the table currently you can just do an
> 
> ALTER TABLE tablename CHANGE columnname columnname
ENUM('a','b','c','x')
> DEFAULT "a" NOT NULL
> 
> then
> 
> UPDATE tablename SET columname = 'x' WHERE columname = 'c'
> 
> Then
> 
> ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','x')
> DEFAULT "a" NOT NULL
> 
> -Original Message-
> From: Gabriel B. [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 16, 2005 12:54 PM
> To: LISTA mysql
> Subject: alter only an enum label
> 
> If i have a table with about 800M records. and one of the fields is a
> enum("a", "b", "c") and i want to change it to enum("a","b","x") will
> it fall into some optimization and be instant?
> 
> and what if previously i've never used the "c" value? isn't there any
> optimization for that? ...leaving "blank" labels on a enum? or another
> command to "add" new labels to a enum?
> 
> thanks,
> Gabriel
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

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




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

RE: alter only an enum label

[Gordon Bruce]

If you have "c" values in the table currently you can just do an 

ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c','x')
DEFAULT "a" NOT NULL

then 

UPDATE tablename SET columname = 'x' WHERE columname = 'c'

Then

ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','x')
DEFAULT "a" NOT NULL

-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 16, 2005 12:54 PM
To: LISTA mysql
Subject: alter only an enum label

If i have a table with about 800M records. and one of the fields is a
enum("a", "b", "c") and i want to change it to enum("a","b","x") will
it fall into some optimization and be instant?


and what if previously i've never used the "c" value? isn't there any
optimization for that? ...leaving "blank" labels on a enum? or another
command to "add" new labels to a enum?

thanks,
Gabriel

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




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

RE: discuss: user management

[Gordon Bruce]

It always helpd me to change MySQL's "user" to connection in my head
when I begin to think about access control. Then in most database
designs that I have seen, row access control is just as important as
database/table/column. Then the question becomes does the "user" have
direct access to the database or is there an intervening filter
[application code].

Now on a development environment I typically group the developers {i.e.
roles} and let each "user" {person} in a group use a common connection.
Even then the # of connections has to be small and relatively generic
{Select on these 20 tables, Select/ Update on these 45 tables, Select/
Insert/ Update/ Delete on these 5 tables}. I have yet to find the DBA
that can define unique MySQL "users" for 500 people.

In an "Accountiing" environment I still will have groups/roles but much
of the identification/enforcement will be done through a combination of
application code and the use of specific database connections. This way
I can enforce things like population of last changed by and timestamp
fields, application navigation recording as well as row level access
control. {i.e. I should only be able to see my own employee data or some
parts of the data for people reporting to me.} Direct access via SQL
would be extremely limited.



-Original Message-
From: Danny Stolle [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 14, 2005 2:05 PM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: discuss: user management

Hi Kevin,

i started this discussion to find out, how most database administrators 
or users involved in managing MySql, would deal with a topic as User 
Management. So the question(s) is(are) more hypothetical, e.g. "What if 
(...) 'you would have a development site and an accounting site'" how 
would you plan your user management?

I like the way you state your opinion on User Management and the 
examples you give. It is not so that i would stick on these options, if 
there are other ideas, please let us discuss them.

but if you have given some examples, i would like to give an example on 
the 3th option: it is not so that you have to create a user with these 
prefixes (_dev, _arch); why not having departmentnames as userID's or 
perhaps fantasynames as userID's (which could be uses as role names).

your question on the role-part: 'why would somebody create roles?' is an

interesting question. i have no direct answer to this question. the only

thing i would come up with is: when you have a lot of tables and you 
have to change a privilege on several tables. you have the choice for 
changing that for 40 users each or 5 roles each.

Best Regards,

Danny Stolle
EmoeSoft, Netherlands


Kevin Struckhoff wrote:
> Danny,
> 
> I would stay away from option 3 for exactly the example you provided.
> You have 1 user with 2 roles. What if you had 30 users with 2 roles? I
> would choose option 2 because I would only have to maintain 2 users in
> MySQL, not 60 as you would in option 3. For option 1, you would have
30
> users, but then you would to give them the 'most permissible'
privileges
> of the 2 roles.
> 
> What I don't know is why you need to have roles in the first place. Do
> you have a large number of users and a large number of roles?
> 
> Kevin Struckhoff 
> Customer Analytics Mgr.
> NewRoads West
> 
> Office 818.253.3819 Fax 818.834.8843
> [EMAIL PROTECTED]
> 
> 
> -Original Message-
> From: Danny Stolle [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, June 14, 2005 11:12 AM
> To: [EMAIL PROTECTED]; mysql@lists.mysql.com
> Subject: Re: discuss: user management
> 
> Hi Kevin,
> 
> yes it is a complex matter, i agree completely. but how would you plan

> this as a dba or the person involved on administrating MySql. For 
> instance: You would choose option 2 as the preferable one. But what 
> would you do if somebody would change its role or that the person
would 
> get other privileges? he will get a new or already created role
userID, 
> but would still be able to logon using the previous user id.
> 
> why wouldn't you choose for the 3th option or 1st option? what 
> disadvantages do you think would option 1 and 3 have?
> 
> Best regards,
> 
> Danny Stolle
> EmoeSoft, Netherlands
> 
> 
> 
> Kevin Struckhoff wrote:
> 
>>Danny,
>> 
>>Although my experience with MySQL user management is limited to just
>>maintaining a handful of users, I find it rather overly-complex
> 
> because
> 
>>of the need to maintain a table of users and 'from where' they can
> 
> have
> 
>>access, and to what databases they can have access to. For example, I
>>just installed MySQL Administrator on my laptop and then I had to add
>>rows allowing me to access MySQL from my laptop. The ODBC connection
>>setup should suffice. For every instance of MySQL, you have to have an
>>entry in the user table for every user from every access point. Then
>>multiply that by the number of databases in each instance and you can
>>see that administration of the users can get out of hand

RE: Foreign key constraint problem

[Gordon Bruce]

In some hierarchies I have seen people put the the current id in the
parent_ID Field {basicaly pointing to them self} to represent the top of
the hierarchy. 

I don't know how much this would affect the rest of your application but
it would get rid of the null's

-Original Message-
From: Marcus Bointon [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 14, 2005 11:37 AM
To: mysql@lists.mysql.com
Subject: Foreign key constraint problem

I have a table that uses a self join to represent simple hierarchies.  
I have a parent_id field that contains a reference to the same  
table's id field. Not all items have a parent, so parent_id is nullable.

The problem I run into is in defining the foreign key constraint - if  
a parent item is deleted, I want all the children to cascade delete.  
But it seems I can't combine cascade deletion with nullable - I can  
never have more than one record with a parent_id of null because the  
insert causes the foreign key constraint to fail. This means I can  
never have more than one tree stored in the table. How should I set  
up this relation so it works how I want? I'd really prefer not to  
maintain it manually...

Marcus
-- 
Marcus Bointon
Synchromedia Limited: Putting you in the picture
[EMAIL PROTECTED] | http://www.synchromedia.co.uk


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




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

RE: CREATE TABLE and specifying DEFAULT

[Gordon Bruce]

I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows and they
both seem to look fine {see the SHOW CREATE TABLE's following the CREATE
TABLE statements}

RUN ON 4.0.20

mysql> CREATE TABLE ID (
-> mat INT UNIQUE PRIMARY KEY,
-> ID_firstname CHAR(35) DEFAULT 'filler',
-> ID_lastname CHAR(35) DEFAULT 'filler',
-> ID_ramqnb CHAR(12) DEFAULT 'filler',
-> ID_numciv_hosp CHAR(10) DEFAULT '-9',
-> ID_appt_hosp CHAR(10) DEFAULT '-9',
-> ID_streetname_hosp CHAR(75) DEFAULT '-9',
-> ID_streettype_hosp CHAR(6) DEFAULT '-9',
-> ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
-> ID_direction_hosp CHAR(2) DEFAULT '-9',
-> ID_city_hosp CHAR(50) DEFAULT '-9',
-> ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
-> ID_province_hosp CHAR(2) DEFAULT 'QC',
-> ID_postal_code_hosp CHAR(7) DEFAULT '-9',
-> ID_phone_number_hosp CHAR(12) DEFAULT '-9',
-> ID_work_number_hosp CHAR(20) DEFAULT '-9',
-> ID_cell_number_hosp CHAR(12) DEFAULT '-9',
-> ID_numciv_study CHAR(10) DEFAULT '-9'
-> );
Query OK, 0 rows affected (0.03 sec)


mysql> show create table ID;
+---+---
-

-
| Table | Create Table

+---+---
-

-
| ID| CREATE TABLE `ID` (
  `mat` int(11) NOT NULL default '0',
  `ID_firstname` char(35) default 'filler',
  `ID_lastname` char(35) default 'filler',
  `ID_ramqnb` char(12) default 'filler',
  `ID_numciv_hosp` char(10) default '-9',
  `ID_appt_hosp` char(10) default '-9',
  `ID_streetname_hosp` char(75) default '-9',
  `ID_streettype_hosp` char(6) default '-9',
  `ID_streettype_spec_hosp` char(25) default 'humbug',
  `ID_direction_hosp` char(2) default '-9',
  `ID_city_hosp` char(50) default '-9',
  `ID_city_spec_hosp` char(150) default 'filler',
  `ID_province_hosp` char(2) default 'QC',
  `ID_postal_code_hosp` char(7) default '-9',
  `ID_phone_number_hosp` char(12) default '-9',
  `ID_work_number_hosp` char(20) default '-9',
  `ID_cell_number_hosp` char(12) default '-9',
  `ID_numciv_study` char(10) default '-9',
  PRIMARY KEY  (`mat`),
  UNIQUE KEY `mat` (`mat`)
) TYPE=MyISAM |
+---+---
-

-
1 row in set (0.00 sec)


___
RUN ON 5.0.6


mysql> CREATE TABLE ID (
-> mat INT UNIQUE PRIMARY KEY,
-> ID_firstname CHAR(35) DEFAULT 'filler',
-> ID_lastname CHAR(35) DEFAULT 'filler',
-> ID_ramqnb CHAR(12) DEFAULT 'filler',
-> ID_numciv_hosp CHAR(10) DEFAULT '-9',
-> ID_appt_hosp CHAR(10) DEFAULT '-9',
-> ID_streetname_hosp CHAR(75) DEFAULT '-9',
-> ID_streettype_hosp CHAR(6) DEFAULT '-9',
-> ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
-> ID_direction_hosp CHAR(2) DEFAULT '-9',
-> ID_city_hosp CHAR(50) DEFAULT '-9',
-> ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
-> ID_province_hosp CHAR(2) DEFAULT 'QC',
-> ID_postal_code_hosp CHAR(7) DEFAULT '-9',
-> ID_phone_number_hosp CHAR(12) DEFAULT '-9',
-> ID_work_number_hosp CHAR(20) DEFAULT '-9',
-> ID_cell_number_hosp CHAR(12) DEFAULT '-9',
-> ID_numciv_study CHAR(10) DEFAULT '-9'
-> );
Query OK, 0 rows affected (0.91 sec)

mysql>
mysql> CREATE TABLE ID1 (
-> mat INT PRIMARY KEY UNIQUE,
-> ID_firstname CHAR(35),
-> ID_lastname CHAR(35),
-> ID_ramqnb CHAR(12),
-> ID_numciv_hosp CHAR(10) DEFAULT '-9',
-> ID_appt_hosp CHAR(10) DEFAULT '-9',
-> ID_streetname_hosp CHAR(75) DEFAULT '-9',
->  ID_streettype_hosp CHAR(6) DEFAULT '-9',
->  ID_streettype_spec_hosp CHAR(25),
-> ID_direction_hosp CHAR(2) DEFAULT '-9',
-> ID_city_hosp CHAR(50) DEFAULT '-9',
-> ID_city_spec_hosp CHAR(150),
-> ID_province_hosp CHAR(2) DEFAULT 'QC',
-> ID_postal_code_hosp CHAR(7) DEFAULT '-9',
-> ID_phone_number_hosp CHAR(12) DEFAULT '-9',
-> ID_work_number_hosp CHAR(20) DEFAULT '-9',
-> ID_cell_number_hosp CHAR(12) DEFAULT '-9'
->
-> );
Query OK, 0 rows affected (0.16 sec)

mysql> show create table ID;
+---+---
-

-
| Table | Create Table

+---+

RE: How to control database size in MySQL Windows?

[Gordon Bruce]

If you are on 5.0.n there is an INFORMATION_SCHEMA which you can query
like this. A casual scan of the mysql tables don't show any sizes and I
don't know of a way to get table/database size via SQL.

mysql> select table_schema, sum(DATA_LENGTH) from
information_schema.tables group by 1;
++--+
| table_schema   | sum(DATA_LENGTH) |
++--+
| information_schema | 0|
| mailprint  | 2523448288   |
| mysql  | 275126   |
| test   | 16510|
++--+
4 rows in set, 79 warnings (6.22 sec)

-Original Message-
From: Salama hussein [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 10, 2005 1:31 PM
To: mysql@lists.mysql.com
Subject: How to control database size in MySQL Windows?


I think the answer to this is "You can't". So I guess what I can do is
run a 
query once every while and get the sizes of all the databases and if any

exceeds a predetermined size, revoke insert and update privilages.

What's is the SQL query like to get a database size and the SQL to get
the 
names of all the databases?

Salama



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




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

RE: Seriously.. When are we going to get subqueries?!

[Gordon Bruce]

You can have any number of timestamp columns, but only one of them can
be set to autoupdate. As of 4.1 you are not limited to this being the
1st one in the table and CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), or
NOW() can be used in the DEFAULT. Read  

http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html

The DATETIME, DATE, and TIMESTAMP Types

for MUCH more detail.

-Original Message-
From: Greg Whalin [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 09, 2005 2:54 PM
To: Jeff Smelser
Cc: mysql@lists.mysql.com
Subject: Re: Seriously.. When are we going to get subqueries?!

Jeff Smelser wrote:
> On Thursday 09 June 2005 01:26 pm, George L. Sexton wrote:
> 
> 
>>Another limitation in MySQL is that you can only have one timestamp
column
>>with a default of CURRENT_TIMESTAMP.
>>
> 
> 
> How many friggin times do I have to say that this is not an issue with
4.1 and 
> above? Which, BTW, is production mysql..
> 
> Why do you keep bringing this up?
> 
> Jeff


Are you sure?  I don't see that from 
http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html

It seems that w/ > 4.1, you can specify any ONE timestamp col w/ default

of CURRENT_TIMESTAMP.  You are not limited to the 1st one, but still 
seems you are limited to a max of 1 timestamp.  Or am I reading this
wrong?

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




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

RE: Are partial searches possible?

[Gordon Bruce]

You can try 

if($queryID = mysql_query("SELECT * 
   FROM   WhInventory 
   WHERE  Booking like ('%15%')
   ORDER BY Booking",$dbLink))

-Original Message-
From: Don [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 08, 2005 1:44 PM
To: mysql@lists.mysql.com
Subject: Are partial searches possible?

Using MySQL 4.0.24 with PHP 4.3.11 

I have the following code: 

if($queryID = mysql_query("SELECT * FROM WhInventory WHERE
Match(Booking)
AGAINST ('" . mysql_escape_string($form['booking']) . "' IN BOOLEAN
MODE)
ORDER BY Booking",$dbLink)) { 

Which works fine if I have an exact entry but fails for a partial entry.
For
example. If I have an entry where "Booking" is 'TSIN15' and I search
on
the string 'TSIN15', it is found. 

How can I get it to find a partial match if the string I enter is only
'15' ? 

Thanks, 
Don


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




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

RE: Mysqldump

[Gordon]

If you just dump the structure with mysqldump and then build 
SELECT INTO OUTFILE  and LOAD DATA INFILE 
statements for each table, the process will run faster than even the
"extended insert" option of mysqldump.

-Original Message-
From: ManojW [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 01, 2005 7:01 PM
To: mySQL list
Subject: Mysqldump

Greetings,
I took a dump of (pretty chunk) database, the output is close to 45G. I
am trying to reload this dump file onto a development  server but it's
taking long time to load the database. Is their a faster way to load the
data in? I am using plain and simple  mysql < dump.sql syntax on a Mysql
4.0.24 server.

Thanks in advance!

Cheers

Manoj


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


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

RE: Database design query

[Gordon]

IF GroupID, HostID and UserID are unique between the three sets then your
GroupMember  table will work although I would still be tempted to add a
MemberType in the GroupMember table.

Isn't MemberID the Foreign Key to UserID/HostID/GroupID althugh I don't know
if you can have three different foreign key definitions on one field. If not
I think you are stuck with 3 tables instead of trying to do it in one.

-Original Message-
From: rtroiana [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 31, 2005 12:23 PM
To: mysql@lists.mysql.com
Subject: Database design query

Hi All,

 

I'm trying to get data from Active Directory and storing in database. So I
have the following tables with their corresponding primary keys:

 

Group   (GroupID)

Host (HostID)

User (UserID)

GroupMember(GroupID, MemberID)

 

The relations between them according to Active Directory should be as
follows:

 

1)   Host and user can be in one or more groups

2)   Groups can be in one or more groups

 

I was trying to implement these relations through Db constraints. I wanted
HostID and UserID should refer to MemberID as Primary Key.

 

My problems is a foreign key field cannot refer to a part of primary key, so

1)   I should add GroupID in Host and User table, which will be
redundancy of data, or

2)   Instead of adding a new field, I should not have any relations in
the database and just implement it in code.

 

I have tried with three different designs, but all of them have some issues.
I tied to add a new table just for Member that would store unique memberID.
Does it seem like an overhead? I don't if I can just do with existing table
or not. 

 

I read some articles online, some of them say it's good to implement
relations from DB and some say to reduce overhead, relations can be
implemented from code. What would the best database practice that you would
suggest?

 

I'll appreciate any help

 

Thanks,

Reema Duggal Troiana
Senior Software Developer
BitArmor Systems, Inc.
357 North Craig Street
Ground Floor
Pittsburgh, PA 15213
[TEL] 412-682-2200 Ext 314
[FAX] 412-682-2201

 



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

RE: LOAD DATA and skip columns in text file...

[Gordon]

The folowing is out of the current MySQL manual. It looks like you could
create an intermediate table with the fields you are interested in the front
and "garbage" fields on the end. Then build a specific LOAD DATA INFILE with
correct mapping for each file type [assuming you can tell this in your
automated process]. Then after loading all 200 of your files run 1
INSERT...SELECT... with just the pertinent FIELDS from the intermediate
table to your final table and TRUNCATE the intermediate table. 

It probably makes sense for the intermediate table to have minimal indexing
so the initial loads will run faster.
_
mysql> LOAD DATA INFILE 'persondata.txt'
->   INTO TABLE persondata (col1,col2,...);

You must also specify a column list if the order of the fields in the input
file differs from the order of the columns in the table. Otherwise, MySQL
cannot tell how to match up input fields with table columns. 

If an input line has too many fields, the extra fields are ignored and the
number of warnings is incremented. 

If an input line has too few fields, the table columns for which input
fields are missing are set to their default values. Default value assignment
is described in section 13.2.6 CREATE TABLE Syntax. 


-Original Message-
From: Jessica Svensson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 25, 2005 12:28 PM
To: mysql@lists.mysql.com
Subject: Re: LOAD DATA and skip columns in text file...

That just complicates things alot since i get around 200 files, 6 times a 
day via an automated process and every textfile looks different from the 
other. To just have different load data would make it much easier.

I have read alot of questions about just this and many people is asking for 
this feature. Thats why i'm woundering if it really havent been impemented 
in these 5 years that have passed.

>From: Harald Fuchs <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: mysql@lists.mysql.com
>Subject: Re: LOAD DATA and skip columns in text file...
>Date: 25 May 2005 13:24:55 +0200
>
>In article <[EMAIL PROTECTED]>,
>"Jessica Svensson" <[EMAIL PROTECTED]> writes:
>
> > LOAD DATA and skip columns in text file...
> > What i have found out is that this is not possible in any existing
> > version of mysql, correct?
>
>
> > I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000
> > while searching on google.
>
> > It said "We have entered this on our TODO list one month ago."
>
> > So that would be almost exactly 5 years ago... is it really that this
> > function has not been implemented during these 5 years? If so, then i
> > guess it could be 5 more years before its impelemented and that i
> > should maybe look for other solutions.
>
>Maybe they didn't implement it yet because there's an easy workaround:
>create a temporary table including the columns to be skipped, LOAD it,
>and then use INSERT..SELECT to copy over only the columns you're
>interested in.
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/[EMAIL PROTECTED]
>

_
Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/


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



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

RE: DB design question

[Gordon]

You probably want to add type to both the address and phone tables. Then you
can be selective in your reporting and still get 1 row per student in your
result set. Just remember if your data has the possibility of not having the
information for a student you want to use LEFT JOIN's vs INNER JOIN's or the
student with no "primary" phone [in the following statement] will not be
included in the result set.

SELECT student_id, 
   name, 
   age,  
   h.street_name AS home_address, 
   s.street name AS school_address,
   n.num AS primary_phone
FROM   student s
   LEFT JOIN address s 
   USING (student_id) 
   LEFT JOIN address h 
   USING (student_id)
   INNER JOIN phone_num n
   USING (student_id)
WHERE  h.type = 'Home' 
   AND s.type = 'School'
   AND n.type = 'Primary'

-Original Message-
From: Koon Yue Lam [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 24, 2005 12:34 PM
To: mysql@lists.mysql.com
Subject: DB design question

Hi, here is the case:

one student may have more than one address, and one student may have more 
than one phone number

so the db would be:

student

student_id
name
age

address
---
address_id
student_id
street_name

phone_num
--
student_id
num
extension

the key of 3 tables are student_id

the problems is, when I want to query both student, address and phone num, 
the sql will be

select * from student s, address a, phone_num n
where s.student_id = a.sudent_id
and s.student_id = n.student_id

it won't provide a nice result as data of student are repeated in every row,

address and phone_num's data are repeated in certain rows
The output is not suitable for reporting and may I ask what is the better 
way of design to handle the above case ?

any help would be apreciated

Regards



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

RE: varchar(10) to decimal

[Gordon]

I was all set to tell you why 16.125 became 16.12 when I ran the test on our
production box. It looks like some where between 4.0.20 and 5.0.4 the ALTER
TABLE to a decimal data type changed from truncation to rounding.

Redhat MySQL 4.0.20 truncates all 
Windows XP MySQL 5.0.4 Rounds with Windows algorithm 
I suspect a MySQL 5.0.4 on Linux would round with Linux alorithm but I don't
have one to test.

See following:

The decimal(6,2) tells MySQL to "round" all values to 2 places and store the
results in a decimal field. MySQL uses the Round routines of the host. 
On a Windows box 
16.125 = 16.13
16.135 = 16.14

On a Linux/Unix box
16.125 = 16.12
16.135 = 16.14

Here it is on windows

mysql> select * from dcml;
++
| a  |
++
| 16.00  |
| 16.25  |
| 16.125 |
| 16.135 |
++
4 rows in set (0.00 sec)

mysql> alter table dcml modify a decimal(6,2);
Query OK, 4 rows affected, 2 warnings (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---+--++
| Level | Code | Message|
+---+--++
| Note  | 1265 | Data truncated for column 'a' at row 3 |
| Note  | 1265 | Data truncated for column 'a' at row 4 |
+---+--++
2 rows in set (0.00 sec)

mysql> select * from dcml;
+---+
| a |
+---+
| 16.00 |
| 16.25 |
| 16.13 |
| 16.14 |
+---+
4 rows in set (0.00 sec)

_

And on Linux
_

mysql> create table dcml (a varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dcml values('16.00'),('16.25'),('16.125'),('16.135');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dcml;
++
| a  |
++
| 16.00  |
| 16.25  |
| 16.125 |
| 16.135 |
++
4 rows in set (0.00 sec)

mysql> alter table dcml modify a decimal(6,2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> show warnings;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corre

Our production system is on 4.0.20 which does not support SHOW WARNINGS
and apparently the ALTER truncates always

mysql> select * from dcml;
+---+
| a |
+---+
| 16.00 |
| 16.25 |
| 16.12 |
| 16.13 |
+---+
4 rows in set (0.00 sec)
_

Linux rule paraphrased: If the value to the right of the rounding column is
a 5 then if the rounding column is even round down if the rounding column is
odd round up.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 18, 2005 10:00 AM
To: Jerry Swanson
Cc: mysql@lists.mysql.com 
Subject: Re: varchar(10) to decimal

Hi,
if varchar represents decimal(6,x) where x>2, it's truncated. Else, it's
converted :


mysql> create table dcml (a varchar(10));
Query OK, 0 rows affected (0.24 sec)

mysql> insert into dcml values('16.00'),('16.25'),('16.125'),('16.135');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dcml;
++
| a  |
++
| 16.00  |
| 16.25  |
| 16.125 |
++
3 rows in set (0.03 sec)

mysql> alter table dcml modify a decimal(6,2);
Query OK, 3 rows affected, 1 warning (0.24 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings;
+-+--++
| Level   | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'a' at row 3 |
+-+--++
1 row in set (0.00 sec)

mysql> select * from dcml;
+---+
| a |
+---+
| 16.00 |
| 16.25 |
| 16.12 |
+---+
3 rows in set (0.00 sec)

Here, only row 3 is truncated !

Mathias



Selon Jerry Swanson <[EMAIL PROTECTED]>:

> decimal(6,2)
>
> On 5/18/05, Philippe Poelvoorde <[EMAIL PROTECTED]> wrote:
> > Jerry Swanson wrote:
> >
> > > I need to change format from varchar(10) to decimal.
> > > When I alter the table the data is trimmed.
> > >
> > > What I'm doing wrrong?
> > >
> > > TH
> > >
> >
> > ALTER TABLE your_table MODIFY your_column double NOT NULL DEFAULT '0.0';
> > should normally work. What is the command you are doing and have you
> > example results ?
> > How did you declare your decimal column ?
> >
> > --
> > Philippe Poelvoorde
> > COS Trading Ltd.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql

RE: Read past Equivalent in MySQL

[Gordon]

If you can add a table structure why not create a SELECTED table with REPORT
ID and PERSON ID as the 2 field PRIMARY KEY.

Then you could INSERT IGNORE into this table [with no BEGN/COMMIT] and the
IGNORE would throw away those already selected.

-Original Message-
From: Duncan Hill [mailto:[EMAIL PROTECTED] 
Sent: Friday, May 13, 2005 10:25 AM
To: mysql@lists.mysql.com
Subject: Re: Read past Equivalent in MySQL

On Friday 13 May 2005 16:19, Eric Bergen typed:
> I agree. It sounds like you could use plain repeatable read isolation
> transactions.  If someone else is modifying those rows you get an older
> version from when your transaction was started. No need for skipping
> anything.

In the case of what I'm programming, I need to be able to skip records that 
have been selected by another instance of the program (don't want to send
the 
same person 40 reports with the same content).  Hence why I use flags on the

table to say 'in progress, don't read me'.

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



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

RE: ACCESS ODBC Interface whit 5.0.4

[Gordon]

Thanks, that looks like my problem. I 'll wait until the fix percolates into
the 5. stream.

-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 04, 2005 10:00 AM
To: mysql@lists.mysql.com
Subject: Re: ACCESS ODBC Interface whit 5.0.4

Hello.

Have you been here?

  http://bugs.mysql.com/bug.php?id=9211



>I have been using ACCESS to do simpe data editing on our MySQL tables
>for 3
>years.
> I recently installed 5.0.4 on my machine to evaluate it. I linked the
> tables
> into ACCESS through my old ODBC driver and  got 
>  ODBC-update on a linked table 'product_order_choice' failed
>  [Microsoft][ODBC Driver Manager] SQL data type out of rance (#0)
>   I then downloaded and installed the current ODBC connector
>   [3.51.11]
>   thinking maybe it was my old ODBC copy, but get the same result.
>    Has anyone else seen this or have any ideas?
>
"Gordon" <[EMAIL PROTECTED]> wrote:


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




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



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

RE: ACCESS ODBC Interface whit 5.0.4

[Gordon]

Here is the table structure.
In this case I tried to change the 4 characters in prod_ID when I get the
message, but I get the same message when I try to make any changes.

Doing the exact same activity works fine on all of the 3.23/4.0/4.1 versions
I have installed previouly. Tables with unsigned integer fields work just
fine through this interface on previous versions. The only anomaly up to
this point is that I can't change timestamp fields through the odbc
connection. They display as dates in access and I can change other fields in
the table correctly, just can't change the timestamp fields.

mysql> show create table product_order_choice;
---+
| Table| Create Table
 
|
---+
| product_order_choice | CREATE TABLE `product_order_choice` (
  `cpny_ID` varchar(4) NOT NULL default '',
  `prod_ID` varchar(4) NOT NULL default '',
  `porc_Look_Up_Type` varchar(25) NOT NULL default '',
  `prft_Sub_Month` char(2) NOT NULL default '00',
  `prft_Sub_Item` char(2) NOT NULL default '0',
  `poch_Name` varchar(100) NOT NULL default '',
  `prct_ID` varchar(4) default NULL,
  `poch_Value` text,
  `poch_Image_Path` varchar(255) default NULL,
  `poch_Link` varchar(255) default NULL,
  `poch_Link_2` varchar(255) default NULL,
  `poch_Active` enum('Yes','No','Hidden') NOT NULL default 'Yes',
  `poch_Timestamp` timestamp(14) NOT NULL,
  `poch_Create` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY
(`cpny_ID`,`prod_ID`,`porc_Look_Up_Type`,`prft_Sub_Month`,`prft_Sub_Item`,`p
och_Name`)
) TYPE=InnoDB |
---+
1 row in set (0.00 sec)

mysql>

-Original Message-
From: Daniel Kasak [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 04, 2005 5:16 PM
To: Gordon; 'MySQL'
Subject: Re: ACCESS ODBC Interface whit 5.0.4

Gordon wrote:

>I have been using ACCESS to do simpe data editing on our MySQL tables for 3
>years.
>
> 
>
>I recently installed 5.0.4 on my machine to evaluate it. I linked the
tables
>into ACCESS through my old ODBC driver and  got 
>
> 
>
>ODBC-update on a linked table 'product_order_choice' failed
>
>[Microsoft][ODBC Driver Manager] SQL data type out of rance (#0)
>
> 
>
>I then downloaded and installed the current ODBC connector [3.51.11]
>thinking maybe it was my old ODBC copy, but get the same result.
>
> 
>
>Has anyone else seen this or have any ideas?
>
>
>  
>
It would help if you posted details of the table / data you're working with.
'Data type out of range' usually means you've tried to put a numerical
value in a field which is too small. For example, you may be trying to
put an int value in a mediumint field. Or it could be that you're using
a field type not supported by MS Access, such as an unsigned int or a
bigint.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au



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

ACCESS ODBC Interface whit 5.0.4

[Gordon]

I have been using ACCESS to do simpe data editing on our MySQL tables for 3
years.

 

I recently installed 5.0.4 on my machine to evaluate it. I linked the tables
into ACCESS through my old ODBC driver and  got 

 

ODBC-update on a linked table 'product_order_choice' failed

[Microsoft][ODBC Driver Manager] SQL data type out of rance (#0)

 

I then downloaded and installed the current ODBC connector [3.51.11]
thinking maybe it was my old ODBC copy, but get the same result.

 

Has anyone else seen this or have any ideas?

RE: Help with a tricky/impossible query...

[Gordon]

One way would be to build a "count" table with one column starting with
value 1 and incrementing by 1 up to say 500 rows or how many your max y
value is. Then just 
   select seq,val from wibble,count where val between x and y

create table count (val INT unsigned default '0' not null primary key)

-Original Message-
From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 13, 2005 7:33 PM
To: MySQL
Subject: Re: Help with a tricky/impossible query...

I should mention that I'm constrained to version 4.0.n so no sub queries for
me!

Andrew


On 14/4/05 1:11 am, "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I need some help with a tricky query.  Before anyone asks, I cannot bring
> this functionality back to the application layer (as much as I'd like to).
> 
> Here's what I need to do...
> 
> create table wibble(
> seq int(3) auto_increment primary key,
> x int(5),
> y int(5)
> );
> 
> insert into wibble set x=5, y=10;
> insert into wibble set x=1, y=3;
> insert into wibble set x=17, y=22;
> 
> mysql> select * from wibble;
> +-+--+--+
> | seq | x| y|
> +-+--+--+
> |   1 |5 |   10 |
> |   2 |1 |3 |
> |   3 |   17 |   22 |
> +-+--+--+
> 3 rows in set (0.09 sec)
> 
> So I want to run a query to explode the x/y ranges by seq.
> 
> The required output is:
> 
> mysql> select some clever things from wibble where some clever stuff
happens
> here;
> +-+--+
> | seq | z|
> +-+--+
> |   1 |1 |
> |   1 |2 |
> |   1 |3 |
> |   1 |4 |
> |   1 |5 |
> |   2 |1 |
> |   2 |2 |
> |   2 |3 |
> |   3 |   17 |
> |   3 |   18 |
> |   3 |   19 |
> |   3 |   20 |
> |   3 |   21 |
> |   3 |   22 |
> +-+--+
> 14 rows in set (0.17 sec)
> 
> Can anyone help me to achieve this result?
> 
> Thanks,
> 
> Andrew
> 
> SQL, Query
> 
> 
> 



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



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

RE: design: table depending on a column

[Gordon]

As long as articles.annotationID can be made distinct from
names.annotationID why not use 2 left joins.

You may have to test annotationType in the select section to map the fields.

Something like the following.


SELECT elements.annotationID, 
   CASE annotationType 
 WHEN 'names' THEN names.name 
 WHEN 'articles' THEN articles.title 
 ELSE '' 
   END AS FIELD1,
   CASE annotationType 
 WHEN 'names' THEN '' 
 WHEN 'articles' THEN articles.author 
 ELSE '' 
   END AS FIELD2
FROM elements 
 LEFT JOIN articles 
 USING (annotationID) 
 LEFT JOIN names 
 USING (annotationID)


-Original Message-
From: mel list_php [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 12, 2005 8:59 AM
To: mysql@lists.mysql.com
Subject: design: table depending on a column




Hi list,

I have a design problem, I'd like to know if there is a nice way to solve 
it

I have elements that can be annotated, an annotation is basic info and a 
link on an other database.
For example: my element id 3, called testElement, is annotated.
the annotation depends on the foreign database, sometimes it's articles so 
i'd like to have id, title, author,abstract, sometimes it's just a name so 
in that case I would have id and name.In both id is the id  required to find

the information in the "foreign" db.

The goal is to search for a string in these annotations and retrieve the 
element id.
At the beginning we will know in which foreign database we want to search 
(articles or name) but these could be extended later on.

So my ideas:

-the trivial approach having everything in one table is not realistic 
because I have other attributes (elementName,elementOrigin) for each 
elementID that I don't want to repeat.

- having a table with elementID,annotationID and an other table with 
annotationID, title, author,name
what I don't like here is having only one table for all the annotations in 
all the databases, if I know in which db to search merging everythin will 
slow down a string search

-having a table with elementID,annotationID,annotationType, and depending on

the annotationType searching in the right table: table articles 
(annotationID,title, author) or table names (annotationID,name).
what I don't like in that case is that I have to retrieve the value of the 
attribute annotationType and then do the search depending on that value. (is

there a way to join with a table which name would be retrieved?something 
like select * from elements left join (select annotationType from elements) 
on annotationID?)


I think the second solution is much slower, but it seems more clear for me.
The right way (one of the irght way!) is probably intermediate between both,

but I can't see it.
I have to be careful about the design because the searches will be a lot of 
text, so I'd like to optimize it.

Thanks for any help,

Melanie

_
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger


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



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

RE: Trouble performing an update

[Gordon]

You can do left joins in an update. 
mysql> show create table t;
+---+---
--
| Table | Create Table
+---+---
--
| t | CREATE TABLE `t` (
  `key1` int(11) NOT NULL default '0',
  `key2` int(11) NOT NULL default '0',
  PRIMARY KEY  (`key1`,`key2`)
) TYPE=MyISAM |
+---+---
--
1 row in set (0.01 sec)

mysql> insert into t values
(5,5),(20,25),(10,15),(10,20),(10,30),(15,20),(20,30);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from t;
+--+--+
| key1 | key2 |
+--+--+
|5 |5 |
|   10 |   15 |
|   10 |   20 |
|   10 |   30 |
|   15 |   20 |
|   20 |   25 |
|   20 |   30 |
+--+--+
7 rows in set (0.00 sec)

mysql> update t as t1
->left join t as t2
->on (t1.key2 = t2.key2
->and t2.key1 = 20)
-> sett1.key1 = 20
-> where  t2.key1 IS NULL
->AND t1.key1 = 10;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t;
+--+--+
| key1 | key2 |
+--+--+
|5 |5 |
|   10 |   30 |
|   15 |   20 |
|   20 |   15 |
|   20 |   20 |
|   20 |   25 |
|   20 |   30 |
+--+--+
-Original Message-
From: Bob Dankert [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 16, 2005 2:27 PM
To: mysql@lists.mysql.com
Subject: Trouble performing an update

I am trying to update a link table (table with two primary keys) where I
want to update all rows where the first primary key is a set value (for
example, change key1 from 10 to 20), but I only want to update these
where the resulting primary key does not already exist in the table
(otherwise an error is thrown on a duplicate key and the remaining rows
are not updated).  Using other databases, I am able to perform a
subquery in the filter for the update such as the following:

 

UPDATE mytable SET key1 = 20 WHERE key2 NOT IN (SELECT key2 FROM mytable
WHERE key1 = 20)

 

Unfortunately, MySQL does not allow you to use a table in a subquery
which is being updated.  If anyone can offer any assistance with this, I
would greatly appreciate it.

 

Thanks,

 

Bob Dankert

 

Envision Information Technologies

Associate

[EMAIL PROTECTED]

v. 608.256.5680

f. 608.256.3780

 

 




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

RE: Slow queries only the first time

[Gordon]

The key to your question is InnoDB. InnoDB keeps data and indexes in its
buffer using LRU to flush. So the 2nd time your data was already in memory.
Depending on your buffer size and database size you have all of the
advantages of a memory resident database for frequently used data 
without the disadvantages of losing the data on system shutdown.

-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 10, 2005 1:43 PM
To: Bob O'Neill
Cc: mysql@lists.mysql.com
Subject: Re: Slow queries only the first time


On Mar 10, 2005, at 11:20 AM, Bob O'Neill wrote:

> Hello.  I am wondering why some of my queries are slow on the first  
> run, but
> speedy on subsequent runs.  They are not being query cached, as I have
> query_cache_type set to DEMAND.  Is it something as simple as pulling  
> the
> data into RAM from disk, or is there something else going on?  Here's a
> simple example:
>
> mysql> select count(*) from foo;
> +--+
> | count(*) |
> +--+
> |  1374817 |
> +--+
> 1 row in set (3.60 sec)

A table scan.

> mysql> select count(*) from foo;
> +--+
> | count(*) |
> +--+
> |  1374817 |
> +--+
> 1 row in set (0.92 sec)

Another table scan, but now some or all of the table is cached in RAM  
(disk cache) by the OS (not by MySQL).

> mysql> show variables like 'query_cache_type';
> +--++
> | Variable_name| Value  |
> +--++
> | query_cache_type | DEMAND |
> +--++
> 1 row in set (0.00 sec)

You could probably verify that the query cache wasn't used by  
monitoring the query cache stats  
.

> I am running MySQL 4.1.10 with InnoDB on RHEL 2.1 (kernel
> 2.4.9-e.49enterprise). Binaries are Linux x86 glibc static gcc RPMs  
> from
> mysql.com.
>
> Thanks,
> -Bob

Michael


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



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

RE: Use MySQL with Microsoft Office

[Gordon]

If Publisher XP's mail merge can find data in ACCESS try linking the tables
into an ACCESS database and then tying Publisher to the ACCESS database.

Sounds klunky but it might work.

-Original Message-
From: GH [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 08, 2005 9:37 AM
To: J.R. Bullington
Cc: mysql@lists.mysql.com
Subject: Re: Use MySQL with Microsoft Office

Thank you all for your responses. 

The reason that I am currently asking about Publisher is that i do
certificates for programs that my organization runs and I am able to
in publisher mail merge the Proper Names of both the recipients and
signatories onto the certificate plus print the wording, graphics and
other content all at once.

with out having to do 2-3 passes through the printer ... a great time
saver when doing 200 certificates

I have the myODBC installed but can not get Publisher XP's mail merge
to get the data.



On Tue, 08 Mar 2005 10:11:03 -0500, J.R. Bullington
<[EMAIL PROTECTED]> wrote:
> I know that this is a little off topic, but if you want a "real" Desktop
Publishing Suite, try Adobe
> (InDesign, specifically). They cannot do MailMerges like in OpenOffice or
M$, but it's much easier
> to use than M$ and looks a lot more professional.
> 
> As a side note, OpenOffice, as of 1.1.2, does not have a Publisher type
equivalent. It also does not
> have an Access equivalent, hence MySQL interoperability.
> 
> J.R.
> 
> -Original Message-
> From: Jigal van Hemert [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 08, 2005 9:59 AM
> To: GH
> Cc: mysql@lists.mysql.com
> Subject: Re: Use MySQL with Microsoft Office
> 
> From: "GH"
> > Does Open Office have a MSPublisher like program?
> 
> I'm not familiar with Publisher, but I gues that it is supposed to help
you make publications in a
> kind of desktop pulishing way.
> In the article at http://www.newsforge.com/article.pl?sid=04/10/04/150207
> the author compares Writer (the wordprocessing part of OpenOffice.org)
with Adobe's Framemaker.
> Maybe that will help you a bit.
> 
> It's worth mentioning that the current version OpenOffice.org 1.1.4 will
soon be replaced by a major
> upgrade as the 2.0 beta has recently become available. On
http://www.openoffice.org you can read all
> about the new features in this release.
> 
> I use both OpenOffice.org and MS Office and both have their pros and cons.
> 
> Regards, Jigal.
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

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



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

RE: select date_format('2004-10-03 15:06:14','%m/%d/%y %T');

[Gordon]

I have > 200 tables with regular datetime fields. I link these tables
through ODBC to an ACCESS database where I can run standard ACCESS append
queries or even copy/paste append into the linked table. ODBC handles the
conversion just fine. 

One caution, MySQL timestamp maps to ACCESS datetime but ACCESS datetime
does not map to MySQL timestamp. 

We are on ODBC 3.51.nn and MySQL 4.0.20, but we have been doing this across
several versions for 3 years.

-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 04, 2005 9:08 AM
To: Mysql
Subject: select date_format('2004-10-03 15:06:14','%m/%d/%y %T');

How do I create a table using:

date_format('2004-10-03 15:06:14','%m/%d/%y %T');

That way I can import Access Data?
-- 
Power to people, Linux is here.

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



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

RE: Odd rounding errors with 4.1

[Gordon]

This reminded me of one more difference between Windows and Linux/Unix.
MySQL use the round function out of the host libraries. If you are on a
Windows box the rule for rounding is if the column immediately to the right
of the column you are rounding to is a 5 then round up 
   i.e. make 2.485 >>> 2.49
make 2.595 >>> 2.60
If you are on a Linux/Unix box the rule for rounding is if the column
immediately to the right of the column you are rounding to is a 5 then round
up if the column you are rounding to is odd and round down if the column you
are rounding to is even
   i.e. make 2.485 >>> 2.49
make 2.595 >>> 2.59

Windows Linux/Unix
2.4850  2.492.48
2.5950  2.602.60
2.7700  2.772.77
7.8500  7.867.85

This was run on a RedHat server

mysql> select round(2.4850,2), round(2.5950,2), round(2.7700,2),
round(2.4850,2)+round(2.5950,2)+round(2.7700,2);
+-+-+-+-
+
| round(2.4850,2) | round(2.5950,2) | round(2.7700,2) |
round(2.4850,2)+round(2.5950,2)+round(2.7700,2) |
+-+-+-+-
+
|2.48 |2.60 |2.77 |
7.85 |
+-+-+-+-
+
1 row in set (0.00 sec)

-Original Message-
From: Martin [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 21, 2005 7:49 PM
To: Hassan Schroeder; mysql@lists.mysql.com
Subject: Re: Odd rounding errors with 4.1

Huh, you know.  Now that I'm not at work and therefore don't have my 
numbers to check against, you're right.

Man, I must need more coffee.

Never mind me. :)

May be back tomorrow, though, when I have the numbers in front of me.  I 
know they didn't add up earlier...

Martin

Hassan Schroeder wrote:
> Martin wrote:
> 
>> My recent test involved the following three values from the column:
>> 2.4950
>> 2.5950
>> 2.7700
> 
> 
>> When I use a SUM() on these I get: 7.860
> 
> 
> Sounds good to me...
> 
>> If I switch the column over to a FLOAT, then the SUM() becomes 
>> 7.858950958
>>
>> Using Excel to test the numbers, or hand-calculating, I get:
>> 7.8550.
> 
> 
> Time for a hand upgrade, I think :-)  5 + 5 = 5???  I don't even
> want to think about how Excel would come up with this...
> 
>> Shouldn't the SUM() remain with the precision of the DECIMAL type and 
>> not try to round to 2 decimal places?
> 
> 
> My own, possibly suspect, hand calculations show that SUM() is right;
> and it's common knowledge that floating point isn't the right thing to 
> use for situations like this -- that's why there *is* a DECIMAL type.
> 
> FWIW!

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



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

RE: how to write this query?

[Gordon]

This works if you don't care about holidays. 
If you do the only solution that I have seen that works is to create a
business day table. Ours is shown below. You have to hand construct the
calendar by removing weekends and holidays for the specific entity. This
calendar forces a non business day DATE to the next business day. Date
arithmetic then becomes simple including FIRST MONDAY OF THE MONTH in the US
where many holidays fall on Monday.

clnd_Day is the relative business day since 2000-01-01 
clnd_Week is the relative business week since 2000-01-01 


mysql> show create table calendar;
+--+-
-
| Table| Create Table

+--+-
-
| calendar | CREATE TABLE `calendar` (
  `clnd_Day` smallint(5) unsigned NOT NULL default '0',
  `clnd_Date` date NOT NULL default '-00-00',
  `clnd_Week_Day_Txt` char(9) default NULL,
  `clnd_Week_Day_Num` tinyint(3) unsigned default NULL,
  `clnd_Char_Date` char(12) default NULL,
  `clnd_Week` smallint(8) unsigned default NULL,
  `clnd_Real_Date` char(10) default NULL,
  PRIMARY KEY  (`clnd_Date`),
  UNIQUE KEY `clnd_Real_Date_IDX` (`clnd_Real_Date`),
  KEY `clnd_Day_IDX` (`clnd_Day`),
  KEY `clnd_Char_Date` (`clnd_Char_Date`)
) TYPE=InnoDB |

mysql> select * from calendar limit 10;
+--++---+---+---
-+---++
| clnd_Day | clnd_Date  | clnd_Week_Day_Txt | clnd_Week_Day_Num |
clnd_Char_Date | clnd_Week | clnd_Real_Date |
+--++---+---+---
-+---++
|1 | 2000-01-01 | Monday| 2 | 01/03/2000
| 1 | 01/01/2000 |
|1 | 2000-01-02 | Monday| 2 | 01/03/2000
| 1 | 01/02/2000 |
|1 | 2000-01-03 | Monday| 2 | 01/03/2000
| 1 | 01/03/2000 |
|2 | 2000-01-04 | Tuesday   | 3 | 01/04/2000
| 1 | 01/04/2000 |
|3 | 2000-01-05 | Wednesday | 4 | 01/05/2000
| 1 | 01/05/2000 |
|4 | 2000-01-06 | Thursday  | 5 | 01/06/2000
| 1 | 01/06/2000 |
|5 | 2000-01-07 | Friday| 6 | 01/07/2000
| 1 | 01/07/2000 |
|6 | 2000-01-08 | Monday| 2 | 01/10/2000
| 2 | 01/08/2000 |
|6 | 2000-01-09 | Monday| 2 | 01/10/2000
| 2 | 01/09/2000 |
|6 | 2000-01-10 | Monday| 2 | 01/10/2000
| 2 | 01/10/2000 |
+--++---+---+---
-+---++
10 rows in set (0.00 sec)

-Original Message-
From: Mike Rains [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 21, 2005 9:33 AM
To: mysql@lists.mysql.com
Subject: Re: how to write this query?

SELECT
   start_date,
   end_date,
   DATEDIFF(end_date, start_date) -
   (WEEK(end_date) - WEEK(start_date)) * 2
   AS business_days
FROM DateDiffs
ORDER BY start_date;

+-+-+---+
| start_date  | end_date| business_days |
+-+-+---+
| 2005-01-04 16:44:57 | 2005-01-10 17:53:33 | 4 |
| 2005-01-17 00:00:00 | 2005-02-18 00:00:00 |24 |
| 2005-01-21 00:00:00 | 2005-02-18 00:00:00 |20 |
| 2005-01-24 00:00:00 | 2005-02-18 00:00:00 |19 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 |15 |
| 2005-01-31 00:00:00 | 2005-02-18 00:00:00 |14 |
| 2005-02-04 00:00:00 | 2005-02-18 00:00:00 |10 |
| 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 9 |
| 2005-02-14 00:00:00 | 2005-02-18 00:00:00 | 4 |
+-+-+---+

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



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

RE: Query problem

[Gordon]

Did you want 
WHERE  Name LIKE 'sandy' 
   OR (main_data.Display_In_Search = 1 
   AND main_data.Expiry_Date >= CurDate())


OR 


WHERE  main_data.Expiry_Date >= CurDate()
   AND (Name LIKE 'sandy' 
OR main_data.Display_In_Search = 1 )
   

-Original Message-
From: Richard Duke [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 17, 2005 4:03 PM
To: mysql@lists.mysql.com
Subject: Query problem

Hi

I have a problem with a query that has many joined tables. The query brings 
back 80 records instead of just one. Any suggestions on how I can overcome 
this?

Many thanks

Richard

Query below:-

SELECT *
FROM (main_data INNER JOIN main_data_facilities ON
main_data_facilities.RecNo = main_data.RecNo) INNER JOIN main_data_meals ON
main_data_meals.RecNo = main_data.RecNo) INNER JOIN main_data_non_smoking ON
main_data_non_smoking.RecNo = main_data.RecNo) INNER JOIN 
main_data_payment_types ON
main_data_payment_types.RecNo = main_data.RecNo) INNER JOIN 
main_data_pets_welcome ON
main_data_pets_welcome.RecNo = main_data.RecNo) INNER JOIN 
main_data_special_dietary_requirements ON
main_data_special_dietary_requirements.RecNo = main_data.RecNo) INNER JOIN 
counties ON
counties.ID = main_data.County) INNER JOIN countries ON
countries.ID = main_data.Country) INNER JOIN facilities ON
facilities.ID = main_data_facilities.ID) INNER JOIN meals ON
meals.ID = main_data_meals.ID) INNER JOIN non_smoking ON
non_smoking.ID = main_data_non_smoking.ID) INNER JOIN payment_types ON
payment_types.ID = main_data_payment_types.ID) INNER JOIN pets_welcome ON
pets_welcome.ID = main_data_pets_welcome.ID) INNER JOIN 
special_dietary_requirements ON
special_dietary_requirements.ID = main_data_special_dietary_requirements.ID 
AND main_data_meals.RecNo = main_data_facilities.RecNo
WHERE Name LIKE 'sandy'
OR ( main_data.Display_In_Search = 1 )
AND ( main_data.Expiry_Date >= CurDate() )


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



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

RE: select where multiple joined records match

[Gordon]

Try this 

Select * 
from   resources, goals 
where  resources.ID = goals.RESOURCE_ID
   and (SUBJECT="English"
and GRADE="1") 
OR
   (SUBJECT="English"
and GRADE="2");

-Original Message-
From: AM Thomas [mailto:[EMAIL PROTECTED] 
Sent: Sunday, February 13, 2005 7:23 AM
To: mysql@lists.mysql.com
Subject: select where multiple joined records match

I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table.  My MySQL is  
version 4.0.23a, if that makes a difference.

Here's a simplified version of my problem.

I have two tables, resources and goals.

resources table:

ID  TITLE
1   civil war women
2   bunnies on the plain
3   North Carolina and WWII
4   geodesic domes


goals table:

ID RESOURCE_ID  GRADE  SUBJECT
1  11  English
2  11  Soc
3  12  English
4  21  English
5  23  Soc
6  32  English
7  41  English

Now, how do I select all the resources which have 1st and 2nd grade
English goals?  If I just do:

Select * from resources, goals where ((resources.ID =
goals.RESOURCE_ID) and (SUBJECT="English") and ((GRADE="1") and
(GRADE="2")));

I'll get no results, since no record of the joined set will have more
than one grade.  I can't just put 'or' between the Grade
conditions; that would give resources 1, 2, 3, and 4, when only 1
really should match.

My real problem is slightly more complex, as the 'goals' table also
contains an additional field which might be searched on.

I'm thinking it's time for me to go into the deep end of SQL (MySQL,
actually), and my old O'Reilly MySQL & mSQL book isn't doing the
trick.

Surely this has come up before - thanks for any guidance.

- AM Thomas
-- 
Virtue of the Small / (919) 929-8687

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



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

RE: Syntax Failures with SELECT SUBSTRING - Help!

[Gordon]

Try SELECT SUBSTRING(AnimalName, 1, 1)

MySQL wants the "(" to immediately follow the function i.e. no spaces.

-Original Message-
From: Sue Cram [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 03, 2005 9:28 AM
To: mysql@lists.mysql.com
Subject: Syntax Failures with SELECT SUBSTRING - Help!

Neither my developer nor I can figure out this one!  The package I'm using
is "Animal Shelter Manager" and is written in SQL.  Every other installation
of the product can use the SELECT SUBSTRING command except mine!  I use the
following code:

SELECT SUBSTRING (AnimalName, 1, 1)
FROM Animal

and I get "Syntax error or access violation near "(AnimalName, 1,1) FROM
Animal" at Line 1."   I can use the following with no error, so I know it
has to be in the SUBSTRING option:

SELECT AnimalName
FROM Animal

I also get a syntax error when I use an "IF" statement.  I get the same
error on my home computer (PC) and the PC's at our shelter office.  I also
get a message at startup that says:  "mmtask.exe Unable to locate component.
Application has failed to start because mmvcp70.dll was not found.
Reinstalling application may fix problem."  

Don't know if this is related to the substring error or not.   I have
reinstalled my application and it doesn't help.  I think I"m using the
current release of SQL but don't know how to check for sure.  As you can
probably tell, I'm new to SQL.  Can anyone help me with this problem?  

Thanks,
Sue in Sequim WA



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

RE: How to select every second record

[Gordon]

Try this.
The second set ... select gives you what you want. 
However, the group by may interfere with the rest of your logic. You also
don't really need the mod(@a,2) in the result set, just in the having.
mysql> set @a:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:[EMAIL PROTECTED],mod(@a,2),ordr_ID, poft_Sub_Month from 
er_poft limit
10;
+--+---+-++
| @a:[EMAIL PROTECTED] | mod(@a,2) | ordr_ID | poft_Sub_Month |
+--+---+-++
|1 | 1 |4245 | 01 |
|2 | 0 |4323 | 01 |
|3 | 1 |4328 | 01 |
|4 | 0 |4329 | 01 |
|5 | 1 |4331 | 01 |
|6 | 0 |4332 | 01 |
|7 | 1 |4333 | 01 |
|8 | 0 |4335 | 01 |
|9 | 1 |4343 | 01 |
|   10 | 0 |4344 | 01 |
+--+---+-++
10 rows in set (0.00 sec)

mysql> set @a:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:[EMAIL PROTECTED],mod(@a,2),ordr_ID, poft_Sub_Month from 
er_poft 
group by 3,4 having mod(@a,2) = 0 limit 5;
+--+---+-++
| @a:[EMAIL PROTECTED] | mod(@a,2) | ordr_ID | poft_Sub_Month |
+--+---+-++
|2 | 0 |4323 | 01 |
|4 | 0 |4329 | 01 |
|6 | 0 |4332 | 01 |
|8 | 0 |4335 | 01 |
|   10 | 0 |4344 | 01 |
+--+---+-++
5 rows in set (0.00 sec)
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 27, 2005 8:50 AM
To: Alessandro Sappia; mysql@lists.mysql.com
Subject: RE: How to select every second record

[snip]
Jay Blanchard wrote:
> [snip]
> Is it possible to select only every second record from a record set?
>  
> I should select the record-number 1, 3, 5, 7, 9, ... or record-number
2,
> 4,
> 6, 8, ...
>  
> Can this be done with LIMIT?
> [/snip]
> 
> Not LIMIT, but you can use MOD, especially with an auto-increment
field
> (id in this case is the auto-increment field)
> 
> select * from table where mod(id, 2) <> '0' returns odd rows
> select * from table where mod(id, 2) <> '1' returns even rows
> 
You have to do it with LIMIT
beacuse id may not help you...
so
select * from table where  [group by ]
  [order by  [desc]] LIMIT 2,1

this select just second resultrow from any kind of resultset made using 
every thing you like in where/order by/groub by and not being limited by

  using IDs (auto_increment)
[/snip]

The problem is that this only returns ONE record, the OP wanted every
other record

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




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

RE: Yet another LEFT JOIN question

[Gordon]

Try something like this 

SELECT A1.ID, 
   SUM(IF(ISNULL(C.AdID),0,1)) AS Clicks, 
   SUM(IF(ISNULL(V.AdID),0,1))  AS Views
FROM   Ads A1 
   LEFT JOIN Clicks C 
   ON A1.ID = C.AdID
   LEFT JOIN Views V 
   ON A1.ID = V.AdID
GROUP BY A1.ID

-Original Message-
From: Ron Gilbert [mailto:[EMAIL PROTECTED] 
Sent: Saturday, December 04, 2004 2:09 PM
To: <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
Subject: Yet another LEFT JOIN question

I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list 
of every time a Ad was clicked on with the Ads ID, and 'Views' is a 
simple list of views that ad got, with the Ads ID.

I am trying to SELECT a list of all the ads, with a count for clicks 
and a count for views, but my LEFT JOIN is not producing what I 
thought.

If the click count is 0, then the view count is OK, but if not, then 
the Click count and view count are equal, but a much too large number.

If I just SELECT for views or clicks, then it works OK, it's when they 
are combined that it falls apart.

SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views
FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID
 LEFT JOIN Views V ON A1.ID = V.AdID
group by A1.ID

CREATE TABLE `Clicks` (
   `AdID` int(10) NOT NULL default '0'
   [snip]
)
CREATE TABLE `Views` (
   `AdID` int(10) NOT NULL default '0'
   [snip]
)
CREATE TABLE `Ads` (
   `ID` int(10) NOT NULL default '0'
   [snip]
)

I have tried a lot of combinations for LEFT JOIN with no luck.  I've 
read all the posts on this list and they don't seem to be doing what I 
am, or else I'm not seeing it.

Thanks, Ron


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


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

RE: matching people with projects via resources

[Gordon]

This may not be elegant, but why not define a 3rd table  proj_c containing
proj and project_rsrc. This assumes that when you define a project you know
how many resources are required. 
CREATE TABLE proj_c (
   proj varchar(11) default NULL, 
   project_rsrc INT default 0);

INSERT INTO proj_c 
VALUES
  ('ark',2),('cabin',1),('monalisa',2),('jeans',2);

Then the sql becomes 

mysql> SELECT name, count(people.rsrc) AS person_rsrc, project_rsrc,
project.proj
-> FROM   people
->LEFT JOIN project
->USING (rsrc)
->LEFT JOIN proj_c
->ON (project.proj = proj_c.proj)
-> GROUP BY name, project.proj
-> HAVING person_rsrc = project_rsrc
-> ;
+-+-+--+--+
| name| person_rsrc | project_rsrc | proj |
+-+-+--+--+
| davinci |   2 |2 | monalisa |
| lincoln |   1 |1 | cabin|
| noah|   2 |2 | ark  |
| noah|   1 |1 | cabin|
+-+-+--+--+
4 rows in set (0.00 sec)
-Original Message-
From: Laszlo Thoth [mailto:[EMAIL PROTECTED] 
Sent: Friday, October 01, 2004 10:53 AM
To: [EMAIL PROTECTED]
Subject: matching people with projects via resources

I'm having difficulty constructing a query.  I've got two kinds of
information:
a table of resources that various people have, and a table of resources that
various projects need.

===
CREATE TABLE `people` (
  `name` varchar(11) default NULL,
  `rsrc` varchar(15) default NULL
);

INSERT INTO `people` VALUES
('noah','wood'),('noah','canvas'),('lincoln','wood'),('davinci','canvas'),('
davinci','paint');

CREATE TABLE `project` (
  `proj` varchar(11) default NULL,
  `rsrc` varchar(15) default NULL
);

INSERT INTO `project` VALUES
('ark','wood'),('ark','canvas'),('cabin','wood'),('monalisa','canvas'),('mon
alisa','paint'),('jeans','canvas'),('jeans','sewingmachine');
===

I need a query that will tell me which people have the resources required to
complete a given project.  Unfortunately all I can get are incomplete
matches:
I'm not sure how to express the concept of "fully satisfying the
requirements"
to MySQL.

Restructuring the tables is allowed: I'm not tied to the current schema, I
just
need to solve the problem.  The only limit is that resources must be
arbitrary:
I can't use a SET to define resources because I might want to insert a new
resource at some future point without redefining the column type.

I'm pretty sure this is a good starting point, but that's just matching
resource
to resource without excluding Lincoln from building an Ark (no canvas).

mysql> SELECT project.proj,project.rsrc,people.name FROM project LEFT JOIN
people ON project.rsrc=people.rsrc;
+--+-+-+
| proj | rsrc| name|
+--+-+-+
| ark  | wood| noah|
| ark  | wood| lincoln |
| ark  | canvas  | noah|
| ark  | canvas  | davinci |
| cabin| wood| noah|
| cabin| wood| lincoln |
| monalisa | canvas  | noah|
| monalisa | canvas  | davinci |
| monalisa | paint   | davinci |
| jeans| canvas  | noah|
| jeans| canvas  | davinci |
| jeans| sewingmachi | NULL|
+--+-+-+

It would also be sufficient but less optimal to solve a subset of this
problem,
where I only determine "who could complete this project" for a single
project
rather than trying to match all projects to all people in one query.

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


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

RE: weird kind of join

[Gordon]

You might also try  
FROM table_a
INNER JOIN table_b
ON table_b.code = substring_index(table_a.code,';',1)

SUBSTRING_INDEX(str,delim,count) 
Returns the substring from string str before count occurrences of the
delimiter delim. If count is positive, everything to the left of the final
delimiter (counting from the left) is returned. If count is negative,
everything to the right of the final delimiter (counting from the right) is
returned. 
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'

This function is multi-byte safe.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 29, 2004 12:35 PM
To: Diana Castillo
Cc: [EMAIL PROTECTED]
Subject: Re: weird kind of join

try this (not tested):

FROM table_a
INNER JOIN table_b
ON table_b.code LIKE concat(table_a.code,';%')

or this:

FROM table_a
INNER JOIN table_b
ON table_b.code RLIKE concat('^',table_a.code,';')


http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html
http://dev.mysql.com/doc/mysql/en/Regexp.html

It's not going to be as quick as a direct lookup because of the CONCAT() 
but at least we preserve the possibility of using an index for 
table_b.code.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Diana Castillo" <[EMAIL PROTECTED]> wrote on 09/29/2004 12:39:40 PM:

> is there anyway to do a joint between a table that has codes like 
> this 10004;XXX  or DE;YYY
> with a table that has just the first part e.g 10004 or DE as the code
> There is no set length to the code , all I know is that it is the 
> part before the semicolon.
> so, I can't say 
> FROM table_a  INNER JOIN table_b ON (table_a_code = 
left(table_b.code,2))
> because I will only match the ones that have 2 character codes.
> 
> 
> Diana Castillo
> Global Reservas, S.L.
> C/Granvia 22 dcdo 4-dcha
> 28013 Madrid-Spain
> Tel : 00-34-913604039 Ext 216
> Fax : 00-34-915228673
> email: [EMAIL PROTECTED]
> Web : http://www.hotelkey.com
>   http://www.destinia.com


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