mysqld freeze at 100% load

[Eli Spizzichino]

As soon as mysqld starts it goes 100% cpu. I can stop it,  there is no
mysql_safe running, and I ran
 mysqlcheck -A -a -c -o -g --auto-repair -u root -p
all is ok, syslog shows nothing important but I can't login to any account
with
mysql client it just sits there, using php it times out.
I don't know how to fix the urgent situation...

>How-To-Repeat:

Looks like it has been started after a release update.
I don't know if this bug is related since is mysql_safe
https://bugs.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+bug/105457/

>Fix:
I don't know

>Submitter-Id:  
>Originator:root
>Organization:
 
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:  
>Severity:  <[ non-critical | serious | critical ] (one line)>
>Priority:  <[ low | medium | high ] (one line)>
>Category:  mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
>Release:   mysql-5.1.41-3ubuntu12.6 ((Ubuntu))
>C compiler:gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3
>C++ compiler:  g++-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3
>Environment:

System: Linux mail.campiaperti.org 2.6.32.16-linode28 #1 SMP Sun Jul 25
21:32:42 UTC 2010 i686 GNU/Linux
Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Using built-in specs.
Target: i486-linux-gnu
Configured with: ../src/configure -v --with-pkgversion='Ubuntu
4.4.3-4ubuntu5' --with-bugurl=file:///usr/share/doc/gcc-4.4/R
Thread model: posix
gcc version 4.4.3 (Ubuntu 4.4.3-4ubuntu5)
Compilation info (call): CC='gcc'  CFLAGS='-O3 -DBIG_JOINS=1
-fno-strict-aliasing'  CXX='g++'  CXXFLAGS='-O3 -DBIG_JOINS=1


-- 
~ Eli ~

table lock

[Eli Shemer]

Hey there

 

I have an external script that generates a lot of data and dumps it into a
table. 

Prior to this ,it removes all the data in this specific table.

It requires both read and write privileges.

 

The web site on the other end reads all the data from the table and
generates standard html/css code with the information from the table.

 

How can I make all of the apache child processes wait for the external
script to complete its operation once it has started?

What would be the best way for this ?

 

Thanks!

 


Internal Virus Database is out-of-date.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007
18:55
 

RE: crash

[Eli Shemer]

Unfortuantely the server crashed and I had to manually boost it

It happened before and I would like to know why so it doesn’t again

 

I don’t have any innodb database on the system, only mysiam

 

Do you think adding ram might help ?

 

From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 13, 2008 8:05 AM
To: Eli Shemer
Subject: Re: crash

 

Hi,

The below information shows that innodb data get crashed and then started
the recovery of  data. During recovery of data there was a memory shortage
(Out of memory). At last recovery gets completed.

Thanks,
Prajapati

On Feb 13, 2008 12:44 AM, Eli Shemer mailto:[EMAIL PROTECTED]"[EMAIL PROTECTED]> wrote:

Can someone explain this?

Thanks.



080212 15:54:39  mysqld started

080212 15:54:39  InnoDB: Started; log sequence number 0 43743

080212 15:54:40 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.0.54-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
Source distribution



Number of processes running now: 0

080212 17:23:24  mysqld restarted

080212 17:23:25  InnoDB: Started; log sequence number 0 43743

080212 17:23:25 [Note] Recovering after a crash using mysql-bin

080212 17:23:25 [Note] Starting crash recovery...

080212 17:23:25 [Note] Crash recovery finished.

/usr/libexec/mysqld: Out of memory (Needed 463827968 bytes)

/usr/libexec/mysqld: Out of memory (Needed 347870208 bytes)

/usr/libexec/mysqld: Out of memory (Needed 260901888 bytes)

/usr/libexec/mysqld: Out of memory (Needed 195674112 bytes)

/usr/libexec/mysqld: Out of memory (Needed 146755584 bytes)

080212 17:23:26 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.0.54-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
Source distribution



Number of processes running now: 0

080212 20:45:03  mysqld restarted

080212 20:45:04  InnoDB: Started; log sequence number 0 43743

080212 20:45:04 [Note] Recovering after a crash using mysql-bin

080212 20:45:08  mysqld ended



080212 21:08:21  mysqld started

080212 21:08:22  InnoDB: Started; log sequence number 0 43743

080212 21:08:22 [Note] Recovering after a crash using mysql-bin

080212 21:08:23 [Note] Starting crash recovery...

080212 21:08:23 [Note] Crash recovery finished.

080212 21:08:23 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.0.54-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
Source distribution

[EMAIL PROTECTED] ~]# date

Tue Feb 12 21:09:19 IST 2008

[EMAIL PROTECTED] ~]#


Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007
18:55




-- 
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: HYPERLINK "http://ed-ventures-online.com"ed-ventures-online.com
Email-id: HYPERLINK "mailto:[EMAIL PROTECTED]"[EMAIL PROTECTED] 

Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007
18:55


Internal Virus Database is out-of-date.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007
18:55
 

crash

