Mysql PDO maximum LONGBLOB data length on fetch

Question 1

I insert about 1.1 Mb of Data into a Mysql Field of type LONGBLOB. This is far away from the maximum supported length of a LONGBLOB field.

The insert seems to work.

If I do a strlen($data) before inserting it returns 1059245.

If i do a query after inserting:

SELECT OCTET_LENGTH(`data`)...

It returns 1059245

But if i do

$stmt = $pdo->prepare("SELECT `data` FROM `tbl_mytable` WHERE `id` = :id LIMIT 1");
$stmt->bindValue(":id", $id, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetch(PDO::FETCH_ASSOC);
echo strlen($data['data']);

it returns 1048576

My data seems to be cutten after 1048576 bytes.

Why do I only receive the first 1048576 bytes of my data when doing a query?

Is it a PDO Configuration, something like max_fetch_length?

Question 2

It was the MYSQL_ATTR_MAX_BUFFER_SIZE which is 1MB by default.

This fixed the issue:

    $pdo->setAttribute(PDO::MYSQL_ATTR_MAX_BUFFER_SIZE, 1024*1024*50);  // 50 MB

Question 3

Your first question:

Specifically, can I include the $result = $stmt3->fetchAll(); before the commit(), and then execute the conditional query?

I see no reason why it should not work. A transaction behaves basically the same as operations without transactions - except that changes are only drafts. Any changes you make in the previous statements will be applied to a "working copy" valid for this single session only. For you it will appear completely transparent. However any changes will be rolled back if you do not commit them.

Also worth noting (emphasis mine):

In layman's terms, any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed.

This can cause racing conditions.

Your second question:

Also, I'm not entirely sure on this, but do I require the $db->rollBack(); within the try block, if the code is exited (return false) before the commit()?

From the documentation it says:

When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back.

Therefore you do not necessarily require to roll back manually as it will be done by the driver itself.

However note the following from the same source as well:

Warning PDO only checks for transaction capabilities on driver level. If certain runtime conditions mean that transactions are unavailable, PDO::beginTransaction() will still return TRUE without error if the database server accepts the request to start a transaction.

So be sure to check the compatibility beforehand!

A few notes

Do NOT begin a transaction in another transaction. This will commit the first transaction implicitely. See this comment.

Another note from the documentation:

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

Question 4

Here's an example of how I would handle this. I wouldn't change your query - it's fine as is. Assuming you want to show everything in the database sorted from latest to earliest post. Let PHP handle the heavy lifting. I've purposely broken some things in to multiple lines instead of nesting the functions to make it easier to read. Condense as you see fit.

I'm not claiming this is the BEST way to do it. Only what I use.

//MOCK UP SOME DISPLAY DATA - YOU WOULD USE YOUR QUERY RESULT INSTEAD
$rows = array();
$rows[] = date('Y-m-d H:i:s');
$rows[] = date('Y-m-d H:i:s');
$rows[] = date('Y-m-d H:i:s');
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, date('n'), date('j') - 1, date('Y')));
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, 12, 24, 2014));
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, 12, 25, 2014));
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, 12, 26, 2014));
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, 3, 2, 2001));

//CREATE AN ARRAY OF THE REPLACEMENTS YOU WANT
$aArray = array();
$aArray[date('Y-m-d')] = 'Today';
$aArray[date('Y-m-d', mktime(0, 0, 0, date('n'), date('j') - 1, date('Y')))] = 'Yesterday';
$aArray[date('Y-m-d', mktime(0, 0, 0, date('n'), date('j') - 2, date('Y')))] = 'Day before Yesterday';
$aArray['2014-12-25'] = 'Christmas 2014';

//INITIALIZE SOME VARIABLES
$cLastHeader = '';
$cCurrHeader = '';

//THIS WOULD BE YOUR QUERY RESULTS LOOP
foreach ($rows AS $nNull => $cDate) {
    $cLookup = substr($cDate, 0, 10);  //TRIM OUT THE TIME FROM CURRENT RECORD

    //IS DATE IN ARRAY? IF NOT, FORMAT
    if (isset($aArray[$cLookup])) {
        $cCurrHeader = $aArray[$cLookup];
    } else {
        $cCurrHeader = $cLookup; //WOULD SHOW 'YYYY-MM-DD'
        $cCurrHeader = date('F Y', strtotime($cLookup)); //WOULD SHOW 'MONTH YYYY'
    }

    //HAS HEADER CHANGED? IF SO PRINT
    if ($cCurrHeader != $cLastHeader) {
        $cLastHeader = $cCurrHeader;
        print($cCurrHeader . "n");
    }

    //PRINT RECORD
    print("t" . $cDate . "n");
}

The output from this is :

Today
    2015-05-28 18:40:35
    2015-05-28 18:40:35
    2015-05-28 18:40:35
Yesterday
    2015-05-27 00:00:00
December 2014
    2014-12-24 00:00:00
Christmas 2014
    2014-12-25 00:00:00
December 2014
    2014-12-26 00:00:00
March 2001
    2001-03-02 00:00:00

Question 5

I FIXED THE ERROR. The solution for anyone out there is simple, the PHP explode function was used to split the contents of a textarea into separate lines but it doesn't work if you use explode() with PHP_EOL. PHP EOL tells you the server's newline character from what I understand. I used the preg_split instead to perform the splitting and it works on both my localhost that runs on Windows and my server that runs on Linux. Thank you everyone for your help!!!

$conn = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                if(isset($_POST["busnumber"], $_POST["busroute"])){
                    $stops = preg_split("/\r\n|\r|\n/", $_POST['busroute']);
                    $sql = 'SELECT * FROM stops WHERE stop_name LIKE :stop';
                    $statement = $conn->prepare($sql);
                    foreach($stops as $stop){
                        $statement->bindValue(':stop', $stop);
                        $statement->execute();
                        while($result = $statement->fetch()){
                            echo $result['stop_id'].' '.$result['stop_name'].'</br>';
                        }
                    }
                }

Question 6

I take that back looks like you can use the cursor orientation contants to select the result... sample code coming... I havent tried this so you may need to play a bit. This is also based on the assumption that a PDO::FETCH_ORI_FIRST acts like a data_seek and leaves the cursor on the first position as opposed to returning it to whatever it was before.

$stmt = $pdo->prepare('SELECT id FROM table', array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();

$first = $pdo->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_FIRST);
$first_row = $first['id'];

// other stuff

// first iteration we rewind to the first record;
$cursor = PDO::FETCH_ORI_FIRST;

while (false !== ($row = $stmt->fetch(PDO::FETCH_ASSOC, $cursor))) {
   $id = $row['id'];
   // successive iterations we hit the "next" record
   $cursor = PDO::FETCH_ORI_NEXT; 
   echo $id;
}

I dont think you can rewind a statement... Assuming these blocks arent seprated by a bunch of intermediary logic id just do it in the loop.

$STH->setFetchMode(PDO::FETCH_COLUMN); // no need to pull an array
$count = 0;
while ($id = $STH->fetch()) {      
  if($count === 0) {
   $first_row = $id;
  }
  echo $id;
  $count++;
}

Mysql PDO maximum LONGBLOB data length on fetch

Answers

Your first question:

Your second question:

A few notes

php,mysql,pdo