#mysql

Test de recherche d’idéogrammes chinois dans #seenthis :
recherche : Qingdao - O.K. ( /recherche ?recherche=Qingdao )
recherche : #Qingdao - O.K. ( /recherche ?recherche=%23Qingdao )
filtrer : #Qingdao .O.K ( /tag/qingdao )

recherche : 青岛 - échec ( /recherche ?recherche=青岛 )
recherche : #青岛 O.K. ( /recherche ?recherche=%23青岛)
filtrer : #青岛 O.K. ( /tag/青岛 )

Les hashtags sont des mots clé alors on cherche au fait pour titre_mot, pas vrai ? Pourquoi la recherche sur le texte par contre échoue-t-elle ?
Je suppose que c’est un problème de bdd, charset UTF-8 mais pas de jeu de caractères étendus.
J’essaie de pour comprendre ces résultats afin de rendre possible la recherche pour des expressions en chinois dans d’autres sites #SPIP.

Des idées ? Merci !

GitHub - noborus/trdsql : CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN. Can output to various formats.
▻https://github.com/noborus/trdsql

Un outil en CLI (WIndows/Linux/MacOS) qui permet d’exécuter des requêtes SQL avec la syntaxe MySQL sur tout un tas de sources : CSV, JSON, MD,... mais aussi MySQL
– propose différents formats de sortie
– permet d’utiliser plusieurs fichiers en entrée

#trdsql #MySQL #csv #json

The complete mysqldump guide (with examples)
▻https://simplebackups.com/blog/the-complete-mysqldump-guide-with-examples

Détail de l’utilisation de plusieurs options utiles de mysqldump : --quick, --single-transaction

#mysqldump #sauvegardes

13 Tips for Tuning and Optimizing MySQL and MariaDB - VITUX
▻https://vitux.com/tune-and-optimize-mysql-mariadb

doc minimum pour les paramètres de configuration de MySQL/MariaDB

#mariaDB #mySQL #configuration #Debian

rename - How to add #prefix of all tables in #mysql - Stack Overflow
▻https://stackoverflow.com/questions/7970798/how-to-add-prefix-of-all-tables-in-mysql

Run all queries that you get from running this query:

SELECT Concat('ALTER TABLE `', TABLE_NAME, '` RENAME TO `dr_', TABLE_NAME, '`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '<name of your db>';

How to fix the mysqldump process privilege error - Another Cup of Coffee
▻https://anothercoffee.net/how-to-fix-the-mysqldump-access-denied-process-privilege-error

2 méthodes pour contourner le problème de droits avec la commande mysqldump sur les BDD en MySQL 8 lorsqu’on a un utilisateur aux droits limités au strict minimum :

la note de version de MySQL sur cette modification de compatibilité : ▻https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html#mysqld-8-0-21-security

#PROCESS #tablespaces #MySQL

Utilisation de EXPLAIN de MySQL pour optimiser des requêtes SQL
▻https://www.mon-code.net/article/157/utilisation-de-la-fonction-explain-de-mysql-pour-optimiser-des-requetes-sq

un exemple d’utilisation de EXPLAIN et d’optimisation en jouant sur les index des tables

#EXPLAIN #MySQL #optimisation

{par_ordre_liste...} - SPIP
▻https://www.spip.net/fr_article6646.html#par_ordre_liste

le critère de tri SPIP par_ordre_liste a un gros défaut : si on l’applique sur un ensemble d’éléments qui ne sont pas tous dans la #LISTE passée en 2ème paramètre, il affiche d’abord les éléments hors de la #LISTE puis ceux qui sont dedans.
Exemple si on a 16 sites syndiqués dans la BDD :

Pour obtenir le résultat « naturellement attendu » (i.e. d’abord ceux de la liste puis les autres) il faut utiliser la requête SQL suivante :

#ORDER_BY_FIELD #ORDER #SPIP #par_ordre_liste #MySQL

dolthub/dolt: Dolt – It’s Git for Data
▻https://github.com/dolthub/dolt

Dolt is a #SQL database that you can #fork, clone, branch, merge, push and pull just like a git repository. Connect to Dolt just like any MySQL database to run queries or update the data using SQL commands. Use the command line interface to import CSV files, commit your changes, push them to a remote, or merge your teammate’s changes.

All the commands you know for #Git work exactly the same for Dolt. Git versions files, Dolt versions tables. It’s like Git and MySQL had a baby.

maxbube/mydumper: Official #mydumper project
▻https://github.com/maxbube/mydumper

What is mydumper? Why?

– Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)
– Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)
– Consistency - maintains snapshot across all threads, provides accurate master and slave log positions, etc
– Manageability - supports PCRE for specifying database and tables inclusions and exclusions

