How to connect to MariaDB secure server from phpMyAdmin and WordPress.

   After “MariaDB with Secure Connections.”, my SQL server has Secure Connections. Now I have to add some options to phpMyAdmin and WordPress settings.
   Their versions are MariaDB 10.2.9 win 32-bit, phpMyAdmin 4.7.4 and WordPress 4.8.2 on Windows 7 32-bit HE SP1.
Continue reading “How to connect to MariaDB secure server from phpMyAdmin and WordPress.”

MariaDB with Secure Connections.

   These days, I was working hard about using MariaDB with Secure Connections. First, I did SHOW VARIABLES LIKE 'have_ssl'; and got this:

+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+

   DISABLED means that the server was compiled with TLS support, but was not started with TLS support. So I can use MariaDB with Secure Connections on my server. Continue reading “MariaDB with Secure Connections.”

I’ve updated to phpMyAdmin4.6.6.

   I’ve updated to phpMyAdmin4.6.6. After that, the new version gave me “OpenSSL error: error:0607A082:digital envelope routines:EVP_CIPHER_CTX_set_key_length:invalid key length” at HOME when I logged in.
   This is maybe because of this 👉 $cfg[‘Servers’][$i][‘ssl_verify’].

   The page says “Disabling the certificate verification defeats purpose of using SSL. This will make the connection vulnerable to man in the middle attacks.”, but my SQL server and phpMyAdmin don’t accept accesses from outside of NAT router and the user is only me. So, as my temporal workaround, I added the next line to my config.inc.php.

$cfg['Servers'][$i]['ssl_verify'] = false;

Memorandum #18.

   Recently I updated some server apps.

    Update information      Edit(Jul.21)
  1. From ActivePerl-5.22.1.2201 to ActivePerl-5.24.0.2400.
    Last time I installed ActivePerl-5.22.1.2201-MSWin32-x86-64int-299574.msi. But now they doesn’t provide an msi file. Then I tried to install ActivePerl-5.24.0.2400-MSWin32-x86-64int-300558.exe, but it gave me an error like the below.

    Error 1723. There is a problem with this Windows Installer package. A DLL required for this install to complete could not be run. Contact your support personnel or package vendor.  Action CheckInstallDir, entry: CheckInstallDirNoBox, library: C:\Users\UserID\AppData\Local\Temp\MSIXXXX.tmp

    Continue reading “Memorandum #18.”

About utf8mb4 on WordPress.

   Yesterday, WordPress 4.2 came. I updated to it from the WordPress Updates page. After it, I need Upgrade Network because my WordPress is a multisite type. But I use a self-signed certificate, so I must to add my CA cert data to wp-includes/certificates/ca-bundle.crt before Upgrade Network. I also need to add the following lines to wp-includes/class-http.php because I use client authentication.

  • curl_setopt( $handle, CURLOPT_SSLCERT, 'the exact path of clientcert.pem' );
    curl_setopt( $handle, CURLOPT_SSLKEY, 'the exact path of clientkey.pem' );

   See The solution of “SSL3_READ_BYTES:sslv3 alert handshake failure” on WordPress.

   By the way, according to WordPress 4.2, WordPress supports utf8mb4 now. So you can use following 4-byte kanjis on your WordPress articles though I couldn’t use them when I checked it on 2013‎.5‎.22‎. emoji are also available in WordPress! Wow! These kanjis are included by level 3 and 4 in JIS X 0213.
𠀋 𡈽 𡌛 𡑮 𡢽 𠮟 𡚴 𡸴 𣇄 𣗄 𣜿 𣝣 𣳾 𤟱 𥒎 𥔎 𥝱 𥧄 𥶡 𦫿 𦹀 𧃴 𧚄 𨉷 𨏍 𪆐 𠂉 𠂢 𠂤 𠆢 𠈓 𠌫 𠎁 𠍱 𠏹 𠑊 𠔉 𠗖 𠘨 𠝏 𠠇 𠠺 𠢹 𠥼 𠦝 𠫓 𠬝 𠵅 𠷡 𠺕 𠹭 𠹤 𠽟 𡈁 𡉕 𡉻 𡉴 𡋤 𡋗 𡋽 𡌶 𡍄 𡏄 𡑭 𡗗 𦰩 𡙇 𡜆 𡝂 𡧃 𡱖 𡴭 𡵅 𡵸 𡵢 𡶡 𡶜 𡶒 𡶷 𡷠 𡸳 𡼞 𡽶 𡿺 𢅻 𢌞 𢎭 𢛳 𢡛 𢢫 𢦏 𢪸 𢭏 𢭐 𢭆 𢰝 𢮦 𢰤 𢷡 𣇃 𣇵 𣆶 𣍲 𣏓 𣏒 𣏐 𣏤 𣏕 𣏚 𣏟 𣑊 𣑑 𣑋 𣑥 𣓤 𣕚 𣖔 𣘹 𣙇 𣘸 𣘺 𣜜 𣜌 𣝤 𣟿 𣟧 𣠤 𣠽 𣪘 𣱿 𣴀 𣵀 𣷺 𣷹 𣷓 𣽾 𤂖 𤄃 𤇆 𤇾 𤎼 𤘩 𤚥 𤢖 𤩍 𤭖 𤭯 𤰖 𤴔 𤸎 𤸷 𤹪 𤺋 𥁊 𥁕 𥄢 𥆩 𥇥 𥇍 𥈞 𥉌 𥐮 𥓙 𥖧 𥞩 𥞴 𥧔 𥫤 𥫣 𥫱 𥮲 𥱋 𥱤 𥸮 𥹖 𥹥 𥹢 𥻘 𥻂 𥻨 𥼣 𥽜 𥿠 𥿔 𦀌 𥿻 𦀗 𦁠 𦃭 𦉰 𦊆 𦍌 𣴎 𦐂 𦙾 𦚰 𦜝 𦣝 𦣪 𦥑 𦥯 𦧝 𦨞 𦩘 𦪌 𦪷 𦱳 𦳝 𦹥 𦾔 𦿸 𦿶 𦿷 𧄍 𧄹 𧏛 𧏚 𧏾 𧐐 𧑉 𧘕 𧘔 𧘱 𧚓 𧜎 𧜣 𧝒 𧦅 𧪄 𧮳 𧮾 𧯇 𧲸 𧶠 𧸐 𧾷 𨂊 𨂻 𨊂 𨋳 𨐌 𨑕 𨕫 𨗈 𨗉 𨛗 𨛺 𨥉 𨥆 𨥫 𨦇 𨦈 𨦺 𨦻 𨨞 𨨩 𨩱 𨩃 𨪙 𨫍 𨫤 𨫝 𨯁 𨯯 𨴐 𨵱 𨷻 𨸟 𨸶 𨺉 𨻫 𨼲 𨿸 𩊠 𩊱 𩒐 𩗏 𩙿 𩛰 𩜙 𩝐 𩣆 𩩲 𩷛 𩸽 𩸕 𩺊 𩹉 𩻄 𩻩 𩻛 𩿎 𪀯 𪀚 𪃹 𪂂 𢈘 𪎌 𪐷 𪗱 𪘂 𪘚 𪚲

   So I can write 「私,𩸽の開きを焼いたのが大好きなのよ」 on WordPress now, ha-ha. I almost forgot to write. Of course, your SQL Server needs utf8mb4 support.

WordPress not auto saving all articles on my main site.

Update information      Edit(Sep.6)

   Recently the autosave feature wasn’t working well on o6asan.com though I cannot recall from when. o6asan’s soliloquy and o6asan’s soliloquy-part2 have no problem.

   Apart from this, I found a lot of “WordPress database error Duplicate entry ‘0’ for key ‘PRIMARY’ for query INSERT INTO `WordPress DB table name` ~” on the Apache error log when I checked the errors about php_opcache.dll on August 29.

   Yesterday, I suddenly remembered the errors on the Apache log, and began to get the solution. I saw a lot of sentences related to Notes when I looked into the log again. At the time, I first recognized this errors and autosave feature had a strong relationship. Besides, the errors began on August 23. I must have done something wrong at updating MariaDB. (-_-;)

   I saw what table names the log included, then found them out, i.e. `wp_postmeta`, `wp_posts`, `wp_redirection_logs`, `wp_sitemeta`. I logged in phpMyAdmin and compared wp_postmeta structure with wp_2_postmeta one. Because wp_2_postmeta has no problem. Finally I noticed wp_postmeta had no AUTO_INCREMENT in meta_id’s extra field. I also looked the rests had the same problem.

   First I backed all data up then tried and fixed them.

  1. Select wp_postmeta table.
  2. Select ‘Structure’ from Menu.
  3. Select ‘Change’ from Action of meta_id.
  4. Check ‘A_I’ box on and save.

   If you use CUI, I think you can use the following.
