Re: LEFT JOIN changes order of results

[Scott Gifford]

Johan Höök [EMAIL PROTECTED] writes:

 Hi Scott,
 I think you've been lucky so far...
 As you're only ordering on listdate, which is the same
 for both homes in your example you might definitely get different
 results once you put in the limit.
 A basic thing about rdb's is that you must never make assumptions
 that it returns resultsets in the same order unless you specify what
 to order by, of course quite often you'll get it back in the same order
 but you must never bank on it.

Hi Johan,

I guess I have been lucky.

[...]

 I guess you somehow have to include the mls_num in your second query
 to ensure that you get same resultset.

I'm looking into adding mls_num into all queries to fix this problem,
but it looks likely to make performance much worse.  Here's MySQL's
plan for a typical query:

mysql EXPLAIN SELECT * 
 FROM faar_homes 
WHERE zip = 48503 
 ORDER BY price DESC 
LIMIT 10 \G
*** 1. row ***
   id: 1
  select_type: SIMPLE
table: faar_homes
 type: index
possible_keys: zip
  key: price
  key_len: 4
  ref: NULL
 rows: 5194
Extra: Using where
1 row in set (0.00 sec)

When I add in mls_num, it uses a filesort:

mysql EXPLAIN SELECT * 
 FROM faar_homes 
WHERE zip = 48503 
 ORDER BY price DESC, mls_num
LIMIT 10 \G
*** 1. row ***
   id: 1
  select_type: SIMPLE
table: faar_homes
 type: ALL
possible_keys: zip
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 5194
Extra: Using where; Using filesort
1 row in set (0.00 sec)

It seems that this fix will cause nearly all of my queries to use
filesort.

Any ideas for avoiding this?

Thanks!

--ScottG.

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

mysqldump : Character set '#33' is not a compiled character set

[Gu Lei]

Hi

It's my first time using mysqldump.
[EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases 
backup_test.sql
mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode:
2)
mysqldump: Character set '#33' is not a compiled character set and is
not specified in the '/usr/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$
[EMAIL PROTECTED] mysql]$ mysqldump -uroot
--character-sets-dir=/usr/local/mysql/share/mysql/charsets
--all-databases --default-character-set=utf8  backup_test.sql
mysqldump: Character set 'utf8' is not a compiled character set and is
not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$

What can I do?
Thanks

Regards,

Gu Lei
-- 

Illegal mix of collations for operation IN

[Dušan Pavlica]

Hello, 

I have problem which I don't understand.

if I send command (from Query Browser or from C++ Builder application) 
SELECT FileName FROM Files WHERE FileName = 'FILE1.TXT' OR FileName = 
'FILE2.TXT'
query executes without any problem but command
SELECT FileName FROM Files WHERE FileName IN ('FILE1.TXT', 'FILE2.TXT')
throws error illegal mix of collations for operation 'IN' 

I thought that IN is somehow by optimizer translated to ORs 
Could someone explain me why first query is OK and second not? Please.

I'm using WinXP SP2 and MySQL 4.1.9-nt-log 
databases and tables use CHARSET=latin2 COLLATE=latin2_czech_cs
I already read some articles about the topic in MySQL forum but they didn't 
helped me much.

Thanks in advance

Dusan Pavlica

Re: How to get the name of the last failed FK constraint

[mfatene]

Hi,
Status;

gives your own session id. To be combined to show innodb status (ksh or perl).

Mathias

Selon Frank Schröder [EMAIL PROTECTED]:

 [EMAIL PROTECTED] wrote:
  Frank Schröder [EMAIL PROTECTED] wrote on 05/31/2005 03:18:11 AM:
 
 
 Hello,
 
 I have an InnoDB table running on MySQL 4.1.11 with multiple FK
 constraints. I'm accessing it via JDBC from Java.
 
 When an FK constraint fails with error 1216 I need to know which of the
 constraints failed.
 
 SHOW INNODB STATUS returns the following output
 
 ...
 CONSTRAINT `u_registration_ibfk_1` FOREIGN KEY (`DEVICE_ID`)
 REFERENCES `u_device` (`DEVICE_ID`)
 ...
 
 Is there a way of getting to the name of the last failed FK constraint
 without using SHOW INNODB STATUS? What I need is the
 'u_registration_ibfk_1' from the above example.
 
 Any help is highly appreciated
 
 --
 Frank
 
 
 
  Have you looked at the results of SHOW INNODB STATUS; ?
 
  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine

 Yes, as you can see from my original post I'm actually trying to figure
 out how to do this  *without* SHOW INNODB STATUS as this reports the
 last FK failure for the entire engine and not just my session - at least
 that's how I interpret the documentation.

 The thing that's really a headscratcher for me is why its possible for
 me to set a name for a constraint if it isn't displayed in an error and
 I can't get to it. It's useless. I have a hard time believing that so I
 figure that I just haven't figured out how to get to it. I just didn't
 think that it was so hard.

 --
 Frank

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





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

Re: Select MAX(column1,column2)

[mfatene]

Hi Scott,
you may be int his case : http://bugs.php.net/bug.php?id=32882

can't reproduce it because of env lack

Mathias

Selon Scott Klarenbach [EMAIL PROTECTED]:

 You guys have been so helpful with this, I'm hoping that I can ask for
 one more favor...

 The reason I needed the greatest(max()) functionality, was to run the
 following query...I can make it work from the command line, but
 everytime I run it from PHP, the MySQL service shuts down, and needs
 to be restarted manually.

 I'm calling a stored procedure 'selectAllRequests' which is the following
 query:

 SELECT
 r.id,
 r.partNumber,
 r.OtherFields,
 functionGetHighestValue(r.partNumber, r.qty) AS 'highestValue'
 FROM request r
 WHERE r.deleted=0
 ORDER BY highestValue DESC, r.dateSent DESC;

 the function I'm calling is as follows:
 CREATE FUNCTION `functionGetHighestValue`(`MPNParam` varchar(60),
 `qtyParam` DOUBLE(10,4)) RETURNS DOUBLE(10,4)
 BEGIN
   DECLARE dHighest DOUBLE(10,4) DEFAULT 0;

   SELECT
   GREATEST(MAX(i.distySellCost), MAX(i.originalCost), 
 MAX(i.unitCost),
 MAX(i.unitSellCost))*qtyParam
   FROM inventory i
   WHERE i.MPN = 'MPNParam' AND i.status=1 INTO dHighest;

   RETURN dHighest;
 END|

 As I say, I can call this procedure from the command line and it
 works, but calling it from PHP results in the MySQL service crashing
 on my Windows 2003 server.  I'm using PHP 5.0.4 and MySQL 5.0.4.  Any
 help is appreciated.  Thanks.



 On 5/27/05, Scott Klarenbach [EMAIL PROTECTED] wrote:
  select greatest(max(col1), max(col2), max(col3), max(col4)) from table
  works the best, as Keith pointed toward initially.  Remember, I forgot
  to mention that I wanted the greatest for the whole table, not just
  for each rowso, 10, 12, 8 is not what I wanted...out of
 
  10  2  3
  5  4  8
  1 12  7
 
  i want 12.
 
  thanks again.
 
  On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
   I forgot :
  
   10, 12, 8 is not a row !!!
  
   Mathias
  
   Selon [EMAIL PROTECTED]:
  
Hi Keith,
yes concat makes an associative lost for max.
But if we split the desc on all the columns, it works :
   
mysql select * from numbers
- order by a desc,b desc,c desc
- limit 1;
+--+--+--+
| a| b| c|
+--+--+--+
|   10 |2 |3 |
+--+--+--+
1 row in set (0.00 sec)
   
it's a real desc ordering.
   
Thanks
   
Mathias
   
   
Selon Keith Ivey [EMAIL PROTECTED]:
   
 [EMAIL PROTECTED] wrote:
  Hi all,
  what is max ? it's the first row when we sort data in descending
 order.
 
  so
 
  select col1,col2,col3,col4 ... from table
  order by concat(col1,col2,col3,col4 ... ) desc
  LIMIt 1;
 
  should be silar to what is needed. I say should :o)

 That would only work if the greatest values for col2, col3, col4,
 etc., all
 occurred in the same row with the greatest value for col1, and if all
 the
 values
 for col1 had the same number of digits (and the same for col2, col3,
 etc.).

 Consider this table:

 10  2  3
  5  4  8
  1 12  7

 Your query would give 5, 4, 8 (because 548 as a string is greater
 than
 1023
 or 1127), but he wants 10, 12, 8.

 --
 Keith Ivey [EMAIL PROTECTED]
 Smokefree DC
 http://www.smokefreedc.org
 Washington, DC

   
   
   
  
  
  
 




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

Re: Design of a Client-side MySQL Java Load Balancer

[mfatene]

Hi,
i think that client load-balacer are more Dispatchers than real load balancer.

load balancing in the database side takes care to number of connections, but
also node load. So thisis more real. But this issue is difficult.

even for oracle with 9iRAC and 10gRAC, load balancing is not completely
controled.

