Sunday, March 25, 2012

error while importing DB in mysql

I upgraded my local testlink mysql server to 5.5.20, and I was trying to import testlink DB to the new version, I was facing an error

error:

#1100 - Table 'pma_column_info' was not locked with LOCK TABLES

I tried to look for solution for it online, found some people suggesting commenting the following line in PhpMyAdmin config file

$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';

tried this, but the import process with giving this time different error

#1146 - Table 'testlink.pma_tracking' doesn't exist

I tried commenting all Advanced phpMyAdmin features, still the import wasn’t working, with different error this time

I read in some forums that I need to install DB create script for advanced features located in

C:\Program Files (x86)\EasyPHP-5.3.9\modules\phpmyadmin\scripts\create_tables.sql

still I was having errors while importing

I noticed in PhpMyConfig file that there are 2 lines for advanced features user name and password, and the lines were commented, I uncommented these 2 lines, uncommented the advanced features configuration, set the user name and password for the root user, saved the config file, then tried to import, worked perfect

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'root';
$cfg['Servers'][$i]['controlpass'] = 'password';

17 comments:

  1. Thanks. It's awesome and helpful

    ReplyDelete
  2. Hi

    That was very helpful
    I change the user and pass and it solve this issue

    tnx again

    ReplyDelete
  3. Thanks for help! Seems like very few people really know what's up with this error - glad you found a solution.

    ReplyDelete
  4. Thank you. Had the same problem with import of the backup from hosting company to phpMyAdmin (I suspect that it was because it was using "Lock Tables").

    Adding those two lines helped (actually I had to add those lines to config.inc manually, were not present as comments).

    Documentation.txt in phpMyAdmin says more about the purpose of those lines:

    This special account is used for 2 distinct purposes: to make possible all
    relational features (see $cfg['Servers'][$i]['pmadb']) and, for a MySQL
    server running with --skip-show-database, to enable a multi-user
    installation (HTTP or cookie authentication mode).

    When using HTTP or cookie authentication modes (or 'config' authentication
    mode since phpMyAdmin 2.2.1), you need to supply the details of a MySQL
    account that has SELECT privilege on the mysql.user (all columns except
    "Password"), mysql.db (all columns) and mysql.tables_priv (all columns
    except "Grantor" and "Timestamp") tables. This account is used to check
    what databases the user will see at login.
    Please see the install section on "Using authentication modes" for more
    information.

    ReplyDelete
  5. Thank you so much! =)

    ReplyDelete
  6. If you're using EasyPHP WAMP, the mentioned file is located in:
    \EasyPHP-12.1\modules\phpmyadmin123456789

    ReplyDelete
  7. what is the name file ? we need to edit in \EasyPHP-12.1\modules\phpmyadmin123456789

    ReplyDelete
    Replies
    1. File name is config.inc.php, located inside phpmyadmin folder
      in my easy php version, it is located in \EasyPHP-5.3.9\modules\phpmyadmin349x120325015958

      Delete
  8. Very helpful, thank you

    ReplyDelete
  9. Thank you, awesome! it helped me out a lot!

    ReplyDelete
  10. thanks, helps to me!

    ReplyDelete
  11. Just had this issue with Mediawiki. In my case, the default value for both of those fields was $dbuser and $dbpass (or something along those lines), which I did change to 'root' and 'mypasswordhere' respectively.

    ReplyDelete
  12. It worked for me as well, thank you very much for sharing.

    ReplyDelete
  13. Hello Thanks, It worked for me :

    $cfg['Servers'][$i]['controluser'] = 'root';
    $cfg['Servers'][$i]['controlpass'] = '';

    Because in EasyPHP-DevServer-13.1VC9, root has no password.
    thank you

    ReplyDelete
  14. Thank's for sharing information

    ReplyDelete