mySQL error code: 1064 after upgrade [Archive]

View Full Version : mySQL error code: 1064 after upgrade

Maelstrom

1-12-05, 01:06 AM

After the upgrade I get this on my invision board. How do I fix it??

mySQL query error: SELECT mod.mid as is_mod, m.id, m.name, m.mgroup, m.password, m.email, m.allow_post, m.view_sigs, m.view_avs, m.view_pop, m.view_img, m.auto_track,
m.mod_posts, m.language, m.skin, m.new_msg, m.show_popup, m.msg_total, m.time_offset, m.posts, m.joined, m.last_post,
m.last_visit, m.last_activity, m.dst_in_use, m.view_prefs, g.*
FROM ibf_members m
LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup)
LEFT JOIN ibf_moderators mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.id='1'

mySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.' at line 6
mySQL error code: 1064
Date: Tuesday 11th of January 2005 09:04:26 PM

Tien

1-12-05, 01:53 AM

I'm running Invisionboard 1.3.1 final and I don't have that error. Did you change anything in the scripts?

EDIT
I can view your board ok without errors

Jeepaholic

1-12-05, 09:27 AM

I have the exact same error here...

I'm running InvisionBoard 1.1.1

The forum is located at http://www.eriejeeppeople.com

The website is on Charon ... The mySQL database is on mysql07

I am just beginning to dig into what the problem is... anyone have any ideas?

Looks to me like ALOT of people are having problems after this mySQL update.

James

1-12-05, 09:30 AM

have you tried running repair table on those tables?

Jeepaholic

1-12-05, 09:52 AM

Just did a repair on all of the tables and no luck...

Still getting this error:

mySQL query error: SELECT mod.mid as is_mod, m.id, m.name, m.mgroup, m.password, m.email, m.allow_post, m.view_sigs, m.view_avs, m.view_pop, m.view_img, m.auto_track,
m.mod_posts, m.language, m.skin, m.new_msg, m.show_popup, m.msg_total, m.time_offset, m.posts, m.joined, m.last_post,
m.last_visit, m.last_activity, m.dst_in_use, m.view_prefs, g.*
FROM ibf_members m
LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup)
LEFT JOIN ibf_moderators mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.id='1'

mySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.' at line 6
mySQL error code: 1064
Date: Wednesday 12th of January 2005 05:50:43 AM

weddingsite

1-12-05, 10:03 AM

I am getting Runtime error - Object expected when trying to add a new post in my forum!
Server is mysql07
database - weddingsite

I tried to run a repair on the tables to no avail.

One strange thing I noted is that in phpmyadmin I have NO PRIVILIGES under the database
see this print screen - http://www.theweddingsite.com/no_privileges.jpg

Please help

Jeepaholic

1-12-05, 10:06 AM

I also have that issue (in addition to he 1064 error)...

NO PRIVLEGES under 'Create New Database' either...

tbonekkt

1-12-05, 10:10 AM

The 'NO PRIVILEGES' isn't a problem - that's how it's always been. You cannot create databases through phpmyadmin here; you must do it through OPS.

agueviv

1-12-05, 10:14 AM

Hey Jeepaholic!!

I have the very same error, I visited your forums (I do off-road too :) ) and seems to be working fine...

Whad did you do to solve this? I´m using IPB v1.1.2

weddingsite

1-12-05, 10:21 AM

The 'NO PRIVILEGES' isn't a problem - that's how it's always been. You cannot create databases through phpmyadmin here; you must do it through OPS.

OK...so what is the problem with not being able to add new posts in my forum?

I am getting the error " A runtime error has occured. Do you wish to debug? Error: Syntax Error"

I also get "Object expected"

Another error is..
2: mysql_num_rows(): supplied argument is not a valid MySQL result resource
(/www/w/weddingsite/cgi-bin/yabbse/Sources/Display.php ln 104)

Server is mysql07

tbonekkt

1-12-05, 10:22 AM

I don't know what the problem is with your forum - is it compatible with MySQL 4.1.x?