you speak abot load balancing and introduce also the failover notion, which
isnot a load balancing concept. Fail over is difficult because controling it
implies that every node must have the image before of every transaction.

With cache fusion, oracle RAC gives a solution, but assumes failover only for
select statements. All DML statements are lost if a node is lost.

The mysql concept of clustering is different from the oracle one. Will oracle
shares all in memory, mysql clusters share nothing. I'm studing this now, so
can't give you more details, but i think that in future versions, all RDMBS
constructors will control the two concepts in the database side.

Mathias


Selon Kevin Burton [EMAIL PROTECTED]:

 I'd love to get some feedback here:

  MySQL currently falls down by not providing a solution to transparent
  MySQL load
  balancing. There are some hardware solutions but these are expensive and
  difficult to configure. Also none of them provide any information
  about the
  current state of your MySQL configuration. For example they can't handle
  transparent query failover if a MySQL box fails. They also can't
  disconnect and
  reconnect to another host if the load grows too high.
 
  To that end I think it makes a lot of sense to have a MySQL
  client-side load
  balancer.
 
  This area is difficult to implement. There are a log of design issues.
  Also the
  issues WRT distributed connection management start to make the problem
  difficult.
 
  The other day I had a bit of an epiphany on this topic.
 
 

 http://peerfear.typepad.com/blog/2005/06/design_of_a_cli.html

 --


 Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com.
 See irc.freenode.net #rojo if you want to chat.

 Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

Kevin A. Burton, Location - San Francisco, CA
   AIM/YIM - sfburtonator,  Web - http://peerfear.org/
 GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412


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





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

RE: Mysqldump

[Gordon]

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

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

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

Thanks in advance!

Cheers

Manoj


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


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

mysql: varchar and case sensitive

[Jerry Swanson]

Column type is username varchar(100).

When I do select from database:
select * from user where username='John';  //returns one row
select * from user where username='john'; //returns one row

The records in the database has username 'John'.

Why it  isn't case sensitive? 'John and 'john' .. are two different strings..?

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

Re: Find the biggest blobs

[mfatene]

Hi,
since reading blobs is not a simple action (heavy), you must store the size of
every file in the table's structure.

if you write with php, somthing like that filesize($binFile) gives you the
column value for every insert

When done, a simple order by filesize gives you what you want before beginning
the blob read :
SELECT bin_data, filetype, filename, filesize FROM tbl_Files
order by filesize desc;

look at http://www.onlamp.com/pub/a/php/2000/09/15/php_mysql.html?page=1 to have
a php blob manipulation sample.

other languages like perl, asp, ado, ... do the same.

hope that helps

Mathias


Selon Roland Carlsson [EMAIL PROTECTED]:

 Hi!

 I've need to find the largest blobs in a table but I seem not to be able
 to figure out what it is. Could anyone please help me with this?

 Regards
 Roland

 --
 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: varchar and case sensitive

[Michael Stassen]

Jerry Swanson wrote:


Column type is username varchar(100).

When I do select from database:
select * from user where username='John';  //returns one row
select * from user where username='john'; //returns one row

The records in the database has username 'John'.

Why it  isn't case sensitive? 'John and 'john' .. are two different strings..?


Because it isn't.  String comparisons are case insensitive by default in 
mysql http://dev.mysql.com/doc/mysql/en/case-sensitivity.html.


If you want a case-sensitive comparison, you need to say so with BINARY:

  SELECT * FROM user WHERE username = BINARY 'John'.

If you want *ALL* comparisons of the username column to be case 
sensitive, then you should declare it as a BINARY type.


  username VARCHAR(100) BINARY

or

  username VARBINARY(100)

See the manual for details:
http://dev.mysql.com/doc/mysql/en/char.html
http://dev.mysql.com/doc/mysql/en/binary-varbinary.html

Michael

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

Re: mysql: varchar and case sensitive

[Grover M. Campos Ancajima]

it depends of the collation of table, for example: if your table has
collation: utf8_english_ci, ci means case insensitive.

El jue, 02-06-2005 a las 09:43 -0400, Jerry Swanson escribió:

 Column type is username varchar(100).
 
 When I do select from database:
   select * from user where username='John';  //returns one row
   select * from user where username='john'; //returns one row
 
 The records in the database has username 'John'.
 
 Why it  isn't case sensitive? 'John and 'john' .. are two different 
 strings..?
 

---
Grover Manuel Campos Ancajima
Ingeniero de Desarrollo
Dictuc - Pontificia Universidad Católica de Chile
http://www.dictuc.cl 
Telf. Of. +56(2)351
Telf. Cel. +5691415436

MySQL - Tiger - install on separate partition

[Kevin Victor]

Is there a recommended way for installing MySQL on Mac OS X(Tiger)? Is
it a good idea to have MySQL installed on a separate partition
considering future upgrades and scalability? Currently i have one 250
GB hard drive and I am expecting MySQL databases not to exceed 100 GB.
Just to try, i created two partition and tried to install MySQL on a
separate partition, the MySQL binary installer doesn't let me to
install on the separate partition, instead it lets me to install only
on the OS X system partition. If this is the case should i just move
the data folder to the new partition and create a symlink? Is this
worth doing or just leaving it on single partition better choice?

Thanks for your help
Kevin

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

RE: Find the biggest blobs

[Artem Koltsov]

Did you try:

select blob_field
from blob_table
order by length(blob_field) DESC limit 1

Regards,
Artem

 -Original Message-
 From: Roland Carlsson [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, June 01, 2005 10:02 AM
 To: mysql@lists.mysql.com
 Subject: Find the biggest blobs
 
 
 Hi!
 
 I've need to find the largest blobs in a table but I seem not 
 to be able 
 to figure out what it is. Could anyone please help me with this?
 
 Regards
 Roland
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
Attention:
Any views expressed in this message are those of the individual sender, except 
where the message states otherwise and the sender is authorized to state them 
to be the views of any such entity. The information contained in this message 
and or attachments is intended only for the person or entity to which it is 
addressed and may contain confidential and/or privileged material.  If you 
received this in error, please contact the sender and delete the material from 
any system and destroy any copies.

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

Re: mysqldump : Character set '#33' is not a compiled character set

[mfatene]

hi,
look at :
usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf

in

http://dev.mysql.com/doc/mysql/en/mysqldump.html

add the port, protocol,password

mathias

Selon Gu Lei [EMAIL PROTECTED]:

 Hi

 It's my first time using mysqldump.
 [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases  backup_test.sql
 mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
 mysqldump: Character set '#33' is not a compiled character set and is
 not specified in the '/usr/share/mysql/charsets/Index' file
 [EMAIL PROTECTED] mysql]$
 [EMAIL PROTECTED] mysql]$ mysqldump -uroot
 --character-sets-dir=/usr/local/mysql/share/mysql/charsets
 --all-databases --default-character-set=utf8  backup_test.sql
 mysqldump: Character set 'utf8' is not a compiled character set and is
 not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file
 [EMAIL PROTECTED] mysql]$

 What can I do?
 Thanks

 Regards,

 Gu Lei
 --




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

Re: unable to start mysqld_multi

[Gleb Paharenko]

Hello.



Check the --no-log option for mysqld_multi to see it's messages on your

console. What is in servers' error logs?







PRASHANT N [EMAIL PROTECTED] wrote:

 hi

 

 as per the online manual, i have configured mysql and able to run the server 
 as mysqld_safe. but if i want to run mysqld_multi with the following 
 configuration it doesnt work... the config file is like

 [mysqld_multi]

 mysqld = /usr/local/mysql/bin/mysqld_safe

 mysqladmin = /usr/local/mysql/bin/mysqladmin

 user   = mutli_admin

 password   = admin_multi

 

 [mysqld2]

 socket = /tmp/mysql.sock2

 port   = 3307

 pid-file   = /usr/local/mysql/var2/hostname.pid2

 datadir= /usr/local/mysql/var2

 user   = amitg

 

 [mysqld3]

 socket = /tmp/mysql.sock3

 port   = 3308

 pid-file   = /usr/local/mysql/var3/hostname.pid3

 datadir= /usr/local/mysql/var3

 user   = shann

 i am starting the server with the following command 

 /usr/local/mysql/bin/mysqld_multi start

 this command doesnt throw any errors as such and the report i am getting my 
 executing the command

 /usr/local/mysql/bin/mysqld_multi report is

 Reporting MySQL servers

 MySQL server from group: mysqld2 is not running

 MySQL server from group: mysqld3 is not running

 what am i doing wrong. please guide me

 regards

 shann

 

 

 ___ 

 Have your own email and web address for life.

 

 http://www.homemaster.net - Homemaster. Come Together. Online.

 

 



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




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

Re: mysqldump : Character set '#33' is not a compiled character set

[Gleb Paharenko]

Hello.



What version of MySQL do you use? Complete support for utf8

exists only in 4.1.xx and 5.x versions.





