VirtueMart Forum

VirtueMart 2 + 3 => Virtuemart Development and bug reports => Topic started by: Cleanshooter on October 19, 2011, 15:56:46 pm

Title: [fixed]Upgrade error: DatabaseMySQL::query: 1064 - You have an error in your SQL
Post by: Cleanshooter on October 19, 2011, 15:56:46 pm
I'm betting this error is unique to me as no one else has mentioned it.  I'm wondering if it because of my use of custom fields?

I downloaded the new RC 1.9.8i and tried to install it but every time I do I get this:

(BTW I am updating form RC 1.9.8H)

JDatabaseMySQL::query: 1064 - 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 'COLUMN `product_order_levels`' at line 1 SQL=ALTER TABLE `jml_virtuemart_products` DROP COLUMN `product_order_levels` COLUMN `product_order_levels`

Callstack:

Call stack
#    Function    Location
1    JAdministrator->dispatch()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/index.php:47
2    JComponentHelper::renderComponent()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/includes/application.php:153
3    require_once()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/application/component/helper.php:156
4    JController->execute()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_akeeba/akeeba.php:167
5    AkeebaControllerInstaller->realinstall()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/application/component/controller.php:679
6    AkeebaModelInstaller->realInstall()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_akeeba/controllers/installer.php:172
7    JInstaller->install()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_akeeba/models/installer.php:219
8    JInstallerComponent->install()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/installer/installer.php:385
9    JInstallerComponent->update()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/installer/adapters/component.php:141
10    com_virtuemartInstallerScript->update()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/installer/adapters/component.php:821
11    com_virtuemartInstallerScript->alterTable()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/tmp/install_4e9ed4419244a/install/script.virtuemart.php:184
12    JDatabaseMySQLi->query()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/tmp/install_4e9ed4419244a/install/script.virtuemart.php:265
13    JError::raiseError()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/database/database/mysqli.php:535
14    JError::raise()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/error/error.php:215

BTW on a side note I moved my development to my local machine recently in an effort to connect my project with the SVN but I'm having trouble merging the Virtuemart SVN with my Local install of Joomla!  If anyone knows how to do this in eclipse or could point me in the right direction it could be helpful.  Do I simply copy the files from the SVN after check out and drop them into the project or can I check them out directly into the project using Subclipse? (Should this be in a separate post somewhere else?)
Title: Re: Upgrade error: DatabaseMySQL::query: 1064 - You have an error in your SQL syntax
Post by: Cleanshooter on October 19, 2011, 16:01:48 pm
Now my entire VM can't update or change anything.  I'm going to do a restore from one of my backups and see if I can update from that.
Title: Re: Upgrade error: DatabaseMySQL::query: 1064 - You have an error in your SQL syntax
Post by: PRO on October 19, 2011, 16:21:04 pm
when happens when you do the table update in the migration tool?
Title: Re: Upgrade error: DatabaseMySQL::query: 1064 - You have an error in your SQL syntax
Post by: Cleanshooter on October 19, 2011, 17:42:34 pm
Alright I restored my back up and then before I attempted another install I updated my tables with the migration tool.  Then I ran the installer and the same error message came up.  So I go back to migration tools and click "Install Tables or if necessary Update them" which loads a similar or the same error that I get when installing. 

I also tried clicking Renew config by file and this error then came up:

Errors

    JDatabaseMySQL::query: 1146 - Table 'jml_kakgg3lgbj.jos_virtuemart_configs' doesn't exist SQL=DELETE FROM `jos_virtuemart_configs` WHERE `virtuemart_config_id`=1
    Information


    Backtrace
    Call stack
    #    Function    Location
    1    JAdministrator->dispatch()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/index.php:47
    2    JComponentHelper::renderComponent()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/includes/application.php:153
    3    require_once()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/application/component/helper.php:156
    4    JController->execute()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_virtuemart/admin.virtuemart.php:59
    5    VirtuemartControllerUpdatesMigration->renewConfig()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/application/component/controller.php:679
    6    VirtueMartModelConfig->deleteConfig()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_virtuemart/controllers/updatesmigration.php:357
    7    VmConfig->loadConfig()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_virtuemart/models/config.php:368
    8    VmConfig->installVMconfig()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_virtuemart/helpers/config.php:315
    9    JDatabaseMySQLi->query()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_virtuemart/helpers/config.php:523
    10    JError::raiseError()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/database/database/mysqli.php:535
    11    JError::raise()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/error/error.php:215

