3

I found out that when I query one of my tables it is case sensitive, so I tried to change the collation (I'm using Workbench in windows). I 

    right clicked on the table -> alter table -> collation 
-> changed from utf8mb4_default to utf8mb4_general_ci

But it didn't work and the queries are still case sensitive. and when I 

right click on the table -> alter table -> collation

is utf8mb4_default

and when I change it to utf8mb4_general_ci again, and apply the change, it says no changes detected!

The column type is VARBINARY, I tried this:

MySQL case insensitive search on varbinary field?

but it takes a lot of time, it is not acceptable.

This is t create statement:

CREATE TABLE `page` (
  `page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `page_namespace` int(11) NOT NULL DEFAULT '0',
  `page_title` varbinary(255) NOT NULL DEFAULT '',
  `page_restrictions` tinyblob NOT NULL,
  `page_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
  `page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `page_is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `page_random` double unsigned NOT NULL DEFAULT '0',
  `page_touched` varbinary(14) NOT NULL DEFAULT '',
  `page_links_updated` varbinary(14) DEFAULT NULL,
  `page_latest` int(8) unsigned NOT NULL DEFAULT '0',
  `page_len` int(8) unsigned NOT NULL DEFAULT '0',
  `page_content_model` varbinary(32) DEFAULT NULL,      
  PRIMARY KEY (`page_id`),
  UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
  KEY `page_random` (`page_random`),
  KEY `page_len` (`page_len`),
  KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`),
  KEY `idx_page_page_is_new` (`page_is_new`),
  KEY `idx_page_page_title_is_new` (`page_title`,`page_is_new`)
) ENGINE=InnoDB AUTO_INCREMENT=44062999 DEFAULT CHARSET=utf8mb4;

Any other suggestions?

Community
  • 1
  • 1
Andi Keikha
  • 1,246
  • 2
  • 16
  • 37
  • Table default does not change columns. _ci means case_insensitive. `VARBINARY` is effectively case sensitive, but messes up other things. Please provide `SHOW CREATE TABLE`. – Rick James Apr 29 '15 at 05:03

2 Answers2

2

Looks like you have the following options:

  1. Convert your binary column to a none binary text column, using a temp column because binary columns cannot be case in-sensitive
  2. Use the Convert function as the link you mentioned
  3. Use the Lower or Upper methods

If you really want the column be always case in-sensitive, I'd say go for option 1.

Arash.m.h
  • 320
  • 2
  • 11
  • I tried that, I got this error: Error 1071: Specified key was too long; max key length is 767 bytes. I tried to change it to varchar(1000). I enabled innodb_large_prefix, using the help from this link: http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/. It didn't work. It still gives me error. – Andi Keikha Apr 28 '15 at 15:07
  • Do you get the same error when using TEXT or LONGTEXT for the destination field? – Arash.m.h Apr 28 '15 at 15:43
  • I get this new error: BLOB/TEXT column 'page_title' can't have a default value – Andi Keikha Apr 28 '15 at 15:45
  • So I need to know on which step you actually get the error 1- adding the temp column, 2- updating the temp column with the varbinary column, 3- Dropping the varbinary column, 4- Renaming the temp column to the desired name? – Arash.m.h Apr 28 '15 at 15:49
0

In mysql there is a collation for each column in addition to the overall collation of the table. You will need to change the collation for each individual column.

(I believe the overall table collation determines the default collation if you create a new column, but don't quote me on that.)

Peter Bowers
  • 3,063
  • 1
  • 10
  • 18
  • In workbench the collation for each column is inactive and said "Table default", which shows I cannot change it. – Andi Keikha Apr 28 '15 at 14:07
  • I also noticed that the column datatype is varbinary, I tried to change it to varchar, but it cannot happen. This is the wikipedia mysql dump that I am using, table page. – Andi Keikha Apr 28 '15 at 14:14
  • Sorry, I'm not familiar with workbench (I usually use phpMyAdmin). Any possibility your mysql user doesn't have privileges to change column definitions within mysql? – Peter Bowers Apr 28 '15 at 14:18
  • I tried it with the shell and the error is that utf8_general_ci is not a valid collation for character set binary. – Andi Keikha Apr 28 '15 at 14:37
  • Yeah, collation doesn't really make sense for binary. You need to get it changed to varchar or char or text or whatever. – Peter Bowers Apr 28 '15 at 18:17