#3049 Export with file option creates invalid commands
When using the export option with the Save as file, option enabled creates some commands that when doing the import of the very same generated file does not import the spanish characters correctly.
This in an example of an export file with the Save as file option enabled:
-- phpMyAdmin SQL Dump
-- version 3.3.4-rc1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 22, 2010 at 04:45 AM
-- Server version: 5.1.46
-- PHP Version: 5.3.2
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES latin1 */;
--
-- Database: `carlos_photoalbums`
--
-- --------------------------------------------------------
--
-- Table structure for table `PhotoAlbums`
--
then the table and the data details and at the end:
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
This is the very same export but WITHOUT the Save as file option enabled
-- phpMyAdmin SQL Dump
-- version 3.3.4-rc1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 22, 2010 at 04:44 AM
-- Server version: 5.1.46
-- PHP Version: 5.3.2
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `carlos_photoalbums`
--
-- --------------------------------------------------------
--
-- Table structure for table `PhotoAlbums`
--
then the table and the data details and at the end there is nothing else.
---------------------------------------------------------------------------------------------------------------------------------------------------------
This behavior is the same using PMA, 332, 333, and the recently released 334.rc1
I use Apache 2.2.11, Php 5.3.2 and MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 294543 $
If I delete those 6 lines (3 at beginning and the 3 at end) the impor works correctly and the spanish characters are saved fine.
Have you defined $cfg['AllowAnywhereRecoding'] in your config.inc.php? If so, to which value?
Please attach a small export of your table containing the structure and a row of data.
Yes it is defined as: $cfg['AllowAnywhereRecoding'] = TRUE;
Example of Table Structure and Data
I don't have a server with mysqlnd to test right now; I tested with mysqli and MySQL client 5.1.47.
Are you using mysqli extension?
I could not reproduce your problem if I left the "character set of the file" in the export and import dialog to it's default value of "utf-8". Is this what you are doing?
I tried again, this time with the same mysqlnd as yours. Got no problem, provided I do not touch the character set choice when exporting and importing. Of course when importing the first time your attached file I had to indicate that the character set is iso-8859-1.
I really don't understand the part about "mysqlnd" it is installed by default with this exec file: mysql-essential-5.1.46-win32.msi.
The PMA home pahe shows these values:
Web server
* Apache/2.2.11 (Win32) PHP/5.3.2
* MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 294543 $
* PHP extension: mysql
BTW I do not have a great MySql experience, I just install it and use it.
As of the problem I hope that I'm clear enough with the following explanation.
1.- I did an Export using the Save as File Option, See attachment: PmaExportOptionsWithFileSelected.png
2.- I did an Import using the file result of step 1. See attachments: PmaImportOptionsWithFileSelected.png and PmaExportOptionsWithFileSelected.sql
3.- Browse with the result of step 2 and there you can see the extrange characters instead of Spanish. See See attachment: PmaBrowseAfterFileSelected.png
4.- I did an Export NOT using the Save as File Option, See attachment: PmaExportOptionsWithFileNotSelected.png.
5.- Then I saved the result of step 4 to a file. See attachment: PmaExportOptionsWithFileNotSelected.sql.
6.- I did an Import using the result of step 1. See attachments: PmaExportOptionsWithFileNotSelected.png and PmaExportOptionsWithFileNotSelected.sql
7.- Browse with the result of step 6 and there you can see the characters in perfect Spanish. See See attachment: PmaBrowseAfterFileNotSelected.png
In all the png files, what I consider to be the relevant parts have been enhanced with red and green rectangles.
Hope I have been clear enough.
Pma Browse After File Not Selected
Pma Browse After File Selected
Pma Export Options With File Not Selected
SQL Pma Export Options With File Not Selected
Pma Export Options With File Selected
SQL Pma Export Options With File Selected
Pma Import Options With File Not Selected
Pma Import Options With File Selected
Correction, instep 7 should read:
PmaImportOptionsWithFileNotSelected.png
and NOT
PmaExportOptionsWithFileNotSelected.png
About mysqlnd: I compile PHP myself with the options I want. Sometimes I use mysqlnd, sometimes not.
About step 1: did you change the "Character set of the file" option to iso-8859-1 or is it there by default?
As I told you in a previous comment, I see "utf-8" there and if I do not change it, your table exports and imports fine. Please try it with "utf-8" and selecting to export in a file. Then we'll find out why you are seeing "iso-8859-1", if it's the case.
I guess you didn't see any of the images,
In all off them, the characters set of the file is set to ISO-8859-1 for all exports and imports.
I have had a lot of problems with the characters set for import and export and found out that when using ISO-8859-1 the characters problem DID NOT OCCUR.
This is why my config file has only the characters set ISO-8859-1 for these operations to avoid problems for me and some users the do work with me.
This problem started as I said with PMA version 3.
As of test with utf-8 I did try it and it worked fine.
The problem I'm addressing remains.
If the only charset available is ISO-8859-1?.
Why is this behavoir occurring when using the export as save file and not when this option is not selected?.
Why are these lines:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES latin1 */;
added when save to file is used and not as a response to the browser?.
Notice that the last line is SET NAMES latin1 and NOT utf-8 as you stated.
I saw the images but by seeing an image it's not possible to deduce if iso-8859-1 was there by default or because you chose it. This is why I asked the question.
These extra lines are generated to help in case a utility like the mysql client is used to execute this file.
Please tell me which settings in your config file have only the character sets ISO-8859-1?
P.S. in the lang directory you will notice that phpMyAdmin now has only UTF-8 language files, so please clarify your question "if the only charset available is iso-8859-1"? I don't understand your point of view.
First, the only paramaters I set with iso-8859-1 are:
$cfg['DefaultCharset'] = 'iso-8859-1';
and
$cfg['AvailableCharsets'] = array(
'iso-8859-1'
);
So that the users can not change anything.
Second I'm not stating any problem with the user's language interface which are the language directory options.
The problem is with those lines added in the export file WHICH ARE causing the problem and,
the specific problem is those lines in the export file.
When you export, the spanish characters are ok
BUT
when you import that very same file is when the spanish characters are handled incorrectly.
This is the problem, It has nothing to do with the user's interface language.
BTW, it does not matter if the characters set for export and import are available as selected or the only option, in the images, it was allways set to iso-8859-1.
With the parameters set with iso-8859-1 in the config file, as you just explained, I can reproduce your problem.
Moreover, when removing just one line
/*!40101 SET NAMES latin1 */;
the import does not produce the strange characters.
Ok, Marc you found the problem now, is there a way to make a temporary patch so those lines are not inserted in the export file?
I need this because as yuo may guess when exporting a large table, how can I get rid of that line?
You wrote that exporting/importing with the default utf-8 works fine in your case, so you just have to remove these lines in your config file and choose utf-8 when exporting and importing:
$cfg['DefaultCharset'] = 'iso-8859-1';
and
$cfg['AvailableCharsets'] = array(
'iso-8859-1'
);
The problem I'm facing is this;
My users are NOT computer savy so I allways try to simplify things for them, in the case of importing exporting files since version 3 came out, it has become a major issue.
One thing is that I know and can allways be careful when I do the imports but when my users have to make an import that is not allways the case.
The file to import can be any of two different sources, a file previously exported by PMA in which case the utf-8 solution does work BUT, when the import is from a file hardcoded in windows by mmeans of any editor the ONLY way to make the import work is using iso-8859-1 characters set.
So now you see, there are TWO different sets of characters to use which is creating the problem with my users, AND THEY DO COMPLAIN.
I think that this problem should be adressed as a BUG that should be solved so that we can allways use the iso-8959-1 for import and export from pma or windows files.
The line /*!40101 SET NAMES latin1 */; SHOULD be changed to the corresponding value for ISO-8859-1 whenever the USER selects that characters set form the available options.
BTW three or four years ago I had the problem when importing data using windows files and you were the one who gave me the solution of doing the import using the iso8859-1 oprion.