Re: describe table : improvement

[Michael Stassen]

[EMAIL PROTECTED] wrote:

Thankyou Gabriel.

So how does one set a column comment then?

Regards

Keith


As part of the column definition, as documented in the manual 
.


  column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]

Michael

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

Re: commit problem

[balaraju mandala]

Thank you Prassad, and Dilip for your reply.

Prasad, i wiil be pleased if u answer to my query, how i can convert my
tables to Innodb?

Thank You,

regards,
bala

Re: MySQL 4.1.18-64 bit won't start after JS2E update on OS X

[Michael Stassen]

Geoffrey Sneddon wrote:

On 21 Apr 2006, at 17:18, Geoffrey Sneddon wrote:

After the JS2E 5.0 Release 4 update on Mac OS 10.4.6 on the 19th,  
MySQL 4.1.18 64-bit hasn't started. It begins to start up, before  
ending with "ERROR!". Any possible reasons/solutions?


Did you check mysql's error log for details?


Managed to fix it: /tmp was broken.

/tmp should be a symbolic link to /private/tmp, and should be owned  by 
root and the group admin. It should have a CHMOD of 755.


The Fix:

sudo rm -r /tmp
sudo ln -s /private/tmp /tmp

All the best,

Geoffrey Sneddon


Are you saying the update changed the ownership, group, or permissions?  I have 
had OS X updates change the permissions on /tmp 2 or 3 times in the past, but 
there was no change for me when I just installed JS2E 5.0 Release 4 update. 
Also, why was it necessary to remove and recreate the link?  Couldn't you just 
reset whatever changed?


Michael


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

Re: bug in simple select, what is going on?

[Michael Stassen]

kmh496 wrote:

hi,
i am running a join query between the german_english table and the
user_todo_german_english tables, to figure out which words a user is
studying.
the key i am using is a combination of wordid + pos + posn (that is part
of speech == pos)
however, i am not able to correctly do the join because of improper
results.  please see the following

mysql> select * from user_todo_german_english where date_col >
'2006-04-22 07:00:00';
+-++--+--+-+
| mb_id   | wordid | posn | pos  | date_col|
+-++--+--+-+
| curious |  94122 |1 |0 | 2006-04-22 07:04:26 |
| curious | 327400 |1 |0 | 2006-04-22 07:04:40 |
| curious | 327402 |1 |0 | 2006-04-22 07:40:41 |
| curious |  59553 |1 |0 | 2006-04-22 07:40:55 |
| curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
+-++--+--+-+
5 rows in set (0.00 sec)

mysql> select wordid,pos,posn from german_english where wordid in
(86851,59553);
++--+--+
| wordid | pos  | posn |
++--+--+
|  59553 | m|1 |
|  86851 | m|1 |
++--+--+
2 rows in set (0.00 sec)

mysql> select * from user_todo_german_english where wordid = '86851' and
posn = '1' and pos = 'm' AND mb_id='curious';
+-++--+--+-+
| mb_id   | wordid | posn | pos  | date_col|
+-++--+--+-+
| curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
+-++--+--+-+
1 row in set, 1 warning (0.00 sec)

clearly, pos is ZERO in the database, and the letter 'm' in the query.
or is my brain farting again?


You didn't provide the table definitions (output of SHOW CREATE TABLE works 
well), but it appears that pos is a numeric column in user_todo_german_english, 
but a string column in german_english.


I notice that every constant in your WHERE clauses is quoted.  You need to 
realize that 86851 is a number, but '86851' is a string.  When mysql sees


  WHERE numeric_col = 'some string'

it must convert 'some string' to a numeric value in order to make the 
comparison.  That is merely an invisible time waster when 'some string' is just 
a quoted number.  In the case of


  pos = 'm'

however, you get correct, but unanticipated behavior.  As pos is a numeric 
column, 'm' must be converted to a number for the comparison.  Because m is not 
a valid numeric value, it gets converted to 0.  Hence the result you see.


You can find the numeric value of any string by adding 0 to it.  For example,

mysql> SELECT '86851', '86851' + 0, 'm', 'm' + 0;
+---+-+---+-+
| 86851 | '86851' + 0 | m | 'm' + 0 |
+---+-+---+-+
| 86851 |   86851 | m |   0 |
+---+-+---+-+
1 row in set (0.00 sec)

You should do two things:

1) Stop quoting numbers.

2) If 'm' is a valid value for pos, then pos should be a string column rather 
than numeric.


Michael

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

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

[Michael Stassen]

David T. Ashley wrote:

I'm using PHP, and I sometimes INSERT new records in a table.  MySQL assigns
a new autoincrement int field on each INSERT ... nothing surprising there.
It goes 1, 2, 3, etc.

What query can I use to find out what value this int autoincrement assigned
field was?  I could of course SELECT based on what was just inserted, but
that seems inefficient.

Thanks for any help,
Dave.


LAST_INSERT_ID()



Michael

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

How to Find Most Recent Autoincrement Index Assigned???

[David T. Ashley]

I'm using PHP, and I sometimes INSERT new records in a table.  MySQL assigns
a new autoincrement int field on each INSERT ... nothing surprising there.
It goes 1, 2, 3, etc.

What query can I use to find out what value this int autoincrement assigned
field was?  I could of course SELECT based on what was just inserted, but
that seems inefficient.

Thanks for any help,
Dave.



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

Re: WHERE doesn't work

[Michael Stassen]

Tom Lobato wrote:



From: "Nicolas Verhaeghe" <[EMAIL PROTECTED]>
Echo the UPDATE query itself and then copy/paste it here that way 
we'll see

what is wrong.


   I already tried it. See some msgs before this. No sucess. When I 
paste the exactily same command to the mysql client, all works fine 
(only the right record is updated).


   Tom


Tom,

With all due respect, there is almost certainly an error in your code.  I've 
been on this list for years, and so far as I can recall, every instance of mysql 
appearing to do something strange from php turned out to be a flaw in the php 
code, not a problem with mysql.  Many of us on the list perform updates with 
WHERE clauses via php without problems.  The most likely explanation is that 
there is something wrong with the WHERE clause as sent to mysql by your php app.


So, you can keep repeating that it doesn't work, and we can keep flailing away 
with blind guesses, but I expect you won't get a solution until you actually 
show us the code.


Also, you've told us that your PHP Version is 4.4.2, and your Mysql client API 
version is 3.23.49, by which I expect you mean that your copy of php was built 
with the 3.23.49 version of the mysql client library.  Unless I've missed it 
though, you haven't told us what version of mysql you have, how it was installed 
(official binary, some other binary, compiled yourself?), and what OS it's on. 
That information may help.


Michael

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

Re: WHERE doesn't work

[Tom Lobato]

From: "Nicolas Verhaeghe" <[EMAIL PROTECTED]>
Echo the UPDATE query itself and then copy/paste it here that way we'll see
what is wrong.


   I already tried it. See some msgs before this. No sucess. When I paste 
the exactily same command to the mysql client, all works fine (only the 
right record is updated).





   Tom 



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

Re: [SPAM] Re: bug in simple select, what is going on?

[kmh496]

