site-install - Never try to drop an existing database. Just drop tables.

1) We handle user initiated abort in this script consistent with the rest of drush. Scripts usually don't have user interaction. See drush_user_abort()

3,4) drush tries to drop/create and if not possible, it tries to drop all tables instead. Typically the configured mysql user at least has that perm. If you are not seeing your tables get dropped, make sure the drupal mysql user has enough perms. Then reopen this issue.

I use site-install in a script as well.
Since the drupal database user has limited permissions I rather handle database creation by my own. Besides that I'd like my script to skip any confirmation.
In order to accomplish these things I applied the attached patch and then run drush site-install with the --no-db-create --yes options.

I also agree with point 1.

The "yes" option is unnecessary. You can already pass -y to it.

The current workflow of how the database is handled works, but I do think its a little silly to always try to drop it first. I too always use a more limited db user and seeing that error is annoying.

It seems like we could accomplish the same by checking if the db exists and then if so, drop all tables, if not, create it. I'm not convinced that the "no-db-create" option is useful.

reframing the issue per #3

I agree that we should assume that this is all run with a limited-access user.
For me it says
ERROR 1044 (42000) at line 1: Access denied for user 'xxx'@'localhost' to database 'xxx'
which is only because of the DROP DATABASE.
Showing an error is just too strong a statement.

There's one use-case when you don't want to drop the database : execute multiple site-install at once, using --db-prefix so you can store all tables in the same database (yes, i had this once).

Since site-install drops the DB, you cannot script a multiple drupal instances installation procedure.

Subscribe

Patch #2 worksforme2

Droping DB is no good idea as user may not have permission to recreate and multisites (not yet tested myself) seems a real problem here.

#3 makes sense, but there is no platform independent way to check if a given DB exists. So we need implementations for mysql, postgres, sqlite, and sqlsrv.

#11: The grant command should only happen if the db is created.

#12: We could use the drush sql-query wrapper to try to contact the db; that way, the db-specific implementations are already provided.

I should fix all of this sql stuff at the sprint.

#14
makes sense. add an option to create the db (if not exists), and otherwise assume that a db already exists.
Maybe even add another option to delete tables (if applies), and otherwise stop if the db is found to be not empty?

Maybe "ALL PRIVILEGES" is too much for some users, but if you have no grant statement, then the db will not work until some privs are granted by hand. I therefore think that at least some privs should be granted; otherwise the create option is not too useful. If we do not have a mechanism to specify the exact permissions desired, "all" is okay for a dev machine. If you don't want "all", you can create the db and set the privs by hand, and then skip the --create-db option. A patch to select the desired permission level would be welcome, though.

I will try to get to this

The good news is that I got pretty far on this. See patch.

The bad news is that my approach needs tweaking. On mysql at least, a limited privilege user can't query INFORMATION_SCHEMA as that requires 'Show Databases' perm. When regular user does that, mysql return NULL and success exit code. This response is indistinguishable from a permissioned user requesting a non-existent DB. Ugh.

With a bit of refactoring, you could use _drush_sql_query to run a "show tables;" query. If you got back zero rows, then do nothing (empty db is good to go); one or more rows, call sql_drop. If there is no database, then the sql connect should return an error, and you can then call sql_create.

Didn't have time to try that, but I would think that would work.

Someone else will have to push this along.

I'll add it to my queue, but it may be a while. Anyone else who wants to jump in with a patch should feel free.

I managed to fix drush_sql_db_exists() for mysql so committed to master.

I am seeing all tests pass except for one unrelated fail in completeTest (@dev/@stage confusion).

I'd like to backport this in 4.6 (we are at 4.5RC right now).

The fix introduces #1332778: drush @drupal.d7 site-install is broken when using alias

I guess backport isn't really necessary. Lets call this fixed and work on the #23 bug elsewhere.