Posting to Telegram channel by bot using mysql, python

Idea to create channel in the telegram came me from my friend. I have a lot of  useful electronic materials about programming, and he offered me to share them in telegram channel. To create channel is very easy. But share materials manually is very boring and wasting time. That’s why I’ve decided to automate it using mysql and python. First of all I’ve created table in the mysql to store my books there. And then i’ve created my first bot in telegram. Documentation of telegram bots: https://core.telegram.org/bots

Also you can find API documentation of telegram bots here: https://core.telegram.org/bots/api

And than was created channel. You can learn how to create channel from here: https://telegram.wiki/generalfaq/channels

So interesting part is a python code to work with telegram api. You can find many written libraries in internet written in python to work with telegram API. But I wanna show how to write simple script from zero. Table structure:

CREATE TABLE `books` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
`content` longblob,
`author` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`description` text COLLATE utf8mb4_unicode_ci,
`image` blob,
`size` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`year` int(11) unsigned DEFAULT NULL,
`extension` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`content_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`content_length` int(20) unsigned DEFAULT ‘0’,
`last_modified` int(11) unsigned DEFAULT ‘0’,
`is_shared` tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`id`),
UNIQUE KEY `book_download` (`book_download`),
KEY `year` (`year`),
KEY `is_shared` (`is_shared`),
KEY `last_modified` (`last_modified`),
KEY `content_length` (`content_length`),
KEY `content_type` (`content_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

So let’s assume that our books are stored in the table already and pending to be shared.

So let’s write our poster class step by step. Required python libraries are requests, pymysql and re. We will use python3. Let’s create project folder and then virtualenv and install required libraries

mkdir telegram_bot
> cd telegram_bot
> virtualenv .py3
> source .py3/bin/activate
> pip install requests pymysql
> touch telegram_bot.py

Now, let’s create our first class

import requests as rq
import pymysql as mysql
import re

class poster:
   _token = "****"
   _url = "https://api.telegram.org/bot"
   _channel_id = "@mychannel"
   _conn = None
   def __init__(self):
      self._url += self._token
      self._conn = mysql.connect(host="localhost", user="user", password="password", db="books", charset="utf8")

   def __exit__(self, exc_type, exc_val, exc_tb):
      self._conn.close()

As you see in example we connect to mysql db on initialization, and close on destroying o object. Now let’s add send_book method:

def send_book(self):
   strsql = """select id, 
                      title, 
                      author, 
                      description, 
                      image, 
                      year, 
                      content,
                      extension
                 from books 
                      limit 2
                order by year desc"""

   cur = self._conn.cursor()
   if cur.execute(strsql):
      for id, title, author, description, image, year, content, extension, in cur:
         self.post_text(description)
         self.post_document(title, extension, author, year, content)
         self.post_image(image, title)

In this method we choose 2 books from database, and then call three other methods in the loop: post_text, post_document, post_image

These methods uses there types of bot API of telegram: sendMessage, sendDocument and sendPhoto

Let’s define next methods:

def post_text(self, description):
   method = self._url + "/sendMessage"
   r = rq.post(method, data={
          "chat_id": channel_id,
          "text": description
       })
   
   if r.status_code != 200:
      raise Exception("post_text error")
def post_photo(self, image, title):
   method = self._url + "/sendPhoto"
   r = rq.post(method, data={
          "chat_id": channel_id,
          "photo": image,
          "caption": title
       })

    if r.status_code != 200:
       raise Exception("post_photo error")
def post_document(self, title, extension, author, year, content):
   method = self._url + "/sendDocument"
   filename = title + "-" + author + "-" + year
   filename = re.sub(r'[^a-zA-Z0-9]+', '', filename)
   filename += "." + extension
   r = rq.post(method, data={
          "chat_id": channel_id,
          "caption": title + ", " + author + ", " + year,
       },
       files={
          "document":(filename, content)
       })
   if r.status_code != 200:
      raise Exception("post_document error")

And last step is to create single script file, import our module and connect this script to cron to share books periodically.

cron.py
-----------------
from telegram_bot import poster

p = poster()
p.send_book()
cron.sh
------------------

#!/bin/bash

cd ~/telegram_bot
source .py3/bin/activate
python cron.py

That’s all 🙂

Advertisements

Wrong binlog size reported after moving binlogs on MySQL version 5.6

Some days ago we found very interesting bug report with my friend: http://bugs.mysql.com/bug.php?id=71879

Problem is that, if a client moves binlog files to the other folder, stops MySQL server, update in the config file bin_log and bin_log_index values for a new path and starts server again, then the result of SHOW BINARY LOG command will be wrong:

mysql> show binary logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| cluster-repo-bin.000001 |       120 |
| cluster-repo-bin.000002 |         0 |
| cluster-repo-bin.000003 |         0 |
| cluster-repo-bin.000004 |         0 |
| cluster-repo-bin.000005 |         0 |
| cluster-repo-bin.000006 |       120 |
+-------------------------+-----------+

As you see, the size of some binlog files are zero. So why? 😮

If you dive into the mysql internals, you can see these lines of codes in the sql/rpl_master.cc:

