Duplicated site not working from new db but does from old db

[AtTheEnd]

Hi,

There is a site running on a Windows 2000 server with mysql 4.0. It's been copied to a new server running Windows 2003 and mysql 4.1. The database has been copied in its entirety. When I modify the db config file to connect to the old Win 2000 site db, the new site runs fine. But when I change the config file to use the new db, the site doesn't work, I get a blank page and no source code. Any suggestions as to what might be happening?

Thanks in advance.

[Atli]

Hi.

What language is your site written in?
You may have to turn on error messages to see what is going on.

Does the new server allow remote connections?
You need to make sure that the server itself allows remote access to port 3306 (unless you changed the MySQL port). If you server has a firewall, it is likely to block this by default.
And make sure the user you are using to connect to MySQL is allowed to connect remotely.

[AtTheEnd]

The site is in php. I turned error messages on and errors are logged, and I checked the log files and there are no messages. No errors display. I created a user with host set to Any Host. I already test the connection... if the db connection parameters are wrong I get an error message. So I know that site is connecting to the database. The db and site files are on the same server.

[Atli]

Ok.

Have you tried running some of the queries your site would run manually?
That would at least tell you if the database is working properly.

Could you show us the code that is connecting to the database?
If the database is working, and you can connect to it, then the problem could be with PHP rather than MySQL.

[AtTheEnd]

Well I created a page on the new site and wrote some code to pull a record from the new database and it worked fine.

How can the problem be PHP if when I change the db connection parameters in php file to connect to the old database at xxx.yy.xx.zz the site shows up. But when i change the xxx.yy.xx.zz to say "localhost" the site disappears. I've already used mysqldump to export and import the database to the new server.

I've been at this for months trying to figure out why this site doesn't work. Seems to be a new problem every week. Now I've been able to get it to work with the remote database, but not the local database.

Much appreciated.

[Atli]

Are there differences between the PHP versions, or the configuration, or anything else you can think of?

There are always subtle differences between versions, some of which have been known to cause problems when migrating sites from servers.
For example, sites that depended on the register_globals directive usually didn't work when moved from PHP4 to PHP5. PHP5 disabled that feature by default, while PHP4 had it enabled. (And they plan to remove that altogether in PHP6.)

There are a lot of configuration settings that can cause your site to act strangely, especially if you depend on features that are disabled by default. Like the short-tags syntax (<? .. ? rather than <?php .. ?>).

Given that you are just receiving a blank page, I would have thought you were receiving an error that is being suppressed. PHP usually doesn't fail without giving you a reason, unless the code is structure to do so.

Try adding this to the top of your page, before anything else:

error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', true);

If there are any errors, that should show them.
Not sure how you enabled the errors before, but this method should always work.

[AtTheEnd]

I think I may have found the problem. The old site running mysql 4.0 and phpmyadmin 2.6.0-pl1 will accept a query such as: "SELECT '*' FROM table WHERE 1" and return the entire table. But the new server running mysql 4.1 and phpmyadmin 2.6.0-pl3 returns an empty record set for "SELECT '*' FROM table WHERE 1".

It thinks the single quotes are around the asterisk is a field not just * to select all the fields.

[Atli]

Does that really work in MySQL4?

In MySQL5, any value that is quoted (with a few exceptions) are considered strings.
So the query you posted should print an asterisk for every row in the table.

Btw, the WHERE clause in that query is completely redundant.

[AtTheEnd]

I tested the query in the old database and it returns all the rows in the specified table. I know WHERE is redundant but the site was coded in such a weird way, and thats how the query is built in the code. so i need to test as it is. The new server returns an empty result with that query. I have no idea why mysql 4.0.20 and phpmyadmin 2.6.0-pl1 will accept a '*' as a * in the query. The single quotes are supposed to be the angled ones, at least thats what the query looks like and thats what I tested on both servers.

[Atli]

Ahh ok. That's a weird way to code.
I tested the `*` thing on 5.0 and 4.1 and both rejected it saying

ERROR 1054 (42S22): Unknown column '*' in 'field list'

Was going to test 4.0 to but it doesn't seem to be available for download anymore.

In any case.
I guess your going to have to either go through the code and rewrite the incorrect queries, or downgrade to 4.0 again :/

[AtTheEnd]

Yup. 4.0 is not available. So I had to debug the entire site to find where the `*` were used in queries and edit the code to remove the ` marks. And hazaah! Thank you very much for all your help. I don't think I would have spent the time to check the queries for compatibility. The site is up and running now.

Thanks again!

[Atli]

Glad you got it working.

Now you just need to upgrade to MySQL 5.1 and your all set for the next few years :]

[r035198x]

Just on that where. It might be useful in circumstances where you want the user to be able to specify a where clause. In that case the user only has to start with " and columnName = 'aValue' and " e.t.c without having to worry about whether to include the "where" or not as it's already been included.