[RESOLVED] HELP! My database admin is a complete moron! Need to get around ridiculous issue!

lvictor

Sad to say, I'm having some severe issues with the administrator of mysql at my company. Because of the master/slave setup with mysql databases, the auto-increment is FORCED to 10. I get "1", "11", "21..."

Yes it's completely moronic. No, I've been trying for a month to get them to change their minds. They refuse and point me to an FAQ entry that states that they auto-increment to 10, no exceptions. Veeeery helpful... :glare: I ask how to get around it: "oh, we're not programmers... no kidding... if you were, you'd know this setup is completely retarded!"

I've been trying out a max(ID) solution, so far, it just shows "1" instead of the highest value +1.) The database requires numbering of records, and having them increase by 10 is well... moronic.

I'm looking for specifically where I'm going wrong here, and as a noob, well, sending me a link to the PHP manual entry for MaxID... let's just say I've read it, I'm using it, and still getting nowhere. Any suggestions? PHP file is attached in ZIP format.

etully

We are in the exact same situation at my company... except that I do trust my sysadmin. I've worked with him for ten years, he's amazingly smart, he understands the difference between controlling your equipment and letting it control you.

We have autoincrement also set at 10 but it's because we're using bi-directional replication. That is, each MySQL server slaves off the other and each one is a master to the other. This way, if one server goes down, the other one can continue to take orders and when the broken one is fixed, it automagically catches up on the orders that were taken while it was offline. The staggered autoincroment prevents collisions between the two databases.

If you only have one master and one slave, you do not need to worry about collisions and therefore don't need an autoincrement of 10. On the other hand, if you leave it at 10, then you will be prepared for the day when your company does decide that it wants up to NINE database servers for redundancy... so I suggest that you leave it at 10.

As a result of my sysadmin enforcing an autoincrement of 10, I have been forced to improve my programming style. I no longer use the autoincremented field for anything that the customer would see. It's strictly for behind the scenes purposes which, while different, is probably a good habit to get into.

You can create a 2nd field (maybe call it id2 or something), where you populate it with a sequential number essentially performing the autoincrement manually. Or, if you prefer, you could write a quick formula ( floor(id/10) ) that returns the actual sequential incremented id.

I know that's not the answer you were looking for. Believe me, I never like to let my systems create artificial constraints on me but in this case, I think it's the right way to go.

lvictor

etully wrote:
You can create a 2nd field (maybe call it id2 or something), where you populate it with a sequential number essentially performing the autoincrement manually. Or, if you prefer, you could write a quick formula ( floor(id/10) ) that returns the actual sequential incremented id.

Sorry, could you be more specific, please?

rulian

Well, if in the DB they use an interger type for the ID rather then plain text it would order itself appropriately rather then aphanumerical. meaning if they change the column ID type from string to biginter there wouldnt be a 1,10,11,12,20 order, since integers are stricly numbers, i would bide by the 1, 2, 4, 10, 11, 12, 20, 21 sequence.

Same is true for any values that are numerical but have their type set as plain text. Since aphabetically 1 comes before 2, it would list all values starting in 1 before 2, just like it would list all words begining with A before listing words starting with B,

etully

Sorry, could you be more specific, please?

Let's say you have a table like this:

id:  int  autoincrement
name:  char(100)
address:  char(100)
city:  char(100)

and your sysadmin insists that the ID field jump by 10. One thing you could do would be to add a field to your database called ID2. Then, whenever you add a new record, you check your table to see what the largest value for ID2 is. Maybe it's 720. You increment that number (721) and you update the record that you just added setting the ID2 field to 721. This way, even if the ID field is jumping by 10's, your ID2 field will increment 1 at a time. This way, the ID field is for behind the scenes use only and it doesn't matter what your sysadmin wants to do.

The other thing you could do is this. If you were using the id field as the order number, for example, you could select the id number and then in PHP, say: $actual_id = floor($id/10); This way, if the autoincrement id's are 1,11,21,31,41,51,61 then the $actual_id values would be 0,1,2,3,4,5.

bradgrafelman

etully wrote:
then in PHP, say: $actual_id = floor($id/10);

Or just use a [man]for/man loop with an increasing counter.

Is there a need to have your ID's be sequential? Can you tell us what good that would do for your application?

lvictor

bradgrafelman wrote:
Or just use a [man]for/man loop with an increasing counter.

Is there a need to have your ID's be sequential? Can you tell us what good that would do for your application?

Well, the application is a help desk, where I've been importing numbered issues (#1-110) and the # is the unique identification for the issue. Every time a new issue is opened, shouldn't it be the next # up (as opposed to every 10th #?) What would be an effective script for providing that sequence?

MarkR

