SCM

Forum: bugs

Monitor Forum | Start New Thread Start New Thread
RE: Strange errors [ reply ]
By: joe speigle on 2008-01-04 13:36
[forum:1002985]
I think what we should really do is issue a create statement command for the schema, and then create the tables inside the schema.

There is a --schema option (an option with an argument). This is one of those things that can make porting not easy.

As it stands now, you'll have to dump each database individually with the appropriate --schema flag.

Any changes in formatting, like that by the MySQL administrator are not parsed as proper SQL so that's why the ENGINE=innoDB line had not been replaced.

If you want to convert the script to work with mysql Administrator, that would be fantastic, we can create a separate download.

RE: Strange errors [ reply ]
By: Kim Bisgaard on 2008-01-04 13:22
[forum:1002983]
No, I used MySQL Aministrator an official MySQL tool for windows and linux to make the backup.

I can also see that my tables get created in PostgreSQL as table name "oceandb.xxx" and not "xxx" nor as "xxx" in scema "oceandb" :-(

Here is the start of the backup:
-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version 5.0.27-log


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema oceanDB
--

CREATE DATABASE IF NOT EXISTS oceanDB;
USE oceanDB;
CREATE TABLE `oceanDB`.`archiveSeaObs` (
`id` int(11) NOT NULL default '0',
`timestamp_utc` datetime NOT NULL default '0000-00-00 00:00:00',
`sealevel` int(11) default NULL,
`seatemp` decimal(5,2) default NULL,
`salinity` decimal(4,1) default NULL,
`waveheight` decimal(5,2) default NULL,
`wavedir` int(11) default NULL,
`currentspeed` decimal(3,1) default NULL,
`currentdir` int(11) default NULL,
PRIMARY KEY (`id`,`timestamp_utc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `oceanDB`.`archiveSeaObs` VALUES (40001,'2006-02-28 12:52:00',147,'-999.00','-999.0','-999.00',-999,'-9.0',-999);

RE: Strange errors [ reply ]
By: joe speigle on 2008-01-04 13:05
[forum:1002981]
yes. does the mysqldump program include the "CREATE DATABASE IF NOT EXISTS oceanDB" line?

My version does not (see below). The script is only designed to work for the mysqldump. Also, the problem is that the database in mysql is not the same as a database in postgres. In postgres, you want to use a schema. Schemas are contained inside the database.

Schemas group database objects together within a database, like on a per-user basis. If you were looking for everybody to have their own databases, just use schmemas and create all the schemas in the same database. If no schemas are made and the object was not created in a schema, it defaults to the "public" schema, as can be seen issuing \dv from the psql prompt.

users can be limited to their own schemas. This creates a mysql-like separation of visibility on the basis of the username they connect as.

There's a disconnect between mysql and postgres in this regards.



[root@localhost mysql2pgsql]# mysqldump combali > my.out
[root@localhost mysql2pgsql]# mysqldump --version
mysqldump Ver 10.10 Distrib 5.1.7-beta, for pc-linux-gnu (i686)
[root@localhost mysql2pgsql]# head -40 my.out
-- MySQL dump 10.10
--
-- Host: localhost Database: combali
-- ------------------------------------------------------
-- Server version 5.1.7-beta-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `address_book`
--

DROP TABLE IF EXISTS `address_book`;
CREATE TABLE `address_book` (
`address_book_id` int(11) NOT NULL auto_increment,
`customers_id` int(11) NOT NULL default '0',
`entry_gender` char(1) NOT NULL default '',
`entry_company` varchar(32) default NULL,
`entry_firstname` varchar(32) NOT NULL default '',
`entry_lastname` varchar(32) NOT NULL default '',
`entry_street_address` varchar(64) NOT NULL default '',
`entry_suburb` varchar(32) default NULL,
`entry_postcode` varchar(10) NOT NULL default '',
`entry_city` varchar(32) NOT NULL default '',
`entry_state` varchar(32) default NULL,
`entry_country_id` int(11) NOT NULL default '0',
`entry_zone_id` int(11) NOT NULL default '0',
PRIMARY KEY (`address_book_id`),
KEY `idx_address_book_customers_id` (`customers_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[root@localhost mysql2pgsql]#

RE: Strange errors [ reply ]
By: Kim Bisgaard on 2008-01-04 07:50
[forum:1002975]
Hi,

I got past my troubles by the following patch:
--- /home/kib/bin/scripts/mysql2pgsql.perl~ 2008-01-03 09:29:23.216820000 +0100
+++ /home/kib/bin/scripts/mysql2pgsql.perl 2008-01-03 16:51:53.677058000 +0100
@@ -194,9 +194,10 @@
sub quote_and_lc($)
{
my $col = shift;
- if ($LOWERCASE) {
- $col = lc($col);
- }
+ $col=~s/`//g; #remove mysql backpings
+ if ($LOWERCASE) {
+ $col = lc($col);
+ }
if ($col =~ m/,/) {
my @cols = split(/,\s?/, $col);
@cols = map {"\"$_\""} @cols;
@@ -320,16 +321,16 @@
# doh! we hope all dashes and special chars are caught by the regular expressions :)
}
if (/^\s*USE\s*([^;]*);/) {
- print OUT "\\c ". $1;
+ print OUT "\\c ". quote_and_lc($1) ."\n";
next;
}
-if (/^(UN)?LOCK TABLES/i || /drop\s+table/i ) {
+if (/^(UN)?LOCK TABLES/i || /drop\s+table/i || /create\s+database/i) {

# skip
# DROP TABLE is added when we see the CREATE TABLE
next;
}
-if (/(create\s+table\s+)([-_\w]+)\s/i) { # example: CREATE TABLE `english_english`
+if (/(create\s+table\s+)([-_\.\w]+)\s/i) { # example: CREATE TABLE `english_english`
print_post_create_sql(); # for last table
$tables_first_timestamp_column= 1; # decision to print warnings about default_timestamp not being in postgres
$create_sql = '';
@@ -344,7 +345,7 @@
$pre_create_sql .= "DROP TABLE $table CASCADE\\g\n"; # custom dumps may be missing the 'dump' commands
}

- s/(create\s+table\s+)([-_\w]+)\s/$1 $table /i;
+ s/(create\s+table\s+)([-_\.\w]+)\s/$1 $table /i;
if ($DEBUG) {
$create_sql .= '-- ' . $_;
}


Notice that I choose to ignore the "create database" - a better approach I will leave to you ;-)

Regards,
Kim

Strange errors [ reply ]
By: Kim Bisgaard on 2008-01-03 10:03
[forum:1002970]
Hi,

I must be doing something unusual as I get strange errors: Here are a couple of examples:

Input 1:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema oceanDB
--

CREATE DATABASE IF NOT EXISTS oceanDB;
USE oceanDB;
CREATE TABLE `oceanDB`.`archiveSeaObs` (
`id` int(11) NOT NULL default '0',
`timestamp_utc` datetime NOT NULL default '0000-00-00 00:00:00',
`sealevel` int(11) default NULL,
`seatemp` decimal(5,2) default NULL,
`salinity` decimal(4,1) default NULL,
`waveheight` decimal(5,2) default NULL,
`wavedir` int(11) default NULL,
`currentspeed` decimal(3,1) default NULL,
`currentdir` int(11) default NULL,
PRIMARY KEY (`id`,`timestamp_utc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Output 1:
-- ##############################################################
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
CREATE DATABASE IF NOT EXISTS oceanDB;
\c oceanDB
CREATE TABLE oceanDB.archiveSeaObs (
id int(11) NOT NULL default '0',
timestamp_utc datetime NOT NULL default '0000-00-00 00:00:00',
sealevel int(11) default NULL,
seatemp decimal(5,2) default NULL,
salinity decimal(4,1) default NULL,
waveheight decimal(5,2) default NULL,
wavedir int(11) default NULL,
currentspeed decimal(3,1) default NULL,
currentdir int(11) default NULL,
PRIMARY KEY (id,timestamp_utc)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- MySQL Administrator dump 1.4


Notice that "ENGINE= ..." has NOT been removed from the output - something has confused the program?

Input 2:
INSERT INTO `oceanDB`.`archiveSeaObs` VALUES (40001,'2006-02-28 12:52:00',147,'-999.00','-999.0','-999.00',-999,'-9.0',-999);

Output 2:
INSERT INTO "oceandb`.`archiveseaobs" VALUES (40001,E'2006-02-28 12:52:00',147,E'-999.00',E'-999.0',E'-999.00',-999,E'-9.0',-999);

Notice the back-pings in the output.

Hope you have a hint to what needs to be done differently?

TIA,
Kim

Powered By FusionForge