RE: (if !update then insert) sequence - result Duplicate key :(
Hi, 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 Regards, Laercio. -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs Sent: quinta-feira, 30 de setembro de 2004 11:39 To: [EMAIL PROTECTED] Subject: Re: (if !update then insert) sequence - result Duplicate key :( In article <[EMAIL PROTECTED]>, "Laercio Xisto Braga Cavalcanti" <[EMAIL PROTECTED]> writes: > Hi, > To solve this you can use the REPLACE command. The problem is that Aleksandr wants to increment a counter, not set it to some fixed value. How could you use REPLACE for that? -- 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: (if !update then insert) sequence - result Duplicate key :(
In article <[EMAIL PROTECTED]>, "Laercio Xisto Braga Cavalcanti" <[EMAIL PROTECTED]> writes: > Hi, > To solve this you can use the REPLACE command. The problem is that Aleksandr wants to increment a counter, not set it to some fixed value. How could you use REPLACE for that? -- 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 :(
Hi,
To solve this you can use the REPLACE command.
Regards,
Laercio.
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs
Sent: quinta-feira, 30 de setembro de 2004 08:25
To: [EMAIL PROTECTED]
Subject: Re: (if !update then insert) sequence - result Duplicate key :(
In article <[EMAIL PROTECTED]>,
"Aleksandr V. Dyomin" <[EMAIL PROTECTED]> writes:
> $key='somekeyvalue';
> dbquery("update sometable set count=count+1 where keyfield='$key'");
> if(mysql_affected_rows()<1)
> dbquery('insert into sometable set keyfield='$key', count=1');
> ---
> First question: this is good method?
It's good if you expect the UPDATE normally to succeed. Otherwise, you
should first try the INSERT.
> Second... My script work on many different hosts with different
> hardware, os(only Linux or FreeBSD), and different PHP and MySQL
> version. It works fine excepting one thing... Sometime happens errors
> like:
> MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert
> into sometable set keyfield='somekeyvalue', count=1
> Why this happens? On some hosts this messageis very rare, but on
> others
> - so often... I cant understand reason :(
You have a race condition:
* Client 1 tries UPDATE, sees that it fails
* Client 2 tries UPDATE, sees that it fails
* Client 1 does INSERT - okay
* Client 2 does INSERT - duplictae key error
If you think this happens seldom, do the following:
1. Try UPDATE
2. If it fails: try INSERT
3. If it fails due to a duplicate key error: repeat step 1
--
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: (if !update then insert) sequence - result Duplicate key :(
In article <[EMAIL PROTECTED]>,
"Aleksandr V. Dyomin" <[EMAIL PROTECTED]> writes:
> $key='somekeyvalue';
> dbquery("update sometable set count=count+1 where keyfield='$key'");
> if(mysql_affected_rows()<1)
> dbquery('insert into sometable set keyfield='$key', count=1');
> ---
> First question: this is good method?
It's good if you expect the UPDATE normally to succeed. Otherwise,
you should first try the INSERT.
> Second... My script work on many different hosts with different
> hardware, os(only Linux or FreeBSD), and different PHP and MySQL
> version. It works fine excepting one thing... Sometime happens errors
> like:
> MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert into
> sometable set keyfield='somekeyvalue', count=1
> Why this happens? On some hosts this messageis very rare, but on others
> - so often... I cant understand reason :(
You have a race condition:
* Client 1 tries UPDATE, sees that it fails
* Client 2 tries UPDATE, sees that it fails
* Client 1 does INSERT - okay
* Client 2 does INSERT - duplictae key error
If you think this happens seldom, do the following:
1. Try UPDATE
2. If it fails: try INSERT
3. If it fails due to a duplicate key error: repeat step 1
--
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 :(
В Чтв, 30.09.2004, в 00:37, [EMAIL PROTECTED] пишет:
> I have another technique for this kind of data merge that speeds things up
> (a lot!) but it requires a third table to make it work
>
> Please post the results of SHOW CREATE TABLE for both the source data
> table and the destination data table and I will show you how it works.
I dont have two tables - only one...
for example:
create table links (
link varchar(20) not null default '',
count int not null default 0,
primary key(link)
)
And I need count how many times each link is clickes... trivial...
But my main trouble is 'Duplicates'...
Another example... I have table:
create table ipsinouts (
ip int not null,
inout enum('in', 'out') not null,
sitefaceid int not null,
cnt tinyint not null,
primary key (inout, ip, sitefaceid)
)
'in' mean incoming to site, in this case sitedfaceid field mean
FACE(some page) ID. 'out' mean OUT to other site(traffic trade) and
sitefaceid mean SITE ID... ip is ip2long(...) result...
So, there is I have sometime duplicates for key, but I dont see
theoretical reasons for this errors :(((
--
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 :(
В Чтв, 30.09.2004, в 00:50, Keith Ivey пишет:
> Aleksandr V. Dyomin wrote:
>
> >$key='somekeyvalue';
> >dbquery("update sometable set count=count+1 where keyfield='$key'");
> >if(mysql_affected_rows()<1)
> > dbquery('insert into sometable set keyfield='$key', count=1');
> >
>
> Another possibility would be
>
> INSERT IGNORE INTO sometable SET keyfield = '$key', count = 0;
> UPDATE sometable SET count = count + 1 WHERE keyfield = '$key';
Not too good method. There is TWO queries ALWAYS, but in my method 1
query for updates(if record exists, which is very often) and two query
for inserts(if record dont exists).
But one question: will this work faster then:
select count(...) as cnt from sometable where keyfield='$key';
...
if($row['cnt']>0)
update...
else
insert...
In other words, which method(now 3 known methods) fastes?
1) select -> (insert or update)
2) update -> (insert if fail)
3) ignoring insert -> update
???
--
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 :(
Aleksandr V. Dyomin wrote:
$key='somekeyvalue';
dbquery("update sometable set count=count+1 where keyfield='$key'");
if(mysql_affected_rows()<1)
dbquery('insert into sometable set keyfield='$key', count=1');
Another possibility would be
INSERT IGNORE INTO sometable SET keyfield = '$key', count = 0;
UPDATE sometable SET count = count + 1 WHERE keyfield = '$key';
--
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]
Re: (if !update then insert) sequence - result Duplicate key :(
I have another technique for this kind of data merge that speeds things up
(a lot!) but it requires a third table to make it work
Please post the results of SHOW CREATE TABLE for both the source data
table and the destination data table and I will show you how it works.
Thanks,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Aleksander V. Dyomin" <[EMAIL PROTECTED]> wrote on 09/29/2004 04:22:02 PM:
> Thanx for reply!
>
> В Срд, 29.09.2004, в 23:21, Jim Grill пишет:
> > > $key='somekeyvalue';
> > > dbquery("update sometable set count=count+1 where keyfield='$key'");
> > > if(mysql_affected_rows()<1)
> > > dbquery('insert into sometable set keyfield='$key', count=1');
> > > ---
> > >
> > > First question: this is good method? Or I MUST use pre-query with
> > > "select count(...) from sometable where keyfield='$key'" for
> detection:
> > > exists needle record(for update) or not(for insert)???
> >
> > This method is fine. That is to say that I have seen it before in
> older
> > code. However, you might take alook at REPLACE:
> > http://dev.mysql.com/doc/mysql/en/REPLACE.html
> If I understended this command right - its not for me.
> There is I cant set different values for different cases(record exists
> and not)...
>
> > And for MySQL 4.1.x you might take a look at INSERT ... ON DUPLICATE
> KEY
> > UPDATE
> > http://dev.mysql.com/doc/mysql/en/INSERT.html
> Not for me too, becouse my script MUST work fine on 3.x too.
>
> > > Second... My script work on many different hosts with different
> > > hardware, os(only Linux or FreeBSD), and different PHP and MySQL
> > > version. It works fine excepting one thing... Sometime happens
> errors
> > > like:
> > > MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert
> into
> > > sometable set keyfield='somekeyvalue', count=1
> >
> > I would look at your logic. What is $key? Where does it come from? If
> > `keyfield` is a unique index then you cannot have duplicate values.
> Yes. Keyfield is UNIQUE. For example we want track url hits in format:
>
> ...someurl.php?link=linkname1
> // insert ... set link='linkname1', count=1
> // or
> // update ... set count=count+1 where link='linkname1'
>
> ...someurl.php?link=linkname2
> // insert ... set link='linkname2', count=1
> // or
> // update ... set count=count+1 where link='linkname2'
> ...etc...
>
> look: for insert count value=1, for update - increment...
> can I use REPLACE in this situation? (without 'select count where
> link='linkname...' :)))
>
> > > Why this happens? On some hosts this messageis very rare, but on
> others
> > > - so often... I cant understand reason :(
> > > My script use DB locks, so two clients cant make this situation(i
> > > think).
> >
> > Do a little research to find out if your locking is working.
> its work while I testing, but I not sure that its work at moments when
> my problem is happens... But track this moments is too hard for me,
> becouse its happens not regulary and wihout any stable(visible) reasons
> :(
>
> > > Only one idea: sometime 'update' query dont work(or work, but dont
> > > return good result), and 'insert' query started... But WHY?
> >
> > Again, I would look at your logic and try to use REPLACE or INSERT ...
> ON
> > DUPLICATE KEY UPDATE.
> not for my clients :(
>
>
>
> --
> 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 :(
Thanx for reply!
В Срд, 29.09.2004, в 23:21, Jim Grill пишет:
> > $key='somekeyvalue';
> > dbquery("update sometable set count=count+1 where keyfield='$key'");
> > if(mysql_affected_rows()<1)
> > dbquery('insert into sometable set keyfield='$key', count=1');
> > ---
> >
> > First question: this is good method? Or I MUST use pre-query with
> > "select count(...) from sometable where keyfield='$key'" for
detection:
> > exists needle record(for update) or not(for insert)???
>
> This method is fine. That is to say that I have seen it before in
older
> code. However, you might take alook at REPLACE:
> http://dev.mysql.com/doc/mysql/en/REPLACE.html
If I understended this command right - its not for me.
There is I cant set different values for different cases(record exists
and not)...
> And for MySQL 4.1.x you might take a look at INSERT ... ON DUPLICATE
KEY
> UPDATE
> http://dev.mysql.com/doc/mysql/en/INSERT.html
Not for me too, becouse my script MUST work fine on 3.x too.
> > Second... My script work on many different hosts with different
> > hardware, os(only Linux or FreeBSD), and different PHP and MySQL
> > version. It works fine excepting one thing... Sometime happens
errors
> > like:
> > MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert
into
> > sometable set keyfield='somekeyvalue', count=1
>
> I would look at your logic. What is $key? Where does it come from? If
> `keyfield` is a unique index then you cannot have duplicate values.
Yes. Keyfield is UNIQUE. For example we want track url hits in format:
...someurl.php?link=linkname1
// insert ... set link='linkname1', count=1
// or
// update ... set count=count+1 where link='linkname1'
...someurl.php?link=linkname2
// insert ... set link='linkname2', count=1
// or
// update ... set count=count+1 where link='linkname2'
...etc...
look: for insert count value=1, for update - increment...
can I use REPLACE in this situation? (without 'select count where
link='linkname...' :)))
> > Why this happens? On some hosts this messageis very rare, but on
others
> > - so often... I cant understand reason :(
> > My script use DB locks, so two clients cant make this situation(i
> > think).
>
> Do a little research to find out if your locking is working.
its work while I testing, but I not sure that its work at moments when
my problem is happens... But track this moments is too hard for me,
becouse its happens not regulary and wihout any stable(visible) reasons
:(
> > Only one idea: sometime 'update' query dont work(or work, but dont
> > return good result), and 'insert' query started... But WHY?
>
> Again, I would look at your logic and try to use REPLACE or INSERT ...
ON
> DUPLICATE KEY UPDATE.
not for my clients :(
--
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 :(
> Hi!
>
> Sorry for my ugly English.
> I use mysql in my PHP script(for calculating INs and OUTs from site).
> And I meet some strange thing there.
>
> Example goal: store statistics for clicks on some links.
> I use next algorithm:
>
> ---
> $key='somekeyvalue';
> dbquery("update sometable set count=count+1 where keyfield='$key'");
> if(mysql_affected_rows()<1)
> dbquery('insert into sometable set keyfield='$key', count=1');
> ---
>
> First question: this is good method? Or I MUST use pre-query with
> "select count(...) from sometable where keyfield='$key'" for detection:
> exists needle record(for update) or not(for insert)???
This method is fine. That is to say that I have seen it before in older
code. However, you might take alook at REPLACE:
http://dev.mysql.com/doc/mysql/en/REPLACE.html
And for MySQL 4.1.x you might take a look at INSERT ... ON DUPLICATE KEY
UPDATE
http://dev.mysql.com/doc/mysql/en/INSERT.html
>
> Second... My script work on many different hosts with different
> hardware, os(only Linux or FreeBSD), and different PHP and MySQL
> version. It works fine excepting one thing... Sometime happens errors
> like:
> MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert into
> sometable set keyfield='somekeyvalue', count=1
I would look at your logic. What is $key? Where does it come from? If
`keyfield` is a unique index then you cannot have duplicate values.
>
> Why this happens? On some hosts this messageis very rare, but on others
> - so often... I cant understand reason :(
> My script use DB locks, so two clients cant make this situation(i
> think).
Do a little research to find out if your locking is working.
>
> Only one idea: sometime 'update' query dont work(or work, but dont
> return good result), and 'insert' query started... But WHY?
Again, I would look at your logic and try to use REPLACE or INSERT ... ON
DUPLICATE KEY UPDATE.
Regards,
Jim Grill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(if !update then insert) sequence - result Duplicate key :(
Hi!
Sorry for my ugly English.
I use mysql in my PHP script(for calculating INs and OUTs from site).
And I meet some strange thing there.
Example goal: store statistics for clicks on some links.
I use next algorithm:
---
$key='somekeyvalue';
dbquery("update sometable set count=count+1 where keyfield='$key'");
if(mysql_affected_rows()<1)
dbquery('insert into sometable set keyfield='$key', count=1');
---
First question: this is good method? Or I MUST use pre-query with
"select count(...) from sometable where keyfield='$key'" for detection:
exists needle record(for update) or not(for insert)???
Second... My script work on many different hosts with different
hardware, os(only Linux or FreeBSD), and different PHP and MySQL
version. It works fine excepting one thing... Sometime happens errors
like:
MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert into
sometable set keyfield='somekeyvalue', count=1
Why this happens? On some hosts this messageis very rare, but on others
- so often... I cant understand reason :(
My script use DB locks, so two clients cant make this situation(i
think).
Only one idea: sometime 'update' query dont work(or work, but dont
return good result), and 'insert' query started... But WHY?
Help me plz.
Thnx.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL for detecting if Column/Index already exists?
On Thu, 23 Sep 2004 17:20:17 -0700, Kyle Kirkland <[EMAIL PROTECTED]> wrote: > > Any chance 'IF EXISTS' being added to the 'ALTER TABLE' statements? > It sure would be nice to execute something like: > > ALTER TABLE tmp ADD INDEX joy ( to, the, world ) IF EXISTS; > Make that 'IF NOT EXISTS'. Sorry... ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL for detecting if Column/Index already exists?
At 4:00 7/27/04, Paul DuBois wrote:
>
>At 16:22 -0500 7/26/04, Ghate, Shishir wrote:
>>I looked at the SHOW COLUMNS statement and they have what I want,
>>but I need to condition off them. For example, I don't want to
>>execute an ALTER TABLE command to add a column if that column is
>>already there. I've tried IF DOES NOT EXIST SHOW COLUMN ...
>>followed by the ALTER TABLE command, but the if check doesn't seem
>>to work on SHOW COLUMNS.
>>
>>Am I missing something, or is this just something that is not supported?
>
>You can process the output of SHOW COLUMNS in your application language
>and use the result to construct the ALTER TABLE statement.
>
>I assume that you're using some kind of programming API to access MySQL
>here. If you're asking "is this supported using SQL alone?" the
>answer is no.
Any chance 'IF EXISTS' being added to the 'ALTER TABLE' statements?
It sure would be nice to execute something like:
ALTER TABLE tmp ADD INDEX joy ( to, the, world ) IF EXISTS;
This seems much more elegant than (pseudo code):
$NewIndexName = "joy";
$Results = run_query( "show index from tmp" );
$FoundIndex = 0;
while ( $Results.hasNext() )
{
$IndexName = $Results.getString( 3 );
if ( $IndexName == $NewIndexName )
{
$FoundIndex = 1;
}
}
if ( ! $FoundIndex )
{
run_update( "alter table tmp add index joy( to, the, world );
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
second write process hangs if another one is writing already,!!bu t in another database!!] bug???
From: root To: Subject: [second write process hangs if another one is writing already,!!but in another database!!] >Description: >How-To-Repeat: >Fix: >Submitter-Id: >Originator:root >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: >Severity: <[ critical ]> >Priority: <[ low | medium | high ] (one line)> >Category: mysql >Class: <[ sw-bug | doc-bug | change-request | support ] (one line)> >Release: mysql-4.0.20-standard (Official MySQL RPM) >Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.20, for pc-linux on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.20-standard Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 7 days 1 hour 31 min 24 sec Threads: 2 Questions: 104795562 Slow queries: 7 Opens: 12664 Flush tables: 1 Open tables: 62 Queries per second avg: 171.716 >C compiler:2.95.3 >C++ compiler: 2.95.3 >Environment: System: Linux malz.erlm.siemens.de 2.4.22-1.2197.nptlsmp #1 SMP Thu Jul 1 15:05:23 EDT 2004 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.3.2/specs Konfiguriert mit: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc-Version 3.3.2 20031022 (Red Hat Linux 3.3.2-1) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='gcc' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 22. Jul 16:34 /lib/libc.so.6 -> libc-2.3.2.so -rwxr-xr-x 1 root root 1570384 7. Jan 2004 /lib/libc-2.3.2.so -rw-r--r-- 1 root root 2445156 27. Okt 2003 /usr/lib/libc.a -rw-r--r-- 1 root root 204 27. Okt 2003 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' 'CXX=gcc' mit freundlichem Gruß / kind regards Daniel Beuter SIEMENS I&S IT PS 221 OP4 Network Solutions Werner von Siemens Str. 60 91052 Erlangen Fon: +49 (9131) -6303-211 Fax: +49 (9131) 7-42234 mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Re: INSERT if record NOT EXISTS
In article <[EMAIL PROTECTED]>,
Adaikalavan Ramasamy <[EMAIL PROTECTED]> writes:
> This seems more like the solution I want. I am using perl-DBI and when
> there is an error (i.e. duplicate insert), the rest of the scrip it not
> executed. But this is gives me the following error. What am I doing
> wrong ?
mysql> desc tb;
> +---+-+--+-+-++
> | Field | Type| Null | Key | Default | Extra |
> +---+-+--+-+-++
> | myID | int(11) | | PRI | NULL| auto_increment |
> | firstname | varchar(10) | YES | MUL | NULL||
> | lastname | varchar(10) | YES | | NULL||
> +---+-+--+-+-++
> 3 rows in set (0.00 sec)
mysql> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') ON
> DUPLICATE KEY UPDATE lastname = lastname;
> ERROR 1064: You have an error in your SQL syntax near 'ON DUPLICATE KEY
> UPDATE lastname = lastname' at line 1
The "ON DUPLICATE KEY..." clause is new in MySQL 4.1.0.
> Alternatively, I am looking for 'try' equivalent in perl, so that if the
> insert is duplicate, the rest of the script is still run. Thank you.
Most people use DBI and DBD::mysql to access a MySQL server from Perl.
Then you don't need something like "try". Just disable
$dbh->{RaiseError}, execute the INSERT, and then check $dbh->err.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL for detecting if Column/Index already exists?
At 16:22 -0500 7/26/04, Ghate, Shishir wrote: I looked at the SHOW COLUMNS statement and they have what I want, but I need to condition off them. For example, I don't want to execute an ALTER TABLE command to add a column if that column is already there. I've tried IF DOES NOT EXIST SHOW COLUMN ... followed by the ALTER TABLE command, but the if check doesn't seem to work on SHOW COLUMNS. Am I missing something, or is this just something that is not supported? You can process the output of SHOW COLUMNS in your application language and use the result to construct the ALTER TABLE statement. I assume that you're using some kind of programming API to access MySQL here. If you're asking "is this supported using SQL alone?" the answer is no. Thanks Shishir Ghate -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 2:21 PM To: Ghate, Shishir; [EMAIL PROTECTED] Subject: Re: SQL for detecting if Column/Index already exists? At 14:08 -0500 7/26/04, Ghate, Shishir wrote: Hello, I am trying to write a db creation script that will create a database as a well as repair an existing database. What I can't seem to find is a set of key words that will allow me to detect if a column or index already exists in a table and thus skip the creation of the column or index. I've found the proper syntax to check for table existance, but I can't find the syntax for column/index existance. If someone could point me to a set of docs or an example SQL command, that would be great. You could use the SHOW COLUMNS and SHOW INDEX statements? -- 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: SQL for detecting if Column/Index already exists?
I looked at the SHOW COLUMNS statement and they have what I want, but I need to condition off them. For example, I don't want to execute an ALTER TABLE command to add a column if that column is already there. I've tried IF DOES NOT EXIST SHOW COLUMN ... followed by the ALTER TABLE command, but the if check doesn't seem to work on SHOW COLUMNS. Am I missing something, or is this just something that is not supported? Thanks Shishir Ghate -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 2:21 PM To: Ghate, Shishir; [EMAIL PROTECTED] Subject: Re: SQL for detecting if Column/Index already exists? At 14:08 -0500 7/26/04, Ghate, Shishir wrote: >Hello, > >I am trying to write a db creation script that will create a >database as a well as repair an existing database. What I can't >seem to find is a set of key words that will allow me to detect if a >column or index already exists in a table and thus skip the creation >of the column or index. I've found the proper syntax to check for >table existance, but I can't find the syntax for column/index >existance. > >If someone could point me to a set of docs or an example SQL >command, that would be great. You could use the SHOW COLUMNS and SHOW INDEX statements? -- 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: SQL for detecting if Column/Index already exists?
At 14:08 -0500 7/26/04, Ghate, Shishir wrote: Hello, I am trying to write a db creation script that will create a database as a well as repair an existing database. What I can't seem to find is a set of key words that will allow me to detect if a column or index already exists in a table and thus skip the creation of the column or index. I've found the proper syntax to check for table existance, but I can't find the syntax for column/index existance. If someone could point me to a set of docs or an example SQL command, that would be great. You could use the SHOW COLUMNS and SHOW INDEX statements? -- 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]
SQL for detecting if Column/Index already exists?
Hello, I am trying to write a db creation script that will create a database as a well as repair an existing database. What I can't seem to find is a set of key words that will allow me to detect if a column or index already exists in a table and thus skip the creation of the column or index. I've found the proper syntax to check for table existance, but I can't find the syntax for column/index existance. If someone could point me to a set of docs or an example SQL command, that would be great. I'm using 4.1.1-alpha. Thanks much Shishir Ghate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
On Mon, 26 Jul 2004 17:47:37 +0100, Adaikalavan Ramasamy
<[EMAIL PROTECTED]> wrote:
> This seems more like the solution I want. I am using perl-DBI and when
> there is an error (i.e. duplicate insert), the rest of the scrip it not
> executed. But this is gives me the following error. What am I doing
> wrong ?
>
> mysql> desc tb;
> +---+-+--+-+-++
> | Field | Type| Null | Key | Default | Extra |
> +---+-+--+-+-++
> | myID | int(11) | | PRI | NULL| auto_increment |
> | firstname | varchar(10) | YES | MUL | NULL||
> | lastname | varchar(10) | YES | | NULL||
> +---+-+--+-+-++
> 3 rows in set (0.00 sec)
>
> mysql> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') ON
> DUPLICATE KEY UPDATE lastname = lastname;
> ERROR 1064: You have an error in your SQL syntax near 'ON DUPLICATE KEY
> UPDATE lastname = lastname' at line 1
You are probably running an older version of mysql that doesn't support this.
Try "insert ignore".
>
> Alternatively, I am looking for 'try' equivalent in perl, so that if the
> insert is duplicate, the rest of the script is still run. Thank you.
eval.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Adaikalavan Ramasamy <[EMAIL PROTECTED]> wrote on 26/07/2004
16:31:44:
> But I have another related question. How do I write an IF ELSE command
> with MYSQL. In this context, I want it to return myID if the record
> already exists, otherwise insert into database.
>
> This naive syntax does not work :
> IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
> lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
> ('Jack', 'Doe');
I don't think it is possible. There is a fairly serious architectural
split in SQL between SELECT, which is read-only and returns a two
dimensional table of results, and UPDATE (and its twins REPLACE and
INSERT), which update the database and return a single integer, usually
the count of rows updated. The command you are trying to do combines both.
The transport level would therefore not know whether to carry a
SELECT-style reply or an UPDATE-style reply.
This is a limitation on SQL, rather than MySQL. You have to do two
operations.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Yes, this does what I want and does not produce an error (which caused
the remaining MYSQL syntax not to be executed). The firstname, lastname
was for example only. In my problem, these are two different identifiers
so I am not worried about multiple dual identifiers.
Thanks to Keith Ivey, Alec Cawley, Gerald Clark and Michael Dykman for
helping with this problem.
I learnt a lot from the list today. Thanks!
Regards, Adai.
On Mon, 2004-07-26 at 18:04, Keith Ivey wrote:
> Adaikalavan Ramasamy wrote:
>
> >This naive syntax does not work :
> >IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
> >lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
> >('Jack', 'Doe');
> >
> Assuming you have the unique index on (firstname, lastname), just do
>
>INSERT IGNORE INTO tb (first_name, lastname) VALUES ('Jack', 'Doe');
>
> But how are you planning to handle multiple people named Jack Doe?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Adaikalavan Ramasamy wrote:
This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');
Assuming you have the unique index on (firstname, lastname), just do
INSERT IGNORE INTO tb (first_name, lastname) VALUES ('Jack', 'Doe');
But how are you planning to handle multiple people named Jack Doe?
--
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]
Re: INSERT if record NOT EXISTS
This seems more like the solution I want. I am using perl-DBI and when
there is an error (i.e. duplicate insert), the rest of the scrip it not
executed. But this is gives me the following error. What am I doing
wrong ?
mysql> desc tb;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| myID | int(11) | | PRI | NULL| auto_increment |
| firstname | varchar(10) | YES | MUL | NULL||
| lastname | varchar(10) | YES | | NULL||
+---+-+--+-+-++
3 rows in set (0.00 sec)
mysql> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') ON
DUPLICATE KEY UPDATE lastname = lastname;
ERROR 1064: You have an error in your SQL syntax near 'ON DUPLICATE KEY
UPDATE lastname = lastname' at line 1
Alternatively, I am looking for 'try' equivalent in perl, so that if the
insert is duplicate, the rest of the script is still run. Thank you.
Regards, Adai.
On Mon, 2004-07-26 at 17:20, Michael Dykman wrote:
> from http://dev.mysql.com/doc/mysql/en/INSERT.html:
> 14.1.4 INSERT Syntax
> INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
> [INTO] tbl_name [(col_name,...)]
> VALUES ({expr | DEFAULT},...),(...),...
> [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
>
> ...
> If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0),
> and a row is inserted that would cause a duplicate value in a UNIQUE index
> or PRIMARY KEY, an UPDATE of the old row is performed.
> ...
>
>
> there is no IF NOT EXISTS syntax in INSERT, but you could make use of the
> ON DUPLICATE KEY mechanism. Assuming you create a unique index on
> firstname, lastname, your update might read:
> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe')
> ON DUPLICATE KEY UPDATE lastname = lastname;
>
> which renders the insert neutral.
>
> On Mon, 2004-07-26 at 11:05, Adaikalavan Ramasamy wrote:
> > I am creating a small database that keeps track of users and assigns
> > them a unique user ID.
> >
> > The problem is that sometimes the users might request to be added more
> > than once (i.e. click on the submit button multiple times). Therefore I
> > only want to add users if their details (here defined by both firstname,
> > lastname) are not in the database. Example :
> >
> > CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
> > VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
> > INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
> > INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
> > INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
> > SELECT * from tb;
> > +--+---+--+
> > | myID | firstname | lastname |
> > +--+---+--+
> > |1 | John | Doe |
> > |2 | Jack | Doe |
> > |3 | John | Smith|
> > +--+---+--+
> >
> > I get syntax error with the following :
> >
> > INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
> > EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
> >
> > In this case, I want no insert because Jack Doe already exists. Can
> > anyone help me ?
> >
> > Thank you.
> >
> > Regards, Adai.
> >
> >
> >
> >
> >
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
from http://dev.mysql.com/doc/mysql/en/INSERT.html:
14.1.4 INSERT Syntax
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
...
If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0),
and a row is inserted that would cause a duplicate value in a UNIQUE index
or PRIMARY KEY, an UPDATE of the old row is performed.
...
there is no IF NOT EXISTS syntax in INSERT, but you could make use of the
ON DUPLICATE KEY mechanism. Assuming you create a unique index on
firstname, lastname, your update might read:
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe')
ON DUPLICATE KEY UPDATE lastname = lastname;
which renders the insert neutral.
On Mon, 2004-07-26 at 11:05, Adaikalavan Ramasamy wrote:
> I am creating a small database that keeps track of users and assigns
> them a unique user ID.
>
> The problem is that sometimes the users might request to be added more
> than once (i.e. click on the submit button multiple times). Therefore I
> only want to add users if their details (here defined by both firstname,
> lastname) are not in the database. Example :
>
> CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
> VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
> INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
> INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
> SELECT * from tb;
> +--+---+--+
> | myID | firstname | lastname |
> +--+---+--+
> |1 | John | Doe |
> |2 | Jack | Doe |
> |3 | John | Smith|
> +--+---+--+
>
> I get syntax error with the following :
>
> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
> EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
>
> In this case, I want no insert because Jack Doe already exists. Can
> anyone help me ?
>
> Thank you.
>
> Regards, Adai.
>
>
>
>
>
--
- michael dykman
- [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 record NOT EXISTS
Thanks Alec ! This works wonderfully.
But I have another related question. How do I write an IF ELSE command
with MYSQL. In this context, I want it to return myID if the record
already exists, otherwise insert into database.
This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');
On Mon, 2004-07-26 at 16:20, [EMAIL PROTECTED] wrote:
> Adaikalavan Ramasamy <[EMAIL PROTECTED]> wrote on 26/07/2004
> 16:05:23:
>
> > I am creating a small database that keeps track of users and assigns
> > them a unique user ID.
> >
> > The problem is that sometimes the users might request to be added more
> > than once (i.e. click on the submit button multiple times). Therefore I
> > only want to add users if their details (here defined by both firstname,
> > lastname) are not in the database. Example :
> >
> > CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
> > VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
> > INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
> > INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
> > INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
> > SELECT * from tb;
> > +--+---+--+
> > | myID | firstname | lastname |
> > +--+---+--+
> > |1 | John | Doe |
> > |2 | Jack | Doe |
> > |3 | John | Smith|
> > +--+---+--+
> >
> > I get syntax error with the following :
> >
> > INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
> > EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
> >
> > In this case, I want no insert because Jack Doe already exists. Can
> > anyone help me ?
>
> Simply create a UNIQUE index on the fields which you with to be unique.
> Add into yoyr table cration the line
> UNIQUE (firstname, lastname),
>
> MySQL will then reject any attempt to make that combination non-unique.
>
> Alec
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Your model is flawed.
My son and I have the same first and last names.
Therefore, we could not be users on your system.
Adaikalavan Ramasamy wrote:
I am creating a small database that keeps track of users and assigns
them a unique user ID.
The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
+--+---+--+
| myID | firstname | lastname |
+--+---+--+
|1 | John | Doe |
|2 | Jack | Doe |
|3 | John | Smith|
+--+---+--+
I get syntax error with the following :
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
Just do a normal insert, and check to see if it failed.
Then deal with it.
You could also use INSERT IGNORE.
In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?
Thank you.
Regards, Adai.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Adaikalavan Ramasamy <[EMAIL PROTECTED]> wrote on 26/07/2004
16:05:23:
> I am creating a small database that keeps track of users and assigns
> them a unique user ID.
>
> The problem is that sometimes the users might request to be added more
> than once (i.e. click on the submit button multiple times). Therefore I
> only want to add users if their details (here defined by both firstname,
> lastname) are not in the database. Example :
>
> CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
> VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
> INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
> INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
> SELECT * from tb;
> +--+---+--+
> | myID | firstname | lastname |
> +--+---+--+
> |1 | John | Doe |
> |2 | Jack | Doe |
> |3 | John | Smith|
> +--+-------+--+
>
> I get syntax error with the following :
>
> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
> EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
>
> In this case, I want no insert because Jack Doe already exists. Can
> anyone help me ?
Simply create a UNIQUE index on the fields which you with to be unique.
Add into yoyr table cration the line
UNIQUE (firstname, lastname),
MySQL will then reject any attempt to make that combination non-unique.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT if record NOT EXISTS
I am creating a small database that keeps track of users and assigns
them a unique user ID.
The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
+--+---+--+
| myID | firstname | lastname |
+--+---+--+
|1 | John | Doe |
|2 | Jack | Doe |
|3 | John | Smith|
+--+---+--+
I get syntax error with the following :
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?
Thank you.
Regards, Adai.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SET PASSWORD = PASSWORD('newpassword') does not work if user has host extension
Right on!
Effectively, user() returns the dns resolved name. So obviously this will
not match the mysql user table entry, where the ip range is specified.
I will try the --skip-name-resolve option, but I have to wait for a
scheduled down time of the server.
Thanks for the expert advice!
At 14:29 13/07/2004, you wrote:
When that user does a select user() are they receiving the numeric ip
address or the dns resolved name? If you set up the privilege with the
numeric ip and dns is resolving this the two values will not match. If
necessary you can use --skip-name-resolve.
-Original Message-
From: Stephane Carre
To: [EMAIL PROTECTED]
Sent: 7/13/04 5:01 AM
Subject: SET PASSWORD = PASSWORD('newpassword') does not work if user has
host extension
I am using MySQL 4.0.18 on Windows.
I want to give my users the possibility to change their MySQL password
after
they log in.
The command SET PASSWORD = PASSWORD('newpassword') works fine as long as
the
user has been created with no specific host extension in the mysql
database
(corresponding to e.g. [EMAIL PROTECTED]). Now if the user is defined in the
mysql
database with a host extension ([EMAIL PROTECTED], for example), the
SET
PASSWORD command fails with the message "Can't find any matching row in
the
user table".
For security reasons, I need the users to be defined with a host
extension.
Is this a bug, or is there a way around this?
(Please do not suggest to use SET PASSWORD FOR [EMAIL PROTECTED] =
PASSWORD
('newpassword') as this is only allowed for users that have access to
the mysql
database!)
--
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: SET PASSWORD = PASSWORD('newpassword') does not work if user has host extension
When that user does a select user() are they receiving the numeric ip
address or the dns resolved name? If you set up the privilege with the
numeric ip and dns is resolving this the two values will not match. If
necessary you can use --skip-name-resolve.
-Original Message-
From: Stephane Carre
To: [EMAIL PROTECTED]
Sent: 7/13/04 5:01 AM
Subject: SET PASSWORD = PASSWORD('newpassword') does not work if user has
host extension
I am using MySQL 4.0.18 on Windows.
I want to give my users the possibility to change their MySQL password
after
they log in.
The command SET PASSWORD = PASSWORD('newpassword') works fine as long as
the
user has been created with no specific host extension in the mysql
database
(corresponding to e.g. [EMAIL PROTECTED]). Now if the user is defined in the
mysql
database with a host extension ([EMAIL PROTECTED], for example), the
SET
PASSWORD command fails with the message "Can't find any matching row in
the
user table".
For security reasons, I need the users to be defined with a host
extension.
Is this a bug, or is there a way around this?
(Please do not suggest to use SET PASSWORD FOR [EMAIL PROTECTED] =
PASSWORD
('newpassword') as this is only allowed for users that have access to
the mysql
database!)
--
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]
SET PASSWORD = PASSWORD('newpassword') does not work if user has host extension
I am using MySQL 4.0.18 on Windows.
I want to give my users the possibility to change their MySQL password after
they log in.
The command SET PASSWORD = PASSWORD('newpassword') works fine as long as the
user has been created with no specific host extension in the mysql database
(corresponding to e.g. [EMAIL PROTECTED]). Now if the user is defined in the mysql
database with a host extension ([EMAIL PROTECTED], for example), the SET
PASSWORD command fails with the message "Can't find any matching row in the
user table".
For security reasons, I need the users to be defined with a host extension.
Is this a bug, or is there a way around this?
(Please do not suggest to use SET PASSWORD FOR [EMAIL PROTECTED] = PASSWORD
('newpassword') as this is only allowed for users that have access to the mysql
database!)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables & optimization of IF()
Hi everybody. I generally try to parametrize out my queries as much as possible with user-variables. So, say you have a general query for all users: --- SELECT ... FROM users --- It's nice to do this: SET @USER_ID:= NULL; SELECT ... FROM users WHERE IF(@USER_ID IS NOT NULL,[EMAIL PROTECTED],1) --- This *works*, but when you set @USER_ID to a number, the 4.1 optimizer doesn't optimize it very well: it does a table-scan, instead of using the index on user_id. Has anyone got a good alternative that will do less table-scans? Or, if a developer is listening, can you give us details on how the optimizer deals with IF()? Thanks. Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables & optimization of IF()
Hi everybody. I often try to parametrize out my queries as much as possible with user-variables. So, say you have a general query for all users: --- SELECT ... FROM users --- It's nice to do this: SET @USER_ID:= NULL; SELECT ... FROM users WHERE IF(@USER_ID IS NOT NULL,[EMAIL PROTECTED],1) --- This *works*, but when you set @USER_ID to a variable, the 4.1 optimizer doesn't optimize it very well: it does a table-scan, instead of using the index on user_id. Has anyone got a good alternative that will do less table-scans? Or, if a developer is listening, can you give us details on how the optimizer deals with IF()? Thanks. Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert data if not duplicate based on order
I don't think its possible in one query. One thing you can do is lock
the table when you select the 20 rows and determine whether to do the
insert. Then unlock when done. This avoids the concurrency issue you
are having, but it may cause unacceptable perfomance if you have a lot
of queries hitting this table. Maybe someone else has a better
solution.
John A. McCaskey
-Original Message-
From: Grant Giddens [mailto:[EMAIL PROTECTED]
Sent: Monday, June 21, 2004 4:59 PM
To: [EMAIL PROTECTED]
Subject: Insert data if not duplicate based on order
Hi,
I have a table that has many rows. I want to be
able to insert a new row only if has a unique field of
the last 20 rows when I sort them by date.
Currently before I insert a new row, I will select a
specific field for 20 rows and run a for loop in php
looking for a match with the data I'm getting ready to
insert. If a match occurs I will skip the insert
command.
This normally works fine, but I have seen occasions
when 2 people visit my website at the same exact time.
In this case, sometimes I might insert the data twice
because I think I'm spinning in the for loop for 2
site visitors simultaneously.
I know this is bad syntax, but this is what I imagine
a insert query to look like:
INSERT INTO $table (datax, datay, dataz)
VALUES ('x', 'y', 'z') IF 'x' unique WHEN
ordering by 'z' DESC LIMIT 20
In this example, 'z' is a datetime field and 'x' is a
varchar. I don't mind duplicate entries in my table,
but I don't want duplicates entries to occur within 20
rows when sorting by date.
Is this possible to do with one INSERT command? I saw
the IGNORE statment in the mysql manual, but I don't
think it really aplies to what I'm trying to do here.
Any suggestions would be appreciated,
Thanks,
Grant
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert data if not duplicate based on order
Hi,
I have a table that has many rows. I want to be
able to insert a new row only if has a unique field of
the last 20 rows when I sort them by date.
Currently before I insert a new row, I will select a
specific field for 20 rows and run a for loop in php
looking for a match with the data I'm getting ready to
insert. If a match occurs I will skip the insert
command.
This normally works fine, but I have seen occasions
when 2 people visit my website at the same exact time.
In this case, sometimes I might insert the data twice
because I think I'm spinning in the for loop for 2
site visitors simultaneously.
I know this is bad syntax, but this is what I imagine
a insert query to look like:
INSERT INTO $table (datax, datay, dataz)
VALUES ('x', 'y', 'z') IF 'x' unique WHEN
ordering by 'z' DESC LIMIT 20
In this example, 'z' is a datetime field and 'x' is a
varchar. I don't mind duplicate entries in my table,
but I don't want duplicates entries to occur within 20
rows when sorting by date.
Is this possible to do with one INSERT command? I saw
the IGNORE statment in the mysql manual, but I don't
think it really aplies to what I'm trying to do here.
Any suggestions would be appreciated,
Thanks,
Grant
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using IF
You can do this as a UNION statement or the long way. This is a UNION example: select rac.name as race, ch.* from characters as ch inner join races as rac on rac.raceID = ch.raceID inner join entityLocation el on el.entityID = ch.characterID and el.visibility <=60 and el.entityType =1 AND el.containerType =6 AND el.containerID =75402 where ch.handle<>'Khan' UNION select rac.name as race, npc.* from npc as npc inner join races as rac on rac.raceID = npc.raceID inner join entityLocation el on el.entityID = npc.characterID and el.visibility <=60 and el.entityType=10 AND el.containerType =6 AND el.containerID =75402 The long way is to do an individual IF( , , ) for each and every column you need to return and not use the * notation. One other problem with your IF statement was that you aliased *ALL* of those columns being returned as 'charData'. That's not good under any circumstance and would have failed you even if the * format were acceptable. Your, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Keith" <[EMAIL PROTECTED]To: <[EMAIL PROTECTED]> om> cc: Fax to: 06/11/2004 01:56 Subject: Using IF AM g'day, Am having a bit of a problem with using IF. This is the error message I get: - SELECT rac.name AS race, IF ( el.entityType =1, ch. * , npc. * )charData FROM characters AS ch, entityLocation AS el, races AS rac, npc AS npc WHERE el.entityType IN ( 1, 10 ) AND el.containerType =6 AND el.containerID =75402 AND IF ( el.entityType =1, el.entityID = ch.characterID AND el.visibility <=60 AND ch.raceID = rac.raceID AND ch.handle <> 'Khan', el.entityID = npc.npcID AND el.visibility <=60 AND npc.raceID = rac.raceID ) LIMIT 0 , 30 MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* , npc. * )charData FROM characters AS ch, entityLocation AS - What I want to do is select everything from characters if the el.entityType is 1, else I want to select everything from npc table. Cheers, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using IF
Hi Keith, I think your problem comes down to the fact that IF() is a function that must return one value, when you put in your '*' you might be specifying more then one value to return. /Johan Keith wrote: g'day, Am having a bit of a problem with using IF. This is the error message I get: - SELECT rac.name AS race, IF ( el.entityType =1, ch. * , npc. * )charData FROM characters AS ch, entityLocation AS el, races AS rac, npc AS npc WHERE el.entityType IN ( 1, 10 ) AND el.containerType =6 AND el.containerID =75402 AND IF ( el.entityType =1, el.entityID = ch.characterID AND el.visibility <=60 AND ch.raceID = rac.raceID AND ch.handle <> 'Khan', el.entityID = npc.npcID AND el.visibility <=60 AND npc.raceID = rac.raceID ) LIMIT 0 , 30 MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* , npc. * )charData FROM characters AS ch, entityLocation AS - What I want to do is select everything from characters if the el.entityType is 1, else I want to select everything from npc table. Cheers, Keith -- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using IF
g'day, Am having a bit of a problem with using IF. This is the error message I get: - SELECT rac.name AS race, IF ( el.entityType =1, ch. * , npc. * )charData FROM characters AS ch, entityLocation AS el, races AS rac, npc AS npc WHERE el.entityType IN ( 1, 10 ) AND el.containerType =6 AND el.containerID =75402 AND IF ( el.entityType =1, el.entityID = ch.characterID AND el.visibility <=60 AND ch.raceID = rac.raceID AND ch.handle <> 'Khan', el.entityID = npc.npcID AND el.visibility <=60 AND npc.raceID = rac.raceID ) LIMIT 0 , 30 MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* , npc. * )charData FROM characters AS ch, entityLocation AS - What I want to do is select everything from characters if the el.entityType is 1, else I want to select everything from npc table. Cheers, Keith
CRITICAL bug in 4.1.2 if one specifies innodb_file_per_table in Unix!
Hi! MySQL/InnoDB-4.1.2 is totally broken and useless if one has specified innodb_file_per_table in my.cnf on Unix! In crash recovery, InnoDB scans the database directories under the datadir, and opens files in in the read-only mode. But the exclusive file lock that InnoDB uses on the files fails in this case! The result is that the crash recovery is NOT applied to those tables, and the tables become corrupt and inaccessible! My apologies for users, the fact that this bug got into 4.1 was due to improper testing. Heikki Innobase Oy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using IF clause
ColdFusion Lists wrote: Hi my table have an field when users can enter any chars If users dont send any content for that field(phone), mysql store "( ) - " without quotes in that What im looking for: Display IF phone field is ( ) - NOT ENTER Otherwise display the phone of user. It's possible? Using IF clause Mysql give me an error - maybe IF(`users`.`phone` IS '( ) - ','NOT ENTER',`users`.`phone`) AS phonefield; What's happened? Thanx for your time In general, you should post your query and the error message you received. That would help us help you. I expect the problem is your use of "IS" as a comparison operator in place of "=". That won't work. IS only works as part of IS NULL or IS NOT NULL. I expect you want something like SELECT IF(users.phone = '( ) - ','NOT ENTER',users.phone) phonefield FROM users; It might be better if you modified your application to store NULL instead of "( ) - " when no phone number is entered, or to require a phone number be entered, whichever is appropriate. In the former case (you store NULLs), you would change the above query to: SELECT IF(users.phone IS NULL,'NOT ENTER',users.phone) phonefield FROM users; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using IF clause
Hi my table have an field when users can enter any chars If users dont send any content for that field(phone), mysql store "( ) - " without quotes in that What im looking for: Display IF phone field is ( ) - NOT ENTER Otherwise display the phone of user. It's possible? Using IF clause Mysql give me an error - maybe IF(`users`.`phone` IS '( ) - ','NOT ENTER',`users`.`phone`) AS phonefield; What's happened? Thanx for your time Diga-me e eu esquecerei Mostre-me e lembrarei Ensina-me e aprenderei - Yahoo! Mail - Participe da pesquisa global sobre o Yahoo! Mail. Clique aqui!
Re: query to see if db exists...........mysql_query( myQuery)
>On Thu, May 27, 2004 at 03:59:46PM -0400, Bono, Saroj AA R62 wrote: >> I am going to use mysql_query() and want to find out if a certain >> database exists. If mysql_real_connect() fails there are many errors >> that could account for this. The database may exist , and I cant take >> the error returned from the failed query to mean the db isnt there and >> should be created. So what sort of query can I use to see whether the db >> exists? I thought of "USE myDb" but once again a failed mysql_query cant >> rule out other factors that can cause the failure. Any suggestions? Are you using PHP? You can use the mysql_list_dbs() function: http://www.php.net/manual/en/function.mysql-list-dbs.php - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query to see if db exists...........mysql_query( myQuery)
On Thu, May 27, 2004 at 03:59:46PM -0400, Bono, Saroj AA R62 wrote: > I am going to use mysql_query() and want to find out if a certain > database exists. If mysql_real_connect() fails there are many errors > that could account for this. The database may exist , and I cant take > the error returned from the failed query to mean the db isnt there and > should be created. So what sort of query can I use to see whether the db > exists? I thought of "USE myDb" but once again a failed mysql_query cant > rule out other factors that can cause the failure. Any suggestions? You could use "SHOW DATABASES LIKE 'myDb'". http://dev.mysql.com/doc/mysql/en/Show_database_info.html Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query to see if db exists...........mysql_query( myQuery)
I am going to use mysql_query() and want to find out if a certain database exists. If mysql_real_connect() fails there are many errors that could account for this. The database may exist , and I cant take the error returned from the failed query to mean the db isnt there and should be created. So what sort of query can I use to see whether the db exists? I thought of "USE myDb" but once again a failed mysql_query cant rule out other factors that can cause the failure. Any suggestions? Many thanks, sb
Re: Looking for a C API if it exisits
On Tue, May 25, 2004 at 03:36:08PM -0600, Sarix wrote: > Cause when I do my web end to all this life > is easy cause I can do $name = $row['Name'] and it works for PHP so I > figured there has to be an API that would do that.. But I can't seem to find > it in the documention. Is there something that will help with this, or is C > just too old to have helpful ablitles like this. :) People have written libraries for C to emulate higher-level languages. Here is an example, but I've not used it myself: http://www.annexia.org/freeware/c2lib/doc/ It speaks of 'vectors', and you could easily wrap your DB access routines in this, to give you something very like the named hashes that Perl and PHP use. -- Brian Reichert <[EMAIL PROTECTED]> 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for a C API if it exisits
Hi all, I've been working with getting my on going C project merged with MySQL. So I can have an interactive Web Site incuded with it. I was wondering, when working with the rows... I'm having to call the values like: name = str_dup(row[0]); // returns me an memory allocated pointer Is there a way that I could call the field by name rather then by it's place value? Or is there a function that I could give it a colums name and it would give me it's int value... Cause when I do my web end to all this life is easy cause I can do $name = $row['Name'] and it works for PHP so I figured there has to be an API that would do that.. But I can't seem to find it in the documention. Is there something that will help with this, or is C just too old to have helpful ablitles like this. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if exists
I think this is half the answer (though you've implied the other half). First, you prevent mysql from accepting duplicate rows by defining a PRIMARY KEY or UNIQUE INDEX on whatever column or columns determine uniqueness. See <http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html> for the details. Then, you can INSERT without fear of creating duplicate rows. Adding IGNORE tells mysql not to bother you with an error message if your INSERT is a duplicate. If it is possible that your current info supersedes what is already in the table, you might want to take a look at the manual page for REPLACE, <http://dev.mysql.com/doc/mysql/en/REPLACE.html>, or, if you use mysql 4.1.x, INSERT...ON DUPLICATE KEY UPDATE... <http://dev.mysql.com/doc/mysql/en/INSERT.html>. Michael [EMAIL PROTECTED] wrote: Silently ignore duplicate index inserts... insert IGNORE into PERSON ( NAME ) values ( 'Test') Ed -Original Message- From: Gerhard Gaupp [mailto:[EMAIL PROTECTED] Hello I'm looking for something like if not exists( select 1 from PERSON where NAME = 'Test' ) then insert into PERSON ( NAME ) values ( 'Test'); end if; I did it with Sybase Database. I need to get Data from Excel and have to be shure that there is no data more than once in the table. So I have to generate a script with test, if this data is in teh table yet, or not, before inserting. Did You Understand? Sorry for my English Greetings from Germany Gerhard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if exists
At 12:12 +0200 5/5/04, Gerhard Gaupp wrote: Hello I'm looking for something like if not exists( select 1 from PERSON where NAME = 'Test' ) then insert into PERSON ( NAME ) values ( 'Test'); end if; I did it with Sybase Database. I need to get Data from Excel and have to be shure that there is no data more than once in the table. So I have to generate a script with test, if this data is in teh table yet, or not, before inserting. Maybe you could just use INSERT IGNORE ... SELECT FROM ... ? That inserts records from one table into another table, ignoring records that duplicate any existing unique key values. Did You Understand? Sorry for my English Greetings from Germany Gerhard -- 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 exists
Silently ignore duplicate index inserts... insert IGNORE into PERSON ( NAME ) values ( 'Test') Ed -Original Message- From: Gerhard Gaupp [mailto:[EMAIL PROTECTED] Hello I'm looking for something like if not exists( select 1 from PERSON where NAME = 'Test' ) then insert into PERSON ( NAME ) values ( 'Test'); end if; I did it with Sybase Database. I need to get Data from Excel and have to be shure that there is no data more than once in the table. So I have to generate a script with test, if this data is in teh table yet, or not, before inserting. Did You Understand? Sorry for my English Greetings from Germany Gerhard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if exists
Hello I'm looking for something like if not exists( select 1 from PERSON where NAME = 'Test' ) then insert into PERSON ( NAME ) values ( 'Test'); end if; I did it with Sybase Database. I need to get Data from Excel and have to be shure that there is no data more than once in the table. So I have to generate a script with test, if this data is in teh table yet, or not, before inserting. Did You Understand? Sorry for my English Greetings from Germany Gerhard
RE: update if insert fails
You can use replace into, but note it cannot do Replace into table VALUES (1,2,3,col4+1); > -Original Message- > From: Andy Ford [mailto:[EMAIL PROTECTED] > Sent: Friday, April 23, 2004 3:59 AM > To: [EMAIL PROTECTED] > Subject: update if insert fails > > Hi > > Is there such a statement where, if the insert fails (due to a duplicate > record) an update will happen > > Thanks > > Andy > > > -- > 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: update if insert fails
Actually if you are using 4.1.x
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
One of the best new features, because insert is faster than an update,
update is faster than a delete, and replace is the slowest command you can
run. These are based on my benchmarks about 6 months ago.
This is definitely one of my favorite commands now.
Donny
> -Original Message-
> From: B. Fongo [mailto:[EMAIL PROTECTED]
> Sent: Friday, April 23, 2004 7:23 AM
> To: 'Andy Ford'; [EMAIL PROTECTED] Mysql. Com
> Subject: RE: update if insert fails
>
> Use REPLACE instead. It is similar to the INSERT except that, it will
> replace any old record that match - with the new one. A replace
> statement may look like this:
>
> REPLACE INTO TestTable (TestId, User) VALUES ("007","Bond");
>
> HTH
>
> Babs
>
>
>
> ||> -Original Message-
> ||> From: Andy Ford [mailto:[EMAIL PROTECTED]
> ||> Sent: Friday, April 23, 2004 1:59 PM
> ||> To: [EMAIL PROTECTED]
> ||> Subject: update if insert fails
> ||>
> ||> Hi
> ||>
> ||> Is there such a statement where, if the insert fails (due to a
> duplicate
> ||> record) an update will happen
> ||>
> ||> Thanks
> ||>
> ||> Andy
> ||>
> ||>
> ||> --
> ||> MySQL General Mailing List
> ||> For list archives: http://lists.mysql.com/mysql
> ||> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: update if insert fails
Use REPLACE instead. It is similar to the INSERT except that, it will
replace any old record that match - with the new one. A replace
statement may look like this:
REPLACE INTO TestTable (TestId, User) VALUES ("007","Bond");
HTH
Babs
||> -Original Message-
||> From: Andy Ford [mailto:[EMAIL PROTECTED]
||> Sent: Friday, April 23, 2004 1:59 PM
||> To: [EMAIL PROTECTED]
||> Subject: update if insert fails
||>
||> Hi
||>
||> Is there such a statement where, if the insert fails (due to a
duplicate
||> record) an update will happen
||>
||> Thanks
||>
||> Andy
||>
||>
||> --
||> 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: update if insert fails
Andy Ford <[EMAIL PROTECTED]> wrote: > > Is there such a statement where, if the insert fails (due to a duplicate > record) an update will happen > Take a look at REPLACE and INSERT .. ON DUPLICATE KEY UPDATE statements: http://dev.mysql.com/doc/mysql/en/REPLACE.html http://dev.mysql.com/doc/mysql/en/INSERT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update if insert fails
Hello Andy, Friday, April 23, 2004, 12:59:28 PM, you wrote: AF> Is there such a statement where, if the insert fails (due to a duplicate AF> record) an update will happen No, but you might want to look at REPLACE INTO to simulate this effect. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update if insert fails
Hi Is there such a statement where, if the insert fails (due to a duplicate record) an update will happen Thanks Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: If() syntax question
From: Don Dachner [mailto:[EMAIL PROTECTED] > Is it possible to do something like this? > > If(select * from xxx, "if record found"..update it, "if > record not found" ..insert it) Try the REPLACE INTO syntax: http://dev.mysql.com/doc/mysql/en/REPLACE.html -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If() syntax question
Is it possible to do something like this? If(select * from xxx, "if record found"..update it, "if record not found" ..insert it) Thanks, Don
ADODB.Connection - How do I open a DB, create if not there, open table, create if not there and add a single record?
This should be simple, no? I can't make it work
Objective:
- bring up a panel to ask for MySQL User/Host/Password and save settings if
we get logged in.
- when we start (likely on a new machine) create a new Weather DB if it
doesn't exist
- when we start ("" """) create a new TimeTable table if it doesn't exist
- loop through our one per hour loop and add new records as we go
I know this code is likely trash but it is my first attempt and I can find
no example how to do what I think should be simple. THANKS!!
Main.bas: (contains global vars)
Global gCnn As ADODB.Connection
Global pRs As ADODB.Recordset
Global SQL As String, UserName As String, UserPassword As String,
UserHost As String
Connect.frm: (loaded on startup - verifies connection)
Private Sub ClickConnect:
Set gCnn = New ADODB.Connection
Set pRs = New ADODB.Recordset
gCnn.ConnectionTimeout = 60
gCnn.CommandTimeout = 400
gCnn.CursorLocation = adUseClient
gCnn.Open "DRIVER={MySQL ODBC 3.51 Driver};" _
& "user=" & UserName _
& ";password=" & UserPassword _
& ";server=" & UserHost _
& ";option=20499"
If gCnn.State = 1 Then
SaveSetting App.Title, "Settings", "UserHost", UserHost
SaveSetting App.Title, "Settings", "UserName", UserName
SaveSetting App.Title, "Settings", "UserPassword", UserPassword
frmWeather.Show vbModal
Unload Me 'we logged on OK, so we will save this info for next
logon
Else
MsgBox "Unable to establish the connection. Check your settings and
try again.", vbCritical, "Error While Connecting"
End
End If
frmWeather (Main Form):
FormLoad:
gCnn.Open "Weather"
If gCnn.State <> 1 Then 'create database if it does not exist
gCnn.Execute "Create Database Weather", , adExecuteNoRecords
gCnn.Open "Weather"
If gCnn.State <> 1 Then
MsgBox "Failed to access Weather DB"
End
End If
End If
On Error GoTo loadstp
SQL = "CREATE TABLE IF NOT EXISTS `TimeTable` (" _
& "`Time` varchar(30) NOT NULL default '', " _
& "`Temperature` varchar(20) NOT NULL default '', " _
& "PRIMARY KEY (`Time`) " _
& ") TYPE=MyISAM"
gCnn.Execute SQL, , adExecuteNoRecords 'create table if not exist
SQL = "SELECT * FROM TimeTable"
pRs.Open SQL, gCnn, adOpenDynamic, adLockOptimistic, adCmdText
[loop logic to loop every hour]
With pRs
.AddNew
.Fields("Time") = Time()
.Fields("Temperature") = fromSendor
.Update
End With
[end loop logic]
Exit Sub
loadstp:
MsgBox Err.Description
End Sub
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested IF statement in 3.23.57
At 18:44 -0500 3/25/04, David Perron wrote: Are nested IF statements valid? Doesn't say anything here: http://www.mysql.com/doc/en/Control_flow_functions.html Yes, you can nest them, but you must do so correctly. :-) Your inner IF() below appears to have four arguments. Trying to get a conditional count using the following statement - but Im receiving an error. I would use a CASE statement but my version doesn't support it. SUM(IF(AdGroups.Status = 'Paused',IF(AdGroupCriteria.Status,Active,1,0),0)) +-+ | version() | +-+ | 3.23.57-Max | +-+ -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nested IF statement in 3.23.57
Are nested IF statements valid? Doesn't say anything here: http://www.mysql.com/doc/en/Control_flow_functions.html Trying to get a conditional count using the following statement - but Im receiving an error. I would use a CASE statement but my version doesn't support it. SUM(IF(AdGroups.Status = 'Paused',IF(AdGroupCriteria.Status,Active,1,0),0)) +-+ | version() | +-+ | 3.23.57-Max | +-+
Re: If DataBase Exists
This works on 4.1.1, not sure if it works on 4.0.14: show databases like '%backups%' - Original Message - From: "A Z" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, March 24, 2004 12:33 AM Subject: If DataBase Exists MySQL 4.0.14 Possible to query for existence of a DB. For example connect to MySQL and run run a command to check if Database (Test2) existed. regards ___ Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- 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: If DataBase Exists
A Z <[EMAIL PROTECTED]> wrote: > > > MySQL 4.0.14 > > Possible to query for existence of a DB. For example > connect to MySQL and run run a command to check if > Database (Test2) existed. > Use SHOW DATABASES command: SHOW DATABASES LIKE 'Test2'; http://www.mysql.com/doc/en/Show_database_info.html But you can see only those databases for which you have some privileges. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If DataBase Exists
MySQL 4.0.14 Possible to query for existence of a DB. For example connect to MySQL and run run a command to check if Database (Test2) existed. regards ___ Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count all rows if limit by?
Thanks a lot, Jigal and Egor - just what i searched for! Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Group by months, return all months even if zero
Hi, Is there a short way to return the results from a query grouped by months and return zero if no results found: eg: SELECT count(*) as total, monthname(col) FROM table GROUP BY monthname(col) To return: 5 - Jan 0 - Feb 3 - Mar 0 - Apr etc. Just wondering. I know the long way to do it, by quering each month one by one... Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count all rows if limit by?
Victor Sp?ng Arthursson <[EMAIL PROTECTED]> wrote: > Hi all! > > Is there a simple way to get the total number of rows a result _should_ > have had if no where-clause where present? Without doing a second > query? > If you want to get total number of rows in the table (without WHERE and without LIMIT) you should execute SELECT COUNT(*) FROM table_name; If you want to obtain number of rows the statement would have returned without the LIMIT clause, you can specify SQL_CALC_FOUND_ROWS keyword in the SELECT statement and then use FOUND_ROWS() function: http://www.mysql.com/doc/en/Information_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count all rows if limit by?
From: "Victor Spång Arthursson" <[EMAIL PROTECTED]> > Is there a simple way to get the total number of rows a result _should_ > have had if no where-clause where present? Without doing a second > query? Example from http://www.mysql.com/doc/en/Information_functions.html : mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS(); Available since 4.0.0.; in versions prior to 4.1.0. this didn't work with LIMIT 0. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count all rows if limit by?
Hi all! Is there a simple way to get the total number of rows a result _should_ have had if no where-clause where present? Without doing a second query? Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with IF acting wierd.
Mike Johnson wrote: From: Mike Johnson SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,"cat","item") AS type FROM main, IF(main.type,items,categories) WHERE IF(main.type,categories.id,items.id)=main.id; Oh, my mistake. I just realized I reversed items and categories in the IF clause. Try this instead: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,"cat","item") AS type FROM main, IF(main.type,categories,items) WHERE IF(main.type,categories.id,items.id)=main.id; Sorry! Actually, now I'm really curious if this works or not. Let me know how it turns out. Unfortunately, that won't work. IF can return a value, not a column reference. As I understand it, you want to join main to categories for rows where main.type is 1, but you want to join main to items for rows where main.type is 0. As Mike explained, these are two separate 2-table joins, not one 3-table join. You didn't say which version of mysql you have. If you have at least 4.0.0, you can combine the results of 2 selects with UNION, so this should work: SELECT main.id, categories.name, 'cat ' AS type FROM main, categories WHERE main.id = categories.id AND main.type UNION SELECT main.id, items.name, 'item' AS type FROM main, items WHERE main.id = items.id AND NOT main.type; If you are still using 3.23.x, you can accomplish the same thing with a temporary table. CREATE TEMPORARY TABLE joins SELECT main.id, categories.name, 'cat ' AS type FROM main, categories WHERE main.id = categories.id AND main.type; INSERT INTO joins SELECT main.id, items.name, 'item' AS type FROM main, items WHERE main.id = items.id AND NOT main.type; SELECT * FROM joins; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query with IF acting wierd.
From: Mike Johnson > SELECT main.id, > IF(main.type,categories.name,items.name), > IF(main.type,"cat","item") AS type > FROM main, IF(main.type,items,categories) WHERE > IF(main.type,categories.id,items.id)=main.id; Oh, my mistake. I just realized I reversed items and categories in the IF clause. Try this instead: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,"cat","item") AS type FROM main, IF(main.type,categories,items) WHERE IF(main.type,categories.id,items.id)=main.id; Sorry! Actually, now I'm really curious if this works or not. Let me know how it turns out. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query with IF acting wierd.
From: Amir Hardon [mailto:[EMAIL PROTECTED] > I have 3 tables: > > main(id int, type tinyint(1)) > categories(id int, name varchar) > items(id int, name varchar) > > I want to select the id and name. > If type is 1 then I want to select the name from categories, > if type is 0 I want to select the name from items, here is > the query I'm trying to use: > > SELECT main.id, IF(main.type,categories.name,items.name), > IF(main.type,"cat","item") AS type FROM main,items,categories WHERE > IF(main.type,categories.id,items.id)=main.id; > > This query gives me each row couple of times, can anyone tell > me why? or can any one give me a better solution? The problem is that in either case of the first IF, you're still joining on 3 tables when you really want two. If main.type is a category and the WHERE clause ends up evaluating to `WHERE categories.id=main.id' you're still joining on the items table unnecessarily, and vice-versa. Without another WHERE clause to limit it, you're getting the full cartesian product from the first two tables against the third. I don't think this works, but it's essentially what you want: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,"cat","item") AS type FROM main, IF(main.type,items,categories) WHERE IF(main.type,categories.id,items.id)=main.id; Note the extra IF in there to join only on /either/ items or categories. I never knew you could use an IF clause to specify a column in a WHERE clause, though, so maybe this will work. It's worh a shot at least. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with IF acting wierd.
Amir, I don't know your table content but if you join two tables eg : table1(id, desc) and table2(id, refid, desc) wich containts: table1: 1 test1 2 test2 table2: 1 1 testbla 2 1 testbla and you select: select id from table1 where table1.id = table2.refid you got two rows (because if you show * it would be: 1 test1 1 1 testbla and 1 test1 2 1 testbla So distinct is the keyword for getting only the unique records. Distinct is in MYSQL very effective (see the user manual for : How MySQL Optimizes `DISTINCT'). regards At 15:02 11-3-04, you wrote: Thanks, but this is just a nicer way to apply my second solution, I'm looking for a more efficient solution (and if someone can than for an explanation - why are the results getting duplicated?) -Amir. On Thursday 11 March 2004 15:40, Hans van Dalen wrote: > SELECT distinct main.id, etc. etc. > > At 14:37 11-3-04, you wrote: > >I have 3 tables: > > > >main(id int, type tinyint(1)) > >categories(id int, name varchar) > >items(id int, name varchar) > > > >I want to select the id and name. > >If type is 1 then I want to select the name from categories, > >if type is 0 I want to select the name from items, here is the query I'm > >trying to use: > > > >SELECT main.id, IF(main.type,categories.name,items.name), > >IF(main.type,"cat","item") AS type FROM main,items,categories WHERE > >IF(main.type,categories.id,items.id)=main.id; > > > >This query gives me each row couple of times, can anyone tell me why? or > > can any one give me a better solution? > > > >My solution which I guess is not good is adding GROUP BY: > > > >SELECT main.id, IF(main.type,categories.name,items.name), > >IF(main.type,"cat","item") AS type FROM main,items,categories WHERE > >IF(main.type,categories.id,items.id)=main.id GROUP BY id, type; > > > >Thanks, > > -Amir. > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with IF acting wierd.
Thanks, but this is just a nicer way to apply my second solution, I'm looking for a more efficient solution (and if someone can than for an explanation - why are the results getting duplicated?) -Amir. On Thursday 11 March 2004 15:40, Hans van Dalen wrote: > SELECT distinct main.id, etc. etc. > > At 14:37 11-3-04, you wrote: > >I have 3 tables: > > > >main(id int, type tinyint(1)) > >categories(id int, name varchar) > >items(id int, name varchar) > > > >I want to select the id and name. > >If type is 1 then I want to select the name from categories, > >if type is 0 I want to select the name from items, here is the query I'm > >trying to use: > > > >SELECT main.id, IF(main.type,categories.name,items.name), > >IF(main.type,"cat","item") AS type FROM main,items,categories WHERE > >IF(main.type,categories.id,items.id)=main.id; > > > >This query gives me each row couple of times, can anyone tell me why? or > > can any one give me a better solution? > > > >My solution which I guess is not good is adding GROUP BY: > > > >SELECT main.id, IF(main.type,categories.name,items.name), > >IF(main.type,"cat","item") AS type FROM main,items,categories WHERE > >IF(main.type,categories.id,items.id)=main.id GROUP BY id, type; > > > >Thanks, > > -Amir. > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with IF acting wierd.
SELECT distinct main.id, etc. etc. At 14:37 11-3-04, you wrote: I have 3 tables: main(id int, type tinyint(1)) categories(id int, name varchar) items(id int, name varchar) I want to select the id and name. If type is 1 then I want to select the name from categories, if type is 0 I want to select the name from items, here is the query I'm trying to use: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,"cat","item") AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id; This query gives me each row couple of times, can anyone tell me why? or can any one give me a better solution? My solution which I guess is not good is adding GROUP BY: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,"cat","item") AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id GROUP BY id, type; Thanks, -Amir. -- 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]
Query with IF acting wierd.
I have 3 tables: main(id int, type tinyint(1)) categories(id int, name varchar) items(id int, name varchar) I want to select the id and name. If type is 1 then I want to select the name from categories, if type is 0 I want to select the name from items, here is the query I'm trying to use: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,"cat","item") AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id; This query gives me each row couple of times, can anyone tell me why? or can any one give me a better solution? My solution which I guess is not good is adding GROUP BY: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,"cat","item") AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id GROUP BY id, type; Thanks, -Amir. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table name is lower case if there is an index on it (4.0.18 )
"Bluemel, Marco" <[EMAIL PROTECTED]> wrote:
> I have a problem with mysql 4.0.18 on WindowsXP
> I set the lower_case_table_names=2 as it is mentioned for windows
> t the mysql website.
>
> (before I'm using version 4.0.15 and had set it to 0)
> so the tablenames should be stored in this case as I wrote it.
>
> So I create a table in Java with an index (this is only a part of the
> table)
>
> String sql = "";
> sql += "CREATE TABLE AB_OBJECTS";
> sql += "(";
> sql += "id INTEGER PRIMARY KEY,";
> sql += "objectId INTEGER NOT NULL,";
> sql += ")";
> _statement.execute(sql);
> _statement.execute("CREATE INDEX ab_object_objectid_index ON
> AB_OBJECTS(objectId) ");
> the problem is that the table is created in lower cases as 'ab_objects'
>
> but if I don't create the index, only the table, its created in upper
> case 'AB_OBJECTS' as it should be.
>
> I have some other tables with and without an index and all should be
> stored in upper case.
Thank you for report!
This bug is already entered to the bug database:
http://bugs.mysql.com/bug.php?id=3109
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table name is lower case if there is an index on it (4.0.18 )
Hi,
I have a problem with mysql 4.0.18 on WindowsXP
I set the lower_case_table_names=2 as it is mentioned for windows
at the mysql website.
(before I'm using version 4.0.15 and had set it to 0)
so the tablenames should be stored in this case as I wrote it.
So I create a table in Java with an index (this is only a part of the
table)
String sql = "";
sql += "CREATE TABLE AB_OBJECTS";
sql += "(";
sql += "id INTEGER PRIMARY KEY,";
sql += "objectId INTEGER NOT NULL,";
sql += ")";
_statement.execute(sql);
_statement.execute("CREATE INDEX ab_object_objectid_index ON
AB_OBJECTS(objectId) ");
the problem is that the table is created in lower cases as 'ab_objects'
but if I don't create the index, only the table, its created in upper
case 'AB_OBJECTS' as it should be.
I have some other tables with and without an index and all should be
stored in upper case.
Thanks
Regards
Marco Bluemel
Re: CREATE TABLE IF NOT EXISTS behavior (should it add rows if the table doesn't exist?)
At 14:42 -0800 3/2/04, Mark Brunkhart wrote: I apologize if this has been discussed or reported as a bug (I searched a bit but couldn't find mention of it). When you create a table using the CREATE TABLE IF NOT EXISTS...SELECT syntax, what is the correct behavior if the table already exists? It should add rows. IF NOT EXISTS applies to the CREATE, not to the SELECT. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE TABLE IF NOT EXISTS behavior (should it add rows if the table doesn't exist?)
I apologize if this has been discussed or reported as a bug (I searched a bit but couldn't find mention of it). When you create a table using the CREATE TABLE IF NOT EXISTS...SELECT syntax, what is the correct behavior if the table already exists? I had thought that no action at all would be taken. It turns out that MySQL adds the rows in the SELECT statement to the table. This seems to be a bug, but I think it could be argued that this is the correct behavior (though I'm not sure I'd agree). I've tested in both 4.0.17 and 4.1 under Windows and get the same result: mysql> create temporary table test select 1; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> create temporary table if not exists test select 2; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test; +---+ | 1 | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec) The way MySQL currently functions is equivalent to two separate statements, a CREATE TABLE and a SELECT INTO where "IF NOT EXISTS" applies only to the CREATE TABLE portion and the SELECT INTO happens regardless. If this is not a bug and not a standard compliance thing, I'd argue that this behavior is not a good choice since you can easily get this behavior simply by issuing the CREATE TABLE IF NOT EXISTS and SELECT INTO statements separately. If your want to avoid adding additional rows to the table if the table already exists (for example, if you initialize the table with some rows but can't be certain if this initialization has already occurred), then I believe it is difficult to create this behavior under the current implementation without external logic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Executing REPLACE from IF statement?
And to finish it off, I just removed pb.ticker="SAP500" statement.
-Original Message-
From: Pilarinos, Dionysios
Sent: Monday, March 01, 2004 4:56 PM
To: [EMAIL PROTECTED]
Subject: RE: Executing REPLACE from IF statement?
After some thought, this is what I came up with:
REPLACE current_profile
select c.*
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker="SAP500" and c.ID=pb.ID and bc.ticker=pb.ticker and
DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate
Now all I have to do is make this run for each pb.ticker without having to
explicitly identify it in the script.
Feel free to give me suggestions.
-Original Message-
From: Pilarinos, Dionysios
Sent: Monday, March 01, 2004 3:53 PM
To: [EMAIL PROTECTED]
Subject: Executing REPLACE from IF statement?
Hi,
I was wondering if someone can point out a way to make the below script
update a row.
Query:
SELECT pb.ID, c.mdate, bc.mdate,
IF(DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate, "Same Date", "Not Same Date")
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker="SAP500" and c.ID=pb.ID and bc.ticker=pb.ticker
Result:
97 | 2004-02-21 | 02/21/2004 | Same Date
98 | 2004-02-21 | 02/21/2004 | Same Date
What I would like to do is "REPLACE current_profile SELECT * FROM current"
where the ID('s) are those returned from the select statement above.
Thanks for your insight and assistance.
Dionysios Pilarinos
**
This message, including any attachments, contains confidential information
intended for a specific individual and purpose, and is protected by law. If
you are not the intended recipient, please contact sender immediately by
reply e-mail and destroy all copies. You are hereby notified that any
disclosure, copying, or distribution of this message, or the taking of any
action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
**
This message, including any attachments, contains confidential information
intended for a specific individual and purpose, and is protected by law. If
you are not the intended recipient, please contact sender immediately by
reply e-mail and destroy all copies. You are hereby notified that any
disclosure, copying, or distribution of this message, or the taking of any
action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
**
This message, including any attachments, contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, please contact sender immediately by reply e-mail and destroy all
copies. You are hereby notified that any disclosure, copying, or distribution of this
message, or the taking of any action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Executing REPLACE from IF statement?
After some thought, this is what I came up with:
REPLACE current_profile
select c.*
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker="SAP500" and c.ID=pb.ID and bc.ticker=pb.ticker and
DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate
Now all I have to do is make this run for each pb.ticker without having to
explicitly identify it in the script.
Feel free to give me suggestions.
-Original Message-
From: Pilarinos, Dionysios
Sent: Monday, March 01, 2004 3:53 PM
To: [EMAIL PROTECTED]
Subject: Executing REPLACE from IF statement?
Hi,
I was wondering if someone can point out a way to make the below script
update a row.
Query:
SELECT pb.ID, c.mdate, bc.mdate,
IF(DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate, "Same Date", "Not Same Date")
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker="SAP500" and c.ID=pb.ID and bc.ticker=pb.ticker
Result:
97 | 2004-02-21 | 02/21/2004 | Same Date
98 | 2004-02-21 | 02/21/2004 | Same Date
What I would like to do is "REPLACE current_profile SELECT * FROM current"
where the ID('s) are those returned from the select statement above.
Thanks for your insight and assistance.
Dionysios Pilarinos
**
This message, including any attachments, contains confidential information
intended for a specific individual and purpose, and is protected by law. If
you are not the intended recipient, please contact sender immediately by
reply e-mail and destroy all copies. You are hereby notified that any
disclosure, copying, or distribution of this message, or the taking of any
action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
**
This message, including any attachments, contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, please contact sender immediately by reply e-mail and destroy all
copies. You are hereby notified that any disclosure, copying, or distribution of this
message, or the taking of any action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Executing REPLACE from IF statement?
Hi,
I was wondering if someone can point out a way to make the below script
update a row.
Query:
SELECT pb.ID, c.mdate, bc.mdate,
IF(DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate, "Same Date", "Not Same Date")
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker="SAP500" and c.ID=pb.ID and bc.ticker=pb.ticker
Result:
97 | 2004-02-21 | 02/21/2004 | Same Date
98 | 2004-02-21 | 02/21/2004 | Same Date
What I would like to do is "REPLACE current_profile SELECT * FROM current"
where the ID('s) are those returned from the select statement above.
Thanks for your insight and assistance.
Dionysios Pilarinos
**
This message, including any attachments, contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, please contact sender immediately by reply e-mail and destroy all
copies. You are hereby notified that any disclosure, copying, or distribution of this
message, or the taking of any action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If Exists update else insert
"Juan E Suris" <[EMAIL PROTECTED]> wrote:
> I need to do something similar to replace, but can't figure out how to do it.
>
> Here's my table:
> user varchar(15) PRI
> cnt int(11)
>
> I want to increment cnt for user if it exists, else insert a row with cnt=1. I tried
> the following, but mysql complains that I can't use the same table in the update and
> select:
>
> replace into table set user='someuser', cnt=(select cnt+1 from table where
> user='someuser')
>
> Can this be done (in 1 query, of course)?
From v4.1.0 MySQL supports INSERT .. ON DUPLICATE KEY UPDATE and you can write your
query as
INSERT INTO table VALUES('someuser', 1)
ON DUPLICATE KEY UPDATE cnt=cnt+1;
http://www.mysql.com/doc/en/INSERT.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If Exists update else insert
I need to do something similar to replace, but can't figure out how to do it. Here's my table: user varchar(15) PRI cnt int(11) I want to increment cnt for user if it exists, else insert a row with cnt=1. I tried the following, but mysql complains that I can't use the same table in the update and select: replace into table set user='someuser', cnt=(select cnt+1 from table where user='someuser') Can this be done (in 1 query, of course)? Thanks, Juan E
Re: Strange behavior with IF?
Hi Diana, > > SELECT IF(ISNULL(network.level), 4, network.level) AS level, > > member.photo_level > > FROM member > > LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) > > > > ORDER BY member.last_login DESC > > LIMIT 0,3 > > > > +---+-+ > > | level | photo_level | > > +---+-+ > > | 4 | 4 | > > | 4 | 4 | > > | 4 | 4 | > > +---+-----+ > > 3 rows in set (0.01 sec) > > > > Then, when I add WHERE. > > > > SELECT IF(ISNULL(network.level), 4, network.level) AS level, > > member.photo_level > > FROM member > > LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) > > WHERE level <= member.photo_level > > ORDER BY member.last_login DESC > > LIMIT 0,3 > > > > Empty set (0.00 sec) > > > > I think it is supposed to return all rows, since all level is the same > > as photo_level, but why does it return empty set? > > > Conditions with fields from the table in the LEFT JOIN side (in this > case, "network") should be in the ON clause, not in the WHERE clause. > Check the manual about using LEFT JOIN and try: > > SELECT IF(ISNULL(network.level), 4, network.level) AS level, > member.photo_level > FROM member > LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id > AND network.level <= member.photo_level) > ORDER BY member.last_login DESC > LIMIT 0,3 Thank you for the reply. I have it fixed, the problem is I should have used "HAVING" instead of "WHERE" and I couldn't use "AS level", because HAVING will confuse it with network.level which is NULL. So here is the working query. SELECT IF(ISNULL(network.level), 4, network.level) AS level_alias, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) HAVING level_alias <= member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Regards, Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with IF?
On Mon, 2004-02-16 at 09:07, Batara Kesuma wrote: > Hi, > > Can someone tell me why this query doesn't work? > > SELECT IF(ISNULL(network.level), 4, network.level) AS level, > member.photo_level > FROM member > LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) > ORDER BY member.last_login DESC > LIMIT 0,3 > > +---+-+ > | level | photo_level | > +---+-+ > | 4 | 4 | > | 4 | 4 | > | 4 | 4 | > +---+-+ > 3 rows in set (0.01 sec) > > Then, when I add WHERE. > > SELECT IF(ISNULL(network.level), 4, network.level) AS level, > member.photo_level > FROM member > LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) > WHERE level <= member.photo_level > ORDER BY member.last_login DESC > LIMIT 0,3 > > Empty set (0.00 sec) > > I think it is supposed to return all rows, since all level is the same > as photo_level, but why does it return empty set? Conditions with fields from the table in the LEFT JOIN side (in this case, "network") should be in the ON clause, not in the WHERE clause. Check the manual about using LEFT JOIN and try: SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id AND network.level <= member.photo_level) ORDER BY member.last_login DESC LIMIT 0,3 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with IF?
Batara Kesuma <[EMAIL PROTECTED]> wrote: > > Can someone tell me why this query doesn't work? > > SELECT IF(ISNULL(network.level), 4, network.level) AS level, > member.photo_level > FROM member > LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) > ORDER BY member.last_login DESC > LIMIT 0,3 > > +---+-+ > | level | photo_level | > +---+-+ > | 4 | 4 | > | 4 | 4 | > | 4 | 4 | > +---+-+ > 3 rows in set (0.01 sec) > > Then, when I add WHERE. > > SELECT IF(ISNULL(network.level), 4, network.level) AS level, > member.photo_level > FROM member > LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) > WHERE level <= member.photo_level > ORDER BY member.last_login DESC > LIMIT 0,3 > > Empty set (0.00 sec) > > I think it is supposed to return all rows, since all level is the same > as photo_level, but why does it return empty set? You can't refer to the column alias in the WHERE clause. Use HAVING clause instead. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior with IF?
Hi, Can someone tell me why this query doesn't work? SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) ORDER BY member.last_login DESC LIMIT 0,3 +---+-+ | level | photo_level | +---+-+ | 4 | 4 | | 4 | 4 | | 4 | 4 | +---+-+ 3 rows in set (0.01 sec) Then, when I add WHERE. SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) WHERE level <= member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Empty set (0.00 sec) I think it is supposed to return all rows, since all level is the same as photo_level, but why does it return empty set? --Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If statement
I need some help with this. Here is the information from the MySql manual. IF search_condition THEN statement(s) [ELSEIF search_condition THEN statement(s)] ... [ELSE statement(s)] END IF IF implements a basic conditional construct. If the search_condition evaluates to true, the corresponding SQL statement is executed. If no search_condition matches, the statement in the ELSE clause is executed Now what I want to do is search to see if a record exists, if it does, update the record with new information, if not insert the data into the table. What confuses me above is that it says that if search condition is true, then it will execute. How do I do a search and see if it is true? If I do something like the following and it returns a result, does that mean it is true? SELECT * from table WHERE column='data'; Could someone give me a short example of what to do if this is not the case? Mike Tuller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG on 64bit & if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
On Sat, Jan 31, 2004 at 10:06:04PM -0500, Michael Stassen wrote: > Meanwhile, perhaps a simpler workaround would be to change how you build > the query in Perl so as to avoid the quotes. Well, I know that this would be easy but I have good reasons to avoid this. The most important one is lru statement-caching in PApp::SQL (a DBI "frontend" that makes sense in many ways including proper utf-8 handling). The other funny thing I noted: errno was set on udf-entry and my function returned NULL because I checked errno after stroull... It may be a feature for others but I call this: bug. .) mysql> select udf_errno(); +-+ | udf_errno() | +-+ | 22 | +-+ 1 row in set (0.00 sec) mysql> BTW: This is EINVAL -- ciao - Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG on 64bit & if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
On Sat, Jan 31, 2004 at 10:06:04PM -0500, Michael Stassen <[EMAIL PROTECTED]> wrote: > Meanwhile, perhaps a simpler workaround would be to change how you build > the query in Perl so as to avoid the quotes. Unfortunately, the heuristic that DBD::mysql uses cannot be changed or forced. Currently, if the first execution uses an integer, it will never quote, even when you later use a string and vice versa. This is a correctness issue, as mysql (properly) treats string comparisons different to integer comparisons and thus will give different results. These problems, however, cannot be solved with the current mysql api, as (AFAICS), there is no type information. So interfaces to mysql from languages without strong typing have to second-guess, and do this often wrong. The only clean solution to this problem is providing type information, so DBD::mysql knows that e.g. an integer is expected. -- -==- | ==-- _ | ---==---(_)__ __ __ Marc Lehmann +-- --==---/ / _ \/ // /\ \/ / [EMAIL PROTECTED] |e| -=/_/_//_/\_,_/ /_/\_\ XX11-RIPE --+ The choice of a GNU generation | | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG on 64bit & if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
I think the problem has nothing to do with &. Taking the & out of your
examples leaves you with:
mysql> select '9223372036854775809' + 0;
+---+
| '9223372036854775809' + 0 |
+---+
| 9.2233720368548e+18 |
+---+
mysql> select CAST('9223372036854775809' AS UNSIGNED INTEGER);
+-+
| CAST('9223372036854775809' AS UNSIGNED INTEGER) |
+-+
| 9223372036854775807 |
+-+
mysql> select CONVERT('9223372036854775809', UNSIGNED INTEGER);
+--+
| CONVERT('9223372036854775809', UNSIGNED INTEGER) |
+--+
| 9223372036854775807 |
+--+
mysql> select 9223372036854775807 & 127;
+---+
| 9223372036854775807 & 127 |
+---+
| 127 |
+---+
See, the & works correctly. It's the conversion of
'9223372036854775809' to a number which causes your problem. I expect
the warnings about the range of BIGINT in the manual
<http://www.mysql.com/doc/en/Column_types.html> apply here. It says
All arithmetic is done using signed BIGINT or DOUBLE values, so you
shouldn't use unsigned big integers larger than 9223372036854775807 (63
bits) except with bit functions! If you do that, some of the last digits
in the result may be wrong because of rounding errors when converting
the BIGINT to a DOUBLE.
...
`-', `+', and `*' will use BIGINT arithmetic when both arguments are
integer values! This means that if you multiply two big integers (or
results from functions that return integers) you may get unexpected
results when the result is larger than 9223372036854775807.
Of course, it's not clear that CAST and CONVERT shouldn't have worked.
Meanwhile, perhaps a simpler workaround would be to change how you build
the query in Perl so as to avoid the quotes.
Michael
Stefan Traby wrote:
WOW !!
The story goes on:
I wrote the UDF-Functions (and64/or64) and while and it seems
to work:
mysql> select and64('9223372036854775809',127);
+--+
| and64('9223372036854775809',127) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
I noted that mysql can't really dial with unsigned:
(you can't even declare it unsigned)
mysql> select or64('9223372036854775809',127);
+-+
| or64('9223372036854775809',127) |
+-+
|-9223372036854775681 |
+-+
1 row in set (0.00 sec)
On Sun, Feb 01, 2004 at 01:09:34AM +0100, Stefan Traby wrote:
Hi !
select 129 & 127; -- ok(1)
select '129' & 127; -- ok(1)
select (0+'129') & 127; -- ok(1)
select CAST('129' AS UNSIGNED INTEGER) & 127; -- ok(1)
select CONVERT('129', UNSIGNED INTEGER) & 127;-- ok(1)
Doing the same with 64 bit values gives strange results:
select 9223372036854775809 & 127; -- returns 1, correct
select '9223372036854775809' & 127; -- ERROR: returns 127
select (0+'9223372036854775809') & 127; -- ERROR: returns 0
select CAST('9223372036854775809' AS UNSIGNED INTEGER) & 127; -- ERROR: returns 127
select CONVERT('9223372036854775809', UNSIGNED INTEGER) & 127;-- ERROR: returns 127
So please tell me how to perform a bitwise 64bit-AND if
a value is quoted.
Never seen such a strange bug for a long time.
Well, I just found it because DBD::mysql quotes large integer
bind-variables even on perl int64...
So do I need to write an UDF-Function to get the correct behavior
or is there another work-arround to fix this?
--
ciao -
Stefan
--
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: BUG on 64bit & if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
WOW !!
The story goes on:
I wrote the UDF-Functions (and64/or64) and while and it seems
to work:
mysql> select and64('9223372036854775809',127);
+--+
| and64('9223372036854775809',127) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
I noted that mysql can't really dial with unsigned:
(you can't even declare it unsigned)
mysql> select or64('9223372036854775809',127);
+-+
| or64('9223372036854775809',127) |
+-+
|-9223372036854775681 |
+-+
1 row in set (0.00 sec)
On Sun, Feb 01, 2004 at 01:09:34AM +0100, Stefan Traby wrote:
> Hi !
>
> select 129 & 127; -- ok(1)
> select '129' & 127; -- ok(1)
> select (0+'129') & 127; -- ok(1)
> select CAST('129' AS UNSIGNED INTEGER) & 127; -- ok(1)
> select CONVERT('129', UNSIGNED INTEGER) & 127;-- ok(1)
>
> Doing the same with 64 bit values gives strange results:
>
> select 9223372036854775809 & 127; -- returns 1, correct
> select '9223372036854775809' & 127; -- ERROR: returns 127
> select (0+'9223372036854775809') & 127; -- ERROR: returns 0
> select CAST('9223372036854775809' AS UNSIGNED INTEGER) & 127; -- ERROR: returns 127
> select CONVERT('9223372036854775809', UNSIGNED INTEGER) & 127;-- ERROR: returns 127
>
> So please tell me how to perform a bitwise 64bit-AND if
> a value is quoted.
>
> Never seen such a strange bug for a long time.
>
> Well, I just found it because DBD::mysql quotes large integer
> bind-variables even on perl int64...
>
> So do I need to write an UDF-Function to get the correct behavior
> or is there another work-arround to fix this?
>
> --
>
> ciao -
> Stefan
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
ciao -
Stefan
"aclocal - emit a warning if -ac kernel is used. "
Stefan TrabyLinux/ia32 office: +49-721-3523165
Mathystr. 18-20 V/8 Linux/alpha cell: +XX-XXX-XXX
76133 Karlsruhe Linux/sparc http://graz03.kwc.at
Germany Linux/arm mailto:[EMAIL PROTECTED]
Europe Linux/mips mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BUG on 64bit & if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
Hi !
select 129 & 127; -- ok(1)
select '129' & 127; -- ok(1)
select (0+'129') & 127; -- ok(1)
select CAST('129' AS UNSIGNED INTEGER) & 127; -- ok(1)
select CONVERT('129', UNSIGNED INTEGER) & 127;-- ok(1)
Doing the same with 64 bit values gives strange results:
select 9223372036854775809 & 127; -- returns 1, correct
select '9223372036854775809' & 127; -- ERROR: returns 127
select (0+'9223372036854775809') & 127; -- ERROR: returns 0
select CAST('9223372036854775809' AS UNSIGNED INTEGER) & 127; -- ERROR: returns 127
select CONVERT('9223372036854775809', UNSIGNED INTEGER) & 127;-- ERROR: returns 127
So please tell me how to perform a bitwise 64bit-AND if
a value is quoted.
Never seen such a strange bug for a long time.
Well, I just found it because DBD::mysql quotes large integer
bind-variables even on perl int64...
So do I need to write an UDF-Function to get the correct behavior
or is there another work-arround to fix this?
--
ciao -
Stefan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: "if" statement
You could use a CASE statement: SELECT CASE WHEN @exp >= 5 AND @exp <= 10 THEN vacation + 4 WHEN @exp >2 AND @exp <5 THEN vacation + 2 ELSE vacation END -Original Message- From: sakhiya [mailto:[EMAIL PROTECTED] Sent: Thursday, January 29, 2004 4:15 PM To: [EMAIL PROTECTED] Subject: "if" statement Hello all, I'm a newbie and I appreciate any help I can get for my basic question. For example,let's say I have a table with fields experience, vacation. I'm doing, select @exp:=experience from the table then I do some mathematical calculations with @exp variable. My question is how do I write statements for "if @exp >= 5 and @exp <10 then vacation = vacation + 4 else if @exp >2 and @exp < 5 then vacation = vacation +2". Thanks in advance for your help. nishi __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- 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]
"if" statement
Hello all, I'm a newbie and I appreciate any help I can get for my basic question. For example,let's say I have a table with fields experience, vacation. I'm doing, select @exp:=experience from the table then I do some mathematical calculations with @exp variable. My question is how do I write statements for "if @exp >= 5 and @exp <10 then vacation = vacation + 4 else if @exp >2 and @exp < 5 then vacation = vacation +2". Thanks in advance for your help. nishi __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If Else statement
What I have is a shell script that gathers information from a computer, and
I want the script to check to see if there is currently a record that
already exists that has a certain Ethernet(MAC) address. I would also like
to have an ID number for the record that auto-increments so that I can have
an asset number to display on a web page. (Some assets I enter may not have
an Ethernet address because I am creating a database for hardware assets.
Computers, printers, etc., so I need to have an asset ID)
I have ethernet_address set as a Unique Key, and asset_id set as Primary_Key
and to auto-increment.
If I just use replace like below, every time the record is updated, the
asset number that is set to auto-increment is updated. So if it was 5
before, it is now 6. I don't what the asset_id to change of course.
/usr/local/mysql/bin/mysql --user=$username --password=$password
--host=$server cetechnology -e \
"REPLACE INTO hardware_assets (ethernet_address, operating_system)\
VALUES \
('$ethernet_address', '$operating_system')";
I don't currently have MySql 4.1 running on my server, I have the 3.23.53
that comes with OS X Server 10.2, so if the suggestion you gave me is the
only way (or the best) I will have to upgrade the server. If there is
another way to do the same thing, please let me know.
Mike
> From: Egor Egorov <[EMAIL PROTECTED]>
> Date: Mon, 26 Jan 2004 15:40:54 +0200
> To: [EMAIL PROTECTED]
> Subject: Re: If Else statement
>
> Mike Tuller <[EMAIL PROTECTED]> wrote:
>> I am trying write a shell script to check to see if a record exists and if
>> it does, update information, and if it doesn't insert information. Is there
>> a way to do an if else statement in MySql?
>>
>
> If you have PRIMARY KEY or UNIQUE index, take a look at REPLACE and INSERT ..
> ON DUPLICATE KEY UPDATE statements:
> http://www.mysql.com/doc/en/REPLACE.html
> http://www.mysql.com/doc/en/INSERT.html
>
> INSERT .. ON DUPLICATE KEY UPDATE is supported since v4.1.0.
>
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
> __ ___ ___ __
> / |/ /_ __/ __/ __ \/ /Egor Egorov
> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
> <___/ www.mysql.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to do a check to see if I update or insert
you SURE you want to do that in a shell script? I'd do it in perl On Monday 26 January 2004 11:39 am, Mike Tuller wrote: > I am stuck on this problem, and know there has to be a solution. > > I have a shell script, where I want to check to see if a record exists, and > if it does then update the record with new information. If it doesn't then > insert the information gathered by a script. > > How, in MySql, using a shell script, can I do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
