transaction log growing out of control during maintenance I have been asked to clean up preceding and appending spaces in several columns of a table.  the table is an import from flat file and has many mistyped rows.

The table has over 150 million rows.

So far I have attempted performing an update statement that updates the columns using ltrim(rtrim(columnname)) and I have also tried to create a temp table (heap) and insert into ... select from using the same ltrim(rtrim(columnname)) syntax.

In every case the transaction log grows out of control to the point of running out of disk space.

I understand how transaction logs work fairly well.  However in the context of performing some kind of bulk maintenance like this, I am stumped.

Currently the database is in bulk-logged recovery model, I am running transaction log backups every 30 seconds and the transaction log continues to grow faster than I can back it up.  I have researched how I might break this job up into batches, but cannot seem to formulate a query that could do this effectively.  I have an identity field I could key off of, all the other columns are varchar columns.  Right now I'm thinking the answer is to simply get more disk space for the server, however I was hoping there was a better solution.

I am using Microsoft SQL Server 2014 Enterprise Edition.

The flat file is long gone; this is a table that has grown in size over the course of 10 years and many versions of SQL Server.  only smaller incremental batch updates are performed and I do believe the new inserts are already cleansed before inserting.  So I am fixing old stuff :(

You should be able to do batch updates to this table without causing the log to grow uncontrollably.

Here is a simple testbed to show how I'd approach this:

USE tempdb;

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(
    id int NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , COL1 nvarchar(150) NOT NULL
    , COL2 nvarchar(150) NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.Table1 (COL1, COl2)
SELECT N'     ' + sc1.name + N'     '
    , N'     ' + sc2.name + N'     '
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

GO

This actually removes the leading and trailing spaces, 4,000 rows per batch:

DECLARE @LogFreePercent DECIMAL(5,2);

/* this temp table will hold all the ID values for
   rows we need to update
*/
IF OBJECT_ID(N'tempdb..#ids', N'U') IS NOT NULL
DROP TABLE #ids;
CREATE TABLE #ids
(
    id int NOT NULL
) ON [PRIMARY];
CREATE CLUSTERED INDEX #ids_cx ON #ids(id);

/* this temp table will hold all the ID values for
   rows we've already updated
*/
IF OBJECT_ID(N'tempdb..#fixed_ids', N'U') IS NOT NULL
DROP TABLE #fixed_ids;
CREATE TABLE #fixed_ids
(
    id int NOT NULL
) ON [PRIMARY];
CREATE CLUSTERED INDEX #fixed_ids_cx ON #fixed_ids(id);

/* Insert the ID values for rows with leading or trailing
   spaces.  This is not efficient, but should do the trick,
   and ensures we only update rows that actually have leading
   or trailing spaces.
*/
INSERT INTO #ids (id)
SELECT id
FROM dbo.Table1 WITH (NOLOCK)
WHERE COL1 LIKE ' %'
    OR COl1 LIKE '% '
    OR COL2 LIKE ' %'
    OR COl2 LIKE '% ';

/* Run a loop while we still have IDs that need processing
*/
WHILE EXISTS (SELECT TOP(1) 1 FROM #ids)
BEGIN
    /* Use a CTE to select 4000 rows to update from the
       temp table, outputing the IDs we've fixed into
       the #fixed_ids table.
    */
    ;WITH src AS (
        SELECT TOP(4000) Table1.*
        FROM dbo.Table1
            INNER JOIN #ids i ON i.id = Table1.id
    )
    UPDATE src
    SET COL1 = LTRIM(RTRIM(COL1))
        , COL2 = LTRIM(RTRIM(COL2))
    OUTPUT inserted.id
    INTO #fixed_ids;

    /* Show some status details
    */
    PRINT (CONVERT(nvarchar(30), GETDATE(), 120) + N' - updated '
        + CONVERT(nvarchar(20), @@ROWCOUNT) + ' rows.')
    SELECT @LogFreePercent = 100 - su.used_log_space_in_percent
    FROM sys.dm_db_log_space_usage su;
    PRINT (N'Log free percent: ' + CONVERT(nvarchar(10), @LogFreePercent));

    /* remove the processed rows from the temp tables
    */
    DELETE FROM #ids
    WHERE EXISTS (SELECT 1 FROM #fixed_ids fi WHERE fi.id = #ids.id);
    DELETE FROM #fixed_ids;

    /* checkpoint the database to allow log records to be truncated
    */
    CHECKPOINT 1;
END

Run this afterwards to see if any rows have leading or trailing spaces:

SELECT [1] = '|' + t1.COL1 + '|'
    , [2] = '|' + t1.COL2 + '|'
FROM dbo.Table1 t1;

You may notice I've got the dreaded WITH (NOLOCK) in the query that reads from the source table, dbo.Table.  This is to help prevent our process from blocking other ongoing transactions.  Since we're only using this to create a list of IDs we want to operate on, this is an acceptable use of READ UNCOMMITTED.

In my slow dev database, this processed 564,000 rows in just under three minutes, and the log never dipped below 97% free.


how to use fn_xe_file_target_read_file to read files over network We are running Microsoft SQL Server 2014, and SQL Server Analysis Services 2014.

I am trying to setup a system to provide us with analytics concerning our SSAS OLAP cubes adoption and usage. I have setup a an Extended Event trace with the events that I need to track and it is creating a bunch of .xel files on the server like it should.

The issue I am having now is that we do not have a SQL instance on that server, so I need to load the files into a database on a different server. I can load the files if I copy them over to the SQL server and call the sys.fn_xe_file_target_read_file function with a local path, but I get an error when I try to read the file with a UNC path.

  The log file name "\\SERVERNAME\f$\ExtendedEvents\MyTrace_0_12345.xel" is invalid.  Verify that the file exists and that the SQL Server service account has access to it.

I have configured folder security on the SSAS server to allow the domain account that our SQL server is running under to have access to the folder. So that shouldn't be a problem. I am making the assumption that this function does not like UNC paths, so my next step would be to map a network drive on the SQL server machine so I can pass a local path to this function.

Before I request this from our DBAs I wanted to reach out to see if my assumption about the UNC paths is correct, and if any one else has worked around a similar issue by mapping a network drive to the remote share. I have read through the MSDN documentation for the function, but I cannot see anything to indicate how I would read a file on a network share.

As Kin suggested in his comment, the F$ share is problematic since that only allows administrator access.  Although you've provided file-system access to the folders in question, the account does not have access to the share.  Create a share specifically for the xel files, and provide access to the SQL Server Service account on both the Share, and the folder.

Using the Universal Naming Convention name, or UNC, is preferable to accessing the file via a mapped-network-drive.  A mapped network drive just obfuscates the true location of the file, and you'll need to somehow create the mapping inside the profile for the SQL Server Service account (that's what Kin is suggesting with the net use command he shows in his comment.)

My SQL Server 2012 instance reads files across the network perfectly like this:

SELECT *
FROM sys.fn_xe_file_target_read_file(
    '\\machine\share\ErrorLogs\system_health_*.xel' --this is the UNC
    , null
    , null
    , null
    );


Best patterns for encrypting database columns/tables in SQL Azure Azure SQL doesn't support many of the encryption features found in SQL Server (Table and Column encryption). What is the best practice for doing application level encryption into a database that doesn't support encryption? Also need

Key rotations
metadata mapping of which tables and which columns are encrypted. This is simple when it's just couple of columns (send an email to all devs/document) but that quickly gets out of hand

If someone would like to recommend a library, I'd be happy to stay away from "DIY" too.

I think that your problem can be solved with the MS framework called "Trust Services".
Buck Woody explains in this article how you can use it to solve problems of encrypted data with SQL Azure. From his article, a short description about how to use Trust Services:

  "With the new Trust Services service, the basic process is that you
  use a Portal to create a Trust Server using policies and other
  controls. You place a X.509 Certificate you create or procure in that
  server. Using the Software development Kit (SDK), the developer has
  access to an Application Layer Encryption Framework to set fields of
  data they want to encrypt. From there, the data can be stored in SQL
  Azure as a standard field – only it is encrypted before it ever
  arrives."

Further reference:

Learn More about Microsoft Codename "Trust Services"
Using Certificate-Based Encryption in Windows Azure Applications
Crypto Services and Data Security in Windows Azure
SQL Azure: Securing SQL Azure


Restrict Postgres roles by IP address outside of the pg_hba.conf file I am currently adding Postgres support to a deployment tool that currently only supports MySQL. This tool dynamically provisions and configures a database, among other things.

In MySQL, we can restrict users to a particular set of IP addresses, with SQL, as part of the grant command like so:

GRANT ALL PRIVILEGES ON my_database_name.* TO 'my_user_name'@'192.168.101.%' IDENTIFIED BY 'my_secret_password'
GRANT ALL PRIVILEGES ON my_database_name.* TO 'my_user_name'@'internal.example.com' IDENTIFIED BY 'my_secret_password'

In Postgres, it seems like the only way to set up a similar arrangement is to first create the user without restriction:

CREATE USER "my_user_name" WITH PASSWORD 'my_secret_password'

I've got it sorted out how to then programmatically restrict this role to a particular database, but it seems like the only way to restrict the Postgres user by host is to create entries in the pg_hba.conf file.

This is problematic, as there are many users/hosts being created programmatically by an agent that does not have file-system access.

It looks like our only three options around this are to:

Manipulate the pg_hba.conf without using SQL to restrict a role, after it has been created using SQL
Allow "all", instead of particular users, for an allowed host
Restrict access to localhost only, and connect using a ssh tunnel

This seems less than ideal, and makes me wonder if I am overlooking an obvious solution. Is it possible to restrict Postgres roles by IP address using only SQL commands?

There is no way to do this with native SQL in PostgreSQL. Also, Postgres manages the privileges and access in a very different way than MySQL.

I've built a VERY BASIC function (needs a lot of tweak and work around, specially handlers when the user already exists) that it adds straightly a user into the pg_hba file, create the user and grant the access to the database.

CREATE OR REPLACE FUNCTION addRemoteUser(
  username text,
  iptarget text DEFAULT '0.0.0.0/0',
  dbtarget text DEFAULT 'all',
  passtext text DEFAULT 'CHANGEME',
  methodauth text DEFAULT 'md5')
  RETURNS boolean
AS $$
  import os
  hbaLine = "host " + str(dbtarget) + "\t" + str(username) + "\t" + str(iptarget) + "\t" + str(methodauth)
  createuser = "CREATE USER " + str(username) + " WITH PASSWORD '" + str(passtext) + "'"
  grantuser = "GRANT connect ON DATABASE " + str(dbtarget) + " TO " + str(username)
  hbaFilePath = plpy.execute("select setting from pg_settings where name = 'hba_file'", 1)
  hs = open(hbaFilePath[0]['setting'],"a")
  hs.write(hbaLine + "\n")
  addUserCommand = plpy.execute(createuser, 1)
  grantUserCommand = plpy.execute(grantuser, 1)
  reload = plpy.execute("select pg_reload_conf()", 1)
  hs.close()
  return True
$$ LANGUAGE plpythonu;

(also posted here on GitHub.)

Obviously is not for production yet, but at least you can have the rough idea how to build the function to do so. It needs PL/Python on the database for this particular example.

I know is not the ideal for this particular case, however I think it's a valid example on how to extend your Postgres installation.

Then, once the function is compiled, you can use SQL to call the function:

SELECT addRemoteUser('mynewuser');


How do I make this Postgres view that performs a join updatable? I've got two tables, products and subscriptions:

CREATE TABLE products (
    id bigint NOT NULL,
    title character varying(75),
    description text,
    manufacturer_id bigint,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    mpn text,
    visible boolean DEFAULT false NOT NULL
);

CREATE TABLE subscriptions (
    id bigint NOT NULL,
    product_id bigint NOT NULL,
    user_id bigint NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

In my application I usually need to know the number of subscribers for a product, and it is tricky sprinkling this logic in the application in all the correct places. So I would like to replace the products table with a view that already includes that information.  So I did this:

ALTER TABLE ONLY products
  RENAME TO products_raw;

CREATE VIEW products AS
  SELECT products_raw.*, COALESCE(a.subscriptions_count, 0) AS subscriptions_count
  FROM products_raw
  LEFT OUTER JOIN (
    SELECT b.product_id, COUNT(*) subscriptions_count
    FROM   subscriptions b
    GROUP BY b.product_id
  ) a ON a.product_id = products_raw.id;

However the products view is not auto-updatable due to the JOIN - I would like any INSERT/UPDATE/DELETE actions to instead be performed on the products_raw table, ignoring the virtual subscriptions_count when applicable.

This is the first time I've created a view or rule, but I attempted this rule:

CREATE RULE products_insert_rule AS ON INSERT TO products DO INSTEAD
  INSERT INTO products_raw VALUES(NEW.*);

However Postgres doesn't like that:

PG::SyntaxError: ERROR:  INSERT has more expressions than target columns
LINE 13:         INSERT INTO products_raw VALUES(NEW.*);
                                                 ^

I also tried writing an INSTEAD OF INSERT trigger:

CREATE FUNCTION products_insert() RETURNS trigger AS $$
BEGIN
  INSERT INTO products_raw VALUES (NEW.*);
  RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER products_insert INSTEAD OF INSERT ON products
  FOR EACH ROW EXECUTE PROCEDURE products_insert();

but got essentially the same error message:

PG::SyntaxError: ERROR:  INSERT has more expressions than target columns
LINE 1: INSERT INTO products_raw VALUES (NEW.*)
                                         ^
QUERY:  INSERT INTO products_raw VALUES (NEW.*)
CONTEXT:  PL/pgSQL function products_insert() line 3 at SQL statement
: INSERT INTO "products" ("title", "description", "created_at", "updated_at") VALUES ($1, $2, $3, $4)

I'm using Postgres 9.4.  Any tips would be appreciated.

Edit: I got a bit closer with this trigger, but it's still erroring due to me not being sure how to cast the products_raw rowtype to a products.  I also don't like the direction this is going with having to repeat default values (including the id sequence)...

CREATE FUNCTION products_insert() RETURNS trigger AS $$
DECLARE
    p products_raw%ROWTYPE;
BEGIN
  IF p.id IS NULL THEN
    p.id = NEXTVAL('products_id_seq');
  END IF;
  IF p.visible IS NULL THEN
    p.visible = false;
  END IF;
  INSERT INTO products_raw VALUES(p.*);

  RETURN p;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER products_insert INSTEAD OF INSERT ON products
  FOR EACH ROW EXECUTE PROCEDURE products_insert();

The error I get with the above trigger is

PG::DatatypeMismatch: ERROR:  returned row structure does not match the structure of the triggering table
DETAIL:  Number of returned columns (8) does not match expected column count (9).
CONTEXT:  PL/pgSQL function products_insert() during function exit
: INSERT INTO "products" ("title", "description", "created_at", "updated_at") VALUES ($1, $2, $3, $4)

You can't use NEW.*, because the view has columns that don't exist in the underlying table.  You have to explicitly list out the columns you want to add.  As people explained above, the reason this didn't work is that you did not provide a default.  You might want to consider using COALESCE() for this purpose.


Are transactions in PostgreSQL via `psycopg2` per-cursor or per-connection? I'm doing some work with PostgreSQL 9.3 using the psycopg2 database API.

I have the DB API set in the minimum isolation level ("autocommit" mode), and am managing my own transactions directly via SQL. Example:

cur = self.conn.cursor()
cur.execute("BEGIN;")
cur.execute("SELECT dbId, downloadPath, fileName, tags FROM {tableName} WHERE dlState=%s".format(tableName=self.tableName), (2, ))
ret = cur.fetchall()
cur.execute("COMMIT;")

Basically, Is the transaction that is started by the cur.execute("BEGIN;") limited to just that cursor, or is it for the whole connection (self.conn.cursor())?

Some of the more complex things I am doing involve multiple separate database operations, that I logically break down into functions. Since this is all in a class which has the connection as a member, it's a lot more convenient to create cursors within each function. However, I'm not sure how creating cursors within a transaction works.

Basically, if transactions are per-connection, I can just create lots of cursors on-the-fly within the transaction. If they're per-cursor, that means I have to pass the cursor around everywhere. Which is it?

The documentation does not touch on this, though the fact that you can call connection.commit() makes me fairly confident the transaction-control is per-connection.

Transactions are per-session, i.e. per-connection.

PostgreSQL doesn't support suspending and resuming transactions, so psycopg2 couldn't make them per-cursor unless it implicitly created new connections behind the scenes.

In practice I don't find psycopg2's cursors particularly useful. They can retain result sets if you're not using incremental fetch from the server, but I don't find them good for much else.

Why manually issue begin and commit though, rather than using the connection methods for them?


In mysql master-slave Replication which server requests the other? I want to setup mysql replication between two servers one of them is my localhost and the other is online server.
I have all availability to make any one of them the master.
But according to that my localhost server doesn't have a static IP, i need to know which server of the two (master & slave) is the one which requests the other for doing updates.
Does the master sends the binlog updates, or the slave is the one which requests for new updates periodically ? so i will make it the localhost.
thank you in advance.

according to this reference: Get MySQL Replication up and running in 5 minutes, Slave must access master to initialize.
slave> CHANGE MASTER TO MASTER_HOST='192.168.0.31',
 -> MASTER_USER='repl_user',
 -> MASTER_PASSWORD='billy',
 -> MASTER_LOG_FILE='',
 -> MASTER_LOG_POS=4;

So, you can setup slave in localhost and master in remote host with accessible IP and port.
This is a client server communication:


how to resolve ORA-04042: procedure, function, package, or package body does not exist and ORA-00942: table or view does not exist I am trying to grant EXECUTE and READ privilege on two tables to a user ktest1.
grant EXECUTE on SYS.KIR_DOKUMENT to ktest2;
grant READ on SYS.KIR_DOKUMENT to ktest2;

when i give grant execute i get below error
grant EXECUTE on SYS.KIR_DOKUMENT to ktest2
Error report -
ORA-04042: procedure, function, package, or package body does not exist
04042. 00000 -  "procedure, function, package, or package body does not exist"

when i give grant read i get below error
grant READ on SYS.KIR_DOKUMENT to ktest2
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:

But i checked the status of the table in dba_objects and they are valid.
the type here is directory.

Kindly advice.

That is not how you grant privileges on a directory.
SQL> create or replace directory KIR_DOKUMENT as '/tmp';

Directory created.

SQL> select owner, object_type, status from dba_objects where object_name = 'KIR_DOKUMENT';

OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE             STATUS
----------------------- -------
SYS
DIRECTORY               VALID

SQL> grant read on SYS.KIR_DOKUMENT to bp;
grant read on SYS.KIR_DOKUMENT to bp
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant execute on SYS.KIR_DOKUMENT to bp;
grant execute on SYS.KIR_DOKUMENT to bp
                     *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

Use the GRANT privilege ON DIRECTORY ... as:
SQL> grant read on directory SYS.KIR_DOKUMENT to bp;

Grant succeeded.

SQL> grant execute on directory SYS.KIR_DOKUMENT to bp;

Grant succeeded.

SQL>


Hybrid Synchronous / Asynchronous Replication? We have a situation where we would like to have synchronous replication in our main data center, but replicate asynchronously to another region. Our second data center is 3,000 miles away (Site B) and we use it as a warm backup in case we need to failover our primary data center (Site A) due to a data center failure.

We are not storing any financial or transactionally sensitive data. Our data is important, of course, but it is not critical to that degree, it consists primarily of updates to website content from content editors.

We are interested in using either Percona XtraDB or Galera Cluster in Site A mixed with something like MySQL, MariaDB or Percona Server for Site B, but don't know if this is even possible or how we might implement such a solution. Can we have a synchronous replication scheme in Site A with an asynchronous backup to the Site B servers? Would we be able to maintain a multi-master implementation in Site A with replicas in Site B?

If it is possible, I assume it would require multiple technologies. What technologies or configurations are feasible solutions?

It's simpler that you may expect.

Use one of the nodes in the Galera/PXC cluster as the master for the async replica.

The node acting as master for the slave needs the following additional configuration parameters for this to work:

log_bin
log_slave_updates
binlog_format = ROW

Connect an asynchronous MySQL/Percona/MariaDB slave to it using a normal asynchronous master/slave replication configuration.

That should be about it.  The slave ideally will be the same flavor and release series as the cluster nodes, either MariaDB or Percona on both ends, though a MariaDB slave is pretty much always a safe bet, regardless of the master.  In no case should the slave run an older version, so when you do upgrades, always upgrade the slave first.  You can also subtend additional slaves from the first one, or connect a second slave to a different cluster node as its master.

Bonus: enable slave_compressed_protocol on the slave so that the data streamed from the master is compressed on the wire.


Model multiple 1-m relationship to a single entity I have an entity called Box. This box has a from property which could be from a variety of different entities. It could be from a user, office, import, storage and many more.

So far I can think of two ways to model this:

Approach one:

| box_id | box_content | from_user | from_office | from_import | from_storage | ...  |
|--------|-------------|-----------|-------------|-------------|--------------|------|
| box_1  | apple       | user_a    | null        | null        | null         | null |
| box_2  | orange      | null      | null        | import_b    | null         | null |

Approach 2:

BOX
| box_id | box_content |
|--------|-------------|
| box_1  | apple       |
| box_2  | orange      |

USER
| user_id | name  |
|---------|-------|
| user_a  | Rick  |
| user_b  | David |

USER <-> BOX
| user_id | box_id |
|---------|--------|
| user_a  | box_1  |

IMPORT <-> BOX
| import_id | box_id |
|-----------|--------|
| import_a  | box_2  |

But I don't like either ways because in approach 1 there are so many null columns and in approach 2 I need to create a new relationship table every time there is a new from entity. Is there a better way to model this in a relational database?

To sum up your added comment: Preferred RDBMS: Postgres 9.6, enforce referential integrity even under concurrent write load, at most 20 different sources, 500k rows, keep it simple.

Referential integrity is the hard part. The one rock solid way is with foreign key constraints. But you want to refer to one of multiple tables. From the same column?

How to have a foreign key pointing to two primary keys?

Short answer: not possible.

That leaves no other choice than either have one column for each source (your approach 1) or one table (your approach 2). Tables would make sense for a n:m relationship, not so much for 1:n. Your approach 2 is too complex, wastes too much storage, and it does not even match your data model to begin with.

Leaves us with approach 1. You worry:

  the problem is they are mutually exclusive. Which means there is only one column with actual value at any point in time and the rest of the columns are all null.

mutual exclusive - enforce that with a simple CHECK constraint. Standard SQL, a bit verbose but fast.
NULL storage - is extremely cheap in Postgres. Basically, 1 bit in the NULL bitmap. Details:

Do nullable columns occupy additional space in PostgreSQL?

So I would do this (Postgres implementation):

CREATE TABLE box (
   box_id      serial PRIMARY KEY
 , box_content text -- NOT NULL?
 , from_user   int  REFERENCES users(user_id)
 , from_office int  REFERENCES office(office_id)
 , from_import int  REFERENCES import(import_id)
  -- more?
 , CONSTRAINT exactly_one_from
   CHECK (CASE WHEN from_user   IS NULL THEN 0 ELSE 1 END
        + CASE WHEN from_office IS NULL THEN 0 ELSE 1 END
        + CASE WHEN from_import IS NULL THEN 0 ELSE 1 END = 1)
);

The CHECK constraints enforces exactly 1 reference, the FOREIGN KEY constraints enforce referential integrity under all circumstances.

If the many NULL columns are a burden to the human eye, consider a VIEW like:

CREATE VIEW box_view AS
SELECT box_id, box_content
     , COALESCE( from_user, from_office, from_import) AS from_id
     , CASE WHEN from_user   IS NOT NULL THEN 'user'
            WHEN from_office IS NOT NULL THEN 'office'
            WHEN from_import IS NOT NULL THEN 'import' END AS from_type
FROM   box;

Again, verbose but fast.

db<>fiddle here

There are many other possible approaches:

A document type like hstore or jsonb.
Multicolumn FK contraints like here:

Enforcing constraints "two tables away"

Enforce referential integrity with trigger instead of FK constraint.

But none of these guarantee referential integrity under concurrent write load like this approach does. Good performance and the smallest disk footprint are additional pros.
The downside: you have to alter the table definition (and view, queries, ...) for every additional "from" type.


mySQL subqueries calculating winning percentage I have a table where I store the data of a random dice game where you win if you get a sum of 7 between the two dices. Here's the table:

id
result
dice1
dice2
player_id

1
LOSE
4
4
9

2
LOSE
4
5
7

3
WIN
4
3
9

4
LOSE
2
3
9

5
WIN
6
1
7

6
WIN
1
6
7

7
LOSE
1
3
7

I need to display the percentage of win of each player and this is the query right now but it's not working, it gives me back the same percentage for every player:
SELECT DISTINCT player_id,
ROUND(((SELECT COUNT(id_game) FROM game WHERE result = 'WIN' ) /
(SELECT COUNT(id_game) FROM game )) * 100 ) AS percentage
FROM game
group by player_id

Do you think it's even possible to do that? I'm starting to doubt it

SELECT player_id,
       COUNT(result) total,
       ROUND(100 * SUM(result = 'WIN') / COUNT(result)) winning_percent,
       ROUND(100 * SUM(result = 'LOSE') / COUNT(result)) loosing_percent
FROM game
GROUP BY player_id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a04d576b918c392f90c1d835fd1a6f51


MySQL set one db in server to slave I have two MySQL servers (A and B) running on separate machines.  I want to replicate database X on A to B.  B is still master for other databases.  How do I set this up so that B is only slaved for X but master for the others?  I currently have A configured to only replicate-do-dbs=x.

To do this, you don't want to have any filtering on the the Master A server. Instead you will want to use replicate filters on the slave, Server B

For a full understanding of replication filters, have a read through this documentation and its sublinks. I will highlight two caveats (emphasis mine):

  On the master, you can control which databases to log changes for by using the --binlog-do-db and --binlog-ignore-db options to control binary logging.....You should not use these options to control which databases and tables are replicated. Instead, use filtering on the slave to control the events that are executed on the slave.

and

  To make it easier to determine what effect an option set will have, it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options.

With these two pieces of information, I'd recommend setting up server B with --replicate-wild-do-table=db_name.%. This is because:

  If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead.
  [src]


Add missing month in a table of values I have a table with the following entries:

customer_ID
lastDate
Sum

1
'2012-04-30'
5

1
'2012-06-30'
4

1
'2013-07-31'
25

2
'2012-04-30'
7

2
'2012-05-31'
4

2
'2012-06-30'
1

2
'2012-07-31'
6

I need to add missing month and the date which gets added should be the last date of that month with frequency value as 0.
The expected output is:

customer_ID
lastDate
Sum

1
2012-04-30
5

1
2012-05-31
0

1
2012-06-30
4

1
2012-07-31
0

1
2012-08-31
0

1
2012-09-30
0

1
2012-10-31
0

1
2012-11-30
0

1
2012-12-31
0

1
2013-01-31
0

1
2013-02-28
0

1
2013-03-31
0

1
2013-04-30
0

1
2013-05-31
0

1
2013-06-30
0

1
2013-07-31
25

2
2012-04-30
7

2
2012-05-31
4

2
2012-06-30
1

2
2012-07-31
6

I need to add missing month and the date which gets added should be the last date of that.
We can achieve this using CTE method. But can a similar result be obtained using joins?
Solution through CTE:
with cte as (
      select id, date, frequency,
             lead(date) over (partition by id order by date) as next_date
      from t
      union all
      select id, eomonth(date, 1), 0, next_date
      from cte
      where eomonth(date, 1) < dateadd(day, -1, next_date)
     )
select id, date, frequency
from cte
order by id, date;

I am trying to build a data pipeline and when I run this on that editor, it does not support recursive CTEs. I have no options.

If you have a table of numbers, or can access the built-in table in master:
SELECT
    T1.id,
    Expanded.[date],
    Expanded.frequency
FROM
(
    -- Add next date for the current id
    SELECT
        T.*,
        next_date =
            LEAD(T.[date], 1) OVER (
                PARTITION BY T.id
                ORDER BY T.[date])
    FROM dbo.t AS T
) AS T1
CROSS APPLY
(
    -- All month ends >= the current date and < next date
    SELECT
        [date] = EOMONTH(T1.[date], SV.number),
        frequency = IIF(SV.number = 0, T1.frequency, 0),
        SV.number
    FROM master.dbo.spt_values AS SV
    WHERE
        SV.[type] = N'P'
        AND SV.number < ISNULL(DATEDIFF(MONTH, T1.[date], T1.next_date), 1)
) AS Expanded
ORDER BY
    T1.id,
    T1.[date],
    Expanded.number;

The output matches that of the recursive CTE you provided (see demo link below):

id
date
frequency

1
2012-04-30
5

1
2012-05-31
0

1
2012-06-30
4

1
2012-07-31
0

1
2012-08-31
0

1
2012-09-30
0

1
2012-10-31
0

1
2012-11-30
0

1
2012-12-31
0

1
2013-01-31
0

1
2013-02-28
0

1
2013-03-31
0

1
2013-04-30
0

1
2013-05-31
0

1
2013-06-30
0

1
2013-07-31
25

2
2012-04-30
7

2
2012-05-31
4

2
2012-06-30
1

2
2012-07-31
6

Explanation
Logically, each row from T1 is expanded by the APPLY into the set of month-ends that ought to be present. Let's look at one particular row in the source table:

id
date
frequency

1
2012-04-30
5

Adding the LEAD in the table expression aliased to T1 gives:

id
date
frequency
next_date

1
2012-04-30
5
2012-06-30

This row is passed to the APPLY:
SELECT
    [date] = EOMONTH(T1.[date], SV.number),
    frequency = IIF(SV.number = 0, T1.frequency, 0),
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number < ISNULL(DATEDIFF(MONTH, T1.[date], T1.next_date), 1)

The references to columns in T1 are outer references. Substituting the values from the current T1 row for [date], next_date, and frequency gives:
SELECT
    [date] = EOMONTH('2012-04-30', SV.number),
    frequency = IIF(SV.number = 0, 5, 0),
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number < ISNULL(DATEDIFF(MONTH, '2012-04-30', '2012-06-30'), 1)

The ISNULL expression determines how many rows are returned from our table of numbers. In the current iteration the ISNULL expression evaluates to 2, so the rows returned from the numbers table have values 0 and 1.
The APPLY table expression as a whole returns:

date
frequency
number

2012-04-30
5
0

2012-05-31
0
1

Notice:

When number = 0, we are dealing with a row that exists in T1, so we just use the frequency given.
When number > 0, the row does not exist in T1 so we need to return a zero for frequency.

That gives us the results needed for the single row we chose from T1. We can do the same for the next row from T1 (substituting values as before), and so on until the result is complete. For more on how APPLY works logically, see my article Understanding and Using APPLY.
The only special handling is for the last row per id, where the LEAD returns NULL. In that case, we only need one row from the numbers table (value zero) to process the current row from T1.

The built-in table is sufficient for a range of dates from '2000-01-01' to '2170-08-01'.
Try the db<>fiddle demo


Optimize MySQL Configuration - MySQL crash and after restart works My WAMP MySQL has crashed 2 times the last week and I really wonder why! Once I restart the server everything works fine! Below I attach the error log of MySQL and the my.ini file! Can anyone suggest anything??

MySQL Error LOG:

2018-02-23 21:45:52 2000 [Note] Plugin 'FEDERATED' is disabled.
2018-02-23 21:45:52 2000 [Note] InnoDB: The InnoDB memory heap is disabled
2018-02-23 21:45:52 2000 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2018-02-23 21:45:52 2000 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-02-23 21:45:52 2000 [Note] InnoDB: Not using CPU crc32 instructions
2018-02-23 21:45:53 2000 [Note] InnoDB: Initializing buffer pool, size = 67.0G
2018-02-23 21:45:55 2000 [Note] InnoDB: Completed initialization of buffer pool
2018-02-23 21:45:55 2000 [Note] InnoDB: Highest supported file format is Barracuda.
2018-02-23 21:47:37 2000 [Note] InnoDB: 128 rollback segment(s) are active.
2018-02-23 21:47:37 2000 [Note] InnoDB: Waiting for purge to start
2018-02-23 21:47:38 2000 [Note] InnoDB: 5.6.12 started; log sequence number 321531472808
2018-02-23 21:48:16 2000 [Note] Server hostname (bind-address): '*'; port: 3306
2018-02-23 21:48:16 2000 [Note] IPv6 is available.
2018-02-23 21:48:16 2000 [Note]   - '::' resolves to '::';
2018-02-23 21:48:16 2000 [Note] Server socket created on IP: '::'.
2018-02-23 21:48:18 2000 [Note] Event Scheduler: Loaded 0 events
2018-02-23 21:48:18 2000 [Note] wampmysqld: ready for connections.
Version: '5.6.12-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

My.ini Configuration File:

[wampmysqld]
port        = 3306
socket      = /tmp/mysql.sock
key_buffer = 64M
max_allowed_packet = 1000M

sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.6.12
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.6.12/data
#innodb_thread_concurrency = 8
innodb_read_io_threads = 12
innodb_buffer_pool_size = 67G
max_connections = 4000
max_user_connections = 0

 # Disable Federated by default
skip-federated

[mysqldump]
 quick
max_allowed_packet = 1000M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 4M
write_buffer = 4M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout

 [mysqld]
 port=3306
 table_definition_cache = 800

My Server Details:

  RAM: 128GB RAM CPU: Intel XEON 3,5GHZ HD: 2TB HDD

Any recommendation??

Suggestions for my.cnf/ini [wampmysqld] section

key_buffer=64M       SHOULD BE
key_buffer_size=64M  # from key_buffer  to be recognized
expire_logs_days=10  # from default 0, to provide limited historical logs
innodb_buffer_pool_instances=8  # to minimize mutex contention
innodb_buffer_pool_dump_at_shutdown=ON  # prepare for WARM on restart
innodb_buffer_pool_load_at_startup=ON  # pool will be warmed up
innodb_buffer_pool_dump_pct=75  # from 25 you will likely need the data
innodb_page_cleaners=64  # 4 to auto-limit = innodb_buffer_pool_instances
innodb_print_all_deadlocks=ON  # chk error log-be proactive to prevent
innodb_purge_threads=4  # to keep up with activity
log_warnings=2  # to have record of attempted connections in error log
#max_connections=4000  # lead with # to have default of 151 until stable
#sort_buffer_size=512K  # lead with # for default of 2M
#max_allowed_packet=1000M  # lead with # for default of 1M

if you need MAP greater than 1M, it is likely for data loading infile
in your SESSION,
SET @max_allowed_packet=nnnnnnnnn  bytes max in input file  1G is THE LIMIT

this technique will HELP avoid OOM due to a new connection.


Storing objects in key/value stores - serialized or separate values? I'm playing around with Go and BadgerDB, which is an embedded disk based (not in-memory like Redis) key/value store. There's just so little information about how to use key/values stores in general.

My main question basically is: Does it make more sense to store one entity/object/item per key? Like this:

user:0 = {"email":"johndoe@example.com","password":"some-hash"}

Or to store every field on its own? Like this:

user:0:email = "johndoe@example.com"
user:0:password = "some-hash"

Does anyone have some experience with this? Does one technique have some performance advantages over the other? Or does one have management issues I don't think about yet? Or is there something else to consider?

It really depends. You've identified one of the primary flaws with a key->value design (structure).

Your former example is more like a document-oriented database, which is kind of an extra implementation on top of a key->value store. Using something like JSON for your document is one approach. Some document-oriented DB's will optimize/index that for you.

Your latter example is more of a traditional key->value approach.

To answer your question more specifically, one problem with storing a JSON string/object as your value would be that every time you want to access part of that value, you generally have to parse (and possibly decompress) the whole thing. The problem with the key->value design is a lack of structure, everything is almost flat. How are you going to represent a class/object this way, and what if you have a web of complicated classes/objects, etc.?

https://en.wikipedia.org/wiki/NoSQL#Types_and_examples


What is the formal definition of a Primary Index and Primary Key? The below is from Fundamentals of Database Systems (7th Edition)

17.1 Types of Single-Level Ordered Indexes
There are several types of ordered indexes. A primary index is specified on the ordering key field of an ordered file of records. Recall from Section 16.7 that an ordering key field is used to physically order the file records on disk, and every record has a unique value for that field. If the ordering field is not a key field—that is, if numerous records in the file can have the same value for the ordering field—another type of index, called a clustering index, can be used. The data file is called a clustered file in this latter case. Notice that a file can have at most one physical ordering field, so it can have at most one primary index or one clustering index, but not both. A third type of index, called a secondary index, can be specified on any nonordering field of a file. A data file can have several secondary indexes in addition to its primary access method. We discuss these types of single-level indexes in the next three subsections.
17.1.1 Primary Indexes
A primary index is an ordered file whose records are of fixed length with two fields, and it acts like an access structure to efficiently search for and access the data records in a data file. The first field is of the same data type as the ordering key field—called the primary key—of the data file, and the second field is a pointer to a disk block (a block address). There is one index entry (or index record) in the index file for each block in the data file. Each index entry has the value of the primary key field for the first record in a block and a pointer to that block as its two field values.

Specifically my question is about a "primary index" as it relates to a more formal academic parlance -- I never went to a university so I have no idea how they're using this term. Is the  "primary index is specified on the ordering key field of an ordered file of records" correct?  Was that the original distinction between a PRIMARY key (for a primary index), and a regular key into a non-primary non-clustered index?
I'm thinking that this book didn't go through 7 editions at $151.00 a pop with the false idea that a primary key necessitate "ordered file of records."

A "key" is a logical construct used to identify a specific record. Given their purpose, keys require uniqueness. Primary Key fields are also required to be declared as NOT NULL. There are also "alternate" keys which allow key fields to be declared as NULL. Both Primary and Alternate keys have an optional, secondary purpose of establishing relationships via Foreign Keys (declarative referential integrity).

An "index" is a physical construct used to access one-or-more records. Uniqueness is not required for this purpose. When an index is declared as unique, it is not required that the indexed column(s) be declared as NOT NULL; NULL is allowed so long as it is unique, or a unique combination when working with multiple index columns.

Given the two quoted paragraphs, here is a terminology translation table:

BOOK → SQL Server
"file" →  Table or Index
"primary index" →  UNIQUE CLUSTERED Index
"clustering index" → non-unique CLUSTERED Index (due to the cluster key column(s) being the row identifier, there is a need for true uniqueness, and so SQL Server will guarantee that uniqueness by adding a hidden, auto-incrementing INT value called the uniqueifier, but only for actual non-unique key values: please see Clustered Index Uniquifier Existence and Size for details)
"secondary index" → NONCLUSTERED Index
"primary key" → PRIMARY KEY defined on a UNIQUE CLUSTERED Index

In SQL Server, a Primary Key does not need to be defined on a UNIQUE CLUSTERED Index, it just requires a Unique Index (though the default behavior when creating a PK when no clustered Index exists is to make the PK clustered).

Also, a Foreign Key can reference either a Primary Key or an "Alternate" Key (i.e. Unique Constraint or Unique Index)

The following example shows both a PK on a NONCLUSTERED Index, and an FK referencing an Alternate Key defined on a column allowing NULL values:

USE [tempdb];

-- DROP TABLE dbo.Parent
CREATE TABLE dbo.Parent
(
  [ParentID] INT NOT NULL
             CONSTRAINT [PK_Parent] PRIMARY KEY
             NONCLUSTERED,
  [AltKey]   INT NULL
             CONSTRAINT [UQ_Parent_AltKey] UNIQUE NONCLUSTERED
);

-- DROP TABLE dbo.ChildID
CREATE TABLE dbo.ChildID
(
  [ChildID]  INT NOT NULL
             CONSTRAINT [PK_Child] PRIMARY KEY
             NONCLUSTERED,
  [AltKey]   INT NULL
             CONSTRAINT [FK_Child_Parent_AltKey] FOREIGN KEY
             REFERENCES dbo.Parent ([AltKey])
);


You should group by the result of division by 60 starting by the minimal date per Id:

declare @UserAction table (Id int, Date datetime, Action nvarchar(200))

INSERT INTO @UserAction VALUES
    (111, '2000-01-01 15:30:10', 'Action Made'),
    (555, '2000-01-01 15:30:10', 'Action Made'),
    (111, '2000-01-01 15:30:20', 'Action Made'),
    (111, '2000-01-01 15:31:05', 'Action Made'),
    (111, '2000-01-01 15:31:10', 'Action Made'),
    (555, '2000-01-01 15:31:10', 'Action Made'),
    (111, '2000-01-01 15:32:05', 'Action Made'),
    (555, '2000-01-01 15:32:05', 'Action Made'),
    (555, '2000-01-03 15:35:10', 'Action Made'),
    (555, '2000-01-03 15:35:20', 'Action Made'),
    (111, '2000-01-03 15:36:05', 'Action Made'),
    (555, '2000-01-03 15:36:05', 'Action Made');

with cte AS
(
select *,
       min(Date) over(partition by id) as min_dt
from @UserAction
)

,cte_gr AS
(
select *,
       datediff(ss, min_dt, Date) / 60 as gr
from cte
)

select id,
       min(date) as [From],
       max(date) as [To],
       count(*) as cnt
from cte_gr
group by id, gr;


Disable redo logs on MariaDB The title is pretty explicit, I just want to disable completely redo logs on  MariaDB 5.5. This is more of a testing database that might eventually be used for a small web forum, but I don't care about being able to recover from crashes, all I care about is saving disk space.

Is there a way to completely disable redo logs? What if I set the max size to 0?

The redo logs are required by the InnoDB storage engine. The redo logs provide the 'D' in ACID - durability. You can't set the innodb_log_file_size to 0 as the smallest allowed value is 1MB. Also, the smallest value for innodb_log_files_in_group is 1, so you can't use that either.

You can shut down the MariaDB server and disable InnoDB - use these settings in your .cnf file:

[mysqld]
skip_innodb
default_storage_engine=Aria

Then delete the InnoDB redo log files (by default: ib_logfile0 and ib_logfile1 in your datadir - by default /var/lib/mysql/) and start the MariaDB server again.


Quick look at how much RAM is allocated to SQL Server? With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server.

With SQL Server 2008, the Working Set or Commit Size never really goes above 500 MB, even though the SQLServer:Memory Manager/Total Server Memory (KB) perf counter states 16,732,760.

Is there a setting where it will actually show the server memory in the Task Manager?  Or is it a result of them changing how memory is used in SQL Server

You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using (maybe you are remembering a 32-bit system with a very small amount of memory). Stop using Task Manager for this, period. Use the performance counter - you can also query the performance counter using DMVs:

SELECT object_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Total Server Memory (KB)';

You could save that as a query shortcut in Tools > Options > Environment > Keyboard > Query Shortcuts, and get accurate results in a query window much faster than getting inaccurate results from Task Manager.

You can also check for memory pressure (and whether you can do anything about it) using these queries:

SELECT object_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

-- SQL Server 2012:
SELECT physical_memory_kb FROM sys.dm_os_sys_info;

-- Prior versions:
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info;

EXEC sp_configure 'max server memory';


Doing that is tricky, because you need to check the differences between the data in both orders, ascending to get the first 'IN', descending to get the last 'OUT'.

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID_Up = Row_Number() OVER (PARTITION BY EMPLID
                                    ORDER BY EMPLID, PUNCHDATE, PUNCHTIME)
       , ID_Down = Row_Number() OVER (PARTITION BY EMPLID
                                      ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
), S AS (
  SELECT a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
       , a.ID_Up aUID, b.ID_Up bUID, a.ID_Down aDID, c.ID_Down cDID
       , a.DeviceType aDeviceType, b.DeviceType bDeviceType, c.DeviceType cDeviceType
       , OKValues
       = CASE WHEN a.ID_Up = 1 THEN 1
              WHEN a.DeviceType = 'IN' AND b.DeviceType = 'OUT' THEN 1
              WHEN a.ID_Down = 1 THEN 1
              WHEN a.DeviceType = 'OUT' AND c.DeviceType = 'IN' THEN 1
              ELSE 0
         END
  FROM   I a
         LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID_Up = a.ID_Up - 1
         LEFT  JOIN I c ON a.EmplID = c.EmplID And c.ID_Down = a.ID_Down - 1
)
SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
FROM   S
WHERE  OKValues = 1
ORDER BY EMPLID, PUNCHDATE, PUNCHTIME

SQLFiddle

the S CTE is to remove the CASE from the WHERE condition, in the CTE I left a lot of noise data, that can be useful to understand the CASE conditions


Is it possible to scale-out with Microsoft SQL Server? Is it possible to scale-out a SQL Server database?

There are some solutions for fail-over clustering and always on, but is there a solution for load balancing and horizontal partitioning on SQL Server?

There are some in Azure, but not in a standalone SQL Server instance...

You've kinda got a bunch of questions in here:

It is possible to scale out READS in Azure SQL DB? Yes.

Depending on the service you use, like Azure SQL DB's Active Secondary Replicas, your read workload can automatically be scaled out across multiple servers without big changes to your application code. However, you do still need to add a separate connection string in your application specifying ApplicationIntent=ReadOnly so that they know it's safe to move your read query over to a readable replica.

Is it possible to scale out WRITES in Azure SQL DB? Yes, if you change code.

The document you linked to, Data Partitioning, is a set of design guidelines that you can use when you code your application. However, those are indeed code changes, not a feature of Azure SQL DB that you just flip on the same way you flip on secondary replicas.

It's up to you to design a layer of code so that when your app needs to run a query, it connects to the appropriate location.

Think of it like the World Wide Web: at Stack Overflow, write workloads are separated out across StackOverflow.com, DBA.StackExchange.com, ServerFault.com, and other sites. However you, the user, have to know which site has the data you want to write to - you can't post an answer to this question over at ServerFault.com. That logic is built into your brain - and the same logic needs to be built into your app if you want to scale out writes across multiple servers.

There was a brief moment in time where Microsoft thought they'd do this work for you with Azure SQL DB Federations, but that was quickly deprecated.

Are the same things possible with SQL Server? Yes.

Scaling out reads is as easy as:

Buying more SQL Servers and building them into an Availability Group
Adding another connection string in your app specifying ApplicationIntent=ReadOnly
Profit!

Scaling out writes isn't that easy, and requires code changes just like it does in Azure SQL DB.

Does either of those do true load balancing? No.

Neither Azure SQL DB nor SQL Server Always On Availability Groups truly balance load across multiple secondaries. You can get into a situation where one replica is running a handful of awful queries, and the rest are sitting around idle. And in that situation, you can still end up getting new queries assigned equally to both the underworked and overworked servers.

Doing true load balancing - keeping the work requirements similar across multiple replicas - is left as an exercise for the reader.


Freeing unallocated space I have a server that the database drive is running out of space. i have deleted some of the old log data and historical data to try and free up the space.what is the best way to free allocated space in SQL server. the only way i could find online is to shrink the database but then i read shrinking the database can cause problems
I have tried using shrink database and it works but i'm just concerned if i'm using the correct method

The problems with shrinking database files are primarily:

The shrinking process can cause fragmentation in tables and indexes.
Barring unusual circumstances, the files will need to be grown again in the immediate future.

Both of these can cause performance issues. Fragmentation of your tables and indexes can make queries take longer, and growing your log file (or your data file, if you don't have instant file instantiation turned on) takes a certain amount of time, where users may be waiting for it to complete.

"Unusual circumstances" would mean some change was made to the database such that space in the data file is not longer needed (for example, the permanent removal of a large table, or the deletion of millions of rows that won't be replaced shortly), or an abnormally large amount of space was required for the log file (for instance, a once-a-year data load cause your log file to grow from its norm 1 GB size to 35 GB; or, your database was using the FULL recovery model but you weren't taking transaction log backups and you've now resolved this (either by starting the backups, or by switching to the SIMPLE recovery model)).

In those unusual circumstances, shrinking your data or log files to a more reasonable size makes sense.

Note: Having a nightly job that shrinks these files doesn't make sense. If you're shrinking either file that often, then it must be growing larger than your job's target daily. If that's happening, you need to rethink that target, and think about adding space to your drives.

A further note: If you have heap tables (tables with no clustered index), SQL Server isn't good about releasing space once columns in those tables are no longer in use. Solution to this include copying the data to a new table, or adding a clustered index.

You need to asses your own system, and determine why you're running low on space. If it's due to natural growth of your data, then additional disk space will be needed at some point. If you got a lot of data that's no longer needed, devising some sort of archival process and then removing the data from production may be your solution. Or, if you've had one of those "unusual circumstances", shrinking files may be all you need to do.


Can't solve MongoParseError: Invalid connection string I'm not able to solve MongoParseError.

First my I tried this

mongoose.connect(`mongodb://${process.env.DB_USER}:${process.env.DB_PASSWORD}@db:27017/${process.env.DB_NAME}`, {useNewUrlParser: true});

resulting in this error

MongoParseError: Unescaped slash in userinfo section

Then I escaped all the slashes

mongoose.connect(`mongodb:\\/\\/${process.env.DB_USER}:${process.env.DB_PASSWORD}@db:27017\\/${process.env.DB_NAME}`, {useNewUrlParser: true});

and I ended up in this error which I'm not able to solve

MongoParseError: Invalid connection string

This is my index.js

'use strict';

const mongoose = require('mongoose');
mongoose.connect(`mongodb:\\/\\/${process.env.DB_USER}:${process.env.DB_PASSWORD}@db:27017\\/${process.env.DB_NAME}`, {useNewUrlParser: true});

var db = mongoose.connection;
db.on('error', console.error.bind(console, 'connection error:'));
db.once('open', function() {
  console.log('ok');
});

const express = require('express');

// Constants
const PORT = 8080;
const HOST = 'localhost';

var kittySchema = new mongoose.Schema({
  name: String
});

var Kitten = mongoose.model('Kitten', kittySchema);

var silence = new Kitten({ name: 'Test1234' });

// App
const app = express();
app.get('/', (req, res) => {
  res.send(silence.name);
});

app.listen(PORT);
console.log(`Running on http://${HOST}:${PORT}`);

And here's the docker-compose.yml

version: '3.7'
services:
  web:
    build: .
    command: npm start
    volumes:
      - ./src:/usr/app/
      - ./src/node_modules:/usr/app/node_modules
    ports:
      - 80:8080
    environment:
      DB_NAME: websitedb
      DB_USER: /run/secrets/db_user
      DB_PASSWORD: /run/secrets/db_password
    secrets:
      - db_user
      - db_password
  db:
    image: mongo:latest
    volumes:
      - ./mongo-init.js:/docker-entrypoint-initdb.d/mongo-init.js:ro
    ports:
      - "27017:27017"
    environment:
      MONGO_INITDB_DATABASE: websitedb
      MONGO_INITDB_ROOT_USERNAME_FILE: /run/secrets/db_user
      MONGO_INITDB_ROOT_PASSWORD_FILE: /run/secrets/db_password
    secrets:
      - db_user
      - db_password
volumes:
  mongo:
secrets:
  db_user:
    file: ./db_user.txt
  db_password:
    file: ./db_password.txt

Can anyone tell me what I'm doing wrong?

Try printing the result of mongodb:\\/\\/${process.env.DB_USER}:${process.env.DB_PASSWORD}@db:27017\\/${process.env.DB_NAME} in console and use the result to connect to mongodb through mongo shell.

Also, post the final result with masked details for review.


Can a Selective XML index help when using query()? Say I have the following SQL query to work with

Select
myxml.query(
'
<go>
{for $each in /This/Company/Department[1]/Role/Person
return if ($each/Shoes/F[@C="9"]="657493742")
then (<ID>{$each/Shoes/F[@C="9916"]/V}</ID>)
else ()}
</go>')
FROM mytable

Is there a way to craft a Selective XML index that would help in this situation? According to the documentation it sounds like it could help with filtering the nodes, but I haven't come across any examples of dealing with a query like this.

From Selective XML Indexes (SXI), Supported Features, Prerequisites, and Limitations

  Selective XML indexes support the XQuery supported by SQL Server
  inside the exist(), value() and nodes() methods.

query() is not supported so it will not work for you.

On the other hand it says that

  For the query() and modify() methods, selective XML indexes may be
  used for node filtering only.

I have not been able to produce a query that use XML indexes using the query() method so I can't really tell you in what cases it may work.

If you want to tackle a performance issue with your query I would recommend you to shred the XML using nodes() and value() and then reconstructing the XML using for xml path. It is usually faster than building the XML with query() and you could also probably make use of selective XML indexes if you need it.


Assignment of a column with dynamic column name I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following:

CREATE OR REPLACE FUNCTION prevent_column_update() RETURNS TRIGGER AS $$
  DECLARE
    col TEXT := TG_ARGV[0];
  BEGIN
    EXECUTE format('SELECT ($1).%I INTO ($2).%I', col, col) USING OLD, NEW;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

And to be used like:

CREATE TRIGGER request_protect_date_price_value
  BEFORE UPDATE OF date_price ON requests
  FOR EACH ROW EXECUTE PROCEDURE prevent_column_update('date_price');

But when updating it fails with:

ERROR:  syntax error at or near "("
LINE 1: SELECT ($1).date_price INTO ($2).date_price
                                    ^
QUERY:  SELECT ($1).date_price INTO ($2).date_price

The error is that the INTO clause is not part of the SQL command. It's part of the plpgsql command EXECUTE.
And dynamic field names are currently not possible, neither in SQL nor PL/pgSQL. But there are ways around this limitation:
Proof of concept

Assign to NEW by key in a Postgres trigger
How to set value of composite variable field using dynamic SQL

You can (ab)use the built-in JSON function json_populate_record() for a similar trick, but that's currently not documented and may be removed in future versions of Postgres.
The sure way is to use the documented #= operator of the additional hstore module. Install the module once per database with
CREATE EXTENSION IF NOT EXISTS hstore;

Then:
CREATE OR REPLACE FUNCTION prevent_column_update()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col text := quote_ident(TG_ARGV[0]);
   _old_val text;
   _new_val text;
BEGIN
   EXECUTE format('SELECT $1.%1$I, $2.%1$I', _col)
   INTO    _old_val, _new_val  -- part of plpgsql command
   USING   OLD, NEW;

   IF _old_val IS DISTINCT FROM _new_val THEN  -- only if it actually changed
      NEW := NEW #= hstore(_col, _old_val);    -- hstore operator #=
   END IF;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Note that hstore operates with text strings. Values for other data types are cast to text and back, which works for any data type I can think of. But it might cause problems for some types (like rounding errors for floating point numbers).
And this trigger definition to make the case complete:
CREATE TRIGGER tbl_upbef_nope
BEFORE UPDATE ON tbl  -- your table here
FOR EACH ROW
EXECUTE PROCEDURE prevent_column_update('date_price');

The column name is case sensitive in this example, since it is passed as string and not as identifier.
What I would do
Just write a new plain trigger function without dynamic SQL for each table. Less hassle, better performance. Byte the bullet on code duplication:
CREATE OR REPLACE FUNCTION prevent_column_update()
  RETURNS TRIGGER AS
$func$
BEGIN
   NEW.date_price:= OLD.date_price;  -- unconditionally
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Trigger:
CREATE TRIGGER tbl_upbef_nope
BEFORE UPDATE OF date_price ON tbl  -- your column and table here
FOR EACH ROW EXECUTE PROCEDURE prevent_column_update();  -- no param

I moved the check to the trigger itself, so the function is not even executed unless the column is updated. Note that this can be circumvented by additional triggers on the same table because (quoting the manual):

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

So if you cannot rule out such additional triggers, fire the trigger on UPDATE unconditionally and check for changes in the trigger function instead.


Mongo Scaling - Too Many Connections - Linear Horizontal Scaling with 100s of App Servers I have a Mongo Cluster. It's sharded on 6 servers, with 6 replica sets, each replica set having one Primary, and two Secondaries.  Additionally, there are 3 config servers. Though, I think the Mongo Cluster configuration is irrelevant to this question.

I have many application servers (currently 100), with the intention of having many more as my volume grows.  On each application server, I have a mongos process, 8 http servers (node.js) each bound to a port (using HAProxy as a reverse proxy to route traffic from port 80).  Each http server connects to the local mongos process using connection pooling with a poolSize of 3.  Pretty simple so far.

The issue is that the number of connections seems to quite simply be:

(number of servers) * (number of http processes) * (poolSize)

In my current case that would be:

100 * 8 * 3 = 2400 total connections

So it would seem that the number of connections scales linearly with the number of servers I'm running.  If I scale up 5x, then the number of connections goes to 9,600 which is dangerously close to the 10,000 limit (as well as seeming unnecessarily wasteful).  What about a 10x or 100x scale up?

It is stated that "any connection count greater than 1000 – 1500 connections should raise an eyebrow".  How can I possibly maintain that number of connections with 100s of application servers? Is a mongos process per application server at this size still appropriate?

The hard connection limit was actually 20,000 but it was removed in version 2.6 and you are now only limited by memory or open file descriptors (whichever you run out of first).  That being said, with connections running into the thousands, even if you can manage it you will be using a lot of memory (1MB per connection for the stack).

At that point you start to evaluate other options for mongos deployment.  You can have a dedicated set of mongos hosts and take advantage of some of the nice HA options built into the drivers and pass in an list of possible mongos processes to use.  If you are concerned about latency, you could potentially collocate (some of) the mongos processes on your shard hosts.

Basically you've started to hit the number of nodes where you are into more advanced design decisions and it really depends on several factors as to what is right for you now, and what is right for you in the future.  If you have some money to throw at it, this is perfect fodder for the type of consulting MongoDB offer (disclosure: I was once one of the people delivering such consulting).  If not, you are asking the right questions, so keep an eye on resource utilisation and see what makes sense for your deployment as you grow.


Oracle dbms_scheduler: Can I create a job that will not overlap with itself? How can I ensure that a dbms_scheduler job will never have more than one instance running?

For example, if I have a job that is scheduled to run once a minute, but it actually runs for three minutes, I don't want a second and third instance of the job to start and run concurrently.

(note) I previously asked this question specifying that either dbms_scheduler or dbms_jobs solutions were acceptable.  I have since learned that dbms_jobs does not work well on RAC, hence this dbms_scheduler-specific question. (end note)

yes, you can, this in fact is default behaviour. See Database Administrator’s Guide chapter 29 Scheduling Jobs with Oracle Scheduler

  Immediately after a job starts, the repeat_interval is evaluated to
  determine the next scheduled execution time of the job. While this
  might arrive while the job is still running, a new instance of the job
  does not start until the current one completes.


Using multiple server databases I was just seeking some higher advice on whether or not having your main data on one database, then having separate server database to use for frequent updating and reading will prove to be an optimization or a failure. My logic was that sharing the load like this across 2 separate server databases will be quicker than packing it all in on one shared hosting Thanks for your time.

Yes, you can and should use multiple servers if you have a high load in your mysql.

you need to see what solution works to you:

Replication

Master -> Slave:
In this one, you will write(and also will be able to read) in the master server, and read from slave,
Master -> Master: In this one, you will be able to write and read in both servers (they will be action as master and slave), you need to be careful with AUTO_INCREMENTS

Cluster: You can have a look on mysql cluster, I'll recommend this option if you have more than 4 server (masters or slaves in total).


How can I join this two queries,in one? I have this 2 queries, and I would like to join them

First query

SELECT proveedors.nombre as proveedor, COUNT(productos.id) as distintos
FROM proveedors
INNER JOIN productos ON proveedors.id = productos.proveedor_id
GROUP BY proveedors.id;

Second query

SELECT proveedors.nombre as proveedor, COUNT(productos.id) as instock
FROM proveedors
INNER JOIN productos ON proveedors.id = productos.proveedor_id
WHERE productos.stock > '0'
GROUP BY proveedors.id;

Because they have the same GROUP BY you can just add the other aggregate function to the select list, like this..

SELECT
  proveedors.nombre as proveedor,
  COUNT(productos.id) as distintos,
  COUNT(CASE WHEN productos.stock > '0' THEN productos.id END) as instock
FROM proveedors
INNER JOIN productos ON proveedors.id = productos.proveedor_id
GROUP BY proveedors.id;

Or you can use the new FILTER clause if your rdbms supports it. PostgreSQL does.

SELECT
  proveedors.nombre as proveedor,
  COUNT(productos.id) as distintos,
  COUNT(productos.id) FILTER (WHERE productos.stock > '0') as instock
FROM proveedors
INNER JOIN productos ON proveedors.id = productos.proveedor_id
GROUP BY proveedors.id;

You should probably not be single-quoting the 0, but I have no idea what rdbms you're using and what's going on there.


Why a copy of my.ini of other MySQL installation not working on my PC I have two MySQL installations on two different PCs not connected to each other. I made some changes in the my.ini file of PC 1 and tried using this file on PC 2, but MySQL service fails to start. Why the copied My.INI not working on my PC when both installations were configured same and the instance names also similar?

First, let's make sure MySQL evens runs properly apart from the service.

Open a DOS Window Session and run these commands

C:\> cd "C:\Program Files\MySQL\MySQL 5.5\bin"
C:\Program Files\MySQL\MySQL 5.5\bin> start mysqld

When you launch mysqld like this, mysqld expects my.ini to be in the parent directory, which is C:\Program Files\MySQL\MySQL 5.5. Using start in DOS will open another DOS Window as a background task. If any error messages appear in that window and that forked DOS Session quickly disappears from the screen, check the Task Manager to see if mysqld is running. If it is, you should be able to log into mysql. If mysqld is not in the Task Manager, launch it as a foreground task like this:

C:\Program Files\MySQL\MySQL 5.5\bin> mysqld

If the DOS prompt appears after some error messages, then mysqld failed. If error messages come up and the DOS prompt does not appear, mysqld is up.

If the settings you configured in my.ini on PC 2 is not in SHOW VARIABLES; then mysqld cannot see the my.ini. To reiterate, mysqld expects my.ini to be in the parent directory.


Can I disable binlogs to save space temporarily I am using percona mysql 8.
I do not use any type of replication, but I read that binlogs are useful for data recovery. I would like to turn of binlogging and flush them while I run pt-online-schema-change to do non-impacting OPTIMIZE table.
After it is done, I want to turn binlogging back on (and then make efforts to move to a server with more space).
Is this safe and recommended? I need to optimize a table and can't go offline and making a copy of the table will make me run out of space unless I remove the 50GB of binlogs

What you need to do is set sql_log_bin to 0 as you start pt-online-schema-change using set-vars
pt-online-schema-change --set-vars sql_log_bin=0

Your binary logs will never know the difference.
Please make sure you have enough space for the temp table (_tablename_new).
UPDATE 2020-12-10 14:45 EST
If you are not doing backups on this server, then you could run
PURGE BINARY LOGS BEFORE CURDATE() - INTERVAL 5 DAY;

to keep the last 5 days worth of binary logs or if you want all the binary logs gone, then run
RESET MASTER;

WARNING !!!
Before doing that, find out if the server is running replication as a Master.
UPDATE 2020-12-10 16:05 EST
Since you are running pt-online-schema-change, you should run it like this
pt-online-schema-change --set-vars sql_log_bin=0 --alter "ENGINE=InnoDB"

This will just create a temp table defragmented. It just so happens that --analyze-before-swap is the default value. That means the temp table has ANALYZE TABLE executed against it before swapping old and new tables. This has the exact same effect as OPTIMIZE TABLE on an InnoDB table.
As mentioned in nbk's comment, you should make sure you have backups. You would also do yourself a world of good by setting up replication and doing the backups from the slave.


ERROR 1031 (HY000) at line X: Table storage engine for 'TBL' doesn't have this option I am trying to import mysqldump into following system:

# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
# rpm -qa | grep ^mysql
mysql-community-libs-compat-5.7.11-1.el7.x86_64
mysql-community-libs-5.7.11-1.el7.x86_64
mysql57-community-release-el7-7.noarch
mysql-community-client-5.7.11-1.el7.x86_64
mysqltuner-1.6.0-1.el7.noarch
mysql-community-server-5.7.11-1.el7.x86_64
mysql-community-common-5.7.11-1.el7.x86_64
#

yet, I getting following error:

# mysql X < ~/X.mysqldump
ERROR 1031 (HY000) at line 13639: Table storage engine for 'Y' doesn't have this option
#

the Y table from mysqldump: (I replaced sensitive information with Zs):

DROP TABLE IF EXISTS `Y`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Y` (
  `Z` int(10) NOT NULL AUTO_INCREMENT,
  `Z` int(10) NOT NULL DEFAULT '0',
  `Z` int(10) NOT NULL DEFAULT '0',
  `Z` tinyint(1) NOT NULL DEFAULT '0',
  `Z` tinyint(1) NOT NULL DEFAULT '1',
  `Z` int(10) NOT NULL DEFAULT '0',
  `Z` datetime DEFAULT NULL,
  `Z` datetime DEFAULT NULL,
  PRIMARY KEY (`Z`),
  KEY `Z` (`Z`),
  KEY `Z` (`Z`),
  KEY `Z` (`Z`),
  KEY `Z` (`Z`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED COMMENT='InnoDB free: 8192 kB; (`Z`) REFER `Z/';
/*!40101 SET character_set_client = @saved_cs_client */;

/etc/my.cnf is all defaults, I did however added following line(s):

# tail -1 /etc/my.cnf
innodb_buffer_pool_size = 64G
#

Also, here is full mysql> SHOW VARIABLES LIKE 'innodb%'; stack.

I'm going over MySQL :: MySQL 5.7 Reference Manual :: 14.4 InnoDB Configuration, but obviously I'm missing something...

Please advise.

Thanks!

Under MySQL 5.7, ROW_FORMAT=FIXED is no longer an option for the InnoDB Storage Engine.

According to the MySQL 5.7 Documentation for innodb_default_row_format, these are values you can use for the table's row format:

DYNAMIC, COMPACT, and REDUNDANT are allowed as default values
COMPRESSED is not allowed to a default value, but you can explicitly use ROW_FORMAT=COMPRESSED

There is no mention of ROW_FORMAT=FIXED for InnoDB in the MySQL 5.7 Docs.

I wrote an answer to a post about 8 months entitled Does an empty column value occupy same storage space as a filled column value? which explains the difference between COMPACT and REDUNDANT. You should find changing to eitherROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT in the dump definition. If you want your VARCHAR columns to behave with the same characteristics as if ROW_FORMAT=FIXED still existed, just change all your VARCHAR columns to CHAR. Then, load the dump.

Please keep in mind that ROW_FORMAT=FIXED really benefits MyISAM (See my old post Which DBMS is good for super-fast reads and a simple data structure?)


How to model a database for a small school portal Good day,
My problem is how to model databases.
I most times get confused, not knowing what table to create or what will be needed.

Is there like a standard someone can take to get things working?

My current project is a simple University Staff management application,
to help the School Admin:

See the distribution of staff arranged by rank in every unit of the
university(programme, dept and faculty).
Carry out transfer of staff
Carry out promotion of staff
Carry out removal of staff
Carry out addition of staff
Answer simple queries, e.g, display professors in faculty of Arts by dept, etc.

I think the way I would tackle a problem like this is to examine it through three lenses (although more may be appropriate in your case).

Objects (usually the nouns in your problem statement)
Relationships (how the nouns interact)
Actions (usually the verbs in your problem statement)

Before I get too much further into this - if you don't mind duplicate data (or potential data-entry errors in your database) then this could be created as a simple flat table with each of the items like programme, department, and facility being text fields associated with each staff member. This, however as I mention, is prone to data entry errors although it makes updating data far simpler.

I am going to propose having several tables which provide you with a hierarchical view of the data. In this case, I'll try to walk through each and explain how I would tackle it:

Objects

I see a few objects that you might want to incorporate in your database:

Staff
Programme
Department
Facility
Position / Role

Each of these will very likely become individual tables in your database. It is best practice to identify these objects with a primary key and a unique identifier (often times an incremental Id column in each table. This, then, allows you to take the next step ...

Relationships

Next, you identify what the relationships are between the objects you've defined. For example, a position / role may "belong to" (be associated with) one department or programme - depending upon how you define those (e.g., an Assistant Professor of Database Design might belong to the Software Department which belongs to the Management Information Sciences programme or vice-versa). A staff member would generally hold only one position / role. If this were the case you would have a tree like this: Staff -> Position -> Department -> Programme (or Staff -> Position -> Programme -> Department depending upon what each means).

If the first is the case, you would likely have a Staff table with a foreign key column called PositionId (or something similar). This PositionId would link in a one-to-many relationship from the Position table to the Staff table. The same would apply for the Position table, which would have a foreign key column called DepartmentId (or ProgrammeId) that had a similar relationship link to the Position table.

One kink in this logic is that it is sometimes possible for a staff person to hold more than one position. This would require a many-to-many relationship which requires a junction table in the middle. For example, if a staff person could hold multiple positions then you would create a new junction table called something like StaffPosition which would have a StaffId and PositionId column in it. This table, then would have one-to-many relationships between it and Staff (on StaffId) and Position (on PositionId). This would make updating the data more complicated, but walking through that is slightly out of the scope of this response.

Actions

Finally, the actions that you define would impact how you are going to interact with these tables. Going through each of your items, I'll give you my best guess on what those would look like (assuming that a Staff person can only hold one position).

Not an action
Not an action
Carry out transfer of staff - perform an UPDATE on the Staff table that changes the PositionId to the new position.
Carry out promotion of staff - perform an UPDATE on the Staff table that changes the PositionId to the new position.
Carry out removal of staff - in this case you might want to do two things: (1) Add a boolean / bit field to the Staff table that indicates whether the staff person is currently employed - you would turn that to OFF in this case and (2) set the PositionId to NULL. You could also, of course, delete the row in the Staff table, but you may need to access this data later (or they may be rehired).
Carry out addition of staff - this would be a simple INSERT into the Staff table, assigning the appropriate PositionId as applicable.
Answer simple queries - the basic schema I've proposed would allow that through some simple INNER JOINS on the proposed tables.

One thing I would mention is that in this configuration, you will need to have additional administrative functions to allow users to not only add Staff persons, but also add / delete Positions, Programmes, Departments, and Facilities (or you can use SQL to do that yourself).

Conclusion
This is a very simple overview and I could see the data model getting pretty complex in your situation. But hopefully this will give you and idea of where to start and how to do so.


Postgresql as docker container not starting with initial data from mapped volume On my macbook I have postgres running in a docker container and I use a mapped volume to persist the data. This works perfectly locally.

However, when I try to do the same on an Ubuntu server the 'initial' data from the mapped volume is not working. Postgres starts up in an 'empty' initial state.
I can see that the volume mapping is working and I've checked and confirmed the data folder for postgres within the container.
Interestingly the mapping does seem to be working, since an added table and data in that table in the default postgres database IS persistent. Furthermore it is interesting to note that I'm getting an error when I try to create a table in a new database. The new database is persistent as well, but the table cant be saved as there is an error thrown:

  could not open file "base/16384/2611": No such file or directory

This is expected as the folder base/16384 doesn't exist.

To me this seems this is a user/rights issue perhaps or some postgres setting? I tried running the container as root, which didn't help.

Any suggestions?

I'm starting the container with either docker-compose or from the command line using;

docker run --rm --name pg -e POSTGRES_PASSWORD=[password] -d -p 5432:5432 -v /root/docker/volumes/postgres:/var/lib/postgresql/data postgres -c listen_addresses='*'

EDIT:
Did try a bunch of things;

The 'bazooka': I chmod 777 the mapped volume, to no effect
run docker container as user postgres:user (this seems to be the user:group of the mapped files), to no effect
run chown of PGDATA folder on docker container start to set owner to postgres:postgres as this seems to be the user:group that is creating a folder when I create a new database, to no effect. even still can't create a table eventhough the database folder was succesfully created

Instead of moving the actual data folder around I used pg_dump and pg_restore within the docker containers per suggestion on the docker forums. This did the trick


Importing CSV into multiple tables using PostgreSQL I have a very big CSV file with the following format:

TAG,TIME,VALUE

as an example row:

footag,2014-06-25 08:00:00.0,3400.0

I used to import it easily inside PostgreSQL 9.3 using the following:
COPY datapoints FROM '/home/foo/my.csv' DELIMITER ',' CSV; where datapoints is a table with the three corresponding columns.

Now, the database into which I need to import has changed. It has two tables. Here are \d+ outputs:

                                          Table "public.tags"
 Column |       Type        |                     Modifiers                     | Storage  | Stats target | Description
--------+-------------------+---------------------------------------------------+----------+--------------+-------------
 id     | integer           | not null default nextval('tags_id_seq'::regclass) | plain    |              |
 tag    | character varying | not null                                          | extended |              |
Indexes:
    "tags_pkey" PRIMARY KEY, btree (id)
    "tags_tag_idx" btree (tag)
Referenced by:
    TABLE "tag_values" CONSTRAINT "tag_values_tag_fkey" FOREIGN KEY (tag_id) REFERENCES tags(id)
Has OIDs: no

                                Table "public.tag_values"
 Column |            Type             | Modifiers | Storage | Stats target | Description
--------+-----------------------------+-----------+---------+--------------+-------------
 tag_id | integer                     | not null  | plain   |              |
 time   | timestamp without time zone | not null  | plain   |              |
 value  | double precision            |           | plain   |              |
Indexes:
    "tag_values_tag_time_idx" btree (tag_id, "time")
Foreign-key constraints:
    "tag_values_tag_fkey" FOREIGN KEY (tag_id) REFERENCES tags(id)
Has OIDs: no

So essentially, the original table is split into two. As you see, there's a foreign key constraint now (between tags(id) and tag_valued(tag_id)) Also, tags(tag) are supposed to be distinct. What is the easiest way to ingest the same .CSV files into such structure?

Thanks!

I find the most reliable way to import CSV data into PostgreSQL is to create a table just for the import (you can delete it after). Often there are wonky values in some columns, so I make all the column types text and then convert them on insert into the "real" table.

The \copy command in psql (different than copy) makes it easy to copy files to a remote server, and insert into ... select makes it easy to copy to another table.


SQL SERVER FileTable/FILESTREAM share suddenly becomes unavailable We had difficulty setting up a FileTable share on a SQL 2014 instance and after giving up on trying to name it after our instance name, we left the name of the share as mssqlserver, but it finally came online, online meaning if you right click in SSMS on the FileTable table you can "Explore FileTable Directory", however it seems to randomly go offline again after some time and you receive the usual canned error message:

So, after magically disabling/enabling FILESTREAM at the server level, database level via t-sql/ssms/configuration manager it some how comes back, but I have to mess with the setting at all of those spots for it to come back! and there seems to be no magical sequence of steps that makes it come back.

Has anyone out there experienced this? Will a restart of the service/server help this? If not, will a repair of sql server help?

It appears that after setting it via configuration manager the issue went away.


MySQL 1264 warning from libmysql I'm using the libmysql.dll library to connect to my database from a 3rd party application (metatrader).

I managed to connect and things are going almost fine.  I use mysql_real_connect and mysql_errno for connecting and capturing possible errors.  I'm using the InnoDB engine and STRICT_TRANS_TABLES in SQL mode.

Questions / Doubts:

I thought mysql_errno only captures errors and not warnings, but I'm getting a 1264 MySQL warning in metatrader (1264 SQLSTATE: 22003 (ER_WARN_DATA_OUT_OF_RANGE)), however it doesn't say where. How can I resolve this?
If I try to manually UPDATE or INSERT against my database I get no warnings and/or errors.

I would like to know if this is a libmysql bug or find out what's going on.  Perhaps there is a specific table/field responsible?

Any pointers?

check my.inior my.cnf if its contain sql-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" if so you need to change it to sql-mode = "NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" then restart the SID service.
Hope that will fix the issue for you


Dump All Databases in MySQL - Fails on mysql and performance_schema I have had two attempts at dumping all databases but both have failed, one with the mysql database and then with the performance_schema database. The precised errors are:-

11:01:01 Dumping mysql - mysqldump: Got error: 1556: You can't use locks with log tables. when doing LOCK TABLES
11:35:23 Dumping performance_schema () - mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

Is there a way to overcome this? Basically I am trying to follw the procedure here:- https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

Many thanks...

Sounds like you are using an older version of the mysqldump client than the database you're taking the dump from.

Do a "which mysqldump", and see if it is in the same bin directory as your running mysqld. Up to date versions of mysqldump ignore performance_schema.


Does order matter for composite primary key in Oracle or any database? I have a Composite Primary key which is combination of Column1, and Column2.  What would be the difference if I have Composite key with Column2, and Column1.  Does it even really matter which order you set columns?

It does, but perhaps not in the way you might expect.

Given col1 and col2, people's initial instinct is to put the more selective column first.

But, that's almost always irrelevant.

First, you need to consider how the index will be used in your SQL statements.  If some SQLs only specify col1, then col1 should probably be first.  If all SQLs will always specify both columns in the where predicate, then it doesn't matter which comes first.

But, what if most of the SQLs specify both col1 and col2, and a few provide only col1 or col2?

Well, in that case, the least selective column should (probably) go first.  There are two reasons for this.  First, if the least selective goes first, then, for those queries where only the second column is specified in the predicate, Oracle will be more likely to do an INDEX SKIP SCAN, if the leading column is not very selective.  Second, by putting the less selective column first, you'll be more likely to be able to take advantage of index compression.

Hope that helps.


How do I make an index for an OR query in MongoDB? So I have a collection with 55 million documents or so. I've enabled system profiling to check for slow queries and I do have a few every now and again.

Example:

{
        "$or" : [
                {
                        "association.ownerId" : ObjectId("55e5c40fe4b03984a0528f89")
                },
                {
                        "association.organisationId" : ObjectId("55e6d92ee4b010a9ebff510d")
                }
        ],
        "visitorId" : 384975548,
        "created" : {
                "$gte" : ISODate("2016-09-18T22:00:00Z"),
                "$lte" : ISODate("2016-09-19T22:00:00Z")
        }
}

.explain() still says it won't use indices only for this query

{
        "cursor" : "BtreeCursor visitorId_1",
        "isMultiKey" : false,
        "n" : 13,
        "nscannedObjects" : 5163,
        "nscanned" : 5163,
        "nscannedObjectsAllPlans" : 44745,
        "nscannedAllPlans" : 44993,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 1621,
        "nChunkSkips" : 0,
        "millis" : 41201,
        "indexBounds" : {
                "visitorId" : [
                        [
                                2962347,
                                2962347
                        ]
                ]
        },
        "server" : "<censored_host>:27017",
        "filterSet" : false
}

Here are some indices I have:

{
            "v" : 1,
            "key" : {
                    "association.ownerId" : 1,
                    "association.organisationId" : 1,
                    "visitorId" : 1,
                    "created" : 1
            },
            "name" : "association.ownerId_1_association.organisationId_1_visitorId_1_created_1",
        "ns" : "censored_dbname.events"
},
{
        "v" : 1,
        "key" : {
                "association.ownerId" : 1,
                "visitorId" : 1,
                "created" : 1
        },
        "name" : "association.ownerId_1_visitorId_1_created_1",
        "ns" : "censored_dbname.events",
        "background" : true
},
{
        "v" : 1,
        "key" : {
                "association.organisationId" : 1,
                "visitorId" : 1,
                "created" : 1
        },
        "name" : "association.organisationId_1_visitorId_1_created_1",
        "ns" : "censored_dbname.events",
        "background" : true
}

Anyone have a clue how I can improve this query?

You're hitting SERVER-13732.

For the query planner to properly consider indexes and apply the appropriate index bounces, the query will need to be reformatted to the following structure:

{
       "$or" : [
            {
                    "association.ownerId" : ObjectId("55e5c40fe4b03984a0528f89"),
                    "visitorId" : 384975548,
                    "created" : {
                           "$gte" : ISODate("2016-09-18T22:00:00Z"),
                           "$lte" : ISODate("2016-09-19T22:00:00Z")
                    }
            },
            {
                    "association.organisationId" : ObjectId("55e6d92ee4b010a9ebff510d"),
                    "visitorId" : 384975548,
                    "created" : {
                           "$gte" : ISODate("2016-09-18T22:00:00Z"),
                           "$lte" : ISODate("2016-09-19T22:00:00Z")
                    }
            }
    ]
}

It should then be able to use the combination of { "association.ownerId" : 1, "visitorId" : 1, "created" : 1 } and { "association.organisationId" : 1, "visitorId" : 1, "created" : 1 }. That being said, you should try testing the re-structured query pattern first using .explain().


Exporting Very Large Database from mysql 5.5 Before I proceed, I would like to confirm can I export a very large table of 3.6GB size using the MySQL Admin Export feature. I have exported tables of small size like 28 MB but just wondering exporting such a large database would break anything?

Say for example, in the image below, I want to export category table which is of 3.6GB size.

Also, I have more tables whose size is 11.6 GB for which I would like to do the export in the future.

I want to use this technique rather than using mysqldump.

There wouldn't be anything broken , mysqlworkbench is a pretty mature software.
You just need to watch the filesystem of the storage where you are going to store the file.
ntfs would be fine, but fat32 will limit you to around 4GB.

However, if you are just looking for an exporter with UI, Navicat Premium is more comfortable in my opinion. Hope it helps.


How to query results where a column with 2 classifications has same value in another column in the same table? I have a table like this:

Column A    Column B
-------------------------
Agent A     abc@gmail.com
Agent B     abc@gmail.com
Agent A     abc@gmail.com
Agent B     def@gmail.com
Agent C     efg@gmail.com

I want to see results where the same email address exists for Agents A and B.

Something like this:

select a.columna, a.email
from (select * from tablename where columna='A') a
join (select * from tablename where columna='B') b
on (a.columnB=b.ColumnB)


How to handle connection pooling for massive multi-tenancy/multi-schema environment Here's a quick run down of the situation:

We have 1 Schema per customer.
We have 2000+ customers.
We have 50+ Database servers (with the above schema's distributed
unevenly amongst them).

We are creating a true stateless app frontend (i.e. improving legacy software).  This means that we could have many (100+) app servers dealing with user traffic, with each app server potentially needing to connect to any one of the 50+ servers to pull data at any one time.

The reason for having so many app servers is that the code is extremely computationally intensive.  It may be that eventually we can move towards fewer app servers with more powerful hardware, but we're not quite there yet.

My question:  How does one manage the connection pooling situation in this type of environment?
If we imagine a "good" connection pool is around 64 connections, it doesn't seem feasible to have each app server generate a connection pool to each DB server. It would result in 100+x64 = 6400 persistent connections being made to each DB server... is that too much?

What can be done?  Is there some sort of connection-pool proxy software that can be used?

You have 2000 schemas spread across 50 servers...I don't think there's a connection pooler that exists to handle that type of situation.  You're going to have to roll your own in application code, I believe.

I'd really step back and take a look at your architecture.  What happens if you double the number of customers?  Triple it?  This type of design seems seriously unmanageable and difficult to scale to new levels of business.

HTH,
Dave Sisk


Connecting to SQL Server from R which is Running on Remote UNIX Machine We have a UNIX machine which includes R engine on it and installed SQL Server ODBC Driver 11.

We need to connect R to SQL Server. SQL Server currently works on another machine, so we tried to connect R to SQL Server with the code below.

library(RODBC)

dbhandle <- odbcDriverConnect('driver=ODBC Driver 11 for SQL Server;server=10.XXX.XX.XX:1663;')

But getting that error:

  Warning messages: 1: In odbcDriverConnect("driver=ODBC Driver 11 for
  SQL Server;server=10.XXX.XX.XX:1663;") :

  [RODBC] ERROR: state HYT00, code 0, message [unixODBC][Microsoft][ODBC
  Driver 11 for SQL Server]Login timeout expired 2: In
  odbcDriverConnect("driver=ODBC Driver 11 for SQL
  Server;server=10.XXX.XX.XX:1663;") :

  [RODBC] ERROR: state 08001, code 87, message
  [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]A network-related
  or instance-specific error has occurred while establishing a
  connection to SQL Server. Server is not found or not accessible. Check
  if instance name is correct and if SQL Server is configured to allow
  remote connections. For more information see SQL Server Books Online.
  3: In odbcDriverConnect("driver=ODBC Driver 11 for SQL
  Server;server=10.XXX.XX.XX:1663;") :

  [RODBC] ERROR: state 08001, code 87, message
  [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network
  Interfaces: Connection string is not valid [87]. 4: In
  odbcDriverConnect("driver=ODBC Driver 11 for SQL
  Server;server=10.XXX.XX.XX:1663;") : ODBC connection failed

If I run telnet to SQL Server from UNIX machine, it works perfectly. I mean there is a healthy connection between these two machine.

How can we connect R to SQL Server without using DSN?

I changed delimiter which between server and port from : to , . It fixed my problem.

dbhandle <- odbcDriverConnect('driver=ODBC Driver 11 for SQL Server;server=10.XXX.XX.XX,1663;')