Questions tagged [mariadb]

1

votes
1

answer
1.6k

Views

Galera cluster replication between two data centers

We have 4 servers located in two data centers. A direct line connects the two datacenters. We are planning to switch from MariaDB to MariaBD Gallera as circular replication is a pain. A load-balancer is used to determine which server will handle an incoming request which can be any of the four serve...
thedethfox
0

votes
0

answer
17

Views

java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:

After having gotten a servlet with a JDBC datasource working fine, I am now trying to wire up a JPA configuration. In doing so I have paged through hundreds of posts and manuals, and tried different things, and every time it all comes back to the same exception mentioned in the subject: java.sql.SQL...
Sid M
1

votes
1

answer
211

Views

Laravel - soft delete - move posts to other table

Is it possible work with Laravel, that it can have soft deleted items in other identical log table? All soft deleted files in other table. I want create on log table Event scheduler (http://dev.mysql.com/doc/refman/5.6/en/events-overview.html) to delete all data after some time.
Maximi
1

votes
1

answer
9.7k

Views

Warning: mysqli_real_connect(): (HY000/1698): Access denied for user 'wpuser'@'localhost' in /var/www/html/wp-includes/wp-db.php on line 1534

Hello so I installed mariadb today and as soon as I was done installing this error occurred. Now, this sort of makes sense to me because while maria is the replacement for sql I imagine naming conventions and such are still different. My problem is I have no idea how to go about fixing this issue, c...
Elizabeth Fuenzalida
0

votes
0

answer
16

Views

Update mysql? SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

I have this error when executing php bin/console doctrine:migrations:migrate First, I had a problem with json type in roles field but I got to know that it's enough to change this type to text as older mysql doesn't cope with json type. Now I am facing problem which I found in documentation for old...
Aerogirl
1

votes
1

answer
28

Views

MySQL Update a column with values combined from target column and another column

I want to update column user with the last 6 characters replaced by id padded with zero. All keys under column user are in the format ^[A-Z]{2}[0-9]{8}$. For example, here's some sample data: MariaDB [test]> SELECT * FROM users ORDER BY id ASC LIMIT 3; +----+------------+ | id | user | +----+-...
iBug
0

votes
0

answer
6

Views

Using CASE WHEN DATE Statement in MariaDB 10.2.15

I created a query to display the ScanIn Field in one month on each date Code : SELECT Employee.Name, CASE WHEN DATE(LogsFormatted.DateIn) = 1 THEN LogsFormatted.ScanIn ELSE '-' END AS '1', CASE WHEN DATE(LogsFormatted.DateIn) = 2 THEN LogsFormatted.ScanIn ELSE '-' END AS '2', CASE WHEN DATE(LogsFor...
Aldan
0

votes
0

answer
4

Views

Way to do array of enums in MariaDB?

Is there way to make array of enums in MariaDB easy way? I am using Sequelize in NodeJS. I need to save array of ENUM datatypes, but Sequelize documentation saying its possible only in PostgreSQL.
Baterka
7

votes
1

answer
154

Views

REGEXP_REPLACE guidance

I've been trying to mass remove spammy links from posts on Wordpress on bulk like the following: . They reside in the wp_posts table under the post_content column. I was attempting to do it via a wild card of adding % within the href tag, because all the URL's are different but the anchor ( full...
Randomer11
1

votes
0

answer
7

Views

What is the syntax for MariaDB 'IN NATURAL LANGUAGE MODE'?

According to the MariaDB documentation: There are no special operators, and searches consist of one or more comma-separated keywords. The search clearly does not need to be comma-separated, as replacing commas with spaces gives the same result. I assume that it breaks the string into separate keywor...
Liam
1

votes
1

answer
279

Views

Laravel: MariaDB - artisan migrate:refresh hangs

I'm using Debian 9 with MariaDB 10.1.27, Composer 1.4.2 and Laravel 5.5.13. I've created an empty database using CREATE DATABASE 'laravel', configured it in Laravel's .env config file and now I've sometimes the problem, that database actions hangs (nothing happens) or in the browser I get an timeout...
user2966991
1

votes
3

answer
460

Views

play 2.4 ebean update optimistic locking

I am trying to implement crud sort of like computer-database example using play 2.4 with ebeans. Everything works except update. When I run the computer-database example against same MariaDB and older ebeans (3.2.2), it does update just fine so does not seem to be an issue with the database. I am...
Slayer6
1

votes
1

answer
663

Views

mariadb alter table lock strategy

I am using MariaDB 10.1.9. Short version: What I really want to know for certain is if I can modify an indexed auto_increment field on an innodb table from int to bigint without locking the table? Long version: Is there a detailed explanation of which ALTER TABLE operations require which lock level?...
Tulvan
1

votes
1

answer
1.5k

Views

Copy data from MySQL to Google BigQuery

What is the easiest and fastest way to migrate some tables of my MySQL database to Google BigQuery? I want something simple like: $ migrate
Daniel Cukier
1

votes
1

answer
40

Views

Deciding between two methods to check if a row exists (more subqueries VS more left joins)

I want to fetch one product information using joins from different tables. I have three additional tables (review,thread,award) and I'd like to check whether records exist relating to this specific product. If they exist, return a non-null value, otherwise null. There is a possibility that more of t...
RedGiant
1

votes
1

answer
208

Views

MariaDB: Can I retrive duplicate column names without using 'AS'

I have a lot of tables that use id as the primary key. When I join several tables together and select *, only one of the id columns is displayed. When using MySql, the only way I could get around this was to use SELECT AS, for example SELECT id AS groupId. Is this still the case with MariaDB? My...
Kim Prince
2

votes
1

answer
24

Views

SQL: Find intersection in a double many-to-many relatiion

Following is a simplified version of my schema and data: users: id | name 1 | Peter 2 | Max 3 | Susan restaurants: id | name 1 | Mario 2 | Ali 3 | Alfonzo 4 | BurgerQueen dishes: id | name 1 | Burger 2 | Pizza 3 | Salad users_dishes: user_id | dish_id 1 | 1 2 | 1 2 | 2 3 | 2 3 | 3 restaurants_dishes...
Paul Pushkin
1

votes
1

answer
2.9k

Views

Sequelize Dialect Error

I recently ran into a bizarre error with my app after installing lodash. I have no idea if it created any impact on my setup, but all of a sudden sequelize throws an error at the dialect property that mysql is not supported and cannot read property 'dialectName' of undefined. I'm not sure why this i...
cphill
1

votes
2

answer
535

Views

Spring Repository performance issues with recursive ORM Class

My aim: I have the children on the lowest level (for exmaple skill id 10 and 12) in a list. Now, I want all parents (parent_id = null) for every child (in this case, parent 34) and save them in a list again. After all I want the path from the parent to each child (34-9-10 and 34-9-12). Later on I wa...
Andy
1

votes
2

answer
1.4k

Views

JPA update with a join

I use spring-jpa with hibernate implementation. I use mariadb I try to do an update with a join. My object structure @Entity public class Room { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long roomId; @ManyToOne @JoinColumn(name = 'appartment_id') private Appartment appartment; }...
robert trudel
1

votes
3

answer
478

Views

Improve delete with IN performance

I struggle to write a DELETE query in MariaDB 5.5.44 database. The first of the two following code samples works great, but I need to add a WHERE statement there. That is displayed in the second code sample. I need to delete only rows from polozkyTransakci where puvodFaktury FAKTURA VO CZ in transa...
David Huňka
1

votes
1

answer
744

Views

AES_Decrypt() returning null value

SQL-Newbie here. I'm working with a table that contains data (of the longblob type), which looks like this (imported into phpMyAdmin): These files are (apperently) AES encrypted using AES_Encrypt() . I know the key to the file with the key-id 2332, and I want to call AES_Decrypt() on that 'blahb' (b...
1

votes
1

answer
2.6k

Views

Warning Unsafe statement written to the binary log using statement

I've created a log system based on trigger. Every time a row is inserted or updated the trigger store a new row in another table. The trigger works fine but after some time I found this message in logs: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMA...
user3396065
1

votes
2

answer
373

Views

How to make Mysql variables work in a query

I've been struggling for a while now with attempting to generate code for automatic aggregations in my mysql/mariadb database. The method That i'm currently trying uses variables. I will admit in advance I'm not a database expert by any means. I'm totally self taught, and have been struggling to fin...
bigjake
1

votes
1

answer
33

Views

Merge data from two cells from the same column in different rows in MySQL/MariaDB

I am having trouble searching for an answer to this question, because of my lack of knowledge about the terminology and SQL, even though I know it probably exists. I have a database with the following tables: desc pkm; +-----------------+---------------+------+-----+---------+-------+ | Field...
One Normal Night
1

votes
5

answer
12.4k

Views

Regular expression to extract a JSON array

I'm trying to use a PCRE regular expression to extract some JSON. I'm using a version of MariaDB which does not have JSON functions but does have REGEX functions. My string is: {'device_types':['smartphone'],'isps':['a','B'],'network_types':[],'countries':[],'category':['Jebb','Bush'],'carriers':[],...
GGGforce
1

votes
1

answer
644

Views

Check constraint not working in mariaDB

I created a table using the following statement. create table constraint_test(name varchar(20), city varchar(20) not null check (city in ('chennai','vellore')), phone numeric(10)); But when I insert as, insert into constraint_test values('abcd,'ooty',123456); it gets stored. How can I restrict it?
Naveen Kumar V
1

votes
1

answer
305

Views

Joomla Database import does not work

i made a backup of my joomla MySQL database using the export function of phpMyAdmin. When i try to import this backup file, it gives me a lot of errors like 'You have an error in your SQL syntax...' even if the SQL looks quite correct. Here is a small part of the backup, which doesnt even work: CREA...
Alphacoder
1

votes
1

answer
24

Views

Select a value that has a greater number of values

Let's say I have a table like this one: Table T: A B ------- 1 x | O 2 x | O 3 x | P 4 y | O 5 y | P 6 y | P 7 z | O 8 z | O 9 z | P I want to select the values in column A that have a greater number of the values in column B. For example, I want to select x, y, or z if they have more O'...
dogwin
1

votes
1

answer
968

Views

why can't I import a mysql dump with triggers into mariadb

We plan to migrate our stuff into swisscom app cloud and therefore need to import existing MySQL dumps into MariaDB - so far no big deal... But the import of the dumps fail as soon as a trigger should be imported. e.g. I have a dump with this single trigger: DELIMITER ;; /*!50003 CREATE*/ /*!50017...
domi
1

votes
1

answer
2.9k

Views

Is MariaDB 10.1 compatible with Doctrine 2.5?

I'm starting a new PHP project with Symfony 3 and Doctrine 2 and I would like to use MariaDB 10.1 (or 10.0) instead of MySQL 5.7, but i cannot find if Doctrine (ORM) 2.5 supports MariaDB 10.1 and, if not, if there's any plan to support it in the future. Does anyone know more? Known incompatibilities...
Stefano P.
1

votes
2

answer
626

Views

Restore data from specific date in mysql database table using mysqlbinlog

I have mysql database table called 'radacct' which has data from 2016-01-01(Jan) to 2016-06-29(June). For some reason the data from 2016-06-20 to 2016-06-25 is lost. I had bin-log enabled so I used 'mysqlbinlog' to export bin files to .sql files ,those sql files contained older data from Jan,Feb,Mar...
satch_boogie
1

votes
1

answer
2.2k

Views

pandas dataframe to mariadb database with to_sql and sqlalchemy

I want to use the pandas function to_sql to write a dataframe into a MariaDB database. My Python code inside PyCharm looks as follows: import pandas as pd import mysql.connector from sqlalchemy import create_engine myd = pd.read_csv('/[path]/[filename].csv') engine = create_engine('mysql+mysqlconnec...
nluckn
1

votes
1

answer
1.2k

Views

Unknown character set utf8mb4

I'm developing my PHP apps on XAMMP on a usb stick, the character set is utf8_unicode_ci, when I exported the db and imported the SQL into my live server at OVH, it gave me this error: 1115 - Unknown character set: 'utf8mb4' My XAMMP dev server DB version: Server type: MariaDB Server version: 10.1.1...
medk
1

votes
1

answer
115

Views

MARIADB: Index not used for a select with join on a range

I have a first table containing my ips stored as integer (500k rows), and a second one containing ranges of black listed ips and the reason of black listing (10M rows) here is the table structure : CREATE TABLE `black_lists` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `ip_start` INT(11) UNSIGNED NOT N...
elarichi.y
1

votes
1

answer
232

Views

How to use regular expression to match word without any letter,digital or special character?

I want to use regular expression to filter out pure Chinese name by this: SELECT `name` FROM `table` WHERE `name` REGEXP '[u4e00-u9fa5]'; But, according to this, it isn't possible,so I want to approach that from opposite direction,find content without any letter,digital and special character(I knew...
Alex Luya
1

votes
1

answer
57

Views

Get number of entries per multiple date intervals using single query

SELECT COUNT(*) FROM `table` WHERE `datetime` > SUBDATE(NOW(), INTERVAL 1 DAY) This will get number of entries during last day. But is it possible to get number of entries for multiple intervals without having to send variation of this query multiple times (INTERVAL 1 DAY, INTERVAL 1 WEEK, INTERVAL...
Martin
1

votes
2

answer
197

Views

1045 when connecting to MySQL server

The error is 1045. But my username and password are correct. I am connecting as fcapdi from 192.168.0.18 (to 192.168.0.240:3306) My users table MariaDB [(none)]> SELECT User,Host FROM mysql.user; +--------+-----------+ | User | Host | +--------+-----------+ | dba | % | | fcapdi | %...
Valarauca
1

votes
1

answer
16

Views

How to make two table condition to update one table

Hello guys I don't get it. I have two tables Posts: id|fromid|toid|receiver|sender 1, null, null, user1, user2 User: id|username 1, user1 2, user2 As you see fromid and toid has null values. So I wanted fill it with user id but I don't get it. later should entry look like: 1, 2, 1, user1, user2 I t...
MaZy
1

votes
2

answer
1k

Views

MySQL memory usage growing up to 100%

I have MariaDB (10.0.20) installed on machine with 256gb RAM. Memory usage is growing all day up to 100% until restarting it. After some restarts memory usage stops growing on 70% - but starts growing again in some hours. What the problem with my server or configuration? Red line - memory. Falling t...
user1964654

View additional questions