Refactor PHP script to do mysql update between 2 database servers

Question

I'm trying to finish a script that connects to two databases, each on a different server, and preforms an update. Basically, the tables being selected from and inserted to are identical: I did a dump/import the other day. The script needs to keep my local table up to date from the remote once since there will be daily records inserted into the remote one and I need to keep it up to date locally.

The key here is that I'm determining the new rows on the remote server by looking at the Auto-incremented Primary key that the tables share, SESSIONID . I'm trying to get my loop below to say, if the id exists in remote server and not local, then insert those records in local server.

I run the below script in powershell by typing php 'filename', and I get both of my successful connection messages, but then it hangs. After about 10 minutes, it had a memory error so I added ini_set('memory_limit', '256M');. After this it would still hang for about 10 minutes, and then say that MySQL server has gone away and result header couldn't be found, both errors occurring on the line where I check to see if the $rowCount failed.

Note: Replication and large dump/import/table recreations are not an option for us in this situation. We have several similar scripts to this running and we want to keep the same process here. I'm merely looking to resolve these errors or have someone give me a more efficient way of coding this script, perhaps using a max id or something along those lines.

Here's the script:

      ini_set('memory_limit', '256M');

      // Create connection
      $conn = new mysqli($servername, $username, $password);
      $conn2 = new mysqli($servername2, $username2, $password2);

      // Check connection
      if ($conn->connect_error) {
          die("Connection failed: " . $conn->connect_error);
      }
      echo "Connected successfully";

      // Check connection2
      if ($conn2->connect_error) {
          die("Connection failed: " . $conn2->connect_error);
      }
      echo "Connected successfully";


    //Start queries
    //Select All rows from the source phone database 
      $source_data = mysqli_query($conn, "select * from cdrdb.session");

      // Loop on the results
      while($source_item = $source_data->fetch_assoc()) {

          // Check if row exists in destination phone database
          $row_exists = $conn2->query("SELECT SESSIONID FROM ambition.session WHERE SESSIONID = '".$source_item['SESSIONID']."' ");

              //if query returns false, rows don't exist with that new ID.
              if (!$row_exists){

                    //Insert new rows into ambition.session
                    $conn2->query("INSERT INTO ambition.session (SESSIONID,SESSIONTYPE,CALLINGPARTYNO,FINALLYCALLEDPARTYNO,DIALPLANNAME,TERMINATIONREASONCODE,ISCLEARINGLEGORIGINATING,CREATIONTIMESTAMP,ALERTINGTIMESTAMP,CONNECTTIMESTAMP,DISCONNECTTIMESTAMP,HOLDTIMESECS,LEGTYPE1,LEGTYPE2,INTERNALPARTYTYPE1,INTERNALPARTYTYPE2,SERVICETYPEID1,SERVICETYPEID2,EXTENSIONID1,EXTENSIONID2,LOCATION1,LOCATION2,TRUNKGROUPNAME1,TRUNKGROUPNAME2,SESSIONIDTRANSFEREDFROM,SESSIONIDTRANSFEREDTO,ISTRANSFERINITIATEDBYLEG1,SERVICEEXTENSION1,SERVICEEXTENSION2,SERVICENAME1,SERVICENAME2,MISSEDUSERID2,ISEMERGENCYCALL,NOTABLECALLID,RESPONSIBLEUSEREXTENSIONID,ORIGINALLYCALLEDPARTYNO,ACCOUNTCODE,ACCOUNTCLIENT,ORIGINATINGLEGID,SYSTEMRESTARTNO,PATTERN,HOLDCOUNT,AUXSESSIONTYPE,DEVICEID1,DEVICEID2,ISLEG1ORIGINATING,ISLEG2ORIGINATING,GLOBALCALLID,CADTEMPLATEID,CADTEMPLATEID2,ts,INITIATOR,ACCOUNTNAME,APPNAME,CALLID,CHRTYPE,CALLERNAME,serviceid1,serviceid2)
                    VALUES ('".$source['SESSIONID']."' ,
                            '".$source['SESSIONTYPE']."' ,
                            '".$source['CALLINGPARTYNO']."' ,
                            '".$source['FINALLYCALLEDPARTYNO']."',
                            '".$source['DIALPLANNAME']."',
                            '".$source['TERMINATIONREASONCODE']."',
                            '".$source['ISCLEARINGLEGORIGINATING']."',
                            '".$source['CREATIONTIMESTAMP']."',
                            '".$source['ALERTINGTIMESTAMP']."',
                            '".$source['CONNECTTIMESTAMP']."',
                            '".$source['DISCONNECTTIMESTAMP']."',
                            '".$source['HOLDTIMESECS']."',
                            '".$source['LEGTYPE1']."',
                            '".$source['LEGTYPE2']."',
                            '".$source['INTERNALPARTYTYPE1']."',
                            '".$source['INTERNALPARTYTYPE2']."',
                            '".$source['SERVICETYPEID1']."',
                            '".$source['SERVICETYPEID2']."',
                            '".$source['EXTENSIONID1']."',
                            '".$source['EXTENSIONID2']."',
                            '".$source['LOCATION1']."',
                            '".$source['LOCATION2']."',
                            '".$source['TRUNKGROUPNAME1']."',
                            '".$source['TRUNKGROUPNAME2']."',
                            '".$source['SESSIONIDTRANSFEREDFROM']."',
                            '".$source['SESSIONIDTRANSFEREDTO']."',
                            '".$source['ISTRANSFERINITIATEDBYLEG1']."',
                            '".$source['SERVICEEXTENSION1']."',
                            '".$source['SERVICEEXTENSION2']."',
                            '".$source['SERVICENAME1']."',
                            '".$source['SERVICENAME2']."',
                            '".$source['MISSEDUSERID2']."',
                            '".$source['ISEMERGENCYCALL']."',
                            '".$source['NOTABLECALLID']."',
                            '".$source['RESPONSIBLEUSEREXTENSIONID']."',
                            '".$source['ORIGINALLYCALLEDPARTYNO']."',
                            '".$source['ACCOUNTCODE']."',
                            '".$source['ACCOUNTCLIENT']."',
                            '".$source['ORIGINATINGLEGID']."',
                            '".$source['SYSTEMRESTARTNO']."',
                            '".$source['PATTERN']."',
                            '".$source['HOLDCOUNT']."',
                            '".$source['AUXSESSIONTYPE']."',
                            '".$source['DEVICEID1']."',
                            '".$source['DEVICEID2']."',
                            '".$source['ISLEG1ORIGINATING']."',
                            '".$source['ISLEG2ORIGINATING']."',
                            '".$source['GLOBALCALLID']."',
                            '".$source['CADTEMPLATEID']."',
                            '".$source['CADTEMPLATEID2']."',
                            '".$source['ts']."',
                            '".$source['INITIATOR']."',
                            '".$source['ACCOUNTNAME']."',
                            '".$source['APPNAME']."',
                            '".$source['CALLID']."',
                            '".$source['CHRTYPE']."',
                            '".$source['CALLERNAME']."',
                            '".$source['serviceid1']."',
                            '".$source['serviceid2']."')");
                  }
      }

have you try a where clause like "where SESSIONID>".$Last_update_Record_sessionID"? and then, use a single shot query of updates (multiple values single query) — Ricardo Ortega Magaña
– Ricardo Ortega Magaña, Commented Sep 20, 2017 at 19:47
I haven't. I started down the current route and went with it. Unfortunately, I'm new to the PHP meets MySQL area. I normally just do this within a SQL editor — Geoff_S
– Geoff_S, Commented Sep 20, 2017 at 19:49
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Sep 20, 2017 at 20:57
This is in urgent need of some kind of array to define the columns you're using rather than hand-typing all out. You could have typos in there and never know. — tadman
– tadman, Commented Sep 20, 2017 at 20:57

rickdenhaan · Accepted Answer · 2017-09-20 19:56:19Z

3

// Check if row exists in destination phone database
$row_exists = $conn2->query("SELECT SESSIONID FROM ambition.session WHERE SESSIONID = '".$source_item['SESSIONID']."' ");

//if query returns false, rows don't exist with that new ID.
if (!$row_exists){

This is incorrect: if query returns false, the query failed to execute. You need to check if ($row_exists->num_rows == 0) instead. The way your code is now, it will always insert every record, again and again. Since you're not checking for errors on the INSERT query you're not noticing the failures you're getting on the duplicate entries for your SESSIONID column (I assume that's the primary key column in your local database as well).

Additionally, it would probably be a lot faster if you only SELECT the sessions you don't already have. Since you're working with an auto increment column, you can pretty much assume that anything in the remote database with a SESSIONID that came after the latest SESSIONID from your local database is new:

//Start queries
$latest_result = $conn2->query("SELECT MAX(`SESSIONID`) FROM `ambition`.`session`");
$latest_row = $latest->fetch_row();
$latest_session_id = $latest_row[0];

//Select All rows from the source phone database 
$source_data = mysqli_query($conn, "SELECT * FROM `cdrdb`.`session` WHERE `SESSIONID` > $latest_session_id");

Tables in MySQL are usually sorted by the primary key column by default, but if you're concerned about the order in which you're inserting data in your local database in case your script gets interrupted for some reason, you could add an explicit ORDER BY `SESSIONID` ASC to the query.

answered Sep 20, 2017 at 19:56

rickdenhaan

11.4k32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Geoff_S Over a year ago

Ok I ran this, updating the code in my question now, but I still get one more error (after fixing a variable name). I get

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 '' at line 60PS

. How do I know what line 60PS is?

rickdenhaan Over a year ago

Don't know where the "PS" comes from, it's probably line 60. Since you're not escaping any of your values, you're probably trying to insert a session that has an ' in one of its columns. Try using a prepared statement instead of just dropping the variables into the query.

Geoff_S Over a year ago

I don't know exactly how to do that. Again, I'm just coming from more of the SQL side, not so much PHP. However, could I implement something to escape the values instead? Line 60 is just the Insert part of my query up to the end of the column names just before the VALUES Line

rickdenhaan Over a year ago

You could use mysqli_real_escape_string(), but you should use a prepared statement with bound parameters instead.

rickdenhaan Over a year ago

By the way, please don't change the code in your question based on the answers you get, by doing that a lot of context is lost if anyone else finds this page as the answer to a similar issue. If you change your code and still have questions about it, add the updated code below the original code.

|

Collectives™ on Stack Overflow

Refactor PHP script to do mysql update between 2 database servers

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related