Skip to content
This repository has been archived by the owner. It is now read-only.

[dev.icinga.com #2181] change varchar(255) to TEXT in mysql #816

Closed
icinga-migration opened this issue Dec 12, 2011 · 22 comments
Milestone

Comments

@icinga-migration
Copy link
Member

@icinga-migration icinga-migration commented Dec 12, 2011

This issue has been migrated from Redmine: https://dev.icinga.com/issues/2181

Created by mfriedrich on 2011-12-12 13:54:51 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2012-05-01 17:36:09 +00:00)
Target Version: 1.7
Last Update: 2014-12-08 14:46:29 +00:00 (in Redmine)


varchar(255) might be less than demanded and therefore the column should be changed to text, same as longoutput and perfdata.

related to #2131

Changesets

2012-04-25 16:21:07 +00:00 by mfriedrich df4ef62

idoutils: change varchar(255) to TEXT in mysql (not cs and address rfc columns) #2181

refs #2181

2012-04-27 09:16:29 +00:00 by mfriedrich 0b9ef1a

revert TEXT for mysql configfile_path, as this is a unique constraint member

more on the issue itsself.

fixes #2181

2012-05-01 14:31:07 +00:00 by mfriedrich 07d4985

revert command_args to TEXT in contactnotificationcommands being a unique constraints

refs #2181

2012-05-01 16:13:19 +00:00 by mfriedrich 275664f

make mysql happy with unique keys *grmbl*

refs #2181

Relations:

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Jan 27, 2012

Updated by mfriedrich on 2012-01-27 20:39:06 +00:00

  • Category set to 24
  • Status changed from New to Assigned
  • Assigned to set to mfriedrich
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 3, 2012

Updated by mfriedrich on 2012-04-03 12:06:12 +00:00

  • Status changed from Assigned to Feedback
  • Target Version deleted 1.7

before changing this, more feedback is required.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 4, 2012

Updated by Tommi on 2012-04-04 19:02:48 +00:00

should be possible. So far i can see are 8 tables affected. In oracle output field is already extended to 2048 Bytes. mysql type text means 65k. if it should be equalized with long_output, then we can think about why we need an output and a long output column, if the size is the same. Searching in bigger fields might be slower, espially in LOBS in oracle. For this exists a special oracle option "Oracle Text", which adds full text search to the DB (and requires propretary sql syntax). Dont know if there is similar in pgsql and mysql.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 25, 2012

Updated by mfriedrich on 2012-04-25 10:46:49 +00:00

affects other varchar limits as well - mostly those with 255 letters.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 25, 2012

Updated by mfriedrich on 2012-04-25 15:50:34 +00:00

  • Subject changed from change output column to text in mysql to change varchar(255) to TEXT in mysql
  • Status changed from Feedback to Assigned
  • Target Version set to 1.7

this should not harm performance that much, and can be a nice addin for 1.7 as change everything release.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 25, 2012

Updated by mfriedrich on 2012-04-25 16:00:43 +00:00

latin1_general_cs varchars will remain, like display_name and name1/2, otherwise selects won't work case sensitive.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 25, 2012

Updated by mfriedrich on 2012-04-25 16:11:20 +00:00

contacts.email_address is left 255 chars, there's an rfc for that.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 25, 2012

Updated by mfriedrich on 2012-04-25 16:37:13 +00:00

  • Status changed from Assigned to Feedback
  • Done % changed from 0 to 90
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 26, 2012

Updated by melle on 2012-04-26 11:32:09 +00:00

  • upgrade from 1.6.1 to latest rev from git went smoothly overall, though there should be an informational text about the change of the location of the idomod binary
  • operation is "as usual", so far there are no obvious problems introduced by upgrading the database objects
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 26, 2012

Updated by mfriedrich on 2012-04-26 11:49:17 +00:00

hmm thanks for the feedback. where would you put that change message? it's already within the Changelog itsself .. make install-idoutils ?

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 26, 2012

Updated by melle on 2012-04-26 12:04:22 +00:00

I noticed this quite important change only by lurking through the changelog out of curiosity, yes. I would not have noticed this when performing a "standard" upgrade, as I do not often look through the changelog at all.

I second your idea showing a message after invoking "make install-idoutils", as at this point people are expecting problems or hints to show up.

Another option would be deleting or moving bin/idomod.o and symlinking lib/idomod.so to bin/idomod.o, so no config change would be required. But I assume this is bad practice in full glory :-)

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 26, 2012

