singlestore-labs / singlestoredb-laravel-driver Goto Github PK

Hey all,

I'm working with the S2 team, and we've run into huge performance issues when querying on multiple JSON fields in S2 (see Support ticket #27009)

Example:

WHERE
JSON_EXTRACT_STRING(`value`, 'lccontent') = 'paid' OR
JSON_EXTRACT_STRING(`value`, 'lccontent') = 'cpm' OR
JSON_EXTRACT_STRING(`value`, 'lccontent') = 'cps' OR
JSON_EXTRACT_STRING(`value`, 'lccontent') = 'cpc'

With the JSON improvements in version 8, we can replace that with something like this, which runs at 1.25s without load:

JSON_MATCH_ANY(MATCH_PARAM_STRING_STRICT() LIKE '%paid%', `value`, 'lccontent') OR
JSON_MATCH_ANY(MATCH_PARAM_STRING_STRICT() LIKE '%cpm%', `value`, 'lccontent') OR
JSON_MATCH_ANY(MATCH_PARAM_STRING_STRICT() LIKE '%cps%', `value`, 'lccontent') OR
JSON_MATCH_ANY(MATCH_PARAM_STRING_STRICT() LIKE '%cpc%', `value`, 'lccontent')

However, the Laravel driver doesn't currently support JSON_MATCH_ANY out of the box.

I've gotten it working on my system, using the following changes to SingleStore\Laravel\Query\Grammar

Functions to determine if we should use this new code:

/**
 * Get the SingleStore engine we're running on
 *
 * @param Builder $query
 * @return string
 */
private function getVersion(Builder $query)
{
    return Str::after($query->getConnection()->getPdo()->getAttribute(PDO::ATTR_CLIENT_VERSION), ' ');
}

/**
 * Whether we should do the JSON override or not (isJsonSelector & v > 8)
 * 8.0+ has a number of improved JSON functions to speed up queries
 *
 * @param Builder $query
 * @param         $where
 * @return bool
 */
private function doJsonOverride(Builder $query, $where)
{
    return $this->isJsonSelector($where['column']) &&
        version_compare($this->getVersion($query), '8.0') >= 0;
}

We then create a function to wrap the default query with JSON_MATCH_ANY, swapping in MATCH_PARAM_* for the colum:

/**
 * Wrap the returned data in a JSON_MATCH_ANY query for S2 performance
 *
 * @param Builder $query
 * @param         $where
 * @param         $originalCallable
 * @return string
 */
protected function wrapJsonMatchAny(Builder $query, $where, $originalCallable)
{
    // Swap out our column, and call the base query
    $column = $where['column'];
    $where['column'] = DB::raw('SINGLESTORE_JSON_MATCH');
    $whereSQL = call_user_func($originalCallable, $query, $where);

    // Set up our JSON Query
    // -------------------------------------------

    // Break apart the column name from the JSON keypath.
    [$field, $path] = $this->wrapJsonFieldAndPath($column);

    // TODO: Get the matching type from the value
    $matchType = 'MATCH_PARAM_STRING_STRICT()';

    // Re-assemble and return
    // -------------------------------------------
    $whereSQL = str_replace('SINGLESTORE_JSON_MATCH', $matchType, $whereSQL);
    return "JSON_MATCH_ANY($whereSQL, {$field}{$path})";
}

Finally, we apply the override to each where* function that we want support for:

protected function whereBasic(Builder $query, $where)
{
    return $this->doJsonOverride($query, $where) ?
        $this->wrapJsonMatchAny($query, $where, 'parent::whereBasic') :
        parent::whereBasic($query, $where);
}

Let me know if a PR would be preferred.

Hello @aarondfrancis!

composer require --dev nunomaduro/larastan
vendor/bin/phpstan analyze -c vendor/nunomaduro/larastan/extension.neon -l 5 src/

You will discover things like

diff --git a/src/Connect/Connection.php b/src/Connect/Connection.php
index 5c3e52e..14007af 100644
--- a/src/Connect/Connection.php
+++ b/src/Connect/Connection.php
@@ -6,18 +6,17 @@
 namespace SingleStore\Laravel\Connect;

 use Illuminate\Database\MySqlConnection;
-use SingleStore\Laravel\Query;
-use SingleStore\Laravel\QueryGrammar;
-use SingleStore\Laravel\Schema;
-use SingleStore\Laravel\SchemaBuilder;
-use SingleStore\Laravel\SchemaGrammar;
+use SingleStore\Laravel\Query\Builder as QueryBuilder;
+use SingleStore\Laravel\Query\Grammar as QueryGrammar;
+use SingleStore\Laravel\Schema\Builder as SchemaBuilder;
+use SingleStore\Laravel\Schema\Grammar as SchemaGrammar;

 class Connection extends MySqlConnection
 {
     /**
      * Get a schema builder instance for the connection.
      *
-     * @return SchemaBuilder
+     * @return \SingleStore\Laravel\Schema\Builder
      */
     public function getSchemaBuilder()
     {
@@ -25,36 +24,40 @@ class Connection extends MySqlConnection
             $this->useDefaultSchemaGrammar();
         }

-        return new Schema\Builder($this);
+        return new SchemaBuilder($this);
     }

     /**
      * Get the default query grammar instance.
      *
-     * @return QueryGrammar
+     * @return \Illuminate\Database\Grammar
      */
     protected function getDefaultQueryGrammar()
     {
-        return $this->withTablePrefix(new Query\Grammar);
+        return $this->withTablePrefix(new QueryGrammar);
     }

     /**
      * Get the default schema grammar instance.
      *
-     * @return SchemaGrammar
+     * @return \Illuminate\Database\Grammar
      */
     protected function getDefaultSchemaGrammar()
     {
-        return $this->withTablePrefix(new Schema\Grammar);
+        return $this->withTablePrefix(new SchemaGrammar);
     }

     /**
      * Get a new query builder instance.
+     *
+     * @return \SingleStore\Laravel\Query\Builder
      */
     public function query()
     {
-        return new Query\Builder(
-            $this, $this->getQueryGrammar(), $this->getPostProcessor()
+        return new QueryBuilder(
+            $this,
+            $this->getQueryGrammar(),
+            $this->getPostProcessor()
         );
     }
 }

Do you like static analysis?

From reading the title, it may seem confusing. But here is the table example:

Schema::create('referral_commissions', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->foreignId('user_id')->shardKey();
    $table->foreignId('referral_id')->index();
    $table->integer('amount');
    $table->timestamp('created_at')->nullable();

    // Indexes
    $table->primary(['id', 'user_id']);
});

