Status variable select_full_join

[Noamn]

The manual says that this variable should be as low as possible. At the
moment, its value is growing slowly but steadily in my database, and I'm
trying to check where the problem is. I have inserted counters into each
program module, so I can tell which queries are being executed.

To the best of my knowledge, I have created indexes for every field which
features in a 'where' or 'join' statement, but there is obviously something
not being covered.

Is there any systematic way of finding out which queries are causing
'select_full_join' to grow?

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

unauthenticated user

[Javier Tacón]

 
Hi all,
 
Today I have a strange problem, all the connections to mysql takes like
15-30 minuts to finally connect. When keeping an eye on the proccesslist
I notice that I have a lot of these:
 
 
| 4650 | unauthenticated user | 172.26.0.118:1037 | NULL | Connect |
NULL | login | NULL |
| 4651 | unauthenticated user | 172.26.0.118:1038 | NULL | Connect |
NULL | login | NULL |
| 4662 | unauthenticated user | 172.26.0.85:47976 | NULL | Connect |
NULL | login | NULL |
| 4701 | unauthenticated user | 172.26.0.85:47977 | NULL | Connect |
NULL | login | NULL |
| 4776 | unauthenticated user | 172.26.0.118:1039 | NULL | Connect |
NULL | login | NULL |
| 4785 | unauthenticated user | 172.26.0.118:1040 | NULL | Connect |
NULL | login | NULL |
| 4888 | unauthenticated user | 172.26.0.118:1041 | NULL | Connect |
NULL | login | NULL |
| 4896 | unauthenticated user | 172.26.0.118:1042 | NULL | Connect |
NULL | login | NULL |
 
I updated the mysql version to 4.1.1 a few days ago and all went
correctly, but I downgraded to 4.0.x again, my old version, and the
problem still exists event when I restart the mysql server.
 
Anyone knows any possible reason for this trouble? May be something
related with DNS servers (attacks) ? Can I debug to know more about this
threads ?
 
 
Thanks in advance.
 

Javier Tacón ([EMAIL PROTECTED]) - Developer
AZ Interactive, SL - (+34) 93 507 
http://www.azinteractive.com http://www.azinteractive.com/  ::
http://www.justlamp.com http://www.justlamp.com/ 
 
 

Improving a query

[Noamn]

I have a query which was written like this
select p.id, p.name, p.money, s.name, agents.name
from projects p, status s, agents
where p.stage = 0
and p.status = s.id
and p.agent = agents.id
order by p.name

Despite the fact that p.name is indexed, 'explain' shows in the extra column
'using temporary; using filesort'. I don't understand why.

Also, I have reason to believe that this query is bumping up the
'select_full_join' count, so I rewrote it as
select p.id, p.name, p.money, s.name, agents.name
from projects p
left join status s on s.id = p.status
left join agents on agents.id = p.agent
order by p.name

This doesn't improve the data returned by 'explain', and it seems that
'select_full_join' increased by two (instead of one, as before).

The 'status' and 'agents' tables exist in the query only to give values to
the pointers stored in the 'projects' table (after all, this is a relational
database with each name being stored only once).

So what's the most efficient way to get at the data?

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

Re: Outer join question

[Diana Soares]

First, you database design. You don't need to separate actresses from
actors... Why do that? They are the same entity, a person, with only one
different attribute: the genre. So, you should join them in one single
table:

Actors
==
act_id
name 
genre ENUM('m','f')

Then, the table DVD. If we mantain things as they are, we would need one
entry in table DVD for each actor/actress in the movie, we would be 
repeating the title N times... Whenever you change it, you would need to
change N records... And is redundant information.

So, let's take DVD as an entity by itself. DVD would be:

DVD
===
dvd_id
title
description
year
other_fields_related...

and then, you relate the 2 tables with this one:

DVD_Actors
==
dvd_id
act_id
leader ENUM('yes','no')

(the table name may not be the happiest :-p)
The field leading tells you if that actor is the leading one or not...

  I want to select the title of each movie, along with the corresponding
  leading actor and/or actress name, but a straightforward join will only
  return those movie titles that have NOT NULL values in BOTH the acto_id
  and actr_id fields in the DVD table.

With this design, you could use:

SELECT M.title, A.name, A.genre, DA.leader 
FROM DVD AS M 
LEFT JOIN DVD_Actors AS DA ON (M.dvd_id=DA.dvd_id) 
LEFT JOIN Actors AS  A ON (DA.act_id=A.act_id)

Please, read the manual about LEFT JOIN.

  My grey-haired memory tells me that an outer join for both the actor table
  and the actress table is the answer, in that the query will return all
  titles *even if* one or both fields are NULL. (At least that was the case
  when I was using Oracle!)

And you were right.
 
  So, can somebody please correct the following query (and explain the
  syntax) so that it will work please? (I haven't tried putting an outer
  join in it because I don't understand the syntax.)

In this example, you are using inner join... please, read the manual
about JOINs.
 
  Select
  actr.name,
  acto.name,
  dvd.title
  from
  actresses actr,
  actors acto,
  dvd
  where
  actr.actr_id = dvd.actr_id
  and
  acto.acto_id = dvd.acto_id
  order by dvd.title;
  
  (I used to put (+) at the end of the outer join line, but don't think this
  will work in MYSQL - at least I don't see it in the manual.)
  
  Thanks in advance for your kind help and sorry for the wordy question!

-- 
Diana Soares


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

Reading securely the value of an autoincrement field used as PK

[Marco Paci]

Environment OS:windows 2003 server, PLATFORM:.NET framework 1.1,
LANGUAGE:C#, .NET PROVIDER:MySQLDriverCS

In an application I'm developing on top of mysql, I wanna use as primary
key for tables an autoincrement field. 
Every time I insert a new record I need to read the value assigned to
the PK field for that record. 
Since the process of inserting a new record and reading its PK field
value is a two step process implemented by:
1) insert into tablename (columnnames) values()
2) select last_insert_id()
,and since because of the architecture of my application I cannot
prevent that 2 concurrent insertions won't be done and since I wanna use
a read_commited isolation level for the transaction, I'm wondering if is
there any way to determine securely the value of an insertion.

I mean.
I've 2 thread th1 and th2.


  |Insert a new record
  |Insert a new Record

  |Read the value of PK field
  |Read the value of PK field
  |
  |
  |
  |
  v
TimeThread 1Thread 2
Axis


Is the value read by the thread 1 for the PK field correct?
Thank you 
Marco Paci

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

[Fwd: Re: Outer join question]

[Diana Soares]

Sorry, i meant gender, not genre.

-Forwarded Message-

First, you database design. You don't need to separate actresses from
actors... Why do that? They are the same entity, a person, with only one
different attribute: the genre. So, you should join them in one single
table:

Actors
==
act_id
name 
genre ENUM('m','f')

Then, the table DVD. If we mantain things as they are, we would need one
entry in table DVD for each actor/actress in the movie, we would be 
repeating the title N times... Whenever you change it, you would need to
change N records... And is redundant information.

So, let's take DVD as an entity by itself. DVD would be:

DVD
===
dvd_id
title
description
year
other_fields_related...

and then, you relate the 2 tables with this one:

DVD_Actors
==
dvd_id
act_id
leader ENUM('yes','no')

(the table name may not be the happiest :-p)
The field leading tells you if that actor is the leading one or not...

  I want to select the title of each movie, along with the corresponding
  leading actor and/or actress name, but a straightforward join will only
  return those movie titles that have NOT NULL values in BOTH the acto_id
  and actr_id fields in the DVD table.

With this design, you could use:

SELECT M.title, A.name, A.genre, DA.leader 
FROM DVD AS M 
LEFT JOIN DVD_Actors AS DA ON (M.dvd_id=DA.dvd_id) 
LEFT JOIN Actors AS  A ON (DA.act_id=A.act_id)

Please, read the manual about LEFT JOIN.

  My grey-haired memory tells me that an outer join for both the actor table
  and the actress table is the answer, in that the query will return all
  titles *even if* one or both fields are NULL. (At least that was the case
  when I was using Oracle!)

And you were right.
 
  So, can somebody please correct the following query (and explain the
  syntax) so that it will work please? (I haven't tried putting an outer
  join in it because I don't understand the syntax.)

In this example, you are using inner join... please, read the manual
about JOINs.
 
  Select
  actr.name,
  acto.name,
  dvd.title
  from
  actresses actr,
  actors acto,
  dvd
  where
  actr.actr_id = dvd.actr_id
  and
  acto.acto_id = dvd.acto_id
  order by dvd.title;
  
  (I used to put (+) at the end of the outer join line, but don't think this
  will work in MYSQL - at least I don't see it in the manual.)
  
  Thanks in advance for your kind help and sorry for the wordy question!
-- 
Diana Soares


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

Re: Outer join question

[Craig A. Finseth]

   First, you database design. You don't need to separate actresses from
   actors... Why do that? They are the same entity, a person, with only one
   different attribute: the genre. So, you should join them in one single
   table:

...
Actually, it is possible to be female and to be an Actor.  For
example, the Screen Actors' Guild officially considers the term
Actor to be gender-neutral and applies this term to all people.
...

Craig

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

Re: Reading securely the value of an autoincrement field used as PK

[Tobias Asplund]

On Tue, 20 Jan 2004, Marco Paci wrote:

 Since the process of inserting a new record and reading its PK field
 value is a two step process implemented by:
 1) insert into tablename (columnnames) values()
 2) select last_insert_id()
 ,and since because of the architecture of my application I cannot
 prevent that 2 concurrent insertions won't be done

http://www.mysql.com/doc/en/LOCK_TABLES.html

What will happen is:

   |Write-lock on table
   |Insert a new record
   | Write-lock on table (waiting...)
   |
   |Read the value of PK field
   |Unlocking table(s)
   | Write-lock gotten
   | Insert new record
   | Read the value of PK field
   | Unlock table(s)
   |
   |
   |
   v
 TimeThread 1   Thread 2
 Axis


cheers,
Tobias

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

Re: Reading securely the value of an autoincrement field used as PK

[robert_rowe]

Use a different connection for each thread. last_insert_id() is isolated per 
connection.

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

Re: Can't Set SQL_MODE?!

[Paul DuBois]

At 13:22 -0800 1/17/04, [EMAIL PROTECTED] wrote:
Howdy all,

I've tried to search for an answer to this on the lists (and on google),
but I haven't been able to come up with anything, so I apologize in
advance if this question is redundant.
According to the docs (http://www.mysql.com/doc/en/SQL_mode.html):

The MySQL server can operate in different SQL modes, and can apply
these modes differentially for different clients
You can set...the SQL_MODE variable with SET [SESSION|GLOBAL]
SQL_MODE='modes' Setting the SESSION variable affects only the
current client You can retrieve the current mode by issuing a
SELECT @@sql_mode statement.
Okay, fair enough. My app needs ANSI (or at least PIPES_AS_CONCAT). So
here I go...
mysql \s
--
mysql Ver 12.22 Distrib 4.0.17, for pc-linux (i686)
Connection id: 8
Current database: mysql
Current user: [EMAIL PROTECTED]
SSL: Not in use
Current pager: stdout
Using outfile: ''
Server version: 4.0.17-standard
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 day 14 hours 32 min 19 sec
Threads: 1 Questions: 708 Slow queries: 0 Opens: 419 Flush tables: 1
Open tables: 48 Queries per second avg: 0.005
--
mysql SET SESSION SQL_MODE='ANSI';
ERROR 1193: Unknown system variable 'SQL_MODE'
mysql SET SESSION sql_mode='ANSI';
ERROR 1193: Unknown system variable 'sql_mode'
mysql SELECT @@sql_mode;
ERROR 1193: Unknown system variable 'sql_mode'
mysql show variables like '%mode%';
+---+---+
| Variable_name | Value |
+---+---+
| sql_mode  | 0 |
+---+---+
1 row in set (0.00 sec)

Huh? Was the documenation just plain lying to me? What gives? Any help
would be extremely appreciated.
sql_mode exists as a variable that can be set after startup time only
as of MySQL 4.1.  I'll amend the documentation.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reading securely the value of an autoincrement field used as PK

[Roger Baklund]

* Tobias Asplund
 On Tue, 20 Jan 2004, Marco Paci wrote:

  Since the process of inserting a new record and reading its PK field
  value is a two step process implemented by:
  1) insert into tablename (columnnames) values()
  2) select last_insert_id()
  ,and since because of the architecture of my application I cannot
  prevent that 2 concurrent insertions won't be done

 http://www.mysql.com/doc/en/LOCK_TABLES.html

 What will happen is:

|Write-lock on table
|Insert a new record
[...]

You seem to be saying that he needs to lock the table?

That is wrong. The LAST_INSERT_ID() function is created specifically for
this purpose, it will return the id of the last auto_increment record
inserted by THIS connection. Two simultaneous users will have two different
connections, thus the LAST_INSERT_ID() will return the correct id for both
(or any number of) users, regardless of who executed the first/last INSERT.

URL: http://www.mysql.com/doc/en/Information_functions.html#IDX1388 
URL: http://www.mysql.com/doc/en/ANSI_diff_Transactions.html#IDX141 

--
Roger


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

Re: Improving a query

[Roger Baklund]

* Noamn
 I have a query which was written like this
 select p.id, p.name, p.money, s.name, agents.name
 from projects p, status s, agents
 where p.stage = 0
 and p.status = s.id
 and p.agent = agents.id
 order by p.name

 Despite the fact that p.name is indexed, 'explain' shows in the
 extra column
 'using temporary; using filesort'. I don't understand why.

Could you show us the output of this EXPLAIN, and also SHOW CREATE TABLE on
projects, status and actors?

--
Roger


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

Unknown MySQL Server Host '̞' (11001)€: Q̞(53)

[Arunachalam]

hi all!

what are all the situations, which raise this error?
If any one got ideas regarding this pls let me know
I haven't step ahead due to this error.
 
  Unknown MySQL Server Host '̞' (11001)€: Q̞(53)

thanks
Arun.


Yahoo! India Mobile: Download the latest polyphonic ringtones.
Go to http://in.mobile.yahoo.com

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

RE: Improving a query

[Noamn]

This is the unadulterated material, complete with Hebrew table names!

mysql describe sochnim;
+-+--+--+-+-++
| Field   | Type | Null | Key | Default | Extra  |
+-+--+--+-+-++
| id  | mediumint(9) |  | PRI | NULL| auto_increment |
| name| char(24) |  | MUL | ||
| passkey | char(8)  | YES  | | NULL||
| email   | char(12) | YES  | | NULL||
+-+--+--+-+-++
4 rows in set (0.00 sec)

mysql show index from sochnim;
+-++--+--+-+---+
-+--++-+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+-++--+--+-+---+
-+--++-+
| sochnim |  0 | PRIMARY  |1 | id  | A |
8 | NULL | NULL   | |
| sochnim |  1 | i_name   |1 | name| A |
8 | NULL | NULL   | |
+-++--+--+-+---+
-+--++-+
2 rows in set (0.01 sec)

mysql describe status;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| id   | mediumint(9) |  | PRI | 0   |   |
| name | char(24) |  | MUL | |   |
| displayorder | tinyint(4)   | YES  | MUL | NULL|   |
+--+--+--+-+-+---+
3 rows in set (0.00 sec)

mysql show index from status;
+++---+--+--+---
+-+--++-+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name  | Collation
| Cardinality | Sub_part | Packed | Comment |
+++---+--+--+---
+-+--++-+
| status |  0 | PRIMARY   |1 | id   | A
|   5 | NULL | NULL   | |
| status |  1 | i_display |1 | displayorder | A
|   5 | NULL | NULL   | |
| status |  1 | i_name|1 | name | A
|   5 | NULL | NULL   | |
+++---+--+--+---
+-+--++-+
3 rows in set (0.00 sec)

mysql describe projects;
+-+--+--+-+-++
| Field   | Type | Null | Key | Default | Extra  |
+-+--+--+-+-++ 
| id  | mediumint(9) |  | PRI | NULL| auto_increment |
| name| char(32) |  | MUL | ||
| sochen  | mediumint(9) | YES  | MUL | NULL||
| schum   | bigint(20)   |  | | 0   ||
| status  | tinyint(4)   | YES  | MUL | NULL||
| adrichal| mediumint(9) | YES  | MUL | NULL||
| customer| mediumint(9) | YES  | MUL | NULL||
| managers| mediumint(9) | YES  | MUL | NULL||
| openeddate  | date | YES  | MUL | NULL||
| targetdate  | date | YES  | | NULL||
| matzav  | tinyint(4)   | YES  | MUL | NULL||
| closeddate  | date | YES  | MUL | NULL||
| closedmonth | mediumint(9) | YES  | | NULL||
| reason  | tinyint(4)   | YES  | | NULL||
| ordered | tinyint(4)   | YES  | | NULL||
| sug | tinyint(4)   | YES  | MUL | NULL||
| comments| mediumint(9) | YES  | | NULL||
| sicui   | tinyint(4)   | YES  | | NULL||
| hazadit | char(8)  | YES  | | NULL||
+-+--+--+-+-++
19 rows in set (0.00 sec)