weddingsite

1-12-05, 10:26 AM

I am not sure. Someone had installed it for me about 2 years ago. The providers do not offer an upgrade version as they stopped business. Shall I install a new forum or is there something else I can do?

Jeepaholic

1-12-05, 10:27 AM

I just cleared the browser cache and cookies and the home page loads up fine.. but whenever I try to log into the forums I get the 1064 error again.

Hmmmm ?!?!?

tbonekkt

1-12-05, 10:28 AM

I really am not a PHP/MySQL guru (although I am learning more and more), so I'll let the experts chime in because I don't want to guide you in the wrong direction.

Maelstrom

1-12-05, 10:32 AM

I just cleared the browser cache and cookies and the home page loads up fine.. but whenever I try to log into the forums I get the 1064 error again.

Hmmmm ?!?!?

Exactly the same here. I clear my cookies and then I can view the board, but the instant I try to log in I get the same error message.

Jeepaholic

1-12-05, 10:52 AM

Looking at the Invision Forums (at least the ones I can still access since they shut them down to customers that use the free version) It appears that others have had similar issues when upgrading their PHP to 4.3.10 and took care of the problem by installing the latest release of Zend Optimizer.

Not sure if this is the issue or not, but I thought I would post it here to help get the wheels turning for the people that really know how this stuff works, I just kinda muddle through it till I get it to work.. LOL

agueviv

1-12-05, 10:58 AM

MidPhase solved this problem to me in another forums, based on this:

Troubleshooting for "Warning: Illegal offset type in ..." in PHP
Posted by kreny at December 17, 2004 02:08 AM
System Environment:
Red Hat 9.0 + Apache 2.0.52 + PHP 4.3.10 + Zend Optimizer v2.1.0 etc.

Description:

I upgraded PHP 4.3.9 to 4.3.10 and when I opened my sites like phpMyAdmin or Nucleus v2.5 beta, the page led to:

Warning: Illegal offset type in abc.php on line 91
Warning: Illegal offset type in abc.php on line 108

After I downgraded PHP to 4.3.9, everything goes well.

Troubleshooting:

I searched PHP Bugs and found some interesting issues:

Bug #31116 Pear not working after update to 4.3.10
http://bugs.php.net/bug.php?id=31116&edit=1

Bug #30914 foreach returns the wrong value
http://bugs.php.net/bug.php?id=30914

So I was wondering that the low-version of Zend Optimizer caused the problem.

Upgrading Zend Optimizer to 2.5.7
Please read the Zend Optimizer User Guide first.
http://www.zend.com/store/products/optimizer-user-guide.php

# tar -zxvf ZendOptimizer-2\[1\].5.7-linux-glibc21-i386.tar.gz
# cd ZendOptimizer-2.5.7-linux-glibc21-i386
# ./install
The following configuration changes have been made:
- The php.ini file has been relocated from /usr/local/lib to /usr/local/Zend/etc.
- A symbolic link for the php.ini file has been created in /usr/local/lib.
- The original php.ini was backed up to /usr/local/lib/php.ini-zend_optimizer.bak
Some lines updated in php.ini:
[Zend]
zend_optimizer.optimization_level=15
zend_extension_manager.optimizer=/usr/local/Zend/lib/Optimizer-2.5.7
zend_extension_manager.optimizer_ts=/usr/local/Zend/lib/Optimizer_TS-2.5.7
zend_extension=/usr/local/Zend/lib/ZendExtensionManager.so
zend_extension_ts=/usr/local/Zend/lib/ZendExtensionManager_TS.so

Then restart the apache and recombine the PHP 4.3.10 by using configure in my previous setting, and don't remember to run:
#libtool --finish /root/source/apache2/php-4.3.10/libs

Problem solved!

CM

The problem was solved in 2 minutes by the MidPhase support dept.

http://weblog.dalouis.com/archives/2004/12/troubleshooting.html

