Mysql ошибка 1101 - Решение и исправление самых разных ошибок на TopOshibok.ru

I heard that mysql version prior to 8.0.13 accept default value for json type column, so I using the cmd:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT '{}' ;

but receive error:

Error Code: 1101. BLOB, TEXT, GEOMETRY or JSON column 'values' can't have a default value

So how do I fix it?

I’m using mysql version 8.0.19 and client tool Workbench

GMB

216k25 gold badges84 silver badges135 bronze badges

asked Apr 12, 2020 at 8:58

From version 8.0.13 onwards, the documentation says (emphasis is mine):

The BLOB, TEXT, GEOMETRY, and JSON data types can be assigned a default value only if the value is written as an expression, even if the expression value is a literal.

You can make your default an expression by surrounding the literal value with parentheses:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT ('{}') ;

Or:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT (JSON_OBJECT()) ;

Prior to version 8.0.13 of MySQL, it was not possible to set a default value on a JSON column, as the 8.0 documentation points out a few paragraphs later :

The BLOB, TEXT, GEOMETRY, and JSON data types cannot be assigned a default value.

answered Apr 12, 2020 at 9:19

GMBGMB

216k25 gold badges84 silver badges135 bronze badges

According to the laravel docs:

$table->json('movies')->default(new Expression('(JSON_ARRAY())'));

The default modifier accepts a value or an Illuminate\Database\Query\Expression instance. Using an Expression instance will prevent Laravel from wrapping the value in quotes and allow you to use database specific functions. One situation where this is particularly useful is when you need to assign default values to JSON columns

https://laravel.com/docs/8.x/migrations#default-expressions

answered May 2, 2022 at 15:47

MySql syntax is a bit different than Oracle/Postgres, hence to make say JSON_Array as default, the query would be —

ALTER TABLE table_name ALTER column_name SET DEFAULT (JSON_ARRAY());

Further reference here

answered Oct 14, 2021 at 16:30

Источник

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.

Already on GitHub?
Sign in
to your account

Closed

bretmette opened this issue

Feb 3, 2017

· 5 comments

Comments

I am running mariadb 10.0.19 but according to the MySQL docs https://dev.mysql.com/doc/refman/5.7/en/blob.html a BLOB/TEXT column is not allowed to have a default value.

[Illuminate\Database\QueryException]                                                                                                                                                                                             
SQLSTATE[42000]: Syntax error or access violation: 1101 BLOB/TEXT column 'value' can't have a default value (SQL: create table `meta` (`id` int unsigned not null auto_increment primary key, `metable_type` varchar(255) not null, `metable_id` int unsigned not null, `type` varchar(255) not null default 'null', `key` varchar(255) not null, `value` longtext not null default '') default character set utf8 collate utf8_unicode_ci)

[PDOException]                                                                                               
SQLSTATE[42000]: Syntax error or access violation: 1101 BLOB/TEXT column 'value' can't have a default value

What are the impacts of removing the default on the rest of the code? I propose removing it, while some platforms throw a warning others throw an error. As of this writing the mode mysql is running at determines if an error will be thrown (STRICT MODE throws an error).

Simple fix is to change

$table->longtext('value')->default('');

$table->longtext('value');

But I am not sure what the impacts of that change are on the rest of the code.

Hi @bretmette,

Good point! Strange that none of the SQL implementations that I tested on flagged that. The default is not only redundant (default for not-null TEXT column is already »), likewise that should not be necessary for any of the needs of the app, a value should always be set by Eloquent. I have pushed a new patch version with an updated migration file.

Thanks for pointing that out!

@frasmage awesome, thank you for being actively involved in your package

For me it was this that worked:

Changing:

$table->longtext('value')->default('');

$table->longText('value')->nullable(true);

Nice, it’s Works for me! To good!

Источник

При обновлении Битрикса получил ошибку:

MySQL Query Error: CREATE TABLE b_sale_cashbox_check_correction( ID int(11) unsigned not null auto_increment, CHECK_ID int(11) not null, CORRECTION_TYPE varchar(50) not null, DOCUMENT_NUMBER varchar(35) not null, DOCUMENT_DATE date not null, DESCRIPTION varchar(255) default », CORRECTION_PAYMENT text default », CORRECTION_VAT text default », PRIMARY KEY (ID) ) [[1101] BLOB, TEXT, GEOMETRY or JSON column ‘CORRECTION_PAYMENT’ can’t have a default value]

Решение — нужно создать эту таблицу «руками»:

 
CREATE TABLE b_sale_cashbox_check_correction( ID int(11) unsigned not null auto_increment, 
CHECK_ID int(11) not null, CORRECTION_TYPE varchar(50) not null, 
DOCUMENT_NUMBER varchar(35) not null, DOCUMENT_DATE date not null, 
DESCRIPTION varchar(255) default '', CORRECTION_PAYMENT text, 
CORRECTION_VAT text, PRIMARY KEY (ID) )

Источник

Error: 1100 SQLSTATE: HY000
(ER_TABLE_NOT_LOCKED)

Message: Table ‘%s’ was not locked with LOCK TABLES

Error: 1101 SQLSTATE: 42000
(ER_BLOB_CANT_HAVE_DEFAULT)

Message: BLOB/TEXT column ‘%s’ can’t have a default value

Error: 1102 SQLSTATE: 42000
(ER_WRONG_DB_NAME)

Message: Incorrect database name ‘%s’

Error: 1103 SQLSTATE: 42000
(ER_WRONG_TABLE_NAME)

Message: Incorrect table name ‘%s’

Error: 1104 SQLSTATE: 42000
(ER_TOO_BIG_SELECT)

Message: The SELECT would examine more than MAX_JOIN_SIZE rows;
check your WHERE and use SET SQL_BIG_SELECTS=1 or SET
SQL_MAX_JOIN_SIZE=# if the SELECT is okay

Error: 1105 SQLSTATE: HY000
(ER_UNKNOWN_ERROR)

Message: Unknown error

Error: 1106 SQLSTATE: 42000
(ER_UNKNOWN_PROCEDURE)

Message: Unknown procedure ‘%s’

Error: 1107 SQLSTATE: 42000
(ER_WRONG_PARAMCOUNT_TO_PROCEDURE)

Message: Incorrect parameter count to procedure ‘%s’

Error: 1108 SQLSTATE: HY000
(ER_WRONG_PARAMETERS_TO_PROCEDURE)

Message: Incorrect parameters to procedure ‘%s’

Error: 1109 SQLSTATE: 42S02
(ER_UNKNOWN_TABLE)

Message: Unknown table ‘%s’ in %s

Error: 1110 SQLSTATE: 42000
(ER_FIELD_SPECIFIED_TWICE)

Message: Column ‘%s’ specified twice

Error: 1111 SQLSTATE: HY000
(ER_INVALID_GROUP_FUNC_USE)

Message: Invalid use of group function

Error: 1112 SQLSTATE: 42000
(ER_UNSUPPORTED_EXTENSION)

Message: Table ‘%s’ uses an extension that doesn’t exist in this
MySQL version

Error: 1113 SQLSTATE: 42000
(ER_TABLE_MUST_HAVE_COLUMNS)

Message: A table must have at least 1 column

Error: 1114 SQLSTATE: HY000
(ER_RECORD_FILE_FULL)

Message: The table ‘%s’ is full

Error: 1115 SQLSTATE: 42000
(ER_UNKNOWN_CHARACTER_SET)

Message: Unknown character set: ‘%s’

Error: 1116 SQLSTATE: HY000
(ER_TOO_MANY_TABLES)

Message: Too many tables; MySQL can only use %d tables in a join

Error: 1117 SQLSTATE: HY000
(ER_TOO_MANY_FIELDS)

Message: Too many columns

Error: 1118 SQLSTATE: 42000
(ER_TOO_BIG_ROWSIZE)

