What is the biggest flaw of <your favorite database>?

Question

We all have our favorite database. If you take an objective look at your chosen database, what disadvantages does it have and what can be improved?

Rules:

• One answer for one flaw:
• a brief description of the restriction followed by

• a more detailed description, an explanation of how this can be done better, or an example of another technology that does not have the same limitations.

• Do not use any database that you have not used widely. It's easy to take pictures with other technologies, but we want to learn how to shape your experience, not your prejudice.

Answer 1

Oracle databases are pretty expensive

Oracle does everything it does, but licensing costs are terrifying. This has been improved thanks to the release of Oracle XE, but the limitations of this mean that this is a growth restriction for your solution.

Answer 2

Microsoft SQL Server 2005 Database

Defect Missing "INSERT or UPDATE"

Description

Often you need to either insert or update a record in a table, depending on whether or not a record exists. Without having an atomic operation for this, this leads to unnecessary transactions.

This does not happen with MySQL or SQLServer 2008.

Answer 3

PostgreSQL Database

Defect No SQL Profiler

We asked the developers about this at a recent conference, and now I understand what they want to implement.

Answer 4

I like the flexibility of sequences in Oracle compared to other database auto-replacements, but the inability to set seq.nextval as the default value for the pk column is somewhat annoying and should be trivial to fix.

Answer 5

Microsoft SQL Server Database

Defect Huge licensing cost

Description

SQL Server has great features and integrates very well with .NET development. The problem is that when you have to increase access to a shared database in a dedicated database, licensing costs are really high. This, in essence, leads to databases that must actually run on a dedicated server, hosted on shared servers with performance and security issues.

This does not happen with MySQL or PostgreSQL.

Answer 6

Microsoft SQL Server 2005 Database

Defect Poorly implemented interface

Description

SQL Server Management Studio does not offer a great user interface:

• The behavior on the tab is strange: you are always looking for the right tab
• Failure persists in 64-bit versions
• Lack of some features of the previous version, for example, an overview of grants for stored procedures

This does not happen with version 2000.

Answer 7

MySQL database

Defect Foreign keys are supported only for certain types of tables

Description

Enough said. It has obvious consequences for maintenance.

From the MySQL manual

Foreign key definitions are subject to the following conditions:

• Both tables must be InnoDB tables, and they must not be TEMPORARY tables.

And here :

For storage systems other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or save it.

This does not happen with any other main database.

Answer 8

MySQL database
Defect Server will launch damaged tables
Description

If MySQL has a damaged table - due to the fact that it was killed during the recording or some other failure - it will start quite happily and allow the user to continue as if the problem does not exist. Of course, this will lead to some error messages in the log, but in my experience, this does not help when you are trying to understand why the application is behaving strangely.

Most other databases will detect and fix a startup error or simply refuse to start with any kind of corruption.

Answer 9

MySQL database 5.0.x and higher

Defect Channel replication errors result in incompatible data on different nodes.

Description

The biggest production issue we are currently facing is that in the MySQL ring, the ring itself causes an error and stops replication.

Creating a ring (or Master-Master-replication) is possible, since 5.xx: you link the databases in a “ring” to replicate data to each other. Each node database receives all changes from all other nodes.

We assume that the error lies with auto-increment errors. This is also known from normal replication, but the sufficinet error messages are missing from the new version in the error log. I highly recommend not using this feature in MySQL unless the problems are fixed here.

Answer 10

Oracle Database

Defect Did not process a long data type for too long

Description

Oracle only had a long data type up to 9i (I believe), after which it was deprecated in favor of LOB. However, there is a ton of code that still has long and all associated limitations. The biggest of them was that each table could have only one long column, and that should have been at the end of the columns. See here for a more comprehensive list of long-term restrictions.

Answer 11

Oracle Database

Problem Temp table definitions are not private

Description Many databases (such as Postgres and Sybase) allow you to create temporary tables on the fly, insert them into them, add indexes if you want, and then query them. Oracle has temporary tables, but temporary table definitions exist in the global namespace. Therefore, the temporary table must be created by the database administrator, you need to synchronize between the definition of the table that they used, and your code, and if for the two parts of the code you need similar (but not identical) table definitions, they need to use different names. These differences make temporary tables much less convenient for developers.

Yes, I understand the benefits of the query optimizer for defining global definitions. However, for me, the lack of convenience makes Oracle temp tables almost useless to me, while I use them very heavily in Postgres.

Answer 12

Database: Oracle

Problem: names of tables, procedures, columns, etc. cannot exceed 30 characters. It infuriates.

Problem: This is JDBC Compliance. For example, stored procedures do not return result sets in a JDBC-compliant path, but instead of the proprietary type OUT. This means that you cannot use higher-level JDBC abstractions.

Answer 13

PostgreSQL does not have a good fault tolerance solution, but I understand that they are working on it.

Answer 14

Database : Sql Compact Edition

Disadvantage . Stored procedures are not supported.

Regardless of this limitation, this database has its own “uses”, especially as a client cache for an application that can be an intelligent client or distributed on mobile platforms.

Answer 15

Oracle Database

Defect Granularity of grants in packages

Description

You can grant permissions only for packages, not for stored procedures inside packages. Or, alternatively, you can grant permissions for individual stored procedures, but then you put them outside of the packages. This requires you to know who will use this stored procedure, and it is really difficult to reorganize.

This does not happen with SQL Server.

Answer 16

Microsoft SQL Server 2005 Database

Defect Missing array type parameters

Description

Useful in searches, many times you need to pass a series of values ​​that need to be matched. In SQL 2005, you can make a workaround using the CLR inside SQLServer. Given the usefulness, it would be wiser to have this feature out of the box.

This does not happen with SQL Server 2008 or Oracle.

Answer 17

Postgres Database

Defect No analytic queries

Description

The analytic queries provided by Oracle are part of the SQL 2003 standard. Unfortunately, Postgres has not yet implemented them.

Answer 18

Database: PostgreSQL

** Problem: ** this connector for C #, for example, does not update and does not work with the advanced function.

Answer 19

Database: all

The disadvantage is poor design by people who did not think it was important to know what you were doing when you created the database. More serious problems are caused in all databases by poor design than from any missing function. Therefore, I believe that they lack "to read my mind and find the best solution without me to think."

Answer 20

Any DBMS SQL

Defect: duplicate lines

One of the advantages of the relational model is that it represents everything without duplicate tuples, that is, using relationships that have keys and do not duplicate. Unfortunately, SQL is not built this way. This makes the work of database developers an unnecessary difficulty. SQL developers must deal with keyless tables and debugging queries that return duplicate rows.