Archív kategorií: MySQL

MySQL Workbench – passwords not being saved in keychain

MySQLWorkbenchIf you have problem, that your MySQL Workbench can’t save passwords for your MySQL connections, try to start mysql-workbench in verbose mode. Maybe you will see a error:

$ mysql-workbench --verbose
** Message: Gnome keyring daemon seems to not be available. Stored passwords will be lost once quit
['un\\"o', 'do``s']
Ready.

Thread started

Pokračovať v čítaní →

MySQL consistency check – permissions and non-accessible databases

mysql-grant-syntax-2The following query lists all grants for nonexistent databases:

Grants for nonexistent databases

/* grants in mysql.db for nonexistent databases */
SELECT DISTINCT db
FROM mysql.db AS d
LEFT JOIN information_schema.SCHEMATA AS s ON (d.db = s.SCHEMA_NAME)
WHERE s.SCHEMA_NAME IS NULL;

/* grants cleanup */
DELETE mysql.db
FROM mysql.db 
LEFT JOIN information_schema.SCHEMATA ON (mysql.db.db =  information_schema.SCHEMATA.SCHEMA_NAME)
WHERE information_schema.SCHEMATA.SCHEMA_NAME IS NULL;

Databases withouth grants

/* databases withouth grants */ \
SELECT DISTINCT s.SCHEMA_NAME
FROM information_schema.SCHEMATA AS s 
LEFT JOIN mysql.db AS d ON (d.db = s.SCHEMA_NAME)
WHERE d.db IS NULL;

Queries are not complete, you are welcome to add other queries.

Create triggers with MySQL procedures

Angry developer

Angry developer

I wrote some SQL procedures, which can create triggers. The main goal was to create triggers for `mysql` schema, but it is not possible for the following limitation of current version MySQL and MariaDB:

1465 HY000 ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA Triggers can not be created on system tables

Pokračovať v čítaní →

Howto migrate s9y blog to WordPress

Migrate s9y posts
– use import from RSS, rss = http://blog-s9y.hostsname.sk/feed/rss.php?version=2.0&all=1

And then update to whole body:

UPDATE wp_posts w, sp_entries s 
  SET w.post_content = CONCAT(s.body , '<!--more-->', s.extended) 
    COLLATE utf8_general_ci
WHERE w.post_title = s.title COLLATE utf8_general_ci ;

Pokračovať v čítaní →

Rýchle nakonfigurovanie MySQL master-master replikácie

Master-master replikácia MySQL databázy je super vec. Ak však objem dát presiahne istú hranicu, býva vytvorenie repliky klasickou cestou dump –> import zdĺhavé. Avšak pri zachovaní istého postupu je možné vytvorenie repliky značne urýchliť. Poďme sa pozrieť na to, ako to spraviť. Pokračovať v čítaní →

Upgrade MySQL v Debiane rozbije master-master replikáciu

Pri poslednom upgrade domácej farmy (laptop, router, storage, …) sa mi stala nemilá vec. Prestala mi fungovať MySQL MASTER-MASTER replikácia medzi laptopom a domácim serveríkom, ktorú použǐvam najmä kvốli zálohovaniu dát.
Stav vyzeral nasledovne, rovnaká chyba na oboch stranách:


(root@localhost) [(none)]> SHOW SLAVE STATUS \G
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
...
Last_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log
MODIFY start_time TIMESTAMP NOT NULL,
MODIFY user_host MEDIUMTEXT NOT NULL,
MODIFY query_time TIME NOT NULL,
MODIFY lock_time TIME NOT NULL,
MODIFY rows_sent INTEGER NOT NULL,
MODIFY rows_examined INTEGER NOT NULL,
MODIFY db VARCHAR(512) NOT NULL,
MODIFY last_insert_id INTEGER NOT NULL,
MODIFY insert_id INTEGER NOT NULL,
MODIFY server_id INTEGER UNSIGNED NOT NULL,
MODIFY sql_text MEDIUMTEXT NOT NULL'
...

Pokračovať v čítaní →