Re: [Spacewalk-devel] MySQL porting info
On Mon, Apr 27, 2009 at 03:13:09PM +0200, Matej Hasul wrote:
Hello, I started working on migrating tables to mysql.
Data types conversion looks like this:
varchar2 - varchar
number - numeric
date - timestamp
Following problems emerged:
1. Mysql doesn`t support user defined data type. So I have no idea what to
do with evr_t.
Please check what the deal with evr_t in PostgreSQL port was. IIRC,
the type is used for collation and presentation, so you should be able
to replace it with plain table and a couple of functions.
2. Mysql doesn`t support nested tables. For example something like create
or replace type channel_name_t as table of varchar(64). Does anyone know
some workaround?
Check if that type is needed at all. Looking around the source code,
it is only used in the channel_name_join function, and that one does
not seem to be called anywhere. So it both looks like a dead code to
me, which should be removed from the Oracle schema as well.
3. Check constraint not working. According to mysql documentation - The
CHECK clause is parsed but ignored by all storage engines.
Workaround: Use before-insert-or-update trigger to implement check
constraint.
OK.
4. Create table test(date1 date default (sysdate), date2 date default
(sysdate)) will not work in mysql, because there can be only one date column
with default clause.
Workaround: Use before-insert-or-update trigger to set actual date(s).
OK.
5. Missing sequence in mysql.
Workaround: One option is to use AUTO_INCREMENT and LAST_INSERT_ID().
Another option is to create table holding sequence number and create
procedures curval('seq_name') and nextval('seq_name').
With the table-holding-sequence-values approach (and I'm not sure
about the first one), keep in mind that it will be
transaction-aware -- if you rollback, you will get the same value next
time. That might or might not be a problem. Also, for the same reason,
the sessions will likely serialize on that table.
--
Jan Pazdziora
Senior Software Engineer, Satellite Engineering, Red Hat
___
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] MySQL porting info
On Tue, Apr 28, 2009 at 10:10:01AM -0400, Tom Lane wrote: The usual experience AFAIR is that the queries have to be dumbed down to the point that they no longer perform well on Oracle (or Postgres), because of mysql's crummy optimizer and lack of support for advanced join strategies. A lot of the Spacewalk code is now Java/Hibernate, so the queries are actually not that complex. -- Jan Pazdziora Senior Software Engineer, Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
[Spacewalk-devel] MySQL porting info
Hello, I started working on migrating tables to mysql.
Data types conversion looks like this:
varchar2 - varchar
number - numeric
date - timestamp
Following problems emerged:
1. Mysql doesn`t support user defined data type. So I have no idea what to
do with evr_t.
2. Mysql doesn`t support nested tables. For example something like create
or replace type channel_name_t as table of varchar(64). Does anyone know
some workaround?
3. Check constraint not working. According to mysql documentation - The
CHECK clause is parsed but ignored by all storage engines.
Workaround: Use before-insert-or-update trigger to implement check
constraint.
4. Create table test(date1 date default (sysdate), date2 date default
(sysdate)) will not work in mysql, because there can be only one date column
with default clause.
Workaround: Use before-insert-or-update trigger to set actual date(s).
5. Missing sequence in mysql.
Workaround: One option is to use AUTO_INCREMENT and LAST_INSERT_ID().
Another option is to create table holding sequence number and create
procedures curval('seq_name') and nextval('seq_name').
Comments are welcome.
Matej Hasul
___
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] MySQL porting info
Matej Hasul wrote:
Hello, I started working on migrating tables to mysql.
Data types conversion looks like this:
varchar2 - varchar
number - numeric
date - timestamp
For the records. Matej is trying to rewrite queries in run time to be
MySQL compatible...
Following problems emerged:
1. Mysql doesn`t support user defined data type. So I have no idea what
to do with evr_t.
map it to different table? I.e change evr_t to integer and use it as
look up value to table EVR with columns epoch, version, release and
queries which use it expand to join with this table?
2. Mysql doesn`t support nested tables. For example something like
create or replace type channel_name_t as table of varchar(64). Does
anyone know some workaround?
The same, leave it as normal table and join it with original in runtime.
3. Check constraint not working. According to mysql documentation - The
CHECK clause is parsed but ignored by all storage engines.
Workaround: Use before-insert-or-update trigger to implement check
constraint.
If possible. But I think we can live without these constraints. Whover
will use mysql can not have the same data consistency as in Oracle or
PostgreSQL by definition.
4. Create table test(date1 date default (sysdate), date2 date default
(sysdate)) will not work in mysql, because there can be only one date
column with default clause.
Workaround: Use before-insert-or-update trigger to set actual date(s).
Err. There can be more default columt with date. But the value have to
be constant, not the function.
Yeah, trigger can fix it.
5. Missing sequence in mysql.
Workaround: One option is to use AUTO_INCREMENT and LAST_INSERT_ID().
Another option is to create table holding sequence number and create
procedures curval('seq_name') and nextval('seq_name').
I think AUTO_INCREMENT and LAST_INSERT_ID() meets our needs.
But yeah, creating table which hold the sequence better map to sequence
as we all know it. But remember that it can not be table which support
transaction, otherwise you will be recycling the numbers from sequence.
--
Miroslav Suchy
Red Hat Satellite Engineering
___
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] MySQL porting info
Matej Hasul matej.ha...@gmail.com writes: Hello, I started working on migrating tables to mysql. Isn't this a complete waste of time? There is exactly 0 chance of migrating all those procedures to mysql. regards, tom lane ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] MySQL porting info
Tom Lane wrote: Matej Hasul matej.ha...@gmail.com writes: Hello, I started working on migrating tables to mysql. Isn't this a complete waste of time? There is exactly 0 chance of migrating all those procedures to mysql. How do you know? -- Miroslav Suchy Red Hat Satellite Engineering ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] MySQL porting info
=?ISO-8859-2?Q?Miroslav_Such=FD?= msu...@redhat.com writes: Tom Lane wrote: Isn't this a complete waste of time? There is exactly 0 chance of migrating all those procedures to mysql. How do you know? Try it for awhile and see what you think. The incompatibilities and missing features you're running into in the DDL are just the tip of the iceberg. regards, tom lane ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
