Does PHPStorm only support a subset of mysql functionality?
I have been trying to use the MySQL command "truncate table x;" and it seems to be ignored in my scripts when I run it through the default database connector in PHPStorm. Also when the truncate commands are the only thing in the script the GUI returns an error message "No statements found in: myScript.sql". Has anyone else had this problem with 'truncate' or any other MySQL commands?
Please sign in to leave a comment.
Hi there,
1) What PhpStorm version are you using exactly?
2) Could you please provide more details on what you are doing and how (with screenshots, please)?
For example -- I've tried to execute this simple script this way and it worked fine for me.
I have been getting this error as well.
The sql dialect is being reset even though it is locked for the project. I wish I could set it to MYSQL for everything or as the default. So, losing the sql dialect makes it look like the preferences are being broken somehow. Even so, there are other scripts that do run in the same project, and their dialect has not been changed and seems to stay set at mysql, at least in one cycle of this behavior. ODD!
One side effect seems to be that the datasources (at least the only one I have set up) gets killed - it is not selectable, but the DB is still up. Even so, if I go to another script and run it - without setting up the datasource again, the second script sometimes causes the datasource to reappear. Very odd!... But it repeats. If I set up the datasource again - from scratch - then the script will run, but the SQL Dialect might have been altered and that causes the issue. So, the issue seems to be that SQL Dialect or the datasource comes and goes (preferences are clobbered??). I restarted, and the same behavior continues. At other times, the datasource is not being lost, BUT, the sql dialect is being reset even though it is locked for the project.
(this is most recent PHPStorm release).
Error: Run Script "No statements found in: ..."
I cannot figure out why, since the same script has been compiled successfully before, and it does not happen on all scripts but different ones each time sometimes. I run through a recompile of several scripts at times because I change several and loose track.
Here is the most recent:
/* source /Users/davandusen1/Documents/dev_mac/scripts_sql_scripts/Find_DB_Differences/SQL_Scripts_for_Finding_DB_Diffs/GetColumnGenCreate.sql */
USE COMPARE1;
DELIMITER $$
DROP PROCEDURE IF EXISTS getColumnGenCreate$$
CREATE PROCEDURE getColumnGenCreate(schema1 VARCHAR(64),
table1 VARCHAR(64),
schema2 VARCHAR(64),
table2 VARCHAR(64),
find1 VARCHAR(64),
repl1 VARCHAR(64),
find2 VARCHAR(64),
repl2 VARCHAR(64),
table_index_start INT,
part_of_unique_key TINYINT,
column_name VARCHAR(2000),
column_type VARCHAR(64),
column_comment VARCHAR(512),
is_nullable VARCHAR(10),
data_type VARCHAR(64),
ordinal_position INT(10),
column_default VARCHAR(1024),
character_set_name VARCHAR(64),
collation_name VARCHAR(64),
column_key VARCHAR(10),
OUT both_str VARCHAR(5000),
OUT left_str VARCHAR(5000),
OUT right_str VARCHAR(5000))
BEGIN
/* not used to define table of differences. This create might not have a purpose, and might be combined with GetColumnUseInDiffQryCreate */
-- example: CALL GetColumnGenCreate('phpBugTracker', 'comment', 'phpBugTracker', 'comment_copy', 'find_str1', 'repl_str1', 'find_str2', 'repl_str2', 1, 1, 1,'title', 'varchar(64)', 'Column provides a title for the silly row', 'YES', 'varchar', 3, '', 'utf8', 'utf8_general_ci', 'MUL', p_both_str, p_left_str, p_right_str);
-- -- debugging variables
DECLARE v_code_section VARCHAR(255) DEFAULT 'GetColumnGenCreate';
DECLARE v_proc_code_loc VARCHAR(255) DEFAULT 'start_debug';
DECLARE v_deb_msg VARCHAR(5000) DEFAULT '';
DECLARE debug_enabled TINYINT DEFAULT 1;
-- -- end of debugging variables
DECLARE done INT DEFAULT 0;
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'start';
CALL debug.debug_on(v_code_section);
END; END IF;
SET @v_column_name := column_name;
SET @v_column_type := column_type;
SET @v_column_comment := column_comment;
SET @v_is_nullable := is_nullable;
SET @v_data_type := data_type;
SET @v_ordinal_position := ordinal_position;
SET @v_column_default := column_default;
SET @v_character_set_name := character_set_name;
SET @v_collation_name := collation_name;
SET @v_column_key := column_key;
-- -------------------------------------------------------------------
SET @v_column_temp_both_seg := '';
SET @v_column_temp_left_seg := '';
SET @v_column_temp_right_seg := '';
SET @v_column_temp_nullable_val := ' NULL ';
SET @v_column_temp_default_val := '';
SET @v_column_temp_comment_val := ' COMMENT ';
-- -------------------------------------------------------------------
SET @v_left_column_temp_default_val := '';
IF debug_enabled <> 0
THEN BEGIN
SET v_deb_msg := concat_ws(' , ', part_of_unique_key, @v_column_name, @v_column_type, @v_column_comment, @v_is_nullable, @v_data_type,
@v_ordinal_position, @v_column_default,
@v_character_set_name, @v_collation_name, @v_column_key);
CALL debug.debug_msg(v_code_section, v_proc_code_loc, v_deb_msg);
END; END IF;
IF length(@v_column_comment) > 0
THEN
SET @v_column_temp_comment_val := concat(' COMMENT ''', replace(@v_column_comment, '''', ''''''), '''');
ELSE
SET @v_column_temp_comment_val := ' COMMENT '''' ';
END IF;
SET @infix := ', ';
SET @tab_index_1 := 1;
SET @tab_index_2 := 2;
IF table_index_start > 0
THEN BEGIN
SET @tab_index_1 := table_index_start;
SET @tab_index_2 := table_index_start + 1;
END; END IF;
IF part_of_unique_key <> 0
THEN BEGIN
-- -- -- Process Unique Keys
SET @v_both_column_temp_nullable_val := ' NOT NULL ';
SET @v_both_column_temp_default_val := '';
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'before generating where Column is UNIQUE KEY clause';
SET v_deb_msg := CONCAT('part_of_unique_key: ', part_of_unique_key, ', Column: ', @v_column_name);
CALL debug.debug_msg(v_code_section, v_proc_code_loc, v_deb_msg);
END;
END IF;
SET @v_column_temp_both_seg := CONCAT(' `', lcase(@v_column_name), '` ', @v_column_type,
@v_both_column_temp_nullable_val, @v_both_column_temp_default_val,
@v_column_temp_comment_val);
IF debug_enabled <> 0
THEN BEGIN
SET v_deb_msg := replace(@v_column_temp_both_seg, '''', '---');
SET v_proc_code_loc := 'after generating where Column is UNIQUE KEY in Create clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, v_deb_msg);
END;
END IF;
SET both_str := CONCAT(@v_column_temp_both_seg, ' \/* FROM getColumnGenInsert KEY *\/ ');
/* LEFT */
SET left_str := @v_column_temp_both_seg;
/* RIGHT */
SET right_str := @v_column_temp_both_seg;
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'generated KEY Column Create Both clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, both_str);
END;
END IF;
END;
ELSE BEGIN
-- -- -- Process Columns Other than Unique Keys
-- -- -- Tweaks for non keys
-- -- -- These may have been set in Parent process
SET @v_left_column_temp_nullable_val := ' NULL ';
SET @v_right_column_temp_nullable_val := ' NULL ';
-- -- --
IF (lcase(@v_data_type) = 'varchar') OR (lcase(@v_data_type) = 'longtext') OR (lcase(@v_data_type) = 'char')
THEN BEGIN
IF length(@v_column_default) > 0
THEN
SET @v_left_column_temp_default_val := concat(' DEFAULT ''', replace(@v_column_default, '''', ''''''), '''');
ELSE
SET @v_left_column_temp_default_val := ' DEFAULT '''' ';
END IF;
END;
ELSEIF (lcase(@v_column_type) = 'text') OR (lcase(@v_column_type) = 'blob')
THEN
SET @v_left_column_temp_default_val := ' ';
ELSE BEGIN
IF length(@v_column_default) > 0
THEN
SET @v_left_column_temp_default_val := concat(' DEFAULT ', @v_column_default, ' ');
ELSE
SET @v_left_column_temp_default_val := ' DEFAULT null ';
END IF;
END;
END IF;
SET @tab_present1_on := 0;
SET @v_right_column_temp_default_val := @v_left_column_temp_default_val;
IF length(table1) > 0
THEN BEGIN
-- -- -- There is a left table!!!!!
SET @tab_present1_on := 1;
SET @v_left_column_comment_val := @v_column_temp_comment_val;
IF @v_is_nullable = 'NO'
THEN
SET @v_left_column_temp_nullable_val := ' NOT NULL ';
SET @v_left_column_temp_default_val := '';
END IF;
END;
ELSE BEGIN
SET @v_left_column_comment_val := @v_column_temp_comment_val;
SET @v_left_column_temp_default_val := ' DEFAULT null ';
SET @v_left_column_temp_nullable_val := ' NULL ';
END;
END IF;
SET @tab_present2_on := 0;
IF length(table2) > 0
THEN BEGIN
-- -- -- There is a right table!!!!!
SET @tab_present2_on := 1;
SET @v_right_column_comment_val := @v_column_temp_comment_val;
IF @v_is_nullable = 'NO'
THEN
SET @v_right_column_temp_nullable_val := ' NOT NULL ';
SET @v_right_column_temp_default_val := '';
END IF;
END;
ELSE BEGIN
SET @v_right_column_comment_val := @v_column_temp_comment_val;
SET @v_right_column_temp_default_val := ' DEFAULT null ';
SET @v_right_column_temp_nullable_val := ' NULL ';
END;
END IF;
/* BOTH */
/* always create both left and right columns, but set default value for missing one to null */
SET @v_column_temp_left_seg := CONCAT(' `sc', @tab_index_1, '_', lcase(@v_column_name), '` ',
@v_column_type, @v_left_column_temp_nullable_val,
@v_left_column_temp_default_val,
@v_left_column_comment_val);
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'after generating BOTH statement clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, @v_column_temp_both_seg);
CALL debug.debug_off(v_code_section);
END;
END IF;
/* always create both left and right columns, but set default value for missing one to null */
SET @v_column_temp_right_seg := CONCAT(
' `sc', @tab_index_2, '_', lcase(@v_column_name), '` ', @v_column_type, @v_right_column_temp_nullable_val, @v_right_column_temp_default_val,
@v_right_column_comment_val);
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'after generating BOTH statement clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, @v_column_temp_both_seg);
CALL debug.debug_off(v_code_section);
END;
END IF;
SET @v_column_temp_both_seg := concat(@v_column_temp_left_seg, @infix, @v_column_temp_right_seg);
SET both_str := CONCAT(@v_column_temp_both_seg, ' \/* FROM getColumnGenCreate *\/ ');
/* LEFT */
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'after generating LEFT statement clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, @v_column_temp_left_seg);
CALL debug.debug_off(v_code_section);
END;
END IF;
SET left_str := @v_column_temp_left_seg;
/* RIGHT */
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'after generating RIGHT statement clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, @v_column_temp_right_seg);
CALL debug.debug_off(v_code_section);
END;
END IF;
SET right_str := @v_column_temp_right_seg;
END;
END IF;
CALL debug.debug_off(v_code_section);
END$$
DELIMITER ;
-- CALL GetColumnGenCreate('bitnami_totem2','block', 'bitnami_drupalpanixx', 'block', 'totem', 'consortium', 'totem', 'consortium', 1, 'title', 'varchar(64)', 'Column provides a title for the silly row', 'YES', 'varchar', 3, '', 'utf8', 'utf8_general_ci', 'MUL', p_both_str, p_left_str, p_right_str);
-----------------------------------------------------------------------------
This one works fine:
use COMPARE1
DELIMITER $$
DROP FUNCTION IF EXISTS getColumnList$$
CREATE FUNCTION getColumnList(schema1 VARCHAR(64), table1 VARCHAR(64)) RETURNS text
BEGIN
-- example: SELECT getColumnList('phpBugTracker', 'comment');
DECLARE column_name VARCHAR(64);
DECLARE column_list TEXT DEFAULT '';
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT c1.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c1 WHERE c1.TABLE_SCHEMA=schema1 AND c1.TABLE_NAME=table1
ORDER BY c1.COLUMN_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND set done := 1;
SET @tab_index_1 := 1;
SET @tab_index_2 := 2;
OPEN cur;
read_loop: LOOP
FETCH cur INTO column_name;
IF done THEN
LEAVE read_loop;
END IF;
IF column_list <> '' THEN
SET column_list := CONCAT(column_list, ', ');
END IF;
SET column_list := CONCAT(column_list, '`', column_name, '`');
END LOOP;
CLOSE cur;
RETURN column_list;
END $$
DELIMITER ;
-- SELECT getColumnList('bitnami_totem2', 'block');
Later it will reverse - the first one will work and who knows which one will stop working....
Note that the first script above, when run by sourcing, produces:
mysql> source /Users/davandusen1/Documents/dev_mac/scripts_sql_scripts/Find_DB_Differences/SQL_Scripts_for_Finding_DB_Diffs/GetColumnGenCreate.sql
Database changed
--------------
DROP PROCEDURE IF EXISTS getColumnGenCreate
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
CREATE PROCEDURE getColumnGenCreate(schema1 VARCHAR(64),
table1 VARCHAR(64),
schema2 VARCHAR(64),
table2 VARCHAR(64),
find1 VARCHAR(64),
repl1 VARCHAR(64),
find2 VARCHAR(64),
repl2 VARCHAR(64),
table_index_start INT,
part_of_unique_key TINYINT,
column_name VARCHAR(2000),
column_type VARCHAR(64),
column_comment VARCHAR(512),
is_nullable VARCHAR(10),
data_type VARCHAR(64),
ordinal_position INT(10),
column_default VARCHAR(1024),
character_set_name VARCHAR(64),
collation_name VARCHAR(64),
column_key VARCHAR(10),
OUT both_str VARCHAR(5000),
OUT left_str VARCHAR(5000),
OUT right_str VARCHAR(5000))
BEGIN
DECLARE v_code_section VARCHAR(255) DEFAULT 'GetColumnGenCreate';
DECLARE v_proc_code_loc VARCHAR(255) DEFAULT 'start_debug';
DECLARE v_deb_msg VARCHAR(5000) DEFAULT '';
DECLARE debug_enabled TINYINT DEFAULT 1;
DECLARE done INT DEFAULT 0;
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'start';
CALL debug.debug_on(v_code_section);
END; END IF;
SET @v_column_name := column_name;
SET @v_column_type := column_type;
SET @v_column_comment := column_comment;
SET @v_is_nullable := is_nullable;
SET @v_data_type := data_type;
SET @v_ordinal_position := ordinal_position;
SET @v_column_default := column_default;
SET @v_character_set_name := character_set_name;
SET @v_collation_name := collation_name;
SET @v_column_key := column_key;
SET @v_column_temp_both_seg := '';
SET @v_column_temp_left_seg := '';
SET @v_column_temp_right_seg := '';
SET @v_column_temp_nullable_val := ' NULL ';
SET @v_column_temp_default_val := '';
SET @v_column_temp_comment_val := ' COMMENT ';
SET @v_left_column_temp_default_val := '';
IF debug_enabled <> 0
THEN BEGIN
SET v_deb_msg := concat_ws(' , ', part_of_unique_key, @v_column_name, @v_column_type, @v_column_comment, @v_is_nullable, @v_data_type,
@v_ordinal_position, @v_column_default,
@v_character_set_name, @v_collation_name, @v_column_key);
CALL debug.debug_msg(v_code_section, v_proc_code_loc, v_deb_msg);
END; END IF;
IF length(@v_column_comment) > 0
THEN
SET @v_column_temp_comment_val := concat(' COMMENT ''', replace(@v_column_comment, '''', ''''''), '''');
ELSE
SET @v_column_temp_comment_val := ' COMMENT '''' ';
END IF;
SET @infix := ', ';
SET @tab_index_1 := 1;
SET @tab_index_2 := 2;
IF table_index_start > 0
THEN BEGIN
SET @tab_index_1 := table_index_start;
SET @tab_index_2 := table_index_start + 1;
END; END IF;
IF part_of_unique_key <> 0
THEN BEGIN
SET @v_both_column_temp_nullable_val := ' NOT NULL ';
SET @v_both_column_temp_default_val := '';
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'before generating where Column is UNIQUE KEY clause';
SET v_deb_msg := CONCAT('part_of_unique_key: ', part_of_unique_key, ', Column: ', @v_column_name);
CALL debug.debug_msg(v_code_section, v_proc_code_loc, v_deb_msg);
END;
END IF;
SET @v_column_temp_both_seg := CONCAT(' `', lcase(@v_column_name), '` ', @v_column_type,
@v_both_column_temp_nullable_val, @v_both_column_temp_default_val,
@v_column_temp_comment_val);
IF debug_enabled <> 0
THEN BEGIN
SET v_deb_msg := replace(@v_column_temp_both_seg, '''', '---');
SET v_proc_code_loc := 'after generating where Column is UNIQUE KEY in Create clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, v_deb_msg);
END;
END IF;
SET both_str := CONCAT(@v_column_temp_both_seg, ' \/* FROM getColumnGenInsert KEY *\/ ');
SET left_str := @v_column_temp_both_seg;
SET right_str := @v_column_temp_both_seg;
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'generated KEY Column Create Both clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, both_str);
END;
END IF;
END;
ELSE BEGIN
SET @v_left_column_temp_nullable_val := ' NULL ';
SET @v_right_column_temp_nullable_val := ' NULL ';
IF (lcase(@v_data_type) = 'varchar') OR (lcase(@v_data_type) = 'longtext') OR (lcase(@v_data_type) = 'char')
THEN BEGIN
IF length(@v_column_default) > 0
THEN
SET @v_left_column_temp_default_val := concat(' DEFAULT ''', replace(@v_column_default, '''', ''''''), '''');
ELSE
SET @v_left_column_temp_default_val := ' DEFAULT '''' ';
END IF;
END;
ELSEIF (lcase(@v_column_type) = 'text') OR (lcase(@v_column_type) = 'blob')
THEN
SET @v_left_column_temp_default_val := ' ';
ELSE BEGIN
IF length(@v_column_default) > 0
THEN
SET @v_left_column_temp_default_val := concat(' DEFAULT ', @v_column_default, ' ');
ELSE
SET @v_left_column_temp_default_val := ' DEFAULT null ';
END IF;
END;
END IF;
SET @tab_present1_on := 0;
SET @v_right_column_temp_default_val := @v_left_column_temp_default_val;
IF length(table1) > 0
THEN BEGIN
SET @tab_present1_on := 1;
SET @v_left_column_comment_val := @v_column_temp_comment_val;
IF @v_is_nullable = 'NO'
THEN
SET @v_left_column_temp_nullable_val := ' NOT NULL ';
SET @v_left_column_temp_default_val := '';
END IF;
END;
ELSE BEGIN
SET @v_left_column_comment_val := @v_column_temp_comment_val;
SET @v_left_column_temp_default_val := ' DEFAULT null ';
SET @v_left_column_temp_nullable_val := ' NULL ';
END;
END IF;
SET @tab_present2_on := 0;
IF length(table2) > 0
THEN BEGIN
SET @tab_present2_on := 1;
SET @v_right_column_comment_val := @v_column_temp_comment_val;
IF @v_is_nullable = 'NO'
THEN
SET @v_right_column_temp_nullable_val := ' NOT NULL ';
SET @v_right_column_temp_default_val := '';
END IF;
END;
ELSE BEGIN
SET @v_right_column_comment_val := @v_column_temp_comment_val;
SET @v_right_column_temp_default_val := ' DEFAULT null ';
SET @v_right_column_temp_nullable_val := ' NULL ';
END;
END IF;
SET @v_column_temp_left_seg := CONCAT(' `sc', @tab_index_1, '_', lcase(@v_column_name), '` ',
@v_column_type, @v_left_column_temp_nullable_val,
@v_left_column_temp_default_val,
@v_left_column_comment_val);
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'after generating BOTH statement clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, @v_column_temp_both_seg);
CALL debug.debug_off(v_code_section);
END;
END IF;
SET @v_column_temp_right_seg := CONCAT(
' `sc', @tab_index_2, '_', lcase(@v_column_name), '` ', @v_column_type, @v_right_column_temp_nullable_val, @v_right_column_temp_default_val,
@v_right_column_comment_val);
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'after generating BOTH statement clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, @v_column_temp_both_seg);
CALL debug.debug_off(v_code_section);
END;
END IF;
SET @v_column_temp_both_seg := concat(@v_column_temp_left_seg, @infix, @v_column_temp_right_seg);
SET both_str := CONCAT(@v_column_temp_both_seg, ' \/* FROM getColumnGenCreate *\/ ');
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'after generating LEFT statement clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, @v_column_temp_left_seg);
CALL debug.debug_off(v_code_section);
END;
END IF;
SET left_str := @v_column_temp_left_seg;
IF debug_enabled <> 0
THEN BEGIN
SET v_proc_code_loc := 'after generating RIGHT statement clause';
CALL debug.debug_msg(v_code_section, v_proc_code_loc, @v_column_temp_right_seg);
CALL debug.debug_off(v_code_section);
END;
END IF;
SET right_str := @v_column_temp_right_seg;
END;
END IF;
CALL debug.debug_off(v_code_section);
END
--------------
Query OK, 0 rows affected (0.00 sec)
mysql>