List:General Discussion« Previous MessageNext Message »
From:Mikhail Berman Date:January 29 2014 5:07pm
Subject:Troubles with creating indexes on float columns on MyISAM tables in
MySQL 5.6.15 and MySQL 5.6.14 running on FreeBSD 8.4
View as plain text  
I got an "interesting" problem with creation of indexes on MyISAM
tables  in MySQL 5.6.15 and MySQL 5.6.14 running on FreeBSD 8.4 for float
columns - I am not able to create indexes on these columns

Indexes on all other columns work just fine

The problem occur while I was loading data from MySQL dump into a database.

Loads would  fail on the line "ENABLE KEYS" in a dump with  "ERROR 2013
(HY000): Lost connection to MySQL server during query"

The problem was recreated in many different scenarios and could be
reconstructed with a simple test:

I have a table:

mysql> show create table LEGAL_REGISTRATION_TWO\G;
*************************** 1. row ***************************
       Table: LEGAL_REGISTRATION_TWO
Create Table: CREATE TABLE `LEGAL_REGISTRATION_TWO` (
  `legal_registration_key` int(10) unsigned NOT NULL DEFAULT '0',
  `company_fkey` varchar(10) NOT NULL DEFAULT '',
  `law_firm_fkey` varchar(10) NOT NULL DEFAULT '',
  `registrant_is_guarantor` int(1) NOT NULL DEFAULT '0',
  `plan_name` text NOT NULL,
  `copy_sent_to_firm` int(1) NOT NULL DEFAULT '0',
  `copy_sent_to_firm_name_address_text` text NOT NULL,
  `law_firm_opinion` int(1) NOT NULL DEFAULT '0',
  `law_firm_opinion_type` varchar(10) NOT NULL DEFAULT '',
  `law_firm_opinion_text` text NOT NULL,
  `law_firm_opinion_text_url` varchar(200) NOT NULL DEFAULT '',
  `law_firm_relationship` varchar(20) NOT NULL DEFAULT '',
  `legal_fees` float NOT NULL DEFAULT '0',
  `accounting_fees` float(10,2) NOT NULL DEFAULT '0.00',
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
I am attempting to
create an index on this field
  `ftp_file_name_fkey` varchar(80) NOT NULL DEFAULT '',
  `form_fkey` varchar(20) NOT NULL DEFAULT '',
  `file_date` varchar(10) NOT NULL DEFAULT '',
  `file_accepted` varchar(20) NOT NULL DEFAULT '',
  `file_size` varchar(10) NOT NULL DEFAULT '',
  `http_file_name_html` varchar(100) NOT NULL DEFAULT '',
  `http_file_name_text` varchar(100) NOT NULL DEFAULT '',
  `qc_check_1` int(1) NOT NULL DEFAULT '0',
  `qc_check_2` int(1) NOT NULL DEFAULT '0',
  `create_date` varchar(10) NOT NULL DEFAULT '',
  `change_date` varchar(10) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

With a single row in it:

mysql> select count(*) from LEGAL_REGISTRATION_TWO;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

When I attempting to alter the table to create an index on a float column I
get the error:

mysql> alter table LEGAL_REGISTRATION_TWO add key test1dx
(`accounting_fees`);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

I have made a number of  changes in /etc/my.cnf trying to resolve this
problem and currently the following entries are in my.cnf:

net_read_timeout=2400
net_write_timeout=2400
big-tables=on
connect_timeout=40
myisam_sort_buffer_size=1073741824

max_allowed_packet = 128M

I am not finding any talk on Internet about this being a problem for
someone else.


Any idea how to solve this problem are greatly appreciated


-- 
Mikhail Berman

Thread
Troubles with creating indexes on float columns on MyISAM tables inMySQL 5.6.15 and MySQL 5.6.14 running on FreeBSD 8.4Mikhail Berman29 Jan 2014