mysql show index from projects;
+--+++--+-+-
--+-+--++-+
| Table| Non_unique | Key_name   | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+--+++--+-+-
--+-+--++-+
| projects |  0 | PRIMARY|1 | id  | A
|1334 | NULL | NULL   

Re: Unknown MySQL Server Host 'Ìz' (11001)?: QÌz(53)

[Roger Baklund]

* Arunachalam
 what are all the situations, which raise this error?
 If any one got ideas regarding this pls let me know
 I haven't step ahead due to this error.

   Unknown MySQL Server Host '̞' (11001)€: Q̞(53)

Could you explain the context or show the statement that causes this error?

An unquoted \r can cause similar errors:

mysql select \rwhatever Iz;
ERROR 2005: Unknown MySQL Server Host 'Iz' (2)

--
Roger


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

Re:_Unknown_MySQL_Server_Host_'Ìz'_(11001)? :_QÌz(53)

[Arunachalam]

 Note: forwarded message attached. 

Please take time to read the mail and 
give me your valuable suggestion.

Thanks
Arun


Yahoo! India Mobile: Download the latest polyphonic ringtones.
Go to http://in.mobile.yahoo.com---BeginMessage---
I have somehow managed to create the datatype in COBOL matching
to C datatype and passed as argument to mysql_init and
mysql_real_connect.

My COBOL coding seems to working fine, but it could not able to
connect to MySQL and retrive Data. Instead it produce an error as;

Unknown MySQL Server Host '̞' (11001)

It seems that MySQL Server Host identified by C API function
as '̞'. I have given my MySQL server host nams as 'MySERVER'.

Is this error message means what i write above? or anything else?
If so, How could I resolve this issue?

any help would be highly appriciated...

thanks

Arun.

Hi MySQLians!
 
 I have clearly mentioned my objective and the portion of work i have finished in my 
 last mail.
 unfortunatly I don't find any reply for the past weekdays. Tii I have a trust, some 
 one would
 take
 time to read this and sent me the suggestion to this list.
 
 My current issue is from mysql_init function of MySQL in libmysql.lib library file. 
 i.e., MYSQL* mysql_init(MYSQL *mysql), here actually this function 
 require a
 parameter
 of MYSQL type NULL pointer and return back the MYSQL handle.
 
 From COBOL, If I pass a NULL pointer to this function, it wouldn't agree with that 
 and produce
 runtime ERROR as  RDB010E: Interrupt trapped: Illegal storage access. 
 
 The error says that, the function trying to access some invalid location other than 
 MySQL -
 right?
 
 To resolve this I try to frame the MYSQL datatype NULL Pointer in COBOL, for that 
 when I look
 the
 Datatype used in mysql.h header file, I found that it has number of decalarations 
 using
 standard
 datatypes and some user defined data types. see below;
 
 typedef struct st_mysql 
 {  
 NET   net;
 gptr  connector_fd; 
 char  *host,
   *user,
   *passwd,
   *unix_socket,
   *server_version,
   *host_info,
   *info,
   *db; 
 struct charset_info_st *charset;
 MYSQL_FIELD   *fields;
 MEM_ROOT  field_alloc; 
 my_ulonglong affected_rows;  
 my_ulonglong insert_id; 
 my_ulonglong extra_info;  
 unsigned long thread_id;
 unsigned long packet_length;  
 unsigned int  port, 
 client_flag, 
 server_capabilities;
 unsigned int protocol_version;  
 unsigned int field_count;  
 unsigned int server_status;  
 unsigned int  server_language;  
 struct st_mysql_options options;  
 enum mysql_status status;  
 my_bool   free_me;  
 my_bool   reconnect;
 char scramble_buff[9];
 my_bool rpl_pivot;
 struct st_mysql * master, 
  *next_slave;  
 struct st_mysql* last_used_slave; 
 struct st_mysql* last_used_con;
 } MYSQL;
 
   - I want to confirm that Is this much lengthy decalration of pointer needed? (OR)
   - We can just find the length and given it to COBOL pointer declaration?
   - Incase Length does the needs how to fix the length for each dataitem in C?
 
 If it succeed we all, as MySQL user can proud of put a mile stone for the support to 
 COBOL.
 
 I would be wonder if any one read this clearly and suggest me. 
 
 thanks
 
 Arun.
 
  --- Arunachalam [EMAIL PROTECTED] wrote:  Hi!
  
  I am trying to connect MySQL Database to COBOL. I have made it possible to some 
  stages by the
  way
  as follows; I have developed this under; 
  Client: Microsoft Windows 2000 Service pack 4.
  CA-Realia COBOL Compiler Version 6.0.45
  Microsoft (R) Incremental Linker Version 6.00.8168
  MySQL server 4.0.17-max in SUSE Linux,
  
  MySQL provides set of C API to interact with MySQL database, to link the functions 
  to the
  compiler
  it provides a library file libmysql.lib. COBOL has the External subroutine 
  featurs to CALL
 the
  program written in other languages. 
  
  When I try to link existing libmysql.lib file directly to the COBOL compiler it 
  won’t
 recognize
  it. So I have prepared a COBOL compiler specific library file from the 
  corresponding
  libmysql.dll
  file, in such a way. (The libmysql.dll I have used to create COBOL compiler 
  specific
  libmysql.lib
  is gathered from Mysql 4.0.17-max-debug for windows, in my localhost)
  
  The COBOL compiler specific libmysql.lib is linked successfully to the COBOL 
  Compiler and I
 have
  written some subroutine call to the function related to database connection from 
  COBOL. Such
  that
  mysql_init, mysql_real_connect, mysql_real_query, mysql_error and mysql_close.
  
  Here mysql_init requires a Null pointer argument and return the MySQL handle back.
  
  mysql_real_connect use that MySQL handle with additional arguments host name, 
  userid,
 password,
  DB
  name, port number, socket and flag to establish 

Re: Improving a query

[Roger Baklund]

*  Noamn
[...]
 { What does the cardinality mean? There should be three values for matzav,
 three for sug and five for status. Is this zero based? }

It is the expected number of different values in this column. The number is
not accurate. You can run ANALYZE TABLE to update this information.

URL: http://www.mysql.com/doc/en/ANALYZE_TABLE.html 

 mysql explain select p.id, p.name, p.schum, s.name, sochnim.name
 - from projects p, status s, sochnim
 - where p.matzav = 0
 - and p.status = s.id
 - and p.sochen = sochnim.id
 - order by p.name;
 +-+++--+--
---+--
 +--+-+
 | table   | type   | possible_keys  | key  | key_len | ref
 | rows | Extra   |
 +-+++--+--
---+--
 +--+-+
 | p   | ref| i_matzav,i_sochen,i_status | i_matzav |

As you can see here, the i_name index is not even considered, and the
i_matzav index is used. This is why it needs to be sorted at the end.

Try creating a compound index on (matzav,name).

--
Roger


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

Repair Table Hung?

[rmck]

Help.

I have a REPAIR table command that has been running since 1/15... I dont know if its 
hung or what? Should I kill it ? Top shows that it seems to be running? 

mysql show processlist;
++--+---+-+-++--+---+
| Id | User | Host  | db  | Command | Time   | State| Info 
 |
++--+---+-+-++--+---+
| 10 | root | localhost | ip_logs | Query   | 391630 | Repair with keycache | REPAIR 
TABLE Jan04 QUICK
++--+---+-+-++--+---

 # ls -alh Jan04.* 

-rw-rw1 mysqlmysql8.6K Dec 29 08:19 /curipdb/ip_logs/Jan04.frm
-rw-rw1 mysqlmysql 23G Jan 15 16:25 /curipdb/ip_logs/Jan04.MYD
-rw-rw1 mysqlmysql 22G Jan 20 06:30 /curipdb/ip_logs/Jan04.MYI
#

top sorted by CPU:

 06:32:07  up 5 days, 14:31,  4 users,  load average: 1.46, 1.49, 1.39 
102 processes: 101 sleeping, 1 running, 0 zombie, 0 stopped 
CPU0 states:   0.2% user   0.1% system0.0% nice   0.0% iowait  99.2% idle 
CPU1 states:  12.0% user   0.4% system0.0% nice   0.0% iowait  87.0% idle 
Mem:  3874188k av, 3863468k used,   10720k free,   0k shrd,   33544k buff 
   2618824k actv,  563052k in_d,   89848k in_c 
Swap: 4289328k av,  527664k used, 3761664k free 3197660k cached 
 
  PID USER PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND 
 5285 mysql  5 -10  501M 497M   452 S   12.5 13.1  1580m   0 mysqld 
17002 root  16   0  1108 1108   820 S 0.3  0.0   0:13   0 top 
  728 root  16   0   2364 0 S 0.1  0.0   1:37   0 sshd 

Thanks 
Rob


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

Re: SQL Query Question

[sulewski]

Hello,

For my final solution I decided to use the inner join method. The query 
is created dynamically based upon a user interface component that 
allows people to build queries using parenthesis, ands and or's.  Plus 
there is another field that I didn't include in the original question 
so as to keep the problem focused.  So here is the basic structure of 
what I did,

each query starts with the standard
select table1.*,table2.* from table1,tabl2,
then I append the inner joins for each search able field. The user can 
choose to search for one value or many values. Realistically I don't 
expect this to go above 3 to 5 fields.
searchtable s1,searchtable s2
Then I include the queries
where (table2.id=s1.rdid and then the dynamic part

((s2.vid=10109 and s2.value=5) and (s1.vid=10089 and s1.value4000)))

now I make sure all the searchtables are matched
and (s1.rdid=s2.rdid)
then the rest of my query which binds table1 to table2 but that 
irrelevant to this discussion
and blah blah blah

Here is an example
select table1.*,table2.* from table1,table2,searchtable s1,searchtable 
s2 where (table2.id=s1.rdid and ((s2. vid =10109 and s2.value=5) and 
(s1. vid =10089 and s1.value4000))) and (s1.rdid=s2.rdid) and blah 
blah blah

This works really fast for a table with about 20,000 records.  I hope 
it works against a large table. But unfortunately when I add an 'or' it 
really slows down by about 5 fold.

select table1.*,table2.* from table1,table2,searchtable s1,searchtable 
s2, searchtable s3 where (table2.id=s1.rdid and ( ((s3. vid =10109 and 
s3.value=5) and (s1. vid =10089 and s1.value4000)) or (v2. vid =10096 
and v2.value=10))) and (s1.rdid=s2.rdid and s2.rdid=s3.rdid) and blah 
blah blah

Thanks for everyone's help! I can't tell you how much I appreciate it.

Joe

On Monday, January 19, 2004, at 07:30  PM, Jochem van Dieten wrote:

Michael Satterwhite wrote:
On Monday 19 January 2004 16:30, Jochem van Dieten wrote:
Michael Satterwhite said:
On Monday 19 January 2004 15:38, Jochem van Dieten wrote:
So let's make it 2 fields:
SELECT
  t1.*
FROM
  table1 t1,
  table2 t2 INNER JOIN table2 t3
   ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554)
WHERE
  t1.rdid = t2.rdid
Add GROUP BY/DISTINCT per your requirements.
Although you're giving Table2 two aliases (t2 and t3) there is still
only two  tables and *ONE* field. In the join listed above, you are
asking for the  records where t2.rdid = t3.rdid (*NOT* what you want
to do, you have now left  t1 out of the join altogether) plus ???
(I'm not sure what this would match,  although it looks
interesting).
Why not hold of judgement until you are sure what it would match?
It's only the second part of the join that I'm not sure of
Then why not hold of judgement until you are sure ?


Table1 (t1) isn't used at all in the join parameters.
Not all joins are specified using the join keyword.


As records from table1 are required in the result, this won't work as 
desired.
Would you please just create the tables and compare all the offered 
suggestions?

Jochem

--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
--
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: _Unknown_MySQL_Server_Host_'Ìz'_(11001)? :_QÌz(53)

[Roger Baklund]

* Arunachalam
 Sent: Tuesday, January 13, 2004 10:39 PM
 To: mySQL List
 Subject: Re: mysql_init function issues :Connecting MySQL to COBOL


 I have somehow managed to create the datatype in COBOL matching
 to C datatype and passed as argument to mysql_init and
 mysql_real_connect.

 My COBOL coding seems to working fine, but it could not able to
 connect to MySQL and retrive Data. Instead it produce an error as;

When you say somehow managed, what does that mean? Have you ever connected
to a mysql server with this code? Or is that what you are trying to do for
the first time?

 Unknown MySQL Server Host '̞' (11001)

 It seems that MySQL Server Host identified by C API function
 as '̞'. I have given my MySQL server host nams as 'MySERVER'.

How do you call the function? There seems to be a problem related to the
parameters...

 Is this error message means what i write above? or anything else?

I would guess there is something wrong with the way you pass the arguments
to the function... ̞ could be binary data.

 If so, How could I resolve this issue?

Change the first parameter or how you pass the parameters to the function.

--
Roger


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

Re: 4.1.1 not an update or upgrade of 4.1.0

[Victoria Reznichenko]

Director General: NEFACOMP [EMAIL PROTECTED] wrote:

 Hi group, I have discovered this morning a strange behaviour of MySQL 4.1.1
 When the SUB-SELECT contains more than one field, it does not work (this works very 
 well with 4.1.0 )

 For example:
 SELECT field1 FROM Table1 WHERE (field2,field3) IN (SELECT field2,field3 FROM Table2)
 
 
 The above query works as expected with mysqld-nt.exe 4.1.0 (on WinXP) but it does 
 not work with mysqld-nt.exe 4.1.1 (on WinXP).
 This problem causes unexpected results as the recordset comes empty (on 4.1.1) while 
 it comes with rows with 4.1.0 and no error is returned!!!

Works fine for me. Could you send me a test case?


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





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

Re: SQL Query Question

[sulewski]

I think I figured out the time problem. If I make s2 in the or s1 and 
remove any instances of s2 it works very fast with the 'or'.

Joe

On Tuesday, January 20, 2004, at 09:50  AM, sulewski wrote:

Hello,

For my final solution I decided to use the inner join method. The 
query is created dynamically based upon a user interface component 
that allows people to build queries using parenthesis, ands and or's.  
Plus there is another field that I didn't include in the original 
question so as to keep the problem focused.  So here is the basic 
structure of what I did,

each query starts with the standard
select table1.*,table2.* from table1,tabl2,
then I append the inner joins for each search able field. The user can 
choose to search for one value or many values. Realistically I don't 
expect this to go above 3 to 5 fields.
searchtable s1,searchtable s2
Then I include the queries
where (table2.id=s1.rdid and then the dynamic part

((s2.vid=10109 and s2.value=5) and (s1.vid=10089 and s1.value4000)))

now I make sure all the searchtables are matched
and (s1.rdid=s2.rdid)
then the rest of my query which binds table1 to table2 but that 
irrelevant to this discussion
and blah blah blah

Here is an example
select table1.*,table2.* from table1,table2,searchtable s1,searchtable 
s2 where (table2.id=s1.rdid and ((s2. vid =10109 and s2.value=5) and 
(s1. vid =10089 and s1.value4000))) and (s1.rdid=s2.rdid) and blah 
blah blah

This works really fast for a table with about 20,000 records.  I hope 
it works against a large table. But unfortunately when I add an 'or' 
it really slows down by about 5 fold.

select table1.*,table2.* from table1,table2,searchtable s1,searchtable 
s2, searchtable s3 where (table2.id=s1.rdid and ( ((s3. vid =10109 and 
s3.value=5) and (s1. vid =10089 and s1.value4000)) or (v2. vid 
=10096 and v2.value=10))) and (s1.rdid=s2.rdid and s2.rdid=s3.rdid) 
and blah blah blah

Thanks for everyone's help! I can't tell you how much I appreciate it.

Joe

On Monday, January 19, 2004, at 07:30  PM, Jochem van Dieten wrote:

Michael Satterwhite wrote:
On Monday 19 January 2004 16:30, Jochem van Dieten wrote:
Michael Satterwhite said:
On Monday 19 January 2004 15:38, Jochem van Dieten wrote:
So let's make it 2 fields:
SELECT
  t1.*
FROM
  table1 t1,
  table2 t2 INNER JOIN table2 t3
   ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554)
WHERE
  t1.rdid = t2.rdid