Message: Row size too large. The maximum row size for the used
table type, not counting BLOBs, is %ld. You have to change some
columns to TEXT or BLOBs

Error: 1119 SQLSTATE: HY000
(ER_STACK_OVERRUN)

Message: Thread stack overrun: Used: %ld of a %ld stack. Use
‘mysqld —thread_stack=#’ to specify a bigger stack if needed

Error: 1120 SQLSTATE: 42000
(ER_WRONG_OUTER_JOIN)

Message: Cross dependency found in OUTER JOIN; examine your ON
conditions

Error: 1121 SQLSTATE: 42000
(ER_NULL_COLUMN_IN_INDEX)

Message: Table handler doesn’t support NULL in given index. Please
change column ‘%s’ to be NOT NULL or use another handler

Error: 1122 SQLSTATE: HY000
(ER_CANT_FIND_UDF)

Message: Can’t load function ‘%s’

Error: 1123 SQLSTATE: HY000
(ER_CANT_INITIALIZE_UDF)

Message: Can’t initialize function ‘%s’; %s

Error: 1124 SQLSTATE: HY000
(ER_UDF_NO_PATHS)

Message: No paths allowed for shared library

Error: 1125 SQLSTATE: HY000
(ER_UDF_EXISTS)

Message: Function ‘%s’ already exists

Error: 1126 SQLSTATE: HY000
(ER_CANT_OPEN_LIBRARY)

Message: Can’t open shared library ‘%s’ (errno: %d %s)

Error: 1127 SQLSTATE: HY000
(ER_CANT_FIND_DL_ENTRY)

Message: Can’t find symbol ‘%s’ in library

Error: 1128 SQLSTATE: HY000
(ER_FUNCTION_NOT_DEFINED)

Message: Function ‘%s’ is not defined

Error: 1129 SQLSTATE: HY000
(ER_HOST_IS_BLOCKED)

Message: Host ‘%s’ is blocked because of many connection errors;
unblock with ‘mysqladmin flush-hosts’

Error: 1130 SQLSTATE: HY000
(ER_HOST_NOT_PRIVILEGED)

Message: Host ‘%s’ is not allowed to connect to this MySQL server

Error: 1131 SQLSTATE: 42000
(ER_PASSWORD_ANONYMOUS_USER)

Message: You are using MySQL as an anonymous user and anonymous
users are not allowed to change passwords

Error: 1132 SQLSTATE: 42000
(ER_PASSWORD_NOT_ALLOWED)

Message: You must have privileges to update tables in the mysql
database to be able to change passwords for others

Error: 1133 SQLSTATE: 42000
(ER_PASSWORD_NO_MATCH)

Message: Can’t find any matching row in the user table

Error: 1134 SQLSTATE: HY000
(ER_UPDATE_INFO)

Message: Rows matched: %ld Changed: %ld Warnings: %ld

Error: 1135 SQLSTATE: HY000
(ER_CANT_CREATE_THREAD)

Message: Can’t create a new thread (errno %d); if you are not out
of available memory, you can consult the manual for a possible
OS-dependent bug

Error: 1136 SQLSTATE: 21S01
(ER_WRONG_VALUE_COUNT_ON_ROW)

Message: Column count doesn’t match value count at row %ld

Error: 1137 SQLSTATE: HY000
(ER_CANT_REOPEN_TABLE)

Message: Can’t reopen table: ‘%s’

Error: 1138 SQLSTATE: 22004
(ER_INVALID_USE_OF_NULL)

Message: Invalid use of NULL value

Error: 1139 SQLSTATE: 42000
(ER_REGEXP_ERROR)

Message: Got error ‘%s’ from regexp

Error: 1140 SQLSTATE: 42000
(ER_MIX_OF_GROUP_FUNC_AND_FIELDS)

Message: Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no
GROUP columns is illegal if there is no GROUP BY clause

