nikolay200669 / common-schema Goto Github PK
View Code? Open in Web Editor NEWAutomatically exported from code.google.com/p/common-schema
common-schema's People
Watchers
common-schema's Issues
_wrap_select_list_columns makes for incorrect query when alias with no "AS" is used
making this a new issue since this is not an aggregate problem.
Seems to me like a glitch on one character in doing the math:
makes a syntax error
-- --------------------------------
[email protected]> SET @query := 'SELECT db the_database FROM mysql.db';
Query OK, 0 rows affected (0.00 sec)
[email protected]> CALL _wrap_select_list_columns(@query, 3, @error); SELECT
@query;
Query OK, 0 rows affected (0.00 sec)
+----------------------------------------------------------------+
| @query |
+----------------------------------------------------------------+
| SELECT db t AS col1, NULL as col2, NULL as col3 from mysql.db |
+----------------------------------------------------------------+
Original issue reported on code.google.com by [email protected] on 10 Nov 2011 at 6:38
sql_grants: incorrect GRANT syntax for stored routines
GRANT USAGE ON *.* TO 'world_user'@'localhost';
GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sakila`.`get_customer_balance` TO
'world_user'@'localhost' WITH GRANT OPTION;
mysql> SELECT sql_grant FROM sql_grants WHERE grantee =
"'world_user'@'localhost'" \G
sql_grant: GRANT USAGE ON *.* TO 'world_user'@'localhost' IDENTIFIED BY
PASSWORD ''
sql_grant: GRANT ALTER ROUTINE, EXECUTE ON `sakila`.`get_customer_balance` TO
'world_user'@'localhost' WITH GRANT OPTION
"ON" stored routine should include "FUNCTION" or "PROCEDURE"
Original issue reported on code.google.com by [email protected] on 18 Nov 2012 at 6:49
split not working on table with implicit self-referencing foreign key
Which version of common_schema are you using? (specify
revision+distribution)
1.3.1
Which component is failing? (specify the view, function, etc.)
split()
What is the expected output? What do you see instead?
I ran an update using split and expected to see updated rows, but nothing was
updated. I think split failed to pick the proper column to split on. I dropped
a bunch of columns from the relevant table to figure out which one caused the
problem, and discovered that it was a self-referential implicit foreign key. A
column that had the name of the table within its name and a unique index on it.
Can you provide with sample data?
Steps to reproduce (using sakila db):
1) This split works in a default sakila installation:
call common_schema.run("
split(sakila.film) {
select
$split_step as step, $split_columns as columns,
$split_min as min_value, $split_max as max_value,
$split_range_start as range_start, $split_range_end as range_end
}" );
2) Add a new column and unique index:
alter table sakila.film add most_similar_film_id smallint unsigned null;
alter table sakila.film add unique key most_similar_film_id
(most_similar_film_id);
3) Now this fails:
call common_schema.run("
split(sakila.film) {
select
$split_step as step, $split_columns as columns,
$split_min as min_value, $split_max as max_value,
$split_range_start as range_start, $split_range_end as range_end
}" );
(As of version 1.1), please provide output of
SELECT * FROM common_schema.status
project_name: common_schema
version: 1.3.1
revision: 437
install_time: 2013-03-19 12:05:01
install_success: 1
base_components_installed: 1
innodb_plugin_components_installed: 1
percona_server_components_installed: 1
install_mysql_version: 5.5.29-log
install_sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Please provide any additional information below.
More detail on my original issue is available here:
http://mechanics.flite.com/blog/2013/03/18/helping-common-schema-help-me/
Original issue reported on code.google.com by [email protected] on 19 Mar 2013 at 5:21
problem with quotes and json_to_xml
This valid (but weird) JSON cannot be converted to XML:
+---+-------------------------------------------------+
| 1 | common_schema.json_to_xml('{ "body" : "\":" }') |
+---+-------------------------------------------------+
| 1 | NULL |
+---+-------------------------------------------------+
1 row in set (0.03 sec)
(this is just the fragment breaking json_to_xml, if you want to see the full
string, just ask)
Thanks for common_schema, it's great.
Original issue reported on code.google.com by [email protected] on 26 Aug 2013 at 11:04
Type-Enhancement
What component are you suggesting? (view, table, function, procedure)
query_analysis_routines: get_trigger_dependencies()
What will this new component do?
will detect dependencies of a given trigger
Characteristics of the component? (e.g. NO SQL for stored function)
DETERMINISTIC, READS SQL DATA
Dependencies for component?
Compatibility with what kind of MySQL version/distribution?
5.5.x, 5.5.x
OS specific issues?
-
Do you expect this to be a heavyweight operation? Why?
-
Anything else?
-
Please label as "Type-Enhancement"
Original issue reported on code.google.com by [email protected] on 20 Feb 2013 at 9:09
data_size_per_table
What component are you suggesting? (view, table, function, procedure)
A view showing disk usage per table.
What will this new component do?
Show how much disk space is used per database & table.
Characteristics of the component? (e.g. NO SQL for stored function)
View
Dependencies for component?
None
Compatibility with what kind of MySQL version/distribution?
Compatible
OS specific issues?
None.
Do you expect this to be a heavyweight operation? Why?
No.
Anything else?
CREATE ALGORITHM=TEMPTABLE VIEW `data_size_per_table` AS select
`information_schema`.`TABLES`.`TABLE_SCHEMA` AS
`TABLE_SCHEMA`,`information_schema`.`TABLES`.`TABLE_NAME` AS
`TABLE_NAME`,round((`information_schema`.`TABLES`.`DATA_LENGTH` / (1024 *
1024)),0) AS `DATA_LENGTH`,round((`information_schema`.`TABLES`.`INDEX_LENGTH`
/ (1024 * 1024)),0) AS
`INDEX_LENGTH`,round(((`information_schema`.`TABLES`.`DATA_LENGTH` / (1024 *
1024)) + (`information_schema`.`TABLES`.`INDEX_LENGTH` / (1024 * 1024))),0) AS
`TABLE_DISK_SPACE`,ifnull(round((`information_schema`.`TABLES`.`INDEX_LENGTH` /
`information_schema`.`TABLES`.`DATA_LENGTH`),4),0) AS `INDEX_DATA_RATIO` from
`information_schema`.`TABLES` where
(`information_schema`.`TABLES`.`TABLE_SCHEMA` not in
('information_schema','performance_schema','mysql','common_schema'))
Original issue reported on code.google.com by [email protected] on 19 Jul 2012 at 11:23
common_schema 1.2.2 - crashed mysqld on production!
Which version of common_schema are you using? (specify
revision+distribution)
1.2.2
Which component is failing? (specify the view, function, etc.)
`_expand_statement_variables`
What is the expected output? What do you see instead?
during execution the file- the session disconnected...
(mysql> \. common_schema-1.2.2.sql;)
Can you provide with sample data?
please see attached error log
(As of version 1.1), please provide output of
SELECT * FROM common_schema.status
project_name: common_schema
version: 1.2.2
revision: 368
install_time: 2012-12-03 22:06:02
install_success: 0
base_components_installed: 0
innodb_plugin_components_installed: 0
percona_server_components_installed: 0
install_mysql_version: 5.5.13-log
install_sql_mode:
Please provide any additional information below.
Original issue reported on code.google.com by [email protected] on 3 Dec 2012 at 11:15
Attachments:
"start" statement conflict between "start transaction" and "start slave"
"start transaction" handled as script, "start slave" is sql.
Need to support ambiguous sql/script statements
Original issue reported on code.google.com by [email protected] on 6 Jan 2013 at 7:25
"Reverse dependencies" view or proc
"What component are you suggesting? (view, table, function, procedure)"
Preferably a view, but otherwise a procedure to list "reverse dependencies": a
lost of objects (tables, views, events, functions, procedures, triggers) that
are dependent upon a particular object.
"What will this new component do?"
Provide rows of dependency data:
- object_schema: schema wherein the object resides
- object_name: name of the object
- object_name: type of the object (table, view, etc.)
- dependent_object_schema: schema of the object depending on this object
"Characteristics of the component? (e.g. NO SQL for stored function)"
whatever it takes
"Dependencies for component?"
whatever it takes
"Compatibility with what kind of MySQL version/distribution?"
5.0 and up
"OS specific issues?"
None
"Do you expect this to be a heavyweight operation? Why?"
Yes.
Original issue reported on code.google.com by roland.bouman on 14 Sep 2011 at 8:15
Need to suppoert "DISTINCT" in _wrap_select_list_columns
Currently produces error
Original issue reported on code.google.com by [email protected] on 22 Nov 2012 at 6:55
_wrap_select_list_columns returning with strange output
[email protected]> set @q:='SELECT id FROM world.City';
Query OK, 0 rows affected (0.00 sec)
[email protected]> CALL _wrap_select_list_columns(@q, 2, @error);
Query OK, 0 rows affected (0.00 sec)
[email protected]> select @q;
+-----------------------------------------------------+
| @q |
+-----------------------------------------------------+
| SELECT , null as col1, null as col2 from world.City |
+-----------------------------------------------------+
1 row in set (0.00 sec)
Original issue reported on code.google.com by [email protected] on 15 Sep 2011 at 11:39
improvement: eval() could use VIEW instead of temporary table
suggestion: eval could generate a VIEW instead of a temporary table. This would
have the advantage of not requiring any storage, and it could be faster too
since the resultset would need to be materialized only once, namely when the
cursor is opened (now, the set is first materialized from the argument query,
then stored, and then that set is opened again to loop through the cursor).
Code would need to be modified slightly because MySQL does not have temporary
views:
CREATE PROCEDURE eval(sql_query TEXT CHARSET utf8)
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT 'Evaluates queries resulting from given query'
BEGIN
SET @q := CONCAT('CREATE OR REPLACE VIEW _tmp_eval_queries', connection_id(), ' AS ', sql_query);
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
BEGIN
DECLARE current_query TEXT CHARSET utf8 DEFAULT NULL;
DECLARE done INT DEFAULT 0;
DECLARE eval_cursor CURSOR FOR SELECT query FROM _tmp_eval_queries;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN eval_cursor;
read_loop: LOOP
FETCH eval_cursor INTO current_query;
IF done THEN
LEAVE read_loop;
END IF;
SET @execute_query := current_query;
IF @common_schema_verbose THEN
SELECT @execute_query AS now_executing FROM DUAL;
END IF;
PREPARE st FROM @execute_query;
EXECUTE st;
DEALLOCATE PREPARE st;
END LOOP;
CLOSE eval_cursor;
END;
SET @q := CONCAT('DROP VIEW _tmp_eval_queries', connection_id());
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
END $$
Original issue reported on code.google.com by roland.bouman on 6 Sep 2011 at 9:54
Error in Documentation for auto_increments_columns
Which version of common_schema are you using? (specify
revision+distribution)
1.2.2
Which component is failing? (specify the view, function, etc.)
Documentation for
auto_increment_columns
What is the expected output? What do you see instead?
In documentation it says
"is_signed: 1 if type is SIGNED, 0 if UNSIGNED"
While but if you load the view and query it puts a 1 for unsigned and 0 for
signed in the "is_signed" column
select * from auto_increment_columns where is_signed = 1 limit 1\G
TABLE_SCHEMA: sanitized
TABLE_NAME: sanitized
COLUMN_NAME: sanitized
DATA_TYPE: int
COLUMN_TYPE: int(5) unsigned
is_signed: 1
max_value: 4294967295
AUTO_INCREMENT: 8
auto_increment_ratio: 0.0000
select * from auto_increment_columns where is_signed = 0 limit 1\G
*************************** 1. row ***************************
TABLE_SCHEMA: sanitized2
TABLE_NAME: sanitized2
COLUMN_NAME: sanitized_id
DATA_TYPE: int
COLUMN_TYPE: int(10)
is_signed: 0
max_value: 2147483647
AUTO_INCREMENT: 3
auto_increment_ratio: 0.0000
Original issue reported on code.google.com by [email protected] on 7 Jan 2013 at 8:55
Tokenizer broken for x<int
mysql> call _get_sql_tokens('p<10000');
+----+-------+-------+-------+-------+
| id | start | level | token | state |
+----+-------+-------+-------+-------+
| 1 | 2 | 0 | p | alpha |
| 2 | 8 | 0 | 0 | error |
| 3 | 8 | 0 | | start |
+----+-------+-------+-------+-------+
this needs to be fixed in order to really fix
http://code.google.com/p/common-schema/issues/detail?id=14
Original issue reported on code.google.com by roland.bouman on 16 Sep 2011 at 6:03
extract_json_value fails with negative value
Which version of common_schema are you using? (specify
revision+distribution)
2.2 523
Which component is failing? (specify the view, function, etc.)
select extract_json_value('{"test":"-2"}','/test'); fails returning NULL
but remove the negative and the function works.
select extract_json_value('{"test":"2"}','/test');
What is the expected output? What do you see instead?
Expect to see 2 instead of NULL
Can you provide with sample data?
(As of version 1.1), please provide output of
SELECT * FROM common_schema.status
Please provide any additional information below.
I do not have the entire common_schema installed as I don't have permission in
my VPS but I have installed the following functions/procedures
get_json_token
encode_xml
extract_json_value
json_to_xml
trim_wspace
unquote
Original issue reported on code.google.com by [email protected] on 29 Nov 2013 at 2:47
get_num_tokens() returns wrong output on multi-chcracter delimiter
need to divide diff by length of delimiter
Original issue reported on code.google.com by [email protected] on 7 Sep 2011 at 9:13
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'find_in_set'
Which version of common_schema are you using?
1.1
Which component is failing?
none that I know of
What is the expected output? What do you see instead?
ERROR 1267 (HY000) at line 7629 in file: 'common_schema-1.1.sql': Illegal mix
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)
ERROR 1267 (HY000) at line 7664 in file: 'common_schema-1.1.sql': Illegal mix
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)
ERROR 1267 (HY000) at line 7706 in file: 'common_schema-1.1.sql': Illegal mix
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)
ERROR 1267 (HY000) at line 7738 in file: 'common_schema-1.1.sql': Illegal mix
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)
ERROR 1267 (HY000) at line 7768 in file: 'common_schema-1.1.sql': Illegal mix
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)
ERROR 1267 (HY000) at line 7800 in file: 'common_schema-1.1.sql': Illegal mix
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for
operation 'find_in_set'
Please provide any additional information below.
MariaDB [common_schema]>show variables like 'version%';
+-------------------------+--------------------+
| Variable_name | Value |
+-------------------------+--------------------+
| version | 5.5.27-MariaDB-log |
| version_comment | MariaDB Server |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+--------------------+
4 rows in set (0.02 sec)
Original issue reported on code.google.com by [email protected] on 24 Sep 2012 at 8:41
Error when installing
I am using common_schema 1.2, and i tried to install on MySQL 5.5.28, but i got
an error (see attach).
The error possibly comes from a SQL create function.
I tried using heidiSQL, but also using MySQL Workbench i got the same error!
Original issue reported on code.google.com by [email protected] on 14 Nov 2012 at 8:57
Attachments:
new install: FUNCTION common_schema.split_token does not exist
Which version of common_schema are you using? (specify
revision+distribution)
1.3
Which component is failing? (specify the view, function, etc.)
new install
First time ever installing common-schema (any version )
What is the expected output? What do you see instead?
missing FUNCTIONS errors on install of common-schemas-1.3
Can you provide with sample data?
( maybe some earlier erros not logged )
[ERROR in query 416] FUNCTION common_schema.split_token does not exist
[ERROR in query 418] FUNCTION common_schema.split_token does not exist
[ERROR in query 419] FUNCTION common_schema.split_token does not exist
[ERROR in query 441] FUNCTION common_schema.split_token does not exist
[ERROR in query 443] FUNCTION common_schema.split_token does not exist
[ERROR in query 445] FUNCTION common_schema.split_token does not exist
[ERROR in query 447] FUNCTION common_schema.split_token does not exist
[ERROR in query 449] FUNCTION common_schema.split_token does not exist
[ERROR in query 451] FUNCTION common_schema.split_token does not exist
[ERROR in query 607] FUNCTION common_schema.get_num_tokens does not exist
[ERROR in query 608] FUNCTION common_schema.get_num_tokens does not exist
(As of version 1.1), please provide output of
SELECT * FROM common_schema.status
project_name version revision install_time install_success base_components_insta
lled innodb_plugin_components_installed percona_server_components_installed inst
all_mysql_version install_sql_mode
common_schema 1.3 419 2013-01-17
20:19:27 1 1 0 0 5.6.9-rc-log STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Please provide any additional information below.
Original issue reported on code.google.com by [email protected] on 18 Jan 2013 at 4:38
split statement to accept expanded variables
Need to accept expanded variables in the query part of the split statement.
This includes the possibility to autodetect table_schema.table_name even if
either or both are expanded variables.
Also be able to explicitly name table_schema.table_name via expanded variables.
Original issue reported on code.google.com by [email protected] on 23 Nov 2012 at 5:41
More unexpected output from _wrap_select_list_columns
SET @query := 'SELECT id, name AS city_name, IF(population < 1000000,
\'small\', \'large\') AS size_description FROM world.City';
CALL _wrap_select_list_columns(@query, 3, @error);
SELECT @query, @error;
result:
SELECT (select id) as col1, (select name AS city_name) as col2, null as col3
NULL
Original issue reported on code.google.com by [email protected] on 16 Sep 2011 at 5:21
get_routine_dependencies does not report back on sql functions and user defined functions.
see subject.
Original issue reported on code.google.com by roland.bouman on 11 Jan 2012 at 7:34
JSON parsing fails with negative integers
Thanks for common schema, it's pretty cool.
Using 2.1 JSON parsing fails with negative integers:
mysql> select common_schema.json_to_xml('{"foo":1}');
+----------------------------------------+
| common_schema.json_to_xml('{"foo":1}') |
+----------------------------------------+
| <foo>1</foo> |
+----------------------------------------+
1 row in set (0.03 sec)
mysql> select common_schema.json_to_xml('{"foo":-1}');
+------------------------------------------+
| common_schema.json_to_xml('{"foo":-1}') |
+------------------------------------------+
| NULL |
+------------------------------------------+
1 row in set (0.03 sec)
Thanks!
Original issue reported on code.google.com by [email protected] on 26 Jul 2013 at 11:51
GROUP BY error installing 1.3
Here's the output:
# mysql < common_schema-1.3.sql
ERROR 1055 (42000) at line 9006: 'routine_privileges.ROUTINE_TYPE' isn't in
GROUP BY
I'm installing into MySQL 5.6 with the following SQL_MODE:
ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_
ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Original issue reported on code.google.com by baron.schwartz on 28 Jan 2013 at 8:02
view to generate SQL to kill slow queries
What component are you suggesting? (view, table, function, procedure)
- view
- What will this new component do?
generate SQL statements to kill slow queries
- Characteristics of the component? (e.g. NO SQL for stored function)
- Dependencies for component?
perhaps one of the security tables from common_schema
- Compatibility with what kind of MySQL version/distribution?
>= 5.1
- OS specific issues?
none
- Do you expect this to be a heavyweight operation? Why?
no
- Anything else?
based on:
http://forge.mysql.com/tools/tool.php?id=106
Please label as "Type-Enhancement"
Original issue reported on code.google.com by [email protected] on 21 Jul 2011 at 4:46
Error 1064 when installing on 5.6.8-rc with workbench
Which version of common_schema are you using? (specify
revision+distribution)
1.2.2 r368
Which component is failing? (specify the view, function, etc.)
What is the expected output? What do you see instead?
11:33:43 INTO metadata (attribute_name, attribute_value) VALUES ('author',
'Shlomi Noach'), ('author_url', 'http://code.openark.org/blog/shlomi-noach'),
('install_success', false), ('install_time', NOW()), ('install_sql_mode',
@@sql_mode), ('install_mysql_version', VERSION()),
('base_components_installed', false), ('innodb_plugin_components_installed',
false), ('percona_server_components_installed', false), ('license_type',
'New BSD'), ('license', ' Copyright (c) 2011 - 2012, Shlomi Noach All rights
reserved. Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:
* Redistributions of source code must retain the above copyright notice, this
list of conditions and the following disclaimer. * Redistributions in
binary form must reproduce the above copyright notice, this list of conditions
and the following disclaimer in the documentation and/or other materials
provided with the distribution. * Neither the name of the organization nor
the names of its contributors may be used to endorse or promote products
derived from this software without specific prior written permission. THIS
SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY
EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. '),
('project_name', 'common_schema'), ('project_home',
'http://code.google.com/p/common-schema/'), ('project_repository',
'https://common-schema.googlecode.com/svn/trunk/'),
('project_repository_type', 'svn'), ('revision', '368'), ('version',
'1.2.2') Error Code: 1064. You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near 'INTO metadata (attribute_name, attribute_value) VALUES ('author',
'Shlomi Noac' at line 1 0.000 sec
Can you provide with sample data?
(As of version 1.1), please provide output of
SELECT * FROM common_schema.status
Please provide any additional information below.
It seems like workbench does not like:
1. some statements which use multiple lines
2. "-- " comments do not work. "-- x" works
Original issue reported on code.google.com by [email protected] on 23 Nov 2012 at 10:50
incorrect action analisys in get_sql_dependencies()
call get_sql_dependencies('CREATE VIEW sakila.simple_actor AS SELECT actor_id,
first_name FROM sakila.actor', 'sakila');
+-------------+--------------+-------------+--------+
| schema_name | object_name | object_type | action |
+-------------+--------------+-------------+--------+
| sakila | actor | table | create |
| sakila | simple_actor | view | create |
+-------------+--------------+-------------+--------+
the actor table does not get created here; so this is confusing.
Original issue reported on code.google.com by [email protected] on 17 Nov 2011 at 7:46
Percona Server components not installed on `Percona Server compatible` flavours
Which version of common_schema are you using?
1.1
Which component is failing?
Percona Server components
What is the expected output? What do you see instead?
+-----------------------------------------------------------+
| complete |
+-----------------------------------------------------------+
| - Base components: installed |
| - InnoDB Plugin components: not installed |
| - Percona Server components: not installed |
| |
| Installation complete. Thank you for using common_schema! |
+-----------------------------------------------------------+
Can you provide with sample data?
N/A
(As of version 1.1), please provide output of SELECT * FROM common_schema.status
MariaDB [(none)]>SELECT * FROM common_schema.status\G
*************************** 1. row ***************************
project_name: common_schema
version: 1.1
revision: 300
install_time: 2012-09-25 07:39:45
install_success: 1
base_components_installed: 1
innodb_plugin_components_installed: 0
percona_server_components_installed: 0
install_mysql_version: 5.5.27-MariaDB-log
install_sql_mode:
1 row in set (0.11 sec)
Please provide any additional information below.
Being a MariaDB server it has all the advantages of Percona Server.
I believe Percona Server components should also be installed on:
- MariaDB
- TokuDB for MariaDB
I'll be happy to assist in testing these as I work daily with them.
Original issue reported on code.google.com by [email protected] on 24 Sep 2012 at 8:48
most QueryScript scripts fail when auto_increment_increment != 1
This is due to the following two facts:
1. the _sql_tokens table uses id AUTO_INCREMENT
2. QueryScript expects ids to be sequential
Original issue reported on code.google.com by [email protected] on 15 Oct 2012 at 9:40
_wrap_select_list_columns does not work properly on full qualified column names
[email protected]> set @q := 'select City.name, City.population from world.City
limit 10';
[email protected]> call _wrap_select_list_columns(@q, 2, @e);
[email protected]> select @q, @e;
+---------------------------------------------------------------+------+
| @q | @e |
+---------------------------------------------------------------+------+
| select City AS col1, City. AS col2 from world.City limit 10 | NULL |
+---------------------------------------------------------------+------+
Original issue reported on code.google.com by [email protected] on 8 Jan 2013 at 12:04
sql_mode parameter for sql tokenizer
Right now the tokenizer dynamically retrieves the sql_mode from the session
(currently only for determining if ANSI_QUOTES are in effect)
the sql mode should be a parameter.
This is required for correct operation of the dependencies procs. that's
because the sql_mode in effect at ddl time when creating a view or routine is
saved along with the object. when running the routine or selecting from the
view that mode is used. so the only way to correctly analyze code is if we know
what sql_mode applies for that object.
Original issue reported on code.google.com by roland.bouman on 16 Sep 2011 at 1:05
get_event_dependencies fails on action statement
-- What steps will reproduce the problem?
CREATE EVENT
sakila.purge_history
ON SCHEDULE
EVERY 1 DAY
ON COMPLETION PRESERVE
ENABLE
DO
DELETE FROM sakila.rental WHERE rental_date < DATE(NOW() - INTERVAL 5 YEAR);
call common_schema.get_event_dependencies('sakila', 'purge_history');
-- What is the expected output? What do you see instead?
I get:
+--------------------------------+
| error |
+--------------------------------+
| Error: unexpected state delete |
+--------------------------------+
Original issue reported on code.google.com by [email protected] on 16 Nov 2011 at 9:23
sql_foreign_keys doesn't guarantee column order
revision 68
problem: sql_foreign_keys
The group_concat expressions for the referencing and referenced columns do not
include an ORDER BY clause. They should in order to guarantee column order for
multi-column foreign keys. With the current implementation of the I_S it's not
likely to go wrong, but to be sure of the correct output, ORDER BY clauses
should be added.
Modified view code below:
CREATE OR REPLACE
ALGORITHM = UNDEFINED
SQL SECURITY INVOKER
VIEW sql_foreign_keys AS
SELECT
KEY_COLUMN_USAGE.TABLE_SCHEMA,
KEY_COLUMN_USAGE.TABLE_NAME,
KEY_COLUMN_USAGE.CONSTRAINT_NAME,
CONCAT(
'ALTER TABLE `', KEY_COLUMN_USAGE.TABLE_SCHEMA, '`.`', KEY_COLUMN_USAGE.TABLE_NAME,
'` DROP FOREIGN KEY `', KEY_COLUMN_USAGE.CONSTRAINT_NAME, '`'
) AS drop_statement,
CONCAT(
'ALTER TABLE `', KEY_COLUMN_USAGE.TABLE_SCHEMA, '`.`', KEY_COLUMN_USAGE.TABLE_NAME,
'` ADD CONSTRAINT `', KEY_COLUMN_USAGE.CONSTRAINT_NAME,
'` FOREIGN KEY (', GROUP_CONCAT('`', KEY_COLUMN_USAGE.COLUMN_NAME, '`' ORDER BY KEY_COLUMN_USAGE.ORDINAL_POSITION), ')',
' REFERENCES `', KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA, '`.`', KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,
'` (', GROUP_CONCAT('`', KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, '`' ORDER BY KEY_COLUMN_USAGE.ORDINAL_POSITION), ')',
' ON DELETE ', MIN(REFERENTIAL_CONSTRAINTS.DELETE_RULE),
' ON UPDATE ', MIN(REFERENTIAL_CONSTRAINTS.UPDATE_RULE)
) AS create_statement
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS USING(CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
WHERE
KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA IS NOT NULL
GROUP BY
KEY_COLUMN_USAGE.TABLE_SCHEMA, KEY_COLUMN_USAGE.TABLE_NAME, KEY_COLUMN_USAGE.CONSTRAINT_NAME, KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA, KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME
;
(Also, the superfluous CONCAT inside the GROUP_CONCAT was removed)
Original issue reported on code.google.com by roland.bouman on 6 Sep 2011 at 1:15
Should have procedure get_event_dependencies
What component are you suggesting? (view, table, function, procedure):
get_event_dependencies
What will this new component do?
This should apply get_sql_dependencies to the code that makes up an event.
Characteristics of the component? (e.g. NO SQL for stored function)
The usual
Dependencies for component?
get_sql_dependencies, mysql.event
Compatibility with what kind of MySQL version/distribution?
5.1 and up.
OS specific issues?
Do you expect this to be a heavyweight operation? Why?
Anything else?
Please label as "Type-Enhancement"
Original issue reported on code.google.com by roland.bouman on 19 Sep 2011 at 8:08
Candidate keys view
What component are you suggesting? (view, table, function, procedure)
- A view
Describe the new component you would like common_schema to have
What will this new component do?
- A view to present with a table's candidate keys (unique keys) by order of
viability
Characteristics of the component? (e.g. NO SQL for stored function)
Dependencies for component?
Compatibility with what kind of MySQL version/distribution?
- any
OS specific issues?
Do you expect this to be a heavyweight operation? Why?
- yes, relies on I_S heavyweight tables
Anything else?
Please label as "Type-Enhancement"
Original issue reported on code.google.com by [email protected] on 16 Jul 2011 at 5:07
candidate_keys lists nullable unique indexes
the current implementation of candidate_keys view lists nullable unique
indexes. These are not true candidate keys, because the occurrence of null
values could make it impossible to uniquely identify a row.
Original issue reported on code.google.com by roland.bouman on 6 Sep 2011 at 10:15
tokenizer broken with strings
Posting this here otherwise I forget the string to reproduce the problem
mysql> call _get_sql_tokens(',''\n''');
+----+-------+-------+-------+--------+
| id | start | level | token | state |
+----+-------+-------+-------+--------+
| 1 | 2 | 0 | , | comma |
| 2 | 5 | 0 | '
' | string |
| 3 | 5 | 0 | | start |
+----+-------+-------+-------+--------+
Original issue reported on code.google.com by roland.bouman on 15 Sep 2011 at 6:47
Temp tables can break replication
Is it possible for common_schema to avoid temp tables, and use uniquely named
real tables instead? When starting a slave from a backup, I got the following
error:
2013-05-18 20:34:34 3454 [ERROR] Slave SQL: Error 'Table
'common_schema._qs_variables' doesn't exist' on query. Default database:
'common_schema'. Query: 'UPDATE _qs_variables SET value_snapshot = NULL WHERE
declaration_depth = NAME_CONST('depth',4)', Error_code: 1146
2013-05-18 20:34:34 3454 [Warning] Slave: Table 'common_schema._qs_variables'
doesn't exist Error_code: 1146
Original issue reported on code.google.com by baron.schwartz on 18 May 2013 at 8:36
_wrap_select_list_columns makes for incorrect query when GROUP BY is used
What steps will reproduce the problem?
select continent, count(*) from world.Country group by continent;
+---------------+----------+
| continent | count(*) |
+---------------+----------+
| Asia | 51 |
| Europe | 46 |
| North America | 37 |
| Africa | 58 |
| Oceania | 28 |
| Antarctica | 5 |
| South America | 14 |
+---------------+----------+
set @q := 'select continent, count(*) from world.Country group by continent';
call common_schema._wrap_select_list_columns(@q, 9, @err);
select @q;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------+
| @q
|
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------+
| select (select continent) as col1, (select count(*) ) as col2, null as
col3, null as col4, null as col5, null as col6, null as col7, null as col8,
null as col9 from world.Country group by continent |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------+
select (select continent) as col1, (select count(*) ) as col2, null as col3,
null as col4, null as col5, null as col6, null as col7, null as col8, null as
col9 from world.Country group by continent ;
+---------------+------+------+------+------+------+------+------+------+
| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 |
+---------------+------+------+------+------+------+------+------+------+
| Asia | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Europe | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| North America | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Africa | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Oceania | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Antarctica | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| South America | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+---------------+------+------+------+------+------+------+------+------+
The idea of wrapping columns as "(select __original_column_definition__) as
col1" appears to be completely wrong when aggregation is used.
This makes for a severe problem. Any ideas out of it?
Original issue reported on code.google.com by [email protected] on 6 Nov 2011 at 6:32
sleep & throttle params round as integer
What steps will reproduce the problem?
call run("while(1){echo yes;sleep 0.2}");
What is the expected output? What do you see instead?
An action every 0.2 seconds; instead loop is instantaneous; 0.2 is rounded as
0.
Original issue reported on code.google.com by [email protected] on 28 Sep 2012 at 8:37
Here are some stored procedures and a config table for pinning tables/indexes in the buffer pool (useful as an event)
use common_schema;
CREATE TABLE IF NOT EXISTS `pin_buffer_pool_config` (
`schema_name` varchar(50) DEFAULT NULL,
`table_name` varchar(50) DEFAULT NULL,
`index_name` varchar(255) DEFAULT 'PRIMARY' comment 'null for all indexes',
`where_clause` varchar(255) DEFAULT NULL comment 'do not include the WHERE keyword'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
delimiter ;;
DROP PROCEDURE IF EXISTS pin_buffer_pool;
-- load all the columns for the given index
-- into the buffer pool
CREATE PROCEDURE pin_buffer_pool()
BEGIN
DECLARE v_done boolean default false;
DECLARE v_stmt TEXT default null;
DECLARE v_got_lock tinyint default 0;
DECLARE v_cursor CURSOR FOR
SELECT CONCAT('SELECT COUNT(CONCAT(',
GROUP_CONCAT(column_name ORDER BY seq_in_index), ')) INTO @discard FROM `',
s.table_schema, '`.`', s.table_name,
'` FORCE INDEX(`', s.index_name, '`)',
IF(where_clause IS NOT NULL, CONCAT(' WHERE ', where_clause), '')
) AS stmt
FROM information_schema.statistics s
JOIN test.pin_buffer_pool_config pbpc
ON pbpc.table_name = s.table_name
AND pbpc.schema_name = s.table_schema
-- when the index_name is null, it means warm all indexes for the table
AND s.index_name = ifnull(pbpc.index_name, s.index_name)
GROUP BY s.index_name;
DECLARE CONTINUE HANDLER FOR
SQLSTATE '02000'
SET v_done = TRUE;
-- DON'T HOLD A LONG TRANSACTION, START A NEW SNAPSHOT FOR EACH READ
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- abort if the last pin is still running
SELECT GET_LOCK('BUFFER_POOL_PIN', 0) INTO v_got_lock;
IF v_got_lock = 1 THEN
OPEN v_cursor;
cursorLoop: LOOP
FETCH v_cursor INTO v_stmt;
IF v_done THEN
CLOSE v_cursor;
LEAVE cursorLoop;
END IF;
set @v_stmt = v_stmt;
prepare v_bp_pin from @v_stmt;
execute v_bp_pin;
deallocate prepare v_bp_pin;
END LOOP;
SELECT RELEASE_LOCK('BUFFER_POOL_PIN') INTO @discard;
END IF;
END;;
DROP PROCEDURE IF EXISTS pin_buffer_pool_loop;;
CREATE PROCEDURE pin_buffer_pool_loop()
BEGIN
-- This procedure can be scheduled to start
-- every second with no harm done. It will
-- simply exit if more than one copy tries
-- to run. This means that an event can
-- be used to ensure the warming function is
-- always on and that it is looping faster
-- than innodb_old_blocks_time (default 1000 in 5.6)
SELECT GET_LOCK('BUFFER_POOL_PIN_LOOP_LOCK', 0) INTO @got_lock;
IF @got_lock = 1 THEN
LOOP
CALL pin_buffer_pool();
select sleep(.25) into @discard;
END LOOP;
SELECT RELEASE_LOCK('BUFFER_POOL_PIN_LOOP_LOCK') INTO @discard;
END IF;
END;;
delimiter ;
Original issue reported on code.google.com by [email protected] on 7 May 2013 at 4:23
Error when loading common_schema created with mysqldump
Which version of common_schema are you using?
I'm using 1.2.2 version
Which component is failing?
I use mysqldump to backup several databases, including commom_schema. The
result if saving on a .sql kackup file. I create c:\common_schema.sql
When loading the .sql backup file created with mysqldump, I use
c:\mysql.exe --host="servidor" --port="3306" --user="root" --password="passw" <
c:\common_schema.sql
I tried also using HeidiSQL and got the same error!
My MySQL version is 5.5.28 and 5.1.42, with the same error!
Original issue reported on code.google.com by [email protected] on 20 Nov 2012 at 4:25
dependencies procedures need documentation
Documentation needed for
- get_sql_dependencies
- get_view_dependencies
- get_routine_dependencies
- get_event_dependencies (tbd)
I propose to document the output (and logic where appropriate) once in
get_sql_dependencies, and document calls and usage for each other procedure.
Those can all refer to the get_sql_dependencies documentation for the output.
Original issue reported on code.google.com by roland.bouman on 19 Sep 2011 at 9:03
Problem with sql_range_partitions
Which version of common_schema are you using?
2.2
Which component is failing?
sql_range_partitions
What is the expected output? What do you see instead?
I expected an output with count_past_partitions, count_future_partitions and
sql_add_next_partition filled, because we have some tables created with range
partitions for every month since 2009-01-01, two partitions for the years
before and one with maxvalue. The partitions have been limited by to_days().
Instead of this expection, the output shows null for these three columns.
Can you provide with sample data?
Here is a shortened output of SHOW CREATE TABLE:
CREATE TABLE `receipt_line` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`receipt_id` int(11) unsigned DEFAULT NULL,
`status` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`receipt_date` date NOT NULL,
`product_id` int(11) DEFAULT NULL,
`data_status` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`receipt_date`),
) ENGINE=InnoDB AUTO_INCREMENT=46694279 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci PACK_KEYS=1 DELAY_KEY_WRITE=1
/*!50100 PARTITION BY RANGE (to_days(receipt_date))
(PARTITION p2007 VALUES LESS THAN (733407) ENGINE = InnoDB,
PARTITION p2008 VALUES LESS THAN (733773) ENGINE = InnoDB,
PARTITION p200901 VALUES LESS THAN (733804) ENGINE = InnoDB,
PARTITION p200902 VALUES LESS THAN (733832) ENGINE = InnoDB,
PARTITION p200903 VALUES LESS THAN (733863) ENGINE = InnoDB,
PARTITION p200904 VALUES LESS THAN (733893) ENGINE = InnoDB,
PARTITION p200905 VALUES LESS THAN (733924) ENGINE = InnoDB,
PARTITION p200906 VALUES LESS THAN (733954) ENGINE = InnoDB,
PARTITION p200907 VALUES LESS THAN (733985) ENGINE = InnoDB,
PARTITION p200908 VALUES LESS THAN (734016) ENGINE = InnoDB,
PARTITION p200909 VALUES LESS THAN (734046) ENGINE = InnoDB,
PARTITION p200910 VALUES LESS THAN (734077) ENGINE = InnoDB,
PARTITION p200911 VALUES LESS THAN (734107) ENGINE = InnoDB,
PARTITION p200912 VALUES LESS THAN (734138) ENGINE = InnoDB,
PARTITION p201001 VALUES LESS THAN (734169) ENGINE = InnoDB,
PARTITION p201002 VALUES LESS THAN (734197) ENGINE = InnoDB,
PARTITION p201003 VALUES LESS THAN (734228) ENGINE = InnoDB,
PARTITION p201004 VALUES LESS THAN (734258) ENGINE = InnoDB,
PARTITION p201005 VALUES LESS THAN (734289) ENGINE = InnoDB,
PARTITION p201006 VALUES LESS THAN (734319) ENGINE = InnoDB,
PARTITION p201007 VALUES LESS THAN (734350) ENGINE = InnoDB,
PARTITION p201008 VALUES LESS THAN (734381) ENGINE = InnoDB,
PARTITION p201009 VALUES LESS THAN (734411) ENGINE = InnoDB,
PARTITION p201010 VALUES LESS THAN (734442) ENGINE = InnoDB,
PARTITION p201011 VALUES LESS THAN (734472) ENGINE = InnoDB,
PARTITION p201012 VALUES LESS THAN (734503) ENGINE = InnoDB,
PARTITION p201101 VALUES LESS THAN (734534) ENGINE = InnoDB,
PARTITION p201102 VALUES LESS THAN (734562) ENGINE = InnoDB,
PARTITION p201103 VALUES LESS THAN (734593) ENGINE = InnoDB,
PARTITION p201104 VALUES LESS THAN (734623) ENGINE = InnoDB,
PARTITION p201105 VALUES LESS THAN (734654) ENGINE = InnoDB,
PARTITION p201106 VALUES LESS THAN (734684) ENGINE = InnoDB,
PARTITION p201107 VALUES LESS THAN (734715) ENGINE = InnoDB,
PARTITION p201108 VALUES LESS THAN (734746) ENGINE = InnoDB,
PARTITION p201109 VALUES LESS THAN (734776) ENGINE = InnoDB,
PARTITION p201110 VALUES LESS THAN (734807) ENGINE = InnoDB,
PARTITION p201111 VALUES LESS THAN (734837) ENGINE = InnoDB,
PARTITION p201112 VALUES LESS THAN (734868) ENGINE = InnoDB,
PARTITION p201201 VALUES LESS THAN (734899) ENGINE = InnoDB,
PARTITION p201202 VALUES LESS THAN (734928) ENGINE = InnoDB,
PARTITION p201203 VALUES LESS THAN (734959) ENGINE = InnoDB,
PARTITION p201204 VALUES LESS THAN (734989) ENGINE = InnoDB,
PARTITION p201205 VALUES LESS THAN (735020) ENGINE = InnoDB,
PARTITION p201206 VALUES LESS THAN (735050) ENGINE = InnoDB,
PARTITION p201207 VALUES LESS THAN (735081) ENGINE = InnoDB,
PARTITION p201208 VALUES LESS THAN (735112) ENGINE = InnoDB,
PARTITION p201209 VALUES LESS THAN (735142) ENGINE = InnoDB,
PARTITION p201210 VALUES LESS THAN (735173) ENGINE = InnoDB,
PARTITION p201211 VALUES LESS THAN (735203) ENGINE = InnoDB,
PARTITION p201212 VALUES LESS THAN (735234) ENGINE = InnoDB,
PARTITION p201301 VALUES LESS THAN (735265) ENGINE = InnoDB,
PARTITION p201302 VALUES LESS THAN (735293) ENGINE = InnoDB,
PARTITION p201303 VALUES LESS THAN (735324) ENGINE = InnoDB,
PARTITION p201304 VALUES LESS THAN (735354) ENGINE = InnoDB,
PARTITION p201305 VALUES LESS THAN (735385) ENGINE = InnoDB,
PARTITION p201306 VALUES LESS THAN (735415) ENGINE = InnoDB,
PARTITION p201307 VALUES LESS THAN (735446) ENGINE = InnoDB,
PARTITION p201308 VALUES LESS THAN (735477) ENGINE = InnoDB,
PARTITION p201309 VALUES LESS THAN (735507) ENGINE = InnoDB,
PARTITION p201310 VALUES LESS THAN (735538) ENGINE = InnoDB,
PARTITION p201311 VALUES LESS THAN (735568) ENGINE = InnoDB,
PARTITION p201312 VALUES LESS THAN (735599) ENGINE = InnoDB,
PARTITION p201401 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
(As of version 1.1), please provide output of
SELECT * FROM common_schema.status
mysql> select * from common_schema.status\G
*************************** 1. row ***************************
project_name: common_schema
version: 2.2
revision: 523
install_time: 2013-11-14 13:52:11
install_success: 1
base_components_installed: 1
innodb_plugin_components_installed: 1
percona_server_components_installed: 0
install_mysql_version: 5.6.12-enterprise-commercial-advanced-log
install_sql_mode: NO_AUTO_VALUE_ON_ZERO
Please provide any additional information below.
The MySQL Version has been changed to 5.6.14-enterprise_commercial-advanced-log.
Original issue reported on code.google.com by [email protected] on 15 Nov 2013 at 1:24
seq generator
CREATE TABLE `seq` (
`name` varchar(20) NOT NULL,
`val` int(10) unsigned NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM -- works inside transactions
DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS I.seq;
CREATE FUNCTION I.`seq`(seq_name char (20)) RETURNS int(11)
begin
update seq set val=last_insert_id(val+1) where name=seq_name;
return last_insert_id();
end;
DROP FUNCTION IF EXISTS I.seq_create;
CREATE FUNCTION I.`seq_create`(seq_name char (20), v int) RETURNS int(11)
begin
insert into seq values(seq_name,v);
return 0;
end;
DROP FUNCTION IF EXISTS I.seq_nextval;
CREATE FUNCTION I.`seq_nextval`(seq_name char (20)) RETURNS int(11)
begin
update seq set val=last_insert_id(val+1) where name=seq_name;
return last_insert_id();
end;
Original issue reported on code.google.com by [email protected] on 2 Aug 2011 at 10:41
Typo in Project Summary
Which version of common_schema are you using?
None currently, but I will be using the latest version (nominally v2.1) as soon
as my hosting provider allows.
Which component is failing?
Human component.
What is the expected output? What do you see instead?
Proper spelling is expected;. Instead I see a misspelled word.
Can you provide with sample data?
"Older versions are relesed under the New BSD License."
(As of version 1.1), please provide output of
SELECT * FROM common_schema.status
n/a
Please provide any additional information below.
In the "License" section of the Project Summary
(http://code.google.com/p/common-schema/, bottom of right-hand panel), the word
which is intended to be "released" is misspelled as "relesed". I checked the
LICENSE* files and those in the 'doc' folder, and this misspelling is not
present in those files.
Original issue reported on code.google.com by [email protected] on 9 Aug 2013 at 5:42
Type-Enhancement - show Innodb_buffer_pool usage via a view
What component are you suggesting? (view, table, function, procedure)
View
What will this new component do?
Report innodb_buffer_poll usage (Used%, Hit%, Dirty Pages%, Write Wait%)
Characteristics of the component? (e.g. NO SQL for stored function)
Attached file has SQL View which reports Used%, Hit%, Dirty Page% and Write
Wait% for the innodb_buffer_pool.
Dependencies for component?
MySQL 5.1
Compatibility with what kind of MySQL version/distribution?
MySQL 5.1 and innodb_plugin
OS specific issues?
N/A
Do you expect this to be a heavyweight operation? Why?
No - values are gathered from information_schema.global_status
Anything else?
Please label as "Type-Enhancement"
Original issue reported on code.google.com by [email protected] on 22 Jul 2011 at 2:29
Attachments:
SQL dependency check error on "UPDATE QUERY"
Which version of common_schema are you using? (specify
revision+distribution)
=> common_schema-1.3.sql
Which component is failing? (specify the view, function, etc.)
=> Procedure Error, Update check is not valid!
What is the expected output? What do you see instead?
=> If check sql dependency, it should report out the right table name, but out
"SET" as table name.
Can you provide with sample data?
sql> call get_sql_dependencies('update test set i = 1', 'dbatest');
+-------------+-------------+-------------+--------+
| schema_name | object_name | object_type | action |
+-------------+-------------+-------------+--------+
| dbatest | set | table | update |
+-------------+-------------+-------------+--------+
Also I find out what cause the result. This result from pattern of "UPDATE
QUERY", which expected table name right after "UPDATE" sentence.
you can check "_get_sql_dependencies_internal.sql" file and go to 72 Line.
I changed some actions for "update query" like this.
#### Original
if v_token in ('alter', 'call', 'create', 'delete', 'drop', 'insert', 'replace', 'select', 'truncate', 'update') then
set v_action = lower(v_token) collate utf8_general_ci
, v_scan_state = v_action
;
#### Fixed
if v_token in ('alter', 'call', 'create', 'delete', 'drop', 'insert', 'replace', 'select', 'truncate') then
set v_action = lower(v_token) collate utf8_general_ci
, v_scan_state = v_action
;
elseif v_token in ('update') then
set v_action = lower(v_token) collate utf8_general_ci
, v_scan_state = 'expect table'
;
I hope to change this bugs, and I also want to add other dependency check like
"TRIGGER".
Thank you.
From Chris in South Korea.
Original issue reported on code.google.com by [email protected] on 22 Jan 2013 at 1:23
enhance _wrap_select_list_columns to "pad" up to n columns
I would like to be able to call upon _wrap_select_list_columns with an
additional param, e.g.
CALL _wrap_select_list_columns("SELECT something FROM somewhere", 9)
such that if there are less than n columns to be wrapped, the procedure will
inject columns so that the query does return n columns.
Injected columns will continue the numbering scheme. If wrapped qurry returns
with col1, col2, col3 without injection, then injection pads col4, col5, col6,
.. coln
The data itself can be (and should be) NULLs; so the wrapped query would look
like:
SELECT (SELECT something) AS col1, NULL AS col2, NULL AS col3, ... FROM
somewhere.
This is required for continued work on foreach()
Original issue reported on code.google.com by [email protected] on 14 Sep 2011 at 5:22
MariaDB common-schema
Which version of common_schema are you using? (specify revision+distribution)
common_schema_percona_server-r218.sql
Which component is failing? (specify the view, function, etc.)
VIEW innodb_index_rows
VIEW innodb_index_stats
What is the expected output? What do you see instead?
Install common-schema. Error.
I don't know if this is MariaDB or Percona issue (I don't have a Percona server
to test).
Can you provide with sample data?
ERROR 1054 (42S22) at line 5410: Unknown column 'row_per_keys' in 'field list'
------------------------------
[root@process ~]# diff common_schema_percona_server-r218.sql
common_schema_percona_server-r218-MariaDB.sql
5420c5420
< TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(row_per_keys, ',', SEQ_IN_INDEX),
',', -1)) AS incremental_row_per_key
---
> TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(rows_per_key, ',', SEQ_IN_INDEX),
',', -1)) AS incremental_row_per_key
5435c5435
< ROUND((index_size - 1)/(index_size - leaf_pages), 1),
---
> ROUND((index_total_pages - 1)/(index_total_pages - index_leaf_pages),
1),
5439c5439
< ROUND(1 + log(leaf_pages)/log((index_size - 1)/(index_size -
leaf_pages)), 1),
---
> ROUND(1 + log(index_leaf_pages)/log((index_total_pages -
1)/(index_total_pages - index_leaf_pages)), 1),
[root@process ~]# mysql -u******* -p <
common_schema_percona_server-r218-MariaDB.sql
Enter password:
complete
Installation complete. Thank you for using common_schema!
Original issue reported on code.google.com by [email protected] on 5 Sep 2012 at 2:50
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.