I also double checked my version of MySQL and the Web Server is running Version: 5.0.91... I'm not sure if that is current enough...
Title: Re: Upgrade error: DatabaseMySQL::query: 1064 - You have an error in your SQL syntax
Post by: Cleanshooter on October 19, 2011, 18:01:41 pm
Now when I try to go into Viretuemart is provides this error:

500 - An error has occurred.

JDatabaseMySQL::query: 1146 - Table 'jml_kakgg3lgbj.jos_virtuemart_configs' doesn't exist SQL=DELETE FROM `jos_virtuemart_configs` WHERE `virtuemart_config_id`=1

Return to Control Panel

Call stack
#    Function    Location
1    JAdministrator->dispatch()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/index.php:47
2    JComponentHelper::renderComponent()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/includes/application.php:153
3    require_once()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/application/component/helper.php:156
4    VmConfig::loadConfig()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_virtuemart/admin.virtuemart.php:27
5    VmConfig::installVMconfig()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_virtuemart/helpers/config.php:315
6    JDatabaseMySQLi->query()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/administrator/components/com_virtuemart/helpers/config.php:523
7    JError::raiseError()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/database/database/mysqli.php:535
8    JError::raise()    /hermes/bosweb/web139/b1391/ipg.collegekidscom/public_html/joomla_cms/libraries/joomla/error/error.php:215

Table jos_virtuemart_configs deos not exist because it is jkm21_virtuemart_configs not jos_....

I wonder why it is looking for the wrong table prefix???  The problem probably resides in the installer somewhere...  I'll start looking
Title: Re: Upgrade error: DatabaseMySQL::query: 1064 - You have an error in your SQL syntax
Post by: jjk on October 19, 2011, 18:12:00 pm
Maybe your problem has been fixed already in SVN Revision 4457 yesterday.
..."Fixed bug when restoring config by file and database has no entry for config_id=1"
Title: Re: Upgrade error: DatabaseMySQL::query: 1064 - You have an error in your SQL syntax
Post by: Cleanshooter on October 19, 2011, 18:12:52 pm
I did find this little problem in administrator/components/com_virtuemart/helpers/config.php line 521

This is the line...

$qry = 'DELETE FROM `jos_virtuemart_configs` WHERE `virtuemart_config_id`=1';

the query shoudl be #__virtuemart_configs not jos_virtuemart_configs....


Mostly because my tables are not using the prefix nor should anyone else ever use the default prefix for security reasons.

I'm going to do a restore again correct this line then try the update again and hope that it takes care fo the issue.
Title: Re: Upgrade error: DatabaseMySQL::query: 1064 - You have an error in your SQL syntax
Post by: Cleanshooter on October 19, 2011, 18:16:26 pm
Maybe your problem has been fixed already in SVN Revision 4457 yesterday.
..."Fixed bug when restoring config by file and database has no entry for config_id=1"

I would love to be hooked up with SVN in my local on eclipse but I haven't figured out how to merge the checked out repository with my local install...  It's probably just something stupid like copy and paste but it's my first time really using Eclipse for development.  In the past I've just been working off a remote server and editing files in Dreamweaver. (which sucks for working with php :( )  "I is Newb" lol
Title: Re: Upgrade error: DatabaseMySQL::query: 1064 - You have an error in your SQL syntax
Post by: Milbo on October 19, 2011, 18:56:58 pm
Heyhoo cleanshooter, this should help you with the svn stuff http://dev.virtuemart.net/projects/virtuemart/wiki/Setting_up_a_Development_Environment.

The first error, great that you found it,.. I fixed it afaik. SEcond error als fixed