ERROR: Unable to load SQL-Dump into database table.

Discussion in 'Developers' Forum' started by rrhode, Mar 27, 2009.

  1. rrhode

    rrhode New Member

    Hello there,

    I get this error when trying to install ISPConfig 3 following the Perfect Server for CentOS 5.2.

    I can connect to the database through phpmyadmin and from the command prompt.

    ispconfig_install.log says:

    I can manually import that sql file into the database (which it actually creates before giving the error and quitting) but then it says there are already tables in the database and quits.

    This is what my mysql.user table looks like.

    Code:
    localhost 		root 	xxxxxxxxxxxxxxxx	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	  	[BLOB - 0 B] 	[BLOB - 0 B] 	[BLOB - 0 B] 	0 	0 	0 	0
    server1.mydomain.com 	root 	xxxxxxxxxxxxxxxx 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	  	[BLOB - 0 B] 	[BLOB - 0 B] 	[BLOB - 0 B] 	0 	0 	0 	0
    127.0.0.1 		root 	  			Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	  	[BLOB - 0 B] 	[BLOB - 0 B] 	[BLOB - 0 B] 	0 	0 	0 	0
    I tried deleting all of them but one and setting its host to % but that still gives the same error. I have tried quite a number of things but obviously nothing thats right yet.

    The only thing that is probably related is when I was on page 5 (http://www.howtoforge.com/perfect-server-centos-5.2-ispconfig-3-p5) near the top on the 2nd step it did the first command but the 2nd command said I had no access. Then neither commands would work anymore.

    This command failed saying there were no permissions.
    mysqladmin -h server1.mydomin.com -u root password mypassword

    Any idea what I should do?

    Thanks for your time =)
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Make sure that you used the mysql root user and not any other user and that the mysql root password does not contain any special chars or contrl characters.
     
  3. rrhode

    rrhode New Member

    Hi Till,

    Thank you for your quick response =)

    Well I am pretty sure its the mysql root user that I am using. I was able to modify the password for it on the first line of the command on page 5: mysqladmin -u root password yourrootsqlpassword

    My password has only letters and numbers.

    At that point line 2 told me access was denied or something. Maybe this was it: error: 'Access denied for user 'root'@'server1.mydomain.com' (using password: NO)'

    It is the same one I used to log into phpmyadmin and to test the connection via the command line. I did mysql -h localhost -u root -p and it asked me to type my password so I typed it and this is its output:

    So I don't really know why it would connect this way and phpmyadmin but not in the install.

    I did manage to get a different message for you that might help. But it still doesn't make sense because I can import the entire thing perfectly fine in phpmyadmin.

    However when I go to the command line and type this:
    It produces this error:
    I am pretty much clueless about that error. I haven't modified the sql file at all.

    Anyway I managed to get past it by removing code inside installer_base.lib.php inside the configure_database function. (Lines 148-161 and line 167)

    I now have ISPConfig 3!!! woohoo =)
     
  4. Leow

    Leow New Member

    My question goes to the original first question by this poster. (rrhode)

    Unable to load SQL-Dump into database table


    While I was installing ISPConfig 3..

    I am not sure about the solution as said by rrhode:

    Anyway I managed to get past it by removing code inside installer_base.lib.php inside the configure_database function. (Lines 148-161 and line 167)


    How am I exactly going to do that and maybe there are other ways? Thank you very much..
    :eek:
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    First, which is the exact ISPConfig version are you using? Please be aware that the svn version can only be used on servers were you are actively programming in ispconfig as it can be broken at any time. So please post only issues from released versions or beta / rc versions or at least say that you used the svn version and then post to the development forum as your problems are not realted to any version that was released.

    Removing these lines simply results in a incomplete setup. Now you have ISPConfig with an empty or not updated database which is completely useless.
     
    Last edited: Mar 28, 2009
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    He used a debelopment version (SVN), so what he said is not related to any released version or to any production setup of ISPConfig. Also his solution leaves a unusable and broken system as commenting out the code that ibstalls the database is not a solution of course.

    If you encounter this problem, make sure thyt you downloaded the latest non svn relaese from sipconfig.org.
     
  7. Leow

    Leow New Member

    Hi till,

    Thanks I am going to get the non-SVN version, sorry I did not mention it.

    Could you post some link maybe including the command needed to install the ISPConfig?

    I have a link which have not tried yet, I will do it now..and post back if the problem persists which I do not think so since it is the SVN fault. Thank you.

    No more issue with ISPConfig 3 after downloaded from non-SVN. The error message did not come up..

    Thanks..
     
    Last edited: Mar 28, 2009
  8. rrhode

    rrhode New Member

    Ohhh I get it now. I downloaded the release version of ISPConfig 3.0.1 but I did the svn export to the install directory as in the Perfect Server guide. I guess I did not read clearly enough. I just did the steps and didn't realize there was an option at that point.

    I manually imported the sql file to phpmyadmin. The lines I removed from the file seem to only try to import the database. I don't believe they do any sort of updates to it but if they do then I guess my install is messed up anyway.

    So far ISPConfig seems to be working. There are some issues with it but I have no doubt they will be fixed in upcoming releases. And since I apparently have an SVN version thats why there are issues.

    Thanks for clearing that up for me =)
     
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    This will not work because the installer changes several values in the datbase after the import so that ispconfig knows the details of your server configuration. You should uninstall ispconfig with the unistall.php script that you can find in the install folder and then reinstall ispconfig 3.0.1 from the tar.gz
     
  10. rrhode

    rrhode New Member

    Thank you again =)

    There were some issues with the uninstall which I am guessing the reason is because I am running CentOS and not Debian but I don't know Debian.

    uninstall.php had: /etc/init.d/mysql
    my system had: /etc/init.d/mysqld

    uninstall.php had: /etc/apache2
    my system had: /etc/httpd/conf
     
  11. till

    till Super Moderator Staff Member ISPConfig Developer

    There is also a fedora uninstall script there which should work better with centos.
     
  12. rrhode

    rrhode New Member

    I just noticed that =) Thats awesome.
     
  13. Laur Balaur

    Laur Balaur New Member

    Hello Till,
    I have the same problem : when I run the install script after i answer the multiserver question error is:
    Shall this server join an existing ISPConfig multiserver setup (y,n) [n]:
    ERROR: Unable to load SQL-Dump into database table.
    ISPConfig install log said:
    /tmp/ispconfig3_install/install/lib/installer_base.lib.php, Line 167: WARNING: could not read in ispconfig3.sql
    I try all the steps above but no success . Installer create a blank dbispconfig database only with ISPConfig 3.0.5.4 and a database with 5 tables with ISPConfig 3.0.5.4p8 .
    I user mysql server 5.7.11 with php 5.6.18 .Mysql use mysqlnd for mysqli .If I import ispconfig3.sql manually is working but the database is empty . I try ISPConfig 3.0.5.4p8 si 3.0.4.5.4 version .
    My mysql root /password are Ok , I can connect to server via shell or phpmyadmin .
    The funny thing is if I use in installation process an external mysql server ( mysql 5.7.11 anf php 7.0) the install process work fine , the database is created Ok, and if I import that complete database to my main server ISPConfig seems to work fine but I am not sure ...
    I am using Centos 7.2 .
    I think is a php ---mysql issue but all my checks are Ok .
    Can you help me ?
    Thank you .
     
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    Most likely a problem with MySQL 5.7 as this MySQL version is not fully compatible with MySQL dumps that were made for older MySQL releases. Ensure that MySQL is not running in SQL mode "STRICT_TRANS_TABLES".

    SQL mode should be e.g.:

    sql_mode=NO_ENGINE_SUBSTITUTION

    but not:

    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
     
  15. Laur Balaur

    Laur Balaur New Member

    Hello Till,
    Thank you for your answer .Avoiding STRICT_TRANS_TABLES solved my problem , ISPConfig work locally and the database is complete .
    I receive in mysqld log an notice:
    2016-03-14T14:42:13.450185Z 41 [Note] Aborted connection 41 to db: 'dbispconfig' user: 'ispconfig' host: 'localhost' (Got an error reading communication packets)
    2016-03-14T14:42:13.450862Z 42 [Note] Aborted connection 42 to db: 'dbispconfig' user: 'ispconfig' host: 'localhost' (Got an error reading communication packets)
    2016-03-14T14:42:13.450928Z 43 [Note] Aborted connection 43 to db: 'dbispconfig' user: 'ispconfig' host: 'localhost' (Got an error reading communication packets)
    Can I resolv this log issue ? My mysql log is full with this notes .
    Thank you .
     
  16. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    Can you login into mysql with the credentials from /usr/local/ispconfig/server/lib/config.inc.php and /usr/local/ispconfig/interface/lib/config.inc.php ?
     
  17. ztk.me

    ztk.me Active Member

    This is unlikely an auth issue, more a "received unexpected command / package" error. Some other rare conditions can be idle time, size of used buffers and probably some other kinds of configurations.

    It could be interesting if there's an issue with the used mysql client lib and server - would recommend to tcpdump the communication to see where it drops.

    Edit: Did you try recompiling PHP using the mysql 5.7 headers?
    Edit2: Also mysql-nd driver behaves a bit different, some code changes may be required to solve the same result set fetching as with default mysql(i) driver.
     
    Last edited: Mar 14, 2016
  18. till

    till Super Moderator Staff Member ISPConfig Developer

    And you should be aware that ispconfig is not the only software that connects to dbispconfig database, the same user and login is used by postfix, dovecot, amavis and pure-ftpd, so any of these might cause this. In the past, I've seen that postfix tends to not close connections and therefore they time out.

    So while you can debug this, I would just consider to disable logging of "Notes" in MySQL as these are not errors.
     
    ztk.me likes this.
  19. Laur Balaur

    Laur Balaur New Member

    Yes, credential from both config file work fine, I can log to mysql server like root and ispconfig user .
     
  20. Laur Balaur

    Laur Balaur New Member

    I don't recompile PHP , I want to do this like a ultimate solution .
    I'll try to switch back to standard php-mysql driver and check the log , I switch to mysql-nd driver to resolve a mysql warning about different version of API between php and mysql . If the switch back resolve the "note" issue I'll let you know .
    Thank you for your idea .
     
    ztk.me likes this.

Share This Page