Hi,
I was able to crash MySQL every time with the following -- it is
incorrectly formatted I know and I should be using GROUP BY in this
particular case
When I do the following query:
SELECT DISTINCT * FROM CUSTOMER INNER JOIN PRODUCT ON CUSTOMER.CUST_NO =
PRODUCT.CUST_NO
Without the distinct there are no issues
SHOW VARIABLES OUTPUT:
back_log50
basedirC:\mysql4\
bdb_cache_size8388600
bdb_log_buffer_size0
bdb_home
bdb_max_lock10000
bdb_logdir
bdb_shared_dataOFF
bdb_tmpdir
bdb_versionSleepycat Software: Berkeley DB 3.2.9a: (August 28
binlog_cache_size32768
bulk_insert_buffer_size8388608
character_setlatin1
character_setslatin1 big5 czech euc_kr gb2312 gbk latin1_de 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_insertON
connect_timeout5
convert_character_set
datadirC:\mysql4\data\
delay_key_writeON
delayed_insert_limit100
delayed_insert_timeout300
delayed_queue_size1000
flushOFF
flush_time1800
ft_min_word_len4
ft_max_word_len254
ft_max_word_len_for_sort20
ft_boolean_syntax+ -><()~*:""&|
have_bdbDISABLED
have_innodbYES
have_isamYES
have_raidNO
have_symlinkYES
have_opensslNO
have_query_cacheYES
init_file
innodb_additional_mem_pool_size2097152
innodb_buffer_pool_size16777216
innodb_data_file_pathibdata1:250M
innodb_data_home_dirc:\mysql4\ibdata
innodb_file_io_threads4
innodb_force_recovery0
innodb_thread_concurrency8
innodb_flush_log_at_trx_commit1
innodb_fast_shutdownON
innodb_flush_method
innodb_lock_wait_timeout50
innodb_log_arch_dirc:\mysql4\iblogs
innodb_log_archiveOFF
innodb_log_buffer_size8388608
innodb_log_file_size5242880
innodb_log_files_in_group3
innodb_log_group_home_dirc:\mysql4\iblogs
innodb_mirrored_log_groups1
interactive_timeout28800
join_buffer_size131072
key_buffer_size16773120
languageC:\mysql4\share\english\
large_files_supportON
local_infileON
logOFF
log_updateOFF
log_binOFF
log_slave_updatesOFF
log_slow_queriesOFF
log_warningsOFF
long_query_time10
low_priority_updatesOFF
lower_case_table_namesON
max_allowed_packet1047552
max_binlog_cache_size4294967295
max_binlog_size1073741824
max_connections100
max_connect_errors10
max_delayed_threads20
max_heap_table_size16777216
max_join_size4294967295
max_sort_length1024
max_user_connections0
max_tmp_tables32
max_write_lock_count4294967295
myisam_max_extra_sort_file_size268435456
myisam_max_sort_file_size2147483647
myisam_recover_optionsOFF
myisam_sort_buffer_size8388608
named_pipeOFF
net_buffer_length8192
net_read_timeout30
net_retry_count10
net_write_timeout60
open_files_limit0
pid_fileC:\mysql4\data\Elite-Win-2000.pid
port3306
protocol_version10
read_buffer_size131072
read_rnd_buffer_size262144
rpl_recovery_rank0
query_cache_limit1048576
query_cache_size0
query_cache_typeON
safe_show_databaseOFF
server_id1
slave_net_timeout3600
skip_external_lockingON
skip_networkingOFF
skip_show_databaseOFF
slow_launch_time2
socketMySQL
sort_buffer_size524280
sql_mode0
table_cache64
table_typeMYISAM
thread_cache_size0
thread_stack65536
tx_isolationREAD-COMMITTED
timezoneAUS Eastern Standard Time
tmp_table_size33554432
tmpdirC:\DOCUME~1\MySQL\LOCALS~1\Temp\
version4.0.3-beta-max-nt
wait_timeout28800
TABLE STRUCTURE:
#
# Table structure for table 'adodbseq'
#
CREATE TABLE `adodbseq` (
`id` int(11) NOT NULL default '0'
) TYPE=InnoDB;
#
# Table structure for table 'articles'
#
CREATE TABLE `articles` (
`article_id` int(11) NOT NULL auto_increment,
`article_title` varchar(125) default NULL,
`category_id` int(11) default NULL,
`article_desc` text,
`date_posted` datetime default NULL,
`date_expire` datetime default NULL,
PRIMARY KEY (`article_id`)
) TYPE=InnoDB;
#
# Table structure for table 'categories'
#
CREATE TABLE `categories` (
`category_id` int(11) NOT NULL auto_increment,
`category_desc` varchar(50) default NULL,
PRIMARY KEY (`category_id`)
) TYPE=InnoDB;
#
# Table structure for table 'cds'
#
CREATE TABLE `cds` (
`cd_no` int(8) NOT NULL auto_increment,
`inv_no` int(8) default NULL,
`cust_no` varchar(8) default NULL,
`prod_code` varchar(10) default NULL,
`date_sent` date default NULL,
`date_expected` date default NULL,
`cd_sent` enum('Y','N') default 'N',
`cd_pack` enum('Y','N') default 'N',
PRIMARY KEY (`cd_no`),
KEY `CD_KEY` (`cd_no`),
KEY `INV_KEY` (`inv_no`),
KEY `CUST_KEY` (`cust_no`)
) TYPE=InnoDB;
#
# Table structure for table 'contacts'
#
CREATE TABLE `contacts` (
`ref_id` int(8) NOT NULL auto_increment,
`cust_no` varchar(8) default NULL,
`name` varchar(76) default NULL,
`ph_area` varchar(10) default NULL,
`ph_number` varchar(15) default NULL,
`ph_extn` varchar(5) default NULL,
`fax_area` varchar(10) default NULL,
`fax_number` varchar(15) default NULL,
`fax_extn` varchar(15) default NULL,
`mobile_no` varchar(15) default NULL,
`email_addr` varchar(76) default NULL,
`web_addr` varchar(76) default NULL,
`user` varchar(15) default NULL,
PRIMARY KEY (`ref_id`),
KEY `REF_KEY` (`ref_id`),
KEY `CUST_KEY` (`cust_no`)
) TYPE=InnoDB;
#
# Table structure for table 'counter'
#
CREATE TABLE `counter` (
`nameoftable` varchar(100) NOT NULL default '',
`counter` int(10) default NULL,
PRIMARY KEY (`nameoftable`),
KEY `NAME_KEY` (`nameoftable`)
) TYPE=InnoDB;
#
# Table structure for table 'customer'
#
CREATE TABLE `customer` (
`cust_no` varchar(8) NOT NULL default '',
`surname` varchar(255) default NULL,
`firstname` varchar(30) default NULL,
`date_entrd` date default NULL,
`post_line1` varchar(38) default NULL,
`post_line2` varchar(38) default NULL,
`post_town` varchar(27) default NULL,
`post_state` char(3) default NULL,
`post_pcode` varchar(8) default NULL,
`post_cntry` varchar(20) default NULL,
`st_line1` varchar(38) default NULL,
`st_line2` varchar(38) default NULL,
`st_town` varchar(27) default NULL,
`st_state` char(3) default NULL,
`st_pcode` varchar(8) default NULL,
`st_cntry` varchar(20) default NULL,
`contact_no` int(8) default '0',
`no_junk` enum('Y','N') default 'N',
`is_dealer` enum('Y','N') default 'N',
`cust_type` int(4) default NULL,
`heard_by` int(4) default NULL,
`user` varchar(15) default NULL,
`email_type` int(4) default NULL,
`opinion` int(4) default NULL,
`cdpack` int(5) unsigned default '0',
`supplier` int(4) default NULL,
`password` varchar(255) default NULL,
`security_level_id` smallint(6) unsigned default '0',
`last_login_date` datetime default NULL,
`last_register_date` datetime default NULL,
`last_password_check` datetime default NULL,
PRIMARY KEY (`cust_no`),
KEY `CUST_KEY` (`cust_no`),
KEY `CONTACTKEY` (`contact_no`),
KEY `SURNAM_KEY` (`surname`),
KEY `FORNAM_KEY` (`firstname`),
KEY `LINE1_KEY` (`st_line1`),
KEY `STCUST_KEY` (`st_state`,`cust_no`),
KEY `STSRNAMKEY` (`st_state`,`surname`),
KEY `STFRNAMKEY` (`st_state`,`firstname`),
KEY `STLINE1KEY` (`st_state`,`st_line1`)
) TYPE=InnoDB;
#
# Table structure for table 'customertype'
#
CREATE TABLE `customertype` (
`type_no` int(8) NOT NULL auto_increment,
`type_text` varchar(30) default NULL,
`applies_to` int(5) default NULL,
PRIMARY KEY (`type_no`),
KEY `REF_KEY` (`type_no`)
) TYPE=InnoDB;
#
# Table structure for table 'dealer'
#
CREATE TABLE `dealer` (
`cust_no` varchar(8) NOT NULL default '',
`surname` varchar(76) default NULL,
`dealer_cut` int(2) default NULL,
`memo` longtext,
`user` varchar(15) default NULL,
PRIMARY KEY (`cust_no`),
KEY `CUST_KEY` (`cust_no`),
KEY `SURNAM_KEY` (`surname`)
) TYPE=InnoDB;
#
# Table structure for table 'departments'
#
CREATE TABLE `departments` (
`type_no` int(8) NOT NULL auto_increment,
`type_text` varchar(30) default NULL,
`applies_to` int(5) default NULL,
PRIMARY KEY (`type_no`),
KEY `REF_KEY` (`type_no`)
) TYPE=InnoDB;
#
# Table structure for table 'events'
#
CREATE TABLE `events` (
`event_id` int(11) NOT NULL auto_increment,
`date_start` datetime default NULL,
`date_end` datetime default NULL,
`event_name` text,
`event_desc` text,
`presenter` varchar(100) default NULL,
`location` text,
`location_url` text,
`user_added` int(11) default '0',
`approved_by` int(11) default '0',
`date_added` datetime default NULL,
PRIMARY KEY (`event_id`)
) TYPE=InnoDB;
#
# Table structure for table 'invdetails'
#
CREATE TABLE `invdetails` (
`invdet_no` int(8) NOT NULL auto_increment,
`inv_no` int(8) default NULL,
`prod_code` varchar(10) default NULL,
`quantity` int(4) default '0',
`unit_price` double default '0',
PRIMARY KEY (`invdet_no`),
KEY `INVDET_KEY` (`invdet_no`),
KEY `INV_KEY` (`inv_no`)
) TYPE=InnoDB;
#
# Table structure for table 'invoice'
#
CREATE TABLE `invoice` (
`cust_no` varchar(8) NOT NULL default '',
`inv_no` int(8) NOT NULL default '0',
`contact_no` int(8) default '0',
`user` varchar(15) default NULL,
`date_inv` date default NULL,
`taxrate_id` varchar(8) default NULL,
`shipid` varchar(20) default NULL,
`shipdate` date default NULL,
`shipcharge` decimal(15,2) default NULL,
`discount` decimal(15,2) default NULL,
`comment` longtext,
`sent` enum('y','n') default 'n',
`datesent` date default NULL,
PRIMARY KEY (`inv_no`),
KEY `CUST_KEY` (`cust_no`),
KEY `CONTACTKEY` (`contact_no`)
) TYPE=InnoDB;
#
# Table structure for table 'knowledge'
#
CREATE TABLE `knowledge` (
`word` varchar(8) NOT NULL default '',
`dates` longtext,
`refs` longtext,
`user` varchar(15) default NULL,
PRIMARY KEY (`word`),
KEY `WORD_KEY` (`word`)
) TYPE=InnoDB;
#
# Table structure for table 'links'
#
CREATE TABLE `links` (
`link_id` int(11) NOT NULL auto_increment,
`link_name` varchar(50) default NULL,
`link_url` varchar(50) default NULL,
`link_desc` text,
`address` text,
`added_by` int(11) default '0',
`approved` int(11) default '0',
`date_approved` datetime default NULL,
`approved_by` int(11) default '0',
`date_added` datetime default NULL,
`category_id` int(11) default '0',
PRIMARY KEY (`link_id`)
) TYPE=InnoDB;
#
# Table structure for table 'lookup_countries'
#
CREATE TABLE `lookup_countries` (
`country_id` int(11) NOT NULL auto_increment,
`country_desc` varchar(40) default NULL,
PRIMARY KEY (`country_id`)
) TYPE=InnoDB;
#
# Table structure for table 'lookup_states'
#
CREATE TABLE `lookup_states` (
`state_id` char(3) NOT NULL default '',
`state_desc` varchar(45) default NULL,
PRIMARY KEY (`state_id`)
) TYPE=InnoDB;
#
# Table structure for table 'marketingtype'
#
CREATE TABLE `marketingtype` (
`type_no` int(8) NOT NULL auto_increment,
`type_text` varchar(30) default NULL,
`applies_to` int(5) default NULL,
PRIMARY KEY (`type_no`),
KEY `REF_KEY` (`type_no`)
) TYPE=InnoDB;
#
# Table structure for table 'members'
#
CREATE TABLE `members` (
`member_id` int(11) NOT NULL auto_increment,
`first_name` varchar(20) default NULL,
`last_name` varchar(20) default NULL,
`member_login` varchar(15) default NULL,
`member_password` varchar(255) NOT NULL default '',
`email` varchar(78) default NULL,
`country_id` int(11) default NULL,
`state_id` char(3) default NULL,
`city` varchar(30) default NULL,
`zip` varchar(10) default NULL,
`address1` varchar(50) default NULL,
`address2` varchar(50) default NULL,
`address3` varchar(50) default NULL,
`phone_day` varchar(20) default NULL,
`phone_evn` varchar(20) default NULL,
`fax` varchar(20) default NULL,
`date_created` datetime default NULL,
`ip_insert` varchar(50) default NULL,
`ip_update` varchar(50) default NULL,
`last_login_date` datetime default NULL,
`security_level_id` smallint(6) default '0',
`last_register_date` datetime default NULL,
`last_password_check` datetime default NULL,
PRIMARY KEY (`member_id`)
) TYPE=InnoDB;
#
# Table structure for table 'news'
#
CREATE TABLE `news` (
`news_id` int(11) NOT NULL auto_increment,
`news_html` text,
PRIMARY KEY (`news_id`)
) TYPE=InnoDB;
#
# Table structure for table 'notes'
#
CREATE TABLE `notes` (
`ref_id` int(8) NOT NULL auto_increment,
`cust_no` varchar(8) default NULL,
`subject` varchar(255) default NULL,
`memo` longtext,
`attached` longtext,
`date_entrd` datetime default NULL,
`date_due` date default NULL,
`reminder` double default NULL,
`product` varchar(10) default NULL,
`version` varchar(10) default NULL,
`username` varchar(15) default NULL,
`assignedto` varchar(15) default NULL,
`status` int(6) default NULL,
`priority` int(6) default NULL,
PRIMARY KEY (`ref_id`),
KEY `REF_KEY` (`ref_id`),
KEY `CUST_KEY` (`cust_no`),
KEY `REMIND_KEY` (`reminder`),
KEY `PROD_KEY` (`cust_no`,`product`),
KEY `VER_KEY` (`cust_no`,`version`),
KEY `SUBJ_KEY` (`cust_no`,`subject`),
KEY `STAT_KEY` (`cust_no`,`status`),
KEY `PRIOR_KEY` (`cust_no`,`priority`),
KEY `ENTRD_KEY` (`cust_no`,`date_entrd`),
KEY `DUE_KEY` (`cust_no`,`date_due`)
) TYPE=InnoDB;
#
# Table structure for table 'officers'
#
CREATE TABLE `officers` (
`officer_id` int(11) NOT NULL auto_increment,
`officer_name` varchar(50) default NULL,
`officer_position` varchar(50) default NULL,
`officer_email` varchar(30) default NULL,
PRIMARY KEY (`officer_id`)
) TYPE=InnoDB;
#
# Table structure for table 'opiniontypes'
#
CREATE TABLE `opiniontypes` (
`type_no` int(8) NOT NULL auto_increment,
`type_text` varchar(30) default NULL,
`applies_to` int(5) default NULL,
PRIMARY KEY (`type_no`),
KEY `REF_KEY` (`type_no`)
) TYPE=InnoDB;
#
# Table structure for table 'paymentmethods'
#
CREATE TABLE `paymentmethods` (
`pmeth_no` int(8) NOT NULL auto_increment,
`pmeth_name` varchar(20) default NULL,
PRIMARY KEY (`pmeth_no`),
KEY `PMETHNO_KE` (`pmeth_no`),
KEY `PNAME_KEY` (`pmeth_name`)
) TYPE=InnoDB;
#
# Table structure for table 'payments'
#
CREATE TABLE `payments` (
`p_no` int(8) NOT NULL auto_increment,
`pmeth_no` int(8) default NULL,
`p_amt` double default NULL,
`p_date` date default NULL,
`inv_no` int(8) default NULL,
PRIMARY KEY (`p_no`),
KEY `PNO_KEY` (`p_no`),
KEY `INVNO_KEY` (`inv_no`),
KEY `PMETH_KEY` (`pmeth_no`)
) TYPE=InnoDB;
#
# Table structure for table 'prodtype'
#
CREATE TABLE `prodtype` (
`prod_name` varchar(20) default NULL,
`prod_code` varchar(10) NOT NULL default '',
`unit_price` float default NULL,
PRIMARY KEY (`prod_code`),
KEY `CODE_KEY` (`prod_code`),
KEY `NAME_KEY` (`prod_name`)
) TYPE=InnoDB;
#
# Table structure for table 'product'
#
CREATE TABLE `product` (
`ref_id` int(8) NOT NULL auto_increment,
`cust_no` varchar(8) default NULL,
`contact_no` int(8) default '0',
`product_id` varchar(10) default NULL,
`version` varchar(10) default NULL,
`purchased` date default NULL,
`registered` date default NULL,
`expires` date default NULL,
`price_paid` double default NULL,
`dealrprice` double default NULL,
`phone_supp` enum('Y','N') default 'N',
`dealer_id` varchar(8) default NULL,
`machine_id` varchar(10) default NULL,
`unlockcode` varchar(16) default NULL,
`user` varchar(15) default NULL,
PRIMARY KEY (`ref_id`),
KEY `REF_KEY` (`ref_id`),
KEY `CUST_KEY` (`cust_no`),
KEY `CONTACTKEY` (`contact_no`),
KEY `OVRVW_KEY` (`cust_no`,`product_id`),
KEY `PROD_KEY` (`product_id`)
) TYPE=InnoDB;
#
# Table structure for table 'register_products'
#
CREATE TABLE `register_products` (
`prod_name` char(20) default NULL,
`prod_code` char(10) NOT NULL default '',
PRIMARY KEY (`prod_code`),
KEY `prod_code` (`prod_code`)
) TYPE=InnoDB;
#
# Table structure for table 'shipping'
#
CREATE TABLE `shipping` (
`ship_no` varchar(8) NOT NULL default '',
`ship_name` varchar(20) default NULL,
PRIMARY KEY (`ship_no`),
KEY `SHIPNO_KEY` (`ship_no`),
KEY `NAME_KEY` (`ship_name`)
) TYPE=InnoDB;
#
# Table structure for table 'softwaresuppliertype'
#
CREATE TABLE `softwaresuppliertype` (
`type_no` int(8) NOT NULL auto_increment,
`type_text` varchar(30) default NULL,
`applies_to` int(5) default NULL,
PRIMARY KEY (`type_no`),
KEY `REF_KEY` (`type_no`)
) TYPE=InnoDB;
#
# Table structure for table 'suppcode'
#
CREATE TABLE `suppcode` (
`code_name` varchar(50) default NULL,
`code_id` varchar(8) NOT NULL default '',
PRIMARY KEY (`code_id`),
KEY `CODE_KEY` (`code_id`),
KEY `NAME_KEY` (`code_name`)
) TYPE=InnoDB;
#
# Table structure for table 'suppcodedetails'
#
CREATE TABLE `suppcodedetails` (
`ref_id` int(8) NOT NULL auto_increment,
`supp_id` varchar(8) default NULL,
`code_id` varchar(10) default NULL,
PRIMARY KEY (`ref_id`),
KEY `REF_KEY` (`ref_id`),
KEY `CODE_KEY` (`code_id`),
KEY `SUPP_KEY` (`supp_id`)
) TYPE=InnoDB;
#
# Table structure for table 'support'
#
CREATE TABLE `support` (
`ref_id` int(8) NOT NULL auto_increment,
`cust_no` varchar(8) default NULL,
`date` datetime default NULL,
`time` datetime default NULL,
`department` int(2) default NULL,
`taken_by` varchar(30) default NULL,
`product` varchar(10) default NULL,
`title` varchar(100) default NULL,
`category` int(8) default NULL,
`scategory` int(8) default NULL,
`key_words` varchar(30) default NULL,
`soln_codes` varchar(8) default NULL,
`detail` longtext,
`solution` longtext,
`ans_phone` enum('Y','N') default 'N',
`ans_fax` enum('Y','N') default 'N',
`ans_email` enum('Y','N') default 'N',
`faqpending` enum('Y','N') default 'N',
`unhappy` enum('Y','N') default 'N',
`testtrack` varchar(6) default NULL,
`user` varchar(15) default NULL,
PRIMARY KEY (`ref_id`),
KEY `REF_KEY` (`ref_id`),
KEY `CUST_KEY` (`cust_no`),
KEY `FAQPENDING` (`faqpending`)
) TYPE=InnoDB;
#
# Table structure for table 'supportcallsubtypes'
#
CREATE TABLE `supportcallsubtypes` (
`type_no` int(8) NOT NULL default '0',
`type_text` varchar(30) default NULL,
`category` int(8) default NULL,
PRIMARY KEY (`type_no`),
KEY `REF_KEY` (`type_no`),
KEY `CATE_KEY` (`category`)
) TYPE=InnoDB;
#
# Table structure for table 'supportcalltypes'
#
CREATE TABLE `supportcalltypes` (
`type_no` int(8) NOT NULL auto_increment,
`type_text` varchar(30) default NULL,
`applies_to` tinyint(5) default NULL,
`admin` set('Y','N') default 'N',
`support` set('Y','N') default 'N',
PRIMARY KEY (`type_no`),
KEY `REF_KEY` (`type_no`)
) TYPE=InnoDB;
#
# Table structure for table 'taxrates'
#
CREATE TABLE `taxrates` (
`taxrate_id` varchar(8) NOT NULL default '',
`tax_desc` varchar(20) default NULL,
`taxrate` float default NULL,
PRIMARY KEY (`taxrate_id`),
KEY `TAXID_KEY` (`taxrate_id`),
KEY `NAME_KEY` (`tax_desc`)
) TYPE=InnoDB;
#
# Table structure for table 'temp_licenses'
#
CREATE TABLE `temp_licenses` (
`ref_id` tinyint(15) unsigned NOT NULL default '0',
`cust_no` varchar(8) NOT NULL default '',
`product_id` varchar(10) NOT NULL default '',
`registered` date default NULL,
`expires` date default NULL,
`machine_id` varchar(10) default NULL,
`unlockcode` varchar(16) default NULL,
PRIMARY KEY (`ref_id`),
UNIQUE KEY `ref_id` (`ref_id`),
UNIQUE KEY `cust_no` (`cust_no`,`product_id`)
) TYPE=InnoDB;
#
# Table structure for table 'users'
#
CREATE TABLE `users` (
`username` varchar(15) NOT NULL default '',
`password` varchar(255) default NULL,
`user_level` int(15) default NULL,
PRIMARY KEY (`username`)
) TYPE=InnoDB;
---------------------------------------------------------------------
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