Add GROUP BY/DISTINCT per your requirements.
Although you're giving Table2 two aliases (t2 and t3) there is 
still
only two  tables and *ONE* field. In the join listed above, you are
asking for the  records where t2.rdid = t3.rdid (*NOT* what you 
want
to do, you have now left  t1 out of the join altogether) plus ???
(I'm not sure what this would match,  although it looks
interesting).
Why not hold of judgement until you are sure what it would match?
It's only the second part of the join that I'm not sure of
Then why not hold of judgement until you are sure ?


Table1 (t1) isn't used at all in the join parameters.
Not all joins are specified using the join keyword.


As records from table1 are required in the result, this won't work 
as desired.
Would you please just create the tables and compare all the offered 
suggestions?

Jochem

--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
--
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

[Javier Tacón]

Well, this has been fixed, for your info, it was a problem from our
DNS's with the reverse name process.

Greets.
Javier Tacon


-Mensaje original-
De: Javier Tacón [mailto:[EMAIL PROTECTED] 
Enviado el: martes, 20 de enero de 2004 11:23
Para: [EMAIL PROTECTED]
Asunto: unauthenticated user


 
Hi all,
 
Today I have a strange problem, all the connections to mysql takes like
15-30 minuts to finally connect. When keeping an eye on the proccesslist
I notice that I have a lot of these:
 
 
| 4650 | unauthenticated user | 172.26.0.118:1037 | NULL | Connect |
NULL | login | NULL |
| 4651 | unauthenticated user | 172.26.0.118:1038 | NULL | Connect |
NULL | login | NULL |
| 4662 | unauthenticated user | 172.26.0.85:47976 | NULL | Connect |
NULL | login | NULL |
| 4701 | unauthenticated user | 172.26.0.85:47977 | NULL | Connect |
NULL | login | NULL |
| 4776 | unauthenticated user | 172.26.0.118:1039 | NULL | Connect |
NULL | login | NULL |
| 4785 | unauthenticated user | 172.26.0.118:1040 | NULL | Connect |
NULL | login | NULL |
| 4888 | unauthenticated user | 172.26.0.118:1041 | NULL | Connect |
NULL | login | NULL |
| 4896 | unauthenticated user | 172.26.0.118:1042 | NULL | Connect |
NULL | login | NULL |
 
I updated the mysql version to 4.1.1 a few days ago and all went
correctly, but I downgraded to 4.0.x again, my old version, and the
problem still exists event when I restart the mysql server.
 
Anyone knows any possible reason for this trouble? May be something
related with DNS servers (attacks) ? Can I debug to know more about this
threads ?
 
 
Thanks in advance.
 

Javier Tacón ([EMAIL PROTECTED]) - Developer
AZ Interactive, SL - (+34) 93 507  http://www.azinteractive.com
http://www.azinteractive.com/  :: http://www.justlamp.com
http://www.justlamp.com/ 
 
 


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

Re:__Unknown_MySQL_Server_Host_'Ìz'_(11001)?_:_QÌz(53)

[Arunachalam]

 --- Roger Baklund [EMAIL PROTECTED] wrote:  * Arunachalam
  Sent: Tuesday, January 13, 2004 10:39 PM
  To: mySQL List
  Subject: Re: mysql_init function issues :Connecting MySQL to COBOL
 
 
  I have somehow managed to create the datatype in COBOL matching
  to C datatype and passed as argument to mysql_init and
  mysql_real_connect.
 
  My COBOL coding seems to working fine, but it could not able to
  connect to MySQL and retrive Data. Instead it produce an error as;
 
 When you say somehow managed, what does that mean? Have you ever connected
 to a mysql server with this code? Or is that what you are trying to do for
 the first time?

WHAT I MEANED HERE IS COBOL WOULDN'T ACCEPT EXISTING LIBMYSQL.LIB, FOR THAT 
I HAVE CREATED THE COBOL COMPILER SPECIFIC LIBMYSQL.LIB, BY CONVERTING LIBMYSQL.DLL
INTO LIBMYSQL.DEF FILE USING AN UTILITY PEXPORTS.EXE AND THEN FROM THE .DEF FILE 
I HAVE CREATED THE LIBMYSQL.LIB USING THE LIB:EXE OF MY COBOL COMPILER.


  Unknown MySQL Server Host '̞' (11001)
 
  It seems that MySQL Server Host identified by C API function
  as '̞'. I have given my MySQL server host nams as 'MySERVER'.
 
 How do you call the function? There seems to be a problem related to the
 parameters...
 

  
  The actual calling routines in C is;
  
  MYSQL *mysql_real_connect(MYSQL *mysql, 
const char *host,
const char *user, 
const char *passwd, 
const char *db, 
unsigned int port, 
const char *unix_socket, 
unsigned long client_flag)
  
  
  I have declared the variables in COBOL with values are;
   01 MYSQL pic x(1024).

   01 DUMMY-POINTER pic 9(9) comp-5.  

   05 C-cMySQLHostName pointer
   05 C-cMySQLHNData
06 filler pic  x(7) value 'myHost'
06 filler pic  x value LOW-VALUE

   05 C-cMySQLUserID pointer
   05 C-cMySQLUIData
06 filler pic  x(4) value 'myUser'
06 filler pic  x value LOW-VALUE
  
   05 C-cMySQLPassword pointer
   05 C-cMySQLPwdData
06 filler pic  x(6) value 'myPwd'
06 filler pic  x value LOW-VALUE
   
   05 C-cMySQLDBName pointer
   05 C-cMySQLDBNData
06 filler pic  x(6) value 'myDB'
06 filler pic  x value LOW-VALUE

   05 C-cMySQLPort pic  9(4)
88 C-cMySQLPortData value 3306

   05 C-cMySQLSocket pointer
   05 C-cMySQLSktData
06 filler  pic x(10) value  'mysql.sock'
06 filler pic  x value LOW-VALUE
   
   05 C-cMySQLFlag pic  9(9)  
 88 C-cMySQLFlagData  value 0  
  
  It's equivalent COBOL coding is;
  
  CALL C_mysql_real_connect
using by reference MySQL
by reference C-cMySQLHostName
by reference C-cMySQLUserId
by reference C-cMySQLPassword
by reference C-cMySQLDBName
by content C-cMySQLPort
by reference C-cMySQLSocket
by content C-cMySQLFlag
giving DUMMY-POINTER
  
I HAVE CLEARLY VERIFIED THAT THE DECLARATION EQIVALENT IN COBOL ARE CORRECT

  Is this error message means what i write above? or anything else?
 
 I would guess there is something wrong with the way you pass the arguments
 to the function... ̞ could be binary data.
 
  If so, How could I resolve this issue?
 
 Change the first parameter or how you pass the parameters to the function.
 
 --
 Roger

I HAVE ALMOST SPENT 1 MONTH TILL NOW

My COBOL CODING DOES NOT PRODUCE ANY ERROR, BUT THE VARIABLE SPACE MySQL STORES THE 
ERROR
MESSAGE FROM MySQL SERVER. 

WHAT I THING IS IT CAN IDENTIFY MYSQL SERVER, BUT BEING SOME REASON IT WON'T PROCEED 
FURTHER, I AM
IN THE POSITION TO IDENTIFY THE REASON AND CURE IT.

THANKS
ARUN.



Yahoo! India Mobile: Download the latest polyphonic ringtones.
Go to http://in.mobile.yahoo.com

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

Re: Repeated 100% CPU problem in FreeBSD

[Ken Menzel]

Hi Arnaud,
   I did not see an answer to this so; I think the info you need is
here http://jeremy.zawodny.com/blog/archives/000203.html and here
http://jeremy.zawodny.com/blog/archives/000697.html

But I think you want skip-name-resolve to be specified in my.cnf.

Ken

I have included the relevant section from Jeremeys blog:
___
1. Non-thread safe DNS Lookups

Certain operations are not thread-safe on FreeBSD. A fine example of
that is gethostbyname(), which MySQL calls to convert host names in to
IP addresses. Usually this happens for each new connection to the
server and whenever MySQL needs to contact another machine--typically
a replication slave connecting to its master.

Based on our testing, the only truly safe way to operate is to use
the --skip-name-resolve flag for starting mysqld AND specifying the IP
address of the master instead of the hostname. That virtually
eliminates the need for MySQL to call gethostbyname().

The symptom of this problem is that the mysqld will consume all the
available CPU time even when there are few (if any) queries running.
You can try and kill -6 the mysqld process and then run it thru gdb to
get a backtrace. You'll likely see something like this:

#0  0x829c94c in _thread_kern_sched_state_unlock () at
./cp/tinfo2.cc:300
#1  0x829c0e0 in _thread_kern_sched () at ./cp/tinfo2.cc:300
#2  0x829c787 in _thread_kern_sched_state () at ./cp/tinfo2.cc:300
#3  0x82c5fdc in kevent () at ./cp/tinfo2.cc:300
#4  0x82c5a4f in res_send () at ./cp/tinfo2.cc:300
#5  0x82a4308 in res_query () at ./cp/tinfo2.cc:300
#6  0x82a4737 in res_querydomain () at ./cp/tinfo2.cc:300
#7  0x82a44bb in res_search () at ./cp/tinfo2.cc:300
#8  0x82a9a00 in _gethostbydnsname () at ./cp/tinfo2.cc:300
#9  0x82a8739 in gethostbyname2 () at ./cp/tinfo2.cc:300
#10 0x82a86d4 in gethostbyname () at ./cp/tinfo2.cc:300
#11 0x8275fc4 in my_gethostbyname_r (
name=0x1b5f79a8 your_hostanme, result=0x9fa659b8,
buffer=0x9fa651b8 \032, buflen=2048, h_errnop=0x9fa651b0)
at my_gethostbyname.c:108
#12 0x80d6fbd in mc_mysql_connect ()
#13 0x80d6b37 in mc_mysql_reconnect ()
#14 0x80d4506 in safe_reconnect ()
#15 0x80d3fb8 in handle_slave ()
#16 0x828ffa5 in _thread_start () at ./cp/tinfo2.cc:300
#17 0x0 in ?? ()
If you see that, get rid of DNS lookups.




- Original Message - 
From: Arnaud Pignard [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, January 16, 2004 7:06 PM
Subject: Re: Repeated 100% CPU problem in FreeBSD


 Hi !

 Anyone find a solution for this problem ?

 We have 2 mysql server with same problem. Happen like every 1-2
months.

 Regards,


 -- 
 Arnaud Pignard ([EMAIL PROTECTED])
 Frontier Online - Opérateur Internet



 -- 
 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 5.0, connection loss, procedure-related, easy to reproduce, detailed report] what you ever wanted to know about trees

[Victoria Reznichenko]

Stefan Traby [EMAIL PROTECTED] wrote:
 Hi !
 
 mysql 5.0 is _really_ cool.
 
 this bug should be reproducable:

Thank you for bug report! It was entered to the bug database as
http://bugs.mysql.com/2460
 
 
 ( cat table.sql ; ./populate.pl ; ./perfect_game.pl 32 ) | mysql test
 
 after that executing the same procedure with same parameter again,
 the connection is lost:
 
 --
 mysql call self_and_up(1);
 +---+
 | id|
 +---+
 | 1 |
 |  1999 |
 |   399 |
 |79 |
 |15 |
 | 2 |
 +---+
 6 rows in set (0.20 sec)
 
 Query OK, 0 rows affected (0.20 sec)
 
 mysql call self_and_up(1);
 ERROR 2013 (HY000): Lost connection to MySQL server during query
 mysql
 --
 


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





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

Re: UDF SELECT query spoils the results

[Alexander]

The problem was solved: it was up to static variable features, not MySQL
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

'mysql' notation

[mc]

Hi all,

Perhaps this question is not appropriate to ask here, pls point me to the
right direction if I am posting to the wrong list..

I would like to hear from the people of this list, which of the below
notation do you prefer more and perhaps also in what situation? And which
one is the official name?

  1) MySQL
  2) mysql
  3) mySQL
  4) mySql


Best regards
mc



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

Re: 'mysql' notation

[Paul DuBois]

At 1:25 +0800 1/21/04, mc wrote:
Hi all,

Perhaps this question is not appropriate to ask here, pls point me to the
right direction if I am posting to the wrong list..
I would like to hear from the people of this list, which of the below
notation do you prefer more and perhaps also in what situation? And which
one is the official name?
Name of what?

  1) MySQL
Used when referring to the MySQL software as a whole or the company MySQL AB.

  2) mysql
This is the name of the mysql client program.

  3) mySQL
  4) mySql
3 and 4 are not used any more.  Though you may see 3 in older logos.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

2nd Request Repair Table Hung?

[rmck]

Help.

I have a REPAIR table command that has been running since 1/15... I dont know if its 
hung or what? Should I kill it ? Top shows that it seems to be running? 

mysql show processlist;
++--+---+-+-++--+---+
| Id | User | Host  | db  | Command | Time   | State| Info 
 |
++--+---+-+-++--+---+
| 10 | root | localhost | ip_logs | Query   | 391630 | Repair with keycache | REPAIR 
TABLE Jan04 QUICK
++--+---+-+-++--+---

 # ls -alh Jan04.* 

-rw-rw1 mysqlmysql8.6K Dec 29 08:19 /curipdb/ip_logs/Jan04.frm
-rw-rw1 mysqlmysql 23G Jan 15 16:25 /curipdb/ip_logs/Jan04.MYD
-rw-rw1 mysqlmysql 22G Jan 20 06:30 /curipdb/ip_logs/Jan04.MYI
#

top sorted by CPU:

 06:32:07  up 5 days, 14:31,  4 users,  load average: 1.46, 1.49, 1.39 
102 processes: 101 sleeping, 1 running, 0 zombie, 0 stopped 
CPU0 states:   0.2% user   0.1% system0.0% nice   0.0% iowait  99.2% idle 
CPU1 states:  12.0% user   0.4% system0.0% nice   0.0% iowait  87.0% idle 
Mem:  3874188k av, 3863468k used,   10720k free,   0k shrd,   33544k buff 
   2618824k actv,  563052k in_d,   89848k in_c 
Swap: 4289328k av,  527664k used, 3761664k free 3197660k cached 
 
  PID USER PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND 
 5285 mysql  5 -10  501M 497M   452 S   12.5 13.1  1580m   0 mysqld 
17002 root  16   0  1108 1108   820 S 0.3  0.0   0:13   0 top 
  728 root  16   0   2364 0 S 0.1  0.0   1:37   0 sshd 

Thanks 
Rob


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

Select on indexed columns

[Balazs Rauznitz]

While doing some benchmarks the other day, I saw surprisingly slow 
query results on columns that were indexed.

Here's the table definition:

create table sex (
id integer,
sex char(1));
create index id_index on sex (id);
create index sex_index on sex (sex);

Then I loaded a million rows, id was from 1 to 1_000_000, sex was
randomly 'F' or 'M'.

When searching on 'id' everything is snappy:

mysql select count(*) from sex where id459000 and id =46;
+--+
| count(*) |
+--+
| 1000 |
+--+
1 row in set (0.00 sec)

However when the 'sex' column is involved:

mysql select count(*) from sex where id459000 and id =46 and sex = 'M';
+--+
| count(*) |
+--+
|  504 |
+--+
1 row in set (5.09 sec)

Any way to make this faster ?

I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.

Insert jokes about sex making MySQL slow here

Thanks,

Balazs



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

Re: Reading securely the value of an autoincrement field used as PK

[Balazs Rauznitz]

On Tue, Jan 20, 2004 at 12:35:07PM +0100, Marco Paci wrote:
 Environment OS:windows 2003 server, PLATFORM:.NET framework 1.1,
 LANGUAGE:C#, .NET PROVIDER:MySQLDriverCS
 
 In an application I'm developing on top of mysql, I wanna use as primary
 key for tables an autoincrement field. 
 Every time I insert a new record I need to read the value assigned to
 the PK field for that record. 
 Since the process of inserting a new record and reading its PK field
 value is a two step process implemented by:
 1) insert into tablename (columnnames) values()
 2) select last_insert_id()
 ,and since because of the architecture of my application I cannot
 prevent that 2 concurrent insertions won't be done and since I wanna use
 a read_commited isolation level for the transaction, I'm wondering if is
 there any way to determine securely the value of an insertion.
 
 I mean.
 I've 2 thread th1 and th2.
 
 
   |Insert a new record
   |Insert a new Record
 
   |Read the value of PK field
   |Read the value of PK field
   |
   |
   |
   |
   v
 TimeThread 1  Thread 2
 Axis
 
 
 Is the value read by the thread 1 for the PK field correct?

Read http://www.mysql.com/doc/en/Getting_unique_ID.html. It's kept on a 
per-connection basis.

Balazs


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

MySQL slowness on querying indexed columns

[Balazs Rauznitz]

I saw surprisingly slow query results on columns that were indexed.

Here's the table definition:

create table sex (
id integer,
sex char(1));
create index id_index on sex (id);
create index sex_index on sex (sex);

