Re: LOCK TABLES

2006-10-17 Thread Visolve DB Team
Hi From the analysis of other sources, The error may be due to: 1. MediaWiki was updated from an older version without updating the database. so to update the database, you can use either the maintenance script maintenance/update.php via the command line, or the web installer (rename

Re: result set on prepared statements

2006-10-17 Thread Roland Volkmann
Hello ViSolve DB Team, thank you for response. I guess I didn't write clearly enough what information I need: general usage of prepared statements I already know. The question right now is, if I get a result set containing several rows, must I fetch *all* of them, if I don't use client side

Re: LOCK TABLES

2006-10-17 Thread mdpeters
mysqldump --user root --password=password horsewiki horsewiki.sql Dan Buettner wrote: Hmmm, sounds like something's pretty abnormal here. Any idea what may have been done here? I wonder if you could step around this with a call to mysqldump that doesn't explicitly lock tables ... what is

Re: LOCK TABLES

2006-10-17 Thread mdpeters
I tried this first to no avail. mysqldump --user root --password=password --skip-lock-tables horsewiki horsewiki.sql mysqldump: mysqldump: Couldn't execute 'show create table `archive`': Table 'horsewiki.archive' doesn't exist (1146) I'll try the update next. Visolve DB Team wrote: Hi

Does new Community version change C API licensing?

2006-10-17 Thread Warren Young
I've looked over as much of the information on the new Community vs. Enterprise version stuff as I can find, and I don't see an answer to this question. Basically, I want to know if the MySQL C API is still dual licensed, under the GPL and the MySQL commercial license. If so, I don't see how

Urgent: How to decode base64 via mysql V 5.0.x

2006-10-17 Thread abhishek jain
Hi, I want to decode base 64 string via mysql . Also i am using aspx .net . Pl. help me. Urgent reply will be appreciated -- Regards, Abhishek jain

Re: Urgent: How to decode base64 via mysql V 5.0.x

2006-10-17 Thread abhishek jain
Hi, Yes that solved the problem and was fast. I would like to know now that is there anyother way for the same in earlier versions of mysql. Thanks again, -- Regards, Abhishek jain On 10/17/06, Ady Wicaksono [EMAIL PROTECTED] wrote:

How to rewrite query

2006-10-17 Thread Mindaugas
Hello, For the Radius server we're using MySQL cluster and the following query looks too slow: select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; Table ipaddr is small (~6MB, 38000 records). Fields in WHERE clause have few values and

RE: LOCK TABLES

2006-10-17 Thread Jerry Schwartz
I wonder if this is a permissions problem. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mdpeters [mailto:[EMAIL PROTECTED] Sent: Monday, October 16, 2006 9:19 PM To: Dan

Re: How to rewrite query

2006-10-17 Thread Dan Buettner
Mindaugas, can you post the output of SHOW CREATE TABLE ipaddr; and EXPLAIN select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; When you say it's too slow, how slow is it? And how fast when it is a memory table? Also, which specific version

Re: Re: How to rewrite query

2006-10-17 Thread Dan Buettner
You should strongly consider adding an index on the fields you're querying against. Right now, none of the fields in your query are indexed in the table. I would try something like this for starters: a multi-column index against all the fields in the query you showed. If you have other queries

Removing DBs from replication

2006-10-17 Thread Marcus Bointon
I have a replication setup with two servers. How can I remove one of the replicated databases from replication so it's available on only one server? All DBs are replicated and there are no replicate-do-db options set. I've tried setting replicaten-do-db for all DBs except the one I want to

Re: How to rewrite query

2006-10-17 Thread mos
At 08:34 AM 10/17/2006, you wrote: Hello, For the Radius server we're using MySQL cluster and the following query looks too slow: select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; Table ipaddr is small (~6MB, 38000 records). Fields in

Re: LOCK TABLES

2006-10-17 Thread mdpeters
I execute using root permissions. I successfully upgraded mediawiki to the latest mediawiki-1.8.2 version for grins. I ran php -cli ./maintenance/update.php without trouble. Jerry Schwartz wrote: I wonder if this is a permissions problem. Regards, Jerry Schwartz Global Information

Prefix Indices

2006-10-17 Thread Robert DiFalco
I have some long VARCHAR fields that a user will sometimes sort on. Does a prefix index in any way help with sorting or just for lookups? Will it speed up a filesort? I couldn't find this information in How MySQL uses indices. R. -- MySQL General Mailing List For list archives:

RE: How to rewrite query

2006-10-17 Thread Jerry Schwartz
I would think that with so few possible values for all but the ip field, indexing the other fields would accomplish nothing. In fact, I'd be surprised if the optimizer didn't realize that and do a sequential read anyways. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington

Re: RE: How to rewrite query

2006-10-17 Thread Dan Buettner
I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the

Hungarian collation

2006-10-17 Thread Peter Gulutzan
Hi, MySQL is looking for an authoritative, official statement which states all the current Hungarian collation rules. Please let other MySQL-using Hungarians (especially if you know a user group in Hungary) know about these questions. Best of all would be a translation of the Hungarian government

RE: References on Optimizing File Sort

2006-10-17 Thread Robert DiFalco
Btw, this is using the InnoDB engine. -Original Message- From: Robert DiFalco Sent: Tuesday, October 17, 2006 9:26 AM To: mysql@lists.mysql.com Subject: References on Optimizing File Sort I have an unavoidable filesort in a very large query. Can someone point me to references for

RE: RE: How to rewrite query

2006-10-17 Thread Jerry Schwartz
I didn't think of that (combinations). You are probably right. Due to my background, I tend not to think a lot about multi-column indices. I would think that you want field with the most possible values first, then the next, etc. Is that what you were thinking? Regards, Jerry Schwartz Global

RE: RE: How to rewrite query

2006-10-17 Thread William R. Mussatto
Would it not be best to have the field with the fewest repeats (i.e., the closest to unique) first, or is that what you meant. Bill On Tue, October 17, 2006 10:12, Jerry Schwartz said: I didn't think of that (combinations). You are probably right. Due to my background, I tend not to think a lot

Re: RE: RE: How to rewrite query

2006-10-17 Thread Dan Buettner
Yes, it'd be best to have the values with highest cardinality / most uniqueness first. On 10/17/06, William R. Mussatto [EMAIL PROTECTED] wrote: Would it not be best to have the field with the fewest repeats (i.e., the closest to unique) first, or is that what you meant. Bill On Tue, October

RE: RE: How to rewrite query

2006-10-17 Thread Jerry Schwartz
That's what Dan (and I) meant. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 1:28 PM To:

Re: How to rewrite query

2006-10-17 Thread Martin Skold
Hi! Try: set engine_condition_pushdown = on; explain select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; to see if you can push the predicates to improve performance. BR -- Martin Mindaugas wrote: Hello, For the Radius server we're using

Re: Does new Community version change C API licensing?

2006-10-17 Thread Kaj Arnö
Warren, As part of today's Press Release on MySQL Enterprise, there's no change in the client side licensing. However, as part of the MySQL Winter of Code and the Connector contest, we have something in store which we will be sharing with you next week. As for free-of-charge, the need to

sql query

2006-10-17 Thread Peter
Hello, Lets suppose I have a table like this one id id_1 id_2 date_time 1 101 1000 2006-07-04 11:25:43 2 102 1001 2006-07-04 11:26:43 3 101 1005 2006-07-04 11:27:43 4 103 1000 2006-07-04 11:25:43 I want to find all id_2 that has same id_1 and time difference in records is no more than 5 minutes

Re: sql query

2006-10-17 Thread Dan Buettner
Hi Peter - Something like this ought to work: SELECT t1.id_2 FROM mytable t1, mytable t2 WHERE t1.id_1 = t2.id_1 AND t1.id != t2.id AND ABS( UNIX_TIMESTAMP(t1.date_time) - UNIX_TIMESTAMP(t2.date_time) ) = 300 Dan On 10/17/06, Peter [EMAIL PROTECTED] wrote: Hello, Lets suppose I have a table

Re: sql query

2006-10-17 Thread Peter Brawley
I want to find all id_2 that has same id_1 and time difference in records is no more than 5 minutes ... How about ... SELECT id_2 FROM tbl AS t1 JOIN tbl AS t2 ON t1.id_2 = t2.id_1 WHERE ABS(SEC_TO_TIME(t1.date_time)-SEC_TO_TIME(t2.date_time))=300; PB - Peter wrote: Hello, Lets

Re: sql query

2006-10-17 Thread Rolando Edwards
Dan's is correct because the clause 'AND t1.id != t2.id' prevents checking a row against itself since the time diff with a row against itself is zero, which is less than 300 - Original Message - From: Dan Buettner [EMAIL PROTECTED] To: Peter [EMAIL PROTECTED] Cc: mysql@lists.mysql.com

Query question

2006-10-17 Thread Erick Carballo
Hello, I would really appreciate your help regarding a query. First, some background: The query is being executed on the following table: mysql describe locBridgeImageLocLevel5; +---+--+--+-+-++ | Field

References on Optimizing File Sort

2006-10-17 Thread Robert DiFalco
I have an unavoidable filesort in a very large query. Can someone point me to references for optimizing filesort? I'm assuming this is going to be changes to my.ini or the hardware. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Re: Query question

2006-10-17 Thread Dan Buettner
Erick, maybe I'm missing something or you mistyped, but you appear to be saying this: you want 2356 and not 13128 but your last SQL query is excluding only 18302. 13128 is not mentioned in the query. Try re-running the query with 13128 instead of 18302 ? Dan On 10/17/06, Erick Carballo

Re: sql query

2006-10-17 Thread Peter
Rolando Edwards wrote: Dan's is correct because Thank you ALL for your kind help !!! Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL

Re: References on Optimizing File Sort

2006-10-17 Thread Dan Buettner
Robert, off the top of my head, you'll probably want to make the sort_buffer_size as large as you can, keeping in mind that this memory setting is allocated per thread. In other words, if you have up to 32 threads, and you allocate 100 MB to this setting, you could eat up 3200 MB this way if

Re: Query question

2006-10-17 Thread Erick Carballo
Dan, thanks for your prompt response. You are correct: I mistyped. However, if I ran the query as you suggest, I obtain the same results: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId)

Re: References on Optimizing File Sort

2006-10-17 Thread Jay Pipes
On Tue, 2006-10-17 at 09:26 -0700, Robert DiFalco wrote: I have an unavoidable filesort in a very large query. Can someone point me to references for optimizing filesort? I'm assuming this is going to be changes to my.ini or the hardware. Well, one method to *eliminate* Using filesort is to

Re: Re: Query question

2006-10-17 Thread Dan Buettner
I see what's happening, Erick. It's matching all the rows in loc1 and loc2 with the same image id. It *is* excluding 13128, but image id 1 is still appearing because of the rows where they match *besides* 13128. For example, 18302 and actually also 2356 since you're joining a table on itself.

Binary Log Files and Load Data In-File

2006-10-17 Thread Ow Mun Heng
Hi Guys, Need some pointers. I've got a MySQL server (5.0.22) which is basically pulling data from SQL Server into a file and then I'm using mysqlimport to load the data into the DB. The updates are being generated like every 2 to 5 seconds. Due to this, my Binary Log files are huge! (and many)

Alter Table Add Column - How Long to update?

2006-10-17 Thread Ow Mun Heng
Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I

'Not a valid MySQL result resource' error

2006-10-17 Thread List
Hello, I'm running f.a.m.p, f =freebsd 4.7 and mysql is 3.23.52. Anyway, I inherited a website from someone else's server(I don't know what they we're running) but the admin section of the website generates this error iin the apache error log when trying to login( on the screen just takes

Re: Alter Table Add Column - How Long to update?

2006-10-17 Thread Ow Mun Heng
On Wed, 2006-10-18 at 09:29 +0800, Ow Mun Heng wrote: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table