[Eli Shemer]

Can someone explain this?

Thanks.

 

080212 15:54:39  mysqld started

080212 15:54:39  InnoDB: Started; log sequence number 0 43743

080212 15:54:40 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.0.54-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
Source distribution

 

Number of processes running now: 0

080212 17:23:24  mysqld restarted

080212 17:23:25  InnoDB: Started; log sequence number 0 43743

080212 17:23:25 [Note] Recovering after a crash using mysql-bin

080212 17:23:25 [Note] Starting crash recovery...

080212 17:23:25 [Note] Crash recovery finished.

/usr/libexec/mysqld: Out of memory (Needed 463827968 bytes)

/usr/libexec/mysqld: Out of memory (Needed 347870208 bytes)

/usr/libexec/mysqld: Out of memory (Needed 260901888 bytes)

/usr/libexec/mysqld: Out of memory (Needed 195674112 bytes)

/usr/libexec/mysqld: Out of memory (Needed 146755584 bytes)

080212 17:23:26 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.0.54-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
Source distribution

 

Number of processes running now: 0

080212 20:45:03  mysqld restarted

080212 20:45:04  InnoDB: Started; log sequence number 0 43743

080212 20:45:04 [Note] Recovering after a crash using mysql-bin

080212 20:45:08  mysqld ended

 

080212 21:08:21  mysqld started

080212 21:08:22  InnoDB: Started; log sequence number 0 43743

080212 21:08:22 [Note] Recovering after a crash using mysql-bin

080212 21:08:23 [Note] Starting crash recovery...

080212 21:08:23 [Note] Crash recovery finished.

080212 21:08:23 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.0.54-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
Source distribution

[EMAIL PROTECTED] ~]# date

Tue Feb 12 21:09:19 IST 2008

[EMAIL PROTECTED] ~]#


Internal Virus Database is out-of-date.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007
18:55
 

RE: Decimal - Maximum is 30

[Eli Shemer]

Actually I'm not in liberty to reveal but it is essential to the project.
I will look for a workaround in code space when I find the time.

-Original Message-
From: Warren Young [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 30, 2008 5:49 PM
To: MySQL List
Subject: Re: Decimal - Maximum is 30

Eli Shemer wrote:
> 
> Is there any possible way to increase this limit ?

I'm curious to know what it is you're doing where you need accuracy 
better than one part in a nonillion.

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


Internal Virus Database is out-of-date.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007
18:55
 

Internal Virus Database is out-of-date.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007
18:55
 

Internal Virus Database is out-of-date.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007
18:55
 



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

Decimal - Maximum is 30

[Eli Shemer]

Hey there

 

Is there any possible way to increase this limit ?


Internal Virus Database is out-of-date.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007
18:55
 

optimization - directories and sub-directories (with descendants)

[Eli]

Hi,

I have a table of directories. Each row represents a directory, which 
holds his name and desc. Another table lists sub-directories from each 
directory source to its sub-directories targets.


dirs:
+--+--++
| dir_id   | dir_name | dir_desc   |
+--+--++
|0 | root |   root dir |
|   11 |   d1 |  dir no. 1 |
|   12 |   d2 |  dir no. 2 |
|   21 |   d3 |  dir no. 3 |
|   22 |   d4 |  dir no. 4 |
|   23 |   d5 |  dir no. 5 |
|   31 |   d6 |  dir no. 6 |
|   32 |   d7 |  dir no. 7 |
|   41 |   d8 |  dir no. 8 |
|   51 |   d9 |  dir no. 9 |
|   52 |  d10 | dir no. 10 |
|   61 |  d11 | dir no. 11 |
+--+--++
12 rows in set (0.00 sec)

subdirs:
+++
| dir_source | dir_target |
+++
|  0 | 11 |
|  0 | 12 |
| 11 | 21 |
| 11 | 22 |
| 11 | 23 |
| 12 | 31 |
| 22 | 31 |
| 22 | 32 |
| 23 | 52 |
| 31 | 41 |
| 41 | 51 |
| 41 | 52 |
| 52 | 61 |
+++
13 rows in set (0.00 sec)

root (0)
   +d1 (11)
   |  +d3 (21)
   |  +d4 (22)
   |  |  +d6 (31)
   |  |  |  +d8 (41)
   |  |  | +d9 (51)
   |  |  | +d10 (52)
   |  |  | +d11 (61)
   |  |  +d7 (32)
   |  +d5 (23)
   | +*d10* (52) -reference
   +d2 (12)
  +*d6* (31) -reference

Note that a directory can be contained in several parent directories (as 
long as it doesn't creates circles) - "references".


Example: I want to search on all the directories under 'd4' that contain 
the word "music".


I got several solutions, but not satisfying:
A) Loop from 'd4' to sub-dirs in first level, and use buffer list for 
next iterations when going deeper into levels. [not good: there can be 
many sub-dirs with descendants, and the loop will iter more; slow on 
searches].
B) Storing the directory tree structure in the form of 'root/d1/d4/d6' 
and etc. [not good: personally I can't use it (specific implementation 
restriction)].
C) Descendants sub-dirs connections to sub-dirs on deeper levels, so 
searching will go over the first level sub-dirs and the descendants 
sub-dirs. [not good: there can be many sub-dirs and there would be many 
descendants sub-dirsl; duplicating descendants on references].