Updated by mfriedrich on 2012-04-26 12:16:40 +00:00

see #2558

the change will look like this now for 1.7 (soon to be pushed to git)

*** IDOUtils installed ***

!!! ATTENTION !!!
        idomod.o was renamed to idomod.so, module extension is now .so
        idomod.so will now be installed to ibdir instead of indir
!!! ATTENTION !!!

Please check Changelog - CHANGES for more information!
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 26, 2012

Updated by melle on 2012-04-26 12:35:00 +00:00

Be sure to not lose any letters in the makefile output by accidentially escaping them (see "ibdir", "indir" above :-D)

But besides this minor cosmetic issue - great work!

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 26, 2012

Updated by mfriedrich on 2012-04-26 12:37:15 +00:00

  • Status changed from Feedback to Resolved
  • Done % changed from 90 to 100

yep, i will tell about that further in #2558
resolving here.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 27, 2012

Updated by mfriedrich on 2012-04-27 08:16:18 +00:00

  • Status changed from Resolved to Assigned
  • Done % changed from 100 to 90

that will not work. mysql cannot garantuee the uniqueness for the index of the first N entries of a TEXT value, but requires either a drop of the unique constraint, or a fallback to varchar again. telling TEXT (255) will not work, sadly.

CREATE TABLE IF NOT EXISTS icinga_configfiles (
  configfile_id serial,
  instance_id bigint unsigned default 0,
  configfile_type smallint default 0,
  configfile_path TEXT character set latin1  default '',
  PRIMARY KEY  (configfile_id),
  UNIQUE KEY instance_id (instance_id,configfile_type,configfile_path)
) ENGINE=InnoDB  COMMENT='Configuration files';
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 27, 2012

Updated by mfriedrich on 2012-04-27 08:17:06 +00:00

http://www.mydigitallife.info/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/

MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length

When creating a new table or altering an existing table with primary keys, unique constraints and indexes, or when defining a new index with Alter Table manipulation statement in MySQL database, the following error may occur and prohibit the the command from completing:

ERROR 1170 (42000): BLOB/TEXT column ‘field_name’ used in key specification without a key length

The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happen when there is a field/column type of TEXT or BLOB or those belongs to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make as primary key or index. With full BLOB or TEXT without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. So, when using BLOB or TEXT types as index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support limit on TEXT or BLOB. TEXT(88) simply won’t work.

The error will also pop up when you try to convert a table column from non-TEXT and non-BLOB type such as VARCHAR and ENUM into TEXT or BLOB type, with the column already been defined as unique constraints or index. The Alter Table SQL command will fail.

The solution to the problem is to remove the TEXT or BLOB column from the index or unique constraint, or set another field as primary key. If you can’t do that, and wanting to place a limit on the TEXT or BLOB column, try to use VARCHAR type and place a limit of length on it. By default, VARCHAR is limited to a maximum of 255 characters and its limit must be specified implicitly within a bracket right after its declaration, i.e VARCHAR(200) will limit it to 200 characters long only.

Sometimes, even though you don’t use TEXT or BLOB related type in your table, the Error 1170 may also appear. It happens in situation such as when you specify VARCHAR column as primary key, but wrongly set its length or characters size. VARCHAR can only accepts up to 256 characters, so anything such as VARCHAR(512) will force MySQL to auto-convert the VARCHAR(512) to a SMALLTEXT datatype, which subsequently fail with error 1170 on key length if the column is used as primary key or unique or non-unique index. To solve this problem, specify a figure less than 256 as the size for VARCHAR field.
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 27, 2012

Updated by mfriedrich on 2012-04-27 08:34:39 +00:00

http://dev.mysql.com/doc/refman/5.1/en/char.html

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 27, 2012

Updated by mfriedrich on 2012-04-27 10:54:43 +00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 90 to 100

Applied in changeset 0b9ef1a.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented May 1, 2012

Updated by mfriedrich on 2012-05-01 16:05:36 +00:00

  • Status changed from Resolved to Assigned
  • Done % changed from 100 to 90

some more unique keys cause failures.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented May 1, 2012

Updated by mfriedrich on 2012-05-01 17:36:09 +00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 90 to 100
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Dec 8, 2014

Updated by mfriedrich on 2014-12-08 14:46:29 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category changed from 24 to IDOUtils
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Jul 7, 2016

Updated by mfriedrich on 2016-07-07 16:33:42 +00:00

  • Relates set to 10061
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
1 participant
You can’t perform that action at this time.