2006-04-22 (토), 00:49 +0100, Philippe Poelvoorde 쓰시길:
> Hi,
> 
> > mysql> select * from user_todo_german_english where wordid = '86851' and
> > posn = '1' and pos = 'm' AND mb_id='curious';
> > +-++--+--+-+
> > | mb_id   | wordid | posn | pos  | date_col|
> > +-++--+--+-+
> > | curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
> > +-++--+--+-+
> > 1 row in set, 1 warning (0.00 sec)
> >
> > mysql> select * from user_todo_german_english where wordid = '86851' and
> > posn = '1' and pos = 'm' AND mb_id='curious';
> > +-++--+--+-+
> > | mb_id   | wordid | posn | pos  | date_col|
> > +-++--+--+-+
> > | curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
> > +-++--+--+-+
> > 1 row in set, 1 warning (0.00 sec)
> 
> there are clear warnings, what are they ?
> 
mysql> show warnings;
+-+--+---+
| Level   | Code | Message   |
+-+--+---+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'm' |
+-+--+---+
1 row in set (0.00 sec)

mysql>   
-- 
my site http://www.myowndictionary.com";>myowndictionary was
made to help students of many languages learn them faster.






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

Re: bug in simple select, what is going on?

[kmh496]

2006-04-22 (토), 08:43 +0900, kmh496 쓰시길:
> hi,
> i am running a join query between the german_english table and the
> user_todo_german_english tables, to figure out which words a user is
> studying.
> the key i am using is a combination of wordid + pos + posn (that is part
> of speech == pos)
> however, i am not able to correctly do the join because of improper
> results.  please see the following
> 
> 
> mysql> select * from user_todo_german_english where date_col >
> '2006-04-22 07:00:00';
> +-++--+--+-+
> | mb_id   | wordid | posn | pos  | date_col|
> +-++--+--+-+
> | curious |  94122 |1 |0 | 2006-04-22 07:04:26 |
> | curious | 327400 |1 |0 | 2006-04-22 07:04:40 |
> | curious | 327402 |1 |0 | 2006-04-22 07:40:41 |
> | curious |  59553 |1 |0 | 2006-04-22 07:40:55 |
> | curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
> +-++--+--+-+
> 5 rows in set (0.00 sec)
> 
> mysql> select wordid,pos,posn from german_english where wordid in
> (86851,59553);
> ++--+--+
> | wordid | pos  | posn |
> ++--+--+
> |  59553 | m|1 |
> |  86851 | m|1 |
> ++--+--+
> 2 rows in set (0.00 sec)
> 
> mysql> select * from user_todo_german_english where wordid = '86851' and
> posn = '1' and pos = 'm' AND mb_id='curious';
> +-++--+--+-+
> | mb_id   | wordid | posn | pos  | date_col|
> +-++--+--+-+
> | curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
> +-++--+--+-+
> 1 row in set, 1 warning (0.00 sec)
> 
> mysql> select * from user_todo_german_english where wordid = '86851' and
> posn = '1' and pos = 'm' AND mb_id='curious';
> +-++--+--+-+
> | mb_id   | wordid | posn | pos  | date_col|
> +-++--+--+-+
> | curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
> +-++--+--+-+
> 1 row in set, 1 warning (0.00 sec)
> 
> mysql>  
> 
> clearly, pos is ZERO in the database, and the letter 'm' in the query.
> or is my brain farting again?
> 
> 
> -- 
> my site http://www.myowndictionary.com";>myowndictionary was
> made to help students of many languages learn them faster.
> 
further information

mysql 2035  0.4  1.6  22548 17088 ?Sl   Apr09
78:46 /usr/local/mysql-5.1.7-beta/libexec/mysqld
--defaults-file=/etc/my.cnf.dict_explicit
--basedir=/usr/local/mysql-5.1.7-beta --datadir=/bup/mysqldata/data
--socket=/tmp/mysql.sock --log-output=FILE

the same results from the commandline using the old mysql client
[EMAIL PROTECTED] current]$ mysql --version
mysql  Ver 14.7 Distrib 4.1.11, for redhat-linux-gnu (i686)

and using php.  
the databse encoding is utf8;  
but i get the following from php after calling 'set names utf8' and from
the clien without saying so.

the table structure is
mysql> show create table user_todo_german_english;
+--+---
+
| Table| Create Table

|
+--+---
+
| user_todo_german_english | CREATE TABLE `user_todo_german_english` (
  `mb_id` varchar(25) default NULL,
  `wordid` int(11) default NULL,
  `posn` tinyint(3) default '1',
  `pos` tinyint(3) default '0',
  `date_col` datetime NOT NULL default '2006-03-23 22:50:02'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--+---
+
1 row in set (0.00 sec)

mysql>
-- 
my site http://www.myowndictionary.com";>myowndictionary was
made to help students of many languages learn them faster.






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

Re: bug in simple select, what is going on?

[Philippe Poelvoorde]

Hi,

> mysql> select * from user_todo_german_english where wordid = '86851' and
> posn = '1' and pos = 'm' AND mb_id='curious';
> +-++--+--+-+
> | mb_id   | wordid | posn | pos  | date_col|
> +-++--+--+-+
> | curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
> +-++--+--+-+
> 1 row in set, 1 warning (0.00 sec)
>
> mysql> select * from user_todo_german_english where wordid = '86851' and
> posn = '1' and pos = 'm' AND mb_id='curious';
> +-++--+--+-+
> | mb_id   | wordid | posn | pos  | date_col|
> +-++--+--+-+
> | curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
> +-++--+--+-+
> 1 row in set, 1 warning (0.00 sec)

there are clear warnings, what are they ?

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

bug in simple select, what is going on?

[kmh496]

hi,
i am running a join query between the german_english table and the
user_todo_german_english tables, to figure out which words a user is
studying.
the key i am using is a combination of wordid + pos + posn (that is part
of speech == pos)
however, i am not able to correctly do the join because of improper
results.  please see the following


mysql> select * from user_todo_german_english where date_col >
'2006-04-22 07:00:00';
+-++--+--+-+
| mb_id   | wordid | posn | pos  | date_col|
+-++--+--+-+
| curious |  94122 |1 |0 | 2006-04-22 07:04:26 |
| curious | 327400 |1 |0 | 2006-04-22 07:04:40 |
| curious | 327402 |1 |0 | 2006-04-22 07:40:41 |
| curious |  59553 |1 |0 | 2006-04-22 07:40:55 |
| curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
+-++--+--+-+
5 rows in set (0.00 sec)

mysql> select wordid,pos,posn from german_english where wordid in
(86851,59553);
++--+--+
| wordid | pos  | posn |
++--+--+
|  59553 | m|1 |
|  86851 | m|1 |
++--+--+
2 rows in set (0.00 sec)

mysql> select * from user_todo_german_english where wordid = '86851' and
posn = '1' and pos = 'm' AND mb_id='curious';
+-++--+--+-+
| mb_id   | wordid | posn | pos  | date_col|
+-++--+--+-+
| curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
+-++--+--+-+
1 row in set, 1 warning (0.00 sec)

mysql> select * from user_todo_german_english where wordid = '86851' and
posn = '1' and pos = 'm' AND mb_id='curious';
+-++--+--+-+
| mb_id   | wordid | posn | pos  | date_col|
+-++--+--+-+
| curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
+-++--+--+-+
1 row in set, 1 warning (0.00 sec)

mysql>  

clearly, pos is ZERO in the database, and the letter 'm' in the query.
or is my brain farting again?


-- 
my site http://www.myowndictionary.com";>myowndictionary was
made to help students of many languages learn them faster.






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

Re: Older version MySQL

[Mike Blezien]

we where hoping to avoid all that, if possible :) it's not much of a product 
server anymore, just for some small developement.