I'm actually trying to create this table with user_id as the shard key. According to SingleStore documentation, the shard key is a subset of the primary key. Taking this into consideration, I set the primary key as id, user_id.

But that does not work since Laravel always sets the increment value as the primary key. This migration is trying to create a table with two primary keys, leading to this SingleStore error:

General error: 1851 COLUMNAR indexes and SKIPLIST indexes cannot be used on the same table

Generated code:

CREATE TABLE
  `referral_commissions` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` bigint unsigned NOT NULL,
    `referral_id` bigint unsigned NOT NULL,
    `amount` int NOT NULL,
    `created_at` TIMESTAMP NULL,
    PRIMARY KEY `referral_commissions_id_user_id_primary` (`id`, `user_id`),
    INDEX `referral_commissions_referral_id_index` (`referral_id`),
    shard key (`user_id`)
  ) DEFAULT CHARACTER
SET
  utf8mb4 COLLATE 'utf8mb4_unicode_ci'

This problem does not seem to be related to the driver but to Laravel itself, which always sets the increment as the primary key. But I think it will be very hard to change this in the framework.

Maybe the SingleStore driver could prevent or control this kind of behavior.

When doing a UNION query, Laravel wraps each union in (). SingleStore doesn't support () around unions. Need to test this and see what exactly is the issue.

I've run into an issue with the way the SingleStore driver handles the following code block.

$orders = Order::select(['orders.make', 'orders.part_no'])
  ->where('orders.dealer_id', 1407)
  ->whereDate('orders.order_date', '>', now()->subWeek());

$shipments = Shipment::select(['shipments.make', 'shipments.part_no'])
  ->where('shipments.dealer_id', 1407)
  ->whereDate('shipments.shipped_date', '>', now()->subWeek());

$all = $orders->union($shipments);

$all->orderBy('part_no');

$all->paginate();

SingleStore driver generates the query as follows:

SELECT
FROM (select count() as aggregate
      from (SELECT
            FROM (SELECT *
                  FROM (select orders.make, orders.part_no
                        from scanitparts.orders
                        where orders.dealer_id = 1407
                          and date(orders.order_date) > 2023 - 12 - 04)
                  union
                  (select shipments.make, shipments.part_no
                   from scanitparts.shipments
                   where shipments.dealer_id = 1407
                     and date(shipments.shipped_date) > 2023 - 12 - 04))
            order by part_no asc) as temp_table)
order by part_no asc

When using the mysql driver the same code block generates the following query:

select count(*) as aggregate
from ((select `orders`.`make`, `orders`.`part_no`
       from `scanitparts`.`orders`
       where `orders`.`dealer_id` = 1407
         and date(`orders`.`order_date`) > '2023-12-04')
      union
      (select `shipments`.`make`, `shipments`.`part_no`
       from `scanitparts`.`shipments`
       where `shipments`.`dealer_id` = 1407
         and date(`shipments`.`shipped_date`) > '2023-12-04')
      order by `part_no` asc) as `temp_table`

The SingleStore driver is adding an extra Select From ... order by on the count() result, which then creates the error Unknown column 'part_no' in 'order clause'. I assume the SingleStore driver should function the same way the mysql driver does in this example?

It is not possible to use the index in a columnstore migration:

COLUMNAR indexes and SKIPLIST indexes cannot be used on the same table

Would it be possible to detect and default to hash indexes automatically?

Alternatively add a new hashIndex or usingHash similar to withoutPrimaryKey.

$table->sortKey();

$table->index(); // defaults to hash index
$table->index()->usingHash(); // or $table->index()->using('hash');
$table->hashIndex();

Laravel changed the way they create tables with primary keys.
I don't know the specifics but it's currently not possible to migrate anything if it contains a primary key.

When including a primary key on any column (in our case id) it throws an exception.

SQLSTATE[HY000]: General error: 1851 COLUMNAR indexes and SKIPLIST indexes cannot be used on the same table (Connection: singlestore, SQL: create table `users` (`id` char(26) not null, `first_name` longtext not null, primary key `users_id_primary`(`id`), sort key(`id` asc), primary key (`id`)) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

It doesn't work if there is a primary key defined in the migration.
It does work if a shardKey() is defined and no primary key is defined.

My application has a lot of frequent queries with specific where clauses. Learning about how to optimize the table for this kind of workload, I found that I would need to increase the number of segments, reducing the number of rows per segment.

I could do that by changing the global variable columnstore_segment_rows, but this would decrease a lot the compression amount for all tables, and this change is only needed for specific ones.

The global variable can be overwritten with this kind of syntax, setting the variable with the columstore index:

CREATE TABLE t (
    id INT, 
    SORT KEY (id) WITH (columnstore_segment_rows=100000)
);

But the Laravel driver does not support this kind of feature. It would be good to have something like:

$table->id()->sortKey()->with('columnstore_segment_rows', 100000);
// OR
$table->sortKey('id')->with('columnstore_segment_rows', 100000);

I can try to make a Pull Request fo this feature if it's applicable.

Source:
https://docs.singlestore.com/managed-service/en/create-a-database/physical-database-schema-design/procedures-for-physical-database-schema-design/configuring-the-columnstore-to-work-effectively.html

When creating the id withoutPrimarykey and then defining the primary key using primaryKey function throwing the following error.

Method SingleStore\Laravel\Schema\Blueprint::primaryKey does not exist.

Following is the full code

Schema::create("some_table", function (Blueprint $table) {
                $table->integer("id", true, true)->withoutPrimaryKey();
                .........
                $table->timestamps();

                $table->primaryKey("id");
                $table->shardKey('id');
            });

Just installed the package to test out singlestore on a local docker instance. I have the localhost cluster up and running, but when I changed my env connection to the singlestore driver, I get an error of "could not find driver." Singlestore studio and Tableplus are able to connect to the localhost cluster just fine.

Excited to try it out, but any thoughts? Thanks!

sitenote: I am using docker-compose and the host is correct. My environment variables are accurate.

# php artisan --version
Laravel Framework 9.23.0

.env

DB_CONNECTION=singlestore
DB_HOST=singlestore
DB_PORT=3306
DB_DATABASE=plutus
DB_USERNAME=root
DB_PASSWORD=*****

composer.lock

            "name": "singlestoredb/singlestoredb-laravel",
            "version": "v1.0.0",
            "source": {
                "type": "git",
                "url": "https://github.com/singlestore-labs/singlestoredb-laravel-driver.git",
                "reference": "2dd741545c83301be264d3849ed5018d10a59db0"
            },

When migrating a table with a very long primary key name (usually a set of 2 or more keys) Laravel generates a very long primary key name. This results in an exception in SingleStore saying it can't save a long primary key name.

But the thing is, the name is never used. It always defaults to be named PRIMARY.

Example
I've go the following migration:

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('redacted', function (Blueprint $table) {
            $table->ulid('redacted_1_id');
            $table->ulid('very_long_column_name_that_is_redacted_2_id');
            $table->timestamps();

            $table->primary([
                'redacted_1_id',
                'very_long_column_name_that_is_redacted_2_id',
            ]);
            $table->sortKey([
                'redacted_1_id',
                'very_long_column_name_that_is_redacted_2_id',
            ]);
        });
    }
};

When I run php artisan migrate I get the following exception:

 Illuminate\Database\QueryException 

  SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'redacted_redacted_1_id_very_long_column_name_that_is_redacted_2_id_primary' is too long (Connection: singlestore, SQL: create table `redacted` (`redacted_1_id` char(26) not null, `very_long_column_name_that_is_redacted_2_id` char(26) not null, `created_at` timestamp null, `updated_at` timestamp null, primary key `redacted_redacted_1_id_very_long_column_name_that_is_redacted_2_id_primary`(`redacted_1_id`, `very_long_column_name_that_is_redacted_2_id`), sort key(`redacted_1_id` asc, `very_long_column_name_that_is_redacted_2_id` asc)) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:795
    791▕         // If an exception occurs when attempting to run a query, we'll format the error
    792▕         // message to include the bindings with SQL, which will make this exception a
    793▕         // lot more helpful to the developer instead of just the database's errors.
    794▕         catch (Exception $e) {
  ➜ 795▕             throw new QueryException(
    796▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    797▕             );
    798▕         }
    799▕     }

The way I solve it now is to add "placeholder" as the second paramater in the $table->primary() method.

It now migrates and when I look at the table I can see that "placeholder" isn't used but it's named PRIMARY:

On another note, I'm not sure where the redacted_1_id index comes from, is that the sort key name?
If so, might be better to name it _SORTKEY or something for the sake of consistency.

I'm currently trying to run a migration on project, but it seems the driver is currently generating a wrong query. From what I understand it seems the "sparse" term is being added before the , while it should be added after the "add ... text null".

Migration code:

Schema::table('users', function (Blueprint $table) {
            $table->text('two_factor_secret')
                    ->after('password')
                    ->nullable()->sparse();

            $table->text('two_factor_recovery_codes')
                    ->after('two_factor_secret')
                    ->nullable()->sparse();
});

Generated query:

alter table `users` add `two_factor_secret` text null after `password` sparse, add `two_factor_recovery_codes` text null after `two_factor_secret` sparse

Error message:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sparse, add `two_factor_recovery_codes` text null after `two_factor_secret` spar' at line 1 (SQL: alter table `users` add `two_factor_secret` text null after `password` sparse, add `two_factor_recovery_codes` text null after `two_factor_secret` sparse)

Hi,

When attempting to run a series of tests that involve the RefreshDatabase trait, I am greeted with the following error:

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1706 Feature 'Dropping more than one table in a single query' is not supported by SingleStore.

This only applies within tests, as manually doing php artisan migrate:fresh doesn't cause this. However my tests all rely on refreshing.

I have this global scope set up for some of my eloquent models:

        static::addGlobalScope('ordered', function(Builder $builder) {
            $builder->orderBy('sort_order','asc');
        });

What happens here is that on every query that is ran for models of that kind, appends ORDER BY sort_order ASC. Mysql is fine with for instance this query:

UPDATE
	`table`
SET
	`field_1` = "test"
WHERE
	`field_2` IS NULL
ORDER BY
	`sort_order` ASC;

But SingleStore does not allow order by on update. Is there any solution to this, other than to remove the use of global scopes in the application?

Hi Guys,

Thanks for updating to support Laravel 10; just a quick one, but you still need to do a release for this update. Could you please publish this new version?

If there's a release I'm unaware of, I do appolagise.

CC @AdalbertMemSQL

Hello, I'm trying to replicate the /*+ MAX_EXECUTION_TIME(15000) */ behavior from MySQL, and the option that was recommended to me is to create a resource pool with a query timeout and use it for specific queries:

CREATE RESOURCE POOL my_pool WITH QUERY_TIMEOUT = 15;

SELECT * FROM <table> OPTION (resource_pool=my_pool);

Unfortunately I can't find a way to append OPTION (resource_pool=my_pool) to a query built with Eloquent.

Could you please add a method to support this? I can try to make a pull request but I'm not completely familiar with Eloquent's way of building queries yet so it may not be perfect.

Hello,

we have been encountering this error since we upgraded to SingleStore version 8.5.15:

Illuminate\Database\QueryException /cliRuntime.php in {closure}
SQLSTATE[HY000] [2002] Operation timed out (SQL: select * from `user` where `user`.`id` = 2251799845008727 limit 1)

The timeout appears randomly on different queries.

Environment :

Vapor : Lamba Serverless
PHP : 8.1
Laravel : 9.21
Cluster SingleStore Cloud (Helios) : 8.5.15
singlestoredb/singlestoredb-laravel : 1.5.4

Are you also experiencing the same error?

I'm trying to rename some columns and indexes but I'm running into some issue.

When trying to rename a column:

SQLSTATE[HY000]: General error: 1706 Feature 'ALTER TABLE CHANGE to modify column type' is not supported by SingleStore. Use MODIFY to change a column's type. (Connection: singlestore, SQL: ALTER TABLE product_attribute_options CHANGE attribute_value_id attribute_option_id CHAR(26) NOT NULL)

Schema::table('product_attribute_options', function (Blueprint $table) {
    $table->renameColumn('attribute_value_id', 'attribute_option_id');
});

When trying to rename an index:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index `order_line_warehouse_storage_location_reservations_primary` to `order_lin' at line 1 (Connection: singlestore, SQL: alter table `order_line_storage_location_reservations` rename index `order_line_warehouse_storage_location_reservations_primary` to `order_line_storage_location_reservations_primary`)

