SQL Error [Column not found]

[sitenzo]

Hello,

when i use this script (version 1.1.1) and use the following code i get a DB error

Do i need to create users in the DB first or how does it works?

use PhpTelegramBot\Laravel\PhpTelegramBotContract;
class TelegramWebhookController extends Controller
{
    public function HandleWebhook(PhpTelegramBotContract $telegramBot)
    {
        $telegramBot->handle();

    }

2020-01-11 19:43:46] local.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'first_name' in 'field list' {"exception":"[object]
(Longman\\TelegramBot\\Exception\\TelegramException(code: 0): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'first_name' in 'field list' at 
/home/{USER}/domains/{DOMAIN}/laravel/vendor/longman/telegram-bot/src/DB.php:498)

i'm using laravel 6.10 Here are my settings

(
    [version:protected] => 0.60.0
    [api_key:protected] => *****
    [bot_username:protected] => *****
    [bot_id:protected] => *****
    [input:protected] => 
    [commands_paths:protected] => Array
        (
            [0] => /home/{user}/domains/{domain}/laravel/vendor/longman/telegram-bot/src/Commands/SystemCommands
        )

    [update:protected] => 
    [upload_path:protected] => 
    [download_path:protected] => 
    [mysql_enabled:protected] => 1
    [pdo:protected] => PDO Object
        (
        )

    [commands_config:protected] => Array
        (
        )

    [admins_list:protected] => Array
        (
        )

    [last_command_response:protected] => 
    [run_commands:protected] => 
    [getupdates_without_database:protected] => 
    [last_update_id:protected] => 
)

[sitenzo]

found the problem, Migration files are incomplete with current version of longman/telegram

[noplanman]

Migration scripts should be correct, they just need to be executed one after another when upgrading.

~Or can you point out which is incomplete?~

Ah, I see, you were talking about the migration scripts in this repo, not the SQL scripts in the core repository 👌

[nullcookies]

[2020-02-14 08:21:36] local.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'first_name' in 'field list' {"exception":"[object] (Longman\TelegramBot\Exception\TelegramException(code: 0): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'first_name' in 'field list' at \vendor\longman\telegram-bot\src\DB.php:498) [stacktrace]

Same problem

[nullcookies]

A lot of fields are missing in the tables (chat, message, message)

Example migration to laravel https://github.com/php-telegram-bot/laravel/blob/master/src/database/migrations/2018_04_18_193554_create_telegram_update_table.php

custom DB https://github.com/php-telegram-bot/core/blob/master/structure.sql#L228-L230

[dehghanimehrdad]

I had this problem updating, I'm not sure how exact this is but I fixed it using below code, I had several bots so I wrote it:

ALTER TABLE chat ADD first_name TEXT NULL AFTER username, ADD last_name TEXT NULL AFTER first_name;

ALTER TABLE message ADD forward_signature TEXT NULL AFTER connected_website, ADD author_signature TEXT NULL AFTER forward_signature, ADD forward_sender_name TEXT NULL AFTER author_signature, ADD passport_data TEXT NULL AFTER forward_sender_name, ADD edit_date DATETIME NULL AFTER passport_data, ADD caption_entities TEXT NULL AFTER edit_date, ADD animation TEXT NULL AFTER caption_entities, ADD game TEXT NULL AFTER caption_entities, ADD poll TEXT NULL AFTER caption_entities, ADD invoice TEXT NULL AFTER caption_entities, ADD successful_payment TEXT NULL AFTER caption_entities, ADD reply_markup TEXT NULL AFTER caption_entities;

ALTER TABLE telegram_update ADD poll_id BIGINT(20) NULL AFTER edited_message_id, ADD pre_checkout_query_id BIGINT(20) NULL AFTER poll_id, ADD shipping_query_id BIGINT(20) NULL AFTER pre_checkout_query_id, ADD edited_channel_post_id BIGINT(20) NULL AFTER shipping_query_id, ADD channel_post_id BIGINT(20) NULL AFTER edited_channel_post_id;

ALTER TABLE callback_query ADD chat_instance CHAR(255) NULL AFTER data, ADD game_short_name CHAR(255) NULL AFTER data;

[noplanman]

You can always use the update scripts in the core repository here: https://github.com/php-telegram-bot/core/tree/master/utils/db-schema-update

The dedicated eloquent migration scripts got forgotten when updating core, which will be fixed up.

[asafov]

Please don't forget to use prefix with migrations.

[asafov]

I can add missed migrations. Can start do it right now.

[asafov]

Added table prefix support for migrations, working on migrations.

[noplanman]

Hi @asafov, what are the odds? I literally wrote the missing migrations yesterday, so I hope you haven't put too much work into it yet!!

I've opened a PR (#23) that we can look at together and discuss things there :+1:

[asafov]

@noplanman f**k... I commited my version, check it please (PR #24 ).

Diff with your version:

1. You don't use table prefix for migrations, i use it (And for foreign_keys too)
2. I use dropIfExists method instead drop
3. You use per-version migrations, i use per-table and per-actions.
4. I use timestamp instead datetime for new tables, for old new raw sql to replace.

Do you have any instant messenger for fast-comminicate?