subject:"RE\: Access alternatives \(WAS\: Security gurus out there\?\)"

The point in the whole discussion is not whether to use MySQL for your
projects, but WHEN to choose MySQL for your projects. Running a simple
website is pretty much a MySQL job, but when you are doing some serious
development whereas business processes heavily rely on database data,
data integrity and maintaining (even forcing) it is of such an
importance you have those features available.

 
Still people say, "hey that list is outdated". Sure it is, it has been
created in if I recall, about autumn of 2003 on a dutch IT forum as a
result of months of MySQL gibberish and a person called ACM had written
it out but still most of the points still affect current releases. Most
companies lack updates, not upgrading to newer versions, still
suggesting older versions are more mature, and thus the result is the
posted listing. Using InnoDB overcomes some of the MySQL behaviour but
still out of the box 

 
The whole point about MySQL is actually only one: The software doesn't
have capabilities of maintaining AND forcing constraints or
relationships. ;) 

 
Somebody asked what database to choose then, I would suggest looking at
PostgreSQL if you need a free solution.
Micha Schopman 
Software Engineer 
Modern Media, Databankweg 12 M, 3821 AL  Amersfoort 
Tel 033-4535377, Fax 033-4535388 
KvK Amersfoort 39081679, Rabo 39.48.05.380
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

> Strictly speaking you're dead on the money. However I wonder how 
> many of the apps in the lower-end realm we are talking about here 
> would need or even appreciate the difference unless someone benched 
> it, and then, would anyone notice? Again, I think when you want to 
> start optimizing to this degree I think you also want to be into a
> bigger/badder db platform, you'll have a dedicated dba, a design team,
> an application dev team etc.  It won't be a small/medium site
> developed in toto by one person and perhaps skinned by another.

I don't see what the scale of the project has to do with whether the project
should be done right. You're not talking about optimization, but rather best
practices and application partitioning.

> I wonder how many developers optimize everything into the db like that?

Unfortunately, not enough. Again, though, it's not a matter of optimization.
Data manipulation logic belongs in SQL. That's what SQL is for. Just like it
would usually be inappropriate to use SQL for presentation logic, it's
usually inappropriate to use CF for data manipulation logic.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Paul Hastings

> Strictly speaking you're dead on the money.  However I wonder how many
> of the apps in the lower-end realm we are talking about here would
> need or even appreciate the difference unless someone benched it, and
> then, would anyone notice?  Again, I think when you want to start

pretty much all of them i'd guess. db interactions are important, if your
app sucks at, your app sucks.

> an application dev team etc.  It won't be a small/medium site
> developed in toto by one person and perhaps skinned by another.

sure it would. if your app uses a db then you better make sure you're using
it properly, size doesn't really matter. if you take on a db as part of app
development then you should be using the db to it's fullest extent possible.

> I wonder how many developers optimize everything into the db like that?

no idea but that's not what i said. i try to get my money's worth out of a
db. it does certain things better than cf, etc. and it should be used for
those things.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Matt Robertson

Paul wrote:

> kinda of an expensive way to do things, certainly not getting your money's
> worth out of the db.

Strictly speaking you're dead on the money.  However I wonder how many
of the apps in the lower-end realm we are talking about here would
need or even appreciate the difference unless someone benched it, and
then, would anyone notice?  Again, I think when you want to start
optimizing to this degree I think you also want to be into a
bigger/badder db platform, you'll have a dedicated dba, a design team,
an application dev team etc.  It won't be a small/medium site
developed in toto by one person and perhaps skinned by another.

I wonder how many developers optimize everything into the db like that?

-- 
--Matt Robertson--
MSB Designs, Inc.
mysecretbase.com
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Paul Hastings

> You mean do the division in the sql?  Thats something I would never
> do, personally.  I  only like to use the db for a container and pretty

kinda of an expensive way to do things, certainly not getting your money's
worth out of the db.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Matt Robertson

Jochem wrote:
> After doing a simple division by 2.20371, your balance doesn't quite
> balance anymore.

You mean do the division in the sql?  Thats something I would never
do, personally.  I  only like to use the db for a container and pretty
much handle all logic outside of it; only storing derived results.  I
would make sure my field spec is capable whatever I fed it, and if
there was some issue like rounding currency properly I would handle
that in the application layer.

I know this isn't a universal preference, and if I recall correctly
doing this sort of thing in SQL is faster.  I found that long, long
ago my life in debugland was a lot easier if I kept my logic all in
code and nowhere near the db.  May be an outdated viewpoint.