Source: google cache:
http://64.233.161.104/search?q=cache:gdVg0eWC4z8J:weblog.dalouis.com/archives/2004/12/troubleshooting.html+&hl=en

Jeepaholic

1-12-05, 11:01 AM

Maelstrom

1-12-05, 11:14 AM

I just checked a different site that I helped set up on the POWWEB servers that is also using InvisionBoard and I can view the forums and log in perfectly fine... It is Invision Board 1.3.1

The URL is http://www.mbrscca.com/forums/

Can't remember the site or mySQL server though (it was a little while ago that I helped set it up)

Maybe that's the problem for me then. I still have 1.1.2 installed. It was the free version and now you have to pay to upgrade I believe. Bummer.

Jeepaholic

1-12-05, 11:21 AM

KarlFry

1-12-05, 12:16 PM

Our admins provided us with the following link this morning in email. I'm not a PHP/mySQL guru either, but perhaps the following link may provide insight on why scripts may have stopped working with the updated mySQL version:

http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html

Karl F.
PowWeb Technical Support

Jeepaholic

1-12-05, 12:20 PM

Whatever happened to backward compatability.....

agueviv

1-12-05, 01:02 PM

I run a phpinfo.php script in my midphase host, where I have an IPB forum, same version, IPB v1.1.2 and got this:

PHP Version: 4.3.10
PHP Operating System: Linux
Zend Encoding Available? Yes
PHP API 20020918

Configure Command './configure' '--with-apxs=/usr/local/apache/bin/apxs' '--with-xml' '--enable-bcmath' '--enable-calendar' '--with-curl' '--with-dom' '--with-dom-xslt' '--with-dom-exslt' '--enable-exif' '--with-swf=/usr/local/flash' '--enable-ftp' '--with-gd' '--with-jpeg-dir=/usr/local' '--with-png-dir=/usr' '--with-xpm-dir=/usr/X11R6' '--with-gettext' '--with-imap' '--with-imap-ssl' '--with-kerberos' '--enable-mbstring' '--enable-mbstr-enc-trans' '--enable-mbregex' '--with-mcrypt' '--with-mhash' '--with-ming=../ming-0.2a' '--enable-magic-quotes' '--with-mysql=/usr' '--with-openssl' '--enable-discard-path' '--with-pear' '--with-pspell' '--enable-sockets' '--enable-track-vars' '--with-ttf' '--with-freetype-dir=/usr' '--enable-gd-native-ttf' '--enable-versioning' '--enable-wddx' '--with-xmlrpc' '--with-zip' '--with-zlib'

PHP Extension 20020429
Zend Extension 20021010
Debug Build no
Thread Safety disabled
Registered PHP Streams php, http, ftp, https, ftps, compress.zlib

This program makes use of the Zend Scripting Language Engine:
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies with Zend Extension Manager v1.0.6, Copyright (c) 2003-2004, by Zend Technologies with Zend Optimizer v2.5.7, Copyright (c) 1998-2004, by Zend Technologies

My server info here in powweb using the same phpinfo.php script is like this

PHP Version 4.3.10

Configure Command './configure' '--prefix=/usr/local/php4' '--enable-force-cgi-redirect' '--enable-safe-mode' '--enable-versioning' '--with-mysql=/usr/local' '--with-enabled-local-infile' '--with-dbase' '--enable-ftp' '--with-openssl' '--with-imap' '--enable-sysvsem' '--enable-sysvshm' '--enable-shmop' '--with-gd' '--enable-gd-native-ttf' '--with-ttf' '--with-freetype-dir' '--with-jpeg-dir=/usr/local' '--with-zlib' '--enable-libgcc' '--enable-mbstring' '--enable-xslt' '--with-xslt-sablot' '--with-gettext' '--with-curl' '--with-mcrypt' '--with-dom' '--with-dom-xslt' '--with-mhash' '--with-zip' '--with-bz2' '--with-iconv' '--enable-exif' '--enable-bcmath' '--enable-calendar' '--enable-xslt' '--with-xslt-sablot' '--enable-sockets'

