openSUSE admin

pjessen wrote:

In principle I see no issue with that, in practice it's not so easy. We don't really have much knowledge of the vBulletin internals, so writing a query to produce what you ask ....

I can offer a complete copy of the entire database, that is easily done. Takes up about 2.5Gb.

@pjessen, as long as it excludes the private moderator and admin forums.

Nycticorax wrote:

Question - are you SUSE staff or otherwise associated with SUSE ?

No and no, why?

Because it would likely have been easy to give you the access to the entire database or database dump. By having to fish out some of the parts, it becomes very difficult. We also have GDPR to consider.

Nycticorax wrote:

I see.

Well, if you give me a full specification of the exported json I can give you a Haskell or Python script that extracts just what we need. You run the script, inspect the result, and send it to me :)

The mysqldump output format is sql, csv or xml. Does that work?
Let's ask the forum moderators if they can work with your proposal - Malcolm, Knurpht, Jim? Wrt GDPR, I am not a lawyer, we may have to ask SUSE Legal, dunno. (we can still try out your script though).

pjessen wrote:

Nycticorax wrote:

I see.

Well, if you give me a full specification of the exported json I can give you a Haskell or Python script that extracts just what we need. You run the script, inspect the result, and send it to me :)

The mysqldump output format is sql, csv or xml. Does that work?
Let's ask the forum moderators if they can work with your proposal - Malcolm, Knurpht, Jim? Wrt GDPR, I am not a lawyer, we may have to ask SUSE Legal, dunno. (we can still try out your script though).

Hi Per
I did wonder about GDPR... I'm happy for a script to run and review the output with the other admins/moderators. But yes, some sort of legal review would be appropriate just to be sure.

malcolmlewis wrote:

pjessen wrote:

Nycticorax wrote:

I see.

Well, if you give me a full specification of the exported json I can give you a Haskell or Python script that extracts just what we need. You run the script, inspect the result, and send it to me :)

The mysqldump output format is sql, csv or xml. Does that work?
Let's ask the forum moderators if they can work with your proposal - Malcolm, Knurpht, Jim? Wrt GDPR, I am not a lawyer, we may have to ask SUSE Legal, dunno. (we can still try out your script though).

I guess still it kinda depends on what data is necessary; with the number of tables in the database schema, building SQL queries is pretty involved, but if we knew what the question is that we're trying to answer, there may be some built-in capabilities that will answer those questions. I think we need to better define what questions we'd like to answer, and then go from that point to see what tools need to be used to answer those questions (maybe I missed that discussion?).

Hi Per
I did wonder about GDPR... I'm happy for a script to run and review the output with the other admins/moderators. But yes, some sort of legal review would be appropriate just to be sure.

Not a lawyer, but if there's no PII in the end report (ie, if it's just aggregate data), GDPR wouldn't apply. It sounds like we really are just looking at message counts or keywords in content.

If it's for internal use only (and not being reported publicly), then GDPR shouldn't apply either, as I understand it.

pjessen wrote:

In principle I see no issue with that, in practice it's not so easy. We don't really have much knowledge of the vBulletin internals, so writing a query to produce what you ask ....

I can offer a complete copy of the entire database, that is easily done. Takes up about 2.5Gb.

In case anyone wonders, on the forum machine:

mysqldump --quick --single-transaction --xml -h mysql.infra.opensuse.org -P3307 webforums

For any myisam tables it might not be a 100% consistent dump, but in this context I think it'll suffice.

hendersj wrote:

I guess still it kinda depends on what data is necessary; with the number of tables in the database schema, building SQL queries is pretty involved, but if we knew what the question is that we're trying to answer, there may be some built-in capabilities that will answer those questions.
I think we need to better define what questions we'd like to answer, and then go from that point to see what tools need to be used to answer those questions (maybe I missed that discussion?).

I don't think we have gone into that level of detail. Adrien has outlined what he needs, and I'm trying to accommodate him without taking on too much effort.

Not a lawyer, but if there's no PII in the end report (ie, if it's just aggregate data), GDPR wouldn't apply. It sounds like we really are just looking at message counts or keywords in content.

Adrien asked for all posts and replies in certain fora, over a certain timespan. If they were emails, they would contain personal data, but maybe they don't in vbulletin ?

If it's for internal use only (and not being reported publicly), then GDPR shouldn't apply either, as I understand it.

Yeah, that's why I asked if Adrien was SUSE staff, for instance.

pjessen wrote:

hendersj wrote:

I guess still it kinda depends on what data is necessary; with the number of tables in the database schema, building SQL queries is pretty involved, but if we knew what the question is that we're trying to answer, there may be some built-in capabilities that will answer those questions.
I think we need to better define what questions we'd like to answer, and then go from that point to see what tools need to be used to answer those questions (maybe I missed that discussion?).

I don't think we have gone into that level of detail. Adrien has outlined what he needs, and I'm trying to accommodate him without taking on too much effort.

Yeah, he's specified what he needs, but not the question(s) he's trying to answer - at least as far as I know. From a 'minimum of effort' perspective, if we know the questions he's trying to answer can be answered within admincp, that saves everyone time. :)

Not a lawyer, but if there's no PII in the end report (ie, if it's just aggregate data), GDPR wouldn't apply. It sounds like we really are just looking at message counts or keywords in content.

Adrien asked for all posts and replies in certain fora, over a certain timespan. If they were emails, they would contain personal data, but maybe they don't in vbulletin ?

Well, there would be PII in data linked together (I'd have to look at the table structure again - it's been a while).

If it's for internal use only (and not being reported publicly), then GDPR shouldn't apply either, as I understand it.

Yeah, that's why I asked if Adrien was SUSE staff, for instance.

It might also make a difference if data that's exported contains additional PII that's not visible publicly on the forums. If the information is already public, I don't know that it's a GDPR violation to provide it again since the data's already out there.

Well, I tried extracting all table_structure nodes from an xml dump, just using "xmllint --xpath", but that quickly crashed:

parser error : PCDATA invalid Char value 27
parser error : Char 0x0 out of allowed range
parser error : Premature end of data in tag field

Nycticorax wrote:

Hello,

Any news on my request? I'm sure you've got a lot on your plate already but Christmas is next corner and working on this around the celebrations is not going to be really fun for anyone. :(

Well, I thought maybe you and Jim wanted to discuss the approach first :

Yeah, he's specified what he needs, but not the question(s) he's trying to answer - at least as far as I know. From a 'minimum of effort' perspective, if we know the questions he's trying to answer can be answered within admincp, that saves everyone time.

I'll see if I can get to try out your script out, but it is a busy time of year.

Thanks, Adrien - I saw the list of the desired data, but not what the questions were that you wanted the data to try to answer. Can you provide some clarity/context around that? When you get the data, what's the analysis that you're looking to do, and what is the goal of that analysis?

I've looked at the extract script, let me quickly record some notes, I will likely be distracted later.

• I'm not familiar with python, so bear with me
• sqlalchemy needs to be installed, but our machines have no pip
• do I need to install pip ?

It might overall be easier to grab the database dump and install it into a local sandbox machine instead of running on our production setup. I may still have a local machine that I was testing vbulletin on before the migration.