Instead of letting Roundup handle this uniqueness constraint, I think the SQL backends are a far better place to do this, i.e. keys should map to unique indices in the backends. (Of course, this means the change affects all backends, not just the backend_mysql.py.
The proposed solution is:

1. Adopt the convention that retired nodes use the nodeid for the value
of the __retired__ column.  So, if the node with id 7 is retired, the
value of __retired__ for that node is "7", not "1".  (There is never a
node with id zero, so this is a robust encoding.)  The "id" column is
also an INTEGER, so there is no problem with widths.

2. Change all code in the relational database backends that does
"__retired__ <> 1" to instead do "__retired__ = 0".  The former
comparison will no longer work with the new encoding.

3. For all tables with a key property ($key), run:

CREATE UNIQUE INDEX $name on $table ($key, __retired__)

This statement will make MySQL check that all records in the table have
a unique combination of the $key column and the __retired__ column.
That will cause an exception in Roundup at the point that the erroneous
commit is attempted.  We can trap this and try to give a sensible error,
but even if we fail to give a good error message, we will at least avoid
database corruption.

Does this sound sensible ?