20210505のMySQLに関する記事は7件です。

Ktor×Exposed MySQL接続

の続きになります。 Exposedを利用し、ローカルMySQL DBへの接続を実装します。 環境 MySQL: Ver 8.0.23 for osx10.15 on x86_64 (Homebrew) 手順 プロジェクトへExposedを追加 MySQLへDB作成 DBへ接続する データアクセス 1. プロジェクトへExposedを追加 build.gradle へ必要なライブラリを追加 ※ mysql-connector-java: JDBCドライバを内包しています。多分ローカルのMySQLとバージョン合わせておけばOK。 dependencies { ︙ implementation "org.jetbrains.exposed:exposed:0.17.13" implementation "mysql:mysql-connector-java:8.0.23" } 変更を適用しインストールを行います。 2. MySQLへDB作成 接続確認の為にMySQLへ簡単なデータを作成しておきます。 mysql> USE test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW tables; +----------------+ | Tables_in_test | +----------------+ | animals | +----------------+ mysql> DESCRIBE animals; +-------+------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | text | NO | | NULL | | | cry | text | YES | | NULL | | +-------+------+------+-----+---------+----------------+ mysql> SELECT * FROM animals; +----+-----------------------+--------------------+ | id | name | cry | +----+-----------------------+--------------------+ | 1 | ゴリラ | ウホウホ | | 2 | オランウータン | アァー! | | 3 | サル | うきゃうきゃ | +----+-----------------------+--------------------+ 3. DBへ接続する Database.connectでDBへ接続できます。 パラメータ url, user, password はそれぞれの設定に合わせてください。 Application.kt private fun connectDB() { Database.connect( url = "jdbc:mysql://127.0.0.1/test", driver = "com.mysql.cj.jdbc.Driver", user = "root", password = "password" ) } @kotlin.jvm.JvmOverloads fun Application.module(testing: Boolean = false) { connectDB() ︙ 4. データアクセス https://github.com/JetBrains/Exposed/wiki/Getting-Started#dsl--dao 上記を参考に、DSL形式でアクセスしてみます。 テーブル定義 Tableを継承したobjectを作成します。 テーブル名とカラム名はDBへ作成したものと合わせます。 src/db/table/Tables.kt object Animals : Table() { val id = integer("id").autoIncrement().primaryKey() val name = varchar("name", 200) val cry = varchar("cry", 200).nullable() } データ取得 雑ですが /animals ルーティングにデータ全件取得する処理を記述(次の章で綺麗にする予定) selectAll()でテーブルの全レコードを取得することができます。 Application.kt get("/animals") { var animals = listOf<Animal>() transaction { animals = Animals.selectAll().map { Animal( id = it[Animals.id].toInt(), name = it[Animals.name].toString(), cry = it[Animals.cry].toString() ) } } call.respond(animals) } // 扱いやすくする為にAnimalデータクラス作成 data class Animal( val id: Int, val name: String, val cry: String?, ) 動作確認 http://0.0.0.0:8080/animalsにアクセスし、DBに作成したデータが表示されることを確認 ??? 参考 JetBrains/Exposed - Getting Started
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

DBIx::Schema::DSLとMySQL型の対比

使える型 データ型 MySQLで言うと tinyint -128から127(0から255) smallint -32,768から32,767(0から65,535) integer int型(-2,147,483,648から2,147,483,647(0から4,294,967,295)) bigint -9,223,372,036,854,775,808から9,223,372,036,854,775,807(0から18,446,744,073,709,551,615) number int型の別名 decimal 固定小数点型 dec decimalの略称 numeric decimalの別名 float 浮動小数点型 double 浮動小数点型 real doubleの別名 bit bit型(2進数) date date型(YYYY-MM-DD) datetime datetime型(YYYY-MM-DD HH:MM:SS[.fraction]) timestamp timestamp型(エポック秒) char  固定長文字列 varchar 可変長文字列 string varchar型の別名 text text型 binary 固定長バイナリバイト文字列 varbinary 可変長バイナリバイト文字列 tinyblob tinyblob型(バイナリデータ型) blob blob型(バイナリデータ型) enum enum型 set set型(複数選択項目を格納できる) emumの書き方 create_table 'テーブル名' => columns { emum 'hoge' => ['aaa', 'bbb', 'ccc'] }
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

【Rails】マイグレーション実行時にMysql2::Error: Duplicate entry for key..が出た

Railsでテーブルのデータに対して一意性制約を付与するマイグレーションファイルを実行しようとした際に上記エラーが発生 class AddIndexToUserBooks < ActiveRecord::Migration[6.0] def change add_index :user_books, [:user_id, :book_id], unique: true end end マイグレーションの内容としては中間テーブルのデータの組み合わせに対して一意性制約を設けるもの。 原因と解決策 すでにテーブルに存在しているデータが一意性制約に反していた。 上記例でいれば、同じuserとbookの組み合わせがすでにテーブルに挿入されていることが原因で一意性制約を付与できない状態だった。 これをテーブルから直接削除して解決 所感 Duplicate entryはマイグレーション実行時には発生しないと思っていたので一瞬焦りました。 マイグレーションが実行される場合はすでにテーブルに存在しているデータとの整合性も判定されているということですね。
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

Web3層システムをAWSで作ってみる#4(EC2 DB編)

リハビリ&社内勉強用のために作成します。 用意するもの ・PC ・インターネット ・TeraTarm ・WinSCP ※ファイル転送時 用語 用語 意味 MySQL DBサーバ用のソフトウェア(MW) ゴール 今回はWeb3層システムをAWSで作ってみる #3(APサーバ編)の内容に追加して、新規のEC2にてDBサーバを作成しTomcatと連携させます。 なお、今回作成するDBサーバはインターネット接続の必要がない(不要なアクセスを防ぎたい)サーバになるので、本来はPrivateSubnetに配備します。が、準備が面倒なのでPublicSubnetに配置します。PrivateSubnetにサーバ作成したい方は以下「追記1」の方法で準備可能です。 手順 1.EC2の作成 まず試しにEC2を作成してみましょう。手順はWeb3層構造のシステムをAWSで作ってみる #2(Webサーバ編)を参照してください。EC2は以下設定値としてください。 項目 設定値 Amazon マシンイメージ (AMI) Amazon Linux 2 AMI (HVM), SSD Volume Type インスタンスタイプの選択 t2.micro ネットワーク vpc_yh_20210501 サブネット PublicSubnet01_20210501 自動割り当てパブリック IP 有効 なお、今回はセキュリティグループを特に作成しません。デフォルトのセキュリティグループでもssh接続は許可されており、今回作成するDBサーバは他のポートからアクセスできるためです。 1.MySQLのインストール AmazonLinuxには標準でMariaDBというDB用MWが入っていますが、利用しないので削除します。 [ec2-user@ip-10-0-1-112 ~]$ yum list installed | grep maria mariadb-libs.x86_64 1:5.5.68-1.amzn2 installed [ec2-user@ip-10-0-1-112 ~]$ sudo yum -y remove mariadb-libs.x86_64 #complete!と出ればOK 次にMySQLのリポジトリをインストールします。 [ec2-user@ip-10-0-1-112 ~]$sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm -y #complete!と出ればOK [ec2-user@ip-10-0-1-181 ~]$ sudo ls -ltr /etc/yum.repos.d/ #mysqlのレポジトリを確認 次にMySQLのインストールを行うが、デフォルトのMySQL8(8.0)をそのままインストールすると関連パッケージが足りないと怒られたので、MySQL5.7(57)をインストールするようyumの設定を書き換えてからインストールする。 [ec2-user@ip-10-0-1-181 ~]$ sudo yum-config-manager --disable mysql80-community [ec2-user@ip-10-0-1-181 ~]$ sudo yum-config-manager --enable mysql57-community [ec2-user@ip-10-0-1-181 ~]$ yum info mysql-community-server From repo : mysql57-community [ec2-user@ip-10-0-1-181 ~]$ sudo yum install mysql-community-server -y #complete!と出ればOK [ec2-user@ip-10-0-1-181 ~]$ mysqld --version mysqld Ver 5.7.34 for Linux on x86_64 (MySQL Community Server (GPL)) #バージョン確認 試しに起動停止してみる。 [ec2-user@ip-10-0-1-181 ~]$ sudo systemctl status mysqld #Active: inactive (dead)を確認。 [ec2-user@ip-10-0-1-181 ~]$ sudo systemctl start mysqld [ec2-user@ip-10-0-1-181 ~]$ sudo systemctl status mysqld #Active: active (running) を確認。 [ec2-user@ip-10-0-1-181 ~]$ sudo systemctl stop mysqld [ec2-user@ip-10-0-1-181 ~]$ sudo systemctl status mysqld #Active: inactive (dead)を確認。 [ec2-user@ip-10-0-1-181 ~]$ sudo systemctl enable mysqld #ついでにOS起動時の自動起動設定も入れておく 2.MySQLへのログイン 標準だと文字コードがItalicになっている。日本語が使えるようutf8に修正。 sudo vi /etc/my.cnf #以下を追記 sudo mysqld --verbose --help > /dev/null #記述内容の確認 my.cnf character_set_server=utf8 MySQLを起動する。 [ec2-user@ip-10-0-1-181 ~]$ sudo systemctl start mysqld [ec2-user@ip-10-0-1-181 ~]$ sudo systemctl status mysqld #Active: active (running) を確認。 初期パスワードはログ参照。 [ec2-user@ip-10-0-1-181 ~]$ sudo cat /var/log/mysqld.log | grep password 2021-05-04T15:47:58.310443Z 1 [Note] A temporary password is generated for root@localhost: kNAHGj=0=3?S PWを変更 [ec2-user@ip-10-0-1-181 ~]$ mysql -u root > use mysql; > ALTER USER 'root'@'localhost' identified BY 'password'; (PASSWORD関数はMySQL8では廃止されている様。) 文字コードがutf8になっていることを確認。 mysql> show variables like '%chara%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) 3.DBの作成 複数のDB(データベース)の中に、複数のTBL(テーブル)が存在する。 まずは新規にDBを作成してみる。 既存のDBを見て mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) そこに新規DBを追加してみる mysql> create database testdb; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.00 sec) 利用するDBを変更。 mysql> use testdb; 4.TBL作成・編集 適当にTBLを作成してみる。 mysql> CREATE TABLE users ( num INT(5) AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), yomi VARCHAR(255), year INT(4)); Query OK, 0 rows affected (0.01 sec) 行を追加してみる。 mysql> insert into users(name, yomi, year) values('新垣 結衣','あらがき ゆい',1988),('石原 さとみ','いしはら さとみ',1986); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from users; +-----+------------------+------------------------+------+ | num | name | yomi | year | +-----+------------------+------------------------+------+ | 1 | 新垣 結衣 | あらがき ゆい | 1988 | | 2 | 石原 さとみ | いしはら さとみ | 1986 | +-----+------------------+------------------------+------+ 2 rows in set (0.00 sec) 行を削除してみる。 mysql> DELETE FROM users WHERE name = '石原 さとみ'; Query OK, 1 row affected (0.00 sec) mysql> select * from users; +-----+---------------+---------------------+------+ | num | name | yomi | year | +-----+---------------+---------------------+------+ | 1 | 新垣 結衣 | あらがき ゆい | 1988 | +-----+---------------+---------------------+------+ 1 row in set (0.00 sec) ユーザ作成してリモート接続権限を付与する。 mysql> CREATE USER 'itskill'@'%' IDENTIFIED BY 'itskill';  mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'itskill'@'%' WITH GRANT OPTION; TomcatとDBの連携 次にTomcatの入ったEC2の方にドライバを入れてDBと連携させる。 jarファイルはこちらからダウンロード。MySqlのバージョンとの互換性を要確認(ReadMeのリンク先からMySQLのドキュメントページに飛べる。)。 [ec2-user@ip-10-0-1-181 ~]$sudo cd /tmp [ec2-user@ip-10-0-1-181 ~]$sudo curl -O https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.12/mysql-connector-java-8.0.12.jar [ec2-user@ip-10-0-1-181 ~]$sudo mv /tmp/mysql-connector-java-8.0.12.jar /opt/apache-tomcat-9.0.45/lib/ [ec2-user@ip-10-0-1-181 ~]$sudo chown tomcat:tomcat /opt/apache-tomcat-9.0.45/lib/mysql-connector-java-8.0.12.jar 続いてserver.xmlに設定を追記する。 [ec2-user@ip-10-0-1-181 ~]$sudo vi /opt/apache-tomcat-9.0.45/conf/server.xml server.xml <Context docBase="itskill" path="/itskill" reloadable="false" source="orrg.eclipse.jst.jee.server:itskill"> <Resource auth="Container"driverClassName="com.mysql.cj.jdbc.Driver" name="jdbc/datasource" type="javax.sql.DataSource" url="jdbc:mysql://[ローカルIP]/testdb" connectionProperties="characterEncoding=utf8;rewriteBatchedStatements=true;useSSL=false;requireSSL=false" username="[ユーザ名]" password="[PW]" /> </Context> 最後にTomcatを再起動して設定を反映する。 追記1:PrivateSubnetにおけるNW環境整備 課題とやることを記載します。 課題①:グローバルIPがないからsshアクセスできない! PublicSubnetに作成したEC2を踏み台に、PrivateSubnetのEC2へssh接続します。PublicSubnetのEC2に秘密鍵を置いてアクセスしたり、ALB経由でアクセスしたりもできますが、セキュリティ観点上微妙なので踏み台がベストプラクティスだと思います。 TeraTerm前提であれば「Amazon EC2」へ、踏み台サーバから「SSHポートフォワード」経由で接続するで可能です。 課題②:EC2からインターネットへ接続できない! yumなどパッケージ系のインストール時、インターネットに接続できないパターンです。 踏み台EC2側でインストール用ファイルを経由する手もありますが、yumなどのコマンドが使えなくなるのでAWSベストプラクティスらしくNATゲートウェイを作成します。NATゲートウェイはAWSが提供する踏み台サービスで、これをPublicSubnetに置いて、代わりにインターネットに接続してもらいます。 NAT ゲートウェイをセットアップするにはどうすればいいですかや NAT ゲートウェイを設定してみる - EC2で可能です。 追記2:RDSの場合 見ながらやろう! AWSを始めよう -RDS編-参照。 ポイントは以下の通り。 ・yum -y install mysqlでmysql cliantを落とす。 ・SG設定は3306でインバウンド通信をWeb側のSG(もしくは任意の場所)に許可設定入れておく。Web側のインバウンドにも3306通信の許可設定を入れておく。 ・subnetは事前に2つ以上作っておく。 ・接続はmysql -h [endpoint] -P 3306 -u [ユーザ名] -p 参考 ①CentOS7にMySQLをインストールして初期設定するまで ②[AWS]EC2(Amazon Linux)にMySQL5.7をインストールする ③基礎MySQL ~その2~ my.cnf (設定ファイル) https://qiita.com/miriwo/items/eb09c065ee9bb7e8fe06 ④Mysql 5.7* パスワードをPolicyに合わせるとめんどくさい件について ⑤CentOS7にMySQL公式リポジトリを使って最新のMySQL8.0をインストール ⑥[CentOS7][MySQL5.7]ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO) ⑦CentOS7でMySQLにログインできない ⑧外部のホストから接続できるようにする方法 備忘:ログインできなくなった時 以下を参照。 CentOS7でMySQL5.7のパスワードリセット
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQL RouterにUnixドメインソケットで繋ごうとした

MySQL InnoDB Cluster練習 - Qiita 前回の続き 概要 MySQL routerは冗長構成にするのではなく、アプリケーションサーバー上でローカルUNIXドメインソケット接続するのが推奨らしい 引き続きRedhat系のAlmaLinux8での検証 結果 --conf-use-socketsを付けてbootstrapすることでrouter起動時にsocketファイルが出来上がる ただしそのままだとsocketファイルのパスはsystemdのprivate-tmp内になってしまう mysqlrouterの--directoryオプションで変更は可能だが、設定ファイル等も移動してしまうのでsystemdのmysqlrouterユニットファイル変更が必要になってしまう bootstrap後に/etc/mysqlrouter/mysqlrouter.confを書き換えるのが現状の良さげな解か? config_generator.ccにsocketsdirというオプションがあり、これが使えれば書き換えしなくて済みそうだが、引数を受け付ける実装が無いため使えなかった。 設定 --conf-use-socketsを付けてbootstrapすることでsocketファイルが出来上がる // router # mysqlrouter --bootstrap root@node1 --user=mysqlrouter --conf-use-sockets # cat /etc/mysqlrouter/mysqlrouter.conf | grep sock socket=/tmp/mysql.sock socket=/tmp/mysqlro.sock socket=/tmp/mysqlx.sock socket=/tmp/mysqlxro.sock # systemctl start mysqlrouter // systemdのprivate-tmpにより実体は以下になる # ll /tmp/systemd-private-2a0d5074208b46fdbb4fad9862a4b0c4-mysqlrouter.service-WHFHbS/tmp/ total 0 srwxrwxrwx 1 mysqlrouter mysqlrouter 0 May 4 17:35 mysqlro.sock srwxrwxrwx 1 mysqlrouter mysqlrouter 0 May 4 17:35 mysql.sock srwxrwxrwx 1 mysqlrouter mysqlrouter 0 May 4 17:35 mysqlxro.sock srwxrwxrwx 1 mysqlrouter mysqlrouter 0 May 4 17:35 mysqlx.sock // Unixドメインソケットで接続可だが期待したパスではない # mysql -u root -p -S /tmp/systemd-private-2a0d5074208b46fdbb4fad9862a4b0c4-mysqlrouter.service-WHFHbS/tmp/mysql.sock mysql> // パスを書き換えてみる # vi /etc/mysqlrouter/mysqlrouter.conf -socket=/tmp/mysql.sock +socket=/var/lib/mysqlrouter/mysql.sock -socket=/tmp/mysqlro.sock +socket=/var/lib/mysqlrouter/mysqlro.sock -socket=/tmp/mysqlx.sock +socket/var/lib/mysqlrouter/mysqlx.sock -socket=/tmp/mysqlxro.sock +socket=/var/lib/mysqlrouter/mysqlxro.sock # systemctl retart mysqlrouter # mysql -u root -p -S /var/lib/mysqlrouter/mysql.sock bootstrap時に変更するオプションがあるようだが、引数を受け付ける実装が無かった。 mysql-8.0.24/router/src/router/src/config_generator.cc if (user_options.find("socketsdir") == user_options.end()) options["socketsdir"] = "/tmp"; MySQL Routerの冗長化について - 2000秒後の私へ MySQL Routerを使用して、InnoDB Clusterを構成する(MySQL Routerの設定主体) - CLOVER? MySQL :: MySQL Router 8.0 :: 3 Deploying MySQL Router
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

【本番環境】【備忘録】rails db:create RAILS_ENV=production でrails aborted! Mysql2::Error::ConnectionError: Access denied for user 'root'@'localhost' (using password: NO)とエラーが出る問題の対処法

AWSのサービスである、EC2とRDSを用いてローカルで作成したRailsのアプリをデプロイしようと作業を進めています。 https://qiita.com/naoki_mochizuki/items/5a1757d222806cbe0cd1 上記記事に従って rails db:create RAILS_ENV=production rails db:migrate RAILS_ENV=production を行ったのですがタイトルのエラーが発生・・・ エラー文 rails aborted! Mysql2::Error::ConnectionError: Access denied for user 'root'@'localhost' (using password: NO) /var/www/rails/アプリ名/bin/rails:9:in `<top (required)>' /var/www/rails/アプリ名/bin/spring:15:in `<top (required)>' bin/rails:3:in `load' bin/rails:3:in `<main>' Tasks: TOP => db:create (See full trace by running task with --trace) 試したこと ①$ mysql -u root -p の実行 MySQL Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.7.34 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 特に問題なく入れた。 ②config/database.ymlを開き、パスワードを記載 いつも使っているのがVSコードなので、VSコードにてdatabase.ymlを開く。 本番環境用のパスワードの欄が空欄だったため、記載する。 しかし全く同じエラー。どうして・・・・・? 結論 ローカル環境ではなく、「本番環境(EC2)」にてconfig/database.ymlを開き、パスワードを記載すると解決します! (ローカル環境にてdatabase.ymlを編集していたので、本番環境への影響がなく、全く意味のないことをしていたようです。。) 手順(注意:すべて本番環境の中で実行する) asami|アプリ名 $ vi config/database.yml database.yml production: <<: *default database: アプリ名_production username: root password: XXXXX # ←ここに記述 無事に通りました! 本当に単純すぎる間違いでかなり時間をロスしました・・・・・
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

SQLでcaseを使う

caseを使ってみよう ユーザ情報テーブルと、購入履歴テーブルを結合し、 購入回数ごとにランキングを付ける。 5回以上 ランクA 2回以上 ランクB 1回未満 ランクC select users.id,count(*), case when count(*) >= 5 then 'A' when count(*) >= 2 then 'B' else 'C' end as user_rank from users join orders on users.id = orders.user_id group by users.id; end は必須! ★忘れやすいので注意★ as でカラム名を付ける nullの値を0にしてみよう select products.id,products.name, case when sum(order_details.product_qty) is null then 0 else sum(order_details.product_qty) end as num from products left outer join order_details on products.id = order_details.product_id group by products.id left join を使うとnullになることがあり、nullだと都合が悪いときに0を入れておく。
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む