Oracle 5.0 Reference Manual Download

Page: 3007 / 3232

Restrictions on XA Transactions

2987

When the procedure

p()

is called, the

SELECT

returns 1 each time through the loop, even though

the view definition is changed within the loop.

As of MySQL 5.0.46,

ALTER VIEW

is prohibited within stored routines, so this restriction does not

apply.

With regard to view updatability, the overall goal for views is that if any view is theoretically updatable,
it should be updatable in practice. This includes views that have

UNION

in their definition. Currently,

not all views that are theoretically updatable can be updated. The initial view implementation was
deliberately written this way to get usable, updatable views into MySQL as quickly as possible. Many
theoretically updatable views can be updated now, but limitations still exist:

• Updatable views with subqueries anywhere other than in the

WHERE

clause. Some views that have

subqueries in the

SELECT

list may be updatable.

• You cannot use

UPDATE

to update more than one underlying table of a view that is defined as a join.

• You cannot use

DELETE

to update a view that is defined as a join.

There exists a shortcoming with the current implementation of views. If a user is granted the basic
privileges necessary to create a view (the

CREATE VIEW

[577]

and

SELECT

[578]

privileges), that user

will be unable to call

SHOW CREATE VIEW

on that object unless the user is also granted the

SHOW

VIEW

[578]

privilege.

That shortcoming can lead to problems backing up a database with

mysqldump

, which may fail due to

insufficient privileges. This problem is described in Bug #22062.

The workaround to the problem is for the administrator to manually grant the

SHOW VIEW

[578]

privilege to users who are granted

CREATE VIEW

[577]

, since MySQL doesn't grant it implicitly when

views are created.

Views do not have indexes, so index hints do not apply. Use of index hints when selecting from a view
is not permitted.

SHOW CREATE VIEW

displays view definitions using an

AS alias_name

clause for each column.

If a column is created from an expression, the default alias is the expression text, which can be quite
long. As of MySQL 5.0.52, aliases for column names in

CREATE VIEW

statements are checked against

the maximum column length of 64 characters (not the maximum alias length of 256 characters). As
a result, views created from the output of

SHOW CREATE VIEW

fail if any column alias exceeds 64

characters. This can cause problems in the following circumstances for views with too-long aliases:

• View definitions fail to replicate to newer slaves that enforce the column-length restriction.

• Dump files created with

mysqldump

cannot be loaded into servers that enforce the column-length

restriction.

A workaround for either problem is the modify each problematic view definition to use aliases that
provide shorter column names. Then the view will replicate properly, and can be dumped and reloaded
without causing an error. To modify the definition, drop and create the view again with

DROP VIEW

and

CREATE VIEW

, or replace the definition with

CREATE OR REPLACE VIEW

For problems that occur when reloading view definitions in dump files, another workaround is to edit
the dump file to modify its

CREATE VIEW

statements. However, this does not change the original view

definitions, which may cause problems for subsequent dump operations.

E.5. Restrictions on XA Transactions

XA transaction support is limited to the

InnoDB

storage engine.

For “external XA,” a MySQL server acts as a Resource Manager and client programs act as
Transaction Managers. For “Internal XA”, storage engines within a MySQL server act as RMs, and

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands