Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Working out problems with Digital Ocean managed database #137

Closed
scripting opened this issue Oct 29, 2019 · 6 comments

Comments

@scripting
Copy link
Owner

@scripting scripting commented Oct 29, 2019

I am plowing ahead with trying to use Digital Ocean's managed database function, figuring that I might get further with their service because DO's docs tend to be much more complete and beginner-friendly than say AWS's.

Perhaps they're easier in this case, but they assume you know a lot about MySQL that I didn't know. So it's a slog. Very slow going. But -- I finally did get my app running somewhat.

The problem I'm currently dealing with is that I use double-quotes for string literals. Their MySQL isn't prepared for that, and throws an error every time I do it. It says "1.234" isn't the name of a column.

I've figured out what's going on, from the String Literals page in the MySQL docs,

If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotation marks because a string quoted within double quotation marks is interpreted as an identifier.

I'm going to change the app to use single quotes, but not today. I need to figure out how to turn xxx off. And that's where I'm kind of stuck.

When I got a list of modes, this is what I got back.

REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Sure enough ANSI_QUOTES is there.

Now to figure out how to get it out of there. And leave a note behind so I know I have to do it if I provision another database.

@scripting

This comment has been minimized.

Copy link
Owner Author

@scripting scripting commented Oct 29, 2019

OK, following the advice on this page, I did the following.

SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

@scripting

This comment has been minimized.

Copy link
Owner Author

@scripting scripting commented Oct 29, 2019

I bet there are some other modes there that I don't want.

@scripting

This comment has been minimized.

Copy link
Owner Author

@scripting scripting commented Oct 29, 2019

Apparently that did not fix the problem. Here's a bit of SQL, followed by the error it generates.

select * from items where flDeleted=false and feedurl in (select feedurl from subscriptions where listname="podcasts.opml") order by whenCreated desc limit 250;

ER_BAD_FIELD_ERROR: Unknown column 'podcasts.opml' in 'where clause'

@scripting

This comment has been minimized.

Copy link
Owner Author

@scripting scripting commented Oct 29, 2019

I have another system where I installed MySQL myself, and this is what sql_mode is there.

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I think I will try setting the DO database to that string for sql_mode and see what happens. I can always delete the database and start over whenever.

@scripting

This comment has been minimized.

Copy link
Owner Author

@scripting scripting commented Oct 29, 2019

Still getting the ER_BAD_FIELD_ERROR.

@scripting

This comment has been minimized.

Copy link
Owner Author

@scripting scripting commented Oct 29, 2019

I decided to go ahead and modify the app to use single quotes instead of double quotes.

Turned out to be much easier than trying to comprehend the weirdnesses of MySQL.

@scripting scripting closed this Oct 29, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
1 participant
You can’t perform that action at this time.