MySQL Database Schema recommendations? – Database Administrators Stack Exchange

enter image description here

Hello, all! I have several needs that I’m attempting to accomplish with my database implementation.

  1. List number of wins / losses for a particular user
  2. Keep track of the amount of coins a user has
  3. Allow user to purchase items from a shop
  4. List profitability from all gambling-based games

For my use case, I have several games the end user can play. The “Fun” category of games does not award ‘coins’; however, I want users to be able to list how many they’ve won. All the gambling-based games allow players to earn / lose their ‘coins’ when playing.

  • Fun Games:

    • Rock, Paper, Scissors
    • Connect Four
    • TicTacToe
  • Gambling Games

    • Slot Machine (Slots)
    • Black Jack
    • HiLo
    • Coin Flip

After doing some research online, I have implemented a ‘match’ table to keep track of all the matches between players. With two player game sessions, two rows will be created. If I want to know how many wins a player has, I can use a sum(wins) from the match table. Additionally, from the ‘gambling’ match table, I can run a similar query to pull the profitability loss / gain. Is there a better way of doing this?

Additionally, if a user profited from a gambling match – I’m assuming I’d need to perform two operations:

  • Add new row in GamblingMatch
  • Update amount of coins a player has

At this moment, I’m not sure of a way to do that in one operation.

Restore MySQL database partially – Database Administrators Stack Exchange

I am using WordPress to run my website, along with the traditional MySQL Databases. Whilst seeking to speed up my website, I decided to clear some of the old and unused tables with WP-Optimizer. Sadly, it seems that I have deleted something I shouldn’t by mistake, since it seems I have partially broken Elementor Plugin.

I luckily have a database backup, but it is a few days old and, given there are a lot of modifications done on a single day, I do not wish to simply import that backup; what I’d like, is to compare my website’s database with my backup and restore the missing tables only, after thorough examination of their content.

I am no SQL expert at all and looking for guidance.

mysql – innodb_force_recovery – Database Administrators Stack Exchange

Stack Exchange Network


Stack Exchange network consists of 176 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

mongodb query performance – Database Administrators Stack Exchange

I’m experiencing slow performance when testing simple queries on Mongo. Here’s the context:

  1. A 8 vCPU, 16 GB RAM VM (ESXi) running Debian 10 / MongoDB Community Edition 4.4.1 as a standalone server.
  2. Debian has been configured with XFS and with the limits set to the recommended values noted here: https://docs.mongodb.com/manual/reference/ulimit/#recommended-ulimit-settings
  3. I have two collections, Sales Order and Quote, where the Quote collection data contains IDs for the Sales Order collection in a one-to-one relationship.
  4. Both collections have ~50k records and each is roughly 10MB in size according to Compass.
  5. Both collections’ ID fields are indexed as well as the field in the Quote collection that points to the Sales Order collection.
  6. The database is being accessed by .NET driver which has recently been updated using NuGet.
  7. The server hosting MongoDB is hosted on-prem on a gigabit network.

I understand that relationships are not the focal point of the design of NoSQL databases, so here’s an example that doesn’t even take the join into account.

It takes 1.7 seconds just to have MongoDB bring back all ~100k documents across the two collections totaling 20MB. The amount of time to bring that much data over the wire/network is ~0.16 seconds, so it isn’t a huge contributing factor.

Access 2016, a product that isn’t meant for production, brings back both tables joined in less than a quarter of a second. (It takes Mongo 3.7 seconds). Now, it doesn’t have to transfer the info over the wire so we can add 0.16 seconds to that, but it’s still wildly off.

Considering that I’ve read plenty that Mongo can do some amazing things, including in the performance category, I must be doing something wrong.

What could cause the results I’m getting?

SSRS Design Report – Database Administrators Stack Exchange

I am new in SSRS Reporting services and I would like to know if its possible to create the reports

SSRS Report image

We need one excel report to contain all the other reports connected to one SSRS Report.

I have many reports in SSRS. I need to create one report and it will contain all the data for each organization and create it as a package.

Could some one please suggest me how to do so?

enter image description here

Many thanks in advance.

rowid in sqlite – Database Administrators Stack Exchange

I have a large and growing table I’m maintaining in sqlite (250M+ rows). I am using this as archival backup for working data on a different system (in postgres). Generally speaking, archive size is more important than archive performance. Therefore, the sqlite db is unindexed.

Every record has a timestamp (stored in epoch ms), and data is never inserted out of date order. Multiple records can have the same timestamp, but the timestamp will only increase over time. Occasionally records will be deleted.

