Re: Re: threading problems in linux C client
Hi, Thanx for responding. Only one worker thread was accesing MYSQL* myHdl. Nevertherless i have placed mutexes around all calls involing MYSQL*. main-thread: Defined MYSQL* myHdl as global variable and called mysql_init(), and mysql_connect(); worker: Defined MYSQL_RES* res and used myHdl to query with no problems. Crashes upon mysql_num_res or mysql_fetch_rows. gdb gives: Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 3076 (LWP 23491)] 0x400519e3 in mysql_fetch_row () from /usr/lib/libmysqlclient_r.so.10 (gdb) bt #0 0x400519e3 in mysql_fetch_row () from /usr/lib/libmysqlclient_r.so.10 #1 0x08054d40 in th_connection () #2 0x40036fef in pthread_start_thread () from /lib/i686/libpthread.so.0 #3 0x400370df in pthread_start_thread_event () from /lib/i686/libpthread.so.0 -Original Message- From: Eric Bergen [EMAIL PROTECTED] To: darren [EMAIL PROTECTED] Date: Wed, 30 Jun 2004 20:06:40 -0500 Subject: Re: threading problems in linux C client If you have multiple threads accessing the same connection remember to make sure that only one thread enters mysql_query() mysql_connect() or mysql_store_result() after you store the result another thread will be able to use that connection without problems. -Eric On Wed, 30 Jun 2004 11:01:35 +, darren [EMAIL PROTECTED] wrote: Hi all, I am writing a multi-threaded client that requires shared access to a single MySQL connection. The environment is Redhat 7.3 with the patched mysql-3.23.58-2.71.i386.rpm installed and compiled with -l libmysqlclient_r. The connection is made from the main thread and a MYSQL_RES* myres declared from the child thread is used to fetch results back to the thread. I get a Segmentation fault the first instance my child thread (only one) accesses MYSQL_RES through mysql_num_rows(myres). (gdb informed me that this is due to mysql_num_rows from libmysqlclient_r.so.10) I have read a few docs about writing MySQL multi-threaded clients and have called my_init() in my main prog and my_thread_init() at the first line of my thread handler. Any ideas? -- 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: Using REGEXP
On 30 Jun 2004 17:45:06 +0200, wrote: In article [EMAIL PROTECTED], SGreen writes: SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON INSTR(t1.txtDevPostCode, sc.short_code) =1 This is the same as SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON t1.txtDevPostCode LIKE concat(sc.short_code, '%') and this query would use indexes on txtDevPostCode and short_code. Thnx to all for this mysql primer, will let you know how it goes, but have been dragged off to another crisis zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select, = and like
Hi,
im running mysql-4.0.18-2, all tables are innodb tables. I found the
following which i dont understand:
one of my tables is named 'PARAMSET_EXT':
mysql explain PARAMSET_EXT;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| PS_Name| varchar(64) | | PRI | | |
| T_Instance | varchar(64) | | PRI | | |
| User | varchar(64) | YES | | NULL| |
| Modified | bigint(80) | | | 0 | |
| D_ID | int(11) | YES | MUL | NULL| |
| Confirmed | tinyint(1) | | | 0 | |
++-+--+-+-+---+
6 rows in set (0.00 sec)
mysql
now i send the following select statement and get the
following results:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where PS_Name = 1;
+-+--++
| PS_Name | T_Instance | User |
+-+--++
| 1 | L2_SD_LOOP_DATA1 | vai_l2 |
| 1 | VA_SD_FUCHSPLATTE| vai_l2 |
| 1 | VA_SD_GIESSPULVER| vai_l2 |
| 1 | VA_SD_GIESSROHRWECHSEL | vai_l2 |
| 1 | VA_SD_STOPFENFUEHRUNG| vai_l2 |
| 1 | VA_SD_VERTEILEREINBAUTEN | vai_l2 |
+-+--++
6 rows in set (0.00 sec)
mysql
now i send the following select statement and get the
following result:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where T_Instance =
VA_SD_STOPFENFUEHRUNG;
Empty set (0.01 sec)
mysql
ok, i expected to get the entry
| 1 | VA_SD_STOPFENFUEHRUNG| vai_l2 |
but, i got an empty set.
(note, that selecting for T_Instances other than
VA_SD_STOPFENFUEHRUNG works!)
however, the following statement works fine:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where T_Instance like
VA_SD_STOPFENFUEHRUNG;
+-+---++
| PS_Name | T_Instance| User |
+-+---++
| 1 | VA_SD_STOPFENFUEHRUNG | vai_l2 |
and now, since the _ character is a wildcard in the like context,
i tried:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where T_Instance like
VA\_SD\_STOPFENFUEHRUNG;
Empty set (0.00 sec)
mysql
which results in an empty set.
why does selecting for VA_SD_STOPFENFUEHRUNG not work?
Is this some kind of bug or is there anything iam doing wrong?
note, that there are NO special characters in VA_SD_STOPFENFUEHRUNG,
except of the _ which is allowed in this context, isnt it?
regards
Karsten
--
perl -MLWP::Simple -e '$u=http://www.dilbert.com;foreach(split//,
LWP::Simple::get($u)){LWP::Simple::getstore($u$1,dilbert.gif)
if(m#IMG SRC=(/comics/dilbert/archive/images/[^]*)#)}'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Startup
David Scott [EMAIL PROTECTED] wrote: Then get a copy of mySQL CC http://dev.mysql.com/downloads/mysqlcc.html Also take a look at MySQL Administrator http://dev.mysql.com/downloads/administrator/index.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql client connectivity and orphaned connections
Jim [EMAIL PROTECTED] wrote: I'm just wondering if anyone can tell me exactly what happens when one is using the mysql client on a remote server and the connection to that server (in this case over ssh) gets dropped. if your ssh session suddenly closes, the only thing that happens is that MySQL command line client will exit. This will not stop server from processing OPTIMIZE TABLE as this is an atomic operation. In this case, I was in the midst of a long OPTIMIZE TABLE. Stepped out to get some coffee and the ssh connection timed out. When I log back in again, and log in to mySQL using the client, I can see that the first mysql session is still in effect, and show processlist shows that the state of the orphaned user is Repair with keycache. But, this is taking too long (over 45 minutes) I think. In some cases this may take DAYS, not minutes. :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: time on tenth of secounds
Matthias Eireiner [EMAIL PROTECTED] wrote: problem is, that I want to save the time with the fraction of seconds. as far as I know MySQL is fine with something like D HH:MM:SS.fraction, but doesn't store the fraction. Is there anything that could fix that problem? I thought about an extra integer column only for the fraction, but I would love to use the MySQL built in sub and add functions. There is no support for fractions in DATETIME type. Feel free to add the fraction manually into another column. Do you really need database's internal functions to support high-resolution time? MySQL is in no case a real-time DBMS. Even inserting a NOW() value into a high-resolution DATETIME column will give you an unpredictable result as you never know how much time for server it will take to process the query. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
utf8_polish_ci
Hello, I'm using MySQL 4.1.2 and it should be available utf8_polish_ci collation. But, it seems that it is not. # Query: # SHOW COLLATION LIKE 'utf%' # 'Collation''Charset','Id','Default','Compiled','Sortlen' 'utf8_general_ci','utf8','33','Yes','Yes','1' 'utf8_bin','utf8','83','','Yes','1' The same results are for windows and linux. Is this a bug ? ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: defaults for net_read_timeout and net_write_timeout?
Marten Lehmann [EMAIL PROTECTED] wrote: what are the defaults for the following options? net_read_timeout 30 seconds. net_write_timeout 60 seconds. See http://dev.mysql.com/doc/mysql/en/Server_system_variables.html for details. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API -- huge result sets slowin me down
Matt Eaton [EMAIL PROTECTED] wrote:
Try to profile application from that point ...
if (mysql_real_query(dbase,sqlBuff,strlen(sqlBuff))) {
printf(Pool Attributes Select Failed... dumbass\n);
fprintf(stderr, Error: %s\n,
mysql_error(dbase));
exit(1);
}
result = mysql_store_result(dbase);
numRows=mysql_num_rows(result);
... to that point and then from here to ..
for (i=0;inumRows;i++) {
row = mysql_fetch_row(result);
tempq=atoi(row[1]);
tempP=atoi(row[0]);
genAttrib[tempP][tempq]=atoi(row[2]);
}
..here. I suppose the cycle could be a slowdown/
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Security
Sheraz [EMAIL PROTECTED] wrote: How can i achieve Security acpect in mysql? How secure can we make transactions over internet for 3306 ? Use SSL. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried
Re: php-mysql RPM
[EMAIL PROTECTED] wrote: I'm installing the php-mysql-4.3.6-1.3.i386.rpm and it's complaining about a dependency libmysqlclient.so.10. My mysql server version is 4.0.20. Can anyone tell me where I can get libmysqlclient.so.10. Download MySQL RPMs from http://dev.mysql.com/ The package containing libmysqlclient is MySQL-shared. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: authentication error
Chip Bell [EMAIL PROTECTED] wrote: Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Are you talking about MySQL user auth OR about some kind of email user authentication against data stored in MySQL database? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql growing pains, 4 days to create index on one table!
Are you running this under Microsoft Windows? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index problem
Oropeza Querejeta, Alejandro [EMAIL PROTECTED] wrote: Hi, i'm trying to create an index on a table with 199 million records. The problem is that is taking too long (8 hours and is not yet finnished). does anyone have any idea? Wait until it finishes.:) We have tables where index creation takes tens of hours or even sometimes _days_, not even hours. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
The email users authenticate against a MySQL table... -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 6:12 AM To: [EMAIL PROTECTED] Subject: Re: authentication error Chip Bell [EMAIL PROTECTED] wrote: Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Are you talking about MySQL user auth OR about some kind of email user authentication against data stored in MySQL database? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select, = and like
Hi Karsten,
I'm quite sure you have special characters in the end of the field
T_Instance for T_Instance like VA_SD_STOPFENFUEHRUNG.
Can you display the contents of this field in hex format?
Regards,
Laercio.
-Original Message-
From: Karsten Backhaus
[mailto:[EMAIL PROTECTED]
Sent: quinta-feira, 1 de julho de 2004 06:24
To: [EMAIL PROTECTED]
Subject: select, = and like
Hi,
im running mysql-4.0.18-2, all tables are innodb tables. I found the
following which i dont understand:
one of my tables is named 'PARAMSET_EXT':
mysql explain PARAMSET_EXT;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| PS_Name| varchar(64) | | PRI | | |
| T_Instance | varchar(64) | | PRI | | |
| User | varchar(64) | YES | | NULL| |
| Modified | bigint(80) | | | 0 | |
| D_ID | int(11) | YES | MUL | NULL| |
| Confirmed | tinyint(1) | | | 0 | |
++-+--+-+-+---+
6 rows in set (0.00 sec)
mysql
now i send the following select statement and get the following results:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where PS_Name
mysql = 1;
+-+--++
| PS_Name | T_Instance | User |
+-+--++
| 1 | L2_SD_LOOP_DATA1 | vai_l2 |
| 1 | VA_SD_FUCHSPLATTE| vai_l2 |
| 1 | VA_SD_GIESSPULVER| vai_l2 |
| 1 | VA_SD_GIESSROHRWECHSEL | vai_l2 |
| 1 | VA_SD_STOPFENFUEHRUNG| vai_l2 |
| 1 | VA_SD_VERTEILEREINBAUTEN | vai_l2 |
+-+--++
6 rows in set (0.00 sec)
mysql
now i send the following select statement and get the following result:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where
mysql T_Instance = VA_SD_STOPFENFUEHRUNG;
Empty set (0.01 sec)
mysql
ok, i expected to get the entry
| 1 | VA_SD_STOPFENFUEHRUNG| vai_l2 |
but, i got an empty set.
(note, that selecting for T_Instances other than VA_SD_STOPFENFUEHRUNG
works!)
however, the following statement works fine:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where
mysql T_Instance like VA_SD_STOPFENFUEHRUNG;
+-+---++
| PS_Name | T_Instance| User |
+-+---++
| 1 | VA_SD_STOPFENFUEHRUNG | vai_l2 |
and now, since the _ character is a wildcard in the like context, i
tried:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where
mysql T_Instance like VA\_SD\_STOPFENFUEHRUNG;
Empty set (0.00 sec)
mysql
which results in an empty set.
why does selecting for VA_SD_STOPFENFUEHRUNG not work?
Is this some kind of bug or is there anything iam doing wrong?
note, that there are NO special characters in VA_SD_STOPFENFUEHRUNG,
except of the _ which is allowed in this context, isnt it?
regards
Karsten
--
perl -MLWP::Simple -e '$u=http://www.dilbert.com;foreach(split//,
LWP::Simple::get($u)){LWP::Simple::getstore($u$1,dilbert.gif)
if(m#IMG SRC=(/comics/dilbert/archive/images/[^]*)#)}'
--
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]
select query that uses a temporary table
Hi All, There's something that bothers me.. I have a query that uses a temporary table (has a necessary GROUP BY clause). The query also uses ORDER BY clause (necessary too). And I also use LIMIT clause. If the query finds 10,000 rows, then MySQL will insert 10,000 rows into the temporary table and sort, which makes the query very slow... :( Any suggestions on how can I speed it up? (I would be satisfied with the top 500 rows, no need in all the 10,000) Mabye, is there a way to tell MySQL to limit the temporary table up to 500 rows? so, when a row is matching into the top 500 rows, the last row will be dropped out (in case the table is on limit), and the new matched row will be inserted into the right place in the temporary table... -thanks, Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crash with join query in 4.1.1
Is this bug a known one? I can reliably crash 4.1.1 with this query every time right now, which is a bit of a bummer. From the error log: Some pointers may be invalid and cause the dump to abort... thd-query at 0x86644a0 = SELECT profileid, profile_name, domain_name, domainid FROM profiles, domain_profile, domains WHERE profileid=ref_profileid AND domainid=ref_domainid AND ref_domainid in (SELECT domainid FROM domains LEFT JOIN domain_profile ON ref_domainid=domainid GROUP BY domain_name HAVING count(ref_profileid) 1) ORDER BY domain_name thd-thread_id=2 ~/crash resolve_stack_dump -s mysqld.sym -n m.stack 0x8089167 handle_segfault + 423 0x82da818 pthread_sighandler + 184 0x80baa71 get_best_combination__FP4JOIN + 145 0x80b899e make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 4158 0x80b5271 optimize__4JOIN + 369 0x80b780f mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_orderT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 767 0x80b487e handle_select__FP3THDP6st_lexP13select_result + 174 0x8096b1a mysql_execute_command__FP3THD + 1354 0x809b3f1 mysql_parse__FP3THDPcUi + 177 0x80957ff dispatch_command__F19enum_server_commandP3THDPcUi + 1631 0x8095192 do_command__FP3THD + 162 0x8094907 handle_one_connection + 551 0x82d7fcc pthread_start_thread + 220 0x830b8fa thread_start + 4 CREATE TABLE `domain_profile` ( `ref_domainid` int(10) unsigned NOT NULL default '0', `ref_profileid` int(10) unsigned NOT NULL default '0', `modified` timestamp NOT NULL, `created` timestamp NOT NULL, KEY `idx_domainid` (`ref_domainid`), KEY `idx_profileid` (`ref_profileid`), CONSTRAINT `0_1700` FOREIGN KEY (`ref_profileid`) REFERENCES `profiles` (`profileid`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `0_1701` FOREIGN KEY (`ref_domainid`) REFERENCES `domains` (`domainid`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Domain::profile mapping'; CREATE TABLE `domains` ( `domainid` int(10) unsigned NOT NULL auto_increment, `domain_name` char(200) NOT NULL default 'INVALID', `domain_active` char(1) NOT NULL default 'N', `modified` timestamp NOT NULL, `created` timestamp NOT NULL, PRIMARY KEY (`domainid`), KEY `idx_active` (`domain_active`) ) TYPE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Master list of domains for customer'; CREATE TABLE `profiles` ( `profileid` int(10) unsigned NOT NULL auto_increment, `profile_name` varchar(100) NOT NULL default 'Default Profile', `modified` timestamp NOT NULL, `created` timestamp NOT NULL, PRIMARY KEY (`profileid`) ) TYPE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of profiles'; mysql select * from profiles; +---+--+-+-+ | profileid | profile_name | modified| created | +---+--+-+-+ | 1 | Built-in Profile | 2004-06-28 10:00:42 | -00-00 00:00:00 | | 2 | Test Profile | 2004-07-01 10:32:04 | 2004-07-01 10:32:04 | +---+--+-+-+ mysql select * from domain_profile; +--+---+-+-+ | ref_domainid | ref_profileid | modified| created | +--+---+-+-+ |1 | 1 | 2004-06-29 17:12:41 | 2004-06-29 17:12:41 | |2 | 1 | 2004-07-01 09:29:57 | 2004-07-01 09:29:57 | |2 | 2 | 2004-07-01 11:37:19 | 2004-07-01 11:37:19 | |1 | 2 | 2004-07-01 11:45:28 | 2004-07-01 11:45:28 | +--+---+-+-+ mysql select * from domains; +--+-+---+-+-+ | domainid | domain_name | domain_active | modified| created | +--+-+---+-+-+ |1 | test.domain | Y | 2004-06-29 17:12:15 | 2004-06-29 17:12:10 | |2 | foo.domain | Y | 2004-06-30 10:52:34 | 2004-06-30 10:52:34 | |3 | jack.domain | Y | 2004-06-30 15:34:04 | 2004-06-30 15:34:04 | +--+-+---+-+-+ *** 1. row *** id: 1 select_type: PRIMARY table: profiles type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using temporary; Using filesort *** 2. row *** id: 1 select_type: PRIMARY table: domain_profile type: ref possible_keys: idx_domainid,idx_profileid key: idx_profileid key_len:
Access denied for user: 'root@localhost' (Using password: NO)
Hi - I am new to MySQL - I just recently re-installed MySQL by rpm and when ever I try to issue a command from the shell prompt, such as mysqladmin or mysqlshow, I would get the error of 'Access denied for user: '[EMAIL PROTECTED]' mailto:'[EMAIL PROTECTED]' (Using password: NO)'. Could someone please tell me what I need to set or did not set. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for user: 'root@localhost' (Using password: NO)
Re-installing does not get rid of your old passwords. You need to use the -p option. Nguyen, Long P (Mission Systems) wrote: Hi - I am new to MySQL - I just recently re-installed MySQL by rpm and when ever I try to issue a command from the shell prompt, such as mysqladmin or mysqlshow, I would get the error of 'Access denied for user: '[EMAIL PROTECTED]' mailto:'[EMAIL PROTECTED]' (Using password: NO)'. Could someone please tell me what I need to set or did not set. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access denied for user: 'root@localhost' (Using password: NO)
shouldn't I be able to issue commands at the shell it goes and does it thing and give back output? or does this mean I have a passwd already set and it's secured? -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 8:57 AM To: Nguyen, Long P (Mission Systems) Cc: [EMAIL PROTECTED] Subject: Re: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) Re-installing does not get rid of your old passwords. You need to use the -p option. Nguyen, Long P (Mission Systems) wrote: Hi - I am new to MySQL - I just recently re-installed MySQL by rpm and when ever I try to issue a command from the shell prompt, such as mysqladmin or mysqlshow, I would get the error of 'Access denied for user: '[EMAIL PROTECTED]' mailto:'[EMAIL PROTECTED]' (Using password: NO)'. Could someone please tell me what I need to set or did not set. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for user: 'root@localhost' (Using password: NO)
On Thursday 01 July 2004 09:08 am, Nguyen, Long P (Mission Systems) wrote: shouldn't I be able to issue commands at the shell it goes and does it thing and give back output? or does this mean I have a passwd already set and it's secured? http://dev.mysql.com/doc/mysql/en/User_Account_Management.html This might help you out. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Performance
What does the network setup look like? What type of data is being replicated varchar, text, blob? Is the network connection being dropped or are there any errors being logged anywhere? -Original Message- From: MaFai To: [EMAIL PROTECTED] Sent: 6/30/04 10:34 PM Subject: Replication Performance Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? Best regards. MaFai [EMAIL PROTECTED] 2004-07-01 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
Do the math and see what your memory usage is with 100 simultaneous connections. If I recal you said you had three gigs of ram. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 5:27 AM Subject: RE: authentication error Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE:
Re: MySQL and VBquestion - problem with query that returns 100000+ records
Shawn I took your advice and let the app run for a good 12+ minutes - miraculously it worked and the resulting report did show up.I am now convinced that the status shown in the task manager is misleading. I am trying to implement your suggestion of using an asynchronous call. So I am doing something like Set Rs = New ADODB.Recordset Rs.CursorLocation = adUseServer 'adUseClient Rs.Open SQL, connection, adOpenForwardOnly, adLockReadOnly, adAsyncFetch Question is how do i poll for the status of adStateExecuting and adStateFetching ? Will the execution get to the 'do loop' at all (before the Rs.Open has completed) if I do something like: Rs.Open SQL, connection, adOpenForwardOnly, adLockReadOnly, adAsyncFetch do while (adStateExecuting and adStateFetching ) . . DoEvents loop OR Do I need to do something like Dim WithEvents Rs as ADODB.recordset and then use the corresponding event handler. The irony of the entire situation is that we were thinking of migrating to MySQL in hopes of improving the performance over MS Access. While there might me other advantages of using MySQL over MS Access, it is definitely failing on the performance front and thats a shame. I am also going to try your modified query and will post the results of the EXPLAIN soon. Thanks a lot for all your help; Greg From: [EMAIL PROTECTED] To: Greg Zimmermack [EMAIL PROTECTED] Subject: Re: MySQL and VBquestion - problem with query that returns 10+ records Date: Wed, 30 Jun 2004 12:37:16 -0400 Greg, I think what you are calling hung may really be a blocked condition. Using ADO, you have the option of your queries being synchronous or asynchronous. A synchronous query will wait for the results before returning control to your program. In other words, your application hands over total control of it's execution to ADO. ADO submits your query then goes to sleep until one of several things occurs: it gets a response from the server, it times out, or any of the other events that will also rouse ADO from its slumber. The task manager in windows checks the status of the various executing applications by sending them windows messages. Since your application is in essence frozen until ADO returns from its call, your application will not respond to any windows messages. Other things like move window, resize window, and close window are also windows messages so it will seem to be completely unresponsive to any action you take until ADO releases its control over the situation. Try setting up the request as an asynchronous call (set the option on your open to adAsynchFetch) and periodically monitoring the state of your recordset for adStateExecuting and adStateFetching (may I suggest a do...loop, a while...wend, or a timer object? ) to see if you are bound by the query's execution or the data retrieval. Your application will also be able to respond to the windows messages as its default message handler will not be waiting on ADO to return. (You can force the default message handler to deal with any outstanding messages by executing a DoEvents call.) You said earlier that if you tried the query manually, you only saw 1000 rows. That clues me in that you are using one of the GUI admin tools as your manual interface. Some of them limit database responses to only the first 1000 rows. I don't know if you can change that value or not, you will have to consult the docs for your tool. However, in the same location where you execute your SQL by hand just add the word EXPLAIN to the beginning of it - EXPLAIN SELECT . (rest of statement) - and you will get a response from the server detailing how the engine plans to deal with that particular query. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Greg Zimmermack [EMAIL PROTECTED]To: [EMAIL PROTECTED] ail.com cc: Fax to: 06/30/2004 12:00 Subject: Re: MySQL and VBquestion - problem with query that returns PM10+ records Thanks for your reply Shawn I will try running the modified query you sent me and check if that helps - meanwhile here are the replies to some of your questions. *I know the application is hung by checking its status in the Windows Task manager( it shows the status as 'Not Responding'). It looks like the app hangs before the query is done executing *The version of MySQL is 4.0.16-nt *I am not sure how to do the EXPLAIN you mentioned. *I removed the ORDERBY clause but that did not make any difference. Appreciate your help Greg From: [EMAIL PROTECTED] To: Greg Zimmermack [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: MySQL and VBquestion - problem with query that returns 10+ records Date: Wed, 30 Jun 2004 11:01:29 -0400 Your SQL syntax is
RE: authentication error
Ok, I have no read_buffer anywhere... 256M (key) + 20 (sort) * 100 (connections) Correct? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:09 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do the math and see what your memory usage is with 100 simultaneous connections. If I recal you said you had three gigs of ram. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 5:27 AM Subject: RE: authentication error Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I
bugzilla not working now after MySQL re-installed
Your thoughts on this would be appreciated.
MySQL was re-installed and now Bugzilla is not working now I am getting this error
when I bring up - http://localhost/bugzilla/ http://localhost/bugzilla/
*
Software error:
Bugzilla is currently broken. Please try again later. If the problem persists, please
contact [EMAIL PROTECTED] The error you should quote is: Can't connect to local MySQL
server through socket '/tmp/mysql.sock' (2) at globals.pl line 140.
For help, please send mail to the webmaster ([EMAIL PROTECTED] mailto:[EMAIL
PROTECTED] ), giving this error message and the time and date of the error.
*
The re-installation of MySQL did something... below is the output of the
./checksetup.pl
[EMAIL PROTECTED] bugzilla-2.16.5]# ./checksetup.pl
Checking perl modules ...
Checking for AppConfig (v1.52) ok: found v1.56
Checking for CGI::Carp (any) ok: found v1.27
Checking forData::Dumper (any) ok: found v2.121
Checking for Date::Parse (any) ok: found v2.27
Checking for DBI (v1.13) ok: found v1.42
Checking for DBD::mysql (v1.2209) ok: found v2.9003
Checking for File::Spec (v0.82) ok: found v0.87
Checking for File::Temp (any) ok: found v0.14
Checking forTemplate (v2.07) ok: found v2.13
Checking for Text::Wrap (v2001.0131) ok: found v2001.09291
Checking for CGI::Carp (any) ok: found v1.27
The following Perl modules are optional:
Checking for GD (v1.19) ok: found v1.20
Checking for Chart::Base (v0.99) ok: found v2.3
Checking for XML::Parser (any) ok: found v2.34
Checking user setup ...
Precompiling templates ...
DBI connect(';localhost;3306','bugs',...) failed: Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (2) at ./checksetup.pl line 1189
[Thu Jul 1 10:00:17 2004] checksetup.pl: DBI connect(';localhost;3306','bugs',...)
failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) at
./checksetup.pl line 1189
h1Software error:/h1
preCan't connect to the mysql database. Is the database installed and
up and running? Do you have the correct username and password selected in
localconfig? AHA
/pre
p
For help, please send mail to this site's webmaster, giving this error message
and the time and date of the error.
/p
[Thu Jul 1 10:00:17 2004] checksetup.pl: Can't connect to the mysql database. Is the
database installed and
[Thu Jul 1 10:00:17 2004] checksetup.pl: up and running? Do you have the correct
username and password selected in
[Thu Jul 1 10:00:17 2004] checksetup.pl: localconfig? AHA
[Thu Jul 1 10:00:17 2004] checksetup.pl:
[EMAIL PROTECTED] bugzilla-2.16.5]#
System.Data.MySql
Hello! Is there an implementation os System.Data.MySQL for the .net-framework? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bugzilla not working now after MySQL re-installed
Nguyen, Long P (Mission Systems) wrote: Your thoughts on this would be appreciated. MySQL was re-installed ,,, preCan't connect to the mysql database. Is the database installed and up and running? Do you have the correct username and password selected in localconfig? What else do you need besides the error message above? It appears that MySQL isn't running. Have you *tried* accessing it from the command-line client, using the username/password set in your localconfig? That would be the first logical step... -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
My bad, just realized that read_buffer_size USED to be record_buffer My total @ 100 connections is 2356M. I'm going to guess that I should shave that down a bit?? -Original Message- From: Chip Bell Sent: Thursday, July 01, 2004 10:17 AM To: Victor Pendleton; [EMAIL PROTECTED] Subject: RE: authentication error Ok, I have no read_buffer anywhere... 256M (key) + 20 (sort) * 100 (connections) Correct? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:09 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do the math and see what your memory usage is with 100 simultaneous connections. If I recal you said you had three gigs of ram. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 5:27 AM Subject: RE: authentication error Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is
RE: bugzilla not working now after MySQL re-installed
MySQL is running.. [EMAIL PROTECTED] /]# ps -ef | grep mysql root 1338 1 0 10:51 pts/200:00:00 /bin/sh /usr/bin/mysqld_safe --d mysql 1362 1338 0 10:51 pts/200:00:00 /usr/sbin/mysqld --basedir=/ --d root 1374 1124 0 10:51 pts/200:00:00 grep mysql -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:38 AM To: Nguyen, Long P (Mission Systems); [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: Your thoughts on this would be appreciated. MySQL was re-installed ,,, preCan't connect to the mysql database. Is the database installed and up and running? Do you have the correct username and password selected in localconfig? What else do you need besides the error message above? It appears that MySQL isn't running. Have you *tried* accessing it from the command-line client, using the username/password set in your localconfig? That would be the first logical step... -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: System.Data.MySql
So far the one I have had the best luck with is ByteFX.Data.MySqlClient I use it in both VB.Net and C# against MySql 4.1.2 on Windows and Linux. It's here: http://www.bytefx.com Larry Lowry - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 01, 2004 7:35 AM Subject: System.Data.MySql Hello! Is there an implementation os System.Data.MySQL for the .net-framework? 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]
MySQL 4.1.3-beta has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 4.1.3, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is the first beta development release, adding new features and fixing recently discovered bugs. The change to beta level indicates, that all planned major features for MySQL 4.1 have been implemented by now. The focus is now on testing and stabilizing these new features and the rest of the code base. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Language-specific collations were added for the ucs2 and utf8 Unicode character sets: Icelandic, Latvian, Romanian, Slovenian, Polish, Estonian, Swedish, Turkish, Czech, Danish, Lithuanian, Slovak, Spanish, Traditional Spanish. * Support for per-connection time zones was added. Now you can select current time zone for connection by setting system @@time_zone variable to a value such as '+10:00' or 'Europe/Moscow' (where 'Europe/Moscow' is the name of one of the time zones described in the system tables). Functions like CURRENT_TIMESTAMP, UNIX_TIMESTAMP, and so forth honor this time zone. Values of TIMESTAMP type are also interpreted as values in this time zone (so now our TIMESTAMP type behaves similar to Oracle's TIMESTAMP WITH LOCAL TIME ZONE, that is, values stored in such a column are normalized towards UTC and converted back to the current connection time zone when they are retrieved from such a column). * Basic datetime with time zone conversion function CONVERT_TZ() was added. It assumes that its first argument is a datetime value in the time zone specified by its second argument and returns equivalent datetime value in time zone specified by its third argument. * CHECK TABLE now can be killed. See section KILL Syntax in the manual. * Warning: Incompatible change! C API change: mysql_shutdown() now requires a second argument. This is a source-level incompatibility that affects how you compile client programs; it does not affect the ability of compiled clients to communicate with older servers. See section mysql_shutdown() in the manual. * OPTIMIZE TABLE for InnoDB tables is now mapped to ALTER TABLE instead of ANALYZE TABLE. * sync_frm is now a settable global variable (not only a startup option). * Added the sync-binlog=N global variable and startup option, which makes the MySQL server synchronize its binary log to disk (fdatasync()) after every Nth write to the binary log. * Changed the slave SQL thread to print fewer useless error messages (no more message duplication; no more messages when an error is skipped (because of slave-skip-errors). * DROP DATABASE IF EXISTS, DROP TABLE IF EXISTS, single-table DELETE and single-table UPDATE are now written to the binary log even if they changed nothing on the master (for example, even if the DELETE matched no row). The old behavior sometimes caused bad surprises in replication setups. * Replication and mysqlbinlog now have better support for the case that the session character set and collation variables are changed within a given session. See section Replication Features and Known Problems in the manual. * Added --innodb-safe-binlog server option, which adds consistency guarantees between the content of InnoDB tables and the binary log. See section The Binary Log in the manual. * LIKE now supports the use of a prepared statement parameter or delimited constant expression as the argument to ESCAPE (Bug #4200). Bugs fixed: * Added missing root user to Windows version of mysqld. (Bug #4242) * Fixed bug in prepared EXPLAIN statement which led to server crash. (Bug #4271) * Fixed a bug of using parameters in some prepared statements via SQL syntax. (Bug #4280) * Fixed a bug in MERGE tables created with INSERT_METHOD=LAST, that were not able to report a key number that caused Duplicate entry error for UNIQUE key in INSERT. As a result, error message was not precise enough (error 1022 instead of error 1062) and INSERT ... ON DUPLICATE KEY UPDATE did not work. (Bug #4008) * Fixed a bug in DELETE from a table with FULLTEXT indexes which under rare circumstances could result in a
RE: authentication error
Can you tweak your key_buffer and see what results you get? -Original Message- From: Chip Bell To: Chip Bell; Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 9:38 AM Subject: RE: authentication error My bad, just realized that read_buffer_size USED to be record_buffer My total @ 100 connections is 2356M. I'm going to guess that I should shave that down a bit?? -Original Message- From: Chip Bell Sent: Thursday, July 01, 2004 10:17 AM To: Victor Pendleton; [EMAIL PROTECTED] Subject: RE: authentication error Ok, I have no read_buffer anywhere... 256M (key) + 20 (sort) * 100 (connections) Correct? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:09 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do the math and see what your memory usage is with 100 simultaneous connections. If I recal you said you had three gigs of ram. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 5:27 AM Subject: RE: authentication error Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell;
Re: Using Access as client all fields are marked #Deleted
I've seen this before and do not exactly remember why, think it had something to do with relationships and links, try removing all relationships and see if that helps, if not try deleting tables and relinking them from scratch (found the link manager doesn't always fix problems for some reason). Daniel Eliav Sharvit wrote: Hi all, My setup is windows 2000, Access 2000 and Mysql built from source on Debian. Some tables can be seen but all new entries are marked #Deleted. If I close the table in Access and re-open it, its looks fine. Others have every field marked #Deleted and nothing I do makes those tables readable. HOWEVER, queries that rely on the data work accurately but show the results as #Deleted. I've installed myodbc, the latest MDAC and Jet Service packs. Is there some test or diagnostic procedure to establish what isn't working? I think I've followed all the documented steps but the lack of diagnostics makes this very difficult. Thanks in advance, Daniel Eliav Sharvit _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
odd problem
Hi,
The code in my function below (insert_internet_usage) has suddenly
dramatically slowed down and I don't understand why. The logs I'm parsing
are the same size as before, but what used to take 15 minutes has shot up to
about 4 hours. It's almost as if the indexes had stopped working.
I wonder if someone here can help me out please? I've tried to include all
the information which I thought might be relevant below
but if you need more let me know. There haven't been any errors reported in
the log since I restarted mysql. Here's my script log:
Thu Jul 1 08:56:18 2004: PARSING
/sawmill/rawlog/SG_CSGL02_main_47061221.log.gz ...
Thu Jul 1 09:00:37 2004: BULK_TABLE_INSERT ...
Thu Jul 1 09:01:43 2004: INSERT_URL_SERVERS ...
Thu Jul 1 09:02:26 2004: INSERT_URL_PATHS ...
Thu Jul 1 09:03:54 2004: INSERT_URL_QUERIES ...
Thu Jul 1 09:04:32 2004: INSERT_USER_AGENTS ...
Thu Jul 1 09:05:09 2004: INSERT_URL_VISITS ...
Thu Jul 1 09:10:06 2004: INSERT_INTERNET_USAGE ...
Thu Jul 1 09:19:31 2004: DELETE_BULK_TABLE ...
Thu Jul 1 09:19:32 2004: PARSING
/sawmill/rawlog/SG_CSGL02_main_47061121.log.gz ...
Thu Jul 1 09:29:02 2004: BULK_TABLE_INSERT ...
Thu Jul 1 09:31:36 2004: INSERT_URL_SERVERS ...
Thu Jul 1 09:33:21 2004: INSERT_URL_PATHS ...
Thu Jul 1 09:36:26 2004: INSERT_URL_QUERIES ...
Thu Jul 1 09:38:07 2004: INSERT_USER_AGENTS ...
Thu Jul 1 09:39:37 2004: INSERT_URL_VISITS ...=== This is where the
time difference has
Thu Jul 1 09:54:19 2004: INSERT_INTERNET_USAGE ... === suddenly
increased
Thu Jul 1 13:28:37 2004: DELETE_BULK_TABLE ...
Thu Jul 1 13:28:38 2004: PARSING
/sawmill/rawlog/SG_CSGL02_main_47061021.log.gz ...
Thu Jul 1 13:39:09 2004: BULK_TABLE_INSERT ...
Thu Jul 1 13:41:59 2004: INSERT_URL_SERVERS ...
Thu Jul 1 13:43:52 2004: INSERT_URL_PATHS ...
Thu Jul 1 13:47:11 2004: INSERT_URL_QUERIES ...
Thu Jul 1 13:48:55 2004: INSERT_USER_AGENTS ...
Thu Jul 1 13:50:35 2004: INSERT_URL_VISITS ...
Thu Jul 1 14:06:55 2004: INSERT_INTERNET_USAGE ...
time now is 16:13 and it's still going.
This is the function:
sub insert_internet_usage(){
my $sql = qq {INSERT internet_usage (uid,time,ip,urlid,timetaken,
cs_size,sc_size,method_ID,action_ID,virus_ID,userag
ent_ID)
SELECT bt.user, bt.time, bt.ip, uv.urlid, bt.timetaken,
bt.cs_size, bt.sc_size, bt.method_ID, bt.action_ID, b
t.virus, ua.ID
FROM bulk_table bt
INNER JOIN url_servers us ON us.server=bt.server
INNER JOIN url_paths up ON up.path=bt.path
INNER JOIN url_queries uq ON uq.query=bt.query
INNER JOIN user_agents ua ON
ua.useragent=bt.useragent
INNER JOIN url_visit uv ON uv.url_server_ID=us.ID
AND uv.url_path_ID=up.ID
AND uv.url_scheme_ID=bt.scheme_ID
AND uv.url_query_ID=uq.ID };
my $timenow=localtime();
print PL \n$timenow: INSERT_INTERNET_USAGE ...;
$dbh2-do($sql) or die $dbh2-errstr;
$dbh2-commit() or die $dbh2-errstr;
}
Here's a bit of extra info:
mysql desc url_visit;
+-+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+-+-+--+-+-++
| urlid | int(11) | | PRI | NULL| auto_increment |
| url_scheme_ID | int(11) | | | 0 ||
| url_server_ID | int(11) | | MUL | 0 ||
| url_path_ID | int(11) | | | 0 ||
| url_query_ID| int(11) | | | 0 ||
| url_category_ID | int(11) | | | 0 ||
+-+-+--+-+-++
6 rows in set (0.01 sec)
mysql desc internet_usage;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| uid | varchar(10) | YES | MUL | NULL| |
| time | datetime| YES | | NULL| |
| ip | int(10) | YES | | 0 | |
| urlid| int(11) | | | 0 | |
| timetaken| int(11) | YES | | 0 | |
| cs_size | int(11) | YES | | 0 | |
| sc_size | int(11) | YES | | 0 | |
| method_ID| int(11) | YES | | 0 | |
| action_ID| int(11) | YES | | 0 | |
| virus_ID | int(11) | YES | | 0 | |
| useragent_ID | int(11) | YES | | 0 | |
+--+-+--+-+-+---+
11 rows in set (0.00 sec)
mysql show status;
+--++
|
RE: odd problem
Your cardinality is null. Analyze the table and then do a show index.
-Original Message-
From: J S
To: [EMAIL PROTECTED]
Sent: 7/1/04 10:22 AM
Subject: odd problem
Hi,
The code in my function below (insert_internet_usage) has suddenly
dramatically slowed down and I don't understand why. The logs I'm
parsing
are the same size as before, but what used to take 15 minutes has shot
up to
about 4 hours. It's almost as if the indexes had stopped working.
I wonder if someone here can help me out please? I've tried to include
all
the information which I thought might be relevant below
but if you need more let me know. There haven't been any errors reported
in
the log since I restarted mysql. Here's my script log:
Thu Jul 1 08:56:18 2004: PARSING
/sawmill/rawlog/SG_CSGL02_main_47061221.log.gz ...
Thu Jul 1 09:00:37 2004: BULK_TABLE_INSERT ...
Thu Jul 1 09:01:43 2004: INSERT_URL_SERVERS ...
Thu Jul 1 09:02:26 2004: INSERT_URL_PATHS ...
Thu Jul 1 09:03:54 2004: INSERT_URL_QUERIES ...
Thu Jul 1 09:04:32 2004: INSERT_USER_AGENTS ...
Thu Jul 1 09:05:09 2004: INSERT_URL_VISITS ...
Thu Jul 1 09:10:06 2004: INSERT_INTERNET_USAGE ...
Thu Jul 1 09:19:31 2004: DELETE_BULK_TABLE ...
Thu Jul 1 09:19:32 2004: PARSING
/sawmill/rawlog/SG_CSGL02_main_47061121.log.gz ...
Thu Jul 1 09:29:02 2004: BULK_TABLE_INSERT ...
Thu Jul 1 09:31:36 2004: INSERT_URL_SERVERS ...
Thu Jul 1 09:33:21 2004: INSERT_URL_PATHS ...
Thu Jul 1 09:36:26 2004: INSERT_URL_QUERIES ...
Thu Jul 1 09:38:07 2004: INSERT_USER_AGENTS ...
Thu Jul 1 09:39:37 2004: INSERT_URL_VISITS ...
=== This is where the
time difference has
Thu Jul 1 09:54:19 2004: INSERT_INTERNET_USAGE ...
=== suddenly
increased
Thu Jul 1 13:28:37 2004: DELETE_BULK_TABLE ...
Thu Jul 1 13:28:38 2004: PARSING
/sawmill/rawlog/SG_CSGL02_main_47061021.log.gz ...
Thu Jul 1 13:39:09 2004: BULK_TABLE_INSERT ...
Thu Jul 1 13:41:59 2004: INSERT_URL_SERVERS ...
Thu Jul 1 13:43:52 2004: INSERT_URL_PATHS ...
Thu Jul 1 13:47:11 2004: INSERT_URL_QUERIES ...
Thu Jul 1 13:48:55 2004: INSERT_USER_AGENTS ...
Thu Jul 1 13:50:35 2004: INSERT_URL_VISITS ...
Thu Jul 1 14:06:55 2004: INSERT_INTERNET_USAGE ...
time now is 16:13 and it's still going.
This is the function:
sub insert_internet_usage(){
my $sql = qq {INSERT internet_usage
(uid,time,ip,urlid,timetaken,
cs_size,sc_size,method_ID,action_ID,virus_ID,userag
ent_ID)
SELECT bt.user, bt.time, bt.ip, uv.urlid, bt.timetaken,
bt.cs_size, bt.sc_size, bt.method_ID, bt.action_ID, b
t.virus, ua.ID
FROM bulk_table bt
INNER JOIN url_servers us ON us.server=bt.server
INNER JOIN url_paths up ON up.path=bt.path
INNER JOIN url_queries uq ON uq.query=bt.query
INNER JOIN user_agents ua ON
ua.useragent=bt.useragent
INNER JOIN url_visit uv ON
uv.url_server_ID=us.ID
AND uv.url_path_ID=up.ID
AND
uv.url_scheme_ID=bt.scheme_ID
AND uv.url_query_ID=uq.ID };
my $timenow=localtime();
print PL \n$timenow: INSERT_INTERNET_USAGE ...;
$dbh2-do($sql) or die $dbh2-errstr;
$dbh2-commit() or die $dbh2-errstr;
}
Here's a bit of extra info:
mysql desc url_visit;
+-+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+-+-+--+-+-++
| urlid | int(11) | | PRI | NULL| auto_increment |
| url_scheme_ID | int(11) | | | 0 ||
| url_server_ID | int(11) | | MUL | 0 ||
| url_path_ID | int(11) | | | 0 ||
| url_query_ID| int(11) | | | 0 ||
| url_category_ID | int(11) | | | 0 ||
+-+-+--+-+-++
6 rows in set (0.01 sec)
mysql desc internet_usage;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| uid | varchar(10) | YES | MUL | NULL| |
| time | datetime| YES | | NULL| |
| ip | int(10) | YES | | 0 | |
| urlid| int(11) | | | 0 | |
| timetaken| int(11) | YES | | 0 | |
| cs_size | int(11) | YES | | 0 | |
| sc_size | int(11) | YES | | 0 | |
| method_ID| int(11) | YES | | 0 | |
| action_ID| int(11) | YES | | 0 | |
| virus_ID | int(11) | YES | | 0 | |
| useragent_ID | int(11) |
Re: bugzilla not working now after MySQL re-installed
Nguyen, Long P (Mission Systems) wrote: MySQL is running.. OK, that's /half/ the question :-) Have you *tried* accessing it from the command-line client, using the username/password set in your localconfig? Does the username and password in localconfig get you into the DB using the MySQL client? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bugzilla not working now after MySQL re-installed
I am able to log into mysql as this: [EMAIL PROTECTED] root]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.0.20-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I tried this and get a 0 affect below.. mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin'; Query OK, 0 rows affected (0.00 sec) Thanks for your help... -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 11:34 AM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: MySQL is running.. OK, that's /half/ the question :-) Have you *tried* accessing it from the command-line client, using the username/password set in your localconfig? Does the username and password in localconfig get you into the DB using the MySQL client? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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: bugzilla not working now after MySQL re-installed
Nguyen, Long P (Mission Systems) wrote: I tried this and get a 0 affect below.. mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin'; Query OK, 0 rows affected (0.00 sec) OK, I assume 'bugs' and 'marvin' are the values in bugzilla's config file; can you log in from the MySQL client using them instead of the root user/pwd? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bugzilla not working now after MySQL re-installed
yes.. below: [EMAIL PROTECTED] root]# mysql -u bugs -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.20-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 11:52 AM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: I tried this and get a 0 affect below.. mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin'; Query OK, 0 rows affected (0.00 sec) OK, I assume 'bugs' and 'marvin' are the values in bugzilla's config file; can you log in from the MySQL client using them instead of the root user/pwd? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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]
Managing table quota
Hi Is it possible to change the table memory usage quota through mysql 4.X scripts as we do in Oracle. We are migrating our application from Oracle 9i to MySQL does any body have experience in this area and are there some common problems faced during this procedure. Any information will be helpful. Regards Shashi Kiran
Re: bugzilla not working now after MySQL re-installed
Nguyen, Long P (Mission Systems) wrote: yes.. below: [EMAIL PROTECTED] root]# mysql -u bugs -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.20-standard Uh, OK. You said MySQL was re-installed -- why? Was it the same version, or an upgrade? If the latter, there's a difference in the authentication between 3.x and 4.x that requires a client upgrade (or that you explicitly use the old auth). If it was the same version re-installed, is it possible some file system permissions were changed? And does anything show up in the MySQL logs when bugzilla runs (or tries to)? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bugzilla not working now after MySQL re-installed
I am able to logon with user 'bugs' and passwd 'marvin' as below:
[EMAIL PROTECTED] root]# mysql -u bugs -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.20-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql
I noticed before the MySQL installed - when I do a 'which mysql' I get output of
'/usr/local/mysql/bin/mysql' - but now I get /usr/bin/mysql.
But why does bugzilla checksetup.pl failed to connect to the database in error below..
Checking user setup ...
Precompiling templates ...
DBI connect(';localhost;3306','bugs',...) failed: Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (2) at ./checksetup.pl line 1189
[Thu Jul 1 12:32:14 2004] checksetup.pl: DBI connect(';localhost;3306','bugs',...)
failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) at
./checksetup.pl line 1189
h1Software error:/h1
preCan't connect to the mysql database. Is the database installed and
up and running? Do you have the correct username and password selected in
localconfig? AHA
/pre
p
For help, please send mail to this site's webmaster, giving this error message
and the time and date of the error.
/p
[Thu Jul 1 12:32:14 2004] checksetup.pl: Can't connect to the mysql database. Is the
database installed and
[Thu Jul 1 12:32:14 2004] checksetup.pl: up and running? Do you have the correct
username and password selected in
[Thu Jul 1 12:32:14 2004] checksetup.pl: localconfig? AHA
[Thu Jul 1 12:32:14 2004] checksetup.pl:
[EMAIL PROTECTED] bugzilla-2.16.5]#
-Original Message-
From: Hassan Schroeder [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 01, 2004 11:52 AM
To: [EMAIL PROTECTED]
Subject: Re: bugzilla not working now after MySQL re-installed
Nguyen, Long P (Mission Systems) wrote:
I tried this and get a 0 affect below..
mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON
bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin';
Query OK, 0 rows affected (0.00 sec)
OK, I assume 'bugs' and 'marvin' are the values in bugzilla's config
file; can you log in from the MySQL client using them instead of the
root user/pwd?
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
dream. code.
--
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: bugzilla not working now after MySQL re-installed
The original error might be slightly more cryptic than it intends. It is saying that it cannot connect to your db through /tmp/mysql.sock. Check if your mySQL installation has installed the socket file in another place. If it has, a quick and dirty fix is to soft link the socket file wherever it is to /tmp/mysql.sock i.e. ln -s /usr/local/mysql/mysql.sock /tmp/mysql.sock Change /usr/local/mysql/mysql.sock to your file. In some distributions it can be found in /var/lib/mysql/mysql.sock. Yiannis. -Original Message- From: Nguyen, Long P (Mission Systems) [mailto:[EMAIL PROTECTED] Sent: 01 July 2004 17:07 To: Hassan Schroeder; [EMAIL PROTECTED] Subject: RE: bugzilla not working now after MySQL re-installed yes.. below: [EMAIL PROTECTED] root]# mysql -u bugs -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.20-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 11:52 AM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: I tried this and get a 0 affect below.. mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin'; Query OK, 0 rows affected (0.00 sec) OK, I assume 'bugs' and 'marvin' are the values in bugzilla's config file; can you log in from the MySQL client using them instead of the root user/pwd? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. Note:__ This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Jaguar Freight Services and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: System.Data.MySql
Yes. The ByteFX provider has been aquired by MySql and will be released shortly as the supported provider. It will not have the namespace System.Data.MySql since System.* is reserved for bits of the framework shipped as native parts of the framework. The namespace will by MySql.Data.MySqlClient -reggie So far the one I have had the best luck with is ByteFX.Data.MySqlClient I use it in both VB.Net and C# against MySql 4.1.2 on Windows and Linux. It's here: http://www.bytefx.com Larry Lowry - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 01, 2004 7:35 AM Subject: System.Data.MySql Hello! Is there an implementation os System.Data.MySQL for the .net-framework? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_safe in 4.1.2
--On Wednesday, June 30, 2004 07:36:23 PM -0500 Paul DuBois [EMAIL PROTECTED] wrote: At 13:58 -0700 6/30/04, Bill MacAllister wrote: Hello, I am a bit confused by processing of user= specifications in the mysqld_safe script. Here is what I see: * If user= is specificed in the [mysqld] or [server] sections then it is ignored and a flag is set that prevents the specification of a user in any subsequent options parsing. * Otherwise the user will be extracted and used from either the [mysqld_safe], [safe_mysqld], or command line arguements. Not quite. For security reasons, only the *first* encountered instance of the user option is used, and any others are ignored. http://dev.mysql.com/doc/mysql/en/Server_options.html See the descripion of --user in the option list. Thanks for the pointer to the documentation. I just missed it. But, my reading of the code in mysqld_safe (4.1.2) does not match the documentation. The first pass through the option sections [mysqld] and [server] have the SET_USER script variable set to 2. With this value set any user specification is ignored, but specifying a user disables any subsequent user specifications because SET_USER is set to 1. Also, I don't see any where that would generate warnings if there are multiple user specifications. It took me quite a while to figure out why my mysqld_safe was trying to run the mysql server as mysql instead of the user that I specified. Still looks like a bug to me. Bill This is counter intuitive if nothing else, and I really don't see why user should be parsed any differently than any other options. Am I missing something or is this a bug? It's by intent. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com +--- | Bill MacAllister | 14219 Auburn Road | Grass Valley, CA 95949 | 530-272-8555 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem on 3.23.57 3.23.58
We have suddenly run into a replication problem between our master and our slave databases. It had been running without a problem for atleast several months since our last upgrade, and for several years before that. I've tried a number of debugging techniques, and now I'm hoping somebody here can make sense of it. On the slave I get error messages like: 040630 2:43:52 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.163' at position 37919441 It does that several times between 2:20am and 4:30am. and every few nights the slave gives up: 040630 4:15:34 Slave thread exiting, replication stopped in log 'mysql-bin.163' at position 99512496 No logs are recorded on the master. This 2am to 5am time coincides with a lot of mysql update/insert traffic on the master, in parallel with alot of different connections. Master and slave are running linux. Master is running 3.23.57 binary and the slave is running 3.23.58 binary. I checked the changelong on mysql's site, and I don't see why the version difference would be a problem. (but I am open to upgrading the master!) Both master and slave have max packet set at 16M. So my initial thought was hardware (since everything was great before). So I feel like I've almost eliminated it by doing the following: Changing ethernet boards on both master and slave (to totally different brand of hardware even) Changed cables Used different switch ports Turned off auto negotiate on the switch port Then I: swapped the slave hardware for completely different hardware (removed raid drives, stuffed into similar server) Upgraded the kernels on both machines to 2.4.26 I also setup a ping over night to both the master and slave servers looking for packet loss, none. I wrote a TCP based ping between both of those servers and our administrative server. It kept passing data back and forth and recorded the total time. No slow down, or Net errors during the time periods Mysql has communications problems. So then I setup a second slave. This box runs debian (the other slave ran redhat). This machine is running the same exact binary mysql that was running on the other slave. This slave ran fine for 3 days (which led to make some false assumptions that it was working fine). But now it's slave breaks too. Interesting though. Both slaves will break at *approximately* the same time (about a minute apart.. Both machines are ntp synced to the second, so it's not *EXACTLY* the same time), and both will be pretty *close* in the binlog, but not exact. I have looked in the biglog at the various queries that it's crapped out on, and they're nothing interesting. I've looked at about 10 of them, and some are very simple updates, some are long complex inserts. A few nights I have SLAVE STOP;ed the slave, and waited until the morning to run the slave log forward. When I run the slave log in the morning, I get the same exact slave disconnects. This is the first time I've seen the slave disconnect outside the 2:30-4am window. So clearly this seems to be a mysql problem... Something in the binlog that's causing it to disconnect, but why do the two slaves not disconnect in the same *EXACT* place? So, before putting the axe through the mysql server, I busted out my packet sniffer. I sniffed the packets from both sides of the connection until it failed. From my reading, it looks like the master is tearing down the connection. It basically looks like this: Master sends lots of data Master sends a PSH ACK packet Master sends lots of data Master sends a PSH ACK packet Master sends lots of data Master sends a PSH ACK FIN packet Slave sends a ACK FIN packet (with slave ACKs in there too) S, it looks like the master is tearing down the connection. Anybody have any thoughts? I can upgrade the master to 3.23.58, but I don't see anything in the mysql change log that implies that will help. (Bringing down the master server requires much dancing around and appeasing the customers due to the outage). My other thought is going to mysql 4.0.x. But again, I don't generally like doing things just because they might help. We have had a plan to upgrade to 4.0.x for some time (we certified our software on it), but we don't have an urgent need to budget the resources required to do it. Is it possible there is some sort of race in the mysql-biglog writing? One of the reasons why this might have only started cropping up now is the 2:30-4am slot has been getting progressively busier and busier, with a huge number of parallel insert/updates (4cpu box). The rest of the day the traffic isn't even close to that time period, and replication works rockstar. Settings on the master: set-variable= key_buffer=1024M set-variable= tmp_table_size=1024M set-variable= max_allowed_packet=16M set-variable= thread_stack=128K set-variable= max_connections=2000 set-variable= max_connect_errors=9 set-variable
Select help
Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select help
Hey Rob, You're looking for a group by to allow mysql to aggregate over the IP's: SELECT ip, count(*) FROM iptable GROUP BY ip ORDER BY ip DESC limit 10; -Matt -Original Message- From: rmck [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:03 PM To: [EMAIL PROTECTED] Subject: Select help Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- 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: authentication error
I had my admin turn it down from 256M to 50M and it started just dogging at 40 connections. We're now at 128M and checking. How do I set the key_buffer appropriately? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:51 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you tweak your key_buffer and see what results you get? -Original Message- From: Chip Bell To: Chip Bell; Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 9:38 AM Subject: RE: authentication error My bad, just realized that read_buffer_size USED to be record_buffer My total @ 100 connections is 2356M. I'm going to guess that I should shave that down a bit?? -Original Message- From: Chip Bell Sent: Thursday, July 01, 2004 10:17 AM To: Victor Pendleton; [EMAIL PROTECTED] Subject: RE: authentication error Ok, I have no read_buffer anywhere... 256M (key) + 20 (sort) * 100 (connections) Correct? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:09 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do the math and see what your memory usage is with 100 simultaneous connections. If I recal you said you had three gigs of ram. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 5:27 AM Subject: RE: authentication error Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED]
Re: Select help
select count(*) as cnt group by ip order by cnt desc limit 10; rmck wrote: Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select help
Woops! Forget I said that, you wanted to order by the most occurrences. Sorry. SELECT ip, count(*) FROM iptable GROUP BY ip ORDER BY 2 DESC limit 10; Heh... I should learn to read one of these days... -Matt -Original Message- From: rmck [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:03 PM To: [EMAIL PROTECTED] Subject: Select help Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- 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: authentication error
What is the value of key_reads/key_read_request? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 12:11 PM Subject: RE: authentication error I had my admin turn it down from 256M to 50M and it started just dogging at 40 connections. We're now at 128M and checking. How do I set the key_buffer appropriately? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:51 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you tweak your key_buffer and see what results you get? -Original Message- From: Chip Bell To: Chip Bell; Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 9:38 AM Subject: RE: authentication error My bad, just realized that read_buffer_size USED to be record_buffer My total @ 100 connections is 2356M. I'm going to guess that I should shave that down a bit?? -Original Message- From: Chip Bell Sent: Thursday, July 01, 2004 10:17 AM To: Victor Pendleton; [EMAIL PROTECTED] Subject: RE: authentication error Ok, I have no read_buffer anywhere... 256M (key) + 20 (sort) * 100 (connections) Correct? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:09 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do the math and see what your memory usage is with 100 simultaneous connections. If I recal you said you had three gigs of ram. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 5:27 AM Subject: RE: authentication error Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer
RE: authentication error
Key_Reads 57 Key_read_request 16218 -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:17 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the value of key_reads/key_read_request? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 12:11 PM Subject: RE: authentication error I had my admin turn it down from 256M to 50M and it started just dogging at 40 connections. We're now at 128M and checking. How do I set the key_buffer appropriately? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:51 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you tweak your key_buffer and see what results you get? -Original Message- From: Chip Bell To: Chip Bell; Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 9:38 AM Subject: RE: authentication error My bad, just realized that read_buffer_size USED to be record_buffer My total @ 100 connections is 2356M. I'm going to guess that I should shave that down a bit?? -Original Message- From: Chip Bell Sent: Thursday, July 01, 2004 10:17 AM To: Victor Pendleton; [EMAIL PROTECTED] Subject: RE: authentication error Ok, I have no read_buffer anywhere... 256M (key) + 20 (sort) * 100 (connections) Correct? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:09 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do the math and see what your memory usage is with 100 simultaneous connections. If I recal you said you had three gigs of ram. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 5:27 AM Subject: RE: authentication error Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged
RE: authentication error
Those values are good. What query is being run when the users are authenticating? Have you looked at the explain plan for that query? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 12:22 PM Subject: RE: authentication error Key_Reads 57 Key_read_request 16218 -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:17 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the value of key_reads/key_read_request? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 12:11 PM Subject: RE: authentication error I had my admin turn it down from 256M to 50M and it started just dogging at 40 connections. We're now at 128M and checking. How do I set the key_buffer appropriately? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:51 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you tweak your key_buffer and see what results you get? -Original Message- From: Chip Bell To: Chip Bell; Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 9:38 AM Subject: RE: authentication error My bad, just realized that read_buffer_size USED to be record_buffer My total @ 100 connections is 2356M. I'm going to guess that I should shave that down a bit?? -Original Message- From: Chip Bell Sent: Thursday, July 01, 2004 10:17 AM To: Victor Pendleton; [EMAIL PROTECTED] Subject: RE: authentication error Ok, I have no read_buffer anywhere... 256M (key) + 20 (sort) * 100 (connections) Correct? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:09 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do the math and see what your memory usage is with 100 simultaneous connections. If I recal you said you had three gigs of ram. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 5:27 AM Subject: RE: authentication error Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent:
Re: Select help
On Thu, 2004-07-01 at 10:03, rmck wrote: Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? Yes. You need to count the number of times an IP appears and sort by that count, then limit it: SELECT ip, COUNT(ip) as num FROM iptable GROUP BY ip ORDER BY num DESC LIMIT 10 -- . Garth Webb . [EMAIL PROTECTED] . . shoes * * schoenen * * chaussures * zapatos . Schuhe * * pattini * * sapatas * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
I don't know how to look at the explain plan for that query..or how to find out what query is being run. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:29 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Those values are good. What query is being run when the users are authenticating? Have you looked at the explain plan for that query? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 12:22 PM Subject: RE: authentication error Key_Reads 57 Key_read_request 16218 -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:17 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the value of key_reads/key_read_request? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 12:11 PM Subject: RE: authentication error I had my admin turn it down from 256M to 50M and it started just dogging at 40 connections. We're now at 128M and checking. How do I set the key_buffer appropriately? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:51 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you tweak your key_buffer and see what results you get? -Original Message- From: Chip Bell To: Chip Bell; Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 9:38 AM Subject: RE: authentication error My bad, just realized that read_buffer_size USED to be record_buffer My total @ 100 connections is 2356M. I'm going to guess that I should shave that down a bit?? -Original Message- From: Chip Bell Sent: Thursday, July 01, 2004 10:17 AM To: Victor Pendleton; [EMAIL PROTECTED] Subject: RE: authentication error Ok, I have no read_buffer anywhere... 256M (key) + 20 (sort) * 100 (connections) Correct? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:09 AM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do the math and see what your memory usage is with 100 simultaneous connections. If I recal you said you had three gigs of ram. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 7/1/04 5:27 AM Subject: RE: authentication error Nope, nothing out of the ordinary. I tested this about 5 times last night and EVERY time the show processlist was 100 or above, it hung. The ONLY variable I can find with a setting of 100 is that delayed_insert_limit. Obviously I was thinking of just changing that and watching, but you guys seem to know A LOT more than I do so I thought someone may have seen this...or have an idea. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:45 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have a high number of temp tables being created or high i/o? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:44 PM Subject: RE: authentication error No sir... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 4:43 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error I would check to see if your server is swapping at this point. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/30/04 3:27 PM Subject: RE: authentication error Hi Victor, I have stumbled on to something. The server is bouncing back and forth b/t fast and slow. When I do show processlist and its above 100, it's slow. When it's below, it's fine. What variable is wrong? I have Max_connections = 250? Thanks Chip -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:12 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold
RE: Production release of MySql 4.1
At 14:09 -0700 6/30/04, Jon Frisby wrote: As I understand it, the particular cycle a release is in depends on how long it's been since a major bug was reported. So an alpha becomes a beta if nobody reports a major bug after N days, and a beta becomes a production release if goes N days without a major bug report. Thus, even if 4.1.3 is released as alpha, it could retroactively be declared beta, and then even No, once a given version is released, it doesn't get changed retroactively. Either a 4.1.3a release would be done, or a 4.1.4 release would be done. release -- although that's pretty unlikely. The long and short of it though, is that nobody can tell you how long until 4.1 will go beta. Well, 4.1.3 has been announced today as a -beta release, so we all know the answer to this now. :-) -JF -Original Message- From: Jonathan Soong [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 7:20 PM To: Jocelyn Fournier Cc: John Murphy; Emmanuel van der Meulen; [EMAIL PROTECTED] Subject: Re: Production release of MySql 4.1 Jocelyn Fournier wrote: Hi, AFAIK 4.1.3 should be beta. It is a little frustrating, at Linux Conf Adelaide 2004 (January), the Mysql guy there said that 4.1 would be in beta, in the next few weeks ... Its now July and its still in Alpha. It says on the webpage MySQL 4.1 -- Alpha release (use this for new development) - and it has said that for 6months+ So we did our development on 4.1, and were expecting it to be beta by February 2004. We're ready to roll it out as soon as it hits beta, i told my boss it would be in beta by March 2004 at the latest. We now have hardware sitting for around with 4.1 alpha on it that cannot be deployed. Does anyone actually have a concrete date when 4.1 will go into beta? Cheers Jon -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Performance
On Thu, Jul 01, 2004 at 11:34:29AM +0800, MaFai wrote: Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? You need to identify the bottleneck. Is the slave's IO thread taking too long to pull binary log entries from the master? Or is the SQL thread hopelessly behind because of a slow hard disk, CPU, or memory shortage? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mac OSX Tiger and 64 Bit
On Tue, Jun 29, 2004 at 10:57:41PM -0700, Bruce Dembecki wrote: So I have a question for those who understand developer speak and MySQL builds and so on... Apple announced their new OS earlier this week, including this information on the improvements to 64 Bit version using the G5 processor: http://www.apple.com/macosx/tiger/64bit.html One of our biggest problems to date on our G5 servers is despite the bulk ram we have installed, the current Apple OS isn't really 64 Bit so we can't give the InnoDB caches more than 2Gb of ram, and thus there are always no empty pages. This statement from Apple stops short of saying the OS was fully 64 bit... But I think they are saying that apps such as mysqld will be able to call larger chunks of memory, which is what we want. It sure looks to me like Tiger will remove those limits... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Stats] MySQL List: June 2004
-- Searchable archives for this list are available at http://www.listsearch.com/mysql.lasso -- == MySQL List Stats June, 2004 == Note: Up/Down % as compared with May, 2004 Posts: 1761 (Down 2%) Authors: 549 (Up 4%) Threads: 608 (Up 1%) Top 20 Contributors by Number of Posts -- SGreen 96 Michael Stassen 85 Victor Pendleton53 Egor Egorov 47 David Blomstrom 46 Paul DuBois 28 gerald_clark28 J S 26 Leonardo Francalanci23 Jeff Smelser22 Robert A. Rosenberg 20 Josh Trutwin16 Paul McNeil 15 Martijn Tonies 15 Eric Bergen 14 shaun thornburgh13 Andrew Pattison 13 David Griffiths 12 Daniel Clark11 Dan Nelson 11 Top 20 Threads by Number of Posts -- load data into 2 tables and set id 27 INDEX DESC 24 authentication error20 Unicode characters become question marks19 AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byt... 13 Query Help 12 New to Dates - Plain English Please 12 strange table speed issue 12 Using REGEXP12 Production release of MySql 4.1 11 RE - Order By Problem 11 error 2711 Slow querys When ADSL is down on W2K10 Err 2002 socks suck noway to start 10 Idea to speed up multiple jdbc connections? 10 delete record that passed 1 day 10 odbc reverse10 Exporting/Importing Databases9 How to COUNT rows when they have a COUNT in them 9 Where are BLOBs / TEXTs stored? 9 Top 20 Search Terms by Number of Requests -- import 9 reference7 enum 6 reset5 update 5 Manual 5 query5 cache5 xml 5 speed4 MySQL4 DATA 4 mysqlcc 4 sql 4 weeknames4 filemaker4 temporary3 all 3 expiration
Re: Replication problem on 3.23.57 3.23.58
somebody here can make sense of it. On the slave I get error messages like: 040630 2:43:52 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.163' at position 37919441 It does that several times between 2:20am and 4:30am. and every few Ah.. right.. I'm not-so-smart. I didn't actually post the entire error log message: 040701 14:56:05 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 14:56:05 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 14792980 040701 14:56:05 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 14792980 040701 15:02:13 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 15:02:13 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 25852745 040701 15:02:13 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 25852745 040701 15:09:14 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 15:09:14 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 37090598 040701 15:09:14 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 37090598 040701 15:15:28 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 15:15:28 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 47700336 040701 15:15:28 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 47700336 are the messages that come out of the slave. I'm currently running a test where I back up the entire slave, run it forward through the logs, and restore the slave back exactly to it's previous point, and run it through the logs again. See if it dies in the same places or if it's somewhat random. -Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding Tables created by views
Folks- I have a table that is chock full of errors..(PLAN shows Table Access full) so I know that it was created wrong Is there an easy way to setup a query to locate all views that use this table? Thanks, Martin Gainty (cell) 617-852-7822 From: Bill MacAllister [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: mysqld_safe in 4.1.2 Date: Thu, 01 Jul 2004 09:21:24 -0700 MIME-Version: 1.0 Received: from lists.mysql.com ([213.136.52.31]) by mc11-f37.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Thu, 1 Jul 2004 09:51:11 -0700 Received: (qmail 15454 invoked by uid 109); 1 Jul 2004 16:48:37 - Received: (qmail 20417 invoked from network); 1 Jul 2004 16:21:57 - Received: pass (lists.mysql.com: local policy) X-Message-Info: JGTYoYF78jG+JHHNCikggl2bAzRuoWUi Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Archive: http://lists.mysql.com/mysql/168278 Delivered-To: mailing list [EMAIL PROTECTED] In-reply-to: [EMAIL PROTECTED] Message-id: [EMAIL PROTECTED] X-Mailer: Mulberry/3.1.5 (Linux/x86) References: [EMAIL PROTECTED] [EMAIL PROTECTED] X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 01 Jul 2004 16:51:13.0639 (UTC) FILETIME=[9E6F1770:01C45F8B] --On Wednesday, June 30, 2004 07:36:23 PM -0500 Paul DuBois [EMAIL PROTECTED] wrote: At 13:58 -0700 6/30/04, Bill MacAllister wrote: Hello, I am a bit confused by processing of user= specifications in the mysqld_safe script. Here is what I see: * If user= is specificed in the [mysqld] or [server] sections then it is ignored and a flag is set that prevents the specification of a user in any subsequent options parsing. * Otherwise the user will be extracted and used from either the [mysqld_safe], [safe_mysqld], or command line arguements. Not quite. For security reasons, only the *first* encountered instance of the user option is used, and any others are ignored. http://dev.mysql.com/doc/mysql/en/Server_options.html See the descripion of --user in the option list. Thanks for the pointer to the documentation. I just missed it. But, my reading of the code in mysqld_safe (4.1.2) does not match the documentation. The first pass through the option sections [mysqld] and [server] have the SET_USER script variable set to 2. With this value set any user specification is ignored, but specifying a user disables any subsequent user specifications because SET_USER is set to 1. Also, I don't see any where that would generate warnings if there are multiple user specifications. It took me quite a while to figure out why my mysqld_safe was trying to run the mysql server as mysql instead of the user that I specified. Still looks like a bug to me. Bill This is counter intuitive if nothing else, and I really don't see why user should be parsed any differently than any other options. Am I missing something or is this a bug? It's by intent. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com +--- | Bill MacAllister | 14219 Auburn Road | Grass Valley, CA 95949 | 530-272-8555 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding Tables created by views
In the last episode (Jul 01), Martin Gainty said: I have a table that is chock full of errors..(PLAN shows Table Access full) so I know that it was created wrong Is there an easy way to setup a query to locate all views that use this table? Mysql doesn't support views yet. It's planned for 5.0, I think. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bugzilla not working now after MySQL re-installed
I re-installed it this time by rpm because when I tried to recompile it with '--with-mqsql' this DRES and some other errors for Keystone tools keeps telling me that MySQL needs to be recompiled with '--with-mqsql'. This time with the re-installed - I used rpm. Same version but by rpm. I noticed that my databases are not there anymore when I do a 'show databases'. I just need to get Bugzilla to run the checksetup.pl script and it will go and create what it needs. but the unable to logon error is preventing it. There should not be any differ between rpm and the compile install is there? -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 12:20 PM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: yes.. below: [EMAIL PROTECTED] root]# mysql -u bugs -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.20-standard Uh, OK. You said MySQL was re-installed -- why? Was it the same version, or an upgrade? If the latter, there's a difference in the authentication between 3.x and 4.x that requires a client upgrade (or that you explicitly use the old auth). If it was the same version re-installed, is it possible some file system permissions were changed? And does anything show up in the MySQL logs when bugzilla runs (or tries to)? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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: bugzilla not working now after MySQL re-installed
The 'mysql.sock' file is at /var/lib/mysql/mysql.sock with a size of 0 Does this look right? [EMAIL PROTECTED] mysql]# pwd /var/lib/mysql [EMAIL PROTECTED] mysql]# ls -l mysql.sock srwxrwxrwx1 mysqlmysql 0 Jul 1 11:41 mysql.sock -Original Message- From: Yiannis Mavroukakis [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 12:23 PM To: [EMAIL PROTECTED] Subject: RE: bugzilla not working now after MySQL re-installed The original error might be slightly more cryptic than it intends. It is saying that it cannot connect to your db through /tmp/mysql.sock. Check if your mySQL installation has installed the socket file in another place. If it has, a quick and dirty fix is to soft link the socket file wherever it is to /tmp/mysql.sock i.e. ln -s /usr/local/mysql/mysql.sock /tmp/mysql.sock Change /usr/local/mysql/mysql.sock to your file. In some distributions it can be found in /var/lib/mysql/mysql.sock. Yiannis. -Original Message- From: Nguyen, Long P (Mission Systems) [mailto:[EMAIL PROTECTED] Sent: 01 July 2004 17:07 To: Hassan Schroeder; [EMAIL PROTECTED] Subject: RE: bugzilla not working now after MySQL re-installed yes.. below: [EMAIL PROTECTED] root]# mysql -u bugs -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.20-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 11:52 AM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: I tried this and get a 0 affect below.. mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin'; Query OK, 0 rows affected (0.00 sec) OK, I assume 'bugs' and 'marvin' are the values in bugzilla's config file; can you log in from the MySQL client using them instead of the root user/pwd? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. Note:__ This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Jaguar Freight Services and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. -- 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: bugzilla not working now after MySQL re-installed
Nguyen, Long P (Mission Systems) wrote: I re-installed it this time by rpm / Same version but by rpm. / I noticed that my databases are not there anymore when I do a 'show databases'. I just need to get Bugzilla to run the checksetup.pl script and it will go and create what it needs. but the unable to logon error is preventing it. If it's been installed in a different place, and you're no longer accessing the original database files, your user/password info will need to be re-entered. But you probably got that already. There should not be any differ between rpm and the compile install is there? I run RedHat but I'm not that much of an RPM fan; tried it a few times and got tired of wierd installation defaults... :-) It's certainly possible the RPM was compiled with different options than the configure/make defaults, though. -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to insert blob data remotely
Hi all!..
How can I insert blob data (specifically a jpg image) into a blob field
remotely?.. I mean, the client has to choose a jpg image in his/her PC
and upload it to the server.
I found this piece of code:
INSERT INTO car_models
(Brand, Picture)
VALUES
('Subaru Impreza', LOAD_FILE('/somepath/impreza.jpg'));
But LOAD_FILE only works if 'picture.jpg' is placed on the same machine
where the server is running.
Any Idea?
Thanks in Advance
Hector.
--
Ing. Hctor Maldonado Melgar
Dpto. Desarrollo de Software
TCI S.A., Lima-Per
[EMAIL PROTECTED]
Of.: 421-3222
Cel: 9503-9205
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to insert blob data remotely
On Thu, 2004-07-01 at 11:06, Héctor Maldonado wrote: But LOAD_FILE only works if 'picture.jpg' is placed on the same machine where the server is running. That makes sense. You could of course NFS mount from the remote machine to the machine you are working from but that is much the same thing. Alternatively if you are working in a language like Perl there are other tricks you can play... -- Peter L. Berghold[EMAIL PROTECTED] Dog event enthusiast, brewer of Belgian (style) Ales. Happiness is having your contented dog at your side and a Belgian Ale in your glass. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to insert blob data remotely
Is using a programming language such as Java, PHP or VB an option?
-Original Message-
From: Hctor Maldonado
To: [EMAIL PROTECTED]; Omar De la Cruz
Sent: 7/1/04 10:06 AM
Subject: how to insert blob data remotely
Hi all!..
How can I insert blob data (specifically a jpg image) into a blob field
remotely?.. I mean, the client has to choose a jpg image in his/her PC
and upload it to the server.
I found this piece of code:
INSERT INTO car_models
(Brand, Picture)
VALUES
('Subaru Impreza', LOAD_FILE('/somepath/impreza.jpg'));
But LOAD_FILE only works if 'picture.jpg' is placed on the same machine
where the server is running.
Any Idea?
Thanks in Advance
Hector.
--
Ing. Hctor Maldonado Melgar
Dpto. Desarrollo de Software
TCI S.A., Lima-Per
[EMAIL PROTECTED]
Of.: 421-3222
Cel: 9503-9205
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to insert blob data remotely.. SOLVED :)
Hi Again..
Fortunately I found a way to do this using C++/Qt. Here's the code for
who are interested in:
QFile *myFile;
QSqlQuery query;
QString strQuery;
QByteArray myByteArray;
// open the image and fill the bytearray
myFile = new
QFile(/root/hm/variostepsa/fotos_marcopolo/paradiso1.jpg);
myFile-open(IO_ReadOnly);
myByteArray = myFile-readAll();
myFile-close();
// Insert the image
strQuery = INSERT INTO Model (Brand, Picture) VALUES ('Subaru', :pic);
query.prepare(strQuery);
query.bindValue(:pic, myByteArray);
if (!query.exec())
qWarning(Eorrr);
I don't know if this is optimal, but it works.. :)
Thanks to all those reply..
Regards,
Hector.
El jue, 01-07-2004 a las 20:32, Peter L. Berghold escribi:
On Thu, 2004-07-01 at 11:06, Hctor Maldonado wrote:
But LOAD_FILE only works if 'picture.jpg' is placed on the same machine
where the server is running.
That makes sense. You could of course NFS mount from the remote machine
to the machine you are working from but that is much the same thing.
Alternatively if you are working in a language like Perl there are other
tricks you can play...
--
Ing. Hctor Maldonado Melgar
Dpto. Desarrollo de Software
TCI S.A., Lima-Per
[EMAIL PROTECTED]
Of.: 421-3222
Cel: 9503-9205
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Auto Date selection and format
Michael/Wesley, Thanks for your help. You got me going in the right direction! Thanks again!!! Mike -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 7:37 PM To: Mike Koponick Cc: [EMAIL PROTECTED] Subject: Re: Auto Date selection and format Mike Koponick wrote: Hello all, I would like to be able to select the certain dates within my script. select created_date, status, user, comment1, comment7, comment8, action from users WHERE customerid = 'Customer' AND created_date BETWEEN '2004-05-31' AND '2004-07-01' ORDER BY created_date, status into outfile 'test5.txt' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY '\n'; I believe your INTO OUTFILE clause is mispalced. http://dev.mysql.com/doc/mysql/en/SELECT.html I would like the first date to be the last day of the previous month and the second date to be the first day of the current month. What is the most effecient way to do this in my script rather than hard coding? Your description doesn't quite match your example. I'll assume the example is right. I don't know about most efficient, but you can do it with a combination of date functions. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Also, I looked for a way to format the output date to MM/DD/YY rather than -MM-DD. Any suggestions? DATE_FORMAT() Thanks in advance. Mike I'm doing this in 2 queries with user variables to try to cut down on ugliness. You could do it in one query by replacing the variables with their definitions in the WHERE clause. SELECT @day:= DAYOFMONTH(CURDATE()) day, @start:= CURDATE() - INTERVAL 1 MONTH - INTERVAL @day DAY start, @end:= CURDATE() - INTERVAL (@day-1) DAY end; +--+++ | day | start | end| +--+++ |1 | 2004-05-31 | 2004-07-01 | +--+++ 1 row in set (0.00 sec) SELECT DATE_FORMAT(created_date, '%m/%d/%y') AS created, status, user, comment1, comment7, comment8, action INTO OUTFILE 'test5.txt' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY '\n'; FROM users WHERE customerid = 'Customer' AND created_date BETWEEN @start AND @end ORDER BY created_date, status Are you aware that BETWEEN is inclusive? That is, this query will include rows from 5/31 and 7/01. With mysql 4.1.1 or later, you could simplify the variable definitions slightly: SELECT @start:= LAST_DAY(CURDATE() - INTERVAL 2 MONTH), @end:= LAST_DAY(CURDATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Training and certification in Kansas City
Where can I get MySQL training and certification in Kansas City? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merging of 2 selects
on 07/01/2004 05:43 PM, Scott Haneda at [EMAIL PROTECTED] wrote: I am not entirely sure what I am after is possible, if someone could help me I sure would appreciate it. Mysql 4.0.18-standard I have 2 basic selects I am doing: SELECT id, image_title, 'images' FROM newsletter_images SELECT id, link_title, 'links' FROM newsletter_links In the end, I get 2 separate result sets, each with 3 items in it, and how ever many records there are. Notice I stuffed in the 2 strings in the select of 'images' and 'links' so I can know which are which. My list of results look something like this: For Images: [ [ 3, this is a test, images ], [ 2, lock, images ] ] For Links: [ [ 1, linky, links ], [ 2, this is a link title, links ] ] I want to merge those to result sets into one, which is rather simple to do on the server in script, I want to do it in SQL though, to save the second select from happening, I also want them ordered by the image_title and link_title, but grouped in a way that I get all the 'images' first, then all the 'links', so in the above example, I would end up with a result set that was like this: [ [ 2, lock, images ], [ 3, this is a test, images ], [ 1, linky, links ], [ 2, this is a link title, links ] ] Someone pointed me to UNION, which I think will do this, so I came up with: (select id, image_title, 'image' from newsletter_images ORDER BY image_title) UNION (select id, link_title, 'links' from newsletter_links ORDER BY link_title); Not sure if I need a GROUP BY or if that is even possible in UNION, perhaps the grouping happens just by the order of my union'd selects? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merging of 2 selects
I am not entirely sure what I am after is possible, if someone could help me I sure would appreciate it. Mysql 4.0.18-standard I have 2 basic selects I am doing: SELECT id, image_title, 'images' FROM newsletter_images SELECT id, link_title, 'links' FROM newsletter_links In the end, I get 2 separate result sets, each with 3 items in it, and how ever many records there are. Notice I stuffed in the 2 strings in the select of 'images' and 'links' so I can know which are which. My list of results look something like this: For Images: [ [ 3, this is a test, images ], [ 2, lock, images ] ] For Links: [ [ 1, linky, links ], [ 2, this is a link title, links ] ] I want to merge those to result sets into one, which is rather simple to do on the server in script, I want to do it in SQL though, to save the second select from happening, I also want them ordered by the image_title and link_title, but grouped in a way that I get all the 'images' first, then all the 'links', so in the above example, I would end up with a result set that was like this: [ [ 2, lock, images ], [ 3, this is a test, images ], [ 1, linky, links ], [ 2, this is a link title, links ] ] -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Parent-Child Relationship Question
I created a database focusing on the world's nations, states, etc. that uses a parent-child relationship, as defined in a table named family. I'm now trying to figure out how to query the parent, and it occurred to me that my tables might not be set up correctly. If anyone is willing to glance at my screenshots at http://www.geoworld.org/tables1.gif and http://www.geoworld.org/tables2.gif, can you give me your impression? I think the only tables you need to focus on are family, area and perhaps type. For example, the word France is found in field Name on table area. The name of its parent, Eurasia, is found in the same field and table, but their relationship is defined in the table family. I posted a rather lengthy question about querying Parent1 on a PHP forum at http://www.phpfreaks.com/forums/index.php?act=STf=1t=36967 As I said, I'm now wondering if my tables are set up correctly to begin with. Thanks. __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
All lists off topic... Job Networking Request
To any Moderators: This is the only time I will send this message. Dear List Members, I am seeking a co-op/paid internship in a Computer Science related field. If you or someone you know, directly or indirectly, works for a company that deals with Computer Science, i.e. Computer Programming, please e-mail me privately to get a copy of my resume. Thank you, Nathanial C. Jones 5th year Computer Science Undergraduate Student at Rochester Institute of Technology, Rochester, NY [EMAIL PROTECTED] OR [EMAIL PROTECTED] OR [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM and InnoDB table design
Hi, I read it somewhere that InnoDB is faster for table with high read/write concurrency. I have a table look like this: CREATE TABLE diary ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, member_id INT UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, body TEXT NOT NULL, date DATE NOT NULL, time TIME NOT NULL, last_accessed TIMESTAMP PRIMARY KEY (id), INDEX member_id (member_id) ) TYPE=InnoDB; INSERT only occurs when someone writes a new diary, which is not very often. But UPDATE occurs everytime diary is accessed, so it happens often. I am thinking to divide the table into MySQL and InnoDB like this: CREATE TABLE diary ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, member_id INT UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, body TEXT NOT NULL, date DATE NOT NULL, time TIME NOT NULL, PRIMARY KEY (id), INDEX member_id (member_id) ) TYPE=MyISAM; CREATE TABLE diary_info ( diary_id INT UNSIGNED NOT NULL, last_accessed TIMESTAMP, PRIMARY KEY(diary_id) ) TYPE=InnoDB; So it is only diary_info that has high read/write concurrency now. But, everytime I have to do a SELECT, I also have to JOIN diary_info. Which one do you think is better? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow response time
Hi, I have (what I thought) was a fairly small mysql setup. It's backing a vpopmail installation, and basically just handling alot of SELECTs. In the process of delivering mail, the db gets hit a few times: -initial check that the user exists during smtp conversation -lookup for homedir during delivery -optional lookup again if doing spam filtering Additionally, everytime someone authenticates via smtp-auth, pop or imap, there's a SELECT to grab the password. So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). However, I'm starting to have problems. The various vpopmail programs that perform all these lookups are timing out, which leads to login failures (and worse). I see a number of simple things like PINGs from a local cronjob that graphs queries/sec timing out. Server load can swing from .30 during a calm period to brief spikes of 10.0-15.0 (generally during a spam run). I'm not sure what exactly to look at. I've bumped my max connections up to 1000, and based my my.cnf on the my-large.cnf file and I'm still seeing plenty of slow queries. I'm not swapping to disk at all, I'm not really taxing the CPU, and iostat shows that the raid array isn't struggling. It's been at least 4 years since I had to get very in-depth with MySQL, and it seems plenty has changed (for the better). If anyone can give me a push in the right direction, it would be much appreciated. Thanks, Charles Here's some stats, perhaps this will make more sense to someone. I know the Aborted_clients is high because vpopmail doesn't bother with mysql_close(). The Aborted_connects increment when the problems start. The db server is local to the mail server, so there's no network issues here. mysql show status; +++ | Variable_name | Value | +++ | Aborted_clients| 15450 | | Aborted_connects | 780| | Bytes_received | 8502062| | Bytes_sent | 18432035 | | Com_admin_commands | 8736 | | Com_alter_table| 0 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 3181 | | Com_change_master | 0 | | Com_check | 0 | | Com_commit | 0 | | Com_create_db | 454| | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 1 | | Com_delete | 3 | | Com_delete_multi | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 1 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_insert | 20 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 0 | | Com_optimize | 0 | | Com_purge | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 7495 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 13646 | | Com_set_option | 1 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_create| 0 | | Com_show_databases | 5 | | Com_show_fields| 20 | | Com_show_grants| 0 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master| 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 4 | | Com_show_slave_status | 0 | | Com_show_status| 22 | |
Re: Parent-Child Relationship Question
And here's a follow up question... After looking at my database from a fresh perspective, I'm now thinking of combining tables area and family into a single table. If I do that, it would make life so much simpler if I had TWO name fields, like this: ID | Name | ParentID | Parent Name az |Arizona|us| United States us | United States |kna | North America jpn | Japan |keu | Eurasia I could then slap a $mycode = 'az on a page and easily fill in its name and the name of its parent without fiddling with queries, joins, unions, etc. I know that duplicating names in two fields isn't the most elegant solution, but would create any major problems? Thanks. __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB tablespace fragmentation bug in MySQL 4.0.20
Hi, ( hopefully a MySQL developer sees this at some point! ) I am giving InnoDB a good workout before rolling it out onto production systems, and found a bug in the way the tablespace gets fragmented when doing basic add/drop of indexes. Below my sig is a series of SQL commands I used to replicate the problem. Basically, when doing a drop index, add index, drop index, one would expect the tablespace to look more or less how it looked after the first drop index since the add index should just reuse what gets reclaimed during the 1st drop. What I am finding however is that this sequence will perpetually grow the tablespace, both on disk, and according to InnoDB. The really interesting thing about this issue is that the tablespace data file grows on disk at the drop index time, not during the add index. I could not believe it when I saw it at first, but I repeated the procedure and confirmed this aspect of this bug a couple times. Note that I am using the autoextend feature with a basic innodb config of: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql4/innodb innodb_data_file_path = ibdata1:100M:autoextend innodb_log_group_home_dir = /usr/local/mysql4/innodb innodb_log_arch_dir = /usr/local/mysql4/innodb transaction-isolation = READ-COMMITTED and the innodb files end up looking like this: ]$ ls -allg /usr/local/mysql4/innodb/ total 504352 drwxr-xr-x2 mysql4096 Jun 21 00:50 . drwxr-xr-x 12 root 4096 Jun 21 00:17 .. -rw-rw1 mysql 25088 Mar 22 22:00 ib_arch_log_00 -rw-rw1 mysql2560 Jun 21 00:50 ib_arch_log_02 -rw-rw1 mysql499122176 Jul 1 19:45 ibdata1 -rw-rw1 mysql 8388608 Jul 1 19:45 ib_logfile0 -rw-rw1 mysql 8388608 Jul 1 19:45 ib_logfile1 and just before the last drop index the ibdata1 file looked like: ]$ ls -allg /usr/local/mysql4/innodb/ -rw-rw1 mysql490733568 Jul 1 19:44 ibdata1 Finally, I call this a bug because it seems that if one is doing no more than routine maintenance on tables by adding/dropping indexes, one will eventually run out of disk space regardless of whether one is actually using that disk space! Also, I have a linux 2.4 kernel that this is running on, with mysql compiled with gcc 3.2.2. Thanks, Josh Josh Chamas, Founder| NodeWorks - http://www.nodeworks.com Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com http://www.chamas.com | Apache::ASP - http://www.apache-asp.org mysql alter table clicks drop index idx_test; Query OK, 891450 rows affected (57.83 sec) Records: 891450 Duplicates: 0 Warnings: 0 mysql show table status like 'clicks'; ++++++-+-+--+---++- +-++++ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment| ++++++-+-+--+---++- +-++++ | clicks | InnoDB | Dynamic| 891651 | 95 |85590016 | NULL | 58458112 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 323584 kB | ++++++-+-+--+---++- +-++++ 1 row in set (0.00 sec) mysql alter table clicks add index idx_test (client_id); Query OK, 891450 rows affected (1 min 4.73 sec) Records: 891450 Duplicates: 0 Warnings: 0 mysql show table status like 'clicks'; ++++++-+-+--+---++- +-++++ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment| ++++++-+-+--+---++- +-++++ | clicks | InnoDB | Dynamic| 891651 | 95 |85590016 | NULL | 91602944 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 291840 kB |
InnoDB, odd insert error shared row locking behavior
Hi again, I ran into some dead locking that was unexpected, I basically think the insert error - share lock behavior is problematic. I would think that either a insert error does not acquire a row lock ( equivalent of a basic select ), or that it would acquire an exclusive row lock as if the insert really happened. Note that I do not need a fix for the dead lock situation, I have one by basically doing a select for update to create a critical section ahead of time, but I am writing up this email suggesting the InnoDB locking behavior could be improved. The dead locks seems to come from the fact that I would have code like this: update ( separate transaction ) ... begin work insert ignore ... ( share lock acquired upon error ) update commit I get a dead lock like this: client 2 insert ignore/error - share lock acquired client 1 update - request exclusive lock, wait client 2 update - request exclusive lock *deadlock*, client 2 transaction killed What is counter-intuitive for me about this is that the insert ignore gets escalated to a share lock when the insert error occurs. I understand that this behavior is as documented at http://dev.mysql.com/doc/mysql/en/InnoDB_Locks_set.html with this entry: INSERT INTO ... VALUES (...) sets an exclusive lock on the inserted row. Note that this lock is not a next-key lock and does not prevent other users from inserting to the gap before the inserted row. If a duplicate-key error occurs, *a shared lock on the duplicate index record is set*. but again, I would think that this would be handled more gracefully with either an exclusive lock, or no lock at all. I believe then that client 2 would not have deadlocked because it already had the lock at insert time that it later needed for the later update. Put another way, for an insert command to come back with a share lock is counterintuitive, even it is during an error condition, but this error condition is no error at all with insert ignore. BTW, I have the READ-COMMITTED mode set in my.cnf if it matters. Also for documentation purposes, I have included below the output from show innodb status regarding one such deadlock. Regards, Josh Josh Chamas, Founder| NodeWorks - http://www.nodeworks.com Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com http://www.chamas.com | Apache::ASP - http://www.apache-asp.org LATEST DETECTED DEADLOCK 040701 17:57:19 *** (1) TRANSACTION: TRANSACTION 0 26896, ACTIVE 0 sec, process no 21491, OS thread id 122896 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 10422, query id 145918 gate 192.168.0.10 dmoz Updating -- ( update low_priority dmoz_track.clients set last_visit = now(), ip_address = '192.168.0.10', user_agent_id = '13', num_visits = num_visits + 1 where client_id = 'VIMHu+tRy/sioy+kgxQBfw' -- ) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 1082 n bits 88 index `PRIMARY` of table `dmoz_track/clients` trx id 0 26896 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 8; 1-byte offs TRUE; info bits 0 0: len 22; hex 56494d48752b7452792f73696f792b6b677851426677; asc VIMHu+tRy/sioy+kgxQBfw;; 1: len 6; hex 690a; asc i ;; 2: len 7; hex 000301191e; asc;; 3: len 8; hex 8000123a16dd69ba; asc: i ;; 4: len 4; hex 800d; asc ;; 5: len 8; hex 8000123a16dfe7a7; asc:;; 6: len 12; hex 3139322e3136382e302e3130; asc 192.168.0.10;; 7: len 4; hex 80001473; ascs;; *** (2) TRANSACTION: TRANSACTION 0 26894, ACTIVE 0 sec, process no 20890, OS thread id 114703 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL thread id 10421, query id 145919 gate 192.168.0.10 dmoz Updating -- ( update low_priority dmoz_track.clients set last_visit = now(), ip_address = '192.168.0.10', user_agent_id = '13', num_visits = num_visits + 1 where client_id = 'VIMHu+tRy/sioy+kgxQBfw' -- ) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 1082 n bits 88 index `PRIMARY` of table `dmoz_track/clients` trx id 0 26894 lock mode S locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 8; 1-byte offs TRUE; info bits 0 0: len 22; hex 56494d48752b7452792f73696f792b6b677851426677; asc VIMHu+tRy/sioy+kgxQBfw;; 1: len 6; hex 690a; asc i ;; 2: len 7; hex 000301191e; asc;; 3: len 8; hex 8000123a16dd69ba; asc: i ;; 4: len 4; hex 800d; asc ;; 5: len 8; hex 8000123a16dfe7a7; asc:;; 6: len 12; hex 3139322e3136382e302e3130; asc 192.168.0.10;; 7: len 4; hex 80001473; ascs;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 1082 n bits 88
Re: Training and certification in Kansas City
At 18:19 -0600 7/1/04, Levi Campbell wrote: Where can I get MySQL training and certification in Kansas City? VUE handles MySQL cert testing. If you visit here: http://vue.com/mysql/ Then on the See where Pearson VUE has test centers that deliver MySQL exams. line, click the test centers link and you'll get to a page where you can find test centers. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
referential integrity for MyIsam
Hi All, I've read that MyIsam type table doesnt have the referential integrity function... I hav tried to find other 3rd party s/w that can solve the issue. Finally I read from PhpMyAdmin documentation that recently PhpMyAdmin can check referential Integrity but I still dont know how to do it after reading the documentation for some times So anyone ever face such issue?? Thanx Flame
Re: referential integrity for MyIsam
starofframe wrote: Hi All, I've read that MyIsam type table doesnt have the referential integrity function... I hav tried to find other 3rd party s/w that can solve the issue. Finally I read from PhpMyAdmin documentation that recently PhpMyAdmin can check referential Integrity but I still dont know how to do it after reading the documentation for some times So anyone ever face such issue?? Thanx Flame Don't like InnoDB? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump of longblob data type
Evewryone, We are using mysql to store documents. We are using mysqldump to take backups. We have seen restores of that backups results in corruption of document data. Can anyone tell me the reason. We have 3.24 lunning on Linux RH7 Thanks Pankaj Malviya -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