I guess we'll stick with what's installed.

Mike
- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Friday, April 21, 2006 5:38 PM
Subject: RE: Older version MySQL




You might like to take a look at smart package manager.

It's a wizz at dealing with dependancy conflicts.

http://labix.org/smart
http://wiki.suselinuxsupport.de/wikka.php?wakka=HowtoSmartPackageManager
http://forums.suselinuxsupport.de/index.php?showtopic=21121

HTH

Keith





In theory, theory and practice are the same;
in practice they are not.

On Fri, 21 Apr 2006, Nicolas Verhaeghe wrote:


To: 'Mike Blezien' <[EMAIL PROTECTED]>,
'MySQL List' 
From: Nicolas Verhaeghe <[EMAIL PROTECTED]>
Subject: RE: Older version MySQL

Dependency issue, I would use RPM Package Manager.

It always solved that type of issue.

http://www.rpm.org/


-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED]
Sent: Friday, April 21, 2006 3:17 PM
To: MySQL List
Subject: Older version MySQL


Hello,

I am attempting to upgrade one of our older servers running RH 7.3
w/glibc-2.2.5-44

it's currently using 3.23.58 and tried installing 4.0.26 rpm's for linux,
but
won't install due to the following error:

libc.so.6(GLIBC_2.3)   is needed by MySQL-shared-4.0.26-0
libpthread.so.0(GLIBC_2.3.2)   is needed by MySQL-shared-4.0.26-0
libc.so.6(GLIBC_2.3)   is needed by MySQL-shared-compat-4.0.26-0
libpthread.so.0(GLIBC_2.3.2)   is needed by
MySQL-shared-compat-4.0.26-0

Now of the RPM's will install. Is there a 4.0.+ version that will install on

this type of server without have to upgrade alot of other
stuff ?? Or can we

build it from source, and if so, what is the recommend configure options
used to
build from source ?

TIA,
Mike(mickalo)Blezien



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

RE: Older version MySQL

[mysql]

You might like to take a look at smart package manager.

It's a wizz at dealing with dependancy conflicts.

http://labix.org/smart
http://wiki.suselinuxsupport.de/wikka.php?wakka=HowtoSmartPackageManager
http://forums.suselinuxsupport.de/index.php?showtopic=21121

HTH

Keith

In theory, theory and practice are the same;
in practice they are not.

On Fri, 21 Apr 2006, Nicolas Verhaeghe wrote:

> To: 'Mike Blezien' <[EMAIL PROTECTED]>,
> 'MySQL List' 
> From: Nicolas Verhaeghe <[EMAIL PROTECTED]>
> Subject: RE: Older version MySQL
> 
> Dependency issue, I would use RPM Package Manager.
> 
> It always solved that type of issue.
> 
> http://www.rpm.org/
> 
> 
> -Original Message-
> From: Mike Blezien [mailto:[EMAIL PROTECTED] 
> Sent: Friday, April 21, 2006 3:17 PM
> To: MySQL List
> Subject: Older version MySQL
> 
> 
> Hello,
> 
> I am attempting to upgrade one of our older servers running RH 7.3 
> w/glibc-2.2.5-44
> 
> it's currently using 3.23.58 and tried installing 4.0.26 rpm's for linux,
> but 
> won't install due to the following error:
> 
> libc.so.6(GLIBC_2.3)   is needed by MySQL-shared-4.0.26-0
> libpthread.so.0(GLIBC_2.3.2)   is needed by MySQL-shared-4.0.26-0
> libc.so.6(GLIBC_2.3)   is needed by MySQL-shared-compat-4.0.26-0
> libpthread.so.0(GLIBC_2.3.2)   is needed by
> MySQL-shared-compat-4.0.26-0
> 
> Now of the RPM's will install. Is there a 4.0.+ version that will install on
> 
> this type of server without have to upgrade alot of other 
> stuff ?? Or can we
> 
> build it from source, and if so, what is the recommend configure options
> used to 
> build from source ?
> 
> TIA,
> Mike(mickalo)Blezien
> ===
> Thunder Rain Internet Publishing
> Providing Internet Solution that Work === 

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

RE: Older version MySQL

[Nicolas Verhaeghe]

Dependency issue, I would use RPM Package Manager.

It always solved that type of issue.

http://www.rpm.org/


-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 21, 2006 3:17 PM
To: MySQL List
Subject: Older version MySQL


Hello,

I am attempting to upgrade one of our older servers running RH 7.3 
w/glibc-2.2.5-44

it's currently using 3.23.58 and tried installing 4.0.26 rpm's for linux,
but 
won't install due to the following error:

libc.so.6(GLIBC_2.3)   is needed by MySQL-shared-4.0.26-0
libpthread.so.0(GLIBC_2.3.2)   is needed by MySQL-shared-4.0.26-0
libc.so.6(GLIBC_2.3)   is needed by MySQL-shared-compat-4.0.26-0
libpthread.so.0(GLIBC_2.3.2)   is needed by
MySQL-shared-compat-4.0.26-0

Now of the RPM's will install. Is there a 4.0.+ version that will install on

this type of server without have to upgrade alot of other stuff ?? Or can we

build it from source, and if so, what is the recommend configure options
used to 
build from source ?

TIA,
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work === 


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

Older version MySQL

[Mike Blezien]

Hello,

I am attempting to upgrade one of our older servers running RH 7.3 
w/glibc-2.2.5-44


it's currently using 3.23.58 and tried installing 4.0.26 rpm's for linux, but 
won't install due to the following error:


   libc.so.6(GLIBC_2.3)   is needed by MySQL-shared-4.0.26-0
   libpthread.so.0(GLIBC_2.3.2)   is needed by MySQL-shared-4.0.26-0
   libc.so.6(GLIBC_2.3)   is needed by MySQL-shared-compat-4.0.26-0
   libpthread.so.0(GLIBC_2.3.2)   is needed by MySQL-shared-compat-4.0.26-0

Now of the RPM's will install. Is there a 4.0.+ version that will install on 
this type of server without have to upgrade alot of other stuff ?? Or can we 
build it from source, and if so, what is the recommend configure options used to 
build from source ?


TIA,
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
=== 



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

RE: WHERE doesn't work

[Nicolas Verhaeghe]

Magic quotes replaces the ' with '' so that when you execute an SQL query,
it is encoded properly.

Or else someone could put a single quote in a form field and screw up your
database.

I do not like magic quote myself.

But we're going OT.

Echo the UPDATE query itself and then copy/paste it here that way we'll see
what is wrong.

Maybe the $id variable is not replaced with the proper content.

-Original Message-
From: Tom Lobato [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 21, 2006 2:31 PM
To: mysql@lists.mysql.com
Subject: Re: WHERE doesn't work



>From: "Nicolas Verhaeghe" <[EMAIL PROTECTED]>
>Ok but what is the problem then?

WHERE doesn't work. Iwrite WHERE in the command for UPDATE exactily one 
record, defined by id = $id, and mysql server UPDATE all the records in the 
table.

>Also are you taking care of converting the single quotes or do you have 
>magic quotes set to on?

What do you mean with "converting the single quotes"?
I dont know what is magic quotes, but, from phinfo.php:

magic quotes gpcon on
magic quotes runtime  off off
magic quotes sybase   off off


Tom 


-- 
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: WHERE doesn't work

[Tom Lobato]

From: "Nicolas Verhaeghe" <[EMAIL PROTECTED]>
Ok but what is the problem then?


   WHERE doesn't work. Iwrite WHERE in the command for UPDATE exactily one 
record, defined by id = $id, and mysql server UPDATE all the records in the 
table.



Also are you taking care of converting the single quotes or do you have
magic quotes set to on?


   What do you mean with "converting the single quotes"?
   I dont know what is magic quotes, but, from phinfo.php:

magic quotes gpcon on
magic quotes runtime  off off
magic quotes sybase   off off


   Tom 



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

RE: WHERE doesn't work

[Nicolas Verhaeghe]

Ok but what is the problem then?

In the case of a group, you need to use "HAVING" for a condition on a listed
field, and if you "id" is an integer, then you may want to drop the '

Also are you taking care of converting the single quotes or do you have
magic quotes set to on?

-Original Message-
From: Tom Lobato [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 21, 2006 12:11 PM
To: mysql@lists.mysql.com
Subject: Re: WHERE doesn't work



Yes, but this output indeed was generated when I made no changes in the 
loaded fields of the web form. When I change something it outputs Changed: 
2, for the case of 2 records in the table.

>>From: "Nicolas Verhaeghe" <[EMAIL PROTECTED]>
>> Normal. MySQL returns 0 changes when the data before and after the
>>UPDATE
>> is
>> the same.
>
> --
> -Rows matched: 2 Changed: 0 Warnings: 0-


Tom 


-- 
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: WHERE doesn't work

[Nicolas Verhaeghe]

Ok but what is the problem then?

In the case of a group, you need to use "HAVING" for a condition on a listed
field, and if you "id" is an integer, then you may want to drop the '

Also are you taking care of converting the single quotes or do you have
magic quotes set to on?

-Original Message-
From: Tom Lobato [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 21, 2006 12:11 PM
To: mysql@lists.mysql.com
Subject: Re: WHERE doesn't work



Yes, but this output indeed was generated when I made no changes in the 
loaded fields of the web form. When I change something it outputs Changed: 
2, for the case of 2 records in the table.

>>From: "Nicolas Verhaeghe" <[EMAIL PROTECTED]>
>> Normal. MySQL returns 0 changes when the data before and after the
>>UPDATE
>> is
>> the same.
>
> --
> -Rows matched: 2 Changed: 0 Warnings: 0-


Tom 


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


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

Re: MySQL 4.1.18-64 bit won't start after JS2E update on OS X

[Geoffrey Sneddon]

On 21 Apr 2006, at 17:18, Geoffrey Sneddon wrote:


After the JS2E 5.0 Release 4 update on Mac OS 10.4.6 on the 19th,  
MySQL 4.1.18 64-bit hasn't started. It begins to start up, before  
ending with "ERROR!". Any possible reasons/solutions?




Managed to fix it: /tmp was broken.

/tmp should be a symbolic link to /private/tmp, and should be owned  
by root and the group admin. It should have a CHMOD of 755.


The Fix:

sudo rm -r /tmp
sudo ln -s /private/tmp /tmp

All the best,

Geoffrey Sneddon


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

Re: WHERE doesn't work

[Tom Lobato]

   Yes, but this output indeed was generated when I made no changes in the 
loaded fields of the web form. When I change something it outputs Changed: 
2, for the case of 2 records in the table.



From: "Nicolas Verhaeghe" <[EMAIL PROTECTED]>
Normal. MySQL returns 0 changes when the data before and after the UPDATE 
is

the same.


--
-Rows matched: 2 Changed: 0 Warnings: 0-



   Tom 



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

RE: WHERE doesn't work

[Nicolas Verhaeghe]

Normal. MySQL returns 0 changes when the data before and after the UPDATE is
the same.

-Original Message-
From: Tom Lobato [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 21, 2006 9:50 AM
To: mysql@lists.mysql.com
Subject: Re: WHERE doesn't work


Writing echo
"-".mysql_error()."--".mysql_info()."-"; 
below 
$result = mysql_query($command);
gives the output:

-- 
-Rows matched: 2 Changed: 0 Warnings: 0-


So, it seems mysql doesnt get error. It simply ignores the WHERE.



>From: "George Law" <[EMAIL PROTECTED]>
>echo mysql_error()."".mysql_info();


-- 
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: WHERE doesn't work

[Nicolas Verhaeghe]

Normal. MySQL returns 0 changes when the data before and after the UPDATE is
the same.

-Original Message-
From: Tom Lobato [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 21, 2006 9:50 AM
To: mysql@lists.mysql.com
Subject: Re: WHERE doesn't work


Writing echo
"-".mysql_error()."--".mysql_info()."-"; 
below 
$result = mysql_query($command);
gives the output:

-- 
-Rows matched: 2 Changed: 0 Warnings: 0-


So, it seems mysql doesnt get error. It simply ignores the WHERE.



>From: "George Law" <[EMAIL PROTECTED]>
>echo mysql_error()."".mysql_info();


-- 
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: Loop Trigger

[Jeff Smelser]

On Wednesday 19 April 2006 11:10, Lucas Vendramin wrote:
> Hi all.
> I am creating a trigger that update the some table witch call it.
> Ex:
> create table t1 (
>  id int, name varchar(50), c int default 0, father int,
>  primary key(id),
>  index (father),
>  foreign key (father) references t1(id) on update restrict on delete
> restrict );
> create trigger tg_t1
> before update on t1
> for each row
> begin
>  update t1 set c=c+1 where father=NEW.id;
> end;

set NEW.c = NEW.c + 1;

or whatever. You dont update on a before trigger.. 

Jeff


pgpeUL5J7xa3l.pgp
Description: PGP signature

Re: Combining ansi and theta joins bug?

[Pat Adams]

On Fri, 2006-04-21 at 11:42 -0500, Duzenbury, Rich wrote:
> Is there some known bug about combining theta and ansi style joins in
> the same query?  As I say, this works on a 4.1 server, and it will be
> troublesome to convert all of the old queries in order to upgrade. 

In MySQL 5.0.12 they changed the way MySQL handles joins to conform to
the ANSI standard.

http://dev.mysql.com/doc/refman/5.0/en/join.html

"Previously, the comma operator (,) and JOIN both had the same
precedence, so the join expression t1, t2 JOIN t3 was interpreted as
((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is
interpreted as (t1, (t2 JOIN t3)). This change affects statements that
use an ON clause, because that clause can refer only to columns in the
operands of the join, and the change in precedence changes
interpretation of what those operands are."
-- 
Pat Adams
Digital Darkness Promotions
Check out the Dallas Music Wiki http://digitaldarkness.com/tiki


signature.asc
Description: This is a digitally signed message part

Re: Per query DB stats... ideally for InnoDB

[Samuel Ziegler]

Thanks for the pointer, Keith.  I had not seen that tool before.
However, while it looks useful, it doesn't seem to address the problem
that I need to solve.

Any other thoughts?

Thanks again,
  - Sam

On Wed, 2006-04-19 at 23:48 +0100, [EMAIL PROTECTED] wrote:
> Have you taken a look at this Samuel?
> 
> http://www.mysql.com/products/tools/administrator/index.html
> 
> Check out the demo in the TRH corner.
> 
> May be of some use to you.
> 
> Regards
> 
> Keith
> 
> In theory, theory and practice are the same;
> in practice they are not.
> 
> On Wed, 19 Apr 2006, Samuel Ziegler wrote:
> 
> > To: [EMAIL PROTECTED]
> > From: Samuel Ziegler <[EMAIL PROTECTED]>
> > Subject: Re: Per query DB stats... ideally for InnoDB
> > 
> > Those functions do give useful information, but as you point out, the
> > trick is associating that information with a specific query.  For my
> > use, there will most likely be other SQL activity going on at the same
> > time which makes doing a 'SHOW ENGINE ...' before and after the query
> > not very useful.
> > 
> > Thanks for the pointers, however.
> >   - Sam
> > 
> > On Wed, 2006-04-19 at 23:30 +0100, [EMAIL PROTECTED] wrote:
> > > Is this of any use at all?
> > > 
> > > From the 5.0.18 manual:
> > > 
> > > 13.5.4.7. SHOW ENGINE Syntax
> > > 
> > > SHOW ENGINE engine_name {LOGS | STATUS }
> > > 
> > > SHOW ENGINE displays log or status information about 
> > > storage engines. The following statements currently are 
> > > supported: 
> > > 
> > > snip
> > > SHOW ENGINE INNODB STATUS
> > > (or SHOW INNODB STATUS)
> > > 
> > > Both return alot of info on the InnoDB storage engine. Not 
> > > sure how to relate this to each SQL query though.
> > > 
> > > may be of interest too:
> > > 
> > > 13.5.4.16. SHOW PROCESSLIST Syntax
> > > 
> > > SHOW [FULL] PROCESSLIST
> > > 
> > > SHOW PROCESSLIST shows you which threads are running. You 
> > > can also get this information using the mysqladmin 
> > > processlist statement. If you have the SUPER privilege, you 
> > > can see all threads. Otherwise, you can see only your own 
> > > threads (that is, threads associated with the MySQL account 
> > > that you are using). See Section 13.5.5.3, KILL Syntax. If 
> > > you do not use the FULL keyword, only the first 100 
> > > characters of each statement are shown in the Info field.
> > > 
> > > This statement is very useful if you get the too many 
> > > connections error message and want to find out what is going 
> > > on. MySQL reserves one extra connection to be used by 
> > > accounts that have the SUPER privilege, to ensure that 
> > > administrators should always be able to connect and check 
> > > the system (assuming that you are not giving this privilege 
> > > to all your users).
> > > 
> > > Regards
> > > 
> > > Keith
> > > 
> > > In theory, theory and practice are the same;
> > > in practice they are not.
> > > 
> > > On Wed, 19 Apr 2006, Samuel Ziegler wrote:
> > > 
> > > > To: mysql@lists.mysql.com
> > > > From: Samuel Ziegler <[EMAIL PROTECTED]>
> > > > Subject: Per query DB stats... ideally for InnoDB
> > > > 
> > > > Is there any way to retrieve per SQL query stats from MySQL?
> > > > Specifically for my need, the ability to determine the amount of system
> > > > resources required to perform the query, ie CPU, disk usage, etc...
> > > > 
> > > > I poked through the docs & did some net searching, but couldn't find
> > > > anything that I could use.
> > > > 
> > > > I chatted with someone who thought that InnoDB had had some code added
> > > > to it to start down this path, but that it wasn't exposed to the user
> > > > level at all.
> > > > 
> > > > An alternative would be a good method of determining the resource cost
> > > > of a query though an examination of the explain data.
> > > > 
> > > > Thanks!
> > > >   - Sam
> 


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

Subselect application

[Chris White]

I was looking around the list search and didn't find much on this subject 
(maybe didn't look back far enough), but I was discussing with a coworker 
about a reasonable application of subselects vs. a WHERE clause or table 
join.  

Thank you in advance.
-- 
Chris White
Interfuel

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

Re: WHERE doesn't work

[Tom Lobato]

From: "Jay Blanchard" <[EMAIL PROTECTED]>
Nope, I do this all of the time.


   me too =)


Head on over to the PHP list and post
your code and see what they say.


   ok, I'll post there.




   Thank you
   Tom


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

RE: WHERE doesn't work

[Jay Blanchard]

[snip]
Maybe it a bug of the php's mysql API?
[/snip]

Nope, I do this all of the time. Head on over to the PHP list and post
your code and see what they say.

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

Re: WHERE doesn't work

[Tom Lobato]

   Maybe it a bug of the php's mysql API?




   Tom

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

Re: WHERE doesn't work

[Tom Lobato]

From: "George Law" <[EMAIL PROTECTED]>
How many rows are in the table, just 2?  (ie - is it updating ALL the
rows?)


   In the instant of this test, just 2, but as I have tested several times,
with any number it behaves so, 2, 10, 15.


As someone else on the list mentioned, maybe you need to remove the
quotes around '5'


   I tried, but didnt work


Or maybe specify where id BINARY = 5;


   I tried now, but didnt work



   Tom 



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

Re: WHERE doesn't work

[Tom Lobato]

From: "Barry" <[EMAIL PROTECTED]>
Tried: WHERE id = 5 ?


   Yes, same trouble 8-(





   Tom

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

Re: WHERE doesn't work

[Tom Lobato]

Writing echo
   "-".mysql_error()."--".mysql_info()."-"; 
below 
   $result = mysql_query($command);

gives the output:

--
-Rows matched: 2 Changed: 0 Warnings: 0-


So, it seems mysql doesnt get error. It simply ignores the WHERE.




From: "George Law" <[EMAIL PROTECTED]>
echo mysql_error()."".mysql_info();



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

Combining ansi and theta joins bug?

[Duzenbury, Rich]

Hi all,

This query works fine on a 4.1 server, but not a 5.0 server:

Select * 
from 
agentrelationships, agents as a2
left outer join agents
on 
agentrelationships.agentidparent = agents.agentid
where
agentrelationships.agentidchild = a2.agentid

On a 5.0 server, I receive 'unknown column
agentrelationships.agentidparent in on clause'.


If I rework the query to change the theta style joins to ansi style
joins, it works fine. 
Select *
from
agentrelationships
left outer join agents
on
agentrelationships.agentidparent = agents.agentid
join agents as a2 on
agentrelationships.agentidchild = a2.agentid

Is there some known bug about combining theta and ansi style joins in
the same query?  As I say, this works on a 4.1 server, and it will be
troublesome to convert all of the old queries in order to upgrade.

Thank you.

Regards,

Rich Duzenbury

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

MySQL 4.1.18-64 bit won't start after JS2E update on OS X

[Geoffrey Sneddon]

After the JS2E 5.0 Release 4 update on Mac OS 10.4.6 on the 19th,  
MySQL 4.1.18 64-bit hasn't started. It begins to start up, before  
ending with "ERROR!". Any possible reasons/solutions?



- Geoffrey Sneddon




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

Re: (Errcode: 13) after moving data directory

[boll]

Dominik Klein wrote:



Did you check FAT-permissions?
When mounting a FAT-partition, you have to set explicit permissions 
while mounting as FAT does not understand the unix permission concept.


Try to mount this way:
mount -t vfat -o uid=mysql,gid=mysql,rw,umask=007 
/dev/[yourdevicename] /your/mountpoint



Hi  Dominik,

In /etc/fstab the partition is mounted with this line:
   /dev/hdb2/mnt/FATvfatuid=27,gid=27,umask=000  0 0
...which I think is the same as what you recommend (uid  27 is "mysql") .

What I really don't understand is:
Why mysqld will start up and use the dataq on the FAT partition (as I 
want it to do) if I start it with "mysqld_safe",
but when I boot the computer or try, as root, "service mysqld start", it 
fails to start with these log errors:

   060421 08:43:10  mysqld started
   060421  8:43:11 [Warning] Can't create test file 
/mnt/FAT/mysqldata/localhost.lower-test
   /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' 
(Errcode: 13)

   060421  8:43:11 [ERROR] Aborting

I'm guessing that since mysqld_safe runs as user mysql, maybe mysqld 
runs as a different user?

How would I find that out?

I will keep  reading the manual,  but  will be grateful for  any ideas.



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

Re: mysql 5 vs. mysql 4: str_to_date

[Dan Nelson]

In the last episode (Apr 21), Vincente Aggrippino said:
> The following query runs fine on my test server at home, running
> MySQL 5.0.15, but on my hosting provider's server, running MySQL
> 4.0.24, it gets a syntax error.  Can anyone help me figure out why?
> ...
> 
> insert into product (name, url, cost, purchase_dt)
> values('Whatchamacallit', 'http://www.whatchamacallit.com', 5,
> str_to_date('2006-04-20', '%Y-%m-%d'))
> 
> Here's the error message :
> #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 '('2006-04-20', '%Y-%m-%d'))' at line 2

>From the manual:

 *  STR_TO_DATE(str,format)  

 STR_TO_DATE() is available as of MySQL 4.1.1. 

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html


-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: Working out Square Footage with Feet and Inches

[2wsxdr5]

Shaun wrote:


I have stored the dimensions as decimal(4,2), does this make a difference?
 



not a very good way to do it if you ask me but here is how to do the 
calculation.


SELECT ((FLOOR(X) + ((X - FLOOR(X))/0.12)) * (FLOOR(Y) + ((Y - 
FLOOR(Y))/0.12))) as SqFt.


FLOOR(X) gives you 6 from the 6.11
X - FLOOR(X) gives you the .11 part
.11/.12 gives you the appropriate fraction of a foot.to add back to X 
then the same thing for Y and multiply.


--
Chris W
KE5GIX

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

http://thewishzone.com


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

If exists query.

[Paul Halliday]

I am trying to formulate a query that will increment if a condition is true.

For example, if I do a select (just let me know if there was data on
this day, if so increment count by 1 and check the next day) where
timestamp between jan and feb.

Thanks.

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

Re: (Errcode: 13) after moving data directory

[Dominik Klein]

boll schrieb:

Hi-
Using MySQL 4.1.11 on Fedora 4.
I moved my data directory to a FAT partition in order to share it with 
Windows dual-boot.
Now when I try to start mysqld normally, it fails with these messages in 
the log:


060420 18:16:03  mysqld started
060420 18:16:03 [Warning] Can't create test file 
/mnt/FAT/mysqldata/localhost.lower-test
/usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' 
(Errcode: 13)

060420 18:16:03 [ERROR] Aborting

However, I can start mysqld using: mysqld_safe, so I know it's possible.
Any suggestions? Thanks in advance.



Did you check FAT-permissions?
When mounting a FAT-partition, you have to set explicit permissions 
while mounting as FAT does not understand the unix permission concept.


Try to mount this way:
mount -t vfat -o uid=mysql,gid=mysql,rw,umask=007 /dev/[yourdevicename] 
/your/mountpoint


Then it should work.

Regards,
Dominik

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

RE: Carriage return in query ???

[George Law]

'\n' works for me in the command line client

select concat('Co: ABC','\n','Acct: 123','\n','Drive: Summer Special');

+-+
| concat('Co: ABC','\n','Acct: 123','\n','Drive: Summer Special') |
+-+
| Co: ABC
Acct: 123
Drive: Summer Special |
+-+



If you are doing this in PHP, why not just use ''?
Maybe you are gettig extra slashes you need to strip? 
 http://us2.php.net/manual/en/function.stripslashes.php


 

-Original Message-
From: John Thorne [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 21, 2006 9:07 AM
To: 'mysql@lists.mysql.com'
Subject: Carriage return in query ???

Hello 

Trying to CONCAT several fields into one with carriage in a
Dreamweaver/php
recordset 

I found an example for MS SQL

SELECT recid, CoName, Drive, Del_Proj, 
'Co: ' +CoName + CHAR(13) + 'Acct: ' + Acct + CHAR(13) +'Drive: ',
Drive) AS
Info
FROM data
ORDER BY recid ASC

Desired Result:

Co: ABC 
Acct: 123
Drive: Summer Special

mySQL:
SELECT recid, CoName, Drive, Del_Proj, 
CONCAT('Co: ',CoName, what syntax here??,'Acct: ',Acct,, what syntax
here
??,'Drive: ', Drive) AS Info
FROM data
ORDER BY recid ASC

have tried '\n' '\\n' '\r' '\\r'  etc 

help
thanks

jrt



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

Carriage return in query ???

[John Thorne]

Hello 

Trying to CONCAT several fields into one with carriage in a Dreamweaver/php
recordset 

I found an example for MS SQL

SELECT recid, CoName, Drive, Del_Proj, 
'Co: ' +CoName + CHAR(13) + 'Acct: ' + Acct + CHAR(13) +'Drive: ', Drive) AS
Info
FROM data
ORDER BY recid ASC

Desired Result:

Co: ABC 
Acct: 123
Drive: Summer Special

mySQL:
SELECT recid, CoName, Drive, Del_Proj, 
CONCAT('Co: ',CoName, what syntax here??,'Acct: ',Acct,, what syntax here
??,'Drive: ', Drive) AS Info
FROM data
ORDER BY recid ASC

have tried '\n' '\\n' '\r' '\\r'  etc 

help
thanks

jrt



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

commit problem

[balaraju mandala]

Hi,

I have a problem with 'commit'. My database contain non transaction tables.
When ever i do something wrong on DB, i am unable to rollback. I am setting
set sutocommit off, but the result is same, no rollback is happening. Please
help me!

regards,
bala

Re: Execution time of "show databases" increases when fewer databasesare visible

[Alexey Polyakov]

On 4/21/06, Nico Sabbi <[EMAIL PROTECTED]> wrote:

> the more databases a user has the less time it takes to execute "show
> databases" and vice versa:

If no rows in `show databases` output appear because some user has
some table/columns privs but no db privs for a database in question,
then I think that check_grant_db I mentioned in previous email may be
safely hacked away.

--
Alexey Polyakov

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

Re: Execution time of "show databases" increases when fewer databasesare visible

[Alexey Polyakov]

On 4/21/06, Nico Sabbi <[EMAIL PROTECTED]> wrote:

> Is this behaviour due to a bug or maybe my mysql tables are messed up?

I'm not sure if we can call it a bug. But spending 8 seconds (and
using 100% CPU) for a simple SHOW DATABASES query is a bit too much,
and should be fixed.

mysqld first reads directories name from its data directory, then
loops over all names and does the following check:

if (thd->master_access & (DB_ACLS | SHOW_DB_ACL) ||
acl_get(thd->host, thd->ip, thd->priv_user, file_name,0) ||
(grant_option && !check_grant_db(thd, file_name)))

If a query runs faster when user has access to more DBs, then the
problem most likely lies within check_grant_db function.

But it all needs more careful investigation.

--
Alexey Polyakov

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

Re: Urgent Fetch and updation in single step/query

[Barry]

WEBBEE . BIZ wrote:

Hi all,
Can we run a query like UPDATE table_name set abc='1'; and still fetch the
value from a column from table .
I mean Can we run select And update query in one statement. If yes how.

Pl. tell me for versions 4.x and 5.x
Pl. do help me.


Hmm should work with PROCEDURE in both versions.

--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

Re: Urgent Fetch and updation in single step/query

[WEBBEE . BIZ]

Hi all,
Can we run a query like UPDATE table_name set abc='1'; and still fetch the
value from a column from table .
I mean Can we run select And update query in one statement. If yes how.

Pl. tell me for versions 4.x and 5.x
Pl. do help me.
--
Regards
Abhishek Jain

On 4/20/06, abhishek jain <[EMAIL PROTECTED]> wrote:
>
> No i do not want an autoincrement key , as i want to be only one row in
> the table and it value gets incremented / updated and i to know whats its
> current value.
>  --
> Regards
> Abhishek Jain
>
>
> On 4/19/06, Michael Kruckenberg < [EMAIL PROTECTED]> wrote:
> >
> > Seems like what you need is an auto-increment key. Is that out of the
> > question?
> >
> > On Apr 18, 2006, at 1:34 AM, abhishek jain wrote:
> > > Dear Friends,
> > > I run several processes and they need to query the mysql 5.0.8
> > > database
> > > simultaneously .I have a config table which have the record id. I
> > > need to
> > > fetch that and increment that .What I feel that the same record id is
> > > fetched by different simultaneosly before i update .Can anyone help
> > > me in
> > > either:
> > > 1)telling me a single query which will fetch and incr. in the same
> > > query. so
> > > the problem of simultaneously queries are solved.
> > > 2)A system by which delaying the other queries are done, I use PHP .
> > > Expecting a quick reply.
> > > Thanks,
> > > Abhishek Jain
> >
> >
>

Re: Working out Square Footage with Feet and Inches

[Shaun]

""Nicolas Verhaeghe"" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]

""RedRed!com IT Department"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Jay Blanchard wrote:
>> [snip]
>> I read this as a SQL syntax question, not a math word problem.  As in
>>
>> SELECT ..., (some expression equaling sq ft) AS sqft... [/snip]
>>
>> Cool, then do it!
>>
>
>
> It would all depend on how he has his feet and inches saved in the
> table,
> and of course, the field names. If we can get that info then maybe we
> would be better able to post a solution.

Sorry Guys, I am just storing the X and Y, so 6 foot 11 inches would be
stored as 6.11



I hope the data type is a varchar or something like that.

So if I am right the "decimal" (actually it's pseudo-duodecimal) part is
stored as "00" to "11"?

I would use string functions to separate the pseudo-duodecimal parts, devide
it by 12 to make it a decimal, add to the feet parts, then use these values
to get a square footage in decimals.

Table dimensions:

ID X Y
1 6.11 5.04
2 6.02 6.00

select  ID,
cast((right(x, 2)  / 12 + left(x,length(x)-3)) as decimal) as decimalX,
cast((right(y, 2)  / 12 + left(y,length(y)-3)) as decimal) as decimalY,
cast((right(x, 2)  / 12 + left(x,length(x)-3)) as decimal) * cast((right(y,
2)  / 12 + left(y,length(y)-3)) as decimal) as decimalSQFT
from dimensions

Gives:

ID decimalX decimalY decimalSQFT
1 6.92 5.33 36.8836
2 6.17 6 37.02

Now if you want to convert the decimal part into square inches, you're on
your own!


Thanks for your reply,

I have stored the dimensions as decimal(4,2), does this make a difference?



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

RE: MySQLDump and Restore

[Arjan Hulshoff]

Or use mysqlimport...

Typ 'mysqlimport --help' for further assistance. There are more usefull
tools in the bin directory of your mysql installation.

HTH,
Arjan. 

-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 21, 2006 09:03 AM
To: Jim
Cc: mysql@lists.mysql.com
Subject: Re: MySQLDump and Restore

Hello Jim,

mysql -u root -p ROOTPASSWD < dump.sql

Thanks,
Abdul.


On Fri, 2006-04-21 at 17:02 +1000, Jim wrote:
> Hi All,
> 
>  
> 
> Am using mySQLDump to backup db as follows
> 
> mysqldump --databases OURDB -uroot -pROOTPASSWRD > dump.sql
> 
>  
> 
> How can I restore OURDB from dump.sql?
> 
>  
> 
> Thanks
> 
> Jim
> 
>  
> 
> 
> 


This email has been Scanned for Viruses!
  www.newbreak.com



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



-- 
The information contained in this communication and any attachments is 
confidential and may be privileged, and is for the sole use of the intended 
recipient(s). Any unauthorized review, use, disclosure or distribution is 
prohibited. If you are not the intended recipient, please notify the sender 
immediately by replying to this message and destroy all copies of this message 
and any attachments. ASML is neither liable for the proper and complete 
transmission of the information contained in this communication, nor for any 
delay in its receipt.

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

Question about MATCH Score

[Taco Fleur]

Hi all,
 
I was wondering if someone could tell me whether the following SQL would
have to perform two searches, 
One to get the score in the SELECT clause and one in the WHERE clause to
only return matches?
If it does perform two searches, would it be best to put WHERE score != 0 in
the WHERE clause instead?
 
SELECT N.noteIdentity
   , LEFT( N.note, 80 ) AS note
   , MATCH (N.note) AGAINST ('' IN BOOLEAN MODE ) AS score
   , CN.candidateIdentity
   , AN.addressIdentity
   , CN_.clientIdentity
   , CN__.contactIdentity
   , EN.entityIdentity
   , JN.jobIdentity
  FROM db_au_com_exclaimit.tbl_note N
  
  INNER JOINdb_au_com_exclaimit.tbl_user U
  ON  N.ownerIdentity = U.userIdentity
  
  LEFT OUTER JOIN  db_au_com_exclaimit.tbl_candidate_note CN
  ON  N.noteIdentity = CN.noteIdentity
  
  LEFT OUTER JOIN  db_au_com_exclaimit.tbl_address_note AN
  ON  N.noteIdentity = AN.noteIdentity
  
  LEFT OUTER JOIN  db_au_com_exclaimit.tbl_client_note CN_
  ON  N.noteIdentity = CN_.noteIdentity
  
  LEFT OUTER JOIN  db_au_com_exclaimit.tbl_contact_note CN__
  ON  N.noteIdentity = CN__.noteIdentity
  
  LEFT OUTER JOIN  db_au_com_exclaimit.tbl_entity_note EN
  ON  N.noteIdentity = EN.noteIdentity
  
  LEFT OUTER JOIN  db_au_com_exclaimit.tbl_job_note JN
  ON  N.noteIdentity = JN.noteIdentity
  
  WHERE MATCH (N.note) AGAINST ('' IN BOOLEAN
MODE )
  ORDER BY   score DESC;
 
Kind regards,
 

Taco Fleur 

Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox   http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994 .

*   

Web Design and Development 
*   

SMS Solutions, including developer API
*   

Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!

 

Execution time of "show databases" increases when fewer databasesare visible

[Nico Sabbi]

Hi,
I have a strange and annoying problem with Mysql 4.0.26 that I hope 
someone will help me to fix:


the more databases a user has the less time it takes to execute "show 
databases" and vice versa:


show databases;
+--+
5 rows in set (7.97 sec)

--- 



show databases;
++
74 rows in set (5.87 sec)

--- 



show databases;
++
141 rows in set (3.66 sec)

--- 



show databases;
++
210 rows in set (1.45 sec)


Is this behaviour due to a bug or maybe my mysql tables are messed up?
Here are some stats:


select count(*) from mysql.user;
+--+
| count(*) |
+--+
| 998  |
+--+
1 row in set (0.02 sec)

select count(*) from mysql.host;
+--+
| count(*) |
+--+
| 0|
+--+
1 row in set (0.03 sec)

select count(*) from mysql.db;
+--+
| count(*) |
+--+
| 1402 |
+--+
1 row in set (0.03 sec)


select count(*) from mysql.columns_priv;
+--+
| count(*) |
+--+
| 0|
+--+
1 row in set (0.03 sec)

mysql> select count(*) from mysql.tables_priv;
+--+
| count(*) |
+--+
| 145894   |
+--+
1 row in set (0.01 sec)


   Nico




--

Email.it, the professional e-mail, gratis per te: http://www.email.it/f



Sponsor:

Sei single e stai cercando l’amore? Entra subito in Meetic, iscriviti gratis, 
consulta i profili di milioni di single e chatta con loro

Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=4051&d=21-4

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

RE: fulltext wildcards

[Taco Fleur]

thank you.
 
Kind regards,
 

Taco Fleur 

Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox   http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994 .

*   

Web Design and Development 
*   

SMS Solutions, including developer API
*   

Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!

 

  _  

From: Gabriel PREDA [mailto:[EMAIL PROTECTED] 
Sent: Friday, 21 April 2006 5:35 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: fulltext wildcards


Try: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

-- 
Gabriel PREDA
Senior Web Developer 

Re: fulltext wildcards

[Gabriel PREDA]

Try: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

--
Gabriel PREDA
Senior Web Developer

fulltext wildcards

[Taco Fleur]

Hi all,
 
I am trying to find a list that shows what wildcards I can use in fulltext
searches, I searched the docs but no luck, does anyone happen to have a link
to a list?
 
thanks in advance.
 
Kind regards,
 

Taco Fleur 

Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox   http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994 .

*   

Web Design and Development 
*   

SMS Solutions, including developer API
*   

Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!

 

Re: mysql 5 vs. mysql 4: str_to_date

[Barry]

Vincente Aggrippino wrote:

The following query runs fine on my test server at home, running MySQL
5.0.15, but on my hosting provider's server, running MySQL 4.0.24, it
gets a syntax error.  Can anyone help me figure out why? ...

insert into product (name, url, cost, purchase_dt)
values('Whatchamacallit', 'http://www.whatchamacallit.com', 5,
str_to_date('2006-04-20', '%Y-%m-%d'))


Here's the error message :
#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 '('2006-04-20', '%Y-%m-%d'))' at line 2

Thank you,
Vince Aggrippino


He doesn't know str_to_date. That's it.

--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

mysql 5 vs. mysql 4: str_to_date

[Vincente Aggrippino]

The following query runs fine on my test server at home, running MySQL
5.0.15, but on my hosting provider's server, running MySQL 4.0.24, it
gets a syntax error.  Can anyone help me figure out why? ...

insert into product (name, url, cost, purchase_dt)
values('Whatchamacallit', 'http://www.whatchamacallit.com', 5,
str_to_date('2006-04-20', '%Y-%m-%d'))


Here's the error message :
#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 '('2006-04-20', '%Y-%m-%d'))' at line 2

Thank you,
Vince Aggrippino

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

Re: MySQLDump and Restore

[Mohammed Abdul Azeem]

Hello Jim,

mysql -u root -p ROOTPASSWD < dump.sql

Thanks,
Abdul.


On Fri, 2006-04-21 at 17:02 +1000, Jim wrote:
> Hi All,
> 
>  
> 
> Am using mySQLDump to backup db as follows
> 
> mysqldump --databases OURDB -uroot -pROOTPASSWRD > dump.sql
> 
>  
> 
> How can I restore OURDB from dump.sql?
> 
>  
> 
> Thanks
> 
> Jim
> 
>  
> 
> 
> 


This email has been Scanned for Viruses!
  www.newbreak.com



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

Re: WHERE doesn't work

[Barry]

Tom Lobato wrote:


   Hi!


   UPDATE command is not working for only the record that I want, but for
all in the table. I sees WHERE is not interpretated by Mysql server. At
least the result is identic to it. For example, If my php execute

   UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24'
WHERE id = '5'

on the server, I have all records in the table clientes updated for these
values. But, if I copy exactily this command from php output and execute in
the Mysql server directly, via some mysql client, it updates only record
whose id = '5'. PHP Version: 4.4.2, Mysql client API version: 3.23.49.
For windows servers or Linux.
   Some Hint?



   Thank you
   Tom



Tried: WHERE id = 5
?


--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

MySQLDump and Restore

[Jim]

Hi All,

 

Am using mySQLDump to backup db as follows

mysqldump --databases OURDB -uroot -pROOTPASSWRD > dump.sql

 

How can I restore OURDB from dump.sql?

 

Thanks

Jim