Duplicate key name "bill data bill id timestamp index"

Help
1

Duplicate key name bill_data_bill_id_timestamp_index

Hi Folks,
I am a bit lost and the conversation on GitHub is also not making much progress.
I am a bit concerned about screwing up billing data when doing some uneducated guess :wink:
Maybe someone is having an idea how to get it resolved?

Thank you!

sudo -u librenms /opt/librenms/validate.php

Component Version
LibreNMS 24.1.0-68-gd75c80b9b (2024-02-06T20:29:24+01:00)
DB Schema 2024_01_09_223917_bill_data_new_primary (287)
PHP 8.2.15
Python 3.11.2
Database MariaDB 10.11.6-MariaDB
RRDTool 1.7.2
SNMP 5.9.3
===========================================

[OK] Composer Version: 2.6.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Your database is out of date!
[FIX]:
./lnms migrate
Attempt to fix this issue (y or n)?:y

In Connection.php line 822:

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘bill_data_bill_id_timestamp_index’ (Connection: mysql, SQL: alter table bill_data add index bill_data_bill_id_timestamp_index(bill_id, timestamp))

In Connection.php line 580:

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘bill_data_bill_id_timestamp_index’

sudo -u librenms ./lnms migrate

                                                                                                               APPLICATION IN PRODUCTION.

┌ Are you sure you want to run this command? ──────────────────┐
│ Yes │
└──────────────────────────────────────────────────────────────┘

INFO Running migrations.

2024_01_09_223927_bill_data_updated_indexes … 7ms FAIL

In Connection.php line 822:

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘bill_data_bill_id_timestamp_index’ (Connection: mysql, SQL: alter table bill_data add index bill_data_bill_id_timestamp_index(bill_id, timestamp))

In Connection.php line 580:

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘bill_data_bill_id_timestamp_index’

sudo -u librenms /opt/librenms/lnms migrate --force -vvv
SQL[select table_name as name, (data_length + index_length) as size, table_comment as comment, engine as engine, table_collation as collation from information_schema.tables where table_schema = ‘libre_db’ and table_type in (‘BASE TABLE’, ‘SYSTEM VERSIONED’) order by table_name 5.95ms]

SQL[select table_name as name, (data_length + index_length) as size, table_comment as comment, engine as engine, table_collation as collation from information_schema.tables where table_schema = ‘libre_db’ and table_type in (‘BASE TABLE’, ‘SYSTEM VERSIONED’) order by table_name 5.92ms]

SQL[select migration from migrations order by batch asc, migration asc 5.21ms]

SQL[select migration from migrations order by batch asc, migration asc 2.81ms]

SQL[select max(batch) as aggregate from migrations 0.65ms]

INFO Running migrations.

2024_01_09_223927_bill_data_updated_indexes … 9ms FAIL
SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘bill_data_bill_id_timestamp_index’ (Connection: mysql, SQL: alter table bill_data add index bill_data_bill_id_timestamp_index(bill_id, timestamp)) {“exception”:“[object] (Illuminate\Database\QueryException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘bill_data_bill_id_timestamp_index’ (Connection: mysql, SQL: alter table bill_data add index bill_data_bill_id_timestamp_index(bill_id, timestamp)) at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:822)
[previous exception] [object] (PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘bill_data_bill_id_timestamp_index’ at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:580)”}

In Connection.php line 822:

[Illuminate\Database\QueryException (42000)]
SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘bill_data_bill_id_timestamp_index’ (Connection: mysql, SQL: alter table bill_data add index bill_data_bill_id_timestamp_index(bill_id, timestamp))