Then I loaded a million rows, id was from 1 to 1_000_000, sex was 
randomly 'F' or 'M'.

When searching on 'id' everything is snappy:

mysql select count(*) from sex where id459000 and id =46;
+--+
| count(*) |
+--+
| 1000 |
+--+
1 row in set (0.00 sec)

However when the 'sex' column is involved:

mysql select count(*) from sex where id459000 and id =46 and sex = 'M';
+--+
| count(*) |
+--+
|  504 |
+--+
1 row in set (5.09 sec)

Any way to make this faster ?

Insert jokes about sex making MySQL slow here

I'm running MySQL 4.0.16 on a 1GHz AMD CPU under linux.

Thanks,

Balazs


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

Re: Reading securely the value of an autoincrement field used as PK

[Balazs Rauznitz]

On Tue, Jan 20, 2004 at 01:25:34PM +0100, Tobias Asplund wrote:
 On Tue, 20 Jan 2004, Marco Paci wrote:
 
  Since the process of inserting a new record and reading its PK field
  value is a two step process implemented by:
  1) insert into tablename (columnnames) values()
  2) select last_insert_id()
  ,and since because of the architecture of my application I cannot
  prevent that 2 concurrent insertions won't be done
 
 http://www.mysql.com/doc/en/LOCK_TABLES.html
 
 What will happen is:
 
|Write-lock on table
|Insert a new record
| Write-lock on table (waiting...)
|
|Read the value of PK field
|Unlocking table(s)
| Write-lock gotten
| Insert new record
| Read the value of PK field
| Unlock table(s)
|
|
|
v
  TimeThread 1 Thread 2
  Axis

Ignore this. There's no locking needed to call last_insert_id(); read 
my other post.

Balazs


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

RE: InnoDB locking 'non-existence' of a row

[Zeltser, Alex]

Hi Chris,

Thanks for the response and the suggestions.  Doesn't SERIALIZABLE level just add 
'LOCK IN SHARE
MODE' to your SELECTs, but other than that works just like the default REPEATABLE READ 
level?  I've
tried by example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but the 
results were the
same.

Is there any way to make the second session block when both it and the first one are 
'locking'
non-existence of a row?

Thanks in advance,

Alex

-Original Message-
From: Chris Nolan [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 16, 2004 4:55 PM
To: Zeltser, Alex
Cc: [EMAIL PROTECTED]
Subject: Re: InnoDB locking 'non-existence' of a row


Hi Alex!

On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote:
 Hi,
 
 I wanted to take advantage of the InnoDB 'gap' locking to lock 
 'non-existence' of a row, the way the manual recommends.  I tried to 
 do this by using 'select ... for update', using the 'mysql' client 
 from two separate sessions as shown below:
 
 Session 1:
  set AUTOCOMMIT=0;
  begin;
  select * from T where A = 'NOT_THERE' for update;
 
 Session 2:
  set AUTOCOMMIT=0;
  begin;
  select * from T where A = 'NOT_THERE' for update;
 
 What I'd hoped to see was the 'select' statement in Session 2 block 
 until either a commit or a rollback was performed in Session 1.  
 Unfortunately, it didn't work that way.  The 'select's in both 
 sessions returned right away, and it was only the subsequent 
 'insert's, 'update's and 'delete's that blocked.  I can understand the 
 rationale behind this behavior, but unfortunately it doesn't help me 
 with my problem.  I'd like to be able to reliably check for existence 
 of a record from two concurrent sessions and have the 'select' in the 
 'second' session block until the first session is either committed or 
 rolled back.  Is there a way to accomplish this somehow?
 
InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do 
not appear.
This is good for application writers and for MySQL itself, as phantom rows appearing 
would break
MySQL's replication.

Basically, InnoDB will place locks on the various index structures involved in your 
query around the
rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return 
nothing, InnoDB
doesn't find any index sections to place any locks on.

Perhaps you should look at using the SERIALIZABLE level of transaction isolation.

Regards,

Chris

 I know I can just try to insert the record and check for duplicates, 
 but is there a way to accomplish it with 'select's?
 
 Thanks in advance,
 
 Alex Zeltser
 
 --
 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]

Server Load by user/DB

[Cedric Fontaine]

Hello !

I'm trying to figure how to get how many queries are done by each
DB/user and not for the whole MySQL server.

How to know how many queries are done each done for each DB ? MySQL Server
load is heavy and I need to know from which websites...

-- 
Cedric - mailto:[EMAIL PROTECTED]
(DH/DSS)PGP-key Server ID: 0xBDD6E604

Haute-Vitesse au Quebec   | Des remises sur vos achats ?
http://www.haute-vitesse.org  | http://www.achatclub.ca



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

Question on MySQL C API - Segfault on mysql_real_connect()

[Deven Phillips]

Hi,

I have a program which calls a function which has an all-inclusive MySQL
set of code (i.e. MySQL is initialized, used, and released within the
span of the single function). The first time the function is called it
function properly, but on subsequent calls, the function terminates with
a segmentation violation. Can I not create and destroy MySQL structures
within the span of a single function and then call it over again? Here's
the full backtrace from gdb.

(gdb) bt full
#0  0x4028babc in mallopt () from /lib/i686/libc.so.6
No symbol table info available.
#1  0x4028ac61 in malloc () from /lib/i686/libc.so.6
No symbol table info available.
#2  0x400319fe in my_malloc () from /usr/lib/libmysqlclient.so.12
No symbol table info available.
#3  0x40042df8 in vio_new () from /usr/lib/libmysqlclient.so.12
No symbol table info available.
#4  0x4002de19 in mysql_real_connect () from
/usr/lib/libmysqlclient.so.12
No symbol table info available.
#5  0x0804a58a in update_music_queue (config=0x40347f50,
current=0x804e5a0) at mysql.c:39
dbptr = (MYSQL *) 0x8050a38
resone = (MYSQL_RES *) 0x400b52a8
restwo = (MYSQL_RES *) 0x4000a2e0
resthree = (MYSQL_RES *) 0x40347f50
row = 0xba68
query = 0x804ec18 gothic
addSongQuery = 0x7de07c3 Address 0x7de07c3 out of bounds
recent = 0xb9f4
temp = 0x6c605fa Address 0x6c605fa out of bounds
songid = 0x54f0523 Address 0x54f0523 out of bounds
requested = 0x54c056f Address 0x54c056f out of bounds
strHolder = 0x58205be Address 0x58205be out of bounds
rowcount = 102303297
count = 134540280
numsongs = 0
stringSize = 105252561
#6  0x0804b9f5 in main (argc=1, argv=0x804e5f0) at main.c:379
conffile = 0x804e5a0 \200\005\005\bp\005\005\b
holder = 0x804e5a0 \200\005\005\bp\005\005\b
current = (song *) 0x804e5a0
shoutconn = (shout_t *) 0x804f7b0
errcount = 0
retval = 134538656
config = (param *) 0x804e5f0
songinfo = 0x8050590 KMFDM - Thrash Up!
metadata = (shout_metadata_t *) 0x8050470
stringSize = 134538656
ppid = 1077185020
---Type return to continue, or q return to quit---
#7  0x4022d7f7 in __libc_start_main () from /lib/i686/libc.so.6
No symbol table info available.

Thanks in advance for any help!!!

Deven Phillips, CISSP


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

Re: Select on indexed columns

[Aleksandar Bradaric]

Hi,

 Any way to make this faster ?

Try to create an index on both fields:

  create index idsex_index on sex (id, sex)


Take care,
Aleksandar


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

Re: Select on indexed columns

[Balazs Rauznitz]

On Tue, Jan 20, 2004 at 07:09:46PM +0100, Aleksandar Bradaric wrote:
 Hi,
 
  Any way to make this faster ?
 
 Try to create an index on both fields:
 
   create index idsex_index on sex (id, sex)

Tried that; same results...

Balazs


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

Slow query times

[Balazs Rauznitz]

While doing some benchmarks the other day, I saw surprisingly slow 
query results on columns that were indexed.

Here's the table definition:

create table sex (
id integer,
sex char(1));
create index id_index on sex (id);
create index sex_index on sex (sex);

Then I loaded a million rows, id was from 1 to 1_000_000, sex was
randomly 'F' or 'M'.

When searching on 'id' everything is snappy:

mysql select count(*) from sex where id459000 and id =46;
+--+
| count(*) |
+--+
| 1000 |
+--+
1 row in set (0.00 sec)

However when the 'sex' column is involved:

mysql select count(*) from sex where id459000 and id =46 and sex = 'M';
+--+
| count(*) |
+--+
|  504 |
+--+
1 row in set (5.09 sec)

Any way to make this faster ?

I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.

Insert jokes about sex making MySQL slow here ;-)

Thanks,

Balazs



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

Re: MySQL slowness on querying indexed columns

[Gregory Newby]

Don't forget to run optimize table after you build
the combined index or make significant changes.
  -- Greg

PS: Sorry to hear of MySQL's gender issues ;-)

On Mon, Jan 19, 2004 at 10:20:12AM -0500, Balazs Rauznitz wrote:
 
 I saw surprisingly slow query results on columns that were indexed.
 
 Here's the table definition:
 
 create table sex (
 id integer,
 sex char(1));
 create index id_index on sex (id);
 create index sex_index on sex (sex);
 
 Then I loaded a million rows, id was from 1 to 1_000_000, sex was 
 randomly 'F' or 'M'.
 
 When searching on 'id' everything is snappy:
 
 mysql select count(*) from sex where id459000 and id =46;
 +--+
 | count(*) |
 +--+
 | 1000 |
 +--+
 1 row in set (0.00 sec)
 
 However when the 'sex' column is involved:
 
 mysql select count(*) from sex where id459000 and id =46 and sex = 'M';
 +--+
 | count(*) |
 +--+
 |  504 |
 +--+
 1 row in set (5.09 sec)
 
 Any way to make this faster ?
 
 Insert jokes about sex making MySQL slow here
 
 I'm running MySQL 4.0.16 on a 1GHz AMD CPU under linux.
 
 Thanks,
 
 Balazs

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

Why mysql LOCK any lines and does not allow that I delete?

[Bruno Rodrigues Silva]

I am with a problem in one query of simple DELETE. 

#1205 - Lock wait timeout exceeded; Try restarting transaction

Why mysql LOCK this line and does not allow that I delete, if I did not 
emit none stantment of LOCK?

Please, help me!

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

Re: Slow query times

[Balazs Rauznitz]

ps: 'optimize table' seems to have no effect. I was also able to 
reproduce this on a different mysql server...

Balazs

On Mon, Jan 19, 2004 at 10:39:26AM -0500, Balazs Rauznitz wrote:
 
 While doing some benchmarks the other day, I saw surprisingly slow 
 query results on columns that were indexed.
 
 Here's the table definition:
 
 create table sex (
 id integer,
 sex char(1));
 create index id_index on sex (id);
 create index sex_index on sex (sex);
 
 Then I loaded a million rows, id was from 1 to 1_000_000, sex was
 randomly 'F' or 'M'.
 
 When searching on 'id' everything is snappy:
 
 mysql select count(*) from sex where id459000 and id =46;
 +--+
 | count(*) |
 +--+
 | 1000 |
 +--+
 1 row in set (0.00 sec)
 
 However when the 'sex' column is involved:
 
 mysql select count(*) from sex where id459000 and id =46 and sex = 'M';
 +--+
 | count(*) |
 +--+
 |  504 |
 +--+
 1 row in set (5.09 sec)
 
 Any way to make this faster ?
 
 I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.
 
 Insert jokes about sex making MySQL slow here ;-)
 
 Thanks,
 
 Balazs
 
 
 
 -- 
 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]

Slow query times

[Balazs Rauznitz]

While doing some benchmarks the other day, I saw surprisingly slow 
query results on columns that were indexed.

Here's the table definition:

create table sex (
id integer,
sex char(1));
create index id_index on sex (id);
create index sex_index on sex (sex);

Then I loaded a million rows, id was from 1 to 1_000_000, sex was
randomly 'F' or 'M'.

When searching on 'id' everything is snappy:

mysql select count(*) from sex where id459000 and id =46;
+--+
| count(*) |
+--+
| 1000 |
+--+
1 row in set (0.00 sec)

However when the 'sex' column is involved:

mysql select count(*) from sex where id459000 and id =46 and sex = 'M';
+--+
| count(*) |
+--+
|  504 |
+--+
1 row in set (5.09 sec)

Any way to make this faster ?

I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.

Insert jokes about sex making MySQL slow here

Thanks,

Balazs



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

MYSQL Database

[Seena Blace]

Hi,
I'm new to this group.I would like to know which frontend tools be good tuned with 
Mysql database like php,perl etc?
I want to develop one application on linux on mysql database which eventually would be 
webbased.Please suggest what combination would be good.
thx
-Seena 


-
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes

RE: InnoDB locking 'non-existence' of a row

[Joe Shear]

hi, 
Selecting a non-existent row won't acquire any locks that prevents
inserts from happening.  One way to accomplish what you want is to
create a separate insert lock table consisting of a table name and a
lock counter.  Add a row for each table that you want to have these
insert locks on, and before performing any inserts, either update the
corresponding row in the insert lock table or select it for an update. 

joe

