Installation Help

Trouble Installing 5.4.1.1 on shared Windows 2008 / IIS 7.5 / Plesk 9.0 host : RESOLVED SQL Error 1064

Permalink March 06, 2011 at 5:43 PM

I think you may find this quite interesting and helpful for many people who are experiencing difficulties installing Concrete5 on a
Shared Windows 2008 / IIS 7.0 / Plesk 9.0 / PHP 5.2.6 environment.

While I am quite new to PHP, I have been been developing on VB.NET and SQL Server for 15+ years (plz don't laugh too hard).

I have a shared hosting account that I decided to try CC5 on (TheHeritage.biz). I had 2 MAJOR problems. The first I caused myself.

When I tried to install, I got a TON of errors, I searched the forums, while there were plenty of PHP configuration related posts, none of them seemed as bad as what I was experiencing. I chased down version numbers on :

------------------- THEM ----- ME
PHP version -------- 5.2.15 ----- 5.2.6
libxml version -------- 2.7.6 ----- 2.6.32
gd version -------- 2.2.1 ----- 2.1.9
MySQL version -------- 5.1.52 ----- 5.0.51a
mysqli -------- 5.1.52 ----- * none *

and read posts on Path_Info and Orig_Path_Info to no avail. Then I remembered, I originally loaded CC5 into my root directory where I was already working on some other options. Before loading, I had renamed index.php to CC5index.php in the concrete5 package. *** and that broke the camels back' *** I renamed my current index.php temporarily and then set CC5's file back to index.php, and 'voila ... I got a BUNCH of green check marks and a screen prompting for DB params'.

Problem 1 solved: Don't rename any files in the CC5 package prior to uploading!

Problem 2 wasn't too far away, because as soon as I filled in the Site/Email/DB params' and clicked install, I got the dreaded:

mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TABLE AreaGroups ( cID INTEGER(10) UNSIGNED NO' at line 8] in EXECUTE(" CREATE TABLE AreaGroupBlockTypes ( cID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, arHandle VARCHAR(255) NOT NULL, gID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, uID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, btID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (cID, arHandle, gID, uID, btID) ) .... "); you really don't wanna see the rest!!!

Me being a SQL Server novice saw this as a challenge...I looked close @ the error and saw something odd ... EXECUTE("....."). I didn't recognize this as standard T-SQL statement, so... off to google I went. I couldn't find any SQL syntax for MySQL that matched that execute statement. I tried numerous variations in MySQL workbench and quickly found out that MySQL had no use for EXECUTE(" <sql statements> "). So, I guessed that maybe EXECUTE("..."); was a function in PHP.

I used grep to find out where in the CC5 package the sql was coming from(my local OS is FedoraCore14). From a terminal:

[someUser@someMachine]$ grep -H -r "Create Table" <rootpackage>/concrete

Lists all file names that contain 'Create Table' recursively from the given path on down. I hunted down the sql files (<rootpackage>/concrete/config/install/*.sql). Then I found the protected function installDB() in install.php under <rootpackage>/concrete/controllers. Quickly got some logging going to watch the SQL file parsing to determine why MySQL didn't like the statements prepared by installDB(). I found the file was read into a string and each statement should have been be split (exploded()) into separate array ELEMENTS:

$sql = file_get_contents($installDirectory . '/install/schema.sql');
$schema = explode("\n\n", $sql);

However, in my environment, this just simply does not work as advertised (I am looking deeper into this later). $schema was always a single ELEMENT array after explode(...), and it had the whole file content in it. Luckily, I recognized that all this is trying to do is split the SQL statements into separate items using a double-line break as a delimiter. So I googled again and found preg_split() in the PHP function library and using:

$schema = preg_split("/(\n|\r\n){2,}/", $sql);

I was able to achieve the desired effect. I did find 1 problem in schema.sql where a statement had an extra linebreak in the middle in one of the last 3 DDL Statements. My logfile told the whole ugly story ... not sure I wanna break it again to reproduce a bad logfile, but I can if you're interested in seeing any of this first hand. These 2 little things had me doubting concrete5 all-together. As it turns out #1 was my fault and #2 seems to be a Windows/IIS issue and CC5 works like a CHAMP!!!

Here's my final modified installDB() function as it exists in install.php at <rootpackage>/concrete/controllers:

****************************************************************************************************************

protected function installDB() {
      $installDirectory = $this->installData['DIR_BASE_CORE'] . '/config';
      if ($_POST['INSTALL_SAMPLE_CONTENT']) {
         $contentfile = $installDirectory . '/install/sample_content.sql';
      } else {
         $contentfile = $installDirectory . '/install/no_sample_content.sql';
      }
      if (!file_exists($contentfile)) {
         throw new Exception(t('Unable to locate database import file.'));
      }
        //Open a logfile so we can see the SQL statements as they are passed to MySQL server from PHP      
        $dblogfile = $installDirectory . '/install/dblog.txt';
        //clear the log file so we start with a clean slate each time we try to install
        file_put_contents($dblogfile, "", LOCK_EX);
        //Read the schema generation SQL statements from the external sql file

Viewing 15 lines of 80 lines. View entire code block.

 
protected function installDB() {
 
      $installDirectory = $this->installData['DIR_BASE_CORE'] . '/config';
      if ($_POST['INSTALL_SAMPLE_CONTENT']) {
         $contentfile = $installDirectory . '/install/sample_content.sql';
      } else {
         $contentfile = $installDirectory . '/install/no_sample_content.sql';
      }
 
      if (!file_exists($contentfile)) {
         throw new Exception(t('Unable to locate database import file.'));
      }
 
        //Open a logfile so we can see the SQL statements as they are passed to MySQL server from PHP      
        $dblogfile = $installDirectory . '/install/dblog.txt';
        //clear the log file so we start with a clean slate each time we try to install
        file_put_contents($dblogfile, "", LOCK_EX);
 
        //Read the schema generation SQL statements from the external sql file
      $sql = file_get_contents($installDirectory . '/install/schema.sql');
 
        //THIS FAILED FOR MY ENVIRONMENT
      //$schema = explode("\n\n", $sql);
        //HERE's MY WORKAROUND
 
        $schema = preg_split("/(\n|\r\n){2,}/", $sql);
        //lets see how big the array is ... ODD ... 1 more than I really expect 76 SQL Statements has 77 elements in the array
        file_put_contents($dblogfile, "tokenz - " . count($schema) . "\n", FILE_APPEND | LOCK_EX);
 
        // I GOT it ... "a| b| c| "    .... will split into 4 elements on the | delimiter - the last element is trivial with length=1 (Space)
        // since this is SQL let's remove the last item in the array so we keep only good TERMINATED SQL STATEMENTS in our array 
        $new_schema = array();
        for($i=0; $i<count($schema)-1; $i++){
            array_push($new_schema,$schema[$i]);
        }
 
        //here we can see the length of the $new_schema array is EXACTLY the number of statements present in the SQL File
        file_put_contents($dblogfile, "Schema Statements - " . count($new_schema) . "\n", FILE_APPEND | LOCK_EX);
 
        //Read the content generation SQL statements from the appropriate external file
      $sql = file_get_contents($contentfile);
      $sql = str_replace('{[CCM:SITE]}', $_POST['SITE'], $sql);
 
        //AGAIN THIS FAILED FOR MY ENVORINMENT
      //$statements = explode("\n\n", $sql);
        //the workaround
        $statements = preg_split("/(\n|\r\n){2,}/", $sql);    
 
        //Log the number of SQL tokens + the trivial end token that we'll remove
        file_put_contents($dblogfile, "tokenz - " . count($statements) . "\n", FILE_APPEND | LOCK_EX);
        //remove the last token from Datafill SQL Statements
        $new_statements = array();
        for($i=0; $i<count($statements)-1; $i++){
            array_push($new_statements,$statements[$i]);
        }
 
        //here we can see the length of the $new_statements array is EXACTLY the number of statements present in the SQL file
        file_put_contents($dblogfile, "Datafill Statements - " . count($new_statements) . "\n", FILE_APPEND | LOCK_EX);
 
        //join the 2 NEW arrays of SQL 
      //Orig source: $statements = array_merge($schema, $statements);
        $statements = array_merge($new_schema, $new_statements);
 
        //generate dblogfile.txt
        //list each SQL command as it will be executed against the DB
        $stcount = 0;
        foreach ($statements as $statement) {
            $stcount++;
            file_put_contents($dblogfile, "$stcount" . " - " . trim($statement) . "\n", FILE_APPEND | LOCK_EX); 
        }
      //end log    
 
        //if you are having problems installing, here is a good point to throw an exception and bail-out to review the dblogfile.txt before  
        //it starts to modify the MySQL database (ie. no DB drop / re-create if we stop here and verify the parsed SQL)
 
        //run the SQL statements #1 at a time ...
        $stcount = 0;
      $db = Loader::db();
      foreach ($statements as $statement) {
            $stcount++;
         if (trim($statement) != "") { 
                $r = $db->execute(trim($statement));  //added trim just in case
            if (!$r) { 
                    //if one statement fails ,,, look at the log file to see where the SQL parsing error is
                    // correct the SQL script, drop & re-create DB, install agian!
               throw new Exception(t('Unable to install database: %s', $db->ErrorMsg()));
            } else {
                    //log each successfully executed statement to the log file
                    file_put_contents($dblogfile, "Success - " . "$stcount" . "\n", FILE_APPEND | LOCK_EX); 
              }
         }
      }
        //Here, the DB has been successfully loaded
        //throw new Exception(t('Database Load Successful -- %s', 'testing'));    //break here if testing schema generation & data loading
        file_put_contents($dblogfile, "Database Loaded Successfully  :)" . "\n", FILE_APPEND | LOCK_EX); //fianlize dblog.txt
   }

****************************************************************************************************************

You NEED TO add dblogfile.txt (empty is ok) to <rootpackage>/concrete/config/install (beside the *.sql files) if you want to try to install with this.
**AND**
DOUBLE check each SQL file (schema.sql, sample_content.sql, & no_sample_content.sql) to make sure there are no linebreaks WITHIN a statement
**AND**
EXACTLY 1 linebreak between EACH SQL statement in all SQL files.

If the install errors-out, review the dblogfile.txt. It will indicate what part of the SQL files that the backend MySQL server was unable to parse and execute. You will probably have to drop and re-create the database to attempt a subsequent install after addressing the problem SQL file. I now have a wonderful running instance of CC5 on my domain, and I am exploring from here. Happy to help anyone who thinks they may be experiencing the same problem.

Hope this helps SOMEONE, sorry so long.
Any Questions, feel free to @ email @ me robbie at xenospark dot net.

Happy coding,
Xenospark

ATTACH: I have attached my modified install.php, dblogfile.txt, schema.sql, sample_content.sql, and no_sample_content.sql in a zip for anyone who thinks they wanna try this on their own.

Hope I haven't violated any forum rules or stepped on any toes. You know I don't RTMFM!!!

1 Attachment

MattWaters replied on Mar 7, 2011 at 1:44 pm Permalink Reply

Thanks for writing up the solutions you found. Have you considered writing a How-To?

http://www.concrete5.org/documentation/how-tos/...

There's a few other specific install walkthroughs in there but I don't think Windows 2008 has been covered yet. You'll also get 1,000 karma points when it's approved.

XenoSpark replied on Mar 11, 2011 at 7:27 am Permalink Reply

Hey Matt,

I will definitely compose a new (or add to an existing) 'How To:' for installs on Windows 2008/IIS 7.0 upon completing my install. I am still experiencing some minor problems, but they are giving me a great opportunity to explore the source in depth and better familiarize myself with the underlying architecture. I am documenting my complete install experience and I'll make it available when I'm up and running stable.

Lucky for me, I'm not under the gun to have a functional solution in place until April. So, that gives me some time to 'tinker'.

Take care,

Xen-

Forums

Installation Help

Trouble Installing 5.4.1.1 on shared Windows 2008 / IIS 7.5 / Plesk 9.0 host : RESOLVED SQL Error 1064

Code

Post Reply

Delete Post

Mark Post as Spam

Destroy Spammer

Sign In?