Course internal ID - Can it start from a specific number?

Course internal ID - Can it start from a specific number?

by Acqua Alta -
Number of replies: 8

Hi,
Each course in Moodle has an internal (Database) ID number that is shown in the URL of a course. For example:
https://MOODLE_SERVER/course/view.php?id=2

I want that when courses will be created, their number won't start from the beginning, but from a specific number that I define. For example. the URL of the first actual course in the system would be:
https://MOODLE_SERVER/course/view.php?id=31487
and then all the other courses that will be created afterwards, would follow this number in sequence.

Is it possible somehow?

Thanks smile

P.S.:
I didn't submit this question in the "Courses and course formats" forum, because it seems to me more like an installation issue. Maybe something that should be done in code prior to installing Moodle.

Average of ratings: -
In reply to Acqua Alta

Re: Course internal ID - Can it start from a specific number?

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

The database ID is managed by the database software your site uses. This reply discusses IDs for users which may help explain why this would be difficult and unlikely to work reliably.

If you could explain the problem you are looking to solve by doing this someone may be able to suggest a different approach.

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: Course internal ID - Can it start from a specific number?

by Acqua Alta -
Thanks for your answer.

It's common for LTI integrations to differentiate between courses by using the course ID ("id" field in mdl_course table). In some setups of Moodle environments, it is in issue that the best way to solve, would be to give each course a unique ID. It would still be a sequential ID, but a one that does not starts from 1, and because each Moodle environment would take a different range of ID numbers, there won't be a collision.
Average of ratings: -
In reply to Acqua Alta

Re: Course internal ID - Can it start from a specific number?

by Matt T -
Ah, ok, I see the reasoning. I'm not aware of any way to manually override the course ID number aside from tricky database manipulation.

Wouldn't you provide the LTI provider with the full course URL and not just a course ID?

Have to admit I'm not very clued into LTI.
Average of ratings: -
In reply to Acqua Alta

Re: Course internal ID - Can it start from a specific number?

by Matt T -
I'm very curious at the rationale underpinning this. Is for cosmetic or functional reasons?
Average of ratings: Useful (1)
In reply to Matt T

Re: Course internal ID - Can it start from a specific number?

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Matt (and Acqua,) I am not the expert about your question, but I can provide some perspectives.

In the mdl_course table, every course has to have a unique primary key, which is the "id" field. This is the number that displays in the URL, as Acqua shows in the original post. For quite a while, it seemed to be a sequential number starting with "1." A primary key needs to be unique, so the database assigns this number. Somewhere along the way, perhaps in 2019, this number (in my Moodle) switched to a larger number, 31460. Then in 2021, it switched to an even larger number, 47665. Why? Don't know. My guess is that this number might be somehow associated with the Moodle version number, and it might be used to help diagnose which Moodle version created a course. Since this is an arbitrary primary key number, it has never bothered me. However, to answer your question, no, one (Acqua) doesn't have control over it.

That being said, there is a textbox called "Course ID number" that you will see when editing course information. In my Moodle, this is blank by default. It appears that this is the field that schools have control over, and schools might want to coordinate this Course ID number with other school systems. For me, no, no need to do any coordination. However, I have personally decided to make this "Course ID number" relate to my sequence of courses by using my own code. For example, a code of 310. What this means to me is that this course was created in Moodle 3.x, and it is the 10th course that I have offered. Note, I run my own Moodle for my own purposes, so I can do whatever I want. I have courses going back to 125, 232, etc. Yep, 232 was my 32nd course while using Moodle 2.0. Someday, I will have a code of 401.

This "Course ID number" is the "idnumber" field in the mdl_course table. Sometimes I will create SQL queries and use this idnumber to sort by course. This might be where Acqua is heading.

Well, hopefully, something that I have said will make sense to you and help solve Acqua's problem.
Average of ratings: -
In reply to Rick Jerz

Re: Course internal ID - Can it start from a specific number?

by Acqua Alta -
Hi Rick,
Thanks for replying. Please see my answer to Leon.

Regarding the switch in numbers that you encountered, I have no explanation for that. For me, the numbers always started from scratch.

Regarding the "Course ID number", I'm already familiar with it. It was great if all integrations could use this field instead of the internal course ID. But I also understand why it's not being used - It's not required while the course internal id always exists in the database.
Average of ratings: -
In reply to Matt T

Re: Course internal ID - Can it start from a specific number?

by Acqua Alta -
Hi Matt,
Thanks for replying. Please see above my reply to Leon.
Average of ratings: -
In reply to Acqua Alta

Re: Course internal ID - Can it start from a specific number?

by Randy Thornton -
Picture of Documentation writers
The answer is yes, you can do this in the database. For tables with auto incrementing columns, you can use the auto_increment_offset value to specifically set the starting point number. It's a MySQL global and session variable, defaulting to 1.

This is commonly used in situations where you are replicating databases and need to avoid a collision of auto incrementing columns. For instance server one may have a table with have an offset of 1 and increment of 2, and a second server two may have it set to an offset of 2 and an increment of 2 - and so the servers will have the following patterns: 1,3,5,7,9 for server one and 2,4,6,8,10 for server two. A practical use of this in Moodle would be with something like managing multiple logs, where you want to replicate the logs from several Moodle servers to a single table on another server, retaining the original ids.

See: https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html and https://www.percona.com/blog/2011/01/12/conflict-avoidance-with-auto_increment_incremen-and-auto_increment_offset/

Of course, the offset marks the starting number only.  So you want to make sure that the second one starts with a number higher than the first one is every going to reach. And the maximum offset number is 65535.
Average of ratings: -