DBReport : A DataBase structure Reporting tool for database administrators

Documentation : DBReport.html





By Patrice Dargenton : patrice.dargenton@free.fr



Version 1.05 - 05/03/2017


Database Administrators needs to compare database structures. Using WinMerge on sql database structure files, it is difficult to compare because a lot of differences appear, whereas only a few of them are meaningful. DBReport shows only (and all) significant information that makes sense for daily administrator work.



Table of contents

Table of contents. 1

Features. 1

Example with the classical Northwind database. 2

Explanation. 5

Not nullable foreign key. 5

Not nullable without default value. 5

Nullable field for a unique index. 5

MySql parameters. 5

sql_mode. 5

innodb_strict_mode. 5

collation. 6

table engine. 6

timeout 6

Queries. 6

How to change the server collation ?. 6

Projects. 7

Versions. 7

Version 1.05 - 05/03/2017. 7

Version 1.04 - 23/10/2016. 7

Version 1.03 - 18/09/2016. 7

Version 1.02 - 24/01/2016. 7

Version 1.01 - 03/01/2016 : First version. 8

Links. 8

See also. 8




- Tables, fields and table relations (links) are displayed ;

- Field types and (if available) default values are displayed ;

- Table and field description are displayed, if available ;

- Index and links (relationships between tables) are sorted and displayed (nobody needs to care about index order) ;

- Update and delete rules for relationships are displayed, if they are different to the default RESTRICT mode, for exemple : (Delete rule : CASCADE) (Update rule : CASCADE) ;

- Duplicate constraints are displayed : the same constraint can be added several times, and it can be hard to detect using an old version of phpMyAdmin (e.g. the version 4.1.4).



Example with the classical Northwind database






Database report 1.05



Login    : root

Server   : localhost

Database : northwind

Indexes  : Sorted


MySql parameters :

version_comment : MySQL Community Server (GPL)

version : 5.6.15-log

protocol_version : 10


innodb_strict_mode : OFF (Default : ON)

net_read_timeout : 30 (30 sec.)

net_write_timeout : 60 (1 mn)

