Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I Have a table when i select from the table i got the invalid data as

mysql> select budgetID,StartDate,modifiedBy from Table_name order by budgetID desc limit 10;
+----------+---------------------+----------------------+
| budgetID |    StartDate        | modifiedBy           |
+----------+---------------------+----------------------+
|      364 | $091-24-68 27:49:32 | -9187343239835811836 |
|      363 | NULL                | -9187343239835811840 |
|      362 | $091-24-69 14:21:19 | -9187343239835811840 |
|      361 | $091-24-69 14:21:19 | -9187343239835811840 |
|      360 | $091-24-69 14:21:19 | -9187343239835811840 |
|      359 | $091-24-69 14:21:19 | -9187343239835811840 |
|      358 | �301-32-83 19:54:95 | -9187343239835811840 |
|      357 | �301-32-83 19:54:95 | -9187343239835811840 |
|      356 | $091-24-69 05:61:82 | -9187343239835811840 |
|      355 | �301-32-83 10:95:58 | -9187343239835811840 |
+----------+---------------------+----------------------+

i have inserted the values for budgetID=365 as

budgetID                351
StartDate               2012-02-01 00:00:00
modifiedBy              1055

but while selecting the result i am getting invalid output.

where budgetID is Primay key bigint and StartDate is datetime and modifiedBy bigint.

AND

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)


mysql> show variables like "%colla%";
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

What may the issue behind this.?

when I created a table with the same structure as this table and inserted the record then the select query is showing result OK.

share|improve this question
    
Your query shows "TSCStartDate" but the metadata shows "StartDate". Is this the problem? –  gbn Apr 24 '12 at 6:52
    
that is same i just edited the column name for some purpose but forgot to edit here. –  Abdul Manaf Apr 24 '12 at 7:13
    
check both tables in the "information_schema.tables" table or with "show create table xxx" and see the differences between both tables –  golimar Apr 24 '12 at 8:43
    
@golimar: there are no differences. –  Abdul Manaf Apr 24 '12 at 8:54
    
I assume you're running these queries from the terminal. It may be possible that the data and the database are fine, but your terminal isn't reading and displaying the characters properly. –  Matt Fenwick Apr 24 '12 at 14:30

1 Answer 1

up vote 1 down vote accepted

Based on your default database encoding, character_set_database=latin1, you should force your client to use latin1. Your client is currently in utf8 and the database appears to be in latin1. This assumes that your table is also using latin1.

To make sure your data is stored correctly in MySQL, you can force the mysql client to use latin1.

1 Force the Client to use latin1

mysql -u root -p --default-character-set=latin1 nameofthedatabase

Then run your SELECT statement to verify the character formatting

select budgetID,StartDate,modifiedBy from Table_name order by budgetID desc limit 10

2 Backup the database using latin1

You can also dump the database using the same option

mysqldump -u root -p --default-character-set=latin1 nameofthedatabase > nameofthedatabase.sql

Then you could view the dump file nameofthedatabase.sql using a standard text editor like textpad or gedit to see if the characters are encoded correctly.

Collation is a set of rules that determine how rows are sorted and how they are compared. Collation does not affect the encoding which is the problem you are currently having.

If both options still give you weird symbols, the import file may not have been correctly formatted in the first place.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.