arrow_upward

Pages (2):
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to fix mysql errors in phpmyadmin when importing a database
#1
@Mashiro

Not sure whether you can help in this. I Googled the issue almost to death and didn't get anywhere. I'm a real noob with tinkering under the phpmyadmin dashboard. My limits are editing user and domain info and passwords.

Today I was testing the functionality of cpanel. So while doing that I thought to reinstall an old phpBB Forum I'd rolled up a few months ago. I thought that would give a few functions of the panel a good workout.

Add on domain worked great. Folders were fine. Except interestingly, in this particular cpanel the folder for the add on domain was created on the same level as public_html and not as a sub-directory inside public_html.

Next workout was to create an empty mysql database and to import my old phpBB database into it. And that's where I got stuck. I did everything correct. According to the book. However an error came up about using "pass" instead of "password" (refer exact wording below). I get it that the wording has changed in more recent databases to full password. However, how does one fix that error in the backup SQL database one imports. I tried to tick a box during the import so it would ignore the error, and it did complete the import then (or so it looked), however when I checked there was zero MB content in it - it basically only imported the structure of the database. Is there a way that I could edit an old database where "pass" is used instead of "password", or is there a database checker somewhere where one can fix errors before one imports the database?

BTW I tried this in two different host's cpanels and the exact same errors came up. This is the error that came up:

Quote:Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.
Terminal
Thank you to Post4VPS and VirMach for my awesome VPS 9!  
#2
I found this article on stackoverflow that describes one potential fix for the error described above.

To paraphrase the article, it suggest that the issue is an error in your my.cnf file.

you'll go into your etc/my.cnf and change

pass=********


to
password=********

see if that makes any difference Smile
#3
(11-25-2020, 09:41 PM)fitkoh Wrote: I found this article on stackoverflow that describes one potential fix for the error described above.

To paraphrase the article, it suggest that the issue is an error in your my.cnf file.

you'll go into your etc/my.cnf and change

pass=********


to
password=********

see if that makes any difference Smile

Thanks @fitkoh I saw that suggestion too. There were also a few others, no doubt copied from the one in stackoverflow. But with most shared hosts one doesn't have access to SSH into the server - for good reason. You can only work with what is provided by the author of WHM through cpanel. So I was hoping to find a solution in phpmyadmin - or a facility outside on the Web where one could test one's database first and there are tools for fixing errors before one imports it.
Terminal
Thank you to Post4VPS and VirMach for my awesome VPS 9!  
#4
(11-25-2020, 10:13 PM)deanhills Wrote: Thanks @fitkoh  I saw that suggestion too. There were also a few others, no doubt copied from the one in stackoverflow. But with most shared hosts one doesn't have access to SSH into the server - for good reason.  You can only work with what is provided by the author of WHM through cpanel.  So I was hoping to find a solution in phpmyadmin - or a facility outside on the Web where one could test one's database first and there are tools for fixing errors before one imports it.
I did some googling myself and wasn't able to find any quick and easy solution. The best option I can think of would be to import it into a system where you do have ssh access and then see if you can make an updated export.
#5
Error? What error? I don't see any errors. At best it is a warning to act now to prevent issues in the future.
Quote:Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.

The warning is telling you that the passwords in your backup script should no longer use the pass prefix and instead use the password prefix. The pass prefix is deprecated and will be removed in future versions of MySQL servers or phpMyAdmin. Once that is removed and you try to import something with the pass prefix only then you will actually get errors because MySQL or phpMyAdmin will not recognize the pass prefix as a valid operation.


You mention it is an old forum. So it must have been from a server that used a much older MySQL server version and probably a much older phpMyAdmin version. So the backup of the databases uses an older style / structure where the pass prefix was still a up to date and widely used thing.

sql backups are usually plain text files. You can open them with a advanced text editor like Notepad++ and do "search and replaced" for pass to passsword prefix. If the backup is something like xyz.sql.bz or xyz.sql.gz you simply have to extract the archive to get the .sql file.

I would recommend to take a look into the source of the .sql database file and find the places that use the pass prefix.


Regarding your phpMyAdmin only having imported the basic structure but not the actual data... ehm. There is an option for that when importing but I don't remember how it is called. It's been a few years since I used phpMyAdmin or similar applications. I did experience the same issue and found the option by trial and error.

I think so... at least. It's been long ago.
[Image: zHHqO5Q.png]
#6
You were dead right @Mashiro.  The simplest solution was to edit the sql file.  I thought I couldn't do it manually and that I had to look for a conversion tool somewhere.  Finally after hours of trying to find a solution how to make the changes, like a database fixer, it turned out to be a very simple solution.

Step 1:  Unzip the .sql.gz folder.
Step 2:  Open the .sql file in NotePad ++
Step 3:  The first line in the sql data base said:

Quote:Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.

I removed that line

Step 4:  I did find = pass and replace = password  

I had to massage it though as there were instances of password and NotePad then doubled it up.  So I had to use different ways of cleaning it up until when I tried find = pass again I found all instances to be a clean password replacement.

Step 4: I then saved the file.

Then when I imported it to the cpanel phpmyadmin, I didn't do it in zipped, but in .sql format.  And there were no issues.  It imported perfectly this time round.

