Phinx: Add debug option to show raw SQL

Created on 30 May 2013 · 30Comments · Source: cakephp/phinx

Sometimes when trying to run migration and failing for weird database errors it's hard to debug the problem without knowing what exact SQL is being issued against the server. The --verbose option shows what Phinx is trying to do, but not exactly how.

Maybe another option -vv or --debug could show the commands sent to the server?

feature

Source

igorsantos07

👍15

Most helpful comment

I've had to do the same myself, and it is pretty simple to get the final query that is executed.

In Db/Adapter/PdoAdapter.php

<?php

    public function execute($sql)
    {
        return $this->getConnection()->exec($sql);
    }

replace with

<?php

    public function execute($sql)
    {
        if (OutputInterface::VERBOSITY_VERBOSE === $this->getOutput()->getVerbosity()) {
            $this->getOutput()->writeln($sql);
        }
        return $this->getConnection()->exec($sql);
    }

This will obviously show the raw queries when you use the --verbose command, but another flag would most likely be more appropriate.

tomzx on 9 Jul 2013

👍7

All 30 comments

:+1:
or --show-sql?

czogori on 2 Jun 2013

Would be great. Would have some common functionality with my feature request #102

joshribakoff on 6 Jul 2013

I've had to do the same myself, and it is pretty simple to get the final query that is executed.

In Db/Adapter/PdoAdapter.php

<?php

    public function execute($sql)
    {
        return $this->getConnection()->exec($sql);
    }

replace with

<?php

    public function execute($sql)
    {
        if (OutputInterface::VERBOSITY_VERBOSE === $this->getOutput()->getVerbosity()) {
            $this->getOutput()->writeln($sql);
        }
        return $this->getConnection()->exec($sql);
    }

This will obviously show the raw queries when you use the --verbose command, but another flag would most likely be more appropriate.

tomzx on 9 Jul 2013

👍7

artptr on 29 Dec 2013

robertmarsal on 17 Jul 2014

+1
But what about non-PDO Adapters?
I know that all of the adapters are extending from PDOAdapter, but what if we want to add a Cassandra Adapter? We will have to copy that implementation, make sense?

mcapodacqua on 21 Jul 2014

+1
Want to see steps of migration. They may be slow.

TrogWarZ on 6 Feb 2015

valkirilov on 13 May 2015

Maybe also report back the number of rows affected/inserted too.

rquadling on 13 May 2015

jiMuBao on 23 Sep 2015

Keloo on 22 Oct 2015

I'd be happy with the verbose flag being used if the intent is that you occasionally (i.e. during debugging/building) you want to see the queries.

I've got a PR for showing the migration start/end times. Using -v.

For this issue, a -v (w|c|sh)ould show the query, with -vv showing maybe the response times and rows affected counts.

Considering Symfony/Console already provides this option, it would seem pointless creating another for something that is of occasional use.

Having said that, a --query-log[=filename] option would also make sense. With the filename being defaulted to something like phinx_2015-10-22_17-46-17_command.sql, where command is migrate or rollback. So you could see a list of SQL files, in order of execution, containing JUST the SQL statements executed.

rquadling on 22 Oct 2015

TiGR on 31 Dec 2015

ghost on 7 Jan 2016

👍1

this seems to be somewhat related to https://github.com/robmorgan/phinx/issues/567 (ie. as both features would show the SQL statements, the one here running them after and that other exiting without running them)

ghost on 7 Jan 2016

chrisforrence on 25 Jan 2016

SanderSander on 1 Feb 2016

mfacenet on 2 Feb 2016

FlorianH on 4 Mar 2016

rewmike on 29 Mar 2016

stromajer on 11 Apr 2016

A potential issue arises where a migration requires data to operate upon. The initial SELECT would be reported, but by not actually being executed, the remainder of the migration would not be processed. Potentially, maybe as a further logging option to allow non destructive queries, allowing SELECT statements through, but not UPDATE, DELETE, INSERT, etc. could be useful.

The quality of the results would obviously depend upon the query and the potential for an infinite loop is present; i.e. Query is for the last non-updated row, loop until all rows are processed. As the SELECT will always return the first non-updated row, there is no exit.

So, a consideration really. Not a requirement (though it would be nice to have if infinite loop detection existed .... and how for out of scope is that!!! Ha!)

rquadling on 11 Apr 2016

This feature would be useful when working with replication tools like slony that requires in some cases the SQL statements used on the master node to sync the new entries or tables to the slave node.

fefas on 18 Aug 2016

@tomzx provided a simple adaption above, that could easily be implemented.

macwinnie on 15 Nov 2016

ahukkanen on 5 Dec 2016

JamesTheHacker on 9 Dec 2016

@tomzx this doesn't work for me. The only output I get is START TRANSACTION. It doesn't print any SQL before it fails.

JamesTheHacker on 11 Dec 2016

leonkielstra on 10 May 2017

Already implemented in v0.10.0 with the --verbose flag

lorenzo on 8 Jun 2018

Just a note - running --dry-run displays raw SQL!

START TRANSACTION
ALTER TABLE `product` CHANGE `date_start` `date_start` DATE NULL DEFAULT ''
ALTER TABLE `product` CHANGE `date_end` `date_end` DATE NULL DEFAULT ''
update product set date_start = NULL where date_start < '1971-01-01'
update product set date_end = NULL where date_end < '1971-01-01'
ALTER TABLE `product` ADD  KEY (`customer_group_id`,`date_start`,`date_end`)
COMMIT
INSERT INTO migrations (`version`, `migration_name`, `start_time`, `end_time`, `breakpoint`) VALUES ('20190709093643', 'testMigration', '2019-07-12 09:04:56', '2019-07-12 09:04:56', 0);