Course and Role Id's only in odd numbers (skipping even numbers)

Course and Role Id's only in odd numbers (skipping even numbers)

by Chye Siaw -
Number of replies: 3

Moodle 3.1.1+

Hi all, we are encountering a very strange issue with moodle.

Upon installing moodle on our server, we noticed that Course and Role Id's are listed as 1,3,5,7, etc. Eg. Manager roleid=1, Course creator roleid=3, Teacher roleid=5, etc. When we create a new course or role, it takes the next 'odd' number.

Does anyone know what is causing this and how to fix it? Our host uses MySQL, not MySQLi, if that makes any difference. Tried installing moodle with MySQLi and mariaDB, both encounters the same issue.

Any help would be very much appreciated!

Average of ratings: -
In reply to Chye Siaw

Re: Course and Role Id's only in odd numbers (skipping even numbers)

by Jamie Kramer -

Are you by chance using a clustered or replicated database backend?

The first thing that came to mind is the mariadb/mysql "auto increment offset" setting:

https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#auto_increment_offset

I don't recommend that for Moodle. I have heard of that being enabled by default in some setups.

You could confirm this by looking at some other tables and see if the id field have incremental ids or if they are also all "odd"



Average of ratings: Useful (1)
In reply to Jamie Kramer

Re: Course and Role Id's only in odd numbers (skipping even numbers)

by Chye Siaw -

Yes, speaking to others we think it is due to our database's default auto increment, but haven't had time to look into that yet. I believe it's auto_increment_increment that needs changing, as compared to auto_increment_offset. You don't recommend changing that?


The other id's (such as courses) are all "odd" as well, so as you say it's most likely the reason. Thanks for your reply!

Average of ratings: -
In reply to Chye Siaw

Re: Course and Role Id's only in odd numbers (skipping even numbers)

by Jamie Kramer -

Hi Chye. Yes I agree, it does seem that the auto_increment_increment is the one causing this.

If you are using master-master database, the two settings we are discussing cannot probably be changed.

It has been a while since I've looked closely at this, but I do not think Moodle supports Master-Master database setup. In fact it broke Moodle on older versions a few years ago when master-master db is used, and more than one front-end web application servers are accessing two different databases. If you are using Moodle with master-master databases in this way, you probably cannot safely change the increment settings. To this point, it is also highly dependent on what type of replication is being used between the two databases.

At this point I would recommend some caution. Find out if you are using a master-master db setup, where both master databases are being used simultaneously. I think it is important to understand this before making any changes. Also a database backup will be very important to have before making changes. The reason I am advising so much caution is because the id numbers are so critical to Moodle.

If you are not using multiple databases with master-master, and it is currently working fine, perhaps no change is needed. Though based on my experiences from a few years ago, I don't recommend using auto_increment_increment.

If you have a "young" site that is not in production, or just in "testing/evaluation" phase, then perhaps starting with a clean slate, a fresh install, is in order. My worry is that continuing as-is may end up with irreversible and unrecoverable damage to the db. Again this all depends on: if you are actually using a master-master setup, if both masters are being used simultaneously, what replication engine is being used, Moodle version, plugins that may behave differently, and probably more considerations.

I would like to find more recent information on Moodle and auto_increment_increment. I did a quick search and didn't find what I was looking for, something more from a very technical developer perspective.


Average of ratings: -