MySQL #164 error, Can't migrate to live server.

[Sirvonfloyd]

Hello Everybody.

I have finished a website in my localhost and im ready to migrate to a live server, I after uploading the files and creating the new database, I can't import the DB because of this error.


-- Database: `cppfcenter`
--

-- --------------------------------------------------------

--
-- Table structure for table `o4wau_assets`
--

CREATE TABLE `o4wau_assets` (
`id` int(10) UNSIGNED NOT NULL COMMENT 'Primary Key',
`parent_id` int(11) NOT NULL DEFAULT '0'COMMENT
) ;
MySQL said: Documentation

#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 ')' at line 14

Unfortunately Im not a BD knowledgeable, in fact very little know about data bases,
The live hosting is Shared hosting at Hostgator.
I make a copy of the DB file and I remove the line 14 but still no working, also after erasing the line 14 another error on line 4 came out.


Im Using.
Joomla 3.6.2
My SQL version 5.7.11
PHP Version 5.6.19
Template is Rockettheme Salient.

Please, I will appreciate any help in this issue.

Thank you in advance.

Carlos

[leolam]

You have a problem in your assets table. I advise you to spend that little money and install aclmanager.net which (which it's diagnostics tool) identifies and repairs all asset issues on your site in a nutshell and fully automated. It is very valuable since it also handles the full ACL. http://www.aclmanagerdemo.net/administrator/index.php

Leo

[Sirvonfloyd]

Leolam.
Thank you for your fast reply.
I will get into that, I was thinking in reinstalling the core joomla files, I wonder if that will work?

Thanks

[leolam]

If you point to the Joomla update component and the option to "reinstall Joomla core files" that won't fly since that only installs the files and folders and does not create the database entry. You might try in phpMyAdmin to repair the tables. Sometimes that helps but the assets table is corrupt or missing and that asset table needs repaired. For that the ACL-Manager Diagnostics is the perfect tool

Leo

[Sirvonfloyd]

Thanks Leo.
the people from hostgator, says that the problem is with the version of mysql, they are using version 5.5 and say that is the issue, and i cant upgrade because i have others websites hosted (shared hosting) and will potentially damage the other ones. they says I have to get a VPS or dedicated server, which i cant afford.
My question is, if I fix the table with ACL-Manager, still will be compatible with the older version of hostgator??

[leolam]

Nonsense by HG. They are just trying to sell you extra services. Totally unrelated to the PHP-version. It is a corrupt database (try "repair" in phpMyAdmin of the database in question) or a missing table which can be resolved with ACLmanager.

Joomla runs well on PHP 5.3.10 up so PHP 5.5.xx no issues at all


Leo

[Sirvonfloyd]

Leo.
Thank you
I just bough ACL manager, but they wont activate my account until the payment clear in their side, says will take 2 to 7 days, I dont understand why, while with any other paid extensions you can download immediately after you submit the payment.
I have contact them to see if my account can be activated asap since i have to upload this site today.

Thank you very much for your help, i will let you know if works

[leolam]

where did you buy this at aclmanager.net ?

[Sirvonfloyd]

Yes, I just got activated. )

[leolam]

ok cool otherwise I would have send Sander a message. He is very helpful

Leo

[Sirvonfloyd]

ok, ACL Manager found several errors, and fixed all. now according ACL have no errors all green and pretty.
I export the db after the fix and still.
-- Table structure for table `o4wau_assets`
--

CREATE TABLE `o4wau_assets` (
`id` int(10) UNSIGNED NOT NULL COMMENT 'Primary Key',
`parent_id` int(11) NOT NULL DEFAULT '0'COMMENT
) ;
MySQL said: Documentation

#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 ')' at line 14


Same error, getting crazy here,!! i check the table, i the line 14 doesnt exit, (eventually since i deleted last week)
any ideas??

[leolam]

Backup your database of the site (as well as the entire site with Akeebabackup) and run the following query which will replace the structure of your assets table

Code: Select all

DROP TABLE IF EXISTS `o4wau__assets`;
CREATE TABLE IF NOT EXISTS `o4wau__assets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `parent_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set parent.',
  `lft` int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set lft.',
  `rgt` int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set rgt.',
  `level` int(10) unsigned NOT NULL COMMENT 'The cached level in the nested tree.',
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The unique name for the asset.\n',
  `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The descriptive title for the asset.',
  `rules` varchar(5120) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'JSON encoded access control.',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_asset_name` (`name`),
  KEY `idx_lft_rgt` (`lft`,`rgt`),
  KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=207 ;

That should for sure resolve this

Leo

[Sirvonfloyd]

Hi Leo.
I manage to apply the fix and worked.
Thank you very much for your help, truly appreciated.

Regards
Carlos

[leolam]

Hi Carlos, Glad to be able to guide you through this. Job well done. (don't forget to backup!)

Leo