10810 Posts in 2495 Topics by 1784 members
Page: 1 2
|Go to End||Next >|
15 September 2010 at 11:09pm
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.
16 September 2010 at 12:43pm
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?
16 September 2010 at 9:33pm
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...
GROUP BY RecordID,Version
...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?
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!
17 September 2010 at 1:06am Last edited: 17 September 2010 1:06am
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>;
20 September 2010 at 12:36pm
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...
20 September 2010 at 1:06pm Last edited: 20 September 2010 1:14pm
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...)
21 September 2010 at 8:42pm Last edited: 22 September 2010 2:32am
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.
[dirParts:protected] => Array
[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
[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/
[allParams:protected] => Array
[Control] => database
[Action] => import
[latestParams:protected] => Array
[unshiftedButParsedParts:protected] => 2
22 September 2010 at 12:00pm
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.
Page: 1 2
|Go to Top||Next >|