[Resolved] Really Dumb Questions?

kenhess

I am sure this has been answered before but I am just curious since I don't know how to ask it via google.

If you setup a table with autoincrement for your record ID, and you set it to INT(2), does this mean you can only put in 99 records into that table?

What happens at the 100th record if none are ever removed?

What if some records are removed,...can new ones go in their place?

And if there really is this limitation, can you go back later and alter the table to provide for larger numbers INT(4)?

Sxooter

Let me ask you a more important question before answering yours. Why would you want to pick a smaller variable to hold your autoincrement column?

Now, the answer is yes, this will limit you to 99 as your max, just as tinyint limits you to 127 (or unsigned to 255 or so).

My recommendation is to use an int (plain old int, which is usually int4 or a 32 bit int, which can hold +/- 2billion or so.) so you don't have to worry. Autoincrement fields shouldn't get used over if possible, as they provide a primary key for most tables they are in, or at least a reliable, unchanging foreign key for other tables that are subordinate to them.

csn

int(2) would allow 65535 values (which are never reused automatically). Just use int(4) - 4294967295 values. If you expect to hit that limit, then use int(8). After that? Maybe varchar(32).

Don't worry about saving space this way (HD space is quite cheap). Besides, joins on different integer sizes degrades performance.

Sxooter

csn, I think you're confusing int(2) with int2

int2 means a two byte int, while int(2) would mean an int with 2 places of precision.

Note that the SQL spec does not allow precision for ints, so int(x) is technically illegal.

I just tested in MySQL, and as per it's usuall extraordinary wonderfully well done error checking, it lets me DEFINE an int with a precision of (2) but doesn't bother to enforce anything. I.e. silently doing the wrong thing.

God I hate that database...

Sxooter

I also tested it in an auto_increment column and it also allows me to exceed two characters.

Why does MySQL let me set a precision value and then not bother to enforce it?

csn

LOL, how do you really feel about MySQL? :p

In MySQL, int(x) is the "display width" and has something to do with how the column is formatted in results. I've never used it. I too once thought it was for precision 😉.

By int(2), int(4), int(8) I meant (in mysql) smallint, int, and bigint.

As an extension to the SQL-92 standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT as listed in the tables above. Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional width specification is used to left-pad the display of values whose width is less than the width specified for the column, but does not constrain the range of values that can be stored in the column, nor the number of digits that will be displayed for values whose width exceeds that specified for the column. When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeroes. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, as in these cases MySQL trusts that the data did fit into the original column width.

http://www.mysql.com/doc/en/Numeric_types.html

Sxooter

Yep, one more thing MySQL did their "own way" that breaks spec.

Everytime I use that database I feel dirty 🙂

kenhess

OY! Ask a simple question and look what happens...a debate.

I figured it out from one of the posts:

INT(1) = 256 entries
INT(2) = 256² entries = 65536
INT(3) = 256³ entries = 16 million
INT(4) = 256⁴ entries = 4 billion

So I get it now. I thought that INT(2) would only give me 1-99 for 2 digits.

Thanks for the info and I love MySQL, by the way. It does what I need it to and the price is right. I have to work with SQL Server 2000 and believe me, MySQL is far easier to deal with.

Everyone has their preferences.

Sxooter

Originally posted by kenhess
OY! Ask a simple question and look what happens...a debate.

We loves us some debate around here. That's a good thing right? 🙂

Thanks for the info and I love MySQL, by the way. It does what I need it to and the price is right. I have to work with SQL Server 2000 and believe me, MySQL is far easier to deal with.

I use free databases for the same reason (read: no budget for intranet applications) and agree completely on the SQL Server 2000 thing.

I just find myself leaning more and more towards using Postgresql over MySQL because of the types of data I have to mess with and the need for moderately complex SQL for certain reports. I.e. I can live with it being slower because I don't need screaming performance for a single simple page, but I do need the increased functionality.

