Re: LOCK TABLES
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
LocalSettings.php, then go to the wiki).
2. --opt is enabled by default with mysqldump, and part of what it does it
lock tables. So try the backup without lock tables, by adding
--skip-lock-tables.
Thanks
ViSolve DB Team.
- Original Message -
From: mdpeters [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, October 16, 2006 9:14 PM
Subject: LOCK TABLES
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: result set on prepared statements
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 cursor. Because when using classic functions instead of prepared statements, I really must fetch *all* rows to avoid memory leak on database engine. With best regards, Roland. Visolve DB Team schrieb am 17.10.2006 07:57: Hi, Hope this link will be useful: http://mirrors.sunsite.dk/mysql/doc/refman/5.1/en/c-api-prepared-statement-functions.html Thanks ViSolve DB Team - Original Message - From: Roland Volkmann [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, October 15, 2006 5:59 PM Subject: result set on prepared statements Hello all, using MySQL C API function |mysql_query() with a query producing a result set, I have to fetch *all* records, if I use ||mysql_use_result() to avoid a client side cursor. So it's written in the manual section |22.2.3.70. But if I want to use prepared statements with MySQL C API functions |mysql_stmt_prepare(), ||mysql_stmt_execute() and ||mysql_stmt_fetch(), I can't find anything in the manual, whether I also have to fetch *all* records, when not using ||mysql_stmt_store_result() (I don't want to use client side cursor). MySQL Version is 5.0.26 on Windows 32 Bit. Any Information is welcome. With best regards, Roland. | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES
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 the commad you're running again? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I tried mv archive.frm .archive.frm first. Then I ran mysqldump again. It moves past archive and onto another table. I did this 6 times, each time moving the next one it complained about until I stopped and put them all back. Dan Buettner wrote: Try looking in the filesystem for the file(s) called archive in the database directory, and move them somewhere else (or if you know you don't need them, delete them). If it is/was a MyISAM table, or just an errant file, this should work. If it is/was InnoDB, this will be trickier ... Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: As you can see, it is clearly showing up but I cannot do anything with it. mysql show tables; +-+ | Tables_in_horsewiki | +-+ | archive | | categorylinks | | externallinks | | hitcounter | | horse_archive | | horse_categorylinks | | horse_externallinks | | horse_hitcounter| | horse_image | | horse_imagelinks| | horse_interwiki | | horse_ipblocks | | horse_job | | horse_logging | | horse_math | | horse_objectcache | | horse_oldimage | | horse_page | | horse_pagelinks | | horse_querycache| | horse_recentchanges | | horse_revision | | horse_searchindex | | horse_site_stats| | horse_templatelinks | | horse_text | | horse_trackbacks| | horse_transcache| | horse_user | | horse_user_groups | | horse_user_newtalk | | horse_validate | | horse_watchlist | | image | | imagelinks | | interwiki | | ipblocks| | job | | logging | | math| | objectcache | | oldimage| | page| | pagelinks | | querycache | | recentchanges | | revision| | searchindex | | site_stats | | templatelinks | | text| | trackbacks | | transcache | | user| | user_groups | | user_newtalk| | validate| | watchlist | +-+ 58 rows in set (0.00 sec) mysql SELECT * FROM archive LIMIT 1; ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist mysql drop table archive; ERROR 1051 (42S02): Unknown table 'archive' Dan Buettner wrote: Michael, is the 'archive' table present in your database? e.g., if you do a 'LIST TABLES', does it show up? What happens if you do a SELECT * FROM archive LIMIT 1 ? I'd hazard a guess that you may have a table definition file with no actual table data files, if you're on MyISAM tables. If you don't need the archive table, can you DROP it successfully? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I am having a devil of a time moving a database from one server to another. My database is one that is in production to support the mediawiki wiki application. This is a Solaris Sparc 10 system using the mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki. I execute this: # mysqldump --user root --password=password horsewiki horsewiki.sql and get this: mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when using LOCK TABLES I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the database to an SQL file. When I attempt to import it on the other system, I get this: Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 494 STR: / SQL: -- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM -- Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon / link rel=shortcut icon href=./favicon.ico type=image/x-icon / titlephpMyAdmin/title meta http-equiv=Content-Type content=text/html; charset=utf-8 / link rel=stylesheet type=text/css href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right /
Re: LOCK TABLES
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
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
LocalSettings.php, then go to the wiki).
2. --opt is enabled by default with mysqldump, and part of what it
does it lock tables. So try the backup without lock tables, by adding
*--skip-lock-tables.*
Thanks
ViSolve DB Team.
- Original Message -
From: mdpeters [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
To: mysql@lists.mysql.com mailto:mysql@lists.mysql.com
Sent: Monday, October 16, 2006 9:14 PM
Subject: LOCK TABLES
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is
horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist
when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the
problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database:
`horsewiki` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
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]
Does new Community version change C API licensing?
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 this new model is any different than before, except that the commercial version is now more expensive. The practical upshot of the previous licensing scheme, for people that used the MySQL C API, was that you either had to buy a MySQL commercial license, or release your code under the GPL. That means MySQL could never be free-of-charge (quoting today's email from Kaj Arnö) for those people. For that to happen, the C API must be released under a more liberal license (LGPL at least), or explicitly made public-domain. So, has anything substantial changed besides the price of a commercial license? -- Warren Young Maintainer of MySQL++ http://tangentsoft.net/mysql++/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent: How to decode base64 via mysql V 5.0.x
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
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: http://firestuff.org/wordpress/wp-content/uploads/2006/03/base64.sql On 10/17/06, abhishek jain [EMAIL PROTECTED] wrote: 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
How to rewrite query
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 no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOCK TABLES
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 Buettner Cc: mysql@lists.mysql.com Subject: Re: LOCK TABLES I tried mv archive.frm .archive.frm first. Then I ran mysqldump again. It moves past archive and onto another table. I did this 6 times, each time moving the next one it complained about until I stopped and put them all back. Dan Buettner wrote: Try looking in the filesystem for the file(s) called archive in the database directory, and move them somewhere else (or if you know you don't need them, delete them). If it is/was a MyISAM table, or just an errant file, this should work. If it is/was InnoDB, this will be trickier ... Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: As you can see, it is clearly showing up but I cannot do anything with it. mysql show tables; +-+ | Tables_in_horsewiki | +-+ | archive | | categorylinks | | externallinks | | hitcounter | | horse_archive | | horse_categorylinks | | horse_externallinks | | horse_hitcounter| | horse_image | | horse_imagelinks| | horse_interwiki | | horse_ipblocks | | horse_job | | horse_logging | | horse_math | | horse_objectcache | | horse_oldimage | | horse_page | | horse_pagelinks | | horse_querycache| | horse_recentchanges | | horse_revision | | horse_searchindex | | horse_site_stats| | horse_templatelinks | | horse_text | | horse_trackbacks| | horse_transcache| | horse_user | | horse_user_groups | | horse_user_newtalk | | horse_validate | | horse_watchlist | | image | | imagelinks | | interwiki | | ipblocks| | job | | logging | | math| | objectcache | | oldimage| | page| | pagelinks | | querycache | | recentchanges | | revision| | searchindex | | site_stats | | templatelinks | | text| | trackbacks | | transcache | | user| | user_groups | | user_newtalk| | validate| | watchlist | +-+ 58 rows in set (0.00 sec) mysql SELECT * FROM archive LIMIT 1; ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist mysql drop table archive; ERROR 1051 (42S02): Unknown table 'archive' Dan Buettner wrote: Michael, is the 'archive' table present in your database? e.g., if you do a 'LIST TABLES', does it show up? What happens if you do a SELECT * FROM archive LIMIT 1 ? I'd hazard a guess that you may have a table definition file with no actual table data files, if you're on MyISAM tables. If you don't need the archive table, can you DROP it successfully? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I am having a devil of a time moving a database from one server to another. My database is one that is in production to support the mediawiki wiki application. This is a Solaris Sparc 10 system using the mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki. I execute this: # mysqldump --user root --password=password horsewiki horsewiki.sql and get this: mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when using LOCK TABLES I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the database to an SQL file. When I attempt to import it on the other system, I get this: Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 494 STR: / SQL: -- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM -- Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon / link rel=shortcut icon href=./favicon.ico type=image/x-icon / titlephpMyAdmin/title meta http-equiv=Content-Type content=text/html; charset=utf-8 /
Re: How to rewrite query
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 of 5.0 are you on? 5.0.x ... what is x? Dan On 10/17/06, Mindaugas [EMAIL PROTECTED] 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 WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to rewrite query
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 you run regularly, you might evaluate those to see if a different field order in the index makes sense. But I think this may helpl your problem query immensely: ALTER TABLE ipaddr ADD INDEX multi_col_idx (stype, ls_id, pool, allocated); Another suggestion I have is for you to change either your query slightly, or your table structure slightly. Your field ls_id is a VARCHAR field, but you are querying it like it is a numeric field, which may be forcing MySQL to do a type conversion on all the rows in the table. Either change your query to look for Is_id = '3' (note the quotes) or change the column type to an INT and leave your query as-is. (you know which will better fit your data) If you do both of these things, I think it should help a lot. Best, Dan On 10/17/06, Mindaugas [EMAIL PROTECTED] wrote: SHOW CREATE TABLE ipaddr; CREATE TABLE `ipaddr` ( `ip` varchar(15) NOT NULL, `pool` varchar(20) NOT NULL, `stype` varchar(1) NOT NULL, `sclass` varchar(1) NOT NULL, `radserv` varchar(1) NOT NULL, `ls_id` varchar(1) NOT NULL default '0', `allocated` datetime default NULL, `msisdn` varchar(20) default NULL, `imsi` varchar(20) default NULL, `session_id` varchar(30) default NULL, `user_name` varchar(20) default NULL, PRIMARY KEY (`ip`), UNIQUE KEY `ipaddr_msisdn_idx` (`msisdn`) ) ENGINE=NDB DEFAULT CHARSET=latin1 EXPLAIN select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; ++-++--+---+--+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+--+-+--+---+-+ | 1 | SIMPLE | ipaddr | ALL | NULL | NULL | NULL| NULL | 37896 | Using where | ++-++--+---+--+-+--+---+-+ When you say it's too slow, how slow is it? And how fast when it is a memory table? With NDB table during peak loads our scripts often does not get the answer. And mysqladmin proc always shows that query in execution. With MEMORY table most often I see sleeping mysql process. I thought that after we find free ip we change allocated to not null. So at the end beginning of table consists of records with allocated is not null. So every query has to pass ~8000 records to find allocated is null row. Am I right there and how to avoid that? Also, which specific version of 5.0 are you on? 5.0.x ... what is x? 5.0.26-max from mysql RPMs. Thanks, Mindaugas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Removing DBs from replication
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 remove, and setting replicate-ignore-db, but it doesn't 'unreplicate' it - after a restart, do and ignore columns are still empty in show master status. What else do I need to do? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rewrite query
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 WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES
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 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 Buettner Cc: mysql@lists.mysql.com Subject: Re: LOCK TABLES I tried mv archive.frm .archive.frm first. Then I ran mysqldump again. It moves past archive and onto another table. I did this 6 times, each time moving the next one it complained about until I stopped and put them all back. Dan Buettner wrote: Try looking in the filesystem for the file(s) called archive in the database directory, and move them somewhere else (or if you know you don't need them, delete them). If it is/was a MyISAM table, or just an errant file, this should work. If it is/was InnoDB, this will be trickier ... Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: As you can see, it is clearly showing up but I cannot do anything with it. mysql show tables; +-+ | Tables_in_horsewiki | +-+ | archive | | categorylinks | | externallinks | | hitcounter | | horse_archive | | horse_categorylinks | | horse_externallinks | | horse_hitcounter| | horse_image | | horse_imagelinks| | horse_interwiki | | horse_ipblocks | | horse_job | | horse_logging | | horse_math | | horse_objectcache | | horse_oldimage | | horse_page | | horse_pagelinks | | horse_querycache| | horse_recentchanges | | horse_revision | | horse_searchindex | | horse_site_stats| | horse_templatelinks | | horse_text | | horse_trackbacks| | horse_transcache| | horse_user | | horse_user_groups | | horse_user_newtalk | | horse_validate | | horse_watchlist | | image | | imagelinks | | interwiki | | ipblocks| | job | | logging | | math| | objectcache | | oldimage| | page| | pagelinks | | querycache | | recentchanges | | revision| | searchindex | | site_stats | | templatelinks | | text| | trackbacks | | transcache | | user| | user_groups | | user_newtalk| | validate| | watchlist | +-+ 58 rows in set (0.00 sec) mysql SELECT * FROM archive LIMIT 1; ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist mysql drop table archive; ERROR 1051 (42S02): Unknown table 'archive' Dan Buettner wrote: Michael, is the 'archive' table present in your database? e.g., if you do a 'LIST TABLES', does it show up? What happens if you do a SELECT * FROM archive LIMIT 1 ? I'd hazard a guess that you may have a table definition file with no actual table data files, if you're on MyISAM tables. If you don't need the archive table, can you DROP it successfully? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I am having a devil of a time moving a database from one server to another. My database is one that is in production to support the mediawiki wiki application. This is a Solaris Sparc 10 system using the mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki. I execute this: # mysqldump --user root --password=password horsewiki horsewiki.sql and get this: mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when using LOCK TABLES I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the database to an SQL file. When I attempt to import it on the other system, I get this: Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 494 STR: / SQL: -- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM -- Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon /
Prefix Indices
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: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to rewrite query
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 Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query 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 WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- 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: How to rewrite query
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 index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: 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 Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query 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 WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- 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]
Hungarian collation
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 standard, if there is one. MySQL has received several complaints/suggestions about Hungarian collation. For example these three people contacted us via a public MySQL mailing list or bugs forum: RITZINGER Peter (http://bugs.mysql.com/bug.php?id=12519) BÁRTHÁZI András (http://lists.mysql.com/mysql/191427) Csongor Fagyal (http://bugs.mysql.com/bug.php?id=22337) In what follows I will refer to what seems to be agreed, and what seems to be disputed. The current latin2_hungarian_ci collation is a chart in sql/share/charsets/latin2.xml, and Mr Barkov has provided an easy-to-read web page: http://myoffice.izhnet.ru/bar/~bar/charts/latin2_hungarian_ci.html This collation is unlike the Hungarian dictionaries, collation descriptions, or products that we've seen. For example the first letter is: Latin Capital Letter A = Latin Small Letter A = control Single Shift 3 = No-Break Space = Latin Small Letter L with caron = Latin Capital Letter A with acute = Latin Small Letter A with acute But there is no reason that small L with caron (which is Slovak not Hungarian) ever sorts with A, there's some dispute whether A with acute should sort with A, and all other accented variants of A should be in this list too. It is likely that MySQL will deprecate this collation (which implies that MySQL will eventually remove it), after introducing a new and more correct one. Most people agree that this is the Hungarian alphabet; a á b c cs d dz dzs e é f g gy h i í j k l ly m n ny o ó ö ő p q r s sz t ty u ú ü ű v w x y z zs (The DOUBLE ACUTE letters ő and ű are sometimes shown as õ and û but I suspect that is a conversion error.) Some people also say there's a secondary sort rule for these short/long vowel pairs: a á, e é, i í, o ó, ö ő, u ú, ü ű For these pairs, long = short usually, but long short if all else is equal. I have seen comments showing that Oracle seems to follow this rule: 'BÁ''BA' is true 'BÁ''BAC' is false but the commenter, though Hungarian, didn't like what Oracle did. (thread 'nlssort' on comp.databases.oracle.server 2002-11-10) One commenter wrote to us about a similar thing, saying it's a mistake that SELECT 'hal' LIKE 'hál' is true. Unfortunately, the same person also disagrees, saying that we should have two collations, one where long short, one where long = short. I have also seen Simonsen's rules: http://std.dkuug.dk/i18n/locales/hu_HU They suggest that A-acute A, etc. I have also seen argument about the same thing for glibc: http://sources.redhat.com/ml/libc-locales/2005-q4/msg2.html Apparently all Hungarians agree that these digraphs are letters: cs dz dzs gy ly ny sz ty zs That's bad but not very bad. MySQL handles digraphs in Spanish. There is also one trigraph: dzs That's very bad. Luckily dzs is rare, it's mostly for English words with a j sound (bridge is 'briddz', gin is 'dzsinn') (so I'm told). There is a special rule when you see the first part of a digraph followed by the digraph. For example, in 'ggy', 'g' is the first part of 'gy' and it's followed by 'gy' ... and MySQL treats it as a repetition of the digraph, i.e. as if it's 'gygy'. This applies to all the letters listed in the previous paragraph, so: ccs = cscs, ddz = dzdz, ddsz = dzsdzs, ggy = gygy, lly = lyly, nny = nyny, ssz = szsz, tty = tyty, zzs = zszs. For example, Mr Ritzinger says that 'tty tz' because tty is expanded to tyty. I know that other products handle the situation, but I've seen them called double compressions, which worries me -- do some people think that 'cscs sorts with ccs' rather than 'ccs sorts with cscs'? A collation which follows the single-character rules, but ignores digraphs and trigraphs, sounds somewhat like what I see in Kaplan's remarks on Microsoft's Hungarian Technical Sort: http://blogs.msdn.com/michkap/archive/2005/11/26/495072.aspx One of the above-listed people would accept this, he says he doesn't care about digraphs or trigraphs. But I have no idea whether Microsoft was following some technical standard. All characters outside the Hungarian alphabet should be done according to UCA 4.0.0 (until MySQL switches to the newer UCA). For Unicode support, I suggest names for the new collations should be: ucs2_hungarian2_ci, utf8_hungarian2_ci. The only other character sets that may have Hungarian collations are latin2 and cp1250. Our concern at this time is only for the primary sort, the collation necessary for searches. The secondary sort or tertiary sort rules, the ones that affect only ORDER BY, are of interest but will only be of importance in the future. -- Peter Gulutzan, Senior Software Architect MySQL AB, www.mysql.com Office: +1 780 472-6838 Mobile: +1 780 904-0297 VoIP: +1 408
RE: References on Optimizing File Sort
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 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:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: How to rewrite query
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 Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query 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 index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: 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 Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query 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 WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- 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: How to rewrite query
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 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 Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query 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 index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: 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 Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query 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 WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- 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: RE: How to rewrite query
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 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 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 Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query 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 index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: 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 Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query 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 WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: How to rewrite query
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: mysql@lists.mysql.com Subject: RE: RE: How to rewrite query 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 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 Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query 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 index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: 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 Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query 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 WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rewrite query
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 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 no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas -- Martin Sköld, Ph.D Software Engineer MySQL AB, www.mysql.com Office: +46 (0)730 31 26 21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does new Community version change C API licensing?
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 buy OEM licenses is only related to *distributing* applications, not using MySQL in-house. This is how it was before, and this is how it is today. We do not *require* in-house users of MySQL to subscribe to MySQL Enterprise. However, we definitely want to make MySQL Enterprise *attractive* for business users who have MySQL in production use, by providing them added value on top of what MySQL Community has. So: For client licensing, nothing as changed -- but stay tuned for an announcement next week! Kaj Warren wrote: 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 this new model is any different than before, except that the commercial version is now more expensive. The practical upshot of the previous licensing scheme, for people that used the MySQL C API, was that you either had to buy a MySQL commercial license, or release your code under the GPL. That means MySQL could never be free-of-charge (quoting today's email from Kaj Arnö) for those people. For that to happen, the C API must be released under a more liberal license (LGPL at least), or explicitly made public-domain. So, has anything substantial changed besides the price of a commercial license? -- Kaj Arnö [EMAIL PROTECTED] MySQL AB, VP Community Relations -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql query
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 ... I hope I explain well In this case this is record 1 and record 3. How can I do this ? Thanks in advance for your help. Peter 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 PROTECTED]
Re: sql query
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 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 ... I hope I explain well In this case this is record 1 and record 3. How can I do this ? Thanks in advance for your help. Peter 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 PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query
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 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 ... I hope I explain well In this case this is record 1 and record 3. How can I do this ? Thanks in advance for your help. Peter Send instant messages to your online friends http://uk.messenger.yahoo.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 10/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query
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 Sent: Tuesday, October 17, 2006 2:55:37 PM GMT-0500 US/Eastern Subject: Re: sql query 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 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 ... I hope I explain well In this case this is record 1 and record 3. How can I do this ? Thanks in advance for your help. Peter 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 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]
Query question
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 | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL | auto_increment | | imageId | int(10) unsigned | NO | | || | locLevel5Id | int(10) unsigned | NO | | || +---+--+--+-+-++ As you may see, this table gathers data from two separate tables (image and and locLevel5). The locLevel5 table contains data from a US official list of places which--unfortunately--mixes location names of counties, cities, and certain geographical places (such as lakes, national parks, mountains, etc.). This creates a many-to-many relationship between imageId and locLevel5Id, as the following queries show: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ mysql select * from locBridgeImageLocLevel5 where imageId = 2; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 3 | 2 |2356 | | 4 | 2 | 18302 | +---+-+-+ So far I have been successful in obtaining data from the tables asking for two discrete criteria using the AND logical operator: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id = 18302); +-+ | imageId | +-+ | 1 | | 2 | +-+ The problem comes when try to find a record in which a condition is positive, and the other negative. Using NOT gave me a syntax error: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 NOT loc2.locLevel5Id = 18302); ERROR 1064 (42000): 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 'loc2.locLevel5Id = 18302)' at line 5 And using in combination with AND, gave me a result that is not correct. mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 18302); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | ... | 259 | | 260 | | 261 | +-+ I should rephrase my last sentence, I am sure the result *IS* correct, but it is not what I am looking for. I am looking for those images that are associated with locLevel5Id 2356, but NOT associated with locLevel5Id 13128. As you can see from the query result above, imageId 1 is selected, however there is a record (locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id = 13128. As I mentioned before, your help will be greatly appreciated. If you need further details, please let me know. Erick Teaching Learning Technologies Center Ballantine Hall 307 http://www.indiana.edu/~tltc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
References on Optimizing File Sort
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:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
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 [EMAIL PROTECTED] wrote: 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 | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL | auto_increment | | imageId | int(10) unsigned | NO | | || | locLevel5Id | int(10) unsigned | NO | | || +---+--+--+-+-++ As you may see, this table gathers data from two separate tables (image and and locLevel5). The locLevel5 table contains data from a US official list of places which--unfortunately--mixes location names of counties, cities, and certain geographical places (such as lakes, national parks, mountains, etc.). This creates a many-to-many relationship between imageId and locLevel5Id, as the following queries show: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ mysql select * from locBridgeImageLocLevel5 where imageId = 2; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 3 | 2 |2356 | | 4 | 2 | 18302 | +---+-+-+ So far I have been successful in obtaining data from the tables asking for two discrete criteria using the AND logical operator: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id = 18302); +-+ | imageId | +-+ | 1 | | 2 | +-+ The problem comes when try to find a record in which a condition is positive, and the other negative. Using NOT gave me a syntax error: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 NOT loc2.locLevel5Id = 18302); ERROR 1064 (42000): 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 'loc2.locLevel5Id = 18302)' at line 5 And using in combination with AND, gave me a result that is not correct. mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 18302); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | ... | 259 | | 260 | | 261 | +-+ I should rephrase my last sentence, I am sure the result *IS* correct, but it is not what I am looking for. I am looking for those images that are associated with locLevel5Id 2356, but NOT associated with locLevel5Id 13128. As you can see from the query result above, imageId 1 is selected, however there is a record (locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id = 13128. As I mentioned before, your help will be greatly appreciated. If you need further details, please let me know. Erick Teaching Learning Technologies Center Ballantine Hall 307 http://www.indiana.edu/~tltc/ -- 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: sql query
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 PROTECTED]
Re: References on Optimizing File Sort
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 enough operations happened simultaneously. An excellent boost from a hardware perspective would be to either 1) set up your temp dirs on a RAM disk, though that can be problematic for replication 2) set up your temp dirs on a dedicated set of fast, striped disks See http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html and http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html On 10/17/06, Robert DiFalco [EMAIL PROTECTED] 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. TIA, R. -- 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 question
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) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 13128); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | ... | 257 | | 258 | | 259 | | 260 | | 261 | +-+ 251 rows in set (0.04 sec) It stills returns imageId 1, eventhough there is a record (locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id = 13128: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ Erick At 2:47 PM -0500 10/17/06, Dan Buettner wrote: 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 [EMAIL PROTECTED] wrote: 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 | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL | auto_increment | | imageId | int(10) unsigned | NO | | || | locLevel5Id | int(10) unsigned | NO | | || +---+--+--+-+-++ As you may see, this table gathers data from two separate tables (image and and locLevel5). The locLevel5 table contains data from a US official list of places which--unfortunately--mixes location names of counties, cities, and certain geographical places (such as lakes, national parks, mountains, etc.). This creates a many-to-many relationship between imageId and locLevel5Id, as the following queries show: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ mysql select * from locBridgeImageLocLevel5 where imageId = 2; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 3 | 2 |2356 | | 4 | 2 | 18302 | +---+-+-+ So far I have been successful in obtaining data from the tables asking for two discrete criteria using the AND logical operator: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id = 18302); +-+ | imageId | +-+ | 1 | | 2 | +-+ The problem comes when try to find a record in which a condition is positive, and the other negative. Using NOT gave me a syntax error: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 NOT loc2.locLevel5Id = 18302); ERROR 1064 (42000): 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 'loc2.locLevel5Id = 18302)' at line 5 And using in combination with AND, gave me a result that is not correct. mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 18302); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 |
Re: References on Optimizing File Sort
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 either use a covering index, or take advantage of InnoDB's clustered organization (which has an implicit sort of data records on the primary key). Can you show us an example of the query in question, using an EXPLAIN? Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Query question
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. Sounds like what you want is to exclude all the image ids for locLevel5Id = 13128 ? Rewrite like so, assuming you have subqueries: SELECT distinct loc1.imageId FROM locBridgeImageLocLevel5 as loc1 INNER JOIN locBridgeImageLocLevel5 as loc2 USING (imageId) WHERE loc1.locLevel5Id = 2356 AND loc2.imageid NOT IN (SELECT imageid from locBridgeImageLocLevel5 WHERE locLevel5Id = 13128); I think I'm understanding your goal!! Dan On 10/17/06, Erick Carballo [EMAIL PROTECTED] wrote: 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) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 13128); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | ... | 257 | | 258 | | 259 | | 260 | | 261 | +-+ 251 rows in set (0.04 sec) It stills returns imageId 1, eventhough there is a record (locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id = 13128: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ Erick At 2:47 PM -0500 10/17/06, Dan Buettner wrote: 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 [EMAIL PROTECTED] wrote: 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 | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL | auto_increment | | imageId | int(10) unsigned | NO | | || | locLevel5Id | int(10) unsigned | NO | | || +---+--+--+-+-++ As you may see, this table gathers data from two separate tables (image and and locLevel5). The locLevel5 table contains data from a US official list of places which--unfortunately--mixes location names of counties, cities, and certain geographical places (such as lakes, national parks, mountains, etc.). This creates a many-to-many relationship between imageId and locLevel5Id, as the following queries show: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ mysql select * from locBridgeImageLocLevel5 where imageId = 2; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 3 | 2 |2356 | | 4 | 2 | 18302 | +---+-+-+ So far I have been successful in obtaining data from the tables asking for two discrete criteria using the AND logical operator: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id = 18302); +-+ | imageId | +-+ | 1 | | 2 | +-+ The problem comes when try to find a record in which a condition is positive, and the other negative. Using NOT gave me a syntax error: mysql SELECT distinct loc1.imageId
Binary Log Files and Load Data In-File
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) I took a look at the binary logs using mysqlbinlog and it returns everyhing as just load data infile statements. There isn't any actual data inside of it. So, my question is, Do I really need these binary logs since I read from the Book/site that binary logs are useful for rolling back transactions / master-slave replication etc. Since these logs does not contain any actual data and I don't have a slave DB. BTW, I've already set the logs to expire every 5 days for the time being. Just wanted to check with the people here for information and advise. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter Table Add Column - How Long to update?
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 see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Btw, where is this temporary table? I don't see it in the DB. and I don't see it in the default /tmp directory. I'm using InnoDB by the way. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'Not a valid MySQL result resource' error
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 you back to login saying invalid:
snip
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_query(): supplied
argument is not a valid MySQL-Link resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 16
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_fetch_array():
supplied argument is not a valid MySQL result resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 17
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_num_rows():
supplied argument is not a valid MySQL result resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 20
end snip
code in question:
script language=javascript
!--
function focus(urlstring) {
window.open(urlstring,adFOCUS,width=700,height=580,scrollbars=yes,toolbar=no,location=no,resizable=no);
}
//--
/script
div id=mainnav
table width=180 border=0 cellpadding=0 cellspacing=0
trtd align='left'img src='_elements/spacer-blank.gif' height='1'
width='180' border='0'/td/tr
trtd align='center'font class='section'uSpecial
Events/u/fontbrbr/td/tr
trtd align=center
? $x = SELECT page_contents.title,page_contents.id FROM
page_contents,page_sections WHERE page_contents.pagename =
page_sections.pagename AND page_contents.display != 'N' AND
page_sections.publicurl = '/specialevents.php' AND begdate = Now() AND
enddate Now() ORDER BY rand() LIMIT 6;
$r = mysql_query($x,$db);
while ($re = mysql_fetch_array($r)) {
print a class=\sideoff\
href=\javascript:focus('http://www.website.com/viewevents.php?rid=$re[id]')\$re[title]/abrbr\n;
}
$rcount = mysql_num_rows($r);
if (($rcount == 6)) {
print a class=\sideoff\
href='http://www.website.com/specialevents.php'continued.../abrbr\n;
}
?
/td
/tr/table
/div
Any and all help is much appreciated, thanks.
--
Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update?
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 add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Btw, where is this temporary table? I don't see it in the DB. and I don't see it in the default /tmp directory. This answers some of the questions. http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html I'm using InnoDB by the way. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
