MySQL MemberAdaptor for Mailman 2.1

[kyrian]

A MemberAdaptor "plugin" which should allow Mailman list members
to be loaded from a MySQL database, rather than just a Mailman
"pickle" file.

Provided as-is, and without warranty, this "plugin" may destroy your
server, soul, scalp, house, and life. Please use it with caution.

Kev Green, oRe Net.
http://www.orenet.co.uk/

[kyrian (kyrian)]

Logged In: YES
user_id=99923

Bit of an oops in version 1.49, 1.50 now uploaded, which should fix it.

[kyrian (kyrian)]

Logged In: YES
user_id=99923

Latest version incorporates automated generation of the necessary tables,
cleaner error reporting, and updated documentation.

[kyrian (kyrian)]

Logged In: YES
user_id=99923

Version 1.57: 2004/12/13

* Merge in Daniel Shriver patch/code for a flat table
architecture.
  [ Suggested by Kevin McCann <kmccann(at)bellanet.org>, but
I hadn't found time to do it myself... ]
* Add bugfix information from Jinhyok Heo
<email address hidden>
* Add in mksqlmailman script from TheSin
<email address hidden>
* Follow Barry Warsaw's suggestion on delivery status timestamp.

[kyrian (kyrian)]

Logged In: YES
user_id=99923

Oh, btw. v1.57 hasn't been tested yet, so it might kill your
server, eat your dog, and stick your wife in the oven. Be
careful using it!

[simboforge (simboforge)]

Logged In: YES
user_id=1226150

neat. just what i was looking for. i have a couple of live
lists running on version version 2.1.5. i am a bit leary
about unzipping this distrobution over the top of my working
mailman as i am not sure how to backup the existing flat
file databases. i would prefer to experiment with dropping
the adapter into my existing installation. any pointers as
to where to start? thx

[kyrian (kyrian)]

Logged In: YES
user_id=99923

The flat file databases are unused when you put the MySQL adaptor in
place, and they are untouched (and indeed not deleted) by it, the MySQL
adaptor only queries the MySQL tables for membership information, without
trying to force you into using it fulltime by deleting anything, etc.

Once you unconfigure the MySQL adaptor, Mailman should revert back to
your existing flat file databases.

Of course, I could have missed something, etc. so do back up your flat file
databases before installing the MySQL adaptor, and then you can just
migrate back to them by restoring the backups.

Either way, you have come up with a good question for an FAQ on the SQL
adaptor :-)

K.

[simboforge (simboforge)]

Logged In: YES
user_id=1226150

excellent. can you please give me a list of the files
modified from the original distro? grep sees six that grep
have msyql in them. thx

[egervary (egervary)]

Logged In: YES
user_id=1255996

Thank you for your good work.

FYI: there's a missing "AND" in MysqlMemberships.py in line 494.

[kyrian (kyrian)]

The file MysqlMemberships.tar.gz was added: version 1.61

[kyrian (kyrian)]

Logged In: YES
user_id=99923

Folks,

Well, I've finally gotten around to releasing an update to
this thing, after a phone conversation with the client who
are using it, and I've incorporated a couple of cosmetic
fixes, fairly substantial changes to make the bounce
processing work properly (the ability to set people to
NOMAIL now exists, and real world testing is commencing
soon), and incorporated the flat vs. wide table archivecture
types with various bugfixes.

Main notes:

* It would be a good idea to change all your delivery_status
fields from VARCHAR(255) to INT(10) or similar, so that they
will work properly, and not bomb out on this new version
(they should be okay I think, but I'd advise the change).

* The missing-'AND' typo has been rectified.

Any suggestions and bugs should be sent to my sourceforge
account address, which ends up in the right place.

K.

[kyrian (kyrian)]

Logged In: YES
user_id=99923

Folks,

For my own sanity I have decided to create a dedicated area
of my business website for various opensource projects, and
put this project among others under there, so please refer
to this location for updates to this patch in future:

http://www.orenet.co.uk/opensource/

I will still be reading bug reports via sourceforge, but if
nothing else, their user interface is much more fiddly and
time consuming to publish files than a quick tar -czvf and
an scp, so I'll be using my own environment to host the
files at least for the time being.

The latest version at this time is 1.61, I belive it still
requires substantial testing which I'll get around to one of
these days.

K.

[adrianwi (adrianwi-users)]

Logged In: YES
user_id=1175103

Thank you for developing this adaptor! So far it's working
well with the limited testing I've done.

There seems to be a minor bug in the addNewMember method of
this adaptor (version 1.61).

At lines 579-584, there are SQL statements which add a new
row in the MySQL database for a new member. In those
statements, the column 'delivery_status' is given the value
of MemberAdaptor.UNKNOWN. MemberAdaptor.py defines:

# Delivery statuses
ENABLED = 0 # enabled
UNKNOWN = 1 # legacy disabled
BYUSER = 2 # disabled by user choice
BYADMIN = 3 # disabled by admin choice
BYBOUNCE = 4 # disabled by bounces

So, it appears that the SQL statements should be changed to
use the value of MemberAdaptor.ENABLED instead of
MemberAdaptor.UNKNOWN (which is "legacy disabled").

Making this change prevents "nomail [reason]" ? from being
set in the user's options for new members (as seen in the
web interface). This was tested with Mailman 2.1.6.

[gustavo_peters (gustavo-peters)]

Logged In: YES
user_id=1272569

asa<cdzxcx

[egervary (egervary)]

Logged In: YES
user_id=1255996

Thank you again for this SQL adaptor.

Non-alphanumeric characters (such as "-", the dash
character, what is valid, and quite common in email and
maillist addresses) in list names generates SQL syntax
errors, you should use backticks in all SELECT, UPDATE, and
INSERT commands, like "SELECT name FROM `%s` WHERE ..."

my patch is available here:
http://www.expertlan.hu/egervary/forum/MysqlMemberships.py.diff

Another issue is with getDeliveryStatus, which does not work
for me. row[0] does not return the value stored in the sql
table, so the function always returns MemberAdaptor.ENABLED.
(the sql table contains '3' properly, so there's no problem
with setDeliveryStatus)

[kyrian (kyrian)]

Logged In: YES
user_id=99923

..."you should use backticks in all SELECT, UPDATE, and
INSERT commands"...

Thank you, I hadn't thought of that when writing the
original. Your patch will be applied to the next version I
release, and you will be credited accordingly.

Unfortunately I've just started using Eclipse & CVS for this
and other projects, which has nuked all the RCS tags I had
before. I'll have to fix that before the next release can be
issued.

As regards the getDeliveryStatus() issue. If I haven't fixed
it yet, I will try to do so as soon as reasonably possible.

There are a couple of other additions and changes I'd also
like to do as suggested by other people. We shall see...

[kyrian (kyrian)]

All,

I've finally awoken, researched, and put in a fix for the non-ascii encoded characters. It's version 1.71. I am not sure if that's the correct version tag due to changing to Eclipse IDE, but that's the tag I've given it anyway ;-)

It'll be here as usual:

http://www.orenet.co.uk/opensource/

I've not had a chance to test it yet, so I would be interested in feedback.

With this done I think the only thing to look into is optimisation...(?)

K.