-- 
--Matt Robertson--
MSB Designs, Inc.
mysecretbase.com
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

Matt Robertson wrote:
> 
> I think the original question has been forgotten here:  What is a good
> replacement for Access?  Well, we can all agree that "anything" is the
> correct answer :-).
> 
> Next, I submit if you are using Access you are probably not running
> mission-control for NASA's next space launch, or tracking currency
> fluctuations the World Bank.  So given the likely limited nature of
> the task at hand I think that opens the door quite a bit in terms of
> what you can and cannot accept in a database.
> 
> For the small to medium site, mySQL should work fine.

Not necessarily. Imagine a database with the financial 
administration of a small club. It gets maybe 10 queries per day. 
Probably less. Queries run in miliseconds. Small site by all means.
Next comes the euro. You need to convert your currency. After 
doing a simple division by 2.20371, your balance doesn't quite 
balance anymore.

Size is not related to the need for data integrity. Nobody cares 
if the big databases behind Slashdot loose the comments of a few 
trolls. The accountant cares if your 913782 byte database misses 
3 cents(!).

> And when your application makes you rich and famous step up to the
> plate and empty your wallet for MS SQL or Oracle, or hire Jochem to
> figure out how to get PostGres running on your Windows server :D

As much as I would like to charge big bucks for that, it is as 
easy as double clicking:
http://pgfoundry.org/download.php/59/postgresql-8.0-beta1-20040809.msi

(Please note this is a BETA installer.)

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Massimo, Tiziana e Federica

> or hire Jochem to figure out how to get PostGres running on your Windows
> server :D

Postgres 8.0 is coming, with Win32 Native Support

http://www.postgresql.org/news/216.html


Massimo Foti
http://www.massimocorner.com

 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Claude Schneegans

>>"In MySQL, databases correspond to directories within the data directory. Tables within a database correspond to at least one
file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of
the underlying operating system determines the case sensitivity of database and table names.

Well then it is THEIR problem, I mean the MySQL developpers.
Programers create tables, not files, and in SQL, tables and columns are NOT case sensitive.
When they use the acronym "SQL" in their name, they should make their best to stick to the standard.
(Ah ah! another one ;-)
Why MySQL does not simply creates all upper case files, like Oracle?

--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send any spam to this address: [EMAIL PROTECTED])
Thanks.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Matt Robertson

I use mySQL a lot myself, but I have to say I don't have too many
illusions about it.  Sitll that doesn't make it any less useful **in
context**

I think the original question has been forgotten here:  What is a good
replacement for Access?  Well, we can all agree that "anything" is the
correct answer :-).

Next, I submit if you are using Access you are probably not running
mission-control for NASA's next space launch, or tracking currency
fluctuations the World Bank.  So given the likely limited nature of
the task at hand I think that opens the door quite a bit in terms of
what you can and cannot accept in a database.

For the small to medium site, mySQL should work fine.  God knows it
certainly will be one hell of a lot faster, and handle concurrent use
far, far better than a poor, besotted Access .mdb.  ...assuming of
course that whoever built the app has their head screwed on straight
and isn't doing something looney.

And when your application makes you rich and famous step up to the
plate and empty your wallet for MS SQL or Oracle, or hire Jochem to
figure out how to get PostGres running on your Windows server :D

-- 
--Matt Robertson--
MSB Designs, Inc.
mysecretbase.com
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Peter Farrell

Thanks Dave for you comments. I need to rebut some I said - I incorrectly stated something and I forgot about a setting.

Micha: "Tablenames are treated case-sensitive on *nix systems, not on windows."

Check out:
http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html

"In MySQL, databases correspond to directories within the data directory. Tables within a database correspond to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default filesystem type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix."

And further on:
"Column names, index names, and column aliases are not case sensitive on any platform."

"How table and database names are stored on disk and used in MySQL is defined by the lower_case_table_names system variable, which you can set when starting mysqld. lower_case_table_names can take one of the following values:

0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. This is the default on Unix systems. Note that if you force this to 0 with --lower-case-table-names=0 on a case-insensitive filesystem and access MyISAM tablenames using different lettercases, this may lead to index corruption.
1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names as of MySQL 4.0.2, and to table aliases as of 4.1.1. This value is the default on Windows and Mac OS X systems.
2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. Note: This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1. Setting lower_case_table_names to 2 can be done as of MySQL 4.0.18."

