[sloppyboy]

Hi Peter,

Here is the begging of my dump file:

Code:

/*
SQLyog Enterprise – MySQL GUI v6.14
MySQL – 5.0.45-community-log : Database – tbrothco_redcart
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*Table structure for table `album_type` */

That stuff looks to be commented out. Were you not expecting this?

To answer your question on the exact server version, I'm assuming you're referring to MySQL. Here are the versions:

– The FROM DB was MySQL 5.0.45

– The TO DB was 4.1.22

Also, please note that after making the following changes, things are displaying correctly in the front end application:

– I changed my AMFPHP PHP installation to use ISO-8859-1

– Altered the problem columns in the database to use UTF8

– In my PHP script connection file to the database, I used this statement – SET NAMES 'utf8';

Do you see any problem to doing it this way?

Tommy

[sloppyboy]

Hi Peter,

Here is my table definition from the original dump file, notice how it was latin1:

Code:

/*Table structure for table `tax_state` */

CREATE TABLE `tax_state` (
`TAX_STATE_ID` int(11) NOT NULL auto_increment,
`TAX_STATE` varchar(80) NOT NULL,
`DISPLAY_NAME` varchar(80) NOT NULL,
`COUNTRY` varchar(10) NOT NULL,
PRIMARY KEY (`TAX_STATE_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=373 DEFAULT CHARSET=latin1;

Here is a sample insert from the dump that has the accented chars:

Code:

insert into `tax_state`(`TAX_STATE_ID`,`TAX_STATE`,`DISPLAY_NAME`,`COUNTRY`) values ('241','Elias Pina','Elías Piña','DO');

I deleted all of the data from this table on the new database, then converted the DISPLAY_NAME column to utf8 in the new database, then used SQLYog database synchronize to sync the old table (latin1) with the new (utf8). After this things seem to look like they should in the db. But didn't you say that SQLYog should not have let me do this?

Here is the output from show vars in the new db after the alter table on tax_state:

Code:

SHOW VARiABLES like '%character%';

“character_set_client” “utf8”
“character_set_connection” “utf8”
“character_set_database” “latin1”
“character_set_results” “utf8”
“character_set_server” “latin1”
“character_set_system” “utf8”
“character_sets_dir” “/usr/share/mysql/charsets/”

Not to confuse you more, and I realize that this is beyond the scope of your product, but after I got everything looking fine in the database, then the accented chars started showing garbage in my front end application (An Adobe Flex2 application). I'm using AMFPHP for Flex(Flash) to communicate to PHP, then PHP is communicating to MySQL. I know that there is a charset setting in the AMFPHP gateway php file for php and also for mysql. Should I be setting the charset for mysql, php…etc to UTF8 or ISO-8859-1 or ?

With appreciation,

Tommy

[sloppyboy]

Hi Peter,

Thank you, that post was very informative. However, what's interesting is that I used the same SQLYog 6.14 to do the dump and the import. This morning I used the database synchronization tool to sync the table from the previous server to the current one and it seemed to do the job fixing all of the erroneous data.

But now I'm confused even more, because it seems like what you're saying is that the tables should not be in latin1, but should be in UTF8, otherwise garbage will occur. If this is the case, then shouldn't there still be garbage in the table after synchronizing data from the previous database to the current database since the column shows latin1 as the charset?

Do you recommend changing all tables to use UTF8?

Tommy

[sloppyboy]

I also wanted to add other info that might help:

– I'm on MySQL 4.1.22

– Using SQLYog v6.14

– I created a database dump and restored this database on a different server using SQLYog (Maybe this was the problem and a possible bug??)

– The FROM DB was MySQL 5.0.45

– The TO DB was 4.1.22

Any help is very much appreciated.

Thank you,

Tommy

[sloppyboy]

peterlaursen wrote on May 11 2007, 03:14 AM:

can you access the tunnelling script from a browser and execute simple PHP scripts form the same folder.

try small scripts containing only one lineand the PHp tags of course) like

Code:

echo 'something';

and

Code:

phpinfo();

Yep – running php scripts works just fine. I still have no idea what might be causing this issue but I keep running into it on various servers. Any other ideas? Thanks,

Tommy

[sloppyboy]

Hello,

I'm still having this issue on some servers. Anybody know of a fix or work around? I can access the file through a browser but not through SQLYog. Here's the latest one:

Code:

HTTP/1.1 403 Forbidden
Date: Fri, 11 May 2007 01:47:36 GMT
Server: WebServerX
Keep-Alive: timeout=15, max=98
Connection: Keep-Alive
Transfer-Encoding: chunked
Content-Type: text/html; charset=iso-8859-1

Forbidden

You don't have permission to access /RedCart/db/SQLyogTunnel.php
on this server.

Additionally, a 404 Not Found
error was encountered while trying to use an ErrorDocument to handle the request.

---

Apache/1.3.37 Server at http://www.loganphoto.com Port 80

Thanks in advance,

Tommy

[sloppyboy]

peterlaursen wrote on Mar 4 2007, 11:32 PM:

You need RELOAD privileges for 'flush' operations.

Hust uncheck the 'flush' options.

Did that help.

BTW: you should upgrade SQLyog

That seemed to work – thanks! Is the upgrade from v5.18 worth it? What will I gain?

Tommy

[sloppyboy]

I just heard back from Site 5 and they aren't going to allow me to have the RELOAD privilege. So back to the question – is there a way to be able to run the backup scheduler successfully without having the RELOAD privilege?

By the way I'm using SQLYog Enterprise v5.18.

Thanks in advance,

Tommy

[sloppyboy]

Thanks for your reply. I think for now I'm going to write a script that parses through a directory of .sql files generated by SQLYog and throws each individual statement into an array to be executed on the db in the right order.

This way I'll be able to generate multiple .sql files from SQLYog, put them in a single directory, and the script will be able to pick them up in sequential order, parse through them, and apply all sql statements in the right order. This is going to be useful for dropping hotfix db schema updates for an application where you would have multiple .sql files based on bug fixes or schema changes in between application releases.

Tommy

[Author]

Posts