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

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';

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?

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

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

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

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

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

SS_HTTPRequest Object
(
     => admin/dbplumber/database/import
    [dirParts:protected] => Array
        (
        )

    [extension:protected] => 
    [httpMethod:protected] => POST
    [getVars:protected] => Array
        (
            [url] => /evolution/admin/dbplumber/database/import
        )

    [postVars:protected] => Array
        (
            [MAX_FILE_SIZE] => 16777216
            [importtype] => rawsql
            [importfile] => Array
                (
                    [name] => evo_20100915_115059_mysql.sql
                    [type] => 
                    [tmp_name] => 
                    [error] => 1
                    [size] => 0
                )

        )

    [headers:protected] => Array
        (
            [Accept] => text/html,text/html,application/xml;q=0.9,*/*;q=0.8
            [Content-Type] => multipart/form-data; boundary=---------------------------268691786114688
            [Referer] => http://localhost/evolution/admin/dbplumber/
        )

    [body:protected] => 
    [allParams:protected] => Array
        (
            [Control] => database
            [Action] => import
            [ID] => 
            [OtherID] => 
        )

    [latestParams:protected] => Array
        (
            [Action] => 
            [ID] => 
            [OtherID] => 
        )

    [unshiftedButParsedParts:protected] => 2
)[/code]

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