xsist10 / cadfael Goto Github PK

View Code? Open in Web Editor NEW

45.0 45.0 6.0 19.81 MB

Tool for performing static analysis on the design and usage patterns of your MySQL database.

License: GNU General Public License v3.0

PHP 99.93% Makefile 0.07%

analysis database hacktoberfest mysql quality

cadfael's People

Contributors

Stargazers

Watchers

Forkers

martijnengler caleeli ctkqiang staabm isotopp nguyenanhung

cadfael's Issues

Bug in quering `events_statements_summary_by_digest` in MySQL 5.6.33

Description: Looks like older versions of MySQL don't have a QUERY_SAMPLE_TEXT column in performance_schema.events_statements_summary_by_digest.

Error:

  An exception occurred while executing '                                                       
                      SELECT *                                                                  
                      FROM performance_schema.events_statements_summary_by_digest               
                      WHERE SCHEMA_NAME = :schema                                               
                        AND QUERY_SAMPLE_TEXT NOT LIKE 'show %'                                 
                        AND QUERY_SAMPLE_TEXT NOT LIKE '% information_schema.%'                 
                        AND QUERY_SAMPLE_TEXT NOT LIKE '% mysql.%'                              
                        AND QUERY_SAMPLE_TEXT NOT LIKE '% sys.%'                                
                        AND QUERY_SAMPLE_TEXT NOT LIKE '% performance_schema.%'                 
                  '
                                                                                                
  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'QUERY_SAMPLE_TEXT' in 'where clause'

MySQL Version: 5.6.33

Query support

Add support to parse and store queries as its own type of Cadfael\Engine\Entity so checks can be added.

Documentation on updating Cadfael manually

We need to provide documentation on different ways to update Cadfael based on build.

Index Cardinality

Raise concerns about low cardinality indexes and attempt to make suggestions about better combinations to increase them.

TypeError: Cannot assign null to property Cadfael\Engine\Entity\Table\InformationSchema::$version of type string

running cadfael on my windows box runs into an error:

$ php cadfael.phar run --host mysql57.ab --username auser --port 3306 mydb
Cadfael CLI Tool

Host: mysql57.ab:3306
User: auser 

What is the database password?

PHP Fatal error:  Uncaught TypeError: Cannot assign null to property Cadfael\Engine\Entity\Table\InformationSchema::$version of type string in phar://C:/dvl/Workspace/cadfael/cadfael.phar/src/Engine/Entity/Table/InformationSchema.php:45
Stack trace:
#0 phar://C:/dvl/Workspace/cadfael/cadfael.phar/src/Engine/Entity/Table.php(151): Cadfael\Engine\Entity\Table\InformationSchema::createFromInformationSchema()
#1 phar://C:/dvl/Workspace/cadfael/cadfael.phar/src/Engine/Factory.php(478): Cadfael\Engine\Entity\Table::createFromInformationSchema()
#2 phar://C:/dvl/Workspace/cadfael/cadfael.phar/src/Cli/Command/RunCommand.php(145): Cadfael\Engine\Factory->buildDatabase()
#3 phar://C:/dvl/Workspace/cadfael/cadfael.phar/src/Cli/Command/RunCommand.php(271): Cadfael\Cli\Command\RunCommand->runChecksAgainstSchema()
#4 phar://C:/dvl/Workspace/cadfael/cadfael.phar/src/Cli/Command/RunCommand.php(298): Cadfael\Cli\Command\RunCommand->processSchemas()
#5 phar://C:/dvl/Workspace/cadfael/cadfael.phar/vendor/symfony/console/Command/Command.php(298): Cadfael\Cli\Command\RunCommand->execute()
#6 phar://C:/dvl/Workspace/cadfael/cadfael.phar/vendor/symfony/console/Application.php(1005): Symfony\Component\Console\Command\Command->run()
#7 phar://C:/dvl/Workspace/cadfael/cadfael.phar/vendor/symfony/console/Application.php(299): Symfony\Component\Console\Application->doRunCommand()
#8 phar://C:/dvl/Workspace/cadfael/cadfael.phar/vendor/symfony/console/Application.php(171): Symfony\Component\Console\Application->doRun()
#9 phar://C:/dvl/Workspace/cadfael/cadfael.phar/bin/cadfael(18): Symfony\Component\Console\Application->run()
#10 C:\dvl\Workspace\cadfael\cadfael.phar(14): require('...')
#11 {main}
  thrown in phar://C:/dvl/Workspace/cadfael/cadfael.phar/src/Engine/Entity/Table/InformationSchema.php on line 45

