3

On aws RDS I have an mysql 8.0.16 instance running. My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty.

This is a problem for me because I would like to create the following table:

CREATE TABLE my_table
(
    ...
    coordinates            POINT         NOT NULL SRID 4326
    ...
);

But this leads to an error message that says There's no spatial reference system with SRID 4326.

Whats going on here? Why is the table empty? I would expect it to contain at least one entry for the SRID 4326.

4
  • 1
    I have the same issue, I can see only one spatial ref system (srid 0), not sure if it is MySQL or RDS. @Jonas did you have any luck?
    – kwalski
    Dec 5, 2019 at 7:49
  • @kwalski Sadly, no. My solution was to start my own MySQL-instance on an EC2 machine using the official Docker image Dec 5, 2019 at 11:03
  • I have the same problem with RDS mysql 8.0.17 May 1, 2020 at 21:20
  • The CREATE SPATIAL REFERENCE SYSTEM statement requires SUPER privelges but RDS doesn't permit you SUPER privelage on the servers. I've confirmed that the CREATE SPATIAL REFERENCE SYSTEM statement does not work with the master account. Documentation that SUPER is required: dev.mysql.com/doc/refman/8.0/en/… and that RDS do not provide it: aws.amazon.com/premiumsupport/knowledge-center/… or grant it to the master account: docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… May 1, 2020 at 21:35

3 Answers 3

1

The new MySQL v8.0.16 image on RDS (as of writing this) is missing spatial references other than SRID 0.

As a workaround, you can create a new instance with v8.0.15 and then perform the minor upgrade (requires reboot).

select count(*) from INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS

...returns:

 5152
0
1

I ran into this issue as well with MySQL 8.0.16 on RDS. On a MySQL instance running on your own server, you could add the missing reference systems yourself, but this requires SUPER access, which isn't available with RDS.

I ended up contacting AWS support via the AWS console, and they were able to add the missing SRIDs for me. This was much simpler than trying to create a new instance, upgrade, copy over the data, and update my app to point to the new database. I had a Developer level support plan, so it's possible they wouldn't do this for everyone.

0

I got confirmation from AWS support that this is indeed an issue.

Here is a workaround from AWS rep (also as mentioned by kwalski):

> Launched new MYSQL instance with version 8.0.16 --> Spatial reference  missing
> Upgraded the problematic 8.0.16 instance to 8.0.17 --> Around 5152 spatial reference exist now.
> Launched new MYSQL instance with version 8.0.17 --> Spatial reference  missing

The issue exist in newly  launched 8.0.16 and also in newly launched 8.0.17 instance.
But upgrading the instance from 8.0.16(problematic) to 8.0.17 can help to overcome the issue but this requires downtime.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.