Thanks for the tip on how the int() thing works. The thing that bothers me most about MySQL is how knowledge gained while learning it applies to no other database around, because it's all so non-standard. then again, sometimes standards ain't all they're cracked up to be.

later...

csn

hehe :p

What specifically don't you guys like about SQL Server 2000? I've done some basic work on it and thought it was okay, with a pretty nice GUI. It seemed to have a lot for data warehousing as well: cubes, DTS, etc.

Sxooter - IYNSHO do you think there's flaky stuff in the SQL specs?

kenhess

Here are my answers:

SQL Server 2k is fine, but it is not cross-platform. I can develop my MySQL and PHP on a Linux Server and drag and drop the php and MySQL DBs onto a Windows Server running PHP and MySQL and it is immediately running and ready for use and vice versa, if I did my dev on Windows with PHP and MySQL and dropped it onto a Solaris Server with PHP and MySQL.

As for PostgreSQL, I don't know. I worked with it some time back and it is fine. I don't now how cross-platform it is as described above. I know it seems larger and slower than MySQL.

MySQL has non-standard things but those, to me, are empowering, not limiting and with the upcoming release of version 5, I think there will be no question as to which DB is superior. One can always look at the comparison chart on www.mysql.com (the crash me app) and choose the DBs you want to compare and take a side-by-side look at the features and shortcomings of each.
I looked at them all and decided that MySQL is the one I want to use for my development.
The cross-platform issue was my biggest sell - and the fact that there is far more documentation and info about it than other OS DBs, but all-in-all, it is just a matter of what I feel comfortable with and confident about.

Sybase, Oracle, SQL Server, PostgreSQL, MySQL, and so on are all fine RDBMS systems, but it boils down to what you can afford and what you want/need to use for your situation.

No one really should criticize someone for what they use because they may not have a choice. I have a client that went with Sybase Adaptive Server, which is fine, but it certainly limits any support, especially cheap support that they may need.

Mmmph. That's all I gotta say about that...mmmmmhmmmmm.

csn

Is there a URL for MySQL 5? I'm just curious what all is on the menu for that. Looking at this page:

http://www.mysql.com/products/mysql/index.html

it looks like:

views (5.1)
stored procedures and triggers
Multi-master replication

PostgreSQL has the first two. I'm not sure if the replication project can do multi-master rep.

I've used MySQL for several years and once thought it was a great DB too (I'd used Access and SQL Server before that). But then I started paying attention to some of the (pretty sparse compared to the MySQL bandwagon) anti-MySQL, pro-PostgreSQL stuff (like the posts of Scott and vincent), and began realizing some of the short-comings of MySQL, and how PG features like views, stored procs, triggers, subqueries, foreign keys, etc. sounded really cool and would make my programming life a lot easier. It's been a pretty big learning curve, but I'm definitely much happier using PostgreSQL. That community also really seems to have a full head of steam, and I think they've put a lot of effort into fixing problems where MySQL traditionally had an advantage (like speed and data integrity). I still use MySQL for some simple and legacy stuff, and they seem to be getting on track with future versions (which I don't expect any time soon), but I definitely prefer PostgreSQL if at all possible 😉.

Sxooter

Originally posted by csn
hehe :p

What specifically don't you guys like about SQL Server 2000? I've done some basic work on it and thought it was okay, with a pretty nice GUI. It seemed to have a lot for data warehousing as well: cubes, DTS, etc.

Sxooter - IYNSHO do you think there's flaky stuff in the SQL specs?

The last flavor of SQL server I played with was 6 or so. The only thing I didn't like about it was that it seemed to crash under heavy load, an unforfiveable sin for a database. We have a giant quad Xeon machine where I work that still crashes every so often, but it's apparently gotten better, what with 4 2+GHz processors to handle the load and all. Note that I'm a unix developer, so the pretty GUI in that system is unavailable to me directly, and I just use php to create schema and what not, so the GUI interface doesn't mean much to me.