Schema::table('order_line_storage_location_reservations', function (Blueprint $table) {
    $table->renameIndex('order_line_warehouse_storage_location_reservations_primary', 'order_line_storage_location_reservations_primary');
});

I'm pretty sure SingleStore is able to rename columns. Not sure about indexes. (https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/alter-table/)

I can see that version 1.5.2 generates 2 primary keys for sample migration below:

Schema::create('table_name', function (Blueprint $table) {
    $table->string('name');
    $table->unsignedBigInteger('valu');

    $table->primary('name');
});

Sql is:

create table `table_name` (
    `name` varchar(255) not null,
    `val` bigint unsigned not null,
    primary key `table_name_name_primary`(`name`),
    primary key (`name`)
) default character set utf8mb4 collate 'utf8mb4_unicode_ci'

which throws SQL error like below:
General error: 1851 COLUMNAR indexes and SKIPLIST indexes cannot be used on the same table

I would appreciate it if you could check it.

As the title states, the command php artisan migrate:fresh doesn't work.

This also impacts unit tests which use the RefreshDatabase trait.

The error:
SQLSTATE[HY000]: General error: 1295 This command is not supported in the prepared statement protocol yet (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE')

Shouldn't we use it like No.1 to keep the unique function intact?

I don't know why you explain like the link below and No.2

https://www.singlestore.com/blog/laravel-singlestoredb-quickstart-guide/

Is there a reason to use index instead of unique?
Is there anything I didn't understand or missed?

If you know why, I want you to explain. Thank you

No. 1

Schema::create('users', function (Blueprint $table) {
      $table->id();
      $table->string('name');
      $table->string('email');
      $table->timestamp('email_verified_at')->nullable();
      $table->string('password');
      $table->rememberToken();
      $table->timestamps();

      $table->unique(['id', 'email']);
});

No.2

Schema::create('users', function (Blueprint $table) {
      $table->id();
      $table->string('name');
      $table->string('email')->index();
      $table->timestamp('email_verified_at')->nullable();
      $table->string('password');
      $table->rememberToken();
      $table->timestamps();
});

I'm currently working through switching from MySQL to SingleStore and I'm getting the following error when trying to run migrations

Method SingleStore\Laravel\Schema\Blueprint::sharedKey does not exist.

I have the SingleStore driver loaded and my database.php file has been updated to use the singlestore driver. If I switch my connection name to "singlestore" everything works, until the migrations try running on the subsequent database connections (dealers, physical, scanitparts). I assume database connection name can be whatever I want? Am I missing something?

'connections' => [
        'shared' => [
            'driver' => 'singlestore',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::ATTR_EMULATE_PREPARES => true,
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_PERSISTENT => env('APP_ENV') !== 'production',
            ]) : [],
        ],

        'dealers' => [
//            'driver' => 'mysql',
            'driver' => 'singlestore',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DEALERS_DATABASE', 'shared'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::ATTR_EMULATE_PREPARES => true,
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_PERSISTENT => env('APP_ENV') !== 'production',
            ]) : [],
        ],

        'physical' => [
//            'driver' => 'mysql',
            'driver' => 'singlestore',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_PHYSICAL_DATABASE', 'shared'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::ATTR_EMULATE_PREPARES => true,
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_PERSISTENT => env('APP_ENV') !== 'production',
            ]) : [],
        ],

        'scanitparts' => [
//            'driver' => 'mysql',
            'driver' => 'singlestore',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_SCANITPARTS_DATABASE', 'shared'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::ATTR_EMULATE_PREPARES => true,
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_PERSISTENT => env('APP_ENV') !== 'production',
            ]) : [],
        ],

    ],