Just wanted to make sure I was understood correctly.

.Peter
MaePub
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

Peter Farrell wrote:
> I agree with Scott that Micha's list seems to be out-dated or not explained well.

Well, I have posted the origins of Micha's list. It is from 
March/April 2003. The explanation is a bit coarse indeed if you 
can't read the original thread in Dutch.

[4. tablenames & case-sensitivy]
> I believe this not to be a MySQL specific problem.

In databases it pretty much is MySQL specific. Maybe SQLLite too.

[5. temporary copy when using DDL]
> What about creating a new table and dumping the "new" data in.

Won't do, while DDL isn't transactional in MySQL (which one could 
consider a number on its own), what you are suggesting isn't even 
atomic.

[6. temporary copy when adding index]
> Yea, but how many times are you adding a new index?

Often enough not to want it to fail.

Serious, even if MySQL handles the failure itself gracefully and 
performs its equivalent of a rollback, there is a short period of 
time that the disk is full. That means there is a risk that many 
other write operations will fail too, not only writes to other 
tables, but also to the internal catalogs (which is why I think 
non-transactional DDL is a point of its own).

> A lot of these I must say you can't get anything better than MySQL for free (maybe PostgreSQL).

At least PostgreSQL and Firebird. From a data integrity point of 
view I might even choose Access over MySQL (if that suits your 
definition of 'free').

I forwarded your points to the guy maintaining the original list.

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

> I've been following this thread closely, but silently.  I've 
> used MySQL rather exclusively in the past couple of years.  I 
> agree with Scott that Micha's list seems to be out-dated or 
> not explained well.

The biggest criticism that Micha mentioned isn't really enumerated in the
list - the unsuitability of MySQL in environments where data integrity is
more important than everything else. It's kind of worrisome to hear people
giving blanket recommendations to "use MySQL" instead of using, say, Access,
because data integrity is one of those things that people tend to take for
granted when using an RDBMS.

> 2. SELECT 'A' = 'a' gets you true.
> This would be true because MySQL does a non-case-sensitive 
> compare.  If you wanted to know of 'A' = 'a' then use BINARY. 
>  Now, this is just the way it's done in MySQL and if you 
> don't like that, then use a different product.

Many databases allow you to control collation.

> 6. Adding indices result in a similar temporary copy.
> Yea, but how many times are you adding a new index? It's not 
> like it does it on every SELECT statement.

It's pretty common to create new indexes after observing database
performance - you learn what fields are being used more than others, and you
apply indexes to those fields.

> 4. Tablenames are treated case-sensitive on *nix systems, not 
> on windows.
> I believe this not to be a MySQL specific problem.  Look at 
> using CFML pages on *nix vs Windows and you'll have the same 
> problem.  It's probably a file system thing.

It may be a filesystem thing, but tablenames are treated the same way in,
say, Oracle on Solaris as they are in Oracle on Windows. I think that's true
for most cross-platform RDBMSs.

> A lot of these I must say you can't get anything better than 
> MySQL for free (maybe PostgreSQL).

I would say that PostgreSQL is much better than MySQL for many use-cases.
MySQL may be much better than Postgres for others.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Peter Farrell

I've been following this thread closely, but silently. I've used MySQL rather exclusively in the past couple of years. I agree with Scott that Micha's list seems to be out-dated or not explained well.

I'll address this list - according to MySQL 4.0.2a-NT:

1. You can define a varchar/char field 'auto_increment'.
Like I would never do this in the first place, but I tried it both with varchar and char. MySQL produces this error:
#1063 - Incorrect column specifier for column '%name of your column%'

2. SELECT 'A' = 'a' gets you true.
This would be true because MySQL does a non-case-sensitive compare. If you wanted to know of 'A' = 'a' then use BINARY. Now, this is just the way it's done in MySQL and if you don't like that, then use a different product.

3. Int(10) is the same as int(1) eventhough the manual says differently.
I don't know where Micha is referencing in the manual. I tried a table with INT(1) and INT(10)...they seem to perform the same. Doc: "The display width 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 having a width exceeding that specified for the column."

4. Tablenames are treated case-sensitive on *nix systems, not on
windows.
I believe this not to be a MySQL specific problem. Look at using CFML pages on *nix vs Windows and you'll have the same problem. It's probably a file system thing.

