Since we switched to a new server with a new version of MySql (v5.0), the select queries on varchar fields return, with the value filled out with spaces to the length of the varchar field. This broke some of my pythonscripts because that was unexpected. It was easily fixed by an .rstrip() on the field before usage... But I'm wondering what causes this?
Is this a feature? ;)
Am I missing an option, that turns this behaviour on by default?
Is this a bug in MySQL for Python?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You put me on the right track. I could trace the problem to a dbf to mysql conversion program that inserts the values complete with trailing spaces. Which was no problem with the older mysql (because they where stripped as you pointed out), but after the upgrade this became evident...
Thank you!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It might be you. Old MySQL (before 5.0.3) stripped trailing space from varchars, which was dumb. Maybe you're inserting values with trailing spaces, and new MySQL is (correctly) returning the exact value you stored.
"VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values."
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Since we switched to a new server with a new version of MySql (v5.0), the select queries on varchar fields return, with the value filled out with spaces to the length of the varchar field. This broke some of my pythonscripts because that was unexpected. It was easily fixed by an .rstrip() on the field before usage... But I'm wondering what causes this?
You put me on the right track. I could trace the problem to a dbf to mysql conversion program that inserts the values complete with trailing spaces. Which was no problem with the older mysql (because they where stripped as you pointed out), but after the upgrade this became evident...
Thank you!
It might be you. Old MySQL (before 5.0.3) stripped trailing space from varchars, which was dumb. Maybe you're inserting values with trailing spaces, and new MySQL is (correctly) returning the exact value you stored.
From http://dev.mysql.com/doc/refman/5.0/en/char.html
"VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values."