PHP API 20020918
PHP Extension 20020429
Zend Extension 20021010
Debug Build no
Thread Safety disabled
Registered PHP Streams php, http, ftp, https, ftps, compress.bzip2, compress.zlib

This program makes use of the Zend Scripting Language Engine:
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies

So, its the same PHP version, the same Zend Engine version and the same IPB forum software version...

Whats different?

Configure Command ?
Zend Extension Manager v1.0.6 and Zend Optimizer v2.5.7 not present in powweb??

We should get a better support from powweb in this issues caused by them. I have the midphase.com reference and it was so quick, 2 minutes literally, with immediatte response to my support ticket to the same PHP upgrade issue, they did not give links to information webpages in order to the user can solve the issue.

Help please.

agueviv

1-12-05, 01:07 PM

I read again my support ticket with Midphase: they solved the problem when they updated Zend Optimizer to version 2.5.7.

Maelstrom

1-12-05, 02:01 PM

I read again my support ticket with Midphase: they solved the problem when they updated Zend Optimizer to version 2.5.7.

So the problem would be fixed if Powweb upgraded their servers with the new version of Zend Optimizer?

Jeepaholic

1-12-05, 02:18 PM

So the problem would be fixed if Powweb upgraded their servers with the new version of Zend Optimizer?

Looks like this might be the case...

Here is a 'notice' from the Zend website http://www.zend.com/store/products/zend-optimizer.php ...

Due to incompatibility of the previous version of Zend Optimizer with PHP 4.3.10, it is strongly recommended that owners of Zend Performance Suite, Zend Accelerator, Zend Studio Server, and Zend WinEnabler, upgrade to Zend Optimizer 2.5.7.

It is also recommended you upgrade to PHP 4.3.10 or PHP 5.0.3.

mjp

1-12-05, 02:28 PM

We should get a better support from powweb in this issues caused by them.Upgrading any of the many programs that are used in the server environment can cause compatibility issues with existing code. Those are hardly problems caused by PowWeb. It is not unreasonable to refer you to the MySQL site for information on the upgrade of their product.

There is a very predictable series of events that happen with any upgrade:

- The upgrade is announced by the vendor and people start asking here in the forum, "When will you upgrade? What are you waiting for? PowWeb SUCKS! Upgrade [whatever it is]!!!"

- We upgrade, and people start complaining here in the forum, "Why did you upgrade? Why couldn't you wait? PowWeb SUCKS! Go back to the previous version of [whatever it is]!!!"

If Invision isn't supporting the free version of their program anymore, that's a shame, but that does not mean that the responsibility for supporting their abandoned product now falls on your web host.

In the case of Zend, that is something we can look at. But there are many factors that contribute to the server environment, so you should not assume that a change made at another host will have the same effect here.

agueviv

1-12-05, 02:38 PM

Its not the Invision free version the cause of the problem. I have the same version working here in powweb and midphase: the one at powweb its down and the one at midphase its working fine.

Jeepaholic

1-12-05, 02:44 PM

If Invision isn't supporting the free version of their program anymore, that's a shame, but that does not mean that the responsibility for supporting their abandoned product now falls on your web host.

I don't think that anyone in this thread specifically asked for help from POWWEB to modify their IPB code to work with the new PHP Upgrade.

Rather.. we asked why aren't our websites working anymore when they were working fine a few days ago. Obviously this issued is linked to the upgrade and not to the IPB software that was working perfectly fine for over a year on your servers.

Hopefully you can look into upgrading the Zend Optimizer to 2.5.7 and find that it does not cause any incompatabilities on your end and hopefully will fix the issue that some appear to be having with the PHP 4.3.10 upgrade.

:cool:

Maelstrom

1-12-05, 02:48 PM

Yes, I don't put any blame on Powweb at all either. Upgrades are necessary. I was just looking for a solution to keep my board up and running, and would have been happy to make any changes necessary on my end to get it done.

princess