Master-master replication is very tricky and should only be used in certain situations. I recommend that you carefully study it, and if it does not have the characteristics you require (which it probably won't do), you abandon it.

Auto increment columns should not be relied on to produce sequential values anyway.

But, in any case, master/master replication is a technology which needs to be used with extreme caution.

Otherwise, chaos will ensue.

I assume that you have master/master replication in your development environment too? And a system to check they're in sync? And you understand PRECISELY what the things are that you can do to break the sync, so that you can avoid doing any of them, anywhere in the application.

It is incredibly easy to create a replication conflict, and very hard to fix it.

Mark

lvictor

MarkR wrote:
Auto increment columns should not be relied on to produce sequential values anyway.

Mark

believe me, I'd just as soon not! Unfortunately, the guy who requested this help desk has his reports IDed by sequential #s, and wants to continue that practice that in the future for consistency. Isn't that the normal way that cases/issues are IDed in a database?

etully

Isn't that the normal way that cases/issues are IDed in a database?

The case numbers that users should see should be sequential, yes. The autoincrement ID, however, should not be relied upon to provide sequential numbers whether or not you are using bi-directional replication. You can keep asking if you want to but the answer is going to be the same.

lvictor

etully wrote:
The case numbers that users should see should be sequential, yes. The autoincrement ID, however, should not be relied upon to provide sequential numbers whether or not you are using bi-directional replication. You can keep asking if you want to but the answer is going to be the same.

Alright, so no matter what method I use, auto-incrementing the #s is a bad idea. The reason I keep asking is because people are throwing out their opinions on what I shouldn't do, without suggesting what I SHOULD do instead.

If relying on auto-sequence is bad news, OK, I can see your reasoning on that. What course of action would you recommend instead? Should I just throw out the ID numbers and use another field (title, date) as identifiers instead?

You know, I think this was better off in its original form... an Excel file that folks emailed each other :evilgrin:

etully

Should I just throw out the ID numbers and use another field (title, date) as identifiers instead?

You don't need to change the user's workflow. Asking the user to start identifying cases by title/date is unreasonable, unnecessary, and unprofessional.

what course of action would you recommend?

Maybe create a new field called id2 or something like that and increment it manually? Please re-read the entire thread; The solution has been provided twice. Your programming style will improve if you avoid relying on the autoincrement ID field to be sequential. The problem you are running into is a textbook example of why you can't - and shouldn't - rely on that.

etully

auto-incrementing the #s is a bad idea

Don't throw the baby out with the bathwater. Autoincrement is a great and wonderful thing. Seriously, it's one of the best things ever invented for databases. It guarantees that you can never have two records that are identical. The problem is that you are using that field as if it were guaranteed to be sequential and it's not. It should be used for behind the scenes stuff - it should be used as a way of making sure that you are acting on precisely the record you mean to be acting on. In other words, it's guaranteed to be unqiue, not guaranteed to be sequential.

lvictor

etully wrote:
One thing you could do would be to add a field to your database called ID2. Then, whenever you add a new record, you check your table to see what the largest value for ID2 is. Maybe it's 720. You increment that number (721) and you update the record that you just added setting the ID2 field to 721. This way, even if the ID field is jumping by 10's, your ID2 field will increment 1 at a time. This way, the ID field is for behind the scenes use only and it doesn't matter what your sysadmin wants to do.

This is definitely the way I'd like to go... I've been looking up the max(ID) function in the manual and google, and trying to use it in the form, as you can see from the code in my original post.

The problem is, it doesn't look like I'm doing it right, because I'm getting a blank field instead of the max # +1. I'm trying to figure out how exactly I'm supposed to phrase the max(ID) function to get it to display the next #up properly. I thought I'd ask the experts here if they could see where I'm going wrong and how I should correct it?

etully

I didn't look at your code but the logic goes like this:

SQL statement to add new record
Get the autoincremented ID number of the record you just added.

Assign it to variable $sys_id by using:
```
$sys_id = mysql_insert_id(); 
```
Lock this table for writing
Find largest value for id2
```
select max(id2) from help_desk_cases;
```
Assign that to variable $x
Increment $x by using $x++;

Update the record you just created like this:

update help_desk_cases set id2=$x where id=$sys_id

Unlock the table

etully

Here is how I use the max(id2) function in MySQL:

	$query = "select max(id2) from help_desk_cases";
	$result = MYSQL_QUERY($query);
	list($sys_id) = @mysql_fetch_row($result);

bradgrafelman

Or, without the list:

$query = "select max(id2) from help_desk_cases"; 
    $result = MYSQL_QUERY($query); 
    $sys_id = mysql_result($result, 0);

mpasternak

I myself never use an auto increment number for anything aside internal tracking and comparison. they're a good way of having a unique key feild for using on coding, But not for user end stuff that needs to look "good".

For example, I have an auto inc field on a table of data, and when i want a report, i just have the SQL link to to that autoinc number instead of a longer select statement using other fields. however I never output this number and it's only a pointer.

in your case, if you absolutely must have the next number in a sequence, I would create a new field for the ID you're looking for and do my own increment.

something along the lines of this.

SELECT MAX IDFIELD, * FROM TABLE

This will give you the highest IDFIELD number. And then when creating your new feild do an increment of 1.

the code would be similar to

$row=mysql_fetch_array(mysql_query("SELECT MAX ID, * FROM TABLE", $pointer));
$newid=$row[ID]++;
mysql_query("INSERT INTO TABLE VALUES ID='$newid'", $pointer);

lvictor

Thanks very much for all your suggestions. After much googling and hitting the books, I've come to the conclusion that if I can't even figure out how to implement the suggestions on the board without looking them up, I'm way below the typical noob and need to quit grabbing a prefab PHP script and try to "doctor" it.

Obviously, PHP and mysql are not something you can just jump into. For this project, I'm just moving over to a "form wizard" that bypasses PHP writing, or just start over with one of the many helpdesk-style programs out there. Either way, I'm through with going insane trying to wrap my head around fixes when it's clear that I've got to get through fundamentals FIRST.

Thanks again, everyone!

etully

Brad is tweaking my SQL and mpasternak is restating what I have already said three times in this thread so yes, I would say that this thread has pretty much run it's course. This would probably be a good time to mark this thread resolved.

In fact, I propose that the moderators just start marking threads resolved when they're done. This will prevent threads from lingering which invites added noise.