A story about trying to connect to MySQL using Heroku and giving up

Overview

I wanted to add a database-based feature to my current linebot, and tried connecting to MySQL using Heroku, but there was a lot of stumbling block. As a result, I gave up, but I hope that it will be helpful for beginners like me, so I will summarize it just in case.

environment

What not to write in this article

--How to use detailed queries in MySQL

procedure

1. Install MySQL

Install MySQL itself with pip install mysql.

2. Connector installation

Install the connector. To be honest, I don't really understand the role, but as the name suggests, it seems necessary to connect to the MySQL server. There are many types of connectors depending on the purpose and language used, but I use mysqlclient. This is also installed with pip install mysqlclient.

3. Preparation on Heroku

In my case, after finishing all the preparations for MySQL described later, I pushed the code to Heroku to check the operation, but I got an MySQL import error. There were two causes, but the first one is the description of requirements in the pushed file. An error occurred because I didn't list the MySQL connector here (By the way, I wasn't sure at first and listed the version of MySQL itself, but when I pushed it, an error occurred. Masu).

Second, when using MySQL on Heroku, it seems that you will use something called ClearDB, and you will need an account created from it. I will omit the detailed registration method, but as a caveat, most of the articles about ClearDB say that after registering, copy the environment variable CLEARDB_DATABASE_URL, change the beginning to mysql2 and set it to DATABASE_URL. , This operation seems to be necessary only for Ruby.

Therefore, this time, I decided to connect to MySQL using the account of CLEARDB_DATABASE_URL when ClearDB was added with heroku addons: create cleardb: ignite. Since it is described in CLEARDB_DATABASE_URL in the form of mysql: // [username]: [password] @ [hostname] / [db_name]? Reconnect = true, mysql --host = [hostname] on the terminal. You can connect by typing --user = [username] --password = [password] [db_name] .

4. Preparation with MySQL

If I can enter MySQL safely, I will create the necessary tables etc., but a problem occurs here. I typed a query on the monitor to create a table normally, but I got the error ʻERROR 2013`. Immediately after that, when I entered the same query again, the execution itself was done although another error occurred. From this, I thought that I probably had to change the timeout system settings, but did not accept the command to change it. After a lot of research, I came to the conclusion that the root cause was that I had to migrate to change the DB settings. However, although I investigated how to migrate Flask, I couldn't understand it at all. Originally I wasn't particular about MySQL itself, so I gave up and decided to use postgresql, which is Heroku's standard DB.

~~ When I looked it up in the error message, it seemed that it could be solved by increasing the value such as connect_timeout, but it is not a fundamental solution and it can be processed without problems if the root user enters, so ClearDB I thought there might be a peculiar problem. So, if you take a closer look at the ClearDB site, ~~

~~ It is recommended to use MySQL Workbench, Sequel Pro for Mac OS X, Navicat, etc. when editing the database. ~~

I found a description saying ~~. These seem to be GUI tools that allow you to operate MySQL without directly entering a query, and I saw someone using MySQL Workbench in an article before, so I decided to try this. Refer to this article for detailed operation method etc., I was able to create the necessary table safely. ~~

As an aside, when I first tried to enter MySQL as a root user after practicing MySQL, I couldn't find the password no matter how much I searched for. I knew that the initial password was listed in the given file when I installed MySQL, but I couldn't find this file no matter how many steps I followed. As a result, I managed to get into MySQL at Refer to this article.

Summary

I wrote it for a long time, but in summary

  1. Install MySQL
  2. Connector installation
  3. Register ClearDB on Heroku (list connector version in requirements)
  4. ~~ Editing in ClearDB with MySQL Workbench ~~ Migration?

It will be.

This time, I tried a new field called database, and it was a great learning experience because I needed a different perspective from writing the code that I had been doing so far. Beta base seems to be indispensable for almost all services, so I would like to take this opportunity to acquire solid knowledge and broaden my skills.

reference

-How to use MySQL -How to use the convenient official tool MySQL Workbench and how to translate it into Japanese -What to do if you forget your root password in MySQL -How to use MySQL on Heroku

Recommended Posts

A story about trying to connect to MySQL using Heroku and giving up
A story about trying to run JavaScripthon on Windows and giving up.
A story about a beginner trying hard to set up CentOS 8 (procedure memo)
A story about trying to install uwsgi on an EC2 instance and failing
A story about a Python beginner trying to get Google search results using the API
I get [Error 2055] when trying to connect to MySQL on Heroku
A story about trying to implement a private variable in Python.
A story about trying to use cron on a Raspberry Pi and getting stuck in space
A story about using Python's reduce
Connect to MySQL using Flask SQLAlchemy
A story about trying to run multiple python versions (Mac edition)
A story about Python pop and append
Scraping using lxml and saving to MySQL
A story about a 503 error on Heroku open
A story about deploying a Twitter-linked app created using Flask + gunicorn on Heroku
A story about simple machine learning using TensorFlow
A story about Go's global variables and scope
A story about running Python on PHP on Heroku
Connect to mysql
A story about modifying Python and adding functions
A story of trial and error trying to create a dynamic user group in Slack
A story about trying to introduce Linter in the middle of a Python (Flask) project
A story about using Resona's software token with 1Password
How to set up a Python environment using pyenv
A story of trying out pyenv, virtualenv and virtualenvwrapper
A story about trying a (Golang +) Python monorepo with Bazel
A story about kindergartens, nursery schools, and children's gardens
A story about struggling to loop 3 million ID data
Connect python to mysql
(Note) A story about creating a question answering system using Spring Boot and machine learning (SVM)
A story that makes it easy to estimate the living area using Elasticsearch and Python
[Note] A story about trying to override a class method with two underscores in Python 3 series.
[Django] A story about getting stuck in a swamp trying to validate a zip with form [TDD]
A story about porting the code of "Try and understand how Linux works" to Rust
[Linux] A story about mounting a NAS through a firewall using NFS
A story about how to specify a relative path in python.
[Python] Chapter 01-03 About Python (Write and execute a program using PyCharm)
A story about installing matplotlib using pip with an error
Try to bring up a subwindow with PyQt5 and Python
A story about how to deal with the CORS problem
A story about everything from data collection to AI development and Web application release in Python (3. AI development)
[Google Photo & Slack Photo Bot] A story about making a bot that acquires photos in Google Photos and sends them to Slack.
How to build a LAMP environment using Vagrant and VirtulBox Note
A story about adding a REST API to a daemon made with Python
Process Splunk execution results using Python and save to a file
A little more about references ~ Using Python and Java as examples ~
How to format a table using Pandas apply, pivot and swaplevel
[Python] Conversation using OpenJTalk and Talk API (up to voice output)
Set up a node to do MNIST on ROS using Tensorflow
A story about wanting to think about garbled characters on GAE / P
A story about trying to reproduce Katsuwo Isono, who does not react to inconvenience, by natural language processing.
A story about someone who wanted to import django from a python interactive shell and save things to a DB
A story about trying to improve the testing process of a system written in C language for 20 years