1

I've exported my database from MySQL Workbench locally and I've just tried to run it on my live server and everything inserts successfully.

Some of the fields are being truncated whenever there is a special character, so 'This is a £ test' becomes 'This is a '.

I'm assuming this is an encoding issue however both the database on live and local have UTF8_unicode_CI as the collation.

royhowie
  • 11,075
  • 14
  • 50
  • 67
Scott Harrison
  • 393
  • 4
  • 17
  • Its only collation issue, Check the table level encoding, Pretty sure you will find the issue or try to refer http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8 – prashant thakre May 08 '15 at 16:21
  • _Not_ "collation issue"; "character set" issue. – Rick James May 08 '15 at 23:14
  • Both locally and in the live environment my connection is UTF8_unicode_ci, my schema, table and all columns are UTF8_unicode_ci collation and UT8 character set. Anyd ideas? – Scott Harrison May 11 '15 at 08:23

1 Answers1

2

Symptom of Truncation: When SELECTing the text, it is not all there; in particular it is truncated before the first non-english character.

How you got in the mess:

  • The client's bytes to be INSERTed into the table were encoded as latin1, and
  • The charset for the connection was utf8 (eg, via SET NAMES utf8), and
  • The table column was declared CHARACTER SET utf8

How to fix the text and the table:

  • The text is not recoverable.
  • The column is already utf8, so no change needed there.

How to fix the code:

  • If you continue to have latin1-encoded text, then change the charset for the connection to latin1.
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Ideally I would like everything in UTF8_unicode_ci if possible. When I copy and paste the data manually from my local MySQL to PHPMyAdmin on my live environment the data is kept so why does it only seem to truncate fields when I import data using a SQL dump file? – Scott Harrison May 11 '15 at 08:48
  • 1
    First a terminology clarification: `utf8_unicode_ci` is a "collation". The text is encoded as `utf8` or `latin1` (or many other "character set" choices). That collation is associated only with `utf8`. However, it _seems_ that your text is really in `latin1` encoding. I am unclear on where the text comes from; please elaborate. – Rick James May 11 '15 at 14:26
  • Thanks for the help so far. The text has been added through an online interface using tinyMCE but I'm not sure how this can cause the problem with the database truncating the data when it works fine when it gets added to the database locally originally? – Scott Harrison May 12 '15 at 08:16
  • 1
    Truncation happens when it is being inserted. There is no "error", just a "warning". Alas the default MySQL settings are not aggressive in reporting "warnings". Probably the fix for the problem is to change the charset setting in the _connection_ to latin1. (That will not recover the text lost to truncation.) – Rick James May 12 '15 at 15:01
  • Okay thanks, how would I change the connection charset when using PHPMyAdmin? – Scott Harrison May 13 '15 at 08:53