Database backup from Local Host to Cpanel

Respected Admin/Sandra,

I exported my gibbon database and imported to my cpanel .
But it shows following error

Configuration Error: there is a problem accessing the current Academic Year from the database.

Please help me solve this.

Best Regards,

Comments

  • Hello @ghulamabbas92,

    This is interesting. In your new database, in cPanel, can you see the table gibbonSchoolYear? If so, does it have any rows? If so, is one of them marked with the status "Current"?

    It sounds like the answer to at least one of these questions must be "No" in your case, where as they all should be "Yes" to have a working install.

    Cheers,

    Ross
  • Respected Admin,
    Thanks for reply .

    Answers to all these are yes as

    no of rows are 10
    and


    024 2018-2019 Current 10 2018-08-17 2019-03-31
  • When I export from one local host to another it works fine but when I import to Cpanel phpmyadmin it gives above error .

    Please suggest a solution
  • This is very odd. I am guessing you have some different mysql mode options selected on the cpanel server compared to your localhost. Are there any useful error messages in your mysql log?
  • edited October 2018
    Respected Admin/Sandra,

    I have checked the mode . I also changed gibbonSetting table for system address and absolute url. I am attaching the database. You are requested to please look into it and help me.
    database address
    [ Removed for privacy ]
    Best Regards,
  • Hi Ghulam Abbas,

    I've removed the dropbox link because it appeared to contain private data. I'll take a look at the file. After moving databases, did you update the Base URL/Base Path in the gibbonSettings table based on the new server location? A new server likely has a different path structure (eg: /var/www/ vs. something else on localhost)

    Otherwise, if a database works well in one install and not in the other it's often a setting in the server setup rather than the database.
  • Respected Sandra,
    Thanks for data protection.
    Each time I updated base URL and Absolute path.
  • Following error is coming now

    Warning: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ilearnw1_gibb541.gibbonSetting' doesn't exist in /home/ilearnw1/public_html/src/Database/Connection.php on line 191
    SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ilearnw1_gibb541.gibbonTheme' doesn't exist
  • Ah, that sheds some light on the issue. I've taken a quick look at your exported file, and all the table names have been changed to lower case. In the database file you shared gibbonSetting is exported as gibbonsetting. This is the same for every table. Is there a way to try your export again that doesn't change the case of these tables? Otherwise, the collation in your database tables should be utf8_general_ci (the ci standing for case-insensitive). However, it's not ideal to have all your tables in a different case than the Gibbon core. Are you using the same database software/version on your localhost as your server?
  • respected Sandra,
    On my local host all tables are in InnoDB and utf8-general-ci and all are in lower case.
    hOW CAN i CHANGE THEM TO UPPER CASE?
  • My webserver information
    Server Information
    Item Detail
    Hosting Package fh_Free
    Server Name cpanel
    cPanel Version 70.0 (build 18)
    Apache Version 2.4.34
    PHP Version 7.0.27
    MySQL Version 5.6.37
    Architecture x86_64
    Operating System linux
    Shared IP Address 195.201.179.80
    Path to Sendmail /usr/sbin/sendmail
    Path to Perl /usr/bin/perl
    Perl Version 5.16.3
    Kernel Version 4.17.10-1.el7.elrepo.x86_64
  • my local host

    Database server
    Server: 127.0.0.1 via TCP/IP
    Server type: MariaDB
    Server connection: SSL is not being used Documentation
    Server version: 10.1.36-MariaDB - mariadb.org binary distribution
    Protocol version: 10
    User: root@localhost
    Server charset: UTF-8 Unicode (utf8)
    Web server
    Apache/2.4.34 (Win32) OpenSSL/1.1.0i PHP/7.2.10
    Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $
    PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
    PHP version: 7.2.10
    phpMyAdmin
    Version information: 4.8.3 (up to date)
    Documentation
    Official Homepage
    Contribute
    Get support
    List of changes
    License
  • Hmm, perhaps this is something related to using MariaDB on your localhost and MySQL on your server. The tables shouldn't be all lowercase... Can you check to see what the collation is on the tables on your webserver? If you have phpMyAdmin on your webserver too you can see it in the structure tab for the database:


    I suspect it worked locally because you've mentioned your local collation is 'utf8-general-ci'. Ensuring the webserver tables match the same collation might help. If all else fails, you may need to find-replace the table names to the proper case in the .sql file before uploading it to your webserver.
  • Respected Sandra,
    I have checked collation on webserver is utf8-general-ci
  • Respected Sandra,
    How can I change case of the tables . Please elaborate method.


  • It looks like your issue may be related to a lower_case_table_names setting in your system:
    https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

    It appears Windows systems set lower_case_table_names to 1 by default, and Unix-based systems set it to 0. Based on the documentation above, try changing this value to 2 on your localhost (& restarting it). Here's a link that has some instructions:

    https://stackoverflow.com/questions/2659684/why-my-tables-names-are-converted-into-lowercase-in-phpmyadmin

    After you've changed your lower_case_table_names setting, the tables likely need updated on your localhost. I've attached an SQL snippet that when run should fix the case of your table names (for all the core tables, you will want to check & rename any tables from other modules).

    After that, export from your localhost and check the SQL file. You should see the correct case for table names. From there you should be good to upload it to your webserver.
  • Respected Sandra,
    Thanks for identifying issue. I am having trouble in finding lower_case_table_names in my.ini.

    I have looked into config files but there is no lower_case_table_names .

    Please help me where to find lower_case_table_names


  • Respected Sandra,

    Thanks for the great effort you made finally I found solution .

    I manually entered lower_case_table_names = 2 in my.ini file

    then I used your file for renaming table but it worked by changing names by appending 2 and then removing 2


    Thanks
    sandra
  • Awesome, glad to hear you got it working!
  • Actually after setting lowercase table names to 2 . When I change gibbonsettings to gibbonSettings ,It does not change .but I changed by Rename gibbonsettings to gibbonSettings2 and Then gibbonSettings2 to gibbonSettings.

    Hope this help others facing same issue .

    Thanks again to intelligent Sandra Kuipers
Sign In or Register to comment.