hepabolu / mytap Goto Github PK

I could not find any example on how to test stored procedure? I see , I can assert using two variables but how do I catch stored procedure result set in variable without out parameter ?

Now that we have support for multiple versions of MySQL it would be great if we could show that by adding build labels for various MySQL versions to the README.

Need to look into configuring Travis to run the tests against various MySQL versions. Maybe using Docker?

Travis tasks don't work any more. Convert them to GitHub actions

The gh-pages branch should render https://hepabolu.github.com/mytap/ but does not. Maybe the GitHub Pages switch needs to be turned on in settings?

table_sha1() is he only function that doesn't pass the test suite. Looks like a MySQL bug, fix or remove?

Hi,
I am trying to figure out if myTap supports Aurora Mysql 5.7?
thanks
Ali

Hi all,

This project is great to offer the way to unit test SQL. I searched in this project and googled a lot, but still not found how to write mytap test to check deadlock issue, can you give me 1 example about this?

Thanks so much.

Objects with skipped tests should have version files created so that incremental version changes can be applied.

I need these tap tests to work on earlier versions as well, i.e. 5.5.x and 5.6.x. The function _table_sha1 concatenates info in multiple columns, a.o. 'generation_expression'. This column doesn't exist in version 5.5. and 5.6.

The code should accommodate for various versions.

Not sure how to proceed, because it's a lot of code duplication if we build a function per version.

In mytap-column.sql, line 395, there's a typo descriptio which can be easily fixed.

It looks as if MR #51 already fixes this. Can that MR be merged and a new patch release be created?

When writing the mytap_privileges code I wrote some general methods for dealing with the whateverobjecttype_are style tests. This was to deal with an earlier failure to apply the DRY principle which was only going to get worse with half a dozen new functions requiring temp tables to be populated and their contents compared.

The new code is working successfully in privileges so it seems an excellent opportunity to apply these new methods to the existing object types. This will affect pretty much every mytap-objectname.sql file and result in the removal of hundreds of lines of almost identical code.

I'm proposing a new branch to deal with all of these changes in one go, it'll look like a massive change but there should be no external changes and, of course, the tests will be unchanged.

Installer required to match capabilities of runtests.sh (host, port). Provide incremental addition of version files. Consider removal of functions e.g. table_sha1() doesn't work in 8.0 (probably a mysql bug), could revove this functionality from the affected version with a drop function ... in a version file.

The enum at line 236 of /mytap-partition.sql needs to allow for 'RANGE COLUMNS'

I'm going to be using mariadb quite a bit over the coming months so I'm planning to do some testing of the project against various mariadb versions and make any necessary changes. This will probably necessitate some changes with the installer to recognise the different versions and possibly a few other changes to accommodate information_schema differences.

MySQL 5.6.38

ERROR 1172 (42000) at line 38 in file: 
'tests/test-mytap-view.sql': Result consisted of more than one row

Feature I miss the most in myTAP is organizing tests in test cases, with possibility of having test suites in near future. My understanding of cases and suites is:

• a test case is a set of assertions
• a test suite is a set of test cases

Yesterday I wrote a POC of pgTAP runtests() in MySQL, today I tried to write some test cases on it, but I feel I failed.

Anyhow, here it is: https://gist.github.com/sq6jnx/26ece74af8882655464b843f36657cc8. Please let me know what do you think about it, what is OK and what can be done better.

And yes, I still feel novice in myTAP and unit testing.

EDIT: I strongly believe, that test cases should run in random order. I don't think pgTAP can do that nor I don't think it should be default behaviour in myTAP, but I'd like to have it.

ERROR 1235 (42000) at line 45 in file: 'tests/test-mytap-trigger.sql': This version of MySQL 
doesn't yet support 'multiple triggers with the same action time and event for one table'

MySQL v5.6.38 used when running the tests.

When we tried to use MyTAP at work, it seemed to be fine on some dev servers, but for sandboxes, it was failing with the following error:

Deploying changes to tap
  + mytap_schema @v0.03 .. ERROR 1418 (HY000) at line 28 in file: '/tmp/o6m_IzqHJP': This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

In MySQL, if you have binary logging enabled, functions which are not deterministic might cause issues in data corruption in replication or data restoration. Here is the relevant section from the MySQL docs:

When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.

By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you might want to use the less safe log_bin_trust_function_creators
variable)

This function is deterministic (and does not modify data), so it is safe:

CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN i;
END;

