Strato und MySQL: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

A customer following error message was shown to me, When I tried the database have symfony to create:

General error: 1709 Index column size too large. The maximum column size is 767 bytes.

This is because, the Strato strange setting in their managed hosting packages such as z.B. has the STRATO PowerWeb. Strato unfortunately won't change this setting, but you can in symfony in the doctrine.yaml (config.YAML) change the charset, Symfony works well on a Strato Server:

doctrine:
    dbal:
        # configure these for your database server
        driver: 'pdo_mysql'
        server_version: '5.6'
        charset: utf8
        default_table_options:
            charset: utf8
            collate: utf8_general_ci

Project: baby taschenrechner.de

The just completed project baby taschenrechner.de deals with the issues relating to the development of the own child:

  • How great my child be in x years
  • My child is like in x years
  • Is my child too hard or too thin
  • What dress size is wear it when?

The Web page is to help parents find out, When you need to buy what dress size, the approaching winter/summer home to have the appropriate.

Parents can thus einschätzne, If the child is too thin or too thick for your age/size/weight ratio.

The following technologies were used for the realization:

Symfony 3, Docker, MySQL, PHP, GIT, Google material design, Amazon AWS

MySQL Monitoriong of the ProcessList / Queries with a console command

A hilreicher command for problem analysis of MySQL servers is to look at the ProcessList, to see, What Quueries are currently running. If you combine this with the watch command, and these 1 reload times per second can be and reduced to the queries, you did a good review, What is going on on the MySQL server and what queries are problematic could:

watch -n 1 "mysql -u root --password='XXX' -h localhost -s -e 'SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE Command=\"Query\";'"

It is important, the command as root to run, to see all queries.

With the help of the parameter n, you can specify the refresh rate in seconds.

The MySQL root password is to complement.

 

Edition:

5361925476      user     localhost       NULL    Query   0       executing       SELECT * FROM xxx WHERE yyy

5361925477      user     localhost       NULL    Query   0       executing       UPDATE zzz WHERE zzz

History for MySQL create table

If you want to log, who when and what changes to a table “table 1″ has made, should you be called a table “tabelle1_history” and automatically by MySQL Trigger respond to update and delete events:

Note: __XYZ__ are placeholders to replace

Trigger on update actions

CREATE DEFINER=`__USER__`@`%` TRIGGER `__DB__`.`__TRIGGER_NAME_DEL__` BEFORE DELETE ON `__DB__`.`__TABLE__` FOR EACH ROW BEGIN
    insert into
        __DB__.__TABLE_HISTORY__
        (
        dataset_id,
        my_data1
        my_data2
        created_on
        created_by
        )

    VALUES (
        OLD.id,
        OLD.my_data1
        OLD.my_data2
        OLD.created_on,
        OLD.created_by
  );

Trigger on delete actions

CREATE DEFINER=`__USER__`@`%` TRIGGER `__DB__`.`__TRIGGER_NAME_UPDATE__` BEFORE UPDATE ON `__DB__`.`__TABLE__` FOR EACH ROW BEGIN
        insert into
        __DB__.__TABLE_HISTORY__
        (
        dataset_id,
        my_data1
        my_data2
        created_on
        created_by
        )

    VALUES (
        OLD.id,
        OLD.my_data1
        OLD.my_data2
        OLD.created_on,
        OLD.created_by
  );

MySQL update deadlock InnoDB

A difficult question has recently occurred when a partitioned MySQL database. There were messages from MySQL always at high load deadlock. Were always 2 Queries affected, they had the same form:

--Query1:
UPDATE myTable SET createdOn='2016-05-23 16:08:29', foo='y' WHERE id=1
--Query2:
UPDATE myTable SET createdOn='2016-05-23 16:08:29', foo='x' WHERE id=2

In the MySQL error log was such an entry to find:

mysql tables in use 1, locked 1
LOCK WAIT 106 lock struct(s), heap size 8400, 53 row lock(s)

Tip: System variable innodb_print_all_deadlocks (as of MySQL 5.5) is it in the MySQL error log deadlocks in InnoDB log and better debug.

Actually it shouldn't come at the two Queires to a deadlock, Since both only a row-level lock on their respective line should have.

This is also the case, If it an index on the column id are. In this case however was through the Partitioning of primary (Unique) Index on 2 Set columns been (combined index): id and createdOn.

As a result, that the index has been put on two Soalten, It came to the the Dealocks. The Solution was, the createdOn to remove from the update statement, because it did make sense on the map.

Import and export all MySQL databases on Windows and Mac

Under Windows, you can very easily all data with one command export in MySQL using mysqldump:

cd C:\xampp\mysql\bin
mysqldump.exe -u root -p --opt --all-databases --verbose > C:\export.sql

 

Explanation:

 –opt:Export all DBs, without “System” databases like mysql, INFORMATION_SCHEMA, performance_schema and test

 –verbose: to log messages to show on the screen, There are not many, so that does not affect the performance is great

Import/export large MySQL databases under XAMPP

When importing large amounts of data under XAMPP ramming through phpMyAdmin quickly limits the script runtime or performance limits:

Fatal error: Maximum execution time exceeded

This can be adapted while, but such an import can take hours. The import of the console is by a factor of 10 faster. So do you need just a few minutes instead of an hour and has no problems with the script runtime or similar. The quickest and easiest is via the MySQL console import:

  • just in the XAMPP Control Panel on Shell Click and then type the following commands in the console:
mysql -u root -p

Enter the password or einfachmit ENTER confirm, If no password has been set. Root is the user of the database, may differ. Then select the database, that is to be imported, GGF. before applying, If these non-existent. more…