SQL Queries
I have just finished running many lists of queries and found that some would not work properly.
I save the queries in a .sql file and use the CTRL+SHIFT+F10 shortcut command "run."
None of these will work:
-- Moves and changes data from name to slug
UPDATE category
SET slug = LOWER(name);
UPDATE category
SET slug = REPLACE(slug, '-', ' ');
UPDATE category
SET slug = REPLACE(slug, ':', '');
UPDATE category
SET slug = REPLACE(slug, '/', '-');
UPDATE category
SET slug = REPLACE(slug, ' ', '-');
UPDATE category
SET slug = REPLACE(slug, '"', '');
UPDATE category
SET slug = REPLACE(slug, "'", "");
UPDATE category
SET slug = REPLACE(slug, '.', '-');
UPDATE category
SET slug = REPLACE(slug, '%', '-pct');
UPDATE category
SET slug = REPLACE(slug, '!', '');
UPDATE category
SET slug = REPLACE(slug, '&', '');
UPDATE category
SET slug = REPLACE(slug, '--', '-');
UPDATE category
SET slug = REPLACE(slug, '---', '-');
The resposne to these commands is the same: "197 row(s) affected in 2 ms"
There are 197 rows in the table, but no changes to the data occur.
However, in another file, this works:
-- Change image link
UPDATE product SET image = REPLACE(image, 'thumbs/', '');
I apologize if this is my own fault in any way, but I thought it worth reporting.
Any help would be appreciated.
Oh, and any of these ran through PhpMyAdmin do work properly.
Thanks.
Please sign in to leave a comment.
Hi.
I'd really appreciate communication from the makers/support team on this as it ruins the convenience of being able to run queries in PhpStorm for me on my current and most important project. Thanks!
how do you check that "no changes to the data occur"?
Hi, thank you for the reply/inquiry.
I had previously ran the queries through PhpMyAdmin, so I quickly realized something was wrong when every response to each query was identical. Some of the queries should only alter one or two rows. I then investigated by using PhpMyAdmin, and found no changes occurred to the related table. Additionally, I tested the very same queries directly through PhpMyAdmin and the changes were made successfully on the first try.
I hope this helps, and thanks again.
Looks like transaction has not been committed.
Try those statements directly in the database console and verify that auto-commit mode is turned on.
Advanced data source properties may control the default behavior as well.
Hi! I really appreciate your response.
Here is what occurred:
sql> UPDATE category
SET slug = LOWER(name);
UPDATE category
SET slug = REPLACE(slug, '-', ' ');
UPDATE category
SET slug = REPLACE(slug, ':', '');
UPDATE category
SET slug = REPLACE(slug, '/', '-');
UPDATE category
SET slug = REPLACE(slug, ' ', '-');
UPDATE category
SET slug = REPLACE(slug, '"', '');
UPDATE category
SET slug = REPLACE(slug, "'", "");
UPDATE category
SET slug = REPLACE(slug, '.', '-');
UPDATE category
SET slug = REPLACE(slug, '%', '-pct');
UPDATE category
SET slug = REPLACE(slug, '!', '');
UPDATE category
SET slug = REPLACE(slug, '&', '');
UPDATE category
SET slug = REPLACE(slug, '--', '-');
UPDATE category
SET slug = REPLACE(slug, '---', '-')
[2011-12-08 11:48:20] 197 row(s) affected in 976 ms
[2011-12-08 11:48:20] 197 row(s) affected in 11 ms
[2011-12-08 11:48:20] 197 row(s) affected in 16 ms
[2011-12-08 11:48:20] 197 row(s) affected in 13 ms
[2011-12-08 11:48:20] 197 row(s) affected in 16 ms
[2011-12-08 11:48:20] 197 row(s) affected in 15 ms
[2011-12-08 11:48:20] 197 row(s) affected in 12 ms
[2011-12-08 11:48:20] 197 row(s) affected in 13 ms
[2011-12-08 11:48:20] 197 row(s) affected in 10 ms
[2011-12-08 11:48:20] 197 row(s) affected in 10 ms
[2011-12-08 11:48:20] 197 row(s) affected in 11 ms
[2011-12-08 11:48:20] 197 row(s) affected in 12 ms
[2011-12-08 11:48:20] 197 row(s) affected in 11 ms
This output is identical to what I receive when running these same queries from the sql file itself.
Interestingly, the similar and working query I listed in the original post also works in the console:
sql> UPDATE product SET image = REPLACE(image, 'thumbs/', '')
[2011-12-08 11:51:13] 6153 row(s) affected in 248 ms
The tables have differing collation. I am not convinced that that should cause a problem, but for the sake of clarity:
Table: product
Engine: InnoDB
Collation: latin1_swedish_ci
Table: category
Engine: InnoDB
Collation: utf8_general_ci
And, again, all of the queries that I am dealing with work when ran from PhpMyAdmin.
Of course that will be my workflow for this, for now, but it would be very nice to keep it within PhpStorm.
Cheers!
Some more questions to track down the problem.
What database/version/JDBC driver do you use?
Do some simple DML test work for you? Like:
create table RWTest ( id integer );
insert into RWTest values (1);
select * from RWTest; -- should display "1" in a cell
drop table RWTest;
If the above script works as expected let's try to split the script into two sessions.
create table RWTest ( id integer );
insert into RWTest values (1);
-- now go to Data Sources view and close connection to the database
-- relaunch console and run:
select * from RWTest; -- should display "1" in a cell
drop table RWTest;
If this doesn't work try perform an explicit COMMIT command (if available).
My database is MySQL 5.5.8.
The JDBC driver is MySQL Connector/J 5.1.18.
The test, as a whole, worked properly with "1" being displayed in a cell, and the test table being cleared from the database.
The second test also worked properly.
Again, thank you so much for this help.