Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
When doing an sql-sync from A to B,
if db B has tables that are not present in db A,
those tables are still there at the end of the sync
So you don't end with A == B.
Kudos for drush + site aliases + sync tools. Awesome power.
Comment | File | Size | Author |
---|---|---|---|
#45 | drush-716412.patch | 2.31 KB | greg.1.anderson |
#18 | drush-sql-sync-drop-first.patch | 15.82 KB | greg.1.anderson |
Comments
Comment #1
moshe weitzman CreditAttribution: moshe weitzman commentedIMO, this is by design. We can't be deleting random tables from a DB. If you want this, delete all tables first or drop and recreate the db. sql-sync can do the create-db part (and perhaps should also do the DROP)? if you agree, reopen this with new title.
Comment #2
yched CreditAttribution: yched commentedWell, the issue is that in some setups, the SQL user in settings.php doesn't have the create db grants.
I ended up doing a
in a wrapper script around db-sync.
Would be kinda cool if db-sync command had an option to force clean db ?
Comment #3
yched CreditAttribution: yched commentedComment #4
moshe weitzman CreditAttribution: moshe weitzman commentedthere is a db-su option which will be used for create-db operation but it is not fully implemented in mysql. i think thats what you need. see #683844: db-su has no password passing feature?. not sure if create-db does a DROP first. It probably should (with confirmation?)
Comment #5
greg.1.anderson CreditAttribution: greg.1.anderson commentedYes, --create-db will drop first.
Comment #6
moshe weitzman CreditAttribution: moshe weitzman commentedok, this is a dupe of the db-su issue then
Comment #7
yched CreditAttribution: yched commentedThe issue with drop / recreate db is that you lose user grants that were set on the db.
That's why I went with the "drop all tables" code in #2 in my custom wrapper script.
Comment #8
moshe weitzman CreditAttribution: moshe weitzman commentedI'm working on this.
Comment #9
moshe weitzman CreditAttribution: moshe weitzman commentedCommitted a sql-drop command. Still needs integration into sql-sync. I will do that soon. If anyone has an opinion on how/where to do that in the flow, let me know.
Comment #10
greg.1.anderson CreditAttribution: greg.1.anderson commentedHere are some thoughts. This problem is not as easy as it might first appear due to the fact that the target db might be remote. In that instance, to get the list of table names you would need to run a remote drush command to get the sql needed to drop the tables, because the tables in the target might be different than the tables in the source. The other problem you might have is that the list of table names could get really long, maybe (if the db had other stuff in it), and you only get 1000 chars or so on the command line before you overflow bash's line buffer. If that isn't an issue, the remote function could just be a 'drush sqlq "show tables;"', which is easy enough to run. You could then only use --drop remotely if there was an instance of drush on the remote machine, though.
Perhaps a cleaner solution execution-wise, but dirtier code-readability-wise would be to use mysql and postgres functions to iterate over the tables and drop them. Then you could send the sql function to the remote machine. I could write that fn in postgres, but don't know if it's possible in mysql. If this works, then it has another advantage of not requiring a remote drush.
Apparently, in Postgres you can do this:
DROP SCHEMA public CASCADE;
However, you need higher privs (similar to create db) to do that. If you have permissions, though, the cascade of the drop of the public schema will take all of your tables along with it. There's no shortcut like that in Mysql, at least not that I know of.
Someone used this technique to get the list of table names in mysql:
Something similar could be done with postgres, or maybe use the drop schema public for postgres and the above trick for mysql? The advantage of this technique is that you can use it to get a sql script that drops all of your tables, and you could insert that code above into the bash script that you send over to the remote machine via ssh. So, you won't run out of command line chars, and you don't need a remote drush for it to work. This is perhaps the best approach; there may be other options as well.
Comment #12
greg.1.anderson CreditAttribution: greg.1.anderson commentedUpdate on the above: I think that using
pg_dump --clean --schema-only | grep ^DROP | psql
is better than droping the schema, since it requires fewer permissions.Edit: Fixed small typo.
Edit again: Said --data-only when I meant --schema-only; fixed that too.
Comment #13
moshe weitzman CreditAttribution: moshe weitzman commentedI think grep would be a new requirement for us. It a reasonable requirement, but each one is a burden for Windows folks I think. Lets do that in php instead.
Comment #14
greg.1.anderson CreditAttribution: greg.1.anderson commentedsql-sync does its remote operations via ssh + bash, so that drush does not need to be installed on the remote machine. I agree that filtering with php is probably best if the destination database is local, though.
Comment #15
greg.1.anderson CreditAttribution: greg.1.anderson commentedIf you're not already working on a different solution, I'll put together a patch that uses php when the destination is local, and inserts code into the ssh / bash script when the destination is remote.
Comment #16
moshe weitzman CreditAttribution: moshe weitzman commentedI'm getting a little uncomfortable with commands doing operations differently depending on how local vs. remote. We just did that with sql-sync sanitizing. My tentative vote is to just use grep everywhere.
Comment #17
moshe weitzman CreditAttribution: moshe weitzman commentedBut yeah, would be great if Greg took this on.
Comment #18
greg.1.anderson CreditAttribution: greg.1.anderson commentedHere is a patch. I tried about four things on postgres, and settled on my second favorite solution, on account of the fact that my favored solution used 'sed'.
Comment #19
moshe weitzman CreditAttribution: moshe weitzman commentedNot sure this was supposed to be commented out. search for
: // mysqldump
We should keep sql-drop as a separate command. Therefore, we should invoke that command from within sql-sync, IMO. Maybe in a COMMAND_PRE hook.
Comment #20
moshe weitzman CreditAttribution: moshe weitzman commentedThe approach looks good to me.
Comment #21
greg.1.anderson CreditAttribution: greg.1.anderson commentedPer #16, I used the same approach for local and remote target sites. If you prefer, I could invoke sql-drop for the local case (but probably not in the pre hook; that comes before the confirmation...)
Comment #22
moshe weitzman CreditAttribution: moshe weitzman commentedI'm interested in enhancing sql-drop so that it does not require drush on the remote side. Doing so we would require us to refactor the dump+grep solution you have here.
Comment #23
greg.1.anderson CreditAttribution: greg.1.anderson commentedHmmm. I had a similar idea to make an sql-import command that handled the remote half of sql-sync, and also moving the remote handling of the dump in sql-sync to the sql-dump file command. So we would then have
sql-dump @a
,sql-drop @b
andsql-import @b
, where @a and @b may be either local or remote, and sql-sync just dispatches to those three functions as needed.What do you think of that? Should we add sql-create to round out the set?
Comment #24
moshe weitzman CreditAttribution: moshe weitzman commentedThat sounds really good to me.
sql-create makes sense as well. site-upgrade passes along --createdb so that might need a touch of refactoring afterwards. i can take care of that.
Comment #25
greg.1.anderson CreditAttribution: greg.1.anderson commentedSounds good.
Comment #26
greg.1.anderson CreditAttribution: greg.1.anderson commentedsql-sync is too tangled; I don't have time to factor out sql-dump and sql-import right now. This is also something better accomplished after unit tests, which means maybe post-drush-4 stable release (depending on how d7 stable is going...).
I will tackle refactoring dump and create, though.
Comment #27
dude4linux CreditAttribution: dude4linux commented+1 for making --drop-all-tables an option for sql-sync. I just got bit while transferring a site to my test server which had previously been used for other testing. I had just assumed that sql-sync would drop all tables and start with a clean database. Later I happened to notice that I had 110 tables instead of the desired 94. Fortunately it was discovered soon enough that I didn't waste a lot of effort.
After giving it some thought, I realised that in some cases the current behaviour is preferred. At some point, after upgrading my site, I'm going to want to move the content to a TurnKey Linux Drupal appliance. It has a pre-installed Drupal setup so I think I only want to overwrite tables that exist in the source while keeping those extra tables that belong to other modules in the destination.
Thanks for all the work you guys have been doing to enhance drush.
Comment #28
greg.1.anderson CreditAttribution: greg.1.anderson commented@dude4linux: I would recommend caution in your plan to merge together two Drupal sites. It is very important that your code and database match up exactly. If you have a working Drupal site, it would be safer to blow away all of the pre-installed stuff, files and db both, and replace it with your site. Back up the pre-installed stuff first in case you want it back. If the pre-installed stuff includes different modules than you have on your side, then use drush to dl and enable them. If you do go ahead and merge two sites together, then do the merge in a dev site, test it, and then sync it back to live after it works.
Comment #29
dude4linux CreditAttribution: dude4linux commented@greg.1.anderson: Don't worry, I'm being very careful. Have backed up my live site to a development server. After I've completed upgrading from 5.23 to 6.19 and have tested the new site, I plan to move it to a TurnKey Linux Drupal appliance. I will make sure that all the same modules/versions are installed on the appliance so all I need to do is move the database, /files, settings.php, etc. The appliance has additional third party modules installed but not enabled. I don't see any reason why I would need to remove the modules and then replace them with the exact same files. When I'm finished, I'll just redirect traffic to the new host and shutdown the current site.
Comment #30
colanSubscribing.
Comment #31
greg.1.anderson CreditAttribution: greg.1.anderson commentedI'm not going to have time to do the refactor for drush-4. Should I submit an update of just #18 + #19 to include in this release, or shall we punt?
I would kind of like to have the option to drop in drush-4, even if the code will need a little refactoring in the future. The basic implementation in #18 is sound.
Comment #32
moshe weitzman CreditAttribution: moshe weitzman commentedI think we should punt. I don't feel strongly about it, but thats my intuition.
Comment #33
greg.1.anderson CreditAttribution: greg.1.anderson commentedOne reason for including it is --structure-tables is destructive without --drop. You can work around that limitation with cc all if the tables in your --structure-tables list are all caches that are cleared by cc. That is probably the case most of the time.
Is there a slick way to figure out what tables cc is going to clear (w/out running it and testing the dump)? Something like
drush cc all -s
(except that doesn't work, because the cc code is in Drupal).Anyway, I'll presume we're going to punt unless the above changes your opinion in some way. I don't have a strong opinion about it.
Comment #34
moshe weitzman CreditAttribution: moshe weitzman commentedthere is no way to get that table list unless you do some real gymnastics like change the DB connection to a no op driver that logs statements. D7 only.
Comment #35
moshe weitzman CreditAttribution: moshe weitzman commentedLets get this into drush5. Upping priority.
Comment #36
Senpai CreditAttribution: Senpai commentedA --drop-all-tables flag could be *very* useful for importing a database using a single command such as:
drush sqlc < db_backup.sql
because often, new or changed tables that aren't in the backup, i.e. 'broken' dev work that needs to be destroyed and reverted, get left behind during a restore such as this.
Will this proposed --drop-all-tables flag also "just work" in the above situation, or do I need to open a separate issue for this concept?
Comment #37
kenorb CreditAttribution: kenorb commentedhttp://build2be.com/content/dumping-all-tables-drush
Comment #38
greg.1.anderson CreditAttribution: greg.1.anderson commentedYes. Drush also has sql-drop now, which works well; it would not be too hard to call it with a --drop flag in sql-sync. Note that if you have database create permissions, you can use --create-db to clear out your database contents.
Comment #39
moshe weitzman CreditAttribution: moshe weitzman commentedFYI, there is also drush_sql_empty_db($target_alias) now.
Comment #40
cristian.stoica CreditAttribution: cristian.stoica commentedsql-drop doesn't drop created VIEWS from the database.
Comment #41
greg.1.anderson CreditAttribution: greg.1.anderson commented#1449056: sql-sync should (by default) delete target db suggests that --drop should be the default.
Comment #42
greg.1.anderson CreditAttribution: greg.1.anderson commentedIn contrast, #1402854: site-install deletes too many tables desires that dropping all tables should not be the default for the site-install commands. Others might feel similarly about sql-sync if we made --drop the default here too. I tend to think that the less destructive option should be the default here.
Comment #43
geek-merlinhmm, thought a bit about this and, really, seems there currently is no general way to know which tables are "ours".
we would need a "table of drupal owned tables" from core to tell.
Comment #44
Elijah LynnAnother vote for:
drush sql-sync --drop @dev @loc
However, thanks for sql-drop, much appreciated. Makes me wonder how many people expect it to drop by default, I spent quite a bit of time thinking it did, like a year or so...
Comment #45
greg.1.anderson CreditAttribution: greg.1.anderson commentedHere is a patch that implements --drop for sql-sync.
Comment #46
moshe weitzman CreditAttribution: moshe weitzman commentedIf we commit #45, we are making --drop dependant on having drush on the target side. Thats a new requirement, right? Ideally we would refactor sql-drop to not require drush. I guess the proposal is to get some new functionality and accept the new limitation?
Comment #47
greg.1.anderson CreditAttribution: greg.1.anderson commentedYes, that is a new limitation, but it only applies if you are using --drop. If you are using sql-sync to pull a remote live or stage site to a local dev (typical), then Drush will be available de-facto.
It would be easier to do this in a Drush-independent way if we used techniques from #990812: Add a "permissions" subcommand to fix/set all file permissions in sql-sync, sql-drop, sql-create, etc., to more easily manage chains of commands to execute on a local or remote machine. I'd recommend committing this and doing a Drush-independent version later, but it would also be reasonable to just wait; it's not too hard to run sql-drop before sql-sync. The main thing that this patch adds is the ability to set 'drop' to true in a command-specific record in drushrc.php, for folks who want Drush to always drop before sync.
Comment #48
moshe weitzman CreditAttribution: moshe weitzman commentedWhen folks ask "does sql-sync require drush on the remote side", I want the answer to be an un unqualified "yes". So, deferring this until we can improve sql-drop.
I hope that any upcoming sql-drop is less voluminous than the code in #990812: Add a "permissions" subcommand to fix/set all file permissions.
Comment #49
greg.1.anderson CreditAttribution: greg.1.anderson commented_drush_sql_drop is already a full page of php code that does different things based on the schema to first find the number of tables, and then run an sql drop on each table. My impression is that it would be quite a bit longer if we changed it to emit the bash commands required to drop all tables for any database schema.
Comment #50
moshe weitzman CreditAttribution: moshe weitzman commentedFYI, some of this code was cleaned up when we introduced wildcard support for structure-tables and friends.
Comment #51
greg.1.anderson CreditAttribution: greg.1.anderson commentedThis issue was marked
closed (won't fix)
because Drush has moved to Github.If this feature is still desired, you may copy it to our Github project. For best results, create a Pull Request that has been updated for the master branch. Post a link here to the PR, and please also change the status of this issue to
closed (duplicate)
.Please ask support questions on Drupal Answers.
Comment #52
R.Hendel CreditAttribution: R.Hendel commentedI created a new issue "sql-sync (and other operations) won't work after sql-drop" at @ github for this.
There is another issue @ github "sql-connect does not work if used after sql-drop" which is very similar and which is caused in my opionion by same reason.