Database default collation (DEFAULT_COLLATION_NAME) : utf8_unicode_ci (Default : utf8_general_ci)



  CategoryID (tinyint(5) unsigned) (autonumber)

  CategoryName (varchar(15)) ('')

  Description (mediumtext)

  Picture (varchar(50)) ('')

    Index   : CategoryID, Primary, Unique

    Index   : CategoryName, Unique



  CustomerID (varchar(5)) ('')

  CompanyName (varchar(40)) ('')

  ContactName (varchar(30)) (Unknown)

  ContactTitle (varchar(30)) ('')

  Address (varchar(60)) ('')

  City (varchar(15)) ('')

  Region (varchar(15)) ('')

  PostalCode (varchar(10)) ('')

  Country (varchar(15)) ('')

  Phone (varchar(24)) ('')

  Fax (varchar(24)) ('')

    Index   : City

    Index   : CompanyName

    Index   : PostalCode

    Index   : Region

    Index   : CustomerID, Primary, Unique



  EmployeeID (int(10) unsigned) (autonumber)

  LastName (varchar(20)) ('')

  FirstName (varchar(10)) ('')

  Title (varchar(30)) ('')

  TitleOfCourtesy (varchar(25)) ('')

  BirthDate (datetime)

  HireDate (datetime)

  Address (varchar(60)) ('')

  City (varchar(15)) ('')

  Region (varchar(15)) ('')

  PostalCode (varchar(10)) ('')

  Country (varchar(15)) ('')

  HomePhone (varchar(24)) ('')

  Extension (varchar(4)) ('')

  Photo (varchar(50)) ('')

  Notes (mediumtext)

  ReportsTo (int(10) unsigned)

    Index   : LastName

    Index   : PostalCode

    Index   : ReportsTo

    Index   : EmployeeID, Primary, Unique



  OrderID (int(10) unsigned) (autonumber)

  CustomerID (varchar(5)) ('')

  EmployeeID (int(10) unsigned)

  OrderDate (datetime)

  RequiredDate (datetime)

  ShippedDate (datetime)

  ShipVia (int(10) unsigned)

  Freight (double) (0)

  ShipName (varchar(40)) ('')

  ShipAddress (varchar(60)) ('')

  ShipCity (varchar(15)) ('')

  ShipRegion (varchar(15)) ('')

  ShipPostalCode (varchar(10)) ('')

  ShipCountry (varchar(15)) ('')

    Index   : CustomerID

    Index   : EmployeeID

    Index   : ShipVia

    Index   : ShipPostalCode

    Index   : ShippedDate

    Index   : OrderID, Primary, Unique



  ID (int(10) unsigned) (autonumber)

  OrderID (int(10) unsigned)

  ProductID (int(10) unsigned)

  UnitPrice (double unsigned) (0)

  Quantity (smallint(5) unsigned) (1)

  Discount (float unsigned) (0)

    Index   : ProductID

    Index   : ID, Primary, Unique

    Index   : Uidx_OrderID_ProductID, Unique, 2 fields :

      field : OrderID

      field : ProductID



  ProductID (int(10) unsigned) (autonumber)

  ProductName (varchar(40)) ('')

  SupplierID (int(10) unsigned)

  CategoryID (tinyint(5) unsigned)

  QuantityPerUnit (varchar(20)) ('')

  UnitPrice (double) (0)

  UnitsInStock (smallint(5) unsigned) (0)

  UnitsOnOrder (smallint(5) unsigned) (0)

  ReorderLevel (smallint(5) unsigned) (0)

  Discontinued (enum('y','n')) (n)

    Index   : CategoryID

    Index   : SupplierID

    Index   : ProductName

    Index   : ProductID, Primary, Unique



  ShipperID (int(10) unsigned) (autonumber)

  CompanyName (varchar(40)) ('')

  Phone (varchar(24)) ('')

    Index   : ShipperID, Primary, Unique



  SupplierID (int(10) unsigned) (autonumber)

  CompanyName (varchar(40)) ('')

  ContactName (varchar(30)) ('')

  ContactTitle (varchar(30)) ('')

  Address (varchar(60)) ('')

  City (varchar(15)) ('')

  Region (varchar(15)) ('')

  PostalCode (varchar(10)) ('')

  Country (varchar(15)) ('')

  Phone (varchar(24)) ('')

  Fax (varchar(24)) ('')

  HomePage (varchar(255)) ('') (collation : utf8_general_ci)

    Index   : PostalCode

    Index   : CompanyName

    Index   : SupplierID, Primary, Unique










  employees : ReportsTo



  customers : CustomerID

  employees : EmployeeID

  shippers : ShipVia



  orders : OrderID

  products : ProductID



  categories : CategoryID

  suppliers : SupplierID






Report created : 19/02/2017 12:14:11 -> 19/02/2017 12:14:12 : 1.5 sec.





Not nullable foreign key

This information reminds that this field needs to be filled, otherwise an error will be thrown.


Not nullable without default value

This information reminds that this field needs to be filled, otherwise an error may or may not be thrown, according to the database server setting. For example, see the MySql strict mode. If you provide a default value for all fields in this case, you will avoid this trap, and you won't have this warning anymore. Otherwise, it is safe to set the same strict mode setting for your development environment as your production environment.


Nullable field for a unique index

MySQL (5.6) can't guarantee uniqueness (unicity) if one field of a unique key is nullable, you can have duplicates records in the table. Consider using a conventional mnemonic code instead, for example '_ALL' for 'all the items' of this field, and set it to not nullable.


MySql parameters


If the database provider corresponds to MySql ("MySql.Data.MySqlClient"), then the following main MySql parameters are displayed in the report, but only if they are different form their default value : sql_mode, innodb_strict_mode, collation, table engine, timeout.


List of all system variables and options :





sql_mode controls what SQL syntax MySQL accepts, and determines whether it silently ignores errors, or validates input syntax and data values. For example, if sql_mode is empty, implicit conversions can be performed without error (but only with warnings), see full documentation :






To understand the strict versus not strict sql_mode with a sample, see the section titled "The Effect of Strict SQL Mode on Statement Execution" there :