Exception trace:
at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:822
Illuminate\Database\Connection->runQueryCallback() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:776
Illuminate\Database\Connection->run() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:569
Illuminate\Database\Connection->statement() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:110
Illuminate\Database\Schema\Blueprint->build() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:555
Illuminate\Database\Schema\Builder->build() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:397
Illuminate\Database\Schema\Builder->table() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:355
Illuminate\Support\Facades\Facade::__callStatic() at /opt/librenms/database/migrations/2024_01_09_223927_bill_data_updated_indexes.php:14
Illuminate\Database\Migrations\Migration@anonymous/opt/librenms/database/migrations/2024_01_09_223927_bill_data_updated_indexes.php:7$1db->up() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:493
Illuminate\Database\Migrations\Migrator->runMethod() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:410
Illuminate\Database\Migrations\Migrator->Illuminate\Database\Migrations{closure}() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:419
Illuminate\Database\Migrations\Migrator->runMigration() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:216
Illuminate\Database\Migrations\Migrator->Illuminate\Database\Migrations{closure}() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/View/Components/Task.php:37
Illuminate\Console\View\Components\Task->render() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:756
Illuminate\Database\Migrations\Migrator->write() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:216
Illuminate\Database\Migrations\Migrator->runUp() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:181
Illuminate\Database\Migrations\Migrator->runPending() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:124
Illuminate\Database\Migrations\Migrator->run() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:90
Illuminate\Database\Console\Migrations\MigrateCommand->Illuminate\Database\Console\Migrations{closure}() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:633
Illuminate\Database\Migrations\Migrator->usingConnection() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:83
Illuminate\Database\Console\Migrations\MigrateCommand->handle() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:36
Illuminate\Container\BoundMethod::Illuminate\Container{closure}() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Util.php:41
Illuminate\Container\Util::unwrapIfClosure() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:93
Illuminate\Container\BoundMethod::callBoundMethod() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:35
Illuminate\Container\BoundMethod::call() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Container.php:662
Illuminate\Container\Container->call() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/Command.php:211
Illuminate\Console\Command->execute() at /opt/librenms/vendor/symfony/console/Command/Command.php:326
Symfony\Component\Console\Command\Command->run() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/Command.php:180
Illuminate\Console\Command->run() at /opt/librenms/vendor/symfony/console/Application.php:1096
Symfony\Component\Console\Application->doRunCommand() at /opt/librenms/vendor/symfony/console/Application.php:324
Symfony\Component\Console\Application->doRun() at /opt/librenms/vendor/symfony/console/Application.php:175
Symfony\Component\Console\Application->run() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:201
Illuminate\Foundation\Console\Kernel->handle() at /opt/librenms/app/Console/Kernel.php:66
App\Console\Kernel->handle() at /opt/librenms/lnms:38

In Connection.php line 580:

[PDOException (42000)]
SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘bill_data_bill_id_timestamp_index’

Exception trace:
at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:580
PDOStatement->execute() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:580
Illuminate\Database\Connection->Illuminate\Database{closure}() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:809
Illuminate\Database\Connection->runQueryCallback() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:776
Illuminate\Database\Connection->run() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:569
Illuminate\Database\Connection->statement() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:110
Illuminate\Database\Schema\Blueprint->build() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:555
Illuminate\Database\Schema\Builder->build() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:397
Illuminate\Database\Schema\Builder->table() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:355
Illuminate\Support\Facades\Facade::__callStatic() at /opt/librenms/database/migrations/2024_01_09_223927_bill_data_updated_indexes.php:14
Illuminate\Database\Migrations\Migration@anonymous/opt/librenms/database/migrations/2024_01_09_223927_bill_data_updated_indexes.php:7$1db->up() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:493
Illuminate\Database\Migrations\Migrator->runMethod() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:410
Illuminate\Database\Migrations\Migrator->Illuminate\Database\Migrations{closure}() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:419
Illuminate\Database\Migrations\Migrator->runMigration() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:216
Illuminate\Database\Migrations\Migrator->Illuminate\Database\Migrations{closure}() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/View/Components/Task.php:37
Illuminate\Console\View\Components\Task->render() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:756
Illuminate\Database\Migrations\Migrator->write() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:216
Illuminate\Database\Migrations\Migrator->runUp() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:181
Illuminate\Database\Migrations\Migrator->runPending() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:124
Illuminate\Database\Migrations\Migrator->run() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:90
Illuminate\Database\Console\Migrations\MigrateCommand->Illuminate\Database\Console\Migrations{closure}() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:633
Illuminate\Database\Migrations\Migrator->usingConnection() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:83
Illuminate\Database\Console\Migrations\MigrateCommand->handle() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:36
Illuminate\Container\BoundMethod::Illuminate\Container{closure}() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Util.php:41
Illuminate\Container\Util::unwrapIfClosure() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:93
Illuminate\Container\BoundMethod::callBoundMethod() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:35
Illuminate\Container\BoundMethod::call() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Container.php:662
Illuminate\Container\Container->call() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/Command.php:211
Illuminate\Console\Command->execute() at /opt/librenms/vendor/symfony/console/Command/Command.php:326
Symfony\Component\Console\Command\Command->run() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/Command.php:180
Illuminate\Console\Command->run() at /opt/librenms/vendor/symfony/console/Application.php:1096
Symfony\Component\Console\Application->doRunCommand() at /opt/librenms/vendor/symfony/console/Application.php:324
Symfony\Component\Console\Application->doRun() at /opt/librenms/vendor/symfony/console/Application.php:175
Symfony\Component\Console\Application->run() at /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:201
Illuminate\Foundation\Console\Kernel->handle() at /opt/librenms/app/Console/Kernel.php:66
App\Console\Kernel->handle() at /opt/librenms/lnms:38

