Help! Reinstall and Upgrade MySQL and PHP not talking

[MacUSA]

Last week a hacker took the IIs 6 server down.  It was running PHP 4 and an early version of MySQL.  There's only one website utilizing PHP and MySQL on this server.  I was forced to erased the HD and reinstall everything.  Windows 2000 Server, PHP 5 and the current version of MySQL.  PHP is working.  MySQL is working.  However they are not connecting to each other.  Going to the website I see SQL commands at the top of each page.

 

We know nothing about MySQL.  The original developer is out of business.  After spending many hours goofing around with this I'm mostly certain that it's just a configuration issue somewhere between MySQL and PHP.  Or perhaps the upgraded PHP and MySQL is not compatible with the old MySQL database.  I don't know what I've missed.

 

Be happy to pay any developer that can get this site back up ASAP.

 

Dan McFalls  (708) 233-6200  ext. 101.

[cooldude832]

what sort of sql errors are showing

[MacUSA]

get_row("Select * from counter Where id=6"); $count = $counter->counter+1; $db->query("Update counter set counter=$count Where id=6"); ?>

[cooldude832]

so the actual sql is being phrased out as text not as programing input? that is very strange can i see some relative code

[MacUSA]

Copied from the first few lines of the index page.

 

<?

include_once "ez_sql.php";

$counter = $db->get_row("Select * from counter Where id=6");

 

$count = $counter->counter+1;

 

$db->query("Update counter set counter=$count Where id=6");

 

 

?>

 

[cooldude832]

can i see ez_sql.php (remove any personal data from it)

[MacUSA]

The attached TXT doc is before I started experimenting.

 

Going to the page on the server yields:

 

SQL/DB Error -- [

Error establishing a database connection!

Are you sure you have the correct user/password?

Are you sure that you have typed the correct hostname?

Are you sure that the database server is running?

]

