Re: selecting more sum()
Hi, just Start here http://dev.mysql.com/doc/mysql/en/select.html mathias Selon Octavian Rasnita [EMAIL PROTECTED]: Hi, I have the following tables: create table articles( id int unsigned not null primary key, title varchar(255) not null, body text not null ); create table newspapers( id int unsigned not null primary key, name varchar(255) not null ); create table visitors( id int unsigned not null primary key, id_articles int unsigned not null ); create table comments( id int unsigned not null primary key, id_articles int unsigned not null ); I would like to select: - the title from `articles` - the length of the body from `articles` - the name of the newspaper which correspond to the title of the article - the number of visitors each articles have (count(*) from visitors where articles.id=visitors.id_articles) - the number of comments each articles have (count(*) from comments where articles.id=comments.id_articles) I don't know how to select the last 2 elements (the number of visitors and the number of comments). I want to select all the articles from `articles` even if there are no visitors or no comments in the `visitors` and `comments` tables, so I might need using left join. I have tried a few ways of doing this, but without any result. Please help me if you can. (I have written the table definitions above right in the email client, so they are not tested, but I think they are correct) Thank you. Teddy -- 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]
Increasing max_connections and table_cache on Solaris 8
Hi, This applies to 4.1.10 on Solaris 8, with 1 GB of RAM. Our internal customer wants to be able to make 1000 concurrent connections to our database, with a mix of MyISAM and InnoDB tables, but according to http://dev.mysql.com/doc/mysql/en/table-cache.html and an online Solaris reference (http://docsun.cites.uiuc.edu/sun_docs/C/solaris_9/SUNWaadm/SOLTUNEPARAM REF/p44.html) I would need a table_cache of roughly 1000*2*3 = 6000 (assuming only MyISAM tables are used which need two file descriptors per table, and three tables per join). However, from experience I know that increasing table_cache from 64 to 256 will already result in 'Too many open files' errors and the database becoming unaccessible. To solve this, and actually allow 1000 connections, should I let the sysadmin increase the limit of open file descriptors on the Solaris box, or is there a setting in MySQL that I overlooked? Note: max_connections is set to 1000 in the my.cnf file, but when starting up, MySQL doesn't accept that value and decreases it to 246. Setting the variable to 1000 whilst MySQL is running /is/ accepted. Kind regards, -- Martijn ASML ITMS Application Support / Webcenter -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
limit the number of startup process
Hi i like to imit te number of process at startup: /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/ but i couldn t find the option thx all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
limit the number of startup process
Hi i like to imit te number of process at startup: /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/ but i couldn t find the option thx all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connection error from c application
Dear All,
I'm new into mysql and has been encountering huge problem in
connecting to the database from the c application. The code execute
with an error message :
Failed to connect to database: Error:
Client does not support authentication protocol requested by server;
consider upgrading MySQL client
Is this a bug?
I'm running the MySql server version 4.1 with server and client both
on same computer running on windows XP.
I have no problem connecting to the server using root with a windfall
password but I cannot connect from the c application below.
I have only one user in the server (root,windfall) and has been
successful in accessing mysql from the client side except when
connecting from a c application.
here is the application which I compile using Visual C++ compiler:
#include stdio.h
#include winsock.h
#include iostream.h
#include mysql.h
int main()
{
MYSQL mysql;
MYSQL_ROW row;
MYSQL_RES *result;
unsigned int num_fields;
unsigned int i;
int num = 0;
int iRetValue = 0;
mysql_init(mysql);
//printf(%s,mysql );
if (!mysql_real_connect(mysql,localhost,root,windfall,mysql,3306,
NULL,0))
{
fprintf(stderr, Failed to connect to database: Error: \n%s\n,
mysql_error(mysql));
}
else
{
printf(SUCCESS\n);
iRetValue = mysql_query(mysql, SELECT * FROM user);
if( iRetValue != 0 )
{
printf(Query Not Executed Properly.Please Check The Syntax.\n);
}
//here goes the error message :o)
else
{
result = mysql_store_result(mysql);
num = mysql_field_count(mysql);
printf(Number Of Rows :%d\n,num );
num_fields = mysql_num_fields( result);
printf(Number Of Coloumns :%d\n,num_fields );
while ((row = mysql_fetch_row(result)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(result);
for(i = 0; i num_fields; i++)
{
printf([%.*s] \t, (int) lengths[i], row[i] ? row[i] : NULL);
}
printf(\n);
}
}
}
}
I would appreciate if you guys can help. thanks.
Elizabeth
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load balancer and cluster
(sorry for my english) hello people: i want to answer if do exists any mysql solution that implements load balancer and redundancy between 'application layer' and 'mysqld layer' i will try to explain me: i want to implement a completed mysql cluster system, and the documentation is very good, but i see a problem: we need to look the picture in this page: http://dev.mysql.com/doc/mysql/en/mysql-cluster-overview.html in this picture we can see that the 'storage layer' implements correctly redundance and load balancer feature, because the 'SQL layer' ask for data to a 'storage layer' but not to a one particular node. but the applications in the 'application layer' do the petitions to the 'SQL layer' directly to a particular node, and them: what happen if a node in the 'SQL layer' is off? how i can offer a load balancer feature between 'application layer' and 'SQL layer'. i searched a lot on the web but i only founded out answers but not solutions.. i also see this thread in this mail list: http://lists.mysql.com/search.php?q=load+balancerlist=m=abegin=60 but i did not found an answer in this thread. any help? thanks a lot fernando guillen (aka d2clon) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexing not working
I was trying to analyze a query that was taking almost 4 seconds to
execute. While trying to create additional indexes - found that the
query is not using any index from table qb_test_result . The type
returned is ALL for qb_test_result .
I have given the tables and query below. It would have been a help if
some one could throw some light on why this is behaving so.. I tried
to read the mysql manual and follow the
steps given there.
CREATE TABLE `qb_question` (
`id` int(11) NOT NULL auto_increment,
`question` text NOT NULL,
`url` varchar(255) NOT NULL default '',
`file` varchar(255) NOT NULL default '',
`marks` int(11) NOT NULL default '0',
`detailedAnswer` text NOT NULL,
`author` int(11) NOT NULL default '0',
`testId` smallint(4) NOT NULL default '0',
`loId` int(11) NOT NULL default '0',
`needWrittenAnswer` enum('Y','N') NOT NULL default 'Y',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `testId` (`testId`,`archive`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test` (
`id` smallint(4) NOT NULL auto_increment,
`categoryId` int(11) NOT NULL default '0',
`title` varchar(80) NOT NULL default '',
`description` text NOT NULL,
`instructions` text NOT NULL,
`author` int(4) NOT NULL default '0',
`type` enum('PUBLIC','POST','PRE','REV') default NULL,
`duration` smallint(6) NOT NULL default '0',
`passrate` float NOT NULL default '0',
`showDetails` enum('Y','N') NOT NULL default 'Y',
`showRandom` enum('Y','N') NOT NULL default 'Y',
`showAssessment` enum('N','Y') NOT NULL default 'N',
`noOfQuestions` int(11) NOT NULL default '0',
`dateAvailable` datetime NOT NULL default '-00-00 00:00:00',
`companyId` int(11) NOT NULL default '0',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `title` (`title`,`author`,`type`),
KEY `categoryId` (`categoryId`),
KEY `companyAuthor` (`companyId`,`author`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test_result` (
`id` int(11) NOT NULL auto_increment,
`testId` smallint(4) NOT NULL default '0',
`studentId` int(11) NOT NULL default '0',
`marks` smallint(4) NOT NULL default '0',
`startTime` int(20) default NULL,
`endTime` int(20) default NULL,
`percentage` float NOT NULL default '0',
`status` enum('FAIL','PASS','POST','UNCOMPLETE') NOT NULL default
'UNCOMPLETE',
PRIMARY KEY (`id`),
KEY `testStudent` (`testId`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test_result_details` (
`sequenceId` int(20) NOT NULL default '0',
`resultId` int(20) NOT NULL default '0',
`questionId` int(20) NOT NULL default '0',
`viewStatus` enum('NV','V','A') NOT NULL default 'NV',
`bookMark` enum('Y','N') NOT NULL default 'N',
`correct` enum('Y','N') NOT NULL default 'N',
`postMarks` int(11) NOT NULL default '0',
KEY `resultId` (`resultId`)
) TYPE=MyISAM ;
EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS
testId, qb_test.title testName, qb_question.marks, qb_test.passrate,
qb_test_result.marks testMark, qb_test_result.percentage
testPercentage, qb_test_result.startTime, qb_test_result.endTime,
qb_test_result.status
FROM qb_test_result, qb_test_result_details, qb_test, qb_question
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test.companyId =1
AND qb_test.author = '2'
AND qb_test_result_details.questionId = qb_question.id
+++---+--+-+---+--+-+
| table | type | possible_keys | key |
key_len | ref | rows | Extra |
+++---+--+-+---+--+-+
| qb_test_result | ALL| PRIMARY,testStudent | NULL |
NULL | NULL | 2494 | |
| qb_test_result_details | ref| resultId | resultId |
4 | qb_test_result.id | 45 | |
| qb_test| eq_ref | PRIMARY,companyAuthor | PRIMARY |
2 | qb_test_result.testId |1 | Using where |
| qb_question| eq_ref | PRIMARY | PRIMARY |
4 | qb_test_result_details.questionId |1 | |
+++---+--+-+---+--+-+
Thank you
Sajith A
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql and limitations and hardware features support
hello people: im very interested about the limitations and hardware features support. for example: software limitation: how much rows does a table can to have? how much size of a database does mysql support? ... hardware features support: has mysql multi-processor support? how much processors? how is the size maximum of the RAM that mysql support? ... any url? thanks a lot d2clon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
Hi,
have you created an index on (id, testId) ?
i can't see the other indexes ? The only ones are the PK and testStudent, so the
PK is used.
Mathias
Selon Sajith A [EMAIL PROTECTED]:
I was trying to analyze a query that was taking almost 4 seconds to
execute. While trying to create additional indexes - found that the
query is not using any index from table qb_test_result . The type
returned is ALL for qb_test_result .
I have given the tables and query below. It would have been a help if
some one could throw some light on why this is behaving so.. I tried
to read the mysql manual and follow the
steps given there.
CREATE TABLE `qb_question` (
`id` int(11) NOT NULL auto_increment,
`question` text NOT NULL,
`url` varchar(255) NOT NULL default '',
`file` varchar(255) NOT NULL default '',
`marks` int(11) NOT NULL default '0',
`detailedAnswer` text NOT NULL,
`author` int(11) NOT NULL default '0',
`testId` smallint(4) NOT NULL default '0',
`loId` int(11) NOT NULL default '0',
`needWrittenAnswer` enum('Y','N') NOT NULL default 'Y',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `testId` (`testId`,`archive`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test` (
`id` smallint(4) NOT NULL auto_increment,
`categoryId` int(11) NOT NULL default '0',
`title` varchar(80) NOT NULL default '',
`description` text NOT NULL,
`instructions` text NOT NULL,
`author` int(4) NOT NULL default '0',
`type` enum('PUBLIC','POST','PRE','REV') default NULL,
`duration` smallint(6) NOT NULL default '0',
`passrate` float NOT NULL default '0',
`showDetails` enum('Y','N') NOT NULL default 'Y',
`showRandom` enum('Y','N') NOT NULL default 'Y',
`showAssessment` enum('N','Y') NOT NULL default 'N',
`noOfQuestions` int(11) NOT NULL default '0',
`dateAvailable` datetime NOT NULL default '-00-00 00:00:00',
`companyId` int(11) NOT NULL default '0',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `title` (`title`,`author`,`type`),
KEY `categoryId` (`categoryId`),
KEY `companyAuthor` (`companyId`,`author`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test_result` (
`id` int(11) NOT NULL auto_increment,
`testId` smallint(4) NOT NULL default '0',
`studentId` int(11) NOT NULL default '0',
`marks` smallint(4) NOT NULL default '0',
`startTime` int(20) default NULL,
`endTime` int(20) default NULL,
`percentage` float NOT NULL default '0',
`status` enum('FAIL','PASS','POST','UNCOMPLETE') NOT NULL default
'UNCOMPLETE',
PRIMARY KEY (`id`),
KEY `testStudent` (`testId`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test_result_details` (
`sequenceId` int(20) NOT NULL default '0',
`resultId` int(20) NOT NULL default '0',
`questionId` int(20) NOT NULL default '0',
`viewStatus` enum('NV','V','A') NOT NULL default 'NV',
`bookMark` enum('Y','N') NOT NULL default 'N',
`correct` enum('Y','N') NOT NULL default 'N',
`postMarks` int(11) NOT NULL default '0',
KEY `resultId` (`resultId`)
) TYPE=MyISAM ;
EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS
testId, qb_test.title testName, qb_question.marks, qb_test.passrate,
qb_test_result.marks testMark, qb_test_result.percentage
testPercentage, qb_test_result.startTime, qb_test_result.endTime,
qb_test_result.status
FROM qb_test_result, qb_test_result_details, qb_test, qb_question
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test.companyId =1
AND qb_test.author = '2'
AND qb_test_result_details.questionId = qb_question.id
+++---+--+-+---+--+-+
| table | type | possible_keys | key |
key_len | ref | rows | Extra |
+++---+--+-+---+--+-+
| qb_test_result | ALL| PRIMARY,testStudent | NULL |
NULL | NULL | 2494 | |
| qb_test_result_details | ref| resultId | resultId |
4 | qb_test_result.id | 45 | |
| qb_test| eq_ref | PRIMARY,companyAuthor | PRIMARY |
2 | qb_test_result.testId |1 | Using where |
| qb_question| eq_ref | PRIMARY | PRIMARY |
4 | qb_test_result_details.questionId |1 | |
+++---+--+-+---+--+-+
Thank you
Sajith A
--
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:
mysql_init
Trying to run exilog as part of the EXIM mta but getting a mysql error both when i run from command line and as part of apache CL /usr/lib/perl5/site_perl/5.8.5/x86_64-linux/auto/DBD/mysql/mysql.so: undefined symbol: mysql_init apache error log /usr/bin/perl: symbol lookup error: /usr/lib/perl5/site_perl/5.8.5/x86_64-linux/auto/DBD/mysql/mysql.so: undefined symbol: mysql_init [Thu Jun 23 11:13:54 2005] [error] [client 143.117.9.15] Premature end of script headers: /var/www/htdocs/exilog_cgi.pl [Thu Jun 23 11:13:55 2005] [error] [client 143.117.9.15] File does not exist: /var/www/htdocs/favicon.ico [EMAIL PROTECTED]:/var/www/cgi-bin# ldd /usr/lib/perl5/site_perl/5.8.5/x86_64-linux/auto/DBD/mysql/mysql.so libcrypt.so.1 = /lib64/libcrypt.so.1 (0x2abbe000) libnsl.so.1 = /lib64/libnsl.so.1 (0x2acf2000) libm.so.6 = /lib64/libm.so.6 (0x2ae0a000) libc.so.6 = /lib64/libc.so.6 (0x2af92000) /lib64/ld-linux-x86-64.so.2 (0x5000) [EMAIL PROTECTED]:/var/www/cgi-bin# [EMAIL PROTECTED]:/var/www/cgi-bin# cat /etc/ld.so.conf /usr/local/lib /usr/local/lib64 /usr/X11R6/lib /usr/x86_64-slackware-linux/lib /usr/lib /usr/lib64 /usr/local/lib64 /lib /lib64 /usr/X11R6/lib64 i've googled and the only answer to problems like this has been to reinstall the DB perl module. Ive tried this but still get same error. any clues??? thanks in advance. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and limitations and hardware features support
From: d2clon im very interested about the limitations and hardware features support. for example: software limitation: how much rows does a table can to have? how much size of a database does mysql support? These depend on the version of MySQL and the OS The number of rows is virtually unlimited; some users appear to store 5,000,000,000 rows in a table, so no worries there The size of a table is limited by the file system: http://dev.mysql.com/doc/mysql/en/table-size.html hardware features support: has mysql multi-processor support? how much processors? AFAIK MySQL runs a query in a single process. Since database servers often need to process more than one query at the same time additional processes will be started to serve the queries for extra connections. The OS will arrange the use of processors by various processes how is the size maximum of the RAM that mysql support? This is again an OS limit. In a 32-bit environment processes are limited to 2GB (3GB or so by using certain pages in various Linux versions); in a 64-bit environment processes can use far more memory (I don't know the limit by heart, but is is much larger than you can afford or the motherboard supports) Using recent developments such as MySQL Cluster you can spread a database over multiple servers, so this will increase the size of the databases MySQL can handle even more. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
Thank you Mathias for your time... have you created an index on (id, testId) ? i can't see the other indexes ? The only ones are the PK and testStudent, so the PK is used. Yes i also tried that .. but it was not getting used.. it listed as the possible_keys PRIMARY and testStudent but the key value was NULL.. I tried to index all combination of fields in qb_test_result.. but nothing was getting use.. Am i doing something wrong in the query? Thank you Sajith A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
Hi Sajith, I tried to recreate you tables and do some tests, but i don't have significant data. So i don't take your explain plan. I suggest you to test after optimize table ... and analyze table ... if you stay with the problem and you can give me a set of data, i'll try to help more. Mathias Selon Sajith A [EMAIL PROTECTED]: Thank you Mathias for your time... have you created an index on (id, testId) ? i can't see the other indexes ? The only ones are the PK and testStudent, so the PK is used. Yes i also tried that .. but it was not getting used.. it listed as the possible_keys PRIMARY and testStudent but the key value was NULL.. I tried to index all combination of fields in qb_test_result.. but nothing was getting use.. Am i doing something wrong in the query? Thank you Sajith A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_init
i've googled and the only answer to problems like this has been to reinstall the DB perl module. Ive tried this but still get same error. any clues??? thanks in advance. Ronan Is libmysqlclient.so available in one of your LD_LIBRARY_PATH directory ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_init
Philippe Poelvoorde wrote: i've googled and the only answer to problems like this has been to reinstall the DB perl module. Ive tried this but still get same error. any clues??? thanks in advance. Ronan Is libmysqlclient.so available in one of your LD_LIBRARY_PATH directory ? thanks for gettin back to me... [EMAIL PROTECTED]:$ cat /etc/ld.so.conf /usr/local/lib /usr/local/lib64 /usr/X11R6/lib /usr/x86_64-slackware-linux/lib /usr/lib /usr/lib64 /usr/local/lib64 /lib /lib64 /usr/X11R6/lib64 [EMAIL PROTECTED]:$ locate libmysqlclient.so /usr/lib64/mysql/libmysqlclient.so.12.0.0 /usr/lib64/mysql/libmysqlclient.so /usr/lib64/mysql/libmysqlclient.so.12 /usr/lib64/libmysqlclient.so /usr/lib64/libmysqlclient.so.12 yes it is im still getting the mysql_init error... what is this usually indicataive of?? a build issue?? PATHs?? config file?? Im using the standard 'came with distribution' MySQL from SLAMD 10.1... im thouroughly exhausted with this. all help / comments *GREATLY* appreciated ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlshow question
Hi Danny, Thank you for helping me here. Yes, I am aware of need to have the space between --host=xxx and --user=xxx, and command I am executing actually has a space. It looks like you believe that my problems related to security of remote logon. That is something, I am going to investigate more closely. My mysql command hangs, too. But, I have not done complete research on all its variations and has not done complete investigation on remote security. Best, Mikhail Berman -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 22, 2005 4:47 PM To: Berman, Mikhail; mysql@lists.mysql.com Subject: Re: mysqlshow question Berman, Mikhail wrote: Hi everyone, My /usr/local/mysql/bin/mysqlshow --host=xxx--user=xxx --password= command hangs. I can ping host from local machine I can locally execute mysqlshow command on the host. I can locally execute mysqlshow command on remote server My environment is UNIX with MySQL 4.1.xx installed on both local server and host. Any help is greatly appreciated, Mikhail Berman Hi Berman, sorry if i might ask some simple questions ... but can you logon remotely at all(?), using the client tools like: - mysql -u username -ppassword -h host -D database - mysqladmin -u username -ppassword -h host flush-tables are you granted for a remote logon? does your mysql-client tools hang as well? the statement show a no-space between '--host=xxx--user=xxx' it should be '--host=xxx --user=xxx' or are you aware of that? Best Regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
Sajith A wrote: EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS testId, qb_test.title testName, qb_question.marks, qb_test.passrate, qb_test_result.marks testMark, qb_test_result.percentage testPercentage, qb_test_result.startTime, qb_test_result.endTime, qb_test_result.status FROM qb_test_result, qb_test_result_details, qb_test, qb_question WHERE qb_test_result.id = qb_test_result_details.resultId AND qb_test_result.testId = qb_test.id AND qb_test.companyId =1 AND qb_test.author = '2' AND qb_test_result_details.questionId = qb_question.id +++---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+--+-+---+--+-+ | qb_test_result | ALL| PRIMARY,testStudent | NULL | NULL | NULL | 2494 | | | qb_test_result_details | ref| resultId | resultId | 4 | qb_test_result.id | 45 | | | qb_test| eq_ref | PRIMARY,companyAuthor | PRIMARY | 2 | qb_test_result.testId |1 | Using where | | qb_question| eq_ref | PRIMARY | PRIMARY | 4 | qb_test_result_details.questionId |1 | | +++---+--+-+---+--+-+ Thank you Sajith A Try using straight joins to force MySQL to join from qb_test_result first. The order chosen by the optimizer has no use for keys from this table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting more sum()
Octavian, You seem to be missing an articles column linking it to newspapers. Supposing an id_newspapers column in articles, your query would be something like SELECT articles.title, CHAR_LENGTH(body) AS Length, newspapers.name AS Newspaper, COUNT(visitors.id) AS Visits, COUNT(comments.id) AS Comments FROM articles INNER JOIN newspapers ON articles.id_newspapers = newspapers.id LEFT JOIN visitors ON articles.id = visitors.id_articles LEFT JOIN comments ON articles.id = comments.id_articles GROUP BY articles.id For how to build such queries look at the manual pages for aggregate functions and for GROUP BY. PB - Octavian Rasnita wrote: Hi, I have the following tables: create table articles( id int unsigned not null primary key, title varchar(255) not null, body text not null ); create table newspapers( id int unsigned not null primary key, name varchar(255) not null ); create table visitors( id int unsigned not null primary key, id_articles int unsigned not null ); create table comments( id int unsigned not null primary key, id_articles int unsigned not null ); I would like to select: - the title from `articles` - the length of the body from `articles` - the name of the newspaper which correspond to the title of the article - the number of visitors each articles have (count(*) from visitors where articles.id=visitors.id_articles) - the number of comments each articles have (count(*) from comments where articles.id=comments.id_articles) I don't know how to select the last 2 elements (the number of visitors and the number of comments). I want to select all the articles from `articles` even if there are no visitors or no comments in the `visitors` and `comments` tables, so I might need using left join. I have tried a few ways of doing this, but without any result. Please help me if you can. (I have written the table definitions above right in the email client, so they are not tested, but I think they are correct) Thank you. Teddy -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.11/26 - Release Date: 6/22/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication general question
I have two mysql boxes setup. Fast machines, I think they are dual 3ghz with boat loads of ram. They are not real busy servers but they have some pretty good sized tables, one of them with a few million rows. My question is, I have Nagios setup to monitor the seconds behind master on the backup server. Usually the boxes are pretty current, within ten or twenty seconds. Other times though they seem to get way behind, like I just bumped the nagios warning email level up to 600 seconds. It doesn't seem to have anything to do really with usage because it usually happens in the middle of the night. Does Mysql do re-indexing or something? What could I do to figure out why the replicatant box is getting so far behind? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: limit the number of startup process
Hello. I don't know such an option. However, in my opinion, max_connections variable could be helpful. See: http://dev.mysql.com/doc/mysql/en/server-system-variables.html [EMAIL PROTECTED] wrote: Hi i like to imit te number of process at startup: /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/ but i couldn t find the option thx all -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increasing max_connections and table_cache on Solaris 8
Hello. I don't have enough experience with Solaris.. But on most Unixes your could change the limit of file descriptors for process, not for whole system. See: http://dev.mysql.com/doc/mysql/en/not-enough-file-handles.html Martijn van den Burg [EMAIL PROTECTED] wrote: Hi, This applies to 4.1.10 on Solaris 8, with 1 GB of RAM.=20 Our internal customer wants to be able to make 1000 concurrent connections to our database, with a mix of MyISAM and InnoDB tables, but according to http://dev.mysql.com/doc/mysql/en/table-cache.html and an online Solaris reference (http://docsun.cites.uiuc.edu/sun_docs/C/solaris_9/SUNWaadm/SOLTUNEPARAM REF/p44.html) I would need a table_cache of roughly 1000*2*3 =3D 6000 (assuming only MyISAM tables are used which need two file descriptors per table, and three tables per join). However, from experience I know that increasing table_cache from 64 to 256 will already result in 'Too many open files' errors and the database becoming unaccessible. To solve this, and actually allow 1000 connections, should I let the sysadmin increase the limit of open file descriptors on the Solaris box, or is there a setting in MySQL that I overlooked? Note: max_connections is set to 1000 in the my.cnf file, but when starting up, MySQL doesn't accept that value and decreases it to 246. Setting the variable to 1000 whilst MySQL is running /is/ accepted. Kind regards, -- Martijn ASML ITMS Application Support / Webcenter -- =0D The information contained in this communication and any attachments is co= nfidential and may be privileged, and is for the sole use of the intended= recipient(s). Any unauthorized review, use, disclosure or distribution i= s prohibited. If you are not the intended recipient, please notify the se= nder immediately by replying to this message and destroy all copies of th= is message and any attachments. ASML is neither liable for the proper and= complete transmission of the information contained in this communication= , nor for any delay in its receipt. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_init
yes it is im still getting the mysql_init error... what is this usually indicataive of?? a build issue?? PATHs?? config file?? Im using the standard 'came with distribution' MySQL from SLAMD 10.1... I've got no idea what is this distribution (Mandrake Linux for AMD 64?) what is strange is that you don't have a line with : libmysqlclient.so.12 = /usr/lib/libmysqlclient.so.12 (0xb7fa4000) or something similar (I've check on a Mandrake 9.2 and a Debian 3.1). I would suggest a buggy DBD module ;) Try to remove it and install it from the source at CPAN (which is normally easy to setup). -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlshow question
Berman, Mikhail wrote: Hi Danny, Thank you for helping me here. Yes, I am aware of need to have the space between --host=xxx and --user=xxx, and command I am executing actually has a space. It looks like you believe that my problems related to security of remote logon. That is something, I am going to investigate more closely. My mysql command hangs, too. But, I have not done complete research on all its variations and has not done complete investigation on remote security. Best, Mikhail Berman -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 22, 2005 4:47 PM To: Berman, Mikhail; mysql@lists.mysql.com Subject: Re: mysqlshow question Berman, Mikhail wrote: Hi everyone, My /usr/local/mysql/bin/mysqlshow --host=xxx--user=xxx --password= command hangs. I can ping host from local machine I can locally execute mysqlshow command on the host. I can locally execute mysqlshow command on remote server My environment is UNIX with MySQL 4.1.xx installed on both local server and host. Any help is greatly appreciated, Mikhail Berman Hi Berman, sorry if i might ask some simple questions ... but can you logon remotely at all(?), using the client tools like: - mysql -u username -ppassword -h host -D database - mysqladmin -u username -ppassword -h host flush-tables are you granted for a remote logon? does your mysql-client tools hang as well? the statement show a no-space between '--host=xxx--user=xxx' it should be '--host=xxx --user=xxx' or are you aware of that? Best Regards, Danny Stolle Netherlands Hi Berman, i am not sure if it IS a security matter. What version are you running? mysql \s mysql mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) mysql ... it could be that you are running a firewall, so the port is blocked and the client is still waiting for an answer (network problems hint)? if it excually was a security matter, you would get error messages, like 'Access denied ... ' or what ever. 'Sorry for the mishap for the space between the statement.' Try to log: use a my.cnf and put inside some logging under the [mysqld] section, like: log-error = location/error.log or locate where you have put your log-files, by: mysqlshow variables like log% Danny Stolle Netherlands ps. If you have solved the problem, please let us know so we can learn from it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
[EMAIL PROTECTED] wrote: Hi, have you created an index on (id, testId) ? i can't see the other indexes ? The only ones are the PK and testStudent, so the PK is used. The PK is not used, nor should it be. Look at the EXPLAIN output. Let me rewrite the query with explicit joins, and reformat it for readability: SELECT long list of columns FROM qb_test_result JOIN qb_test_result_details ON qb_test_result.id = qb_test_result_details.resultId JOIN qb_test ON qb_test_result.testId = qb_test.id JOIN qb_question ON qb_test_result_details.questionId = qb_question.id WHERE qb_test.companyId =1 AND qb_test.author = '2'; There are no restrictions on rows from qb_test_result in the WHERE clause, so every row matches. Hence, a full table scan of qb_test_result is required, and no index will be used. Adding indexes to qb_test_result won't help. There is an index on (companyId,author) in qb_test which might have helped, but apparently the optimizer decided that there would be more matching rows in qb_test than the total number of rows in qb_test_result. I do notice that author is an INT, but the query compares it to the string '2'. I doubt that confused the optimizer, but it should be fixed anyway. Change that comparison to AND qb_test.author = 2; Michael Selon Sajith A [EMAIL PROTECTED]: I was trying to analyze a query that was taking almost 4 seconds to execute. While trying to create additional indexes - found that the query is not using any index from table qb_test_result . The type returned is ALL for qb_test_result . I have given the tables and query below. It would have been a help if some one could throw some light on why this is behaving so.. I tried to read the mysql manual and follow the steps given there. snip EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS testId, qb_test.title testName, qb_question.marks, qb_test.passrate, qb_test_result.marks testMark, qb_test_result.percentage testPercentage, qb_test_result.startTime, qb_test_result.endTime, qb_test_result.status FROM qb_test_result, qb_test_result_details, qb_test, qb_question WHERE qb_test_result.id = qb_test_result_details.resultId AND qb_test_result.testId = qb_test.id AND qb_test.companyId =1 AND qb_test.author = '2' AND qb_test_result_details.questionId = qb_question.id +++---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+--+-+---+--+-+ | qb_test_result | ALL| PRIMARY,testStudent | NULL | NULL | NULL | 2494 | | | qb_test_result_details | ref| resultId | resultId | 4 | qb_test_result.id | 45 | | | qb_test| eq_ref | PRIMARY,companyAuthor | PRIMARY | 2 | qb_test_result.testId |1 | Using where | | qb_question| eq_ref | PRIMARY | PRIMARY | 4 | qb_test_result_details.questionId |1 | | +++---+--+-+---+--+-+ Thank you Sajith A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connection error from c application
I guess this is a problem with the version of the C client Libraries
that you are using, because of the way 4.1.x version of mysql stores
the hash value of the password using the PASSWORD( ) function, so when
you are giving the password embedded in the C code, may be the server
does not match it with the one stored byt it , you could try two
things i guess as per the
1) Upgrade all client programs to use the 4.1.1 or newer client library.
Reset the user that needs a pre-4.1 client to use an old password:
mysql UPDATE user SET Password = OLD_PASSWORD('mypass')
- WHERE Host = 'some_host' AND User = 'some_user';
mysql FLUSH PRIVILEGES;
OR
2) Tell the server to use the older password hashing algorithm:
Start mysqld with --old-passwords.
Set the password for all users that has a long password. You can find
these users with:
SELECT * FROM mysql.user WHERE LEN(password) 16;
http://dev.mysql.com/doc/mysql/en/password-hashing.html
Hope this helps
Kishore Jalleda
On 6/23/05, Elizabeth Bonifacio [EMAIL PROTECTED] wrote:
Dear All,
I'm new into mysql and has been encountering huge problem in
connecting to the database from the c application. The code execute
with an error message :
Failed to connect to database: Error:
Client does not support authentication protocol requested by server;
consider upgrading MySQL client
Is this a bug?
I'm running the MySql server version 4.1 with server and client both
on same computer running on windows XP.
I have no problem connecting to the server using root with a windfall
password but I cannot connect from the c application below.
I have only one user in the server (root,windfall) and has been
successful in accessing mysql from the client side except when
connecting from a c application.
here is the application which I compile using Visual C++ compiler:
#include stdio.h
#include winsock.h
#include iostream.h
#include mysql.h
int main()
{
MYSQL mysql;
MYSQL_ROW row;
MYSQL_RES *result;
unsigned int num_fields;
unsigned int i;
int num = 0;
int iRetValue = 0;
mysql_init(mysql);
//printf(%s,mysql );
if (!mysql_real_connect(mysql,localhost,root,windfall,mysql,3306,
NULL,0))
{
fprintf(stderr, Failed to connect to database: Error: \n%s\n,
mysql_error(mysql));
}
else
{
printf(SUCCESS\n);
iRetValue = mysql_query(mysql, SELECT * FROM user);
if( iRetValue != 0 )
{
printf(Query Not Executed Properly.Please Check The Syntax.\n);
}
//here goes the error message :o)
else
{
result = mysql_store_result(mysql);
num = mysql_field_count(mysql);
printf(Number Of Rows :%d\n,num );
num_fields = mysql_num_fields( result);
printf(Number Of Coloumns :%d\n,num_fields );
while ((row = mysql_fetch_row(result)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(result);
for(i = 0; i num_fields; i++)
{
printf([%.*s] \t, (int) lengths[i], row[i] ? row[i] : NULL);
}
printf(\n);
}
}
}
}
I would appreciate if you guys can help. thanks.
Elizabeth
--
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 Server Parameter (v. 4.0.20-pc-linux-i686)
I'm working on fine tuning the Server Parameters for a machine (with 2 GB of RAM). The database has a fair number of tables. Many of them are full of blobs. The version of our production MySQL is: 4.0.20-pc-linux-i686. In trying to decide on some of the buffer variables I've seen some very desparate choices with a couple of them. They are: read_buff_size, sort_buffer_size, read_rnd_buffer_size. Many people have these set rather high (like 128M+). But I read somewhere that these settings are PER CONNECTION and not for the server itself. If that is so - shouldn't they be more like 8M, 8M 6M ?? Are there other variables that are PER CONNECTION and not for the whole server that I need to re-think? thanks, erin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server Parameter (v. 4.0.20-pc-linux-i686)
Yes you are right, these are Thread specific settings, leave them at 1M or 2M, atleast that's what I set on our productions systems, else you risk at running out of memory under high loads, and it works great, the other parameters as cited in the book High performance Mysql , by Jeremy Zawodny says this min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool net_buffer Hope thsi helps Kishore Jalleda On 6/23/05, erin oneill [EMAIL PROTECTED] wrote: I'm working on fine tuning the Server Parameters for a machine (with 2 GB of RAM). The database has a fair number of tables. Many of them are full of blobs. The version of our production MySQL is: 4.0.20-pc-linux-i686. In trying to decide on some of the buffer variables I've seen some very desparate choices with a couple of them. They are: read_buff_size, sort_buffer_size, read_rnd_buffer_size. Many people have these set rather high (like 128M+). But I read somewhere that these settings are PER CONNECTION and not for the server itself. If that is so - shouldn't they be more like 8M, 8M 6M ?? Are there other variables that are PER CONNECTION and not for the whole server that I need to re-think? thanks, erin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server Parameter (v. 4.0.20-pc-linux-i686)
Since I can't get the book until this evening ... In most sample my.cnf files online I see the following setup: [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer=16M write_buffer = 16M If these are per thread -- aren't they a bit high? thanks. erin --- Kishore Jalleda [EMAIL PROTECTED] wrote: Yes you are right, these are Thread specific settings, leave them at 1M or 2M, atleast that's what I set on our productions systems, else you risk at running out of memory under high loads, and it works great, the other parameters as cited in the book High performance Mysql , by Jeremy Zawodny says this min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool net_buffer Hope thsi helps Kishore Jalleda On 6/23/05, erin oneill [EMAIL PROTECTED] wrote: I'm working on fine tuning the Server Parameters for a machine (with 2 GB of RAM). The database has a fair number of tables. Many of them are full of blobs. The version of our production MySQL is: 4.0.20-pc-linux-i686. In trying to decide on some of the buffer variables I've seen some very desparate choices with a couple of them. They are: read_buff_size, sort_buffer_size, read_rnd_buffer_size. Many people have these set rather high (like 128M+). But I read somewhere that these settings are PER CONNECTION and not for the server itself. If that is so - shouldn't they be more like 8M, 8M 6M ?? Are there other variables that are PER CONNECTION and not for the whole server that I need to re-think? thanks, erin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Discover Yahoo! Get on-the-go sports scores, stock quotes, news and more. Check it out! http://discover.yahoo.com/mobile.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: how to add comments to an existing table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 How would I add a comment to rows in an existing table, if I am not changing the row definition? This is for mysql 4.1 and 5.0. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCuxBsikQgpVn8xrARAvsRAJ0YrVwCPorS2914+jwLA9sWKt7gYQCglFo+ ojh1r5Skifmg83MZAcVonO4= =2WLs -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: how to add comments to an existing table
James Black [EMAIL PROTECTED] wrote on 06/23/2005 03:41:32 PM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 How would I add a comment to rows in an existing table, if I am not changing the row definition? This is for mysql 4.1 and 5.0. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCuxBsikQgpVn8xrARAvsRAJ0YrVwCPorS2914+jwLA9sWKt7gYQCglFo+ ojh1r5Skifmg83MZAcVonO4= =2WLs -END PGP SIGNATURE- You still need to ALTER TABLE and use the CHANGE predicate. You are CHANGE-ing a column into it's own definition, almost. The definition is going to be different because you are adding a comment to the row. mysqlshow create table sampleresult \G *** 1. row *** Table: sampleresult Create Table: CREATE TABLE `sampleresult` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `Report_ID` int(11) unsigned default NULL, `TestGroup` varchar(30) NOT NULL default '', `TestKey` varchar(30) NOT NULL default '', `Value` decimal(11,4) NOT NULL default '0.', `Units` varchar(10) NOT NULL default '', `AnalyzedByPerson_ID` int(11) unsigned default NULL, PRIMARY KEY (`ID`), ) Now let's say I wanted to add a comment to the TestGroup field (all one big statement, ignore the message wrapping): ALTER TABLE sampleresult CHANGE TestGroup TestGroup varchar(30) NOT NULL default '' COMMENT 'column comment here'; I am not sure about MyISAM but when I do this on Innodb, it creates a hidden table with the new definition, populates the new table with the old data, then swaps names. I found this out when my disk ran out of room during an ALTER TABLE (using per-file tablespaces) because someone had put an 8GB backup file on there and didn't clear it off. With all of the other info in that disk (including the backup file), I only had 256MB of free space to work with and the table I wanted to alter took up about 450MB. The warning is: make sure you have enough room for a copy of your table before you begin to alter it. Also because the ALTER is going to copy the data anyway, why make it do it once for EACH COLUMN? Add comments to all of the columns that need it in a single statement by separating your CHANGE predicates with commas ALTER TABLE tablename CHANGE field1 ..., CHANGE field2 ..., CHANGE field3 ..., etc... That way you take care of it all at once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Indexing not working
Selon Michael Stassen [EMAIL PROTECTED]: The PK is not used, nor should it be. Look at the EXPLAIN output. Hi, i hate the confusion people do between the primary key and the automatic index on the PK. Primary key is a generic concept for all databases. It assumes unicity and managed data insertion. It's a physical notion. When i say using PK, i mean using PK, not the PK index. This is a sequential reading of all data pages, one by one. I always here saying, it's a full table scan ? What does this mean ? this means that the rdbms doesn't use a rowid found in the index leaf pages to access randomly (hash) to data pages. mysql create table ordered (a int auto_increment primary key,b varchar(10)); mysql insert into ordered(b) values(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); mysql select * from ordered; ++--+ | a | b| ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | | 6 | NULL | | 7 | NULL | | 8 | NULL | | 9 | NULL | | 10 | NULL | ++--+ 10 rows in set (0.02 sec) This is an FTS (using the primary key, not the PK Index). Or if you want, there no index fast scan (or full scan) before data reading. if i'm wrong, execuse my ugnorance. That can be. Mathias Let me rewrite the query with explicit joins, and reformat it for readability: SELECT long list of columns FROM qb_test_result JOIN qb_test_result_details ON qb_test_result.id = qb_test_result_details.resultId JOIN qb_test ON qb_test_result.testId = qb_test.id JOIN qb_question ON qb_test_result_details.questionId = qb_question.id WHERE qb_test.companyId =1 AND qb_test.author = '2'; There are no restrictions on rows from qb_test_result in the WHERE clause, so every row matches. Hence, a full table scan of qb_test_result is required, and no index will be used. Adding indexes to qb_test_result won't help. There is an index on (companyId,author) in qb_test which might have helped, but apparently the optimizer decided that there would be more matching rows in qb_test than the total number of rows in qb_test_result. I do notice that author is an INT, but the query compares it to the string '2'. I doubt that confused the optimizer, but it should be fixed anyway. Change that comparison to AND qb_test.author = 2; Michael Selon Sajith A [EMAIL PROTECTED]: I was trying to analyze a query that was taking almost 4 seconds to execute. While trying to create additional indexes - found that the query is not using any index from table qb_test_result . The type returned is ALL for qb_test_result . I have given the tables and query below. It would have been a help if some one could throw some light on why this is behaving so.. I tried to read the mysql manual and follow the steps given there. snip EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS testId, qb_test.title testName, qb_question.marks, qb_test.passrate, qb_test_result.marks testMark, qb_test_result.percentage testPercentage, qb_test_result.startTime, qb_test_result.endTime, qb_test_result.status FROM qb_test_result, qb_test_result_details, qb_test, qb_question WHERE qb_test_result.id = qb_test_result_details.resultId AND qb_test_result.testId = qb_test.id AND qb_test.companyId =1 AND qb_test.author = '2' AND qb_test_result_details.questionId = qb_question.id +++---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+--+-+---+--+-+ | qb_test_result | ALL| PRIMARY,testStudent | NULL | NULL | NULL | 2494 | | | qb_test_result_details | ref| resultId | resultId | 4 | qb_test_result.id | 45 | | | qb_test| eq_ref | PRIMARY,companyAuthor | PRIMARY | 2 | qb_test_result.testId |1 | Using where | | qb_question| eq_ref | PRIMARY | PRIMARY | 4 | qb_test_result_details.questionId |1 | | +++---+--+-+---+--+-+ Thank you Sajith A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication general question
What type of drives to you have on your system? That is often more important than CPU speed. My guess is that there are nightly maintenance crons slowing down disk access. I have never monitored replication via the seconds-behind-master function as we do not use 4.1, so I can't speak for how accurate it is.. Atle - Flying Crocodile Inc, Unix Systems Administrator On Thu, 23 Jun 2005 [EMAIL PROTECTED] wrote: I have two mysql boxes setup. Fast machines, I think they are dual 3ghz with boat loads of ram. They are not real busy servers but they have some pretty good sized tables, one of them with a few million rows. My question is, I have Nagios setup to monitor the seconds behind master on the backup server. Usually the boxes are pretty current, within ten or twenty seconds. Other times though they seem to get way behind, like I just bumped the nagios warning email level up to 600 seconds. It doesn't seem to have anything to do really with usage because it usually happens in the middle of the night. Does Mysql do re-indexing or something? What could I do to figure out why the replicatant box is getting so far behind? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server Parameter (v. 4.0.20-pc-linux-i686)
Yes they are very high, also key_buffer_size is a global parameter shared by all threads, it is recommended to be around 25% of your RAM, I would suggest since you have 2GB ram the following key_buffer_size = 500M ( greatly improves speed ) sort_buffer_size= 2M read_buffer_size=2M write_buffer_size = 2M and for the section myisamck in my.cnf which is used only for table analysis and repair (that means when you run mysiasmchk) Note: myisamchk uses no more memory than you specify , you could use these key_buffer = 256M sort_buffer = 256M read_buffer=2M write_buffer = 2M Key_Buffer* is always global, I guess in this case( myisamchk) sort_buffer is also global , also you have couple of chapters which might interest you from that book online at http://dev.mysql.com/books/hpmysql-excerpts/ch06.html http://dev.mysql.com/books/hpmysql-excerpts/ch07.html Kishore Jalleda On 6/23/05, erin oneill [EMAIL PROTECTED] wrote: Since I can't get the book until this evening ... In most sample my.cnf files online I see the following setup: [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer=16M write_buffer = 16M If these are per thread -- aren't they a bit high? thanks. erin --- Kishore Jalleda [EMAIL PROTECTED] wrote: Yes you are right, these are Thread specific settings, leave them at 1M or 2M, atleast that's what I set on our productions systems, else you risk at running out of memory under high loads, and it works great, the other parameters as cited in the book High performance Mysql , by Jeremy Zawodny says this min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool net_buffer Hope thsi helps Kishore Jalleda On 6/23/05, erin oneill [EMAIL PROTECTED] wrote: I'm working on fine tuning the Server Parameters for a machine (with 2 GB of RAM). The database has a fair number of tables. Many of them are full of blobs. The version of our production MySQL is: 4.0.20-pc-linux-i686. In trying to decide on some of the buffer variables I've seen some very desparate choices with a couple of them. They are: read_buff_size, sort_buffer_size, read_rnd_buffer_size. Many people have these set rather high (like 128M+). But I read somewhere that these settings are PER CONNECTION and not for the server itself. If that is so - shouldn't they be more like 8M, 8M 6M ?? Are there other variables that are PER CONNECTION and not for the whole server that I need to re-think? thanks, erin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Discover Yahoo! Get on-the-go sports scores, stock quotes, news and more. Check it out! http://discover.yahoo.com/mobile.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to SELECT something (CONCAT) and search the field
Hey All- Got a fun question - I hit the manual but not much luck on my question. I want to combine 2 fields and then search them SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', last_name) LIKE '%$user%' Does this make sense? The CONCAT function was the closest I found to try and do what I want to do. I alread tried this: SELECT concat(first_name, ' ', last_name) as fullname FROM user... This did not work. If anyone has any ideas on how to search for users when the first_name and last_name fields are broken up I'm all ears! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MAC OS X backup after crash
Hi everyone, I just subscribed to the list and some of the articles are very helpful. I'm facing some problem on my mysql database server. One of our Raid drives crashed and we had to backup the whole data to another drive (only files), we did no mysqldump. Now I want to restore a certain database from the crashed drive to the new server. I compressed it and uncompressed it in the data folder of the new server but the server only sees the database but no info. The 1st dbase was on MAC 10.1.5 and the current one is one 10.3. Any help please. Alex __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
select first_name, lastname from user where first_name like '%$user%' or last_name like '%$user%' ; --- Matt Babineau [EMAIL PROTECTED] wrote: Hey All- Got a fun question - I hit the manual but not much luck on my question. I want to combine 2 fields and then search them SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', last_name) LIKE '%$user%' Does this make sense? The CONCAT function was the closest I found to try and do what I want to do. I alread tried this: SELECT concat(first_name, ' ', last_name) as fullname FROM user... This did not work. If anyone has any ideas on how to search for users when the first_name and last_name fields are broken up I'm all ears! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,' ',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.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: How to SELECT something (CONCAT) and search the field
sorry for the first select (bad copy of a string 'lastname'):
mysql select concat(firstname,' ',lastname) from names where concat(firstname,'
',lastname) like 'Jean Dupond%';
++
| concat(firstname,' ',lastname) |
++
| Jean Dupond|
++
1 row in set (0.00 sec)
mathias
Selon [EMAIL PROTECTED]:
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.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]
Re: replication general question
I don't think network latency would be an issue. This is within a protected
network dmz so it has it's own switch.
Here is the nagios script stuff. Might be more than what you need but let me
know if you are able to use some of it.
I have two on the server. first, I have:
[EMAIL PROTECTED] jabbott]# more secondsBehind.sh
#!/bin/sh
mysql -pMYSECRET -e show slave status\G | grep Seconds
Then I have this that I run in the rc.local. This sets up a port that listens
for a connection on port 5151. I have hole open in my firewall into my dmz for
port 5151
[EMAIL PROTECTED] jabbott]# more socket.pl
#!/usr/bin/perl
use IO::Socket;
$server_port = 5151;
$server = IO::Socket::INET-new(LocalPort = $server_port,
Type = SOCK_STREAM,
Reuse = 1,
Listen= 10)
or die Could not be a tcp server on port $server_port : [EMAIL PROTECTED];
while ($client = $server-accept ()) {
my $sysArg = `/home/jabbott/secondsBehind.sh`;
# uncomment the next line for debugging
print $client is the new connection\n\n;
print $client $sysArg\n;
print connect \n;
close ($client);
}
close ($server);
Then, on the Nagios side I have this:
$ cat /usr/lib/nagios/plugins/mysql-replication-lag.pl
#!/usr/bin/perl -w
use strict;
use lib nagios/plugins ;
use utils qw($TIMEOUT %ERRORS);
use IO::Socket;
$ENV{'PATH'}='';
$ENV{'BASH_ENV'}='';
$ENV{'ENV'}='';
my ($ip_address,$port,$warn,$critical) = @ARGV;
# Just in case of problems, let's not hang Nagios
$SIG{'ALRM'} = sub {
print No Answer from Client\n;
exit $ERRORS{UNKNOWN};
};
alarm($TIMEOUT);
my $sock = new IO::Socket::INET(
PeerAddr = $ip_address,
PeerPort = $port,
Proto= 'tcp',
);
unless ($sock) {
print Socket could not be created. Reason: $!\n;
exit $ERRORS{'UNKNOWN'};
}
my $result = $sock || Could not read socket\n;
close($sock);
alarm(0);
print $result;
unless ($result =~ /^\s*Seconds_Behind_Master:\s*/i) {
exit $ERRORS{'UNKNOWN'};
}
$result =~ s/\D//g;
exit $ERRORS{'CRITICAL'} if ($result$critical);
exit $ERRORS{'WARNING'} if ($result$warn);
exit $ERRORS{'OK'}
1;
__END__
On Thu, 23 Jun 2005, James Green wrote:
Checked for network latency? I have replication running on similar
hardware hooked up to the same switch, and have never seen it rise above
0 seconds behind.
Not that I check often, I have no need to...
Is your nagios script open for public use - I was about to have to write
something for this task myself.
Thanks,
James
On 23/6/2005, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
I have two mysql boxes setup. Fast machines, I think they are dual 3ghz
with boat loads of ram. They are not real busy servers but they have some
pretty good sized tables, one of them with a few million rows.
My question is, I have Nagios setup to monitor the seconds behind master on
the backup server. Usually the boxes are pretty current, within ten or
twenty seconds. Other times though they seem to get way behind, like I just
bumped the nagios warning email level up to 600 seconds. It doesn't seem to
have anything to do really with usage because it usually happens in the
middle of the night. Does Mysql do re-indexing or something? What could I
do to figure out why the replicatant box is getting so far behind?
--ja
--
--
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 SELECT something (CONCAT) and search the field
the substring will only work as long as you don't have spaces in the first
and last name columns
'Billy Ray' Smith and John 'Von Hoenhiem' would cause problems
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 23, 2005 4:05 PM
To: Matt Babineau
Cc: mysql@lists.mysql.com
Subject: Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather
than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.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]
Character Sets in 3.23.58
Hello, We are running mySQL 3.23.58 on a RedHat server. We have a database that needs to store Chinese (big5) and Vietnamese (Unicode-8 should work)how do I get it to store alternative character sets? The docs online only relate to ver 4so the character set attribute doesn't work. Any help would be appreciatedI'm new to this multiple language thing. Thanks! -Andy -- -- Andrew Mull Tower Communications, LLC 102 Carmen Drive Blandon, PA 19510 Work: 610-926-9734 Cell: 484-794-9433 [EMAIL PROTECTED] www.tower-communications.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to SELECT something (CONCAT) and search the field
yes in therory. But practicaly, you always have business rules and data
knowledge without what you can do nothing.
so the substring must be constructed according to data.
Mathias
Selon Ben Kutsch [EMAIL PROTECTED]:
the substring will only work as long as you don't have spaces in the first
and last name columns
'Billy Ray' Smith and John 'Von Hoenhiem' would cause problems
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 23, 2005 4:05 PM
To: Matt Babineau
Cc: mysql@lists.mysql.com
Subject: Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather
than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.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]
Extended insert syntax and replication
I have two servers doing replication for logs. When I do extended insert
syntax on the master to combine multiple log entries, the slave complains
about duplicate primary key numbers, even though my inserts don't set the
primary key and the primary key is auto_increment.
So any time I use extended insert syntax, my replication breaks with:
Duplicate entry '2835610' for key 1 on query. Default database:
'AppServerLog'. Query: 'INSERT DELAYED INTO AppServerLog.Details (SessionID,
FunctionCallID, DetailLevel, Tag, DateTime, SourceFileName,
SourceLineNumber, Data) VALUES
(361019539513084542,0,'Low',23,20050623142238,'',0,'Detected incoming call')
Here are the tables that cause this behavior. Replication only breaks when
I use extended insert syntax. What I notice in the slave is that it seems
that it is doing the inserts out of order.
CREATE TABLE `Details` (
`ID` bigint(20) NOT NULL auto_increment,
`SessionID` bigint(20) NOT NULL default '0',
`FunctionCallID` bigint(20) NOT NULL default '0',
`DetailLevel` enum('Error','Warn','Low','High') NOT NULL default 'Error',
`Tag` int(11) NOT NULL default '0',
`DateTime` datetime NOT NULL default '-00-00 00:00:00',
`SourceFileName` varchar(100) NOT NULL default '',
`SourceLineNumber` int(11) NOT NULL default '0',
`Data` varchar(200) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `FunctionCalls` (
`Sequence` int(11) NOT NULL auto_increment,
`ServerName` varchar(32) NOT NULL default '',
`SessionID` bigint(20) NOT NULL default '0',
`ProcessID` int(11) NOT NULL default '0',
`ThreadID` int(11) NOT NULL default '0',
`FunctionName` varchar(64) NOT NULL default '',
`FunctionVersion` int(11) NOT NULL default '0',
`CalledDateTime` datetime NOT NULL default '-00-00 00:00:00',
`FinishedDateTime` datetime NOT NULL default '-00-00 00:00:00',
`Exception` tinyint(4) NOT NULL default '0',
`ID` bigint(20) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Session` (
`Sequence` int(11) NOT NULL auto_increment,
`IP` varchar(24) NOT NULL default '',
`Identity` varchar(64) NOT NULL default '',
`ProgramName` varchar(32) NOT NULL default '',
`ProgramSessionID` bigint(20) NOT NULL default '0',
`Established` datetime NOT NULL default '-00-00 00:00:00',
`ID` bigint(20) NOT NULL default '0',
`AppServerNumber` int(11) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thanks for the help
Thanks for the help, I'll give some of these examples a try~!!! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Extended insert syntax and replication
Hi,
this is a perl script converter for inserts to simple form. if you work from a
mysqldump, you can try it to see if the converted inserts do not genrate errors
:
http://platon.sk/cvs/cvs.php/scripts/perl/mysql/mysqldump-convert.pl
Mathias
Selon Jeremiah Gowdy [EMAIL PROTECTED]:
I have two servers doing replication for logs. When I do extended insert
syntax on the master to combine multiple log entries, the slave complains
about duplicate primary key numbers, even though my inserts don't set the
primary key and the primary key is auto_increment.
So any time I use extended insert syntax, my replication breaks with:
Duplicate entry '2835610' for key 1 on query. Default database:
'AppServerLog'. Query: 'INSERT DELAYED INTO AppServerLog.Details (SessionID,
FunctionCallID, DetailLevel, Tag, DateTime, SourceFileName,
SourceLineNumber, Data) VALUES
(361019539513084542,0,'Low',23,20050623142238,'',0,'Detected incoming call')
Here are the tables that cause this behavior. Replication only breaks when
I use extended insert syntax. What I notice in the slave is that it seems
that it is doing the inserts out of order.
CREATE TABLE `Details` (
`ID` bigint(20) NOT NULL auto_increment,
`SessionID` bigint(20) NOT NULL default '0',
`FunctionCallID` bigint(20) NOT NULL default '0',
`DetailLevel` enum('Error','Warn','Low','High') NOT NULL default 'Error',
`Tag` int(11) NOT NULL default '0',
`DateTime` datetime NOT NULL default '-00-00 00:00:00',
`SourceFileName` varchar(100) NOT NULL default '',
`SourceLineNumber` int(11) NOT NULL default '0',
`Data` varchar(200) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `FunctionCalls` (
`Sequence` int(11) NOT NULL auto_increment,
`ServerName` varchar(32) NOT NULL default '',
`SessionID` bigint(20) NOT NULL default '0',
`ProcessID` int(11) NOT NULL default '0',
`ThreadID` int(11) NOT NULL default '0',
`FunctionName` varchar(64) NOT NULL default '',
`FunctionVersion` int(11) NOT NULL default '0',
`CalledDateTime` datetime NOT NULL default '-00-00 00:00:00',
`FinishedDateTime` datetime NOT NULL default '-00-00 00:00:00',
`Exception` tinyint(4) NOT NULL default '0',
`ID` bigint(20) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Session` (
`Sequence` int(11) NOT NULL auto_increment,
`IP` varchar(24) NOT NULL default '',
`Identity` varchar(64) NOT NULL default '',
`ProgramName` varchar(32) NOT NULL default '',
`ProgramSessionID` bigint(20) NOT NULL default '0',
`Established` datetime NOT NULL default '-00-00 00:00:00',
`ID` bigint(20) NOT NULL default '0',
`AppServerNumber` int(11) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
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: Extended insert syntax and replication
The inserts succeed on the master, so the problem isn't my SQL syntax. They
are legal extended inserts. It's just that the replication slave seems to
insert them out of order and screw up the auto-increment primary key.
- Original Message -
From: [EMAIL PROTECTED]
To: Jeremiah Gowdy [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, June 23, 2005 2:51 PM
Subject: Re: Extended insert syntax and replication
Hi,
this is a perl script converter for inserts to simple form. if you work
from a
mysqldump, you can try it to see if the converted inserts do not genrate
errors
:
http://platon.sk/cvs/cvs.php/scripts/perl/mysql/mysqldump-convert.pl
Mathias
Selon Jeremiah Gowdy [EMAIL PROTECTED]:
I have two servers doing replication for logs. When I do extended insert
syntax on the master to combine multiple log entries, the slave complains
about duplicate primary key numbers, even though my inserts don't set the
primary key and the primary key is auto_increment.
So any time I use extended insert syntax, my replication breaks with:
Duplicate entry '2835610' for key 1 on query. Default database:
'AppServerLog'. Query: 'INSERT DELAYED INTO AppServerLog.Details
(SessionID,
FunctionCallID, DetailLevel, Tag, DateTime, SourceFileName,
SourceLineNumber, Data) VALUES
(361019539513084542,0,'Low',23,20050623142238,'',0,'Detected incoming
call')
Here are the tables that cause this behavior. Replication only breaks
when
I use extended insert syntax. What I notice in the slave is that it
seems
that it is doing the inserts out of order.
CREATE TABLE `Details` (
`ID` bigint(20) NOT NULL auto_increment,
`SessionID` bigint(20) NOT NULL default '0',
`FunctionCallID` bigint(20) NOT NULL default '0',
`DetailLevel` enum('Error','Warn','Low','High') NOT NULL default
'Error',
`Tag` int(11) NOT NULL default '0',
`DateTime` datetime NOT NULL default '-00-00 00:00:00',
`SourceFileName` varchar(100) NOT NULL default '',
`SourceLineNumber` int(11) NOT NULL default '0',
`Data` varchar(200) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `FunctionCalls` (
`Sequence` int(11) NOT NULL auto_increment,
`ServerName` varchar(32) NOT NULL default '',
`SessionID` bigint(20) NOT NULL default '0',
`ProcessID` int(11) NOT NULL default '0',
`ThreadID` int(11) NOT NULL default '0',
`FunctionName` varchar(64) NOT NULL default '',
`FunctionVersion` int(11) NOT NULL default '0',
`CalledDateTime` datetime NOT NULL default '-00-00 00:00:00',
`FinishedDateTime` datetime NOT NULL default '-00-00 00:00:00',
`Exception` tinyint(4) NOT NULL default '0',
`ID` bigint(20) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Session` (
`Sequence` int(11) NOT NULL auto_increment,
`IP` varchar(24) NOT NULL default '',
`Identity` varchar(64) NOT NULL default '',
`ProgramName` varchar(32) NOT NULL default '',
`ProgramSessionID` bigint(20) NOT NULL default '0',
`Established` datetime NOT NULL default '-00-00 00:00:00',
`ID` bigint(20) NOT NULL default '0',
`AppServerNumber` int(11) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
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: Extended insert syntax and replication
see this for troubleshootings :
http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/Replication_Problems.html
hope that helps
Mathias
Selon Jeremiah Gowdy [EMAIL PROTECTED]:
The inserts succeed on the master, so the problem isn't my SQL syntax. They
are legal extended inserts. It's just that the replication slave seems to
insert them out of order and screw up the auto-increment primary key.
- Original Message -
From: [EMAIL PROTECTED]
To: Jeremiah Gowdy [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, June 23, 2005 2:51 PM
Subject: Re: Extended insert syntax and replication
Hi,
this is a perl script converter for inserts to simple form. if you work
from a
mysqldump, you can try it to see if the converted inserts do not genrate
errors
:
http://platon.sk/cvs/cvs.php/scripts/perl/mysql/mysqldump-convert.pl
Mathias
Selon Jeremiah Gowdy [EMAIL PROTECTED]:
I have two servers doing replication for logs. When I do extended insert
syntax on the master to combine multiple log entries, the slave complains
about duplicate primary key numbers, even though my inserts don't set the
primary key and the primary key is auto_increment.
So any time I use extended insert syntax, my replication breaks with:
Duplicate entry '2835610' for key 1 on query. Default database:
'AppServerLog'. Query: 'INSERT DELAYED INTO AppServerLog.Details
(SessionID,
FunctionCallID, DetailLevel, Tag, DateTime, SourceFileName,
SourceLineNumber, Data) VALUES
(361019539513084542,0,'Low',23,20050623142238,'',0,'Detected incoming
call')
Here are the tables that cause this behavior. Replication only breaks
when
I use extended insert syntax. What I notice in the slave is that it
seems
that it is doing the inserts out of order.
CREATE TABLE `Details` (
`ID` bigint(20) NOT NULL auto_increment,
`SessionID` bigint(20) NOT NULL default '0',
`FunctionCallID` bigint(20) NOT NULL default '0',
`DetailLevel` enum('Error','Warn','Low','High') NOT NULL default
'Error',
`Tag` int(11) NOT NULL default '0',
`DateTime` datetime NOT NULL default '-00-00 00:00:00',
`SourceFileName` varchar(100) NOT NULL default '',
`SourceLineNumber` int(11) NOT NULL default '0',
`Data` varchar(200) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `FunctionCalls` (
`Sequence` int(11) NOT NULL auto_increment,
`ServerName` varchar(32) NOT NULL default '',
`SessionID` bigint(20) NOT NULL default '0',
`ProcessID` int(11) NOT NULL default '0',
`ThreadID` int(11) NOT NULL default '0',
`FunctionName` varchar(64) NOT NULL default '',
`FunctionVersion` int(11) NOT NULL default '0',
`CalledDateTime` datetime NOT NULL default '-00-00 00:00:00',
`FinishedDateTime` datetime NOT NULL default '-00-00 00:00:00',
`Exception` tinyint(4) NOT NULL default '0',
`ID` bigint(20) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Session` (
`Sequence` int(11) NOT NULL auto_increment,
`IP` varchar(24) NOT NULL default '',
`Identity` varchar(64) NOT NULL default '',
`ProgramName` varchar(32) NOT NULL default '',
`ProgramSessionID` bigint(20) NOT NULL default '0',
`Established` datetime NOT NULL default '-00-00 00:00:00',
`ID` bigint(20) NOT NULL default '0',
`AppServerNumber` int(11) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
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: connection error from c application
Hello.
I've compiled your code using libraries and includes from
MySQL-4.1.6 (yes, my testing environment isn'tperfect :) and
successfully connected to MySQL-4.1.10. Old passwords was off,
so I used a 4.1. authorization protocol.
Elizabeth Bonifacio [EMAIL PROTECTED] wrote:
Dear All,
I'm new into mysql and has been encountering huge problem in
connecting to the database from the c application. The code execute
with an error message :
Failed to connect to database: Error:
Client does not support authentication protocol requested by server;
consider upgrading MySQL client
Is this a bug?
I'm running the MySql server version 4.1 with server and client both
on same computer running on windows XP.
I have no problem connecting to the server using root with a windfall
password but I cannot connect from the c application below.
I have only one user in the server (root,windfall) and has been
successful in accessing mysql from the client side except when
connecting from a c application.
here is the application which I compile using Visual C++ compiler:
#include stdio.h
#include winsock.h
#include iostream.h
#include mysql.h
int main()
{
MYSQL mysql;
MYSQL_ROW row;
MYSQL_RES *result;
unsigned int num_fields;
unsigned int i;
int num =3D 0;
int iRetValue =3D 0;
mysql_init(mysql);
//printf(%s,mysql );
if (!mysql_real_connect(mysql,localhost,root,windfall,mysql,3306,
NULL,0))
{
fprintf(stderr, Failed to connect to database: Error: \n%s\n,
mysql_error(mysql));
}
else
{
printf(SUCCESS\n);
iRetValue =3D mysql_query(mysql, SELECT * FROM user);
if( iRetValue !=3D 0 )
{
printf(Query Not Executed Properly.Please Check The Syntax.\n);
}
//here goes the error message :o)
else
{
result =3D mysql_store_result(mysql);
num =3D mysql_field_count(mysql);
printf(Number Of Rows :%d\n,num );
num_fields =3D mysql_num_fields( result);
printf(Number Of Coloumns :%d\n,num_fields );
while ((row =3D mysql_fetch_row(result)))
{
unsigned long *lengths;
lengths =3D mysql_fetch_lengths(result);
for(i =3D 0; i num_fields; i++)
{
printf([%.*s] \t, (int) lengths[i], row[i] ? row[i] : NULL);
}
printf(\n);
}
}
}
}
I would appreciate if you guys can help. thanks.
Elizabeth
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication general question
Hello. I haven't heard about periodical MySQL jobs, but Unix boxes usually have some midnight cron jobs (updating of locate database for example). [EMAIL PROTECTED] wrote: I have two mysql boxes setup. Fast machines, I think they are dual 3ghz with boat loads of ram. They are not real busy servers but they have some pretty good sized tables, one of them with a few million rows. My question is, I have Nagios setup to monitor the seconds behind master on the backup server. Usually the boxes are pretty current, within ten or twenty seconds. Other times though they seem to get way behind, like I just bumped the nagios warning email level up to 600 seconds. It doesn't seem to have anything to do really with usage because it usually happens in the middle of the night. Does Mysql do re-indexing or something? What could I do to figure out why the replicatant box is getting so far behind? --ja -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compiling + making source for 5.7 on alpha
Can anyone advise please, tried binary installation but other users have reported encountering similar problems so I thought I'd try a source installation. Adjusted the pentiumpro flags out and configure reported no errors -when I use make I get *** no targets. stop. The makefile is in there, am I overlooking something elementary? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.11/26 - Release Date: 22/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
I do this all the time and it works flawlessly. Just like your example and even
more extreme. I use this technique to provide search mechamisms for my
applications. ex,
SELECT ID
FROM table
WHERE concat(field1, field2, field3,...{all the fields in the table}) Like
'%searchstring%';
This works great for me. Is 'user' your actually table name and is it possible
that the table name is being confused with the 'user' table in the MySQL
database?
Good Luck
Matt Babineau [EMAIL PROTECTED] 6/23/05 1:50:12 PM
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
could a Trigger execute a outside command?
Hi, I am using MySql on Mac OS X. I am wondering if I could execute a command (shell, perl) by a Trigger. I read the manual and it seems this is impossible. ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
While the query WHERE expr LIKE '%$user%' works without fail, it can
not use an index, and thus on large tables will be exceedingly slow.
mysql explain select last, first from users where concat(last,first)
like '%user%'\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: users
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.01 sec)
Therefor, where it's possible, I would suggest adding a fulltext index
on (first, last), or what ever columns you need. This will handle the
concatenation of these two columns inside the index :)
mysql alter table users add fulltext index (first, last);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
Then use the query WHERE MATCH expr AGAINST ('%$user%').. like this...
mysql explain select first, last from users where match (first,last)
against ('%user%')\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: users
type: fulltext
possible_keys: first_2
key: first_2
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.01 sec)
Hope that helps,
~Deva
Ed Reed wrote:
I do this all the time and it works flawlessly. Just like your example and even more extreme. I use this technique to provide search mechamisms for my applications. ex,
SELECT ID
FROM table
WHERE concat(field1, field2, field3,...{all the fields in the table}) Like '%searchstring%';
This works great for me. Is 'user' your actually table name and is it possible that the table name is being confused with the 'user' table in the MySQL database?
Good Luck
Matt Babineau [EMAIL PROTECTED] 6/23/05 1:50:12 PM
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
