MySQL Bugs: #115179: Replication Setup Documentation missing SOURCE

Bug #115179	Replication Setup Documentation missing SOURCE_SSL=1.
Submitted:	30 May 21:03	Modified:	31 Jul 15:14
Reporter:	Jean-François Gagné	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	8.4.0, 8.0.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

when I follow the instructions in the 8.4 Documentation for setting-up replication, more specifically the section "Setting the Source Configuration on the Replica" [1], when running "START REPLICA", I get the error "Authentication requires secure connection".  See how to repeat for details.

[1]: https://dev.mysql.com/doc/refman/8.4/en/replication-howto-slaveinit.html

I think the "CHANGE REPLICATION SOURCE TO" is missing "SOURCE_SSL=1", when I add it, it works.  See how to repeat for details.

Even if the documentation mentions another solution when not using a secure connection (GET_SOURCE_PUBLIC_KEY=1), I think the commands from the doc should be working without having to read fine-prints.

I set this as Severity Serious (s2) because even though this is not Critical, the Documentation not working is more than Minor / S3.

Related: Bug#112766 (Feature req: SOURCE_SSL should default to 1 for replication).

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
dbdeployer deploy multiple mysql_8.4.0

./n1 <<< "RESET BINARY LOGS AND GTIDS"
./n1 <<< "CREATE USER 'repl'@'%' IDENTIFIED BY 'password'"
./n1 -u root <<< "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'"

port=$(./n1 -N <<< "select @@global.port")
sql="change replication source to SOURCE_HOST='127.0.0.1', SOURCE_PORT=$port, SOURCE_USER='repl', SOURCE_PASSWORD='password'"

# As show by the output below, this does not work.
./n2 <<< "$sql; start replica"; \
  sleep 1 &&
  ./n2 <<< "show replica status\G" | grep -e Last_IO_Error:
                Last_IO_Error: Error connecting to source 'repl@127.0.0.1:24401'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

# If I add SOURCE_SSL=1, it works.
./n3 <<< "$sql, SOURCE_SSL=1; start replica"; \
  sleep 1 &&
  ./n3 <<< "show replica status\G" | grep -e Last_IO_Error:
                Last_IO_Error: 

# As written in the doc, adding GET_SOURCE_PUBLIC_KEY=1 also works.
./n2 <<< "stop replica; $sql, GET_SOURCE_PUBLIC_KEY=1; start replica"; \
  sleep 1 &&
  ./n2 <<< "show replica status\G" | grep -e Last_IO_Error:
                Last_IO_Error: 

Suggested fix:
Add SOURCE_SSL=1 in the doc.

Also consider having SOURCE_SSL default as 1 as suggested in Bug#112766.

Hello Jean-François,

Thank you for the report and feedback.

regards,
Umesh

(updating version: was "8.40, 8.0.37", typo fixed to "8.4.0, 8.0.37").

This is as intended.

Reasoning: 

1. A secure connection is not required by default, only if SOURCE_SSL=1.

2. Setting SOURCE_SSL=1 means an encrypted connection is *required*; replication cannot fall back to an unencrypted connection in such a case.

3. The page already provides a link to info regarding options to employ for encrypted connections, and info about SOURCE_SSL and friends is included on that page.

4. Including SOURCE_SSL=1 in a list of options intended for a default replication scenario will give rise to bug reports asking why replication fails when the option is "specified just as shown in the Manual".

If and when we begin to require encrypted connections for replication, we'll be happy to revisit this issue then, but not at this time with things as they are now.

Thanks!

jon.

Thanks for your reply Jon, but please allow me to argue a little with you.

From my point of view, things do not "work as intended".  In my argument, I focus on the error message I got, quoted below from the original report.

> Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

I do not understand all the nuances of "Authentication plugin 'caching_sha2_password'", but if it "requires secure connection", and if it is the default configuration (from what I understand, this is something new in 8.4), then replication needs a secure connection by default.  And this reasoning contradicts what you write, quoting you below.

> A secure connection is not required by default, only if SOURCE_SSL=1.

Maybe the solution is not modify the documentation to add "SOURCE_SSL=1" (or "GET_SOURCE_PUBLIC_KEY=1"), but I would expect the instructions in the manual to "just work" (adding one of these is the way I found to "make things work"), without having to read the fine-print, and without having to understand all the intricacies of caching_sha2_password and secure connections.

So could this bug report be reconsidered, maybe without focusing on the title I might have wrongly chosen (Replication Setup Documentation missing SOURCE_SSL=1), but on the fact that using the current documentation, things do not work as expected.

Thanks, J-F

> from what I understand, this is something new in 8.4

I was wrong here.  Re-reading my report, this also affects recent 8.0 (I just reproduced with 8.0.39).