CODEWITHSUNDEEP
phpmysql
kaiser
kaiser

Reputation: 22353

MySQL stops after inserting 255 rows

I got a pretty large DB-Table that I need to split into smaller tables for different reasons.

The handling happens via php close to this example:

// Note: It's an example and not working code - the actual function is much larger
function split_db()
{
    $results = "
        SELECT *
        FROM big_table
    ";

    foreach ( $results as $result )
    {
        // Here I split the big_tables contents and ...
        $some_val = $result->SomeVal;
        // ...
        $another_val = $result->AnotherVal;

        // ... here I insert the contents in the different tables
        $sql = "
            INSERT
            INTO first_small_table
            // ...
            VALUES
            // ...
        ";
    }
}

Problem: The query inserts 255 rows, no matter if I'm in the local environment or on the test server.

Question: Why? What am I doing wrong or am I missing something? And how would I avoid this?

Info about MySQL-Client-Version:

I'm no MySQL-Hero, so any help and explanation is appreciated, as Google brought nothing meaningful (to me) up. Thanks!

Upvotes: 1

Views: 1382

Answers (2)

zerkms
zerkms

Reputation: 255045

I bet you have your primary key of unsigned tinyint type, that has limit of 255 for the maximum value.

So change it to just int

ALTER TABLE first_small_table MODIFY id INT;

Upvotes: 7

Vincent Savard
Vincent Savard

Reputation: 35927

I can't say why you're limited to 255 rows, but what I can say is that you can do a single query to add your rows from your big table into your small table with a INSERT INTO ... SELECT query :

INSERT INTO first_small_table (col1, col2, col3)
SELECT col1, col2, col3
FROM big_table;

If you don't need to use PHP, then by all mean don't use it. It's faster to only use SQL.

Upvotes: 5

Related Questions