I'm not sure there's truly flakey stuff in the specs really. The biggest complaint I have is that it took until the SQL3 spec for things like enumerated types and sequences to get a specification, long after many dbms engines already had their own definition of those thing. I'd say the only flaky thing really is all the "implementation dependent" stuff like precision of an int and such like that. I.e. too many open ended things.

Sxooter

Originally posted by kenhess
Here are my answers:

MySQL has non-standard things but those, to me, are empowering, not limiting

But MySQL also has what I consider to be fatal flaws for a database used for anything other than simple text based storage. For instance, if you try to insert numbers that are out of range, instead of giving an error, it simply inserts the max/min value for that type. Silently.

In fact, there are dozens of ways MySQL will silently fail while storing the wrong thing, and that's simply unacceptable in things like accounting, payroll, sales forecasts, marketing analyses, and so on. Put in a date wrong? Instead of kicking it out, it silently stores 0000-00-00 for you. Imagine looking at 6 months of invoices to find them all dated 0000-00-00! Not a good thing.

For autoincrement fields it basically does the equivalent of

begin;
select max(id)+1 from table;
insert into...
commit;

in a relational database, this is a huge, giant, unforgivable choke point for massive parallel updates. Most databases are designed to handle massive parallel inserts, MySQL is designed to handle massive parallel selects.

Neither is inherently better, just better suited to certain things (i.e. financial transactions versus content management.)

and with the upcoming release of version 5, I think there will be no question as to which DB is superior. One can always look at the comparison chart on www.mysql.com (the crash me app) and choose the DBs you want to compare and take a side-by-side look at the features and shortcomings of each.

1: How can MySQL be superior based on where it's going? You've not got the code to test, so it's hard to say. when the code ships as beta, you can test it and then make the evaluation that it is better. Til then it's nothing more than marketing. And there's more to a database than pure speed. Correctness first, speed second.

2: Crashme was written by the MySQL crew, to make their database look good. For an example of this, look at how big they list the maximum query for postgresql as being 16 Megs. Why 16 Megs? Because that's the buffer size they allocate in their test, and when they hit the end, they assume Postgresql must have that limit. In fact, it's sloppy programming to automatically assume that when you hit the limit it was the client program that caused you to hit it. Postgresql has NO LIMIT on query length.

3: MySQL is still a great tool, but that crashme program doesn't tell you the whole story. The silent failure modes of MySQL make it a dangerous tool if you aren't paying close attention to what's happening in your application. You can easily wind up with thousands of man-hours of data entry being screwed up because the database failed silently and allowed bad data in.

4: Lack of constraints (other than unique) in MySQL make it particularly poorly suited to data that is to be used for complex projections, where incorrect data could sink your company.

As for the issues of cross platform, I will freely admit that Postgresql's performance under windows is, at best, mediocre, and it's not a preferred platform. It's a particularly poor choice for content management where speed of delivery is paramount, and data integrity is seconday.

But for what I use a database for, I wouldn't use Windows anyway. So for me, not running on Windows (in production) is no great loss when choosing Postgresql.

kenhess

Ok, well you sound like the Oracle (pardon the pun) on all issues related to Databases, so I submit.

PostgreSQL is the ultimate OS database and I will immediately convert to it. You have convinced me.

Who cares about performance anyway,...correctness is the real key...although that could be taken care of in the code...but who wants to be a competent programmer when you have the awesome power of PostgreSQL behind you.

Hell, I don't know what I was ever thinking to use MySQL.

Hey, MySQL AB,...you suck ass! You guys should quit farting with MySQL and convert to PostgreSQL, but then again, they probably use PostgreSQL anyway, and are just stealing the code for that crappy shit MySQL.

kenhess <PostgreSQL Convert>

Sxooter

Hey, no reason to be a dick about it. My review is actually pretty well balanced. Read it again, seriously. I use MySQL for some things, and made it quite clear it was the preferred database for certain things. It just bugs me when people who know little to nothing of database theory try to pawn it off as the equivalent of Postgresql, Oracle, or DB2, which is isn't.

