Error 500: CDbCommand failed to execute after backup

Discussion in 'Common issues' started by rritz, Apr 4, 2017.

  1. rritz

    rritz Member

    Joined:
    Jun 30, 2016
    Messages:
    163
    Likes Received:
    23
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    2
    Hello fellow mw users,
    I just had to reinstall my server, and I did a backup of mw afterwards.

    My setup: VPS running CentOS6 64x, custom installer script for mw and powermta.

    After running the installer I uploaded the entire mw folder per ftp

    In phpmyadmin deleted all tables in mailwizz database and imported my backup.sql

    The file was too big, so I had to split it. Although theoretically I could upload files up to 64MB big, the upload timed out on my 13MB zip file.

    I used SQL Dump Splitter, and imported the files. All went smooth, phpmyadmin reported all imports as successful.

    I opened mw and on first sight, all looks good. My lists are there, my campaigns are there.

    But whenever I try to open the info on a campaing, or try to go to page two of my campaigns, or try to set the number of campaigns shown I get this error:

    " Error 500: CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'operator.' in 'where clause' "

    Looks like mw is looking for a column that's not there? Can I find out what column that is and where it should be? I can see all my 79 campaigns in the mw_campaign table.

    Could the Dump Splitter have caused the problem?

    I guess I could try to import the database via bash line, although I am not sure where to find my database user and password

    I'd appreciate any help - Thxs !
     
  2. rritz

    rritz Member

    Joined:
    Jun 30, 2016
    Messages:
    163
    Likes Received:
    23
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    2
    Oh, just sw this: When I click on "groups" in the "campaign" tab I get the error:
    Error 500!
    The relation "campaignsCount" in active record class "CampaignGroup" is specified with an invalid foreign key. The columns in the key must match the primary keys of the table "mw_campaign_group".
     
  3. twisted1919

    twisted1919 Administrator Staff Member

    Joined:
    Dec 27, 2014
    Messages:
    10,477
    Likes Received:
    2,440
    To me it seems the import hasn't finished properly, maybe importing it from command line will help, which is not that hard:
    https://www.google.ro/webhp?sourcei...=UTF-8#q=mysql+import+sql+file+command+line&*

    If you go via FTP to your app, in apps/common/config folder and open the main-custom.php file, you have there the username, password and database name ;)
     
    rritz likes this.
  4. rritz

    rritz Member

    Joined:
    Jun 30, 2016
    Messages:
    163
    Likes Received:
    23
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    2
    Thank you @twisted1919 !
    In kb.mailwizz there is an article about migration and I think in the code there there's the line I need to import the db. Is that right?

    So I would navigate to /var/www/mwzz
    by running cd /var/www/mwzz

    Say I upload my sql file to /var/www and it is called backup.sql
    the I need to run this command:
    wget /var/www/backup.sql

    and then
    mysql -u MAILWIZZ_USER -p MAILWIZZ_DATABASE < backup.sql

    Correct? Sorry I am total ignorant of VPS and SSH but I try to learn.

    I read somewhere that the syntax should be
    mysql -uMAILWIZZ_USER -pMAILWIZZ_DATABASE < backup.sql (no spaces)

    and I also thought -p would be password?
    If I do not enter password on the command line - which is recommended I believe then I will get a prompt to enter the password ?

    Thank you all again! And I do hope that at some point some other users may find this post useful
     
  5. twisted1919

    twisted1919 Administrator Staff Member

    Joined:
    Dec 27, 2014
    Messages:
    10,477
    Likes Received:
    2,440
    This is correct.

    This is also very correct ;)

    Make sure your database is empty, then run the "mysql -u MAILWIZZ_USER -p MAILWIZZ_DATABASE < backup.sql" command and you'll be prompted for a password. Enter it and wait for the import to complete ;)
     
    rritz likes this.
  6. rritz

    rritz Member

    Joined:
    Jun 30, 2016
    Messages:
    163
    Likes Received:
    23
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    2
    Thxs a lot @twisted1919 !!

    I tried, waiting for half hour and more. Seems like import never gets finished. putty looks as if it is working. Never get a success or error msg there.
    I look into phpmyadmin and it seems at least as if import went ok. But the error persists

    btw I found out I had to enter absolute path for wget to work

    wget /var/www/backup.sql doesn't work
    wget http://www.mydomain.com/backup.sql does
     
    Last edited: Apr 5, 2017
  7. twisted1919

    twisted1919 Administrator Staff Member

    Joined:
    Dec 27, 2014
    Messages:
    10,477
    Likes Received:
    2,440
    It can take hours sometimes to finish, so let it run till the command returns in putty.
     
    rritz likes this.
  8. rritz

    rritz Member

    Joined:
    Jun 30, 2016
    Messages:
    163
    Likes Received:
    23
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    2
    Ok Thank you for letting me know! I didn't think it could possibly take that long :) lol I'll wait
     
  9. twisted1919

    twisted1919 Administrator Staff Member

    Joined:
    Dec 27, 2014
    Messages:
    10,477
    Likes Received:
    2,440
    @rritz - i once had an index update command run for around 46 hours, so it's totally possible, depends on your database size.
     
    rritz likes this.
  10. rritz

    rritz Member

    Joined:
    Jun 30, 2016
    Messages:
    163
    Likes Received:
    23
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    2
    ic .. and I have to keep putty open all of the time? I know this is a stupid question very likely, but the import is happening on my server and the server is online anyway. So will the operation abort if I close putty, or if my internet connection should fail during that time?
    Sorry again I am such a noob
     
  11. twisted1919

    twisted1919 Administrator Staff Member

    Joined:
    Dec 27, 2014
    Messages:
    10,477
    Likes Received:
    2,440
    Yeah, closing the connection will interrupt the action :-s So don't :p
     
  12. rritz

    rritz Member

    Joined:
    Jun 30, 2016
    Messages:
    163
    Likes Received:
    23
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    2
    I wonder how you can keep putty open long enough? I get network connection errors all the time, sometimes after only 30 minutes, sometimes after a couple hours. I read it can be either the internet connection, the router, my PC's OS, the firewall or some other unknown issues that close the connection. No idea where to start troubleshooting even.

    I have given up on that project - will have to find another way of restoring my installation
     
  13. frm.mwz

    frm.mwz Well-Known Member

    Joined:
    Mar 8, 2016
    Messages:
    3,720
    Likes Received:
    682
    S.E:
    2020-11-29 06:30:25
    L.T:
    Regular
    L.C:
    8
    You need to detach the command from the session,
    e.g.
    nohup /mwz/command.sh & disown
    this way, it will not hangup, and the command and session will be detached from each other by the "disown", i.e. the command will continue to run, even if the session is ended/hungup.
    You can open several putty sessions/windows and test it, e.g. run htop in one and start a send-campaign in another with the above, then close that session/window and see it continuing in the other. Once you are comfortable with its use, you can do your 'almost never ending' import ;)

    PS: There are also numerous settings in putty that help prevent from session/connection breakup.
     
    rritz likes this.
  14. rritz

    rritz Member

    Joined:
    Jun 30, 2016
    Messages:
    163
    Likes Received:
    23
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    2
    Thank you very much @frm.mwz ! I didn't find this info anywhere else, although I searched a lot - really helpful!!

    BTW - for anyone who might read this thread because she / he's stuck with the same problem -
    I finally managed to upload my sql file by:
    using SQL Dump Splitter and adding head and bottom to each split file to make it a complete sql file in itself
    PLUS adding SET foreign_key_checks = 0; to each file at the top
     
    frm.mwz and twisted1919 like this.

Share This Page