Re: export db to oracle
On Wed, Nov 17, 2010 at 1:43 AM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 11/16/2010 15:14, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. I suggest you also look at the syntax for SELECT INTO OUTFILE, too. Dumps are usually scripts of SQL statements that Oracle may not read appropriately. I'm not quite sure which formats Oracle reads in, although CSV is probably a good guess. if you disable mysqldump's extended insert syntax, however, I think the actual insert statements should be perfectly fine for most any database. You may need to tweak create statements for datatypes and syntax, though; it may be easier to just recreate the emtpy tables by hand. I think I also have vague memories of an option to use ANSI-SQL standard syntax, although that might just as well have been some third-party tool. And, speaking of third-party tools: tOra can (if well-compiled) be used to manage both MySQL and Oracle; maybe that nice tool can help you. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: export db to oracle
On Wed, Nov 17, 2010 at 2:26 PM, who.cat win@gmail.com wrote: Maybe you can dump as a csv format,then create table all tables in oracle .After that you can write a script program format the csv to oracle which can be recognized. MySQL's select into outfile may well be good enough to manage to output oracle-formatted inserts; and as I said standard (non-extended) insert syntax is probably good enough for oracle anyway. If you're going to be programming anyway, why not just write something that connects to both DBs and inserts into Oracle using prepared statements for speed ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: export db to oracle
Hello, It should be possible to connect Oracle to the MySQL (or other) database using a DBlink (using a MySQL ODBC driver) the tables could then be copied using PLSQL. Maybe you could link directly to Oracle and copy the code using MySQL procedures or scripts (I have more experienc of Oracle which works quite well as I described) This way, you can avoid use of external files and CSV etc. It is very likely quicker since you can use bulk loads or 'select into' routines once you have the right table structures and field type in place. This is a technique that I have used for ETL and data integration and it is very manageable. You can trap errors using cursors if the data has anomalies. Kevin O'Neill - Original Message - From: Johan De Meersman vegiv...@tuxera.be To: Shawn Green (MySQL) shawn.l.gr...@oracle.com Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com Sent: Wednesday, November 17, 2010 8:58 AM Subject: Re: export db to oracle On Wed, Nov 17, 2010 at 1:43 AM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 11/16/2010 15:14, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. I suggest you also look at the syntax for SELECT INTO OUTFILE, too. Dumps are usually scripts of SQL statements that Oracle may not read appropriately. I'm not quite sure which formats Oracle reads in, although CSV is probably a good guess. if you disable mysqldump's extended insert syntax, however, I think the actual insert statements should be perfectly fine for most any database. You may need to tweak create statements for datatypes and syntax, though; it may be easier to just recreate the emtpy tables by hand. I think I also have vague memories of an option to use ANSI-SQL standard syntax, although that might just as well have been some third-party tool. And, speaking of third-party tools: tOra can (if well-compiled) be used to manage both MySQL and Oracle; maybe that nice tool can help you. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error Unknown column in 'where clause'
Some discussion of causes for this is at: http://dev.mysql.com/doc/refman/5.0/en/join.html Look for the part beginning: Join Processing Changes in MySQL 5.0.12 On Nov 16, 2010, at 10:09 AM, Tompkins Neil wrote: Hi I've the following query : SELECT players_bids.players_bids_id, players_bids.players_id, players_bids.bid_date, players_bids.bid_type, players_bids.bid_value, (SELECT SUM(IF(home_users_id = players_bids.users_id_from,home_manager_points,away_manager_points)) FROM fixtures_results WHERE (home_users_id = players_bids.users_id_from OR away_users_id = players_bids.users_id_from) AND worlds_id = 1) AS manager_points, players_bids.users_id_from, (SELECT ROUND((SUM(won_home)+SUM(won_away))/COUNT(*)*100,0) FROM (SELECT IF(home_goals away_goals, 1, 0) AS won_home ,0 AS won_away FROM fixtures_results WHERE (home_users_id = players_bids.users_id_from) AND status = 'approved' UNION ALL SELECT 0 AS won_home ,IF(away_goals home_goals, 1, 0) as won_away FROM fixtures_results WHERE away_users_id = players_bids.users_id_from AND status = 'approved') s1) AS wins, players_bids.users_id_to, players_bids.worlds_id, players_bids.seasons_id, players_master.first_name, players_master.second_name, players_master.known_as, players_master.estimated_value, players_master.rating, players_master.positions_id, players.games_played, players_bids.teams_id_from, players_bids.teams_id_to, teams_master_from.team_name AS team_name_from, teams_master_to.team_name AS team_name_to FROM players_bids INNER JOIN players ON players_bids.players_id = players.players_id INNER JOIN players_master ON players_bids.players_id = players_master.players_id INNER JOIN teams_master teams_master_from ON players_bids.teams_id_from = teams_master_from.teams_id INNER JOIN teams_master teams_master_to ON players_bids.teams_id_to = teams_master_to.teams_id WHERE players_bids.bid_status = 'accepted' AND players_bids.players_id = 279 AND players_bids.worlds_id = 1 ORDER BY players_bids.bid_type ASC, players_bids.bid_value DESC, manager_points DESC, players_bids.bid_date ASC but the problem I have is that when referencing players_bids.users_id_from within the UNION I get back the following error : Error Code: 1054 Unknown column 'players_bids.users_id_from' in 'where clause' Any ideas how to overcome this problem ? Cheers Neil -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: export db to oracle
You can try using Oracle SQL Developer Tool which got the data import function from MySql. On 11/17/2010 4:14 AM, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. TIA -Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Does mysql cache strip out /* comments */ first?
Like most developers, I have a wrapper that all of my SQL queries go
through in PHP.
We have a dedicated NOC screen that shows the mytop status of each
DEV/TEST/PROD master/slave pair.
http://daevid.com/content/examples/snippets.php (Automatic Monitoring of
remote servers)
We sometimes see stuck queries and are always hesitant to kill them off
because we never know WHO is executing that SQL. Is it a customer? Is it a
developer? Is it the boss? Is it rogue from some script gone awry? Mytop
doesn't give the full query due to screen real-estate amongst other
reasons. The downside is they bog down the server until they eventually
time-out or complete.
Anyways, today I implemented a simple, transparent and effective step
towards this puzzle.
I prefix ALL SQL (since it goes through my sql_query() function) with
/* ${SCRIPTNAME} */
Now all sql in the mytop shows up as:
/* foo.php */ SELECT * FROM foo WHERE id = 1;
/* bar.php */ UPDATE bar SET a = b WHERE id = 2;
Etc...
What I'd REALLY like to do is add more information in there. Perhaps add
the FUNCTION/METHOD and the logged-in web USER that is actually executing
that SQL, etc.
My concern is, my gut tells me that the built in mysql cache system is
dumb. And by that I mean, I suspect that mySQL isn't smart enough to
strip out comments from the SQL statement string BEFORE storing it as the
cache hash key (yet I have no facts either way to back it up and hence the
reason for this email).
http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html
Can anyone please tell me I'm wrong and that it is smarter than I give it
credit for, as I think this would be a very useful feature (or bug-fix as
the case may be).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does mysql cache strip out /* comments */ first?
Given that even spacing is important, it's a safe bet that it takes comments
into consideration, too.
Easily tested, though: grab one of the heaviest queries you have from your
slowlog, and execute with identical and different comments.
On Thu, Nov 18, 2010 at 8:31 AM, Daevid Vincent dae...@daevid.com wrote:
Like most developers, I have a wrapper that all of my SQL queries go
through in PHP.
We have a dedicated NOC screen that shows the mytop status of each
DEV/TEST/PROD master/slave pair.
http://daevid.com/content/examples/snippets.php (Automatic Monitoring of
remote servers)
We sometimes see stuck queries and are always hesitant to kill them off
because we never know WHO is executing that SQL. Is it a customer? Is it a
developer? Is it the boss? Is it rogue from some script gone awry? Mytop
doesn't give the full query due to screen real-estate amongst other
reasons. The downside is they bog down the server until they eventually
time-out or complete.
Anyways, today I implemented a simple, transparent and effective step
towards this puzzle.
I prefix ALL SQL (since it goes through my sql_query() function) with
/* ${SCRIPTNAME} */
Now all sql in the mytop shows up as:
/* foo.php */ SELECT * FROM foo WHERE id = 1;
/* bar.php */ UPDATE bar SET a = b WHERE id = 2;
Etc...
What I'd REALLY like to do is add more information in there. Perhaps add
the FUNCTION/METHOD and the logged-in web USER that is actually executing
that SQL, etc.
My concern is, my gut tells me that the built in mysql cache system is
dumb. And by that I mean, I suspect that mySQL isn't smart enough to
strip out comments from the SQL statement string BEFORE storing it as the
cache hash key (yet I have no facts either way to back it up and hence the
reason for this email).
http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html
Can anyone please tell me I'm wrong and that it is smarter than I give it
credit for, as I think this would be a very useful feature (or bug-fix as
the case may be).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