Gu Lei [EMAIL PROTECTED] wrote:

 [-- text/plain, encoding 7bit, charset: us-ascii, 23 lines --]

 

 Hi

 

 It's my first time using mysqldump.

 [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases  backup_test.sql

 mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)

 mysqldump: Character set '#33' is not a compiled character set and is

 not specified in the '/usr/share/mysql/charsets/Index' file

 [EMAIL PROTECTED] mysql]$

 [EMAIL PROTECTED] mysql]$ mysqldump -uroot

 --character-sets-dir=/usr/local/mysql/share/mysql/charsets

 --all-databases --default-character-set=utf8  backup_test.sql

 mysqldump: Character set 'utf8' is not a compiled character set and is

 not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file

 [EMAIL PROTECTED] mysql]$

 

 What can I do?

 Thanks

 

 Regards,

 

 Gu Lei



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




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

Re: Illegal mix of collations for operation IN

[Gleb Paharenko]

Hello.



Usually debugging of such kind of problems starts with examination of

the output of:

  show variables like '%char%';

  show variables like '%colla%';



Send the output of:

  show create table your_table;







Hello, 



I have problem which I don't understand.



if I send command (from Query Browser or from C++ Builder application) 

SELECT FileName FROM Files WHERE FileName = 'FILE1.TXT' OR FileName =

'FILE2.TXT'

query executes without any problem but command

SELECT FileName FROM Files WHERE FileName IN ('FILE1.TXT', 'FILE2.TXT')

throws error illegal mix of collations for operation 'IN' 



I thought that IN is somehow by optimizer translated to ORs 

Could someone explain me why first query is OK and second not? Please.



I'm using WinXP SP2 and MySQL 4.1.9-nt-log 

databases and tables use CHARSET=latin2 COLLATE=latin2_czech_cs

I already read some articles about the topic in MySQL forum but they

didn't helped me

much.



Thanks in advance



Dusan Pavlica

Du$an Pavlica [EMAIL PROTECTED] wrote:



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




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

Re: server and my client in different time zone

[Gleb Paharenko]

Hello.



This link might be helpful:

  http://dev.mysql.com/doc/mysql/en/time-zone-support.html





Scott Haneda [EMAIL PROTECTED] wrote:

 Is there a way to tell NOW() in mysql to be based on a time in the future?

 Bascially, I am in PST, and they are in EST, I have a ton of reports that

 show the time and date, and they want them in EST time, not the time the

 server is in.

 

 it is trivial to modify the display output of the time, but there are

 reports and other things that really need this taken into consideration, if

 there is a way to tell a certain single database to oprtate 3 hours ahead, I

 am pretty happy.



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




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

Re: mysql shutsdown immediately after password input

[Gleb Paharenko]

Hello.



See:

  http://dev.mysql.com/doc/mysql/en/gone-away.html







Michael Bujokas [EMAIL PROTECTED] wrote:

 I have never got mysql to start because each time I input my password, the 

 window closes (I am using Windows XP). The error is something like: ERROR 

 2013: Lost connection to mysql server during query. How do I resolve this?

 

 -M.B.

 

 

 



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




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

Re: Replication problem

[Gleb Paharenko]

Hello.



I suggest you to check what query was written to the master binary log

and then report a bug. Please include all information you've found in the 
report. 









Weicheng Pan [EMAIL PROTECTED] wrote:

 Dear Gleb:

  The problem has occured again, and I grab some output.

 This box run mysql 4.1.11 on FreeBSD 5.4 AMD64, with 8GB RAM

 

 In this case,

 The original query is INSERT INTO LOW_PRIORTY INTO `statistics`.`search` 

 (`userid`, `id`, `func`) VALUES('lty0105', '8', 2)

 The strange query is 'INSERT LOW_PRIORITY INTO `statistics`.`search` ( 

 `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p)

 Slave server got a strange character 'p' after character '2'

 

 

 Slave:

 mysql show slave status\G;

 *** 1. row ***

 Slave_IO_State: Waiting for master to send event

Master_Host: db0

Master_User: repl

Master_Port: 3306

  Connect_Retry: 5

Master_Log_File: db0-bin.56

Read_Master_Log_Pos: 755005566

 Relay_Log_File: db10-relay-bin.60

  Relay_Log_Pos: 572461705

  Relay_Master_Log_File: db0-bin.56

   Slave_IO_Running: Yes

  Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: blog,album_database

 Replicate_Do_Table:

 Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

 Replicate_Wild_Ignore_Table: album_database.%,blog.%

 Last_Errno: 1054

 Last_Error: Error 'Unknown column '2p' in 'field list'' on 

 query. Default database: ''. Query: 'INSERT LOW_PRIORITY INTO 

 `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 

 2p)'

   Skip_Counter: 0

Exec_Master_Log_Pos: 692584947

Relay_Log_Space: 634882324

Until_Condition: None

 Until_Log_File:

  Until_Log_Pos: 0

 Master_SSL_Allowed: No

 Master_SSL_CA_File:

 Master_SSL_CA_Path:

Master_SSL_Cert:

  Master_SSL_Cipher:

 Master_SSL_Key:

  Seconds_Behind_Master: NULL

 1 row in set (0.00 sec)

 

 Master:

 mysql show master status\G;

 *** 1. row ***

File: db0-bin.56

Position: 761127837

Binlog_Do_DB:

 Binlog_Ignore_DB:

 1 row in set (0.00 sec)

 

 Finally I dump the slave log:

 [EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog  db10-relay-bin.60 | grep 
 ( 

 'lty0105', '8', 2p)

 INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) 

 VALUES ( 'lty0105', '8', 2p);

 [EMAIL PROTECTED] [ /home/mysql ]

 

 With best regards,

 Weicheng.

 

 - Original Message ---

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




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

Re: table full on mysql-cluster

[Gleb Paharenko]

Hello.



There are some tips at:

  http://dev.mysql.com/doc/mysql/en/mysql-cluster-faq.html



See also:

  http://dev.mysql.com/doc/mysql/en/mysql-cluster-db-definition.html







We have the following problem.



Cluster means table 'TABLENAME' is full 



We have 11076890 rows in this table. 

Where is the limit defined ?

Disk are Not full. RAM not full too.

Table engine is NDBCLUSTER.

Can anybody help ?













Tilmann Grossmann [EMAIL PROTECTED] wrote:



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




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

Re: How to get the name of the last failed FK constraint

[Frank Schröder]

[EMAIL PROTECTED] wrote:

Hi,
Status;

gives your own session id. To be combined to show innodb status (ksh or perl).

Mathias


I'm not sure I understand.

Are you saying that SHOW INNODB STATUS shows only the information of the 
current session or that I can supply a session id to the call?


--
Frank

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

Re: Import dump (4.0 4.1) and collation problem

[[EMAIL PROTECTED]]

Hi,

I don't understand very well, what should I do...

Does anyone has a step-by-step instructions on how to import a ver. 4.0 DB dump
into a 4.1 version ?

Thanks for any help

Roberto Jobet




 look at automatic character set conversion
 http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html

 mathias

 Selon [EMAIL PROTECTED]:

  Salut,
  i don't see what doesn't work ! Where characters are replaced by ?, since 
  you
  say that insert,select and import work fine ? is it in the export ?
 
  see http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html for migration.
 
 
  Mathias
 
  Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
 
   Voilà,
  
   Everything works fine: insert, select and importing. I have always the 
   same
   text:
  
   'Ceci est un test en Français'
  
   So what could be the problem ? and how to recover foreign characters ?
  
   Thanks
  
   Roberto Jobet
  
  
i mean :
if you launch :
into into table (toto) values ('Ceci est un test en Français');
then :
select toto from table;
   
can you read it correctly ? And if the same row is imported, is there 
any
difference ?
   
A bientôt
Mathias
   
   
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
   
 Salut,

 je vois que tu es français, j'aimerai bien continuer en français mais
  il
   vaut
 mieux en anglais si jamais il y avait quelqu'un avec le même problème
  ;-)


 So what I'm trying to achieve is to have UTF-8 databases in order to
   support
 multilingual content.

 But first I have to understand how to recover foreign characters in my
   DB.

 What do you mean by: try some inserts to see if it is an import
  problem
or
 just the client display ?

 Merci d'avance

 Roberto Jobet


  hi,
  your config supports european characters :
 
  mysql select * from tst;
  +--+---+
  | a| txt   |
  +--+---+
  |1 | Ceci est un test en Français  |
  |1 | se facilitará el conocimiento de la evolución del |
  +--+---+
  2 rows in set (0.00 sec)
 
  mysql show variables like '%coll%';
  +--+---+
  | Variable_name| Value |
  +--+---+
  | collation_connection | latin1_swedish_ci |
  | collation_database   | latin1_swedish_ci |
  | collation_server | latin1_swedish_ci |
  +--+---+
  3 rows in set (0.00 sec)
 
 
  have you tried some inserts to see if it is an import problem, or
  just
   the
  client display ?
 
  Mathias
 
  Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
 
   Hi,
  
   I recently upgraded from 4.0 to 4.1 because of the multilingual
   support.
  
   But importing the DB dump into the new 4.1 version all foreign
   characters
   (french, spanish, portoguese) have been replaced by ? character...
  
   I've been trying to understand the new collation feature but i
   seems
 quite
   difficult to setup.
  
   Anybody has already faced this problem ?
  
   Here's my current configuration:
  
   1) my.cnf (only the mysqld section)
  
   [mysqld]
   user= mysql
   pid-file= /var/run/mysqld/mysqld.pid
   socket  = /var/run/mysqld/mysqld.sock
   port= 3306
   # Both location gets rotated by the cronjob.
   #log= /var/log/mysql.log
   log = /var/log/mysql/mysql.log
   basedir = /usr
   datadir = /db
   tmpdir  = /tmp
   language= /usr/share/mysql/english
  
   2) Output show variables like '%char%';
  
   character_set_client utf8
   character_set_connection utf8
   character_set_database latin1
   character_set_results utf8
   character_set_server latin1
   character_set_system utf8
   character_sets_dir /usr/share/mysql/charsets/
  
   3) Out put show variables like '%coll%';
  
   collation_connection utf8_general_ci
   collation_database latin1_swedish_ci
   collation_server latin1_swedish_ci
  
   Thanks for any help
  
   Regards
  
   Roberto Jobet




 
 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
 Scaricalo su INTERNET GRATIS 6X http://www.libero.it



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