When innodb_strict_mode is ON, InnoDB returns errors rather than warnings for certain conditions. This is analogous to sql_mode in MySQL, it enables additional error checks for InnoDB tables :



The default value is ON since MySQL 5.7.7.


"Oracle recommends enabling innodb_strict_mode when using ROW_FORMAT and KEY_BLOCK_SIZE clauses in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements. When innodb_strict_mode is disabled, InnoDB ignores conflicting clauses and creates the table or index with only a warning in the message log. The resulting table might have different characteristics than intended, such as lack of compression support when attempting to create a compressed table. When innodb_strict_mode is enabled, such problems generate an immediate error and the table or index is not created."



Once the default collation is set for DBReport, the database default collation is displayed if it is different from the default collation. The table collation is displayed in the same way, and finally each column collation.


Column collation is important because adding a foreign key may fail if the collation of columns is not the same.


table engine

Once the default engine is set for DBReport, the table engine is displayed if it is different from the default table engine.



The actual session level value for net_read_timeout and net_write_timeout are displayed. They have the same value as the global variable, if no session level instruction changes their value.




Here are the queries for these MySql parameters for the Northwind database (schema_name and table_schema are the database name) :


SHOW VARIABLES WHERE Variable_Name IN ('version', 'version_comment', 'protocol_version', 'sql_mode', 'innodb_strict_mode', 'net_read_timeout', 'net_write_timeout');


SHOW VARIABLES LIKE 'collation_server';




SELECT TABLE_NAME, ENGINE, COLLATION_NAME FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = 'northwind';


SELECT table_name, C.column_name, COLLATION_NAME FROM information_schema.`COLUMNS` C WHERE table_schema = 'northwind';


How to change the server collation ?


See there : http://kosalads.blogspot.fr/2013/03/mysql-55-how-to-change-mysql-default.html


If you run :

show variables like '%collation%'


you get :

collation_connection = utf8mb4_general_ci

collation_database = latin1_swedish_ci

collation_server = latin1_swedish_ci


If you create a new database, it's collation will be : latin1_swedish_ci


Add this in my.ini at the [mysqld] section :

init_connect='SET collation_connection = utf8_general_ci'

init_connect='SET NAMES utf8'





Shut down MySql and restart it, and do again the test :


If you run :

show variables like '%collation%'


you get :

collation_connection = utf8mb4_general_ci

collation_database = utf8_general_ci

collation_server = utf8_general_ci


If you create a new database, it's collation will be : utf8_general_ci


Be careful because if you miss some parameters for MySql (for exemple init_connect), you will crash MySql, PhpMyAdmin, and your databases, make backup for all of them before changing this config !





- ListBox or ComboBox to recall a list of databases recently used, instead of only the last one.





Version 1.05 - 05/03/2017

- Main MySql parameters added in the report : sql_mode, innodb_strict_mode, collation, table engine, timeout ;

- ForeignKeyDeleteRule, ForeignKeyUpdateRule : default value are now configurable.


Version 1.04 - 23/10/2016

- DBReport version added in the report ;

- Default value for String : the empty string is distinguished from the null string.


Version 1.03 - 18/09/2016

- Report generation time added to the end of the report ;

- Duplicate constraints bug fixed (the same constraint can be added several times, and it can be hard to detect using an old version of phpMyAdmin) ;

- DbReader.ReaderProgress handled ;

DbReader version -> (faster !) ;

- DotNet 4.0 -> DotNet 4.5.


Version 1.02 - 24/01/2016

- DB report change : 'an unique index' -> 'a unique index' ;

- MouseOver control messages : disabled ;

- Reset settings button ;

- Cancel button ;

- Check boxes for options ;

- Option to display or hide the description of tables and fields.


Version 1.01 - 03/01/2016 : First version





- The classical Northwind database




- Library used : https://dbschemareader.codeplex.com


WinDiff : version 5.2.3790.0 - 24/03/2003 shipped with Windows 2003 (Microsoft Source Code Samples)


WinMerge : an Open Source compare tool like WinDiff



See also


- (french) DBComp2 : le comparateur de structure de base de données Access (translate)

  Source code : DBComp.vbp.html