This function uses UUID(), which is not deterministic, so the function also is not deterministic and is not safe:

CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
  RETURN UUID();
END;

It should be able to be set via the options in the install script.

MySQL 5.6.38

ERROR 1064 (42000) at line 10 in file: 'tests/test-mytap-user.sql': 
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 'IF EXISTS '__tapuser__'@'localhost'' at line 1

'if exists' doesn't exist in version 5.6.38

Hi,

I forked your project a couple of weeks back and have been working on extending some of the tests but keep hitting a brick wall trying to run functions in the mytap-routines.sql file.

e.g.
mysql> SELECT tap.has_function('sakila','inventory_in_stock','');
ERROR 1264 (22003): Out of range value for column 'CHARACTER_MAXIMUM_LENGTH' at row 1

I can run the database test on it's own
e.g.
mysql> SELECT tap._has_routine('sakila','inventory_in_stock','FUNCTION');
+------------------------------------------------------------+
| tap._has_routine('sakila','inventory_in_stock','FUNCTION') |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

and, after altering _has_routine so it has a function body of RETURN 1 , I can select has_function successfully and produce tap output.

I've no issues with any of the column, table or view tests, they're all fine.

I've been through information_schema.routines, there's nothing dubious in CHARACTER_MAXIMUM_LENGTH, not that we're actually touching that column here.

The raw query comes back correctly as

mysql> SELECT true into @b_result FROM information_schema.routines as db WHERE db.routine_schema = 'sakila' AND db.routine_name = 'inventory_in_stock' AND db.routine_type = 'FUNCTION';
Query OK, 1 row affected (0.01 sec)

mysql> select @b_result;
+-----------+
| @b_result |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)

This query also comes back fine.

and the row queries as

mysql> SELECT * FROM information_schema.routines WHERE routine_schema = 'sakila' AND routine_name = 'inventory_in_stock' AND routine_type = 'FUNCTION'\G
*************************** 1. row ***************************
SPECIFIC_NAME: inventory_in_stock
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: sakila
ROUTINE_NAME: inventory_in_stock
ROUTINE_TYPE: FUNCTION
DATA_TYPE: tinyint
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 3
NUMERIC_SCALE: 0
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: tinyint(1)
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
DECLARE v_rentals INT;
DECLARE v_out INT;

SELECT COUNT(*) INTO v_rentals
FROM rental
WHERE inventory_id = p_inventory_id;

IF v_rentals = 0 THEN
  RETURN TRUE;
END IF;

SELECT COUNT(rental_id) INTO v_out
FROM inventory LEFT JOIN rental USING(inventory_id)
WHERE inventory.inventory_id = p_inventory_id
AND rental.return_date IS NULL;

IF v_out > 0 THEN
  RETURN FALSE;
ELSE
  RETURN TRUE;
END IF;

END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: READS SQL DATA
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2017-11-04 15:10:48
LAST_ALTERED: 2017-11-04 15:10:48
SQL_MODE: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: paul@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)

I've googled the error and found nothing relevant, run the function against 4 different schemas, I've even tried on a fresh install of mysql (5.7.20), same result. Have you seen this error before?

Thanks

I was wondering that on your homepage you have an insert example:

-- Insert stuff.
SELECT tap.ok(
    insert_stuff( 'www.foo.com', '1,2,3', @domain_id, @src_id ),
    'insert_stuff() should return true'
);    

How does this actually work? MySQL chokes on putting INSERT INTO... inside the tap.ok().

How else would you test if an insertion worked or not?

In my case, the insertion should actually result in an error involving the primary key. But I cannot find a way to run a query inside a SQL script that would error and continue. Kind of like try catch inside SQL. Does mytap address this need?

I'm going to add some user privilege tests. Some will likely be focused around whether a particular privilege has been granted to a certain user and the level at which the privilege is granted e.g. user/role, schema, table.

Any other suggestions would be appreciated.

most likely just a typo (s/pg/my), but the documentation for todo( how_many, why ) states pgTAP will run the tests normally, but print out special flags indicating they are “todo” tests. but does not state myTAP behavior

https://hepabolu.github.io/mytap/documentation/

I'm trying to install current master (14513c6) on 5.7.27-0ubuntu0.18.04.1. The test suite takes couple of seconds, but fails on:

# ...
ok 192 - col_charset_is() description supplied should have the proper description
ok 193 - col_charset_is() column not found diagnostic should fail
ok 194 - col_charset_is() column not found diagnostic should have the proper diagnostics
ERROR 1436 (HY000) at line 1333 in file: 'tests/test-mytap-column.my': Thread stack overrun:  68896 bytes used of a 196608 byte stack, and 128000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.
============= constraints ============
1..127
ERROR 1172 (42000) at line 58 in file: 'tests/test-mytap-constraint.my': Result consisted of more than one row
============= engines ============
1..14
ERROR 1172 (42000) at line 17 in file: 'tests/test-mytap-engine.my': Result consisted of more than one row
============= events ============
1..64
ERROR 1172 (42000) at line 28 in file: 'tests/test-mytap-event.my': Result consisted of more than one row
============= indexes ============
1..57
ERROR 1172 (42000) at line 30 in file: 'tests/test-mytap-index.my': Result consisted of more than one row
============= partitions ============
1..92
ERROR 1172 (42000) at line 64 in file: 'tests/test-mytap-partition.my': Result consisted of more than one row
============= privileges ============
1..208
ERROR 1045 (28000) at line 64 in file: 'tests/test-mytap-privilege.my': Access denied for user 'sq6jnx'@'%' (using password: YES)
============= role ============
1..33
ok 1 - SKIP: Requires MySQL version >= 8.0.11
ok 2 - SKIP: Requires MySQL version >= 8.0.11
ok 3 - SKIP: Requires MySQL version >= 8.0.11
# ...

I've tried to $ sudo mysqld --thread_stack=512000, but behaviour is the same.

What I've forgot?

Hi, this looks very promising. However I can't install it since there is a major typo; you wrote:

DELIMITER //

DROP FUNCTION IF EXISTS mytap_version;
CREATE FUNCTION mytap_version()
RETURNS NUMERIC
BEGIN
RETURN 0.01;
END //

this can't work because of the ; at the end of the DROP line. It should be:

DELIMITER //

DROP FUNCTION IF EXISTS mytap_version //
CREATE FUNCTION mytap_version()
RETURNS NUMERIC
BEGIN
RETURN 0.01;
END //

for every function...

Helma,
I've been working for some time on my fork of this repository and reached a point where I've done all the things I'd like to achieve with the code. At this point I'd be looking to do a pull request into the main repository in the hope that you'd take the changes. However, I tend to work in concentrated bursts and I tend to make rather sweeping changes to which can affect large sections of the code so the my fork eyour repository have now converged considerably. I'll summarise the main differences.

The categories of database objects that can be tested now extends to

mytap-global.sql (variables)
mytap-schemata.sql
mytap-engine.sql
mytap-collation.sql
mytap-charset.sql
mytap-user.sql
mytap-event.sql
mytap-table.sql
mytap-view.sql
mytap-column.sql
mytap-trigger.sql
mytap-routines.soriesql
mytap-constraint.sql
mytap-index.sql
mytap-partition.sql

Within each class of object, I've added methods to test most, if not always all, the properties of the object that can be found in the information_schema. I've also added tests, which exist in pgTAP, to ensure that only the correct objects exist e.g. columns_are(), tables_are(), routines_are etc.

Function parameters have been changed from TEXT to match the specification of the schema object being tested, so schema, table and column names are all VARCHAR(64) etc. Parameter names more closely match the objects tested and are applied consistently.

Default descriptions are moved before any diagnostic tests. This makes it possible to see which test the diagnostic message applies to rather than overwriting the description as currently happens. Diagnostic tests are applied to check the existence of the parent object of the one being tested. I think this is currently broken in every testing function.

I've rewritten the tests to provide comprehensive coverage for all the new object tests. Although check_test allows 3 aspects of the function to be tested simultaneously I've separated the testing of pass/fail from the test on the description and diagnostic messages because I'm of the school that believes tests each test should test should apply to one, and only one, aspect under test.

I've added a script autotap.sql which, given a schema name, will generate appropriate tests to check all objects in the shema. The assumption is that the current state is actually correct. This should allow people to retrofit TAP tests to existing projects. For example, if you apply the script to the sakila test database it will generate 1168 tests on the schema, tables, view, columns, constraints, indexes etc.

The changes are rather extensive, so I think it might be useful if you were able to look over my fork to see if there's any aspect you don't agree which I could change and decide if it's appropriate to pull the changes into the main repo.

Paul Campbell

Check if the mysql.time_zone* tables are filled. And/or check if the server correctly handles timezones.