2020/10/27: Difference between revisions

From Woozle Writes Code
Jump to navigation Jump to search
No edit summary
No edit summary
 
(9 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{nav/codeblog}}
I've got a bit of a double-bind situation here...
I've got a bit of a double-bind situation here...
* Email retrieval on cloud5 (where it had been for some months) stopped working when I upgraded Ubuntu to 20.04. {{l/htyp|Dovecot}} gives errors on IMAP and POP3. Postfix (SMTP) is still working, and we are able to access email via Roundcube, but not desktop or mobile clients.
* Email retrieval on cloud5 (where it had been for some months) stopped working when I upgraded Ubuntu to 20.04. {{l/htyp|Dovecot}} gives errors on IMAP and POP3. Postfix (SMTP) is still working, and we are able to access email via Roundcube, but not desktop or mobile clients.
* Meanwhile, Dovecot on the new cloud1 server ''is'' working -- but MySQL refuses to accept connections from MySQL Workbench, making it difficult to set up databases, which makes it difficult (though not impossible) to set up Roundcube there.
* Meanwhile, Dovecot on the new cloud1 server ''is'' working -- but MySQL refuses to accept connections from MySQL Workbench, making it difficult to set up databases, which makes it difficult (though not impossible) to set up Roundcube there.


I'm focusing on fixing the MySQL issue, as that is a blocker on several issues.
Breaking this down into two major pieces:
* [[/db]]: (FIXED) The MySQL issue came first, as that was a blocker to properly moving email (and some other stuff)
* The email issue spilled over into [[../28|the next day]].


One symptom: MySQL on cloud5 (which works properly) will only let me connect via CLI if I have a username and password. MySQL on cloud1 (not working for ssh tunnel connections) will let me connect from CLI with or without a username and with or without a password.
Moved the HypertWiki from cloud5 to cloud1, and finally decided it should just be the site for {{ls/domain|hypertwins.org}} instead of being limited to a subdomain. (The subdomain, {{ls/domain|wiki.hypertwins.org}}, should still redirect to the new location so that old links and image embeds will still work... I haven't done that yet.)
 
After looking extensively at configuration files, one relevant difference seems to be the GRANTS (<code>SHOW GRANTS FOR 'root'@'localhost';</code>):
<table width=100%><caption>'''cloud1''' (kind of not working)</caption>
<tr><td>
Grants for root@localhost
</td></tr>
<tr><td>
<source lang=mysql>
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
</source>
</td></tr>
<tr><td>
2 rows in set (0.000 sec)
</td></tr></table>
 
<table><caption>'''cloud5''' (working properly)</caption>
<tr><td>
Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              </td></tr>
<tr><td>
<source lang=mysql>
GRANT
  SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
  PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
  CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
  REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,
  CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE
ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                             
 
GRANT 
  APPLICATION_PASSWORD_ADMIN,
  AUDIT_ADMIN,BACKUP_ADMIN,
  BINLOG_ADMIN,
  BINLOG_ENCRYPTION_ADMIN,
  CLONE_ADMIN,
  CONNECTION_ADMIN,
  ENCRYPTION_KEY_ADMIN,
  GROUP_REPLICATION_ADMIN,
  INNODB_REDO_LOG_ARCHIVE,
  INNODB_REDO_LOG_ENABLE,
  PERSIST_RO_VARIABLES_ADMIN,
  REPLICATION_APPLIER,
  REPLICATION_SLAVE_ADMIN,
  RESOURCE_GROUP_ADMIN,
  RESOURCE_GROUP_USER,
  ROLE_ADMIN,
  SERVICE_CONNECTION_ADMIN,
  SESSION_VARIABLES_ADMIN,
  SET_USER_ID,
  SHOW_ROUTINE,
  SYSTEM_USER,
  SYSTEM_VARIABLES_ADMIN,
  TABLE_ENCRYPTION_ADMIN,
  XA_RECOVER_ADMIN
ON *.* TO `root`@`localhost` WITH GRANT OPTION
 
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
</source>
</td></tr>
<tr><td>
3 rows in set (0.00 sec)
</td></tr></table>
Trying to apply the cloud5 settings to cloud1, I get this for the first GRANT query:
<blockquote>
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION' at line 1
</blockquote>

Latest revision as of 02:09, 23 November 2021

Codeblog

I've got a bit of a double-bind situation here...

  • Email retrieval on cloud5 (where it had been for some months) stopped working when I upgraded Ubuntu to 20.04. Dovecot gives errors on IMAP and POP3. Postfix (SMTP) is still working, and we are able to access email via Roundcube, but not desktop or mobile clients.
  • Meanwhile, Dovecot on the new cloud1 server is working -- but MySQL refuses to accept connections from MySQL Workbench, making it difficult to set up databases, which makes it difficult (though not impossible) to set up Roundcube there.

Breaking this down into two major pieces:

  • /db: (FIXED) The MySQL issue came first, as that was a blocker to properly moving email (and some other stuff)
  • The email issue spilled over into the next day.

Moved the HypertWiki from cloud5 to cloud1, and finally decided it should just be the site for hypertwins.org instead of being limited to a subdomain. (The subdomain, wiki.hypertwins.org, should still redirect to the new location so that old links and image embeds will still work... I haven't done that yet.)

Retrieved from "https://wooz.dev/mw/index.php?title=2020/10/27&oldid=932"