Jump to:

10979 Posts in 2722 Topics by 1815 members

All other Modules

SilverStripe Forums » All other Modules » DB Plumber import / export

Discuss all other Modules here.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1 2
Go to End
Author Topic: 2502 Views
  • swaiba
    Avatar
    Forum Moderator
    1788 Posts

    DB Plumber import / export Link to this post

    Hi,

    I have tried to use the db plumber to 1) export from live and 2) import locally.
    The export seems fine because there is a large amount of sql produced, but the import locally is failing...

    I select the sql file created on the sever in 'Import SQL File' then I click 'Import' and it redirects me to 'localhost/mywebstie/#' (in chrome) or it just says SQL executed - but it clearly has not (firefox).

    Also the if I try using phpmyadmin to import the file it fails on the first line... which I guess is due to the use of double quotes...

    DELETE FROM "BannerAds";

    Any help / suggestions will be much appreciated.

    Barry

  • apiening
    Avatar
    Community Member
    55 Posts

    Re: DB Plumber import / export Link to this post

    hi swaiba,

    exactly. db plumber uses the silverstripe db connection which is setup to use double quotes for identifiers which is ansi compliant and works accross different rdbms whereas mysql (and phpmyadmin) by default use backticks. if you want to execute your db plumber sql in phpmyadmin you have to tell mysql to use ansi compliant syntax first. add

    SET sql_mode = 'ANSI';


    as the first line of your dbplumber dump.

    that should do.

    good luck and keep us posted on your progress

    p.s.: what was the error you got when reading the db plumber dump with db plumber?

  • swaiba
    Avatar
    Forum Moderator
    1788 Posts

    Re: DB Plumber import / export Link to this post

    Hi Andy,

    thanks for the set sql_mode - that did mean that phpmyadmin was able to start to execute it. Then I had a different issue... it seems that my live site has tons of records that broke a RecordID_Version constraint... on Page_versions (or CustomPage_version) for each of these pages...

    SELECT RecordID,Version,count(*)
    FROM Page_versions
    GROUP BY RecordID,Version
    HAVING count(*)>1;

    ...but that is fine I just dropped the local constraints and it all happily imported using phpmyadmin.

    Is this something you have seen before? If so is it possible to add something to drop all constraints at the beginning of the script and then re-enable at the end? (as an option naturally) Also an option for adding the setting of ANSI mode?

    Basically I can now export and re-import - it would be great if those road bumps could be ironed out so that it wouldn't take any effort to import/export apart from a couple of clicks.

    >> p.s.: what was the error you got when reading the db plumber dump with db plumber?

    I don't get an error when using chrome or safari, it just redirects to 'localhost/mywebstie/#' - so it doesn't seem to do any import it just shows the homepage with a '#' appended. (Maybe some sort of JavaScript issues?) I'm using wamp on windows if that helps.

    When using FireFox it just says SQL executed - but it has not as I truncate a simple table first - and it is not re-filled - so it seems to just return true without doing anything.

    Thanks again Andy for your help this is much appreciated!

    Barry

  • swaiba
    Avatar
    Forum Moderator
    1788 Posts

    Re: DB Plumber import / export Link to this post

    Hi again,

    While I am clearly chatting to silverstripe db expert - I wonder if there is any advice you might have on cleaning a database to remove xtra fields and tables that are no longer required... now I have the import export running fairly smoothly (minus having to add the sql set mode and use phpmyadmin)... the key areas of failure now are the tables or fields that have been temporarily created by changing a dataojbect, then doing a build, then removing the code.

    I've manually dropped several columns and Table_ToTable relation tables, but is there a way of "cleaning" a database during a build so that it removes these old pieces of data OR a simple modification to the exported script to handle these differences?

    IF EXISTS <table> DROP <table>;
    CREATE <table> <blah blah blah>;

    Barry

  • apiening
    Avatar
    Community Member
    55 Posts

    Re: DB Plumber import / export Link to this post

    hi barry,

    unfortunately not. obsolete tables get marked as such and can be deleted if you are certain that you don't need them anymore. there is no automatic removal of tables. the same applies to columns but they don't get marked. sapphire leaves the decision about what data can be deleted without harm to the developer.

    by the way i added the ansi switch to the export for mysql so you don't have to add it manually. (r110939)

    did you figgure out what caused the error during your import / export operation? can you isolate the sql command that breaks the import? it is nagging me that there is a bug...

    cheers

  • apiening
    Avatar
    Community Member
    55 Posts

    Re: DB Plumber import / export Link to this post

    i think i found it. the lexer was choking on parenthesis inside string literals.... (r110943)

    can you check and confim? (make sure you checkout the most recent revision, the zipped packages sometimes take a while before they get updated...)

    cheers

    andy

  • swaiba
    Avatar
    Forum Moderator
    1788 Posts

    Re: DB Plumber import / export Link to this post

    Hi Andy,

    Sorry to say that I get the same result on both chrome (redirected to host/sitename/# (without any sql processed) and in Firefox again the 'SQL Executed' message flashes in green box, but nothing has actually happened.

    I can confirm that I have the release with the added 'SET sql_mode = 'ANSI';' and the 'no records' message in the sql section.

    I have place some logging inside DBP_Database / import($request) and the following is found... because 'tmp_name' is empty it is not doing the import, this is likely to be because I am on a localhost and the file has not been uploaded in anyway as I still have access to the original - this is just a guess though.

    Barry

  • apiening
    Avatar
    Community Member
    55 Posts

    Re: DB Plumber import / export Link to this post

    ahh, ok !

    i fixed the chrome bug. that should be working now (r111069).

    the other one looks like the upload failed either due to insufficient permissions to upload temp dir or file size. can you check if your file size exceeds 16m or your servers post_max_size or upload_max_filesize? i added an error msg (r111078). check it out.

    cheers

    andy

    2502 Views
Page: 1 2
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.