On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote:
 Hi Chris,
 
 Thanks for the response and the suggestions.  Doesn't SERIALIZABLE level just add 
 'LOCK IN SHARE
 MODE' to your SELECTs, but other than that works just like the default REPEATABLE 
 READ level?  I've
 tried by example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but the 
 results were the
 same.
 
 Is there any way to make the second session block when both it and the first one are 
 'locking'
 non-existence of a row?
 
 Thanks in advance,
 
 Alex
 
 -Original Message-
 From: Chris Nolan [mailto:[EMAIL PROTECTED] 
 Sent: Friday, January 16, 2004 4:55 PM
 To: Zeltser, Alex
 Cc: [EMAIL PROTECTED]
 Subject: Re: InnoDB locking 'non-existence' of a row
 
 
 Hi Alex!
 
 On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote:
  Hi,
  
  I wanted to take advantage of the InnoDB 'gap' locking to lock 
  'non-existence' of a row, the way the manual recommends.  I tried to 
  do this by using 'select ... for update', using the 'mysql' client 
  from two separate sessions as shown below:
  
  Session 1:
   set AUTOCOMMIT=0;
   begin;
   select * from T where A = 'NOT_THERE' for update;
  
  Session 2:
   set AUTOCOMMIT=0;
   begin;
   select * from T where A = 'NOT_THERE' for update;
  
  What I'd hoped to see was the 'select' statement in Session 2 block 
  until either a commit or a rollback was performed in Session 1.  
  Unfortunately, it didn't work that way.  The 'select's in both 
  sessions returned right away, and it was only the subsequent 
  'insert's, 'update's and 'delete's that blocked.  I can understand the 
  rationale behind this behavior, but unfortunately it doesn't help me 
  with my problem.  I'd like to be able to reliably check for existence 
  of a record from two concurrent sessions and have the 'select' in the 
  'second' session block until the first session is either committed or 
  rolled back.  Is there a way to accomplish this somehow?
  
 InnoDB's next-key locking is a bit different to this - it ensures that phantom rows 
 do not appear.
 This is good for application writers and for MySQL itself, as phantom rows appearing 
 would break
 MySQL's replication.
 
 Basically, InnoDB will place locks on the various index structures involved in your 
 query around the
 rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return 
 nothing, InnoDB
 doesn't find any index sections to place any locks on.
 
 Perhaps you should look at using the SERIALIZABLE level of transaction isolation.
 
 Regards,
 
 Chris
 
  I know I can just try to insert the record and check for duplicates, 
  but is there a way to accomplish it with 'select's?
  
  Thanks in advance,
  
  Alex Zeltser
  
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
  
-- 
Joe Shear [EMAIL PROTECTED]


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

MYSQL Database

[Seena Blace]

Hi,
I'm new to this group.I would like to know which frontend tools be good tuned with 
Mysql database like php,perl etc?
I want to develop one application on linux on mysql database which eventually would be 
webbased.Please suggest what combination would be good.
thx
-Seena 



-
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes

RE: InnoDB locking 'non-existence' of a row

[Zeltser, Alex]

Hi Joe,

Thanks for your reply.  Actually, in my experience (and according to the docs), if you 
select 'for
update' or 'lock in share mode', you _can_ lock non-existence of a row for inserts.  
In that case I
think the 'gap' where the row would be is locked, and attempts to insert the row from 
another
transaction will block or fail (until the first one does a commit or a rollback).  
Perhaps I'm
misunderstanding what's happening?

Unfortunately, what I'm trying to do is try to have one transaction 'lock' the 
non-existence of a
row with a select, and another wait until the lock is released--also with a select.  
I've considered
doing what you propose with a separate lock table, and may still do just that, but 
first wanted to
see if I can accomplish the same thing with some clever DB manipulation.

Thanks!

Alex

-Original Message-
From: Joe Shear [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 20, 2004 11:00 AM
To: Zeltser, Alex
Cc: [EMAIL PROTECTED]
Subject: RE: InnoDB locking 'non-existence' of a row


hi, 
Selecting a non-existent row won't acquire any locks that prevents inserts from 
happening.  One way
to accomplish what you want is to create a separate insert lock table consisting of a 
table name and
a lock counter.  Add a row for each table that you want to have these insert locks on, 
and before
performing any inserts, either update the corresponding row in the insert lock table 
or select it
for an update. 

joe

On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote:
 Hi Chris,
 
 Thanks for the response and the suggestions.  Doesn't SERIALIZABLE 
 level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than 
 that works just like the default REPEATABLE READ level?  I've tried by 
 example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but 
 the results were the same.
 
 Is there any way to make the second session block when both it and the 
 first one are 'locking' non-existence of a row?
 
 Thanks in advance,
 
 Alex
 
 -Original Message-
 From: Chris Nolan [mailto:[EMAIL PROTECTED]
 Sent: Friday, January 16, 2004 4:55 PM
 To: Zeltser, Alex
 Cc: [EMAIL PROTECTED]
 Subject: Re: InnoDB locking 'non-existence' of a row
 
 
 Hi Alex!
 
 On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote:
  Hi,
  
  I wanted to take advantage of the InnoDB 'gap' locking to lock
  'non-existence' of a row, the way the manual recommends.  I tried to 
  do this by using 'select ... for update', using the 'mysql' client 
  from two separate sessions as shown below:
  
  Session 1:
   set AUTOCOMMIT=0;
   begin;
   select * from T where A = 'NOT_THERE' for update;
  
  Session 2:
   set AUTOCOMMIT=0;
   begin;
   select * from T where A = 'NOT_THERE' for update;
  
  What I'd hoped to see was the 'select' statement in Session 2 block
  until either a commit or a rollback was performed in Session 1.  
  Unfortunately, it didn't work that way.  The 'select's in both 
  sessions returned right away, and it was only the subsequent 
  'insert's, 'update's and 'delete's that blocked.  I can understand the 
  rationale behind this behavior, but unfortunately it doesn't help me 
  with my problem.  I'd like to be able to reliably check for existence 
  of a record from two concurrent sessions and have the 'select' in the 
  'second' session block until the first session is either committed or 
  rolled back.  Is there a way to accomplish this somehow?
  
 InnoDB's next-key locking is a bit different to this - it ensures that 
 phantom rows do not appear. This is good for application writers and 
 for MySQL itself, as phantom rows appearing would break MySQL's 
 replication.
 
 Basically, InnoDB will place locks on the various index structures 
 involved in your query around the rows that have been returned bt a 
 SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't 
 find any index sections to place any locks on.
 
 Perhaps you should look at using the SERIALIZABLE level of transaction 
 isolation.
 
 Regards,
 
 Chris
 
  I know I can just try to insert the record and check for duplicates,
  but is there a way to accomplish it with 'select's?
  
  Thanks in advance,
  
  Alex Zeltser
  
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
  
-- 
Joe Shear [EMAIL PROTECTED]


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

Re[2]: Select on indexed columns

[Aleksandar Bradaric]

Hi,

  Any way to make this faster ?
 
 Try to create an index on both fields:
 
   create index idsex_index on sex (id, sex)

 Tried that; same results...

Could  you  post  the  result of the EXPLAIN command on that
query?


Take care,
Aleksandar


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

Re: Select on indexed columns

[Balazs Rauznitz]

On Tue, Jan 20, 2004 at 08:23:32PM +0100, Aleksandar Bradaric wrote:
 Hi,
 
   Any way to make this faster ?
  
  Try to create an index on both fields:
  
create index idsex_index on sex (id, sex)
 
  Tried that; same results...
 
 Could  you  post  the  result of the EXPLAIN command on that
 query?

mysql explain  select count(*) from sex where id459000 and id =46 and sex = 'M';
+---+--++---+-+---++-+
| table | type | possible_keys  | key   | key_len | ref   | 
rows   | Extra   |
+---+--++---+-+---++-+
| sex   | ref  | id_index,sex_index,sex,id,sex_both | sex_index |   2 | const | 
506151 | Using where |
+---+--++---+-+---++-+
1 row in set (0.00 sec)

mysql desc sex;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| id| int(11) | YES  | MUL | NULL|   |
| sex   | char(1) | YES  | MUL | NULL|   |
+---+-+--+-+-+---+
2 rows in set (0.00 sec)

Thanks,

Balazs


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

Advice needed for high volume of inserts

[STE-MARIE, ERIC]

Hello,

This is my first message on this list.  So I take the occasion to salute every 
one of you.

I'm looking to implement a database server wich will mostly insert data.  The 
data structure is not complexe.  The data will be insterted in 1 table at 
burst rates of 13000 insert/seconds and sustained rates of about 5000/sec for 
about 8 hours a day.   There will be selects from time to times, so let 
assume 100 selects /day.   Note that the row size will be about 80 bytes.  
The data needs to be kept in the database for around 90 days after which, the 
data is dropped from the database.  Being more familiar with Oracle, I was 
considering a partitioned table for the entries.  Someone I work with told me 
that MySQL had merge tables that could serve the same purpose.   The goal is 
to optimize selects and simplify the aging of the data by droping a partition 
and creating an other one every day.

If I have a choice between MySQL and other products, I might push the MySQL 
solution as long as it can do the job.  My hope, with this message is to get 
comments or suggestions for this kind of setup that could help me fit MySQL 
in this project.  Any suggestion is welcomed, like hardware size, warnings, 
pros, cons, etc. etc.  



Finally, we hope to save a bundle in oracle licenses.  

Thank You in advance.

-Eric



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

RE: Slow query times

[Matt Griffin]

I wouldn't imagine that creating an index on a column with only two possible
values could make things any faster.  You only get a maximum 50% reduction
in row scans, rather than the normal log based reduction with a random value
distribution.  In addition, you contend with the overhead of using the
index.  What is the runtime without the index?

Matt

-Original Message-
From: Balazs Rauznitz [mailto:[EMAIL PROTECTED]
Sent: Monday, January 19, 2004 12:53 PM
To: [EMAIL PROTECTED]
Subject: Slow query times



While doing some benchmarks the other day, I saw surprisingly slow
query results on columns that were indexed.

Here's the table definition:

create table sex (
id integer,
sex char(1));
create index id_index on sex (id);
create index sex_index on sex (sex);

Then I loaded a million rows, id was from 1 to 1_000_000, sex was
randomly 'F' or 'M'.

When searching on 'id' everything is snappy:

mysql select count(*) from sex where id459000 and id =46;
+--+
| count(*) |
+--+
| 1000 |
+--+
1 row in set (0.00 sec)

However when the 'sex' column is involved:

mysql select count(*) from sex where id459000 and id =46 and sex =
'M';
+--+
| count(*) |
+--+
|  504 |
+--+
1 row in set (5.09 sec)

Any way to make this faster ?

I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.

Insert jokes about sex making MySQL slow here

Thanks,

Balazs



--
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: Advice needed for high volume of inserts

[Peter J Milanese]

-Forwarded by Peter J Milanese/MHT/Nypl on 01/20/2004 02:36PM -

To: STE-MARIE, ERIC [EMAIL PROTECTED]
From: Peter J Milanese/MHT/Nypl
Date: 01/20/2004 02:31PM
cc: [EMAIL PROTECTED]
Subject: Re: Advice needed for high volume of inserts


It'll work.

I do slight less on the way of inserts. What I do is dynamically generate
the tables within my entry code, and merge tables based on the query. Good
for large log parsers. Be aware that this can break greatly if it's a
non-redundant live feed (to mysql). I think that's a problem anywhere
though. Mysql should not hold you back though.

Peter J. Milanese

-STE-MARIE, ERIC [EMAIL PROTECTED] wrote: -

To: [EMAIL PROTECTED]
From: STE-MARIE, ERIC [EMAIL PROTECTED]
Date: 01/20/2004 02:20PM
Subject: Advice needed for high volume of inserts

Hello,

This is my first message on this list. So I take the occasion to salute
every
one of you.

I'm looking to implement a database server wich will mostly insert data.
The
data structure is not complexe. The data will be insterted in 1 table at
burst rates of 13000 insert/seconds and sustained rates of about 5000/sec
for
about 8 hours a day. There will be selects from time to times, so let
assume 100 selects /day. Note that the row size will be about 80 bytes.
The data needs to be kept in the database for around 90 days after which,
the
data is dropped from the database. Being more familiar with Oracle, I was
considering a partitioned table for the entries. Someone I work with told
me
that MySQL had merge tables that could serve the same purpose. The goal is
to optimize selects and simplify the aging of the data by droping a
partition
and creating an other one every day.

If I have a choice between MySQL and other products, I might push the MySQL
solution as long as it can do the job. My hope, with this message is to get
comments or suggestions for this kind of setup that could help me fit MySQL
in this project. Any suggestion is welcomed, like hardware size, warnings,
pros, cons, etc. etc.



Finally, we hope to save a bundle in oracle licenses.

Thank You in advance.

-Eric



--
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: Slow query times

[Peter J Milanese]

-Forwarded by Peter J Milanese/MHT/Nypl on 01/20/2004 02:37PM -

To: [EMAIL PROTECTED]
From: Peter J Milanese/MHT/Nypl
Date: 01/20/2004 02:34PM
cc: [EMAIL PROTECTED]
Subject: RE: Slow query times


You may also want to try :

count(1)

instead of

count(*)


count(*) pulls back the data while count(1) does not.



Peter J. Milanese

-Matt Griffin [EMAIL PROTECTED] wrote: -

To: [EMAIL PROTECTED]
From: Matt Griffin [EMAIL PROTECTED]
Date: 01/20/2004 02:33PM
Subject: RE: Slow query times

I wouldn't imagine that creating an index on a column with only two
possible
values could make things any faster. You only get a maximum 50% reduction
in row scans, rather than the normal log based reduction with a random
value
distribution. In addition, you contend with the overhead of using the
index. What is the runtime without the index?

Matt

-Original Message-
From: Balazs Rauznitz [mailto:[EMAIL PROTECTED]
Sent: Monday, January 19, 2004 12:53 PM
To: [EMAIL PROTECTED]
Subject: Slow query times



While doing some benchmarks the other day, I saw surprisingly slow
query results on columns that were indexed.

Here's the table definition:

create table sex (
id integer,
sex char(1));
create index id_index on sex (id);
create index sex_index on sex (sex);

Then I loaded a million rows, id was from 1 to 1_000_000, sex was
randomly 'F' or 'M'.

When searching on 'id' everything is snappy:

mysql select count(*) from sex where id459000 and id =46;
+--+
| count(*) |
+--+
| 1000 |
+--+
1 row in set (0.00 sec)

However when the 'sex' column is involved:

mysql select count(*) from sex where id459000 and id =46 and sex =
'M';
+--+
| count(*) |
+--+
| 504 |
+--+
1 row in set (5.09 sec)

Any way to make this faster ?

I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.

Insert jokes about sex making MySQL slow here

Thanks,

Balazs



--
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: Slow query times

[Balazs Rauznitz]

You are perfectly correct. Without the index on sex query times are 
lightning fast. 

I don't quite get you explanation why; where can I read up on how 
queries are done with and without an index ?

Can one make a generalization that unless the number of different 
values in a column is less than log(number of rows), it does not make 
sense to use an index ?

Thanks,

Balazs

On Tue, Jan 20, 2004 at 02:33:28PM -0500, Matt Griffin wrote:
 I wouldn't imagine that creating an index on a column with only two possible
 values could make things any faster.  You only get a maximum 50% reduction
 in row scans, rather than the normal log based reduction with a random value
 distribution.  In addition, you contend with the overhead of using the
 index.  What is the runtime without the index?
 
 Matt
 
 -Original Message-
 From: Balazs Rauznitz [mailto:[EMAIL PROTECTED]
 Sent: Monday, January 19, 2004 12:53 PM
 To: [EMAIL PROTECTED]
 Subject: Slow query times
 
 
 
 While doing some benchmarks the other day, I saw surprisingly slow
 query results on columns that were indexed.
 
 Here's the table definition:
 
 create table sex (
 id integer,
 sex char(1));
 create index id_index on sex (id);
 create index sex_index on sex (sex);
 
 Then I loaded a million rows, id was from 1 to 1_000_000, sex was
 randomly 'F' or 'M'.
 
 When searching on 'id' everything is snappy:
 
 mysql select count(*) from sex where id459000 and id =46;
 +--+
 | count(*) |
 +--+
 | 1000 |
 +--+
 1 row in set (0.00 sec)
 
 However when the 'sex' column is involved:
 
 mysql select count(*) from sex where id459000 and id =46 and sex =
 'M';
 +--+
 | count(*) |
 +--+
 |  504 |
 +--+
 1 row in set (5.09 sec)
 
 Any way to make this faster ?
 
 I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.
 
 Insert jokes about sex making MySQL slow here
 
 Thanks,
 
 Balazs
 
 
 
 --
 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: Advice needed for high volume of inserts

[STE-MARIE, ERIC]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On January 20, 2004 02:31 pm, Peter J Milanese wrote:
 It'll work.
  
 I do slight less on the way of inserts. What I do is dynamically generate
 the tables within my entry code, and merge tables based on the query. Good
 for large log parsers. Be aware that this can break greatly if it's a
 non-redundant live feed (to mysql). I think that's a problem anywhere
 though. Mysql should not hold you back though. 
 Peter J. Milanese

Thanks peter... Out of curiosity, what kind of hardware do you use and how 
what kind of I/O do you have?  

Thanks again.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFADYRdQR+WnN6TbikRAqp9AJ4ycP/8a81tQoENnq48GBN9KLhhtgCeNIZ5
3vAUgqv8GA9NseXYsJt8zW0=
=w8HR
-END PGP SIGNATURE-



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

Re: Advice needed for high volume of inserts

[Peter J Milanese]

Hardware is Dell PE2650/Dual Xeons (2G) 4G Ram
I have a raid10 array for data volumes.

Bottleneck would be Disk. That's the problem I had, so I went raid10 on
the box.

The most I push is about 10k, and it's not too bad. Complex queries may
suffer during insert times, but you can get around a lot of that based on
your table layout. I was using it to test live web stats in a web farm.
Developed the application, then ran into caching issues and haven't looked
at it since. MySQL did the right thing though.

P

-STE-MARIE, ERIC [EMAIL PROTECTED] wrote: -

To: Peter J Milanese [EMAIL PROTECTED]
From: STE-MARIE, ERIC [EMAIL PROTECTED]
Date: 01/20/2004 02:41PM
cc: [EMAIL PROTECTED]
Subject: Re: Advice needed for high volume of inserts

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On January 20, 2004 02:31 pm, Peter J Milanese wrote:
 It'll work.

 I do slight less on the way of inserts. What I do is dynamically generate
 the tables within my entry code, and merge tables based on the query.
Good
 for large log parsers. Be aware that this can break greatly if it's a
 non-redundant live feed (to mysql). I think that's a problem anywhere
 though. Mysql should not hold you back though.
 Peter J. Milanese

Thanks peter... Out of curiosity, what kind of hardware do you use and how
what kind of I/O do you have?

Thanks again.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFADYRdQR+WnN6TbikRAqp9AJ4ycP/8a81tQoENnq48GBN9KLhhtgCeNIZ5
3vAUgqv8GA9NseXYsJt8zW0=
 =w8HR
-END PGP SIGNATURE-



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

Strange problem with index

[Grzegorz Paszka]

Hi.

I use MySQL 4.0.17 from rpm.
When I want insert new row by perl script to one of my table I get such error :
DBD::mysql::st execute failed: Duplicate entry '- Modified the spec file provided by 
...'  for key 3
Table looks :
mysql desc spak;
+--++--+-+-+---+
| Field| Type   | Null | Key | Default | Extra |
+--++--+-+-+---+
| id_lacz  | int(11)| YES  | MUL | NULL|   |
| selektor | int(4) | YES  | MUL | NULL|   |
| data | mediumtext | YES  | MUL | NULL|   |
+--++--+-+-+---+
3 rows in set (0.01 sec)

mysql show index from spak;
+---++---+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | 
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---++---+--+-+---+-+--++--++-+
| spak  |  1 | spak_id_lacz  |1 | id_lacz | A |  
206999 | NULL | NULL   | YES  | BTREE  | |
| spak  |  1 | spak_selektor |1 | selektor| A |
   5 | NULL | NULL   | YES  | BTREE  | |
| spak  |  1 | spak_data |1 | data| A |  
344999 |  200 | NULL   | YES  | BTREE  | |
| spak  |  1 | spak_fdata|1 | data| A | 
1034998 |1 | NULL   | YES  | FULLTEXT   | |
+---++---+--+-+---+-+--++--++-+
4 rows in set (0.04 sec)

mysql select count(*) from spak;
+--+
| count(*) |
+--+
|  1034998 |
+--+
1 row in set (0.00 sec)

Data length in data column is rather big;

I did myisamchk -r , optimize table, repair table, mysqldump and insert it again, but 
problem still exists.

I read that such error occurs when I insert no unique value to column with unique 
property.
But as you can see I haven't UNIQUE key anywhere. 

I need help becouse I don't know what to do.

Regards.
-- 
Grzegorz Paszka

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

Re: 2nd Request Repair Table Hung?

[Sergei Golubchik]

Hi!

On Jan 20, rmck wrote:
 Help.
 
 I have a REPAIR table command that has been running since 1/15... I
 dont know if its hung or what? Should I kill it ? Top shows that it
 seems to be running? 

No, it did not hang - Repair with keycache is slow, especially for 23G
file. You should configure MySQL so that it'll use Repair by sorting.
See the manual for details.
 
 mysql show processlist;
 ++--+---+-+-++--+---+
 | Id | User | Host  | db  | Command | Time   | State| Info   
|
 ++--+---+-+-++--+---+
 | 10 | root | localhost | ip_logs | Query   | 391630 | Repair with keycache | REPAIR 
 TABLE Jan04 QUICK
 ++--+---+-+-++--+---
 
  # ls -alh Jan04.*   
   
 -rw-rw1 mysqlmysql8.6K Dec 29 08:19 /curipdb/ip_logs/Jan04.frm
 -rw-rw1 mysqlmysql 23G Jan 15 16:25 /curipdb/ip_logs/Jan04.MYD
 -rw-rw1 mysqlmysql 22G Jan 20 06:30 /curipdb/ip_logs/Jan04.MYI
 #
 
 top sorted by CPU:
 
  06:32:07  up 5 days, 14:31,  4 users,  load average: 1.46, 1.49, 1.39 
 102 processes: 101 sleeping, 1 running, 0 zombie, 0 stopped 
 CPU0 states:   0.2% user   0.1% system0.0% nice   0.0% iowait  99.2% idle 
 CPU1 states:  12.0% user   0.4% system0.0% nice   0.0% iowait  87.0% idle 
 Mem:  3874188k av, 3863468k used,   10720k free,   0k shrd,   33544k buff 
2618824k actv,  563052k in_d,   89848k in_c 
 Swap: 4289328k av,  527664k used, 3761664k free 3197660k cached 
  
   PID USER PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND 
  5285 mysql  5 -10  501M 497M   452 S   12.5 13.1  1580m   0 mysqld 
 17002 root  16   0  1108 1108   820 S 0.3  0.0   0:13   0 top 
   728 root  16   0   2364 0 S 0.1  0.0   1:37   0 sshd 
 
 Thanks 
 Rob
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   ___/  www.mysql.com

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

Re: fulltext match against and other WHERE clauses

[Brent Baisley]

I have no problem combining MATCH with WHERE and HAVING. Are you sure 
there is supposed to be matches? Also, you need to make sure that you 
are formatting your date correctly (-mm-dd) for searching in MySQL 
if it is a date field you are searching on.

On Jan 20, 2004, at 12:36 PM, Alex Bruckert wrote:

Hi There

i use a query:

SELECT * FROM volltexttabelle ft
LEFT JOIN suchtabelle s ON s.article_id = ft.article_id
WHERE MATCH (ft.index_text) AGAINST ('+equator ' IN  BOOLEAN MODE )
which works fine

now i want to modify it ie add AND s.search_date=1980 or other clauses
which
cant be handled by the MATCH part
is this possible? i always receive no matching results when trying 
that, am
i just
doing something wrong? or is this just not supposed to be done that way

thanks for help
Alex
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL slowness on querying indexed columns

[Brent Baisley]

It sounds like you are looking for a quickie. Sorry, couldn't resist.

You should run optimize table to make sure your indexes are optimized. 
Also, you should check which index is being used first. Your sex column 
probably is split 50/50, so if MySQL is optimizing your query to use 
the sex index first, it will take a long time.

On Jan 19, 2004, at 10:20 AM, Balazs Rauznitz wrote:

When searching on 'id' everything is snappy:

mysql select count(*) from sex where id459000 and id =46;
+--+
| count(*) |
+--+
| 1000 |
+--+
1 row in set (0.00 sec)
However when the 'sex' column is involved:

mysql select count(*) from sex where id459000 and id =46 and 
sex = 'M';
+--+
| count(*) |
+--+
|  504 |
+--+
1 row in set (5.09 sec)

Any way to make this faster ?

Insert jokes about sex making MySQL slow here

I'm running MySQL 4.0.16 on a 1GHz AMD CPU under linux.

Thanks,

Balazs

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL Server Question

[Nicholas]

Hello Everyone,

I downloaded and installed the
mysql-standard-4.0.17-apple-darwin6.8-powerpc.dmg 
file from the mysql web site to my ibook running 
Mac OSX Version 10.1.5 on it.

I read the documentation on the mysql site and it 
said to do the following next:

shell cd /usr/local/mysql
shell sudo ./bin/mysqld_safe
(Enter your password, if necessary)

The server started running at this point but less then 
one second later, the server killed itself. I looked 
at the localhost.err file in the /usr/local/mysql/data 
directory and it had the following text in it:
 
040117 10:35:09 mysqld started
dyld: /usr/local/mysql/bin/mysqld Undefined symbols:
/usr/local/mysql/bin/mysqld undefined reference to _localtime_r
expected to be defined in /usr/lib/libSystem.B.dylib
040117 10:35:10 mysqld ended

Has anyone else experienced this problem on their Mac?
Does anyone know how I can correct this issue?

I know that my version of OSX is a little old 
as far as the mysql web site documentation says but 
the installation process went smoothly without any 
errors at all coming up.

Any help on this matter would be greatly appreciated.

Thanks in advance.

~~Nick 




___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!

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

Re: MySQL Server Question

[sulewski]

I'm running on 10.2.8 just fine.  I hear panther speeds up the ibooks. 
But that is just what I read.

On Tuesday, January 20, 2004, at 03:16  PM, Nicholas wrote:

Hello Everyone,

I downloaded and installed the
mysql-standard-4.0.17-apple-darwin6.8-powerpc.dmg
file from the mysql web site to my ibook running
Mac OSX Version 10.1.5 on it.
I read the documentation on the mysql site and it
said to do the following next:
shell cd /usr/local/mysql
shell sudo ./bin/mysqld_safe
(Enter your password, if necessary)
The server started running at this point but less then
one second later, the server killed itself. I looked
at the localhost.err file in the /usr/local/mysql/data
directory and it had the following text in it:
040117 10:35:09 mysqld started
dyld: /usr/local/mysql/bin/mysqld Undefined symbols:
/usr/local/mysql/bin/mysqld undefined reference to _localtime_r
expected to be defined in /usr/lib/libSystem.B.dylib
040117 10:35:10 mysqld ended
Has anyone else experienced this problem on their Mac?
Does anyone know how I can correct this issue?
I know that my version of OSX is a little old
as far as the mysql web site documentation says but
the installation process went smoothly without any
errors at all coming up.
Any help on this matter would be greatly appreciated.

Thanks in advance.

~~Nick



___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
--
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 on indexed columns

[Dan Nelson]

In the last episode (Jan 20), Balazs Rauznitz said:
 On Tue, Jan 20, 2004 at 08:23:32PM +0100, Aleksandar Bradaric wrote:
 
 mysql explain  select count(*) from sex where id459000 and id =46 and sex = 
 'M';
 +---+--++---+-+---++-+
 | table | type | possible_keys  | key   | key_len | ref   | 
 rows   | Extra   |
 +---+--++---+-+---++-+
 | sex   | ref  | id_index,sex_index,sex,id,sex_both | sex_index |   2 | const | 
 506151 | Using where |
 +---+--++---+-+---++-+
 1 row in set (0.00 sec)

Try hinting it to use the compound index, or maybe recreate the index
with the fields in the other order (sex,id), so it can do a straight
range scan.  With an (id,sex) index, it should be able to do a range
scan but still has to discard half the records.

What's the difference between id, and id_index, and sex and sex_index? 
SHOW KEYS FROM sex will list the subparts of all the keys, and their
cardinality (you may have to ANALYZE the table first).

-- 
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: MySQL Server Question

[Nicholas]

Ok, thanks for the info.  As I said below I am using 
Mac OSX version 10.1.5 which has been patched numerous times 
from the apple web site.  I don't need to speed anything up 
on my ibook since the database I am going to create is not 
going to be used by multiple users.  

~~Nick



 --- On Tue 01/20, sulewski  [EMAIL PROTECTED]  wrote:
From: sulewski [mailto: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
Date: Tue, 20 Jan 2004 15:20:33 -0500
Subject: Re: MySQL Server Question

I'm running on 10.2.8 just fine.  I hear panther speeds up the ibooks. brBut that is 
just what I read.brbrbrOn Tuesday, January 20, 2004, at 03:16  PM, Nicholas 
wrote:brbrbr Hello Everyone,brbr I downloaded and installed thebr 
mysql-standard-4.0.17-apple-darwin6.8-powerpc.dmgbr file from the mysql web site to 
my ibook runningbr Mac OSX Version 10.1.5 on it.brbr I read the documentation 
on the mysql site and itbr said to do the following next:brbr shell cd 
/usr/local/mysqlbr shell sudo ./bin/mysqld_safebr (Enter your password, if 
necessary)brbr The server started running at this point but less thenbr one 
second later, the server killed itself. I lookedbr at the localhost.err file in the 
/usr/local/mysql/databr directory and it had the following text in it:brbr 
040117 10:35:09 mysqld startedbr dyld: /usr/local/mysql/bin/mysqld Undefined 
symbols:br /usr/local/mysql/bin/mysqld undefined reference to _localtime_rbr 
expected to be defined in /usr/lib/libSystem.B.dylibbr 040117 10:35:10 mysqld 
endedbrbr Has anyone else experienced this problem on their Mac?br Does 
anyone know how I can correct this issue?brbr I know that my version of OSX is a 
little oldbr as far as the mysql web site documentation says butbr the 
installation process went smoothly without anybr errors at all coming up.brbr 
Any help on this matter would be greatly appreciated.brbr Thanks in 
advance.brbr ~~Nickbrbrbrbrbr 
___br Join Excite! - 
http://www.excite.combr The most personalized portal on the Web!brbr -- br 
MySQL General Mailing Listbr For list archives: http://lists.mysql.com/mysqlbr 
To unsubscribe:br http://lists.mysql.com/[EMAIL PROTECTED]brbrbr

___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!

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

Stumped on a query

[Chris Boget]

I'm working with data that has not been normalized.  If it were
up to me and I had the time, I'd go in and change all the code
so that the data were normalized, but right now that's not an
option.

The data I'm working with looks like this:

table1.columnA = '1;3;4;6;8;9;12;13;14;15';

table2.columnA = '3';
table2.columnB = 'this';

I need to write a query that will do something along these lines:

SELECT * FROM table1, table2 WHERE
  table2.columnB = 'this'
AND 
  table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );

The above query obviously does not work.  But I hope you get the
idea of what I am trying to do.  I won't have the value of '3' to specify
in the WHERE part of the query as that value is going to be a result
of the set.  But using that value, I need to get the records from table1
where the columnA value of table2 is part of the columnA value of
table1.

thnx,
Chris



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

Re: MYSQL Database

[Douglas Sims]

Hi

You should check out: http://onlamp.com/  L.A.M.P. 
(Linux/Apache/MySQL/Perl(or PHP) are becoming the de facto standards for 
web-based applications, I think far eclipsing Java (JSP/Servlets) and 
Microsoft ASP/VB.

Unlike Java (which is driven to a large degree by Sun's promotion) and 
ASP (heavily promoted by MS), LAMP has become so widespread because it's 
just really good (and 
cheap).http://news.netcraft.com/archives/web_server_survey.html

I personally prefer to program in Perl, which is The Coolest Language 
Ever Invented, although Java has advantages.  C/C++ for server-side 
programming are great if you have lots of money and time and are 
concerned handling massive amounts of traffic.  ASP (Visual Basic) is 
really terrible.  Although I do a lot of work in it, I don't like it.  
It does not have the same semantic versatility of C-based languages like 
Perl.  And regular expressions in VB are a heinous pastiche of the true 
elegance of regular expressions in Perl.

I'm sure many people will disagree vociferously with my opinions here 
and they may have good points also, which I have neglected.  Programming 
languages are like indentation styles - you can do a very fine job with 
different ones, and yet most people become very particular about their 
own styles and hate working with others.  One might also dispute my 
argument that LAMP is far more widespread than ASP or Java as the survey 
I cited doesn't really consider server-side programming language, just 
servers, but I suspect far more people are running mysql/[php|perl] on 
linux than anything else and the server-side languages used probably 
mirror this.  Perhaps someone else can offer better statistics.

In short, I would use Linux/Apache/MySQL/Perl.

Now I'm afraid I will have roused the VB or Java crowds.  Perhaps I 
should sign this with an assumed name?

/Alfred E. Neuman/





Seena Blace wrote:

Hi,
I'm new to this group.I would like to know which frontend tools be good tuned with Mysql database like php,perl etc?
I want to develop one application on linux on mysql database which eventually would be webbased.Please suggest what combination would be good.
thx
-Seena 



-
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
 



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

Cross dependency found in OUTER JOIN

[Verdon Vaillancourt]

I hope the question is OK :)

The first 4 queries work fine, the last causes the error. I'm probably
missing something obvious, but this is complex for me.

TIA for any tips,
verdon

GODD


SELECT 
volunteers.volunteer_id,volunteers.first,volunteers.middle,volunteers.last,v
olunteers.organization FROM volunteers  WHERE volunteers.volunteer_id  0
ORDER BY volunteer_id

SELECT 
volunteers.volunteer_id,volunteers.first,volunteers.middle,volunteers.last,v
olunteers.organization FROM volunteers  RIGHT JOIN availability ON
volunteers.volunteer_id = availability.volunteer_id  WHERE
volunteers.volunteer_id  0 AND availability.start_time = 0 AND
availability.end_time = 0 AND availability.day_of_week = 1   ORDER BY
volunteer_id 


SELECT 
volunteers.volunteer_id,volunteers.first,volunteers.middle,volunteers.last,v
olunteers.organization FROM volunteer_skills RIGHT JOIN volunteers ON
volunteer_skills.volunteer_id = volunteers.volunteer_id  RIGHT JOIN
phone_numbers ON volunteers.volunteer_id = phone_numbers.volunteer_id  WHERE
volunteers.volunteer_id  0 AND ( string_id = 6 and skill_level = 3)  AND
phone_numbers.number LIKE '%345%' GROUP BY volunteer_skills.volunteer_id
ORDER BY volunteer_id


SELECT 
volunteers.volunteer_id,volunteers.first,volunteers.middle,volunteers.last,v
olunteers.organization FROM volunteer_skills RIGHT JOIN volunteers ON
volunteer_skills.volunteer_id = volunteers.volunteer_id  RIGHT JOIN
availability ON volunteers.volunteer_id = availability.volunteer_id  WHERE
volunteers.volunteer_id  0 AND ( string_id = 6 and skill_level = 2)  AND
availability.start_time = 0 AND availability.end_time = 0 AND
availability.day_of_week = 1  GROUP BY volunteer_skills.volunteer_id  ORDER
BY volunteer_id 



NO GOOD


SELECT 
volunteers.volunteer_id,volunteers.first,volunteers.middle,volunteers.last,v
olunteers.organization FROM volunteers  RIGHT JOIN phone_numbers ON
volunteers.volunteer_id = phone_numbers.volunteer_id  RIGHT JOIN
availability ON volunteers.volunteer_id = availability.volunteer_id  WHERE
volunteers.volunteer_id  0 AND phone_numbers.number LIKE '%345%' AND
availability.start_time = 0 AND availability.end_time = 0 AND
availability.day_of_week = 1   ORDER BY volunteer_id

SQL Error: Cross dependency found in OUTER JOIN.  Examine your ON conditions 


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

RE: Slow query times

[Matt Griffin]

I couldn't say without some hard benchmarks.  Just keep in mind that an
index still only breaks your data up into fairly large compartments which
must be scanned.  It's like this:
rows scanned = # rows / (log2(# of options))
At some threshold of log2(# of options) it's faster to scan the whole table.
MySQL usually detects this and ignores the index, especially if another
index was already used to break down the number of rows.

Matt

-Original Message-
From: Balazs Rauznitz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 20, 2004 2:46 PM
To: Matt Griffin
Cc: [EMAIL PROTECTED]
Subject: Re: Slow query times



You are perfectly correct. Without the index on sex query times are
lightning fast.

I don't quite get you explanation why; where can I read up on how
queries are done with and without an index ?

Can one make a generalization that unless the number of different
values in a column is less than log(number of rows), it does not make
sense to use an index ?

Thanks,

Balazs

On Tue, Jan 20, 2004 at 02:33:28PM -0500, Matt Griffin wrote:
 I wouldn't imagine that creating an index on a column with only two
possible
 values could make things any faster.  You only get a maximum 50% reduction
 in row scans, rather than the normal log based reduction with a random
value
 distribution.  In addition, you contend with the overhead of using the
 index.  What is the runtime without the index?

 Matt

 -Original Message-
 From: Balazs Rauznitz [mailto:[EMAIL PROTECTED]
 Sent: Monday, January 19, 2004 12:53 PM
 To: [EMAIL PROTECTED]
 Subject: Slow query times



 While doing some benchmarks the other day, I saw surprisingly slow
 query results on columns that were indexed.

 Here's the table definition:

 create table sex (
 id integer,
 sex char(1));
 create index id_index on sex (id);
 create index sex_index on sex (sex);

 Then I loaded a million rows, id was from 1 to 1_000_000, sex was
 randomly 'F' or 'M'.

 When searching on 'id' everything is snappy:

 mysql select count(*) from sex where id459000 and id =46;
 +--+
 | count(*) |
 +--+
 | 1000 |
 +--+
 1 row in set (0.00 sec)

 However when the 'sex' column is involved:

 mysql select count(*) from sex where id459000 and id =46 and sex =
 'M';
 +--+
 | count(*) |
 +--+
 |  504 |
 +--+
 1 row in set (5.09 sec)

 Any way to make this faster ?

 I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.

 Insert jokes about sex making MySQL slow here

 Thanks,

 Balazs



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


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

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


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

Re: MYSQL Database

[sulewski]

I'm a java person and I'm happy to say you didn't rouse me. There are 
many fine quality in lamp and java. I don't know PHP but I've seen some 
really nice apps written in php.  It looks like a nice clean language 
and very nice for web development.

On Tuesday, January 20, 2004, at 03:34  PM, Douglas Sims wrote:

Hi

You should check out: http://onlamp.com/  L.A.M.P. 
(Linux/Apache/MySQL/Perl(or PHP) are becoming the de facto standards 
for web-based applications, I think far eclipsing Java (JSP/Servlets) 
and Microsoft ASP/VB.

Unlike Java (which is driven to a large degree by Sun's promotion) and 
ASP (heavily promoted by MS), LAMP has become so widespread because 
it's just really good (and 
cheap).http://news.netcraft.com/archives/web_server_survey.html

I personally prefer to program in Perl, which is The Coolest Language 
Ever Invented, although Java has advantages.  C/C++ for server-side 
programming are great if you have lots of money and time and are 
concerned handling massive amounts of traffic.  ASP (Visual Basic) is 
really terrible.  Although I do a lot of work in it, I don't like it.  
It does not have the same semantic versatility of C-based languages 
like Perl.  And regular expressions in VB are a heinous pastiche of 
the true elegance of regular expressions in Perl.

I'm sure many people will disagree vociferously with my opinions here 
and they may have good points also, which I have neglected.  
Programming languages are like indentation styles - you can do a very 
fine job with different ones, and yet most people become very 
particular about their own styles and hate working with others.  One 
might also dispute my argument that LAMP is far more widespread than 
ASP or Java as the survey I cited doesn't really consider server-side 
programming language, just servers, but I suspect far more people are 
running mysql/[php|perl] on linux than anything else and the 
server-side languages used probably mirror this.  Perhaps someone else 
can offer better statistics.

In short, I would use Linux/Apache/MySQL/Perl.

Now I'm afraid I will have roused the VB or Java crowds.  Perhaps I 
should sign this with an assumed name?

/Alfred E. Neuman/





Seena Blace wrote:

Hi,
I'm new to this group.I would like to know which frontend tools be 
good tuned with Mysql database like php,perl etc?
I want to develop one application on linux on mysql database which 
eventually would be webbased.Please suggest what combination would be 
good.
thx
-Seena

-
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes


--
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: Stumped on a query

[Tobias Asplund]

On Tue, 20 Jan 2004, Chris Boget wrote:

 The data I'm working with looks like this:

 table1.columnA = '1;3;4;6;8;9;12;13;14;15';

 table2.columnA = '3';
 table2.columnB = 'this';

 I need to write a query that will do something along these lines:

 SELECT * FROM table1, table2 WHERE
   table2.columnB = 'this'
 AND
   table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );


 AND
INSTR(table1.columnA, table2.columnA)

You can find more info about the INSTR function at:
http://www.mysql.com/doc/en/String_functions.html#IDX1189


cheers,
Tobias

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

RE: Slow query times

[Matt Griffin]

Ignore that silly equation.  I tried to simplify and ended up with something
mathematically ridiculous.  I'm sure someone can come up with a more
accurate simplification.

Matt


-Original Message-
From: Matt Griffin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 20, 2004 3:46 PM
To: 'Balazs Rauznitz'
Cc: [EMAIL PROTECTED]
Subject: RE: Slow query times


I couldn't say without some hard benchmarks.  Just keep in mind that an
index still only breaks your data up into fairly large compartments which
must be scanned.  It's like this:
rows scanned = # rows / (log2(# of options))
At some threshold of log2(# of options) it's faster to scan the whole table.
MySQL usually detects this and ignores the index, especially if another
index was already used to break down the number of rows.

Matt

-Original Message-
From: Balazs Rauznitz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 20, 2004 2:46 PM
To: Matt Griffin
Cc: [EMAIL PROTECTED]
Subject: Re: Slow query times



You are perfectly correct. Without the index on sex query times are
lightning fast.

I don't quite get you explanation why; where can I read up on how
queries are done with and without an index ?

Can one make a generalization that unless the number of different
values in a column is less than log(number of rows), it does not make
sense to use an index ?

Thanks,

Balazs

On Tue, Jan 20, 2004 at 02:33:28PM -0500, Matt Griffin wrote:
 I wouldn't imagine that creating an index on a column with only two
possible
 values could make things any faster.  You only get a maximum 50% reduction
 in row scans, rather than the normal log based reduction with a random
value
 distribution.  In addition, you contend with the overhead of using the
 index.  What is the runtime without the index?

 Matt

 -Original Message-
 From: Balazs Rauznitz [mailto:[EMAIL PROTECTED]
 Sent: Monday, January 19, 2004 12:53 PM
 To: [EMAIL PROTECTED]
 Subject: Slow query times



 While doing some benchmarks the other day, I saw surprisingly slow
 query results on columns that were indexed.

 Here's the table definition:

 create table sex (
 id integer,
 sex char(1));
 create index id_index on sex (id);
 create index sex_index on sex (sex);

 Then I loaded a million rows, id was from 1 to 1_000_000, sex was
 randomly 'F' or 'M'.

 When searching on 'id' everything is snappy:

 mysql select count(*) from sex where id459000 and id =46;
 +--+
 | count(*) |
 +--+
 | 1000 |
 +--+
 1 row in set (0.00 sec)

 However when the 'sex' column is involved:

 mysql select count(*) from sex where id459000 and id =46 and sex =
 'M';
 +--+
 | count(*) |
 +--+
 |  504 |
 +--+
 1 row in set (5.09 sec)

 Any way to make this faster ?

 I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.

 Insert jokes about sex making MySQL slow here

 Thanks,

 Balazs



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


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

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


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


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

many queries versus big joins

[Stephen Fromm]

In general, is it more efficient to do many queries or one large query
with many joins?


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

Re: Stumped on a query

[Fred van Engen]

On Tue, Jan 20, 2004 at 03:02:45PM -0600, Chris Boget wrote:
   The data I'm working with looks like this:
   table1.columnA = '1;3;4;6;8;9;12;13;14;15';
   table2.columnA = '3';
   table2.columnB = 'this';
   I need to write a query that will do something along these lines:
   SELECT * FROM table1, table2 WHERE
 table2.columnB = 'this'
   AND
 table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );
   AND
  INSTR(table1.columnA, table2.columnA)
 
 Well, the only problem with going this route is that if table1.columnA 's value
 was, instead, '1,4,6,8,9,13,14,15', that row would still match (when it really
 shouldn't) because of the '13' within the string.  The '3' from table2.columnA
 is part of the table1.columnA string.
 

Try this:

INSTR(CONCAT(';',table1.columnA,';'),CONCAT(';',table2.columnA,';'))

Note that MySQL can't make use of any index here, so it will check all
n x m combinations, with n and m the number of records in table1 and
table2. You really want to do this for small tables only.

Consider an additional table to hold each of your 1, 3, 4, 6, ...
values together with the unique id of a record in table1.


Regards,

Fred.

-- 
Fred van Engen  XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC  Almere
fax: +31 36 5462424 The Netherlands

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

Re: Stumped on a query

[Tobias Asplund]

On Tue, 20 Jan 2004, Chris Boget wrote:

   The data I'm working with looks like this:
   table1.columnA = '1;3;4;6;8;9;12;13;14;15';
   table2.columnA = '3';
   table2.columnB = 'this';
   I need to write a query that will do something along these lines:
   SELECT * FROM table1, table2 WHERE
 table2.columnB = 'this'
   AND
 table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );
   AND
  INSTR(table1.columnA, table2.columnA)

 Well, the only problem with going this route is that if table1.columnA 's value
 was, instead, '1,4,6,8,9,13,14,15', that row would still match (when it really
 shouldn't) because of the '13' within the string.  The '3' from table2.columnA
 is part of the table1.columnA string.

Oops

INSTR(a, CONCAT(',', b, ',')) OR
INSTR(a, CONCAT(b, ',')) = 1 OR
INSTR(a, CONCAT(',', b)) = CHAR_LENGTH(a) - CHAR_LENGTH(b)

might work better, sorry about that (replace a and b with appropriate
columns).

The first line checks if ,column, exists, which will be in all cases when
it's not either first or last.
The second line checks if it exists first in the commaseparated list, and
the last line checks if it exists last.
There's probably an easier way to do this, but since I submitted a faulty
reply I should atleast make up for it ;)

cheers,
Tobias

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

Re: Improving a query

[Roger Baklund]

* Noamn
[...]

Please reply to the list. :)

 Adding the composite index doesn't make any difference as far as
 I can see.
 Isn't there a way of forcing a specific index to be used?

Yes. From mysql version 4.0.9 you can write FORCE INDEX, for earlier
versions you can try USE INDEX.

URL: http://www.mysql.com/doc/en/SELECT.html 

 Does all the above have anything to do with the
 'select_full_join' variable?

Well... the 'select_full_join' variable should increase every time you do a
join without keys... but check below.

 Presumably this gets increased everytime a table has the type ALL, and
 that's what I want to avoid.

Note that the ALL probably is because there are very few rows in the status
table.

URL: http://www.mysql.com/doc/en/How_to_avoid_table_scan.html 

What execution times are you looking at? Is it very slow?

--
Roger


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

RE: Stumped on a query

[Ted . A . Gifford]

Why not:

SELECT * FROM table1, table2 WHERE
table2.columnB = 'this' AND
FIND_IN_SET(table2.columnA, REPLACE(table1.columnA,';',',') )  0

Documented here: http://www.mysql.com/doc/en/String_functions.html

Ted Gifford


-Original Message-
From: Tobias Asplund [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 20, 2004 1:26 PM
To: Chris Boget
Cc: [EMAIL PROTECTED]; MySQL
Subject: Re: Stumped on a query

On Tue, 20 Jan 2004, Chris Boget wrote:

   The data I'm working with looks like this:
   table1.columnA = '1;3;4;6;8;9;12;13;14;15';
   table2.columnA = '3';
   table2.columnB = 'this';
   I need to write a query that will do something along these lines:
   SELECT * FROM table1, table2 WHERE
 table2.columnB = 'this'
   AND
 table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );
   AND
  INSTR(table1.columnA, table2.columnA)

 Well, the only problem with going this route is that if table1.columnA 's
value
 was, instead, '1,4,6,8,9,13,14,15', that row would still match (when it
really
 shouldn't) because of the '13' within the string.  The '3' from
table2.columnA
 is part of the table1.columnA string.

Oops

INSTR(a, CONCAT(',', b, ',')) OR
INSTR(a, CONCAT(b, ',')) = 1 OR
INSTR(a, CONCAT(',', b)) = CHAR_LENGTH(a) - CHAR_LENGTH(b)

might work better, sorry about that (replace a and b with appropriate
columns).

The first line checks if ,column, exists, which will be in all cases when
it's not either first or last.
The second line checks if it exists first in the commaseparated list, and
the last line checks if it exists last.
There's probably an easier way to do this, but since I submitted a faulty
reply I should atleast make up for it ;)

cheers,
Tobias

-- 
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: Starting up MySQL :(

[walt]

Annie Law wrote:
 
 Hi,
 
 I would appreciate any help on the following. I have searched the mailing list for 
 information But have not come up with a solution for my situation which has been 
 frustrating. I think I need some things need to be clarified.
 
 The OS that is running is RedHat V.9.0 linux. MySQL was installed when RedHat V.9.0 
 was installed. Here are the packages that that I have installed
 
snip

Annie,
There should be a startup/stop script for mysql in /etc/init.d/ . Try
running
/etc/init.d/mysql stop

After that, check for mysql with ps -eaf |grep -i mysq . If you find a
process running, try to kill it with kill -15 pid. You may have to use
kill -9 pid if the processes wont go away with -15. You should be able
to start mysql using /etc/init.d/mysql start. Once the server is
running, su to the mysql user and you should be able to log in to the
database using mysql.


walt

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

unsupported driver trouble

[tait sanders]

hi all,

not sure if this is the appropriate place to post this -- so please 
point me in the right direction if not here...

I have a G4 with os10.3.2 server, mysql v4.0.16, and MoveableType 2.6

I'm trying to initialise the moveabletype system by running mt-load.cgi 
and am recieving the following error:

Unsupported driver MT::ObjectDriver::DBI::mysql: Can't locate DBI.pm 
in @INC...

I've checked in my mt.cfg file and I have added:
ObjectDriver DBI::mysql
Database /var/mysql/mtweblog
DBUser labadmin
so I don't understand what's going on.

please help

ta
tait


tait sanders
computer technician
sir robert webster bldg
trc
unsw

Re: Slow query times

[mos]

At 11:52 AM 1/19/2004, you wrote:

While doing some benchmarks the other day, I saw surprisingly slow
query results on columns that were indexed.
It's because MySQL won't use the Sex_Index index because the value that you 
are searching for (Namely F or M) accounts for more than 30% of the 
data, probably 50%. So if F appeared in say 20% of the data, then the 
index would be used. MYSQL does this because the overhead in accessing the 
index for more than 30% of the data is greater than just accessing the data 
alone. It has to do at least 1 I/O to get the index entry, and another to 
get the corresponding data record.

From the manual: http://www.mysql.com/doc/en/MySQL_indexes.html
Note that sometime MySQL will not use an index, even if one is available. 
One instance of this is when use of the index would require MySQL to access 
more than 30% of the rows in the table. (In this case a table scan is 
probably much faster, as it will require many fewer seeks.) However, if 
such a query uses LIMIT to only retrieve part of the rows, MySQL will use 
an index anyway, as it can much more quickly find the few rows to return in 
the result. 

You can try a LIMIT 100 to force it to use an index.
Of course for what you are trying to accomplish, a
select count(*) as Num from Sex group by Sex

would be much faster.

Mike 



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

Re: many queries versus big joins

[mos]

At 02:54 PM 1/20/2004, you wrote:
In general, is it more efficient to do many queries or one large query
with many joins?
Good question. :)

I would break it down into smaller queries and use a loop because a large 
query, would consume a huge amount of memory and a join needs to create a 
temporary table so that would consume a large amount of disk space. If the 
queries are more than 5-10k rows, I would definitely break them into 
smaller queries.  Doing a large join with hundreds of thousands of rows, or 
millions of rows, would put a strain on the server for both I/O and CPU 
that it would stop others from being able to access it.

Mike 



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

query syntax help

[Mike Blezien]

Hello all,

I've been looking at this SQL query a dozen times or more, but keep getting a 
syntax error message, Query:

SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS 
name,aw.siteid,ai.email,as.username,as.status
FROM affiliate_info ai,affiliate_signup as,affiliate_website aw
WHERE aw.siteid = 1000
AND ai.affilid = as.affilid AND aw.affilid = ai.affilid

what is wrong with this query syntax ?? the syntax error is suppose to be in 
this area:
`affiliate_website aw WHERE aw.siteid = 1000`

MySQL version 4.0.15 w/InnoDB tables

TIA,

--
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query syntax help

[Fred van Engen]

On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote:
 I've been looking at this SQL query a dozen times or more, but keep getting 
 a syntax error message, Query:
 
 SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS 
 name,aw.siteid,ai.email,as.username,as.status
 FROM affiliate_info ai,affiliate_signup as,affiliate_website aw

AS is a reserved word.

 WHERE aw.siteid = 1000
 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid
 
 what is wrong with this query syntax ?? the syntax error is suppose to be 
 in this area:
 `affiliate_website aw WHERE aw.siteid = 1000`
 

A bit before that.


Regards,

Fred.

-- 
Fred van Engen  XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC  Almere
fax: +31 36 5462424 The Netherlands

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

Re: query syntax help

[Jochem van Dieten]

Mike Blezien said:

 I've been looking at this SQL query a dozen times or more, but keep
 getting a  syntax error message, Query:

 SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS
 name,aw.siteid,ai.email,as.username,as.status
 FROM affiliate_info ai,affiliate_signup as,affiliate_website aw
  ^^
 reserved word

 WHERE aw.siteid = 1000
 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid

Jochem





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

Increment value

[Mike Mapsnac]

In the table value login_count is int(4). For example if value login_count 
equal to 3 and each time user login I  want to increment by one.

update customer set login_count='how? where id=12121212;

What function I should use in order to icrement the value?

_
Check out the coupons and bargains on MSN Offers! 
http://shopping.msn.com/softcontent/softcontent.aspx?scmId=1418

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

Re: query syntax help

[Mike Blezien]

Thx's Fred...

as soon as I sent the email and re-read it again... I spotted the 'as' alias 
table reference to the table, was actual a reserved word,..causing the error :)

thx's again.

--
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Fred van Engen wrote:
On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote:

I've been looking at this SQL query a dozen times or more, but keep getting 
a syntax error message, Query:

SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS 
name,aw.siteid,ai.email,as.username,as.status
FROM affiliate_info ai,affiliate_signup as,affiliate_website aw


AS is a reserved word.


WHERE aw.siteid = 1000
AND ai.affilid = as.affilid AND aw.affilid = ai.affilid
what is wrong with this query syntax ?? the syntax error is suppose to be 
in this area:
`affiliate_website aw WHERE aw.siteid = 1000`



A bit before that.

Regards,

Fred.



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

Re: Too slow recovering mysqldump files

[Mikel -]

Here we are my options:

set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M
I follow the example that came here: 
http://www.mysql.com/doc/en/InnoDB_start.html;

Greetings and best regards

Mikel


From: Heikki Tuuri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: Too slow recovering mysqldump files
Date: Mon, 19 Jan 2004 22:44:50 +0200
Mikel,

have you set the size of the InnoDB log files as recommended in the manual?

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/

- Original Message -
From: Mikel - [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Monday, January 19, 2004 7:25 PM
Subject: Too slow recovering mysqldump files
 Hi list,  does anyone know a faster way to recover a mysqldump file 
cause
 When I recovered one dump file it took 26 hours ! to finish, I think 
it's
 too slow.
 Thnx in advanced, greetings

 MySQL server 3.23.58
 RedHat 7.3
 4GB RAM
 2 scsi disk via fiber channel (333GB each)
 2 processor Xeon 1.6GHZ

 dump file size: 2.5 GB
 ibdata: 11GB
 innodb tables
 key_buffer=850M
 innodb_buffer_pool_size=850M
 table_cache=1500

 _
 MSN. Más Útil Cada Día  http://www.msn.es/intmap/


 --
 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]
_
MSN. Más Útil Cada Día  http://www.msn.es/intmap/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Increment value

[Tobias Asplund]

On Tue, 20 Jan 2004, Mike Mapsnac wrote:

 In the table value login_count is int(4). For example if value login_count
 equal to 3 and each time user login I  want to increment by one.

 update customer set login_count='how? where id=12121212;

SET login_count = login_count + 1


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

Re: Increment value

[Mike Mapsnac]

I know that. I'm lookign for mysql function that will increment the value.




From: Tobias Asplund [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Mike Mapsnac [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: Increment value Date: Tue, 20 Jan 2004 23:36:22 +0100 (CET)
On Tue, 20 Jan 2004, Mike Mapsnac wrote:

 In the table value login_count is int(4). For example if value 
login_count
 equal to 3 and each time user login I  want to increment by one.

 update customer set login_count='how? where id=12121212;

SET login_count = login_count + 1

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Get a FREE online virus check for your PC here, from McAfee. 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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

Re: Repeated 100% CPU problem in FreeBSD

[Arnaud Pignard]

Hi Ken,

I have compile MySQL with this param : -D SKIP_DNS_CHECK

As i have upgrade with portupgrade and forget to repass param, i have put 
dns off on my.cnf

I will check if make -D SKIP_DNS_CHECK work. If yes, the problem is not dns 
resolve.
I hope it's bad param pass to makefile :)

Thanks for answer.

Best Regards,

At 16:59 20/01/2004, you wrote:
Hi Arnaud,
   I did not see an answer to this so; I think the info you need is
here http://jeremy.zawodny.com/blog/archives/000203.html and here
http://jeremy.zawodny.com/blog/archives/000697.html
But I think you want skip-name-resolve to be specified in my.cnf.

Ken

I have included the relevant section from Jeremeys blog:
___
1. Non-thread safe DNS Lookups
Certain operations are not thread-safe on FreeBSD. A fine example of
that is gethostbyname(), which MySQL calls to convert host names in to
IP addresses. Usually this happens for each new connection to the
server and whenever MySQL needs to contact another machine--typically
a replication slave connecting to its master.
Based on our testing, the only truly safe way to operate is to use
the --skip-name-resolve flag for starting mysqld AND specifying the IP
address of the master instead of the hostname. That virtually
eliminates the need for MySQL to call gethostbyname().
The symptom of this problem is that the mysqld will consume all the
available CPU time even when there are few (if any) queries running.
You can try and kill -6 the mysqld process and then run it thru gdb to
get a backtrace. You'll likely see something like this:
#0  0x829c94c in _thread_kern_sched_state_unlock () at
./cp/tinfo2.cc:300
#1  0x829c0e0 in _thread_kern_sched () at ./cp/tinfo2.cc:300
#2  0x829c787 in _thread_kern_sched_state () at ./cp/tinfo2.cc:300
#3  0x82c5fdc in kevent () at ./cp/tinfo2.cc:300
#4  0x82c5a4f in res_send () at ./cp/tinfo2.cc:300
#5  0x82a4308 in res_query () at ./cp/tinfo2.cc:300
#6  0x82a4737 in res_querydomain () at ./cp/tinfo2.cc:300
#7  0x82a44bb in res_search () at ./cp/tinfo2.cc:300
#8  0x82a9a00 in _gethostbydnsname () at ./cp/tinfo2.cc:300
#9  0x82a8739 in gethostbyname2 () at ./cp/tinfo2.cc:300
#10 0x82a86d4 in gethostbyname () at ./cp/tinfo2.cc:300
#11 0x8275fc4 in my_gethostbyname_r (
name=0x1b5f79a8 your_hostanme, result=0x9fa659b8,
buffer=0x9fa651b8 \032, buflen=2048, h_errnop=0x9fa651b0)
at my_gethostbyname.c:108
#12 0x80d6fbd in mc_mysql_connect ()
#13 0x80d6b37 in mc_mysql_reconnect ()
#14 0x80d4506 in safe_reconnect ()
#15 0x80d3fb8 in handle_slave ()
#16 0x828ffa5 in _thread_start () at ./cp/tinfo2.cc:300
#17 0x0 in ?? ()
If you see that, get rid of DNS lookups.


- Original Message -
From: Arnaud Pignard [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, January 16, 2004 7:06 PM
Subject: Re: Repeated 100% CPU problem in FreeBSD
 Hi !

 Anyone find a solution for this problem ?

 We have 2 mysql server with same problem. Happen like every 1-2
months.

 Regards,


 --
 Arnaud Pignard ([EMAIL PROTECTED])
 Frontier Online - Opérateur Internet



 --
 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: MYSQL Database

[Jochem van Dieten]

Douglas Sims wrote:
You should check out: http://onlamp.com/  L.A.M.P. 
(Linux/Apache/MySQL/Perl(or PHP)
Or Linux/Apache/Middleware/PostgreSQL ;-)

Jochem

--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

how to access iblog file?

[amrin]

Hi all,

i have run mysql ver 8.21 distrib 3.23.43 for win. i use innodb
architecture in my database. i have trouble access iblog file. Anyone know
how to access iblog file ?

Thanks in advance.

-M. Amrin-


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

Re: MYSQL Database

[Sam Vilain]

Lynch 'im!!!

:-)

On Wed, 21 Jan 2004 13:33, Jochem van Dieten wrote;

   Douglas Sims wrote:

You should check out: http://onlamp.com/  L.A.M.P. 
(Linux/Apache/MySQL/Perl(or PHP)
   
   Or Linux/Apache/Middleware/PostgreSQL ;-)
   
   Jochem
   
   -- 
   I don't get it
   immigrants don't work
   and steal our jobs
- Loesje
   
   
   -- 
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
   
   
   

-- 
Sam Vilain, [EMAIL PROTECTED]

  Only the ignorant man becomes angry.  The wise man understands.
 --Indian wisdom.


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

unable to update root password or connect.

[smrtalec]

This is a new install of mysql 3.23 on a SuSE8.0 Box. I installd everythign via suse 
rpms. I ran mysql_install_db  when I to run the update root password I get the 
following error. I'm logged into the server via ssh as root while exicuting the 
commands. any ideas.

inglewood:/etc # mysqladmin -u root -h inglewood -p password '**'
Enter password:
mysqladmin: connect to server at 'inglewood' failed
error: 'Host 'inglewood.studio3arc.com' is not allowed to connect to this MySQL server'




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

InnoDB key adding slowness

[Keith Thompson]

Hi all,

I decided I needed another index in an InnoDB table that has about
25 million rows (each 80 bytes long).  As I've done dozens of times
with MyISAM tables, I did a simple alter table:
alter table WMH_CHK_a add key JoinTrans (TransID)

This has been running for 14 hours and I have no idea how much
longer it's going to take, but it's getting darned frustrating
as it's preventing lots of other work.

This is not a complicated table and it is running on a 4-CPU Sun
server with a high-speed disk setup.  The server has 4GB memory
and I've got all of the InnoDB parameters set up at a moderate
level (such as a 1GB buffer_pool_size), etc.  It is otherwise not
very busy on other activity, so it should have most of the system's
resources available to it.

Any ideas what is taking this so long?  This would not have
taken more than a couple hours with a similar MyISAM table on
the same server (based on prior experience).

Thanks -keith



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

Re: InnoDB key adding slowness

[Keith Thompson]

I forgot to mention too that this is the 64bit MySQL 4.0.17
running on Solaris9.

Hi all,

I decided I needed another index in an InnoDB table that has about
25 million rows (each 80 bytes long).  As I've done dozens of times
with MyISAM tables, I did a simple alter table:
   alter table WMH_CHK_a add key JoinTrans (TransID)

This has been running for 14 hours and I have no idea how much
longer it's going to take, but it's getting darned frustrating
as it's preventing lots of other work.

This is not a complicated table and it is running on a 4-CPU Sun
server with a high-speed disk setup.  The server has 4GB memory
and I've got all of the InnoDB parameters set up at a moderate
level (such as a 1GB buffer_pool_size), etc.  It is otherwise not
very busy on other activity, so it should have most of the system's
resources available to it.

Any ideas what is taking this so long?  This would not have
taken more than a couple hours with a similar MyISAM table on
the same server (based on prior experience).

Thanks -keith



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

Ignore Replication Temp Tables

[Todd Burke]

Is there any way to disable replication of all temp tables using
replicate-ignore-table or some other means?  The names of the temp tables
are generated randomly by a script.  Thanks

Todd


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

Newbie Question About Passwords, etc.

[David Blomstrom]

I'm new to all of this, but I recently installed a
preconfigured package with Apache, PHP, MySQL and
PHPMyAdmin from ApacheFriends (XAMPP). I tried a
couple other packages, but they didn't work. XAMPP has
been wonderful so far, but I goofed when I tried to
connect MySQL with Dreamweaver.

I was reading a tutorial on connecting MySQL to
Dreamweaver at
http://www.macromedia.com/support/dreamweaver/ts/documents/mysql_config.htm#utilities

It said I need to assign MySQL a password - and that's
as far as I got.

I have two MySQL/Bin folders on my C drive - the
original at C:/XAMPP/mysql/bin/ and a copy of XAMPP's
MySQL folder at C:/mysql/bin/ I think I created the
second folder when I was first playing with XAMPP and
tyring to figure it out. I was going to delete it, but
I thought I might first use it for practice by
assigning it a password.

So I changed the directory to C:/mysql/bin/ and typed
in the following:

 mysqladmin -u root password LGSyM

Then I refreshed a page in PHPMyAdmin 2.5.3 that
illustrates some basic MySQL settings, but I got the
following error message:

phpMyAdmin tried to connect to the MySQL server, and
the server rejected the connection. You should check
the host, username and password in config.inc.php and
make sure that they correspond to the information
given by the administrator of the MySQL server.

Error

MySQL said:

#1045 - Access denied for user: '[EMAIL PROTECTED]'
(Using password: NO)

[Documentation]

I want to get some advice before I make the problem
any worse. Can I fix this by removing the password I
created? If so, how can I do that? Should I then
assign the password to the proper directory -
C:/XAMPP/mysql/bin/ ?

I'm not even sure if I need a password. I'm just using
MySQL on my computer. My sites are hosted by an ISP
with its own MySQL installation. But I'm not certain
if I can connect MySQL to Dreamweaver without a
password.

One more question... If I've created too big a mess, I
could probably just delete the entire XAMPP package
and start again. But will the password I created still
be in my computer somewhere?

Thanks!


__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus

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

Re: Starting up MySQL :(

[Ernesto Celis]

Annie Law wrote:
 First, I went to the MySQL website to look up the section
 on Unix Post-installation Procedures. I then tried the
 following as root and got the following result:

 --
 -

 Shell cd /usr/bin

 Shell mysql_install_db

If you do this as root then you only be able to start mysqld
as root

 To start mysqld at boot time you have to copy
 support-files/mysql.server to the right place for your
 system

RedHat has a init script for all the services that were
installed by anaconda, there is one for mysqld in
/etc/rc.d/init.d, if you have configured mysql to star at
the boot time this script uses /etc/my.cnf for the user and
passwords needed to start mysqld.


 You can start the MySQL daemon with:

 cd / ; /usr/bin/mysqld_safe 

Again if you was root when did mysql_install_db then you
should do this to start mysqld if this wasn't started at
boot time:
--
/usr/bin/safe_mysqld --user=root --password=YOURPASS 
--

If you are going to use mysql in a exposed server then is
very insecure run it as root.

Cheers

Usuario Linux #323140
celiseATprodigy.net.mx

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

Re: Slow query times

[Matt W]

Hi Balazs,

The likely answer is the one that nobody mentioned: it's an optimizer
bug in 4.0.16.  If you look at the EXPLAIN output for the second query,
it's probably using a ref type on the sex column, instead of the more
restrictive id index.  If so, that's the bug.  From
http://www.mysql.com/doc/en/News-4.0.17.html

Fixed optimizer bug, introduced in 4.0.16, when REF access plan was
preferred to more efficient RANGE on another column.

If that's the problem, upgrading will fix it. :-)


Matt


- Original Message -
From: Balazs Rauznitz
Sent: Monday, January 19, 2004 9:39 AM
Subject: Slow query times



 While doing some benchmarks the other day, I saw surprisingly slow
 query results on columns that were indexed.

 Here's the table definition:

 create table sex (
 id integer,
 sex char(1));
 create index id_index on sex (id);
 create index sex_index on sex (sex);

 Then I loaded a million rows, id was from 1 to 1_000_000, sex was
 randomly 'F' or 'M'.

 When searching on 'id' everything is snappy:

 mysql select count(*) from sex where id459000 and id =46;
 +--+
 | count(*) |
 +--+
 | 1000 |
 +--+
 1 row in set (0.00 sec)

 However when the 'sex' column is involved:

 mysql select count(*) from sex where id459000 and id =46 and
sex = 'M';
 +--+
 | count(*) |
 +--+
 |  504 |
 +--+
 1 row in set (5.09 sec)

 Any way to make this faster ?

 I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M
RAM.

 Insert jokes about sex making MySQL slow here ;-)

 Thanks,

 Balazs


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