CODEWITHSUNDEEP
mysqlphpmyadmin
Foonation
Foonation

Reputation: 215

Unrecognize data type nvarchar in database

I tried to import MySQL Server data into PHPMyAdmin MySQL database. Unrecognized data type "nvarchar" found. My database collation set to "utf_general_ci".

enter image description here

Upvotes: 3

Views: 2382

Answers (1)

Shadow
Shadow

Reputation: 34294

You do not have to worry about the nvarchar message, that was generate by phpmyadmin, not by MySQL. MySQL does allow nvarchar data type, see MySQL manual on national data character set:

Standard SQL defines NCHAR or NATIONAL CHAR as a way to indicate that a CHAR column should use some predefined character set. MySQL uses utf8 as this predefined character set. For example, these data type declarations are equivalent:

CHAR(10), CHARACTER SET utf8 NATIONAL CHARACTER(10), NCHAR(10)

As are these:

VARCHAR(10), CHARACTER SET utf8 NATIONAL VARCHAR(10), NVARCHAR(10), NCHAR VARCHAR(10), NATIONAL CHARACTER VARYING(10), NATIONAL CHAR VARYING(10)

The real issue is at the bottom of the error message: row size too large. This error message comes from MySQL and that's the one you need to solve.

mysql uses utf8 character set for nvarchar data type. An utf8 character in mysql uses up to 3 bytes. Your config_data field is defined as nvarchar(21844), therefore it requires up to 21844*3+2=65534 bytes.

As the error message says, a row can be up to 65535 bytes long, so you have 1 byte left, but the other fields push the row size above the limit.

What you can do:

  1. Reduce config_data field's length so the overall row length fits into the limit.
  2. Change config_data data type to varchar and use a character set that requires less bytes - just make sure that character set supports all the characters you need.
  3. Change config_data data type to text because only a small portion of a text field's value is actually stored in the row itself. This is actually suggested in the error message itself.

Upvotes: 2

Related Questions