TR Forums

Hey guys, I'm running into an issue with the following:

SUBSTR((UNIX_TIMESTAMP(`vmail`.`mailbox`.`created`) -
CHAR_LENGTH(`vmail`.`mailbox`.`password`)), -(8)) AS `ID`,

FWIW I didn't write this. The point is to create a UID that doesn't change. I don't understand WHY the author wrote it that way. No. ****. Clue. It's part of a plugin I'm using and the rest works wonderfully.

It's for a SQL based webmail address book. The problem occurs when I mass create users. They all get the same time stamp. And the character length of their password hash sure as hell isn't friggin' varied enough to come close to unique between all users. Any suggestions on how I might achieve the goal here? My only thought right now is to try and drill further down into the time stamp and see if I can get a milliseconds return or some such. That might work if it's stored that way. Maybe. Thanks!

You're barking up the wrong tree, mate.

What you want is simply a sequential ID (assuming you don't have one already). If you want to have a separate identifier for some reason, then what you're looking is a "UUID," which all RDBMS offer in one way or another, precisely to avoid users falling into the same pits over and over again.

Do note that GUIDs are usually a PITA to index, as they're not sequential. If all you need is to inequivocally point at a user, a bog-standard incrementing integer is by far the best option.

Leave funky calculations alone, they're the domain of TheDailyWtf

You know, I IMAGINE you're right. My problem is that I have NO IDEA what the author was trying to achieve EXACTLY. From his README:

"ID has to be a Unique Integer but does not have to be ordered or anything else, just unique and static, must not change per user [but can change if the user changes, so can be based on the timestamp of user modification/creation]"

The part the gets me is that it can change at each user level. So yeah, to me it sounds like I can just use a sequential ID. My trepidation here is more along the lines of... well then why the @!#$@! did you do it THAT way!? I'm a friggin' newb with SQL and I know you can do it other ways.

Yeah I don't blame you for doing a little head-scratching. After reading that I'm not sure if they knew exactly why they picked that blob as a user ID either.

It does sound like a serial ID would do fine though. Like morphine said, they're used all over the place to avoid exactly these kind of problems.

Ah...I think I'm getting it now. This is for a VIEW. It seems like you can't add data to a VIEW that doesn't exist... but you CAN alter existing data. So this is beginning to make a little more sense.

A view definition is subject to the following restrictions: The SELECT statement cannot refer to system or user variables.

&

http://forums.mysql.com/read.php?101,259575,261738#msg-261738

I think this explains why he was hooking into an existing value. Now I have no clue where to go. Thoughts appreciated. Thanks!

Unless more context is there, I am not sure why AUTO_INCREMENT won't work. After the number is committed to the table, it is not a variable anymore? It's not like this ID has user input in there.

Also, why would a view contain an internal "ID"? So is this going to be shown/printed to users?

A view is really just shorthand for a particular query. Sometimes you need one if you're doing database driven publication or using some other frontend application which needs a single, aggregate data source instead of just looking at the tables involved directly. Trying to use a view to invent data which isn't there is just going to cause headaches. If the view needs an ID for some reason, does the table storing users not have its own primary key?

The number isn't getting committed to a table. Only to a view based on the table. I think the author did it this way so as to not mess with the program which owns the database the view is being created from. I'm not sure where in his code the integer becomes important. I could likely figure it out, but damn if I'm not looking to change how the thing functions at that level.

I could probably add a column to the table with a UID of some sort, but I don't know enough to know if this might effect anything else in the system... and I'm not sure where I would have to build such a function in to the program itself so that each new addition gets the new ID. I could probably figure it all out, but future updates and breaking etc.

The simplest thing seems to be to somehow correct the tiny bit of SQL that's attempting to give each row in the view a unique integer. I can think of a few ways to achieve a unique ID based on the data, but the requirement of it being an integer keeps pulling me short.

If the view needs an ID for some reason, does the table storing users not have its own primary key?

It doesn't. I have no idea if this is poor design or not at my knowledge level. I have some strings that are unique... but deriving an integer from one of them, and ensuring the resulting integers would be unique... ugh.

My thought process right now, since this is based on the creation date, is if I can build some sort of recursive statement that checks if the date in a table exists more than once and increments the smallest portion by 1 if it does before moving to the next date to check.

|FN|Steel wrote:

If the view needs an ID for some reason, does the table storing users not have its own primary key?

It doesn't. I have no idea if this is poor design or not at my knowledge level.

Yeah that's a poor design. Every table should have a primary key(and some may also have one or more foreign keys). Join conditions are a mess without them, and basic operations like updates and deletes run the risk of unintended consequences.

Are you sure the users table has no primary key? This sounds really bad.

Do you know the way the view is used, is this "unique ID" is really necessary? Seems like they may need to be stable in the sense that queries run at different times should get you the same UID for a given user. At this point to avoid collisions with the created timestamp you may as well tag an SHA hash at the end of this "ID" and hope for the best.

Or, if the view query is not run very often, create a new table that maps to the real users table and add a column with completely reassigned ID/numbers. And you run some scheduled jobs to update it later? But seriously, if there is no primary key in the users table, how does it handle the multiple John Does that are in the system?

Maybe I'm not knowledgeable enough to know where the primary key is? I have a database. A bunch of tables... none of which seem to be anything resembling a PK or have one. It would be in a table, right?

I don't know what the UID is used for. All I know is that it needs to be an integer... and you may have just solved my problem, actually. Adding/subtracting the current time stamp, at a more detailed level, to the saved creation date might work.

The view is for a Global Address Book (GAB) for email. That may address your question as there can't be multiple John Does. It's username based on a naming convention of johnd johnd01 johnd02 etc. Also the view is refreshed each time a user selects the GAB in the client.

*sigh* Of course that won't work. It's going to return NOW() once, not once for each entry. Bah.

Just since I know you guys are hanging by the seat of your pants wondering if I came up with a solution....

I went through the Issues log on GitHub for the code and found someone having a similar issue. They decided to make this change and apparently haven't had any issues with it:





It corrected my issue as well. So I'm just gonna keep an eye on it. Apparently the program the plugin was created for was the one "suggesting numeric IDs". *shrug* It works! That's all I care about for now!

Check your users (emails?) table to see if it has an integer, auto-increment column.

If there isn't one, create it, say, "id_user". It'll automatically populate with sequential numbers. You can then replace that horrible thing in the view with just pulling that ID in it, ex: "SELECT blah blah.... id_user FROM ...."

I would NOT advise using that MD5 hack. Read up on hash collisions

Check your users (emails?) table to see if it has an integer, auto-increment column.

I did. I dumped the whole thing out. No such luck.

I would NOT advise using that MD5 hack. Read up on hash collisions

Well damnit. I mean, I guess I could just leave it as the userid? It will always be unique.

Try this, maybe it's got a key which is non-numeric, like email or username.

SHOW CREATE TABLE users

Replace "users" with whatever the name of your users table is. There should be a line in there which says "PRIMARY KEY ('some_column_name')". If there is, use that as your user id(because it actually is your user id!). If there isn't, then you should add one, like morphine said above.

Adding an auto increment column to the table is easy. My lack of knowledge in what it could break is one thing which stops me. In general, my guess is that it wouldn't break anything. Nothing should be referring to the tables in such a funky fashion that an extra table would throw it off, but I don't know that. For all I know there's a good best practice for counting tables or something to return something else. Yeah, probably not, but still.

The second thing is pure laziness. Unless auto_increment works contrary to how I believe, each time I add a user I'd have to update the column manually... or dig into the code and find the piece that adds the information to this particular table and add auto_increment. Which I then might have to change each time updates are received for the main program.

It's not really pure laziness though, it's more that I really don't have time to spend on minutiae at the moment. I've already devoted a few hours to this and have a lot more to accomplish.

The database increments an auto_increment column on its own.  It should be entirely transparent to any application using it.

auto increments require no user intervention; in T-SQL at least you'd just omit that column from your insert.

What actual RDBMS is this? Would be useful to assist more thoroughly

MariaDB

Yeah, an auto_increment field in MariaDB will automatically increment if handed a null or empty value, so you'd just modify code to not hand anything to that column 

Kewl. I'll look into it in a bit. I just completely **** over the webmail client trying to get a calendar running. Yay!

Since there are github issues on this, are you looking at some open source stuff?

|FN|Steel wrote:

Kewl. I'll look into it in a bit. I just completely **** over the webmail client trying to get a calendar running. Yay!

Was somebody writing changes directly to prod? ; ) 
(One of those key rules to avoid right next to 'don't drink and DBA') 

So basically if I am understanding, your program needs a unique integer ID.  Unfortunately, MySQL (and Maria) have limitations that prevent automatically creating those in a view. 

You could try something like this:






For each row, this will count the number of rows that are smaller or equal.  So as long as the combination is unique (you can add however many columns you need) the ID column should match the row number in your result set.  It's been a while since I've used MySQL but I think this will work...  I know it doesn't like subqueries in the FROM section, but I don't recall it minding them in the SELECT section...  You may need to cast your timestamp as character to add them.

Flying Fox wrote:

Since there are github issues on this, are you looking at some open source stuff?

Oh yeah. I'm working with iRedMail, which is a combination of RoundCube, Dovecot, Postfix, Amavis, and ClamAV. This particular piece is a plug-in for RoundCube which makes a global address book based on the SQL tables.

pikaporeon wrote:

Was somebody writing changes directly to prod? ; )
(One of those key rules to avoid right next to 'don't drink and DBA')

lol - It's not QUITE production yet, but yes, I should have had a backup running already. It was "lemme do this one last piece"... and it kinda exploded into a **** ton of problems. I upgraded from PHP 5.4 to 7 (due to a security vulnerability in 5.4), which screwed all sorts of ****. Then I rebooted the machine only to discover that an upgrade I THOUGHT I had stopped from MariaDB 5ish to 10ish had at least partially gone through and there were all kinds of linking errors and I had to clear up the yum installs and then restore my tables. THEN, somehow... and I really have no **** clue how it happen, Postfix was gone. The CF files were all still there, but none of the application files. I honestly have no clue how that happened. Reinstalling it worked though, so that's nice. Only took me about 10 hours of work to suss out all the missing dependencies and get it happy again. Then I promptly made a backup.

Oh, so you upgraded a multiple-year-old programming language with the latest version of its interpreter. Jeez, what did you have that day, and can I have some too?