How to find Default Access Privileges on PostgreSQL with pg_default_acl

This is a basic code sequence to set the Default Access Privileges on a PostgreSQL database. This example defines future privileges for future objects for this user. But what if you want to view the stored information about default access privileges ? There is a view called pg_default_acl ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO myuser; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL PRIVILEGES ON SEQUENCES TO myuser; If you want to view the default access information stored you can use the PG_DEFAULT_ACL view. The default record set i.e SELECT * FROM PG_DEFAULT_ACL will... Read more →

MongoDB 4.2.7 Bug - Off-by-one overflow in block-modification bitmaps for incremental backup

A bug on MongoDB 4.2.7 has been identified- and a fix is included in 4.2.8 .Certain conditions are required for the bug to appear - detailed below. Based on the supporting documentation for the MongoDB 4.2.7 bug to occur: Must be running MongoDB 4.2.7. Managed by Ops Manager 4.2.13+ or managed in Cloud Manager with MongoDB agent version v10.14.0+. Incremental backups are enabled. The DB server has collection files greater than 2GB on disk. These collections are modified anytime after backups are enabled. If these conditions are met , there is a possibility that MongoDB will improperly overwrite memory. Read... Read more →

Failed: error dumping users and roles: error writing data for collection with mongodump

Question: I've started the a MongoDB migration using the mongodump module, but when executing the command : mongodump --username="my_un" --password="my_pw" I receive the following error message. Failed: error dumping users and roles: error writing data for collection `admin.system.users` to disk: error reading collection: Failed to parse: { find: "system.users", skip: 0, snapshot: true, $readPreference: { mode: "secondaryPreferred" }, $db: "admin" }. Unrecognized field 'snapshot'. I've executed the same command on other installations with no issues? Is there quick fix for this error message? Answer: I've seen this error before and can be related to the default usage of _id during... Read more →

Easy fix for ERROR 1010 (HY000): Error dropping database

Question : I'm trying to drop a database in MariaDB with the following command : DROP DATABASE mydatabase, but getting an error message : ERROR 1010 (HY000): Error dropping database (can't rmdir './mydatabase', errno: 39 "Directory not empty") What is stopping the database from being dropped? Answer: MariaDB represents a database in the data directory. When you're executing the DROP DATABASE command - you're making a request to delete the table files - followed by removal of the directory. The DROP DATABASE command - does not drop non table file - therefore it cannot progress to the remove directory step.... Read more →

Overcome the MongoDB Backup is down alert

I've set up a new MongoDB single node replicaset using MongoDB 4.2.7. Although I'm using the same server and agents configuration settings as other replicasets in the same deployment - there is a regular MongoDB alert being triggered. Backup is down Backup Agent Alert Details CREATED 2020/05/29 13:12 GMT DATA-BEARING HOSTS (INSTALL AGENT ON AT LEAST ONE) rsdevelop (at least one of the following mongod(s) needs a co-located agent: [muserver.xxxxx.net:27017]) OPEN The scheduled backups are being completed , and there aren't any errors. Why is the Backup is down alert being triggered? I checked through the MongoDB documentation and the... Read more →

How to migrate databases with mongodump and mongorestore

For small deployments , the MongoDB utilities mongodump and mongorestore is an efficient and useful method. The steps below outline how to take a backup of the MongoDB databases on server1 and migrate\restore onto server2 The mongodump utility can create a backup for a server, individual databases or collection(s), or a customised query for a collection subset. In this example - I'm taking a mongodump of all the user databases including admin on MongoDB - server1. I'm using a logon account with sufficient privileges , that has at least backupadmin privileges Step 1 - This command backups all the databases... Read more →

How to edit a user password with MongoDB Ops Manager

Question: I have a list of MongoDB Users refreshed and appearing in the Deployment | MongoDB Users Screen. These were all users created directly on the MongoDB server outside of the MongoDB Ops Manager. When the users were refreshed the Synched status is set at No. What I'm trying to do is change the password for the user via the MongoDB Ops Manager - but without having to do it at the MongoDB server. The user is created in 3 replica sets - and I'd like to maintain the same password in all the 3 replicasets. Is it possible to... Read more →

MariaDB is number function

Question: I am creating a View in MariaDB to to streamline a recordset requested by an application. As part of the View - there is a requirement to create some CASE statements and check to assess whether the data is numeric. How can I create a function to check if the data is numeric? Answer: MariaDB has plenty of Numeric functions - which process Functions dealing with numerals, including ABS, CEIL, DIV, EXP, PI, SIN, etc - but you are looking for a function - which checks to see if some data is a number. MariaDB has good support for... Read more →

How to make MySQL tables case insensitive

Question: I'm working with vendor supplied ddl scripts, which are causing case-sensitive issues. Early in the scripts - there is a CREATE TABLE statement . But further down the script there is an INSERT statement - but the case of the table is lower - causing an error . How can I fix this issue? Answer:To fix this issue - change the "lower_case_table_names" variable . To see the current value of lower_case_table_names use : show variables like '%lower_case_table_names%' To solve this problem - you will need to change the value to "1" . Find the location of the "my.cnf" file... Read more →

MariaDB EXPLAIN same query - different results on different servers

Same data , same queries , different response time. Server 1 is MariaDB version 10.2.6 using the innodb version 5.7.14 Server 2 is MariaDB version 10.3.17 a using the innodb version 10.3.17 There is a VIEW and within the VIEW there is a UNION covering two SELECT statements . The underlying dataset is greater than 100 million rows.Note: Dataset is refreshed twice a year - Since the migration to Server 2 - the index from the table 1 is not used , causing a significant drop is response time for certain queries. The Execution Plan is now forcing a Table... Read more →