UNIONS 'Got error 12 from storage engine'
Have a query that consists of 10 unions. For a period of time it will constantly return with an error 12. Moments later it might actually work once or twice. I can find nothing in any logs or whatever. The odd thing is that when it isn't working, simply reducing the unions to 8 or less returns with no problem. A) Very little to be read about on the net for Error 12 B) What little there is talks mostly of BDB, which is of no relevance. I've tried reproducing the problem with lots of unions, even tried joining some simple table to try to get the same error, but to no avail. I'm guess it is related to my specific schema. I'm on 4.1.11, up from 4.1.10a but that didn't fix it. Any debugging avenues suggested? I know someone is going to want an explain, and it'll have to wait until the morning. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SLOW 22million rows, 5 hour query?
I must have done a poor job at explaining this. I already have all those primary keys, etc. If you are required to do a full table scan on the table, as I am in my example, please explain to me how an index even matters in that case. There is _no_ where clause in the query so how will an index help? :) Now I have been benchmarking creating a CKEY combined key column that just basically is a concat(month_day, ',', bucket, ',', src). I made that a char(44) column and make that the primary key. Things seem faster due to use only one row for the primary key instead of 3, as well as a less complex group by. Preliminary results show the CKEY to be 50% faster on the particular query I'm using. 1) Yes, it does have to calculate floor(bucket / 3) but that is fairly in expensive call on a per-row basis, one would think. 2) I already have that index. It's a Primary Key(month_day, src, bucket). I'm still confused on how the index will speed it up on the source table side. We are NOT I/O bound. Looks more like cpu bound to me. Mysql uses 25% cpu on the solaris which is 1 entire cpu on a 4 processor machine. I'll give the order by NULL a shot Cliff Jigal van Hemert [EMAIL PROTECTED] writes: insert into new_table select month_day, floor(bucket/3) as bucket, date, src, avg(value) as value from source_table group by month_day, bucket, src; Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort Query OK, 11495208 rows affected (4 hours 47 min 21.01 sec) IMHO two things slow you down: 1) floor(bucket/3) as bucket. This means that MySQL has to calculate this value for each record to be able to use it as a grouping value. If you always use this same expression for grouping, it might be useful to have a column with the precalculated value. 2) there's only one table involved, so only one index will be used. If you had an index that contains month_day, bucket and src it would speed up things perhaps. Furthermore, it depends on the speed of the disks, your configuration, memory configuration and use how fast it will go. But explain indicates that MySQL needs to copy the data into a temporary table and use filesort to order and group things. Maybe it will help to surpress the automatic sorting that is done by MySQL because of the GROUP BY, by adding ORDER BY NULL? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SLOW 22million rows, 5 hour query?
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html Do not use DELAYED with INSERT ... SELECT. With respect to the table structure...can you explain how when you have to read every single row regardless how the structure (assuming you are going down the path of idexes) affects the query? Cliff Donny Simonton [EMAIL PROTECTED] writes: Use insert delayed, and you will cut your time in half. At least with my experience. But also how long does it actually take to run the query itself. Giving a summary explain doesn't help much. You really need a table structure that the select is using and a full explain. Donny -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 6:02 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: SLOW 22million rows, 5 hour query? On 23 Apr 2004, at 9:48 pm, [EMAIL PROTECTED] wrote: Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort The filesort is a giveaway. Can you increase the sort buffer size so that the sort can happen in memory rather than having to use a file to sort? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- 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: InnoDB tables using 90% cpu
Well I recompiled with Linuxthreads with the same result. I ran explain on both queries and the only difference is innodb looks at 31 rows where myisam looked at 23. Both are of type range. Is there anything else I can do before giving up on innodb? - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: Sasha Pachev [EMAIL PROTECTED] Cc: Cliff [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 7:14 PM Subject: Re: InnoDB tables using 90% cpu Sasha Pachev wrote: Cliff wrote: The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. Compare the output of EXPLAIN for both MyISAM and InnoDB. Also, if you have a Linux or even Windows system around, test the same query on them to see if there is any difference. I am not that familiar with FreeBSD, but from what I've seen, I would tend to use the approach when in doubt, use LinuxThreads. The advice you've got does make sense - InnoDB runs a number of background threads, so if your thread implementation is lacking, it would hurt InnoDB more than MyISAM. FreeBSD's thread library is a user-space setup. This can result in major performance issues when doing things that block. -- 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: InnoDB tables using 90% cpu
The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads. - Original Message - From: Sasha Pachev [EMAIL PROTECTED] To: Cliff [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, March 08, 2004 9:18 AM Subject: Re: InnoDB tables using 90% cpu Cliff wrote: Hi, I have a whole database I wanted to convert to InnoDB from MyISAM, but do not want to use alter table because of the problems I had last time. I made a whole dump of the table using mysqldump and changed all of the table create definitions from MyISAM to InnoDB. Theoretically this should be just like creating a new innodb table from scratch and inserting new records. However, while the MyISAM tables used ~30% of the cpu usage on a query, InnoDB runs anywhere from 50-90% depending on the query. The databases combined are approximately 200MB. Here is my cnf file: [mysqld] basedir=/mysql long_query_time=3 log-slow-queries=/tmp/slowmysql.log innodb_data_home_dir = innodb_data_file_path = /mysql/data/innodb_data:300M:autoextend set-variable = innodb_buffer_pool_size=300M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 This is mysql 4.0.18 on freebsd 4.8-STABLE. We have 1GB of ram which should be plenty to run the large queries that we are doing. Thanks in advance. 50-90% CPU vs only 30% could be actually an improvement ( less disk I/O, and relatively more time to get the data). The question is - does the query actually take less time? If not, it could be because a certain optimization available with MyISAM is not available with InnoDB. Isolate the trouble query, and do an EXPLAIN. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Major problem converting MyISAM to InnoDB
Thanks to everyone who helped. It turned out that we added an index to the table that was not in the frm file. This apparently causes innodb tables to have weird behavior, but nonetheless it is solved. Thanks! - Original Message - From: Sasha Pachev [EMAIL PROTECTED] To: Cliff [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, March 05, 2004 10:16 AM Subject: Re: Major problem converting MyISAM to InnoDB Cliff wrote: I think I may have gotten closer to the root of the problem. It seems that the field named 'comments' is the one that crashes the server when it is selected. Here is the output of the logs when I do 'select comments from allusa': 040304 23:14:52 mysqld restarted 040304 23:14:53 InnoDB: Started /mnt/disk2/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 Fatal error '_waitq_remove: Not in queue' at line ? in file /usr/src/lib/libc_r/uthread/uthread_priority_queue.c (errno = ?) Number of processes running now: 0 I searched google and it seems that error has been seen in many programs since it is a threading issue. Any ideas on what would cause this? Threading error is probably a consequence of the process getting SIGSEGV, which happens most likely because the .frm you have defines a different physical structure of the record than the one that is really there in the table space. If you access a field after comments (in the physical layout), do you crash or get garbage? -- Sasha Pachev Create online surveys at http://www.surveyz.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]
InnoDB tables using 90% cpu
Hi, I have a whole database I wanted to convert to InnoDB from MyISAM, but do not want to use alter table because of the problems I had last time. I made a whole dump of the table using mysqldump and changed all of the table create definitions from MyISAM to InnoDB. Theoretically this should be just like creating a new innodb table from scratch and inserting new records. However, while the MyISAM tables used ~30% of the cpu usage on a query, InnoDB runs anywhere from 50-90% depending on the query. The databases combined are approximately 200MB. Here is my cnf file: [mysqld] basedir=/mysql long_query_time=3 log-slow-queries=/tmp/slowmysql.log innodb_data_home_dir = innodb_data_file_path = /mysql/data/innodb_data:300M:autoextend set-variable = innodb_buffer_pool_size=300M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 This is mysql 4.0.18 on freebsd 4.8-STABLE. We have 1GB of ram which should be plenty to run the large queries that we are doing. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Major problem converting MyISAM to InnoDB
Hi, I ran CHECK TABLE with the EXTENDED option on the table and it reports everything ok: mysql check table allusa extended; +---+---+--+--+ | Table | Op| Msg_type | Msg_text | +---+---+--+--+ | bb.allusa | check | status | OK | +---+---+--+--+ 1 row in set (4.74 sec) I am running FreeBSD 4.9-STABLE. The error log did not show anything while or after the query ran: 040304 09:09:32 mysqld started 040304 9:09:33 InnoDB: Started /mnt/disk2/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 If I do a select statement inside of mysql into an outfile, the server crashes. However if I do a select on the same data to stdout, it works fine most of the time. I am having my terminal program capture the screen output from mysql to a file for each field and editing the records to a format that is easy to insert back in. As you can imagine this is less than ideal, but it is the only solution I can come up with at this point. Thanks for your help, if you need anything else let me know. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 04, 2004 12:36 AM Subject: Re: Major problem converting MyISAM to InnoDB Cliff, please run CHECK TABLE ... on your table and look if mysqld prints anything to the .err log. Send the WHOLE .err log to me. What is your operating system version? amount of changes since today. I am trying all I can to recover the data from this table because I know it is in there. I deleted the frm file and recreated it because mysql was complaining about the file being corrupt. I used the same table definition as before. I pointed the datafile for innodb What does this mean: mysql was complaining about the file being corrupt? Did it say that the .frm file was corrupt? What did it print exactly? used the same table definition as before. I pointed the datafile for innodb to the correct file that is believe to be corrupt ... What does the above mean? Are you sure that you recreated the .frm file with the right CREATE TABLE? You can use the innodb_table_monitor to print the internal data dictionary of InnoDB: http://www.innodb.com/ibman.php#InnoDB.Monitor Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html - Original Message - From: Sasha Pachev [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 04, 2004 7:18 AM Subject: Re: Major problem converting MyISAM to InnoDB Cliff wrote: Recently I tried to convert our largest table from MyISAM to InnoDB. During the process I believe there was a problem where something was corrupt along the way. It was stupid, but I did not verify that our backup system was working correctly, since I assumed it had been running as usual. It was not however. Our latest backup was from November of 2003, which is a large amount of changes since today. I am trying all I can to recover the data from this table because I know it is in there. I deleted the frm file and recreated it because mysql was complaining about the file being corrupt. I used the same table definition as before. I pointed the datafile for innodb to the correct file that is believe to be corrupt and this is what happens. I can login to mysql and execute queries like usual, however some queries are crashing mysqld and restarting it. For instance, a record with id of 10027 was one that was edited just before the table altering. If I select * where id=10027, the server restarts. I have tried using set-variable = innodb_force_recovery = 4 in my.cnf with no luck either. I know the data is in the file because I can page through it and see portions of each IDs information. Here is the error I get when the server restarts from server.err: 040303 17:49:08 mysqld ended 040303 17:49:09 mysqld started 040303 17:49:10 InnoDB: Started /mnt/disk2/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072
Re: Major problem converting MyISAM to InnoDB
- Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 04, 2004 1:24 PM Subject: Re: Major problem converting MyISAM to InnoDB Cliff, - Original Message - From: Cliff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 04, 2004 7:07 PM Subject: Re: Major problem converting MyISAM to InnoDB Hi, I ran CHECK TABLE with the EXTENDED option on the table and it reports everything ok: mysql check table allusa extended; +---+---+--+--+ | Table | Op| Msg_type | Msg_text | +---+---+--+--+ | bb.allusa | check | status | OK | +---+---+--+--+ 1 row in set (4.74 sec) hmm... then the table most probably is not corrupt inside InnoDB. I am running FreeBSD 4.9-STABLE. The error log did not show anything while or after the query ran: 040304 09:09:32 mysqld started 040304 9:09:33 InnoDB: Started /mnt/disk2/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 If I do a select statement inside of mysql into an outfile, the server crashes. However if I do a select on the same data to stdout, it works fine most of the time. I am having my terminal program capture the screen output from mysql to a file for each field and editing the records to a format that is easy to insert back in. As you can imagine this is less than ideal, but it is the only solution I can come up with at this point. Thanks for your help, if you need anything else let me know. This sounds strange. I cannot recall a case where a simple SELECT consistently crashes, while CHECK TABLE reports the table as ok! An explanation might be that the .frm file is not the right one for the table, though I do not understand how the mysql client then could work. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html This is what I was thinking. I recreated the .frm file after having the problems by creating a new innodb table with the same structure as the original. I was told by someone else the frm for myisam and innodb are the same file. I found the field names in the innodb file by catting through the data file searching, but they are very scattered obviously. I am almost positive I am not missing any fields or even that the field definitions are different because I have a table with the exact same setup. I will keep poking around to see if I can find anything new, but at this point I am almost out of ideas. Thanks for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Major problem converting MyISAM to InnoDB
I think I may have gotten closer to the root of the problem. It seems that the field named 'comments' is the one that crashes the server when it is selected. Here is the output of the logs when I do 'select comments from allusa': 040304 23:14:52 mysqld restarted 040304 23:14:53 InnoDB: Started /mnt/disk2/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 Fatal error '_waitq_remove: Not in queue' at line ? in file /usr/src/lib/libc_r/uthread/uthread_priority_queue.c (errno = ?) Number of processes running now: 0 I searched google and it seems that error has been seen in many programs since it is a threading issue. Any ideas on what would cause this? - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 04, 2004 1:24 PM Subject: Re: Major problem converting MyISAM to InnoDB Cliff, - Original Message - From: Cliff [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 04, 2004 7:07 PM Subject: Re: Major problem converting MyISAM to InnoDB Hi, I ran CHECK TABLE with the EXTENDED option on the table and it reports everything ok: mysql check table allusa extended; +---+---+--+--+ | Table | Op| Msg_type | Msg_text | +---+---+--+--+ | bb.allusa | check | status | OK | +---+---+--+--+ 1 row in set (4.74 sec) hmm... then the table most probably is not corrupt inside InnoDB. I am running FreeBSD 4.9-STABLE. The error log did not show anything while or after the query ran: 040304 09:09:32 mysqld started 040304 9:09:33 InnoDB: Started /mnt/disk2/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 If I do a select statement inside of mysql into an outfile, the server crashes. However if I do a select on the same data to stdout, it works fine most of the time. I am having my terminal program capture the screen output from mysql to a file for each field and editing the records to a format that is easy to insert back in. As you can imagine this is less than ideal, but it is the only solution I can come up with at this point. Thanks for your help, if you need anything else let me know. This sounds strange. I cannot recall a case where a simple SELECT consistently crashes, while CHECK TABLE reports the table as ok! An explanation might be that the .frm file is not the right one for the table, though I do not understand how the mysql client then could work. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- 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]
Major problem converting MyISAM to InnoDB
Recently I tried to convert our largest table from MyISAM to InnoDB. During the process I believe there was a problem where something was corrupt along the way. It was stupid, but I did not verify that our backup system was working correctly, since I assumed it had been running as usual. It was not however. Our latest backup was from November of 2003, which is a large amount of changes since today. I am trying all I can to recover the data from this table because I know it is in there. I deleted the frm file and recreated it because mysql was complaining about the file being corrupt. I used the same table definition as before. I pointed the datafile for innodb to the correct file that is believe to be corrupt and this is what happens. I can login to mysql and execute queries like usual, however some queries are crashing mysqld and restarting it. For instance, a record with id of 10027 was one that was edited just before the table altering. If I select * where id=10027, the server restarts. I have tried using set-variable = innodb_force_recovery = 4 in my.cnf with no luck either. I know the data is in the file because I can page through it and see portions of each IDs information. Here is the error I get when the server restarts from server.err: 040303 17:49:08 mysqld ended 040303 17:49:09 mysqld started 040303 17:49:10 InnoDB: Started /mnt/disk2/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Number of processes running now: 0 040303 17:49:29 mysqld restarted 040303 17:49:29 InnoDB: Started /mnt/disk2/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 As you can see I am running mysql 4.0.18. Here is the my.cnf file with innodb settings: basedir=/mysql long_query_time=3 log-slow-queries=/tmp/slowmysql.log innodb_data_home_dir = innodb_data_file_path = /mysql/data/ibdata1:10M:autoextend #innodb_data_file_path = /mysql/innodb/ib_datafile:2000M:autoextend set-variable = innodb_buffer_pool_size=300M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 #skip-innodb #innodb_force_recovery=1 This information is extremely important and any help would be greatly appreciated. If there is any tools I could possibly use to recover this is mysql is not an option that would be great. I also have tried mysqldump and it only restarts the server just like a query does. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api: core dump on mysql_real_connect
On Wed, 25 Feb 2004, Sasha Pachev wrote: where dbh is a global MYSQL structure. This code works fine on the old system. If I pull it out into it's own little test program on the new server, it also works fine. But when I put it in with the analog source code, it compiles fine but the mysql_real_connect causes a core dump when run. The most common reason for the above error is mysql.h header/libmysqlsclient.so library incompatibility. Make sure they are in sync. Except that when I put the same code into it's own little program, it works fine. i.e. I build a C program whose main does nothing but call the connect function. Compiles/runs with no coredump. Cliff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C api: core dump on mysql_real_connect
I've got on that really has me stumped ...
I've modified tha analog web stats program before to use a mysql database
before and I'm trying to do it again on a new system. I have this
function:
void db_connect(){
printf(start connect\n);
mysql_init(dbh);
if (!mysql_real_connect(dbh,localhost,usr,xxx,rdns,0,NULL,0)){
fprintf(stderr, Connection to rdns database failed\n);
exit(1);
}
printf(finish connect\n);
}
where dbh is a global MYSQL structure. This code works fine on the old
system. If I pull it out into it's own little test program on the new
server, it also works fine. But when I put it in with the analog source
code, it compiles fine but the mysql_real_connect causes a core dump when
run.
Running 4.0.17 on FreeBSD 4.9R
Any ideas?
Cliff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.14 runs CPU usage up and eventually stop
Hi, I am running 4.0.14 on a FreeBSD 4.8-STABLE machine (upgraded to CVS from last week) and I am having a problem. We run a fairly large website using PHP so there is always many connections to the database. On 4.0.12 the cpu usage never shot to over 20% and ran extremely fast. After upgrading to 4.0.14, the cpu goes anywhere between 20-60% and in many situations will not accept any database connections. After browsing the website for a few minutes, I will type mysql on the command line and it will give an error of too many connections. Does anyone know what may be causing this high load from the upgrade? Again the previous versions did not do this and ran very well. I could use 4.0.12 but I wanted to report this in case it continues in future versions. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with FULLTEXT searches
I am trying to get the fulltext search working properly as described in the
manual but some queries do not seem to be working correctly. When I run this
query:
SELECT allusa.name,title,full_text,match(title,full_text) against ('chicken
soup lemon') from recipes, bb.allusa where innid = bb.allusa.id AND
match(title,full_text) against ('chicken soup lemon') LIMIT 0, 10
It displays the results fine. Then I try this:
SELECT allusa.name,title,full_text,match(title,full_text) against ('
+chicken +soup +lemon ') from recipes, bb.allusa where innid = bb.allusa.id
AND match(title,full_text) against (' +chicken +soup +lemon ') LIMIT 0, 10
The result returned are the same as the query above...I thought the + clause
made the search do a boolean AND search, but it does not seem to be making
any difference. Is there a bug in the fulltext search? I have tried
different queries as well with the same results. I am running mysql 4.0.12
on freebsd 4.8. Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with FULLTEXT searches
Ok, I added IN BOOLEAN MODE and now it somewhat works...It is just a matter
of optimizing it now. Thanks.
- Original Message -
From: Cliff [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, June 27, 2003 8:13 AM
Subject: Problem with FULLTEXT searches
I am trying to get the fulltext search working properly as described in
the
manual but some queries do not seem to be working correctly. When I run
this
query:
SELECT allusa.name,title,full_text,match(title,full_text) against
('chicken
soup lemon') from recipes, bb.allusa where innid = bb.allusa.id AND
match(title,full_text) against ('chicken soup lemon') LIMIT 0, 10
It displays the results fine. Then I try this:
SELECT allusa.name,title,full_text,match(title,full_text) against ('
+chicken +soup +lemon ') from recipes, bb.allusa where innid =
bb.allusa.id
AND match(title,full_text) against (' +chicken +soup +lemon ') LIMIT 0, 10
The result returned are the same as the query above...I thought the +
clause
made the search do a boolean AND search, but it does not seem to be making
any difference. Is there a bug in the fulltext search? I have tried
different queries as well with the same results. I am running mysql 4.0.12
on freebsd 4.8. Thanks
--
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]
Make Test fails
Description: make test fails on create. How-To-Repeat: make test output of ./mysql-test-run --local create shown here -- CUT -- Installing Test Databases Removing Stale Files Installing Master Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/master-data --skip-innodb --skip-bdb --language=../sql/share/english/ Installing Slave Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/slave-data --skip-innodb --skip-bdb --language=../sql/share/english/ Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TEST USER SYSTEM ELAPSEDRESULT create [ fail ] Error at line 185: Result length mismatch Below are the diffs between actual and expected results: --- *** r/create.result 2003-03-20 17:41:56.0 +0300 --- r/create.reject 2003-03-20 18:45:56.0 +0300 *** *** 262,268 Table Create Table t1CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0' ! ) TYPE=HEAP drop table t1; SET SESSION table_type=gemini; SELECT @@table_type; --- 262,268 Table Create Table t1CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0' ! ) TYPE=HEAP CHARSET=latin1 drop table t1; SET SESSION table_type=gemini; SELECT @@table_type; *** *** 273,278 Table Create Table t1CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0' ! ) TYPE=MyISAM SET SESSION table_type=default; drop table t1; --- 273,278 Table Create Table t1CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0' ! ) TYPE=MyISAM CHARSET=latin1 SET SESSION table_type=default; drop table t1; --- Please follow the instructions outlined at http://www.mysql.com/doc/R/e/Reporting_mysqltest_bugs.html to find the reason to this problem and how to report this. Aborting. To continue, re-run with '--force'. Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished -- CUT -- Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Cliff Stanford Organization: organization of PR author (multiple lines) MySQL support: none Synopsis: ./mysql-test-run --local create fails Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (Source distribution) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.53a, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.53a Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 52 days 13 hours 15 min 17 sec Threads: 2 Questions: 87805227 Slow queries: 96 Opens: 15025 Flush tables: 1 Open tables: 64 Queries per second avg: 19.338 C compiler:gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) C++ compiler: g++ (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Environment: machine, os, target, libraries (multiple lines) System: Linux ih.may.be 2.4.18-18.8.0smp #1 SMP Wed Nov 13 23:11:20 EST 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Nov 25 17:05 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x2 root root 1235468 Sep 6 2002 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local' '--with-tcp-port=3307' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com
Re: Fwd: Re: Re: [Bug in UNION clause]
That is weird, I just created the same tables with the same data and it returns 49 rows containing this: | 8647 | FULL | 24 Henry Guesthouse| San Francisco | USA | San Francisco | CA| CA| http://www.24Henry.com | FULL | | 65.00 | 109.00 |1 | 24 Henry Guesthouse is located just a few short bltocks from the heart of the Castro District. There are 5 beautifully furnished rooms in this Victorian home, dating from the 1880's. | | | | 8647 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 3082 | FULL | Amsterdam Hotel| San Francisco | USA | San Francisco | CA| CA| http://www.amsterdamhotel.com | FULL | | 99.00 | 149.00 |1 | Located on Nob Hill. Quality accommodations and friendly service provided at modest rates. A little bit of Europe in America. Near Union Square, Financial District, cable car. | yes | no | no | 3082 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 877 | FULL | Annie's Cottage| San Francisco | USA | San Francisco | CA| CA| http://anniescottage.com | FULL | | 140.00 |0.00 |1 | Guestroom with private entrance, private bath, sitting area, deck, refrigerator/microwave. Country hideaway in the middle of San Francisco. Furnished with antiques. | | no | no | 877 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 20353 | FULL | Artists Inn| San Francisco | USA | San Francisco | CA| CA| http://www.artistsinn.com | FULL | | 170.00 |0.00 |1 | Artists Inn is a charming 19th century farm house and an artist's studio building on either side of a secluded garden courtyard. Located in Pacific Heights in the heart of SF. | ltd | no | no | 20353 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 3073 | FULL | Bock's Bed Breakfast | San Francisco | USA | San Francisco | CA| CA| http://www.sfgate.com/listings/venue.php?lodging,m46557 | FULL | | 75.00 | 90.00 |0 | Restored 1906 Edwardian residence. Splendid view, decks, private phones, TVs and clock radios in room. Golden Gate Park and excellent public transportation nearby| yes | no | no | 3073 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 25899 | FULL | Castillo Inn | San Francisco | USA | San Francisco | CA| CA| | FULL | | 60.00 | 80.00 |0 | Gay accommodations mainly. Centrally located close to Castro and Market Streets. | | no | no | 25899 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 964 | FULL | Country Cottage| San Francisco | USA | San Francisco | CA| CA| http://www.bbsf.com/country.html | FULL | | 89.00 |0.00 |1 | The Country Cottage BB is a cozy country-style BB in the heart of San Francisco. The four guestrooms are comfortabely furnished in American country antiques and brass beds.| | | | 964 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 1968 | FULL | Edward II Inn Suites | San Francisco | USA | San Francisco | CA| CA| http://www.edwardii.com | FULL | | 85.00 | 235.00 |1 | Edward II Inn is a three-story European-style inn, situated only blocks from San Francisco's yacht harbor. | yes | no | ltd | 1968 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 1963 | FULL | Garden Studio, The | San Francisco | USA | San Francisco | CA| CA| | FULL | | 110.00 |0.00 |0 | Studio apartment opens to garden, has private entrance, private bath, queen-sized bed. On lower level of charming Edwardian home; 2 blocks from Golden Gate Park. | yes | no | no | 1963 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 16074 | FULL | Inn 1890 | San Francisco | USA | San Francisco | CA| CA| http://www.inn1890.com | FULL | | 89.00 | 159.00 |1 | Landmark Victorian Inn blending old elegance w/modern comforts. Centrally located, convenient to all SF destinations. | yes | ltd | no | 16074 | NULL | NULL | NULL| NULL | NULL | NULL | NULL| NULL | | 1964 | FULL | Moffatt House | San Francisco | USA | San Francisco | CA| CA| http://www.moffatthouse.com | FULL | | 79.00 | 99.00 |1 | Walk to Golden Gate Park's major attractions from our
Re: can't use the table. What happen, please!!!!!!!!!!
That is a problem with a php script you are running, it is not passing the expected data as an argument. - Original Message - From: Sandra [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 09, 2002 4:41 PM Subject: can't use the table. What happen, please!! Hi all, I am getting the error when trying to connect server from my client: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in . I had my tables in test database, but created a new database and passed my tables to there. Since then I receive that message when I execute my .php I used GRANT to assing the user privileges. any answer? thanks, Sandra - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [Bug in UNION clause]
: wc: id: 8647 historic: NULL skiing: NULL romance: NULL antiques: NULL family_fun: NULL spas: NULL luxury_: NULL historic: NULL *** 1. row *** id: 1967 line: FULL name: The Inn city: San Francisco country: USA listcity: San Francisco state: CA liststate: CA url: http://www.thebandb.com line: FULL bookable: cost_range_low: 80.00 cost_range_high: 420.00 showlink: 1 desc1: child: pets: wc: id: 1967 historic: NULL skiing: NULL romance: NULL antiques: NULL family_fun: NULL spas: NULL luxury_: NULL historic: NULL *** 2. row *** id: 7954 line: FULL name: The Hensley House city: San Jose country: USA listcity: San Francisco state: CA liststate: CA url: http://www.hensleyhouse.com line: FULL bookable: cost_range_low: 150.00 cost_range_high: 325.00 showlink: 1 desc1: The Hensley House is a 3 story Victorian and a two story craftsman style home. Each house is decorated in antiques relative to the period. child: ltd pets: no wc: no id: 7954 historic: y skiing: romance: y antiques: y family_fun: spas: luxury_: y historic: y *** 3. row *** id: 2193 line: FULL name: Gerstle Park Inn city: San Rafael country: USA listcity: San Francisco state: CA liststate: CA url: http://www.gerstleparkinn.com line: FULL bookable: cost_range_low: 179.00 cost_range_high: 245.00 showlink: 1 desc1: All rooms have private baths, patio or decks. All are furnished with rich fabrics and beautiful antiques. King suites with Jacuzzi tub. child: yes pets: no wc: ltd id: 2193 historic: y skiing: romance: y antiques: y family_fun: spas: luxury_: historic: y *** 4. row *** id: 12884 line: FULL name: Gables Inn-Sausalito, The city: Sausalito country: USA listcity: San Francisco state: CA liststate: CA url: http://www.gablesinnsausalito.com line: FULL bookable: cost_range_low: 135.00 cost_range_high: 325.00 showlink: 1 desc1: Originally built in 1869, the inn has recently been completely renovated into a cozy BB offering 9 distinctive suites artfully decorated. Just 5 minutes north of the Golden Gate Bridge. child: yes pets: no wc: yes id: 12884 historic: y skiing: romance: y antiques: family_fun: y spas: luxury_: y historic: y Is this all you need? Thanks. Victoria Reznichenko [EMAIL PROTECTED] wrote in message news:aidphj$gj9$[EMAIL PROTECTED]... cliff, Thursday, August 01, 2002, 11:46:28 PM, you wrote: Description: c The UNION clause displays results 2 times even when not using the c ALL keyword with it. example: (SELECT c allusa.id,line,name,city,country,listcity,state,liststate,url,line,bookable, cost_range_low,cost_range_high,showlink,desc1,child,pets,wc,spec.id,spec.his toric,spec.skiing,spec.romance,spec.antiques,spec.family_fun,spec.spas,spec. luxury_,spec.historic c FROM bb.allusa LEFT JOIN bb.spec on allusa.id=spec.id WHERE city = 'san francisco' AND line=1 order by state,city,name) UNION (SELECT c allusa.id,line,name,city,country,listcity,state,liststate,url,line,bookable, cost_range_low,cost_range_high,showlink,desc1,child,pets,wc,spec.id,spec.his toric,spec.skiing,spec.romance,spec.antiques,spec.family_fun,spec.spas,spec. luxury_,spec.historic c FROM bb.allusa LEFT JOIN bb.spec on allusa.id=spec.id WHERE (line=1) AND (city LIKE '%san%' OR listcity LIKE '%san%' OR state='san' OR statename LIKE '%san%' OR country LIKE '%san%' OR name LIKE c '%san%') AND (city LIKE '%francisco%' OR listcity LIKE '%francisco%' OR state='francisco' OR statename LIKE '%francisco%' OR country LIKE '%francisco%' OR name LIKE '%francisco%')ORDER BY c state,city,name ) LIMIT 15,15 c produces the city San Francisco twice in the listing. How-To-Repeat: c http://www.lanierbb.com/search/search.php?pt=bbq=san+franciscostart=15nu mr=29 Can you send a dump of your tables for testing? -- For technical support contracts, goto https
Problems When Upgrading to MySQL 4.02
Hi, I am trying to upgrade to 4.02 from 3.23.47 on FreeBSD 4.6. The mysql database does not want to work for some reason, when I move my old mysql database over the new one, it does not start up, giving an error about permissions even though the permissions of that directory are 770 mysql.mysql. Is this is a common bug? I had it running once, but it was not displaying my webpage correctly with broken images everywhere instead of the pictures that are grabbed from the db. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems When Upgrading to MySQL 4.02
all of the files are chown -R mysql in that directory. Any other ideas? - Original Message - From: Gerald Clark [EMAIL PROTECTED] To: Cliff [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, August 01, 2002 7:05 AM Subject: Re: Problems When Upgrading to MySQL 4.02 Check the files in the directory. I bet they are not owned by mysql. Cliff wrote: Hi, I am trying to upgrade to 4.02 from 3.23.47 on FreeBSD 4.6. The mysql database does not want to work for some reason, when I move my old mysql database over the new one, it does not start up, giving an error about permissions even though the permissions of that directory are 770 mysql.mysql. Is this is a common bug? I had it running once, but it was not displaying my webpage correctly with broken images everywhere instead of the pictures that are grabbed from the db. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[Bug in UNION clause]
Description: The UNION clause displays results 2 times even when not using the ALL keyword with it. example: (SELECT allusa.id,line,name,city,country,listcity,state,liststate,url,line,bookable,cost_range_low,cost_range_high,showlink,desc1,child,pets,wc,spec.id,spec.historic,spec.skiing,spec.romance,spec.antiques,spec.family_fun,spec.spas,spec.luxury_,spec.historic FROM bb.allusa LEFT JOIN bb.spec on allusa.id=spec.id WHERE city = 'san francisco' AND line=1 order by state,city,name) UNION (SELECT allusa.id,line,name,city,country,listcity,state,liststate,url,line,bookable,cost_range_low,cost_range_high,showlink,desc1,child,pets,wc,spec.id,spec.historic,spec.skiing,spec.romance,spec.antiques,spec.family_fun,spec.spas,spec.luxury_,spec.historic FROM bb.allusa LEFT JOIN bb.spec on allusa.id=spec.id WHERE (line=1) AND (city LIKE '%san%' OR listcity LIKE '%san%' OR state='san' OR statename LIKE '%san%' OR country LIKE '%san%' OR name LIKE '%san%') AND (city LIKE '%francisco%' OR listcity LIKE '%francisco%' OR state='francisco' OR statename LIKE '%francisco%' OR country LIKE '%francisco%' OR name LIKE '%francisco%')ORDER BY state,city,name ) LIMIT 15,15 produces the city San Francisco twice in the listing. How-To-Repeat: http://www.lanierbb.com/search/search.php?pt=bbq=san+franciscostart=15numr=29 Fix: Could not find a fix Submitter-Id: submitter ID Originator:Cliff Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.2-alpha (Official MySQL binary) Server: /usr/local/bin/mysqladmin Ver 8.35 Distrib 4.0.2-alpha, for unknown-freebsdelf4.6 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.2-alpha Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 4 hours 38 min 52 sec Threads: 34 Questions: 478382 Slow queries: 2 Opens: 1924 Flush tables: 1 Open tables: 64 Queries per second avg: 28.591 Environment: System: FreeBSD lists.travelguides.com 4.6-STABLE FreeBSD 4.6-STABLE #0: Sat Jul 13 13:04:51 CDT 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/TRAVELGUIDES i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 132 Jun 21 07:31 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Jun 21 07:31 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 579412 Jun 21 07:31 /usr/lib/libc.so.4 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --with-named-z-libs=not-used --disable-shared 'CFLAGS=-O3 -fno-omit-frame-pointer' CXX=gcc CC=gcc 'CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY BY ¿?
On Tue, 18 Jun 2002 20:04:54 -0300 César Aracena wrote: Hi all. Is there any possible way for me to arrange a SELECT query made to MySQL in to orders? Like __. ORDER BY lastname THEN BY firstname? SELECT * FROM tablename ORDER BY lastname, firstname; -- Cliff Wells, Software Engineer Logiplex Corporation (www.logiplex.net) (503) 978-6726 x308 (800) 735-0555 x308 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT using C API
On 18 Jun 2002 20:22:09 -0500
rm wrote:
Hello list,
I'm new to C programming, and have used MySQL for only a short time; but
I've got a situation where I need to update an existing table using a C
function.
I'm working on Redhat 7.1 using the MySQL rpm that came with the
package.
I've written the function so that it compiles, and executes from the
command line, and updates the MySQL table - but only with the static
entries I write into the code. This is the INSERT statement:
res = mysql_query(my_connection, INSERT INTO pending(mbmid, pw_name,
pw_domain) VALUES('', 'anyname', 'anydomain'));
Using the above INSERT statement the values are correctly added to the
table. But, what I'm trying to do is find the correct method to use
variables in lieu of the constants.
I've tried:
res = mysql_query(my_connection, INSERT INTO pending(mbmid, pw_name,
pw_domain) VALUES('%s', '%s', '%s'),mbmID, Uname, Udomain);
You need to do it like this:
#define MAX_QUERY 1024
char my_query[MAX_QUERY];
snprintf(my_query, MAX_QUERY,
INSERT INTO pending(mbmid, pw_name, pw_domain) VALUES ('%s', '%s',
'%s'),
mbmID, Uname, Udomain);
res = mysql_real_query(my_connection, my_query, strlen(my_query));
The %s format string is not a part of the C language proper, but rather a part
of several of the C library functions (printf, fprintf, sprintf, etc).
The reason to use snprintf versus sprintf is to avoid overwriting memory if the
query string becomes too long. If your C library doesn't provide it then you
can use sprintf, but you'll have to be more careful.
Regards,
--
Cliff Wells, Software Engineer
Logiplex Corporation (www.logiplex.net)
(503) 978-6726 x308 (800) 735-0555 x308
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slow to a crawl
I have one query, that basically reads from one table of 13 million rows, groups it by two fields, and inserts into a rollup table. The result set ends up being about 31,000 rows. This runs in about 2 minutes which is acceptable. However, I add an additional group by field, which will probably result in a million results and mysql goes to a crawl. It doesn't even max out one cpu as the former query does. Instead vmstat reports a process blocked and in wait, while mysql goes down to hardly any cpu. The box lags a tad bit like it's I/O related, but the I/O isn't abnormal. As soon as I use mysqladmin to kill the query everything is back to normal. I'll note the load goes above 3.0 when this is running but none of it's in CPU ... I realize this is very vague, bu I'm just shooting in the dark here wondering if someone has seen similar oddities, and it might be resolvable by tuning some of the parameters. 3.23.49, precompiled from mysql.com 2.4.9-21smp Linux key_buffer=16M table_cache=128 sort_buffer=8M record_buffer=2M tmp_table_size=16M I've tried increasing these but didn't help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: slow to a crawl
Benjamin Pflugmann [EMAIL PROTECTED] writes: Hello. First, key_buffer=16M seems a bit low for me with 13 million rows (well, depends on the indexes, and how much free memory you have, of course). 1 gig, plenty free. If I am not mistaken, sort_buffer/tmp_table_size are the one mostly needed for this query, I think. Btw, you can get more info about the query with EXPLAIN. Please post the result of it (for both SELECTs), if you answer. Query 1, works: group by bayid, ds1 table typepossible_keys key key_len ref rowsExtra detail_20020408 ALL NULLNULLNULLNULL13083666Using temporary Query 2, bad: group by bayid, ds1, scid table typepossible_keys key key_len ref rowsExtra detail_20020408 ALL NULLNULLNULLNULL13083666Using temporary Another idea is that you have an index over the two fields you were talking, but not the additional field. In this case, the first query can run only from index, but the second needs to read from the table file. EXPLAIN should show. Btw, this is a full table scan, no WHERE clause exists, so I'm not sure where indexes would change the behaviour of this. Btw, please always post what you observe, e.g. the vmstat output. Maybe someone on the list can see something you didn't. Query 1: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 0 0492 5084 91104 837016 0 0 4 43 3 1 1 1 1 0 0492 5084 91104 836632 0 0 11968 0 1178 2444 47 6 47 2 0 0492 5084 91104 835524 0 0 8228 714 1653 3283 36 6 58 2 0 0492 5096 91104 835248 0 0 12342 8 1205 2553 49 7 44 2 0 0492 5096 91104 836780 0 0 11220 0 1180 2477 47 7 47 2 0 0492 5096 91104 836764 0 0 10472 714 1729 3297 40 6 54 1 0 0492 5096 91104 836744 0 0 11220 0 1220 2609 49 5 46 0 1 0492 5096 91104 836728 0 0 10392 736 1599 3259 42 7 50 Query 2: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 2 0 0492 5060 91148 820528 0 0 4 44 3 1 1 1 2 0 0492 5096 91152 817160 0 0 16836 0 1276 2611 84 13 3 4 0 0492 5092 91164 817340 0 0 11232 3040 1168 2573 81 16 3 3 0 0492 16404 91168 820312 0 0 9736 2722 1776 4093 74 16 10 2 1 1492 5092 91168 834696 0 0 11976 7756 1183 2793 81 17 2 0 2 1492 5092 91168 834420 0 0 9734 8382 1950 3270 60 15 25 2 1 1492 5092 91168 833448 0 0 10476 164 1705 2571 45 7 47 2 1 1492 5092 91172 833172 0 0 13100 6382 1536 2897 78 18 3 1 2 1492 5092 91172 832888 0 0 9354 9148 2187 3468 46 14 41 3 1 2492 5092 91172 831904 0 0 11226 6210 1644 2567 60 11 28 2 2 1492 5092 91172 832900 0 0 13102 7286 1740 2507 75 19 6 You can see how very eradict the machine gets... Interactive typing even starts to pause when you see the idle near zero, it's like something is thrashing in the kernel but top yields no clues. Cliff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Escaped by 2 things?
Short and simple, is it possible to escape by two different parameters? This
used to work:
SELECT * from allusa WHERE (updated_by='I' or updated_by='A' or
updated_by='L') AND (id9) AND last_updated '2002020100' ORDER BY
last_updated INTO OUTFILE
'/home/www/site/admin/admin/dbdump/bb200202010700.txt' FIELDS TERMINATED
BY ' ' ENCLOSED BY '' ESCAPED BY ('' '\n');
but now it gives the error:
ERROR 1064: You have an error in your SQL syntax near ' ' '' at line 1
anyone know if this is possible?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
hung read() after SIG ALARM + libmysqlclient + Race Condition?
Hope this makes sense
I'm seeing random hung reads() after a mysql insert that appears to be
related to a sig alarm race condition. The basic workflow of this program
is
while (1)
recvfrom() /* few hundred packets a sec */
parse packet
insert into sql
However, once per second a sig alarm is generated which does house keeping
and what-not. This will work for minutes or hours, but eventually the
read(3, ...) blocks , which is the mysql socket. When this occurs you just
see the repeated sig alarms being catch, but immediately returning to that
read() which never returns. Note there is still hundreds of udp packets
coming in but never able to be read because the never-returing read(3,) on
the mysql sock.
Normal syscalls resemble:
write(3, \1\1\0\0\3INSERT INTO call_logging.de..., 261) = 261
read(3, \3\0\0\1, 4) = 4
read(3, \0\1\0, 3)= 3
recvfrom(5, ...) = 398
Here is (hopefully enough) machine information:
*** Precompiled binaries from mysql.com.
*** Linux mmlog2 2.4.9-21smp #1 SMP Thu Jan 17 14:01:48 EST 2002 i686 unknown
*** My Program complied as such (tried both shared and static linked,)
*** Note, this program isn't making use of threads, and I've seen the same
*** results with -D_REENTRANT -lpthread and without, as well as linking
*** the non _r libmysqlclient
gcc -o clconsumer -O -D_REENTRANT -g -lpthread -lm -lz -I/usr/include/mysql
-I/usr/local/mysql/include clconsumer.o /usr/local/mysql/lib/libmysqlclient_r.a
mysql Ver 11.17 Distrib 3.23.49, for pc-linux-gnu (i686)
Connection id: 13
Current database:
Current user: root@localhost
Current pager: stdout
Using outfile: ''
Server version: 3.23.49
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset:latin1
Server characterset:latin1
UNIX socket:/tmp/mysql.sock
Uptime: 4 hours 55 min 3 sec
Threads: 4 Questions: 3261389 Slow queries: 0 Opens: 195 Flush tables: 1 Open
tables: 7 Queries per second avg: 184.228
--
Here is the infinite syscalls when mysql isn't returning (expected?) data on
the socket?
read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted)
--- SIGALRM (Alarm clock) ---
time(NULL) = 1014267832
rt_sigaction(SIGALRM, {SIG_IGN}, {0x4002fa10, [ALRM], SA_RESTART|0x400}, 8) = 0
alarm(1)= 0
rt_sigaction(SIGALRM, {0x4002fa10, [ALRM], SA_RESTART|0x400}, {SIG_IGN}, 8) = 0
sigreturn() = ? (mask now [])
read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted)
--- SIGALRM (Alarm clock) ---
time(NULL) = 1014267833
rt_sigaction(SIGALRM, {SIG_IGN}, {0x4002fa10, [ALRM], SA_RESTART|0x400}, 8) = 0
alarm(1)= 0
rt_sigaction(SIGALRM, {0x4002fa10, [ALRM], SA_RESTART|0x400}, {SIG_IGN}, 8) = 0
sigreturn() = ? (mask now [])
read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted)
--- SIGALRM (Alarm clock) ---
Cliff
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using password: _No_
Hi,
After a couple helpful answers to my post, I realized I wasn't going
crazy, but that the root probably had a password and I just didn't know
it. Found this documentation which wasn't very clear except for the
comments at the bottom of the page.
http://www.mysql.com/doc/R/e/Resetting_permissions.html
1. /usr/local/mysql/bin/safe_mysqld --skip-grant-tables
2. /usr/local/mysql
3. use mysql;
4. update user set password = password('...') where user = 'root'
and host='localhost';
5. Stop and Start the MySQL server.
Thanks very much for the help.
Abdulhakeem wrote:
No you don't have to re-install.You should use this command to start the
mysql server;
etc/init.d/mysqld start
Then use this command to connect to the server
mysql
However if you are logged in as a different user other than root you must
specfy your user name,host name and password by using the following command
mysql -h localhost -u user -p mypass
I hope this helps,please let me know as i am new to mysql too(1 week)
--
Atoyebi Abdulhakeem (MCSE)
[EMAIL PROTECTED] www.artsinscience.com
-Original Message-
From: Cliff [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 13, 2002 4:18 AM
To: [EMAIL PROTECTED]
Subject: Using password: _No_
Hello,
Can anyone help?
When trying to run mysql I get an ERROR 1045: Access denied for user:
'root@localhost' (Using password: No).
In the documentation it mentions 'Using password: YES'. Have tried
starting mysql with --skip-grant-tables and then mysqladmin
flush-privileges which doesn't seem to do anything.
What does the Password No mean? How do I correct it short of
reinstallation? Well, I don't know maybe I need to start over yet
again.
Thanks very much for any help,
Cliff
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using password: _No_
Hello, Can anyone help? When trying to run mysql I get an ERROR 1045: Access denied for user: 'root@localhost' (Using password: No). In the documentation it mentions 'Using password: YES'. Have tried starting mysql with --skip-grant-tables and then mysqladmin flush-privileges which doesn't seem to do anything. What does the Password No mean? How do I correct it short of reinstallation? Well, I don't know maybe I need to start over yet again. Thanks very much for any help, Cliff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using password: _No_
Thanks. This works when I use any other user besides root without a password. Guess the problem is that there is a password for root and I don't know what it is. Thanks again for getting me over the first mindless hurtle. Regards, Cliff Austin Brian P. Austin wrote: what command are you issuing. is it mysql? If so, what arguments are you sending it? It should look like this mysql -u user -p prompt: password -Original Message- From: Cliff [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 12, 2002 9:18 PM To: [EMAIL PROTECTED] Subject: Using password: _No_ Hello, Can anyone help? When trying to run mysql I get an ERROR 1045: Access denied for user: 'root@localhost' (Using password: No). In the documentation it mentions 'Using password: YES'. Have tried starting mysql with --skip-grant-tables and then mysqladmin flush-privileges which doesn't seem to do anything. What does the Password No mean? How do I correct it short of reinstallation? Well, I don't know maybe I need to start over yet again. Thanks very much for any help, Cliff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql on irix
Hello, I installed binary mysql-3.23.44-sgi-irix6.5-mips on irix 6.5.10 following README. When I issue 'mysql mysql' or 'mysql versions' I got: ERROR 1135: Can't create a new thread (errno 1). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug It's probably happend to some folks using irix befor so I'm hoping that there is a solution to it. Thanks, Cliff -- Cliff Liu SAIC email: [EMAIL PROTECTED] Phone: 301-614-5062 Fax: 301-614-5269 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Bugs? Re: HAVING [cond...] problem
Still doesn't work, I've even tried: HAVING ( Avg_Xmit = 12345 ) AND ( Samples = 25 ) 19000 rows returned, none equalling 12345, but all have Samples = 25. It's almost as if this became an OR. Cliff Gerald Clark [EMAIL PROTECTED] writes: Have you tried: HAVING ( Avg_Xmit 28800 ) AND ( Samples = 10 ) ? Cliff Daniel wrote: I'm having some difficulting on 3.23.38, linux x86... When I write the following query the HAVING clause is only listening to the 'AND Samples = 10' and displas all Avg_Xmit regardless if it's less than or equal to 28800. If I remove the 'AND Samples = 10' I get the correct results, but of course samples are permitted if less than 10, which I do not want. Any ideas? SELECT INET_NTOA(NAS_Identifier), NAS_DS1, AVG(Ascend_Xmit_Rate) as Avg_Xmit, AVG(Ascend_Data_Rate) as Avg_Recv, AVG(Acct_Session_Time) / 60 as ACHT, COUNT(*) as Samples FROM tbl_data_call_detail WHERE Acct_Session_Time 0 GROUP BY NAS_Identifier, NAS_DS1 HAVING Avg_Xmit 28800 AND Samples = 10 ORDER BY Avg_Xmit DESC Regards, Cliff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Gerald L. Clark [EMAIL PROTECTED] -- Cliff Daniel Level 3 Communications The government deficit is the difference between the amount of money the government spends and the amount it has the nerve to collect. --Sam Ewing - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
