Re: SQL_NO_CACHE
If SQL_NO_CACHE is specify, the cache will never be used : The Query Cache behaviour is quite simple, it uses the exact given query syntax as a hash to search into the query cache; it means writing 'select' or 'SELECT' is different. It also means adding SQL_NO_CACHE will search in the cache for a different query string, which will never be cached :) Jocelyn Le 04/03/2009 17:39, Perrin Harkins a écrit : On Wed, Mar 4, 2009 at 11:23 AM, Thomas Spahnit...@lawbiz.ch wrote: SQL_NO_CACHE means that the query result is not cached. It does not mean that the cache is not used to answer the query. Oh, right, he's looking for this: SET SESSION query_cache_type=off; - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL_NO_CACHE
MySQL Key buffer and OS cache could also have an impact. Have you tried disabling the Key Buffer first ? Jocelyn Le 04/03/2009 18:26, Morten Primdahl a écrit : Thanks for all the suggestions. The caching must be done somewhere else. There is no index on the column and there are about 500.000 rows in the table. A MySQL restart doesn't flush the cache in play, but a full restart of my laptop does (OS X). I may be chasing the wrong problem, but we have seen a query take a lot of time on a production machine and have not been able to pin point why, as the EXPLAIN looks good and the query is responsive enough when I run it manually. I was just trying to reproduce that. The below is after a full restart: mysql SET SESSION query_cache_type=off; Query OK, 0 rows affected (0.00 sec) mysql select SQL_NO_CACHE count(*) from users where email = 'hello' AND 456 = 456; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (28.80 sec) mysql RESET QUERY CACHE; Query OK, 0 rows affected (0.00 sec) mysql select SQL_NO_CACHE count(*) from users where email = 'hello' AND 789 = 789; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.44 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL_NO_CACHE
Just curious : if there's no index on the column why don't you try to add one ? That's probably why it takes a lot of time on the production machine. Jocelyn Le 04/03/2009 18:26, Morten Primdahl a écrit : Thanks for all the suggestions. The caching must be done somewhere else. There is no index on the column and there are about 500.000 rows in the table. A MySQL restart doesn't flush the cache in play, but a full restart of my laptop does (OS X). I may be chasing the wrong problem, but we have seen a query take a lot of time on a production machine and have not been able to pin point why, as the EXPLAIN looks good and the query is responsive enough when I run it manually. I was just trying to reproduce that. The below is after a full restart: mysql SET SESSION query_cache_type=off; Query OK, 0 rows affected (0.00 sec) mysql select SQL_NO_CACHE count(*) from users where email = 'hello' AND 456 = 456; +--+ | count(*) | +--+ | 0 | +--+ 1 row in set (28.80 sec) mysql RESET QUERY CACHE; Query OK, 0 rows affected (0.00 sec) mysql select SQL_NO_CACHE count(*) from users where email = 'hello' AND 789 = 789; +--+ | count(*) | +--+ | 0 | +--+ 1 row in set (0.44 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problem with more than 500 concurrent queries
Hi, Could try your script with the key_buffer set to 0 ? Regards, Jocelyn Fournier [EMAIL PROTECTED] a écrit : Hello, Thanks for you help. You can see the results in the .err file below. I've run it twice while the algorithm was running, but my knowledge in MySQL is still too poor to identify any problem here. Beside these locks that I do not know what they are. This is the third release of the algorithm. The first release used stacks. In a tree with 10 levels and an average of 2 children per node, it used to take about 2 minutes since everything was running sequentially. When I added the threads it went down to 30 seconds in total. Now the tree I have has 38 levels and 8 average children per node. The total of threads will be: Threads=(1^0)+(8^1)+(8^2)+..+(8^30) . Processed sequentially it will take really long. I am limiting the number of threads in Ruby to 100, which should represent almost 100 concurrent queries equal to the number of connections. But the database is not responding as I expect. I am for sure doing something wrong, but I do not know exactly what. Thanks again for your answers, This is the error log: 080627 12:06:38 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 080627 12:06:38 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 080627 12:06:38 InnoDB: Started; log sequence number 0 46409 080627 12:06:39 [Note] Event Scheduler: Loaded 0 events 080627 12:06:39 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.1.25-rc' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) Status information: Current dir: /usr/local/mysql/data/ Running threads: 181 Stack size: 196608 Current locks: lock: 0x107a854: lock: 0x1074e54: lock: 0x1071654: lock: 0x1067854: lock: 0x105d054: lock: 0x1054c54: lock: 0x1050c54: lock: 0x1045654: lock: 0x1040454: lock: 0x103a454: Key caches: default Buffer_size:1719664640 Block_size: 1024 Division_limit:100 Age_limit: 300 blocks used:67 not flushed: 0 w_requests: 0 writes: 0 r_requests: 1456 reads: 67 handler status: read_key: 153 read_next: 334 read_rnd 0 read_first: 3 write: 0 delete 0 update: 0 Table status: Opened tables: 17 Open tables: 10 Open files:20 Open streams: 0 Alarm status: Active alarms: 180 Max used alarms: 181 Next alarm time: 28794 Begin safemalloc memory dump: End safemalloc memory dump. Events status: LLA = Last Locked At LUA = Last Unlocked At WOC = Waiting On Condition DL = Data Locked Event scheduler status: State : INITIALIZED Thread id : 0 LLA: n/a:0 LUA: n/a:0 WOC: NO Workers: 0 Executed : 0 Data locked: NO Event queue status: Element count : 0 Data locked : NO Attempting lock : NO LLA : init_queue:132 LUA : init_queue:142 WOC : NO Next activation : never Status information: Current dir: /usr/local/mysql/data/ Running threads: 266 Stack size: 196608 Current locks: lock: 0x107a854: lock: 0x1074e54: lock: 0x1071654: lock: 0x1067854: lock: 0x105d054: lock: 0x1054c54: lock: 0x1050c54: lock: 0x1045654: lock: 0x1040454: lock: 0x103a454: Key caches: default Buffer_size:1719664640 Block_size: 1024 Division_limit:100 Age_limit: 300 blocks used:72 not flushed: 0 w_requests: 0 writes: 0 r_requests: 1785 reads: 72 handler status: read_key: 188 read_next: 411 read_rnd 0 read_first: 3 write: 0 delete 0 update: 0 Table status: Opened tables: 17 Open tables: 10 Open files:20 Open streams: 0 Alarm status: Active alarms: 265 Max used alarms: 279 Next alarm time: 28789 sh-3.2# From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: 26.06.2008 22:52 Subject: Re: Performance problem with more than 500 concurrent queries At 10:39 AM 6/26/2008, you wrote: Hello, thanks for the answer. Where is the error.log stored? I run the mysqladmin, it requires the password and it exits immediately. But I cannot find any error.log. Thanks, Guillermo Guillermo, Look in the \MySQL\Data\*.err file. Also I don't know why you need to use threads. Why not just use a stack instead, that way you need only 1 connection to MySQL. That's what we used to do when a programming language didn't have threads (back in the old days). Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance of this sql
Hi,
AFAIK, to optimize your query you should have :
1 index on B.KEYWORDS. (I assume it's the KD_KW_KI_IDX_0806120615 index ?)
1 index on C.KR_ID. Your index is not UNIQUE here, is this expected ?
1 index unique on A.CLUSTER_ID (it's already the case)
BTW, why are you using a derived table here ?
You could write directly :
select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID limit 40
Regards,
Jocelyn Fournier
Ananda Kumar a écrit :
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row ***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row ***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row ***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row ***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar [EMAIL PROTECTED] wrote:
The below query performance in 10 sec when there are no other activity on
db
, but when any insert or LOAD DATA Index creation happens it takes close
to
80 sec. Any ways to improve the performance of this sql.
innodb_buffer=11GB , key_buffer=3 GB, we have totally 16GB
EXPLAIN select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS,
A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A,
R_DATA B, ER_MAP C where B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID
and
C.CLUSTER_ID = A.CLUSTER_ID ) A limit
40;
++-+++-+-+-+-
--+--+--+
| id | select_type | table | type | possible_keys
|
key | key_len | ref
| rows | Extra|
++-+++-+-+-+-
--+--+--+
| 1 | PRIMARY | derived2 | ALL| NULL
|
NULL| NULL| NULL
| 3278 | |
| 2 | DERIVED | B | ref| PRIMARY,KD_KW_KI_IDX_0805230323
|
KD_KW_KI_IDX_0805230323 | 767 |
| 1524 | Using where; Using index |
| 2 | DERIVED | C | ref| PRIMARY
|
PRIMARY | 10 |
reh.B.kr_id |1 | Using index |
| 2 | DERIVED | A | eq_ref | PRIMARY
|
PRIMARY | 10 |
reh.C.cluster_id |1 | |
++-+++-+-+-+-
--+--+--+
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance of this sql
Hi,
Oops, indeed, C is a primary key :)
But what's weird is MySQL is using a ref type for the join between B and
C, and not an eq_ref.
Could you check B.KR_ID and C.KR_ID are of the same data type ?
Regards,
Jocelyn
Ananda Kumar a écrit :
Hi Jo,
Yes there is a combined index on (keywords,kr_id) on B,
c.kr_id is a primary key.
Let me talk to my dev and check why they are using derived. Thanks for
noticing this.
On 6/23/08, *Jocelyn Fournier* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi,
AFAIK, to optimize your query you should have :
1 index on B.KEYWORDS. (I assume it's the KD_KW_KI_IDX_0806120615
index ?)
1 index on C.KR_ID. Your index is not UNIQUE here, is this expected ?
1 index unique on A.CLUSTER_ID (it's already the case)
BTW, why are you using a derived table here ?
You could write directly :
select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID limit 40
Regards,
Jocelyn Fournier
Ananda Kumar a écrit :
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID,
A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C
where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row ***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row ***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row ***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row ***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by
adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
The below query performance in 10 sec when there are no
other activity on
db
, but when any insert or LOAD DATA Index creation
happens it takes close
to
80 sec. Any ways to improve the performance of this sql.
innodb_buffer=11GB , key_buffer=3 GB, we have totally 16GB
EXPLAIN select * from (select A.LEAF_CATEG_ID,
A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS,
A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A,
R_DATA B, ER_MAP C where B.KEYWORDS IN ('CAMERA') and
B.KR_ID = C.KR_ID
and
C.CLUSTER_ID = A.CLUSTER_ID ) A limit
40;
++-+++-+-+-+-
--+--+--+
| id | select_type | table | type | possible_keys
|
key | key_len | ref
| rows | Extra
Re: improve performance of this sql
Ok, so just to be sure, in C, kr_id is not unique by design (you have
several different cluster_id for the same kr_id) ?
Ananda Kumar a écrit :
B has single column KR_ID as primary key, where as C has combined
primary key (kr_id,cluster_id) and data type on both tables for KR_ID
are same.
On 6/23/08, *Jocelyn Fournier* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi,
Oops, indeed, C is a primary key :)
But what's weird is MySQL is using a ref type for the join between B
and C, and not an eq_ref.
Could you check B.KR_ID and C.KR_ID are of the same data type ?
Regards,
Jocelyn
Ananda Kumar a écrit :
Hi Jo,
Yes there is a combined index on (keywords,kr_id) on B,
c.kr_id is a primary key.
Let me talk to my dev and check why they are using derived.
Thanks for noticing this.
On 6/23/08, *Jocelyn Fournier* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi,
AFAIK, to optimize your query you should have :
1 index on B.KEYWORDS. (I assume it's the KD_KW_KI_IDX_0806120615
index ?)
1 index on C.KR_ID. Your index is not UNIQUE here, is this
expected ?
1 index unique on A.CLUSTER_ID (it's already the case)
BTW, why are you using a derived table here ?
You could write directly :
select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP
C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID limit 40
Regards,
Jocelyn Fournier
Ananda Kumar a écrit :
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID,
A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B,
ER_MAP C
where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and
C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row
***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row
***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row
***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row
***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by
adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
The below query performance in 10 sec when there
are no
other activity on
db
Re: Innodb, why not?
Hi, According to the manuel, Falcon is not yet optimized for performances, so benchmarking it would not be fair. And I do not recommand using the binary alpha release in production, you could corrupt badly your database (some bugs has only been fixed a few days ago concerning this corruption). Regards, Jocelyn Fournier www.mesdiscussions.net mos a écrit : At 03:54 PM 1/25/2007, you wrote: Another thing to consider is: heh, silly mail client :). Another thing to consider is this: http://dev.mysql.com/doc/falcon/en/index.html Though it's Not recommended for production use, I've heard people still use it in production environments. -- Chris, Falcon doesn't currently support RI. And like Innodb, it requires its own table space so it too may get fragmented. http://dev.mysql.com/doc/falcon/en/se-falcon-createdb.html and will likely require packing (sweeping?) from time to time. It would be nice to see some benchmarks compared to InnoDb and MyISAM. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select from two tables when they correspond, otherwise one table
Hi, SELECT products.sku, products.title, products.price, qty_price.qty, qty_price.qprice FROM products LEFT JOIN qty_price ON (products.sku = qty_price.qsku) WHERE products.vendor_id=15 AND products.category='widgets'; should do what you want. Regards, Jocelyn Fournier www.mesdiscussions.net boll a écrit : This query will display from the 'products' and 'qty_price' tables when the 'products.sku' and 'qty_price.qsku' fields match: SELECT products.sku, products.title, products.price, qty_price.qty, qty_price.qprice FROM products, qty_price WHERE products.sku = qty_price.qsku AND products.vendor_id=15 AND prod_test.category='widgets'; I would like to change it so that it will display the fields from 'products' even if there is no corresponding row in the 'qty_price' table. Thanks in advance for your suggestions. J. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave behind master... or not ?
Hi, I encounter a really strange behaviour with some of my slaves servers : I'm using MySQL 4.1.20 x86_64 on both master and slave servers. If I issue three times the command SHOW SLAVE STATUS several times in a few seconds, I could obtain the following results for the Seconds_Behind_Master column : 0 48 0 I don't understand how it's possible within 1 or 2 seconds to switch from 0 second behind master to 48 seconds behind master and then back again to 0 second behind master. Any idea of what could be wrong here ? Thanks, Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave behind master... or not ?
Hi, Thanks for the answer. I don't think this is what's happening because I can also see within a few seconds : 0 48 0 48 0 for example. Thanks, Jocelyn Michael Loftis a écrit : --On September 5, 2006 3:18:21 PM +0200 Jocelyn Fournier [EMAIL PROTECTED] wrote: Hi, Any idea of what could be wrong here ? My guess is that the variable is based on the last update timestamp, and the last time that the slave has seen data from the master. If it had been about a minute since the last update, then an update came through and the slave then saw the update it would think oh I'm about a minute behind since my timestamp is about a minute behind the timestamp I just saw. They're not constantly exchanging heartbeats or anything of any kind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about master_pos_wait
Hi, It could be usefull when you need to wait the slave to be in sync with the master, after some specific queries for example. Regards, Jocelyn wangxu a écrit : Slave server should automatically catch up master server when it be started. When i need use master_pos_wait? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18
Hi, I think you should change the tmpdir variable value to a directory which have enough room to create your temp big table (by default, it points to /tmp dir). Regards, Jocelyn Patrick Herber a écrit : Hello! I have a database with a big table (Data File 45 GB, Index File 30 GB). Since I have some performance troubles with table-locking in a multi-user environment (when one of them performs a complex query all the other have to wait up to 1 minute, which is not very nice...), I would like to convert this (and other tables) into InnoDB engine. I first tried using the innodb_file_per_table option but when running the statement ALTER TABLE invoice ENGINE=INNODB; ERROR 1114 (HY000): The table '#sql...' is full (this about one our after the start of the command, when the size of the file was bigger than ca. 70GB (I don't know exactly the size)) I tried then without the innodb_file_per_table option, setting my innodb_data_file_path as follows: innodb_data_file_path=ibdata1:500M;ibdata2:500M;ibdata3;500M;ibdata4:500M;ib data5:500M;ibdata6:500M;ibdata7:500M;ibdata8:500M;ibdata9:500M;ibdata10:500M :autoextend Also in this case I got the same error message. What should I do in order to convert this table? Should I set in the innodb_data_file_path for example 50 Files, each big 4GB ? Thanks a lot for your help. Best regards, Patrick PS: I'm running MySQL 5.0.18 on a Linux 2.6.13-15.7-smp server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with SQL DELETE issue
Hi, Excepted if he found a bug in an older version of MySQL, it's of course false ! (it would be a major issue which would make MySQL just unusable) Regards, Jocelyn David Rabinowitz a écrit : Hi, We are using MySQL 4.1.16, recently upgraded from 4.0.18. On the old server we tried not to delete records, as their is a common belief that deleting records will corrupt the table's index and we will have to call repair table. I couldn't find any documentation on that. Unfortunately he is not working here any more, so we cannot ask him where he heard about it. Can someone confirm or deny this? Regards, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT (*): Fast if NO where clause. slow with WHERE clause (yes, slow even if index is used).
Hi, What about SELECT count(*) FROM table1 - SELECT count(*) FROM table1 WHERE status = 1 ? (this query should be mush faster) Regards, Jocelyn jpow wrote: Hi everyone, I have this problem of slow count * when I use a where clause. 1. I have a table of ~1m rows. 2. There is a status column which can be 0 or 1. 3. Most of the rows have a status of 0, but maybe 10% of them have a status of 1. 4. I need to know how many records are status 1 / 0 for pagination/other purposes. 5. I already have a multi index comprising: id, status I encountered this issue: A) SELECT count(*) FROM table1 : Super fast 0.00x secs (select tables optimized away) B) SELECT count(*) FROM table 1 WHERE status = 0 : Quite slow ~0.5-0.6 secs (uses where; uses index) I understand that the the query w/o the where clause is extremely fast coz the query doesnt even need to access the table index, it can just retrieve the total no of rows (which is stored) However, when i put in the WHERE clause, MYSQL needs to access the index. 0.5-0.6 secs is too slow for me, as the db is fast scaling upwards. Is there any faster way for me to retrieve the total count for rows with status = 0/1? Many Thanks J Pow -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wanted: Help with 'ON DUPLICATE KEY' syntax
Hi, Are you using MySQL-4.1 ? (ON DUPLICATE KEY syntax has been introduced in 4.1) Regards, Jocelyn Siegfried Heintze wrote: Thanks for deciphering that terrible message, Shawn. I accidentally must have hit the paste key too many times. Anyway, here is my new insert statement: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (211584,'2005-06-26',2) ON DUPLICATE KEY UPDATE cJobTitle=2 DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE cJobTitle=2' at line 1 at ./crawl-hot-jobs.pl line 675. I'm looking at the documentation on http://dev.mysql.com/doc/mysql/en/insert.html and I don't see what I am doing wrong. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS 10.4 (Tiger) compilation errors on 4.1.11...
Hi, I've already seen this problem with gcc 4.0, I have to change size_socket declaration to socklen_t. Anyway, you won't be able to compile properly MySQL because of a bug in gcc 4.0 which will be fixed in 4.0.1. Take a look at http://gcc.gnu.org/bugzilla/show_bug.cgi?id=21173 for more details. Regards, Jocelyn Wells Oliver wrote: Has anyone else tried to compile 4.1.11 on OS 10.4? My compilation fails on mysqldd.cc: mysqld.cc: In function `int bootstrap(FILE*)': mysqld.cc:3350: warning: converting of negative value '-0x1' to 'ulong' mysqld.cc: In function `void* handle_connections_sockets(void*)': mysqld.cc:3589: error: invalid conversion from 'size_socket*' to 'socklen_t*' mysqld.cc:3589: error: initializing argument 3 of 'int accept(int, sockaddr*, socklen_t*)' mysqld.cc:3662: error: invalid conversion from 'size_socket*' to 'socklen_t*' mysqld.cc:3662: error: initializing argument 3 of 'int getsockname(int, sockaddr*, socklen_t*)' sql_list.h: At global scope: sql_list.h:401: warning: inline function `base_ilist::~base_ilist()' used but never defined make[4]: *** [mysqld.o] Error 1 make[3]: *** [all-recursive] Error 1 make[2]: *** [all] Error 2 make[1]: *** [all-recursive] Error 1 make: *** [all] Error 2 I believe 10.4 has gcc 4.0. Anyway; any tips appreciated. Thanks! -- Wells Oliver [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT with ON DUPLICATE error
Hi, This is a known bug which will be fixed in MySQL 4.1.11. See http://bugs.mysql.com/bug.php?id=8675 and http://bugs.mysql.com/bug.php?id=8147 Regards, Jocelyn Eli wrote: 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: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hi, For me it sounds like a glibc issue. BTW, currently the 4.1.10a build is compiled against glibc-2.2, does MySQL plan to build next releases against glibc-2.3 which seems to handle much better a high number of simultaneous connected threads ? Thanks ! Jocelyn Gleb Paharenko wrote: Hello. Please switch to the mysql-debug-4.1.10a version and send the error log with resolved stack trace. Include the output of the following statement as well: SHOW VARIABLES; I'm getting this strange error when there are more than 1100 mysql connections connected to the same server. What about ulimits and free memory of your system? Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, Yes - am using the standard binaries and have even upgraded to mysql-standard-4.1.10a-pc-linux-gnu-i686. I'm still getting this error - does anyone have any ideas?=20 Cheers, Andrew -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thu 31 March 2005 02:31 To: mysql@lists.mysql.com Subject: Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Hello. I strongly recommend you to upgrade to the latest release. Do you use official binaries? See: http://dev.mysql.com/doc/mysql/en/crashing.html Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, =20 I'm getting this strange error when there are more than 1100 mysql=20 connections connected to the same server. =20 [EMAIL PROTECTED] mysql]# bin/mysql bin/mysql: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of=20 available memory, you can consult the manual for a possible=20 OS-dependent bug' =20 I've had this running fine in the past with MySQL 4.0.17 and Red Hat=20 7.3 (linux 2.4..) but with the same hardware and MySQL versions using=20 Fedora core 2 (linux 2.6) I am getting these problems. =20 I have checked max_connections and others in my.cnf and all is good. I'm running 'out of the box' linux and 'out of the box' MySQL binaries. =20 Has anyone had this before? =20 I would love to hear your thoughts and ideas.. =20 Cheers for the help, =20 Andrew =20 SQL, Query =20 =20 -- For technical support contracts, goto https://order.mysql.com/?ref=3Densita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com --=20 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1.10 on Linux 2.4.9-e.59smp crash (UNCLASSIFIED)
Hi, It seems your latest resolve is wrong. (perhaps you've done it against 4.1.10 symbols ?) Doing it on 4.1.10a symbols gives me : 0x808b193 handle_segfault + 423 0x82debe8 pthread_sighandler + 184 0x80dbbf8 MYSQL_LOG::write(Log_event *) + 1564 0x80b3caf close_temporary_tables(THD *) + 247 0x8081c2f THD::cleanup(void) + 119 0x808ad8b end_thread(THD *, bool) + 19 0x8098c56 handle_one_connection + 950 0x82dc39c pthread_start_thread + 220 0x8305d2a thread_start + 4 which basically is the same than the ones you got before. Regards, Jocelyn C. Tate Baumrucker wrote: Classification: UNCLASSIFIED Caveats: NONE Implemented latest 4.1.10a-standard-log binary version and saw another crash w/in about 3 hrs. Here's the log: 050328 15:39:35 mysqld started 050328 15:39:36 InnoDB: Started; log sequence number 15 2379024139 /data/mysql/bin/mysqld: ready for connections. Version: '4.1.10a-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=6 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 802415 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x96225e90 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe7f458, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808b193 0x82debe8 0x80dbbf8 0x80b3caf 0x8081c2f 0x808ad8b 0x8098c56 0x82dc39c 0x8305d2a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=3549 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 050328 17:22:04 mysqld restarted 050328 17:22:04 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050328 17:22:04 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 15 2433246331. InnoDB: Doing recovery: scanned up to log sequence number 15 2435610788 050328 17:22:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 10782286, file name ./performdb-1-bin.000111 050328 17:22:06 InnoDB: Flushing modified pages from the buffer pool... 050328 17:22:06 InnoDB: Started; log sequence number 15 2435610788 /data/mysql/bin/mysqld: ready for connections. Version: '4.1.10a-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) And the stack trace: 0x808b193 mysql_unlock_read_tables__FP3THDP13st_mysql_lock + 131 0x82debe8 gbksortorder + 8 0x80dbbf8 mysql_prepare_update__FP3THDP13st_table_listT1PP4ItemUiP8st_order + 248 0x80b3caf yyparse__FPv + 59439 0x8081c2f sql_type__C10Field_geomR6String + 367 0x808ad8b mysql_errno_to_sqlstate + 43 0x8098c56 __static_initialization_and_destruction_0 + 5974 0x82dc39c my_strntol_8bit + 172 0x8305d2a canonicalize + 530 This one looks a bit different that the last ... Tate -Original Message- From: Baumrucker, Christopher T Mr ITA-IC/Lockheed Martin [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 3:22 PM To: 'mysql@lists.mysql.com' Subject: Mysql 4.1.10 on Linux 2.4.9-e.59smp crash (UNCLASSIFIED) Classification: UNCLASSIFIED Caveats: NONE All, Having stability issues w/ Mysql 4.1.10 (innodb) on RH Linux 2.4.9-e.59smp running AS 2.1. DB crashes randomly during execution of various SQL code (all using user variables and temporarly tables and mostly
Re: INSERT ON DUPLICATE KEY UPDATE
Hi, It could perhaps be related to bug #8147. http://bugs.mysql.com/bug.php?id=8147 Regards, Jocelyn Aleksandr Guidrevitch a écrit : Hi all ! I'm having problem with INSERT ... ON DUPLICATE KEY UPDATE on mysql 4.1.10 --8--8--8--8--8--8--8--8--8 INSERT INTO lot_end (id, owner_id, category_id, title, current_price, buy_price, end_time, price, bid_count, currency_name) SELECT lot.id, lot.owner_id, lot.category_id, lot.title, lot.current_price, lot.buy_price, lot.end_time, IF(lot.type_id = 2, lot.buy_price * exchange_rate.exchange_rate, lot.current_price * exchange_rate.exchange_rate) as priceA , COUNT(bid.id) as bid_countA, currency.name FROM lot INNER JOIN bid ON bid.lot_id = lot.id INNER JOIN currency ON currency.id = lot.currency_id INNER JOIN exchange_rate ON exchange_rate.currency_id = lot.currency_id WHERE lot.id = 1 GROUP BY lot.id ON DUPLICATE KEY UPDATE lot_end.price = VALUES(priceA), lot_end.bid_count = VALUES(bid_countA); --8--8--8--8--8--8--8--8--8 it reports ERROR 1054 (42S22): Unknown column 'priceA' in 'field list' Also, I've tried --8--8--8--8--8--8--8--8--8 ... on duplicate key update lot_end.price = IF(lot.type_id = 2, lot.buy_price * exchange_rate.exchange_rate,lot.current_price * exchange_rate.exchange_rate), lot_end.bid_count = COUNT(bid.id); --8--8--8--8--8--8--8--8--8 and it reports ERROR (HY000): Invalid use of group function Could anyone tell me how to use ON DUPLICATE KEY UPDATE with GROUP BY functions ? Sincerely, Aleksandr Guidrevitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit of a sql script
Hi, The only limit I'm aware of is the size of a query itself, which can not be bigger than the max_allowed_packet size. Regards, Jocelyn Mauricio Pellegrini a écrit : Hi , I'm using mysql 4.1.4 gamma with InnoDB suse 8.2 reiser fs. I'am doing backups using mysqldump which creates a sql script with the structure for all tables plus the correspondig commands to create them and also the data to be inserted in each one of them. Latter when I have to restore one of these backups I create the corresponding database an run the following from the shell mysql db_name db_name.sql This seems to work just fine so far, but I'am concerned about the size of this script ( db_name.sql has 450mb ). My question is: there could be a size problem when mysql handles the content of this script due to its size. In other words is there a limit on the size of a script that mysql can hable from the command line ? I hope I've made myself clear Best regards Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query_cache_size Question
Hi, How many questions for thoses results ? There's a lot of lowmem_prunes, so I would indeed increase the memory size to reduce the risk of lowmem_prunes. What is your query_cache_limit ? Jocelyn Mauricio Pellegrini a écrit : Thanks, this the result of show status like qcache% +-+--+ | Variable_name | Value| +-+--+ | Qcache_free_blocks | 3330 | | Qcache_free_memory | 13372320 | | Qcache_hits | 9149 | | Qcache_inserts | 1942009 | | Qcache_lowmem_prunes| 372898 | | Qcache_not_cached | 508528 | | Qcache_queries_in_cache | 3160 | | Qcache_total_blocks | 9711 | +-+--+ Am I right at saying that the value (16Mb)is ok ? Qcache_not_cached means the number of query results that didn't get into the cache because of a space limitation ? If so, perhaps adding few more Mb would improve that number , isn't it? On Wed, 2005-03-09 at 19:30, Dan Nelson wrote: In the last episode (Mar 09), Mauricio Pellegrini said: Hi, I'm trying to tweak the execution time for querys on my mysql server which is using InnoDB. I know that there are a lot of things to check but one of them, perhaps not the most important, is the query_cache_size. I have a dual Xeon cpu with 4gb of ram and this is a Linux dedicated server which runs only Apache and Mysql 4.1.5 I've declared Query_cache_size = 16M in the my.cnf file and I would say that in the whole we have 60% of select querys and a 40% of update querys. Is Query_cache_size=16M too conservative considering that I have 4Gb of ram ? A better question is: what is your current utilization of that 16MB cache? The query cache flushes any results dependant on a table when that table is modified, so if you really have 40% updates, chances are that very few queries are cachable at all. Run show status like 'qcache%'; , and take a look at free_memory, inserts, and hits. -- 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: Query_cache_size Question
Even with only 16 Mo, your query cache is quite efficient (78% of your queries are fetched directly from the cache). Try to increaze your query_cache_size until there's no more additional lowmem_prunes reported. And executed from time to time FLUSH QUERY CACHE to defragment your query cache. Jocelyn Mauricio Pellegrini a écrit : Thanks these are fresh results | query_cache_limit| 1048576 | | query_cache_min_res_unit | 4096 | show status like ques%; +---+--+ | Variable_name | Value| +---+--+ | Questions | 14189604 | +---+--+ show status like qcache%; +-+--+ | Variable_name | Value| +-+--+ | Qcache_free_blocks | 2731 | | Qcache_free_memory | 12372064 | | Qcache_hits | 11156553 | | Qcache_inserts | 1945643 | | Qcache_lowmem_prunes| 372898 | | Qcache_not_cached | 509594 | | Qcache_queries_in_cache | 4101 | | Qcache_total_blocks | 11000| +-+--+ On Wed, 2005-03-09 at 20:19, Jocelyn Fournier wrote: Hi, How many questions for thoses results ? There's a lot of lowmem_prunes, so I would indeed increase the memory size to reduce the risk of lowmem_prunes. What is your query_cache_limit ? Jocelyn Mauricio Pellegrini a écrit : Thanks, this the result of show status like qcache% +-+--+ | Variable_name | Value| +-+--+ | Qcache_free_blocks | 3330 | | Qcache_free_memory | 13372320 | | Qcache_hits | 9149 | | Qcache_inserts | 1942009 | | Qcache_lowmem_prunes| 372898 | | Qcache_not_cached | 508528 | | Qcache_queries_in_cache | 3160 | | Qcache_total_blocks | 9711 | +-+--+ Am I right at saying that the value (16Mb)is ok ? Qcache_not_cached means the number of query results that didn't get into the cache because of a space limitation ? If so, perhaps adding few more Mb would improve that number , isn't it? On Wed, 2005-03-09 at 19:30, Dan Nelson wrote: In the last episode (Mar 09), Mauricio Pellegrini said: Hi, I'm trying to tweak the execution time for querys on my mysql server which is using InnoDB. I know that there are a lot of things to check but one of them, perhaps not the most important, is the query_cache_size. I have a dual Xeon cpu with 4gb of ram and this is a Linux dedicated server which runs only Apache and Mysql 4.1.5 I've declared Query_cache_size = 16M in the my.cnf file and I would say that in the whole we have 60% of select querys and a 40% of update querys. Is Query_cache_size=16M too conservative considering that I have 4Gb of ram ? A better question is: what is your current utilization of that 16MB cache? The query cache flushes any results dependant on a table when that table is modified, so if you really have 40% updates, chances are that very few queries are cachable at all. Run show status like 'qcache%'; , and take a look at free_memory, inserts, and hits. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behaviour of USE INDEX(...)
Hi, I was assuming USE INDEX was only telling MySQL which INDEX it needed to use, but it seems to be not always the case. With MySQL 4.1.10, USE INDEX(topic) for the following query seems to change the way the index is used. mysql EXPLAIN SELECT numreponse FROM searchjoinhardwarefr13 WHERE id='24399' AND numreponse='2307728' AND topic='26369'; ++-++--+ --+---+-+---+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-++--+ --+---+-+---+--+--+ | 1 | SIMPLE | searchjoinhardwarefr13 | ref | PRIMARY,numreponse,topic | topic | 3 | const | 117 | Using where; Using index | ++-++--+ --+---+-+---+--+--+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT numreponse FROM searchjoinhardwarefr13 USE INDEX(topic) WHERE id='24399' AND numreponse='2307728' AND topic='26369'; ++-++---+---+--- +-+--+---+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-++---+---+--- +-+--+---+--+ | 1 | SIMPLE | searchjoinhardwarefr13 | range | topic | topic | 10 | NULL | 17761 | Using where; Using index | ++-++---+---+--- +-+--+---+--+ 1 row in set (0.00 sec) 'topic' is a compound index on (`topic`,`numreponse`,`id`) fields. Did I miss something about USE INDEX ? Is this the expected behaviour ? Thanks ! -- Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The table '#sql_6d1b_0' is full during multitable UPDATE
Hi, I've just encountered a strange problem when trying to update a table : UPDATE searchmainhardwarefr0, searchjoinhardwarefr0 SET searchmainhardwarefr0.numeropost=searchjoinhardwarefr0.topic WHERE searchmainhardwarefr0.numreponse=searchjoinhardwarefr0.numreponse; ERROR 1114 (HY000): The table '#sql_11be_0' is full searchjoinhardwarefr0 contains only 70624 rows, and searchmainhardwarefr0 contains 946113 rows. However I succeed in updating the table with the following query : UPDATE searchmainhardwarefr0, threadhardwarefr0 SET searchmainhardwarefr0.numeropost=threadhardwarefr0.numeropost WHERE searchmainhardwarefr0.numreponse=threadhardwarefr0.numreponse; threadhardwarefr0 contains 76291 ans is also larger on the disk. This sounds like a bug for me, but I want to be sure I didn't miss anything. I'm using MySQL-4.1.8 Thanks, Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 allows non-unique values in a unique index?!
Hi, This could also be a badly corrupted table, what does CHECK TABLE / REPAIR TABLE report ? Regards, Jocelyn - Original Message - From: Kevin A. Burton [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 03, 2005 10:04 PM Subject: MySQL 4.1.7 allows non-unique values in a unique index?! WOW! This is a really bad bug... mysql SHOW INDEX FROM LITERAL; +-++--+--+-+--- +-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++--+--+-+--- +-+--++--++-+ | LITERAL | 0 | PRIMARY |1 | ID | A |14331755 | NULL | NULL | | BTREE | | | LITERAL | 0 | VALUE|1 | VALUE | A |14331755 | NULL | NULL | | BTREE | | +-++--+--+-+--- +-+--++--++-+ Notice the unique index on VALUE ? Now take a look at this: mysql SELECT *, MD5(LITERAL.VALUE) AS MD5_VALUE FROM LITERAL WHERE ID=567344 OR ID=14076840; +--+---+--+ | ID | VALUE | MD5_VALUE| +--+---+--+ | 567344 | Law | 81588d326cebe6416d3904db93603af1 | | 14076840 | Law | 81588d326cebe6416d3904db93603af1 | +--+---+--+ 2 rows in set (0.00 sec) Seems like a fatal bug to me! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
Hi Vlad ! Why not using select (select min( a ) is null from a) or null; as a workaround ? Regards, Jocelyn - Original Message - From: Vlad Shalnev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 03, 2004 7:01 AM Subject: Re: Comparing bug in 4.1.7 Sergei Golubchik wrote: Hi! On Dec 02, Vlad Shalnev wrote: Looks like a bug. Could you submit a bugreport at http://bugs.mysql.com ? I've submitted a bugreport. Downgrade to 3.23 and wait for this problem solving. Thanks for all It happens after upgrade from 3.23.46. mysql create table a ( a int not null ); Query OK, 0 rows affected (0.00 sec) mysql select min( a ) is null or null from a; +--+ | min( a ) is null or null | +--+ | NULL | - Why ??? +--+ 1 row in set (0.00 sec) It is very important for me to solve this problem. Thanks for any help Regards, Sergei -- -- -- Vlad A. Shalnev E-mail: [EMAIL PROTECTED] Gravity can't be blamed for someone falling in love ( Albert Einstein ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query takes terribly long
Hi,
The only reason which could explain this is that statistics about the second
query are perhaps not uptodate.
Did you try to do an ANALYZE TABLE on it ?
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 11:05 AM
Subject: Re: Query takes terribly long
Hello,
Thanks for the hint!
With RESET QUERY CACHE, I'm always sure that the cache is empty and with
that, I've discovered that using the key as you said, with the datetime
field included, is much!!! faster than the other key (no filesort is
used).
But, the strange thing is that with the index
{put,front,topcategory,approvedby} (where it takes up to 5 seconds to
execute the query), explain tells me he has to process 5475 rows but
with your index {put,front,topcategory,putdatetime} (which seems to
always take up about 0.05 seconds, thank you very much), explain tells
me he has to process 6243 rows. And that's probably the reason why MySQL
keeps using my index and I need USE INDEX (...) to force him into using
yours.
But I don't understand the difference in rows to process... do you?
For now, I will put USE INDEX (...) into the query as I also need the
other key.
Dirk.
Jocelyn Fournier wrote:
Are you sure this not because your query is cached by the MySQL query
cache
now ? (try RESET QUERY CACHE before testing your query to be sure)
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Saturday, September 18, 2004 11:53 PM
Subject: Re: Query takes terribly long
Hello,
Now this I don't understand, now the query takes only 0.05s on any of
the two indexes.
I didn't do anything! Not even the analyze table I was going to try!
I'll keep you posted, thank you for the explanation, I understand the
reason for the filesort now.
Dirk.
Jocelyn Fournier wrote:
Hi,
Well that's strange MySQL says more rows will be returned.
How many times does the query takes ?
And if you run ANALYZE TABLE on your table, does this change anything
about
rows statistics ?
MySQL do not need filesort with this kind of index, because it can use
the
index to retrieve the row in the right order directly.
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Saturday, September 18, 2004 11:37 PM
Subject: Re: Query takes terribly long
Hello,
I added the index you said, but mysql (yes 4.x) still prefers using
the
other index {put,front,topcategory,approvedby}.
And indeed, he uses the filesort. When I force into using your index,
it
seems to be even slower (more rows but no filesort as you said)
But I have a question about that, why would your index avoid a
filesort?
I don't understand that.
Any other suggestion on how to speed up?
Anyway, thanks for all the help,
Dirk.
Jocelyn Fournier wrote:
Hi,
You can try to add an index on (put,front,topcategory,putdatetime) to
avoid
MySQL has to do a filesorting on the data returned. (I assume you're
using
MySQL 4.x)
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Dirk Schippers [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, September 18, 2004 10:25 PM
Subject: Query takes terribly long
Hello,
I have this MyISAM table story (52MB):
+-+-+--+-+-+-
-
-
-
+
| Field | Type| Null | Key | Default
|
Extra |
+-+-+--+-+-+-
-
-
-
+
| id | int(10) unsigned| | PRI | [NULL]
|
auto_increment |
| sequelof| int(10) unsigned| | | 0
||
| prevsequel | int(10) unsigned| | | 0
||
| userid | int(10) unsigned| | MUL | 0
||
| title | varchar(255)| YES | | [NULL]
||
| topcategory | tinyint(3) unsigned | | | 1
||
| category| tinyint(3) unsigned | | | 1
||
| rated | tinyint(1) unsigned | | | 0
||
| language| tinyint(3) unsigned | | | 0
||
| font| int(10) unsigned| | | 1
||
| fontsize| varchar(4) | | | 2
||
| story | mediumtext
Re: Query takes terribly long
Hi,
You can try to add an index on (put,front,topcategory,putdatetime) to avoid
MySQL has to do a filesorting on the data returned. (I assume you're using
MySQL 4.x)
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Dirk Schippers [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, September 18, 2004 10:25 PM
Subject: Query takes terribly long
Hello,
I have this MyISAM table story (52MB):
+-+-+--+-+-+
+
| Field | Type| Null | Key | Default |
Extra |
+-+-+--+-+-+
+
| id | int(10) unsigned| | PRI | [NULL] |
auto_increment |
| sequelof| int(10) unsigned| | | 0
||
| prevsequel | int(10) unsigned| | | 0
||
| userid | int(10) unsigned| | MUL | 0
||
| title | varchar(255)| YES | | [NULL]
||
| topcategory | tinyint(3) unsigned | | | 1
||
| category| tinyint(3) unsigned | | | 1
||
| rated | tinyint(1) unsigned | | | 0
||
| language| tinyint(3) unsigned | | | 0
||
| font| int(10) unsigned| | | 1
||
| fontsize| varchar(4) | | | 2
||
| story | mediumtext | | |
||
| note| text| | |
||
| adddatetime | datetime| | | -00-00 00:00:00
||
| putdatetime | datetime| | MUL | -00-00 00:00:00
||
| put | tinyint(1) | | MUL | 0
||
| putby | int(10) unsigned| | | 0
||
| approvedby | int(10) unsigned| | | 0
||
| blockbot| tinyint(1) unsigned | | | 0
||
| front | tinyint(1) | | | 1
||
| selection | tinyint(1) | | | 0
||
| timesread | int(10) unsigned| | | 0
||
| ipnumber| varchar(20) | | |
||
| words | int(11) | | | 0
||
| review | tinyint(1) unsigned | | MUL | 0
||
| avgscore| int(10) unsigned| YES | | [NULL]
||
| numvotes| int(10) unsigned| YES | | [NULL]
||
| numreacts | int(10) unsigned| YES | | [NULL]
||
+-+-+--+-+-+
+
with indexes: id = primary, putdatetime, userid,
{put,front,topcategory,approvedby} and review.
If I want to know the 30 most recently added and approved items, I do
the following simple query:
SELECT id FROM story WHERE put=1 AND front=1 AND topcategory=1 ORDER BY
putdatetime DESC LIMIT 0,30
This query sometimes takes up to 10 seconds!!!
I must admit that the cache is disabled at this moment (I want to see
realistic timings), but still I think 10 seconds is terribly long to
retrieve 30 id's!
Explain tells me that it is using the index
{put,front,topcategory,approvedby} and narrows the query to 5475 rows
(of the total of 18818).
The table will certainly grow a lot in the future so I am very worried
about the performance.
What can I do about this? Is there any way to improve this?
Enabling the cache is not an option as the data in the table is altered
a lot.
Anyone?
Dirk.
--
Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com
Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.
--
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: Mysqld segfaults irregulary on debian/opteron platform
Hi,
If you're using 4.0.20, jump to 4.0.21 :
Fixed crash in MATCH ... AGAINST() on a phrase search operator with a
missing closing double quote.
Regards,
Jocelyn
Hi,
Our mysqld segfaults from time to time on our system. We don't see any
other programs segfaulting, and therefore suspect a bug in mysql.
I enclose a resolved stacktrace, and any other system information that
might be helpful. I hope someone has any suggestions on what to do in
this
matter. Should we file a mysql bug-report?
Please advise. I am happy to provide any additional information that you
should find necesarry to resolve the issue.
Sincerely,
Anders Schau Knatten
Norway
OS: Debian GNU/Linux
Kernel: Linux 2.6.8-rc1 #4 SMP Fri Jul 16 18:03:51 CEST 2004 x86_64
unknown
CPU: Dual AMD Opteron 250
Resolved stack-trace:
0x80eb61f handle_segfault + 423
0x5567ff54 _end + 1294348216
0x832bd1a ft_init_boolean_search + 586
0x832bed7 ft_init_boolean_search + 1031
0x832c57d ft_boolean_find_relevance + 513
0x832c303 ft_boolean_read_next + 835
0x814a7cc ft_read__9ha_myisamPc + 52
0x812037f error_if_full_join__FP4JOIN + 1791
0x811a496 create_myisam_from_heap__FP3THDP8st_tableP15TMP_TABLE_PARAMib
+ 1870
0x811a256 create_myisam_from_heap__FP3THDP8st_tableP15TMP_TABLE_PARAMib
+ 1294
0x8112dd8
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP13select_result
+ 7000
0x811f963 handle_select__FP3THDP6st_lexP13select_result + 95
0x80f7d7e mysql_execute_command__Fv + 1010
0x80fb438 mysql_parse__FP3THDPcUi + 568
0x80f6ed1 dispatch_command__F19enum_server_commandP3THDPcUi + 1469
0x80fcadd do_command__FP3THD + 153
0x80f6187 handle_one_connection + 639
0x5567d0ba _end + 1294336286
0x55814d6a _end + 1296006606
Additional info from syslog:
Sep 14 03:14:05 mysqld[21855]: Cannot determine thread, fp=0xff3feb58,
backtrace may not be correct.
Sep 14 03:14:05 mysqld[21855]: Stack range sanity check OK, backtrace
follows:
(...)
Sep 14 03:14:05 mysqld[21855]: New value of fp=(nil) failed sanity
check,
terminating stack trace!
(...)
Sep 14 03:14:05 mysqld[21855]: Trying to get some variables.
Sep 14 03:14:05 mysqld[21855]: Some pointers may be invalid and
cause the
dump to abort...
Sep 14 03:14:05 mysqld[21855]: thd-query at 0x8599938 = SELECT t.tid^M
Sep 14 03:14:05 mysqld[21855]: ^I^I^I^I^I^I^IFROM ippbe_topics t^M
Sep 14 03:14:05 mysqld[21855]: ^I^I^I^I^I^I^IWHERE t.last_post
1092531929 AND t.forum_id IN
(27,190,89,11,12,13,14,15,16,17,18,19,20,23,24,25,26,28,87,30,31,32,33,35,37,38,40,41,46,44,48,51,53,
54,57,58,60,61,62,63,64,75,65,66,67,82,69,81,72,73,76,78,79,80,83,84,88,86,91,108,94,95,96,131,98,132,130,101,133,103,104,107,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,
128,129,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,156,155,157,158,159,162,161,160,184,189,179,178,182,181,175,169,172,171,174,177,176,185,183,180,187,188,191,19
2,193,200,194,195,196,197,198,199,203,202,209,206,208,210,212,211,213,215,214,216,217,219,218)^M
Sep 14 03:14:05 mysqld[21855]: ^I^I^I^I^I^I^I AND t.approved=1 AND
MATCH(title) AGAINST ('resette' IN BOOLEAN MODE)
Sep 14 03:14:05 mysqld[21855]: thd-thread_id=127169
--
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: Problem with Mysql 4.0.18 + Debian
Hi,
A quick fix would be to set the wait_timeout variable in the my.cnf to a
much smaller value than 28800 (default value).
Try to add wait_timeout=60 in the my.cnf for example, the connections should
be automatically closed after 60 secondes if there are not used anymore.
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Jan Kirchhoff [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 7:43 PM
Subject: Re: Problem with Mysql 4.0.18 + Debian
That sounds like a typical mod_perl-problem. The script is making new
connections and doesn't close the old ones.
You should add debug-code to your script and add
* * * * * root mysql -e 'show processlist'
/tmp/mysql_processlist_debug_`date +%s`.txt
to your /etc/crontab in order to log the processlist once every minute
in a txt-file in /tmp
Jan
[EMAIL PROTECTED] wrote:
Thanks for the two responses.
William Mussatto said:
Are you running mod_perl?
Yes I am, with Perl 5.8.3.
Victor Pendleton said:
What does mysql show processlist look like?
Here is what it looks like currently, but the system is not in its
unresponsive phase right now. I can't force it to go all wonky on me,
it will probably be tomorrow before the process count explodes again.
++-+---+-+-+--+---+
--+
| Id | User| Host | db | Command | Time | State | Info
|
++-+---+-+-+--+---+
--+
| 8 | citidel | localhost | citidel | Sleep | 0| | NULL
|
| 71 | citidel | localhost | citidel | Sleep | 2192 | | NULL
|
| 72 | citidel | localhost | citidel | Sleep | 2141 | | NULL
|
| 78 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 79 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 87 | citidel | localhost | citidel | Sleep | 741 | | NULL
|
| 88 | citidel | localhost | citidel | Sleep | 730 | | NULL
|
| 89 | citidel | localhost | citidel | Sleep | 607 | | NULL
|
| 95 | citidel | localhost | citidel | Query | 0| NULL | show
processlist |
++-+---+-+-+--+---+
-
Ryan Richardson said:
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/2/04 9:16 AM
Subject: Problem with Mysql 4.0.18 + Debian
Hello:
I posted this before but I have not gotten a response. I have a
Debian
(woody) server running a good sized database (7.2GB of db files), Mysql
4.0.18. I am running Apache 1.3.29 + perl and using mysql as the
backend. In my.cnf, I have max_connections=300.
Here's the problem. I had the site up several days, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running. However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes. Formerly
I'd
get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm line
25
However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable, so
the website becomes unreachable.
I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems. I am wondering if there is a connection.
I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded. I have tried this scenario at least a
dozen times, and the same thing always happens.
Ryan
--
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 in Mysql 4.0
Hi, AFAIK, date is *not* a reserved keyword, not need to backtick it :) Regards, Jocelyn Fournier www.presence-pc.com - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: fgmmoribe [EMAIL PROTECTED] Cc: mysql [EMAIL PROTECTED] Sent: Tuesday, July 27, 2004 7:11 AM Subject: Re: select in Mysql 4.0 fgmmoribe wrote: I have a table like this +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | int(3)| | PRI | NULL| auto_increment | | idTable | int(3) unsigned | | | 0 | | | title | varchar(150) | YES | | NULL| | | description | varchar(150) | YES | | NULL| | | date| datetime | YES | | NULL| | +-+---+--+-+-++ Is there anyway to make select command like this in Mysql 4.0: select * from #temp where cod in (select max(cod) from #temp group by idtable) order by data desc could someone help me? thanks Fernando Subqueries require mysql 4.1. date is a reserved word, so not the best choice for a column name. You'll always have to quote it with backticks to use it. Your query doesn't seem to match your table. That said, I think you want http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. Michael -- 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: Aborted connection error (error reading comm packets) ?
Hi, Add skip-log-warnings in your my.cnf. log-warnings seems to have been enabled by default since 4.0.19. Regards, Jocelyn - Original Message - From: Don MacAskill [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 08, 2004 11:29 AM Subject: Aborted connection error (error reading comm packets) ? I just switched from 4.0.18-max to 4.0.20-max on AMD64 and I'm getting tons of these in my error log: 040708 2:24:12 Aborted connection 65531 to db: 'db' user: 'user' host: `10.1.1.27' (Got an error reading communication packets) When I switch back to 4.0.18-max, they go away. These are the stock compiled .tar.gz distributions. My service seems to still run fine, and it's not reporting not being able to connect. Multiple hosts get the error, not just one IP. Any ideas? Thanks, Don -- 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: Re: Production release of MySql 4.1
Hi, 4.1.3 is labeled beta in the bktree. So I assume 4.1.3 will be beta when it will be released ;) Regards, Jocelyn - Original Message - From: Andrew Pattison [EMAIL PROTECTED] To: Jon Frisby [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 11:45 PM Subject: [SPAM] Re: Production release of MySql 4.1 I saw reference somewhere (I believe it was either an Apache or PHP discussion) to 4.1.3 being beta but I'm not sure if this was just wishful thinking on the part of those particular developers. If this is the case then going by the dates of previous releases in the 4.1 branch (not always a good guide) then 4.1 will go beta somewhere around the period November 2004 to February 2005. How long was 4.0 is alpha? I seem to recall it was more than 6 months. Are you able to run 4.1 in some sort of test environment to see how it performs for you? You may find it works well enough to deploy right now, you may not. A major bug for one guy may not affect you at all - it could be platform specific or affect a feature you don't use. Cheers Andrew. - Original Message - From: Jon Frisby [EMAIL PROTECTED] To: 'Jonathan Soong' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 10:09 PM Subject: RE: Production release of MySql 4.1 As I understand it, the particular cycle a release is in depends on how long it's been since a major bug was reported. So an alpha becomes a beta if nobody reports a major bug after N days, and a beta becomes a production release if goes N days without a major bug report. Thus, even if 4.1.3 is released as alpha, it could retroactively be declared beta, and then even release -- although that's pretty unlikely. The long and short of it though, is that nobody can tell you how long until 4.1 will go beta. -JF -Original Message- From: Jonathan Soong [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 7:20 PM To: Jocelyn Fournier Cc: John Murphy; Emmanuel van der Meulen; [EMAIL PROTECTED] Subject: Re: Production release of MySql 4.1 Jocelyn Fournier wrote: Hi, AFAIK 4.1.3 should be beta. It is a little frustrating, at Linux Conf Adelaide 2004 (January), the Mysql guy there said that 4.1 would be in beta, in the next few weeks ... Its now July and its still in Alpha. It says on the webpage MySQL 4.1 -- Alpha release (use this for new development) - and it has said that for 6months+ So we did our development on 4.1, and were expecting it to be beta by February 2004. We're ready to roll it out as soon as it hits beta, i told my boss it would be in beta by March 2004 at the latest. We now have hardware sitting for around with 4.1 alpha on it that cannot be deployed. Does anyone actually have a concrete date when 4.1 will go into beta? Cheers Jon -- Jonathan Soong Information Services Institute of Medical and Veterinary Science (IMVS) Email: [EMAIL PROTECTED] Web : http://www.imvs.sa.gov.au Tel : +61 8 82223095 Fax : +61 8 82223147 -- 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: Re: Production release of MySql 4.1
Yes indeed, Lenz is preparing the build and has updated the news section. (let's hope no critical bugs will be discovered which could slip the release :)) Jocelyn - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Jocelyn Fournier [EMAIL PROTECTED] Cc: Andrew Pattison [EMAIL PROTECTED]; Jon Frisby [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, July 01, 2004 12:02 AM Subject: Re: Re: Production release of MySql 4.1 On Wed, Jun 30, 2004 at 11:52:32PM +0200, Jocelyn Fournier wrote: Hi, 4.1.3 is labeled beta in the bktree. So I assume 4.1.3 will be beta when it will be released ;) And that looks to be soon, based on the commits I've seen. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Production release of MySql 4.1
Hi, AFAIK 4.1.3 should be beta. Regards, Jocelyn - Original Message - From: John Murphy [EMAIL PROTECTED] To: Emmanuel van der Meulen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, June 09, 2004 12:31 AM Subject: Re: Production release of MySql 4.1 Emmanuel van der Meulen wrote: Daniel Kasak wrote on Tuesday, June 08, 2004 00:38; Emmanuel van der Meulen wrote: Hello all, Does anyone have an estimate of when MySql 4.1 will be released for production. MySql.cm says soon. Please advise, would that likely be weeks/months? Kind regards Emmanuel Months. I'd say at least 6 months. Thank you for the note and valuable feedback. Kind regards Emmanuel How about when 4.1 will go beta. Is that closer? -- 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]
Process in waiting for table state after an ALTER TABLE
Hi, I'm using MySQL 4.0.18-max (same problem with 4.0.18-standard) on one server, and I encounter a strange problem : When I make some change on a table (adding index for exemple), at the end of the ALTER process all the queries that must write on this table switch in the Waiting for table state forever. A flush table doesn't solve the problem and I have to kill mysql and restart the server... Last time this problem occurs, I was converting a table to InnoDB, and the Waiting for table problems occured when the ALTER TABLE switched to renaming table state, at this end of the ALTER process... When I restarted mysql, the InnoDB table was OK. Any idea of what could happen on this server, since it's the first time I see this kind of problem with MySQL ? Thanks, Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stability of MyISAM-Ststis vs. Dynamic - (was: Automatic conversion from `char` TO `varchar`)
Hi, If all your fields have a fixed length, you can change the type of the table by doing : ALTER TABLE your_table ROW_FORMAT=fixed; Regards, Jocelyn - Original Message - From: Merten Christian [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, February 09, 2004 10:01 AM Subject: Stability of MyISAM-Ststis vs. Dynamic - (was: Automatic conversion from `char` TO `varchar`) As to the discussion about char vs. varchar fields, I am now into a problem which is near by the above one: We are about to design a new database with high importance to stability and crash recovery. So I found, that two types of MyISAM tables exist (static and dynamic). If you have only fixed length fields, the table will be static. But when you add just one field of variable length, the table will be dynamic. Therefore, I tried to move any i.e. varchar type fields to char, but the type of the table does not change when I am using the ALTER statement. Right now, I only can do this conversion by dumping data and structure, removing the table, changing the fields and then inserting structure and data again. * Is there another way to do this ? * Has anybody ever had to rebuild a corrupted table, and is rebuiling a static table really easier for software than rebuilding a dynamic one? Greetinx, Chris SZM Studios * Dipl.Ing. Christian Merten * Ingenieur Broadcast-Support Oberwallstraße 6 * 10117 Berlin Tel. [030] 2090-3167 * Fax [030] 2090-3092 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.szm-studios.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Track error - Commands Out of Sync
Hi, Do you use PHP ? If so, which version is installed ? Regards, Jocelyn - Original Message - From: Free Grafton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 10:05 PM Subject: How to Track error - Commands Out of Sync We are currently running about 50 databases on MySQL 4.0.14. Occasionally our users will get the error: Couldn't select database. Message: Commands out of sync; You can't run this command now Message Number: 2014 When this happens to one customer, it affects them all. We end up having to restart the MySQL services to clean things up. Our goal is to eliminate whatever is causing this, but cannot determine the cause. I have checked the online help on MySQL and we are not calling the functions normally associated with the error. Any thoughts would be greatly appreciated. Free Grafton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Track error - Commands Out of Sync
So take a look here : http://bugs.php.net/bug.php?id=19529 and upgrade your PHP version, since it's fixed in PHP 4.3.x ;) Jocelyn - Original Message - From: Free Grafton [EMAIL PROTECTED] To: Jocelyn Fournier [EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 10:23 PM Subject: Re: How to Track error - Commands Out of Sync Jocelyn, We use PHP for our application. We are running version 4.2.3. Thanks for the help. On 8/26/03 2:16 PM, Jocelyn Fournier [EMAIL PROTECTED] wrote: Hi, Do you use PHP ? If so, which version is installed ? Regards, Jocelyn - Original Message - From: Free Grafton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 10:05 PM Subject: How to Track error - Commands Out of Sync We are currently running about 50 databases on MySQL 4.0.14. Occasionally our users will get the error: Couldn't select database. Message: Commands out of sync; You can't run this command now Message Number: 2014 When this happens to one customer, it affects them all. We end up having to restart the MySQL services to clean things up. Our goal is to eliminate whatever is causing this, but cannot determine the cause. I have checked the online help on MySQL and we are not calling the functions normally associated with the error. Any thoughts would be greatly appreciated. Free Grafton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Free Grafton Church Community Builder, Inc. http://www.churchcommunitybuilder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert...on duplicate key update
Hi,
ON DUPLICATE KEY UPDATE is only available with MySQL 4.1.x.
Regards,
Jocelyn
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, July 08, 2003 8:32 AM
Subject: Insert...on duplicate key update
Description:
I have been writing an update program in perl, and sought to make use of
the
insert...update construct described in the insert syntax section of the
manual supplied
with the download of mysql-4.0.13-standard. However, upon using the
construct in my code,
I receive syntax error messages centered on the 'on duplicate key
update..' section of the
statement.
How-To-Repeat:
here is my statement in a simplified form, given directly to the
database:
mysql insert into connections (taskID, deadline, user) values
('001',7,'Bob')
- ON DUPLICATE KEY UPDATE deadline=deadline+7, user='Jane';
i even tried removing all but on ecolumn name from the statement, with the
followiign error still resulting:
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'ON DUPLICATE KEY UPDATE user='Jane'' at line 2
The column names are correct
i have noticed that this section no longer exists in the current version
of the manual, but assumed
it would still be correct given I have been working from the manual
distributed with version I'm
running. An explaination of where I'm going wrong, or a simple
confirmation of the removal of the
insert...update construct would be extremely helpful/appreciated.
Fix:
Submitter-Id: submitter ID
Originator:
Organization:
--
Zoe Scaife - System Support Engineer Intern
==
,-_|\ Sun ServicesE-mail : [EMAIL PROTECTED]
/ \ Sun Microsystems Australia Direct : +61 (8) 8414 9320
\_,-*_/ Level 7, 41 Currie St Phone : +61 (8) 8414 9350
v Adelaide SA 5000 mobile : 0402 256 628
==
The optimist views the glass as half full,
The pessimist sees it as half empty -
Engineers know the glass is twice as big as it needs to be :)
MySQL support: none
Synopsis: manual description of insert...update syntax appears to be
incorrect
Severity: non-critical
Priority: medium
Category: mysql
Class: doc-bug
Release: mysql-4.0.13-standard (Official MySQL-standard binary)
Server: ./mysqladmin Ver 8.40 Distrib 4.0.13, for sun-solaris2.8 on
sparc
Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.13-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 22 hours 31 min 8 sec
Threads: 2 Questions: 470 Slow queries: 0 Opens: 18 Flush tables: 1
Open tables: 5 Queries per second avg: 0.006
C compiler:gcc (GCC) 3.2
C++ compiler: gcc (GCC) 3.2
Environment:
System: SunOS kwel 5.8 Generic_108528-14 sun4u sparc SUNW,Ultra-4
Architecture: sun4
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc
/usr/dist/pkgs/devpro/5.x-sparc/bin/cc
GCC: Reading specs from
/opt/sfw/lib/gcc-lib/sparc-sun-solaris2.8/2.95.3/specs
gcc version 2.95.3 20010315 (release)
Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer'
CXX='gcc'
CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -
fno-rtti' LDFLAGS='' ASFLAGS=''
LIBC:
-rw-r--r-- 1 root bin 1768540 May 25 2002 /lib/libc.a
lrwxrwxrwx 1 root root 11 Nov 19 2001 /lib/libc.so -
./libc.so.1
-rwxr-xr-x 1 root bin 1146284 May 25 2002 /lib/libc.so.1
-rw-r--r-- 1 root bin 1768540 May 25 2002 /usr/lib/libc.a
lrwxrwxrwx 1 root root 11 Nov 19 2001 /usr/lib/libc.so -
./libc.so.1
-rwxr-xr-x 1 root bin 1146284 May 25 2002 /usr/lib/libc.so.1
Configure command: ./configure '--prefix=/usr/local/mysql'
'--with-comment=Official MySQL-standard binary'
'--with-extra-charsets=complex' '--with-server-suffix=-standard'
'--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler'
'--with-named-z-libs=no' '--with-named-curses-libs=-lcurses'
'--disable-shared' '--with-innodb' 'CC=gcc'
'CFLAGS=-O3 -fno-omit-frame-pointer'
'CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -
fno-rtti' 'CXX=gcc'
Perl: This is perl, version 5.005_03 built for sun4-solaris
--
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: Strange Mull in show fields and table keeps crashing
Hi, What about upgrading to MySQL 4.0.13 ? 4.0.0 is not a production release, and a lot of bugs have been fixed between 4.0.0 and 4.0.13. Regards, Jocelyn - Original Message - From: Karl J. Stubsjoen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 11:13 PM Subject: Strange Mull in show fields and table keeps crashing Hello, I have this table: CLUB Field Type Null Key Default Extra clubid int(11) PRI NULL auto_increment clubusgf int(11) UNI 0 program char(3) url varchar(75) email varchar(75) phone varchar(14) fax varchar(14) address1 varchar(75) MUL address2 varchar(75) city varchar(75) notes varchar(255) zip varchar(10) clubname varchar(75) contact_primary varchar(40) state char(2) contact_secondary varchar(40) See address1 above the the MUL next to it. What is that? Also, this table keeps crashing, it crashes when I make an edit to anything in this field. So I copy the column, move the data over and then I can make edits in this field. However, another field in my table will get this strange MUL indication. From this point forward then, any changes to the data in that column will cause the table to crash. I've succesfully repaired the table a 1/2 dozen times or so... and copied/renamed about 4 of the columns as they took on this MUL characteristic. Any ideas how to fix this problem? Any ideas what is going on? Here is my version of MySQL: Server version: 4.0.0-alpha Karl -- 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: Can we crypt passwords on MySQL
Hi,
Using PASSWORD function to crypt password is not recommended, it's mainly
used for internal mysql password encryption.
Prefer using MD5/SHA1 functions to encrypt a password.
Regards,
Jocelyn
- Original Message -
From: [EMAIL PROTECTED]
To: Grégoire Dubois [EMAIL PROTECTED]
Cc: 'Mysql' [EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:14 AM
Subject: RE: Can we crypt passwords on MySQL
There is a PASSWORD('your_clear_text_password_here') function you can use
wherever you define a new password. See the manual for more.
Lian
-Original Message-
From: Grégoire Dubois [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:55 AM
Cc: 'Mysql'
Subject: Can we crypt passwords on MySQL
Hi all,
Is it possible to crypt the passwords on MySQL. If yes, how does it
work, and how is it to be implemented.
Any link would be great.
Thank you.
Grégoire Dubois.
--
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: Can we crypt passwords on MySQL
It's not recommended because in MySQL 4.1, it returns a different result
each time PASSWORD() is used, with the same input string.
For MD5/SHA1 function, take a look here :
http://www.mysql.com/doc/en/Miscellaneous_functions.html
Jocelyn
- Original Message -
From: development [EMAIL PROTECTED]
To: 'Jocelyn Fournier' [EMAIL PROTECTED]
Cc: 'Mysql' [EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:38 AM
Subject: RE: Can we crypt passwords on MySQL
Jocelyn
Why is that not recommended ?
Do you have any links for how to encrypt the password with MD5 ?
Freddie
-Original Message-
From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]
Sent: Freitag, 27. Juni 2003 00:19
To: [EMAIL PROTECTED]; Grégoire Dubois
Cc: 'Mysql'
Hi,
Using PASSWORD function to crypt password is not recommended, it's mainly
used for internal mysql password encryption.
Prefer using MD5/SHA1 functions to encrypt a password.
Regards,
Jocelyn
- Original Message -
From: [EMAIL PROTECTED]
To: Grégoire Dubois [EMAIL PROTECTED]
Cc: 'Mysql' [EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:14 AM
Subject: RE: Can we crypt passwords on MySQL
There is a PASSWORD('your_clear_text_password_here') function you can use
wherever you define a new password. See the manual for more.
Lian
-Original Message-
From: Grégoire Dubois [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:55 AM
Cc: 'Mysql'
Subject: Can we crypt passwords on MySQL
Hi all,
Is it possible to crypt the passwords on MySQL. If yes, how does it
work, and how is it to be implemented.
Any link would be great.
Thank you.
Grégoire Dubois.
--
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: Can we crypt passwords on MySQL
The 'user' table in the 4.3.7 chapter is a table used by the authentication
system in MySQL :)
Jocelyn
- Original Message -
From: [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]; Grégoire Dubois
[EMAIL PROTECTED]
Cc: 'Mysql' [EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:42 AM
Subject: RE: Can we crypt passwords on MySQL
Strange...
This is what the online manual says in 4.3.7 Setting up passwords:
Passwords must be encrypted when they are inserted in the user table, so
the
INSERT statement should have been specified like this instead:
mysql INSERT INTO user (Host,User,Password)
- VALUES('%','jeffrey',PASSWORD('biscuit'));
You must also use the PASSWORD() function when you use SET PASSWORD
statements:
mysql SET PASSWORD FOR jeffrey@% = PASSWORD('biscuit');
However in 6.3.6.2 Miscellaneous Functions it says indeed:
PASSWORD(str)
OLD_PASSWORD(str)
[...]
Note: The PASSWORD() function is used by the authentication system in
MySQL
Server, you should NOT use it in your own applications. For that purpose,
use MD5() or SHA1() instead. Also see RFC-2195 for more information about
handling passwords and authentication securely in your application.
Whom to believe?
Lian
-Original Message-
From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]
Sent: Friday, June 27, 2003 1:19 AM
To: [EMAIL PROTECTED]; Grégoire Dubois
Cc: 'Mysql'
Subject: Re: Can we crypt passwords on MySQL
Hi,
Using PASSWORD function to crypt password is not recommended, it's
mainly
used for internal mysql password encryption.
Prefer using MD5/SHA1 functions to encrypt a password.
Regards,
Jocelyn
- Original Message -
From: [EMAIL PROTECTED]
To: Grégoire Dubois [EMAIL PROTECTED]
Cc: 'Mysql' [EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:14 AM
Subject: RE: Can we crypt passwords on MySQL
There is a PASSWORD('your_clear_text_password_here') function
you can use
wherever you define a new password. See the manual for more.
Lian
-Original Message-
From: Grégoire Dubois [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:55 AM
Cc: 'Mysql'
Subject: Can we crypt passwords on MySQL
Hi all,
Is it possible to crypt the passwords on MySQL. If yes, how does it
work, and how is it to be implemented.
Any link would be great.
Thank you.
Grégoire Dubois.
--
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: upgrading to v 4.1
Hi, What do you call hung up ? All query appearing in opening table state when doing a show processlist ? If this is the case, I'm experiencing the same problem here with Linux. Regards, Jocelyn - Original Message - From: LS [EMAIL PROTECTED] To: Ted Rogers [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 12:42 AM Subject: Re: upgrading to v 4.1 I don't know about Mac OS X, but I can tell you that with 4.1 on FreeBSD w/LinuxThreads, my application's query threads get hung up almost instantly, requiring a kill -9. When I back down to 4.0.13 (and 3.23), I don't see that particular problem anymore. So keep an eye out for how it behaves for you on Max OS X under load. --- Ted Rogers [EMAIL PROTECTED] wrote: Is it recommended that I upgrade MySQL 3.23.53 to 4.1.x? Are then any special notes I might remember when doing so? (Mac OS X Server 10.2.6) Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql.log error - Please help
Hi, Or simply use perror 28 :) [EMAIL PROTECTED]:~$ perror 28 Error code 28: No space left on device Regards, Jocelyn - Original Message - From: Brian Reichert [EMAIL PROTECTED] To: Minal Amle [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 04, 2003 9:06 AM Subject: Re: Mysql.log error - Please help On Fri, Apr 04, 2003 at 12:22:18PM +0530, Minal Amle wrote: Hello, I am facing a problem while inserting data in one of the tables of my mysql database as: Error writing file '/var/log/mysql.log' (Errcode :28) I suspect this 'Errcode' corresponds to 'errno' under UNIX systems. On mine, that would corresspond to: #define ENOSPC 28 /* No space left on device */ My box, a FreeBSD box, has this constant in /usr/include/sys/errno.h. your system may vary... man errno Good luck... Thanks in advance. Regards, Minal Amle Software Engineer OAS Information Systems Pvt. Ltd. #2, Symphony-C, Range Hills Road, Pune 411020 Tel: +91 20 5520311 Fax: +91 20 5520312 Email: [EMAIL PROTECTED] http://www.oas.co.in -- Brian 'you Bastard' Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303 Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble with MySQL4, host field in show processlist
Hi, I opened today a bug report for a similar bug here : http://bugs.mysql.com/bug.php?id=189 This problem only happens with DELAYED thread. Regards, Jocelyn - Original Message - From: Sasa Ugrenovic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 5:37 PM Subject: Trouble with MySQL4, host field in show processlist Hi, My first post here l;) Ok, here it goes. I recently installed MySQL4 and transfered all my databases there. Now, it all works great, but the host field, in show processlist command, don't work correctly. It don't display correct info from where the query has came. Example: mysql show processlist; ++--+-+--++- -++--+ | Id | User | Host| db | Command| Time | State | Info | ++--+-+--++- -++--+ | 32 | DELAYED | localhost | db1 | Delayed_insert | 0 | Waiting for INSERT | | | 1387 | DELAYED | localhost | db2 | Delayed_insert | 68 | Waiting for INSERT | You see the localhost, value for host. It isn't correct. These queries are from different server, not from localhost. I'm executing show processlist from localhost only. Anyone know how can i fix this, or something to show correct info ? Thanks in advance. -- 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: command line exectution of query(newbie)
Hi, The right DELETE syntax is : delete from table name where id 5; Regards, Jocelyn - Original Message - From: Anil Garg [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 06, 2003 6:26 PM Subject: command line exectution of query(newbie) Hi, What am i doing wrong: # ./mysql database name -e delete * from table name where id 5; I get the mysql help page on exectuting the above command. The mysql verison i am using is : 3.23.52. Thanks and regards anil. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Report a bug
Hi, AFAIK, Aux is indeed a special filename in Windows (if I execute Aux under the W2K console, it opens the open with window). Regards, Jocelyn - Original Message - From: Pedro Vasconcelos [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, February 15, 2003 6:20 PM Subject: Re: Report a bug It works fine on Unix. Is Aux a special filename in Windows? Not that i am aware of. Maybe you should try this on Windows. I could be just a problem on windows version. Thanks. On Sat, 15 Feb 2003, Paul DuBois wrote: At 18:10 + 2/15/03, Pedro Vasconcelos wrote: The NameOfFile.sql contains: # # Table structure for table 'Aux' # DROP TABLE IF EXISTS Aux; CREATE TABLE Aux ( PrecoBase decimal(40,2) default NULL ) TYPE=MyISAM; # # Dumping data for table 'Aux' # LOCK TABLES Aux WRITE; INSERT INTO Aux VALUES (100.00),(100.11),(100.00),(100.22),(101.00),(102.00); UNLOCK TABLES; when i try this command: mysql -u USER -p DATABASE NameOfFile.sql (after i insert the password) it blocks. I am using 3.23.55 in win2000 It works fine on Unix. Is Aux a special filename in Windows? On Sat, 15 Feb 2003, Paul DuBois wrote: At 17:44 + 2/15/03, Pedro Vasconcelos wrote: if you have a file NameOfFile.sql where you have a table named Aux and you try to create it using: mysql -u USER -p DATABASE NameOfFile.sql when it reaches the definition of table Aux, this will block the execution of the command. I have no problem creating a table named Aux, there's probably something wrong with the statement other than the table name. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: non-unique indicies in HEAP tables handled incorrectly.
Hi, I just want to confirm MySQL 4.0.8 seems to be broken too. However MySQL 4.1 is working fine with this testcase. Regards, Jocelyn - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 09, 2003 6:32 AM Subject: non-unique indicies in HEAP tables handled incorrectly. Description: I am using mysql 4.04. Here is a quote from the mysql manual that started me on this adventure: You can have non-unique keys in a HEAP table (this isn't common for hashed tables). Based on this claim, I designed a database which took advantage of non-unique keys in HEAP tables. A number of things myseriously failed to work right, however, and problems could be shown only to surface when the tables in question were of type HEAP rather than anything else. The below repro script demonstrates the problem in just about the simplest cast possible. There is one key and one value column. Two rows are inserted, with the same value for the key. Doing an unqualified row count gives the correct number, 2, but doing a row count with a traversal by the key only yields a value of 1! Clearly what is happening is that the underlying hash index implementation does not utilize any sort of chaining to bin duplicate keys, or if it does, the traversal code stops after reading the first hashed value. This problem has deeper implications. I actually first ran into it when doing JOINs between a table and a heap table with non-unique index: when the non-unique index was involved in the JOIN condition, less rows were produced in the output than there should have been. Actually, this means the relational algebra is broken... Anyway, strictly speaking, the above claim from the documentation is not violated by these phenomenon; you can indeed have non-unique keys in a HEAP table. Just don't expect them to work =) I hope this is in fact a bug, and the claim was not put forth with this trivial meaning (that would seem fairly dishonest). I checked the changelogs for newer versions and didn't see anything referring to this bug, so that is why I have not tried anything later than 4.04. How-To-Repeat: create temporary table heaptest (id int default 0, name varchar(32), key(id)) TYPE=Heap; insert into heaptest values(1, 'foo'); insert into heaptest values(1, 'bar'); select count(*) as unqualified_count from heaptest; select count(*) as count_by_nonunique_key from heaptest where id=1; Fix: I have no clue, implementation wise (I wish I had time to go look...). I'm assuming that the HEAP index implementation is either not chained, or the index traversal code is not aware of this chaining. I'd be surprised if it was the former, because that would mean someone knowingly broke the relational algebra. Submitter-Id: submitter ID Originator: Aaron Krowne Organization: Digital Library Research Lab Virginia Tech Blacksburg, VA, USA MySQL support: none Synopsis: non-unique indicies in HEAP tables handled incorrectly. Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.4-beta (Official MySQL RPM) Environment: System: Linux shaun 2.4.18 #1 Tue Dec 3 05:32:59 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20010902 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' '-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Dec 3 05:53 /lib/libc.so.5 - libc.so.5.4.46 -rw-r--r--1 root root 563068 Feb 4 2002 /lib/libc.so.5.4.46 lrwxrwxrwx1 root root 13 Dec 3 05:53 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1109068 Nov 19 13:13 /lib/libc-2.3.1.so -rw-r--r--1 root root 2344038 Nov 19 13:14 /usr/lib/libc.a -rw-r--r--1 root root 178 Nov 19 13:14 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client -ldflags=-all-static --without-berkeley-db --with-innodb --without-vio --wit hout-openssl --enable-assembler --enable-local-infile --with-mysqld-user=mys ql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra -charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/et c --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/share/i nfo --includedir=/usr/include --mandir=/usr/share/man --with-embedded-server --enable-thread-safe-client '--with-comment=Official MySQL RPM' CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-ex ceptions -fno-rtti -mpentium' CXX=gcc - Before posting,
How to use HANDLER statement with primary key ?
Hi, I'm trying to use to following HANDLER syntax : HANDLER tbl_name READ index_name=(value1); However I fail since I don't know what is 'index_name' for a PRIMARY KEY (I already tried PRIMARY, `PRIMARY KEY`, and the column name without success). Any idea ? BTW, are HANDLER statements cached by the query cache ? Thanks and regards, Jocelyn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: really slow query results --- SOLVED
Hi, The performance problem on his query was due to the missing index on join columns. However I was assuming using table1 INNER JOIN table2 ON condition would have helped the optimiser to choose the tables on which it had to perform the join. Regards, Jocelyn - Original Message - From: Harald Fuchs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 19, 2002 12:40 PM Subject: Re: really slow query results --- SOLVED In article [EMAIL PROTECTED], Dan Nelson [EMAIL PROTECTED] writes: INNER JOIN and WHERE do the same thing: * `INNER JOIN' and `,' (comma) are semantically equivalent. Both do a full join between the tables used. Normally, you specify how the tables should be linked in the WHERE condition. That's what I always thought, but this must be wrong when Joseph noticed a difference in performance. Any experts out there with comments on that? [Filter fodder: SQL query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can MySQL handle 120 million records?
Hi, I'm using MySQL on a database with 134 Millions of rows (10.9 GB) (some tables contains more than 40 millions of rows) under quite high stress (about 500 queries/sec avg). (using HEAP, MyISAM and InnoDB tables) I never experienced any losses, *even with MySQL-4.1* (yes, I'm currently using 4.1 on this production server ;)). So for me MySQL is ready for a mission critical environment :) Regards, Jocelyn - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Michael She [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Muruganandam [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 4:06 PM Subject: Re: Can MySQL handle 120 million records? On Wed, Dec 18, 2002 at 02:38:31AM -0500, Michael She wrote: Being paranoid... Have you ever lost data with MySQL before? No. Is it reliable. Yes. It doesn't crash and doesn't lose data. If it did either, we'd never have used it this much. I have no problems using MySQL as a lightweight database for simple chores, but I'm a bit weary about putting into a mission critical environment. Why, exactly? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 3 days, processed 136,569,878 queries (457/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can MySQL handle 120 million records?
Previous hardware was a Bi PIII-733 with 786 MB of RAM, and 1 SCSI drive, under Linux (kernel 2.4.18). It worked fine, with sometimes some slowdown, mainly because of the hard drive. Now the server is Bi Athlon MP 2200+, 2 GB of RAM, and Maxtor Atlas 10K3 SCSI 320 (RAID-5) (still kernel 2.4.18) The load average is roughly 0.7. Regards, Jocelyn - Original Message - From: W. D. [EMAIL PROTECTED] To: Jocelyn Fournier [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Michael She [EMAIL PROTECTED]; [EMAIL PROTECTED]; Muruganandam [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 5:38 PM Subject: Re: Can MySQL handle 120 million records? At 10:40 12/18/2002, Jocelyn Fournier wrote: Hi, I'm using MySQL on a database with 134 Millions of rows (10.9 GB) (some tables contains more than 40 millions of rows) under quite high stress (about 500 queries/sec avg). (using HEAP, MyISAM and InnoDB tables) I never experienced any losses, *even with MySQL-4.1* (yes, I'm currently using 4.1 on this production server ;)). So for me MySQL is ready for a mission critical environment :) Wow! What kind of hardware? What OS? Start Here to Find It Fast!© - http://www.US-Webmasters.com/best-start-page/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can MySQL handle 120 million records?
Hi, I assume you are speaking about this comment : ++--+--+-- + | Table | Op | Msg_type | Msg_text | ++--+--+-- + | database.table_name | optimize | error | 28 when fixing table | | database.table_name| optimize | status | Operation failed | ++--+--+-- + 2 rows in set (40.91 sec) I typed it in again thinking it had some sort of rollback or perhaps just a machine glitch but then I typed it in again and got the folowing mysql optimize table table_name; ++--+--+-- ---+ | Table | Op | Msg_type | Msg_text | ++--+--+-- ---+ | database.table_name | optimize | error | Can't open file: 'table_name.MYD'. (errno: 144) | ++--+--+-- ---+ and lo all my data is lost... thank god for mysql dump. Well error 28 means there is no space left on the device. When you run and OPTIMIZE TABLE statement, MySQL locks the main table and recreate in // the index file. As MySQL failed to recreate the index file, the table was marked as crashed (errno: 144), but in any case data were lost (data file is not altered during an optimize) : he just have to execute a REPAIR TABLE statement to have all his record back. Take a look at what happens during an optimize of the following : Before OPTIMIZE TABLE searchmainhardwarefr8 : [root@forum] /home/mysql/Hardwarefr l searchmainhardwarefr8.* 19:28:52 -rw-rw1 mysqlmysql27589205 Dec 18 19:25 searchmainhardwarefr8.MYD -rw-rw1 mysqlmysql16257024 Dec 18 19:25 searchmainhardwarefr8.MYI -rw-rw1 mysqlmysql8596 Oct 18 17:03 searchmainhardwarefr8.frm During OPTIMIZE TABLE searchmainhardwarefr8 : [root@forum] /home/mysql/Hardwarefr l searchmainhardwarefr8.* 19:29:21 -rw-rw1 mysqlmysql27589205 Dec 18 19:25 searchmainhardwarefr8.MYD -rw-rw1 mysqlmysql16257024 Dec 18 19:25 searchmainhardwarefr8.MYI -rw-rw1 mysqlmysql 6696960 Dec 18 19:29 searchmainhardwarefr8.TMM -rw-rw1 mysqlmysql8596 Oct 18 17:03 searchmainhardwarefr8.frm After OPTIMIZE TABLE searchmainhardwarefr8 : [root@forum] /home/mysql/Hardwarefr l searchmainhardwarefr8.* 19:29:22 -rw-rw1 mysqlmysql27589205 Dec 18 19:25 searchmainhardwarefr8.MYD -rw-rw1 mysqlmysql16257024 Dec 18 19:29 searchmainhardwarefr8.MYI -rw-rw1 mysqlmysql8596 Oct 18 17:03 searchmainhardwarefr8.frm As you can see, only the MYI file (index file) has changed, the data file remains untouched. Since you can completly recreate the MYI using the MYD file, there is no data lost possibility, even if the hard disk is full. Regards, Jocelyn - Original Message - From: Michael She [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Muruganandam [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 5:16 PM Subject: Re: Can MySQL handle 120 million records? At 08:06 AM 12/18/2002 -0800, Jeremy Zawodny wrote: I have no problems using MySQL as a lightweight database for simple chores, but I'm a bit weary about putting into a mission critical environment. Why, exactly? Mainly for 2 reasons: 1. MySQL hasn't been proven yet in the corporate environment 2. Some of the comments in the mySQL manual... people losing data doing routine stuff like table optimizations, adding keys, etc. If a database is reliable, things like that shouldn't happen. Comments like those in the MySQL manual scared me. -- Michael She : [EMAIL PROTECTED] Mobile : (519) 589-7309 WWW Homepage : http://www.binaryio.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)
Hi, I think you'd better add an unique ID to both table defined as int corresponding to each seq_ID, and then do the join on this ID rather than on Seq_ID (join on varchar is far from the fastest solution :)) (unless seq_ID could be converted into int directly ?) (but it takes time, even for me (bi athlon MP 2200+) : mysql SELECT COUNT(*) FROM searchmainhardwarefr7 LEFT JOIN searchjoinhardwarefr7 ON searchjoinhardwarefr7.numreponse=searchmainhardwarefr7.numreponse; +--+ | COUNT(*) | +--+ | 39396361 | +--+ 1 row in set (3 min 23.15 sec) mysql EXPLAIN SELECT COUNT(*) FROM searchmainhardwarefr7 LEFT JOIN searchjoinhardwarefr7 ON searchjoinhardwarefr7.numreponse=searchmainhardwarefr7.numreponse; ++-+---++---+--- -+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+--- -+-+--+--+-+ | 1 | SIMPLE | searchmainhardwarefr7 | index | NULL | numreponse | 4 | NULL | 39396576 | Using index | | 1 | SIMPLE | searchjoinhardwarefr7 | eq_ref | numreponse| numreponse | 4 | searchmainhardwarefr7.numreponse |1 | Using index | ++-+---++---+--- -+-+--+--+-+ ) Regards, Jocelyn - Original Message - From: Qunfeng Dong [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 9:17 PM Subject: Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-) Boy, you guys are die-hard MySQL fans :-) I think your strong defending convinced us MySQL can handle 120 million records :-) But I know some ordinary users out there like me who are not experts on tuning the MySQL performance (they did send me private emails saying they encountered the similar slow join problem). So please help us to keep the faith. We are trying to develop a simple biology database to maintain some DNA Sequence information. My problem is coming from the following two tables: CREATE TABLE NewSequence ( Seq_ID varchar(50) NOT NULL, GenBank_Acc varchar(10), Organismvarchar(50) NOT NULL, Seq_Type enum(EST,GSS,EST Contig,EST Singlet,GSS Contig,GSS Singlet,GSS Plasmid Contig,Protein) NOT NULL, Seq_Length int NOT NULL, Seq_Title textNOT NULL, Comment text, Entry_Date dateNOT NULL, PRIMARY KEY (Seq_ID), UNIQUE (GenBank_Acc), INDEX (Seq_Type), INDEX (Organism) ); This NewSequence table is used to track some general info about sequence. Notice I have to use text datatype to describe Comment and Seq_Title fields; therefore I have to use varchar for other string fields. In addition, the Seq_ID is not numerical. BTW, I found indexing on Seq_Type. Organism which are very repeative still helps with accessing. This table has 2676711 rows. CREATE TABLE NewSequence_Homolog ( Seq_ID varchar(50) NOT NULL, Homolog_PID int NOT NULL, Homolog_Descvarchar(50) NOT NULL, Homolog_Species varchar(50), PRIMARY KEY (Seq_ID, Homolog_PID) ); This NewSequence_Homolog table is to track which protein sequences (homolog) are similar to the sequence I store in the NewSequence table. This table has 997654 rows. mysql select count(*) from NewSequence s left join NewSequence_Homolog h on s.Seq_ID = h.Seq_ID; +--+ | count(*) | +--+ | 3292029 | +--+ 1 row in set (1 min 30.50 sec) So a simple left join took about 1 min and half. First, is this slow or I am too picky? This is the Explain. mysql explain select count(*) from NewSequence s left join NewSequence_Homolog h on s.Seq_ID = h.Seq_ID; +---+---+---+-+-+--+-+-- ---+ | table | type | possible_keys | key | key_len | ref | rows| Extra | +---+---+---+-+-+--+-+-- ---+ | s | index | NULL | PRIMARY | 50 | NULL | 2676711 | Using index | | h | ref | PRIMARY | PRIMARY | 50 | s.Seq_ID |9976 | Using index | +---+---+---+-+-+--+-+-- ---+ I am running MySQL 3.23.49 on RedHat linux 7.3 on a dedicated server with 4 GB memory. The only setting I changed is to copy
Re: Re[2]: No descending index ?
Hi, I assume it's because MySQL returns more than 30% of the totally of the rows. On one of my table, with 4.0.6-gamma : mysql SELECT COUNT(*) FROM news; +--+ | COUNT(*) | +--+ | 4985 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(*) FROM news WHERE datec '2000-12-31' order by datec DESC; +--+ | COUNT(*) | +--+ | 778 | +--+ 1 row in set (0.01 sec) mysql EXPLAIN SELECT * FROM news WHERE datec '2000-12-31' order by datec DESC; +---+---+---+---+-+--+--+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+---+-+--+--+--- --+ | news | range | datec | datec | 8 | NULL | 761 | Using where | +---+---+---+---+-+--+--+--- --+ 1 row in set (0.00 sec) 30 %, so it uses index mysql SELECT COUNT(*) FROM news WHERE datec '2001-12-31' order by datec DESC; +--+ | COUNT(*) | +--+ | 2998 | +--+ 1 row in set (0.01 sec) mysql EXPLAIN SELECT * FROM news WHERE datec '2001-12-31' order by datec DESC; +---+--+---+--+-+--+--+- + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+- + | news | ALL | datec | NULL |NULL | NULL | 4985 | Using where; Using filesort | +---+--+---+--+-+--+--+- + 1 row in set (0.00 sec) 30%, so no index used So the result of your explain seems normal for me. Regards, Jocelyn - Original Message - From: Mpu Gondrong [EMAIL PROTECTED] To: Jeremy Zawodny [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 1:13 AM Subject: Re[2]: No descending index ? Selasa, 17/12/2002 7:47:38, Jeremy menulis: JZ I'm not quite sure what you're after, but MySQL 4.0 has support JZ for reading indexes in reverse order in some circumstances. This JZ greatly speeds up some queries that were slow in 3.23. I'm using MySQL 4.0.4-beta. For example: EXPLAIN SELECT * FROM news where date = '2002-12-31' order by date desc table type possible_keys key key_len ref rows Extra news ALL date NULL NULLNULL 14138 where used Or is that normal ? Any sample 'circumstances' ? TIA. Tertanda, Oguds [36856104] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The Manual on MySQL is not updated...
Hi, About bk -r get -Sq, after a bk pull, is bk -r edit still needed or bk -r get -Sq could replace it ? Thanks and regards, Jocelyn - Original Message - From: Lenz Grimmer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 2:56 PM Subject: Re: The Manual on MySQL is not updated... -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 11 Dec 2002, Dyego Souza do Carmo wrote: http://www.mysql.com/doc/en/Installing_source_tree.html In the manual , to compile a source tree only type: shell cd mysql-4.0 shell bk -r get -Sq shell aclocal; autoheader; autoconf; automake; shell ./configure # Add your favorite options here shell make but , if you using innodb this is not true, to perfect compilation of mysql with innodb you take: shell cd mysql-4.0 shell bk -r get -Sq shell aclocal; autoheader; autoconf; automake; shell cd innobase shell aclocal; autoheader; autoconf; automake; shell cd .. shell ./configure # Add your favorite options here shell make if you not type aclocal; autoheader; autoconf; automake; in innobase directory , the compilation print a error. i´m true ? Yes, that's correct. Thanks for the hint - I fixed this part of the manual now. Bye, LenZ - - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE9+KOsSVDhKrJykfIRAhv8AJwJX6831og2hBTRVaGq3gZIr7GTVgCfSFwE ZJfa8h+vQ8MFpE+gpkhEZ68= =WBQe -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Compiling 3.23.54-1 - new tools installed !
Hi, You should downgrade automake to 1.5 and if it still doesn't work, try with autoconf 2.52. Regards, Jocelyn - Original Message - From: gnu_is_not_unix [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 8:52 PM Subject: Re: Compiling 3.23.54-1 - new tools installed ! Hello, Yes. You need automake 1.5 / libtool 1.4. It appears this made it into the CHANGES for the 4.0.x series, but not the 3.23.x series. I'm BCC'ing this to our build engineer, so he can correct the manual. Thanks! -Mark well, [root@kula-szpiegula /root]# libtool --version ltmain.sh (GNU libtool) 1.4.3 (1.922.2.110 2002/10/23 01:39:54) [root@kula-szpiegula /root]# automake --version automake (GNU automake) 1.7.2 [root@kula-szpiegula /root]# autoconf --version autoconf (GNU Autoconf) 2.57 and error again: rpm --rebuild MySQL-3.23.54-1.src.rpm ... sql/Makefile.am:94: `YFLAGS' is a user variable, you should not override it; sql/Makefile.am:94: use `AM_YFLAGS' instead. /usr/share/automake-1.7/am/depend2.am: am__fastdepCC does not appear in AM_CONDITIONAL strings/Makefile.am: Assembler source seen but `CCAS' is undefined strings/Makefile.am: strings/Makefile.am: The usual way to define `CCAS' is to add `AM_PROG_AS' strings/Makefile.am: to `configure.in' and run `aclocal' and `autoconf' again. strings/Makefile.am: Assembler source seen but `CCASFLAGS' is undefined strings/Makefile.am: strings/Makefile.am: The usual way to define `CCASFLAGS' is to add `AM_PROG_AS' strings/Makefile.am: to `configure.in' and run `aclocal' and `autoconf' again. error: Bad exit status from /var/tmp/rpm-tmp.80279 (%build) -- Best regards, gnu_is_not_unix - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Desc question
Hi, if ($var=='grade_num') $filter='DESC'; else $filter='ASC'; $fetch = mysql_query(SELECT * FROM players ORDER BY $var $filter); should work. Regards, Jocelyn - Original Message - From: Alex Behrens [EMAIL PROTECTED] To: MYSQL [EMAIL PROTECTED] Sent: Monday, December 09, 2002 12:03 AM Subject: Desc question hey all, I have a quick question. I'm trying to display a list of players by their name, number, position, and grade. Name, number, and position all display properly. However, grade (which is sorted grade number) is backwards, since 12th grade is after 11, 10, etc. How can I make it so only one form selection adds a DESC tag to the query, is this possible? I'm using this code: $fetch = mysql_query(SELECT * FROM players ORDER BY $var); select name='var' size='1' style='border-style: solid; border-color: #025689' option value='name'Name/option option value='position'Position/option option value='number'Number/option option value='grade_num'Grade/option I want only the Grade option to use the desc option with the $fetch query. can I do this? -mysql Thanks! -Alex Big Al Behrens E-mail: [EMAIL PROTECTED] Urgent E-mail: [EMAIL PROTECTED] (Please be brief!) Phone: 651-482-8779 Cell: 651-329-4187 Fax: 651-482-1391 ICQ: 3969599 Owner of the 3D-Unlimited Network: http://www.3d-unlimited.com Send News: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select * From table where name Like 'help'; Help
Hi, A dirty solution would be to search : field LIKE '% one %' or field LIKE 'one %' or field LIKE '% one' or field='one'; Regards, Jocelyn - Original Message - From: Beauford.2003 [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Tuesday, December 10, 2002 12:50 AM Subject: Select * From table where name Like 'help'; Help Hi, Hey, did this list change their spam filters? Now every time I send an email it gets bounced unless the word MySQL or Query is in the email. Anyway, Please read below for a recent problem I am having. I want to be able to search on my database and be able to get the following, but running into a few snags. If I search for the word - One - I want to see: Another One One Day One on One Your The One But not: Fashioned Stone Everyone Also, and maybe part of the solution - is there a way for the search to be case sensitive. TIA Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: abnormally high load under 4.04
Hi, Try also to download 4.0.5a (not 4.0.5), this version could solve your load problem (due to a problem with the glibc used during the build) Regards, Jocelyn - Original Message - From: Josh Marcus [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 06, 2002 2:16 AM Subject: abnormally high load under 4.04 Recently, I upgraded some moderately taxed mysql servers from 3.23.49a to 4.0.4 (all installed via redhat rpms downloaded from mysql.com). The motivation for the upgrade was actually that replication seems a little flaky under 3.23.49a. For example, we've had unexplained corrupt binlog issues. (If anyone has any advice on this, that'd be appreciated.) About 20 hours after the upgrade, and then periodically after that, the machine the server was on would grind to a halt with a load of 100-200. It was difficult to grab any information at all about what was goin' on considering the load. Once mysqld was killed restarted, things would return to normal until some period later when the machine would grind to a halt once again. Any advice? What should we looking for? One issue might be that we have the maximum number of connections set way too high for the 1GB of memory the box has. --j - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sleeping threads problem
Hi, If you are using mysql_pconnect, your connections will not be closed at the end of the script, even if mysql_close is used. Regards, Jocelyn - Original Message - From: cristian ditoiu [EMAIL PROTECTED] To: Dan Nelson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 2:33 PM Subject: Re: sleeping threads problem Nope , you haven't read my email . I use PHP and MySql . Php closes mysql connections when the script ends . And the script ends every time a user hits a page . http://www.php.net/manual/en/function.mysql-close.php Quote : 'Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution' - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: cristian ditoiu [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 03, 2002 7:27 PM Subject: Re: sleeping threads problem In the last episode (Dec 03), cristian ditoiu said: Hello , i use MySql 3.23.49-log with Php 4.2.1on RH 7.2 . The problem is that i got many many (16-30) sleeping threads almost all the time . The code is pretty clean , and i suspect that those threads are sleaping beacuse of some strange ascii characters (like CR) . Is any intelegent way to find out WHY are those threads sleeping ? They are sleeping because you have not given them a query to process. Sleeping threads are idle connections. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Difference between 4.0.5 and 4.0.5a
Hi, MySQL-4.0.5a is compiled again a new patched glibc library, to prevent from MySQL having any load issues (which was the case on some systems). Features are exactly the same between 4.0.5 and 4.0.5a. Regards, Jocelyn - Original Message - From: Ray Elenteny [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 27, 2002 2:47 PM Subject: Difference between 4.0.5 and 4.0.5a Hi, I've been trying to determine the difference between MySQL 4.0.5 and 4.0.5a. I can't seem to find any documentation relating to the change. Can someone point me to the documentation or tell me what has changed? Thanks, Ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: join with OR and LIMIT fails to return result
Hi, John : I don't see any connection between corereader and this known bug in MySQL-4.0.4 ?? Bill : AFAIK, this problem has been fixed in MySQL-4.0.5, just take a look at the changelog : Fixed a newly introduced bug that caused ORDER BY ... LIMIT # to not return all rows. http://www.mysql.com/doc/en/News-4.0.5.html Regards, Jocelyn - Original Message - From: John Ragan [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Bill Marrs [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 9:37 PM Subject: Re: join with OR and LIMIT fails to return result interesting. of course, removing one of the limiting selection criteria would increase the number of possible selected records. and the limit clause does not seem to be correctly constucted. but forgive me if i just don't understand. if you have a windows box for a front end, download corereader. it will let you quickly experiment with point-and-click queries to debug query logic.http://corereader.com Description: join with OR and LIMIT fails to return result How-To-Repeat: This query fails to return a result: SELECT Subscriptions.UID FROM Subscriptions, Accounts WHERE Accounts.UID = Subscriptions.User AND (Accounts.Email = 'bill' OR Accounts.UName = 'bill') ORDER BY Subscriptions.Created DESC limit 1; Empty set (0.02 sec) But, if I remove one of the parenthesized OR tokens, it works: SELECT Subscriptions.UID FROM Subscriptions, Accounts WHERE Accounts.UID = Subscriptions.User AND Accounts.UName = 'bill' ORDER BY Subscriptions.Created DESC limit 1; +-+ ID | +-+ | 255 | +-+ 1 row in set (0.00 sec) Or, if I remove the LIMIT, it works: mysql SELECT Subscriptions.UID FROM Subscriptions, Accounts WHERE Accounts.UID = Subscriptions.User AND (Accounts.Email = 'bill' OR Accounts.UName = 'bill') ORDER BY Subscriptions.Created DESC; +-+ | UID | +-+ | 255 | +-+ 1 row in set (0.11 sec I've already worked around this, I'm just letting you guys know. Fix: -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: thread_concurrency (Try number of CPU's*2 for thread_concurrency)
Hi, No, you should set it to 4 if you have 2 CPU :) (2*2 ;)) Regards, Jocelyn - Original Message - From: Jacob Friis Larsen [EMAIL PROTECTED] To: Mysql maillist [EMAIL PROTECTED] Sent: Monday, November 25, 2002 8:46 AM Subject: thread_concurrency (Try number of CPU's*2 for thread_concurrency) set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 If I have 2 CPU's, does the above mean that I should set thread_concurrency=16 ? I am using a MySQL database 4.0.4 Regards, Jacob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: unwanted timestamp update
Hi, Take a look here : http://www.mysql.com/doc/en/DATETIME.html Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: a.. The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. b.. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) c.. You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW(). Regards, Jocelyn - Original Message - From: ozy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 22, 2002 7:16 PM Subject: unwanted timestamp update Hello everyone, /first of all, sorry about my bad english :-)/ i had just found some interesting thing with MySQL 3.23 (win2000). i have a table like this: ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | page | varchar(255) | YES | | NULL| | | host | varchar(255) | YES | | NULL| | | browser| varchar(255) | YES | | NULL| | | referer| varchar(255) | YES | | NULL| | | datum | timestamp(14) | YES | | NULL| | | discrete | char(1) | YES | | NULL| | | counter_id | int(11) | YES | | NULL| | | hely | varchar(255) | YES | | NULL| | ++---+--+-+-+---+ it is just a work table so nothing interesting. hely is NULL for all of the records, datum is different for (almost) all. (datum is a hungarian word for date). now i run an SQL query like this one: update szurtcounter set hely='kulfold'; after that the value of hely for all of my records is 'kulfold', BUT!! the value of the datum field is THE CURRENT SYSTEM TIME also for all of my records! i don't get this one :-( thanks for any help -- Best regards, Csite Laszlo aka ozy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select from two table
Hi, Try : SELECT table1.name,table1.position,table2.position FROM table1 LEFT JOIN table2 USING(name); Take a look here : http://www.mysql.com/doc/en/JOIN.html Regards, Jocelyn - Original Message - From: Osman Omar [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Saturday, November 23, 2002 4:14 AM Subject: select from two table I have 2 table with same structure table 1 have name and position table 2 have name and position name in table 1 same as name in table 2 but position in table 1 may not same as position in table 2 how do I get data like this what is sql command nameposition.table1 position.table2 thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select from two table
don't forget FROM table1,table2 ;) : SELECT name, table1.position, table2.position FROM table1,table2 WHERE table1.name=table2.name; Regards, Jocelyn - Original Message - From: Dennis Salguero [EMAIL PROTECTED] To: Osman Omar [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Saturday, November 23, 2002 1:21 AM Subject: Re: select from two table You might want to add some unique IDs or something else in common to make this query a bit better, but the following is what you should be doing: SELECT name, table1.position, table2.position WHERE table1.name=table2.name Good Luck! Dennis - Original Message - From: Osman Omar [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Friday, November 22, 2002 11:14 PM Subject: select from two table I have 2 table with same structure table 1 have name and position table 2 have name and position name in table 1 same as name in table 2 but position in table 1 may not same as position in table 2 how do I get data like this what is sql command nameposition.table1 position.table2 thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Where is 4.1 source
Hi, Take a look here : http://www.mysql.com/doc/en/Installing_source_tree.html Regards, Jocelyn - Original Message - From: Daniel Kiss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 21, 2002 11:58 AM Subject: Where is 4.1 source Hi all, Where can I download MySQL version 4.1 source code? Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Where is 4.1 source
I just want to add that unlike what it is printed (Download BitKeeper from http://www.bitmover.com/cgi-bin/download.cgi. You will need Bitkeeper 2.0 or newer to access our repository.), Bitkeeper 3.x seems to be needed. Perhaps the manual should be updated :) Regards, Jocelyn - Original Message - From: Iikka Meriläinen [EMAIL PROTECTED] To: Daniel Kiss [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, November 21, 2002 12:11 PM Subject: Re: Where is 4.1 source On Thu, 21 Nov 2002, Daniel Kiss wrote: Hi all, Where can I download MySQL version 4.1 source code? Hi, There are no 4.1 source distributions yet, see this: http://www.mysql.com/doc/en/Installing_source_tree.html You need to download the bits via BK. Iikka Meriläinen Vaala, Finland E-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate key delete record with same key
Hi, Could you give us SHOW CREATE TABLE your_table ? Greetings from France too ;) Jocelyn - Original Message - From: BPF Webmaster [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Thursday, November 21, 2002 2:25 PM Subject: Duplicate key delete record with same key Using PHP 4.2.3 with MySQL 3.23.52 Report: --- - Table has 2 keys: 'account' as PRIMARY and 'id' as unique - I make an SQL update on an existing a record with id=anid setting id=anotherid but another record exists with id =anotherid - The update is performed and the record already existing with id=anotherid is deleted!!! Expected behavior: -- - Update failed with Duplicate key attempt message I guess it's a bug, isn't it? The workaround is very easy but I really guess the behavior is weird. Greetings from France, JM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT INTO () SELECT...
Hi, Yes it works, but you can't insert and select data from the same table with this syntax. Regards, Jocelyn - Original Message - From: Mirza Muharemagic [EMAIL PROTECTED] To: Eric [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, November 22, 2002 12:30 AM Subject: Re: INSERT INTO () SELECT... Hi Eric, thats oracle function INSERT INTO ... SELECT. it doesn't work in mysql. Mirza [EMAIL PROTECTED] __ 21.11.2002 22:13 Hi, This should work, I think, but doesn't INSERT INTO holds (ord_num) SELECT orders.ord_num FROM orders LEFT JOIN holds ON orders.ord_num = holds.ord_num WHERE holds.ord_num IS NULL I have some order numbers that are not in holds that are in orders. I want to make entries in holds to match those missing. The above SELECT gives me exactly what I want, but I get ERROR 1066: Not unique table/alias: 'holds' Thanks, Eric mysql,sql why because we love you! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: does this list work or not?
Hi, In fact it seems the list have had some problems this week-end, as sent messages seems to have been delayed of about 1 day. Regards, Jocelyn - Original Message - From: Bryant Hester [EMAIL PROTECTED] To: 'Siomara Pantarotto' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, November 18, 2002 1:48 PM Subject: RE: does this list work or not? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 You can rest assured that the list does indeed work. And as it is a list, and you have posted a question to the list, you have to wait until someone reads your question that might be able to help you with whatever problem you are having. As I have zero experience in the introduction of another language into my MySQL database, I don't know why your tables don't look exactly as they should. Perhaps you could give some more information, such as what character-set you are using in your database. Have you looked in the manual for any possible causes of this problem? But, I digress, please be assured that your posts are being received by the list. Bryant Hester - -Original Message- From: Siomara Pantarotto [mailto:[EMAIL PROTECTED]] Sent: Sunday, November 17, 2002 9:22 AM To: [EMAIL PROTECTED] Subject: does this list work or not? I have posted many emails asking help for my question and it seems that the list does not work. Can someone reply please? From: [EMAIL PROTECTED] To: Siomara Pantarotto [EMAIL PROTECTED] Subject: Re: testing Date: Sun, 17 Nov 2002 16:12:41 +0100 Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: testing _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -BEGIN PGP SIGNATURE- Version: PGPfreeware 7.0.3 for non-commercial use http://www.pgp.com iQA/AwUBPdjvsklWu7/HFp4nEQJ+WACg07SNp0qxYCVDhYrnArmlCQLCbWkAn1HG a7ymjfdpf1dPOkg/yFrfZSFg =rLW5 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL + PHP : Commands out of sync; You can't run this command now
Hi, It's known bug of PHP, which has just been fixed in 4.3 CVS. Use mysql_connect instead of mysql_pconnect as a workaround of the problem. (or get the latest CVS version of php 4.3.0) For more infos : http://bugs.php.net/bug.php?id=19529 Regards, Jocelyn - Original Message - From: Antoine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 18, 2002 5:24 PM Subject: MySQL + PHP : Commands out of sync; You can't run this command now Hi, I'm having weird errors right now, running MySQL queries from PHP as an Apache module. Sometimes (in a seemingly random way) some queries fail and mysql_error() displays Commands out of sync; You can't run this command now. I would say it seems to happen when different queries are launched at the same time by different threads, or nearly. It is MySQL 4.0.4, compiled from source, together with PHP/4.2.3. They are on two different machines. What does this error message mean ? I've never had it before... (and I've been using this setup for a bit of time). Thanks Antoine. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please help with strange Win2K error
Hi, Perror 13 means : perror 13 Error code 13: Permission denied So it seems you don't have the permission to create the temporary table is this directory. Regards, Jocelyn - Original Message - From: Stefan Hinz [EMAIL PROTECTED] To: MySQL Main List [EMAIL PROTECTED] Sent: Monday, November 18, 2002 6:08 PM Subject: Please help with strange Win2K error Dear list, sorry for bothering you again, but no one has answered to my request for help so far, not even Jeremy Z. who *I know* can solve every and any problem remotely ;-) On Win2K with MySQL 4.0.4 I get this error with a MyISAM table: mysql ALTER TABLE auftrag CHANGE Nummer Nummer INT UNSIGNED NOT NULL PRIMARY KEY; ERROR 7: Error on rename of '.\trainee\auftrag.MYI' to '.\trainee\#sql2-648-2.MYI' (Errcode: 13) Trying a workaround, I switched the table type of this table to innoDB. Now, I can issue the above statement without getting an error. However, innoDB tables have a MyISAM style .frm file. I get a quite similar error when I try the following: mysql ALTER TABLE auftrag ADD FOREIGN KEY (Auftraggeber) REFERENCES auftraggeber(Schl); ERROR 1005: Can't create '.\trainee\#sql-6fc_2.frm'. (Error: 150) Seems to be an (internal) temporary table problem. Does anyone know what's happening, and how to solve this prob? Any help is greatly appreciated. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order issue with MySQL
Hi, perhaps try : ORDER BY SUBSTRING_INDEX(your_field, '-', 1) asc,SUBSTRING_INDEX(your_field, '-', 2) asc,SUBSTRING_INDEX(your_field, '-', 3) asc; Regards, Jocelyn - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 4:38 PM Subject: Order issue with MySQL Hello All, Using MySQL for sql...webpage forms: I am trying to order the following: 1-03-0A0019-03-0D004; this works fine using simple order by asc. when I added 11-02-0B001, etc. it came after 1-03- 0D004, then continued to rest of order. how do I fix this so that 11-xx-xxx comes after 9-xx- xxx? The first digit is numbered as odd. thanks, Lamar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Commands out of sync
Hi, This is a known bug of PHP : http://bugs.php.net/bug.php?id=19529 A quick fix for this problem is to use mysql_connect instead of mysql_pconnect. Regards, Jocelyn - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 11, 2002 7:09 PM Subject: Commands out of sync Hi- I'm running MySQL 3.23.51 with PHP 4.2.3 on a Slackware 8.1 system with Dual Athlon MP 1.2's and 1 GB RAM. We run a busy site, approximately 3 million page views/day, almost all of which hit the database. The problem is that we are getting the error Commands out of sync. You cannot perform this command now. I have read the unhelpful section of the MySQL manual, and done several google searches, none of which have helped. None of our queries are very complex, nor does this error seem to occur with any consistency. Sometimes it happens when we're busy, sometimes when we're slow, etc. We never use anything like mysql_query_unbuffered(), only mysql_query(), mysql_select_db(), and mysql_pconnect(). Has anyone encountered this error before? Is there any known situation where this should occur? Any help is greatly appreciated. Matt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql 4.1 Sub Selects
Hi, It remains some bugs in MySQL 4.1 subselect implementation which are on the way to be resolve. (take a look at the [EMAIL PROTECTED]) Also, support for IN subselect are not yet available (you can find the code in reading the bk commit, but it's not yet push). Lastly, subselect are not yet optimised (it will be started after finishing main subselect constructions). Regards, Jocelyn - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Greg Matthews [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, November 10, 2002 10:00 AM Subject: Re: MySql 4.1 Sub Selects On Sun, Nov 10, 2002 at 05:51:43PM +1100, Greg Matthews wrote: It would be a huge help to get hold of MySql 4.1. What's shaken out mean? Is 4.1 still mid-development, or is it in alpha, more or less finished, and needs debugging? Is there anyway soon that someone could build and release some binaries? I'd be happy to do testing if 4.1 is in a semi-usable state. I can build them and make 'em available. But I won't support them. If they crash for you, they crash. But they'd certainly be helpful for testing new features, if that's what you're intending. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld refuse to die
Hi, Why not trying killall -9 mysqld ? Regards, Jocelyn - Original Message - From: Gelu Gogancea [EMAIL PROTECTED] To: Jack Chen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, November 09, 2002 12:08 PM Subject: Re: mysqld refuse to die Hi, If you really wish to kill the mysql daemon in this way : You should try : ps -ef |grep mysql ...show all the mysqld processes and after this must enumarate all pid of processes in a single kill command. E.g. kill -9 1024 1056 ...(processes which are open). But it's more health if you can stop the server using : /etc/rc.d/mysqld stop Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jack Chen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 09, 2002 7:57 AM Subject: Re: mysqld refuse to die I have just figured out: kill -9 xxx (process number) Thanks, Jack Jack Chen, Stein Lab, Cold Spring Harbor Labs 1 Bungtown Road, Cold Spring Harbor, NY, 11724 Tel: 1 516 3676904; e-mail: [EMAIL PROTECTED] On Sat, 9 Nov 2002, Jack Chen wrote: Hi All, Please help me with this problem: For some reason, I could not bring down my mysqld by running mysqld stop An error message indicate: fail What's going on? Thanks, Jack Jack Chen, Stein Lab, Cold Spring Harbor Labs 1 Bungtown Road, Cold Spring Harbor, NY, 11724 Tel: 1 516 3676904; e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- Xnet scaneaza automat toate mesajele impotriva virusilor folosind RAV AntiVirus. Xnet automatically scans all messages for viruses using RAV AntiVirus. Nota: RAV AntiVirus poate sa nu detecteze toti virusii noi sau toate variantele lor. Va rugam sa luati in considerare ca exista un risc de fiecare data cand deschideti fisiere atasate si ca MobiFon nu este responsabila pentru nici un prejudiciu cauzat de virusi. Disclaimer: RAV AntiVirus may not be able to detect all new viruses and variants. Please be aware that there is a risk involved whenever opening e-mail attachments to your computer and that MobiFon is not responsible for any damages caused by viruses. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld refuse to die
just kill mysqld_safe and then the mysqld process :) (but AFAIK all the mysql thread are names mysqld ??) - Original Message - From: Gelu Gogancea [EMAIL PROTECTED] To: Jocelyn Fournier [EMAIL PROTECTED]; Jack Chen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, November 09, 2002 12:20 PM Subject: Re: mysqld refuse to die ...because not all mysql processes are named mysql and is one (and you know about this) which is for safe running(mysqld_safe or safe_mysqld) which create new threads when another is killed. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jocelyn Fournier [EMAIL PROTECTED] To: Gelu Gogancea [EMAIL PROTECTED]; Jack Chen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, November 09, 2002 2:12 PM Subject: Re: mysqld refuse to die Hi, Why not trying killall -9 mysqld ? Regards, Jocelyn - Original Message - From: Gelu Gogancea [EMAIL PROTECTED] To: Jack Chen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, November 09, 2002 12:08 PM Subject: Re: mysqld refuse to die Hi, If you really wish to kill the mysql daemon in this way : You should try : ps -ef |grep mysql ...show all the mysqld processes and after this must enumarate all pid of processes in a single kill command. E.g. kill -9 1024 1056 ...(processes which are open). But it's more health if you can stop the server using : /etc/rc.d/mysqld stop Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jack Chen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 09, 2002 7:57 AM Subject: Re: mysqld refuse to die I have just figured out: kill -9 xxx (process number) Thanks, Jack Jack Chen, Stein Lab, Cold Spring Harbor Labs 1 Bungtown Road, Cold Spring Harbor, NY, 11724 Tel: 1 516 3676904; e-mail: [EMAIL PROTECTED] On Sat, 9 Nov 2002, Jack Chen wrote: Hi All, Please help me with this problem: For some reason, I could not bring down my mysqld by running mysqld stop An error message indicate: fail What's going on? Thanks, Jack Jack Chen, Stein Lab, Cold Spring Harbor Labs 1 Bungtown Road, Cold Spring Harbor, NY, 11724 Tel: 1 516 3676904; e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- Xnet scaneaza automat toate mesajele impotriva virusilor folosind RAV AntiVirus. Xnet automatically scans all messages for viruses using RAV AntiVirus. Nota: RAV AntiVirus poate sa nu detecteze toti virusii noi sau toate variantele lor. Va rugam sa luati in considerare ca exista un risc de fiecare data cand deschideti fisiere atasate si ca MobiFon nu este responsabila pentru nici un prejudiciu cauzat de virusi. Disclaimer: RAV AntiVirus may not be able to detect all new viruses and variants. Please be aware that there is a risk involved whenever opening e-mail attachments to your computer and that MobiFon is not responsible for any damages caused by viruses. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: little problem, I need some help...
Hi, I don't see any group column in your table, are you displaying the right table (describe pupils; instead of describe pupuils; ?). A KEY column is a column which have an INDEX on it. Regards, Jocelyn - Original Message - From: 3mip1s4la-Emilio Pisanty [EMAIL PROTECTED] To: R. Hannes Niedner [EMAIL PROTECTED] Cc: MySQL Mailinglist [EMAIL PROTECTED] Sent: Sunday, November 10, 2002 12:34 AM Subject: Re: little problem, I need some help... tables have met with a 1175 error (You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column). Nothing is obvious and is hard to advise you if you don't give us some more info on the table structure and the update query you have trouble with. /h ok. thable structure is this: mysql describe pupils; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | name | varchar(20) | YES | | NULL| | | surname | varchar(20) | YES | | NULL| | | surname2 | varchar(20) | YES | | NULL| | | form | char(3) | YES | | NULL| | | tutor| varchar(20) | YES | | NULL| | | sex | char(1) | YES | | NULL| | | birth| date| YES | | NULL| | | math | char(3) | YES | | NULL| | | optA | char(3) | YES | | NULL| | | optB | char(3) | YES | | NULL| | +--+-+--+-+-+---+ 10 rows in set (0.00 sec) it was built in version 3.23, and we recently updated to 4.0.4 beta. the query I'm running is mysql UPDATE pupuils SET tutor = 'URIOSTEGUI' WHERE group = 'U6U'; (setting the name of the tutor for Upper 6) and I meet with: ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column I don't quite understand what a KEY column is, can someone explain it to me? if you can't answer, where can I find error listings? because they don't appear to be on the site. hope this helps you help me... thanks in advance, Emilio Pisanty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1030: Got error 124 from table handler
Hi, In fact, it's : joce@forum:~$ perror 124 Error code 124: Wrong medium type 124 = Wrong index given to function but the solution to resolve the problem remains the same ;) Regards, Jocelyn - Original Message - From: Dicky Wahyu Purnomo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 08, 2002 2:11 AM Subject: Re: ERROR 1030: Got error 124 from table handler Pada Fri, 08 Nov 2002 01:59:20 + David Herring [EMAIL PROTECTED] menulis: What does ERROR 1030: Got error 124 from table handler mean when trying to select a single value from a table created as a UNIOn of another table ? root@slimmer:~# perror 127 Error code 127: Unknown error 127 127 = Record-file is crashed Table corrupt ... do mysql repair table tablename; or shell# mysqlcheck -r -p db table -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP: Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone: +62 21 79199577 - HP: +62 8158787286 Web: http://www.1rstwap.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1030: Got error 124 from table handler
What is the handler for Collaborate ?? (What does show create table Collaborate display ?) - Original Message - From: David Herring [EMAIL PROTECTED] To: Dicky Wahyu Purnomo [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, November 08, 2002 2:29 AM Subject: Re: ERROR 1030: Got error 124 from table handler Dicky, Thank you for the quick response - but it appears tables created as unions do not support these repair commands:- # bin/mysqlcheck -r -p test Collaborate Enter password: test.Collaborate error: The handler for the table doesn't support check/repair # mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 43 to server version: 3.23.52-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql repair Collaborate; ERROR 1064: You have an error in your SQL syntax near 'Vodafone_Collaborate' at line 1 mysql Any other ideas ? Would trying an older version of MySQL be useful ? thx dave Dicky Wahyu Purnomo wrote: Pada Fri, 08 Nov 2002 01:59:20 + David Herring [EMAIL PROTECTED] menulis: What does ERROR 1030: Got error 124 from table handler mean when trying to select a single value from a table created as a UNIOn of another table ? root@slimmer:~# perror 127 Error code 127: Unknown error 127 127 = Record-file is crashed Table corrupt ... do mysql repair table tablename; or shell# mysqlcheck -r -p db table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Connection
Hi, After a little search, it seems to mean to treat BIGINT as INT. Regards, Jocelyn - Original Message - From: Arthur [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 25, 2002 4:59 PM Subject: Connection Hello mysql, Often I've seen OPTION=16384 in a connection string Whay does it mean? -- Best regards A backpacker in the foot hills of MySQL, Arthur mailto:ArthurMaloney;seipas.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Commands out of sync using PHP
Hi, The problem comes from PHP : http://bugs.php.net/bug.php?id=19529 Regards, Jocelyn - Original Message - From: Willem Bison [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 23, 2002 11:06 AM Subject: Commands out of sync using PHP I'm getting lots of random Commands out of sync; You can't run this command now errors when calling mysql_query(). I'm pretty sure this has started after upgrading to PHP 4.2.3. The only way to stop it is restarting apache. I can't see any pattern in the errors: it happens on different tables, long/slow queries etc. Once the error starts occuring, it happens with about 1/3 of all queries. mysql client api: 3.23.39 mysql server: 3.23.44 apache 1.3.26 php: 4.2.3 red-hat 7.3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to index a large table?
Hi, In fact, I think you run out of disk space. You MYD takes 135 GB. You have 375 GB free When MySQL try to add the index on your database, it copies the MYD and frm under a #sql* name. So again 135 GB are eaten again. So it remains 240 GB to build the index file, so it's possible you run out of free space during the MYI generation ? (what does df report ? ) Regards, Jocelyn - Original Message - From: Chris Stoughton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 22, 2002 9:49 PM Subject: How to index a large table? I have tried a few different variables to allow a create index command to finish successfully. 1. I set tmpdir to be a file system with ample space 2. I increased tmp_table_size 3. I increases myisam_sort_buffer_size to 100M Here are the sizes of the database files: bash-2.04$ ls -l targetTsObj* -rw-rw1 mysqlmysql144173128578 Oct 20 05:37 targetTsObj.MYD -rw-rw1 mysqlmysql5120 Oct 21 11:38 targetTsObj.MYI -rw-rw1 mysqlmysql 32750 Oct 18 21:05 targetTsObj.frm I continue to get this error: Database changed mysql create index targetTsObjobjId on targetTsObj (objId); ERROR 1034: 136 when fixing table mysql It takes 75 minutes, for this to happen. During that time, it creates a set of files called #sql*.MYD, .MYI, and .frm, and these grow until they are identical in size to the targetTsObj.* files. Then, after several minutes of mysqld consuming 99% CPU time, it ends with an error. 1. Is there a variable I should set in my.cnf? 2. Should I be using innodb tables instead of myisam? Thanks. == For the record, here is what mysqladmin variables says: +-+- + | Variable_name | Value | +-+- + | back_log| 50 | | basedir | / | | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | bdb_home| /export/data/dp20.a/data/mysql/ | | bdb_max_lock| 1 | | bdb_logdir | | | bdb_shared_data | OFF | | bdb_tmpdir | /export/data/dp20.a/tmp/ | | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (August 14, 2002) | | binlog_cache_size | 32768 | | character_set | latin1 | | character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /export/data/dp20.a/data/mysql/ | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | have_bdb| YES | | have_gemini | NO | | have_innodb | DISABLED | | have_isam | YES | | have_raid | NO | | have_openssl| NO | | init_file | | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | | | innodb_data_home_dir | | | innodb_file_io_threads | 4 | | innodb_force_recovery | 0 | | innodb_thread_concurrency | 8 | | innodb_flush_log_at_trx_commit | 16777216 | | innodb_fast_shutdown| ON | | innodb_flush_method | | | innodb_lock_wait_timeout| 50 | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | | | innodb_mirrored_log_groups | 1 | | interactive_timeout | 28800 | | join_buffer_size| 131072 | | key_buffer_size | 536866816 | | language| /usr/share/mysql/english/ | | large_files_support | ON | | locked_in_memory| OFF | | log
Re: Error 1034: 136 when fixing table from Create Index on long table
Hi, [root@forum] /usr/local/mysql/var perror 136 Error code 136: Unknown error 136 136 = No more room in index file Are you sure your file system can handle the size of your index file ? Regards, Jocelyn - Original Message - From: Chris Stoughton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 22, 2002 12:26 AM Subject: Error 1034: 136 when fixing table from Create Index on long table I am running 3.23.52-Max under Linux. I now have a table with 54M rows: mysql select count(*) from targetTsObj; +--+ | count(*) | +--+ | 54549046 | +--+ 1 row in set (0.05 sec) Creating an index on this takes 1 hour 10 minutes, with this error: mysql create index targetTsObjobjId on targetTsObj (objId); ERROR 1034: 136 when fixing table The describe command shows that no index has been built. Previously, it had trouble building this same index, and complained about not being able to open a file in /tmp I suspect that /tmp was not large enough, so we changed the tmpdir variable in my.cnf to point to a file system with *plenty* of roomw and restarted the server. myisamchk seems to have no complaints about this table: bash-2.04$ myisamchk targetTsObj Checking MyISAM file: targetTsObj Data records: 54549046 Deleted blocks: 0 - check file-size - check key delete-chain - check record delete-chain - check index reference bash-2.04$ myisamchk -d targetTsObj MyISAM file: targetTsObj Record format: Fixed length Character set: latin1 (8) Data records: 54549046 Deleted blocks: 0 Recordlength: 2643 table description: Key Start Len Index Type bash-2.04$ Please let me know what this error means, how to get around it, or what additional information you need. Thanks! Here is the output of mysqladmin variable +-+- + | Variable_name | Value | +-+- + | back_log| 50 | | basedir | / | | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | bdb_home| /export/data/dp20.a/data/mysql/ | | bdb_max_lock| 1 | | bdb_logdir | | | bdb_shared_data | OFF | | bdb_tmpdir | /export/data/dp20.a/tmp/ | | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (August 14, 2002) | | binlog_cache_size | 32768 | | character_set | latin1 | | character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /export/data/dp20.a/data/mysql/ | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | have_bdb| YES | | have_gemini | NO | | have_innodb | DISABLED | | have_isam | YES | | have_raid | NO | | have_openssl| NO | | init_file | | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | | | innodb_data_home_dir | | | innodb_file_io_threads | 4 | | innodb_force_recovery | 0 | | innodb_thread_concurrency | 8 | | innodb_flush_log_at_trx_commit | 16777216 | | innodb_fast_shutdown| ON | | innodb_flush_method | | | innodb_lock_wait_timeout| 50 | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | | | innodb_mirrored_log_groups | 1 | | interactive_timeout | 28800 | | join_buffer_size| 131072 | | key_buffer_size | 536866816 | | language| /usr/share/mysql/english/ | | large_files_support
Re: ERROR 2013: Lost connection to MySQL server during query
Hi, Run mysqld_safe with the --log-bin switch (it will recreate a new bin log at each crash, so it's much more simple to see which query is responsible for the crash). Regards, Jocelyn Fournier - Original Message - From: Eric [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 18, 2002 6:37 PM Subject: Re: ERROR 2013: Lost connection to MySQL server during query Hi, I know there is more than one reason that this error can occur, but I have not seen a good guide to debuging. It might help, if you are using DBI to do a trace to a log file. I have this happen every once in a while, but can't track it down because it is one query out of hundreds and I can't reproduce it. The same query executing over and over and over, but sometimes it chokes with the Lost connection error. Isn't there a way to make the mysql error log a LOT more verbose? Eric At 11:56 AM 2002-10-18 -0400, //mikezero/ wrote: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Repair TABLE - OPERATION FAILED
Hi, perror 28 Error code 28: No space left on device Make some space on your hard disk ;) Regards, Jocelyn - Original Message - From: tl [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 11, 2002 10:55 AM Subject: Repair TABLE - OPERATION FAILED Hello, Please help me. mysql repair table none; +++--+-+ | Table | Op | Msg_type | Msg_text| +++--+-+ | n_toccata.none | repair | error| 28 when writing to datafile | | n_toccata.none | repair | error| 28 when writing to datafile | | n_toccata.none | repair | status | Operation failed| +++--+-+ 3 rows in set (0.14 sec) mysql select * from none limit 12; ERROR 1016: Can't open file: 'none.MYD'. (errno: 144) mysql check table none; ++---+--+--- --+ | Table | Op| Msg_type | Msg_text | ++---+--+--- --+ | n_toccata.none | check | warning | Table is marked as crashed and last repair failed | | n_toccata.none | check | warning | Size of indexfile is: 309248 Should be: 35840 | | n_toccata.none | check | error| Size of datafile is: 2809856 Should be: 2809916 | | n_toccata.none | check | error| Corrupt | ++---+--+--- --+ 4 rows in set (0.00 sec) Thank's very much. Alvydas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
Heikki, Jeremy, FYI, I've just tested latest pull (including Monty's patch) without Heikki 1.1318 changeset (Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY orders wrong then;) and now it works perfectly fine : mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE numeropost='0' ORDER BY numreponse ASC LIMIT 0,3; +---+-++ | LOWER(pseudo) | date| numreponse | +---+-++ | kytine| 2002-09-18 20:37:31 | 1360 | | joce | 2002-09-18 20:42:20 | 1361 | | kytine| 2002-09-18 20:46:32 | 1362 | +---+-++ 3 rows in set (0.82 sec) mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE numeropost='0' ORDER BY numreponse DESC LIMIT 0,3; +---+-++ | LOWER(pseudo) | date| numreponse | +---+-++ | kytine| 2002-09-18 20:46:32 | 1362 | | joce | 2002-09-18 20:42:20 | 1361 | | kytine| 2002-09-18 20:37:31 | 1360 | +---+-++ 3 rows in set (0.00 sec) So it seems ORDER BY + WHERE primarykey LIKE 'abc%' ORDER BY primarykey DESC works fine now. Regards, Jocelyn - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Jocelyn Fournier [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 8:39 PM Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180... Jeremy, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: Jocelyn Fournier [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 8:25 PM Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180... On Wed, Sep 25, 2002 at 09:43:13PM +0300, Heikki Tuuri wrote: Jocelyn, below the latest patch which puts the code as it was in 4.0.3. Some LIKE 'abc%' ... DESC queries may return wrong results, but this is the best we can get to 4.0.4. I have to ask Monty about the use of HA_READ_PREFIX_LAST. Other bugs may be lurking in the use/non-use of that search flag. FYI, my server is happy now too. thank you for testing the source tree! Now we found and fixed these bugs before the actual release was made :). One bug remains: I tested yesterday that WHERE primarykey LIKE 'abc%' ORDER BY primarykey DESC; really works wrong in upcoming InnoDB-4.0.4. It only returns rows where primarykey = 'abc'. But better fix that bug in 4.0.5 since it requires a somewhat more fundamental change. Until that users beware! I'll rebuild with Monty's latest patch and see that things work as expected. Thanks! Jeremy Thank you, Heikki Innobase Oy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 51 days, processed 1,083,975,227 queries (244/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
BTW, I assume Monty's change means that MyISAM tables are able to not use filesorting on WHERE key_name1='constant' ORDER BY key_name DESC type queries whereas InnoDB tables are not able to do so ? e.g. : mysql ALTER TABLE threadhardwarefr13 type=innodb; mysql EXPLAIN SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE numeropost='18' ORDER BY numreponse DESC LIMIT 0,3; ++--+---+-+-+---+--- ---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+-+-+---+--- ---++ | threadhardwarefr13 | ref | PRIMARY | PRIMARY | 3 | const | 39 | where used; Using filesort | ++--+---+-+-+---+--- ---++ 1 row in set (0.00 sec) mysql ALTER TABLE threadhardwarefr13 type=myisam; Query OK, 1387 rows affected (3.54 sec) Records: 1387 Duplicates: 0 Warnings: 0 mysql EXPLAIN SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE numeropost='18' ORDER BY numreponse DESC LIMIT 0,3; ++--+---+-+-+---+--- ---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+-+-+---+--- ---++ | threadhardwarefr13 | ref | PRIMARY | PRIMARY | 3 | const | 77 | where used | ++--+---+-+-+---+--- ---++ 1 row in set (0.00 sec) Regards, Jocelyn - Original Message - From: Jocelyn Fournier [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 8:53 PM Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180... Heikki, Jeremy, FYI, I've just tested latest pull (including Monty's patch) without Heikki 1.1318 changeset (Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY orders wrong then;) and now it works perfectly fine : mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE numeropost='0' ORDER BY numreponse ASC LIMIT 0,3; +---+-++ | LOWER(pseudo) | date| numreponse | +---+-++ | kytine| 2002-09-18 20:37:31 | 1360 | | joce | 2002-09-18 20:42:20 | 1361 | | kytine| 2002-09-18 20:46:32 | 1362 | +---+-++ 3 rows in set (0.82 sec) mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE numeropost='0' ORDER BY numreponse DESC LIMIT 0,3; +---+-++ | LOWER(pseudo) | date| numreponse | +---+-++ | kytine| 2002-09-18 20:46:32 | 1362 | | joce | 2002-09-18 20:42:20 | 1361 | | kytine| 2002-09-18 20:37:31 | 1360 | +---+-++ 3 rows in set (0.00 sec) So it seems ORDER BY + WHERE primarykey LIKE 'abc%' ORDER BY primarykey DESC works fine now. Regards, Jocelyn - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Jocelyn Fournier [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 8:39 PM Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180... Jeremy, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: Jocelyn Fournier [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 8:25 PM Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180... On Wed, Sep 25, 2002 at 09:43:13PM +0300, Heikki Tuuri wrote: Jocelyn, below the latest patch which puts the code as it was in 4.0.3. Some LIKE 'abc%' ... DESC queries may return wrong results, but this is the best we can get to 4.0.4. I have to ask Monty about the use of HA_READ_PREFIX_LAST. Other bugs may be lurking in the use/non-use of that search flag. FYI, my server is happy now too. thank you for testing the source tree! Now we found and fixed these bugs before the actual release was made :). One bug remains: I tested yesterday that WHERE primarykey LIKE 'abc%' ORDER BY primarykey DESC; really works wrong in upcoming InnoDB-4.0.4. It only returns rows where primarykey = 'abc'. But better
Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
Yes, I've also tested the LIKE 'abc%' but forgot to include it in my mail :)
CREATE TABLE `threadhardwarefr12` (
`numeropost` mediumint(8) unsigned NOT NULL default '0',
`numreponse` int(10) unsigned NOT NULL default '0',
`pseudo` varchar(35) NOT NULL default '',
`date` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`pseudo`)
) TYPE=InnoDB
mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE
pseudo LIKE 'kyt%' ORDER BY pseudo;
+---+-++
| LOWER(pseudo) | date| numreponse |
+---+-++
| kytine| 2002-09-18 20:37:31 | 1360 |
| kytoune | 2002-09-18 20:42:20 | 1361 |
+---+-++
2 rows in set (0.00 sec)
Regards,
Jocelyn
- Original Message -
From: Heikki Tuuri [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, September 26, 2002 9:28 PM
Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
Jocelyn,
- Original Message -
From: Jocelyn Fournier [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, September 26, 2002 9:53 PM
Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
Heikki, Jeremy,
FYI, I've just tested latest pull (including Monty's patch) without
Heikki
1.1318 changeset (Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER
BY
orders wrong then;) and now it works perfectly fine :
mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12
WHERE
numeropost='0' ORDER BY numreponse ASC LIMIT 0,3;
+---+-++
| LOWER(pseudo) | date| numreponse |
+---+-++
| kytine| 2002-09-18 20:37:31 | 1360 |
| joce | 2002-09-18 20:42:20 | 1361 |
| kytine| 2002-09-18 20:46:32 | 1362 |
+---+-++
3 rows in set (0.82 sec)
mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12
WHERE
numeropost='0' ORDER BY numreponse DESC LIMIT 0,3;
+---+-++
| LOWER(pseudo) | date| numreponse |
+---+-++
| kytine| 2002-09-18 20:46:32 | 1362 |
| joce | 2002-09-18 20:42:20 | 1361 |
| kytine| 2002-09-18 20:37:31 | 1360 |
+---+-++
3 rows in set (0.00 sec)
So it seems ORDER BY + WHERE primarykey LIKE 'abc%' ORDER BY primarykey
DESC
works fine now.
it is the LIKE 'abc%' which does not work. Your test above does not have
LIKE.
I tested now that adding the flag
HA_NOT_READ_PREFIX_LAST
back to index_flags() in ha_innodb.h seems to fix also the LIKE problem.
Thus people who compile themselves can fix also this last known bug
through
the patch below. The patch probably does not make it to 4.0.4 because the
4.0.4 build may have been finished today. 4.0.3 does not have the DESC
bug.
But I will add PAGE_CUR_LE_OR_EXTENDS to InnoDB-4.0.5 and remove the above
flag. Having different search methods for InnoDB and MyISAM is too
bug-prone, as we have seen.
Regards,
Jocelyn
Regards,
Heikki
ChangeSet
1.1323 02/09/26 21:21:44 [EMAIL PROTECTED] +1 -0
ha_innodb.h:
Put the flag HA_NOT_READ_PREFIX_LAST back to index_flags: seems to fix
also the LIKE ... DESC bug
sql/ha_innodb.h
1.46 02/09/26 21:21:34 [EMAIL PROTECTED] +1 -5
Put the flag HA_NOT_READ_PREFIX_LAST back to index_flags: seems to fix
also the LIKE ... DESC bug
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: heikki
# Host: hundin.mysql.fi
# Root: /home/heikki/mysql-4.0
--- 1.45/sql/ha_innodb.h Wed Sep 25 20:35:01 2002
+++ 1.46/sql/ha_innodb.h Thu Sep 26 21:21:34 2002
@@ -81,10 +81,6 @@
HA_NO_WRITE_DELAYED |
HA_PRIMARY_KEY_IN_READ_INDEX |
HA_DROP_BEFORE_CREATE |
- /* We should also list HA_NOT_READ_PREFIX_LAST
- here but it currently seems to break ORDER BY;
- until release 4.0.5 some LIKE 'abc%' ... DESC
- queries will not work correctly */
HA_NO_PREFIX_CHAR_KEYS |
HA_TABLE_SCAN_ON_INDEX),
last_dup_key((uint) -1),
@@ -100,7 +96,7 @@
ulong index_flags(uint idx) const
{
return (HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER |
- HA_KEY_READ_ONLY);
+ HA_KEY_READ_ONLY | HA_NOT_READ_PREFIX_LAST);
}
uint max_record_length
Re: Mysql vs. Oracle and concat ||
Hi, Well, why not trying... concat() :) SELECT concat(numer,',',text) FROM Table; http://www.mysql.com/doc/en/String_functions.html Regards, Jocelyn Fournier - Original Message - From: MySQL [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 8:46 PM Subject: Mysql vs. Oracle and concat || Hi all, I'm a DBA in the Oracle World. I want to make a sql query in mysql, with a concat (||) known i Oracle world. Like this. select numer ||','|| text from Table: Where the output will be eg. 1,HI 2,Frank And so on Hope one of you can see my problem. I've tried the same on mysql, but it mess all up. Regards Frank - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
Hi Jeremy, Same problem for me, I submitted a bug report with a repeatable testcase yesterday to the bugs list. Reading the source code and changeset 1.1287, it appears this part of the source code should be desactivated ? (ChangeSet 1.1287 02/09/15 02:23:53 [EMAIL PROTECTED] +2 -0 ha_innodb.h, ha_innodb.cc: Reverted the change which allowed use of HA_READ_PREFIX_LAST in InnoDB) Regards, Jocelyn - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, September 25, 2002 5:19 PM Subject: InnoDB: Assertion failure in file ha_innodb.cc line 2180... Heikki and others, One of my slaves is dying frequently. It runs MySQL 4.0.4 (pulled yesterday) on FreeBSD. I'm getting an assertion failure on line 2180, which appears to be: case HA_READ_PREFIX_LAST: ut_a(0); return(PAGE_CUR_LE); What can I do to help debug this? Thanks, Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 50 days, processed 1,063,578,875 queries (244/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
Hi Heikki,
The query doesn't crash anymore, but the ORDER BY doesn't work at all (I
tested it on a table with data).
How-to-repeat :
INSERT INTO threadhardwarefr13 (pseudo,date,numreponse) VALUES
('kytine','2002-09-18 20:37:31','1360'),('joce','2002-09-18
20:42:20','1361'),('kytine','2002-09-18 20:46:32','1362');
mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE
numeropost='0' ORDER BY numreponse DESC LIMIT 0,3;
+---+-++
| LOWER(pseudo) | date| numreponse |
+---+-++
| kytine| 2002-09-18 20:37:31 | 1360 |
| joce | 2002-09-18 20:42:20 | 1361 |
| kytine| 2002-09-18 20:46:32 | 1362 |
+---+-++
3 rows in set (0.00 sec)
mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE
numeropost='0' ORDER BY numreponse ASC LIMIT 0,3;
+---+-++
| LOWER(pseudo) | date| numreponse |
+---+-++
| kytine| 2002-09-18 20:37:31 | 1360 |
| joce | 2002-09-18 20:42:20 | 1361 |
| kytine| 2002-09-18 20:46:32 | 1362 |
+---+-++
3 rows in set (0.00 sec)
Regards,
Jocelyn
- Original Message -
From: Heikki Tuuri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, September 25, 2002 7:09 PM
Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
Jeremy, Jocelyn,
can you try the following this patch?
The flag which bans MySQL using a descending cursor to calculate
column LIKE 'jhghj%' ORDER BY column DESC
queries was apparently put to the wrong place in ha_innodb.h. The
assertion
I had added to 4.0.4 revealed this hidden bug.
The patch fixes also the crash submitted by Jocelyn Fournier earlier
today:
...
CREATE TABLE `threadhardwarefr13` (
`numeropost` mediumint(8) unsigned NOT NULL default '0',
`numreponse` int(10) unsigned NOT NULL auto_increment,
`pseudo` varchar(35) NOT NULL default '',
`date` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`numeropost`,`numreponse`),
UNIQUE KEY `numreponse` (`numreponse`),
KEY `pseudo` (`pseudo`,`numeropost`)
) TYPE=InnoDB;
SELECT LOWER(pseudo),date FROM threadhardwarefr13 WHERE
numeropost='166498'
ORDER BY numreponse DESC LIMIT 0,3;
- assertion failure at line 2180 of ha_innodb.cc
...
I have to check why MySQL uses that flag in Jocelyn's query. It does not
have a LIKE '%' clause.
Thank you for the bug reports,
Heikki
ChangeSet
1.1317 02/09/25 19:02:46 [EMAIL PROTECTED] +2 -0
ha_innodb.cc, ha_innodb.h:
Move to the right place the flag which bans use of
HA_READ_PREFIX_LAST;
add
diagnostics and remove the assertion on line 2180 of ha_innodb.cc
sql/ha_innodb.cc
1.110 02/09/25 19:02:06 [EMAIL PROTECTED] +6 -1
Move to the right place the flag which bans use of
HA_READ_PREFIX_LAST;
add
diagnostics and remove the assertion on line 2180 of ha_innodb.cc
sql/ha_innodb.h
1.44 02/09/25 19:02:03 [EMAIL PROTECTED] +2 -1
Move to the right place the flag which bans use of
HA_READ_PREFIX_LAST;
add
diagnostics and remove the assertion on line 2180 of ha_innodb.cc
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: heikki
# Host: hundin.mysql.fi
# Root: /home/heikki/mysql-4.0
--- 1.109/sql/ha_innodb.cc Tue Sep 24 17:11:56 2002
+++ 1.110/sql/ha_innodb.cc Wed Sep 25 19:02:06 2002
@@ -2179,7 +2179,12 @@
case HA_READ_AFTER_KEY: return(PAGE_CUR_G);
case HA_READ_BEFORE_KEY: return(PAGE_CUR_L);
case HA_READ_PREFIX: return(PAGE_CUR_GE);
- case HA_READ_PREFIX_LAST: ut_a(0); return(PAGE_CUR_LE);
+ case HA_READ_PREFIX_LAST:
+ ut_print_timestamp(stderr);
+fprintf(stderr,
+ InnoDB: Warning: Using HA_READ_PREFIX_LAST\n);
+ return(PAGE_CUR_LE);
+
/* InnoDB does not yet support ..PREFIX_LAST!
We have to add a new search flag
PAGE_CUR_LE_OR_PREFIX to InnoDB. */
--- 1.43/sql/ha_innodb.h Fri Sep 20 23:26:10 2002
+++ 1.44/sql/ha_innodb.h Wed Sep 25 19:02:03 2002
@@ -82,6 +82,7 @@
HA_PRIMARY_KEY_IN_READ_INDEX |
HA_DROP_BEFORE_CREATE |
HA_NO_PREFIX_CHAR_KEYS |
+ HA_NOT_READ_PREFIX_LAST |
HA_TABLE_SCAN_ON_INDEX),
last_dup_key((uint) -1),
start_of_scan(0)
@@ -96,7 +97,7 @@
ulong index_flags(uint idx) const
{
return (HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER |
-HA_KEY_READ_ONLY | HA_NOT_READ_PREFIX_LAST);
+HA_KEY_READ_ONLY);
}
uint max_record_length() const { return HA_MAX_REC_LENGTH; }
uint
Re: MySQL errors in newer PHP 4.2.3
Hi Steven, Some precisions : after taking a look at my mail, I noticed the very first time I saw this error was with PHP 4.2.1 and MySQL 4.0.2 (reported on this list on 27 june 2002). Regards, Jocelyn - Original Message - From: Steven Roussey [EMAIL PROTECTED] To: 'Jocelyn Fournier' [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: 'Mysql' [EMAIL PROTECTED] Sent: Friday, September 20, 2002 2:38 AM Subject: RE: MySQL errors in newer PHP 4.2.3 Hmm, do you use pconnect or connect? I noticed someone changed our PHP script to use persistent connections (likely why the server is running slower). I am wondering if a cancelled connection is being reused or if it just is a more general bug in the mysql client code in PHP Sincerely, Steven Roussey http://Network54.com/?pp=e -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Hi, Same problem for me, although it was already here with 4.2.0 for me (well it seems to be also a high QPS problem...). The problem seems to disappear with an apache restart, but sometimes appear again randomly. Regards, Jocelyn - Original Message - From: Steven Roussey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Mysql [EMAIL PROTECTED] Sent: Friday, September 20, 2002 2:14 AM Subject: MySQL errors in newer PHP 4.2.3 Since updating to 4.2.3, we have been getting intermittent errors of Commands out of sync. Anyone else see this? Sincerely, Steven Roussey http://Network54.com/?pp=e php,sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL errors in newer PHP 4.2.3
Hi, Same problem for me, although it was already here with 4.2.0 for me (well it seems to be also a high QPS problem...). The problem seems to disappear with an apache restart, but sometimes appear again randomly. Regards, Jocelyn - Original Message - From: Steven Roussey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Mysql [EMAIL PROTECTED] Sent: Friday, September 20, 2002 2:14 AM Subject: MySQL errors in newer PHP 4.2.3 Since updating to 4.2.3, we have been getting intermittent errors of Commands out of sync. Anyone else see this? Sincerely, Steven Roussey http://Network54.com/?pp=e php,sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