running on a windows box

 php -v
PHP 8.0.15 (cli) (built: Jan 18 2022 13:43:14) ( NTS Visual C++ 2019 x64 )
Copyright (c) The PHP Group
Zend Engine v4.0.15, Copyright (c) Zend Technologies

the mysql server version 5.7.33-0ubuntu0.16.04.1-log

tested on eda93c3

Empty tables in different TABLESPACE might have large data_free on creation

When a table is created normally like this:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

it will have an information_schema.TABLES entry with data_free that equals 0.

> select TABLE_NAME, DATA_FREE from information_schema.TABLES WHERE TABLE_SCHEMA="test" AND TABLE_NAME LIKE "user";
+------------+-----------+
| TABLE_NAME | DATA_FREE |
+------------+-----------+
| user       |         0 |
+------------+-----------+

However if you create it in a tablespace:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` int NOT NULL,
  PRIMARY KEY (`id`)
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB

it might have a completely different data_free value which currently will confuse Cadfael into reporting that it is an empty file but has had records previously:

> select TABLE_NAME, DATA_FREE from information_schema.TABLES WHERE TABLE_SCHEMA="test" AND TABLE_NAME LIKE "user";
+------------+-----------+
| TABLE_NAME | DATA_FREE |
+------------+-----------+
| user       |   5242880 |
+------------+-----------+

This data_free value is determined by:

> SELECT TABLESPACE_NAME, DATA_FREE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME LIKE 'innodb_system';
+-----------------+-----------+
| TABLESPACE_NAME | DATA_FREE |
+-----------------+-----------+
| innodb_system   |   5242880 |
+-----------------+-----------+

Add --html output

To support human consumption of the results, provide an option to output an HTML response.

Add --json output

To support non-human consumption of the results, provide an option to output a JSON response (which can then be consumed via tools like jq).

Check sql_require_primary_key=on

For MySQL 8.0+, this should be set to on to avoid future agony.

SET_VAR(sql_require_primary_key=ON)

To do this, you'll need to create a new check in src/Engine/Check/Database and get the database variables from $entity->getVariables().

You can use Check/Database/UnsupportedVersion.php as an example of how to build a check.

You will also need to add the new check here.

A unit test will also be needed to ensure the check works.

Check that the default authentication plugin is sane

Ensure that the system variable default_authentication_plugin is not set to a deprecated authentication plugin:

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_default_authentication_plugin
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin

Extension of checking if account are not using deprecated authentication plugins in this issue: #85

Ensure that the code doesn't die if the query parsing fails and returns a relevant warning to the user.

Per #66

print tool version

I think it would be helpful, if the tool would print its own version

Actual

$ php cadfael.phar run --host mysql57.ab --username auser --port 3306 mydb
Cadfael CLI Tool

Host: mysql57.ab:3306
User: auser

Expected

$ php cadfael.phar run --host mysql57.ab --username auser --port 3306 mydb
Cadfael CLI Tool 0.2.5

Host: mysql57.ab:3306
User: auser

Attempt to find out why the greenlion/php-sql-parser is dying on certain queries

Per #66

SELECT a FROM table INNER JOIN (SELECT b FROM table2);

SELECT
    a,
    b
FROM
    (
        `shop`
    JOIN table2 ON((table2.x = shop.y))
    );

Tablespace Entity

To support bugfix #19, we need to add a new Tablespace Entity

TODO

Add new TableSpace entity
Add Factory support to extract and build them
Attach TableSpace to appropriate Table entities

Index limit for text types

For high cardinality text types, any index should use a prefix limiter to avoid overly large indexes.

Example:

CREATE TABLE a (
    # ... other columns
    name VARCHAR(255),
    KEY idx_name (name)
);

would be better written as this:

CREATE TABLE a (
    # ... other columns
    name VARCHAR(255),
    KEY idx_name (name(12))
);

This avoids overly large indexes since the first 12 characters will likely provide enough cardinality to provide a fast lookup without wasting disk space.

Considerations

Sometimes, if query that a text field is part of returns only the columns in the index, then there might be a good use-case for keeping the full column value in the index.

CREATE TABLE a (
    # ... other columns
    name VARCHAR(255),
    city VARCHAR(30),
    postal_code VARCHAR(10),
    KEY idx_compound (city, postal_code, name)
);


# This will only need to use the index to collect the results and never have to lookup the actual table entries
SELECT name, city, postal_code
FROM a
WHERE postal_code = "AAAAA" AND city = "Paris" AND name = "Bob";

# This will still need to lookup the table records as `some_other_column` is not part of the index
SELECT some_other_column, name, city, postal_code
FROM a
WHERE postal_code = "AAAAA" AND city = "Paris" AND name = "Bob";

If we actually do implement an index prefix here like this:

CREATE TABLE a (
    # ... other columns
    name VARCHAR(255),
    city VARCHAR(30),
    postal_code VARCHAR(10),
    KEY idx_compound (city, postal_code, name(32))
);

Then this query won't be able to use just the index to return the result as name will not longer be completely stored in the index (only a subset of it) which means it will still need to lookup the table records.

SELECT name, city, postal_code
FROM a
WHERE postal_code = "AAAAA" AND city = "Paris" AND name = "Bob";

Add sql_mode setting checks

There are a number of warnings in certain versions of MySQL warning about deprecation or sql mode combinations.

Document and add these as checks.

Scratch pad of warnings seen in the wild

Warning (Code 3135): 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
Warning (Code 3090): Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.

Detect passwordless Accounts

Cadfael should detect accounts with no password.

In MySQL 8.0

mysql> create user cookie@"%";
mysql> select user, host, plugin, authentication_string from user where authentication_string = "";
...
| keks             | %         | caching_sha2_password |                                                                        |
...

Cadfael should flag accounts with an empty or NULL authentication_string field.

In MySQL 5.7

mysql [localhost:5734] {msandbox} (mysql) > select user, host, plugin, authentication_string from user where authentication_string = "";
+------+------+-----------------------+-----------------------+
| user | host | plugin                | authentication_string |
+------+------+-----------------------+-----------------------+
| keks | %    | mysql_native_password |                       |
+------+------+-----------------------+-----------------------+
1 row in set (0.00 sec)

Cadfael should flag account with an empty or NULL authentication_string field.

In MySQL 5.6

mysql [localhost:5644] {msandbox} (mysql) > select user, host, password, plugin, authentication_string from user where user = "keks";
+------+------+----------+-----------------------+-----------------------+
| user | host | password | plugin                | authentication_string |
+------+------+----------+-----------------------+-----------------------+
| keks | %    |          | mysql_native_password |                       |
+------+------+----------+-----------------------+-----------------------+
1 row in set (0.00 sec)

Cadfael should flag accounts with an empty Password field, if the plugin field has the value mysql_native_password.

In MySQL 5.5

mysql [localhost:5562] {msandbox} (mysql) > select user, host, password, plugin, authentication_string from user where user = "keks";
+------+------+----------+--------+-----------------------+
| user | host | password | plugin | authentication_string |
+------+------+----------+--------+-----------------------+
| keks | %    |          |        | NULL                  |
+------+------+----------+--------+-----------------------+
1 row in set (0.00 sec)

Cadfael should flag accounts with an empty Password field, if the plugin field is NULL or empty.

Add external configuration

To allow customized checks (both built in and third part) as well as pre-configuring thresholds and outputs, the tool needs to support some form of external configuration similar to tools like psalm.

--performance_schema short hand (-ps) collides with -p for port

When running the command with both --port PORT -ps the port value is dropped, and replaced with an s.

Add --password-file support

To allow non-interactive usage, allow a password file to be specified in place of entering the password for the database at the prompt.

This is to support the Unix Philosophy point 2:

Expect the output of every program to become the input to another, as yet unknown, program. Don't clutter output with extraneous information. Avoid stringently columnar or binary input formats. Don't insist on interactive input.

Support parsing SQL create statement

As an alternative to scanning a database, support providing a *.sql file with CREATE TABLE statements.

This will allow very paranoid people to avoid needing to give the tool access to the database.

Rate of Growth Check

Overview

Implement a check that measures rate of growth of tables to help detect:

Abnormal changes in the rate of growth (spikes/acceleration/drops).
Expected future issues with disk capacity (although we cannot detect the capacity of a database since that's OS level information and our checker might be running remotely, we can either request it in a configuration or use educated guesses).

Technical Implementation

For detecting rate of growth there are a few mechanisms but some require expected conventions (which aren't universal).

We will always require either a store to track how tables have changed over time (which requires us to keep a persistent store) or some form of timestamp on each record which we can accurately guess as the created date.

For the persistent store option, this will not be effective for CI/CD pipeline runs and will mean we can only get an answer after waiting a sufficiently long time between runs to see how the database has changed and we only have a resolution of the interval between each run of Cadfael. It will, however, be comprehensive since we can just store the information_schema.TABLES.TABLE_ROWS result for each table.

If we take the approach of looking for a timestamp. we can look for a date/time type field in the table with automatic initialization and without automatic updating. We also, because we will be selecting data from the table, would want either this field or an AUTO INCREMENT PRIMARY KEY for easy selection of subsets of data to determine rates of growth over various time periods. Since all these conditions won't be matched for every table, this approach would not be comprehensive but would be able to give an immediate answer without requiring sampling over a long period of time.

Sampling would be done like this:

Determine max PRIMARY KEY value (M) for the column (C)
Find timestamp (T1) of row where C=M
Find the timestamp (T2) of row where C=M/2
Determine gap between T1 and T2. If it's over THRESHOLD (possibly 1 day), sample between M - M/4 (between the two points)
Repeat process until resolution between entries is under THRESHOLD
Use processed intervals to develop rate of growth THRESHOLD to THRESHOLD over an extended period (with some cutoff based on age of data).

We could experiment with both options and see.

Work Needed

Implement check
Add test
Add documentation to Wiki

References

MySQL Timestamp Initialization

Ensure sql_mode is strict

Issues

Recommend that sql mode is set to strict avoid data truncation, bad charset conversion, weird groupings or other weird issues and have it throw errors on insertion.

 select @@sql_mode as sql_mode;

Remediation

Suggest MySQL 8.0 defaults

ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION

Recommend at least a minimum of

STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION

Work Needed

Implement check (use RequirePrimaryKey as a template).
Add test (use RequirePrimaryKeyTest as a template).
Add documentation to Wiki (use Force-Primary-Key-Requirement as a template).

References

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_mode
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict

Add reference property to reports

To take advantage of #4, a reference property should be added to the Check class and used to store the appropriate external reference link. This link should be displayed on all output formats.

Add getReferenceUri function to the Check interface
Create getReferenceUri function in each check that has a wiki reference and mark it return the URI of the reference.
- If no page is created yet, return empty string

filter issues by status

running php vendor/bin/cadfael run --host mysql80.ab --username testuser --port 3306 mydb -vvv yields a big wall of text.
its kind of overwhelming and identifying what is critical or needs attention first is hard to grasp for the user.

it would be great if I could filter to only show Critical, Warning,.. etc.
when filtered there could be a summary table on the bottom which shows me how much errors/warnings/.. I did not see, because of my current filter

Add CONTRIBUTING.md

Title says it all.

Identify incorrect storage for UUID

No one should be using CHAR or VARCHAR to store a UUID. BINARY(16) is the correct format.

Look for columns with uuid or id in the title and are string fields of 32-36 characters.

References

More Account Reports

Cadfael should do more reporting on accounts:

Accounts that use (for the server version outdated) authentication methods.
- 5.6 should alert on mysql_old_password, 8.0 on mysql_native_password.
Locked and expired accounts should be reported.
Accounts with excessive privileges should be reported
- Accounts with SUPER/rootlike/global privs.
- Accounts that can execute DDL.

Attempt queue detection using LOCK digests

Tables used as queues often have locking mechanisms used. Lets see if we can figure out a generic way to detect this.

Early exit for non-supported versions of MySQL

We don't want to support versions of MySQL before 5.6.

This should probably mention $active_version is not a supported version of MySQL. rather than $active_version is not a valid MySQL version.
Also add an inspection further up in the factory loading function so we bail out sooner on versions of MySQL (use $database->getVersion() after $database->set*() are called to get the version) that we don't support with a warning. Throwing an exception is probably the best way.

Document Query Design

Create a new wiki entry on ways to write efficient queries to provide guidance when the Inefficient Query check triggers.

Invalid schema specified: information_schema

running the tool against mysql 8.0.30-0ubuntu0.20.04.2 I am running into this error:

$ vendor/bin/cadfael run --host mysql80.ab --username testuser --port 3306 mydb -vvv

Cadfael CLI Tool

Host: mysql80.ab:3306
User: testuser

What is the database password?

[2022-09-14T16:17:13.612721+00:00] name.INFO: Collecting MySQL VARIABLES. [] []
[2022-09-14T16:17:13.633567+00:00] name.INFO: Collecting MySQL GLOBAL STATUS. [] []
[2022-09-14T16:17:13.636969+00:00] name.INFO: Collecting GRANTs. [] []
[2022-09-14T16:17:13.638765+00:00] name.INFO: Checking for permission to access mysql.SCHEMA_PRIVILEGES. [] []
[2022-09-14T16:17:13.638889+00:00] name.INFO: Collecting MySQL user accounts. [] []
[2022-09-14T16:17:13.641175+00:00] name.INFO: Collecting all table names in database. [] []
[2022-09-14T16:17:13.654122+00:00] name.INFO: Checking for permission to access information_schema.innodb_tablespaces. [] []
[2022-09-14T16:17:13.654328+00:00] name.INFO: Collecting MySQL tablespaces from information_schema.innodb_tablespaces. [] []
[2022-09-14T16:17:13.691405+00:00] name.INFO: Checking for permission to access information_schema.innodb_sys_tablespaces. [] []
[2022-09-14T16:17:13.691680+00:00] name.INFO: Checking for permission to access information_schema.TABLES. [] []
[2022-09-14T16:17:13.691815+00:00] name.INFO: Checking for permission to access information_schema.COLUMNS. [] []
[2022-09-14T16:17:13.691913+00:00] name.INFO: Checking for permission to access information_schema.STATISTICS. [] []
[2022-09-14T16:17:13.692016+00:00] name.INFO: Checking for permission to access mydb.?. [] []
[2022-09-14T16:17:13.692614+00:00] name.INFO: Collecting information_schema.TABLES. [] []
[2022-09-14T16:17:13.700318+00:00] name.INFO: Collecting information_schema.COLUMNS. [] []
[2022-09-14T16:17:13.723562+00:00] name.INFO: Collecting information_schema.STATISTICS. [] []
[2022-09-14T16:17:13.733909+00:00] name.INFO: Collecting schemas. [] []
[2022-09-14T16:17:13.735840+00:00] name.INFO: Checking for permission to access sys.schema_auto_increment_columns. [] []
[2022-09-14T16:17:13.735952+00:00] name.INFO: Collecting sys.schema_auto_increment_columns. [] []
[2022-09-14T16:17:13.747667+00:00] name.INFO: Checking for permission to access sys.schema_index_statistics. [] []
[2022-09-14T16:17:13.747792+00:00] name.INFO: Collecting sys.schema_index_statistics. [] []
[2022-09-14T16:17:14.179659+00:00] name.INFO: Checking for permission to access mysql.innodb_index_stats. [] []
[2022-09-14T16:17:14.179798+00:00] name.INFO: Collecting mysql.innodb_index_stats. [] []
[2022-09-14T16:17:14.182407+00:00] name.INFO: Constructing indexes. [] []
[2022-09-14T16:17:14.183191+00:00] name.INFO: Checking for permission to access sys.schema_redundant_indexes. [] []
[2022-09-14T16:17:14.183318+00:00] name.INFO: Collecting sys.schema_redundant_indexes. [] []
[2022-09-14T16:17:14.361977+00:00] name.INFO: Checking for permission to access information_schema.innodb_tables. [] []
[2022-09-14T16:17:14.362173+00:00] name.INFO: Checking for permission to access information_schema.innodb_sys_tables. [] []
[2022-09-14T16:17:14.362267+00:00] name.INFO: Checking for permission to access performance_schema.?. [] []

In Database.php line 247:
 Cadfael\Engine\Factory->buildDatabase() at C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Cli\Command\RunCommand.php:146
 Cadfael\Cli\Command\RunCommand->runChecksAgainstSchema() at C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Cli\Command\RunCommand.php:272
 Cadfael\Cli\Command\RunCommand->processSchemas() at C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Cli\Command\RunCommand.php:299
 Cadfael\Cli\Command\RunCommand->execute() at C:\dvl\Workspace\cadfael\vendor\symfony\console\Command\Command.php:298
 Symfony\Component\Console\Command\Command->run() at C:\dvl\Workspace\cadfael\vendor\symfony\console\Application.php:1028
 Symfony\Component\Console\Application->doRunCommand() at C:\dvl\Workspace\cadfael\vendor\symfony\console\Application.php:299
 Symfony\Component\Console\Application->doRun() at C:\dvl\Workspace\cadfael\vendor\symfony\console\Application.php:171
 Symfony\Component\Console\Application->run() at C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\bin\cadfael:19
 include() at C:\dvl\Workspace\cadfael\vendor\bin\cadfael:120

run [--host HOST] [-p|--port PORT] [-u|--username USERNAME] [-s|--secret SECRET] [-ps|--performance_schema] [-o|--output-format OUTPUT-FORMAT] [--] <schema>...

tested on eda93c3

running on a windows box

 php -v
PHP 8.0.15 (cli) (built: Jan 18 2022 13:43:14) ( NTS Visual C++ 2019 x64 )
Copyright (c) The PHP Group
Zend Engine v4.0.15, Copyright (c) Zend Technologies

Provide a catalogue of reasoning

Whenever the engine provides a recommendation, it should also provide a reference to the reasoning behind the recommendation. To avoid the output being too verbose, a separate catalogue that the user can lookup should be included as a separate command.

Don't load performance_schema data unless the --performance_schema is passed.

Per #66 don't load the performance_schema information unless the user has specified the --performance_schema flag.

Identify incorrect column definition for emails

Create a check to identify whether an email column has problems with it's definitions.

First you'll need to identify if you think a column is an email field.
This can be done by looking at the meta data about the column (like the COMMENT and the data type) and for phrase that match email in the name of the column.

An email column should be:

Maximum of 254 characters
Must support emoji characters (CHARACTER SET utf8mb4)
[Optional] Validate a sample of the data stored in the field. Don't write the validation logic. Consider using something like https://packagist.org/packages/egulias/email-validator

A test should be provided with this to ensure that we a) detect email like fields and b) correctly identify issues.

References:

Mock up a new text output format

Our tabular text output isn't scaling well:

It requires extra wide terminal window
It's visually unappealing
It violates the Unix Philosophy point 2:

Expect the output of every program to become the input to another, as yet unknown, program. Don't clutter output with extraneous information. Avoid stringently columnar or binary input formats. Don't insist on interactive input.

Suggestion

Output similar to PHPUnit where . indicates a passed check, s indicates a skipped check and n, c, w and f indicate a notice, concern, warning or failure.

.....s.s.s......nn....c...w.ffff....
......ss...c.cnw...wwwcss......fwww.

This is then followed by the issues listed sequentially (grouped by either check or entity examined)

Identify high cardinality columns with expensive storage

If a column has high cardinality with expensive fields (large string inputs), it's possible that it would make sense to:

Denormalize it
Change it to an enum

Undefined array key "no_quotes

after I got the tool working with the Fix from #65

I am now running into more errors

PHP Warning:  Undefined array key "no_quotes" in C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Engine\Entity\Query.php on line 138
PHP Warning:  Trying to access array offset on value of type null in C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Engine\Entity\Query.php on line 138
PHP Fatal error:  Uncaught TypeError: array_pop(): Argument #1 ($array) must be of type array, null given in C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Engine\Entity\Query.php:140
Stack trace:
#0 C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Engine\Entity\Query.php(140): array_pop()
#1 C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Engine\Entity\Query.php(110): Cadfael\Engine\Entity\Query->getTableNamesInQuery()
#2 C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Engine\Factory.php(155): Cadfael\Engine\Entity\Query->linkTablesToQuery()
#3 C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Engine\Factory.php(727): Cadfael\Engine\Factory->getEventStatementsSummary()
#4 C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Cli\Command\RunCommand.php(145): Cadfael\Engine\Factory->buildDatabase()
#5 C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Cli\Command\RunCommand.php(271): Cadfael\Cli\Command\RunCommand->runChecksAgainstSchema()
#6 C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Cli\Command\RunCommand.php(298): Cadfael\Cli\Command\RunCommand->processSchemas()
#7 C:\dvl\Workspace\cadfael\vendor\symfony\console\Command\Command.php(298): Cadfael\Cli\Command\RunCommand->execute()
#8 C:\dvl\Workspace\cadfael\vendor\symfony\console\Application.php(1028): Symfony\Component\Console\Command\Command->run()
#9 C:\dvl\Workspace\cadfael\vendor\symfony\console\Application.php(299): Symfony\Component\Console\Application->doRunCommand()
#10 C:\dvl\Workspace\cadfael\vendor\symfony\console\Application.php(171): Symfony\Component\Console\Application->doRun()
#11 C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\bin\cadfael(19): Symfony\Component\Console\Application->run()
#12 C:\dvl\Workspace\cadfael\vendor\bin\cadfael(120): include('...')
#13 {main}
  thrown in C:\dvl\Workspace\cadfael\vendor\cadfael\cadfael\src\Engine\Entity\Query.php on line 140

I think cadfael is trying to parse a sql statement - which it somehow found in the server.

tested on eda93c3

Detect UUID use/type for PRIMARY KEY

Using a UUID as a PRIMARY KEY has various performance problems however these are being addressed by newer versions of UUID (UUIDv6 or UUIDv7) or using UUID_TO_BIN(string_uuid, swap_flag) to re-order the records on INSERT/SELECT, but we need to be able to detect which version is in use before we advice a fix.

Requirements:

Implement Check
Add tests
Add documentation to Wiki

Sane Auto Increment checks for AUTO_INCREMENT starting point

The Sane Auto Increment check should take into account that the user manually specified a starting point in the negative range.

Support levels of issues

To make it easier to use Cadfael for CI (Cadfael exits with an error if any checks fail), allow the user to specify what level of reports to exit on.

To make the process faster, we can exclude checks what don't have a reporting that level or higher.

This means we need checks to report their highest level of status.

Avoid date functions when an index is available

 DATE(`date_field`) = `context`.`date`

Almost always that should have been phrased as:

 ( `date_field` >= `context`.`date`
   AND
   `date_field` < DATE_ADD(`context`.`date`, INTERVAL 1 DAY)
 )

to make it use an index.

Fix time based testing so it doesn't start failing randomly

Change UnsupportedVersion to use carbon to manage time so it can be properly mocked

EmptyTable check should use the auto increment starting point not 0

The EmptyTable Check assumes that the auto increment value starts at 0. It should use the defined starting point defined by the table since this can be set independently.

ALTER TABLE t1 AUTO_INCREMENT = -100;

Also need to find out where this value is stored in MySQL.

support older php versions

in https://github.com/xsist10/cadfael/releases/tag/0.3.1 you mentioned

To offset this for users stuck on earlier version I will be looking at providing a docker based version that can be run from a container to make it easier for portable usage.

instead of doing the docker dance, you might alternatively use a simpler method.
downgrading the used functions and automatically release a separate package, as described here:
https://tomasvotruba.com/blog/how-to-release-php-81-and-72-package-in-the-same-repository/

IMO thats easier to implement for you and also easier to consume from user side - as it does not require docker as an addtional dependency

Updating Cadfael

Currently there is no documentation on updating Cadfael and no automated way to detect if it should be updated or ways to make it auto-update itself.

#40
Add mechanism for checking what the latest release is (both major and minor)
Add mechanism for auto-updating a local install if it's a phar.

xsist10 / cadfael Goto Github PK

cadfael's People

Contributors

Stargazers

Watchers

Forkers

cadfael's Issues

TODO

Considerations

Scratch pad of warnings seen in the wild

In MySQL 8.0

In MySQL 5.7

In MySQL 5.6

In MySQL 5.5

Overview

Technical Implementation

Work Needed

References

Issues

Remediation

Suggest MySQL 8.0 defaults

Recommend at least a minimum of

Work Needed

References

Suggestion

Recommend Projects

Recommend Topics

Recommend Org