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.
7 comments
Comment actions Permalink

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!

0
Comment actions Permalink

how do you check that "no changes to the data occur"?

0
Comment actions Permalink

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.

0
Comment actions Permalink

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.

0
Comment actions Permalink

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!
0
Comment actions Permalink

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).
0
Comment actions Permalink

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.

0

Please sign in to leave a comment.