1-12-05, 02:58 PM

you guys should at least notify your customers before upgrading.. im having the exact same problem and i have to say its VERY irritating having to deal with this today

agueviv

1-12-05, 03:16 PM

So the problem would be fixed if Powweb upgraded their servers with the new version of Zend Optimizer?

I really don´t know if the new version of Zend Optimizer will solve our problem. All I know is that last friday MidPhase upgraded PHP and I have some issues with my IPB 1.1.2 and then at 9:12 I opened a ticket with MidPhase support and they solved at 9:36, upgrading to Zend Optimizer to version 2.5.7.

That´s all what I know...

princess

1-12-05, 03:30 PM

I really don´t know if the new version of Zend Optimizer will solve our problem. All I know is that last friday MidPhase upgraded PHP and I have some issues with my IPB 1.1.2 and then at 9:12 I opened a ticket with MidPhase support and they solved at 9:36, upgrading to Zend Optimizer to version 2.5.7.

That´s all what I know...

I host my other sites with other hosts and i understand how you feel. powweb has the most not so up to par tech support out of all my host sorry to say... i dont even bother coming to the board to complain but this time i had to. how can the technical support of a hostign company not know about mysql or the errors associated with it or even worse how to fix it in due time.. oh well i want to see how this play out seeing as i still have a few months till my payment is up....

zinco

1-12-05, 03:48 PM

See this post:

http://forum.powweb.com/showthread.php?p=264421#post264421

James

1-12-05, 04:00 PM

PowWeb has been using the newest verision of Zend since it came out, it is not up to us to upgrade, its up to you to change it in your php.ini to the new version.

princess

1-12-05, 04:02 PM

PowWeb has been using the newest verision of Zend since it came out, it is not up to use to upgrade, its up to you to change it in your php.ini to the new version.

for dummies like me hosting with powweb can you at least tell us how to go about doing that

James

1-12-05, 04:06 PM

We already do that too:

http://support.powweb.com/index.php?category=PHP/MySQL&topic_id=35

princess

1-12-05, 04:14 PM

it doesnt say where to save it to and am i saving it as php.ini or customphp.ini

James

1-12-05, 04:24 PM

you save it as "php.ini" and you put it in you htdocs directory. If you are already using Zend then you would have to have already done this. I will see that page gets updated to be more clear.

princess

1-12-05, 04:27 PM

no luck still down!

agueviv

1-12-05, 04:28 PM

Its just a matter of create a file named php.ini in the /htdocs dir, using the coded provided here: http://support.powweb.com/index.php?category=PHP/MySQL&topic_id=57 (click on Click here) ?????

Is just copy - paste or I have to configure in order to work?

Maelstrom

1-12-05, 04:34 PM

Well, I've created the php.ini file with the Zend additions and uploaded it to my /htdocs folder, but I'm still getting the same error message. I'm stumped.

Maelstrom

1-12-05, 04:35 PM

See this post:

http://forum.powweb.com/showthread.php?p=264421#post264421

This seems to suggest that I should rename any instance of "mod" to "mod2" in every php file for the board?

mjp

1-12-05, 04:38 PM

I will see that page gets updated to be more clear.Done.

princess

1-12-05, 04:54 PM

still not working..ive tried it more then 5 times

agueviv

1-12-05, 05:05 PM

Not working...

I pasted all the commented code in the php.ini file, and I un-comented the Zend block like this:

[Zend]
zend_optimizer.optimization_level=15
zend_extension_manager.optimizer=/usr/local/Zend/lib/Optimizer-2.5.7
zend_extension_manager.optimizer_ts=/usr/local/Zend/lib/Optimizer_TS-2.5.7
zend_extension=/usr/local/Zend/lib/ZendExtensionManager.so
zend_extension_ts=/usr/local/Zend/lib/ZendExtensionManager_TS.so

agueviv

1-12-05, 05:07 PM

This seems to suggest that I should rename any instance of "mod" to "mod2" in every php file for the board?