Reported by Ken V (@xewl) on Twitter: https://twitter.com/Xewl/status/1612700319674413056

Laravel 11 has been released (https://laravel-news.com/laravel-11). Unfortunately, this package does not currently support it. Please add support for Laravel 11. Thank you!

Hi folks,

Any chance of being able to add support for the https://github.com/staudenmeir/laravel-cte package, as currently it gives the following error:

BadMethodCallException : Call to undefined method SingleStore\Laravel\Query\Builder::withExpression()

I'd happily donate in return as I want to avoid raw queries.

Thanks

Hey there,

making my way through building an app based on SingleStore and noticed now, that when the migrations use ->unique() it by default does not work, since it does not include all keys defined by the shard key.

You can replicate this issue by using a fresh laravel install and running the migration.

Example from the create_failed_jobs_table:

 Schema::create('failed_jobs', function (Blueprint $table) {
            $table->id();
            $table->string('uuid')->unique();
            $table->text('connection');
            $table->text('queue');
            $table->longText('payload');
            $table->longText('exception');
            $table->timestamp('failed_at')->useCurrent();
        });

Error:

Should this be solved automatically within this Adapter, so it would make the unique keys including the shard key so people using this adapter does not have to edit the default create table migrations?

I can just remove the ->unique() and make the keys myself, but everyone would have to do so by default, which is extra overhead.

Hi,

The singlestore driver seems incompatible with Vapor.

From a middleware where we do this : auth()->check() === false

Here is the error received :

PDOException
Illuminate\Database\QueryException
SQLSTATE[HY000] [2002]  (trying to connect via (null)) (SQL: select * from `user` where `id` = 1024732 limit 1)

Even trying to register singlestore connection in the AppServiceProvider following this issue : #2 it does not work

Maybe an extension is missing on Vapor but which one? Is there an incompatibility with Alpine?

On EC2 it works but not on serverless :(.

Any idea?

After searching a bit about why my ORDER BY x DESC query was so slow in big tables even with a sort key, I found that sort keys by default work only for ASC queries.

If I would like to take advantage of the sort key in a DESC query, I would need to create a table using the sort key desc, ex:

SORT KEY (Price desc)

But it seems the Laravel driver does not support setting the key orientation.

Source: https://www.singlestore.com/forum/t/optimizing-performance-of-order-by-x-desc-on-large-tables/2308/4.

whereNull on a JSON expression generates this test json_type(JSON_EXTRACT_JSON(data, 'value1')) = 'NULL' which does not work in SingleStore.

Also: need to go through the tests in JsonWhereTest.php since it appears they aren't running against SingleStore. If they were, we would have caught this issue.

Inserting emojis becomes ������ in JSON columns.

CREATE TABLE `test` (
  `a` JSON COLLATE utf8mb4_unicode_ci,
  `b` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

DB::table('test')->insert([
    'a' => json_encode(['text' => 'Hello world! æøå 😀']),
    'b' => 'Hello world! æøå 😀',
]);

Using the JSON_UNESCAPED_UNICODE flag seems to work:

This affects Eloquent models with JSON casting attributes (like array and object).

Is this related to Laravel, PDO MySQL driver, or SingleStore DB?

Normally, I would expect the stored JSON value to use backslashes to escape Unicode characters (PHP default with json_encode), but that does not seem to be the way SingleStore DB handles it.

Getting this error while trying to migrate!

`2014_10_12_000000_create_users_table .................................................................................................... 3ms FAIL

BadMethodCallException

Method Illuminate\Database\Schema\Blueprint::shardKey does not exist.`

We are in the process of migrating to Singlestore and have encountered a compatibility issue with Laravel's Database Notifications.

The issue arises from the HasDatabaseNotifications trait, which defines a relationship that automatically applies a latest sorting order by default. This sorting behavior is intended for notification retrieval but also inadvertently affects deletion operations. While this automatically generated query functions correctly in MySQL and PostgreSQL, it appears that Singlestore does not support this syntax.

<?php

namespace Illuminate\Notifications;

trait HasDatabaseNotifications
{
    /**
     * Get the entity's notifications.
     *
     * @return \Illuminate\Database\Eloquent\Relations\MorphMany
     */
    public function notifications()
    {
        return $this->morphMany(DatabaseNotification::class, 'notifiable')->latest();
    }

delete from
  `notifications`
where
  `notifications`.`notifiable_type` = App \ Models \ User
  and `notifications`.`notifiable_id` = 218
  and `notifications`.`notifiable_id` is not null
  and JSON_EXTRACT_STRING(data, 'format') = filament
  and `id` = 9bf43012 - ecff - 4ef1 - afb2 - 26ee0218109f
order by
  `created_at` desc

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by `created_at` desc' at line 1

@carlsverre the behaviour should be documented in Singlestores doc. In the example is a order by in a subquery but no mention of the not allowed behaviour.

Reference: https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/delete/#example

@olliescase

An error occurs when trying multiple transactions at once.

"message": "There is already an active transaction",
"exception": "PDOException",

example code

        DB::beginTransaction();
        MarketingAgreementLog::create([
            'user_id' => 1,
            'is_agreement' => true,
            'type' => 'update_profile',
        ]);
        DB::rollBack();

        DB::beginTransaction();
        MarketingAgreementLog::create([
            'user_id' => 1,
            'is_agreement' => true,
            'type' => 'update_profile',
        ]);
        DB::rollBack();

        DB::beginTransaction();
        MarketingAgreementLog::create([
            'user_id' => 1,
            'is_agreement' => true,
            'type' => 'update_profile',
        ]);
        DB::commit();

The code below works normally on mysql, but an error occurs on singlestore.

Is it a natural result due to the difference between single store and mysql?

Or is it an error that needs to be corrected?

environment

php 8.1.13
laravel 9.45
singlestoredb-laravel 1.4.1
singlestore db version 8.0.4

Laravel 10 has been released today (https://laravel-news.com/laravel-10). This package currently doesn't support it, please add support for Laravel 10. Thanks!

In Laravel Migrations, $table->id() always creates a column of type BIGINT(20). I am trying to use a custom numeric data type using following code in migrations.

$table->mediumInteger("id", true, true);

When using this package, it always creates a column of BIGINT datatype though the size is different e.g. BIGINT(8). I have tried other numeric data types like smallInteger, integer but they do not work.

I also tested in Laravel (without) this package and it works the way it suppose to.

Am I missing something?

When adding a column like so:

 $table->string('model_value')->nullable();

I must set the charset and collation manually so it matches the driver config like so:

$charset = config('database.connections.singlestore.charset');
$collation = config('database.connections.singlestore.collation');
$table->string('model_value')->nullable()->charset($charset)->collation($collation);

is this expected behaviour?

Do you happen to have a simple example project xD Probably doing something wrong but I'm stuck on the .env config part and getting

Unsupported driver [http].

I'm running the docker container like so but not 100% sure what to fill in .env

Hello.

We are in the process of migrating a MySQL database to Singlestore and we found some issues when trying to execute some queries generated by the singlestoredb-laravel-driver.

For example, the following query:

select
  `segments`.*,
  `auto_segments_customers`.`virtual_customer_id` as `pivot_virtual_customer_id`,
  `auto_segments_customers`.`segment_id` as `pivot_segment_id`,
  `auto_segments_customers`.`created_at` as `pivot_created_at`,
  `auto_segments_customers`.`updated_at` as `pivot_updated_at`,
  `auto_segments_customers`.`domain_id` as `pivot_domain_id`,
  `auto_segments_customers`.`domain_type` as `pivot_domain_type`
from
  `segments`
  inner join `auto_segments_customers` on `segments`.`id` = `auto_segments_customers`.`segment_id`
where
  `auto_segments_customers`.`virtual_customer_id` in (2)
  and (
    (
      `segments`.`domain_id` = 1
      and `segments`.`domain_type` = 'wholesalers'
    )
    or (
      `segments`.`domain_id` in (1)
      and `segments`.`domain_type` = 'manufacturers'
      and exists (
        select
          *
        from
          `segment_types`
        where
          `segments`.`segment_type_id` = `segment_types`.`id`
      )
    )
    or (
      `segments`.`domain_id` in (1)
      and `segments`.`domain_type` = 'manufacturer-branches'
      and exists (
        select
          *
        from
          `segment_types`
        where
          `segments`.`segment_type_id` = `segment_types`.`id`
      )
    )
  )
order by
  IF(
    (
      segments.domain_id = 1
      AND segments.domain_type = 'wholesalers'
    ),
    0,
    1
  )

Returns the following error:

ERROR 1749 ER_DISTRIBUTED_FEATURE_LOCKDOWN: Feature 'Correlated subselect that can not be transformed and does not match on shard keys' is not supported by SingleStore Distributed.

Can you help understanding the problem here and how we should solve it?

Thank you

First of all, congrats on the package release!

I've found a bug the following bug.

This gets thrown if you use database as your cache driver. In this case it was thrown because the RouteServiceProvider.php uses rate limiting which uses cache which uses the database.

My guess is that the RouteServiceProvider gets processed before the SingleStoreServiceProvider.

For the people running into this issue, you can solve it by adding the SingleStoreProvider code to your AppServiceProvider. Note: this is a temporary fix!

Hey we've got this weird issue where migrations don't create composite primary keys the same way they used to. We used to be able to migrate nullable primary keys but now they don't get created correctly anymore.

Our production table on SingleStore Cloud:

Our staging and local table:

Our migration (which hasn't changed):

This is the case for all our pivot tables which have a nullable column.

I'm suspecting the driver because the local environment hasn't been updated in months and still experienced this change/issue.

Production isn't impacted (for now) but having to manually run queries to fix the columns every time we use migrate:fresh kinda sucks. Let met know if you need more info to debug.

It would be great if this feature was supported.

As I understand, the following would have to be done in order to change a field (This example changes a field called "name" from a varchar to text):

1. Add a column of the desired type (ALTER TABLE table_name ADD COLUMN field2 TEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE utf8mb4_unicode_ci; )
2. Fill values in this column with the column you want to change the data type (UPDATE table_name SET name2 = name;)
3. Remove the previous column (ALTER TABLE table_name DROP COLUMN name;)
4. Change the name of the new column (ALTER TABLE table_name CHANGE name2 name;)

Would it be possible to implement this? Or is there already another way to do this?