Database backup from Local Host to Cpanel

Administration
1

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,

2

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

3

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

4

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

5

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?

6

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,

7

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.

8

Respected Sandra,
Thanks for data protection.
Each time I updated base URL and Absolute path.

9

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

10

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 <code class="CodeInline">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?

11

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?

12

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

13

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

14

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.

15

Respected Sandra,
I have checked collation on webserver is utf8-general-ci

16

Respected Sandra,
How can I change case of the tables . Please elaborate method.

17

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.
18

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

19

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

20

Awesome, glad to hear you got it working!