1.9.2 New Features Planned For 5.0

The following features are planned for inclusion into MySQL 5.0. Note that because we have many developers that are working on different projects, there will also be many additional features. There is also a small chance that some of these features will be added to MySQL 4.1. For a list what is already done in MySQL 4.1, see section 1.6.1 Features Available in MySQL 4.1.

For those wishing to take a look at the bleeding edge of MySQL development, we have already made our BitKeeper repository for MySQL version 5.0 publically available. See section 2.3.4 Installing from the Development Source Tree.

Stored Procedures

• Stored procedures are currently being implemented. This effort is based on SQL-99, which has a basic syntax similar (but not identical) to Oracle PL/SQL. We will also implement the SQL-99 framework to hook in external languages, and (where possible) compatibility with, for example, PL/SQL and T-SQL.

New functionality

• Elementary cursor support.
• Visible RTREE index for MyISAM tables. In 4.1 RTREE indexes are used internally for geometrical data, but not directly usable.
• Dynamic length rows for HEAP tables.

Standards compliance, portability and migration

• Add true VARCHAR support (there is already support for this in MyISAM).

Speed enhancements

• SHOW COLUMNS FROM table_name (used by mysql client to allow expansions of column names) should not open the table, only the definition file. This will require less memory and be much faster.
• Allow DELETE on MyISAM tables to use the record cache. To do this, we need to update the threads record cache when we update the `.MYD' file.
• Better in-memory (HEAP) tables:
  • Dynamic size rows.
  • Faster row handling (less copying).

Internationalisation

• When using SET CHARACTER SET we should translate the whole query at once and not only strings. This will enable users to use the translated characters in database, table, and column names.

Usability enhancements

• Resolving the issue of RENAME TABLE on a table used in an active MERGE table possibly corrupting the table.

User Comments

Full-text search capabilities for BLOB fields over binary files stored in this fields with plugin support (.doc, .pdf, .xls...) like Microsoft SQL Server.

What about XML Support like Oracle? Creating Databases based on XML data and such things?

How about the ability to include more than 1 server in a query as you can with Microsoft Access and MS SQL Server?

What about an OLAP support in MySQL? I think, GROUP_CONCAT function is a step in this direction... What about the query language? MDX or something more "open" (W3C compliant)? Bye

Any plan to add support for Java store procedures? I'm using
Oracle java store procedures for all my applications, I can
tell you that once you get use to java in the database, you
will never touch PL/SQL again.

How about adding support for functions and triggers along with stored procedures. Esp. triggers which eases many of the arbitary db operations required by applications.

Using BCD arithmetic rather than float when using fields which are described or converted as decimal.
This will become more important when procedures are implemented.
As a non oracle user, how about having a javascript option( which more people are familiar with ) for the procedure
language.

Hi,
i am with Oracle, and a fan of PL/SQL. It would be great if some concepts like Packages, keywords, ...
would be near as possible to oracle-plsql. So a lot of PL/SQL-modules could be used immediatly.

Carl

I have been in search for a replacement for both Ms SQL2000 and Oracle DBMS. Well, from seeing what is planned for 5.0 release, I think you have done it! Keep up the good work!

how about cloned tables?

For convenience I like to split big tables into smaller
clones with different data but identical schema.

I guess I am looking for a view..

How about time zone support (as in Oracle 9i)? But with a better/slightly simplified interface than in Oracle.

Congratulations on such a nice project.I do hope your efforts provide developers with a new taste of using database servers in the Open way ..!

But how about "refrential integrity" in MYISAM databases? The use of "stored procedures" along with "refrential integrity" makes developing much more practical.

Good Luck.

Nice improvements for 5.0! But we need referential integrity and optimizing, repairing for Innodb tables.

Thanks

-Select Time-Out
-Views

And my data warehouse will be a happier place.

How about column based tables and SQL optimized for time series analisys?

Oracle embedd JVM,Can MySQL embedd Mono?

I'll add my voice to "Mike on Tuesday" above. Connections to multiple servers simultaneously has a real wow! factor. I think Oracle does this with "database links"? Gets my vote.

Views!

With full implementation of views and subselects, most of the main reasons for picking another platform are gone...

Views, definitely need views.
Subselects would be nice, as well as set operations, Intersect, Union and so on.

For me:
* Views
* Support for Stored Procedures in C# or any other .net language though Mono
* Data Transformation Services, like MS-SQL Server 2000. It would be great to import and export directly from MySQL.

Bye!

VIEW VIEW VIEW please ..... I need

"Any plan to add support for Java store procedures? I'm using
Oracle java store procedures for all my applications, I can
tell you that once you get use to java in the database, you
will never touch PL/SQL again."

Oracle itself states that Java is best used for intesive calculation type problems. Like compute some weird science formula then give the results to some PL/SQL to process the database inserts and what not. PL/SQL is best used used for database intensive things like returnign a record set and doing some logic and then inserting some results based on that logic. This is not to say you can't use Java for all stored procedures or PL/SQL for all stored procedures. The thing to keep in mind is one is better in certaion situations then the other if you get down to the nitty gritty.

How about a features that allows you to limit the amount of simultaneous connections by mysql user?

How about Zend?! would be nice have a php into mysql... many sites could put they functions used in php into the mysql server... the zend accelerator could help too... and the zend encoder...

I've implemented User Definable Functions using Java by integrating the JVM and extending the SQL Create Function syntax. Anybody interested? (Still Version 3.x)

- Views with union all
- Triggers

If you are implementing stored procedure support in 5.0, and writing it from scratch, I would not bother using PL/SQL syntax at all. You should seriously look at integrating with the Mono project instead, to cut down on the development effort, and add support for virtually any language in stored procedures, including C#, VB, Java, etc. If someone really wants, they can make a Mono compliant PL/SQL compiler and you'd still get your PL/SQL stored procedures that way.

MySQL is great! Thanks to all of development team. But a few feature should be added as listed below
1. Transformation Service like SQL2000 Tools in order to transform from others database format suchas MSAccess, MSSQLServer, for examples to help user to accomplish their task.
2. Stored procedure, already in progress. thanks.
3. View
4. User Defined Datatype, function.
5. ************ Validation Text (like MSAccess) ***** I found that myself a very usefull for Database Designer in order to inform user what condition they've just violate to rules and how to correct those problems. Even MS SQL2000 doesn't have it (I don't know why?), Only Constraint exitst witout a place to input "Validation Text". That leave task of handling error to programmer (but DB Designer is responseible or database design and rules of each field).

With this features, and referential integrity, Others DB Server have great competitions.... Thanks

is it possible for MySQL to put in a TRUE DB Syncronization for MySQL 5.0 between server DB and Client Db, Server Db to server DB, Client DB to Client Db on any pc system. this would be something very nice to have if Db needs to syncronize between multiple computers and with syncronization of the dbs on these PCs.

i am not sure this features is implemented on the other Db Softwares such as Oracle or Postgres sql.

if MySQL doesn't have it, it should be implemented it to the 5.0 version.

Freeman

i need views !!!
plz

Enhanced Full-text support, esp. Proximity Operators, enhanced scoring of full-text search results, weight factors in indexed strings.

I have seen most of the above in the to-do list, but I haven't got a clue on what is the implementation schedule. Enhance full-text support is very important.

Also, I add my vote on Subselects, Views, Referential Integrity, Cursors, Stored Procedures and OLAP support (in this priority order).

For release 5.0

Could you consider adding a dblinks option within the MySQL table to allow other MySQL servers to be accessed within queries.

For example

create database link <dbaselink1> for <host>.<db> connect as <user> identified by <pwd>;

select db1.col1 from dbaselink1.table db1;

Priority order of new features (IMHO):

1. Views
2. Referntial integrity
3. Triggers
4. Stored procedures
5. Multiserver connections

You can't name MySQL as "SQL server" without it, it still be only "ISAM file manger + SQL interpretator" and not more. Why you began investigations to Replication and to other things from "big RDBMS" World without support of the "must have" things?

... And ple-e-e-ase do not implement PL/SQL-like syntax in triggers/stored procs. It will be better if you provide more wide Java support in MySQL. It more actually, I think.

Please implement in this order

- Referntial integrity
- Views
- Triggers, Stored procedures
etc.

to call MySql an "SQL Server".

What about an AUTO_INCREMENT extension "BY n"
I need it for safety to have different ID's
in several tables.

how about Auto Date like with Auto Increase IDs but it adds 'todays' date when adding a DB Feild

I would like to have a high level algebra that allows you to add or subtract databases themselves. A - B = C so that C is the difference between two schema, and then D = D + C allow you to update D with the difference between A and C. At least for the schema.

Definitley PL/SQL for Stored Procedure support, but also for other languages like PHP, perl, python, and java. Forget about VB and C# and all other non-standard languages. That is about it. Any other request might make the product to bulky (Ref integrity, Triggers, Views, tool after tool). Right now mysql is a very fast database and I would like it to stay that way. A full blown database server with all the bells and whistles you can get at Micosoft and Oracle. It appears by looking at the responses that many are looking for a free Oracle or Free SQL server.

How about internationalisation of (among others) the Date functions?
It would be nice to have Dutch dates by config, rather then by modifying the source.

One let-down with Oracle is no priority. Resource limits dont allow for a big query to run "in the background" while more important (near real-time) transactions are occuring.

4 or 5 levels of priority would solve most problems in this area.

How about displaying execution plan as in MS SQL Server or Oracle? I would love it. :-)

Materialized views as an extension of views. This is essentially snapshot replication which you do now, but in the form of a view. E.g. you create a snapshot from a query (like a view), except it is kept in a table. It can be replicated to another server, or used on the same one as a manner of performance enhancement or a security tool or both. Views alone are good but are often too poor performing.

Database referential integrity is good, but in really large systems implementing it is a performance hit. If you constantly have to go to million+ row tables to check integrity, you application performance can be hurt. Many developers of larger systems would rather use application logic to ensure integrity, so I don't think integrity constraints would be high up there on priority.

It is good to see built in stored procedures coming along... triggers would be good too, since they are specialized stored procedures.

I agree with the request for some way to view query plans... and possibly a way to add query hints to aid performance.

Good stuff... no kidding.

I think it would be very interesting if mysqldump could report the tables with order other than alphabetic, so foreign key settings, for example, could be generated from a dump.

Having to change (when moving to MySQL) WHERE clause code to use CONCAT for adding strings is a pain: the rest of the world seems to be OK with using +, and hopefully this would become an option here, also.

Please add creation of conditional index like clipper:

CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON tbl_name (col_name[(length)],... )
WHERE <condition>

This save space reduzing size of index file and is more
powerful for speed searchs.

Please can support for DOMAINs be added?

Please add
- subselects
- views

My $ 0.02:

INTEGRITY CHECKS! INTEGRITY CHECKS! INTEGRITY CHECKS!

There's nothing more important for a serious DBMS - not even performance, in my opinion. (you can always keep the different table types for "security <=> performance" trade-offs)

With InnoDB referential integrity works quite well, the most important thing missing in MySQL -- and I mean, I _really_ miss this, it's fundamental -- are CHECK contraints, naturally with complete working subselects. (you should also point out in the current documentation that CHECK constraints are not supported at all, the documentation for v4.0.12 was so misunderstandable that it made me believe they work with InnoDB)

Other important things (in order of priority)
- udfs, triggers
- views

- domains
- (real) sequences
- materialized views
- ...all other stuff :o)

hi,
its great to work with mysql innodb and bdb tables.
which is supporting transactions, but still there are some issues with mysql one of such is row level locking

it would be great if you can implement row level locking features in version 5.0,in oracle there is "for update nowait" clause.
i don’t know whether there is any such feature in mysql.
i am small baby in this town
since in multi-user environment row level locking plays a major role.
hope this feature will be a real boom for all mysql fans and developers like me, who are eagerly waiting for making a real time applications using mysql.


best Regards
Dayananda shenoy

1. Referential integrity
2. Triggers
3. Stored procedures
4. Views

All above is a MUST for a database.

I agree with others, referential integrity, views and stored procedures are basic requirements if you want to call MySQL a real SQL server.
In my opinion even more important features are:
=> better space management (don't need to reinvent the wheel, look at Oracle tablespace, by far the most flexible storage management concept of all RDBMS!),
=> better backup/recovery mechanism (built-in hot backup is a must!),
=> more compact export dump (without INSERT statements that almost double the size of the file, at least in my case – lots of short fields) and finally,
=> LOAD DATA. We need functionality and features no more no less than that of Oracle SQL*Loader (easy handling of fixed record data (LOAD DATA is a PAIN in MySQL for fixed sized record files). Ok, I'm biased in my first complaint (99% of my input is in fixed sized record files), however not knowing what went wrong (and where) during the load is simply not acceptable – we need log capability such as that of Oracle SQL*Loader, this is more important than load speed!! What's good to load 10GB in a few machine minutes (cheap) then spend several man hours (expensive) to find out which records were faulty.

Unfortunately I'm not a programmer, so all I can do is to cry like a baby and hope that someone will hear me ;-)

What’s with everyone wanting VIEWS? This isn't MS ACCESS guys, we don't need views. MySQL has an EXCELENT priv system, the requirement for views is essentially gone. True, you may save yourself a itty bitty little space in your code, but over all your queries will be unnecessarily clunky then.

We actually have a developer here who makes an attempt to listen to the community, and what do we ask for? Views and RI (which they've said how many times that they not only don't plan to implement in the near future, they don't even really WANT to implement it). Come on guys, don't be lazy, do your RI in your code, and pick the right tool for your job.

For versions 5.x, I'd simply hope that we get replication air tight, and the ability to DELETE using a sub select, as having to put aside 100MB of ram for a temp table to delete child records gets really old, really quick.. especially considering MySQL is usually used in constrained environments.

As additional 'some day' features, I personally would love to see IOTs (just imagine the speed… just imagine), Table Partitioning (won’t hold my breath) and a full plan of what our good friend, the optimizer, plans to do with our SQL statements.

Sadly, as I am just one voice, I’m guessing we’ll just get views…!

At any rate, great job to everyone on the MySQL team; you guys should be proud.

Cheers,
Chris

For what we do, Java stored procedures are important. PL/SQL is also good, but all of ours are in Java in Oracle right now, even SQL intensive ones.

Views are also handy if you need to morph your data. For example, if you already have an address book, you can create a "phone log" table for a phonebook program without having to edit it. This lets you keep legacy software around without having to fiddle with the schema.

Scott

I need support for money type fields to move from sybase.

I'd like to see the ablity to alter tables without rebuilding the table and without taking it offline. In particular, column additions, and index additions.

For Joshua Wilson:
if you need money types you can use DECIMAL with 2 decimals. There is no need for Ms types.

What about support for MS SQL's Uniqueidentifier datatype?

Please can you add tree support like oracle "CONNECT BY"

It would be really nice to be able to INSERT into multiple tables with one statement, like this:

INSERT INTO users,contacts SET users.name=dan7, contacts.name=Dan, contacts.phone=etc, users.contactid=contacts.id

Where both tables have an auto_increment ID field, and they are InnoDB tables, and users has a required Foreign Key field called 'contactid' that refers to a contacts.id

Of course by the time this is implemented (if it is), I'll have a mess of Perl code that does it anyway.

I would love to see some kind of native support for mysql clustering. It seems replication is perhaps not the best solution for scaling if an application is under massive load. Any thoughts about this?

Hi

I guess VIEW is the most essential and needed thing in any database management system and not having view is taking so much development time. Without view code will have more redundancy.

I guess VIEW must be given the first priority.

Add your own comment.