ALTER TABLE `your WP DB name`.`wp_postmeta` CHANGE `meta_id` `meta_id`
BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

   I did this for `wp_postmeta` and `wp_posts` without difficulty. But for `wp_redirection_logs` and `wp_sitemeta`, I had the following error.
#1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry ‘1’ for key ‘PRIMARY’

   `wp_redirection_logs` table has just logs of the plugin Redirection. So I emptied the table and did the above steps again. If you use CUI, I think you can use the following.
TRUNCATE `your WP DB name`.`wp_redirection_logs`;

   But I need the contents of the table `wp_sitemeta`. So, I first emptied the table and did the above steps again. Then I clipped `wp_sitemeta` INSERT statement out from the back-up sql file and imported it to the table.

   The errors on the log file have gone and the autosave feature works well now. Mission complete!

   Don’t trust me too much because I handled the errors in my own fashion. m(_”_)m

Edit(Sep.6):
   When I updated to BulletProof Security .50.8, I had a trouble that the Notice “Network/Multisite BPS plugin Network Activation correction:” had not gone away. So, I went talk to the forum. Then I resolved the problem with his help. This trouble is related to the AUTO_INCREMENT missing again. I think it is maybe caused by phpMyAdmin bug that I read several days ago. But who knows about the truth? Sigh.

   Anyway, the Notice has gone. Now I can sleep in peace (^_^;).

Memorandum #6.

   I don’t know why but I’m very tired. I jot down for my memory.

   My server OS is Windows7 HP SP1 (x86).

  1. PHP 5.5.15 (php-5.5.15-Win32-VC11-x86.zip)
    —> PHP 5.5.16 (php-5.5.16-Win32-VC11-x86.zip)
  2. MariaDB 10.0.12 (mariadb-10.0.12-win32.zip)
    —> MariaDB 10.0.13 (mariadb-10.0.13-win32.zip)
  3. phpMyAdmin 4.2.7 (phpMyAdmin-4.2.7-english.zip)
    —> phpMyAdmin 4.2.7.1 (phpMyAdmin-4.2.7.1-english.zip)

   My guess tells me all of them are security releases. So I’ve dealt with them promptly.

phpMyAdmin Configuration storage.

Update information      Edit(Jul.5)    Edit2(Jul.9)

   phpMyAdmin has an infrastructure named Configuration storage since version 3.4.2. Logging in phpMyAdmin at the first time, you have the message “The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here.” because this is deactivated by default. When activated, you can use features like bookmarks, comments, SQL history, relations, PDF schema, and MIME transformations. For me, bookmarks feature is convenient. Well, I’m going to write how to activate it.
   By the way, when we install phpMyAdmin on our servers, we must consider a lot of things for secure. But I don’t write about it here. Please read Official Documentation and take full responsibility for your actions.

   At the very first time for activating this, you need to take the following three steps.

  1. Create a user and its database by create_tables.sql in MySQL.
  2. Make the above user to a control user
  3. Customize your config.inc.php.

   Now, I’ll start.

  1. Open the file create_tables.sql by a text editor and uncomment the next two lines.
    ————
    GRANT SELECT, INSERT, DELETE, UPDATE ON `phpmyadmin`.* TO
    ‘pma’@localhost;
    ————

    Log in your phpMyAdmin as a root and import the create_tables.sql. After that you have a database phpmyadmin and a user pma with no password.

    Note) In my opinion, you’d better change the names of database and user to other unique names because there are a lot of mal-attacks given clues by them. Editing the create_tables.sql before your import can makes this easy and possible.

  2. Input following statements from the phpMyAdmin SQL Query window.
    ————
    GRANT USAGE ON mysql.* TO ‘pma’@’localhost’ IDENTIFIED BY ‘pmapass’;
    GRANT SELECT (
    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
    File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
    Execute_priv, Repl_slave_priv, Repl_client_priv
    ) ON mysql.user TO ‘pma’@’localhost’;
    GRANT SELECT ON mysql.db TO ‘pma’@’localhost’;
    GRANT SELECT ON mysql.host TO ‘pma’@’localhost’;
    GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
    ON mysql.tables_priv TO ‘pma’@’localhost’;
    ————
    Of course, you should change ‘pmapass’ to ‘your proper controluser password’. If you use your pmadb database name and a controluser name instead of phpmyadmin and pma, you also need to edit them.
     
    Log out your phpMyAdmin.
  3. Open your config.inc.php and uncomment the following twenty lines. If you use your pmadb database name and a controluser name instead of phpmyadmin and pma, you also need to edit them. Of course, about pmapass.
    ————
    /*
    * phpMyAdmin configuration storage settings.
    */

    /* User used to manipulate with storage */
    // $cfg[‘Servers’][$i][‘controlhost’] = ”; ⇐It’s need or not depends on your environment.
    // $cfg[‘Servers’][$i][‘controlport’] = ”; ⇐It’s need or not depends on your environment.
    $cfg[‘Servers’][$i][‘controluser’] = ‘pma’;
    $cfg[‘Servers’][$i][‘controlpass’] = ‘pmapass’;

    /* Storage database and tables */
    $cfg[‘Servers’][$i][‘pmadb’] = ‘phpmyadmin’;
    $cfg[‘Servers’][$i][‘bookmarktable’] = ‘pma__bookmark’;
    $cfg[‘Servers’][$i][‘relation’] = ‘pma__relation’;
    $cfg[‘Servers’][$i][‘table_info’] = ‘pma__table_info’;
    $cfg[‘Servers’][$i][‘table_coords’] = ‘pma__table_coords’;
    $cfg[‘Servers’][$i][‘pdf_pages’] = ‘pma__pdf_pages’;
    $cfg[‘Servers’][$i][‘column_info’] = ‘pma__column_info’;
    $cfg[‘Servers’][$i][‘history’] = ‘pma__history’;
    $cfg[‘Servers’][$i][‘table_uiprefs’] = ‘pma__table_uiprefs’;
    $cfg[‘Servers’][$i][‘tracking’] = ‘pma__tracking’;
    $cfg[‘Servers’][$i][‘designer_coords’] = ‘pma__designer_coords’;
    $cfg[‘Servers’][$i][‘userconfig’] = ‘pma__userconfig’;
    $cfg[‘Servers’][$i][‘recent’] = ‘pma__recent’;
    $cfg[‘Servers’][$i][‘favorite’] = ‘pma__favorite’;
    $cfg[‘Servers’][$i][‘users’] = ‘pma__users’;
    $cfg[‘Servers’][$i][‘usergroups’] = ‘pma__usergroups’;
    $cfg[‘Servers’][$i][‘navigationhiding’] = ‘pma__navigationhiding’;
    $cfg[‘Servers’][$i][‘savedsearches’] = ‘pma__savedsearches’;
    ————

    Log in your phpMyAdmin again.

    You don’t have the message “The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here.” anymore.

   That’s it!

   You can use phpMyAdmin configuration storage features now.

Edit(Jul.5):
   I forgot to write.
   At upgrades, you simply re-import the new create_tables.sql file after backing up your database, importing the file will not overwrite existing data, but will create any new tables. After that, you maybe need to edit your config.inc.php file.
   You already have your control user, so you must not uncomment the lines in the create_tables.sql file. You also keep in mind about your pmadb database name and a controluser name instead of phpmyadmin and pma.

Edit2(Jul.9):
   When I wrote the reply for くりくりさん, I suddenly thought I am scared of 1. and 2. having the time lag. My sql server doesn’t have the open port to the Internet and it has the only one user, me! So, I might have no need for such nerve. But if you have much busier server than mine, such a server has more occasion that is attacked by someone. The time lag gives attackers that they penetrate your server as the new pma with no password. This I am scared!!

   Well, at first make a controluser with password and give it the privileges. After that, import the file create_tables.sql. My guess is this is better.

   Anyway, I’ll write my controluser current privileges:
————
GRANT USAGE ON *.* TO ‘pma’@’localhost’ IDENTIFIED BY PASSWORD ‘pmapass’;
GRANT SELECT, INSERT, UPDATE, DELETE ON pma_main.* TO ‘pma’@’localhost’;
GRANT SELECT (
Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
Execute_priv, Repl_slave_priv, Repl_client_priv
) ON mysql.user TO ‘pma’@’localhost’;
GRANT SELECT ON mysql.db TO ‘pma’@’localhost’;
GRANT SELECT ON mysql.host TO ‘pma’@’localhost’;
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
ON mysql.tables_priv TO ‘pma’@’localhost’;
————