To validate that the archive is tracking with my production data, I perform a “checksum” of sorts, counting the number of records in a given date range, and comparing the archive to the production dataset. Doing a select count(*) where ts > nnn is slow on the archive (as expected), but it occurs to me that I might be able to use rowid to do a binary search for the first record where ts = nnn and then (perhaps) subtract rowids to get my count of records much faster than the ordinary select would, kind of like a home grown index (or, at very least, allow me to restrict the part of the database that needs to be searched by adding where rowid > mmm and rowid < ooo to the query.

So, some questions:

  1. Is rowid maintained in “insertion order” such that if my data is inserted in order of timestamp, I can assume that a higher rowid will never have a lower timestamp?
  2. Can I therefore implement my own binary search to rapidly find a record with a particular ts? (or use max(rowid) to find the maximum timestamp)?
  3. Will deleting records create “holes” that sqlite will attempt to “fill in” with subsequent insertions?
  4. What events would cause the rowid of a given record to change?
  5. If I do decide to insert data out of date order, how can I sort the data and reset the rowids to account for that?
  6. Is there a less janky way to leverage the fact that my data is sorted to get at least some of the benefits of indexing without rolling my own?
  7. Is there a better way than counting to validate that records in a specified date range stored in databases on two different systems, one in postgres, one in sqlite, contain the same data? (I presume they do, but I’m a paranoid sort, especially around the idea that my syncing process might somehow drop records. I’m less worried that two corresponding records would contain different values).

A few more probably irrelevant details: The postgres production system is on a very space-constrained machine, and can only hold about 6 months of data. The sqlite database is on a very slow machine with tons of disk space, and holds the total archive going back several years. Because I occasionally share the entire archive with other researchers, sqlite is a convenient container, but I don’t index because I want the files to be small when I back them up and ship them around. The only work I do on the archival dataset is add records and verify that the data has been properly backed up, as described above. Copying the archive locally (to, say, make a replica and strip out an index before compressing) is a very slow operation on the archive machine.

UserNotFound mongodb error – Database Administrators Stack Exchange

I know there are lot of articles with the same issue and I explored every other article trying everything and still this error persists.

  • use admin.

  • created an admin user with role: dbAdminAnyDatabase

  • use mydb.

  • created an admin user with different name for mydb

  • created a readonly user with: db.createUser({user: "nameuser", pwd: "password", roles: ({role: "read", db: "mydb"})});

  • setup a mongod.conf file with:

  • security:
    authorization: enabled

    net:
    port: 12345
    bindIp: 0.0.0.0 #default value is 127.0.0.1

  • then run the command: sudo mongod --port 27017 --dbpath ~/data/mongodb --config /etc/mongod.conf

  • and then on localhost i run the command to check:

    mongo -u nameuser -p password localhost:12345/mydb

  • then the error come as:

    connecting to: mongodb://localhost:12345/mydb MongoDB server version: 3.6.3 2020-10-04T03:05:37.054+0530 E QUERY (thread1) Error: Authentication failed. : DB.prototype._authOrThrow@src/mongo/shell/db.js:1608:20 @(auth):6:1 @(auth):1:2 exception: login failed

  • and on server side the error looks like this:

    2020-10-04T02:53:11.835+0530 I NETWORK (listener) connection accepted from 127.0.0.1:60240 #3 (1 connection now open) 2020-10-04T02:53:11.835+0530 I NETWORK (conn3) received client metadata from 127.0.0.1:60240 conn: { application: { name: "MongoDB Shell" }, driver: { name: "MongoDB Internal Client", version: "3.6.3" }, os: { type: "Linux", name: "Ubuntu", architecture: "x86_64", version: "18.04" } } 2020-10-04T02:53:11.836+0530 I ACCESS (conn3) SCRAM-SHA-1 authentication failed for nameuser on mydb from client 127.0.0.1:60240 ; UserNotFound: Could not find user nameuser@mydb 2020-10-04T02:53:11.837+0530 I NETWORK (conn3) end connection 127.0.0.1:60240 (0 connections now open)

  • Now please tell me if I can try anything new. I have tried so many articles from stackoverflow but none address the issue. Every resolved issue is the common mistake which I have checked already and I am not doing any of these. Tell me if you have any suggestion to work this out.

    files – How do I exempt Administrators from a Filescreen made specifically to limit users? (Windows Server 2008 r2)

    Using FSRM I’ve been able to block a certain file type from being generated (*.sync) from an application. However I’d like this limitation to only apply to users and not Administrators, they SHOULD have the ability to generate these files. How do I go about exempting Administrators? I’m on Windows Server 2008 R2 VM running on Azure (if that helps any).

    SQL Server Setup Failure – Database Administrators Stack Exchange

    Unable to upgrade the enterprise edition of SQL Server 2014 SP1 (12.1.4100.1) to SP3 version. I checked the registry key “ConfigurationState” and it’s already 1 but still got the below error

    “SQL server setup has encountered the following error:

    NT services SQLAgents$instancename could not be started.

    Error code 0x84B20001″

    Any help is appreciated

    Can database administrators change physical schema?

    I was reading the Chapter 1 of the 7th edition of Database System Concepts and ran into this:

    Schema and physical-organization modification. The DBA carries
    out changes to the schema and physical organization to reflect the
    changing needs of the organization, or to alter the physical
    organization to improve performance.

    Is it true that database administrators can access the physical schema and change it or have I misunderstood what is cited above?