Different sort order of Laravel Eloquent all() on different servers

Question

I have several foreach loops to show all players in a game. There are 10 players in the table, with an id column of type int, auto-incremented. The code looks like this:

foreach(Player::all() as $player) {
    echo $player->id;
}

On my dev Mac, MySQL 5.1.70, I get: 1 2 3... On my prod server, MySQL 5.5.31, I get: 1 10 2 3 4 ....

This is consistent in every other place in my code. Now, I get that this is an alphanumeric sort. The question is: why? What can I turn on on the server to get it to match my dev machine, without performing broad code changes?

I'm using PHP 5.4, Apache 2.4, Laravel 4.0.5 with Eloquent

Update:
I found another symptom to this, which might actually be the root cause of the problem: upon analyzing client-side AJAX calls to the server, I found that values return as ints from the dev server, but as strings from the prod server - causing tons of misbehaviors. This explains why I get an ASCII sort. But agai, the question is: what do I need to "fix" on the prod server for this to work?

If you don't define a sort, you get a random sort, as it apparently doesn't matter. So... define an `->orderBy()` if it does? — Wrikken, Jul 15 '13 at 21:49
Before I add ->orderBy() to all my queries, I'd like to understand the difference in environment. To me, that might be more important than the actual resolution of the local issue - I may encounter other similar issues that stem from a server property. — Traveling Tech Guy, Jul 15 '13 at 21:53
It is as I say: you don't specify an order, Laravel doesn't specify an order, so the database will get the values in whichever order seems the most efficient to it, which most likely hss to do with the order in which they have been written on the disk. This is _often_ the order of the primary key, however, this is _by no means guaranteed if you don't specify it_. — Wrikken, Jul 15 '13 at 21:57
@Wrikken thanks for your time. I've looked further, and as I feared, this is just a symptom to a bigger problem (see my update) - I'm hoping there's a magic setting I can add and solve this. — Traveling Tech Guy, Jul 15 '13 at 21:59
Note that if you often use a specific order, you can help speed it along with an [`ALTER TABLE tablename ORDER BY columnname`](http://dev.mysql.com/doc/refman/5.1/en/alter-table.html), but note:_"ORDER BY enables you to create the new table with the rows in a specific order. **Note that the table does not remain in this order after inserts and deletes.**"_ — Wrikken, Jul 15 '13 at 21:59
Ah, if you let javascript sort it, either force (`intval`) the types, or let the dev & prod server use the mysql native driver (`mysqlnd`, see [the installation instructions](http://www.php.net/manual/en/ref.pdo-mysql.php)) to be able to fetch 'native' types. — Wrikken, Jul 15 '13 at 22:01
[see also this question to determine which driver you use](http://stackoverflow.com/questions/1475701/how-to-know-if-mysqlnd-is-the-active-driver) — Wrikken, Jul 15 '13 at 22:07
Hey @Wrikken, you were right! It was the PDO driver. Once I installed mysqlnd, it all started working again! Please insert your reply as an answer so I can mark it correctly. Also, add how to install mysqlnd, for future reference (`sudo yum install php54-mysqlnd` -- and NOT php-mysqlnd - pay attention to the 54!) — Traveling Tech Guy, Jul 15 '13 at 22:32

score 1 · Accepted Answer · answered Jul 17 '13 at 01:45

For posterity's sake, here's the full answer. First - thanks to @Wrikken - he nailed it in the comments. If he enters it as an answer, I'll mark it correct.

The differences I found between servers:

With MySQL 5.5, the default database engine is InnoDB, as opposed to MyISAM on my machine. That, somehow, effects default sorting order. So one solution was to add a line to the Eloquent schema creation, to ensure that the table gets created on the MyISAM engine:

Schema::create('blah', function($t) { $t->engine = "MyISAM"; $t->increments('id'); //more fields ... });
The reson all AJAX results returned as strings on the prod server, as opposed to int, float, boolean etc. on my dev machine, was because the MySQL Native Driver was not installed on the prod machine. After some trial and error, what needed to be done to solve this was to install mysqlnd (sudo yum install php54-mysqlnd - pay attention to the 54!), and restart Apache.
Finnaly, there remains the fact I was relying on server behavior to provide predictive program behavior - a big mistake. So I changed all the ::all() calls in my code to ::orderby('id')->get(), and now I get what I need, regardless of server, engine, database, etc.

Different sort order of Laravel Eloquent all() on different servers

1 Answers1