Strange insert: ERROR 1172 (42000)
This is so weird! I'm getting a:
ERROR 1172 (42000): Result consisted of more than one row
I've setup 2 machines. Same table (dbsetup.sql) , same insert
statement (foo.sql), same version (5.0.24a).
Only difference is:
One is my laptop on FC5-i386, the other is a server on RHEL4u4-i386
Any clues? Help!
p.s. I've attached the table definition and the insert statement.
INSERT INTO logs
(host, facility, priority, level, tag, datetime, program, msg,
event_id, username, computer_name)
VALUES (
REPLACE(TRIM('SGBSPR01.isddc.men.maxis.com.my'),' ',''),
REPLACE(TRIM('user'),' ',''),
REPLACE(TRIM('notice'),' ',''),
REPLACE(TRIM('notice'),' ',''),
REPLACE(TRIM('0d'),' ',''),
REPLACE(TRIM('2006-09-26 22:06:49'),' ',''),
REPLACE(TRIM('MSWinEventLog;0;Security;6077;Tue Sep 26 22'),' ',''),
REPLACE(TRIM('MSWinEventLog;0;Security;6077;Tue Sep 26 22:06:41 2006;593;Security;SYSTEM;User;Success Audit;SGBSPR01;Detailed Tracking;;A process has exited: Process ID: 5708Image File NameC:\\WINDOWS\\system32\\cmd.exe User Name: SGBSPR01$ Domain:ISDDCLogon ID: (0x0,0x3E7);6070'), ': ',':'),
SUBSTRING_INDEX(SUBSTRING_INDEX('MSWinEventLog;0;Security;6077;Tue Sep 26 22:06:41 2006;593;Security;SYSTEM;User;Success Audit;SGBSPR01;Detailed Tracking;;A process has exited:Process ID: 5708 Image File Name: C:\\WINDOWS\\system32\\cmd.exe User Name: SGBSPR01$ Domain: ISDDC Logon ID: (0x0,0x3E7);6070', ';', 6), ';', -1),
SUBSTRING_INDEX(SUBSTRING_INDEX('MSWinEventLog;0;Security;6077;Tue Sep 26 22:06:41 2006;593;Security;SYSTEM;User;Success Audit;SGBSPR01;Detailed Tracking;;A process has exited:Process ID: 5708 Image File Name: C:\\WINDOWS\\system32\\cmd.exe User Name: SGBSPR01$ Domain: ISDDC Logon ID: (0x0,0x3E7);6070', ';', 8), ';', -1),
SUBSTRING_INDEX(SUBSTRING_INDEX('MSWinEventLog;0;Security;6077;Tue Sep 26 22:06:41 2006;593;Security;SYSTEM;User;Success Audit;SGBSPR01;Detailed Tracking;;A process has exited:Process ID: 5708 Image File Name: C:\\WINDOWS\\system32\\cmd.exe User Name: SGBSPR01$ Domain: ISDDC Logon ID: (0x0,0x3E7);6070', ';', 11), ';', -1)
);
CREATE TABLE IF NOT EXISTS logs (
seq bigint(12) unsigned NOT NULL primary key auto_increment,
hostchar(35),
facilitychar(8),
prioritychar(7),
level char(7),
tag char(2),
datetimedatetime,
program char(15),
msg text,
event_idinteger,
usernamechar(25),
computer_name char(25),
index msg_idx (msg(255)),
index host_idx (host),
index program_idx (program),
index datetime_idx (datetime),
index priority_idx (priority),
index facility_idx (facility),
index event_id_idx (event_id),
index username_idx (username),
index computer_name_idx (computer_name)
) TYPE=MyISAM row_format=fixed;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange insert
Hi all, I have an one insert statement in my hub page. For some reason, and just only on this link, I get two inserts happening about 2 seconds apart. I looked all over my code for a second insert statment, but it is just that one. Why should it be recording two inserts? This is what my insert statement looks like: insert into hub_stats values (2006-01-05, 11:01:50, XX.XX.XXX.XX, http://my.Site.com/Pervious-Page.html;, http://my.Site.com/Current-Page.html;, SessionIDB9A9F3); This is what my data looks like after hub page has been loaded. *** 2. row *** date: 2006-01-05 time: 11:12:24 IP_address: XX.XX.XXX.XX refer_URL: http://my.Site.com/Pervious-Page.html current_URL: http://my.Site.com/current-Page.html sessionid: 29072B7E7D9E00D9C1651D8E16B9A9F3 *** 3. row *** date: 2006-01-05 time: 11:12:26 IP_address: XX.XX.XXX.XX refer_URL: http://my.Site.com/Pervious-Page.html current_URL: http://my.site.com/error.html sessionid: 29072B7E7D9E00D9C1651D8E16B9A9F3 3 rows in set (0.00 sec) And it is always going to this error.html, and I can't figure out what the heck is that??? Got any idea? Thanks!
Re: Strange insert
I assume you did not intend to post to this list, but if you did then this is a problem with your application code, not with MySQL, so I'm afraid this list is not the best place to ask. James Harvard At 11:19 am -0600 5/1/06, Ngim wrote: Hi all, I have an one insert statement in my hub page. For some reason, and just only on this link, I get two inserts happening about 2 seconds apart. I looked all over my code for a second insert statment, but it is just that one. Why should it be recording two inserts? This is what my insert statement looks like: insert into hub_stats values (2006-01-05, 11:01:50, XX.XX.XXX.XX, http://my.Site.com/Pervious-Page.html;, http://my.Site.com/Current-Page.html;, SessionIDB9A9F3); This is what my data looks like after hub page has been loaded. *** 2. row *** date: 2006-01-05 time: 11:12:24 IP_address: XX.XX.XXX.XX refer_URL: http://my.Site.com/Pervious-Page.html current_URL: http://my.Site.com/current-Page.html sessionid: 29072B7E7D9E00D9C1651D8E16B9A9F3 *** 3. row *** date: 2006-01-05 time: 11:12:26 IP_address: XX.XX.XXX.XX refer_URL: http://my.Site.com/Pervious-Page.html current_URL: http://my.site.com/error.html sessionid: 29072B7E7D9E00D9C1651D8E16B9A9F3 3 rows in set (0.00 sec) And it is always going to this error.html, and I can't figure out what the heck is that??? Got any idea? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange insert
Sorry I apologize, it has to do something with my apache. -Original Message- From: James Harvard [mailto:[EMAIL PROTECTED] Sent: Thursday, January 05, 2006 11:46 AM To: Ngim Cc: mysql@lists.mysql.com Subject: Re: Strange insert I assume you did not intend to post to this list, but if you did then this is a problem with your application code, not with MySQL, so I'm afraid this list is not the best place to ask. James Harvard At 11:19 am -0600 5/1/06, Ngim wrote: Hi all, I have an one insert statement in my hub page. For some reason, and just only on this link, I get two inserts happening about 2 seconds apart. I looked all over my code for a second insert statment, but it is just that one. Why should it be recording two inserts? This is what my insert statement looks like: insert into hub_stats values (2006-01-05, 11:01:50, XX.XX.XXX.XX, http://my.Site.com/Pervious-Page.html;, http://my.Site.com/Current-Page.html;, SessionIDB9A9F3); This is what my data looks like after hub page has been loaded. *** 2. row *** date: 2006-01-05 time: 11:12:24 IP_address: XX.XX.XXX.XX refer_URL: http://my.Site.com/Pervious-Page.html current_URL: http://my.Site.com/current-Page.html sessionid: 29072B7E7D9E00D9C1651D8E16B9A9F3 *** 3. row *** date: 2006-01-05 time: 11:12:26 IP_address: XX.XX.XXX.XX refer_URL: http://my.Site.com/Pervious-Page.html current_URL: http://my.site.com/error.html sessionid: 29072B7E7D9E00D9C1651D8E16B9A9F3 3 rows in set (0.00 sec) And it is always going to this error.html, and I can't figure out what the heck is that??? Got any idea? 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]
Strange insert behavior with larger tables
This weekend I wanted to better characterize how different key configurations affect the way rows are inserted. I ran into some behavior I can't understand: I created a prototype table to allow me to track the number of events that occur on a particular day. Events are described with three integer fields and a type field which can be 0-3. The table then is: date date not null, type tinyint not null, val1 int not null, val2 int not null, val3 int not null, occurrences int not null. I then added a primary key (date, type, val1, val2, val3) because I want to ensure that I have only one row for each combination of these items. I ran these benchmarks on my Win2K box (real environment Linux, just testing here). I wrote a program which inserts a number of rows for each day (with unique type, val1, val2, val3 values). type can have the values 0-3, and val1-3 can be between 0 and 24,000. I found that I could insert about 10,000 rows in about 4-5 seconds (512 M RAM, IDE drives UDMA). It consistently took between 4-7 seconds to insert 10,000 rows until I got up to about 2 million rows. Around that time it started to slow down significantly taking about 10 seconds to insert 10,000 rows. By the time I got up to about 15 million rows, it was taking over 30-40 seconds to insert 10,000 rows. The strange thing, however is that as soon as I changed to a new day (the first element in my primary key) rows began inserting very quickly again. I was right back where I started-getting 10,000 rows in only 4-5 seconds. I guessed that the key was the limiting factor here somehow, so I tried my test again, but first dropped the primary key. Without the key I no longer have a guarantee of uniqueness, but I'm able to insert 10,000 rows every 3-4 seconds consistently well over 5 million for a single day. Therefore, it appears to be the key which is so dramatically slowing inserts as I get more rows. Curious, I ran perfmon on my machine and repeated the experiment. I observed the following: - Time to insert quadruples (from 4-5 sec/10,000 rows to 20-25 sec/10,000 rows) - Processor usage drops 2/3s (100% usage to 37% usage) - Amount written to disk/sec drop 4 fold (~400 bytes/sec to ~200 bytes/sec) - Number of writes/sec almost double (232 to 385) - Avg. amount of data with each write drops 4 fold (19000 bytes to 5000 bytes) - Avg. time to do each write stays constant (.004 sec) - The machine is NOT swapping page files on and off the disk. To summarize, at first when doing these inserts the machine is CPU bound. It is using 100% of CPU resources. However, as the number of rows for the day we are inserting increases, the machine becomes severely IO bound. This seems to be due to the fact that although the number of writes to the disk almost double, the average amount of data drops 4 fold as does the amount of data being written with each byte. Basically we switch from doing fewer larger writes and being CPU bound, to doing many many more tiny writes that the disk can't keep up with. At first (before seeing the perfmon data) I though this might have something to do with the key being held in a buffer until it grew too large. Then is when we'd start to see the performance slow down. However, I have not been able to find a value of the key buffer which really changes performance. I've tried values between 3M and 70M, with very similar results. I do get slightly poorer performance with the 3M than 70M (starts slowing down around 1.3 million instead of 2 million) but nothing that I think accounts for this problem. Why do inserts slow down once I get around two million, and I don't seem to be able to affect this by tweaking buffer sizes? Why do inserts then speed up again when switching to a new value for date, but slow down again once I get close to 2 million for the new value? I've included below a dump of variables. Thanks in advance for your insight, Brett Error back_log: 50 basedir: C:\mysql\ binlog_cache_size: 32768 character_set: latin1 character_sets: latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert: ON connect_timeout: 5 datadir: g:\mysql data\ delay_key_write: ON delayed_insert_limit: 100 delayed_insert_timeout: 300 delayed_queue_size: 1000 flush: OFF flush_time: 1800 have_bdb: NO have_gemini: NO have_innodb: NO have_isam: YES have_raid: NO have_ssl: NO init_file: interactive_timeout: 28800 join_buffer_size: 131072 key_buffer_size: 52424704 language: C:\mysql\share\english\ large_files_support: ON log: OFF log_update: OFF log_bin: OFF log_slave_updates: OFF log_long_queries: OFF long_query_time: 10 low_priority_updates: OFF lower_case_table_names: 1 max_allowed_packet: 1048576 max_binlog_cache_size: 4294967295 max_binlog_size: 1073741824 max_connections: 100 max_connect_errors: 10 max_delayed_threads: 20