Problems importing dump 4.0 4.1

[[EMAIL PROTECTED]]

Hi,

I'm trying to import a db dump made on ver. 4.0, on a new 4.1 version.

All accented characters (french, spanish) are replaced by a '?'

What it's the right way to import it ?


Thanks in advance

Roberto Jobet





Navighi a 4 MEGA e i primi 3 mesi sono GRATIS. 
Scegli Libero Adsl Flat senza limiti su http://www.libero.it



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

Re: Mysqldump

[mfatene]

Hi,
try stop server, cold copy to dev server, start on dev.

mathias
Selon ManojW [EMAIL PROTECTED]:

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

 Thanks in advance!

 Cheers

 Manoj


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





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

Re: Mysqldump

[Dan H Orlic]

you might also try --opt as an argument to mysqldump such as:


mysqldump -u root --opt --add-drop-table db_name  output.dmp

On Thu, 2005-06-02 at 08:46 +0200, [EMAIL PROTECTED] wrote:
 Hi,
 try stop server, cold copy to dev server, start on dev.
 
 mathias
 Selon ManojW [EMAIL PROTECTED]:
 
  Greetings,
  I took a dump of (pretty chunk) database, the output is close to 45G. I
  am trying to reload this dump file onto a development  server but it's
  taking long time to load the database. Is their a faster way to load the
  data in? I am using plain and simple  mysql  dump.sql syntax on a Mysql
  4.0.24 server.
 
  Thanks in advance!
 
  Cheers
 
  Manoj
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 
 


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

AUTO_INC lock

[Dobromir Velev]

Hi,
Can somebody tell me what the AUOT_INC lock is doing. When my db is 
experiencing high load I see a lot of these in the INNODB status monitor.

I'm doing a lot of inserts, and from time to time I need to generate some 
reports that are based on a large amount of data, and then the MySQL server 
starts locking the inserts. The strange thing it is locking even inserts to 
tables that are not used in the report.


Here is some data from the INNODB monitor

--
---TRANSACTION 2 559663507, ACTIVE 7 sec, process no 27751, OS thread id 
120930675 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 25414071, query id 168565699 xx.xx.xx.xx user update
INSERT INTO slogs VALUES 
(NULL,29837,'OK','0.00','100.97','102.12','103.16','N',Now(),0.131011*1000)
--- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `db_name/slogs` trx id 2 559663507 lock mode AUTO-INC waiting

-- 
Dobromir Velev

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

Re: Replication problem

[Weicheng Pan]

Dear Gleb:

   I forgot to give the master's bin log dump data,
The data writen in master log is correct:

[EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db0-bin.56 | grep ( 'lty0105', 
'8', 2p)
INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) 
VALUES ( 'lty0105', '8', 2 );


And the slave log:
[EMAIL PROTECTED]  [ /home/mysql ] mysqlbinlog  db10-relay-bin.60 | grep ( 
'lty0105', '8', 2p)
INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) 
VALUES ( 'lty0105', '8', 2p);


I took your suggestion and have sent a report.
And thank you very much.

With best regards,
Weicheng Pan
Jun 3, 2005.