Heck, that's it's strong point, it's very good at what those databases aren't so good at.

There are plenty of people implementing content management with Oracle and Java and wondering why their corporate intranet needs a 16 way Ultra sparc just to bring up the menus in the lunch room who would be better served by MySQL.

Maybe next time you can read the actual factual entire post before sticking your foot in your mouth.

csn

Originally posted by Sxooter
The last flavor of SQL server I played with was 6 or so. The only thing I didn't like about it was that it seemed to crash under heavy load, an unforfiveable sin for a database. We have a giant quad Xeon machine where I work that still crashes every so often, but it's apparently gotten better, what with 4 2+GHz processors to handle the load and all. Note that I'm a unix developer, so the pretty GUI in that system is unavailable to me directly, and I just use php to create schema and what not, so the GUI interface doesn't mean much to me.

Oh, I agree. By "pretty nice", I meant feature-filled and powerful (at least that was my impression). But of course you'll need to have it (the GUI app), and Windows, loaded - using additional memory. The GUI isn't a big deal to me either - PG has plenty of interfaces: psql, phppgadmin, pgadmin, EMS PG Manager.

Sxooter

Originally posted by kenhess

Who cares about performance anyway,...correctness is the real key...although that could be taken care of in the code...but who wants to be a competent programmer when you have the awesome power of PostgreSQL behind you.

Here's where you prove you have no clue about databases in heavy parellel update environments.

Handling data integrity in your code instead of the database is the quickest way to get your data corrupted. There's no way you can do all your data integrity checking in code as fast as Postgresql or Oracle can IN THE DATABASE because if you try to do it in code, it's like trying to build a house on a sandbar as it washes out from underneath you. About the time you figure out if there's a row with the same id as yours, someone else has inserted it underneath you. Plus, by the time you've coded around MySQL's shortcomings, Postgresql and Oracle just left you in the dirt, because a single transaction took care of all the issues for you, with the only client side code being "check if the insert transaction threw an error, and retry in 5 seconds if so."

Nevermind, you've got a closed mind on the subject. I honestly wasn't trying to be a dick about all this, but your response really pissed me off.

yes, I do know more about databases than you, and yes, someday you will realize it, when you stare in horror at the wrong data your beloved MySQL stored because you didn't understand the issues involved in parallel updates.

kenhess

LOL. And just where do you work on your grandiose scale?

I have searched your posts and you don't really ever help anyone...so not sure why you post except to tout your false knowledge...but that is none of my concern.

You were terribly wrong in your post about INT(X), so the rest is really moot...so no one really cares what you think, least of all me.

This is the last post from me since I did mark this as Resolved.

Sxooter

Originally posted by kenhess
LOL. And just where do you work on your grandiose scale?

I have searched your posts and you don't really ever help anyone...so not sure why you post except to tout your false knowledge...but that is none of my concern.

You were terribly wrong in your post about INT(X), so the rest is really moot...so no one really cares what you think, least of all me.

This is the last post from me since I did mark this as Resolved.

Yes, when logic fails, attack the person. Not a single rebut to a single point.

Where I work is the URL in my profile. Small multi million dollar information company that's ~50 years old.

You'll notice that in this VERY THREAD someone mentioned I had helped them. huh. how many answers have YOU given here? I'm willing to bet none.

Do a search for Sxooter and the word thanks and you'll see what I mean.

By the way, the standard definition of the number in a paren after a type declaration is for it's precision. Given the fact that YET AGAIN MySQL has varied from the standard way of doing things it's not surprising I didn't know about that variation.

Did you in fact go back and read my evaluation? I bet not.

Please, leave forever if you like. You won't be missed here.

For examples where I've helped people in the last TWO DAYS look at these posts:

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10253935
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10253826
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10253745
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10253668

Most of the messages have the word "thanks" in their response, a word you probably aren't familiar with using.

But I expect your (in)ability to read and comprehend might not allow you to notice such things.