Maelstrom, as I said, I have an IPB working fine with another host, I did nothing to fix it when the host upgraded PHP.

agueviv

1-12-05, 05:11 PM

Jeepaholic , any luck with the custom php.ini file??

zinco

1-12-05, 05:20 PM

Don't think it has to do with zend of the php.ini file guys. Has to do with changes in MySql 4.1.

One change I know for sure affected my board and the fix was pretty easy for me.
http://forum.powweb.com/showthread....4421#post264421

For editing .php files I use HTML Kit, very nice free editor.
http://www.chami.com/html-kit/

agueviv

1-12-05, 05:22 PM

zinco

1-12-05, 05:56 PM

I think it would apply to any files that use php scripts to query the data base.

princess

1-12-05, 06:26 PM

ive tried ur suggestion and it doesnt work.. at this point im not even sure where to go with this anymore except to switch hosts... too stressful

agueviv

1-12-05, 06:47 PM

I run my phpinfo.php script again and got this:

This program makes use of the Zend Scripting Language Engine:
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies with Zend Extension Manager v1.0.6, Copyright (c) 2003-2004, by Zend Technologies with Zend Optimizer v2.5.7, Copyright (c) 1998-2004, by Zend Technologies

Powweb is using mysql 4.1.7 and my other host is using 4.0.22.

Well, seems like we should aim at mysql as ZINCO stated...

If sombody using IPB gets a fix, please share.

netbat199

1-15-05, 02:58 AM

Don't give up hope... I'm not... I have too much work into my forum to just give up...

Hopefully (usually) a fix of some sort is figued out where some coder will find where the issue in the old code that is not compatible with the mySQL update and will put up the lines of code that need to be modified to work with the new version of mySQL.

*fingers crossed*

I agree, as I am in the same situation.
I am using Invision Power Board v1.1.2
I can access the homepage but when I attempt to log in I get the following:

-----------------------------------------------------------------------------------------------------
mySQL query error: SELECT mod.mid as is_mod, m.id, m.name, m.mgroup, m.password, m.email, m.allow_post, m.view_sigs, m.view_avs, m.view_pop, m.view_img, m.auto_track,
m.mod_posts, m.language, m.skin, m.new_msg, m.show_popup, m.msg_total, m.time_offset, m.posts, m.joined, m.last_post,
m.last_visit, m.last_activity, m.dst_in_use, m.view_prefs, g.*
FROM ibf_members m
LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup)
LEFT JOIN ibf_moderators mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.id='1'

mySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.' at line 6
mySQL error code: 1064
Date: Friday 14th of January 2005 10:55:38 PM
-----------------------------------------------------------------------------------------------------

IanS

1-15-05, 08:25 AM

I agree, as I am in the same situation.
I am using Invision Power Board v1.1.2
I can access the homepage but when I attempt to log in I get the following:

-----------------------------------------------------------------------------------------------------
mySQL query error: SELECT mod.mid as is_mod, m.id, m.name, m.mgroup, m.password, m.email, m.allow_post, m.view_sigs, m.view_avs, m.view_pop, m.view_img, m.auto_track,
m.mod_posts, m.language, m.skin, m.new_msg, m.show_popup, m.msg_total, m.time_offset, m.posts, m.joined, m.last_post,
m.last_visit, m.last_activity, m.dst_in_use, m.view_prefs, g.*
FROM ibf_members m
LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup)
LEFT JOIN ibf_moderators mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.id='1'

mySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.' at line 6
mySQL error code: 1064
Date: Friday 14th of January 2005 10:55:38 PM
-----------------------------------------------------------------------------------------------------http://forum.powweb.com/showthread.php?t=45461&highlight=version

might throw some light on your problem. Although I'm no expert, it appears that you have 'mod' as part of statements, in version 4.1 mod is a reserved word and not allowed.

zinco

1-15-05, 04:42 PM

Here is a better thread by princess for IPB boards.

http://forum.powweb.com/showthread.php?t=45494&highlight=princess