Do you have any other suggestions? What's the better way?


-Thanks in advance... :-)

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

Re: spanned indexes

[Eli]

Gleb Paharenko wrote:

You may want to think about the MERGE storage ENGINE, however
it doesn't have a real spanned index. See:
   http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html


MERGE is not the case here, since it is JOIN of tables which are 
different in defintion. MERGE table would be helpful if UNION on 
same-defined tables was used..


It seems that the solution for this (as for now) is to combine all the 
tables into one big table and index fields over it.


-thanks, Eli

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

Re: spanned indexes

[Eli]

OK.. Thanks for the fast replies..

The reason I thought about this is that I got several tables that hold 
their own specific data and are indexed inside themselves. But I got an 
issue to search on JOIN of 2 or more tables with comparison and/or 
ordering on combinations of fields from the various tables. This usualy 
ended up in a case that the first table in the JOIN used his own index, 
while the rest had to do full table scan, so thought that if there was a 
spanned index it would be much faster.. Unfortunately, as I thought, it 
is impossible (for now at least)..


Anyways, it seems that the way to overcome this problem is to have one 
big table joined from all the smaller tables. Problem is that it will be 
real big with many-many fields and some long TEXT/BLOB fields.


Thanks for the help,
-Eli.

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

spanned indexes

[Eli]

Hi,

Is it possible, or probably will be possible, to define indexes spanned 
on more than one table?
I mean that if I have column col_a from table tbl_A, and col_b from 
table tbl_B.. Can I define a KEY with both those columns? Where will the 
index be saved?


-thanks.

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

Re: Speeding UNION with merging indexes

[Eli Hen]

You're right.. Your suggestion will speed it up.. but if you want to 
have LIMIT 1000,10 then you will have to retrieve 1010 rows from each 
sub-query then order the UNIONed rows and get the 10 rows you want. Here 
is just 2 sub-queries, but what if you got 10 UNIONed sub-queries.. and 
what if you use JOINs.. mabye merging the indexes will be faster..


If I knew how merging indexes is performed, mabye we could think of an 
actual algorithm to do this.. But, I believe the gurus already thought 
about this, so that's why I asked if this feature listed for the future 
releases?


Thanks :-)


[EMAIL PROTECTED] wrote:



I am not sure about index merging but you should be able to speed 
things up if you ORDER BY and LIMIT your inner queries as well:


(SELECT * FROM t1 WHERE a='a' ORDER BY id limit 0,5)
UNION
(SELECT * FROM t2 WHERE a='a' ORDER BY id limit 0,5)
ORDER BY id LIMIT 0,5

To answer your UNION query, you only want 5 records in your results 
(the 5 with the lowest ID values). It doesn't matter if those 5 come 
from the first inner query or the second but you will use at most 5 
records from either. By reducing the number of records you need to 
UNION, you seriously decrease the processing required to achieve your 
outer goal. Granted, this is a rather special case but this is the 
second time this week that this pattern of query has appeared on the 
list so it can't be too special.


To get more abstract in analyzing an index merge process, can we 
discuss under which conditions it would be useful to merge indexes? 
 How would you describe the initial conditions of any UNION query that 
would describe a situation in which index merging would be of benefit 
to the execution of the query. I think it would be useful in more 
situations than when UNIONing the complete table query against two 
identical tables, if we can describe the decision in more general 
terms. Could you use index merging during JOINs or just UNIONs? What 
other types of queries could benefit from this being added to the 
query engine? Is it only useful in LIMIT queries?


Come on everyone, put on your thinking caps and work like DB 
developers for a bit and let's see if we can' t hash out a set of 
parameters that make sense. This sounds like a useful tool to add to 
the core engine but I think it needs a bit of thought about when to 
use it and when not to use it. What are your ideas?


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Eli Hen <[EMAIL PROTECTED]> wrote on 08/03/2005 04:44:55 PM:

> Hi,
>
> In the example you gave, it seems that MySQL doesn't merge the index of
> t1 in both sub-queries (which is the same index).. but it runs the
> sub-queries seperatedly, using the index on each sub-query seperatedly..
>
> Mabye I wasn't clear enough with my question.. let me phrase it again:
> Say I got 2 tables which are defined the same way but different names.
>
> mysql> SHOW CREATE TABLE t1 \G
> *** 1. row ***
>Table: t1
> Create Table: CREATE TABLE `t1` (
>   `id` int(11) NOT NULL auto_increment,
>   `a` char(10) NOT NULL,
>   PRIMARY KEY  (`id`),
>   KEY `a` (`a`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> mysql> SHOW CREATE TABLE t2 \G
> *** 1. row ***
>Table: t2
> Create Table: CREATE TABLE `t2` (
>   `id` int(11) NOT NULL,
>   `a` char(10) NOT NULL,
>   PRIMARY KEY  (`id`),
>   KEY `a` (`a`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
>
> mysql> EXPLAIN (SELECT * FROM t1 WHERE a='a') UNION (SELECT * FROM t2
> WHERE a='a') ORDER BY id LIMIT 0,5 \G
> *** 1. row ***
>id: 1
>   select_type: PRIMARY
> table: t1
>  type: ref
> possible_keys: a
>   key: a
>   key_len: 10
>   ref: const
>  rows: 1000
> Extra: Using where
> *** 2. row ***
>id: 2
>   select_type: UNION
> table: t2
>  type: ALL
> possible_keys: a
>   key: NULL
>   key_len: NULL
>   ref: NULL
>  rows: 500
> Extra: Using where
> *** 3. row ***
>id: NULL
>   select_type: UNION RESULT
> table: 
>  type: ALL
> possible_keys: NULL
>   key: NULL
>   key_len: NULL
>   ref: NULL
>  rows: NULL
> Extra: Using filesort
> 3 rows in set (0.01 sec)
>
> MySQL executes each sub-query seperatedly (which returns 1000 and 500
> rows), then it combines them and use files

RE: Speeding UNION with merging indexes

[Eli Hen]

Hi,

In the example you gave, it seems that MySQL doesn't merge the index of 
t1 in both sub-queries (which is the same index).. but it runs the 
sub-queries seperatedly, using the index on each sub-query seperatedly..


Mabye I wasn't clear enough with my question.. let me phrase it again:
Say I got 2 tables which are defined the same way but different names.

mysql> SHOW CREATE TABLE t1 \G
*** 1. row ***
  Table: t1
Create Table: CREATE TABLE `t1` (
 `id` int(11) NOT NULL auto_increment,
 `a` char(10) NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t2 \G
*** 1. row ***
  Table: t2
Create Table: CREATE TABLE `t2` (
 `id` int(11) NOT NULL,
 `a` char(10) NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


mysql> EXPLAIN (SELECT * FROM t1 WHERE a='a') UNION (SELECT * FROM t2 
WHERE a='a') ORDER BY id LIMIT 0,5 \G

*** 1. row ***
  id: 1
 select_type: PRIMARY
   table: t1
type: ref
possible_keys: a
 key: a
 key_len: 10
 ref: const
rows: 1000
   Extra: Using where
*** 2. row ***
  id: 2
 select_type: UNION
   table: t2
type: ALL
possible_keys: a
 key: NULL
 key_len: NULL
 ref: NULL
rows: 500
   Extra: Using where
*** 3. row ***
  id: NULL
 select_type: UNION RESULT
   table: 
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: NULL
   Extra: Using filesort
3 rows in set (0.01 sec)

MySQL executes each sub-query seperatedly (which returns 1000 and 500 
rows), then it combines them and use filesort to order, then it limits 
and give only the first 5 rows.
This is very expensive for just 5 first rows that match the same WHERE 
clause from 2 tables which have the SAME definition and indexes. It 
might be possible to merge the indexes of t1 and t2 and speed up the 
query... Is it possible? Does MySQL intend to do this?


[BTW: I gave just a private case. It might be examined to cover more cases.]

-thanks in advance



Hello.


MySQL uses indexes in queries which are parts of UNION. See:

mysql> explain (select * from t1 where a=5) union (select * from t1 where 
a=3)\G;
*** 1. row ***
  id: 1
 select_type: PRIMARY
   table: t1
type: ref
possible_keys: a
 key: a
 key_len: 5
 ref: const
rows: 1
   Extra: Using where; Using index
*** 2. row ***
  id: 2
 select_type: UNION
   table: t1
type: ref
possible_keys: a
 key: a
 key_len: 5
 ref: const
rows: 1
   Extra: Using where; Using index
*** 3. row ***
  id: NULL
 select_type: UNION RESULT
   table: 
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: NULL
   Extra:
3 rows in set (0.00 sec)


mysql> show create table t1\G;
*** 1. row ***
  Table: t1
Create Table: CREATE TABLE `t1` (
 `a` int(11) default NULL,
 KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1




Hello,

MySQL implemented index_merge in version 5...
Does MySQL supports something like index_merge to speed up UNION 
queries? If yes, for which version (assumed release time)?

Does anyone know of other DBs systems that can speed up UNION queries?
This issue is quite critical for our system..

-thanks in advance.






--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
  __  ___ ___   __
 /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@/stripped/
/_/  /_/\_, /___/\___\_\___/   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]

Speeding UNION with merging indexes

[Eli Hen]

Hello,

MySQL implemented index_merge in version 5...
Does MySQL supports something like index_merge to speed up UNION 
queries? If yes, for which version (assumed release time)?

Does anyone know of other DBs systems that can speed up UNION queries?
This issue is quite critical for our system..

-thanks in advance.



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

INSERT with ON DUPLICATE error

[Eli]

Hi,
I got this table:
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| id   | int(10) unsigned|  | PRI | 0   |   |
| value| varchar(128)| YES  | | NULL|   |
+--+-+--+-+-+---+
I try this query:
INSERT INTO tbl
(id,value)
(SELECT ref_id,'fixed value'
FROM another_tb
WHERE id>'100')
ON DUPLICATE KEY UPDATE id=id;
but I get the next error:
ERROR 1110 (42000): Column 'id' specified twice
When I remove the fields list (id,value), then it works, and as expected 
the duplicated key rows are not changed.
It doesn't matter if I use INSERT-SELECT or regular SELECT.

Is this a bug?
-thanks, Eli
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subquery speed : IN (SELECT ...) versus IN (X, Y, Z, ...)

[Eli]

Hi,
You may put indexes on the following fields:
1. cityname (in tblcity)
2. zip (in tblpeople)
I assume it will speed up your queries. If you already do have those 
indexes, then try to look at the 'EXPLAIN' of the first query (with 
sub-query), and see the column 'type' that describes you how the tables 
are joined.
(see: http://dev.mysql.com/doc/mysql/en/explain.html )
You may post here your EXPLAIN results.

Also note that when you use the second query (B), you also spend time on 
the SQL1 query that you didn't consider about its time.

-Eli
HMax wrote:
Hello list,
We are currently tuning our queries speed and we found out that the
ones using subqueries are quite slower than the 'usual' ones. Here is
an example of a wierd behavior.
We have a city list associated with zipcode, and user can search a
database of people living in a given city. The problem is that a city
can have several zip codes.
Our first request is :
SQL1 = " SELECT zip FROM tblcity WHERE cityname = 'Paris' "
This request actually returns something like 20 results.
The second request list the people living in areas with those zip codes:
SQL2 = " SELECT people FROM tblpeople WHERE zip IN (###) "
In ### we can either put
- A : SQL1
- B :  the list build from a recordset opened on SQL1 which would give
something like : '75000', '75001', '75002', '75003', etc...
Queries speed are 0.16s for A, and 0.05s for B.
Can anybody explain this behavior, and maybe offer some advices on
optimizing our queries.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Full-Text scoring (MySQL 4.1 and future)

[Eli]

Hello,
Full-Text in MySQL 4.1 allows you to calculate a row score using 
full-text search and calculation from the same table as the full-text.

For example:
SELECT A.field1,B.field2,B.field3,
 MATCH(B.name,B.description) AGAINST('Hello World' IN BOOLEAN 
MODE)+10*B.field2-5*B.field3 AS score
   FROM A INNER JOIN B USING (key)
   WHERE MATCH(B.name,B.description) AGAINST('Hello World' IN BOOLEAN 
MODE)+10*B.field2-5*B.field3
   ORDER BY score DESC
   LIMIT 0,10;

This search is fast, since it doesn't use temporary table, because all 
fields on the score calculation are from table B. The query time is 
monotone (grows as you ask for farther results from the start limit).
If I want to add to the score +3*A.field1 then MySQL ends the search 
with a temporary table that makes him run over all matched rows, which 
makes the search run slower, and the query time is constant.

Does anyone know if MySQL tends to calculate scoring also from fields 
out of the full-text table? (Mabye it is available for MySQL 5 even in 
its beta?)

Please, that's important for me to design my DB.
-thanks, Eli
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Convert to character set (upgrading from 4.0 to 4.1)

[Eli]

Hi..
I encountered some problems with character sets.
On the 4.0 version I stored UTF-8 strings. When upgraded to 4.1, I saw 
the default character set was 'latin1', so I converted to UTF-8 using this:

ALTER TABLE my_table CONVERT TO CHARACTER SET 'utf8', DEFAULT CHARACTER 
SET 'utf8';

When viewing the pages, I see that some of the characters were corrupted 
(the same chars always). I tried to return to convert back to 'latin1' 
but the problem remained.

What was the problem?
How can I fix it?
BTW: for some reason I cannot reply to posts in this mailing list using 
Thunderbird. (On PHP mailing list replying works).

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

Error 1271 (HY000) - Illegal mix of collations

[Eli]

Hi,
I'm running a query using UNION, where all parts of the union are 
queries from the same syntax and from tables with same definition, and 
the select is same too. Each of the union parts is a query with JOINs.

I got this error:
ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'
I tried to look after it on the net, but couldn't find anything meaningful.
thanks in advance,
-Eli.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Selecting first non-null values from a set of rows

[Eli]

Eli <[EMAIL PROTECTED]> wrote on 02/08/2005 02:26:41 PM:

Hello,
Say I get these rows in a regular query:
col1   col2col3
---
NULLB1NULL
NULLNULLNULL
A3  B3  NULL
A4  NULLC4
A5  B5  C5
NULLB6  C6
(It's important to keep the rows in that order).
I want to get 1 row of the first non-null values from every column.. the
row: A3 B1 C4.
Like the COALESCE function in MySQL, but on rows.
Please help...
-thanks, Eli

IF you want all 3 columns to be non-null, make that a condition of your 
query.

SELECT col1, col2, col3
FROM sometable
WHERE col1 is not null
AND col2 is not null
AND col3 is not null
LIMIT 1; 

However, you cannot guarantee a repeatable order to the results of any 
query unless you force the engine to sort the results by providing an 
ORDER BY clause to your query. Without an ORDER BY, the query engine is 
free to respond with records in any order it pleases.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Sorry, but that is not the case I'm looking for.. :-(
The row I need is: A3 B1 C4 (the first non-null values from several rows).
Your query will return: A5 B5 C5 (row 5 only).
Mabye there's a way to use COALESCE function in MySQL, but I couldn't figure 
how...
-thanks, Eli
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Selecting first non-null values from a set of rows

[Eli]

Hello,
Say I get these rows in a regular query:
col1col2col3
---
NULLB1  NULL
NULLNULLNULL
A3  B3  NULL
A4  NULLC4
A5  B5  C5
NULLB6  C6
(It's important to keep the rows in that order).
I want to get 1 row of the first non-null values from every column.. the
row: A3 B1 C4.
Like the COALESCE function in MySQL, but on rows.
Please help...
-thanks, Eli
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ERROR 1062: Duplicate entry

[Eli Shemer]

Hey,

I am trying to create an index on a varchar column, but I am getting a
suspicious error I am unable to overcome.

Indeed there are duplicated rows but since there is no constraint nor a
key in the table, I do not see why this error is generated.

I would appreciate some help.

Here are the details.

mysql> create index url_site_idx on URL(Site(255));
ERROR 1062: Duplicate entry 'Transmeta unveils futuristic Crusoe chip ' 
for key 1
mysql>

mysql> select version();
+-+
| version()   |
+-+
| 4.0.17-standard |
+-+
1 row in set (0.00 sec)

mysql> show create table URL;
+---+---

+
| Table | Create Table

|
+---+---

+
| URL   | CREATE TABLE `URL` (
  `Parent` text,
  `URL` text,
  `Site` text,
  `Description` text,
  `Topsite` int(11) default NULL,
  `Star` tinyint(1) default NULL
) TYPE=MyISAM |
+---+---

+
1 row in set (0.00 sec)

mysql> describe URL;
+-++--+-+-+---+
| Field   | Type   | Null | Key | Default | Extra |
+-++--+-+-+---+
| Parent  | text   | YES  | | NULL|   |
| URL | text   | YES  | | NULL|   |
| Site| text   | YES  | | NULL|   |
| Description | text   | YES  | | NULL|   |
| Topsite | int(11)| YES  | | NULL|   |
| Star| tinyint(1) | YES  | | NULL|   |
+-++--+-+-+---+
6 rows in set (0.00 sec)

## no indexes at all currently
mysql> show index from URL;
Empty set (0.00 sec)

Thanks.
Eli Shemer.
Siteware.



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

checking that any element from one group appears in another group?

[Eli Hen]

Hello All,

In MySQL it is possible to check if an element is existing in a group, like:

... WHERE 'a' IN ('a','b','c','d') ...

but that checks one element only.
I want to check if any element from a group exists in another group, like:

... WHERE ('a','f','g') IN ('a','b','c','d') ...

'a' in the first group appears in the second group, so it will return true,
no matter if 'f' or 'g' exist in too.

Well, is there anything like above that I can use?

-thanks, Eli



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

Re: Fulltext creation on 4.1: ERROR 1034

[Eli Hen]

"Kurt Haegeman" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Sergei Golubchik wrote:
>
> >Hi!
> >
> >On Jan 13, Kurt Haegeman wrote:
> >
> >
> >>Hi,
> >>
> >>When trying to create a fulltext index on my large table, I get the
> >>following error:
> >>
> >>ERROR 1034 (HY000): 121 when fixing table
> >>
> >
> Hi Sergei,
>
> alter table articles
> add fulltext( text );
>
> After several hours of processing, the error below is generated.
>
> Regards,
> Kurt.
>

Did you try to check the table using myisamcheck or "CHECK TABLE articles;"
???
It might be that your table is corrupted..



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

REGEXP or LIKE

[Eli Hen]

Hello,

With your experience.. which runs better: REGEXP or LIKE? assuming you can
express the query in both forms.

I found that LIKE is twice faster than REGEXP using i.e:
LIKE '%1068812942%'
REGEXP '1068812942'

-thanks, Eli



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

Re: Multiple Roles

[Eli Hen]

"Caroline Jen" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> In case that a user has multiple roles; for example,
> John Dole is both author and editor,
>
> 1. I should have two rows for John Dole?
>
>John Dole author
>John Dole editor
>
>or. I should have only one row and use comma ',' to
>
>separate the roles?
>
>John Dole author, editor
>
> 2. How do I create the table for the second case (see
> below)?
>
>   create table user_roles (
>   user_name varchar(15) not null,
>   role_name varchar(15) not null, varchar(15) null
>   );
>

for the second option, you can use VARCHAR for roles_names, only make sure
that you have enough space to define there all combinations of roles. you
can also use BLOB for it (VARCHAR is up to 255 chars length).

CREATE TABLE user_roles (
user_nameVARCHAR(15) NOT NULL,
roles_names  VARCHAR(31) NOT NULL
);

roles_names is of length 31 cuz the comma is also a char.



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

InnoDB size against MyISAM size

[Eli Hen]

Hello,

My HDD is running low and I MyISAM tables are keep crashing... I think that
converting to InnoDB will be more stable, but what about the data files
sizes? convertion to InnoDB will need more or less disk space than MyISAM?

-thanks, Eli




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

Re: Two many key part specified

[eli]

> On 3 Sep 2003 at 18:59, eli wrote:
> 
>> EXCEPTION java.sql.SQLEXCEPTION:Invalid argument value, message from
>> server "Two many key part specified. Max 16 parts allowed".
>> [...]
>> INDEX(ID, Field_1, Field_2,Field_3, Field_23)
>> )
>> 
>> I was supossed the maximum number of index was 32.
> 
> The maximum number of indexes is not the same as the maximum number
> of parts for a single index.  Somehow I doubt you really want a
> single index on 24 fields.  Having Field_23 at the end of the index,
> for example, is useless unless you're already specifying all of ID
> and Field_1 through Field_22 in the query as well.

Thanks. It was very useful. I realized I was wrong writting indexes.

eli


SetFile DATABASE FACTORY
 
- Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh )

<[EMAIL PROTECTED]>TEL 93 238 56 00

 FileMaker Inc es miembro del BSA ( 900 211 048)
SetFile - FSA Partners




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

Two many key part specified

[eli]

Hi,

I was creating tables with more or less columns with their index. When I try
to created one of these tables I was sent the next message:

EXCEPTION java.sql.SQLEXCEPTION:Invalid argument value, message from server
"Two many key part specified. Max 16 parts allowed".

The query to create this table is as follows:

CREATE TABLE FASE( 
ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
Field_1 char(6), 
Field_2 char(6),  
Field_3 char(6),
Field_4 INT, 
Field_5 Date, 
Field_6 char(6),
Field_7 INT, 
Field_8 INT,
Field_9 Date,
Field_10 char(6),
Field_11 Date,
Field_12 char(6),
...

INDEX(ID, Field_1, Field_2,Field_3, Field_23)
)

I was supossed the maximum number of index was 32.

Am I wrong? This message didn't tell me this? If not, what does it tell me?

Thanks in advanced.

Eli





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

Two many key part specified

[eli]

Hi,

I was creating tables with more or less columns with their index. When I try
to created one of these tables I was sent the next message:

EXCEPTION java.sql.SQLEXCEPTION:Invalid argument value, message from server
"Two many key part specified. Max 16 parts allowed".

The query to create this table is as follows:

CREATE TABLE FASE( 
ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
Field_1 char(6), 
Field_2 char(6),  
Field_3 char(6),
Field_4 INT, 
Field_5 Date, 
Field_6 char(6),
Field_7 INT, 
Field_8 INT,
Field_9 Date,
Field_10 char(6),
Field_11 Date,
Field_12 char(6),
...

INDEX(ID, Field_1, Field_2,Field_3, Field_23)
)

I was supossed the maximum number of index was 32.

Am I wrong? This message didn't tell me this? If not, what does it tell me?

Thanks in advanced.

Eli



SetFile DATABASE FACTORY
 
- Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh )

<[EMAIL PROTECTED]>TEL 93 238 56 00

 FileMaker Inc es miembro del BSA ( 900 211 048)
SetFile - FSA Partners




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

Use Like or =

[eli]

Hi,

I have a question about using LIKE or equal.

I mean, comparing two strings with exact coincidence, without case
sensitive, which is better? Or are they the same? Do they work equal?

For Instance.

"abc"="abc"

Or

"abc" LIKE "abc"

I use Mysql 4.0.12.

Thanks in advanced.

Eli


SetFile DATABASE FACTORY
 
- Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh )

<[EMAIL PROTECTED]>TEL 93 238 56 00

 FileMaker Inc es miembro del BSA ( 900 211 048)
SetFile - FSA Partners




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

Connections

[eli]

Thank you for the information about LIKE and equal.

I have another question.

What is better/quicly 50 simultaneously query/update at the same time from
50 different users or 50 simultaneously query/update at the same time from
one user? 

Thanks for your information in advanced,

eli



> At 16:53 +0100 8/5/03, eli wrote:
>> Hi,
>> 
>> I have a question about using LIKE or equal.
>> 
>> I mean, comparing two strings with exact coincidence, without case
>> sensitive, which is better? Or are they the same? Do they work equal?
>> 
>> For Instance.
>> 
>> "abc"="abc"
>> 
>> Or
>> 
>> "abc" LIKE "abc"
>> 
>> I use Mysql 4.0.12.
>> 
>> Thanks in advanced.
>> 
>> Eli
> 
> Functionally, the two expressions are the same. In terms of efficiency,
> the "=" operator's probably somewhat better than LIKE.  You can try
> checking this for yourself as follows:
> 
> mysql> select benchmark(1000,'abc' LIKE 'abc');
> +--+
> | benchmark(1000,'abc' LIKE 'abc') |
> +--+
> |0 |
> +--+
> 1 row in set (2.60 sec)
> 
> mysql> select benchmark(1000,'abc' = 'abc');
> +---+
> | benchmark(1000,'abc' = 'abc') |
> +---+
> | 0 |
> +---+
> 1 row in set (2.09 sec)


SetFile DATABASE FACTORY
 
- Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh )

<[EMAIL PROTECTED]>TEL 93 238 56 00

 FileMaker Inc es miembro del BSA ( 900 211 048)
SetFile - FSA Partners




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

Command "Show Column"

[eli]

Hi again,

I know that "Show Column" query shows the name of all variables with their
characteristics. 

I want to know if there is a command that ONLY shows the name of the
varibales of a column (without characteristics).

Thanks a lot.

eli




SetFile DATABASE FACTORY
 
- Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh )

<[EMAIL PROTECTED]>TEL 93 238 56 00

 FileMaker Inc es miembro del BSA ( 900 211 048)
SetFile - FSA Partners




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

Re: Relationships in Mysql

[eli]

el [DATE], [NAME] en [ADDRESS] escribió:

Thank you for your help and for the lesson.

Eli


> I would just add a few words to Nils' excellent reply:
> 
> In the most general sense, a foreign key is just a primary
> key value that occurs outside of its native table (i.e. in
> a "foreign" table, get it?) in order to point to the record
> in its native table with that value as its primary key.
> 
> You can use foreign keys in your database design and
> programming without actually formally declaring them as
> such in the database system. Indeed, as you point out, you
> *have* to use them to establish relationships among your
> tables.
> 
> If the database system doesn't recognize the foreign key
> columns as foreign keys, then you must programatically
> ensure that referential integrity is maintained:
> --when you insert a new record, make sure that any foreign
> key points to an existing record (a record with that key
> value as its primary key)
> --don't ever change the value of a record's primary key
> (that is, unless you are prepared to change the value of
> all the foreign keys in all the records pointing to that
> record)
> --don't ever delete a record that has other records
> pointing to it via foreign keys (unless you delete all
> those records too).
> 
> Many people assume that a relational database system *must*
> recognize such foreign keys (and relationships among the
> tables) in order to be truly relational. But the term
> "relational" refers, not to the relationships among the
> tables, but to the basic relation that is defined by each
> table (and Codd's theory that even the most complex data
> structure can be broken down into such simple relations).
> 
> --John
> 
> On Thursday 24 July 2003 08:00 pm, Nils Valentin wrote:
>> Hi Eli,
>> 
>> The short answer is .. you create relationships between
>> tables by creating foreign keys and primary keys.
>> However, you might not even need them ;-) - I explain
>> below.
>> 
>> Foreign keys and primary keys are used to create
>> relations between tables.
>> 
>> Using them will bind you to some rules which you can use
>> to your advantage (or not), thats entirely up to you.
>> 
>> If you define a primary key than any foreign key can link
>> to the primary key. However , a foreign ke can only
>> contain a value which is already defined in a primary
>> key.
>> 
>> This can be used to restrict that only valid values are
>> insert for foreign keys or f.e no primary key is deleted
>> where still existing foreign keys are pointing to the
>> primary key record. Currently only the InnoDB table
>> format supports Foreign keys.
>> 
>> 
>> In order to use a join you dont need to create any keys.
>> You can free define the columns to be used for the join
>> condition.
>> 
>> Please see the JOIN Syntax for more information.
>> 
>> http://www.mysql.com/doc/en/JOIN.html
>> 
>> Best regards
>> 
>> Nils Valentin
>> Tokyo/Japan
>> 
>> 2003? 7? 25? ??? 03:36?eli :
>>> Hi,
>>> 
>>> Can anyone explain me how to create relationships
>>> between tables in Mysql?
>>> 
>>> For making some kind of functions like joins, it is
>>> indispensable to have relationships defined?
>>> 
>>> 
>>> Thanks in advanced.
>>> 
>>> 
>>> eli


SetFile DATABASE FACTORY
 
- Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh )

<[EMAIL PROTECTED]>TEL 93 238 56 00

 FileMaker Inc es miembro del BSA ( 900 211 048)
SetFile - FSA Partners




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

FW: Relationships in Mysql

[eli]

Hi,

Can anyone explain me how to create relationships between tables in Mysql?

For making some kind of functions like joins, it is indispensable to have
relationships defined?


Thanks in advanced.


eli



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