What a relief!  And another lesson learned for a phpmyadmin noob.  I was looking for complicated solutions, and it was dead simple in the end.   Tongue
Terminal
Thank you to Post4VPS and VirMach for my awesome VPS 9!  
#7
Great to hear to you got it running. Many database engines create clear text backup files or rather said dumps. On import the file is just loaded and the lines inside it are executed. That's mostly it. So you can basically do this kind of fixes on many database engines that are based on SQL. Some like sqlite might need a small tool like a sqlite editor/browser. It's bascially just encoding the binary form of the sqlite database file and turns it into human readable code and backwards.

We sometimes always seem to be looking for complicated solutions to a issue that is easy to solve (we = humans).
[Image: zHHqO5Q.png]
#8
@Mashiro  I had an interesting experience tonight with the database of my phpBB Forum.  I've been trying to upgrade my previous installation 3.0 to 3.3, and everything went well except for upgrading the database.  So tonight I deleted everything and thought to start again.  One thing I did was to go to the original database before I edited it in NotePad ++.  I only deleted the error note about replacing pass with password.  I didn't do any of the replacements.  And woe behold.  That worked too.  No errors came up when I imported the database.

I'm still uploading all of the folders after I deleted the installation completely.  However, while I'm waiting I'm wondering whether the database issue could have something to do with my php version and the mysql driver indicated in my config.php file.  The version of my php is PHP 7.3.17RC1 (cli) (built: Mar 31 2020 15:20:37) ( NTS )

The version of my mysql is mysql  Ver 14.14 Distrib 5.5.62, for Linux (x86_64) using readline 5.1

Here are the config file contents that are needed - the driver says mysql should it be mysqli? I found the documentation below for phpBB drivers:

https://area51.phpbb.com/docs/code/3.2.x...river.html

Quote:// phpBB 3.2.x auto-generated configuration file
// Do not change anything in this file!
$dbms = 'phpbb\\db\\driver\\mysql';  
$dbhost = 'localhost';
$dbport = '';
$dbname = 'database name';
$dbuser = 'user name';
$dbpasswd = 'password';
$table_prefix = 'phpbb_';
$phpbb_adm_relative_path = 'adm/';
$acm_type = 'phpbb\\cache\\driver\\file';

@define('PHPBB_INSTALLED', true);
// @define('PHPBB_DISPLAY_LOAD_TIME', true);
@define('PHPBB_ENVIRONMENT', 'production');
// @define('DEBUG_CONTAINER', true);
Terminal
Thank you to Post4VPS and VirMach for my awesome VPS 9!  
#9
(11-27-2020, 06:42 PM)deanhills Wrote: @Mashiro  I had an interesting experience tonight with the database of my phpBB Forum.  I've been trying to upgrade my previous installation 3.0 to 3.3, and everything went well except for upgrading the database.  So tonight I deleted everything and thought to start again.  One thing I did was to go to the original database before I edited it in NotePad ++.  I only deleted the error note about replacing pass with password.  I didn't do any of the replacements.  And woe behold.  That worked too.  No errors came up when I imported the database.

I'm still uploading all of the folders after I deleted the installation completely.  However, while I'm waiting I'm wondering whether the database issue could have something to do with my php version and the mysql driver indicated in my config.php file.  The version of my php is PHP 7.3.17RC1 (cli) (built: Mar 31 2020 15:20:37) ( NTS )

The version of my mysql is mysql  Ver 14.14 Distrib 5.5.62, for Linux (x86_64) using readline 5.1

Here are the config file contents that are needed - the driver says mysql should it be mysqli?  I found the documentation below for phpBB drivers:

https://area51.phpbb.com/docs/code/3.2.x...river.html

If the  phpbb community is to be believed, you should be able to use mysqli. https://www.phpbb.com/community/viewtopic.php?t=2403011

mysqli is also listed as a requirement for version 3.3 https://www.phpbb.com/support/docs/en/3.3/ug/

The upgrade guide seems pretty involved for the 3.0 to 3.3 transition: there's several steps, which means it's easy to make mistakes. - it wouldn't hurt to take a glance at it while you're waiting for your uploads to finish if you haven't already.  https://www.phpbb.com/support/docs/en/3..../upgrade3/
#10
@deanhills

Sorry for the late reply to your new message.

I'm afraid I'm no help in regards to this subject. The last time I touched phpBB was maybe about 10 years ago (+/- a few years). I guess since that time a lot has changed.

Generally said the PHP version could be an issue if the phpBB version doesn't support it but otherwise PHP won't be an issue for database related parts of phpBB. The phpBB requirements site that @fitkoh linked says phpBB 3.3 needs PHP 7.1 or higher. So you actually should be fine here.

The database version you have also seems to be fine as phpBB 3.3 requires MySQL 4.1.3 or MariaDB 5.1 or higher. You have 5.5.62. However as you noticed phpBB requires the mysqli driver while your own configuration still uses the mysql driver.

Basically what @fitkoh has linked to / already said in one or another way.


Edit: That said such major version upgrades over a long time are always a huge pain to deal with. Too many changes in code and database structure and etc.
[Image: zHHqO5Q.png]
Pages (2):
lockThread Closed 


Possibly Related Threads…
Thread
Author
Replies
Views
Last Post
2,130
11-27-2020, 02:53 AM
Last Post: Rehan
2,039
10-07-2020, 07:33 PM
Last Post: deanhills
2,071
10-12-2019, 03:29 PM
Last Post: Rzarcasm

person_pin_circle Users browsing this thread: 1 Guest(s)
Sponsors: VirMach - Host4Fun - CubeData - Evolution-Host - HostDare - Hyper Expert - Shadow Hosting - Bladenode - Hostlease - RackNerd - ReadyDedis - Limitless Hosting