/* The file ends with EOF or empty line */

while ((length=my_b_gets(index_file, fname, sizeof(fname))) > 1)

{

int dir_len;

ulonglong file_length= 0;                   // Length if open fails

fname[–length] = ‘\0’;                     // remove the newline

….

As you see, my_b_gets function reads index file of binlogs and assigns a real path of binlogs to the fname char array. When we debugged fname we detected that fname contains unreal path to the binlog files (for ex: ./mysql-bin.000001). For this reason the size calculator can’t give us real size of binlog files. To resolve this problem we added a few lines of code to correct the real path of files:

// bug fix for http://bugs.mysql.com/bug.php?id=71879

// if bin file path starts with ./ then we will prepend to it full path of binlog directory

if(fname[0] == ‘.’ && fname[1] == ‘/’)

{

std::string ofname(fname);

// get the new folder path of binlog files and assign to the bin_log_value variable.

std::string bin_log_value(mysql_bin_log.get_name());

// corrects the path of binlog file

std::size_t lpos = bin_log_value.find_last_of(“/”);

std::string dir_path = bin_log_value.substr(0, lpos);

std::string full_path = dir_path + ofname.substr(1, ofname.length());

strcpy(fname, full_path.c_str());

}

Bug fix – Error -1 from storage engine while creating table with non-existing datadir on MySQL version 5.6

According to this bug report http://bugs.mysql.com/bug.php?id=79151

when client tries to create a new table with not existing (or write permission denied) datadir, then he gets an error message like this: ERROR 1030 (HY000): Got error -1 from storage engine

As you see it is not informative error message, we don’t know what’s wrong at the backend. But in the version 5.7 it seems this problem was resolved already.

I discovered that at the low level of innodb storage engine, program tries to create subfolders, when occurs a problem it checks system error partly in the file storage/innobase/fil/fil0fil.cc:

success = os_file_create_subdirs_if_needed(path);

os_file_create_subdirs_if_needed function in the storage/innobase/os/os0file.cc file checks system errors but returns only boolean value depending on the result:

/*Creates all missing subdirectories along the given path.

@returnTRUE if call succeeded FALSE otherwise */

UNIV_INTERN

ibool

os_file_create_subdirs_if_needed(

/*=============================*/

const char*path)/*!< in: path name */

{

if (srv_read_only_mode) {

ib_logf(IB_LOG_LEVEL_ERROR,

“read only mode set. Can’t create subdirectories ‘%s'”,

path);

return(FALSE);

}

char*subdir = os_file_dirname(path);

if (strlen(subdir) == 1

    && (*subdir == OS_FILE_PATH_SEPARATOR || *subdir == ‘.’)) {

/* subdir is root or cwd, nothing to do */

mem_free(subdir);

return(TRUE);

}

/* Test if subdir exists */

os_file_type_ttype;

iboolsubdir_exists;

iboolsuccess = os_file_status(subdir, &subdir_exists, &type);

if (success && !subdir_exists) {

/* subdir does not exist, create it */

success = os_file_create_subdirs_if_needed(subdir);

if (!success) {

mem_free(subdir);

return(FALSE);

}

success = os_file_create_directory(subdir, FALSE);

}

mem_free(subdir);

return(success);

}

Depends on the value of boolean variable success in the fil0fil.cc file, program sends to the user unclear message. For this reason I added to the fil0fil.cc file a few lines of codes to resolve this problem:

// checking success variable value if false and global errno variable has a value

if(!success && errno) {

// sends to the client custom message with system error number

my_error(ER_GET_ERRNO, MYF(0), errno);

mem_free(path);

return(DB_ERROR);

}

MySQL doesn’t show informative error message for read-only filesystem in Linux on version 5.6

My first bug fix was about a year ago. You can see bug report here: http://bugs.mysql.com/bug.php?id=72259

If to shortly explain, so problem was that when you make file system read only, and try to start the server, it fails. And there is no exact error message in the log files which explains the problem correctly.

 

I added to the sql/mysqld.cc file a simple function whitch checks the given path’s permission and returns the state code:

// checks if file system is read-only

int is_filesystem_read_only(char const* name) {

    if (access(name, W_OK) == -1) {

        if (access(name, R_OK) == 0) {

            return R_OK; // read only

        } else if (access(name, F_OK) == 0) {

            return F_OK; // file exists but have not any access

        } else {

            return -1; // file does not exist

        }

    } else {

        return W_OK; // read/write

    }

}

Then in the mysqld_main function where everythink starts, I got a value of datadir parameter and pass to the is_filesystem_read_only function, then I check a state code. According to this state code I write a custom informative message to the log file. Codes are below:

// Get –datadir value to check if filesystem is read-only

  for (int i = 1; i < argc; i++) {

      if (strstr(argv[i], “–datadir”)) {

          std::string str(argv[i]);

          char const* dataDirPath = str.substr(10, str.length()).c_str();

          if (is_filesystem_read_only(dataDirPath) == R_OK) {

              my_message_local(ERROR_LEVEL, “File system (%s) is read-only”, dataDirPath);

              return 1;

          }

          break;

      }

  }

Bug fixed 🙂