Thread

I ported a EE site from my local machine to a development server.  I have everything setup and the control panel is working fine.  The front end is broken and I traced down the cause to the failure of one database query. I can replicate the matter with a simple template as follow:

template:

{exp:weblog:entries dynamic="off" weblog="static_pages"}    
    {title}

{/exp:weblog:entries}

MySql code that failed:

SELECT t.entry_id FROM exp_weblog_titles AS t LEFT JOIN exp_weblogs ON t.weblog_id = exp_weblogs.weblog_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id WHERE t.entry_id !='' AND t.site_id IN ('1') AND t.entry_date < 1216282941 AND (t.expiration_date = 0 || t.expiration_date > 1216282941) AND exp_weblogs.is_user_blog = 'n' AND t.weblog_id = '4' AND t.status = 'open' ORDER BY t.sticky desc, t.entry_date desc LIMIT 0, 100

My local machine with MySQL 5.1.23rc returns me all entries but the development server with MySQL 5.0.45 returns null.  I use phpMyAdmin and observe the same behavior.  There are some talk about LEFT JOIN issue with MySQL 5.0.45 but is confirmed not a bug.

Does this ring any bell to anyone?

Do you have a weblog called static_pages?

I just use an example.  Actually it is called “cma_pages” and it does exist in the database.  I imported it using phpMyAdmin.

All backend operation works just fine and I can see all weblog and entries. I am under the impression that it has something to do with the complex LEFT JOIN as the same query returns NULL if I exercise it in phpMyAdmin.  It works fine in my local PC.

I am no MySQL expert but is this a right sytnax for the following:

(t.expiration_date = 0 || t.expiration_date > 1216282941)

It works on MySQL 5.0.45 after I change the conditional statement to

(t.expiration_date = 0 OR t.expiration_date > 1216282941)

So I changed mod.weblog.php line 1667 to: (replace || with OR)

$sql .= " AND (t.expiration_date = 0 OR t.expiration_date > ".$timestamp.") ";

And it works perfectly now on m development server running MySQL 5.0.45.

It came down to a simple SQL command like the following will not work in MySQL 5.0.45

SELECT * FROM `exp_weblog_titles` WHERE 1 AND (0=0 || 0> 1)

Replacing ‘||’ operator with ‘OR’ will solve the problem.  Can any EE database guru comment on this topic?

Is this a MySQL bug or EE issue.  In any case, please move this thread to the bug forum.

arnoldc, I can move this to the bug forum, but remember that EE works with versions of PHP/MySQL than you’re working with.

Does your fix work with earlier versions of MySQL?

I can’t comment for now as I only have access to either 5.0.45 and 5.1.23rc servers with the new changes.

It is weird because both “||” or “OR” operators should be same thing.  I am leaning this is either a bug in MySQL or a server configuraiton issue but would like to hear if anyone is actually running EE with 5.0.45…

I am running 5.0.41 and have no such issues.

I think it may have something to do with MySQL setup on my development server.  It must be under some very secured configuration as EE crapped out at the following MySQL statement when I attempted to delete a weblog entry.

SELECT title FROM exp_weblog_titles WHERE entry_id = "77"

Removing the quotes and the query will work.

My SQL installation experience is limited to local PC so I am not sure what is involved for a real web server.  This development server is newly installed by my IT team but appreciate if someone points me a direction what to look for….

Yes, both || and OR are valid.  http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

Is this a Windows server by chance?  Its installer has some default options that you won’t find on any of the *nix installers, and it’s also easier to trip it into an uncommon SQL mode.

Run both of these queries on the database server:

SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

That will display which mode(s) are enabled.

Hi Derek,

It is running off Linux.  And here are the results:

global sql mode
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI

Session sql mode
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI

Interesting, does your server administrator have a reason for setting those?  Looks like they might have a background with Oracle/MSSQL from the look of those settings.  Here are the details:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

REAL_AS_FLOAT will not affect you, but PIPES_AS_CONCAT will not work with ExpressionEngine (or most apps expecting standard syntax), and ANSI/ANSI_QUOTES will kill every commercial MySQL app I’m familiar with.

You’re going to need to run ExpressionEngine on a MySQL server that is closer to stock.

That explains everything.  Pass the ball back to IT now, thanks!.

Please let us know how it turns out, arnoldc.