Database tables map One To Many without foreign key in @JoinColumn

Possible to not have foreign key relationship in child table to its parent in the database, while in JPA it will still be able to map out fine? How will this be done?

Can I still use @JoinColumn to define the field for reference back to the parent table?

This is a one-to-many relationship. One parent record, many child records.

I only care about writes to the database tables right now.

How can I login to MySQL as 127.0.0.1 or ::1 instead of localhost?

I’ve set different password for 'root'@'localhost' and 'root'@'127.0.01' like this:

mysql -u root
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pswd1');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('pswd2');

but when I try to login like this:

mysql -u root -p -h 127.0.0.1

I still have to use the first password pswd1 even though according to this:

enter image description here

another password pswd2 was set for 127.0.0.1.

What is the problem here? How can I set 3 different passwords for ‘root’@’localhost’, ‘root’@’127.0.0.1’ and ‘root’@’::1′ and login to those accounts with those passwords?

Go to Source
Author: JConstantine

MySQL Cluster Data node can’t get the node ID?

I have a MySQL Multi Node ( Cluster ) like this

A is ndb_mgmd and also the client

B and C is ndbd

I have successfully set up everything but the Data Node just cant get the Node Id.

In ndb_mgmd server when I use ndb_mgm and show, I got something like this:

Connected to Management Server at: xxxx:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2    @xxxxxxxx  (mysql-8.0.21 ndb-8.0.21, Nodegroup: 0)
id=3    @xxxxxxxx (mysql-8.0.21 ndb-8.0.21, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @xxxxxxxx  (mysql-8.0.21 ndb-8.0.21)

[mysqld(API)]   1 node(s)
id=4    @xxxxxxxx  (mysql-8.0.21 ndb-8.0.21)

I even show NodeId=2 status and got this :

ndb_mgm> 2 status
Connected to Management Server at: xxxxxxxx:1186
Node 2: started (mysql-8.0.21 ndb-8.0.21)

The problem is in the Ndbd server somehow it cant get the ID:

2020-09-05 22:51:13 [ndbd] INFO     -- Angel connected to 'xxxxxxx:1186'
2020-09-05 22:51:43 [ndbd] ERROR    -- Failed to allocate nodeid, error: 'Error: Could not alloc node id at xxxxxxxxx port 1186: No free node id found for ndbd(NDB).'

Things i have tried :

  1. See if the config.ini got problem but everthing look fine: ndbd nodeid , etc
  2. Check my.cnf in ndbd server : The ndb-connectstring point to ndb_mgm server .

Thanks for reading this question .

Go to Source
Author: Ta219

select from table programatically in mysql

Need something where I can select from a table using a function.

Example:

SELECT * FROM HEX(UNHEX('users')) which should select from a table called “users”

This is just an example but the general idea is I want to be able to use functions to define what table I select from. Or maybe an alternative?

Go to Source
Author: Charlie Wells

Queue Implementation with Custom Consistent Absolute Ordering

H!

I need to create a schema for a queue. Elements in this queue will be processed in an order based on a column. However, this is not always the e.g. date_added column. Users should be able to freely re-order items in the queue, thus setting a custom position – let’s call that field position. Therefore, queue items that have a position >= to the new position I set for a specific item need to be re-ordered on each update.

I really want to avoid using a server-side script to manually go through each of the now outdated elements and updating their position. Is there a way to do this automatically, and with relatively low resource usage? I’m sure people have implemented such a queue in MySQL before, but there don’t seem to be relevant answers anywhere, although the task sounds trivial. Any ideas?

Go to Source
Author: XedinUnknown

MySQL stops in windows VM and shows innodb error log sequence number is in future

2020-08-28 16:46:02 d64 InnoDB: Error: page 1 log sequence number 23113680
InnoDB: is in the future! Current system log sequence number 3181581.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.

I’m using VM and OS is Windows Server 2016 and Uniform Server.
MySQL stops upon this error and doesn’t work until it is restarted.
After restarting, it runs for some hours and stops again.
I have tried moving ib_logfile but still it didn’t help.
How to overcome this?

Go to Source
Author: Anushree Bharadwaj

mysql client auto reconnect in case of ERROR 2013

I’m searching for a way how to instruct mysql client to reconnect in case of ERROR 2013. I’m testing PXC with ProxySQL and want to keep sql statements flowing from the client in case when the writer node get killed and new one is promoted. Is it possible for mysql client to reconnect when server goes down during the query? Can mysql client rerun the sql query (insert, update, …)? With Sysbench it is possible if setting the –mysql-ignore-errors=all parameter.

Go to Source
Author: Sevak

What database and cloud service should I use for an Instagram-like app?

I am a beginner in the world of databases and I’m so confused with the amount of tools available. I am building an Instagram like app in Flutter and wanted to know what database should I study for it?

And which cloud service should I use, AWS or Azure?

Go to Source
Author: Aeden Thomas

How to actually set up MariaDB replication/high availability

I’m setting up a high availability LAMP stack – so far, I have two servers with HAProxy running on both their frontends that own a floating IP. HAProxy redirects requests to backend Apache servers running on each of them (same machine, port 8000).

This all works, and I’m happy with this so far for high availability. I have yet to deal with the databases yet however.

Since I only have two servers, I don’t think I can do master-master like Galera cluster due to the requirement of quorum. So, I planned to do master-slave read only replication.

Most of the guides I see online don’t mention the MaxScale proxy that MariaDB suggests in its high availability guide (page 4 for example)

I’m just really confused overall on what to do and how to set it up?

From my perspective, I’d like to set up a MaxScale proxy on one of my servers and run MariaDB on both servers; MaxScale will handle things like redirects. How can I actually accomplish what I’m looking for?

Go to Source
Author: forkwasher

Combining Two Containers

I’m working on a Windows laptop and running Docker for Windows on it. I want to run an Ubuntu container with a specific version and a MySQL server on it. So the question is, do I have to download MySQL on the Ubuntu container or can I run 2 containers (Ubuntu and MySQL) and combine them? How do I combine these 2 containers?

Go to Source
Author: user19215

mysql has two pid files

I just finished re-building my (5.7) slave from a hot backup. When I issue

 systemctl start mysqld

it just “sat” there for over 20 minutes. It usually takes less than 10 seconds for the service to start. When I look at my server status, I see two mysql processes

mysql     1938     1  0 15:25 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
mysql     1940     1 99 15:25 ?        00:11:48 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root      4024  3122  0 15:37 pts/0    00:00:00 grep --color=auto mysqld

I stopped and restarted my service and there are two different PIDs showing up again

 [root@bi-db002-prd ~]# systemctl status mysqld -l
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: activating (start) since Mon 2020-08-17 15:46:45 UTC; 45s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7066 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
  Control: 7088 (mysqld)
   CGroup: /system.slice/mysqld.service
           ├─7088 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
           └─7092 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Aug 17 15:46:45 bi-db002-prd systemd[1]: Starting MySQL Server...

I have never seen that. Is there something incorrect with my configuration?

Go to Source
Author: A B

Saving a user ID to MYSQL

Hi i have a MYSQL database which i need the logged in user to save information to. this is easy enough but i would like it to automatically save the users_id in the table also so that when i run a report on tables joined i can see who saved what.

Does anyone know how to do this? Below is what i have so far.. I can insert manually but would like the user_id to save from the logged in user.
Thank you in advance for any help.

function edit_rams(){
    include("inc/db.php");
    if(isset($_GET['edit_rams'])){
        $id=$_GET['edit_rams'];

        $get_op=$conn->prepare("SELECT * FROM rams WHERE id='$id'");
        $get_op->setFetchMode(PDO:: FETCH_ASSOC);
        $get_op->execute();
        $row=$get_op->fetch();

        echo"<h2>Edit Operative</h2><div class='cat'><div class='update'>
        <form class='edit_form' method='post' enctype='multipart/form-data'>
        <h2>Confimation</h2>
        <p>Are you sure you Want to complete this RAMS?</p>
        <p>By Agreeing to this you are declaring that you have read and understood everything contained within these RAMS</p>
       <center><button name='add_rams2'>Complete RAMS</button></center>
        </form><br /><br /><br /></div></div>";

if(isset($_POST['add_rams2'])){
            $ram_name=$_session['user(id)'];
            
            $add_rams2=$conn->prepare("INSERT into rams_1 (user_id, ram_id, signature)VALUES('$ram_name', '1', 'steve')");
            
if($add_rams2->execute()){
                echo"<script>alert('Operative Added Successfully')</script>";
                echo"<script>window.open('dashboard.php?test','_self')</script>";
                }else{
                    echo"<script>alert('Operative Not Added Successfully')</script>";
                    echo"<script>window.open('dashboard.php?test','_self')</script>";
                }
            }
    }
}`

Go to Source
Author: steven meadows

Is it normal for MySQL to gradually increase memory usage over time?

Is it normal for MySQL to increase memory usage over time? See image below of my server’s memory usage over the last two weeks. After “service mysql restart” it drops to 40%. The database is used by a node.js app.

Server has 4GB of RAM with below additions to mysqld.cnf:

innodb_ft_min_token_size = 1
ft_min_word_len = 1
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 5
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
max_connections = 300
long_query_time = 1
innodb_ft_enable_stopword = 0

Thank you!

Go to Source
Author: steven

Restaurant reservations – Tables combinations

I have 3 tables in a reservation system for a restaurant. So far the software was used only by restaurant’s staff, however we want to accept reservations online as well. We have some small tables for 2 that can be easily moved to each other and make room for bigger parties and I want to accept reservations automatically if all of the tables that can be combined are available.

tables: holds all tables for each area in the restaurant.

| id | min_capacity | max_capacity | name | area   |
|----|--------------|--------------|------|--------|
| 1  | 2            | 4            | #1   | Inside |
| 2  | 6            | 8            | #2   | Inside |

reservations: holds reservation details

| id | datetime            | name     | status   |
|----|---------------------|----------|----------|
| 1  | 2020-09-01 20:00:00 | John Doe | Upcoming |
| 2  | 2020-09-05 13:00:00 | Jane Doe | Upcoming |

And one pivot table that holds reservation <=> table relation:

| id | table_id | reservation_id |
|----|----------|----------------|
| 1  | 1        | 1              |
| 2  | 2        | 2              |

How can I store different combinations of tables (manually entered) and “attach” reservations to tables/table combinations (so I can check if tables are available for specific time) efficiently?

Go to Source
Author: Clarissa

What is the best database design for storing survey form with different types of questions and answer formats and branching is possible?

I would like to store the format of the survey form which can branch into different question based on
Questions can be video, audio, text and answer can be text, multiple choice, video, audio, geolocation etc. Also based on the answers of a question branching into different question should be possible. It should also be possible for user to fill the form in multiple session so some state should also be there. So the answers to the columns can be missing due to branching as well as the response being incomplete. There is a need of fast filtering and analysis of the database. Also, it should be possible to extract all the responses of a particular form in CSV file. What would be the best implementation for this problem?

Go to Source
Author: Shrey Paharia