update media in layouts from library is not working

[reggyjunk]

When updating media from within the library, choosing "Update this media in all layouts it is assigned to." did not work for me. I'm using the Xamp 1.82, php 5.4.22.

The error was in the legacy mysql php code in module_db_mysql.php. I replaced some of the legacy code in the module with PHP-PDO and was able to make update media in layouts work. There were three issues as below.

1: module.class.php -> SetMediaInformation

  !$result = $db->query($SQL...

returned false when in fact the row was there

2: flash.module.php -> SetRegionInformation

 !$row= $db->GetSingleRow($SQL

returned false when in fact the row was there

3: module.class.php -> ReplaceMediaInAllLayouts

  $db->query("UPDATE lklayoutmedia SET mediaid = %d WHERE lklayoutmediaid = %d", $newMediaId, $row['lklayoutmediaid']);

took 3 minutes to complete the update so this left the Edit/Save dialog box up until query was complete. Replaced legacy code with PHP-PDO. Returned to expected behavior; dialog box goes away quickly as expected because the update query doesn't take but a moment to update the db.

[reggyjunk (regjunk06)]

Left this out: I'm using Xibo server 1.6rc1.

[Alex Harrington (alexharrington)]

Any chance of supplying a patch for this if you've already made code changes and tested please?

[reggyjunk (regjunk06)]

Alex -

The debugging took the time. If you can make use of any of code below, please do. These will need to be properly integrated into the whole, which they currently are not. nGetSingleRow is more complete and finished. nupdate_query is a quick fix to address 3: ReplaceMediaInAllLayouts as above.

From module_db_mysql.php

       // GetSingle Replacement

 public function nGetSingleRow($sql, $assoc = true) {
            try {
                $dbh = PDOConnect::init();
                $sth = $dbh->prepare($sql);
  $sth->execute();
  $row = $sth->fetch(($assoc) ? PDO::FETCH_ASSOC : PDO::FETCH_BOTH);
  if (!$row) {
                   Debug::LogEntry('audit', 'WARNING: nGetSingleRow returned empty sql = ' . $sql, 'db', 'nGetSingleRow');
                   $dbh = null;
     return null;
  } else {
                   Debug::LogEntry('audit', 'CONFIRMED: nGetSingleRow returned something sql = ' . $sql, 'db', 'nGetSingleRow');
     $dbh = null;
     return $row;
  }
            } catch (Exception $e) {
                Debug::LogEntry('audit', 'failed nGetSingleRow ' . $e->getMessage() . ' sql = ' . $sql, 'db', 'nGetSingleRow');
  $dbh = null;
  return null;
     }

 // Replaces update query
 public function nupdate_query($sql) {
            try
                $dbh = PDOConnect::init();
                $sth = $dbh->prepare($sql);
  $sth->execute();
            } catch (Exception $e) {
                Debug::LogEntry('audit', 'failed nupdate_query ' . $e->getMessage() . ' sql = ' . $sql, 'db', 'nupdate_query');
  $dbh = null;
  return null;
     }
 }

[reggyjunk (regjunk06)]

Alex -

One more thing. In 1: above:

The code in module.class.php, in the routine SetMediaInformation, the original query is written to return a single row.

                // Load what we know about this media into the object
                $SQL = "SELECT duration, name, UserId, storedAs FROM media WHERE mediaID = '$mediaid'"

My changes to that code, replaces the call to query with the new nGetSingleRow :

  //if (!$result = $db->query($SQL))
  // nonassoc, returns array
  $row = $db->nGetSingleRow($SQL,false);
                if ( is_null($row) ) ...

[Dan Garner (dangarner)]

Sorry, but I don't understand why the patch to this works. As far as I can see the SQL statements are the same but you have changed them to use PDO instead of mysql ?

We are slowly migrating the core code over to PDO - but not through the module_db_mysql file. Our intentions are to keep that file intact so that we aren't changing wholesale the entire tested DB interaction.

If I could understand what you did specifically that made it work with PDO the we could fix that - perhaps you can list out the steps taken to cause it to fail?

[reggyjunk (regjunk06)]

Yes, it's not clear why the original deprecated routines fail. Are you seeing the failure on your end too?

That's the interesting part and the difficulty in tracking it down. In looking at the original code, it appeared that it should work. The points of failure acted the same way, every time as described.

From my testing, there wasn't anything special required to cause the bug. All of the issues relate to depreciated routines. By replacing the legacy/depreciated routines with PDO, it was corrected.

I thought it might be linked to the version of PHP and or MySQL I was using. I agree, it is not a tidy conclusion certainly if it is the plan to keep the legacy mysql routines.

[Launchpad Janitor (janitor)]

[Expired for Xibo because there has been no activity for 60 days.]