SQL/DB Error -- [

Error selecting database handblessings!

Are you sure it exists?

Are you sure there is a valid database connection?

 

I'm certain there is a connection and I have the correct login info.  I was able to test it with the MySQL GUI tools.

 

Thanks!

 

 

 

[attachment deleted by admin]

[cooldude832]

try using an absolute path or including a port number

[MacUSA]

Tried substituting the IP address and port 3306 for the localhost and using the full path to the database.  Same results except the ez_sql.php page now displays:

 

SQL/DB Error -- [

Error establishing a database connection!

Are you sure you have the correct user/password?

Are you sure that you have typed the correct hostname?

Are you sure that the database server is running?

]

SQL/DB Error -- [

Error selecting database C:\mysql\data\handblessings!

Are you sure it exists?

Are you sure there is a valid database connection?

]

PHP Notice: Undefined property: db::$last_query in C:\www\members\handblessings\www\EZ_SQL.PHP on line 90 PHP Notice: Undefined property: db::$last_query in C:\www\members\handblessings\www\EZ_SQL.PHP on line 90

 

 

 

[cooldude832]

I'm unfamilar with this mysql script you are using, and I don't really see a need for it, try looking in the third party script help for help on thiis script.

[MacUSA]

Thanks for your assistance.  Unfortunately I know nothing about MySQL and VERY LITTLE PHP.  I expected this to just work like it did before I had to remake the server.  I copied all the files before the reformat and put them all back except for installing the new PHP and MySQL.

 

If I can't find someone to fix this I may just convert the SQL database to Access and redo the site in ColdFusion -- a language I can work with.  Just a lot of unnecessary hours.

 

Thanks again.

 

Dan

[wildteen88]

Just to make sure its not the script a fault, run the following:

<?php

echo "Detecting Mysql: ";

if(extension_loaded('mysql'))
{
    echo "MySQL Extension loaded<br />\n<br />\n";
}
else
{
    die('MySQL Extension no loaded wont be able to continue');
}

echo "Defining MySQL credentials...<br />\n";

$db_host = 'localhost';
// define your username, password and database here
$db_user = 'USERNAME';
$db_pass = 'PASSWORD';
$db_base = 'DATABASE';

echo "Connecting to mysql...<br />\n";

$conn = @mysql_connect($db_host, $db_user, $db_pass) or die('Connection failed!<br />' . mysql_error());
mysql_select_db($db_base) or die('Unabble to select database<br />' . mysql_error());

echo "Connection successful!<br /><br />\nDefining SQL query: ";

$query = 'SELECT * FROM counter_tbl WHERE id=1';

echo "($query)<br />\nRunning query...<br />\n";

$result = mysql_query($query) or die("SQL Query Error:<br />\n" . mysql_error());

echo "Query ran successfully<br />\n";

if(mysql_num_rows($result) == 1)
{
    echo "Getting counter column<br />\n";

    $row = mysql_fetch_assoc($result);

    $count = $row['counter'] + 1;

    echo "Counter retrived, incremented by 1<br />\nDefining query to update counter...<br />\n";

    $query = "UPDATE counter_tbl SET counter='$count' WHERE id=1";

    echo "Running query ($query)<br />\n";

    mysql_query($query) or die("SQL Query Error:<br />\n" . mysql_error());

    echo "Query ran successfully<br />\n";
}

?>

 

Post any output you get here. From the output you get you should be able to debug why your script isn't working.

 

[MacUSA]

I'm assuming you wanted me to start a new php test document.  Here's the results:

 

PHP Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in C:\www\members\handblessings\www\testr.php on line 3

 

[wildteen88]

Umm... Intresting. This the secound time this has happened.

 

Download the attached file instead, rename to test.php

 

[attachment deleted by admin]

[MacUSA]

Detecting Mysql: MySQL Extension loaded

 

Defining MySQL credentials...

Connecting to mysql...

Connection failed!

Access denied for user 'USERNAME'@'localhost' (using password: YES)

 

Is there somewhere in IIs or the PHP application that I need to define the "root" user and password that I assigned when installing MySQL?

 

Thanks.

[wildteen88]

You define it in the PHP script I just provided. Just edit these variables:

$db_user = 'USERNAME'; // change USERNAME to username you want to login to MySQL as
$db_pass = 'PASSWORD'; // change PASSWORD to the password for the user name you supplied 
                                   // above (delete PASSWORD if no password is set for specified user)
$db_base = 'DATABASE';  // the database that contains the counter table

 

Sorry I forgot to mention that earlier.

[MacUSA]

First I would like to thank all that have assisted.  I keep getting a bit closer with your help.

 

Next.  As I mentioned previously, i know nothing about MySQL.  I just realized that I was supposed to add the database user to the Adminstrator application.  After doing that I now get:

 

Detecting Mysql: MySQL Extension loaded

 

Defining MySQL credentials...

Connecting to mysql...

Connection successful!

 

Defining SQL query: (SELECT * FROM counter_tbl WHERE id=1)

Running query...

SQL Query Error:

Table 'handblessings.counter_tbl' doesn't exist

 

The path to the database is (and was previously):

C:\mysql\data\handblessings

 

Within that directory there are a bunch of files (tables i think) and another direcroty named data.  This is where the counter table resides.  I tried copying the files in the subfolder to the handblessings folder and still get the same message above.  I reverted everything back.

 

Dan

[wildteen88]

You cannot modify/change the location  of the files with in C:\mysql\data

 

If you move/change files within that directory you have a chance of corrupting your database. You should instead run a full database backup. This backup should of been performed before you uninstalled the previous version of MySQL. Unfortunately as you are new to MySQL you probably didn't realise this and I guess you thought you could treat the files/folders in C:\mysql\data the same you can with MS Access files. This is not the case.

 

[MacUSA]

I still have the original copies I backed up from the server before reformatting.  Although they weren't saved in the method you suggest this doesn't appear to be the problem.

 

The database is in a subdirectory named data of the main database directory.  eg.  MySQL/Data/handlessing/data/  I can see the database named COUNTER when I go to the Backup option in the Admin app and create a new backup project.  So it is there but PHP thinks not.

 

Dan

[wildteen88]

You can't do that. You cannot use the files generated by mysql in mysql/data for backups. This is not how you do database backups with MySQL.

 

try copying the files from MySQL/Data/handlessing/data to MySQL/Data/handlessing instead. I doubt it will work though. You may have corrupted your database.

 

Last time I backed up the files in MySQL/Data (when I first go started with MySQL) I corrupted my database and had to rebuild my database from scratch.

[MacUSA]

Let me clarify.  I copied all files off the drive (while SQL was disabled).  Reformatted and reinstalled everything.  Installed newer versions of MySQL and PHP that were on there previously.  Copied all data back in the same position as previously.  This site ran 5 years with the data directory within the database directory within the data directory of the MySQL directory.

 

The database does not appear to be damaged.  I’m reviewing its contents in the MySQL Query browser.

 

I feel strongly that this is a configuration issue.  I just don’t know what I’m doing.  I am willing to pay somebody to hop on using Timbuktu or logmein.com to fix this ASAP.

 

Thanks,

 

Dan

 

[wildteen88]

look into your my.ini file in the mysql folder. Search for:

#Path to the database root
datadir="C:/MySQL/Data/"

 

Append handlessing to the datadir:

#Path to the database root
datadir="C:/MySQL/Data/handlessing/data/"

 

Save ini and restart MySQL.

[MacUSA]

1)  There are no .ini files in the root level of the MySQL directory.

2)  Once I get this working i would like to try getting the other unrelated databases going that are in the MySQL/data directory.  Making the change you suggested would prevent those from working.

3)  If the data connections work similar to what I use in ColdFusion and MS Access, making the directory change in the ini file wouldn't make a difference.  The data connection and the file locations are unrelated once setup.

4)  I'm wondering (again i know nothing about MySQL) were the databases supposed to undergo some conversion process going from version 4 to 5?

 

Thanks again,

 

Dan

[wildteen88]

Why cant you just move all files in C:/MySQL/Data/handlessing/data/ to C:/MySQL/Data/handlessing/. Perhaps then mysql can read your database.

 

Because you are using the wrong backup method I can not recommend you what to do.

 

The correct way was to create a mysql dump of the databases. That way when you go to install a new version of mysql you just have to import the dump file(s) and MySQL will recreate all the databases/tables as they where.

 

You would not have any of the issues you're having now.

 

Do you have a lot of data backed up for your databases?

[MacUSA]

I already did try this.  it didn't work.