Re: Is bad hardware confusing MySQL and InnoDB?
Hi Maurice, You say the MySQL data wasn't on the stuck volume, but were the InnoDB logs? What is the disk configuration? It sounds to me like bad hardware/software, which, unfortunately MySQL and InnoDB cannot protect you from... Regards, Jeremy Maurice Volaski wrote: Some processes on a server (64-bit Gentoo Linux with MySQL 5.0.44), which seemed to be related to I/O on LVM volumes hung and it was necessary to force reboot it. The mysql data was not on an LVM volume though it still may have been affected since over time, more and more processes became unresponsive. While fsck recovered the journal and detected no problems on any volume, at least one database was not spared: 070911 23:40:34 InnoDB: Page checksum 3958948568, prior-to-4.0.14-form checksum 2746081740 InnoDB: stored checksum 2722580120, prior-to-4.0.14-form stored checksum 2746081740 InnoDB: Page lsn 0 491535, low 4 bytes of lsn at page end 491535 InnoDB: Page number (if stored to page already) 199, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 17 InnoDB: Also the page in the doublewrite buffer is corrupt. InnoDB: Cannot continue operation. Is it wrong to expect InnoDB to have avoided this or does it suggest that it couldn't have, i.e., a hardware defect? -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is bad hardware confusing MySQL and InnoDB?
Some processes on a server (64-bit Gentoo Linux with MySQL 5.0.44), which seemed to be related to I/O on LVM volumes hung and it was necessary to force reboot it. The mysql data was not on an LVM volume though it still may have been affected since over time, more and more processes became unresponsive. While fsck recovered the journal and detected no problems on any volume, at least one database was not spared: 070911 23:40:34 InnoDB: Page checksum 3958948568, prior-to-4.0.14-form checksum 2746081740 InnoDB: stored checksum 2722580120, prior-to-4.0.14-form stored checksum 2746081740 InnoDB: Page lsn 0 491535, low 4 bytes of lsn at page end 491535 InnoDB: Page number (if stored to page already) 199, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 17 InnoDB: Also the page in the doublewrite buffer is corrupt. InnoDB: Cannot continue operation. Is it wrong to expect InnoDB to have avoided this or does it suggest that it couldn't have, i.e., a hardware defect? -- Maurice Volaski, [EMAIL PROTECTED] Computing Support, Rose F. Kennedy Center Albert Einstein College of Medicine of Yeshiva University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tangent: confusing iostat readings (was Re: why are tmp tables being created on disk?)
Mathieu Bruneau [EMAIL PROTECTED] wrote: BTW, here's another oddity I noticed - here's typical output from iostat 60: | avg-cpu: %user %nice%sys %iowait %idle |7.350.003.590.94 88.12 | | Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn | sda 29.20 8.00 342.08480 20528 | sda1 0.00 0.00 0.00 0 0 | sda2 0.00 0.00 0.00 0 0 | sda3 0.00 0.00 0.00 0 0 | sda4 0.00 0.00 0.00 0 0 | sda5 43.74 8.00 342.08480 20528 | sdb 2.43 0.00 173.70 0 10424 | sdb1 21.71 0.00 173.70 0 10424 I've been running this for a few hours and it consistently shows lots of writes but no reads at all on sdb1, the partition where I have my binary logs and tmpdir. Is MySQL writing lots of tmp files and not reading them? Or, how else can I interpret this? The binlog are creating most of your constant write most probably. If you have no slave attached, you're not reading them at all... Yes and no. In fact, this iostat output comes from a slave, where there's hardly any binglog activity (but a fair amount of relay log activity). However, I noticed the same thing on the master. Before tmpdir pointed to a directory on sdb1, there was a much lower, and fairly constant, level of writes to that partition, which did not surprise me. After I pointed tmpdir to sdb1, the amount of write activity grew substantially and started varying much more (from as low as ~50 to as high as ~400), but I still see no reading recorded. -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Confusing backslash issues with LIKE
Hello, I'm having some trouble with the LIKE operator on MySQL 5.0. Here's my transcript: mysql select 'abc\\def'; +-+ | abc\def | +-+ | abc\def | +-+ 1 row in set (0.00 sec) mysql select 'abc\\def' like '%\\%'; ++ | 'abc\\def' like '%\\%' | ++ | 0 | ++ 1 row in set (0.00 sec) mysql select 'abc\\def' like '%%'; +--+ | 'abc\\def' like '%%' | +--+ |1 | +--+ 1 row in set (0.00 sec) The last two show my problem. When I search for a backslash, I need to escape it *twice*. Why that? I can't see that from the manual [1]. [1] http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confusing backslash issues with LIKE
On 23.05.2007 14:49 CE(S)T, Yves Goergen wrote: [1] http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like Never mind. This very page says why it is like it is. It's definitely too hot in here today. :( -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: // confusing syntax error when importing //
How did you try to do it on the remote system?
I copied and pasted your query to a server running MySQL
4.1.12-standard-log, and another running MySQL 5.0.19-standard-log,
and they both created the table just fine.
Have you tried copying and pasting the table creation itself to see if
that works?
Or using the commandline to do it on the remote machine --
mysql -u root -p -h host db file.sql
-Sheeri
On 4/3/06, julian oliver [EMAIL PROTECTED] wrote:
--apologies if you receive this email from another account--
hi,
i exported a *.sql using phpMyAdmin on a system running:
mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i486) using
readline 5.1
i'm trying to import it from this localhost to a remote host
running:
mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i486) using
readline 5.1
the 13MB file fails during import with this error:
//-
Error
SQL query:
-- phpMyAdmin SQL Dump
-- version 2.8.0.2-Debian-3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 03, 2006 at 07:09 PM
-- Server version: 5.0.19
-- PHP Version: 4.4.2-1+b1
--
-- Database: `nuke`
--
--
--
-- Table structure for table `cur`
--
CREATE TABLE `cur` (
`cur_id` int( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
`cur_namespace` tinyint( 2 ) unsigned NOT NULL default '0',
`cur_title` varchar( 255 ) CHARACTER SET latin1 COLLATE latin1_bin NOT
NULL default '',
`cur_text` mediumtext NOT NULL ,
`cur_comment` tinyblob NOT NULL ,
`cur_user` int( 5 ) unsigned NOT NULL default '0',
`cur_user_text` varchar( 255 ) CHARACTER SET latin1 COLLATE latin1_bin
NOT NULL default '',
`cur_timestamp` varchar( 14 ) CHARACTER SET latin1 COLLATE latin1_bin
NOT NULL default '',
`cur_restrictions` tinyblob NOT NULL ,
`cur_counter` bigint( 20 ) unsigned NOT NULL default '0',
`cur_is_redirect` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_minor_edit` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_is_new` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_random` double unsigned NOT NULL default '0',
`cur_touched` varchar( 14 ) CHARACTER SET latin1 COLLATE latin1_bin NOT
NULL default '',
`inverse_timestamp` varchar( 14 ) CHARACTER SET latin1 COLLATE
latin1_bin NOT NULL default '',
UNIQUE KEY `cur_id` ( `cur_id` ) ,
KEY `cur_namespace` ( `cur_namespace` ) ,
KEY `cur_title` ( `cur_title` ( 20 ) ) ,
KEY `cur_timestamp` ( `cur_timestamp` ) ,
KEY `cur_random` ( `cur_random` ) ,
KEY `name_title_timestamp` ( `cur_namespace` , `cur_title` ,
`inverse_timestamp` ) ,
KEY `user_timestamp` ( `cur_user` , `inverse_timestamp` ) ,
KEY `usertext_timestamp` ( `cur_user_text` , `inverse_timestamp` ) ,
KEY `namespace_redirect_timestamp` ( `cur_namespace` , `cur_is_redirect`
, `cur_timestamp` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 PACK_KEYS =1 AUTO_INCREMENT
=1028;
MySQL said: Documentation
#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'collate
+latin1_bin NOT NULL default '',
`cur_text` mediumtext
//-
as i know very little about mysql generally, i'm having a hard time
deriving what the right syntax might be here.
out of interest the same *.sql imports on the local system just fine
using mysql on the commandline ('mysql -u user -p database
file.sql'),
but on the remote machine fails with the same error as seen above.
if someone could point me in the right direction i'd be very grateful.
cheers,
julian
--
julian oliver
[EMAIL PROTECTED]
--
http://www.fastmail.fm - Does exactly what it says on the tin
--
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]
// confusing syntax error when importing //
--apologies if you receive this email from another account--
hi,
i exported a *.sql using phpMyAdmin on a system running:
mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i486) using
readline 5.1
i'm trying to import it from this localhost to a remote host
running:
mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i486) using
readline 5.1
the 13MB file fails during import with this error:
//-
Error
SQL query:
-- phpMyAdmin SQL Dump
-- version 2.8.0.2-Debian-3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 03, 2006 at 07:09 PM
-- Server version: 5.0.19
-- PHP Version: 4.4.2-1+b1
--
-- Database: `nuke`
--
--
--
-- Table structure for table `cur`
--
CREATE TABLE `cur` (
`cur_id` int( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
`cur_namespace` tinyint( 2 ) unsigned NOT NULL default '0',
`cur_title` varchar( 255 ) CHARACTER SET latin1 COLLATE latin1_bin NOT
NULL default '',
`cur_text` mediumtext NOT NULL ,
`cur_comment` tinyblob NOT NULL ,
`cur_user` int( 5 ) unsigned NOT NULL default '0',
`cur_user_text` varchar( 255 ) CHARACTER SET latin1 COLLATE latin1_bin
NOT NULL default '',
`cur_timestamp` varchar( 14 ) CHARACTER SET latin1 COLLATE latin1_bin
NOT NULL default '',
`cur_restrictions` tinyblob NOT NULL ,
`cur_counter` bigint( 20 ) unsigned NOT NULL default '0',
`cur_is_redirect` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_minor_edit` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_is_new` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_random` double unsigned NOT NULL default '0',
`cur_touched` varchar( 14 ) CHARACTER SET latin1 COLLATE latin1_bin NOT
NULL default '',
`inverse_timestamp` varchar( 14 ) CHARACTER SET latin1 COLLATE
latin1_bin NOT NULL default '',
UNIQUE KEY `cur_id` ( `cur_id` ) ,
KEY `cur_namespace` ( `cur_namespace` ) ,
KEY `cur_title` ( `cur_title` ( 20 ) ) ,
KEY `cur_timestamp` ( `cur_timestamp` ) ,
KEY `cur_random` ( `cur_random` ) ,
KEY `name_title_timestamp` ( `cur_namespace` , `cur_title` ,
`inverse_timestamp` ) ,
KEY `user_timestamp` ( `cur_user` , `inverse_timestamp` ) ,
KEY `usertext_timestamp` ( `cur_user_text` , `inverse_timestamp` ) ,
KEY `namespace_redirect_timestamp` ( `cur_namespace` , `cur_is_redirect`
, `cur_timestamp` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 PACK_KEYS =1 AUTO_INCREMENT
=1028;
MySQL said: Documentation
#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'collate
+latin1_bin NOT NULL default '',
`cur_text` mediumtext
//-
as i know very little about mysql generally, i'm having a hard time
deriving what the right syntax might be here.
out of interest the same *.sql imports on the local system just fine
using mysql on the commandline ('mysql -u user -p database
file.sql'),
but on the remote machine fails with the same error as seen above.
if someone could point me in the right direction i'd be very grateful.
cheers,
julian
--
julian oliver
[EMAIL PROTECTED]
--
http://www.fastmail.fm - Does exactly what it says on the tin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a confusing error: 111
唯一的账户root没有密码，服务可以启动 ps -e | grep mysqld 有进程mysqld_safe,mysqld n 个 我想改/加密码，mysqladmin -uroot 返回错误，连不上服务器 to Xiaodong: 你说得原来的协议指的是? thank u all. On 9/7/05, 王静 [EMAIL PROTECTED] wrote: /usr/local/mysql-- On 9/7/05, Gu Lei [EMAIL PROTECTED] wrote: 王静 写道: hi all. i've installed MySQL database server 4.1.14 under linux platform After setting up the server successfully, i envoked mysqladmin, but what i got is an error: can't connect to local MySQL server through socket '/tmp/mysql.sock'(111) is there anyone who ever run into similar problem and how to solve it? thank u. 你的mysql装在哪个目录下了？
Re: a confusing error: 111
On 9/7/05, Ben [EMAIL PROTECTED] wrote: What linux where you using? RedHat linux 2.4.21 A blank password is not a good idea, I'm not sure if it is allowed anyway. yeah, i know. so what i'm trying to do is adding/changing password for the only account root with the tool mysqladmin. but the error occured as i've posted before I had the same prob with SuSE 9.3 and I updated MySQL and the problem left, But also you get the error when the account is not set properly. Can you log into # mysql -u root test ? yes, i can log into #mysql and get the prompt mysql Thank u. ---Original Message--- From: 王静 Date: 09/07/05 17:52:52 To: mysql@lists.mysql.com Subject: Re: a confusing error: 111 i recall i've managed to set up a connection to MySQL server using account root with the password item blank in Kdevelop. when i invoke mysqlaccess -uroot -p, it returned a prompt for password, i just hit return, but failed this time. mysqladmin -uroot got the same suituation with the error 111. i'm sure the default port 3306 isn't blocked by firewall, since i can access the server with the tool mysql. any port else invovled? thank u. On 9/7/05, Ben [EMAIL PROTECTED] wrote: yes, You dont have an open account with root permissions mysqladmin -u root passwd password replace password with a real password also check to make sure your firewall isnt blocking your query if on a seperate network! hope this helps ---Original Message--- From: 王静 Date: 09/07/05 17:04:31 To: mysql@lists.mysql.com Subject: a confusing error: 111 hi all. i've installed MySQL database server 4.1.14 under linux platform After setting up the server successfully, i envoked mysqladmin, but what i got is an error: can't connect to local MySQL server through socket '/tmp/mysql.sock'(111) is there anyone who ever run into similar problem and how to solve it? thank u. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] nz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] nz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a confusing error: 111
yeah, i can access the database server normally with the client mysql. it works all right. the point is mysqladmin doesn't work. Thank u. On 9/7/05, 王静 [EMAIL PROTECTED] wrote: On 9/7/05, Ben [EMAIL PROTECTED] wrote: What linux where you using? RedHat linux 2.4.21 A blank password is not a good idea, I'm not sure if it is allowed anyway. yeah, i know. so what i'm trying to do is adding/changing password for the only account root with the tool mysqladmin. but the error occured as i've posted before I had the same prob with SuSE 9.3 and I updated MySQL and the problem left, But also you get the error when the account is not set properly. Can you log into # mysql -u root test ? yes, i can log into #mysql and get the prompt mysql Thank u. ---Original Message--- From: 王静 Date: 09/07/05 17:52:52 To: mysql@lists.mysql.com Subject: Re: a confusing error: 111 i recall i've managed to set up a connection to MySQL server using account root with the password item blank in Kdevelop. when i invoke mysqlaccess -uroot -p, it returned a prompt for password, i just hit return, but failed this time. mysqladmin -uroot got the same suituation with the error 111. i'm sure the default port 3306 isn't blocked by firewall, since i can access the server with the tool mysql. any port else invovled? thank u. On 9/7/05, Ben [EMAIL PROTECTED] wrote: yes, You dont have an open account with root permissions mysqladmin -u root passwd password replace password with a real password also check to make sure your firewall isnt blocking your query if on a seperate network! hope this helps ---Original Message--- From: 王静 Date: 09/07/05 17:04:31 To: mysql@lists.mysql.com Subject: a confusing error: 111 hi all. i've installed MySQL database server 4.1.14 under linux platform After setting up the server successfully, i envoked mysqladmin, but what i got is an error: can't connect to local MySQL server through socket '/tmp/mysql.sock'(111) is there anyone who ever run into similar problem and how to solve it? thank u. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] nz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] nz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a confusing error: 111
Try redoing from scratch but this time dont use a client just use command line! yeah, i can access the database server normally with the client mysql. it works all right. the point is mysqladmin doesn't work. Thank u. On 9/7/05, 王静 [EMAIL PROTECTED] wrote: On 9/7/05, Ben [EMAIL PROTECTED] wrote: What linux where you using? RedHat linux 2.4.21 A blank password is not a good idea, I'm not sure if it is allowed anyway. yeah, i know. so what i'm trying to do is adding/changing password for the only account root with the tool mysqladmin. but the error occured as i've posted before I had the same prob with SuSE 9.3 and I updated MySQL and the problem left, But also you get the error when the account is not set properly. Can you log into # mysql -u root test ? yes, i can log into #mysql and get the prompt mysql Thank u. ---Original Message--- From: 王静 Date: 09/07/05 17:52:52 To: mysql@lists.mysql.com Subject: Re: a confusing error: 111 i recall i've managed to set up a connection to MySQL server using account root with the password item blank in Kdevelop. when i invoke mysqlaccess -uroot -p, it returned a prompt for password, i just hit return, but failed this time. mysqladmin -uroot got the same suituation with the error 111. i'm sure the default port 3306 isn't blocked by firewall, since i can access the server with the tool mysql. any port else invovled? thank u. On 9/7/05, Ben [EMAIL PROTECTED] wrote: yes, You dont have an open account with root permissions mysqladmin -u root passwd password replace password with a real password also check to make sure your firewall isnt blocking your query if on a seperate network! hope this helps ---Original Message--- From: 王静 Date: 09/07/05 17:04:31 To: mysql@lists.mysql.com Subject: a confusing error: 111 hi all. i've installed MySQL database server 4.1.14 under linux platform After setting up the server successfully, i envoked mysqladmin, but what i got is an error: can't connect to local MySQL server through socket '/tmp/mysql.sock'(111) is there anyone who ever run into similar problem and how to solve it? thank u. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] nz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] nz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a confusing error: 111
hi all. i've installed MySQL database server 4.1.14 under linux platform After setting up the server successfully, i envoked mysqladmin, but what i got is an error: can't connect to local MySQL server through socket '/tmp/mysql.sock'(111) is there anyone who ever run into similar problem and how to solve it? thank u. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a confusing error: 111
i recall i've managed to set up a connection to MySQL server using account root with the password item blank in Kdevelop. when i invoke mysqlaccess -uroot -p, it returned a prompt for password, i just hit return, but failed this time. mysqladmin -uroot got the same suituation with the error 111. i'm sure the default port 3306 isn't blocked by firewall, since i can access the server with the tool mysql. any port else invovled? thank u. On 9/7/05, Ben [EMAIL PROTECTED] wrote: yes, You dont have an open account with root permissions mysqladmin -u root passwd password replace password with a real password also check to make sure your firewall isnt blocking your query if on a seperate network! hope this helps ---Original Message--- From: 王静 Date: 09/07/05 17:04:31 To: mysql@lists.mysql.com Subject: a confusing error: 111 hi all. i've installed MySQL database server 4.1.14 under linux platform After setting up the server successfully, i envoked mysqladmin, but what i got is an error: can't connect to local MySQL server through socket '/tmp/mysql.sock'(111) is there anyone who ever run into similar problem and how to solve it? thank u. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] nz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a confusing error: 111
/usr/local/mysql-- On 9/7/05, Gu Lei [EMAIL PROTECTED] wrote: 王静 写道: hi all. i've installed MySQL database server 4.1.14 under linux platform After setting up the server successfully, i envoked mysqladmin, but what i got is an error: can't connect to local MySQL server through socket '/tmp/mysql.sock'(111) is there anyone who ever run into similar problem and how to solve it? thank u. 你的mysql装在哪个目录下了？
This is confusing..?
Given the following table: CREATE TABLE campaign_t ( acct_id int(11) unsigned NOT NULL default '0', site_id tinyint(3) unsigned NOT NULL default '0', ref_id int(11) unsigned NOT NULL default '0', datestamp char(10) NOT NULL default '', raws int(11) unsigned NOT NULL default '0', uniques int(11) unsigned NOT NULL default '0', trial_signups int(11) NOT NULL default '0', full_signups int(11) NOT NULL default '0', annual_signups int(11) unsigned NOT NULL default '0', PRIMARY KEY (acct_id,site_id,ref_id,datestamp), KEY acct_id (acct_id), KEY site_id (site_id), KEY ref_id (ref_id), KEY datestamp (datestamp) ) TYPE=MyISAM; How come it doesn't use the 'datestamp' index on this query: mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | where used | ++--+---+--+-+--+++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: This is confusing..?
I seem to remember someone saying that if a query would end up returning more than x% of a table (I think it was either 30% or 50%), then mysql just ends up doing a full scan regardless... -Original Message- From: Eric Anderson [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 2:08 PM To: [EMAIL PROTECTED] Subject: This is confusing..? Given the following table: CREATE TABLE campaign_t ( acct_id int(11) unsigned NOT NULL default '0', site_id tinyint(3) unsigned NOT NULL default '0', ref_id int(11) unsigned NOT NULL default '0', datestamp char(10) NOT NULL default '', raws int(11) unsigned NOT NULL default '0', uniques int(11) unsigned NOT NULL default '0', trial_signups int(11) NOT NULL default '0', full_signups int(11) NOT NULL default '0', annual_signups int(11) unsigned NOT NULL default '0', PRIMARY KEY (acct_id,site_id,ref_id,datestamp), KEY acct_id (acct_id), KEY site_id (site_id), KEY ref_id (ref_id), KEY datestamp (datestamp) ) TYPE=MyISAM; How come it doesn't use the 'datestamp' index on this query: mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g ++--+---+--+-+--+- ---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+- ---++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | where used | ++--+---+--+-+--+- ---++ 1 row in set (0.00 sec) -- 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: This is confusing..?
Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Eric Anderson [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 11:08 AM --To: [EMAIL PROTECTED] --Subject: This is confusing..? -- -- --Given the following table: -- --CREATE TABLE campaign_t ( -- acct_id int(11) unsigned NOT NULL default '0', -- site_id tinyint(3) unsigned NOT NULL default '0', -- ref_id int(11) unsigned NOT NULL default '0', -- datestamp char(10) NOT NULL default '', -- raws int(11) unsigned NOT NULL default '0', -- uniques int(11) unsigned NOT NULL default '0', -- trial_signups int(11) NOT NULL default '0', -- full_signups int(11) NOT NULL default '0', -- annual_signups int(11) unsigned NOT NULL default '0', -- PRIMARY KEY (acct_id,site_id,ref_id,datestamp), -- KEY acct_id (acct_id), -- KEY site_id (site_id), -- KEY ref_id (ref_id), -- KEY datestamp (datestamp) --) TYPE=MyISAM; -- --How come it doesn't use the 'datestamp' index on this query: -- --mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g --++--+---+--+-+--+ +--- ---+ --| table | type | possible_keys | key | key_len | ref | rows | --Extra | --++--+---+--+-+--+ +--- ---+ --| campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | --where used | --++--+---+--+-+--+ +--- ---+ --1 row in set (0.00 sec) -- --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: This is confusing..?
At 11:08 -0800 11/6/03, Eric Anderson wrote: Given the following table: CREATE TABLE campaign_t ( acct_id int(11) unsigned NOT NULL default '0', site_id tinyint(3) unsigned NOT NULL default '0', ref_id int(11) unsigned NOT NULL default '0', datestamp char(10) NOT NULL default '', raws int(11) unsigned NOT NULL default '0', uniques int(11) unsigned NOT NULL default '0', trial_signups int(11) NOT NULL default '0', full_signups int(11) NOT NULL default '0', annual_signups int(11) unsigned NOT NULL default '0', PRIMARY KEY (acct_id,site_id,ref_id,datestamp), KEY acct_id (acct_id), KEY site_id (site_id), KEY ref_id (ref_id), KEY datestamp (datestamp) ) TYPE=MyISAM; How come it doesn't use the 'datestamp' index on this query: mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g Possibly because datestamp and 20041105 have different datatypes. There's an implicit data conversion required for the comparison, which in some cases prevents use of an index. Try writing '20041105' rather than 20041105 and see if that makes a difference. ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | where used | ++--+---+--+-+--+++ 1 row in set (0.00 sec) -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: This is confusing..?
Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. What's the syntax for 'show keys'? -- -- Eric Anderson - ICQ: 3849549 - San Diego, CA [EMAIL PROTECTED] | Mac and Bumble | http://www.macandbumble.com [EMAIL PROTECTED] | VIP BumbleCash | http://vip.bumblecash.com [EMAIL PROTECTED] | Online Net-Entertainment | http://www.on-e.com -- You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g Try surrounding the value with quotes, i.e.: explain SELECT * FROM campaign_t WHERE datestamp '20041105' -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
Possibly because datestamp and 20041105 have different datatypes. There's an implicit data conversion required for the comparison, which in some cases prevents use of an index. Try writing '20041105' rather than 20041105 and see if that makes a difference. Nope.. :-/ mysql explain SELECT * FROM campaign_t WHERE datestamp '20041105'\g ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438473 | where used | ++--+---+--+-+--+++ I think someone's onto something though regarding the 'optimize' command. The above selection is actually a DELETE, and it results in about a 3% deletion of the table per day. This has been running for months (if not years) with no optimization. Is it possible that this table is so fragmented? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
On Thu, Nov 06, 2003 at 11:08:03AM -0800, Eric Anderson wrote: Given the following table: CREATE TABLE campaign_t ( acct_id int(11) unsigned NOT NULL default '0', site_id tinyint(3) unsigned NOT NULL default '0', ref_id int(11) unsigned NOT NULL default '0', datestamp char(10) NOT NULL default '', raws int(11) unsigned NOT NULL default '0', uniques int(11) unsigned NOT NULL default '0', trial_signups int(11) NOT NULL default '0', full_signups int(11) NOT NULL default '0', annual_signups int(11) unsigned NOT NULL default '0', PRIMARY KEY (acct_id,site_id,ref_id,datestamp), KEY acct_id (acct_id), KEY site_id (site_id), KEY ref_id (ref_id), KEY datestamp (datestamp) ) TYPE=MyISAM; How come it doesn't use the 'datestamp' index on this query: mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | where used | ++--+---+--+-+--+++ 1 row in set (0.00 sec) If most of the rows match that WHERE clause, it'll just scan the table rather than using the index. It's faster to do so. This is documented in the manual. http://www.mysql.com/doc/en/How_to_avoid_table_scan.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,006,826,779 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: This is confusing..?
Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. mysql show index from campaign_t\g +++---+--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++---+--+-+---+-+--++-+ | campaign_t | 0 | PRIMARY |1 | acct_id | A | 1898 | NULL | NULL | | | campaign_t | 0 | PRIMARY |2 | site_id | A | 5621 | NULL | NULL | | | campaign_t | 0 | PRIMARY |3 | ref_id | A | 109627 | NULL | NULL | | | campaign_t | 0 | PRIMARY |4 | datestamp | A | 438511 | NULL | NULL | | | campaign_t | 1 | acct_id |1 | acct_id | A | 1898 | NULL | NULL | | | campaign_t | 1 | site_id |1 | site_id | A | 36 | NULL | NULL | | | campaign_t | 1 | ref_id|1 | ref_id | A | 54813 | NULL | NULL | | | campaign_t | 1 | datestamp |1 | datestamp | A | 76 | NULL | NULL | | +++---+--+-+---+-+--++-+ I have no idea how to interpret this.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
Hi Eric, The Cardinality of the datestamp column is only 76. That means it thinks there's only 76 unique values in all of your rows. In other words, there's a good chance that your WHERE matches more than ~30% of the rows, in which case the index won't be used. If you haven't run OPTIMIZE in years, it'd probably be a good idea to do that anyway... see if it changes anything. OPTIMIZE includes ANALYZE by the way. On a side note, you might as well remove the separate acct_id index since it's the first column of the PRIMARY KEY anyway where it can be used as if it was a separate index. The additional index is just wasting space. Drop this index before you run the OPTIMIZE. :-) ALTER TABLE campaign_t DROP INDEX acct_id; OPTIMIZE TABLE campaign_t; Matt - Original Message - From: Eric Anderson Cc: [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 1:45 PM Subject: RE: This is confusing..? Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. mysql show index from campaign_t\g +++---+--+-+ ---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++---+--+-+ ---+-+--++-+ | campaign_t | 0 | PRIMARY |1 | acct_id | A |1898 | NULL | NULL | | | campaign_t | 0 | PRIMARY |2 | site_id | A |5621 | NULL | NULL | | | campaign_t | 0 | PRIMARY |3 | ref_id | A | 109627 | NULL | NULL | | | campaign_t | 0 | PRIMARY |4 | datestamp | A | 438511 | NULL | NULL | | | campaign_t | 1 | acct_id |1 | acct_id | A |1898 | NULL | NULL | | | campaign_t | 1 | site_id |1 | site_id | A | 36 | NULL | NULL | | | campaign_t | 1 | ref_id|1 | ref_id | A | 54813 | NULL | NULL | | | campaign_t | 1 | datestamp |1 | datestamp | A | 76 | NULL | NULL | | +++---+--+-+ ---+-+--++-+ I have no idea how to interpret this.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Html entities and MySql, this is a confusing one to me.
Was working on a database job last night, and ran into a little stumbling block, hope all this comes though in email, if you see a ® it is a registered mark just in case the email clients out there change it around. So I have a simple database, one table, and say one field, varchar(14) and in it I put hello®, I use html input form elements and a middleware called WebSiphon, I have also done this in php with phpmyadmin as well, the character shows up on display in the Browser as a registered mark, as well as in phpmyadmin, all good so far. Now, in my shell in the mysql prompt, or on a dump of the data, the registered mark is not what it should be, here is a example. mysql select name from custom_bikes; ++ | name | ++ | 2002 FAT BOYÆ, FLSTF - | | 2002 V-RODÆ, VRSCA - | | 2002 ROAD KINGÆ FLHR - | | 2002 FAT BOYÆ, FLSTF - | | 2002 FAT BOYÆ, FLSTFI -| | 2002 DYNA WIDE GLIDEÆ, FXDWG - | | 2002 SOFTAILÆ DEUCE, FXSTDI - | | 2002 V-RODÆ, VRSCA | | 2002 FAT BOYÆ FLSTFI | | 2002 SOFTAILÆ STANDARD, FXST | ++ Where the Æ should be a ® So in my middleware, I have made a function called encodeHTML(string) And of course it does not work to convert the ® to reg; for propper html display in a browser. Granted, most browsers are forgiving, but I consider this muddy data in MySql and would like the correct data in there. Thanks for any help. - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Html entities and MySql, this is a confusing one to me.
on 01/13/2003 1:58 PM, Paul DuBois at [EMAIL PROTECTED] wrote: How do you know this isn't simply a failure of your terminal window to display the character properly? That's the most likely problem here, not that your data are stored incorrectly. Well, I don¹t for certain, I just did a dump to a text file and here were the results... As you can see the pesky Æ is in place of the ®, which really makes no sense to me, as in a browser is displays as a ®, but if I do a string replace on the ® it does not see it as one, so it wont replace it with the html entity I desire, this also happens on a few other characters as well. -- -- Dumping data for table 'custom_bikes' -- INSERT INTO custom_bikes VALUES (5,'2002 FAT BOYÆ, FLSTF -','Custom Black Powder','$23,152 plus','Custom Black Powder Coating on Accessories\r\nCustom Wheels\r\nExhaust/Hi-Flow\r\nCustom Pegs and Footboards',1,'1','qmKjwFN4eobgxQyCiQZ5u7mm.jpg',20030112182536,200301121538 31); INSERT INTO custom_bikes VALUES (3,'2002 V-RODÆ, VRSCA -','Skull paint','$23,397 plus','Custom Skull Paint\r\nHi-Flow/Pipes\r\nCustom Pegs and Grips',7,'1','5cFX4x85HAe2Fao2pGQUSAyp.jpg',20030112183701,20030112153742); INSERT INTO custom_bikes VALUES (4,'2002 ROAD KINGÆ FLHR -','Flame paint','$23,809 plus','Custom Luxury Rich Red with Red Flames paint\r\nPerformance Exhaust/Hi-Flow\r\nSecurity System\r\nCustom handgrips, footboards, and other accessories',6,'1','Y5Es935New44GzuUnTxJFA3i.jpg',20030112175803,20030112153 750); INSERT INTO custom_bikes VALUES (6,'2002 FAT BOYÆ, FLSTF -','Silver with blue flames','$22,497 plus','Custom Silver and Blue Flame Paint\r\nPipes/Hi-Flow\r\nCustom Grips, Pegs, and Footboards',4,'1','RXofzWGeVbc14G4EDbAwrQw3.jpg',20030112175757,200301121538 41); INSERT INTO custom_bikes VALUES (8,'2002 FAT BOYÆ, FLSTFI -','White pearl with white flames','$31,274 plus','Custom White Paint With White Flames\r\nFull Chrome Accessory Package\r\nCustom Wheels\r\nPerformance Exhaust/Hi-Flow',5,'1','X5m5YtfFuGoLDuT5MJ9YYjuh.jpg',20030112175800,2003011 2153901); INSERT INTO custom_bikes VALUES (9,'2002 DYNA WIDE GLIDEÆ, FXDWG -','Skull paint','$26,952 plus','Custom Skull Paint\r\nCustom Wheels\r\nTriple Trees\r\nLots of Chrome\r\nPerformance Exhaust/Hi-Flow',3,'1','7uG5VXUfgiwfgwN4Xg4jpR6P.jpg',20030112182532,2003011 2153907); INSERT INTO custom_bikes VALUES (10,'2002 SOFTAILÆ DEUCE, FXSTDI -','Flame paint','$34,619 plus','Custom Flame Paint\r\nCustom Wheels\r\nChrome Accessory Package\r\n95î Big Bore\r\nExhaust/Hi-Flow',2,'1','GrjGCz33woRHwe9JH9xyWZ7j.jpg',20030113050601 ,20030112153914); INSERT INTO custom_bikes VALUES (14,'2002 V-RODÆ, VRSCA','Flame Paint','$22,817 plus ttl','Custom Flame Paint\r\nHi-Flow/Pipes\r\n',8,'1','HpcfDW5BmkoozLVWDYQA63bX.jpg',20030113065 241,20030113065241); INSERT INTO custom_bikes VALUES (15,'2002 FAT BOYÆ FLSTFI','Snakeskin Paint','SOLD','Custom Snakeskin Paint\r\nScreaminí Eagle Exhaust/Hiflow\r\n',9,'1','GuCeu6qbwzysXg2QxmFrWvoW.jpg',20030113065405,2003 0113065405); INSERT INTO custom_bikes VALUES (16,'2002 SOFTAILÆ STANDARD, FXST','Flamepaint','SOLD','Custom Flame Paint\r\nLots of Chrome\r\nCustom Accessories\r\nSecurity System\r\nVance and Hines Exhaust',10,'1','6grjaxN7LKcUtNneMAsoMvB4.jpg',20030113065514,20030113065514 ); sql,query,queries,smallint - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Html entities and MySql, this is a confusing one to me.
At 13:51 -0800 1/13/03, Scott Haneda wrote: Was working on a database job last night, and ran into a little stumbling block, hope all this comes though in email, if you see a ® it is a registered mark just in case the email clients out there change it around. So I have a simple database, one table, and say one field, varchar(14) and in it I put hello®, I use html input form elements and a middleware called WebSiphon, I have also done this in php with phpmyadmin as well, the character shows up on display in the Browser as a registered mark, as well as in phpmyadmin, all good so far. Now, in my shell in the mysql prompt, or on a dump of the data, the registered mark is not what it should be, here is a example. mysql select name from custom_bikes; ++ | name | ++ | 2002 FAT BOYÆ, FLSTF - | | 2002 V-RODÆ, VRSCA - | | 2002 ROAD KINGÆ FLHR - | | 2002 FAT BOYÆ, FLSTF - | | 2002 FAT BOYÆ, FLSTFI -| | 2002 DYNA WIDE GLIDEÆ, FXDWG - | | 2002 SOFTAILÆ DEUCE, FXSTDI - | | 2002 V-RODÆ, VRSCA | | 2002 FAT BOYÆ FLSTFI | | 2002 SOFTAILÆ STANDARD, FXST | ++ Where the Æ should be a ® How do you know this isn't simply a failure of your terminal window to display the character properly? That's the most likely problem here, not that your data are stored incorrectly. So in my middleware, I have made a function called encodeHTML(string) And of course it does not work to convert the ® to reg; for propper html display in a browser. Granted, most browsers are forgiving, but I consider this muddy data in MySql and would like the correct data in there. Thanks for any help. - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Html entities and MySql, this is a confusing one to me.
on 01/13/2003 2:10 PM, Scott Haneda at [EMAIL PROTECTED] wrote: How do you know this isn't simply a failure of your terminal window to display the character properly? That's the most likely problem here, not that your data are stored incorrectly. Correct you are, the data in MySql was clean, I needed to set my terminal to the proper charset, but I still for the life of me can not figure out why I can not string replace on it in my middleware. - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
getting a bit confusing -
Hi me again , regarding my situation i was able to get a responce via mysql \r library(eexample db) localhost thus used the select cmd select * from members; and everthings fine . From the manual it does mention that certian installations allow user to connect as anonymous unamed user to the server running on the localhost thus i need no options . I basically want to enter MySql as the administrator thus utilise the cmd mysql -u root mysql this all worked fine but i have amended the host table with the sql data staements sql schema statemets except alter. ie i mean select, inser, update, delete, and also create and drop_priv using the insert into command. Have caused any major problem that can be reversed Cheers Chuckie Amadi Systems Programmer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confusing SELECT statement
you can use select addresses.id from addresses LEFT JOIN mlist ON addresses.id=mlist.addr_id AND mlist.mlist_id=$mlist_id WHERE mlist.mlist_id IS NULL On Thu 27. September 2001 08:09, Adam Gintis wrote: I have the following table mlist: +--+-+ | mlist_id | addr_id | +--+-+ |1 | 1 | |2 | 2 | |2 | 3 | |1 | 3 | +--+-+ This is the join table of my address book and my mailing lists. So person 1 is in list 1, person 2 is in list 2, and person 3 is in both lists. What I want to do is get a list of everyone that is NOT in a certain list. In other words, if I'm editing list 1, I need a list of all the people in the address book that aren't in list 1 (person 2). If I'm editing list 2, I want person 1. I never want to see person 3, because he is already in both lists. I'm currently trying to do this with a statement like SELECT * FROM mlist GROUP BY addr_id HAVING mlist_id != 'list I'm currently editing'. The problem is that group by addr_id keeps the first row's value for mlist_id. So when I'm editing list 2, it works. But when I'm editing list 1, person 3 still shows up. If the last two rows were the other way around in the table, it would work for list 1 and not for list 2. So the question is, is there a command I can use to look through the aggregate set of mlist_id's, or is there another way to write this query? Thanks! --- Adam Gintis Vanderbilt University - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Confusing SELECT statement
I have the following table mlist: +--+-+ | mlist_id | addr_id | +--+-+ |1 | 1 | |2 | 2 | |2 | 3 | |1 | 3 | +--+-+ This is the join table of my address book and my mailing lists. So person 1 is in list 1, person 2 is in list 2, and person 3 is in both lists. What I want to do is get a list of everyone that is NOT in a certain list. In other words, if I'm editing list 1, I need a list of all the people in the address book that aren't in list 1 (person 2). If I'm editing list 2, I want person 1. I never want to see person 3, because he is already in both lists. I'm currently trying to do this with a statement like SELECT * FROM mlist GROUP BY addr_id HAVING mlist_id != 'list I'm currently editing'. The problem is that group by addr_id keeps the first row's value for mlist_id. So when I'm editing list 2, it works. But when I'm editing list 1, person 3 still shows up. If the last two rows were the other way around in the table, it would work for list 1 and not for list 2. So the question is, is there a command I can use to look through the aggregate set of mlist_id's, or is there another way to write this query? Thanks! --- Adam Gintis Vanderbilt University - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: my sql and memory--A confusing issue for a newbie
VVM Ravikumar Sarma Chengalvala wrote: Hi, 1. How much memory does mysql allocate by default to a database?Any default table wise memory limitations as well? Its doesn't allocated very much by default ( 10M ? ), and there is no per table memory allocation. 2. Is mysql's memory management and Oracle's memory management similar?Or altogeather different? They both have things like key buffers sort buffers that you can explicitly size, but MySQL doesn't have a db_block_buffer kind of thing that Oracle has. Rather, it lets your OS cache the data file, which works very well in Linux just fine on WinNT. --Josh _ Joshua Chamas Chamas Enterprises Inc. NodeWorks free web link monitoring Huntington Beach, CA USA http://www.nodeworks.com1-714-625-4051 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
