[High]Thread creation error
Hi all, after random times not only about high usage, i have this message on Mysql Administrator ( Windows ), logged to my Dedicated Database ( Linux ). Here is a print of error: http://img25.imageshack.us/img25/2342/mysqlthreaderror.jpg Here is a print of putty using 'top' on dedicated server: http://img15.imageshack.us/img15/2412/tophym.jpg Here is a print of ./mysqltuner.pl http://img5.imageshack.us/img5/8991/mysqltuner.jpg Here is a print of ./tuning-primer.sh http://img17.imageshack.us/img17/544/tunning.jpg And finally a print of my.cnf http://img6.imageshack.us/img6/2877/83767908.jpg Here my mysqladmin extended-status mysqladmin status Uptime: 188937 Threads: 10 Questions: 21396848 Slow queries: 1 Opens: 501 mysqlreport Flush tables: 3 Open tables: 124 Queries per second avg: 113.249 MySQL 5.0.77-log uptime 2 7:18:39 Thu May 7 23:31:40 2009 __ Key _ Buffer used 302.78M of 1.56G %Used: 18.92 Current 400.58M%Usage: 25.04 Write hit 79.26% Read hit 99.83% __ Questions ___ Total 22.84M 114.7/s DMS 22.49M 113.0/s %Total: 98.50 Com_459.78k 2.3/s2.01 -Unknown174.07k 0.9/s0.76 QC Hits 52.34k 0.3/s0.23 COM_QUIT 5.14k 0.0/s0.02 Slow 3 s1 0.0/s0.00 %DMS: 0.00 Log: ON DMS22.49M 113.0/s 98.50 REPLACE 11.65M58.5/s 51.01 51.79 SELECT4.34M21.8/s 18.99 19.28 DELETE4.18M21.0/s 18.29 18.56 INSERT2.17M10.9/s9.51 9.65 UPDATE 160.05k 0.8/s0.70 0.71 Com_ 459.78k 2.3/s2.01 admin_comma 173.95k 0.9/s0.76 show_status 140.38k 0.7/s0.61 show_innodb 140.03k 0.7/s0.61 __ SELECT and Sort _ Scan 574.01k 2.9/s %SELECT: 13.24 Range 4.89k 0.0/s0.11 Full join 0 0/s0.00 Range check 0 0/s0.00 Full rng join 0 0/s0.00 Sort scan 208.82k 1.0/s Sort range578.84k 2.9/s Sort mrg pass 0 0/s __ Query Cache _ Memory usage 11.08M of 128.00M %Used: 8.65 Block Fragmnt 35.60% Hits 52.34k 0.3/s Inserts 4.25M21.4/s Insrt:Prune 4.25M:121.4/s Hit:Insert 0.01:1 __ Table Locks _ Waited 59.71k 0.3/s %Total: 0.26 Immediate 22.54M 113.2/s __ Tables __ Open 136 of 500%Cache: 27.20 Opened513 0.0/s __ Connections _ Max used 13 of 50 %Max: 26.00 Total 5.28k 0.0/s __ Created Temp Disk table439 0.0/s Table 141.93k 0.7/sSize: 32.0M File 14 0.0/s __ Threads _ Running 1 of 10 Cached 3 of 200 %Hit: 99.75 Created13 0.0/s Slow0 0/s __ Aborted _ Clients 260 0.0/s Connects7 0.0/s __ Bytes ___ Sent8.93G 44.8k/s Received8.03G 40.3k/s __ InnoDB Buffer Pool __ Usage 0 of 0 %Used: 0.00 Read hit0.00% Pages Free 0%Total: 0.00 Data 0 0.00 %Drty: 0.00 Misc 0 0.00 Latched 0.00 Reads 0 0/s From file 0 0/s0.00 Ahead Rnd 0 0/s Ahead Sql 0 0/s Writes 0 0/s Flushes 0 0/s Wait Free 0 0/s __ InnoDB Lock _ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows Data Reads 0 0/s Writes0 0/s fsync 0 0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read
reduce number of open files ?
hi list, i am wondering if there is a way to reduce the number of open files. The database has InnoDB and MyISAM. I have a lot a partitions is that a problem ? (To many open files causes problems for mysqldump) running is vanilla 5.1.34 show status like '%open%' ; +--+---+ | Variable_name| Value | +--+---+ | Com_ha_open | 0 | | Com_show_open_tables | 0 | | Open_files | 12437 | | Open_streams | 0 | | Open_table_definitions | 192 | | Open_tables | 362 | | Opened_files | 14520 | | Opened_table_definitions | 0 | | Opened_tables| 0 | | Slave_open_temp_tables | 0 | +--+---+ re, walter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
utf8_bin or utf8_general_ci
Hello, I would like save text (different languages) in a MySQL-Table and I don't know, which Collation is the right one for me. utf8_bin or utf8_general_ci? OK, in utf8_general_ci I know that the Collation is CASE INTENSIVE but it isn't a problem. But what is better for Text in different languages? Thunder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Lock wait timeout error
Here is the table structure. CREATE TABLE `UP_UserEx` ( `UserId` INT(11) NOT NULL, `UserNationality` CHAR(2) NOT NULL DEFAULT '', `UserProvince` CHAR(2) NOT NULL DEFAULT '', `UserCity` CHAR(4) NOT NULL DEFAULT '', `HomePhone` VARCHAR(32) NOT NULL DEFAULT '', `WorkPhone` VARCHAR(32) NOT NULL DEFAULT '', `OtherPhone` VARCHAR(32) NOT NULL DEFAULT '', `PersonalEmail` VARCHAR(64) NOT NULL DEFAULT '', `WorkEmail` VARCHAR(64) NOT NULL DEFAULT '', `OtherEmail` VARCHAR(64) NOT NULL DEFAULT '', `PrimaryEmail` SMALLINT(6) NOT NULL DEFAULT '0', `Company` VARCHAR(128) NOT NULL DEFAULT '', `CompanyWebsite` VARCHAR(128) NOT NULL DEFAULT '', `Occupation` VARCHAR(64) NOT NULL DEFAULT '', `JobTitle` VARCHAR(64) NOT NULL DEFAULT '', `Interest` VARCHAR(128) NOT NULL DEFAULT '', `Profile` VARCHAR(256) NOT NULL DEFAULT '', `Phrases` LONGTEXT NOT NULL, `LunarAnimal` SMALLINT(6) NOT NULL DEFAULT '0', `Horoscope` SMALLINT(6) NOT NULL DEFAULT '0', `BloodType` SMALLINT(6) NOT NULL DEFAULT '0', `ReservedFlag` INT(11) NOT NULL DEFAULT '0', `ReservedXml` LONGTEXT, PRIMARY KEY (`UserId`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 The lock table timeout error display when I use the following statement. UPDATE UP_UserEx SET Phrases ='' WHERE UserID = 1; I don't know why? Thanks for your patient read. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com
Re: Memory corrupting, while retrive the query generated
Dear walter Harms,
Thanks for your valuable solution, but in the code which you
provided is printing only one row , if i try to print whole table, or 2, or
3, columns fully means its giving segmentation fault, kindly check the below
code for furthur information.
software used:
---
1. MYSQL 6.0.0
2.MySQL Connection C 6.0
3.Cygwin (used to run the programs using GCC)
Operating Systems:
Windows Vista Home basic
building executable:
--
exporting c connection library (mysql.h) as,
export PATH=$PATH:c:/Program Files/MySQL/MySQL Connection C 6.0/lib/opt
and i copied the libmysql.dllto local folder where the c code
resides,
gcc -g -c simple.c
gcc -g libmysql.dll simple.o
running:
./a.exe
if i run the below code its giving output like this ,(trying to get all
values from a particular column of a table).
---output----
num_fields = 2
127.0.0.1
localhost
28 [main] a 3836 _cygtls::handle_exceptions: Error while dumping state
(probably corrupted stack)
Segmentation fault (core dumped)
Code as follows: (i just modified your code , so as to print all the values
regarding particular column)
--code
starts here
/*
simple DB connect test
gcc -L/usr/lib/mysql -lmysqlclient connect.c
*/
#define _GNU_SOURCE
#include stdio.h
#include stdlib.h
#include C:\Program Files\MySQL\MySQL Connector C 6.0.0\include\mysql.h
int main()
{
MYSQL *MySQL;
MYSQL_ROW row;
MYSQL_RES *res;
char *dbhost = localhost;
char *dbuser = root;
char *dbpass = ;
char *dbname = mysql;
char sel_smt[200];
int ret;
unsigned int num_fields, i;
MySQL = mysql_init(NULL);
if (MySQL == NULL) {
fprintf(stderr, Connection failed\n);
exit(1);
}
if (mysql_real_connect
(MySQL, dbhost, dbuser, dbpass, dbname, 0, NULL, 0) 0) {
fprintf(stderr, %s\n, mysql_error(MySQL));
exit(1);
}
//sprintf(sel_smt, select * from user;);
//printf(\n %s\n, sel_smt);
if (mysql_query(MySQL, select host, user from user;) != 0) {
fprintf(stderr, %s\n, mysql_error(MySQL));
exit(1);
}
res = mysql_store_result(MySQL);
if (res == NULL) {
fprintf(stderr, %s\n, mysql_error(MySQL));
exit(1);
}
//row = mysql_fetch_row(res);
//printf(%s\n, row[0] ? row[0] :
-- instead of printing one row
num_fields = mysql_num_fields(res);
|
|
printf(\n num_fields = %d\n, num_fields);
|
|
while ((row = mysql_fetch_row(res)) != NULL)
|-- printing host and user column fully
{
|
for(i = 0;i num_fields;i++)
|
printf(%s\n, row[i]?row[i]:NULL);
|
|
}
|
//free(sel_smt);
mysql_free_result(res);
mysql_close(MySQL);
exit(0);
}
--code
ends here
Thanks and regards,
Ravi
- Original Message -
From: walter harms wha...@bfs.de
To: Ravi raj ravi...@vinjey.com
Cc: mysql@lists.mysql.com; Vinoth Kumar vin...@vinjey.com
Sent: Thursday, May 07, 2009 6:40 PM
Subject: Re: Memory corrupting, while retrive the query generated
hi ravi,
this works for me. it should help
you to get a starting point
re,
wh
/*
simpple DB connect test
gcc -L/usr/lib/mysql -lmysqlclient connect.c
*/
#define _GNU_SOURCE
#include stdio.h
#include stdlib.h
#include mysql/mysql.h
int main()
{
MYSQL *MySQL;
MYSQL_ROW row;
MYSQL_RES *res;
char *dbhost = localhost;
char *dbuser = dbuser;
char *dbpass = ;
char *dbname = mysql;
char *sel_smt;
int ret;
MySQL = mysql_init(NULL);
if (MySQL == NULL) {
fprintf(stderr, Connection failed\n);
exit(1);
}
if (mysql_real_connect
(MySQL, dbhost, dbuser, dbpass, dbname, 0, NULL, 0) 0) {
fprintf(stderr, %s\n, mysql_error(MySQL));
exit(1);
}
asprintf(sel_smt, select count(*) from user);
if (mysql_query(MySQL, sel_smt) != 0) {
fprintf(stderr, %s\n, mysql_error(MySQL));
exit(1);
}
res = mysql_store_result(MySQL);
if (res == NULL) {
fprintf(stderr, %s\n, mysql_error(MySQL));
exit(1);
}
row = mysql_fetch_row(res);
printf(%s\n, row[0] ? row[0] : NULL);
free(sel_smt);
mysql_free_result(res);
mysql_close(MySQL);
exit(0);
}
Ravi raj schrieb:
Dear All,
I want to connect
Re: Memory corrupting, while retrive the query generated
Ravi raj schrieb: Dear walter Harms, Thanks for your valuable solution, but in the code which you provided is printing only one row , if i try to print whole table, or 2, or 3, columns fully means its giving segmentation fault, kindly check the below code for furthur information. software used: --- 1. MYSQL 6.0.0 2.MySQL Connection C 6.0 3.Cygwin (used to run the programs using GCC) Operating Systems: Windows Vista Home basic building executable: -- exporting c connection library (mysql.h) as, export PATH=$PATH:c:/Program Files/MySQL/MySQL Connection C 6.0/lib/opt and i copied the libmysql.dllto local folder where the c code resides, gcc -g -c simple.c gcc -g libmysql.dll simple.o running: ./a.exe if i run the below code its giving output like this ,(trying to get all values from a particular column of a table). ---output---- num_fields = 2 127.0.0.1 localhost 28 [main] a 3836 _cygtls::handle_exceptions: Error while dumping state (probably corrupted stack) Segmentation fault (core dumped) hi Ravi, i have checked your programm on my box and it works as expected. (linux,mysql 5.0) That leaves only your environment (compiler,libraries,...) as culprit. The most easy think to do now is to install a linux and give it a try. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with mysql query, multiple list
Hi All I am kind of stuck with this query , cant expand my thinking. May this is a limitation. Here it is I have a database with many cols two of which are start and end position for an event. Now I have a list of event time stamps, I want to find all the info once the current event time stamp is = start time of event and =end time of event. something like this select * from table_name where start = ( LIST of time stamps) AND end =( list of time stamps). Clearly above query accepts only one item in the list. Is there a way to do this for multiple items in the list ??? I can't think of anything at this moment. Thanks, -Abhi
Unix compress equivalent
Hi all What is the equivalent in unix (more specifically python) to the compress() function. I am trying to make csv file for use with load data infile and am wondering how to compress the strings that I would usually compress with compress() in a regular sql statement. The field I am writing this into is longblob and I need the compressed version here to be identical to what compress() would do Thanks olaf - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with mysql query, multiple list
aah okie I think I was trying to get too clever. Guess that won't work ...
Thanks,
-Abhi
On Fri, May 8, 2009 at 12:34 PM, Barney Boisvert bboisv...@gmail.comwrote:
You'll have to iterate over your two lists of timestamps and build a
set of ORed conditional pairs:
sql = select ... from ... where 1 = 0
for (i = 0; i timestamps.length; i++) {
sql += or start = + timestamps[i] + and end = + timestamps[i]
}
You'll want to use bind parameters in real life, of course.
cheers,
barneyb
On Fri, May 8, 2009 at 9:26 AM, Abhishek Pratap abhishek@gmail.com
wrote:
Hi All
I am kind of stuck with this query , cant expand my thinking. May this
is a
limitation. Here it is
I have a database with many cols two of which are start and end position
for
an event.
Now I have a list of event time stamps, I want to find all the info once
the
current event time stamp is = start time of event and =end time of
event.
something like this
select * from table_name where start = ( LIST of time stamps) AND end
=(
list of time stamps).
Clearly above query accepts only one item in the list. Is there a way to
do
this for multiple items in the list ??? I can't think of anything at this
moment.
Thanks,
-Abhi
--
Barney Boisvert
bboisv...@gmail.com
http://www.barneyb.com/
Re: Help with mysql query, multiple list
why not something like below. Assume you have 3 pairs of start/end timestamps and you want to find everything within those 3 time periods: select * from table_name where start = start1 and end = end1 union select * from table_name where start = start2 and end = end2 union select * from table_name where start = start3 and end = end3 On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap abhishek@gmail.comwrote: Hi All I am kind of stuck with this query , cant expand my thinking. May this is a limitation. Here it is I have a database with many cols two of which are start and end position for an event. Now I have a list of event time stamps, I want to find all the info once the current event time stamp is = start time of event and =end time of event. something like this select * from table_name where start = ( LIST of time stamps) AND end =( list of time stamps). Clearly above query accepts only one item in the list. Is there a way to do this for multiple items in the list ??? I can't think of anything at this moment. Thanks, -Abhi -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Help with mysql query, multiple list
Hi Jim Unfortunately I have thousands of such points. So explicit statement calling will be very expensive both computationally and in terms of writing.. Thanks, -Abhi On Fri, May 8, 2009 at 12:37 PM, Jim Lyons jlyons4...@gmail.com wrote: why not something like below. Assume you have 3 pairs of start/end timestamps and you want to find everything within those 3 time periods: select * from table_name where start = start1 and end = end1 union select * from table_name where start = start2 and end = end2 union select * from table_name where start = start3 and end = end3 On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap abhishek@gmail.comwrote: Hi All I am kind of stuck with this query , cant expand my thinking. May this is a limitation. Here it is I have a database with many cols two of which are start and end position for an event. Now I have a list of event time stamps, I want to find all the info once the current event time stamp is = start time of event and =end time of event. something like this select * from table_name where start = ( LIST of time stamps) AND end =( list of time stamps). Clearly above query accepts only one item in the list. Is there a way to do this for multiple items in the list ??? I can't think of anything at this moment. Thanks, -Abhi -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Unix compress equivalent
Or even better, can I tell load data infile or somewhere in the table definition to compress whatever is written to the file? Thanks Olaf On 5/8/09 12:29 PM, Olaf Stein olaf.st...@nationwidechildrens.org wrote: Hi all What is the equivalent in unix (more specifically python) to the compress() function. I am trying to make csv file for use with load data infile and am wondering how to compress the strings that I would usually compress with compress() in a regular sql statement. The field I am writing this into is longblob and I need the compressed version here to be identical to what compress() would do Thanks olaf - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with mysql query, multiple list
Abhi,
I might not be understanding the problem but could you use the max
and min timestamp values and use something like
SELECT * FROM TABLE WHERE start BETWEEN max AND min AND end BETWEEN max AND min
or
SELECT * FROM TABLE WHERE START IN (1,2,3,4,5) AND END IN(1,2,3,4,5)
I might be completely off-base here though as I don't think I fully
comprehend what you're trying to do.
Kyong
At 09:36 AM 5/8/2009, Abhishek Pratap wrote:
aah okie I think I was trying to get too clever. Guess that won't work ...
Thanks,
-Abhi
On Fri, May 8, 2009 at 12:34 PM, Barney Boisvert bboisv...@gmail.comwrote:
You'll have to iterate over your two lists of timestamps and build a
set of ORed conditional pairs:
sql = select ... from ... where 1 = 0
for (i = 0; i timestamps.length; i++) {
sql += or start = + timestamps[i] + and end = + timestamps[i]
}
You'll want to use bind parameters in real life, of course.
cheers,
barneyb
On Fri, May 8, 2009 at 9:26 AM, Abhishek Pratap abhishek@gmail.com
wrote:
Hi All
I am kind of stuck with this query , cant expand my thinking. May this
is a
limitation. Here it is
I have a database with many cols two of which are start and end position
for
an event.
Now I have a list of event time stamps, I want to find all the info once
the
current event time stamp is = start time of event and =end time of
event.
something like this
select * from table_name where start = ( LIST of time stamps) AND end
=(
list of time stamps).
Clearly above query accepts only one item in the list. Is there a way to
do
this for multiple items in the list ??? I can't think of anything at this
moment.
Thanks,
-Abhi
--
Barney Boisvert
bboisv...@gmail.com
http://www.barneyb.com/
Kyong Kim
Instructional Multimedia/Web Programmer
Foothill College
12345 El Monte Rd
3601
Los Altos Hills, CA 94022
650-949-7091
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with mysql query, multiple list
then either build the statement by way of a program like a perl script or select all records with a start time after the min start time of all in your list and an end time less than the max end time in your list then filter them further either in a program or a store procedure. On Fri, May 8, 2009 at 11:45 AM, Abhishek Pratap abhishek@gmail.comwrote: Hi Jim Unfortunately I have thousands of such points. So explicit statement calling will be very expensive both computationally and in terms of writing.. Thanks, -Abhi On Fri, May 8, 2009 at 12:37 PM, Jim Lyons jlyons4...@gmail.com wrote: why not something like below. Assume you have 3 pairs of start/end timestamps and you want to find everything within those 3 time periods: select * from table_name where start = start1 and end = end1 union select * from table_name where start = start2 and end = end2 union select * from table_name where start = start3 and end = end3 On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap abhishek@gmail.comwrote: Hi All I am kind of stuck with this query , cant expand my thinking. May this is a limitation. Here it is I have a database with many cols two of which are start and end position for an event. Now I have a list of event time stamps, I want to find all the info once the current event time stamp is = start time of event and =end time of event. something like this select * from table_name where start = ( LIST of time stamps) AND end =( list of time stamps). Clearly above query accepts only one item in the list. Is there a way to do this for multiple items in the list ??? I can't think of anything at this moment. Thanks, -Abhi -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