- Original Message - 
From: Gleb Paharenko [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Thursday, June 02, 2005 4:51 PM
Subject: Re: Replication problem



Hello.

I suggest you to check what query was written to the master binary log
and then report a bug. Please include all information you've found in the 
report.








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

Need help in basic query

[Anoop kumar V]

Hi mysql-ers,

I need help in a basic query:

I have this table:

select * from isr2_aud_log where 
id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
--and name_rec_type = 'Assignment'
order by id_secr_rqst, dt_aud_rec
 
++-+-+
| id_secr_rqst | dt_aud_rec | name_rec_type |
++-+-+
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
Submission |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
Exception Requested |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
Exception Resource |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 | 
Director Approval |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 | 
Assignment |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 | 
Risk Assessment |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 | 
Assignment |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 | 
SERB Approval |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | 
Assignment |
++-+-+

and i am using this query:

SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest,
t1.dt_aud_recAS date1
FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = 
t2.id_secr_rqst 
AND t1.name_rec_type = 'Assignment' AND 
t1.id_secr_rqst IN (
'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
GROUP BY t2.id_secr_rqst HAVING date1 = latest

What I expected to get is the id_secr_rqst which has the last name_rec_type 
= 'Assignment'
In this case there is only one id_secr_rqst and it has the last 
name_rec_type as 'Assignment'. But I do not seem to get consistent results.
As I am using an older version of mysql I do not have the liberty to use 
subqueries and will have to do everything using joins only. 

The problem I am facing is that this query only sometimes returns rows and 
most of the time I get an empty result set. This table does not have any 
primary keys.

Can somebody please point out what is the mistake I am doing - I think it 
just needs a tweak here and there (I hope..)

Thanks,
Anoop

-- 
Thanks and best regards,
Anoop

View

[Jerry Swanson]

Does Mysql 4 supports views?

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

Re: Need help in basic query

[Anoop kumar V]

OK - I have found the cause of the inconsistency - 

Whenever I have more than one record which has name_rec_type as 'Assignment' 
I do not get any results (I get an empty result)
But if I have only one Assignment record then it returns the correct row.

Question is: How can I overcome this - I just need the id_secr_rqst if and 
only if it has the latest name_rec_type as 'Assignment' - I do not care what 
the earlier records contained.

help please,
Anoop

On 6/2/05, Anoop kumar V [EMAIL PROTECTED] wrote:
 
 Hi mysql-ers,
 
 I need help in a basic query:
 
 I have this table:
 
 select * from isr2_aud_log where 
 id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
 --and name_rec_type = 'Assignment'
 order by id_secr_rqst, dt_aud_rec
  
 
 ++-+-+
 | id_secr_rqst | dt_aud_rec | name_rec_type |
 
 ++-+-+
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
 Submission |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
 Exception Requested |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
 Exception Resource |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 | 
 Director Approval |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 | 
 Assignment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 | 
 Risk Assessment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 | 
 Assignment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 | 
 SERB Approval |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | 
 Assignment |
 
 ++-+-+
 
 and i am using this query:
 
 SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest, 
 t1.dt_aud_rec AS date1 
 FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = 
 t2.id_secr_rqst 
 AND t1.name_rec_type = 'Assignment' AND 
 t1.id_secr_rqst IN (
 'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
 GROUP BY t2.id_secr_rqst HAVING date1 = latest
 
 What I expected to get is the id_secr_rqst which has the last 
 name_rec_type = 'Assignment'
 In this case there is only one id_secr_rqst and it has the last 
 name_rec_type as 'Assignment'. But I do not seem to get consistent results.
 As I am using an older version of mysql I do not have the liberty to use 
 subqueries and will have to do everything using joins only. 
 
 The problem I am facing is that this query only sometimes returns rows and 
 most of the time I get an empty result set. This table does not have any 
 primary keys.
 
 Can somebody please point out what is the mistake I am doing - I think it 
 just needs a tweak here and there (I hope..)
 
 Thanks,
 Anoop
 
 -- 
 Thanks and best regards,
 Anoop 




-- 
Thanks and best regards,
Anoop

SELECT problem

[René Fournier]

I'm having a really hard time selecting rows from a table in one SELECT 
statement. I can do it in two SELECTS, but it seems I should be able to 
do it in one.


TRIPS

id  dateperson_id   cost
---
1   2005-01-01  2   500
2   2005-01-05  1   400
3   2005-01-12  4   350
4   2005-01-15  3   175
5   2005-01-17  2   385
6   2005-01-25  2   200
7   2005-02-03  3   600
8   2005-02-08  1   580
9   2005-02-20  4   320

PERSONS

id  name
-
1   john
2   jane
3   mike
4   mary
5   henry


Okay, I want to select from Trips the most recent trip for each person. 
As you can see, some of the Persons have travelled more than once, but 
I only want the last trip each one made. Also, not everyone in Persons 
has made a trip (Henry).


Here's the output I'm looking for:

2005-02-20  4   320 mary
2005-02-08  1   580 john
2005-02-03  3   600 mike
2005-01-25  2   200 jane


I've written and re-written my SELECT queries  numerous times, but 
can't seem to retrieve just one, most-recent trip/person. Any ideas?


...Rene


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

Re: View

[Andreas Ahlenstorf]

Am 02.06.2005 um 20:31 schrieb Jerry Swanson:


Does Mysql 4 supports views?


No.

Regards,
A.


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

RE: View

[Bartis, Robert M (Bob)]

I believe 5.0 does.

-Original Message-
From: Andreas Ahlenstorf [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 02, 2005 2:55 PM
To: Jerry Swanson
Cc: mysql@lists.mysql.com
Subject: Re: View



Am 02.06.2005 um 20:31 schrieb Jerry Swanson:

 Does Mysql 4 supports views?

No.

Regards,
A.


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

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

Re: View

[SGreen]

Jerry Swanson [EMAIL PROTECTED] wrote on 06/02/2005 02:31:06 PM:

 Does Mysql 4 supports views?
 

There were at least two other ways you could have found this information:
RTFM: http://dev.mysql.com/doc/mysql/en/ansi-diff-views.html
Search this list's archives: http://lists.mysql.com/mysql

This is usually a very friendly list but I suspect you are going to get 
some rather short answers to your question.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: SELECT problem

[SGreen]

René Fournier [EMAIL PROTECTED] wrote on 06/02/2005 02:53:51 PM:

 I'm having a really hard time selecting rows from a table in one SELECT 
 statement. I can do it in two SELECTS, but it seems I should be able to 
 do it in one.
 
 TRIPS
 
 id  date person_id  cost
 ---
 1  2005-01-01   2 500
 2  2005-01-05   1 400
 3  2005-01-12   4 350
 4  2005-01-15   3 175
 5  2005-01-17   2 385
 6  2005-01-25   2 200
 7  2005-02-03   3 600
 8  2005-02-08   1 580
 9  2005-02-20   4 320
 
 PERSONS
 
 id  name
 -
 1  john
 2  jane
 3  mike
 4  mary
 5  henry
 
 
 Okay, I want to select from Trips the most recent trip for each person. 
 As you can see, some of the Persons have travelled more than once, but 
 I only want the last trip each one made. Also, not everyone in Persons 
 has made a trip (Henry).
 
 Here's the output I'm looking for:
 
   2005-02-20   4 320  mary
   2005-02-08   1 580  john
   2005-02-03   3 600  mike
   2005-01-25   2 200  jane
 
 
 I've written and re-written my SELECT queries  numerous times, but 
 can't seem to retrieve just one, most-recent trip/person. Any ideas?
 
 ...Rene
 

This is a VERY FAQ. It is a variant on the Groupwize maximum problem well 
documented here: 
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html

Basically you need to determine the max(trips.date) for each person_id 
then use that list (in combination with the person table) to create the 
report you wanted in the first place.  The article shows 3 ways to make it 
happen: save your list into a temporar table, generate your list as the 
result of a subquery, or use the max-concat hack.

If you prefer, the same article is also available in French, German, 
Japanese, Portuguese, and Russian. Just click on the appropriate link to 
the side. 

If you have tried this and still can't make it work, please come back with 
your query and I am sure someone will be very happy to help.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: LEFT JOIN changes order of results

[mfatene]

hi,
mls_num is not in a key, have you tried index creation on (zip,price
desc,mls_num) ?

mathias


Selon Scott Gifford [EMAIL PROTECTED]:

 Johan Höök [EMAIL PROTECTED] writes:

  Hi Scott,
  I think you've been lucky so far...
  As you're only ordering on listdate, which is the same
  for both homes in your example you might definitely get different
  results once you put in the limit.
  A basic thing about rdb's is that you must never make assumptions
  that it returns resultsets in the same order unless you specify what
  to order by, of course quite often you'll get it back in the same order
  but you must never bank on it.

 Hi Johan,

 I guess I have been lucky.

 [...]

  I guess you somehow have to include the mls_num in your second query
  to ensure that you get same resultset.

 I'm looking into adding mls_num into all queries to fix this problem,
 but it looks likely to make performance much worse.  Here's MySQL's
 plan for a typical query:

 mysql EXPLAIN SELECT *
  FROM faar_homes
 WHERE zip = 48503
  ORDER BY price DESC
 LIMIT 10 \G
 *** 1. row ***
id: 1
   select_type: SIMPLE
 table: faar_homes
  type: index
 possible_keys: zip
   key: price
   key_len: 4
   ref: NULL
  rows: 5194
 Extra: Using where
 1 row in set (0.00 sec)

 When I add in mls_num, it uses a filesort:

 mysql EXPLAIN SELECT *
  FROM faar_homes
 WHERE zip = 48503
  ORDER BY price DESC, mls_num
 LIMIT 10 \G
 *** 1. row ***
id: 1
   select_type: SIMPLE
 table: faar_homes
  type: ALL
 possible_keys: zip
   key: NULL
   key_len: NULL
   ref: NULL
  rows: 5194
 Extra: Using where; Using filesort
 1 row in set (0.00 sec)

 It seems that this fix will cause nearly all of my queries to use
 filesort.

 Any ideas for avoiding this?

 Thanks!

 --ScottG.

 --
 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: LEFT JOIN changes order of results

[Scott Gifford]

[EMAIL PROTECTED] writes:

 hi,
 mls_num is not in a key, have you tried index creation on (zip,price
 desc,mls_num) ?

Hi mathias,

mls_num is the primary key, so it does have its own index.

I could create a multi-column index covering (zip,price,mls_num), but
that was really just one example of many searches; there are about 10
fields that are commonly used for searches, and about 4 that are
commonly sorted by, so creating all of those indexes would require 40
indexes, and that's if the searches only use one field.

ScottG.

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

Re: Need help in basic query

[Anoop kumar V]

SOLVED:

I changed my query to include max(t1.dt_aud_rec) instead of t1.dt_aud_rec.

I had guessed that it required just a tweak here and there...

Does anybody have any other suggestions apart from this??

Thanks,
Anoop

On 6/2/05, Anoop kumar V [EMAIL PROTECTED] wrote:
 
 OK - I have found the cause of the inconsistency - 
 
 Whenever I have more than one record which has name_rec_type as 
 'Assignment' I do not get any results (I get an empty result)
 But if I have only one Assignment record then it returns the correct row.
 
 Question is: How can I overcome this - I just need the id_secr_rqst if and 
 only if it has the latest name_rec_type as 'Assignment' - I do not care what 
 the earlier records contained.
 
 help please,
 Anoop
 
 On 6/2/05, Anoop kumar V [EMAIL PROTECTED] wrote:
  
  Hi mysql-ers,
  
  I need help in a basic query:
  
  I have this table:
  
  select * from isr2_aud_log where 
  id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
  --and name_rec_type = 'Assignment'
  order by id_secr_rqst, dt_aud_rec
   
  
  ++-+-+
  | id_secr_rqst | dt_aud_rec | name_rec_type |
  
  ++-+-+
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
  Submission |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
  Exception Requested |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
  Exception Resource |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 | 
  Director Approval |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 | 
  Assignment |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 | 
  Risk Assessment |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 | 
  Assignment |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 | 
  SERB Approval |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | 
  Assignment |
  
  ++-+-+
  
  and i am using this query:
  
  SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest, 
  t1.dt_aud_rec AS date1 
  FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = 
  t2.id_secr_rqst 
  AND t1.name_rec_type = 'Assignment' AND 
  t1.id_secr_rqst IN (
  'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
  GROUP BY t2.id_secr_rqst HAVING date1 = latest
  
  What I expected to get is the id_secr_rqst which has the last 
  name_rec_type = 'Assignment'
  In this case there is only one id_secr_rqst and it has the last 
  name_rec_type as 'Assignment'. But I do not seem to get consistent results.
  As I am using an older version of mysql I do not have the liberty to use 
  subqueries and will have to do everything using joins only. 
  
  The problem I am facing is that this query only sometimes returns rows 
  and most of the time I get an empty result set. This table does not have 
  any 
  primary keys.
  
  Can somebody please point out what is the mistake I am doing - I think 
  it just needs a tweak here and there (I hope..)
  
  Thanks,
  Anoop
  
  -- 
  Thanks and best regards,
  Anoop 
 
 
 
 
 -- 
 Thanks and best regards,
 Anoop 
 



-- 
Thanks and best regards,
Anoop

Re: Problems importing dump 4.0 4.1

[Gleb Paharenko]

Hello.



Specify the right character set with --default-character-set command

line option for mysql (if you're using it for reading dump). Chapter 10

of the manual could be helpful. See:

  http://dev.mysql.com/doc/mysql/en/charset.html









[EMAIL PROTECTED] [EMAIL PROTECTED] wrote:

 Hi,

 

 I'm trying to import a db dump made on ver. 4.0, on a new 4.1 vers=

 ion.

 

 All accented characters (french, spanish) are replaced by a '?'=0D

 =

 

 What it's the right way to import it ?

 

 

 Thanks in advance

 

 Robert=

 o Jobet

 =0A=0A=0A=0A=

 =0ANavighi a 4 MEGA e i primi 3 mesi sono GRATIS. =0AScegli Liber=

 o Adsl Flat senza limiti su http://www.libero.it=0A

 

 



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




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

Re: Need help in basic query

[mfatene]

Hi,
Try just :

SELECT id_secr_rqst task_id, MAX(dt_aud_rec) AS latest
FROM isr2_aud_log WHERE
name_rec_type = 'Assignment' AND id_secr_rqst
='TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF'
GROUP BY id_secr_rqst
;

++-+
| task_id| latest  |
++-+
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 |
++-+
1 row in set (0.02 sec)

Mathias


Selon Anoop kumar V [EMAIL PROTECTED]:

 Hi mysql-ers,

 I need help in a basic query:

 I have this table:

 select * from isr2_aud_log where
 id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF')
 --and name_rec_type = 'Assignment'
 order by id_secr_rqst, dt_aud_rec


++-+-+
 | id_secr_rqst | dt_aud_rec | name_rec_type |

++-+-+
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
 Submission |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
 Exception Requested |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
 Exception Resource |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 |
 Director Approval |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 |
 Assignment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 |
 Risk Assessment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 |
 Assignment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 |
 SERB Approval |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 |
 Assignment |

++-+-+

 and i am using this query:

 SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest,
 t1.dt_aud_recAS date1
 FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst =
 t2.id_secr_rqst
 AND t1.name_rec_type = 'Assignment' AND
 t1.id_secr_rqst IN (
 'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF')
 GROUP BY t2.id_secr_rqst HAVING date1 = latest

 What I expected to get is the id_secr_rqst which has the last name_rec_type
 = 'Assignment'
 In this case there is only one id_secr_rqst and it has the last
 name_rec_type as 'Assignment'. But I do not seem to get consistent results.
 As I am using an older version of mysql I do not have the liberty to use
 subqueries and will have to do everything using joins only.

 The problem I am facing is that this query only sometimes returns rows and
 most of the time I get an empty result set. This table does not have any
 primary keys.

 Can somebody please point out what is the mistake I am doing - I think it
 just needs a tweak here and there (I hope..)

 Thanks,
 Anoop

 --
 Thanks and best regards,
 Anoop




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

Re: View

[mfatene]

NO

Selon Jerry Swanson [EMAIL PROTECTED]:

 Does Mysql 4 supports views?

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





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

Re: SELECT problem

[mfatene]

Hi René,
thsi can be a solution, many others are possible :

mysql select  distinct the_date, person_id, cost, name
- from trips,persons
- where person_id=persons.id
- and the_date in(select max(the_date) from trips a
-  where a.person_id=person_id
-  group by person_id)
- ;
++---+--+--+
| the_date   | person_id | cost | name |
++---+--+--+
| 2005-02-08 | 1 |  580 | john |
| 2005-01-25 | 2 |  200 | jane |
| 2005-02-03 | 3 |  600 | mike |
| 2005-02-20 | 4 |  320 | mary |
++---+--+--+
4 rows in set (0.00 sec)

Mathias

Selon René Fournier [EMAIL PROTECTED]:

 I'm having a really hard time selecting rows from a table in one SELECT
 statement. I can do it in two SELECTS, but it seems I should be able to
 do it in one.

 TRIPS

 iddateperson_id   cost
 ---
 1 2005-01-01  2   500
 2 2005-01-05  1   400
 3 2005-01-12  4   350
 4 2005-01-15  3   175
 5 2005-01-17  2   385
 6 2005-01-25  2   200
 7 2005-02-03  3   600
 8 2005-02-08  1   580
 9 2005-02-20  4   320

 PERSONS

 idname
 -
 1 john
 2 jane
 3 mike
 4 mary
 5 henry


 Okay, I want to select from Trips the most recent trip for each person.
 As you can see, some of the Persons have travelled more than once, but
 I only want the last trip each one made. Also, not everyone in Persons
 has made a trip (Henry).

 Here's the output I'm looking for:

   2005-02-20  4   320 mary
   2005-02-08  1   580 john
   2005-02-03  3   600 mike
   2005-01-25  2   200 jane


 I've written and re-written my SELECT queries  numerous times, but
 can't seem to retrieve just one, most-recent trip/person. Any ideas?

 ...Rene


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





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

Re: LEFT JOIN changes order of results

[mfatene]

you can also try to increase the value of the tmp_table_size variable.

A+

Selon [EMAIL PROTECTED]:

 You have a sort because you did an order by.
 If you had an index with the desired order by, it may be used.
 Try as you usage of covering indexes.

 you certainly know that one multi-column index is similar to a lot of
 multi-column others when desired columns are in the right position of columns
 used in the index.

 this may let you implement less than 40 indexes. Otherwise force mls_num in
 all
 indexes you create an add it in the queries that doesn't use it with an
 always
 true condition (nls_num =0 for example)


 Mathias

 Selon Scott Gifford [EMAIL PROTECTED]:

  [EMAIL PROTECTED] writes:
 
   hi,
   mls_num is not in a key, have you tried index creation on (zip,price
   desc,mls_num) ?
 
  Hi mathias,
 
  mls_num is the primary key, so it does have its own index.
 
  I could create a multi-column index covering (zip,price,mls_num), but
  that was really just one example of many searches; there are about 10
  fields that are commonly used for searches, and about 4 that are
  commonly sorted by, so creating all of those indexes would require 40
  indexes, and that's if the searches only use one field.
 
  ScottG.
 






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

Re: How to get the name of the last failed FK constraint

[mfatene]

I say this :
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.11-nt-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql status
--
mysql  Ver 14.7 Distrib 4.1.11, for Win32 (ia32)

Connection id:  1
Current database:
Current user:   [EMAIL PROTECTED]
SSL:Not in use
Using delimiter:;
Server version: 4.1.11-nt-max-log
Protocol version:   10
Connection: localhost via TCP/IP
Server characterset:latin2
Db characterset:latin2
Client characterset:latin1
Conn.  characterset:latin1
TCP port:   3306
Uptime: 12 hours 17 min 30 sec

Threads: 1  Questions: 3  Slow queries: 0  Opens: 11  Flush tables: 1  Open
tables: 0  Queries per second avg: 0.000
--

mysql


Connection id:  1 is yours.

Mathias

Selon Frank Schröder [EMAIL PROTECTED]:

 [EMAIL PROTECTED] wrote:
  Hi,
  Status;
 
  gives your own session id. To be combined to show innodb status (ksh or
 perl).
 
  Mathias
 
 I'm not sure I understand.

 Are you saying that SHOW INNODB STATUS shows only the information of the
 current session or that I can supply a session id to the call?

 --
 Frank

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

Unauthenticated User (lots of connections problem)

[Michael Brown]

There's very few solutions on this problem in my research. I recently
discovered a connection issue I cannot debug for the life of me. I don't
know if it's related to a network/dns problem, if its the client, or the db
server.
 
My slave client, MySQL 4.0.23a (tried upgrading to 4.1.10, no change) tries
to connect to our MySQL 4.1.7 server via PHP5 using mysql_connect(). Upon
the connection, it makes approximately 600 connection attempts until the
server times out with max connections, then stops and says 'Could not
connect to MySQL Server'. The connection attempts appear on the server as:
 
19331 | unauthenticated user | XXX.XXX.XXX.XXX
BLOCKED::http://www.xamo.com:61519 :61519 | NULL  | Connect|
NULL | Reading from net   | NULL 
 
The even wierder part is that the IP address it says the connection is
coming from isn't the IP address of where it's actually coming from, its a
completely different IP on the local network. I don't get that at all. My 3
other slaves don't have this problem at all.
 
I'm using PHP 5 to make the connections on each box, same specs on all slave
boxes. The only difference is one is on a different network. All slaves are
from external networks. Doesn't appear to be a firewall issue as the
firewall is allowing the connections just like the other slaves. The problem
slave can ping the database server and make other connections.
 
I'm unsure if this is something on the Server, or if it's an issue related
to the one problematic slave. It happens on the connection attempt, so it
can't be a permissions issue with the server, or anything to do with SQL
queries. Any insight into this at all?
 
 
Michael Brown
Xamo Entertainment Inc.
 
 

Re: LEFT JOIN changes order of results

[mfatene]

You have a sort because you did an order by.
If you had an index with the desired order by, it may be used.
Try as you usage of covering indexes.

you certainly know that one multi-column index is similar to a lot of
multi-column others when desired columns are in the right position of columns
used in the index.

this may let you implement less than 40 indexes. Otherwise force mls_num in all
indexes you create an add it in the queries that doesn't use it with an always
true condition (nls_num =0 for example)


Mathias

Selon Scott Gifford [EMAIL PROTECTED]:

 [EMAIL PROTECTED] writes:

  hi,
  mls_num is not in a key, have you tried index creation on (zip,price
  desc,mls_num) ?

 Hi mathias,

 mls_num is the primary key, so it does have its own index.

 I could create a multi-column index covering (zip,price,mls_num), but
 that was really just one example of many searches; there are about 10
 fields that are commonly used for searches, and about 4 that are
 commonly sorted by, so creating all of those indexes would require 40
 indexes, and that's if the searches only use one field.

 ScottG.




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

Re: Cannot start mysql due to possibly a bug

[Gleb Paharenko]

Privet!



file: './mysql/host.frm' (errno: 13)



perror 13

OS error code  13:  Permission denied



Change the owner of /var/lib/mysql to 'mysql' user. Please next time

post your messages to the list, the probability of helpful answers

increases in several times :)











On Tue, 31 May 2005 07:03:48 +0200

Dobroe vremja sutok. Vot eshe dannye s togo komjutera gde mysql ne

hochet zapuskatsea:





[EMAIL PROTECTED] mouse $ cat /etc/hosts

# /etc/hosts:  This file describes a number of hostname-to-address

#  mappings for the TCP/IP subsystem.  It is mostly

#  used at boot time, when no name servers are running.

#  On small systems, this file can be used instead of a

#  named name server.  Just add the names, addresses

#  and any aliases to this file...

# $Header: /home/cvsroot/gentoo-src/rc-scripts/etc/hosts,v 1.8

2003/08/04 20:12:25 azarah Exp $

#



127.0.0.1   localhost

192.168.1.111   mouse mynetwork.mouseware.com

192.168.1.1 router mynetwork.mouseware.com router



# IPV6 versions of localhost and co

#::1 ip6-localhost ip6-loopback

#fe00::0 ip6-localnet

#ff00::0 ip6-mcastprefix

#ff02::1 ip6-allnodes

#ff02::2 ip6-allrouters

#ff02::3 ip6-allhosts





[EMAIL PROTECTED] mouse $ cat /etc/mysql/my.cnf

# /etc/mysql/my.cnf: The global mysql configuration file.

# $Header:

/var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.0.14-r1,v 1.2

2004/07/18 02:47:43 dragonheart Exp $

#

# This file can be simultaneously placed in three places:

# 1. /etc/mysql/my.cnf to set global options.

# 2. /var/lib/mysql/my.cnf to set server-specific options.

# 3. ~/.my.cnf to set user-specific options.

#

# One can use all long options that the program supports.

# Run the program with --help to get a list of them.

#

# The following values assume you have at least 32M RAM!



[client]

#password   = my_password

port= 3306

socket  = /var/run/mysqld/mysqld.sock



[safe_mysqld]

err-log = /var/log/mysql/mysql.err



[mysqld]

#skip-innodb

user= mysql

pid-file= /var/run/mysqld/mysqld.pid

socket  = /var/run/mysqld/mysqld.sock

log-error   = /var/log/mysql/mysqld.err

# If set, mysql logs all queries(general query log). This will be

deprecated in

# 5.0. This logs all queries, even error queries and is slow.

# log  = /var/log/mysql/mysql.log

#

# If you really need logging, use rather binary logging. Especially

when doing

# replication. Read

# file:/usr/share/doc/mysql-*/manual.html.gz#Replication

# You can use PURGE MASTER LOGS TO '$hostname-bin.010' to get rid of

old logs

# from $hostname-bin.01 up to $hostname-bin.09 while the slave server

is

# running.

# Before doing that, check which logfile slave curently uses by running

# mysql SHOW SLAVE STATUS

# To list logfiles on master do:

# mysql SHOW MASTER LOGS

# Then use PURGE for those not needed anymore only! Never remove the

files

# manually!

#

# Also consult RESET MASTER and RESET SLAVE commands before doing any

changes

# mysql RESET MASTER - Deletes all binary logs listed in the index

#   file, resetting the binlog index file to be

empty.

# mysql RESET SLAVE - Makes the slave forget its replication position

in

#  the master logs.

# mysql SET SQL_LOG_BIN=0  - this turns off logging (execute on MASTER

only)

# mysql SET SQL_LOG_BIN=1  - this turns on logging (execute on MASTER

only)

#

# log-bin

# set-variable  = binlog-do-db=non_existant

# set-variable  = binlog-ignore-db=database_name

#

# server-id has to unique for each master or slave in your network,

# lets use last number from IP address

# server-id  = 207

basedir = /usr

datadir = /var/lib/mysql

tmpdir  = /tmp

language= /usr/share/mysql/english

skip-locking

set-variable= key_buffer=16M

set-variable= max_allowed_packet=1M

set-variable= thread_stack=128K

# keep secure by default!

bind-address= 127.0.0.1

#   bind-address= 192.168.1.111

port= 3306

# this can make it even more secure:

#skip-networking



[mysqldump]

quick

set-variable= max_allowed_packet=1M



[mysql]

#no-auto-rehash # faster start of mysql but no tab completition



[isamchk]

set-variable= key_buffer=16M







[EMAIL PROTECTED] mouse $ cat /var/log/mysql/mysqld.err

050531 18:10:17  InnoDB: Started

050531 18:10:17 /usr/sbin/mysqld: Can't find file: './mysql/host.frm'

(errno: 13)

050531 18:10:17 Fatal error: Can't open privilege tables: Can't find

file: './mysql/host.frm' (errno: 13)

050531 18:10:17 Aborting



050531 18:10:17  InnoDB: Starting shutdown...

050531 18:10:19  InnoDB: Shutdown completed

050531 18:10:19 /usr/sbin/mysqld: Shutdown Complete



050531 18:12:44 /usr/sbin/mysqld: 

How to find random records in a subset?

[Brian Dunning]

I am using a routine to find 50 random records in a large MySQL  
database (about a million records) where I generate a list of 50  
random unique ID's, and then use MySQL's in command to find them. I  
can't use order by rand() due to its performance hit.


But I have to take it one more step: I want to first limit my found  
set to those matching a different search criteria, and then find 50  
of those.


Anyone? Can this be done all within MySQL, or is it going to require  
some humongo PHP arrays?


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

Re: How to find random records in a subset?

[Warren Young]

Brian Dunning wrote:


But I have to take it one more step: I want to first limit my found  set 
to those matching a different search criteria, and then find 50  of those.


SELECT id FROM bla WHERE whatever

That gets you a list of IDs that match the criteria.  Then select 50 
IDs, and issue the full-record SELECT for those 50.


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

RE: How to find random records in a subset?

[Michael Brown]

Brian,

I've done this using a huge array before, trust me avoid it like the plague.
It will eat up more memory than you might expect. I've done the following
with success, but it depends on your table setup.

?
$cres=mysql_query(SELECT id FROM tablename ORDER BY id DESC LIMIT
1);
$crow=mysql_fetch_object($cres);
$lastid=$crow-id;
for($z=0;$z20;$z++){
$nums[]=rand(1,$lastid);
}
$SQL=SELECT * FROM tablename WHERE ;
foreach($nums as $key=$val){
$SQL.=id=$val or ;
}
$SQL=substr($SQL,0,strlen($SQL)-4); // chop off the last ' or '
$dres=mysql_query($SQL);
// do what you want with it
?

I hope it helps.

Michael Brown
Xamo Entertainment

-Original Message-
From: Brian Dunning [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 02, 2005 6:32 PM
To: mysql@lists.mysql.com
Subject: How to find random records in a subset?

I am using a routine to find 50 random records in a large MySQL database
(about a million records) where I generate a list of 50 random unique ID's,
and then use MySQL's in command to find them. I can't use order by
rand() due to its performance hit.

But I have to take it one more step: I want to first limit my found set to
those matching a different search criteria, and then find 50 of those.

Anyone? Can this be done all within MySQL, or is it going to require some
humongo PHP arrays?

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



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

Re: SELECT problem and QUESTION OF SPEED

[René Fournier]

Thanks for the solution. It looks like it would work, but I don't have 
MySQL 4.1 (which I believe is required for this to work, since this is  
SUBSELECT, isn't it?).


Assuming I have to use two selects, which would you say is faster, 
creating a temporary table in MySQL, or extracting the data back into 
PHP, and recreating a SELECT from there?


...Rene

On 2-Jun-05, at 2:28 PM, [EMAIL PROTECTED] wrote:


Hi René,
thsi can be a solution, many others are possible :

mysql select  distinct the_date, person_id, cost, name
- from trips,persons
- where person_id=persons.id
- and the_date in(select max(the_date) from trips a
-  where a.person_id=person_id
-  group by person_id)
- ;
++---+--+--+
| the_date   | person_id | cost | name |
++---+--+--+
| 2005-02-08 | 1 |  580 | john |
| 2005-01-25 | 2 |  200 | jane |
| 2005-02-03 | 3 |  600 | mike |
| 2005-02-20 | 4 |  320 | mary |
++---+--+--+
4 rows in set (0.00 sec)

Mathias

Selon René Fournier [EMAIL PROTECTED]:

I'm having a really hard time selecting rows from a table in one 
SELECT
statement. I can do it in two SELECTS, but it seems I should be able 
to

do it in one.

TRIPS

id  dateperson_id   cost
---
1   2005-01-01  2   500
2   2005-01-05  1   400
3   2005-01-12  4   350
4   2005-01-15  3   175
5   2005-01-17  2   385
6   2005-01-25  2   200
7   2005-02-03  3   600
8   2005-02-08  1   580
9   2005-02-20  4   320

PERSONS

id  name
-
1   john
2   jane
3   mike
4   mary
5   henry


Okay, I want to select from Trips the most recent trip for each 
person.

As you can see, some of the Persons have travelled more than once, but
I only want the last trip each one made. Also, not everyone in Persons
has made a trip (Henry).

Here's the output I'm looking for:

2005-02-20  4   320 mary
2005-02-08  1   580 john
2005-02-03  3   600 mike
2005-01-25  2   200 jane


I've written and re-written my SELECT queries  numerous times, but
can't seem to retrieve just one, most-recent trip/person. Any ideas?

...Rene


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






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









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

RE: Unauthenticated User (lots of connections problem)

[Daniel]

BLOCKED::http://www.xamo.com:61519 is not actually part of the
output from SHOW PROCESSLIST correct?

Have you tried starting MySQL with --skip-name-resolve ? If this is
the issue I'm thinking it is, would you be able to run tcpdump 
through grep for DNS traffic and see if anything is going on. And
how well or not do the resolvers in /etc/resolv.conf on the master
server resolve the problematic slave's IP address, reverse and
forward? Is the problem slave the one on the different network?

-Daniel

-Original Message-
From: Michael Brown [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 02, 2005 4:55 PM
To: mysql@lists.mysql.com
Subject: Unauthenticated User (lots of connections problem)


There's very few solutions on this problem in my research. I recently
discovered a connection issue I cannot debug for the life of me. I don't
know if it's related to a network/dns problem, if its the client, or the db
server.
 
My slave client, MySQL 4.0.23a (tried upgrading to 4.1.10, no change) tries
to connect to our MySQL 4.1.7 server via PHP5 using mysql_connect(). Upon
the connection, it makes approximately 600 connection attempts until the
server times out with max connections, then stops and says 'Could not
connect to MySQL Server'. The connection attempts appear on the server as:
 
19331 | unauthenticated user | XXX.XXX.XXX.XXX
BLOCKED::http://www.xamo.com:61519 :61519 | NULL  | Connect|
NULL | Reading from net   | NULL 
 
The even wierder part is that the IP address it says the connection is
coming from isn't the IP address of where it's actually coming from, its a
completely different IP on the local network. I don't get that at all. My 3
other slaves don't have this problem at all.
 
I'm using PHP 5 to make the connections on each box, same specs on all slave
boxes. The only difference is one is on a different network. All slaves are
from external networks. Doesn't appear to be a firewall issue as the
firewall is allowing the connections just like the other slaves. The problem
slave can ping the database server and make other connections.
 
I'm unsure if this is something on the Server, or if it's an issue related
to the one problematic slave. It happens on the connection attempt, so it
can't be a permissions issue with the server, or anything to do with SQL
queries. Any insight into this at all?
 
 
Michael Brown
Xamo Entertainment Inc.
 
 


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

Re: mysqldump : Character set '#33' is not a compiled character set

[Gu Lei]

Hello Gleb Paharenko

Version is 4.1.10-max

Regards

Gu Lei

Gleb Paharenko :

Hello.

What version of MySQL do you use? Complete support for utf8
exists only in 4.1.xx and 5.x versions.


Gu Lei [EMAIL PROTECTED] wrote:
  

[-- text/plain, encoding 7bit, charset: us-ascii, 23 lines --]

Hi

It's my first time using mysqldump.
[EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases  backup_test.sql
mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
mysqldump: Character set '#33' is not a compiled character set and is
not specified in the '/usr/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$
[EMAIL PROTECTED] mysql]$ mysqldump -uroot
--character-sets-dir=/usr/local/mysql/share/mysql/charsets
--all-databases --default-character-set=utf8  backup_test.sql
mysqldump: Character set 'utf8' is not a compiled character set and is
not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$

What can I do?
Thanks

Regards,

Gu Lei




  

Re: mysqldump : Character set '#33' is not a compiled character set

[Gu Lei]

[EMAIL PROTECTED] :

  hi,
look at :
usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf

in

http://dev.mysql.com/doc/mysql/en/mysqldump.html

add the port, protocol,password

mathias

Selon Gu Lei [EMAIL PROTECTED]:

  
  
Hi

It's my first time using mysqldump.
[EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases  backup_test.sql
mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
mysqldump: Character set '#33' is not a compiled character set and is
not specified in the '/usr/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$
[EMAIL PROTECTED] mysql]$ mysqldump -uroot
--character-sets-dir=/usr/local/mysql/share/mysql/charsets
--all-databases --default-character-set=utf8  backup_test.sql
mysqldump: Character set 'utf8' is not a compiled character set and is
not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$

What can I do?
Thanks

Regards,

Gu Lei
--


  
  

  

Hi

Thanks.
What does --defaults-extra-file mean?
I've seen http://dev.mysql.com/doc/mysql/en/mysqldump.html. But I don't
know what should --defaults-extra-file be set.

Regards

Gu Lei
-- 

Re: mysqldump : Character set '#33' is not a compiled character set

[Gu Lei]

Gu Lei ??:


[EMAIL PROTECTED] ??:


hi,
look at :
usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf

in

http://dev.mysql.com/doc/mysql/en/mysqldump.html

add the port, protocol,password

mathias

Selon Gu Lei [EMAIL PROTECTED]:

 


Hi

It's my first time using mysqldump.
[EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases  backup_test.sql
mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
mysqldump: Character set '#33' is not a compiled character set and is
not specified in the '/usr/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$
[EMAIL PROTECTED] mysql]$ mysqldump -uroot
--character-sets-dir=/usr/local/mysql/share/mysql/charsets
--all-databases --default-character-set=utf8  backup_test.sql
mysqldump: Character set 'utf8' is not a compiled character set and is
not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$

What can I do?
Thanks

Regards,

Gu Lei
--

   




 


Hi

Thanks.
What does --defaults-extra-file mean?
I've seen http://dev.mysql.com/doc/mysql/en/mysqldump.html. But I 
don't know what should --defaults-extra-file be set.


Regards

Gu Lei
--


Sorry , it's my fault.
There are two version of mysql on the same machine and I used the wrong one.

Reagards,

Gu Lei

--

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

Re: mysqldump : Character set '#33' is not a compiled character set

[Gu Lei]

Gu Lei :

Hello Gleb Paharenko

Version is 4.1.10-max

Regards

Gu Lei

Gleb Paharenko :

  

Hello.

What version of MySQL do you use? Complete support for utf8
exists only in 4.1.xx and 5.x versions.


Gu Lei [EMAIL PROTECTED] wrote:
 



[-- text/plain, encoding 7bit, charset: us-ascii, 23 lines --]

Hi

It's my first time using mysqldump.
[EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases  backup_test.sql
mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
mysqldump: Character set '#33' is not a compiled character set and is
not specified in the '/usr/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$
[EMAIL PROTECTED] mysql]$ mysqldump -uroot
--character-sets-dir=/usr/local/mysql/share/mysql/charsets
--all-databases --default-character-set=utf8  backup_test.sql
mysqldump: Character set 'utf8' is not a compiled character set and is
not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file
[EMAIL PROTECTED] mysql]$

What can I do?
Thanks

Regards,

Gu Lei
   

  

 




  

Sorry , it's my fault.
There are two version of mysql on the same machine and I used the wrong one.

Reagards,

Gu Lei


-- 

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

Re: View

[Spenser]

Views are supported, however, starting with MySQL version 5.0.1.  See
this page of the on-line documentation to read more about it:
http://dev.mysql.com/doc/mysql/en/views.html


On Thu, 2005-06-02 at 14:31 -0400, Jerry Swanson wrote:
 Does Mysql 4 supports views?



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

Re: Design of a Client-side MySQL Java Load Balancer

[Kevin Burton]

[EMAIL PROTECTED] wrote:


Hi,
i think that client load-balacer are more Dispatchers than real load balancer.

load balancing in the database side takes care to number of connections, but
also node load. So thisis more real. But this issue is difficult.

 

No... you're making assumptions.  With the two-phase protocol I 
developed the nodes cooperate and distribute load and connections.  They 
also handle failover.


Simply put I can do a better job than hardware balancers because I 
already KNOW what MySQL can do.  Most load balancers are dumb.



even for oracle with 9iRAC and 10gRAC, load balancing is not completely
controled.

you speak abot load balancing and introduce also the failover notion, which
isnot a load balancing concept. Fail over is difficult because controling it
implies that every node must have the image before of every transaction.

 


Image?

Failover isn't a load balancing concept?  Not according to our hardware 
vendor :)



With cache fusion, ora

 cle RAC gives a solution, but assumes failover only fo select 
statements. All DML statements are lost if a

 node is lost.

The DML situation here is a tough one.  For SELECTS I have no problem 
with failover.  For DML I would have no problem unless you're in a 
transaction. 


We don't use transaction and I think they're evil anyway.

Kevin

--


Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.


Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 



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