Questions tagged [mariadb]

1

votes
1

answer
9

Views

Seeking solution for implmenting backups in only one DC

We are running MariaDB Galera cluster in 3 Datacenters. We are using mariabackup tool for taking backups in each datacenter but, since the same data is replicating on all the 3 datacenters we are trying to implement a solution which includes executing the backup script in only DC and if there is dow...
Harika Kusuma
1

votes
2

answer
30

Views

How to compare 2 field in table and make status field

How do I write a query to compare 2 fields and make status? the table is as follows I tried this code: select a, b , if ( a=b,'true','false') as status from x I want the result like a | b | status A | A | True A | B | False A | C | False B | B | true
Agus Candra
1

votes
4

answer
49

Views

Only insert into table if it is empty

I want to insert a row to the table if only the table is empty. I went through this Execute INSERT if table is empty? but am unable to do so.i am using MariaDB as rdbms.Please help.Thanks in advance. Here is my sql syntax: INSERT INTO `policy` (`policy1`, `policy2`, `policy3`, `policy4`) SELECT ('F'...
rock11
0

votes
0

answer
21

Views

Make letters have the same encoding in MariaDB / MySQL?

In MariaDB/mysql, I have a list of names that don't sort properly alphabetically - it looks like half the list is sorted A-Z, then the other half starts A-Z again (without any accents or anything, just basic English A-Za-z). When I look at the hex value of the names, they are indeed sorted correctly...
heykatieben
0

votes
0

answer
5

Views

Mariadb - REGEXP_REPLACE - Use subexpression in subquery

I have this query: SELECT REGEXP_REPLACE( (SELECT ','test':'36','abc' AS test), '(\,\'test\':\')([0-9]*)\'', (SELECT id FROM data WHERE input = '\\2' LIMIT 1) ) The issue is that the backreference to the subexpression is not working properly, the complete result just becomes NULL. However, the bac...
TheNiceGuy
0

votes
1

answer
21

Views

GROUP BY and ORDER

I have a very simple Maria DB for home automation purpose. This is the essential extract of my ER: TelldusActionsPerformed is a log table. It is used for keeping track of very different actions (like a temperature measurements, a door closing, a power switch turned etc) performed at different times....
Hauns TM
1

votes
2

answer
33

Views

Local variable sum previous columns

I am trying to use @variable1 + @variable2 into a query but is actually given 0 as result. MariaDB Server Version: 10.2.21 set @start_at = '2019-01-01'; set @end_at = '2019-01-16'; set @receivable = 0; set @invoiced = 0; SELECT DISTINCT Customer.custnr 'Customer Number', Address.name 'Name', @rec...
joepa37
1

votes
3

answer
52

Views

Unknown column 'ID' in 'field list', but the column IS in the list

Error: Unknown column 'ID' in 'field list' I am baffled. As you can see below, ID is a column under the wp_posts table: create table wp_posts ( ID integer primary key auto_increment, post_title varchar(30), post_type varchar(30) ); create table wp_postmeta ( ID integer primary key auto_increment,...
ptrcao
1

votes
2

answer
54

Views

MariaDB - INNODB skipping the number sequence while creating incremental records - why?

I do not know if this is expected behavior with INNODB, but I really think it's totally weird. If I use the same SQL statement using MYISAM, the behavior occurs as expected. MYISAM CREATE TABLE main_database.numero ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) ) ENGINE = MYISAM DEFAULT CHARSET...
Magno Alberto
0

votes
0

answer
4

Views

How can i fix thisnin crystal report

https://ibb.co/x1tR4RJ I have this error when i try to run the report. I already install crystal report runtime 64 and 32 bit but still not working, how can i fix that?
Newb
1

votes
1

answer
38

Views

sql distinct or group by to get correct order

Okay, so i have a list of posts and some posts are replies to other posts. I'd like to get a list of post parents in reverse order of replies. I've tried group by but it always lists the wrong order and distinct is the only way i've managed to get it to work but obviously then it only lists the post...
Jezzzz
1

votes
0

answer
73

Views

MariaDB odd in its simple usage of Active Record in different Debian 9 servers and Rails apps

In a pair of OVZ-6 servers and same Ruby on Rails application, the simple select post comments (one-to-many association) are displaying the three existent comments but some full records at the end: Great solution for a big and old problem. This is a problem that I have started to web design years ag...
consen
1

votes
1

answer
28

Views

SQL query in mariadb

I have a task to write a SQL query to find which hospital has the most doctors and for that hospital to show all the patients and dates of their checkup. Relations that are involved in my db are next: Hospital (HospitalID, Name_of_the_Hospital, Place, Phone number) Scheduling_Checkups (Hospital, Do...
Pera
-1

votes
0

answer
11

Views

Migrating from MariaDB to MySQL - differences

I am working on a small social network (just to fill my free time) and I started with a Maria database. But I had some problems with it (long story) so I changed it to a MySQL database. But after this I am not able to insert values into some tables (for example with Maria I inserted values to 'messa...
Paula
1

votes
0

answer
767

Views

PHP MariaDB access denied for [email protected] through browser without sudo

I am trying to create a new database as root, which has no password set. I log in from the command line using sudo to access root without a problem. Using a PHP script to create a database as root, I don't provide a password and I get Access denied for user 'root'@'localhost'. I'm not supposed to in...
1

votes
1

answer
23

Views

mySQL: Query just keeps loading

I want to join 4 tables together. One table comes from database A, while 3 other tables come from database B. When I run the following query, the computer just keeps loading, with no error or result in sight. My query is: SELECT tableA.ts, tableA.voltage, tableB.Day0, tableC.Day0, tableD.Day0 FROM...
Craver2000
1

votes
1

answer
289

Views

MaxScale blocks direct connect to database

I have a 2 nodes mariadb cluster with one maxscale load balancer. maxscale blocks connection if i want to connect directly to a database: so for example: mysql -h 35.300.208.100 -u finn -p works and if i then do a USE test i can do everything with the database 'test'. so the rights are correct....
siterytive
1

votes
2

answer
392

Views

How to count days between 2 dates except holiday and weekend?

I started a HR management project and I want to count days between 2 dates without counting the holidays and weekends. So the HR can count employee's day off Here's the case, I want to count between 2018-02-14 and 2018-02-20 where there is an office holiday on 2018-02-16. The result should be 3 days...
Richard Doe
1

votes
0

answer
90

Views

Migrate mysql to MariaDB after Debian 9 upgrade without lose data

there is a way to migrate to MariaDB without losing database data and leave no package trace after mysql removal? I get an dependency error while install mariadb-server, other answer has suggested to clean mysql folder then reimport backup, but with short downtime i can't use this method. I'm on Deb...
Edoardo Cortese
1

votes
0

answer
54

Views

Laravel is adding some “\n” to query builder

I'm in MariaDB 5.6, Laravel 5.4. I have a query builder like this: DB::select( DB::raw(' INSERT INTO rgl_ejer_por_comunas ( com_id, sum ) VALUES ( $com, $sum ) ')); It was working fine for weeks, now I don't know why Laravel is adding some '\n' at the end of each line so it's giving me now this e...
pmirnd
1

votes
0

answer
41

Views

MySQL select not work correctly on fields contain accents

Hi guys i have a problem with my DB, in detail i have this table: CREATE TABLE `energy_vector` ( `id` char(36) NOT NULL, `creation_date` datetime DEFAULT NULL, `modification_date` datetime DEFAULT NULL, `denomination` varchar(255) DEFAULT NULL, `aliases` longtext, PRIMARY KEY (`id`) ) ENGINE=InnoDB...
alvarofvr
1

votes
1

answer
255

Views

MariaDB MySQL Optimisation

I stuck a bit with setting the best performance of MariaDB on my VPS. I have: 6 cores; 16GB RAM; 300GB SSD; CloudLinux; LiteSpeed >> MySQLTuner 1.7.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output fi...
Tom R
1

votes
2

answer
65

Views

I can't understand HQL to SQL translation

Why my simple List products = session.createQuery('FROM Shopping').list(); gives following error: java.sql.SQLException: Unknown column 'shopping0_.product' in 'field list' Here is mapping file Shopping.hbm.xml This class contains the employee detail. The table is: +----------+-------------+------+...
superstate
1

votes
0

answer
88

Views

c# mysql data adding mariadb server version for the right syntax use near

When I want to enter c # you have an error in your sql syntax check the manual that corresponds to your mariadb server version for the right syntax use near '*) from tabloadı where tabloalanı ve eklemek istediğim bilgiler private void btnkaydet_Click(object sender, EventArgs e) { try { if (baglan...
darkanqel
1

votes
0

answer
255

Views

Mysql not starting generating below errors centos

Mysql is not starting generating following error. When I moved ibdata1 file in /var/lib/mysql directory it starts working but this removes my databases. The system encountered an error while starting the “mysql” service with the command “/etc/init.d/mysql start”: The subprocess reported err...
deepsingh
1

votes
1

answer
43

Views

brew - mysql is not available unless the installation account is signed on

I have set up an environment (apache/php/mariadb) on a Mac using Homebrew. All is working great as long as the account I used to perform the install is signed onto the Mac. If the Mac is started and no account has logged on, then I’m unable to access the database over the network. If one of the...
mike
1

votes
2

answer
159

Views

mysql 5.5 to mariadb 10.2.13: 4000% increase in execution time for select

I am facing a performance issue that has manifested itself after a migration from mysql 5.5 to mariadb 10.2.13 . The sizing of both machines including configs is the same. We have noticed that a certain select statement suddenly takes ~20 secs to execute instead of 0.5 sec. The data is purely read f...
Mustermann
1

votes
3

answer
229

Views

ICp 2.1.0.1: Installation failed with error TASK [master: Waiting for MariaDB service to start]

I am installing ICp 2.1.0.1 and I received an error at the TASK [master: Waiting for MariaDB service to start] msg: The MariaDB component failed to start. After this msg the installation completed with failed status. We are installing ICp with 3 Masters, 3 Proxies and 2 Workers. We have 1 IP for VI...
Christiano Monteiro
1

votes
1

answer
35

Views

Search table multiple columns on variable number of parameters

I have a search input box where a user can enter one or more word and the php script should return all the row where all those words are include in any column. Suppose a table like this: |car_id|make|model|year|plate| +----------------------------+ | 1 |Audi|A4 |2010|AAAAA| | 2 |Audi|A4 |2...
1

votes
1

answer
28

Views

Find data between a range of dates

I want to find data with the same sum within 2 days before and 2 days after a specific date. The query below is fetching data from 2017-09-02. I know that means before and after the specific date. SELECT id, datetime_payed, COUNT(data_sum) AS existing FROM transactions WHERE id_user = '1' AND data_...
Erik
1

votes
1

answer
114

Views

Strange errors in postfix mail.warn log

How to avoid getting strange records in the mail.warn file? I started to receive it after Debian upgrade from jessie to stretch and postfix upgrade from 2.11.3 to 3.1.8. It is generated once, just after the system reboot. Despite the strange errors recorded in mail.warn file, it seems that postfix w...
MarioBig
1

votes
1

answer
31

Views

Determine if a record is a descendant of another record using recursive CTE

The following table stores topics in an instruction manual. Each topic has a parent topic except for record id of 1 which is the root. The application allows all topics except for the root topic to have the parent changed, and I will need to prevent an ancestor from being assigned as a descendant...
user1032531
1

votes
2

answer
36

Views

Selecting sub queries using all operator without joining in mysql

I need to display the ID, Name and Date of birth from all workers from department 1 who are younger than workers from department 2. I'm trying to do with without a join. Here's what I have at the minute, it doesn't fail when I run it but it shows all the workers from department 2, regardless of thei...
Ben Cowley
1

votes
0

answer
46

Views

I need a SQL totals table to update automatically with running totals. Should I use triggers or merge (with php)? How do I combine 3 triggers into 1?

I have tried the following three statements and am unsure of how to combine them: CREATE TRIGGER upd_totals AFTER INSERT ON pc12_status FOR EACH ROW INSERT INTO total_hrs (acft_hrs, date_flight, acft_tail) SELECT hrs_end, flight_date, tail_no FROM acft_status INNER JOIN total_hours ON acft_status....
Rachel Hamm
1

votes
2

answer
36

Views

How to map a table in application-memory to queries

This issue has me bugging, and I cant find it on google or anything. Im using MySQL/MariaSQL for this problem. My problem is not related products/categories, but I will use the analogy. Suppose you have a list of records (products), with lots of information (columns) about each (including eg. the am...
Florian Mertens
1

votes
1

answer
67

Views

Remote connection is intermittent

I have a problem with my MariaDB server. Server A Debian: MariaDB + apache2. Server B Debian: apache2 + Remote access to MariaDB from (A) server. The problem is that server B often is losing connections with MariaDB. Sometimes connection is okey but sometimes it shows error: 'Server has gone away'....
Arkadiusz
1

votes
0

answer
237

Views

Change character set for aws rds without restart

I am trying to change the character set for AWS RDS mysql instance. By default, the character sets are as following +--------------------------+-----------------------------------------------+ | Variable_name | Value | +--------------------------+--...
laocius
1

votes
0

answer
47

Views

Is it possible to select the relationship between to nodes in a OQGRAPH query?

I'm playing around with the OQGRAPH storage engine for MariaDB https://mariadb.com/kb/en/library/oqgraph-storage-engine/ and I want to implement relationships between nodes. Example data based on https://mariadb.com/kb/en/library/oqgraph-examples: Table: people_relationships origid destid relationsh...
Mike Reiche
1

votes
1

answer
138

Views

Is there a native graph database extension for MySQL/MariaDB?

I want to store some kind of my relational data into graphs. There are two options: Parent/Child Nested Sets Where Nested Sets are only useful for hierachical data, the Parent/Child model has a huge impact on performance, especially when querying large sets of nodes. I noticed, that MariaDB has a st...
Mike Reiche
1

votes
0

answer
296

Views

Cannot establish local connection MySQL Workbench

I have a problem connecting to my localhost mysql databse with Workbench. Using Debian 9, Workbench is installed from the apt's stable release package. Also I am running MySQL(MariaDB) and Apache2 server. Checked them both and both are running as services, both on the right ports. I have no problem...
Vlad Skurtolov

View additional questions