Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hello. Please switch to the mysql-debug-4.1.10a version and send the error log with resolved stack trace. Include the output of the following statement as well: SHOW VARIABLES; >I'm getting this strange error when there are more than 1100 mysql >connections connected to the same server. What about ulimits and free memory of your system? "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote: > Hi, > > Yes - am using the standard binaries and have even upgraded to > mysql-standard-4.1.10a-pc-linux-gnu-i686. I'm still getting this error > - does anyone have any ideas?=20 > > Cheers, > > Andrew > > -Original Message- > From: Gleb Paharenko [mailto:[EMAIL PROTECTED] > Sent: Thu 31 March 2005 02:31 > To: mysql@lists.mysql.com > Subject: Re: Can't create a new thread (errno 11). If you are not out of > available memory, you can consult the manual for a possible OS-dependent > bug' > > Hello. > > I strongly recommend you to upgrade to the latest release. Do you use > official binaries? See: > > http://dev.mysql.com/doc/mysql/en/crashing.html > > > > > > "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote: >> Hi, >>=20 >> I'm getting this strange error when there are more than 1100 mysql=20 >> connections connected to the same server. >>=20 >> [EMAIL PROTECTED] mysql]# bin/mysql >> bin/mysql: connect to server at 'localhost' failed >> error: 'Can't create a new thread (errno 11). If you are not out of=20 >> available memory, you can consult the manual for a possible=20 >> OS-dependent bug' >>=20 >> I've had this running fine in the past with MySQL 4.0.17 and Red Hat=20 >> 7.3 (linux 2.4..) but with the same hardware and MySQL versions using=20 >> Fedora core 2 (linux 2.6) I am getting these problems. >>=20 >> I have checked max_connections and others in my.cnf and all is good. >> I'm running 'out of the box' linux and 'out of the box' MySQL > binaries. >>=20 >> Has anyone had this before? >>=20 >> I would love to hear your thoughts and ideas.. >>=20 >> Cheers for the help, >>=20 >> Andrew >>=20 >> SQL, Query >>=20 >>=20 > > > -- > For technical support contracts, goto > https://order.mysql.com/?ref=3Densita > This email is sponsored by Ensita.NET http://www.ensita.net/ > __ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > <___/ www.mysql.com > > > > > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- 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]
RE: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hi, Yes - am using the standard binaries and have even upgraded to mysql-standard-4.1.10a-pc-linux-gnu-i686. I'm still getting this error - does anyone have any ideas? Cheers, Andrew -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thu 31 March 2005 02:31 To: mysql@lists.mysql.com Subject: Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Hello. I strongly recommend you to upgrade to the latest release. Do you use official binaries? See: http://dev.mysql.com/doc/mysql/en/crashing.html "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote: > Hi, > > I'm getting this strange error when there are more than 1100 mysql > connections connected to the same server. > > [EMAIL PROTECTED] mysql]# bin/mysql > bin/mysql: connect to server at 'localhost' failed > error: 'Can't create a new thread (errno 11). If you are not out of > available memory, you can consult the manual for a possible > OS-dependent bug' > > I've had this running fine in the past with MySQL 4.0.17 and Red Hat > 7.3 (linux 2.4..) but with the same hardware and MySQL versions using > Fedora core 2 (linux 2.6) I am getting these problems. > > I have checked max_connections and others in my.cnf and all is good. > I'm running 'out of the box' linux and 'out of the box' MySQL binaries. > > Has anyone had this before? > > I would love to hear your thoughts and ideas.. > > Cheers for the help, > > Andrew > > SQL, Query > > -- 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: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hello. I strongly recommend you to upgrade to the latest release. Do you use official binaries? See: http://dev.mysql.com/doc/mysql/en/crashing.html "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote: > Hi, > > I'm getting this strange error when there are more than 1100 mysql > connections connected to the same server. > > [EMAIL PROTECTED] mysql]# bin/mysql > bin/mysql: connect to server at 'localhost' failed > error: 'Can't create a new thread (errno 11). If you are not out of > available memory, you can consult the manual for a possible OS-dependent > bug' > > I've had this running fine in the past with MySQL 4.0.17 and Red Hat 7.3 > (linux 2.4..) but with the same hardware and MySQL versions using Fedora > core 2 (linux 2.6) I am getting these problems. > > I have checked max_connections and others in my.cnf and all is good. > I'm running 'out of the box' linux and 'out of the box' MySQL binaries. > > Has anyone had this before? > > I would love to hear your thoughts and ideas.. > > Cheers for the help, > > Andrew > > SQL, Query > > -- 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]
Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hi, I'm getting this strange error when there are more than 1100 mysql connections connected to the same server. [EMAIL PROTECTED] mysql]# bin/mysql bin/mysql: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' I've had this running fine in the past with MySQL 4.0.17 and Red Hat 7.3 (linux 2.4..) but with the same hardware and MySQL versions using Fedora core 2 (linux 2.6) I am getting these problems. I have checked max_connections and others in my.cnf and all is good. I'm running 'out of the box' linux and 'out of the box' MySQL binaries. Has anyone had this before? I would love to hear your thoughts and ideas.. Cheers for the help, Andrew SQL, Query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: if statement help
Just forwarding this to the list. On Wednesday, March 30, 2005 10:43, Christopher Vaughan wrote: > Tom Crimmins on Wednesday, March 30, 2005 at 11:31 AM -0500 wrote: >> Look at the functions HOUR(time), MINUTE(time), SECOND(time). These >> will give you interger output for each part of the time field. Also >> TIME_TO_SEC(time) may be useful for you. I believe that all of these >> are supported in 3.23. If your times aren't greater than 838:59:59 >> this should work for you. > > Thanks for the help: > > SELECT sum( HOUR ( job_walltime ) ) Hours, sum( > MINUTE ( job_walltime ) ) Minutes, sum( > SECOND ( job_walltime ) ) Seconds > FROM `Jobs` > > This cold medicine that I'm on has slowed me down a bit. > > -Chris Vaughan > > www.clusters.umaine.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if statement help
"Christopher Vaughan" <[EMAIL PROTECTED]> wrote on 30/03/2005 16:48:47: > I have data in a table listed as > 44:22:22 > 333:33:33 > It stands for hhh:mm:ss > I want to break each part of the data into different parts based on > the ':' to separate them. Then I want to take that data and sum it. > I wrote an if statement to parse through this table but I can't get > it to work. I am not sure If my syntax is > wrong because I can't find anything to check against it. > > Here is the syntax: > > IF > (SELECT job_walltime > FROM time > WHERE CHAR_LENGTH( job_walltime ) >=9) > THEN > (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( > `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds > FROM `time`) > ELSE > (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( > `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds > FROM `time`) > END > > I know this isn't the only way to do this but this but this is the > first suggestion that comes to mind. Any input would be great. IF is an operator, not a command, so it comes after the SELECT. In C terms, it is more like the "?:" operator than an "if()...else". Thus you can do SELECT x, IF (x > y, "IS BIGGER THAN", "IS SMALLER THAN"), y FROM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if statement help
I think I remember a match_at(":") or pat_index(":") UDF which would
return the position of the first ":" for you, but I can't find it if it
does exist.
On Wed, 30 Mar 2005, Christopher Vaughan wrote:
>I have data in a table listed as
>44:22:22
>333:33:33
>It stands for hhh:mm:ss
>I want to break each part of the data into different parts based on the ':' to
>separate them. Then I want to take that data and sum it. I wrote an if
>statement to parse through this table but I can't get it to work. I am not
>sure If my syntax is
>wrong because I can't find anything to check against it.
>
>Here is the syntax:
>
>IF
>(SELECT job_walltime
>FROM time
>WHERE CHAR_LENGTH( job_walltime ) >=9)
>THEN
>(SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` ,
>4, 2 )) , sum( right( job_walltime, '2' ) ) seconds
>FROM `time`)
>ELSE
>(SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` ,
>3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds
>FROM `time`)
>END
>
>I know this isn't the only way to do this but this but this is the first
>suggestion that comes to mind. Any input would be great.
>
>Further info MySQL 3.23.58 running on RedHat 9
>
>-Chris Vaughan
>
>www.clusters.umaine.edu
>
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if statement help
On Wednesday, March 30, 2005 10:24, Christopher Vaughan wrote: > Tom Crimmins on Wednesday, March 30, 2005 at 11:10 AM -0500 wrote: >> If you convert it to a time field you can use mysql built-in >> functions to do what you want. You are limited to the range >> -838:59:59 to 838:59:59 though. >> >> http://dev.mysql.com/doc/mysql/en/time.html >> http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html > > I'm not sure that this is going to work. Since the length of the > data ranges from 8-9 characters in length and I also need to sum all > the times on hour, minute and second. I looked at the addtime > function but for the version of MySQL we have installed it does not > work and upgrading it would be an unwanted hassle. > > > -Chris Vaughan > > www.clusters.umaine.edu Look at the functions HOUR(time), MINUTE(time), SECOND(time). These will give you interger output for each part of the time field. Also TIME_TO_SEC(time) may be useful for you. I believe that all of these are supported in 3.23. If your times aren't greater than 838:59:59 this should work for you. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if statement help
On Wednesday, March 30, 2005 09:49, Christopher Vaughan wrote: > I have data in a table listed as > 44:22:22 > 333:33:33 > It stands for hhh:mm:ss If you convert it to a time field you can use mysql built-in functions to do what you want. You are limited to the range -838:59:59 to 838:59:59 though. http://dev.mysql.com/doc/mysql/en/time.html http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html > I want to break each part of the data into different parts based on > the ':' to separate them. Then I want to take that data and sum it. > I wrote an if statement to parse through this table but I can't get > it to work. I am not sure If my syntax is wrong because I can't find > anything to check against it. > > Here is the syntax: > > IF > (SELECT job_walltime > FROM time > WHERE CHAR_LENGTH( job_walltime ) >=9) > THEN > (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( > `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) > seconds > FROM `time`) > ELSE > (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( > `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) > seconds > FROM `time`) > END > > I know this isn't the only way to do this but this but this is the > first suggestion that comes to mind. Any input would be great. > > Further info MySQL 3.23.58 running on RedHat 9 > > -Chris Vaughan -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if statement help
I have data in a table listed as 44:22:22 333:33:33 It stands for hhh:mm:ss I want to break each part of the data into different parts based on the ':' to separate them. Then I want to take that data and sum it. I wrote an if statement to parse through this table but I can't get it to work. I am not sure If my syntax is wrong because I can't find anything to check against it. Here is the syntax: IF (SELECT job_walltime FROM time WHERE CHAR_LENGTH( job_walltime ) >=9) THEN (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds FROM `time`) ELSE (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds FROM `time`) END I know this isn't the only way to do this but this but this is the first suggestion that comes to mind. Any input would be great. Further info MySQL 3.23.58 running on RedHat 9 -Chris Vaughan www.clusters.umaine.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
use of 'if()' function in 'order by' clause...
Hi all, Using mysqld (mysql Ver 12.22 Distrib 4.0.18, for pc-linux (i686)) and have come across some funky SQL code that appears to work occassionally - maybe, depending on the sql client I'm using, but I'm not sure why/not. I'm using mysql from the command line client, as well as a jdbc client (Netbeans), and MySQL Control Center Center client. I read you can use 'order by null' if you don't want MySQL to do its default ordering of 'group by' results, but this 'order by if( , scalar1, scalar2 )' stuff doesn't seem correct to me. I understand that the following works, and it makes sense to me: mysql> select fname, lname -> from employee -> order by 2; Works. This will order by the 2nd column, lname. Great. But should the following work? I would think not, and it doesn't seem to, but not everyone is convinced: mysql> select fname, lname -> from employee -> order by if(fname is not null, 1, 2); Works. Well, it executes, but it does not order by the first or second column, just the natural order of the table it seems. One added feature is that our 'order by if' function looks like this: -> order by if(fname is null, 0, 1); Doing an 'order by 0' produces an error when not inside an 'if' function, but inside, it seems to be effectively ignored. I'm a bit confused right now on what the behavior of this thing is, but it *seems* to me like this function is *not* working at all - that is, the use of the if(whatever_you_want,blah,blah)' seems to have no effect whatsoever on the ordering of the query results. Can someone shed some light? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL coredumps on FreeBSD if the SQL query contains a regexp expression
>Description: I am using MySQL 5.x on FreeBSD 5.4-PRE together with libpthread. These are the knobs I did provide to make (to compile the actual FreeBSD port): FORCE_PKG_REGISTER=yes \ WITH_OPENSSL=yes \ BUILD_OPTIMIZED=yes \ WITH_PROC_SCOPE_PTH=yes If executing the following SQL query, mysqld will quit with a SIGSEGV signal. SELECT post_urltitle FROM evo_posts WHERE post_urltitle REGEXP '^erster_eintrag(_[0-9]+)?$' AND ID <> 0; >How-To-Repeat: Just execute the just provided SQL expression on the same FreeBSD system. >Fix: No idea. >Submitter-Id: >Originator:Daniel S. Haischt >Organization: Daniel S. Haischt Mit freundlichen Gruessen / With kind regards DAn.I.El S. Haischt Want a complete signature??? Type at a shell prompt: $ > finger -l [EMAIL PROTECTED] >MySQL support: none >Synopsis: MySQL coredumps on FreeBSD if the SQL query contains a regexp >expression >Severity: serious >Priority: high >Category: mysql >Class: sw-bug >Release: mysql-5.0.2-alpha (FreeBSD port: mysql-client-5.0.2) >C compiler:cc (GCC) 3.4.2 [FreeBSD] 20040728 >C++ compiler: c++ (GCC) 3.4.2 [FreeBSD] 20040728 >Environment: System: FreeBSD abyssone.abyssworld.de 5.4-PRERELEASE FreeBSD 5.4-PRERELEASE #4: Fri Mar 4 15:10:39 CET 2005 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/ABYSSONE i386 Some paths: /usr/local/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Configured with: FreeBSD/i386 system compiler Thread model: posix gcc version 3.4.2 [FreeBSD] 20040728 Compilation info: CC='cc' CFLAGS='-O -pipe -O2 -O3 -fno-omit-frame-pointer -fno-gcse' CXX='c++' CXXFLAGS='-O -pipe -O2 -O3 -fno-omit-frame-pointer -fno-gcse -O2 -felide-constructors -fno-exceptions -fno-rtti -O -pipe -O2 -O3 -fno-omit-frame-pointer -fno-gcse -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS=' -rpath=/usr/local/lib' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 883816 Mar 3 17:16 /lib/libc.so.5 -r--r--r-- 1 root wheel 1876040 Mar 3 17:16 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 14 Mar 3 17:16 /usr/lib/libc.so -> /lib/libc.so.5 lrwxr-xr-x 1 root wheel 16 Dec 1 13:13 /usr/lib/libc.so.4 -> /usr/lib/libc.so Configure command: ./configure '--localstatedir=/var/db/mysql' '--with-debug' '--without-readline' '--without-libedit' '--without-bench' '--without-extra-tools' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-comment=FreeBSD port: mysql-client-5.0.2' '--enable-thread-safe-client' '--with-openssl=/usr/local' '--enable-assembler' '--with-berkeley-db' '--with-named-thread-libs=-pthread' '--without-server' '--prefix=/usr/local' '--build=i386-portbld-freebsd5.4' 'LDFLAGS= -rpath=/usr/local/lib' 'CFLAG S=-O -pipe -O2 -O3 -fno-omit-frame-pointer -fno-gcse' 'CXX=c++' 'build_alias=i386-portbld-freebsd5.4' 'CC=cc' 'CXXFLAGS=-O -pipe -O2 -O3 -fno-omit-frame-pointer -fno-gcse -O2 -felide-constructors -fno-exceptions -fno-rtti -O -pipe -O2 -O3 -fno-omit-frame-pointer -fno-gcse -felide-constructors -fno-rtti -fno-exceptions' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need to know if mysql can handle this data
hi, i'm trying to choose a db, and looking at mysql. here's the problem. we're looking at storing large blob type objects - up to 30,000 char's per field, and possibly a table of 1000 records by 300 fields/record. can i put that all in one table? I have mac osx, so i'm not worried about the filesize constraints. my other question is, if i reverse the layout, i.e. store the data vertically with one char/field (i know, tons of wasted overhead) with 30,000 records, can my sql realistically and efficiently deal with a switch of how the data is stored(i.e. records being in columns instead of rows) and can it effeciently deal with 1000 tables __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound "IF" Statements?
"Sue Cram" <[EMAIL PROTECTED]> wrote on 02/08/2005 01:18:48 AM:
> Thanks to the people who helped me with my IF statement question
> last night. Now I need to carry it one step further to a compound
> 'IF' statement. Again, can't find much information in the manuals --
>
> Several people sent me "IF (Adopted=1, 'Y', 'N') AS Adopted FROM
> Animal" and it works great. Now I want my report to show the
> English Language meaning for a field ('Location' in a table called
> 'Animal') that contains numeric values. In other languages it
> would be something like this (a compound if statement):
>
> IF Location=1
>MOVE 'Downstairs Cat Room' to Location
> ELSE IF Location=2
> MOVE 'Kitten Room' to Location
> ELSE IF Location=3
>MOVE 'Quarantine ' to Location
> ELSE MOVE 'Unknown' to Location
> END IF
>
> In other words, I want to test for the comparison being true, and if
> it isn't true I want to test it again for another value... I
> couldn't find any information about this construct in the manuals or
> past list messages. I also tried using the 'CASE .. WHEN .. THEN ..
> END' but couldn't figure out the syntax errors I was getting.
>
> Thanks again for your help and support-
> Sue
SQL can resolve that for you without an IF() as long as you have at least
partially normalized your data. Here's one way to solve this issue. Create
a new table, call it "Location".
CREATE TABLE "Location" (
ID int not null auto_increment primary key
, Locale varchar(25) not null
, UNIQUE(Locale)
);
Then populate the Location table so that you build a look-up list of
ID-Locale pairs (the ID values will be auto-generated)
INSERT Locations (Locale) VALUES ('Downstairs Cat Room'),('Kitten
Room'),('Quarantine');
Now do an OUTER JOIN to optionally match the rows of one table to the rows
of another (we need all of the rows from Animal and only those rows from
Location that match up). I strongly prefer using LEFT JOIN over RIGHT
JOIN. The JOIN will line up the rows in each table so that when the ON
condition(s) is/are true you will see data from one or both tables. In an
OUTER JOIN, those rows that don't satisfy the ON condition from the table
in the _opposite_ direction of the JOIN will get all null values in all of
their columns (even for columns that normally would not be null). By
checking for the NULL value we detect an Animal.Location value that isn't
listed in the Location table.
SELECT Name
, IF(Adopted=1,'Y','N') as Adopted
, IF(Location.Locale Is Null,'Unknown',Location.Locale) as
Location
FROM Animal
LEFT JOIN Location
ON Location.ID = Animal.Location;
This does two things for you:
1) It creates a master list of locations in the data and keeps it out of
your code.
2) It makes your query more flexible. If for some reason you wanted to
change the name of the 'Kitten Room' to 'Animal Nursery' then all you need
to change is that one row of data on the Location table. Since you should
write your front end to work off of the same list (the Location table, not
something hard coded) everything will automatically stay in sync (no code
changes). It makes everything a little easier to manage, trust me.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Syntax for Compound "IF" Statements?
Harald Fuchs wrote: That's correct, but it can be written shorter and clearer: CASE Lccation WHEN 1 THEN 'Downstairs Cat Room' WHEN 2 THEN 'Kitten Room' WHEN 3 THEN 'Quarantine' ELSE 'Unknown' END AS Location - surely it would be better to have the location ids and location names in a lookup table, and simply write the query as a join between the animals table and the locations table? Hard-coding the meaning of ids into the code itself is never a good idea. - ian -- +---+ | Ian Sales Database Administrator | | | | "If your DBA is busy all the time... | | ...he's not doing his job properly" | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound "IF" Statements?
In article <[EMAIL PROTECTED]>, Johan Höök <[EMAIL PROTECTED]> writes: > Hi, > I guess your "CASE" statement should look something like: > CASE WHEN Location=1 THEN 'Downstairs Cat Room' > WHEN Location=2 THEN 'Kitten Room' > WHEN Location=3 THEN 'Quarantine' > ELSE 'Unknown' END AS Location That's correct, but it can be written shorter and clearer: CASE Lccation WHEN 1 THEN 'Downstairs Cat Room' WHEN 2 THEN 'Kitten Room' WHEN 3 THEN 'Quarantine' ELSE 'Unknown' END AS Location -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound "IF" Statements?
I didn't found an IF ... ELSE ... in MySQL ... and I'm using it for at
least 4 years !... since the old 3.23.xx times !!!
So you will have to do:
SELECT a,b,c,
IF(Location=1, 'Downstairs Cat Room',
IF(Location=2, 'Kitten Room',
IF(Location=3, 'Quarantine', 'Unknown')
)
) as Location
FROM `table_name`
Syntax for IF is:
IF(expr1, expr2, expr3)... that means
IF expr1 == TRUE
THEN expr2
ELSE expr3
As you saw in the response I gave you I used instead of expr3 another
IF... and so on !!!
Gabriel PREDA
www.amr.ro
www.lgassociations.info
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound "IF" Statements?
Hi,
I guess your "CASE" statement should look something like:
CASE WHEN Location=1 THEN 'Downstairs Cat Room'
WHEN Location=2 THEN 'Kitten Room'
WHEN Location=3 THEN 'Quarantine'
ELSE 'Unknown' END AS Location
/Johan
Sue Cram wrote:
Thanks to the people who helped me with my IF statement question last night.
Now I need to carry it one step further to a compound 'IF' statement. Again,
can't find much information in the manuals --
Several people sent me "IF (Adopted=1, 'Y', 'N') AS Adopted FROM Animal" and it
works great. Now I want my report to show the English Language meaning for a field
('Location' in a table called 'Animal') that contains numeric values. In other
languages it would be something like this (a compound if statement):
IF Location=1
MOVE 'Downstairs Cat Room' to Location
ELSE IF Location=2
MOVE 'Kitten Room' to Location
ELSE IF Location=3
MOVE 'Quarantine ' to Location
ELSE MOVE 'Unknown' to Location
END IF
In other words, I want to test for the comparison being true, and if it isn't true I want to test it again for another value... I couldn't find any information about this construct in the manuals or past list messages. I also tried using the 'CASE .. WHEN .. THEN .. END' but couldn't figure out the syntax errors I was getting.
Thanks again for your help and support-
Sue
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound "IF" Statements?
I did a lot of homework on IF statements recently.
Below are copies of some of the scripts I'm using now.
This first example draws on a database filled with the
names of the world's continents, oceans, nations and
states, each given an ID in a field named IDArea. Each
page on my site has an echo statement - $mycode -
which is matched to a value in IDArea.
I want most of the higher ranking pages to display an
index of the nations or states they include. So the
script below says that the pages Earth ($mycode =
ear), North America ($mycode = na), etc. will include
a file named include/index/$mycode.php. Thus, the
Earth page will include include/index/ear.php. You can
see this index in the right column of the page at
www.geozoo.org/world/
But I want all the pages that don't feature indexes to
display affiliate ads, like those on my France page at
http://www.geozoo.org/world/eur/fra/.
* * * * * * * * * *
The followingn script says that IF there's any data
for a particular page ($mycode, like usa) in a field
named GZArticle, then everything between the first
line (the IF statement) and the last line (ENDIF) will
be displayed:
Introduction
* * * * * * * * * *
I can't even remember exactly how the following script
works, at least not at this late hour. But it displays
various trivia only if there are trivia items in the
relevant database table cells:
Biggest Native Land Mammal*
Animal
Trivia
() ~ lb.
* * * * * * * * * *
The following script is from my head section. It uses
a combination of $mycode (e.g. usa) and $mykind (e.g.
nation) values to determine what style sheets are
displayed on various pages.
');
break;
case 'about':
echo ('');
break;
case 'ref':
echo ('');
break;
case 'topic':
echo ('');
break;
}
?>
');
break;
case 'na':
case 'sa':
case 'cam':
case 'eur':
case 'mes':
case 'afr':
case 'aus':
echo ('
');
break;
case 'anx':
echo ('
');
break;
case 'oce':
echo ('
');
break;
case 'pac':
case 'atl':
case 'ino':
case 'arc':
case 'sou':
echo ('
');
break;
}
?>
');
break;
case 'pac':
case 'atl':
case 'ino':
echo ('
');
break;
case 'usa':
case 'can':
case 'mex':
echo ('
');
break;
/* default:
print '' .
$data_details['NameParent'] . ' >';
break;
*/
}
?>
* * * * * * * * * *
Towards the end of the script below, you'll see an IF
satement that says IF there's a nickname in a cell
named NNPx, then echo (display) that nickname.
Otherwise, include a page named suggest/nickname.php.
(Most of the pages on all my sites are designed to
display a title, subtitle (or nickname) and quote. If
no nickname or quote is available, they're replaced
with a message inviting people to submit ideas.)
0)) {
} else {
die('Invalid query: ' . mysql_error());
}
{
$data_details[IDParent] =
substr("$data_details[IDParent]", 0, 2); // returns
"1st 2 characters, turning usa into us"
$res = mysql_query ("SELECT * FROM gzstates where
IDArea = '$data_details[IDParent]-$mycode_2Char'") or
die (mysql_error());
//
while ($row = mysql_fetch_array ($res))
{
if ($row['NNPx']) echo ($row['NNPx']);
else
include($_SERVER['DOCUMENT_ROOT']."/includes/suggest/nickname.php");
// echo ''. $row['NNPx'] .'';
}
}
?>
* * * * * * * * * *
In the middle of the script I pasted below, you'll
find this code:
{
if(empty($row['YearEnded']))
{
$class='hilite';
}
else
{
$class= '';
}
It says if there's no data in a database table cell
named YearEnded (the year a governor's term ended),
give that a row a class named hilite. Thus,
becomes
Otherwise, leave it , or something like
that. Thus, rows representing CURRENT governors (whose
terms haven't ended are hilited yellow.
If I remember correctly, the following script inserts
a link closing tag - - IF a link is included in a
cell named LinkPA.
if($row['LinkPA'] != '')
{ print(""); }
Here's the entire script:
0)) {
} else {
die('Invalid query: ' . mysql_error());
}
{
$res = mysql_query ("SELECT * FROM pxgovernors where
IDArea = 'us-$mycode'") or die (mysql_error());
echo '
Gove
Syntax for Compound "IF" Statements?
Thanks to the people who helped me with my IF statement question last night.
Now I need to carry it one step further to a compound 'IF' statement. Again,
can't find much information in the manuals --
Several people sent me "IF (Adopted=1, 'Y', 'N') AS Adopted FROM Animal" and it
works great. Now I want my report to show the English Language meaning for a
field ('Location' in a table called 'Animal') that contains numeric values.
In other languages it would be something like this (a compound if statement):
IF Location=1
MOVE 'Downstairs Cat Room' to Location
ELSE IF Location=2
MOVE 'Kitten Room' to Location
ELSE IF Location=3
MOVE 'Quarantine ' to Location
ELSE MOVE 'Unknown' to Location
END IF
In other words, I want to test for the comparison being true, and if it isn't
true I want to test it again for another value... I couldn't find any
information about this construct in the manuals or past list messages. I also
tried using the 'CASE .. WHEN .. THEN .. END' but couldn't figure out the
syntax errors I was getting.
Thanks again for your help and support-
Sue
Re: what happen if exceed max connection in innodb
Hi Chenri, > 1. is this value for one database or for overall mysql connections? The value is for overall mysql connections... so it's not for one database. > 2. i'm unable to generate 100 connections, i don't have 100 workstations yet, > how do i test it? Why would you ? > 3. what will happen if the connection exceed 100 connection? > (does mysql crash or display database unavailable error message > or it just queued the connection requests) No it does not crash... It will refuse connection number 101... but there is an issue... depending on the OS there is a "number of connections" that the OS can keep in state of pending... until the application can accept it... so you can get a faked number... Let's say you work with PHP... a PHP script takes under a second to execute then all connections are closed... so it is possible that the OS can keep the connection number 101 in state of pending untill there are only 99 active connections on MySQL... yours becoming connection nr 100 ... MySQL will accept it and serve it... > 4. how should i measure the cache and ram needed for the value of connections? Run it with "acceptable parameters" ... if you see in "SHOW STATUS" high values for "Aborted_connects" or "Aborted_clients" then you should worry... Also you might wanna take a look @ Jeremy's "mytop" (http://jeremy.zawodny.com/mysql/mytop/) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what happen if exceed max connection in innodb
Hello. > 1. is this value for one database or for overall mysql connections? This values is for overall mysql connections. > 2. i'm unable to generate 100 connections, i don't have 100 workstations yet, >how do i test it? You may write a perl script, for example. > 3. what will happen if the connection exceed 100 connection? > (does mysql crash or display database unavailable error message > or it just queued the connection requests) Usually you'll get a Too many connections error when you try to connect to the mysqld server. See: http://dev.mysql.com/doc/mysql/en/too-many-connections.html > 4. how should i measure the cache and ram needed for the value of connections? Some helpful hints you may find at: http://dev.mysql.com/doc/mysql/en/table-cache.html Chenri <[EMAIL PROTECTED]> wrote: > my my.ini contain > max_connections=100 > > this mean that simultaneous connection available is 100 connections, > my questions are: > 1. is this value for one database or for overall mysql connections? > 2. i'm unable to generate 100 connections, i don't have 100 workstations yet, > >how do i test it? > 3. what will happen if the connection exceed 100 connection? > (does mysql crash or display database unavailable error message > or it just queued the connection requests) > 4. how should i measure the cache and ram needed for the value of connections? > > > thanx > > -- 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]
what happen if exceed max connection in innodb
my my.ini contain max_connections=100 this mean that simultaneous connection available is 100 connections, my questions are: 1. is this value for one database or for overall mysql connections? 2. i'm unable to generate 100 connections, i don't have 100 workstations yet, how do i test it? 3. what will happen if the connection exceed 100 connection? (does mysql crash or display database unavailable error message or it just queued the connection requests) 4. how should i measure the cache and ram needed for the value of connections? thanx -- Chenri J Taman Palem Lestari B18 - 19A (021) 926 68651 - Esia jadi bisa SMS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create index if not exists????
I'm writing an install script and need to create an index if it does not exists. I've tried SQL statements but none of them work. Basically I'm looking to do something like $sql[] = "ALTER TABLE `exp_members` ADD INDEX IF NOT EXIST `idx_members_username` ( `username` )"; Is this possible? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert if Update failed without Select
Hi,
Try using the DBI->trace(2) method. This will print a lot of stuff on
your screen however it shows the UPDATE after it has bound all the
variables etc. You will see the statement as it will be passed to the
database. This has saved me a lot of work recently in showing up bugs
very quickly. Also note that under certain circumstances, the UPDATE may
not return 0 but return 0E0. This is also in the docs shown below.
It is documented on the CPAN site. http://www.cpan.org and go to search,
look for DBI and read the documentation there. It is comprehensively
covered there. I would also probably tend to use placeholders for your
prepare, also if you use the RaiseError rather than having to check (at
least while you are debugging) you will get an instant response to any
errorcode eg.
my %err_handle = (
PrintError => 1,
RaiseError => 1
);
$dbh = $dsn etc.etc.etc.
$sth = $dbh->prepare("UPDATE transaction SET salesvolume=?,
netsales=?,
transtype=?,
returnreason=?
WHERE
prodcode=? AND
custcode=? AND
date=?");
$sth->execute($salesvol, $netsales, $transtype, $returnreason,
$prodcode, $custcode, $transdate);
$sth->finish;
print $sql;
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: sam wun [mailto:[EMAIL PROTECTED]
Sent: Monday, 17 January 2005 4:23 PM
To: [EMAIL PROTECTED]
Cc: Mattias J; mysql@lists.mysql.com
Subject: Re: Insert if Update failed without Select
[EMAIL PROTECTED] wrote:
>>Please also note hat UPDATE returns the number of records updated. If
your
>>UPDATE returns 0, you know that the record does not exist, and you
might
>>want to INSERT instead.
>>
>>
>
>There is one situation where the number of records updated will return
>0, yet the row exists. If you update the record with the exact same
>information, mySQL will return a count of 0 rows updated. Yet the row
>exists.
>
>
Hi, how can I find out the return value from update if I execute update
in perl dbi?
Here is my sample code:
$sql ="update transaction set salesvolume=$salesvol, netsales=$netsales,
transtype=\"$transtype\",
returnreason=\"$returnreason\"
where prodcode=\"$prodcode\" and
custcode=\"$custcode\" and date=$transdate";
$udpate_sql = qq {$sql;};
$sth = $dbh->prepare($update_sql) || die "prepare: $update_sql:
$DBI::errstr";
$sth->execute || die "Unable to execute query: $dbh->errstr\n";
$sth->finish;
print $sql;
Currently I found somethingn strange with mysql(perhaps with perl dbi as
well), after setup 1 or 2 test data, I tried to make a change to one of
the fiield in a table, but after update is completed, I don't see the
corresponding field in a record is updated by the new value.
If I copy the update statement exactly from the output of the print
statement ( print $sql), and paste it to the mysql login prompt to
execute it, the update statement update the record instantly. I don't
know what is happening here, why perl dbi does not do what the update
statement supposed to do? Have I missed a commit statement? but I don't
have idea how to place a commit statement to perl dbi.
Thanks
Sam
> Brad Eacker ([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: Insert if Update failed without Select
[EMAIL PROTECTED] wrote:
Please also note hat UPDATE returns the number of records updated. If your
UPDATE returns 0, you know that the record does not exist, and you might
want to INSERT instead.
There is one situation where the number of records updated will return
0, yet the row exists. If you update the record with the exact same
information, mySQL will return a count of 0 rows updated. Yet the row
exists.
Hi, how can I find out the return value from update if I execute update
in perl dbi?
Here is my sample code:
$sql ="update transaction set salesvolume=$salesvol, netsales=$netsales,
transtype=\"$transtype\",
returnreason=\"$returnreason\"
where prodcode=\"$prodcode\" and
custcode=\"$custcode\" and date=$transdate";
$udpate_sql = qq {$sql;};
$sth = $dbh->prepare($update_sql) || die "prepare: $update_sql:
$DBI::errstr";
$sth->execute || die "Unable to execute query: $dbh->errstr\n";
$sth->finish;
print $sql;
Currently I found somethingn strange with mysql(perhaps with perl dbi as
well), after setup 1 or 2 test data, I tried to make a change to one of
the fiield in a table, but after update is completed, I don't see the
corresponding field in a record is updated by the new value.
If I copy the update statement exactly from the output of the print
statement ( print $sql), and paste it to the mysql login prompt to
execute it, the update statement update the record instantly. I don't
know what is happening here, why perl dbi does not do what the update
statement supposed to do? Have I missed a commit statement? but I don't
have idea how to place a commit statement to perl dbi.
Thanks
Sam
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert if Update failed without Select
>Please also note hat UPDATE returns the number of records updated. If your >UPDATE returns 0, you know that the record does not exist, and you might >want to INSERT instead. There is one situation where the number of records updated will return 0, yet the row exists. If you update the record with the exact same information, mySQL will return a count of 0 rows updated. Yet the row exists. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert if Update failed without Select
Please also note hat UPDATE returns the number of records updated. If your UPDATE returns 0, you know that the record does not exist, and you might want to INSERT instead. At 2005-01-15 14:54, you wrote: At 2005-01-15 14:48, you wrote: Without using Select statement, how can I execute Insert SQL statement if Update action is failed? I may be asking for too much. If Select statemnet have to be used to determine the existence of a recordset, what is an efficient way to execute Insert if Update is failed? I m using MySQL 5.0 Look at http://dev.mysql.com/doc/mysql/en/REPLACE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert if Update failed without Select
At 2005-01-15 14:48, you wrote: Without using Select statement, how can I execute Insert SQL statement if Update action is failed? I may be asking for too much. If Select statemnet have to be used to determine the existence of a recordset, what is an efficient way to execute Insert if Update is failed? I m using MySQL 5.0 Look at http://dev.mysql.com/doc/mysql/en/REPLACE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert if Update failed without Select
Hi, Without using Select statement, how can I execute Insert SQL statement if Update action is failed? I may be asking for too much. If Select statemnet have to be used to determine the existence of a recordset, what is an efficient way to execute Insert if Update is failed? I m using MySQL 5.0 Any example would be very apprecated. Thanks Sam. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master will insist on running as a SLAVE if data/master.info exists.
Kevin A. Burton wrote: Mikael Fridh wrote: Kevin A. Burton wrote: This is a bug. Feature. Putting system configuration information on a unix machine in /var.. .yeah... thats not a feature. It's a feature because you installed it there. I decided to put my datadir in /mysql/data which makes the configuration information NOT in /var, see? Even so, it's not 'configuration information', it's more like state information which mysql stores and constantly flushes to disk to make it as crash-safe as possible. Slave is always "started" unless my.cnf says "skip-slave-start". Anyway, WITH skip-slave-start you will still have the slave information (binlog positions etc.) initiated but it will just not start replicating. The information in master.info overrides anything in my.cnf. Again... and this file is in /var... Whats the point of /etc/my.cnf... why not just store everything in /var? If you want you can have the datadir in /etc - it's your choice! Take care -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master will insist on running as a SLAVE if data/master.info exists.
Mikael Fridh wrote: Kevin A. Burton wrote: This is a bug. Feature. Putting system configuration information on a unix machine in /var.. .yeah... thats not a feature. In fact, you don't need those configuration statements in the first place. You could just as well initiated the slave replication by issuing CHANGE MASTER TO... Yes... I realize Slave is always "started" unless my.cnf says "skip-slave-start". Anyway, WITH skip-slave-start you will still have the slave information (binlog positions etc.) initiated but it will just not start replicating. The information in master.info overrides anything in my.cnf. Again... and this file is in /var... Whats the point of /etc/my.cnf... why not just store everything in /var? Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master will insist on running as a SLAVE if data/master.info exists.
Kevin A. Burton wrote: This is a bug. Feature. I have all the slave configuration in /etc/my.cnf commented out. However the machine was ONCE a slave and so data/master.info exists. > I commented out all the /etc/my.cnf settings WRT master-* yet when I restart the box it STILL insists on starting the slave via master.info In fact, you don't need those configuration statements in the first place. You could just as well initiated the slave replication by issuing CHANGE MASTER TO... If these settings aren't enabled the slave shouldn't be started. Slave is always "started" unless my.cnf says "skip-slave-start". Anyway, WITH skip-slave-start you will still have the slave information (binlog positions etc.) initiated but it will just not start replicating. The information in master.info overrides anything in my.cnf. -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master will insist on running as a SLAVE if data/master.info exists.
This is a bug. I have all the slave configuration in /etc/my.cnf commented out. However the machine was ONCE a slave and so data/master.info exists. I commented out all the /etc/my.cnf settings WRT master-* yet when I restart the box it STILL insists on starting the slave via master.info If these settings aren't enabled the slave shouldn't be started. Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if you know the answer,please answer immediately contd
Take a look at http://dev.mysql.com/doc/mysql/en/myisamchk_syntax.html and http://dev.mysql.com/doc/mysql/en/myisamchk_memory.html & http://groups.google.be/groups?hl=nl&lr=&ie=UTF-8&threadm=cb9qd5%241oqt%241%40FreeBSD.csie.NCTU.edu.tw&rnum=3&prev=/groups%3Fq%3Dmyisamchk%2Bmonitoring%26hl%3Dnl%26lr%3D%26ie%3DUTF-8%26selm%3Dcb9qd5%25241oqt%25241%2540FreeBSD.csie.NCTU.edu.tw%26rnum%3D3 What platform are you using? Rik Op zaterdag 25 december 2004 06:17, schreef sirisha gnvg: > myisamchk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if you know the answer,please answer immediately contd
I am a student and we are doing project in mysql database monitoring.Yesterday I posted my doubts regarding that project to this forum. I got reply from Mr.Harish, his suggestion is to use show table status or myisamchk -d -v .Thank you sir for your reply. Actually i haven't made the queations clearer. This is what we have to do in our project,we have to monitor mysql database and continously calculate memory already used by and free memory available for each database(or table),each log file,each temporary table(mysql temporary tables).If free memory is less than a certain percentage we have to send an alert box to the user asking him to backup those tables and logfiles. So we need information about both used space and free space not just the size of those tables or log files. I asked to give details about the tables myisamchk works on.By that question I mean that because Myisamchk is giving details about memory used by tables those details should have been stored in some system tables or files.We need the names of those system tables or files. So if you have information regarding any of the above issues please answer immediately. yours sincerely, sirisha. Yahoo! India Matrimony: Find your life partneronline.
RE: if you know please answer immediately
And, by the way the myisamchk command will be myisamchk -dvv Eg: $ cd /home/mysql-data/data/test Considering the mysql data path is set in /home/mysql-data/ Here "test" is the database name. $ll -rw-rw1 mysqlmysql8660 May 17 2004 abc.frm -rw-rw1 mysqlmysql 570 May 17 2004 abc.MYD -rw-rw1 mysqlmysql1024 Dec 25 10:42 abc.MYI "abc" is the table name. So, the command which can be used is myisamchk -dvv /home/mysql-data/data/test/abc - Harish -Original Message- From: sirisha gnvg [mailto:[EMAIL PROTECTED] Sent: Saturday, December 25, 2004 12:30 AM To: mysql@lists.mysql.com Subject: if you know please answer immediately >I am a student and we are doing a project in mysql database monitoring. > 1 . we found that the memory used and free memory available for tables in individual database is given by myisamchk -d -v command.But we don't have any information about the tables or files myisamchk works on.so please give information if available. > 2 . we are required to find memory used and free memory available for all types of tables,log files of mysql,temporary tables used by mysql database.We don't get help from any of our professors since they don't have working experience in mysql. >please give details to any one of the above questions if not all the questions. Yahoo! India Matrimony: Find your life partneronline. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if you know please answer immediately
Dear Sirisha, The command "show table status from " will give you the details pertaining to the type of the table(MyISAM/InnoDB) and also the memory used. Hope this helps you. -Harish -Original Message- From: sirisha gnvg [mailto:[EMAIL PROTECTED] Sent: Saturday, December 25, 2004 12:30 AM To: mysql@lists.mysql.com Subject: if you know please answer immediately >I am a student and we are doing a project in mysql database monitoring. > 1 . we found that the memory used and free memory available for tables in individual database is given by myisamchk -d -v command.But we don't have any information about the tables or files myisamchk works on.so please give information if available. > 2 . we are required to find memory used and free memory available for all types of tables,log files of mysql,temporary tables used by mysql database.We don't get help from any of our professors since they don't have working experience in mysql. >please give details to any one of the above questions if not all the questions. Yahoo! India Matrimony: Find your life partneronline. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if you know please answer immediately
>I am a student and we are doing a project in mysql database monitoring. > 1 . we found that the memory used and free memory available for tables in > individual database is given by myisamchk -d -v command.But we don't have any > information about the tables or files myisamchk works on.so please give information if available. > 2 . we are required to find memory used and free memory available for all > types of tables,log files of mysql,temporary tables used by mysql database.We > don't get help from any of our professors since they don't have working > experience in mysql. >please give details to any one of the above questions if not all the questions. Yahoo! India Matrimony: Find your life partneronline.
Re: if last binary byte is space (ascii 32) mysql drops it
Mark, - Original Message - From: "Mark Maunder" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, December 03, 2004 10:52 PM Subject: Re: if last binary byte is space (ascii 32) mysql drops it So what you're saying is that BINARY isn't binary because it chomps spaces off the end, thereby corrupting the binary data. Sounds like a bug. Should I report it? it is in the TODO to implement a real VARCHAR to MySQL. That fix may also fix the annoying problem that MySQL removes trailing characters ASCII(32) from the end of all strings, including a BINARY string. It is really counterintuitive, I agree. It does not hurt to report this as a bug at bugs.mysql.com. It could speed up the fixing of this problem. On Fri, 2004-12-03 at 12:30, Paul DuBois wrote: I agree about using the TINYBLOB to avoid trailing space truncation, but BINARY and VARBINARY are MySQL data types now. http://dev.mysql.com/doc/mysql/en/BINARY_VARBINARY.html Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if last binary byte is space (ascii 32) mysql drops it
So what you're saying is that BINARY isn't binary because it chomps spaces off the end, thereby corrupting the binary data. Sounds like a bug. Should I report it? On Fri, 2004-12-03 at 12:30, Paul DuBois wrote: > I agree about using the TINYBLOB to avoid trailing space truncation, but > BINARY and VARBINARY are MySQL data types now. > > http://dev.mysql.com/doc/mysql/en/BINARY_VARBINARY.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if last binary byte is space (ascii 32) mysql drops it
At 14:10 -0600 12/3/04, Dan Nelson wrote: In the last episode (Dec 03), Mark Maunder said: This all started when one of the 16 byte binary primary keys kicked out a duplicate key error. It seems mysql does not store the last byte of the binary value if it is a space. That is, ascii 32 or hex 20. How do I force it to store the space? Thanks! create table testtable ( id binary(16) NOT NULL PRIMARY KEY ) ENGINE=MyISAM; There is no native mysql BINARY type; it's mapped to CHAR(n) BINARY. CHARS and VARCHARS trim trailing blanks (A known issue, but low priority I think). Try using a TINYBLOB column type instead. I agree about using the TINYBLOB to avoid trailing space truncation, but BINARY and VARBINARY are MySQL data types now. http://dev.mysql.com/doc/mysql/en/BINARY_VARBINARY.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if last binary byte is space (ascii 32) mysql drops it
Thing is I don't want a dynamic table for performance reasons. I'm storing an MD5 hash which is 16 bytes. As a workaround I'm only using 8 bytes of the hash and storing it in a bigint(20) column for now. So I guess eighteen quintillion, four hundred forty six quadrillion, seven hundred forty four trillion, seventy three billion, seven hundred nine million, five hundred fifty two thousand possible combinations will have to be unique enough for now. This turned out to be a very hard to debug little issue for me. Perhaps others will be more lucky. I'd like to see it fixed asap. On Fri, 2004-12-03 at 12:10, Dan Nelson wrote: > In the last episode (Dec 03), Mark Maunder said: > > This all started when one of the 16 byte binary primary keys kicked out > > a duplicate key error. It seems mysql does not store the last byte of > > the binary value if it is a space. That is, ascii 32 or hex 20. > > > > How do I force it to store the space? Thanks! > > > > create table testtable ( id binary(16) NOT NULL PRIMARY KEY ) ENGINE=MyISAM; > > There is no native mysql BINARY type; it's mapped to CHAR(n) BINARY. > CHARS and VARCHARS trim trailing blanks (A known issue, but low > priority I think). Try using a TINYBLOB column type instead. -- Mark D. Maunder <[EMAIL PROTECTED]> http://www.workzoo.com/ "The Best jobs from the Best Job Sites." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if last binary byte is space (ascii 32) mysql drops it
In the last episode (Dec 03), Mark Maunder said: > This all started when one of the 16 byte binary primary keys kicked out > a duplicate key error. It seems mysql does not store the last byte of > the binary value if it is a space. That is, ascii 32 or hex 20. > > How do I force it to store the space? Thanks! > > create table testtable ( id binary(16) NOT NULL PRIMARY KEY ) ENGINE=MyISAM; There is no native mysql BINARY type; it's mapped to CHAR(n) BINARY. CHARS and VARCHARS trim trailing blanks (A known issue, but low priority I think). Try using a TINYBLOB column type instead. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if last binary byte is space (ascii 32) mysql drops it
This all started when one of the 16 byte binary primary keys kicked out a duplicate key error. It seems mysql does not store the last byte of the binary value if it is a space. That is, ascii 32 or hex 20. How do I force it to store the space? Thanks! create table testtable ( id binary(16) NOT NULL PRIMARY KEY ) ENGINE=MyISAM; insert into testtable ( id ) values (0x3b3331105ee3f0779ad5f041e75f9420); select hex(id) from testtable; #HEX value retreived is 3B3331105EE3F0779AD5F041E75F94 and has length 30 select hex(id) from testtable where id=0x3b3331105ee3f0779ad5f041e75f9420; #nothing found -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If statement in a where query....
Check whether below query helps u ? Select * from db where IFNULL(last_renewal_date,signup_date) between '2004-11-01' and '2004-11-30' Regards --Yoge Mike Morton wrote: I am trying to find a list of people based on renewal dates, the logic of the query would be: Select everything from the db where the last renewal date is between a and b, or if there is no last renewal date then where the signup date is between a and b The query that I have is something like: Select * from db where last_renewal_date between '2004-11-01' and '2004-11-30' or if( length(last_renewal_date) < 1, signup_date between '2004-11-01' and '2004-11-30' ) order by last_renewal_date Obviously the syntax is wrong here, but is there a way to accomplish this logic using an if statement in the where statement? TIA! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * "Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple." - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If statement in a where query....
- Original Message - From: "Mike Morton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 01, 2004 2:47 PM Subject: If statement in a where query > I am trying to find a list of people based on renewal dates, the logic of > the query would be: > > Select everything from the db where the last renewal date is between a and > b, or if there is no last renewal date then where the signup date is between > a and b > > The query that I have is something like: > > Select * > from db > where last_renewal_date > between '2004-11-01' and '2004-11-30' or > if( > length(last_renewal_date) < 1, > signup_date between '2004-11-01' and '2004-11-30' > ) > order by last_renewal_date > > Obviously the syntax is wrong here, but is there a way to accomplish this > logic using an if statement in the where statement? > You don't need an 'if' to do what you want. Using 'and' and 'or' and brackets around your conditions should be enough. Something like this should do the trick: Select * from db where last_renewal_date between '2004-11-01' and '2004-11-30' or (length(last_renewal_date) < 1 and signup_date between '2004-11-01' and '2004-11-30') order by last_renewal_date That will ensure that the rows which are selected either have their last renewal date in Nov 2004 -OR- that it is less than a year since their last renewal and they signed up in Nov 2004. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If statement in a where query....
You don't need an IF only parentheses. This is almost a literal translation of your exact statement Select * from db where (last_renewal_date between '2004-11-01' and '2004-11-30') or (last_renweal_date is null AND signup_date between '2004-11-01' and '2004-11-30' ) order by last_renewal_date Since you are checking the same beginning and ending dates twice, it makes sense to move those values to their own variables (keeping the variable names in line with your example): SET a='2004-11-01', b='2004-11-30'; Select * from db where (last_renewal_date between a and b) or (last_renewal_date is null AND signup_date between a and b ) order by last_renewal_date; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mike Morton <[EMAIL PROTECTED]> wrote on 12/01/2004 02:47:19 PM: > I am trying to find a list of people based on renewal dates, the logic of > the query would be: > > Select everything from the db where the last renewal date is between a and > b, or if there is no last renewal date then where the signup date is between > a and b > > The query that I have is something like: > > Select * > from db > where last_renewal_date > between '2004-11-01' and '2004-11-30' or > if( > length(last_renewal_date) < 1, > signup_date between '2004-11-01' and '2004-11-30' > ) > order by last_renewal_date > > Obviously the syntax is wrong here, but is there a way to accomplish this > logic using an if statement in the where statement? > > TIA! > > -- > Cheers > > Mike Morton > > > * > * Tel: 905-465-1263 > * Email: [EMAIL PROTECTED] > * > > > "Indeed, it would not be an exaggeration to describe the history of the > computer industry for the past decade as a massive effort to keep up with > Apple." > - Byte Magazine > > Given infinite time, 100 monkeys could type out the complete works of > Shakespeare. Win 98 source code? Eight monkeys, five minutes. > -- NullGrey > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
If statement in a where query....
I am trying to find a list of people based on renewal dates, the logic of the query would be: Select everything from the db where the last renewal date is between a and b, or if there is no last renewal date then where the signup date is between a and b The query that I have is something like: Select * from db where last_renewal_date between '2004-11-01' and '2004-11-30' or if( length(last_renewal_date) < 1, signup_date between '2004-11-01' and '2004-11-30' ) order by last_renewal_date Obviously the syntax is wrong here, but is there a way to accomplish this logic using an if statement in the where statement? TIA! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * "Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple." - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IF() problem
A clarification. If a string value begins with a digit, MySQL does not
convert it to zero (FALSE), but rather to an integer made up of the
leading digits in the value (until a non-digit is reached). This can be
shown by using the CAST() function.
mysql> SELECT '21b' = 0, CAST('21b' AS SIGNED);
+---+---+
| '21b' = 0 | CAST('21b' AS SIGNED) |
+---+---+
| 0 |21 |
+---+-------+
1 row in set (0.00 sec)
However, if the string value begins with a non-digit, then it is coerced
or cast to zero:
mysql> SELECT 'b21' = 0, CAST('b21' AS SIGNED);
+---+---+
| 'b21' = 0 | CAST('b21' AS SIGNED) |
+---+---+
| 1 | 0 |
+---+---+
1 row in set (0.00 sec)
It is still true that Przemyslaw was assuming that a non-empty string
would always evaluate as TRUE, and this isn't necessarily the case. So
the best strategy is to compare directly with the empty string, as
already discussed.
Thanks to Paul DuBois for reminding me of this. My apologies to anyone
whom I might have misled.
--
Jon Stephens, Technical Writer
MySQL AB www.mysql.com
Office: +61 (07) 3388 2228
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IF() problem
Jon Stephens <[EMAIL PROTECTED]> wrote: > *Any* string value evaluates as 0 (FALSE), not just the empty string. Right. Didn't know that. > You want "If BOOK1PL is not empty, return BOOK1PL, otherwise return > BOOK1EN", correct? > > Then try this instead: > > SELECT IF(BOOK1PL <> '', BOOK1PL, BOOK1EN) > FROM tBooksextra WHERE ksi = 'id'; Thanks, that is correct. -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IF() problem
Date: Wed, 17 Nov 2004 12:50:47 +0100 To: [EMAIL PROTECTED] From: "Przemyslaw Popielarski" <[EMAIL PROTECTED]> Subject: IF() problem Message-ID: <[EMAIL PROTECTED]> select IF(BOOK1PL,BOOK1PL,BOOK1EN) from tBooksextra where ksi='id' -> (content of BOOK1EN) select BOOK1PL from tBooksextra WHERE BOOK1PL IS NOT NULL AND BOOK1PL!='' AND ksi='id' -> (content of BOOK1PL). Why didn't I get the content of BOOK1PL in 1st query? (checked in 4.0.21 and 4.1.7) Observe: mysql> SELECT 'something' = 0, '' = 0; +-++ | 'something' = 0 | '' = 0 | +-++ | 1 | 1 | +-++ 1 row in set (0.02 sec) *Any* string value evaluates as 0 (FALSE), not just the empty string. You want "If BOOK1PL is not empty, return BOOK1PL, otherwise return BOOK1EN", correct? Then try this instead: SELECT IF(BOOK1PL <> '', BOOK1PL, BOOK1EN) FROM tBooksextra WHERE ksi = 'id'; -- Jon Stephens, Technical Writer MySQL AB www.mysql.com Office: +61 (07) 3388 2228 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IF() problem
select IF(BOOK1PL,BOOK1PL,BOOK1EN) from tBooksextra where ksi='id' -> (content of BOOK1EN) select BOOK1PL from tBooksextra WHERE BOOK1PL IS NOT NULL AND BOOK1PL!='' AND ksi='id' -> (content of BOOK1PL). Why didn't I get the content of BOOK1PL in 1st query? (checked in 4.0.21 and 4.1.7) ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fast method needed to determine if a table is corrupt
Hi All, InnoDB tables as the solution is incorrect. I've been running some fairly large InnoDB databases, and crashes using InnoDB are probably ALOT worse than with MyIsam tables. InnoDB tables tend to corrupt very easily on such things as power outages, with corrupted page data error which means that mysql doesn't start at all, and the only option is to start InnoDB in recovery mode, dump and hope for the best. How big is your database? If it can fit in RAM, I'd suggest using a ramdisk to store your database with snapshots taken every X minutes and stored to disk (or using NVRAM to store the database). Other than that, there's no quick way to check for corruption that I know of. Cheers, Mark Steele Implementation Director CDT Inc. -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: November 8, 2004 12:43 PM To: Tim Murtaugh Cc: '[EMAIL PROTECTED]' Subject: Re: Fast method needed to determine if a table is corrupt In the last episode (Nov 08), Tim Murtaugh said: > I'm using MySQL server version 4.0.15a in an embedded envirionment (as > a standalone server, I'm not using the embedded server library). I > have 128 MB of memory and disk space is tight. I'm using MyISAM > tables. > > If my system loses power, some tables are left in a corrupt state. As > stated in the MySQL documentation, I think the data tables are OK, its > just that the tables were not closed properly and are considered > corrupt by MySQL. > > I need a FAST way to determine if a table is corrupt. I've tried > myisamcheck --fast and --check-only-changed options, and increased the > buffer sizes (-O key_buffer_size and -O sort_buffer_size), as > mentioned in the documentation. The fastest time I can achieve is > 6:55. > > I've also tried "CHECK TABLE tablename FAST QUICK" on a table I know > is marked as corrupt, and the fastest time I can achieve is 6:58. > > I need to detemine if a table is corrupt within a few SECONDS, not > minutes. How can I do this? Make your tables smaller? :) You have to check each record to see that it's okay. If your tables are big, you have to spend time reading them. > The documentation says there is a flag in myisam tables that indicates > when a table is corrupt. Is there a way I can quickly check this flag? If mysql tries to read a record or index and can't, it sets this flag to keep you from accessing the table until you repair it. You may be better off using InnoDB tables and taking the space/performance hit. InnoDB uses a logfile to allow it to roll back partially-commited transactions after a crash, so you never have to check or repair your tables. -- Dan Nelson [EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: Fast method needed to determine if a table is corrupt
Hello. I assume that MyISAM tables are checked thorougly, i.e. each record is read and compared to the table definition. How big your table is and what kind of storage it is on? If the time to check is comparable to the time needed to actually read the whole data from the storage - then it's probably the best time. You can ask in [EMAIL PROTECTED] then. Tim Murtaugh <[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]
Re: Fast method needed to determine if a table is corrupt
Tim Murtaugh wrote: Hi, I'm using MySQL server version 4.0.15a in an embedded envirionment (as a standalone server, I'm not using the embedded server library). I have 128 MB of memory and disk space is tight. I'm using MyISAM tables. If my system loses power, some tables are left in a corrupt state. As stated in the MySQL documentation, I think the data tables are OK, its just that the tables were not closed properly and are considered corrupt by MySQL. You need to provide a way to have an orderly shutdown on power loss. You are trying to treat the symptom, not the problem. I need a FAST way to determine if a table is corrupt. I've tried myisamcheck --fast and --check-only-changed options, and increased the buffer sizes (-O key_buffer_size and -O sort_buffer_size), as mentioned in the documentation. The fastest time I can achieve is 6:55. I've also tried "CHECK TABLE tablename FAST QUICK" on a table I know is marked as corrupt, and the fastest time I can achieve is 6:58. I need to detemine if a table is corrupt within a few SECONDS, not minutes. How can I do this? The documentation says there is a flag in myisam tables that indicates when a table is corrupt. Is there a way I can quickly check this flag? I want to make this an automated check that I can write in C. Are there third-party tools available to help me? Thanks, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fast method needed to determine if a table is corrupt
In the last episode (Nov 08), Tim Murtaugh said: > I'm using MySQL server version 4.0.15a in an embedded envirionment > (as a standalone server, I'm not using the embedded server library). > I have 128 MB of memory and disk space is tight. I'm using MyISAM > tables. > > If my system loses power, some tables are left in a corrupt state. As > stated in the MySQL documentation, I think the data tables are OK, > its just that the tables were not closed properly and are considered > corrupt by MySQL. > > I need a FAST way to determine if a table is corrupt. I've tried > myisamcheck --fast and --check-only-changed options, and increased > the buffer sizes (-O key_buffer_size and -O sort_buffer_size), as > mentioned in the documentation. The fastest time I can achieve is > 6:55. > > I've also tried "CHECK TABLE tablename FAST QUICK" on a table I know > is marked as corrupt, and the fastest time I can achieve is 6:58. > > I need to detemine if a table is corrupt within a few SECONDS, not > minutes. How can I do this? Make your tables smaller? :) You have to check each record to see that it's okay. If your tables are big, you have to spend time reading them. > The documentation says there is a flag in myisam tables that > indicates when a table is corrupt. Is there a way I can quickly check > this flag? If mysql tries to read a record or index and can't, it sets this flag to keep you from accessing the table until you repair it. You may be better off using InnoDB tables and taking the space/performance hit. InnoDB uses a logfile to allow it to roll back partially-commited transactions after a crash, so you never have to check or repair your tables. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fast method needed to determine if a table is corrupt
Hi, I'm using MySQL server version 4.0.15a in an embedded envirionment (as a standalone server, I'm not using the embedded server library). I have 128 MB of memory and disk space is tight. I'm using MyISAM tables. If my system loses power, some tables are left in a corrupt state. As stated in the MySQL documentation, I think the data tables are OK, its just that the tables were not closed properly and are considered corrupt by MySQL. I need a FAST way to determine if a table is corrupt. I've tried myisamcheck --fast and --check-only-changed options, and increased the buffer sizes (-O key_buffer_size and -O sort_buffer_size), as mentioned in the documentation. The fastest time I can achieve is 6:55. I've also tried "CHECK TABLE tablename FAST QUICK" on a table I know is marked as corrupt, and the fastest time I can achieve is 6:58. I need to detemine if a table is corrupt within a few SECONDS, not minutes. How can I do this? The documentation says there is a flag in myisam tables that indicates when a table is corrupt. Is there a way I can quickly check this flag? I want to make this an automated check that I can write in C. Are there third-party tools available to help me? Thanks, Tim
Re: Select with an IF statements
[snip] That's up to you of course. Personally, I think some things are better handled by the database and some are better handled by the programming language. Things like conditional logic tend to be best handled by the programming language in my view. Your mileage may vary ;-) [/snip] AT the risk of starting a religious war, I disagree to a degree. If the conditional logic on the programming language side does not cause or invoke more calls to the database that would be OK, but generally the less you have to go to the DB the better. If the conditional logic in the query causes less information to be returned you gain efficiency on both sides of the coin generally. Note that I said "generally". Sometimes it is much more efficient to retunr larger datasets to the application and work the magic from there. If your databases are formed well, indexed for the job at hand, and the queries are written smertly you are better off putting more of the conditional logic in the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
Well that wasn't a very nice thing to say. On Mon, 2004-10-25 at 11:56, none none wrote: > Like so many other people.. > > No one puts any collective thought into what they are doing.. > > Instead of moving on and trying to finish the rest yourself, you rely > on someone else to finish it for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
Like so many other people..
No one puts any collective thought into what they are doing..
Instead of moving on and trying to finish the rest yourself, you rely
on someone else to finish it for you.
-Original Message-
From: Eve Atley [mailto:[EMAIL PROTECTED]
Sent: Monday, October 25, 2004 9:49 AM
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: RE: Determining if query will work
I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!
It's running beautifully. My only question is, after I run the queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp table?
As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?
Final query setup posted below.
Thanks,
Eve
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value)
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;
INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with an IF statements
That's up to you of course. Personally, I think some things are better handled by the database and some are better handled by the programming language. Things like conditional logic tend to be best handled by the programming language in my view. Your mileage may vary ;-) After all, just because something might possibly be done by MySQL doesn't mean it is the *best* place to do it. For example, a database can store BLOBs like photographs or wave files; does that mean databases should be used to *create* those files? In my view, the answer is no: you use graphics programs to create pictures and music programs to create music. Rhino - Original Message - From: "Luke Venediger" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, October 27, 2004 5:47 PM Subject: Re: Select with an IF statements > Hi Rhino, > > I don't think that answer solves the problem. I do use a programming > language, and doing a query like this means I can make use of the > database engine and not add overhead to my application. > > Cheers, > Luke Venediger. > > On Wed, 27 Oct 2004 17:26:56 -0400, Rhino <[EMAIL PROTECTED]> wrote: > > > > - Original Message - > > From: "Luke Venediger" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Wednesday, October 27, 2004 4:58 PM > > Subject: Select with an IF statements > > > > > Hi, > > > > > > I'm trying to do the following: > > > > > > SELECT > > > IF((ProductStatus IS NOT NULL), "Available", "Not Available") as > > ProductStatus > > > FROM > > > tb_Product > > > WHERE > > > ProductName = "MyProduct"; > > > > > > It works fine if the ProductName "MyProduct" works, and returns > > > "Available". However, if the product name doesn't work the query > > > doesn't return any rows. I would like it to return "Not Available" if > > > the product isn't found. > > > > > > Is there a better way to do this? > > > > > Yes; use a programming language like Java, Perl or PHP. > > > > Rhino > > > > > > > -- > Get Firefox Browser! Reclaim the web. http://getfirefox.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: Select with an IF statements
Hi Rhino, I don't think that answer solves the problem. I do use a programming language, and doing a query like this means I can make use of the database engine and not add overhead to my application. Cheers, Luke Venediger. On Wed, 27 Oct 2004 17:26:56 -0400, Rhino <[EMAIL PROTECTED]> wrote: > > - Original Message - > From: "Luke Venediger" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, October 27, 2004 4:58 PM > Subject: Select with an IF statements > > > Hi, > > > > I'm trying to do the following: > > > > SELECT > > IF((ProductStatus IS NOT NULL), "Available", "Not Available") as > ProductStatus > > FROM > > tb_Product > > WHERE > > ProductName = "MyProduct"; > > > > It works fine if the ProductName "MyProduct" works, and returns > > "Available". However, if the product name doesn't work the query > > doesn't return any rows. I would like it to return "Not Available" if > > the product isn't found. > > > > Is there a better way to do this? > > > Yes; use a programming language like Java, Perl or PHP. > > Rhino > > -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with an IF statements
Thanks Paul! Luke Venediger. On Wed, 27 Oct 2004 16:17:51 -0500, Paul DuBois <[EMAIL PROTECTED]> wrote: > At 22:58 +0200 10/27/04, Luke Venediger wrote: > >Hi, > > > >I'm trying to do the following: > > > >SELECT > > IF((ProductStatus IS NOT NULL), "Available", "Not Available") as > >ProductStatus > >FROM > > tb_Product > >WHERE > > ProductName = "MyProduct"; > > > >It works fine if the ProductName "MyProduct" works, and returns > >"Available". However, if the product name doesn't work the query > >doesn't return any rows. I would like it to return "Not Available" if > >the product isn't found. > > > >Is there a better way to do this? > > SELECT IF(COUNT(*),"Available","Not Available") As ProductStatus > FROM >tb_Product > WHERE >ProductName = "MyProduct"; > > > -- > Paul DuBois, MySQL Documentation Team > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with an IF statements
- Original Message - From: "Luke Venediger" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 27, 2004 4:58 PM Subject: Select with an IF statements > Hi, > > I'm trying to do the following: > > SELECT > IF((ProductStatus IS NOT NULL), "Available", "Not Available") as ProductStatus > FROM > tb_Product > WHERE > ProductName = "MyProduct"; > > It works fine if the ProductName "MyProduct" works, and returns > "Available". However, if the product name doesn't work the query > doesn't return any rows. I would like it to return "Not Available" if > the product isn't found. > > Is there a better way to do this? > Yes; use a programming language like Java, Perl or PHP. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with an IF statements
At 22:58 +0200 10/27/04, Luke Venediger wrote: Hi, I'm trying to do the following: SELECT IF((ProductStatus IS NOT NULL), "Available", "Not Available") as ProductStatus FROM tb_Product WHERE ProductName = "MyProduct"; It works fine if the ProductName "MyProduct" works, and returns "Available". However, if the product name doesn't work the query doesn't return any rows. I would like it to return "Not Available" if the product isn't found. Is there a better way to do this? SELECT IF(COUNT(*),"Available","Not Available") As ProductStatus FROM tb_Product WHERE ProductName = "MyProduct"; -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select with an IF statements
Hi, I'm trying to do the following: SELECT IF((ProductStatus IS NOT NULL), "Available", "Not Available") as ProductStatus FROM tb_Product WHERE ProductName = "MyProduct"; It works fine if the ProductName "MyProduct" works, and returns "Available". However, if the product name doesn't work the query doesn't return any rows. I would like it to return "Not Available" if the product isn't found. Is there a better way to do this? Thanks, Luke Venediger. -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
I think this will be my last question on the matter.
I was reading previous messages to the list regarding the boolean search
function, and its problems when searching with 3-letter search terms.
I'll need to search for "sap" as an ERP term. Is there an efficient way
to avoid getting something like 'sappy' when what I really want is just
'sap', nothing preceeding and nothing following?
Thanks,
Eve
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, October 25, 2004 12:54 PM
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: RE: Determining if query will work
I am so happy to get you working. Hopefully we helped some other along
the way ,too :-)
It's a good idea when working with data that you should always clean up
after yourself, regardless of what language you are using. Not only does
it free up resources faster it helps to make sure that you don't end up
with a contention issue like this.
I take the blame for this one! I gave you the CREATE TEMPORARY...
without the corresponding DROP TEMPORARY TABLE.
http://dev.mysql.com/doc/mysql/en/DROP_TABLE.html
Just put:
DROP TEMPORARY TABLE tmpCandidates
at the end of each pass and you will get rid of the temp table. No more
error message. Temp tables are connection specific so you don't have to
worry about more than one user/process sharing the same temp table,
unless they share the same database connection (connection pooling is
one example)
Come back to the list if you need any more help.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/25/2004 12:48:43 PM:
>
> I can't tell you how *glad* I am to get this running! Big hugs to you,
> Shawn!
>
> It's running beautifully. My only question is, after I run the
queries,
> I notice it won't let me create the temporary table again (saying
> 'tmpCandidates' already exists). Do I just need to then log out of my
> client (MySQL Control Center) and back in to get rid of that temp
table?
> As I'll need to change what it searches for (ie baan, peoplesoft,
etc.).
> Or is there a query I can put in at the end of the queries to destroy
> the temporary table once through with it?
>
> Final query setup posted below.
>
> Thanks,
> Eve
>
>
> CREATE TEMPORARY TABLE wow.tmpCandidates
> SELECT DISTINCT r.Candidate_ID
> FROM wow.resume r
> WHERE r.Section_ID = '1'
> AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
> MODE);
>
> INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
> Section_Value)
> SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
> r.Section_Value
> FROM wow.tmpCandidates tc
> INNER JOIN wow.resume r
> on r.Candidate_ID = tc.Candidate_ID;
>
> INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
> Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
> Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
> Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
> Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
> Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
> Interview_Availability, Interview_Contact, US_Experience,
> Location_Country)
> SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
> c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
> c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
> c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
> c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
> c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
> c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
> c.Interview_Availability, c.Interview_Contact, c.US_Experience,
> c.Location_Country
> FROM wow.tmpCandidates tc
> INNER JOIN wow.candidate c
> ON c.Candidate_ID = tc.Candidate_ID;
>
RE: Determining if query will work
I am so happy to get you working. Hopefully we helped some other along the
way ,too :-)
It's a good idea when working with data that you should always clean up
after yourself, regardless of what language you are using. Not only does
it free up resources faster it helps to make sure that you don't end up
with a contention issue like this.
I take the blame for this one! I gave you the CREATE TEMPORARY... without
the corresponding DROP TEMPORARY TABLE.
http://dev.mysql.com/doc/mysql/en/DROP_TABLE.html
Just put:
DROP TEMPORARY TABLE tmpCandidates
at the end of each pass and you will get rid of the temp table. No more
error message. Temp tables are connection specific so you don't have to
worry about more than one user/process sharing the same temp table, unless
they share the same database connection (connection pooling is one
example)
Come back to the list if you need any more help.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/25/2004 12:48:43 PM:
>
> I can't tell you how *glad* I am to get this running! Big hugs to you,
> Shawn!
>
> It's running beautifully. My only question is, after I run the queries,
> I notice it won't let me create the temporary table again (saying
> 'tmpCandidates' already exists). Do I just need to then log out of my
> client (MySQL Control Center) and back in to get rid of that temp table?
> As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
> Or is there a query I can put in at the end of the queries to destroy
> the temporary table once through with it?
>
> Final query setup posted below.
>
> Thanks,
> Eve
>
>
> CREATE TEMPORARY TABLE wow.tmpCandidates
> SELECT DISTINCT r.Candidate_ID
> FROM wow.resume r
> WHERE r.Section_ID = '1'
> AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
> MODE);
>
> INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
> Section_Value)
> SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
> r.Section_Value
> FROM wow.tmpCandidates tc
> INNER JOIN wow.resume r
> on r.Candidate_ID = tc.Candidate_ID;
>
> INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
> Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
> Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
> Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
> Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
> Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
> Interview_Availability, Interview_Contact, US_Experience,
> Location_Country)
> SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
> c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
> c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
> c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
> c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
> c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
> c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
> c.Interview_Availability, c.Interview_Contact, c.US_Experience,
> c.Location_Country
> FROM wow.tmpCandidates tc
> INNER JOIN wow.candidate c
> ON c.Candidate_ID = tc.Candidate_ID;
>
RE: Determining if query will work
I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!
It's running beautifully. My only question is, after I run the queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp table?
As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?
Final query setup posted below.
Thanks,
Eve
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value)
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;
INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
>What is the definition of resume_erp (what is the result of: SHOW CREATE TABLE resume_erp) ? What that error is telling you is that you are Glad you added more info, or I would have been asking you, 'what do you mean by definition?' :) CREATE TABLE `resume_erp` ( `Candidate_ID` int(10) NOT NULL default '0', `Section_ID` int(10) default NULL, `Section_Value` longtext ) TYPE=MyISAM DEFAULT CHARSET=latin1 And if you need candidate_erp...I had figured candidate_erp was the problem?... CREATE TABLE `candidate_erp` ( `Candidate_ID` int(11) NOT NULL auto_increment, `Vendor_ID` longtext, `Last_Name` longtext, `First_Name` longtext, `Middle_Initial` longtext, `Condition_Type` longtext, `Employer` longtext, `Country_ID` longtext, `Visa_Status` longtext, `Dt_Visa` longtext, `MMDD_Birth` longtext, `SSN` longtext, `CSG_Comments` longtext, `Working` longtext, `Available` longtext, `Start_Date` longtext, `Location` longtext, `HoldOnPeriod` longtext, `Relocation` longtext, `Tech_Ranking` longtext, `Comm_Ranking` longtext, `Availability` longtext, `Cert_Comments` longtext, `Dt_Submitted` longtext, `Def_Rate` longtext, `Def_Rate_Unit` longtext, `Other_Country` longtext, `Currency_id` longtext, `Interview_Availability` longtext, `Interview_Contact` longtext, `US_Experience` longtext, `Location_Country` longtext, PRIMARY KEY (`Candidate_ID`) ) TYPE=MyISAM DEFAULT CHARSET=latin1 > INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) > SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, r.Section_ID, > r.Section_Value > FROM wow.tmpCandidates tc > INNER JOIN wow.resume r > on r.Candidate_ID = tc.Candidate_ID > INNER JOIN wow.candidate c > ON c.Candidate_ID = tc.Candidate_ID; >The only reason you need to JOIN the table candidate to this query is to >make sure that you don't add any records to resume_erp unless they have a >corresponding record in candidate. If you are sure that all resumes still >have their corresponding candidate records then you can drop the second >INNER JOIN and just use the candidate_ID column from either resume or >tmpCandidates (your choice) in your SELECT clause. Hmm, ok, thanks for this. Tells me I can safely drop te second INNER JOIN then. INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID FROM wow.tmpCandidates tc INNER JOIN wow.resume r on r.Candidate_ID = tc.Candidate_ID; - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
What is the definition of resume_erp (what is the result of: SHOW CREATE
TABLE resume_erp) ? What that error is telling you is that you are
attempting to add a record that matches a set of conditions that you said
could only exist once on the entire table (either your primary key or a
unique key). By looking at the definition of that table we can determine
where you should look for the duplication.
continued below...
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/22/2004 03:27:47 PM:
>
> I think we're on to something. I've got one table importing data
> correctly, resume to resume_erp. However, 'candidate' to 'candidate_erp'
> appears goofy. I get the following error:
>
> [mysql.loosefoot.com] ERROR 1062: Duplicate entry '1185' for key 1
>
> With the following queries, query 3 being the problematic one:
>
> # Connection: mysql.loosefoot.com
> # Host: mysql.loosefoot.com
> # Saved: 2004-10-22 14:51:15
> #
> CREATE TEMPORARY TABLE wow.tmpCandidates
> SELECT DISTINCT r.Candidate_ID
> FROM wow.resume r
> WHERE r.Section_ID = '1'
> AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE);
>
> INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
> SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, r.Section_ID,
> r.Section_Value
> FROM wow.tmpCandidates tc
> INNER JOIN wow.resume r
> on r.Candidate_ID = tc.Candidate_ID
> INNER JOIN wow.candidate c
> ON c.Candidate_ID = tc.Candidate_ID;
The only reason you need to JOIN the table candidate to this query is to
make sure that you don't add any records to resume_erp unless they have a
corresponding record in candidate. If you are sure that all resumes still
have their corresponding candidate records then you can drop the second
INNER JOIN and just use the candidate_ID column from either resume or
tmpCandidates (your choice) in your SELECT clause.
>
> INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
> First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
> Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available,
> Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking,
> Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate,
> Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability,
> Interview_Contact, US_Experience, Location_Country)
> SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
> c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
> c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
> c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
> c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
> c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
> c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
> c.Interview_Availability, c.Interview_Contact, c.US_Experience,
> c.Location_Country
> FROM wow.tmpCandidates tc
> INNER JOIN wow.resume r
> on r.candidate_ID = tc.Candidate_ID
> INNER JOIN wow.candidate c
> ON c.Candidate_ID = tc.Candidate_ID;
>
>
> But I think we're almost there:)
Yes, we are.
>
> Thanks,
> Eve
You're welcome,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Determining if query will work
I think we're on to something. I've got one table importing data
correctly, resume to resume_erp. However, 'candidate' to 'candidate_erp'
appears goofy. I get the following error:
[mysql.loosefoot.com] ERROR 1062: Duplicate entry '1185' for key 1
With the following queries, query 3 being the problematic one:
# Connection: mysql.loosefoot.com
# Host: mysql.loosefoot.com
# Saved: 2004-10-22 14:51:15
#
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE);
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available,
Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking,
Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate,
Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability,
Interview_Contact, US_Experience, Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
But I think we're almost there:)
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
Incidentally, we are using a newer 4 version of MySQL now, if there's
another way apart from a temp table to do this?
Let me try with the temp table meanwhile. Thanks!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, October 22, 2004 1:27 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Determining if query will work
I would simplify this search by breaking the query into two steps, just
as
you described. First locate all of the resumes where section 1 contains
the bit of text you are looking for then use those results to get the
full
resume (all 6 sections)
There are 3 ways to do this. One is a single-query method but it will
require a rather complex set of JOINS and I don't think the response
from
it will be good enough (it should be quite slow, based on my
experience),
one uses a subquery which your current version of MySQL may not support
(last I read, you were on 3.23.58), and the third which I show below
uses
a TEMP TABLE:
CREATE TEMPORARY TABLE tmpCandidates
SELECT DISTINCT Candidate_ID
FROM resume
WHERE Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country
FROM tmpCandidates tc
INNER JOIN wow.candidate c
ON tc.Candidate_ID = c.Candidate_ID;
If you had 200 resumes from 150 candidates where section 1 matched
"peoplesoft" and each resume had 6 sections, this will give you 150
candidate returns (you didn't get all 200 resume hits because I
eliminated
the duplicate candidate matches with the DISTINCT keyword). If you want
to
get each set of candidate information along with their resume
information,
you will end up with all columns of the candidate information being
duplicated 6 times, once for each resume section. That query would look
like:
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country, r2.section_id,
r2.section_value
FROM tmpCandidates tc
INNER JOIN resume r
on r.candidate_ID = tc.candidate_id
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
This will return the full 1200 rows (200 resume matches x 6 sections per
resume) because we re-introduced (by joining to the Resume table) the
multiple resumes for each candidates.
Once you have tuned your select queries to return what you want, it's
trivially simple to add the INSERT (...) clause to the beginning of it
to
get the results to go into a table instead of to your client.
Keep me informed on your progress, please. Thank you for being so
patient.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/22/2004 12:43:26 PM:
>
> Sorry for the delay in this. Still having problems with this one.
>
> >The second query is going to produce one row for every resume. Based
> >on
>
> >your results, I assume you have 1000 resumes that have candidates
> >associated with them.
>
> Let's say I have 256 resulting rows for table 'resume', Section_ID 1,
> where the word 'peoplesoft' was found. What I'm really trying to do is
> get the 256 that come up in the first query, and then work with only
> that 256. The way the table 'resume' is set up is with the following
> fields:
>
> Candidate_ID
> Section_ID
> Section_Value
>
> Section_ID 1 is where all basic text in a resume is stored; there are
> a total of 6 sections, each section holding something different. I
> must search first on Section 1 for references to 'peoplesoft'.
> Ultimately, each candidate will have 6 total sections in resume - in
> this case, 256 x 6 = 1536. Therefore, I would need the second query
> to return 1536, returning results for all candidates matching for
> Section 1, then using the Candidate_ID to find all resulting rows in
> table 'resume'.
>
> >I also notice that ALL of the fields i
Re: Conditional statement: IF
That's what I though. In Transact SQL this is valid code to run on the server, and you may optionally choose to put it all into a single stored procedure. In most other databases, a stored procedure has a language that is not executable outside the procedure code itself. With MySQL there will be stored procedure support in version 5.0 of the Server, but in your case the 5.0 alpha will not help, as stored procedure calls probably has an issue or two with the .NET provider. Frankly, the way Sybase does this (which was later inherited by SQL Server) was the way you did things at that time. It's just procedural code, the only difference being that is runs on the server. Today, you would probably put this type of logic in a stored procedure or an appserver. None of these is an option for you, so I guess that you have to put it in your code. Really, it's not that much of an issue, and the performance gains from those days or doing things this way are way less now. In some cases, cleaver SQL constructs can be used for simple conditional processing, but it's not generally applicable. Good luck to you /Karlsson Luke Venediger wrote: Hi Anders, Thanks for that. Yes, I have come from an MSSQL environment, and I'm using .Net 1.1 with the MySQL Connector/Net. The idea behind using conditional statements was to assign a query to a business task. For example, I could write a query to handle adding items to a shopping cart. In the query, before I add the item, I need to check if the item is in stock. If it's not in stock, I need to return a result set that indicates there has been an error, i.e. "There is no stock of the requested item." Putting this logic in my code means having to execute a number of statements with code checks in-between, where I would rather only execute one query. Cheers, Luke Venediger. On Fri, 22 Oct 2004 18:49:54 +0200, Anders Karlsson <[EMAIL PROTECTED]> wrote: I don't know why you want to do this, but looking at your sybtax, it seems like you come from a Sybase or SQL Server environment. What you are trying to achieve is the way it is done in Transact SQL, where the conditional statements and stuff like that which are typically used in stored procedures, may also be used outside stored procedures. As someone else suggested, a real programming environment might be more appropriate, with a MySQL connection. Like Perl or so. Depite this, the simple example you may well be done with reasonably normal SQL in MySQL like this: select IF(@val = 1,'It is 1', 'It is not one'); But I suspect you want something more advanced than this, as this is not the most useful program the world has seen. (But not the least useful either). /Karlsson Luke Venediger wrote: Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT "The value is 1"; ELSE SELECT "The value is not 1"; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm <___/ www.mysql.com Cellphone: +46 708 608121 -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm <___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **[SPAM]** Conditional statement: IF
T-SQL style procedural scripting will not be initially available outside of stored procedures. Stored procedures are new to 5.0 (still under development) so what you want to code is not available, yet. You will have to make program branch choices in a programming language as the SQL engine currently does not support that construction in ad hoc SQL scripts. However, you were shown two methods by which a query can return optional values. Perhaps you can minimize your round-trips to the server by using one of those methods. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Luke Venediger <[EMAIL PROTECTED]> wrote on 10/22/2004 01:48:41 PM: > Hi, > > Apologies, yes I have RTFM, and should've clarified my question. I > actually need to do something more like this: > > IF(@SomeValue = 1) > BEGIN > THEN > SELECT "Data" as Column1, > "MoreData" as Column2 > ELSE > SELECT "SomeValue is not = 1" as Error > END IF > > Thanks, > Luke Venediger. > > > > On Fri, 22 Oct 2004 09:39:21 -0500, Jay Blanchard > <[EMAIL PROTECTED]> wrote: > > [snip] > > I'm trying to write a conditional statement in my query. Is the > > following possible in MySQL? > > > > IF(@SomeValue = 1) > > THEN > > SELECT "The value is 1"; > > ELSE > > SELECT "The value is not 1"; > > END IF > > > > I've tried different variations and nothing seems to be working (I'm > > getting syntax errors). I'm using MySQL 4.0.18. Any help would be > > greatly appreciated. > > [/snip] > > > > Have you RTFM? > > > > SELECT IF(@SomeValue = 1, 'The value is 1', 'The value is not 1') > > > > http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html > > > > > -- > Get Firefox Browser! Reclaim the web. http://getfirefox.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: **[SPAM]** Conditional statement: IF
Hi, Apologies, yes I have RTFM, and should've clarified my question. I actually need to do something more like this: IF(@SomeValue = 1) BEGIN THEN SELECT "Data" as Column1, "MoreData" as Column2 ELSE SELECT "SomeValue is not = 1" as Error END IF Thanks, Luke Venediger. On Fri, 22 Oct 2004 09:39:21 -0500, Jay Blanchard <[EMAIL PROTECTED]> wrote: > [snip] > I'm trying to write a conditional statement in my query. Is the > following possible in MySQL? > > IF(@SomeValue = 1) > THEN > SELECT "The value is 1"; > ELSE > SELECT "The value is not 1"; > END IF > > I've tried different variations and nothing seems to be working (I'm > getting syntax errors). I'm using MySQL 4.0.18. Any help would be > greatly appreciated. > [/snip] > > Have you RTFM? > > SELECT IF(@SomeValue = 1, 'The value is 1', 'The value is not 1') > > http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html > -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditional statement: IF
Hi Anders, Thanks for that. Yes, I have come from an MSSQL environment, and I'm using .Net 1.1 with the MySQL Connector/Net. The idea behind using conditional statements was to assign a query to a business task. For example, I could write a query to handle adding items to a shopping cart. In the query, before I add the item, I need to check if the item is in stock. If it's not in stock, I need to return a result set that indicates there has been an error, i.e. "There is no stock of the requested item." Putting this logic in my code means having to execute a number of statements with code checks in-between, where I would rather only execute one query. Cheers, Luke Venediger. On Fri, 22 Oct 2004 18:49:54 +0200, Anders Karlsson <[EMAIL PROTECTED]> wrote: > I don't know why you want to do this, but looking at your sybtax, it > seems like you come > from a Sybase or SQL Server environment. What you are trying to achieve > is the > way it is done in Transact SQL, where the conditional statements and > stuff like that which > are typically used in stored procedures, may also be used outside stored > procedures. > > As someone else suggested, a real programming environment might be more > appropriate, > with a MySQL connection. Like Perl or so. > > Depite this, the simple example you may well be done with reasonably > normal SQL in MySQL > like this: > > select IF(@val = 1,'It is 1', 'It is not one'); > > But I suspect you want something more advanced than this, as this is not > the most useful program the > world has seen. (But not the least useful either). > > /Karlsson > Luke Venediger wrote: > > >Hi, > > > >I'm trying to write a conditional statement in my query. Is the > >following possible in MySQL? > > > >IF(@SomeValue = 1) > >THEN > > SELECT "The value is 1"; > >ELSE > > SELECT "The value is not 1"; > >END IF > > > >I've tried different variations and nothing seems to be working (I'm > >getting syntax errors). I'm using MySQL 4.0.18. Any help would be > >greatly appreciated. > > > >Thanks, > >Luke Venediger. > > > > > > > > -- > __ ___ ___ __ >/ |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer > /_/ /_/\_, /___/\___\_\___/ Stockholm > <___/ www.mysql.com Cellphone: +46 708 608121 > > -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
I would simplify this search by breaking the query into two steps, just as
you described. First locate all of the resumes where section 1 contains
the bit of text you are looking for then use those results to get the full
resume (all 6 sections)
There are 3 ways to do this. One is a single-query method but it will
require a rather complex set of JOINS and I don't think the response from
it will be good enough (it should be quite slow, based on my experience),
one uses a subquery which your current version of MySQL may not support
(last I read, you were on 3.23.58), and the third which I show below uses
a TEMP TABLE:
CREATE TEMPORARY TABLE tmpCandidates
SELECT DISTINCT Candidate_ID
FROM resume
WHERE Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country
FROM tmpCandidates tc
INNER JOIN wow.candidate c
ON tc.Candidate_ID = c.Candidate_ID;
If you had 200 resumes from 150 candidates where section 1 matched
"peoplesoft" and each resume had 6 sections, this will give you 150
candidate returns (you didn't get all 200 resume hits because I eliminated
the duplicate candidate matches with the DISTINCT keyword). If you want to
get each set of candidate information along with their resume information,
you will end up with all columns of the candidate information being
duplicated 6 times, once for each resume section. That query would look
like:
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country, r2.section_id,
r2.section_value
FROM tmpCandidates tc
INNER JOIN resume r
on r.candidate_ID = tc.candidate_id
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
This will return the full 1200 rows (200 resume matches x 6 sections per
resume) because we re-introduced (by joining to the Resume table) the
multiple resumes for each candidates.
Once you have tuned your select queries to return what you want, it's
trivially simple to add the INSERT (...) clause to the beginning of it to
get the results to go into a table instead of to your client.
Keep me informed on your progress, please. Thank you for being so patient.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/22/2004 12:43:26 PM:
>
> Sorry for the delay in this. Still having problems with this one.
>
> >The second query is going to produce one row for every resume. Based on
>
> >your results, I assume you have 1000 resumes that have candidates
> >associated with them.
>
> Let's say I have 256 resulting rows for table 'resume', Section_ID 1,
> where the word 'peoplesoft' was found. What I'm really trying to do is
> get the 256 that come up in the first query, and then work with only
> that 256. The way the table 'resume' is set up is with the following
> fields:
>
> Candidate_ID
> Section_ID
> Section_Value
>
> Section_ID 1 is where all basic text in a resume is stored; there are a
> total of 6 sections, each section holding something different. I must
> search first on Section 1 for references to 'peoplesoft'. Ultimately,
> each candidate will have 6 total sections in resume - in this case, 256
> x 6 = 1536. Therefore, I would need the second query to return 1536,
> returning results for all candidates matching for Section 1, then using
> the Candidate_ID to find all resulting rows in table 'resume'.
>
> >I also notice that ALL of the fields in your select statement come from
>
> >the candidate table. If that's truly the case, why do you need to JOIN
> to
> >the resume table?
>
> See above. :) Resume material is boolean-searched, then I have to copy
> that Candidate_ID from *both* tables 'candidate' and 'resume' into
> 'candidate_erp' and 'resume_erp'.
>
> Thanks,
> Eve
>
Re: Conditional statement: IF
I don't know why you want to do this, but looking at your sybtax, it seems like you come from a Sybase or SQL Server environment. What you are trying to achieve is the way it is done in Transact SQL, where the conditional statements and stuff like that which are typically used in stored procedures, may also be used outside stored procedures. As someone else suggested, a real programming environment might be more appropriate, with a MySQL connection. Like Perl or so. Depite this, the simple example you may well be done with reasonably normal SQL in MySQL like this: select IF(@val = 1,'It is 1', 'It is not one'); But I suspect you want something more advanced than this, as this is not the most useful program the world has seen. (But not the least useful either). /Karlsson Luke Venediger wrote: Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT "The value is 1"; ELSE SELECT "The value is not 1"; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm <___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
Sorry for the delay in this. Still having problems with this one.
>The second query is going to produce one row for every resume. Based on
>your results, I assume you have 1000 resumes that have candidates
>associated with them.
Let's say I have 256 resulting rows for table 'resume', Section_ID 1,
where the word 'peoplesoft' was found. What I'm really trying to do is
get the 256 that come up in the first query, and then work with only
that 256. The way the table 'resume' is set up is with the following
fields:
Candidate_ID
Section_ID
Section_Value
Section_ID 1 is where all basic text in a resume is stored; there are a
total of 6 sections, each section holding something different. I must
search first on Section 1 for references to 'peoplesoft'. Ultimately,
each candidate will have 6 total sections in resume - in this case, 256
x 6 = 1536. Therefore, I would need the second query to return 1536,
returning results for all candidates matching for Section 1, then using
the Candidate_ID to find all resulting rows in table 'resume'.
>I also notice that ALL of the fields in your select statement come from
>the candidate table. If that's truly the case, why do you need to JOIN
to
>the resume table?
See above. :) Resume material is boolean-searched, then I have to copy
that Candidate_ID from *both* tables 'candidate' and 'resume' into
'candidate_erp' and 'resume_erp'.
Thanks,
Eve
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 20, 2004 3:50 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Determining if query will work
> SELECT *
> FROM wow.resume r
> INNER JOIN wow.candidate c
> ON c.Candidate_ID = r.Candidate_ID
> WHERE r.Section_ID = '1'
> AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
> MODE);
>
> INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
> SELECT SQL_CALC_FOUND_ROWS c.candidate_id, r.Section_ID,
r.Section_Value
> FROM wow.resume r
> INNER JOIN wow.candidate c
> ON r.Candidate_ID = c.Candidate_ID;
>
> INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
> First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
> Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working,
> Available, Start_Date, Location, HoldOnPeriod, Relocation,
> Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
> Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
> Interview_Availability, Interview_Contact, US_Experience,
> Location_Country) SELECT SQL_CALC_FOUND_ROWS c.candidate_id,
> c.Vendor_ID, c.Last_Name, c.First_Name, c.Middle_Initial,
> c.Condition_Type, c.Employer, c.Country_ID, c.Visa_Status, c.Dt_Visa,
> c.MMDD_Birth, c.SSN, c.CSG_Comments, c.Working, c.Available,
> c.Start_Date, c.Location, c.HoldOnPeriod, c.Relocation,
> c.Tech_Ranking, c.Comm_Ranking, c.Availability, c.Cert_Comments,
> c.Dt_Submitted, c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
> c.Interview_Availability, c.Interview_Contact, c.US_Experience,
> c.Location_Country FROM wow.resume r INNER JOIN wow.candidate c
> ON r.Candidate_ID = c.Candidate_ID;
>
> I changed the boolean search to what I was matching against (this time
> 'peoplesoft') with a resulting 256 rows.
>
> I ran the first two queries, which resulted in part 1 returning 256
> rows, part 2 affecting 1000 rows, and nothing entering into the
> database candidate_erp. The third resulted in ERROR 1136: Column count
> doesn't match value count at row 1.
The second query is going to produce one row for every resume. Based on
your results, I assume you have 1000 resumes that have candidates
associated with them.
In the third query you left out a field (c.def_rate, perhaps?). Here are
your fields lined up vertically
INSERT fieldsSELECT fields
Candidate_ID,c.candidate_id,
Vendor_ID, c.Vendor_ID,
Last_Name, c.Last_Name,
First_Name, c.First_Name,
Middle_Initial, c.Middle_Initial,
Condition_Type, c.Condition_Type,
Employer, c.Employer,
Country_ID, c.Country_ID,
Visa_Status, c.Visa_Status,
Dt_Visa,c.Dt_Visa,
MMDD_Birth, c.MMDD_Birth,
SSN,c.SSN,
CSG_Comments, c.CSG_Comments,
Working,c.Working,
Available, c.Available,
Start_Date, c.Start_Date,
Location, c.Location,
HoldOnPeriod, c.HoldOnPeriod,
Relocation, c.Relocation,
Tech_Ranking, c.Tech_Ranking,
Comm_Ranking,c.Comm_Ranking,
Availability, c.Availability,
Cert_Comments, c.Cert_Comments,
Dt_Submitted, c.Dt_Submitted,
Def_Rate,
Def_Rate_Unit, c.Def_Rate_Unit,
Other_Country, c.Other_Country,
Currency_id, c.Currency_id,
Interview_
Re: Conditional statement: IF
Are you trying to accomplish this in a program, a script or from the command line? If you are using a program, what language are you using? If you are using a script, what platform and shell are you using? (For example, Linux with the bash shell or Windows.) If you are using the command line, I'm pretty sure you can't put ifs around your SQL. Rhino - Original Message - From: "Luke Venediger" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, October 22, 2004 10:31 AM Subject: Conditional statement: IF > Hi, > > I'm trying to write a conditional statement in my query. Is the > following possible in MySQL? > > IF(@SomeValue = 1) > THEN > SELECT "The value is 1"; > ELSE > SELECT "The value is not 1"; > END IF > > I've tried different variations and nothing seems to be working (I'm > getting syntax errors). I'm using MySQL 4.0.18. Any help would be > greatly appreciated. > > Thanks, > Luke Venediger. > > -- > Get Firefox Browser! Reclaim the web. http://getfirefox.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]** Conditional statement: IF
[snip] I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT "The value is 1"; ELSE SELECT "The value is not 1"; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. [/snip] Have you RTFM? SELECT IF(@SomeValue = 1, 'The value is 1', 'The value is not 1') http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Conditional statement: IF
Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT "The value is 1"; ELSE SELECT "The value is not 1"; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: match a fulltext search with a "-" dash, can't match if - exist
Louie,
Mysql treats the dash as a word separator. There's no way to change
that unless you change the source code and recompile Mysql.
Also, the default min word length is 4. So not only is "E" not
matched; even "018" will not be matched. The min word length is easily
configurable, though. Put a .cnf file in the conf dir of your
installation, and have the line:
ft_min_word_len=3
and restart your mysql server. If "018" is not a common word in your
index, then it will show up as a match.
-linda
On Wed, 20 Oct 2004 08:17:06 +0100, Chris Elsworth <[EMAIL PROTECTED]> wrote:
> On Mon, Oct 18, 2004 at 11:19:55AM +0800, Louie Miranda wrote:
> >
> > mysql> select * from fullsearch where match (title,body) against ('018-E');
> > Empty set (0.00 sec)
> >
> >
> > it returns an empty set, is it possible to also search with "-" dash? chars?
>
> If I remember correctly, you need to pass the string as a phrase to fulltext:
>
> select * from fullsearch where match (title,body) against ('"018-E"');
>
> I'd prefer being able to escape the - with \, since using a phrase has
> other disadvantages (like partial word matching goes out the window),
> but you can't.
>
> --
> 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: Determining if query will work
See embedded comments
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/20/2004 03:33:41 PM:
>
> Based on Shawn's tips, I revised the query to read thusly:
>
> SELECT *
> FROM wow.resume r
> INNER JOIN wow.candidate c
> ON c.Candidate_ID = r.Candidate_ID
> WHERE r.Section_ID = '1'
> AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
> MODE);
>
> INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
> SELECT SQL_CALC_FOUND_ROWS c.candidate_id, r.Section_ID, r.Section_Value
> FROM wow.resume r
> INNER JOIN wow.candidate c
> ON r.Candidate_ID = c.Candidate_ID;
>
> INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
> First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
> Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available,
> Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking,
> Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate,
> Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability,
> Interview_Contact, US_Experience, Location_Country)
> SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
> c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
> c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
> c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
> c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
> c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
> c.Other_Country, c.Currency_id, c.Interview_Availability,
> c.Interview_Contact, c.US_Experience, c.Location_Country FROM wow.resume
> r
> INNER JOIN wow.candidate c
> ON r.Candidate_ID = c.Candidate_ID;
>
> I changed the boolean search to what I was matching against (this time
> 'peoplesoft') with a resulting 256 rows.
>
> I ran the first two queries, which resulted in part 1 returning 256
> rows, part 2 affecting 1000 rows, and nothing entering into the database
> candidate_erp. The third resulted in ERROR 1136: Column count doesn't
> match value count at row 1.
The second query is going to produce one row for every resume. Based on
your results, I assume you have 1000 resumes that have candidates
associated with them.
In the third query you left out a field (c.def_rate, perhaps?). Here are
your fields lined up vertically
INSERT fieldsSELECT fields
Candidate_ID,c.candidate_id,
Vendor_ID, c.Vendor_ID,
Last_Name, c.Last_Name,
First_Name, c.First_Name,
Middle_Initial, c.Middle_Initial,
Condition_Type, c.Condition_Type,
Employer, c.Employer,
Country_ID, c.Country_ID,
Visa_Status, c.Visa_Status,
Dt_Visa,c.Dt_Visa,
MMDD_Birth, c.MMDD_Birth,
SSN,c.SSN,
CSG_Comments, c.CSG_Comments,
Working,c.Working,
Available, c.Available,
Start_Date, c.Start_Date,
Location, c.Location,
HoldOnPeriod, c.HoldOnPeriod,
Relocation, c.Relocation,
Tech_Ranking, c.Tech_Ranking,
Comm_Ranking,c.Comm_Ranking,
Availability, c.Availability,
Cert_Comments, c.Cert_Comments,
Dt_Submitted, c.Dt_Submitted,
Def_Rate,
Def_Rate_Unit, c.Def_Rate_Unit,
Other_Country, c.Other_Country,
Currency_id,c.Currency_id,
Interview_Availability, c.Interview_Availability,
Interview_Contact, c.Interview_Contact,
US_Experience, c.US_Experience,
Location_Country c.Location_Country
I also notice that ALL of the fields in your select statement come from
the candidate table. If that's truly the case, why do you need to JOIN to
the resume table?
I would just change your FROM clause to read:
FROM wow.candidate c
This way you don't get 1000 records in your candidates_erp table when you
don't have that many to start with. The duplicates would be caused by the
JOIN to resume because I know that some of your candidates have more than
one resume.
You are doing great so far. You are almost there.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
>
>
> >Yes, you were close. May I suggest a little reading?
>
> I'll give those links a look, though often the reading in there seems a
> bit thick!
>
> - Eve
>
>
>
>
>
RE: Determining if query will work
Based on Shawn's tips, I revised the query to read thusly:
SELECT *
FROM wow.resume r
INNER JOIN wow.candidate c
ON c.Candidate_ID = r.Candidate_ID
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, r.Section_ID, r.Section_Value
FROM wow.resume r
INNER JOIN wow.candidate c
ON r.Candidate_ID = c.Candidate_ID;
INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available,
Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking,
Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate,
Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability,
Interview_Contact, US_Experience, Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country FROM wow.resume
r
INNER JOIN wow.candidate c
ON r.Candidate_ID = c.Candidate_ID;
I changed the boolean search to what I was matching against (this time
'peoplesoft') with a resulting 256 rows.
I ran the first two queries, which resulted in part 1 returning 256
rows, part 2 affecting 1000 rows, and nothing entering into the database
candidate_erp. The third resulted in ERROR 1136: Column count doesn't
match value count at row 1.
>Yes, you were close. May I suggest a little reading?
I'll give those links a look, though often the reading in there seems a
bit thick!
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining if query will work
First a bit of friendly advice:
When performing an INSERT...SELECT... it is better to explicitly list the
source columns (part of the SELECT statement). Doing so will ensure that
if you re-order some columns or if you add/drop columns to/from one of the
tables participating in the SELECT portion of your statement that your
INSERT will still function. It is also the only way to perform an INSERT
so that an auto_increment column works properly. I know you end up with
longer statements but the statements are less likely to break and they are
easier to debug.
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/20/2004 01:31:59 PM:
>
> I am attempting to copy data from 1 table into another, based on certain
> criteria. I have set up the following queries, but am unsure if they
> will function properly:
>
> #This pulls back all data for matching candidates with keyword from
> RESUME and CANDIDATE
> SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
> = '1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
> c.Candidate_ID = r.Candidate_ID;
This query has a JOIN in it without an ON condition. Creating a JOIN in
this fashion relies on the optimizer to detect that you have a valid
table-to-table condition in your WHERE statement and apply it correctly.
If for some reason the optimizer misses this condition (c.Candidate_ID =
r.Candidate_ID) during the JOIN phase of the query (or if you neglected to
make one), you will produce a Cartesian product (every possible
combination of each row from both tables) of the tables you are joining
and will have to filter out all but the matching rows during the WHERE
phase of the query. I would re-write it this way:
SELECT *
FROM wow.resume r
INNER JOIN wow.candidate c
ON c.Candidate_ID = r.Candidate_ID
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE);
>
> #This must copy all corresponding fields from RESUME into RESUME_ERP
> *for each candidate ID previously found*
> INSERT INTO wow.resume_erp
> (Candidate_ID, Section_ID, Section_Value)
> SELECT SQL_CALC_FOUND_ROWS *
> FROM wow.resume r
> INNER JOIN wow.candidate c;
Here is an example of a INSERT...SELECT... that does not specify the
source columns. This is also an example of a Cartesian product. If you
have 12000 resumes and 1 candidates then the resume_erp table will end
up with 1200 (12 million) rows in it (12000 x 1). This is probably
NOT what you wanted. I would probably rewrite it this way
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, r.Section_ID, r.Section_Value
FROM wow.resume r
INNER JOIN wow.candidate c
ON r.candidate_ID = c.candidate_ID;
I don't know all of your field names so I had to make up an ON clause.
Change the field names to match those actually in your system.
> #This must copy all corresponding fields from CANDIDATE into
> CANDIDATE_ERP *for each candidate ID previously found*
> INSERT INTO wow.candidate_erp
> (Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
> Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
> SSN, CSG_Comments, Working, Available, Start_Date, Location,
> HoldOnPeriod, Relocation, Tech_Ranking, Comm_Ranking, Availability,
> Cert_Comments, Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country,
> Currency_id, Interview_Availability, Interview_Contact, US_Experience,
> Location_Country)
> SELECT SQL_CALC_FOUND_ROWS *
> FROM wow.resume r
> INNER JOIN wow.candidate c;
This is another example of a Cartesian product. This also another example
of a "naked" SELECT clause.
Specify your source columns (do not use "SELECT SQL_CALC_FOUND_ROWS *")
and add an ON constraint to your INNER JOIN so that the appropriately
related rows are matched up.
>
> Am I on the right track?
Yes, you were close. May I suggest a little reading?
http://dev.mysql.com/doc/mysql/en/JOIN.html
http://dev.mysql.com/doc/mysql/en/INSERT.html
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html
>
> Please read further for the kind of data returned, if necessary.
>
> Thanks,
> Eve
>
Determining if query will work
I am attempting to copy data from 1 table into another, based on certain
criteria. I have set up the following queries, but am unsure if they
will function properly:
#This pulls back all data for matching candidates with keyword from
RESUME and CANDIDATE
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
= '1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
#This must copy all corresponding fields from RESUME into RESUME_ERP
*for each candidate ID previously found*
INSERT INTO wow.resume_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
#This must copy all corresponding fields from CANDIDATE into
CANDIDATE_ERP *for each candidate ID previously found*
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN, CSG_Comments, Working, Available, Start_Date, Location,
HoldOnPeriod, Relocation, Tech_Ranking, Comm_Ranking, Availability,
Cert_Comments, Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country,
Currency_id, Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Am I on the right track?
Please read further for the kind of data returned, if necessary.
Thanks,
Eve
--
Here's the kind of data returned...
| Candidate_ID | Section_ID | Section_Value
| Candidate_ID | Vendor_ID | Last_Name | First_Name | Middle_Initial
| Condition_Type | Employer| Country_ID | Visa_Status | Dt_Visa
| MMDD_Birth | SSN| CSG_Comments
| Working | Available | Start_Date | Location| HoldOnPeriod |
Relocation | Tech_Ranking | Comm_Ranking | Availability |
Cert_Comments
| Dt_Submitted | Def_Rate | Def_Rate_Unit | Other_Country |
Currency_id | Interview_Availability | Interview_Contact|
US_Experience | Location_Country |
| 1185 | 1 |Information Technology
Strategic Systems Planning & Development / Project Management / E-
Commerce / Technical Leadership/ Architectural Guidance/ Systems Design
& Implementation / Organizational Structure
_
Management career encompasses a strong functional, technical, financial
and business acumen, including executive leadership in application
solutions, implementation, IT operations and teamwork. Experienced in
global business and information technology, ERP systems (MAPICS, BPCS,
JD Edwards, BAAN, ASK, Data 3. Having considerable experience and
expertise in the areas of distribution/logistics, materials management,
manufacturing, systems design and implementation, LAN/WANs, contract
negotiations and legal obligations, in addition to strategic business
planning.
SELECTED CAREER ACCOMPLISHMENTS
ó Designed and implemented a corporate-wide inventory and order
entry system that improved customer service and saved $70 million
dollars of inventory investment.
ó Automated internal operations through workflow/imaging systems
with an investment of $85,000 that saved $750,000 in annual engineering,
sales and accounting costs.
ó Instituted a technology leasing program that freed-up $8 million
in annual operating capital allowing for significant improvements in
infrastructure.
ó Implemented customer-focused manufacturing planning and
scheduling system that enabled sales growth of 70% in a world wide heavy
equipment machinery manufacturing company.
ó Consolidated contracts cutting cost for voice and data
networking services with annual savings of $200,000.
ó Re-implemented a failed ERP (BPCS) project with major gains in
on-time shipments, lower supply chain cost and improved management
control with an estimated value of almost $8 million dollars to the
corporation.
ó Created and marketed a successful educational game that taught
the basic principles of teamwork.
Systems experience includes: Enterprise Resource Planning (BPCS,
MAPICS), BPCS - Supply Chain Management, Human Resource Information
Systems (HRIS), CAD/CAM, Accounting and Financial reporting systems,
MRPII Class A certification, Configuration Management, Production
Scheduling, Forecasting, Inventory Management, Distribution (including
DRP), Purchasing, Warehousing, Imaging and Work Flow, and state of the
art manufacturing technologies, Process Flow, CIM, TQM. Sales Force
Automation and CRM.
| 1185 | 462 | Bishop | Jack|
| E | | 1 | N/A |
| | 123456 |
| 0 | 1 | 7/21/2003 | Lanchster PA| 24 hrs | No
| 5| 5| Immediate|
|
Determining if query will work
I am attempting to copy data from 1 table into another, based on certain
criteria. I have set up the following queries, but am unsure if they
will function properly:
#This pulls back all data for matching candidates with keyword from
RESUME and CANDIDATE
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
= '1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
#This must copy all corresponding fields from RESUME into RESUME_ERP
*for each candidate ID previously found*
INSERT INTO wow.resume_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
#This must copy all corresponding fields from CANDIDATE into
CANDIDATE_ERP *for each candidate ID previously found*
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN, CSG_Comments, Working, Available, Start_Date, Location,
HoldOnPeriod, Relocation, Tech_Ranking, Comm_Ranking, Availability,
Cert_Comments, Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country,
Currency_id, Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Am I on the right track?
Please read further for the kind of data returned, if necessary.
Thanks,
Eve
--
Here's the kind of data returned...
| Candidate_ID | Section_ID | Section_Value
| Candidate_ID | Vendor_ID | Last_Name | First_Name | Middle_Initial
| Condition_Type | Employer| Country_ID | Visa_Status | Dt_Visa
| MMDD_Birth | SSN| CSG_Comments
| Working | Available | Start_Date | Location| HoldOnPeriod |
Relocation | Tech_Ranking | Comm_Ranking | Availability |
Cert_Comments
| Dt_Submitted | Def_Rate | Def_Rate_Unit | Other_Country |
Currency_id | Interview_Availability | Interview_Contact|
US_Experience | Location_Country |
| 1185 | 1 |Information Technology
Strategic Systems Planning & Development / Project Management / E-
Commerce / Technical Leadership/ Architectural Guidance/ Systems Design
& Implementation / Organizational Structure
_
Management career encompasses a strong functional, technical, financial
and business acumen, including executive leadership in application
solutions, implementation, IT operations and teamwork. Experienced in
global business and information technology, ERP systems (MAPICS, BPCS,
JD Edwards, BAAN, ASK, Data 3. Having considerable experience and
expertise in the areas of distribution/logistics, materials management,
manufacturing, systems design and implementation, LAN/WANs, contract
negotiations and legal obligations, in addition to strategic business
planning.
SELECTED CAREER ACCOMPLISHMENTS
ó Designed and implemented a corporate-wide inventory and order
entry system that improved customer service and saved $70 million
dollars of inventory investment.
ó Automated internal operations through workflow/imaging systems
with an investment of $85,000 that saved $750,000 in annual engineering,
sales and accounting costs.
ó Instituted a technology leasing program that freed-up $8 million
in annual operating capital allowing for significant improvements in
infrastructure.
ó Implemented customer-focused manufacturing planning and
scheduling system that enabled sales growth of 70% in a world wide heavy
equipment machinery manufacturing company.
ó Consolidated contracts cutting cost for voice and data
networking services with annual savings of $200,000.
ó Re-implemented a failed ERP (BPCS) project with major gains in
on-time shipments, lower supply chain cost and improved management
control with an estimated value of almost $8 million dollars to the
corporation.
ó Created and marketed a successful educational game that taught
the basic principles of teamwork.
Systems experience includes: Enterprise Resource Planning (BPCS,
MAPICS), BPCS - Supply Chain Management, Human Resource Information
Systems (HRIS), CAD/CAM, Accounting and Financial reporting systems,
MRPII Class A certification, Configuration Management, Production
Scheduling, Forecasting, Inventory Management, Distribution (including
DRP), Purchasing, Warehousing, Imaging and Work Flow, and state of the
art manufacturing technologies, Process Flow, CIM, TQM. Sales Force
Automation and CRM.
| 1185 | 462 | Bishop | Jack|
| E | | 1 | N/A |
| | 123456 |
| 0 | 1 | 7/21/2003 | Lanchster PA| 24 hrs | No
| 5| 5| Immediate|
|
Re: match a fulltext search with a "-" dash, can't match if - exist
On Mon, Oct 18, 2004 at 11:19:55AM +0800, Louie Miranda wrote:
>
> mysql> select * from fullsearch where match (title,body) against ('018-E');
> Empty set (0.00 sec)
>
>
> it returns an empty set, is it possible to also search with "-" dash? chars?
If I remember correctly, you need to pass the string as a phrase to fulltext:
select * from fullsearch where match (title,body) against ('"018-E"');
I'd prefer being able to escape the - with \, since using a phrase has
other disadvantages (like partial word matching goes out the window),
but you can't.
--
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: match a fulltext search with a "-" dash, can't match if - exist
> mysql> select * from fullsearch where match (title,body) against
('018-E');
> Empty set (0.00 sec)
>
>
> it returns an empty set, is it possible to also search with "-" dash?
chars?
I'm not an expert but others will correct me :
In a fulltext search, the search string must be at least 4 characters ?
Otherwise, Mysql ignore it ?
Bertrand.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
match a fulltext search with a "-" dash, can't match if - exist
this is a working example i found on mysql.com
this is my example of fullsearch
mysql> desc fullsearch;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| id| int(10) unsigned | | PRI | NULL| auto_increment |
| title | varchar(200) | YES | MUL | NULL||
| body | text | YES | | NULL||
+---+--+--+-+-++
3 rows in set (0.00 sec)
my data:
mysql> select * from fullsearch;
++---+---+
| id | title | body |
++---+---+
| 1 | MySQL Tutorial| DBMS stands for DataBase ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 6 | 018-E | Test for Title Item COde search language, etc |
| 7 | MySQL Security| When configured properly, MySQL ... |
++---+---+
my search the title with a "-" code
mysql> select * from fullsearch where match (title,body) against ('018-E');
Empty set (0.00 sec)
it returns an empty set, is it possible to also search with "-" dash? chars?
--
Louie Miranda
http://www.axishift.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show databases shows all even if no rights;
On 10/17/2004 7:12 AM, Michael J. Pawlowsky <[EMAIL PROTECTED]> wrote: > All priviliges for that user in mysql.user are set to N. > I know this is hard to read but here are the outputs from user and db. [snip] > Paul DuBois wrote: > >> At 10:02 -0400 10/16/04, Michael J. Pawlowsky wrote: >> >>> I just noticed that a restricted user to only one database can still >>> run "show databases;" >>> and see all the names of the databases in MySQL. >>> >>> You would think that it would only return the databases that that user >>> is allowed to connect to. >>> >>> Is there a way I can show only those databases that he has rights to >>> without giving him rights to the mysql database to use the db table? >> >> >> Make sure that the user doesn't have any global privileges that apply >> to databases. If the user has such a privilege, SHOW DATABASES will >> display all databases. (To check this, look at the privilege columns >> in the mysql.user table for the user's account record.) Your grant tables suggest that you are not running MySQL 4.0. As of 4.0.2, the behavior you describe disappears: http://dev.mysql.com/doc/mysql/en/Show_database_info.html Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show databases shows all even if no rights;
All priviliges for that user in mysql.user are set to N. I know this is hard to read but here are the outputs from user and db. +---++--+-+-+--- --+-+-+---+-+---+--- ---+---++-+++ | Host | User | Password | Select_priv | Insert_priv | Update_pri v | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Proces s_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +---++--+-+-+--- --+-+-+---+-+---+--- ---+---++-+++ | localhost | fhgweb | x | N | N | N | N | N | N | N | N | N | N | N | N | N | N | +---++--+-+-+--- --+-+-+---+-+---+--- ---+---++-+++ and from the db table... mysql> select * from db where User like 'fhgweb'; +---+---++-+-+-+ -+-+---++-++ + | Host | Db| User | Select_priv | Insert_priv | Update_priv | Delete_ priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alt er_priv | +---+---++-+-+-+ -+-+---++-++ + | localhost | fhgdb | fhgweb | Y | Y | Y | Y | N | N | N | N | N | N | +---+---++-+-+-+ -+-+---++-++ + 1 row in set (0.01 sec) Paul DuBois wrote: At 10:02 -0400 10/16/04, Michael J. Pawlowsky wrote: I just noticed that a restricted user to only one database can still run "show databases;" and see all the names of the databases in MySQL. You would think that it would only return the databases that that user is allowed to connect to. Is there a way I can show only those databases that he has rights to without giving him rights to the mysql database to use the db table? Make sure that the user doesn't have any global privileges that apply to databases. If the user has such a privilege, SHOW DATABASES will display all databases. (To check this, look at the privilege columns in the mysql.user table for the user's account record.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show databases shows all even if no rights;
At 10:02 -0400 10/16/04, Michael J. Pawlowsky wrote: I just noticed that a restricted user to only one database can still run "show databases;" and see all the names of the databases in MySQL. You would think that it would only return the databases that that user is allowed to connect to. Is there a way I can show only those databases that he has rights to without giving him rights to the mysql database to use the db table? Make sure that the user doesn't have any global privileges that apply to databases. If the user has such a privilege, SHOW DATABASES will display all databases. (To check this, look at the privilege columns in the mysql.user table for the user's account record.) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show databases shows all even if no rights;
I just noticed that a restricted user to only one database can still run "show databases;" and see all the names of the databases in MySQL. You would think that it would only return the databases that that user is allowed to connect to. Is there a way I can show only those databases that he has rights to without giving him rights to the mysql database to use the db table? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 questions: hostname variable and exit if slave
Regarding number 1, you might be able to do something with this:
mysql> show variables like 'pid%';
+---+-+
| Variable_name | Value |
+---+-+
| pid_file | /var/lib/mysql/tatooine.xyzcorp.com.pid |
+---+-+
Regarding number 2 (as well as #1), my inclination is to reach for perl,
but I don't know if that's an option for you.
HTH,
Ted
At 11:01 PM 10/6/2004, you wrote:
I have 2 unrelated questions:
1) Is there any way to get the hostname that the server is
running on from within mysql?
All I can come up with is (pseudo coded):
mysql> system echo insert mysql.hostname `hostname` ... >
hostname.sql
mysql> source hostname.sql
Does mysql make it available as a variable somewhere? I have scoured
the docs to find nothing obvious.
Note that I am not interested in where I am connected from
(select user();).
2) Is there a way to exit an sql script at the top of the
script if it turns out that it is running on a slave in a
replication scenario?
... something like:
if slave then exit;
(followed by more sql)
A 'show slave status' dumps something like a table, but I don't
see how to save any of the info in a variable that I might
later use in a case statement. Basically, if the results of
show slave status aren't zip, I should exit because I am the/a
slave.
Thanks for any clues!
Brian
--
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]
2 questions: hostname variable and exit if slave
I have 2 unrelated questions:
1) Is there any way to get the hostname that the server is
running on from within mysql?
All I can come up with is (pseudo coded):
mysql> system echo insert mysql.hostname `hostname` ... > hostname.sql
mysql> source hostname.sql
Does mysql make it available as a variable somewhere? I have scoured
the docs to find nothing obvious.
Note that I am not interested in where I am connected from
(select user();).
2) Is there a way to exit an sql script at the top of the
script if it turns out that it is running on a slave in a
replication scenario?
... something like:
if slave then exit;
(followed by more sql)
A 'show slave status' dumps something like a table, but I don't
see how to save any of the info in a variable that I might
later use in a case statement. Basically, if the results of
show slave status aren't zip, I should exit because I am the/a
slave.
Thanks for any clues!
Brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
Laercio Xisto Braga Cavalcanti wrote: When you use the replace command if the row does not exist it is inserted. MySQL Reference Manual: Section 14.1.6 REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted Read what you quoted. The old record is *deleted* if it exists, and then a new record is inserted. So he wouldn't be able to get the incremented count. -- Keith Ivey <[EMAIL PROTECTED]> Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