5. Change a piece of a table definition and mysql creates a temporary
copy of the table, very nice if you have a 6GB table occupying a 10GB
tablespace... (yes, the change will fail)
Hmm...I would have to check this one out more. However, every DB has faults and other DBs may do this different. If you really have a 6GB table...should you be changing the structure at this point in the game? What about creating a new table and dumping the "new" data in.

6. Adding indices result in a similar temporary copy.
Yea, but how many times are you adding a new index? It's not like it does it on every SELECT statement.

7. What does zerofill do to a integer field? A database is meant to
store data, not to format it while storing.
Actually I find this to be a nice feature...if necessary. For instance you are using Auto_Increment INT field and you are using it as your order number. Maybe you want all of your order numbers to be a specific length.

8. When I define a char(32) (md5-strings anyone) field, I really don't
mean varchar(32) (MySQL automatically changes all char(X >4) to
varchar(X)).

I quote Jochem:
"If any column in a table has a variable length, the entire row
becomes variable-length as a result. Therefore, if a table
contains any variable-length columns (VARCHAR, TEXT, or BLOB),
all CHAR columns longer than three characters are changed to
VARCHAR columns.
http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html"

That's all I have time with for now. A lot of these I must say you can't get anything better than MySQL for free (maybe PostgreSQL). I like MySQL and every program has it weriod things - I should mention CF here because you all know the work arounds that we end up having to use.

.Peter
MaePub
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

Mark A Kruger wrote:
> 
> The firestorm has started and you are going to be challenged on most of
> these I would wager. Still, your list shows you are no novice.

The list is not Micha's, it is maintained by ACM and many people 
contributed in a public process: 
http://gathering.tweakers.net/forum/list_messages/733783/

> I liked it
> well enough to post it on my blog.  When the point by point rebuttals come in
> I'll post them too - for the sake of fair play .

Just make sure they contain credible claims (CLI logs) :)

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

So you are admitting that the points used in your argument for not
using MySQL are outdated?

That's like arguing that you shouldn't use Windows Xp because of flaws
found in Wndows '95.   IF you are going to use data to support your
argument, make sure its accurate and up to date.

- Original Message -
From: Micha Schopman <[EMAIL PROTECTED]>
Date: Thu, 2 Sep 2004 17:29:07 +0200
Subject: RE: Access alternatives (WAS: Security gurus out there?)
To: CF-Talk <[EMAIL PROTECTED]>

This listing contains some issues not affecting newer versions, the list
 has been created some time ago and is indeed outdated on a few points
 but for the overall ... it is MySQL in its current state. But the point
 was more or less, MySQL does not contain integrity functionality, and
 even if you use InnoDB which adds some of it, it is still possible to
 workaround the constraints very easily. MySQL was never designed for
 extensive use in company environments, it started as a personal database
 system. Somewhat like MsAccess level at those days.

Micha Schopman 
 Software Engineer 
 Modern Media, Databankweg 12 M, 3821 AL  Amersfoort 
 Tel 033-4535377, Fax 033-4535388 
 KvK Amersfoort 39081679, Rabo 39.48.05.380
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Damien McKenna

On Sep 2, 2004, at 11:46 AM, Jochem van Dieten wrote:
> - SAPdb http://www.sapdb.org/

The MySQL folks now own this.
-- 
Damien McKenna - Web Developer - [EMAIL PROTECTED]
The Limu Company - http://www.thelimucompany.com/ - 407-804-1014
"Nothing endures but change." - Heraclitus
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

Andrew Dixon wrote:
> How about Firdbird? I've never used it and not with CF, but I have
> heard good things about it. Anyone know if it can be used with CF? or
> if it is any good.

Yes. Yes.

There are quite a few Free Open Source relational databases to 
choose from:
- Cloudscape    http://www-306.ibm.com/software/data/cloudscape/
- FireBird  http://firebird.sourceforge.net/
- Ingreshttp://www3.ca.com/Solutions/Product.asp?ID=1013
- PostgreSQL    http://www.postgresql.org/
- SAPdb http://www.sapdb.org/

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

Andrew Dixon wrote:

> And it does automatically change a char(32) to varchar(32), tested on 4.0.20.

"If any column in a table has a variable length, the entire row 
becomes variable-length as a result. Therefore, if a table 
contains any variable-length columns (VARCHAR, TEXT, or BLOB), 
all CHAR columns longer than three characters are changed to 
VARCHAR columns."
http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Mark A Kruger

Micha,

