SQL
First the excel file was converted to a CSV file using LibreOffice Calc.
Then using the web generator https://codebeautify.org/csv-to-sql-converter and entering some database specific details (tablename, columnname identifier, columnname targetvalue) and choosing "CSV to SQL(INSERT)" an SQL script was generated that inserts into each row with the identifier the corresponding value from the CSV file.
The output was saved as an .sql file.
A a database backup created.
Afterwards the sql-file was uploaded into phpMyAdmin as an import script and executed perfectly.
Taggings:
Joins are more expensive than a pure concatenation. In your case maybe UNION or UNION ALL might be a good solution because it parses the first SELECT statement and moves on to the next SELECT statement and adds the results to the end of an output table. The finding of the different connections of the joined tables is a huge cost factor.
Performance monitoring of a cloud deployment
An application go-live took a wrong turn - a dozen of connected clients took a cloud system down, that should cope with thousands of them. As I was assigned to analyze the problems I discovered that this is a composite problem created by (amongst other things) careless usage of Hibernate (an ORM framework for easier, vendor-agnostic database access) and way to much complex logic built 'into' the database - killing both the database and the web applications host by using up its CPU.
After using Hibernate statistics for analyzing the transactions that are being sent to the database, I started by rewriting some SQL queries that didn’t perform well. Some complex Java/Hibernate logic even had a kind of memory leak in it which could be resolved by writing a single database stored procedure. A connectivity checking method for the clients was moved from saving ping data in the database to an efficient in memory solution using an in-memory data grid called Hazelcast.
Nevertheless the CPU load stayed above an acceptable threshold. Using an application performance management tool I could find another root cause in a flawed function that was being called periodically. Looking at the CPU load graph one could see that the CPU went about each three minutes from under 10 to almost 100 percent. The load was created by one single thread with about 80% of network IO and 20% code execution, where multiple database calls were made, responses sorted and analized. Not only was the sorting flawed, also the database calls were being performed for all rows in one table and not just for a small subset of the entries. After a rewrite the CPU usage dropped to a single-digit percentage which kept the system in a healthy state.
First, the Site Address and the WordPress address need to be updated. Go to Settings->General in the WordPress administration area and change the value of these two settings to the new URL. New contents are now created with HTTPS.
Unfortunately, changing the site address does not update URLs in existing contents, so a SQL-Statement to replace the old URLs with new one is required (replace example.org with the URL of the site to move):
UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://www.example.org', 'https:// www.example.org)
Taggings:
Moving a WordPress site to HTTPS
An existing WordPress site shall be moved from HTTP to HTTPS. All resources should be loaded via HTTPS after the update. Therefore, all text occurrences, hyperlinks and image resources in pages and posts must be updated with the new URL.
INSERT INTO field_data_field_taggings (entity_type, bundle, deleted, revision_id, language, delta, entity_id, field_taggings_tid)
SELECT "node" as entity_type, n.type AS bundle, 0 AS deleted, n.vid AS revision_id, "und" as language,
@i := IF(n.nid > @prev_nid, 0, @i + 1) AS delta, @prev_nid := n.nid AS entity_id,
tid as field_taggings_tid FROM taxonomy_index t, node n
JOIN (SELECT @prev_nid := NULL, @i := -1) v
WHERE n.nid=t.nid
Mapping taxonomy terms from Drupal 5 to Drupal 7
In Drupal 7 tagging terms are stored as fields of the entity. In previous Drupal versions tags are assigned to nodes stored in a table taxonomy_index(nid, tid, created). In D7 we have some table as for example field_data_field_taggings were each term refrerence is stored as a row. If more then one term is assigned to a content node (e.g. in the answers module), the value in the column delta incremented to have a unique key. To generate this table we need an sql statement that increments this delta if more than one term exist for the node.
