I think i stumbled upon a bug that's got something to do with join
size. Detailed information below.
Using 2 intel-linux platforms with replication the query stated
below works fine on one machine:
- mysql Ver 11.15 Distrib 3.23.37, for pc-linux-gnu (i686)
- Linux deedee 2.2.16-SMP #1 SMP Wed Aug 16 05:07:06 GMT 2000 i686
unknown
and gives reduced results on the other:
- mysql Ver 11.15 Distrib 3.23.41, for suse-linux (i686)
- Linux blossom 2.4.10-4GB #1 Fri Sep 28 17:20:21 GMT 2001 i686 unknown
The query states as follows:
SELECT isp.naam AS provider, MID(log.logtijd,1,10) AS datum,
acties.adres AS adres, acties.omschrijving AS adres_omschrijving,
SUM(log.data) AS bytes, SUM(log.duur)/10 AS seconden,
SUM(log.data)*80/SUM(log.duur) AS kbs, COUNT(*) AS aantal,
inbelpunten.id, inbelpunten.plaats, inbelpunten.netwerk FROM log, taken,
deploy , acties , inbelpunten, isp WHERE log.taakID = taken.ID AND
taken.deployID = deploy.ID AND log.actieID = acties.ID AND
taken.inbelpuntid=inbelpunten.id AND inbelpunten.ispid=isp.id AND
log.logtijd >= '2002-03-22' and log.logtijd < DATE_ADD('2002-03-28',
INTERVAL 1 DAY) AND (deploy.keyword = 'STISDN') AND taken.medium='isdn'
AND ((log.meldingid = 10) OR (log.meldingid = 11) OR (log.meldingid =
12)) GROUP BY isp.ID ORDER BY kbs DESC
The output should be (as on the first machine):
*************************** 1. row ***************************
provider: FREELER
datum: 2002-03-22
adres: http://www.intre.com/test/50.html
adres_omschrijving: Amsterdam 5
bytes: 29588864
seconden: 4267.60
kbs: 55466.986596682
aantal: 576
id: 58
plaats: Leiden
netwerk:
cut here for obvious reasons
---------------------------------8<-------------------------
*************************** 24. row ***************************
provider: TISCALI-BUSINESS
datum: 2002-03-22
adres: http://www.intre.com/test/50.html
adres_omschrijving: Amsterdam 5
bytes: 29610153
seconden: 8270.10
kbs: 28643.090651866
aantal: 584
id: 13
plaats: Leiden
netwerk:
24 rows in set (9.55 sec)
BUT on the other machine it shows the following:
*************************** 1. row ***************************
provider: NOKNOK
datum: 2002-03-22
adres: http://www.intre.com/test/50.html
adres_omschrijving: Amsterdam 5
bytes: 275117
seconden: 42.80
kbs: 51423.738317757
aantal: 9
id: 23
plaats: Leiden
netwerk:
*************************** 2. row ***************************
provider: TISCALI-GRATIS
datum: 2002-03-28
adres: http://server106.hypermart.net/ipingres/50.html
adres_omschrijving: Seattle
bytes: 28783
seconden: 20.50
kbs: 11232.390243902
aantal: 1
id: 96
plaats: Leiden
netwerk:
2 rows in set (6.52 sec)
Now if I reduce the date-part of the query e.g.:
AND log.logtijd >= '2002-03-22' to
AND log.logtijd >= '2002-03-26'
it works like expected again giving me the 24 rows.
The same counts for changing:
and log.logtijd < DATE_ADD('2002-03-28', INTERVAL 1 DAY) to
and log.logtijd < DATE_ADD('2002-03-24', INTERVAL 1 DAY)
Adding other restrictions to the where-clause limiting the total
join size also seem to work.
The details according to your bug-report guide:
- MYSQL: Ver 11.15 Distrib 3.23.41, for suse-linux (i686)
- MACHINE: Intel P4, ASUS P4X, 1,5 GB DDR
- OS: Linux blossom 2.4.10-4GB #1 Fri Sep 28 17:20:21 GMT 2001 i686
unknown
- VIRTUAL MEMORY: 1 GB
- TABLE INFO: see attachment
- RECORD COUNTS:
- LOG: 14308073
- TAKEN: 1056551
- DEPLOY: 5268
- ACTIES: 6437884
- INBELPUNTEN: 236
- ISP: 95
- VARIABLES: see attachment
Please comment on this. I've read the mysql documentation and
bugfixes information and couldn't find this as a known issue.
Since the machine in question produces online reports i can't just
run crashme scripts or anyting the like.
Also, let me know if you need additional information.
Vico Timmermans
IPing research bv
Leiden
The Netherlands
-- Attachment --
# MySQL dump 8.14
#
# Host: localhost Database: iping
#--------------------------------------------------------
# Server version 3.23.41
#
# Table structure for table 'log'
#
CREATE TABLE log (
ID int(11) unsigned NOT NULL auto_increment,
taakID int(11) unsigned NOT NULL default '0',
actieID int(11) default NULL,
logtijd datetime NOT NULL default '0000-00-00 00:00:00',
meldingID int(11) NOT NULL default '0',
duur int(11) NOT NULL default '0',
data varchar(80) default NULL,
client varchar(40) NOT NULL default '',
traceroute text,
bps int(11) default NULL,
PRIMARY KEY (ID),
KEY taakID (taakID),
KEY logtijd (logtijd),
KEY meldingID (meldingID)
) TYPE=MyISAM;
#
# Table structure for table 'taken'
#
CREATE TABLE taken (
ID int(11) unsigned NOT NULL auto_increment,
inbelpuntID int(11) unsigned NOT NULL default '0',
begintijd datetime NOT NULL default '0000-00-00 00:00:00',
eindtijd datetime NOT NULL default '0000-00-00 00:00:00',
omschrijving varchar(160) default NULL,
redials int(11) unsigned default '1',
timeout int(11) unsigned default '1800',
done enum('0','1','2') NOT NULL default '0',
busy enum('0','1') NOT NULL default '0',
medium enum('v90','isdn','dubbel','x75') NOT NULL default 'v90',
attempt int(11) NOT NULL default '0',
deployID int(11) unsigned NOT NULL default '0',
client varchar(40) NOT NULL default 'All',
PRIMARY KEY (ID),
KEY inbelpuntID (inbelpuntID),
KEY deployID (deployID),
KEY begintijd (begintijd),
KEY eindtijd (eindtijd)
) TYPE=MyISAM;
#
# Table structure for table 'deploy'
#
CREATE TABLE deploy (
ID int(11) unsigned NOT NULL auto_increment,
omschrijving varchar(160) NOT NULL default '',
datum datetime NOT NULL default '0000-00-00 00:00:00',
startdatum date default NULL,
einddatum date default NULL,
history enum('0','1') NOT NULL default '0',
keyword varchar(8) default NULL,
PRIMARY KEY (ID),
KEY keyword (keyword)
) TYPE=MyISAM;
#
# Table structure for table 'acties'
#
CREATE TABLE acties (
ID int(11) unsigned NOT NULL auto_increment,
taakID int(11) unsigned NOT NULL default '0',
adres varchar(255) default NULL,
timeout int(11) unsigned default '120',
done enum('0','1','2','3') default '0',
actietype
enum('ping','http','httpmozilla','nameserver','traceroute','tracebadroute','pop3','smtp','wait','response')
NOT NULL default 'ping',
pings int(11) default NULL,
packetsize int(11) default NULL,
poplogin varchar(30) default NULL,
poppassword varchar(30) default NULL,
badspeed tinyint(4) default NULL,
omschrijving varchar(30) default NULL,
proxy varchar(40) default NULL,
PRIMARY KEY (ID),
KEY taakID (taakID)
) TYPE=MyISAM;
#
# Table structure for table 'inbelpunten'
#
CREATE TABLE inbelpunten (
ID int(11) unsigned NOT NULL auto_increment,
ISPID int(11) unsigned NOT NULL default '0',
telefoonnr varchar(15) default '',
plaats varchar(40) default '',
username varchar(40) default '',
password varchar(40) default '',
ISDN enum('0','1') default '0',
dubbelISDN enum('0','1') default '0',
V90 enum('0','1') default '0',
chatstring varchar(255) NOT NULL default '',
X75 enum('0','1') default '0',
smtp_server varchar(50) default NULL,
pop3_server varchar(50) default NULL,
email varchar(50) default NULL,
pop3_username varchar(40) default NULL,
pop3_password varchar(40) default NULL,
netwerk varchar(40) default NULL,
netwerkgeheim varchar(40) default NULL,
PRIMARY KEY (ID)
) TYPE=MyISAM;
#
# Table structure for table 'isp'
#
CREATE TABLE isp (
ID int(11) unsigned NOT NULL auto_increment,
Naam varchar(40) default '',
Omschrijving text,
resolv_domain varchar(40) default NULL,
resolv_ns varchar(15) default NULL,
ip_local varchar(15) default NULL,
ip_remote varchar(15) default NULL,
PRIMARY KEY (ID)
) TYPE=MyISAM;
*************************** 1. row ***************************
Variable_name: back_log
Value: 50
*************************** 2. row ***************************
Variable_name: basedir
Value: /usr/
*************************** 3. row ***************************
Variable_name: binlog_cache_size
Value: 32768
*************************** 4. row ***************************
Variable_name: character_set
Value: latin1
*************************** 5. row ***************************
Variable_name: character_sets
Value: 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
*************************** 6. row ***************************
Variable_name: concurrent_insert
Value: ON
*************************** 7. row ***************************
Variable_name: connect_timeout
Value: 5
*************************** 8. row ***************************
Variable_name: datadir
Value: /var/lib/mysql/
*************************** 9. row ***************************
Variable_name: delay_key_write
Value: ON
*************************** 10. row ***************************
Variable_name: delayed_insert_limit
Value: 100
*************************** 11. row ***************************
Variable_name: delayed_insert_timeout
Value: 300
*************************** 12. row ***************************
Variable_name: delayed_queue_size
Value: 1000
*************************** 13. row ***************************
Variable_name: flush
Value: OFF
*************************** 14. row ***************************
Variable_name: flush_time
Value: 0
*************************** 15. row ***************************
Variable_name: have_bdb
Value: NO
*************************** 16. row ***************************
Variable_name: have_gemini
Value: NO
*************************** 17. row ***************************
Variable_name: have_innodb
Value: NO
*************************** 18. row ***************************
Variable_name: have_isam
Value: YES
*************************** 19. row ***************************
Variable_name: have_raid
Value: NO
*************************** 20. row ***************************
Variable_name: have_ssl
Value: NO
*************************** 21. row ***************************
Variable_name: init_file
Value:
*************************** 22. row ***************************
Variable_name: interactive_timeout
Value: 28800
*************************** 23. row ***************************
Variable_name: join_buffer_size
Value: 8384512
*************************** 24. row ***************************
Variable_name: key_buffer_size
Value: 268431360
*************************** 25. row ***************************
Variable_name: language
Value: /usr/share/mysql/english/
*************************** 26. row ***************************
Variable_name: large_files_support
Value: ON
*************************** 27. row ***************************
Variable_name: locked_in_memory
Value: OFF
*************************** 28. row ***************************
Variable_name: log
Value: OFF
*************************** 29. row ***************************
Variable_name: log_update
Value: OFF
*************************** 30. row ***************************
Variable_name: log_bin
Value: OFF
*************************** 31. row ***************************
Variable_name: log_slave_updates
Value: OFF
*************************** 32. row ***************************
Variable_name: log_long_queries
Value: OFF
*************************** 33. row ***************************
Variable_name: long_query_time
Value: 10
*************************** 34. row ***************************
Variable_name: low_priority_updates
Value: OFF
*************************** 35. row ***************************
Variable_name: lower_case_table_names
Value: 0
*************************** 36. row ***************************
Variable_name: max_allowed_packet
Value: 1047552
*************************** 37. row ***************************
Variable_name: max_binlog_cache_size
Value: 4294967295
*************************** 38. row ***************************
Variable_name: max_binlog_size
Value: 1073741824
*************************** 39. row ***************************
Variable_name: max_connections
Value: 500
*************************** 40. row ***************************
Variable_name: max_connect_errors
Value: 10
*************************** 41. row ***************************
Variable_name: max_delayed_threads
Value: 20
*************************** 42. row ***************************
Variable_name: max_heap_table_size
Value: 16777216
*************************** 43. row ***************************
Variable_name: max_join_size
Value: 4294967295
*************************** 44. row ***************************
Variable_name: max_sort_length
Value: 1024
*************************** 45. row ***************************
Variable_name: max_user_connections
Value: 0
*************************** 46. row ***************************
Variable_name: max_tmp_tables
Value: 32
*************************** 47. row ***************************
Variable_name: max_write_lock_count
Value: 4294967295
*************************** 48. row ***************************
Variable_name: myisam_recover_options
Value: 0
*************************** 49. row ***************************
Variable_name: myisam_max_extra_sort_file_size
Value: 256
*************************** 50. row ***************************
Variable_name: myisam_max_sort_file_size
Value: 2047
*************************** 51. row ***************************
Variable_name: myisam_sort_buffer_size
Value: 67108864
*************************** 52. row ***************************
Variable_name: net_buffer_length
Value: 7168
*************************** 53. row ***************************
Variable_name: net_read_timeout
Value: 30
*************************** 54. row ***************************
Variable_name: net_retry_count
Value: 10
*************************** 55. row ***************************
Variable_name: net_write_timeout
Value: 60
*************************** 56. row ***************************
Variable_name: open_files_limit
Value: 0
*************************** 57. row ***************************
Variable_name: pid_file
Value: /var/lib/mysql/mysqld.pid
*************************** 58. row ***************************
Variable_name: port
Value: 3306
*************************** 59. row ***************************
Variable_name: protocol_version
Value: 10
*************************** 60. row ***************************
Variable_name: record_buffer
Value: 268431360
*************************** 61. row ***************************
Variable_name: record_rnd_buffer
Value: 268431360
*************************** 62. row ***************************
Variable_name: query_buffer_size
Value: 0
*************************** 63. row ***************************
Variable_name: safe_show_database
Value: ON
*************************** 64. row ***************************
Variable_name: server_id
Value: 3
*************************** 65. row ***************************
Variable_name: slave_net_timeout
Value: 3600
*************************** 66. row ***************************
Variable_name: skip_locking
Value: ON
*************************** 67. row ***************************
Variable_name: skip_networking
Value: OFF
*************************** 68. row ***************************
Variable_name: skip_show_database
Value: OFF
*************************** 69. row ***************************
Variable_name: slow_launch_time
Value: 2
*************************** 70. row ***************************
Variable_name: socket
Value: /var/lib/mysql/mysql.sock
*************************** 71. row ***************************
Variable_name: sort_buffer
Value: 33554424
*************************** 72. row ***************************
Variable_name: sql_mode
Value: 0
*************************** 73. row ***************************
Variable_name: table_cache
Value: 384
*************************** 74. row ***************************
Variable_name: table_type
Value: MYISAM
*************************** 75. row ***************************
Variable_name: thread_cache_size
Value: 0
*************************** 76. row ***************************
Variable_name: thread_stack
Value: 65536
*************************** 77. row ***************************
Variable_name: transaction_isolation
Value: READ-COMMITTED
*************************** 78. row ***************************
Variable_name: timezone
Value: CET
*************************** 79. row ***************************
Variable_name: tmp_table_size
Value: 33554432
*************************** 80. row ***************************
Variable_name: tmpdir
Value: /tmp/
*************************** 81. row ***************************
Variable_name: version
Value: 3.23.41
*************************** 82. row ***************************
Variable_name: wait_timeout
Value: 28800
82 rows in set (0.00 sec)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
