Issue with phpmyadmin prefs table

[mchelen]

I get this error when trying to browse a table in phpmyadmin:

SELECT `prefs`
FROM `phpmyadmin`.`pma_table_uiprefs`
WHERE `username` = 'root'
AND `db_name` = 'symfony'
AND `table_name` = 'users'

MySQL reports: #1146 - Table 'phpmyadmin.pma_table_uiprefs' doesn't exist

This might be an Ubuntu-wide issue http://stackoverflow.com/questions/20731487/phpmyadmin-pma-table-uiprefs-doesnt-exist

I was able to fix it by using the interactive setup: sudo dpkg-reconfigure phpmyadmin

Tested on Ubuntu 14.04

[achton]

Same thing here. Reconfigured with dpkg and the missing phpmyadmin table was recreated.

[nerdstein]

Originally posted this in the wrong queue: https://github.com/geerlingguy/drupal-vm/issues/71

[nerdstein]

This still seems to be active

[geerlingguy]

Solution could be: http://stackoverflow.com/a/24937839/100134

I'll see how simple it is to just reconfigure the package as part of the setup task. Annoying... but it seems to be the status quo with Debian package installation :P

[geerlingguy]

Annoyingly, neither reconfiguring the package without user interaction (e.g. dpkg-reconfigure -f noninteractive phpmyadmin), nor manually running the create_tables.sql.gz script seems to fix this issue (see below for both solutions):

# Method 1 - this doesn't work.
- name: Reconfigure PHPMyAdmin (to fix an install error).
  command: dpkg-reconfigure -f noninteractive phpmyadmin
  when: install_phpmyadmin_debian.changed
  sudo: yes

# Method 2 - this doesn't work either.
- name: Expand phpMyAdmin create_tables.sql script.
  command: gzip -d /usr/share/doc/phpmyadmin/examples/create_tables.sql.gz
  when: install_phpmyadmin_debian.changed
  sudo: yes

- name: Reconfigure PHPMyAdmin (to fix an install error).
  shell: mysql < /usr/share/doc/phpmyadmin/examples/create_tables.sql
  when: install_phpmyadmin_debian.changed
  sudo: yes

It looks like this was caused by the Debian/Ubuntu package not being updated to take into account the double-underscore pma__ table names (originally suggested ten years ago) that are now defaults with phpMyAdmin installation.

I'm going to close this ticket and just recommend that Debian/Ubuntu users manually run sudo dpkg-reconfigure phpmyadmin after this role is finished.

Solutions to this problem:

• Run sudo dpkg-reconfigure phpmyadmin
• Edit /etc/phpmyadmin/config.inc.php and change all the table names in there manually so they're pma__* instead of pma_*
• Start using RHEL or CentOS, where this is not an issue ;)

Unfortunately, I don't want to take on the work of trying to automate these fixes; if someone else can figure out a way to automate the fix simply, please submit a PR.

[mchelen]

It looks like the only missing step after importing create_tables.sql is to rename the tables in /etc/phpmyadmin/config.inc.php? If so, I can try to write a sed command to take care of that.

[Luukyb]

I fixed this issue on my end, as root in PhpMyAdmin, I executed the SQL script create_tables.sql with a little change. I changed for every table created the double underscore by a single underscore. Script available here https://gist.github.com/Luukyb/efd2bb9b7932081b68a3

[korzh-nick]

Perhaps I do not understand what the root password for phpmyadmin. This patch solved the problem.
config.yml:

# phpmyadmin
phpmyadmin_mysql_user: phpmyadmin
phpmyadmin_mysql_password: PASSW
install_phpmyadmin_debian.changed: true

patch:

diff --git a/defaults/main.yml b/defaults/main.yml
index 7c75eb3..58da411 100644
--- a/defaults/main.yml
+++ b/defaults/main.yml
@@ -10,5 +10,5 @@ phpmyadmin_mysql_host: localhost
 phpmyadmin_mysql_port: ""
 phpmyadmin_mysql_socket: ""
 phpmyadmin_mysql_connect_type: tcp
-phpmyadmin_mysql_user: root
-phpmyadmin_mysql_password: "{{ mysql_root_password }}"
+phpmyadmin_mysql_user: ""
+phpmyadmin_mysql_password: ""
diff --git a/tasks/main.yml b/tasks/main.yml
index aaf669d..487617a 100644
--- a/tasks/main.yml
+++ b/tasks/main.yml
@@ -29,3 +29,23 @@
   - { key: connect_type, value: "{{ phpmyadmin_mysql_connect_type }}" }
   - { key: user, value: "{{ phpmyadmin_mysql_user }}" }
   - { key: password, value: "{{ phpmyadmin_mysql_password }}"}
+  - { key: controluser, value: "{{ phpmyadmin_mysql_user }}" }
+  - { key: controlpass, value: "{{ phpmyadmin_mysql_password }}"}
+
+- name: Copy phpmyadmin configuration files
+  template: src={{ item }}.j2 dest={{ phpmyadmin_config_dir }}/{{ item }} owner=root group=www-data
+  with_items:
+    - config-db.php
+
+- name: Expand phpMyAdmin create_tables.sql script.
+  command: gzip -d /usr/share/doc/phpmyadmin/examples/create_tables.sql.gz
+  when: install_phpmyadmin_debian.changed
+  sudo: yes
+
+- name: Reconfigure PHPMyAdmin (to fix an install error).
+  shell: mysql < /usr/share/doc/phpmyadmin/examples/create_tables.sql
+  when: install_phpmyadmin_debian.changed
+  sudo: yes
+
+- name: creating MySQL user for phpmyadmin
+  mysql_user: name={{ phpmyadmin_mysql_user }} password={{phpmyadmin_mysql_password}} priv=phpmyadmin.*:ALL state=present
diff --git a/templates/config-db.php.j2 b/templates/config-db.php.j2
new file mode 100644
index 0000000..2df067c
--- /dev/null
+++ b/templates/config-db.php.j2
@@ -0,0 +1,19 @@
+<?php
+##
+## database access settings in php format
+## automatically generated from /etc/dbconfig-common/phpmyadmin.conf
+## by /usr/sbin/dbconfig-generate-include
+## Thu, 27 Oct 2016 14:13:00 +0000
+##
+## by default this file is managed via ucf, so you shouldn't have to
+## worry about manual changes being silently discarded.  *however*,
+## you'll probably also want to edit the configuration file mentioned
+## above too.
+##
+$dbuser="{{ phpmyadmin_mysql_user }}";
+$dbpass="{{ phpmyadmin_mysql_password }}";
+$basepath='';
+$dbname='phpmyadmin';
+$dbserver='';
+$dbport='';
+$dbtype='mysql';
diff --git a/vars/Debian.yml b/vars/Debian.yml
index f8a72d5..c94bbbf 100644
--- a/vars/Debian.yml
+++ b/vars/Debian.yml
@@ -1,2 +1,3 @@
 ---
 __phpmyadmin_config_file: /etc/phpmyadmin/config.inc.php
+phpmyadmin_config_dir: /etc/phpmyadmin
diff --git a/vars/RedHat.yml b/vars/RedHat.yml
index adb32d7..e409f6b 100644
--- a/vars/RedHat.yml
+++ b/vars/RedHat.yml
@@ -1,2 +1,3 @@
 ---
 __phpmyadmin_config_file: /etc/phpMyAdmin/config.inc.php
+phpmyadmin_config_dir: /etc/phpMyAdmin