Quite a big moodle problem

[timbo343]

We are running moodle 1.8.1 and have lost our administrators table in MYSQL, which is tied into AD. Hence we have lost the original loging that was created to build moodle before it was tied into AD. The guy configuring and that works on the moodle server is our network manager who works part time. What im asking is, is there anyway we can rebuild a list of administrators or will it have to be start from scratch job, as the main IT teachers cannot upload any courses for the kids and cannot track their grades.

 

Thanks

 

Tim

[webman]

Just restore it from the last backup?

[timbo343]

ahhh, yeah never thought of that... derr.. will see when he took a backup... trouble is he doesnt seem to be all there and is a bit slow at doing things

 

Cheers

[timbo343]

Right, we dont know if he has been taking backups, i guess he hasnt. I think we are pretty screwed unless anyone has any ideas.

[webman]

Has anyone had similar problems on the Moodle forum at all?

[timbo343]

im looking at something on the moodle site http://moodle.org/mod/forum/discuss.php?d=54165 but i dont know how mysql works, so im a bit stuck on how to get into the databases.

[timbo343]

right im into the mysql server. how on earth do i find the tables?

[Geoff]

The database doesn't work like that. There is no 'administrstors' table. There is a 'mdl_user' table that holds all the basic info for all your users. Each user has an 'id' in this table. This is cross referenced with the 'mdl_role_assignment' table. Which sets the role assignments for users, including the admin role. Therefore you must first search the mdl_user table for a replacement 'admin' user and find the id. Once this has been done then you can add a new record to the 'mdl_role_assignment' table and manually give this user admin rights.

 

For example, if I wanted to give the user with id 20 administration rights.

 

INSERT INTO `mdl_role_assignments` ( `id` , `roleid` , `contextid` , `userid` , `hidden` , `timestart` , `timeend` , `timemodified` , `modifierid` , `enrol` , `sortorder` )
VALUES (
LAST_INSERT_ID( ) , '1', '1', '20', '0', '0', '0', UNIX_TIMESTAMP( NOW () ) , '0', 'manual', '0'
);

[timbo343]

okay lol. quick question, is there a way to view what is inside the tables?

[webman]

SELECT * FROM tablename;

 

Will show all columns. Which will be a lot of data to look through. You will best off doing a few, such as:

 

SELECT id, roleid, userid FROM tablename

 

Replacing tablename with the name of the table you want to look at.

[timbo343]

webman, just to let you know, i did a search:

 

SELECT id, roleid, userid FROM mdl_user;

 

and i got

 

ERROR 1054 (42S22): Unknown column 'roleid' in 'field list'

 

Does this mean that roleid does not exist

[webman]

It just means that column doesn't exist in the mdl_user table. I was just plucking example fields from the query in Geoff's post, which operates on the mdl_role_assignments table.

 

You can find out the columns in a table like so:

 

SHOW COLUMNS FROM tablename

[timbo343]

ok, another question.

 

Is it possible to find the user with the id of say 145 and show all the details?

[Geoff]

SELECT * FROM 'mdl_user' WHERE 'id' = 145;

 

Just be aware there are numerous other mdl_user_* tables with additional user info in them. However I don't believe they are relevant to what you are attempting to do.

[timbo343]

what im trying to do is to look at a specific user to see what access they have, and change it if it aint right, as i have found the mdl_role table with all the roles

[Geoff]

ah in that case you want to look at the roles table.

 

SELECT * FROM 'mdl_role_assignments' WHERE 'userid' = 145;

[timbo343]

how do you edit the tables through mysql?

[Geoff]

Use the 'UPDATE' keyword to change rows. Use the 'INSERT' keyword to create new ones. Use 'DELETE' to remove them.

[timbo343]

Cheers

Ill give it ago

Just want to say thanks to everyone that has helped me out on this topic