Error: 1141 SQLSTATE: 42000
(ER_NONEXISTING_GRANT)

Message: There is no such grant defined for user ‘%s’ on host ‘%s’

Error: 1142 SQLSTATE: 42000
(ER_TABLEACCESS_DENIED_ERROR)

Message: %s command denied to user ‘%s’@’%s’ for table ‘%s’

Error: 1143 SQLSTATE: 42000
(ER_COLUMNACCESS_DENIED_ERROR)

Message: %s command denied to user ‘%s’@’%s’ for column ‘%s’ in
table ‘%s’

Error: 1144 SQLSTATE: 42000
(ER_ILLEGAL_GRANT_FOR_TABLE)

Message: Illegal GRANT/REVOKE command; please consult the manual
to see which privileges can be used

Error: 1145 SQLSTATE: 42000
(ER_GRANT_WRONG_HOST_OR_USER)

Message: The host or user argument to GRANT is too long

Error: 1146 SQLSTATE: 42S02
(ER_NO_SUCH_TABLE)

Message: Table ‘%s.%s’ doesn’t exist

Error: 1147 SQLSTATE: 42000
(ER_NONEXISTING_TABLE_GRANT)

Message: There is no such grant defined for user ‘%s’ on host ‘%s’
on table ‘%s’

Error: 1148 SQLSTATE: 42000
(ER_NOT_ALLOWED_COMMAND)

Message: The used command is not allowed with this MySQL version

Error: 1149 SQLSTATE: 42000
(ER_SYNTAX_ERROR)

Message: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax
to use

Источник

The ‘SQL server error 1101’ occurs due to space issues.

Here at Bobcares, we have seen several such SQL related issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and see how to fix it.

Why does ‘SQL server error 1101’ occur

Generally, this error appears when the database engine is not able to allocate any new pages to the database due to insufficient disk space.

For instance, the error appears as below:

~~
Error: 1101, Severity: 17, State: 2
Could not allocate a new page for database ‘DBNAME’ because of insufficient disk space in the filegroup ‘PRIMARY’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting auto-growth on for existing files in the filegroup.
~~

Here are the different reasons for this error to occur.

The database drives are running out of enough space.
You have set a restriction for the database maximum file size or have put size limits for files.
Autogrowth is disabled for database files.

How we fix ‘SQL server error 1101’ error

This error occurs due to space issues. So in order to fix this error, we need to make some space for the database files to grow. However, this error can vary depending upon the reason for the error to occur. Sometimes, if any restriction is set for the database maximum file size then this error occurs. In some cases, the error may occur because there is no space left in the drives.

Now let’s see the suggestions our Support Engineers provide to our customers to resolve this error.

1. Increase Database File Size Limits or remove any restrictions

Even though there is enough drive space for the database, you still receive this error. It happens because there is a limit set for the Database File Size. In order to fix this error, you can either increase the size limit or remove the restrictions. Also, you can set database filesize to unlimited size. Here the database will grow till the database file drive limit.

2. Database Drives space

Another reason for the error is the disk drive running out of space where you have your database files. Here are the 4 different options to overcome this problem.

Add an additional database file to some other disk where there is enough space.
Add an additional Disk Space.
Move additional/unwanted files from your existing database drives.
Move or Drop/Purge any unwanted data (like table/indexes).

3. Enabling Autogrowth

If you have your auto-growth setting disabled then you will receive this error message. Simply enabling it by launching the database property window will fix the error.
For that, click on three dots next to each database files in the column “Authogrowth/Maxsize” and click at the enable auto-growth checkbox.

Moreover, you can use the ALTER statement to enable the database auto-growth setting. Here is the ALTER command:

ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’DBName_Data’, FILEGROWTH = 10000KB )

Note: Ensure to run this ALTER statement for each database file separately.
Under the NAME section, pass the logical filename of your database file.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, this error arises while changing or viewing the values of any advanced server configuration option. Today, we saw the suggestions our Support Engineers provide to our customers to fix this error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник