Categories
Building CodeKN.com Coding Session Golang Videos

Golang GORM: Set size for index columns, automate table alter scripts (Coding Session)

In this video session, I am working on CodeKN.com and, in particular, its URL Meta Parser worker. During the coding session, I will set the size for MySQL index fields using Golang GORM models, automate table alter execution for setting collation on the table level, and also debug the application to find errors with page data parser implementation.

Topics:

  • Review MySQL Indexes for URLLink and URLImage models/tables.
  • Finish work on URL Meta Worker.

What’s done:

  • Review field/column size for URL and title columns.
  • Alter tables to have proper collation.
  • Automate alter queries when the database regenerated using db.Exec on GORM instance.

This session is a follow-up to my previous live streaming here:

Set collation on the table level

ALTER TABLE
    urls_url_metas
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE
    urls_url_links
    CONVERT TO CHARACTER SET utf8mb4
        COLLATE utf8mb4_unicode_ci;

GORM: Set size for string fields

type URLLink struct {
	CreatedAt time.Time
	UpdatedAt time.Time
	DeletedAt *time.Time `sql:"index"`

	URL       string `gorm:"unique_index:idx_url;not null"`
	LinkURL   string `gorm:"unique_index:idx_url;not null"`
	LinkTitle string `gorm:"size:5000"`
}

func (URLLink) TableName() string {
	return TablePrefix + "url_links"
}

type URLImage struct {
	CreatedAt time.Time
	UpdatedAt time.Time
	DeletedAt *time.Time `sql:"index"`

	URL        string `gorm:"unique_index:idx_url;not null"`
	ImageURL   string `gorm:"unique_index:idx_url;not null"`
	ImageTitle string `gorm:"size:5000"`
}

func (URLImage) TableName() string {
	return TablePrefix + "url_images"
}

The code snippet above was the initial version. As you can see I set size for some key columns to 5000 which is wrong. More on this above. But for now here is the fixed version.

type URLLink struct {
	CreatedAt time.Time
	UpdatedAt time.Time
	DeletedAt *time.Time `sql:"index"`

	URL       string `gorm:"unique_index:idx_url;not null;size:255"`
	LinkURL   string `gorm:"unique_index:idx_url;not null;size:255"`
	LinkTitle string `gorm:"size:255"`
}

func (URLLink) TableName() string {
	return TablePrefix + "url_links"
}

type URLImage struct {
	CreatedAt time.Time
	UpdatedAt time.Time
	DeletedAt *time.Time `sql:"index"`

	URL        string `gorm:"unique_index:idx_url;not null;size:255"`
	ImageURL   string `gorm:"unique_index:idx_url;not null;size:255"`
	ImageTitle string `gorm:"size:255"`
}

func (URLImage) TableName() string {
	return TablePrefix + "url_images"
}

Now, I set the URL size to 255, which is the recommended limit for MySQL key columns.

Docker: How to start MySQL container

Start a new MySQL container with the specified username, password, and scheme name. The root password is set separately.

docker run -d --name codekn-mysql -p 3306:3306 -e MYSQL_USER=codekn -e MYSQL_PASSWORD=codekn -e MYSQL_DATABASE=codekn -e MYSQL_ROOT_PASSWORD=codekn mysql:5.7.25

I also have the following set of Makefile targets to start, stop, recreate MySQL containers:

# MYSQL

.PHONY: local/mysql/resume
local/mysql/resume:
	docker start codekn-mysql

.PHONY: local/mysql/start
local/mysql/start:
	docker run -d --name codekn-mysql -p 3306:3306 -e MYSQL_USER=codekn -e MYSQL_PASSWORD=codekn -e MYSQL_DATABASE=codekn -e MYSQL_ROOT_PASSWORD=codekn mysql:5.7.25

.PHONY: local/mysql/stop
local/mysql/stop:
	docker stop codekn-mysql

.PHONY: local/mysql/destroy
local/mysql/destroy: local/mysql/stop
	docker rm codekn-mysql

.PHONY: local/mysql/recreate
local/mysql/recreate: local/mysql/destroy local/mysql/start
	echo "codekn-mysql recreated"

Error: Specified key was too long; max key length is 3072 bytes

This error occurred when I set to big size for my key MySQL columns (URL, Title).

Error when applying collation related alter to the table:

“Specified key was too long; max key length is 3072 bytes”

https://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes

What to follow-up

I will work on these items as a follow up:

  • Pagedata parser: join links (abc.com and /about -> abc.com/about).
  • Pagedata parser: fix parsing title for the links (and probably also for the images).
  • Pagedata parser: fix issue with repeating images.

In this session, I referred to HTML tokenizer a lot. Actually, the main fixes for URL parser will be in this part. You can read more about the logic behind page data parser in this article:

By Kanan Rahimov

Sr. Software Engineer

Leave a Reply