Re: C api - mysql_list_fields
It works for me, I used your code:
Field =mycol Type=254 Length=20
so at least your code is fine, and the problem must be somewhere
else. I am using RH EL3.
cheers,
Doug
On 6 Oct 2008, at 19:52, Mike Aubury wrote:
I'm probably being a bit stupid - but I'm trying to determine (in
code) the
length of the string in the schema for a given table.
So - for example :
create table a (
blah char(20)
)
I want to return '20', but I'm getting '60' when I use
mysql_list_fields..
(Always seems to be 3x longer that I'm expecting)...
Am I missing something ? (or should I just divide by 3!!)
Heres an example :
#include stdio.h
#include stdlib.h
#include mysql.h
MYSQL conn;
int main(int argc,char *argv[]) {
// run with username port as arguments
char *tabname=a;
char *db=test1;
char *u;
char *p;
MYSQL_RES *result;
MYSQL_FIELD *field;
if (argc!=3) {
printf(usage : %s username password\n, argv[0]);exit(2);
}
u=argv[1]; p=argv[2];
mysql_init(conn);
if (!mysql_real_connect(conn, NULL,u,p,db,0,NULL,0) ) {
fprintf(stderr,
Failed to connect to database: Error: %s\n,
mysql_error(conn));
exit(2);
}
result = mysql_list_fields (conn, tabname, NULL);
field = mysql_fetch_field (result);
printf(Field =%s Type=%d Length=%d\n, field-name,
field-type, field-length);
}
Thanks in advance...
--
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: Ancient, unsolved high-CPU problem
it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancient, unsolved high-CPU problem
nothing on a server is weird, just not understood yet :-) so, 'show processlist' comes up with nothing. Does 'ps auxw' show any php processes still active? Does 'netstat -atp' show any established connections to mysql? How long does it take to re-create the problem? You've restarted mysql, how long to high cpu again? Did you start to rebuild indexes any time recently? You seem to have ruled out the web scripts, so can you stop the 30% cron job scripts running? in your other email CPU usage is 45%. The top figure is the percentage of CPU usage that mysql has used, compared with other processes. 92% of 45% (I believe). if you run 'vmstat 5' it will print out resource usage every 5 seconds (ignore the first line) which is more readable than top output as you see changes over time. Change 5 for an interval that suits your purpose (ie, if it takes 10 hours to happen, then vmstat 300). cheers, On 23 Sep 2008, at 15:04, Rene Fournier wrote: Appreciate the suggestions, some of which I've done. The processlist typically just shows the known PHP command-line scripts that run. Maybe 8-10 on average, 20 max. Here's a strange thing: If I stop all the requests to MySQL (shut down Apache, and exit all the commandline PHP scripts), MySQL's CPU usage remains high. So... no processes in MySQL, nothing hitting the database, yet MySQL CPU stays stuck at 60-70%. If I shutdown MySQL and restart, it's normal again. Weird, no? On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing List
Re: another INNODB vs MYISAM question
if you switch the default engine type any new tables would be created with that new engine type. it does not convert existing tables to your new format. if you have existing innodb tables you need to have the innodb settings active, in my.cnf On 15 Aug 2008, at 06:01, [EMAIL PROTECTED] wrote: Hello mysql, As I have previously mentioned, I installed WAMPSERVER 2.0 on my Windows XP pro box recently. It installed INNODB as the Default Engine. All of my legacy Databases are MYISAM and after the installation, I copied them all into the DATA folder and everything worked, even adding new tables etc. but the new stuff was INNODB. So I ended up with some MYISAM databases that contained INNODB tables in them. After a few weeks I got to thinking that mixing INNODB and MYISAM might not be a good thing and switched the Default Engine to MYISAM in my.ini file. I didn't just switch the default, I commented out all the INNODB calls in the my.ini file as well. As I half expected, all the databases that I had added INNODB tables failed when I tried to fire up the applications that used them. Although I am not new to mysql, I have had a bit of MYISAM tunnel vision with it so my question is, if I had just switched the default engine and NOT disabled the INNODB calls in my.ini, would that have prevented the problem? I restored all the MYISAM files and got everything back working again. I don't want to go through the lengthy reproduction exercise of reinstalling everything to test the theory so if someone has had some experience with this, I would appreciate hearing from them. -- Best regards, mikesz mailto:[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: Slow mysql connections
Do you have any other services running on the server, such as mail or
web? How long does it take to connect to those services ('telnet
server 25', or telnet server 80')?
What are you using to connect to mysql? how long does 'telnet
server 3306' take (assuming you are on the default port and have
firewall access)? How long does it take to connect from a local
server session?
When you are doing the above tests, do them with the server name and
with the IP address.
The above should let you rule out your client, and any server related
issues such as DNS or firewall. So you will know if it is a mysql
problem or not.
cheers,
Doug
On 13 Aug 2008, at 05:39, Krishna Chandra Prajapati wrote:
Hi all,
Connecting to mysql server (Production) is taking 5 to 6 seconds.
Production
has 16Gb ram. Previously it was using only 6GB ram. The details are as
follows. DNS looks fine.
free -m
total used free sharedbuffers
cached
Mem: 15899 15877 21 0 97
13913
-/+ buffers/cache: 1865 14033
Swap:0 0 0
---
vmstat
procs ---memory-- ---swap-- -io -system--
cpu
r b swpd free buff cache si sobibo in cs us
sy id
wa
0 0 0 26792 100600 142428680089 15000
7 1
92 0
1
-
cat /proc/meminfo
MemTotal: 16281204 kB
MemFree: 26692 kB
Buffers:100916 kB
Cached: 14243248 kB
SwapCached: 0 kB
Active:8338968 kB
Inactive: 7679900 kB
HighTotal: 0 kB
HighFree:0 kB
LowTotal: 16281204 kB
LowFree: 26692 kB
SwapTotal: 0 kB
SwapFree:0 kB
Dirty: 224 kB
Writeback: 0 kB
AnonPages: 1674640 kB
Mapped: 11616 kB
Slab: 215140 kB
PageTables: 4232 kB
NFS_Unstable:0 kB
Bounce: 0 kB
CommitLimit: 8140600 kB
Committed_AS: 2182136 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 252 kB
VmallocChunk: 34359738043 kB
--
ps aux (The below process is only process using cpu and memory)
mysql 3963 10.6 10.2 2190136 1671816 ? Sl Mar28 21019:10
/usr/sbin/mysqld --basedir=/usr --datadir=/v
cpu usage 10.6
memory usage 10.2
Please tell how what are the reasons and how can i solve this
problem.
Krishna Chandra Prajapati
MySQL DBA,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