The firestorm has started and you are going to be challenged on most of
these I would wager. Still, your list shows you are no novice.  I liked it
well enough to post it on my blog. When the point by point rebuttals come in
I'll post them too - for the sake of fair play .

-mark

  -Original Message-
  From: Micha Schopman [mailto:[EMAIL PROTECTED]
  Sent: Thursday, September 02, 2004 9:02 AM
  To: CF-Talk
  Subject: RE: Access alternatives (WAS: Security gurus out there?)

  Or the following list, the missing referential integrity still remains
  as a MySQL nogo. MySQL is perfect for your average website, but
  definitely not for company critical data.
  Weird behaviour
  1. You can define a varchar/char field 'auto_increment'.
  2. SELECT 'A' = 'a' gets you true.
  3. Int(10) is the same as int(1) eventhough the manual says differently.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

This listing contains some issues not affecting newer versions, the list
has been created some time ago and is indeed outdated on a few points
but for the overall ... it is MySQL in its current state. But the point
was more or less, MySQL does not contain integrity functionality, and
even if you use InnoDB which adds some of it, it is still possible to
workaround the constraints very easily. MySQL was never designed for
extensive use in company environments, it started as a personal database
system. Somewhat like MsAccess level at those days.
Micha Schopman 
Software Engineer 
Modern Media, Databankweg 12 M, 3821 AL  Amersfoort 
Tel 033-4535377, Fax 033-4535388 
KvK Amersfoort 39081679, Rabo 39.48.05.380
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Marlon Moyer

Regarding number 5, do other Db's do this differently.  I know MSSql will
create a temp table, copy all rows, drop original table, create new table
with new defs, and then copy all data back.  So actually MSSql uses more
space to make table modifications.  This is using Enterprise manager.  You
could do it more efficiently with sql statements.


> -Original Message-
> From: Scott Stroz [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 02, 2004 9:57 AM
> To: CF-Talk
> Subject: Re: Access alternatives (WAS: Security gurus out there?)
> 
> Some of Micha's points are inaccurate...in some downtime, I plan on
> testing each issue he presents.
> 
> I can tell you that the first point he makes is inaccurate.  In
> version 4.0.17 you CANNOT 'define a varchar/char field
> 'auto_increment'. '
> 
> That's about as far as I have gotten...I just started ;-)...but so far
> he is 0 for 1.
> 
> 
> - Original Message -
> From: Mark A Kruger <[EMAIL PROTECTED]>
> Date: Thu, 2 Sep 2004 09:49:18 -0500
> Subject: RE: Access alternatives (WAS: Security gurus out there?)
> To: CF-Talk <[EMAIL PROTECTED]>
> 
> Micha,
> 
>  Ok ok... so it has a few problems (lol).  I think you should apply to
write
>  the next "NOT MySQL for dummies" book.
> 
>  Seriously - this is a pretty comprehensive list and great fodder for
those
>  of us who live off of upselling. Thanks!
> 
>  Mark A. Kruger, CFG, MSCE
>  www.cfwebtools.com
>  www.necfug.com
>  http://blog.mxconsulting.com
> 
> 
>    -Original Message-
>    From: Micha Schopman [mailto:[EMAIL PROTECTED]
>    Sent: Thursday, September 02, 2004 9:02 AM
>    To: CF-Talk
>    Subject: RE: Access alternatives (WAS: Security gurus out there?)
> 
>    Or the following list, the missing referential integrity still remains
>    as a MySQL nogo. MySQL is perfect for your average website, but
>    definitely not for company critical data.
>    Weird behaviour
>    1. You can define a varchar/char field 'auto_increment'.
>    2. SELECT 'A' = 'a' gets you true.
>    3. Int(10) is the same as int(1) eventhough the manual says
differently.
> 
>    4. Tablenames are treated case-sensitive on *n?x systems, not on
>    windows.
>    5. Change a piece of a table definition and mysql creates a temporary
>    copy of the table, very nice if you have a 6GB table occupying a 10GB
>    tablespace... (yes, the change will fail)
>    6. Adding indices result in a similar temporary copy.
>    7. What does zerofill do to a integer field? A database is meant to
>    store data, not to format it while storing.
>    8. When I define a char(32) (md5-strings anyone) field, I really don't
>    mean varchar(32) (MySQL automatically changes all char(X >4) to
>    varchar(X)).
>    9. This is correct according to mysql: SELECT a, b, count(c) FROM d
>    GROUP BY a; what will MySQL do with the b?
>    10. If you update a record and set it to the same value, mysql'll
define
>    that as unaffected. Even if it does change a timestamp
> field.
> 
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Andrew Dixon

And it does automatically change a char(32) to varchar(32), tested on 4.0.20.

that makes it 0 for 2...

Andrew.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

Some of Micha's points are inaccurate...in some downtime, I plan on
testing each issue he presents.

I can tell you that the first point he makes is inaccurate.  In
version 4.0.17 you CANNOT 'define a varchar/char field
'auto_increment'. '

That's about as far as I have gotten...I just started ;-)...but so far
he is 0 for 1.

- Original Message -
From: Mark A Kruger <[EMAIL PROTECTED]>
Date: Thu, 2 Sep 2004 09:49:18 -0500
Subject: RE: Access alternatives (WAS: Security gurus out there?)
To: CF-Talk <[EMAIL PROTECTED]>

Micha,

 Ok ok... so it has a few problems (lol).  I think you should apply to write
 the next "NOT MySQL for dummies" book.

 Seriously - this is a pretty comprehensive list and great fodder for those
 of us who live off of upselling. Thanks!

 Mark A. Kruger, CFG, MSCE
 www.cfwebtools.com
 www.necfug.com
 http://blog.mxconsulting.com

   -Original Message-
   From: Micha Schopman [mailto:[EMAIL PROTECTED]
   Sent: Thursday, September 02, 2004 9:02 AM
   To: CF-Talk
   Subject: RE: Access alternatives (WAS: Security gurus out there?)

 
   Or the following list, the missing referential integrity still remains
   as a MySQL nogo. MySQL is perfect for your average website, but
   definitely not for company critical data.
   Weird behaviour
   1. You can define a varchar/char field 'auto_increment'.
   2. SELECT 'A' = 'a' gets you true.
   3. Int(10) is the same as int(1) eventhough the manual says differently.

 
   4. Tablenames are treated case-sensitive on *n?x systems, not on
   windows.
   5. Change a piece of a table definition and mysql creates a temporary
   copy of the table, very nice if you have a 6GB table occupying a 10GB
   tablespace... (yes, the change will fail)
   6. Adding indices result in a similar temporary copy.
   7. What does zerofill do to a integer field? A database is meant to
   store data, not to format it while storing.
   8. When I define a char(32) (md5-strings anyone) field, I really don't
   mean varchar(32) (MySQL automatically changes all char(X >4) to
   varchar(X)).
   9. This is correct according to mysql: SELECT a, b, count(c) FROM d
   GROUP BY a; what will MySQL do with the b?
   10. If you update a record and set it to the same value, mysql'll define
   that as unaffected. Even if it does change a timestamp
field.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Mark A Kruger

Micha,

Ok ok... so it has a few problems (lol).  I think you should apply to write
the next "NOT MySQL for dummies" book.

Seriously - this is a pretty comprehensive list and great fodder for those
of us who live off of upselling. Thanks!

Mark A. Kruger, CFG, MSCE
www.cfwebtools.com
www.necfug.com
http://blog.mxconsulting.com
  -Original Message-
  From: Micha Schopman [mailto:[EMAIL PROTECTED]
  Sent: Thursday, September 02, 2004 9:02 AM
  To: CF-Talk
  Subject: RE: Access alternatives (WAS: Security gurus out there?)

  Or the following list, the missing referential integrity still remains
  as a MySQL nogo. MySQL is perfect for your average website, but
  definitely not for company critical data.
  Weird behaviour
  1. You can define a varchar/char field 'auto_increment'.
  2. SELECT 'A' = 'a' gets you true.
  3. Int(10) is the same as int(1) eventhough the manual says differently.

  4. Tablenames are treated case-sensitive on *n?x systems, not on
  windows.
  5. Change a piece of a table definition and mysql creates a temporary
  copy of the table, very nice if you have a 6GB table occupying a 10GB
  tablespace... (yes, the change will fail)
  6. Adding indices result in a similar temporary copy.
  7. What does zerofill do to a integer field? A database is meant to
  store data, not to format it while storing.
  8. When I define a char(32) (md5-strings anyone) field, I really don't
  mean varchar(32) (MySQL automatically changes all char(X >4) to
  varchar(X)).
  9. This is correct according to mysql: SELECT a, b, count(c) FROM d
  GROUP BY a; what will MySQL do with the b?
  10. If you update a record and set it to the same value, mysql'll define
  that as unaffected. Even if it does change a timestamp field.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Andrew Dixon

How about Firdbird? I've never used it and not with CF, but I have
heard good things about it. Anyone know if it can be used with CF? or
if it is any good.

http://firebird.sourceforge.net/

Andrew.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

Or the following list, the missing referential integrity still remains
as a MySQL nogo. MySQL is perfect for your average website, but
definitely not for company critical data.
Weird behaviour 
1. You can define a varchar/char field 'auto_increment'. 
2. SELECT 'A' = 'a' gets you true. 
3. Int(10) is the same as int(1) eventhough the manual says differently.

4. Tablenames are treated case-sensitive on *n?x systems, not on
windows. 
5. Change a piece of a table definition and mysql creates a temporary
copy of the table, very nice if you have a 6GB table occupying a 10GB
tablespace... (yes, the change will fail) 
6. Adding indices result in a similar temporary copy. 
7. What does zerofill do to a integer field? A database is meant to
store data, not to format it while storing. 
8. When I define a char(32) (md5-strings anyone) field, I really don't
mean varchar(32) (MySQL automatically changes all char(X >4) to
varchar(X)). 
9. This is correct according to mysql: SELECT a, b, count(c) FROM d
GROUP BY a; what will MySQL do with the b? 
10. If you update a record and set it to the same value, mysql'll define
that as unaffected. Even if it does change a timestamp field.

Bugs
1. Insert a NULL value in a NOT NULL field and the query gets executed.
In short, you can't force NOT NULL... 
2. Under high loads MySQL will restart itself occasionally. When a child
doesn't die fast enough "socket already in use"-error occurs. 
3. Replication doesn't work that well. 
4. MySQL will always try to insert a date into a date field, even if you
supply an empty or bogus string (it'll insert -00-00 then). 
5. When underflowing or overflowing a integer field, mysql will insert
the largest or smallest possible value instead. 
6. Long texts are chopped to the correct size, not denied. 
7. MySQL will also insert 0 into a numeric field if you supply an empty
or bogus string. 
8. Close a connection and a long query still runs on... The only way to
kill it is via the (mysql)admin 
9. Connections with a timeout? Very nice when you need to do a lot of
calculations on your data or a query lasts very long. "Lost connection
to MySQL server during query" 
10. You can insert the same value twice in a ENUM-field 
11. You can't use a Temporary table more than once in a query.
http://www.mysql.com/doc/en/Temporary_table_problems.html

Missing functionality
1. No subselects in 3.x versions and a partial implementation in 4.0.x. 
2. No triggers. 
3. No foreign keys with default tableformat. 
4. No (free) decent hotbackup tool (in the distribution). 
5. No transactionsupport in the default tableformat. 
6. No procedural language(s). 
7. No stored procedures/functions. 
8. No ipaddress or geometrical datatypes. 
9. JDBC driver still buggy. 
10. No specific character encoding and multibyte charsupport. 
11. MySQL has trouble optimising complex queries, SELECT ... FROM ...
WHERE something BETWEEN ... can be considerd complex in some cases... 
12. It can't use multiple indices for filtering records from one table. 
13. It can't do ORDER BY ... DESC that well, it'll do a filesort. Even
if there are only two results to sort. 
14. There is no locale and timezone support. 
15. No rowlevel tablelocking, except in innodb. 
16. No views. 
17. Unions are not available in the more mainstream 3.x releases, only
in the brand new 4.x releases. 
18. The explain command hardly explains anything. 
19. No real boolean/bit/bitarray type. 
20. No CHECK-contraints.

 
Micha Schopman 
Software Engineer 
Modern Media, Databankweg 12 M, 3821 AL  Amersfoort 
Tel 033-4535377, Fax 033-4535388 
KvK Amersfoort 39081679, Rabo 39.48.05.380
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Andy Allan

Sweet. That's what I (and probably others) was after rather than just a "Don't
use MySQL".

Don't actually use MySQL myself but this sort of thing is good to know.

Andy

Quoting Jochem van Dieten <[EMAIL PROTECTED]>:

> Andy Allan wrote:
> > I'll second that motion for an explanation.
> 
> I'll second that 'motion of distrust':
> http://sql-info.de/mysql/gotchas.html
> 
> Jochem
> 
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

> What's a better choice? SQL Server?  

SQL Server is certainly a better choice for a multi-user database. Oracle,
Sybase and other database servers would also be better than Access.
Unfortunately, they also tend to be quite expensive in many cases.
Fortunately, there are cheap and/or free alternatives. SQL Server Express (a
free version of SQL Server) is currently in beta. If you're in a Unix
environment, you might also look at Postgres.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Dick Applebaum

Have a look at Cloudscape

http://www-306.ibm.com/software/data/cloudscape/

It may be overkill but they have an option to encrypt the entire 
database  - even the schema is not visible.

Dick

On Sep 2, 2004, at 5:30 AM, Chris Kavanagh wrote:

> >  If you're storing lots of sensitive information in a web 
> application,
>  > DON'T RUN IT ON ACCESS!!! Access is a desktop database, threads
>  > frequently crop up on here with lots of info on why it's really NOT
>  > suited to a server environment...
>
>  What's a better choice?  SQL Server?  Does anyone know of an "idiot's
>  guide" to it I could check out somewhere?
>
>  Ciao!
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

Andy Allan wrote:
> I'll second that motion for an explanation.

I'll second that 'motion of distrust':
http://sql-info.de/mysql/gotchas.html

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

> > MySQL is out of the question for sensitive information. MySQL 
> > is missing to much functionality to maintain data integrity 
> > on db level.
>
> Really. Like what?

I don't follow MySQL development too closely, so this may have been
addressed by now, but the versions I looked at didn't ensure declarative
referential integrity in many cases. I suspect this is what Micha is
referring to. Jochem would be the go-to guy for a full explanation, I'm
sure!

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Andy Allan

I'll second that motion for an explanation.

Quoting Scott Stroz <[EMAIL PROTECTED]>:

> Micha,
> 
> Please explain.
> 
> On Thu, 2 Sep 2004 14:43:16 +0200, Micha Schopman
> <[EMAIL PROTECTED]> wrote:
> > MySQL is out of the question for sensitive information. MySQL is missing
> >  to much functionality to maintain data integrity on db level.
> >  Micha Schopman 
> >  Software Engineer 
> >  Modern Media, Databankweg 12 M, 3821 AL  Amersfoort 
> >  Tel 033-4535377, Fax 033-4535388 
> >  KvK Amersfoort 39081679, Rabo
> 39.48.05.380
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

Micha,

Please explain.

On Thu, 2 Sep 2004 14:43:16 +0200, Micha Schopman
<[EMAIL PROTECTED]> wrote:
> MySQL is out of the question for sensitive information. MySQL is missing
>  to much functionality to maintain data integrity on db level.
>  Micha Schopman 
>  Software Engineer 
>  Modern Media, Databankweg 12 M, 3821 AL  Amersfoort 
>  Tel 033-4535377, Fax 033-4535388 
>  KvK Amersfoort 39081679, Rabo 39.48.05.380
> 
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Andrew Dixon

Really. Like what?

Andrew.

- Original Message -
From: Micha Schopman <[EMAIL PROTECTED]>
Date: Thu, 2 Sep 2004 14:43:16 +0200
Subject: RE: Access alternatives (WAS: Security gurus out there?)
To: CF-Talk <[EMAIL PROTECTED]>

MySQL is out of the question for sensitive information. MySQL is missing
 to much functionality to maintain data integrity on db level.
 Micha Schopman 
 Software Engineer 
 Modern Media, Databankweg 12 M, 3821 AL  Amersfoort 
 Tel 033-4535377, Fax 033-4535388 
 KvK Amersfoort 39081679, Rabo 39.48.05.380
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Access alternatives (WAS: Security gurus out there?)

MySQL is out of the question for sensitive information. MySQL is missing
to much functionality to maintain data integrity on db level.
Micha Schopman 
Software Engineer 
Modern Media, Databankweg 12 M, 3821 AL  Amersfoort 
Tel 033-4535377, Fax 033-4535388 
KvK Amersfoort 39081679, Rabo 39.48.05.380
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)

2004-09-02 Thread Massimo Foti

> What's a better choice?  SQL Server?  Does anyone know of an "idiot's
> guide" to it I could check out somewhere?
>
A good entry-level book on SQL server:

Step by Step Microsoft SQL Server 2000 Programming
by Rebecca M. Riordan
Paperback - 685 pages (November 2000)
Microsoft Press
ISBN: 0735611424

The book is very newbie friendly, but you may easily outgrow it and feel the
need for something more


Massimo Foti
http://www.massimocorner.com

 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Access alternatives (WAS: Security gurus out there?)