on cli

MariaDB [libre_db]> select column_name as name, data_type as type_name, column_type as type, collation_name as collation, is_nullable as nullable, column_default as default, column_comment as comment, extra as extra from information_schema.columns where table_schema = ‘libre_db’ and table_name = ‘bill_data’ order by ordinal_position asc ;
±----------±----------±--------------------±----------±---------±--------±--------±---------------+
| name | type_name | type | collation | nullable | default | comment | extra |
±----------±----------±--------------------±----------±---------±--------±--------±---------------+
| id | bigint | bigint(20) unsigned | NULL | NO | NULL | | auto_increment |
| bill_id | int | int(10) unsigned | NULL | NO | NULL | | |
| timestamp | datetime | datetime | NULL | NO | NULL | | |
| period | int | int(11) | NULL | NO | NULL | | |
| delta | bigint | bigint(20) | NULL | NO | NULL | | |
| in_delta | bigint | bigint(20) | NULL | NO | NULL | | |
| out_delta | bigint | bigint(20) | NULL | NO | NULL | | |
±----------±----------±--------------------±----------±---------±--------±--------±---------------+
7 rows in set (0.004 sec)

MariaDB [libre_db]> alter table bill_data add index bill_data_bill_id_timestamp_index(bill_id, timestamp);
ERROR 1061 (42000): Duplicate key name ‘bill_data_bill_id_timestamp_index’

2

Do show create table bill_data;

3

Hi laf,
many thanks!

MariaDB [libre_db]> show create table bill_data;
±----------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±----------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bill_data | CREATE TABLE bill_data (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
bill_id int(10) unsigned NOT NULL,
timestamp datetime NOT NULL,
period int(11) NOT NULL,
delta bigint(20) NOT NULL,
in_delta bigint(20) NOT NULL,
out_delta bigint(20) NOT NULL,
PRIMARY KEY (id),
KEY bill_data_bill_id_index (bill_id),
KEY bill_data_bill_id_timestamp_index (bill_id,timestamp)
) ENGINE=InnoDB AUTO_INCREMENT=6792936 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
±----------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

4

Looks like the schema changes have already been run the migrations aren’t showing correctly.

I don’t know if this is the right way to sort it but you can do:

select * from migrations;

You shouldn’t see 2024_01_09_223927_bill_data_updated_indexes within that output.

Hopefully this is the last you see (ignore the first and third columns)

| 287 | 2024_01_09_223917_bill_data_new_primary | 5 |

If so, do:

insert into migrations set migration='2024_01_09_223927_bill_data_updated_indexes', batch=X;

Where X = the next incremental number from the 3rd column above, I.e mine would be 6.

5

laf, You are the BEST!
Thank you!!!

MariaDB [libre_db]> select * from migrations;

| 285 | 2024_01_08_223812_custom_map_node_image | 56 |
| 286 | 2024_01_09_211518_custom_map_node_maplink | 56 |
| 287 | 2024_01_09_223917_bill_data_new_primary | 57 |
±----±---------------------------------------------------------------------------±------+
287 rows in set (0.001 sec)

MariaDB [libre_db]> insert into migrations set migration=‘2024_01_09_223927_bill_data_updated_indexes’, batch=58;
Query OK, 1 row affected (0.001 sec)

sudo -u librenms /opt/librenms/validate.php

Component Version
LibreNMS 24.1.0-68-gd75c80b9b (2024-02-06T20:29:24+01:00)
DB Schema 2024_01_09_223927_bill_data_updated_indexes (288)
PHP 8.2.15
Python 3.11.2
Database MariaDB 10.11.6-MariaDB
RRDTool 1.7.2
SNMP 5.9.3
===========================================

[OK] Composer Version: 2.6.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database Schema is current
[OK] SQL Server meets minimum requirements
[OK] lower_case_table_names is enabled
[OK] MySQL engine is optimal
[OK] Database and column collations are correct
[OK] Database schema correct
[OK] MySQL and PHP time match
[OK] Active pollers found
[OK] Dispatcher Service is enabled
[OK] Locks are functional
[OK] No active python wrapper pollers found
[WARN] Using database for locking, you should set CACHE_DRIVER=redis
[OK] rrdtool version ok
[OK] Connected to rrdcached
root@thor-odd:/home/wriedel# sudo -u librenms /opt/librenms/daily.sh
Updating to latest codebase OK
Updating Composer packages OK
Updating SQL-Schema OK
Updating submodules OK
Cleaning up DB OK
Fetching notifications OK
Caching PeeringDB data OK

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.