dimitri
ERROR Database error 42601: syntax error at or near "unsigned"
pgloader version 3.6.2
compiled with SBCL 1.4.0-1.el7
sb-impl::*default-external-format* :UTF-8
2020-04-19T19:46:29.629000+01:00 ERROR Database error 42601: syntax error at or near "unsigned"
QUERY: CREATE TABLE test_db.avh2d_ak_profiles
(
id int unsigned not null,
description varchar(255) not null,
configuration text,
filters text,
quickicon smallint not null default '1'
);
2020-04-19T19:46:29.634000+01:00 FATAL Failed to create the schema, see above.
2020-04-19T19:46:29.641000+01:00 LOG report summary reset
table name errors read imported bytes total time read write
----------------- --------- --------- --------- --------- -------------- --------- ---------
fetch meta data 0 552 552 0.581s
Create Schemas 0 0 0 0.002s
Create SQL Types 0 2 2 0.017s
Create tables 0 0 0 0.000s
----------------- --------- --------- --------- --------- -------------- --------- ---------
----------------- --------- --------- --------- --------- -------------- --------- ---------
2020-04-19T19:46:29.694000+01:00 INFO Stopping monitor
[root@iamsan scripts]# psql -U test_user test_db
psql (12.2)
Type "help" for help.
test_db=> CREATE TABLE test_db.avh2d_ak_profiles
test_db-> (
test_db(> id int unsigned not null,
test_db(> description varchar(255) not null,
test_db(> configuration text,
test_db(> filters text,
test_db(> quickicon smallint not null default '1'
test_db(> );
ERROR: syntax error at or near "unsigned"
LINE 3: id int unsigned not null,
^
test_db=>
part of dump from mysql (Joomla database):
--
-- Table structure for table `avh2d_ak_profiles`
--
DROP TABLE IF EXISTS `avh2d_ak_profiles`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `avh2d_ak_profiles` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`configuration` longtext COLLATE utf8mb4_unicode_ci,
`filters` longtext COLLATE utf8mb4_unicode_ci,
`quickicon` tinyint NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
Hi @iamnumlock ; can you give us the result of the MySQL catalog query that pgloader uses to determine the data types on the source database?
select c.table_name, t.table_comment,
c.column_name, c.column_comment,
c.data_type, c.column_type, c.column_default,
c.is_nullable, c.extra
from information_schema.columns c
join information_schema.tables t using(table_schema, table_name)
where c.table_schema = 'Joomla' and t.table_type = 'avh2d_ak_profiles'
order by table_name, ordinal_position;
Meanwhile you should be able to install a per-column user-defined casting rule so that your load can make progress.
Hi @iamnumlock ; can you give us the result of the MySQL catalog query that pgloader uses to determine the data types on the source database?
select c.table_name, t.table_comment, c.column_name, c.column_comment, c.data_type, c.column_type, c.column_default, c.is_nullable, c.extra from information_schema.columns c join information_schema.tables t using(table_schema, table_name) where c.table_schema = 'Joomla' and t.table_type = 'avh2d_ak_profiles' order by table_name, ordinal_position;Meanwhile you should be able to install a per-column user-defined casting rule so that your load can make progress.
LOCK TABLES avh2d_ak_profiles WRITE;
/*!40000 ALTER TABLE avh2d_ak_profiles DISABLE KEYS /;
INSERT INTO avh2d_ak_profiles VALUES (1,'Default Backup Profile','###AES128###zDDOdvi8wcbpNm/Km0/qJJDhCWdtINqtB+AW56AKzg5BPObtWoUdWfEG9tv4P/Hxp19PrpK7EhP4GgOloKdxUWxVJSKO+34GsQx2i6z3bXBXqOm9WtdNHlr8e/zoTrCGSrzYmT/hoM1qTnR/BmZEFzjxOZlr/H
VfMDZm8Qh0TfA05zb4JHINGFfc5Yl6vDRrZiMrVGMBqn4DnUDYhnURGXduuS2dthRs09TABGPX47c1ulYfDRSvIzremsYrXBUuMk96mMXPI714fiRHEcfB2saTRIh+Dxfr7KVRZZQJzlpL3mxv43/sVuIg5De8x1ntw4banN43mzIHOfhglYTvP1jJTxYQnxqlqrywbUlgyqPOYun/oESdvYt+Py69d+fQ8QetHXzjdO1F
rqP1XiFanBOYqFFh1/gfApERVE1XRPyjFXyXTLfxN/fHy+nfd8FLqBDdqK0W5elbz3eaKF88qcxgOEYOFB7biXYmGoKftvarubyGnskTkdtfZWmhkiw8NsFMaYb2PTvh6kuoOiGYuMkEY26FRgR4Pn8UJjS5X5oDgyLOKBFAhxTZeGNwybhOQ3Ln3jHVSRNc9aRo+vVui/c8PU799+NlDMpAaOq41owmJRigKMOn/uHZ+b
aikYcPlviR1b4fa7nr24tnTkLNnmW6y7pG03MnWhsT83+y7j0zzsed/MjPRgSa4yipYQoqPOW/mo2kBp2zxGSMiXP2CsoKT0zfGqesMoS30QYWBKO9KHIF2qaPuvWrId7+bzlXDTSOhW3ZuarQD7AyRQHR3M7HL04u/1nu562CnoSkoukoVTaIYEQzdx3Qdrby9g1uG08RC0Q167VO6j4A/+D6D2LEg+BymBKVsvId3eOw
Oh3gn9/uEuiAHdzEjF/1ewbdbtJyNqrAiSPKdAER74f3m/Mx34SayS7PPDIZXgXvKuoamzzO3JUr9gz9QRt78BAZf9xWnLP3SMnAQ04vxe6PzOwaOIM4qSwtOnUBw0UHgpV4lsWwtlZdQxVI7gd/gf/sMf8uTODst7oLDS3UDzBtGIZUGGxGBUhbiqIRlLz1DiRoBYdthS9orj1vfQLqAIGilN1UfeyzVgPs4lj82+nTkC
3sae8y1m7ZX8jH9bfW5OGIMTXWjEytPJBScXM1B4DlHN3i3xNUYqQ9921qOWyxU+MNxRjmJex5+p7cS1lmp/T0xkAAUl/7desUbVIw8O+kojymvlRk4XXIx/+T48pjp2oB7kT3+J0kr+SBWKi8E78fiyrnaFnQuLWjf0HMtkct6/zpd/iYLqjlCHLbiWGSytUrk5mFeS32se/Ty+ZpZKwCv0mhaM+YtEgjKvcIILHfclRC
ca6LGSrbGaMgAux3O6jM3oM3tj8egjPVFkvZ0wl0hcGpOXl9rqYOxeHVNtdbHHWpzMmUz5sBR9v3FSYHfTRngM4WFENWMKcyMDMCvZQk8/A5mdi0Fq5vKv6+MU1/RkzRakmO737a7+QgLTXPMQaJV73QLsGi1kdlyvmvkrqrot3BjvPfm1TFOdH46SS3+G3iWhdWMjrm2bw4gG9C5RpjZUT3W0HjvfnpO8KMNUVFzY1jS/
h6A7iGJ28/UkC1l3WHWLvxjigPWFF5n+WIPz1XJMETtN9KgQ50PGGpazAmpzmCS7CMdWL9+mipeGCfPx3csnH6aPe41Qhc61j0uLsVgVgwewPsoJKC7nUgjQsk23pDfc9GqJLUq3d6TjwcM9knl/MCAh3fe9tAehWOYdg6mfqqRQLGSH/Q2Gd2j76mqAirzayWCNviTZOj2jif0KwaiOEv/0K014qJXPD0FmXjbuVKUaEQ
1DWD7TeY65uGMMOOIRI2o8ncMM9I6pXSupyvi98oLTCgjrnDLrqqAEzCVhGj4listBr1rn/oAD4ApZFvS2jTSGK0c90OSqVpIK5o2Ir5VBIbHXTfsBBa5yJzZNgJ98wAwO91+QeFD3k8B+azijwGzXhdyDT0LGgjVaat8s62z0pdHJEvV1Y5gmNl6EvjASnqX98go31TxqBv1CS1tacZ8MVJBxEqYhUeikHqmIDWy39j9J
86xHYDYO9Y2sUrxkkMzXnF8nMx1WfNQ4lMcyKrVaExVtZ1MZCRX+yHHzfaiVO3LEoQelRQiCQVGXxnpaXT4bmYWfA5FcvH5nCVZ7nL/nH3rDmBGhTWOzVxjLwdk3GyU/9ohYY7JTy0HTnKlGTjLPbsnR6Z5YR9VMdAB+iGOkZ8AhVMOtwnXJD9/6kW04l7YvIlihBDO7nWENvnU4kdhtOyGnEH5i7hwUt9QvQBxfZKwDpH
Oax1iPRtx+otg2Ho9rt2B8vhdO7qP8yAp4eM+0zdkrkmfeeZ5xuoilCU0v0RpNqOAkyw9Cx4CanouEY8P7ce+vKf5coxajWzv/QERA8TsUS354SZoHrLdwIta406SZNq2sJk4ZMJsoNfMkJ0FvjPLhpNuXPXd8+/7egKI5YIuwj8nMxm28uWhGed0BYzqK8llo0UxppUyKseqmyx8zrgnOqp9Ktt8u4O68tuNMHftvFGMT
kqvuHNNHEgUqBpZCuYMTXsvW4BgOp01GivLuAVH8OBzV+qLFgcXihIryA74o7noIeaTnTA8IM5IkNCw3El9sfgIRY4pK7yYu4ouO1bYR3aZCsgyE4JIUHhsnbJhqjZeRm5vTCjIrf919s/96wdpbdVeWpjsGrKcDs+I/atleKPbaiFLjFtdnymKWB3s7m5ChD+8Q7QET9+fZdHv2vt7HJsf3b0HdzYTr15WykEKEvxFkqN
9zBqpylsZX1aMx1/seJAeH3EtTc1tcJIiDGwOc1rTgvLzs2Vmx+V4ayBiTDHTZ0NEaH+KLXVfBueeoBoFQPbihGIViVe7cjbj5j9y+xkRhdcBZdiT9t++HIjVGWbz1ZXsq9/cLfZWJ4Ocbf2plB0eFHvhLApzQwXBCOXgjVrDZS9b8w4CsniU5KHUH0LW5YVyEYCIDyaZJF0h6cXcJvstUr1VNraDCwPbWMjchJX1rnfgY
gfzBg1LuxIHH+h28Ae0tGZtDnCqzU90YSGeok3GmRwVpudlX/Z+stRMVQrfngw2mpVMaNf2bPFfBUTXkKGElQqrXh+vcdDpjcZIgvdrCaqoMA+b4ID9MqkgAtJAjmS6c6xQ8aLm/1LU/89j6X4Qs/M7yJGY7zkN59YRWpYgc+5tkaKMctR7SxZP0iIid7kX1l+S2vqA03jIfsxio8+F9Z4Q46MKTry8UlT8oE6XHhTXqC5
ce4hVF/aZwda01fOJq6lYmbZS4l3DCXdeso+Hf1155VYYqLY8MgTS00E2mkRjo+eV9kozm2Z1AOsU0DW1bVH5MOkBIhvyN+8XnFFa6bsN4W8TFUWlHQxkZXLWr0O0LH3Mq6qSVzYPIfd4d4cfapX7Lx6ZLQ/QgrN15eWromDP+xPwKyG/7FaKEL2wLnF/MRxdpGQ570//E8VV5UmPldISWwgk2Y2gmDwYycsHpWdyjD5fN
p2TBNPVYXTXCqFUjIisC5ZcUGDPD1Vyk+WwTZnYhbL/PQOuUXGGOGmQ2I55Jkgj+zQXZKYq5IndV39+ZwdYABXpK22u/4dPlB6MydVUUwM2ATy3EJG7ce5BXHOB0jgOmsuVEYe3mTV+leKWHoUJyZjQiry3LJWfWyz6je4YBdunz7cbHWFm0TV4ADHr/6klNy3xaAi5o1pMdk4qZlNJy7QmrIKKXYHCnJt8SxPiBipgSDI
rDLeUHFmrTsbenSYFI79LijPgaHaHF37b15na7/CTOOXxFq8GTlocjS9VN+j2+RwK73rmxc2BtjjgTL3bbSInYgC03lbYLmA6NRrM1pSP8aXWfxSNDg15DA8PMqxzkTVuXMLyc1jU+halElpTmRQL90f2L3qHejoTen0llyvsM6U+9mbS9hmdBrRY8Si7b8xq7A/9IblZlB2gnMtxJiuA1/MZD/orCQXEncZWE/Vx8C9kG
G7shI8SCF5/M+XKQaE5Ym2wekp+PLbliPXW+9R+mjeyBVpi+DMJ96227HYMp75kOFn60aXy3duZu+UM5fxHrFYZzyRwFcYDuqiBxZXB7m1SqUJxC6Y70/z0Ix0cFnkSjJD5QfY1hAHCb1lNtxsjnQhsoXzprrxsDKt2NIRoE3DBa95sSeAjuSKXT7r8sGcAxqZcKEMFoDcqEVOOupvVgJVf0pQU1SdoRn1SjsVYHOkdOhn
aZs6/6AjNITwPRkhcdusGkXrvzTw6rw4gaAMPndHoDDV9feSmdQOBQMkgTrZLgPxxLDGSlBJVmgkoNkfwDJEdjDCmZUkA/s7DAAA','',1);
/!40000 ALTER TABLE avh2d_ak_profiles ENABLE KEYS */;
UNLOCK TABLES;
root@localhost [my_db] > select c.table_name, t.table_comment, -> c.column_name, c.column_comment, -> c.data_type, c.column_type, c.column_default, -> c.is_nullable, c.extra -> from information_schema.columns c -> join information_schema.tables t using(table_schema, table_name) -> where c.table_schema = 'Joomla' and t.table_type = 'avh2d_ak_profiles' -> order by table_name, ordinal_position; Empty set (0.01 sec)
root@localhost [my_db] >
I have the same issue not sure what to do - copying data from heroku mysql to postgres on localhost - connects to Heroku successfully but cannot create Schema
@iamnumlock @python-force please try adding
CAST type int when unsigned to bigint,
type int with extra auto_increment when unsigned to bigserial;
@iamnumlock @python-force please try adding
CAST type int when unsigned to bigint, type int with extra auto_increment when unsigned to bigserialt;
bigserial; Without 't' at end.
