re: SELECT DISTINCT bug MySQL 4.0.3 MAX NT
Glenn, Friday, September 20, 2002, 5:27:01 AM, you wrote: GW I was able to crash MySQL every time with the following -- it is GW incorrectly formatted I know and I should be using GROUP BY in this GW particular case GW When I do the following query: GW SELECT DISTINCT * FROM CUSTOMER INNER JOIN PRODUCT ON CUSTOMER.CUST_NO = GW PRODUCT.CUST_NO It's a known bug and is already fixed in 4.0.4 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
SELECT DISTINCT bug MySQL 4.0.3 MAX NT
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_lock1
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
SELECT DISTINCT - bug?
Jag har problem med en enkel SELECT DISTINCT, på ett enda fält i en enda
tabell. Har jag missat något mycket grundläggande eller är det en BUG?
mysql repair table faktura_history;
+++--+--+
| Table | Op | Msg_type | Msg_text |
+++--+--+
| adzdisplay.faktura_history | repair | status | OK |
+++--+--+
1 row in set (0.00 sec)
mysql describe faktura_history;
+---+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-++
| id| int(11) | | PRI | NULL| auto_increment |
| salj_id | int(11) | YES | | NULL||
| kund_id | int(11) | YES | | NULL||
| summa | int(11) | YES | | NULL||
| fritext | text | YES | | NULL||
| fakturerad| enum('y','n') | YES | | NULL||
| dagar | int(11) | YES | | NULL||
| pris | int(11) | YES | | NULL||
| extra_procent | float(10,5) | YES | | NULL||
| updated | timestamp(14) | YES | | NULL||
| indate| timestamp(8) | YES | | NULL||
| ovrig | enum('y','n') | YES | | n ||
+---+---+--+-+-++
12 rows in set (0.00 sec)
mysql SELECT DISTINCT indate FROM faktura_history;
+--+
| indate |
+--+
| 20010515 |
| 20010516 |
| 20010517 |
| 20010521 |
| 20010522 |
| 20010522 |
| 20010522 |
| 20010522 |
| 20010522 |
| 20010530 |
| 20010530 |
| 20010530 |
| 20010530 |
| 20010530 |
| 20010530 |
| 20010530 |
| 20010530 |
| 20010530 |
| 20010530 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010531 |
| 20010601 |
| 20010601 |
| 20010601 |
| 20010601 |
| 20010601 |
| 20010605 |
| 20010605 |
| 20010605 |
| 20010612 |
| 20010612 |
| 20010612 |
| 20010612 |
| 20010612 |
| 20010612 |
| 20010612 |
| 20010613 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010618 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010626 |
| 20010627 |
| 20010702 |
| 20010702 |
| 20010703 |
| 20010716 |
| 20010719 |
| 20010802 |
| 20010807 |
| 20010809 |
| 20010813 |
| 20010814 |
| 20010814 |
| 20010814 |
| 20010814 |
| 20010814 |
| 20010814 |
| 20010814 |
| 20010814 |
| 20010815 |
| 20010815 |
| 20010815 |
| 20010815 |
| 20010815 |
| 20010907 |
| 20011009 |
| 20011012 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
| 20011029 |
+--+
150 rows in set (0.00 sec)
Känns inte särskilt DISTINCT. :( Samma sak händer med DISTINCTROW.
Mvh Anders Bjarby, Infomaker Scandinavia AB
-
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
RE: SELECT DISTINCT - bug?
* Anders Bjarby Jag har problem med en enkel SELECT DISTINCT, på ett enda fält i en enda tabell. Har jag missat något mycket grundläggande eller är det en BUG? I would suggest you use english on this list... :) cut | indate| timestamp(8) | YES | | NULL|| I think your problem is the data type... timestamp is a special field type, used for automatic storing of the current time for inserts and updates. I think the entire timestamp is stored in the column, even if you have defined it as timestamp(8). (Only the display width of the column is restricted by (8).) Try changing it to a DATE field. -- Roger database, table - 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