When it comes to backing up and restoring MySQL database, most people usually use the very popular mysqldump. Whilst, mysqldump can be very easy to use for a smaller database, it doesn’t work well with larger databases. It’s very slow for huge databases and very error prone when used for very big MySQL databases.

▻https://dotlayer.com/extremely-fast-mysql-backup-restore-using-mydumpermyloader

Semble être utilisé par wikmedia cf :

Its speed and parallelism makes it perfect to save time and load data a lot faster.
It is easier to recover single tables or rows.
It compress nicely

▻https://wikitech.wikimedia.org/wiki/MariaDB#Dumping_tables_with_mydumper

#mysqldump_alternative

sort MySQL-ORDER BY valeurs dans IN() - Résolu
▻https://code.i-harness.com/fr/q/ea0a3#answer3

utiliser une liste pour ordonner les résultats d’une requete (typiquement faire un
id_truc IN (1, 3, 5, 8)
et avoir les résultats triés selon le même ordre
ce qui se fait avec :
SELECT * FROM spip_truc WHERE id_truc IN (1, 3, 5, 8) ORDER BY FIELD (id_truc, 1, 3, 5, 8)
Ce qui donne en requête SPIP :
<BOUCLE_t(TRUCS){id_truc IN  1, 3, 5, 8}{par FIELD (id_truc, 1, 3, 5, 8)}>
ça fonctionne très bien comme ça...
...sauf qu’avec la valeur de la liste en dynamique (#SET/ #GET) ça ne veut rien savoir (même avec tous les bricolages possibles...) Dommage ! :-(

#mysql #FIELD #trier #SPIP

John’s Observation Loft : Adding or removing individual SQL modes in MySQL’s sql_mode variable
▻http://johnemb.blogspot.com/2014/09/adding-or-removing-individual-sql-modes.html

La syntaxe des requêtes MySQL pour ajouter/modifier/supprimer à la volée des valeurs aux sql_mode (défini dans la configuration du serveur)

#sql_mode #MySQL #configuration

#MariaDB #Memory Allocation - MariaDB Knowledge Base
▻https://mariadb.com/kb/en/library/mariadb-memory-allocation

Allocating RAM for MySQL - The short answer

If using just MyISAM, set key_buffer_size to 20% of available RAM. (Plus innodb_buffer_pool_size=0)

If using just InnoDB, set innodb_buffer_pool_size to 70% of available RAM. (Plus key_buffer_size = 10M, small, but not zero.)

Rule of thumb for tuning mysql:

– Start with released copy of my.cnf / my.ini.
– Change key_buffer_size and innodb_buffer_pool_size according to engine usage and RAM.
– Slow queries can usually be ’fixed’ via indexes, schema changes, or SELECT changes, not by tuning.
– Don’t get carried away with the query cache until you understand what it can and cannot do.
– Don’t change anything else unless you run into trouble (eg, max connections).
– Be sure the changes are under the [mysqld] section, not some other section.

The 20%/70% assumes you have at least 4GB of RAM. If you have a tiny antique, or a tiny VM, then those percentages are too high.

Now for the gory details. (NDB Cluster is not discussed here.)

#mysql

How to change a MySQL database’s table prefix | Rich Jenks
▻https://richjenks.com/change-mysql-database-table-prefix

The output will be a series of SQL queries that will rename the tables for you

#mysql #prefixe

Using #docker To Run #mysql Server In Your Development Environment
▻https://hackernoon.com/using-docker-to-run-mysql-server-in-your-development-environment-e32d523

https://cdn-images-1.medium.com/max/900/0*3nDnCayBCd9pA-8r.jpg

Today I’m going to show you how can you can use Docker to run MySQL Server in your development environment. Using Docker to run MySQL is much easier than manually installing it and for when you have multiple instances of MySQL running at the same time. I’m assuming that you already have Docker Community Edtion install on your machine. If you do not have Docker installed follow the instruction here for your operating system.Install MySQL Using DockerIf you search online for how to install MySQL using Docker, you will see many different examples on how to do this. I’m going to show how to do it so that you can connect to the database from your IDE and from the applications you are developing.Type this command at the terminal:$ docker run -p 3306:3306 —name hb-mysql-example -e (...)

#devops #java #programming

Impact on Insert Performance — Primary Keys in #mysql
▻https://hackernoon.com/impact-on-insert-performance-primary-keys-in-mysql-590ecf8ba67?source=rs

https://cdn-images-1.medium.com/max/1024/1*WTsG9lqGrPzqPbhWxlf-9Q.png

Impact on Insert Performance — Primary Keys in MySQLThe importance of Primary Keys in MySQLNote: I have used MySQL and it’s storage engine #innodb interchangeably.A couple of days ago, someone at work asked me whether inserts are faster in a table when there’s a primary key present in the table. My immediate answer was that they were faster. I had some idea about this from experience and previous reading but didn’t remember the reason why it was so.For a business user unaware about InnoDB internals or InnoDB’s existence, it might seem perfectly intuitive to assume that inserts will be faster without a primary key because a primary key is an additional constraint on the table, i.e., MySQL has to check for uniqueness, nullability. When it comes to InnoDB, that is not the case. Also, a table (...)

#database #performance-testing #database-administration

Adding #elasticsearch To Legacy Application Using #logstash
▻https://hackernoon.com/adding-elasticsearch-to-legacy-application-using-logstash-98c19ac2f0fb?s

https://cdn-images-1.medium.com/max/1024/1*nNR_e2gAr8kwLKW2ZoZOhA.png

ElasticSearch has created a boom in the market with its ability to store, scaling, performing full-text search and analytics on the humongous amount of data in near real time. ElasticSearch is open source search and analytics engine generally used in applications which have complex search features.In this tutorial, we are going to consider a scenario where we wish to add ElasticSearch to our legacy application as a Secondary Data Source. Say suppose we have a legacy application which uses #mysql database and now we have a requirement where we need to have a heavy search and we have decided to move to ElasticSearch. My idea here would still stick to Relational Databases as a Primary Data Store and use ElasticSearch as a Secondary Data Store for the requirements where we need to have the (...)

#add-elasticsearch #advanced-search

#sharding writes with #mysql and increment offsets
▻https://hackernoon.com/sharding-writes-with-mysql-and-increment-offsets-48df32379563?source=rss

https://cdn-images-1.medium.com/max/600/0*UvsNvfPreQlGvO9Z.png

Sharding Writes with MySQL and Increment OffsetsTL;DRwe shard writes and avoid conflicts using an increment offset on PKseach #data center contains local write serverswe use multi-source replication on read servers so they contain all the data from all data centers, partitioned by monthAt the beginning, things were simpleWe were using MySQL: one master for writing, and a few slaves for reading. Besides replication lag (more on that later), it worked fine. And then you scale. And then it fails! The master is a SPOF — “Single Point Of Failure”.Whether the process failed, the VM crashed, or we had to do maintenance on the server, having only one “master” to write to meant that the service was down for minutes. And this was not acceptable.The first thing we implemented was sharding based on the (...)

#data-replication #database

#activerecord on #mysql— Iterating over large tables with conditions
▻https://hackernoon.com/activerecord-on-mysql-iterating-over-large-tables-with-conditions-453bd8

https://cdn-images-1.medium.com/max/738/1*ZVxd0g85DK_AiHSym6CtNA.png

In this article, I’m going to demonstrate performance differences between two ways of iterating over the records in a MySQL database table with millions of records. In a high volume analytics system, tables with millions of records are quite common and iterating over the full table or a subset of these tables becomes often necessary — whether it’s to perform computations, run a migration, or create parallelized background jobs on the records. At AirPR, we have many database tables with 100s of millions of records, and it becomes important to write efficient code for iterations because there is often an order of magnitude difference between a good and not-so-good approach.Find Each MethodThe standard approach provided natively by ActiveRecord is the find_eachmethod.For the purposes of this (...)

#rails #programming #activerecord-on-mysql

MySQL — Wikilivres
▻https://fr.wikibooks.org/wiki/MySQL

Documentation de MySQL en français (puisque Oracle a supprimée le français de la doc officielle...)

#mysql #documentation

Elephant in the Room : #database #backup
▻https://hackernoon.com/elephant-in-the-room-database-backup-574da50e6d88?source=rss----3a8144ea

https://cdn-images-1.medium.com/max/1024/1*Tm3culOnUMproMU2I6j58w.jpeg

Photo by Alexandre ChambonWe’ve seen it many times. Yet it sill occurs on a regular basis, as if there’s no permanent cure for this chronic problem.About 9 years ago, Sidekick, a popular T-Mobile smartphone at the time, lost all of user data in the cloud, leaving 800,000 users without access to their own personal data such as email, notes, contacts, calendars and photos.Later on, Microsoft decided to discontinue the cloud service and eventually, sales of the device.As a result, a massive business and its ecosystem ceased to exist. It was the biggest disaster in cloud computing history.GitLab also suffered from broken recovery procedures when an engineer had accidentally deleted a directory on the wrong server, by running rm -rf /important-data on their primary database.Side note: Kudos to (...)

#mysql #cloud-computing #database-backup

Showdown : #mysql 8 vs PostgreSQL 10
▻https://hackernoon.com/showdown-mysql-8-vs-postgresql-10-3fe23be5c19e?source=rss----3a8144eabfe

https://cdn-images-1.medium.com/max/1024/1*eojT2CVjYsQNtaLFxvKUuw.jpeg

Photo by David MarcuNow that MySQL 8 and PostgreSQL 10 are out, it’s a good time to revisit how the two major open source relational databases compete against each other.Before these versions, the general perception has been that while #postgres is superior in feature sets and its pedigree, MySQL is more battle tested at scale with massive concurrent reads /writes.But with the latest releases, the gap between the two has gotten significantly narrowed.Feature ComparisonLet’s take a look at the “trendy” features that we all love to talk about.It used to be easy to say that MySQL works best for online transactions, and PostgreSQL works best for analytical processes. But not anymore.Common Table Expressions (CTEs) and window functions have been the main reason to choose PostgreSQL. But now, (...)

#database #programming

bash - How to output MySQL query results in CSV format ? - Stack Overflow
▻https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format#356605

Plein de méthodes pour sortir le résultat d’une requête MySQL dans un fichier CSV (même si le serveur est configuré en mode paranoïaque (—secure-file-priv option) qui l’empêche de sortir le résultat dans un fichier.

On peut aussi utiliser (élégamment !) pager : ▻https://www.zen-tech.info/2010/02/enregistrer-resultat-dun-requete-mysql-dans-un-fichier

#csv #MySQL #pager #batch #sed

MariaDB : déplacer le répertoire /var/lib/mysql | memo-linux.com
▻https://memo-linux.com/mariadb-deplacer-le-repertoire-var-lib-mysql

Un mémo sur comment déplacer le répertoire /var/lib/mysql vers la partition /home

#mariadb #mysql #linux #serveur

Convertir une base de données SQLite vers MySQL/MariaDB - PAB’s blog
▻https://blog.bandinelli.net/index.php?post/2014/03/27/sqlite3-to-mysql

LE script miraculeux pour la conversion sqlite > sql
NB : ne pas oublier de faire un rechercher/remplacer pour virer les chaînes COLLATE NOCASE et COLLATE BINARY avant de lancer l’import du fichier SQL obtenu.
...et ne pas oublier de remettre le AUTO_INCREMENT sur tous les champs id_<objet> qui sont les clés primaire des tables spip_<objet>

#conversion #sqlite #mysql #mariadb #python #utilitaire #SPIP