Problem in importing large sql file in phpmyadmin

Problem in importing large sql file in phpmyadmin

by solmaz naderi -
Number of replies: 6

Hi everyone

My sql file is 1.5 G and have problem in importing DB. I have tried many way but I confused what is solution.

 I have changed related lines in php.ini :

max_execution_time = 300000

max_input_time = 50000

max_input_vars = 50000

memory_limit = 1500M

post_max_size = 1500M

and changed phpmyadmin line code:

\phpmyadmin\libraries\config.default.php

 $cfg['ExecTimeLimit'] = 300; to $cfg['ExecTimeLimit'] = 0;

But get following error:


Script timeout passed, if you want to finish import, please resubmit the same file and import will resume

-- Table structure for table `mdl_backup_controllers`

--


CREATE TABLE `mdl_backup_controllers` (

  `id` bigint(10) NOT NULL,

  `backupid` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',

  `operation` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'backup',

  `type` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',

  `itemid` bigint(10) NOT NULL,

  `format` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',

  `interactive` smallint(4) NOT NULL,

  `purpose` smallint(4) NOT NULL,

  `userid` bigint(10) NOT NULL,

  `status` smallint(4) NOT NULL,

  `execution` smallint(4) NOT NULL,

  `executiontime` bigint(10) NOT NULL,

  `checksum` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',

  `timecreated` bigint(10) NOT NULL,

  `timemodified` bigint(10) NOT NULL,

  `controller` longtext COLLATE utf8mb4_unicode_ci NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='To store the backup_controllers as they are used'--

-- Dumping data for table `mdl_backup_controllers`

--


INSERT INTO `mdl_backup_controllers` (`id`, `backupid`, `operation`, `type`, `itemid`, `format`, `interactive`, `purpose`, `userid`, `status`, `execution`, `executiontime`, `checksum`, `timecreated`, `timemodified`, `controller`) VALUES

(1, 'b7e8510b65f2f3023f653fb4fc1de61e', 'backup', 'course', 862, 'moodle2', 0, 10, 6, 1000, 1, 0, '70c78a4ecb57a1a1da62a462226dec0e', 1593496248, 1593496251, '')



would you help me how can I fix that?

Thanks in advance

Solmaz

Average of ratings: -
In reply to solmaz naderi

Re: Problem in importing large sql file in phpmyadmin

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Yes, importing large sql backup files using phpMyAdmin can be problematic. Have you tried from the MySQL command line? There are many forms of the command, but they are similar to this one:

mysql -udbusername -p moodledbname
Average of ratings:Useful (1)
In reply to Rick Jerz

Re: Problem in importing large sql file in phpmyadmin

by solmaz naderi -
Fiiiiiixed!!!!!!
Thanks dear Mr. Jerz
Average of ratings: -
In reply to solmaz naderi

Re: Problem in importing large sql file in phpmyadmin

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Great to hear this, Solmaz. I tend to like to drop all tables before doing an import.
Average of ratings:Useful (1)
In reply to Rick Jerz

Re: Problem in importing large sql file in phpmyadmin

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
I forgot to mention that I do use phpMyAdmin to drop all tables because I find it to be the fastest way. I find it to be the fastest. As far as I know, MySQL does not have a "drop all tables" command, but maybe someone else will know more about this.
Average of ratings:Useful (1)
In reply to Rick Jerz

Re: Problem in importing large sql file in phpmyadmin

by solmaz naderi -
I did not find this option ("drop all tables" ) in the MySQL. But even without that option, the problem will be solved.
Also I have added following lines in my.config:

[mysqld]
innodb_file_format = Barracuda
innodb_large_prefix = 1
innodb_file_per_table = ON

I do not know how effective above codes, but no more errors were observed and importing was done correctly.
Average of ratings: -
In reply to solmaz naderi

Re: Problem in importing large sql file in phpmyadmin

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Editing my.cnf is a correct thing to do, but it's tricky. And it depends upon the version of MySQL or MariaDB that you have.

However, tweaking my.cnf can help improve the performance of your Moodle, so it is worth exploring.

There is some guidance in the Moodle docs. I suggest that when you try a change, do it one line at a time.

I am going to attach a copy of my.cnf for you to see. Don't take my my.cnf and copy it to yours! Read what I am doing, read other resources for your db, see if you might agree with my setting, try it, and see if it improves. But be very careful as you learn and progress. Have a backup of your moodle, just in case. I have tried to document my my.cnf to help.

Average of ratings: -