is vertical scaling good idea for a mysql database server?

"You can't throw hardware at a performance problem."  Or, rather, you can't do that twice.

CPU speed has not changed significantly in 20 years.
The number of cores increases, but InnoDB has always hit a brick wall before using all cores.
SSDs were a quantum improvement over HDDs, but nothing is on the horizon for the next leap.
Bigger RAM (hence buffer_pool) gets into "diminishing returns".
Ethernet makes a 10x "speedup" every so often.
It would take years to fill a Petabyte disk; don't you want to read the data before that?
etc.

One trick is to virtualize a big hunk of iron by having multiple VMs, each running an instance of MySQL.  This does "horizontal" scaling on a single "vertically" scaled server.  This at least helps with the brick wall I mentioned.  But then you have a big, costly, single-point-of-failure.

My usual answer is "let's see if we can program smarter".

A recurring example is Data Warehousing.  DBAs come to this (and other) forums bemoaning that their 'report' (a big SELECT with a big GROUP BY) is taking too long.  I point out the beauty of a Summary table -- 10x speedup, 10x smaller disk footprint.  Poof, problem gone.  No hardware upgrade could have gotten 10x.

Replication provides nearly infinite scaling, nearly linear, of reads.

Sharding (horizontal scaling) provides nearly infinite scaling, nearly linearly, but it does not work for all applications.

The best I see in Vertical is adding enough RAM to turn an I/O-bound query into a CPU-bound query running in the buffer_pool.  I regularly see a 10x improvement for that.  But it is a one-time improvement.  That is, adding even more RAM won't help any more.

So, to your title question, I say a resounding No.
OK, Vertical may help, but be aware of its limits.


How to Create Table Name That Contains Last Month in Suffix

declare
  l_cmd clob;
begin
  l_cmd := 'create table abc_'|| to_char(add_months(SYSDATE,-1), 'MONDD') || ' (c1 number)';
  execute immediate l_cmd;
end;
/

SQL> select table_name from user_tables where table_name like 'ABC%';

TABLE_NAME
----------
ABC_JAN13

SQL> desc abc_jan13
 Name  Null?    Type
 ----- -------- -------
 C1             NUMBER


How to give read only access to a user to view the tables of different owner in oracle

The REFERENCES privilege kind of provides this:

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> grant create session to u1, u2;

Grant succeeded.

SQL> grant create table to u1;

Grant succeeded.

SQL> create table u1.t1 (c1 number);

Table created.

SQL> grant references on u1.t1 to u2;

Grant succeeded.

SQL> conn u2/u2
Connected.
SQL> desc u1.t1
 Name           Null?     Type
 ------------------------------------
 C1                       NUMBER

SQL> select * from u1.t1;
select * from u1.t1
                 *
ERROR at line 1:
ORA-01031: insufficient privileges


Honestly it really just depends on your use cases, but for me I would likely just store all 10 columns in the same table and call it a day (unless they were very unrelated to each other, then I might normalize it a bit). Having the full list of fields might influence different answers, and your approach is a valid one as well. It just might be more work to maintain that way.
Essentially it would be a Transaction table, and log a record for each change on any of the fields. Then it's easiest to look at what the current state of a given product is, or what its state was for a given timeframe.
Yea you'll run into some data duplication, but most modern database systems automatically compress their tables anyway. So unless you think you're going to be creating billions of transactions a day and start creeping into big data territory, I wouldn't be super concerned from a performance perspective.


You need to restore last FULL backup, the Differential, the 10:15 transaction backup and the 10:30 one with the STOPAT = '2015-05-22 22:24:00' clause.


Your code is obtaining the result of the RETURN @result at the end of the stored proc.  The results you want are being passed out of the stored procedure  as a ResultSet.  You need to use a DataReader object to see the rows.

I'm not sure how to do that using Linq.  You'll hopefully get a better answer on your StackOverflow question.

Sample code for a VB.Net console application to read the results of executing a Stored Procedure:

Module Module1

    Sub Main()
        Dim cConnectionBuilder As New SqlClient.SqlConnectionStringBuilder
        Dim sConnectionString As String

        cConnectionBuilder.ApplicationName = "Test App"
        cConnectionBuilder.ConnectTimeout = 15
        cConnectionBuilder.DataSource = "<servername here>"
        cConnectionBuilder.InitialCatalog = "tempdb"
        cConnectionBuilder.IntegratedSecurity = True
        cConnectionBuilder.Pooling = False
        sConnectionString = cConnectionBuilder.ConnectionString

        Using cConnection As New SqlClient.SqlConnection(sConnectionString)
            cConnection.Open()
            'drop the existing sample_proc procedure if it exists
            Using cCommand As New SqlClient.SqlCommand("IF OBJECT_ID(N'dbo.sample_proc', N'P') IS NOT NULL DROP PROCEDURE dbo.sample_proc;", cConnection)
                cCommand.CommandType = CommandType.Text
                cCommand.ExecuteNonQuery()
            End Using
            'create the sample_proc procedure
            Using cCommand As New SqlClient.SqlCommand("CREATE PROCEDURE dbo.sample_proc (@TestID int) AS BEGIN SELECT name FROM sys.columns; RETURN 1; END", cConnection)
                cCommand.CommandType = CommandType.Text
                cCommand.ExecuteNonQuery()
            End Using
            'execute the sample_proc procedure, and print a line in the console window for each row in the ResultSet
            Using cCommand As New SqlClient.SqlCommand("dbo.sample_proc", cConnection)
                cCommand.Parameters.Add(New SqlClient.SqlParameter("@TestID", 1))
                cCommand.CommandType = CommandType.StoredProcedure
                Using dReader As SqlClient.SqlDataReader = cCommand.ExecuteReader
                    While dReader.Read
                        'get each row from the resultset
                        Dim sName As String = dReader.GetString(dReader.GetOrdinal("name"))
                        Console.WriteLine(sName)
                    End While
                    dReader.Close()
                End Using
            End Using
            cConnection.Close()
        End Using
        Console.WriteLine("Press 'Q' key to quit.")
        Dim c As ConsoleKeyInfo = Console.ReadKey
        Do Until c.Key = ConsoleKey.Q
            System.Threading.Thread.Sleep(1)
            c = Console.ReadKey
        Loop
    End Sub

End Module

Output looks like this:

account_name
account_name
addr
Press 'Q' key to quit.


Does number of backup tables hinder performance

As long as the database and its tables and many indexes are just sitting there, there is essentially no harm:

The extra db and tables have no impact on your ordinary queries.
Some meta operation involving the information_schema will notice them.
The copy takes disk space.  (So would a dump.)

An advantage over a dump:  You can quickly perform a query on the old copy.


Should you update child states based on the parent?

It's opinion based, but I would err on the side of updating the bare minimum when a status changes.

If you cancel an event and only update the Event row indicating cancelled then you minimize the amount of data that would need to be updated should the event be reinstated.
Likewise, if you cancel a session, and only update the Session row indicating cancelled then you minimize the amount of data that would need to be updated _should the session be reinstated.
As Joe W points out in a comment:

  if you do update the sessions as well if there was sessions canceled
  for other reasons you would have a harder time getting the status
  correct if you un-cancel an event as you might not know exactly which
  sessions should still be canceled.


How can i make SQL exec statements run in order

Short answer is yes, that is the default behavior.

SQL is a data access language that will run statments in batches, you'd have to use other features (Service Broker for example) to run statements asynchronously.

One of the main concepts of the language is the idea of Transactions. There are several articles out there which explain this concept in detail with straightforward examples. For Example.

I'd highly recommend practicing this because understanding how to use Transactions becomes especially important when one statement relies on the successful execution of the previous statement.


Mysql 8, Query on user table garbles terminal. Is this an Installation issue?

Before I had this issue, I had to reinitialize my instance due to screwing up the mysql database when restoring backup dumps.

I fixed this issue by reinitializing the database

sudo service mysql stop
sudo mv /var/lib/mysql old_mysql
mkdir mysql; chown -R mysql.mysql mysql/
mysqld --initialize

Right here, here is where there was a problem. During the initial install I choose the mysql password rather than the new more secure authentication method. I wasn't sure how that would impact restoring dumps from a MySQL 5.7 instance.

I fixed the issue this way:

ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password  BY 'password'

I did this for every row that had a 'root'@ user and this fixed the issue.

Obviously, the secured method has some control characters in the authentication_string that should not be directly output to terminal.


PostgreSQL 9.5 database showing signs of corruption in logs, though clients work normally

If only pg_statistic is affected, you might get out of this cheap:
Set allow_system_table_mods = on in postgresql.conf and restart PostgreSQL. Then run
TRUNCATE pg_catalog.pg_statistic;
ANALYZE;

Don't forget to reset allow_system_table_mods when you are done.
But you shouldn't ever trust a PostgreSQL database cluster that has had database corruption. Create a new cluster, export the old one with pg_dumpall and import it into the new cluster. Then discard the cluster that had data corruption.
You might want to create the new cluster with --data-checksums so that you get an error message if data change while on disk.
Now you should investigate the possible causes. Upgrade to the latest minor release and test your hardware for problems.


Have you created a contact group in Outlook or a Distribution group in Exchange? (I'm assuming you are using Exchange?)

The Outlook group is just a container that lets you send emails to many people from your personal machine.  The SQL Server is trying to send to a group that exists only on your Outlook and not on the Exchange server which is why single email recipients work but not the group.

I would suggest setting up, or asking your email admin to setup a new SQL support distribution group on your mail server.

or

You can send to multiple single email addresses by separating the address of the recipient with a ;


Transactional Replication - Snapshot metrics

The metrics I want to capture are:

  The size of the snapshot generated for each publication.
  The time it took to generate the snapshot.
  The time it took to apply the snapshot at the subscriber.

  Is it possible to get this information by querying distribution?

You cannot get such metrics using T-SQL. You can check the replication status using T-SQL though.

Depending of how large/big your publication database is (for small databases in same network, you should not even look at the time/size as it will be super fast), you can follow below steps to get a ROUGH estimate (ball park figure) of the size and time of snapshot to get generated and apply at the subscriber :

Note: I put emphasis on Rough estimate as depending on what you are replicating e.g. store procedures, indexes, constraints, etc there will be additional files generated as well.

Select top 10 (depends on you - how many you choose) articles with highest row counts from the publication database and BCP OUT the data. This will give a better judgement for space requirement of snapshot agent. This would be resource intensive operation and hence should be scheduled during quiet activity of the server.

e.g. bcp publisher_database.article_name out largeTable1.dat -T -c

Note: Above will give you an estimated time taken by BCP and the size of the BCP file.

So above point will answer you 1st and 2nd points.
Now to get the time it took to apply the snapshot to the subscriber, you have to run bcp in from the publisher server specifying the SUBSCRIBER_SERVER_NAME

e.g. bcp subscriber_database.article_name in -S SUBSCRIBER_SERVER_NAME\instance_name largeTable1.dat -T -c

This way you get an estimate of how long will it take to apply snapshot to subscriber.


Is it possible to upgrade Oracle 8i to 12c?

There is no in-place upgrade possible but the existing exp utility is still supported for importing dmp files.

Some of the issues you need to research are:

I have taken an export from an 8i database and imported it into an 11g with no issues. Importing into 12 should not be a problem but planning is everything!
will you use the same character set?  Often organizations start with western only and need to use UTF16 for more compatibility with other languages.
do yourself a favour and do an install on a new machine with new hardware.  12 requires more of everything, memory, hard drive.
many 8i parameters are not supported so you need to establish if there are any custom settings that you need equivalents for

Of course this would need to be tested.


SQL Server - Handling localization of strings in nested non-deterministic view stacks

Looking at the given code, we can say,

First, this should not be a view but it should be a stored procedure, as it is not just reading from a table, but it uses UDFs.
Second, the UDF should not be called frequently for the same column. Here, it's called once in the select

,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID

and Second time for joining

.IETFLangCode = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS')

One can generate values in a temporary table or use a CTE (Common Table Expression) to get those values in the first place before the join takes place.

I have generated a sample USP that will provide some improvements:

CREATE PROCEDURE usp_UserWKStringI18N
AS
BEGIN
    -- Do operation using UDF
    SELECT b.WKType
        ,b.WKIndex
        ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID
        ,dbo.UserI18N_Session_Locale_Key() AS IETFSessionLangCode
        ,dbo.UserI18N_Database_Locale_Key() AS IETFDatabaseLangCode
    INTO #tempTable
    FROM UserWKStringBASE b;

    -- Now final Select
    SELECT b.WKType
        ,b.WKIndex
        ,CASE
            WHEN ISNULL(il.I18NID, N'') = N''
                THEN id.I18NString
            ELSE il.I18nString
            END AS WKString
        ,CASE
            WHEN ISNULL(il.I18NID, N'') = N''
                THEN id.IETFLangCode
            ELSE il.IETFLangCode
            END AS IETFLangCode
        ,b.I18NID
        ,b.IETFSessionLangCode
        ,b.IETFDatabaseLangCode
    FROM #tempTable b
    LEFT OUTER JOIN User3StringI18N il
        ON il.I18NID = b.I18NID
            AND il.IETFLangCode = b.IETFSessionLangCode
    LEFT OUTER JOIN User3StringI18N id
        ON id.I18NID = b.I18NID
            AND id.IETFLangCode = b.IETFDatabaseLangCode
END

Please try this


Upgrading from single server to new GTID-replicated master and slave MySQL 5.7

I think your project is realistic.

I suggest a migration path below who can minimising the database downtime and make things smooth.

For this explanation, I named the Current database server PrdOld, the next database Master (RW) server Prd01 and the next database Slave (RO) Prd02.

As replication from 5.6 to 5.7 is supported, put your current MySql 5.6 database (PrdOld) as  a replication master

see at log_bin, relay_log, sync_binlog, server_id, expire_logs_days, binlog_cache_size, max_binlog_size my.cnf variables
don't search to enable GTID now
format=ROW and innodb_file_per_table are advisable

Prepare Prd01 as slave of PrdOld and master of Prd02
Prepare Prd02 as slave of Prd01

So we'll have a replication chain:

  PrdOld --> Prd01 --> Prd02

Do a full mysqldump (all replicated databases) of PrdOld
Write down File and Position from SHOW MASTER STATUS; command

For a simpler process I choose to enable replication right now from Prd01 to Prd02 but it's not mandatory, it depend of your database (size is a factor but not the only one) and activity (load, ...).

Restore your dump on Prd01 witch is propagated to Prd02 (it may generate some slave lag during this step)
Enable replication between PrdOld and Prd01

You have now three live and synced databases.

Observe for a while how things are working. And decide quietly when you want to switch the application db  connection context to prd01.
Then cut PrdOld to Prd01 replication mechanism and shutdown definitively PrdOld.

For the db HA aspect between Prd01 and Prd02 (in case of failure), choose to give some virtual IP to each db server (as IPmaster and IPslave) of use a load balancer in front of them (HAProxy, NGinx, ...).

Enable master-master replication (known also circular replication) between Prd01 and Prd02
Enable GTID mechanism

see at log_slave_updates, gtid_mode, enforce_gtid_consistency variables

Notice: You have to watch carefully the Seconds_Behind_Master indicator (during dump restore then after during production) in SHOW SLAVE STATUS\G. Big number is not necessary a problem is it doesn't stay for a while ...


Connect to remote EC2 Postgresql database

You need to work your way up the networking stack to determine where the issue is.

Can you ping the destination from the source?

Can you telnet to the postgres port(5432 by default) from the source?

Can you connect to the postgres service with a management tool(pgadmin or psql) from the source?


What Oracle SQL syntax do I need to find transactions that occurred during time intervals of a day for a period of a year?

Simply add a WHERE-condition like this:

WHERE datecol BETWEEN DATE '2014-01-01' AND DATE '2014-12-31'
  AND EXTRACT(HOUR FROM CAST(datecol AS TIMESTAMP(0))) BETWEEN 15 AND 23

To dynamically define the previous year:

WHERE datecol >= ADD_MONTHS(TRUNC(CURRENT_DATE, 'year'), -12) -- jan 1st previous year
  AND datecol < TRUNC(CURRENT_DATE, 'year')                   -- jan 1st current year
  AND EXTRACT(HOUR FROM CAST(datecol AS TIMESTAMP(0))) BETWEEN 15 AND 23


Error Code: 1136. Column count doesn't match value count at row 1

Change from

SELECT
    @countryname
FROM
    (
    SELECT
        @countryname AS countryName,
        @nationality AS nationality,
        @countryprefix AS countryPrefix

to

SELECT
    @countryname AS countryName,
    @nationality AS nationality,
    @countryprefix AS countryPrefix


Calling stored procedure from job does nothing

I started yesterday by profiling the job execution and found nothing of interest.

As it turns out, the devil was in calling [MyOtherDatabase].dbo.fn_LocalTimeToUTC.

For some reason (that is beyond me), there's some credentials checking going on (yes: in a time conversion function...) but if that fails, no error is returned - but also no result. Don't ask. I didn't write it.

Once I decided to skip the conversion everything worked flawlessly.

I'll be wanting those two days of my life back now.


Database design for tracked inventory that could get “transformed”

I'm guessing that you want two things:  A full history of each item, and a "current" location of each item.  Let's make that two database tables.

When items are "combined", let's give the combination a separate serial number.

CREATE TABLE History (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    item_id ...,   -- serial number of item
    combined_id ...,  -- pseudo-serialnumber of combination (or 0 if item by self)
    dt DATE TIME,   -- when the change occurred
    PRIMARY KEY(id),
    INDEX(item_id, dt)  -- handy for listing history of an item
) ENGINE=InnoDB;

CREATE TABLE Current (
    item_id ...,   -- serial number of item
    combined_id ...,  -- what it is currently in
    dt DATE TIME,   -- when it was constructed
    PRIMARY KEY(item_id),
    INDEX(combined_id)   -- to identify current components
) ENGINE=InnoDB;

CREATE TABLE Combos (
    combined_id INT UNSIGNED AUTO_INCREMENT NOT NULL, -- for generating ids
    etc -- description or whatever
    PRIMARY KEY(combined_id)
) ENGINE=InnoDB;

When you "combine" a bunch of items:

INSERT into Combos to get a new combined_id.
Change Current as needed
INSERT multiple rows into History.


encryption of SSRS subscription emails and attachments

Not out of the box as far as I am aware, and if you need to distribute reports to external parties this is a bad idea as they will all have to install third party encryption software (incurring licensing and support costs).  It will also put your help desk people into the job of babysitting all the users as they forget their keys.

You're much better off providing a secure portal (this can be done with Sharepoint if you have this) and emailing out links to the reports.


When you restore a database backup, it completely overwrites the prior database regardless of its contents. There is no merging functionality built into the SQL Server restore process.

If you need to merge the contents of two databases, or restore a particular table by itself, your best bet is to:

Restore the backup to a different database name, like Production_Restored
Copy the objects you want from Production_Restored into the Production database
Drop the Production_Restored backup when you're done

As you copy objects from one database to another, keep in mind that transaction logging is involved. If you copy a 50GB table from one of these databases to the other, the target database's transaction log will grow, fast. Your best bet there is to move the data across in batches. (And of course, there will be blocking involved as data is changed.)


Following @a_horse_with_no_name's suggestion again, because he's really smart though super, super-resilient to using the Post Your Answer functionality.

SELECT DISTINCT ON(id),
  id,
  views - lead(views) OVER (PARTITION BY id ORDER BY update_date DESC) AS vdiff
FROM (
  SELECT id,
    views,
    update_desc,
    row_number() OVER (PARTITION BY id ORDER BY update_date DESC) AS rn
  FROM videoupdate
) AS t
WHERE rn <=2
ORDER BY id, update_desc DESC;


Create a Constraint such that only one of two fields must be filled

You need to use a check constraint:

create table kevin
(
  one   integer,
  other integer,
  constraint only_one_value
        check (        (one is null or other is null)
               and not (one is null and other is null) )
);

This ensures that at least one of the columns has a value and that not both have a value.

If those are varchar columns you might want to check for empty values ('') as well. To do that use nullif() which converts a value to null if it is equal to the second argument of the function.

create table kevin
(
  one   integer,
  other integer,
  constraint only_one_value
        check (        (nullif(one,'') is null or nullif(other,'') is null)
               and not (nullif(one,'') is null and nullif(other,'') is null) )
);


SSIS export to CSV Flat File Destination task, error The code page on input column is ... and is required to be

The issue is caused by a clash in code pages between 1252 (i.e. Windows-1252) in SQL Server and 65001 (i.e. UTF-8) that your CSV file is expecting.

I believe one solution to the issue would be to CAST your source columns to NVARCHAR in the initial SELECT from the database, or use a Data Conversion task to convert them to Unicode strings.

An alternative would be set the code page in the Flat File Connection Manager to 1252 ANSI - Latin.


Merge reports 'You must rerun snapshot because current snapshot files are obsolete', gets caught in Death Loop

For problems with dropping the table the things I would look at making sure there isn't a trigger, view or foreign key preventing the table from being dropped. Next time you apply the snapshot run Profiler pointed at the target database/server to return only Exceptions and Error messages while applying the snapshot against the subscriber. The messages returned should guide you to the exact cause.

Regarding the snapshot becoming invalid, double check the retention period configured for your distribution database. If this is less than the total time it takes to successfully apply the snapshot to the subscriber it will invalidate the snapshot and re-run the snapshot generation and restart the copy to the subscriber.


Python Postgres psycopg2 ThreadedConnectionPool exhausted

Don't pass the parameter close=True. putconn already "closes" the connection. Think of putconn like "put connection back in pool", which is probably what you want.


Alternatively you can try the following method:

SELECT
  x.*
FROM
  (
    VALUES
      ('emp1%', 3),
      ('emp3%', 2)
  ) AS v (pattern, row_count)
  CROSS APPLY
  (  -- your query
    SELECT top (v.row_count)
               employee_id, employee_ident, utc_dt, rx_dt
    FROM       employee
    INNER JOIN employee_mdata_history
    ON         employee.ident=employee_mdata_history.employee_ident
    WHERE      employee_id like v.pattern
    ORDER BY   rx_dt desc
  ) AS x
;

The VALUES row constructor represents your pattern list as a table, additionally supplying each pattern with the number of rows to retrieve for that pattern. The CROSS APPLY operator applies your query to every row of the pattern list, i.e. to every pattern, limiting the number of rows for each pattern to the corresponding value from the pattern list.

As a side note, please let me take this opportunity to suggest that you always qualify your columns with the table alias in a query that is reading from two or more tables. That makes your query easier to read/understand. You can always use short aliases to avoid repeating potentially long table names. For instance:

SELECT TOP (1)
  e.employee_id,
  h.employee_ident,
  ...
FROM
  dbo.employee AS e
  INNER JOIN dbo.employee_mdata_history AS h
    ON e.ident = h.employee_ident
WHERE
  e.employee_id LIKE ...
ORDER BY
  ...


I have a suggestion that could simply your code and your SQL

You are doing round trips at this point by checking each individual someID, coming back to the client, seeing if the check failed. If the check failed, it is OK to perform the INSERT.  That's another round trip. You will require some kind of locking for reads.

If you have to INSERT 10000 rows:

In the best case, you run 10000 SELECTs are find that someID exists
In the worst case, you run 10000 SELECTs, each someID does not exist, and do 10000 INSERTs.

Why not just perform all this with a single SQL statement ?

function doStuff(int someID) {
    getvalue = `SELECT value FROM statetable`
    INSERT IGNORE INTO targettable VALUES (someID,getvalue);
}

This works only if someID is unique in targettable

In your code, try submitting a list of someID values, iterate that list, and submit INSERT IGNORE commands for all someID values. That you can do in a START TRANSACTION/COMMIT block.

function doStuff(int someIDList) {
    int idcount = 0;
    getvalue = `SELECT value FROM statetable`
    for someID in someIDList
    do
        if idcount = 0 then
            START TRANSACTION
        endif;
        idcount++;
        INSERT IGNORE INTO targettable VALUES (someID,getvalue);
    end while;
    if idcount > 0 then
        COMMIT
    endif;
}


For PostgreSQL database, restrict remote connections to password

The line

host    all             all             0.0.0.0/0               trust

Lets anyone in without a password (other than connections over the unix file socket, connections over the loopback device, and replication users).  Since it occurs earlier in the file, it takes priority over the x.x.x.x/x line.


You are mixing up Db2 (Db2 for Linux, Unix and Windows) and Db2 for z/OS.

Db2 9.7 is LUW (Linux, Unix and Windows) and FIST_VALUE is available on these OS'es.

Db2 for z/OS does not support FIRST_VALUE.


How about a GROUP BY count on foobar from scratch ???
First, insert any new data into foobar
Then, do a fresh GROUP BY count on foobar into the temp table:
CREATE TABLE foo_amount_new LIKE foo_amount;
INSERT INTO foo_amount_new
SELECT foo_id,COUNT(1)
FROM foobar WHERE bar_id = ...
GROUP BY foo_id;

Finally, swap the temp table in and drop the old foo_amount
ALTER TABLE foo_amount RENAME foo_amount_zap;
ALTER TABLE foo_amount_new RENAME foo_amount;
DROP TABLE foo_amount_zap;

However, with a table in the billions, this is an uphill battle because you have an index to rebuild. Since the following happens on every INSERT ... ON DUPLICATE KEY:

the amount would have to incremented
the amount would have to shift with in the amount index

Try removing the amount index so as to speed up INSERTs and UPDATEs.
ALTERNATE SUGGESTION
Try out your temp table solution using another method
STEP 01) CREATE TABLE foobar_new LIKE foobar;
STEP 02) Do your bulk INSERTs into foobar_new
STEP 03) CREATE TABLE foo_amount_new LIKE foo_amount;
STEP 04) Perform GROUP BY count on the latest bulk INSERT batch
INSERT INTO foo_amount_new
SELECT foo_id,COUNT(1) FROM foobar_new WHERE bar_id = ...
GROUP BY foo_id;

STEP 05) Perform a bulk INSERT into foobar from foobar_new
INSERT INTO foobar SELECT * FROM foobar_new;

STEP 06) Perform a bulk UPDATE of foo_amount from foo_amount_new
UPDATE foo_amount A INNER JOIN foo_amount_new B
USING (foo_id) SET A.amount = A.amount + B.amount;

STEP 07) Drop the temp tables
DROP TABLE foobar_new;
DROP TABLE foo_amount_new;


Agent shows disabled(although it is not) after migration

It is whenever the agent service is stopped that the run_value of sp_configure 'Agent XPs'changes to 0. Whenever the agent service is started, it is set automatically back to 1.

This is expected behaviour. When you STOP sql server agent, the congiguration option Agent XPs changes the config_value and run_value to 0. When you start sql agent, then the config_value and run_value for Agent XPs changes back to 1.

It is important to run reconfigure with override when you enable the configuration option for the first time using sp_configure 'Agent XPs', 1


How to create a master key on the master database?What are the roles required to GRANT to the login and user of the master db for the same?

I don't believe it's possible, or useful to CREATE MASTER KEY in the master database in Azure SQL Database/Azure Synapse Analytics (formerly SQL Data Warehouse).  And the error you're seeing is a bit misleading, as you get that error whenever you attempt an operation in master that is allowed in normal SQL Server, but prohibited in Azure SQL Database.

The Database Master Key (DMK) in the master database is used to protect Certificates and Asymmetric Keys in master, but you can't create either of those in the master database in Azure SQL Database.  And in normal SQL Server the DMK in master protects the Database Encryption Keys (DEK) for Transparent Database Encryption (TDE) of user databases.

The encryption hierarchy for TDE in Azure SQL Database works differently:

  Transparent data encryption encrypts the storage of an entire database
  by using a symmetric key called the database encryption key. This
  database encryption key is protected by the transparent data
  encryption protector. The protector is either a service-managed
  certificate (service-managed transparent data encryption) or an
  asymmetric key stored in Azure Key Vault (Bring Your Own Key). You set
  the transparent data encryption protector at the server level for
  Azure SQL Database and Data Warehouse, and instance level for Azure
  SQL Managed Instance.


Export the table permission before dropping:

    SELECT
  (
    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as +
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
  ) AS GRANT_STMT
FROM sys.database_permissions AS dp
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('public','guest')
--  AND o.name IN ('My_Procedure')      -- Uncomment to filter to specific object(s)
--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs


SQL Server 2014 Minimum Memory configuration - VMware

There is no clear best practice on this. It all depends on your VMWare configuration and failover requirements.

The idea behind this is the fact that it should be preventing SQL Server from releasing memory when the VMWare balloon driver thinks it needs this.

There are several possible problems with that. One of them being the fact that the balloon driver may be requesting memory because a physical host is failing and the VM needs to be moved elsewhere (or other VM's need to be moved to the host the SQL VM resides on).

In case of disaster, what do you prefer? A slow SQL Server or no SQL Server?

The balloon driver will just request memory from other processes on the server if SQL doesn't release it (SSRS, OS, whatever) and if the OS starts paging SQL will suffer anyway.

If you have this configuration because of you are overcommitting memory it might be a better option to reserve memory for the server in VMWare to make sure SQL actually has available what has been allocated.

This setting could be a good configuration, but setting it as a default seems wrong to me. You need to thoroughly understand what it does and what the effects on VMWare are to decide if it should be configured in your environment. Have a good chat with the datacenter/VMWare folks to understand how they have things set up too.


The problem is that renaming ( (attack - defense) as capacity_difference ) is not known at the time when your case expression is evaluated. I.e. Your case expression is evaluating the column capacity_difference which you have not assigned a value. I would suggest that you get rid of that column, and do:
SELECT name
     , type_1
     , type_2
     , HP
     , CASE when capacity_difference > 90
            then "collect asap"
            when capacity_difference < 90 and capacity_difference > 50
            then "good"
            when capacity_difference > 0 and capacity_difference < 50
            then "okay"
            when capacity_difference > -10 and capacity_difference < 0
            then "bad"
            ELSE "worse"
       END AS Collectability
FROM (
    SELECT (attack - defense) as capacity_difference
         ,  name
         , type_1
         , type_2
         , HP
    FROM pokemon
) AS t;


ARCHIVELOG in Oracle 12c multitenant PDB

Because all PDBs share the same redo log files, it's not possible to enable archive log mode only for a specific PDB. All PDBs share the archive log mode of the CDB.

  Remember, the PDBs themselves don't have any instance (processes and memory areas) or control file and redo logs

Oracle Database 12c Feature: Multitenant Database


Removing AG and clustering - make nodes stand-alone

Yes, the machines stay up and running when the cluster is destroyed and it does not require a reboot.  However, I would still recommend doing this after business hours if possible because things don't always work as designed.


Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format?

The most important thing about documentation is that it makes sense to you.  There's no really standard way of doing this.

If you've got lots of stored procedures that all connect to each other starting with a Visio diagram with one object for each procedure, then links between them so you can track how things go from procedure to procedure is probably a pretty good start.


If I understand you correctly you want a comma separated list of triggering_id's

select source_id,
       string_agg(triggering_id::text,',') as id_list
from notifications
group by source_id;


MongoDB terminates when it runs out of memory

As per MongoDB BOL Here Changed in version 3.4: Values can range from 256MB to 10TB and can be a float. In addition, the default value has also changed.

Starting in 3.4, the WiredTiger internal cache, by default, will use the larger of either:

50% of RAM minus 1 GB, or
256 MB.

With WiredTiger, MongoDB utilizes both the WiredTiger internal cache and the filesystem cache.

Via the filesystem cache, MongoDB automatically uses all free memory that is not used by the WiredTiger cache or by other processes.

The storage.wiredTiger.engineConfig.cacheSizeGB limits the size of the WiredTiger internal cache. The operating system will use the available free memory for filesystem cache, which allows the compressed MongoDB data files to stay in memory. In addition, the operating system will use any free RAM to buffer file system blocks and file system cache.

To accommodate the additional consumers of RAM, you may have to decrease WiredTiger internal cache size.

For further your ref WiredTiger Storage Engine and Configuration File Options


Management Data Warehouse SQL Server data collection reports

You have to enable Data Collection

  Using SQL Server Management Studio

  In Object Explorer, expand the Management node.
  Right-click Data Collection, and then click Enable Data Collection.

or Using Transact-SQL

USE msdb;
GO
EXEC dbo.sp_syscollector_enable_collector;

FYI, MDW works using SQL Agent jobs, so the Agent has to be started and collector's jobs have to be enabled.


MySQL: Procedure for mysql certification

MySQL certification, exam, course & cost details can all be found on the Oracle Education site here (Indian site linked to):

https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=458&get_params=p_track_id:MYSQLDBA56OCP

The certification process is simple enough to become a MySQL Certified Associate or Professional.  You just need to pass the relevant exams (e.g. for Oracle Certified Professional, MySQL 5.6 Database Administrator, you have to pass 1Z0-883 / MySQL 5.6 Database Administrator).

That's it.

For the Expert certification status things are a bit more complex, but check out the Oracle Education site linked above.

The cost is high if you do the courses and the exams.  If you're confident in your abilities and have lots of experience, you can do the exams without having done the courses, but in my experience, doing the course is a good grounding and they will cover everything that the exam requires.

How beneficial?  Well that depends on the job market, employer's requirements and your other skills.  Obviously a certification separates you from people who don't have one, but that's all you can realistically say.  There will be non-certified people with more experience than you.


OK - I'll try and answer this question, however you should realise that there is no "right" answer - just assessments based on situation and experience.

This is a classic question about multi-tenancy - shared versus isolated. There's a very good article here from Microsoft.

AIUI, MySQL has a limit of 32,000 databases per server - but if you're running into that limit, you should obtain more server instances long before that happens. Anyway, there are file limits associated with various OS configurations - but I don't have a clue about Amazon's limitations.

I'm more of a believer in isolated schemas with a caveat about reference tables and the like - use a qualified schema.table.name for, say, cities or similar.

Just a note, when I refer to a given post in my answer, please take the time to look at the entire thread - as I said, there are valid differences of opinion on this matter.

From my answer here, "You may have some data duplication issues with a multi-tenant approach, but that's a matter for you to assess in the light of your application/usage/servers/budget..."

One of the better answers that I have seen is here - however it requires monitoring, expertise and effort - you'll get nothing for nothing (except here where the advice is free! :-) )

My own recommendation is that you change your partitioning/sharding scheme to make it by customer (and possibly by year if that suits you), but I would NOT have a database per monitored system - that strikes me as overkill/unproductive/not_necessary/confusing. That way, your number of schemas is either your number of clients (or no. clients * years).

My understanding is that these data are a logging kind of scenario, so therefore, previous years would not be modified very frequently.

Under my scenario, any database modifications would only occur for no._of_clients (or no._of_clients * years) and I believe that it may present a simplification of your application and your job.

Again, I emphasise that there are alternative valid viewpoints that could be argued - this is my 0.02c based on my own experience and knowledge. Another good thread is here.

For GIS tasks, I would definitely prefer PostgreSQL, but it could be that MySQL 5.7 (and greater) will be a big improvement with the adoption of the Boost.Geometry library for GIS functions.


The typo in ORDER BY aside, you need descending sort order to get the ingredient with the maximum amount first.

SELECT Iname, Amount
FROM   Composition
WHERE  Name = 'Pancake'
ORDER  BY Amount DESC
FETCH  FIRST 1 ROWS ONLY;

Assuming Amount is defined NOT NULL.

FETCH FIRST 1 ROWS ONLY is the syntax defined by the SQL standard and supported by several RDBMS, but not all. Depending on your undisclosed RDBMS you may need a different syntax variant. Here is a pretty complete list:

https://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause


Filtering special characters in to_tsquery

You may want to just leave it as users enter it, since you can safely use strings processed twice via quote_literal. In the to_tsquery documentation, the example for phrases within ts queries is:

SELECT to_tsquery('''supernovae stars'' & !crab');

Adding some junk to the phrase breaks it, unless you pass it twice via quote_literal, which makes any string safe to use:

SELECT to_tsquery(quote_literal(quote_literal('supernovae '':|stars')) || ' & !crab');


The operating system returned error 665(The requested operation could not be completed due to a file system limitation) on SQL Server 2014

DBCC creates a database snapshot, and database snapshots are implemented using NTFS sparse files. Error 665 reported for DBCC has been reported before, and blogged about by the CSS team, read:

SQL Server reports operating system error 1450 or 1452 or 665 (retries)
Sparse File Errors: 1450 or 665 due to file fragmentation: Fixes and Workarounds

The articles linked show the problem details, and the applicable fix (depends on your OS version and configuration).


PostgreSQL - trying to speed up query responses from view that has join and group by

where id <> 'ALL'

is not sargable. Try to rewrite with id IN (...) or id > 'ALL'
Create an index like this one :
CREATE INDEX X ON precipitaion (id, "time") INCLUDE (country, prec)


The issue might be happening due to innodb_max_dirty_pages_pct and innodb_log_buffer_size as before shutdown Innodb needs to flush all the dirty buffers. It depends on the buffer pool size that how much dirty buffers are there.

You can check the link https://www.percona.com/blog/2010/09/02/how-long-innodb-shutdown-may-take for more detail.

In order to reduce the time, you can tune some mysql settings as mentioned in the blog https://www.percona.com/blog/2009/04/15/how-to-decrease-innodb-shutdown-times/


Version vectors are a way of each node in a cluster communicating its local version number to all other nodes in a cluster. They are also known as vector clocks.
In essence, when a node A passes a message to another node B, A includes in that message what it knows about the version number of all nodes in the cluster. Because it contains values for all other nodes it is an array, or vector, of version numbers. Node B uses this information to update what it knows about the version numbers across the cluster. It can use this information to work out a global ordering of events from across the whole cluster.
In turn node B includes this updated information in messages it sends to other nodes, including to node A.
Search for vector clock. There are a great many explanations, both academic and practical.


How to control the order of servers listed in the SSMS connection dialog?

Use Registered Servers or set up a Central Management Server (CMS). I recommend the latter if you can take the (small) time to set up the infrastructure because it will allow everyone to see the same list of servers, instead of just you (Registered Servers are stored in the registry). The only "drawback" with a CMS is that you have to use Windows Authentication because passwords aren't persisted to disk.

Both these features allow you to maintain an ordered/grouped list of servers, and manage servers as groups. This is very powerful because Policy-Based Management is tightly integrated with the GUI.

This works with all server versions -- I run SSMS 2012 and manage 2005-2012 servers. A CMS server can be set up on an Express edition, so licencing isn't a concern; however, I do recommend taking system database backups so the server list is recoverable.


problem transforming table to First normal form

It is better to create a separate table for agent_status. Now it seems easy to add agent_status column in agent table itself. But with my little experience I can tell you, business requirements may change in future, sometime too often. Our data model must be developed in a way it should accommodate all the future requirements and changes without much alteration on the underlying model.

In some cases, business will required to track history of agent_status over a period of time (@Ziggy) and, there are possibilities, where you are required to add new status values, also you may required to change status name in future in order to accommodate your business requirement.

If you are sure that, your business requirement will not change in future, you can add agent_status column in agent table itself, or else create a separate entity named agent_status.

I hope this answer will give you some idea, Thank you.


Why doesn't Oracle expdp create the dump file?

It was an issue with the TEMP tablespace, which was stored in a disk partition with no more available space.

Following CaM's suggestion (thanks!), I checked that my_user doesn't have the EXP_FULL_DATABASE role, so I launched again the expdp command but without the schemas parameter. For some reason, doing it this way Oracle finally took pity of me, gave up its shenanigans and decided to show the error:

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS while calling
           DBMS_LOB.CREATETEMPORARY []
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

After "moving" the temp datafile to another partition and having a nice fight with Unix to release the disk space, the expdp finally works again.


Multiple BindIp in MongoDB 3.4.1 on Debian Jessie

Remove []. so solution is: bindIp: 127.0.0.1,192.168.20.1 and remember, no space characters anywhere in that string.


Unable to revoke permissions for 'NT AUTHORITY\SYSTEM' on SQL Server 2016

This should revoke the permissions

REVOKE CONTROL SERVER TO [NT AUTHORITY\SYSTEM]
REVOKE SHUTDOWN TO [NT AUTHORITY\SYSTEM]

Tests

use [master]
GO
GRANT CONTROL SERVER TO [NT AUTHORITY\SYSTEM]
GO
use [master]
GO
GRANT SHUTDOWN TO [NT AUTHORITY\SYSTEM]
GO

EXECUTE AS LOGIN = 'NT AUTHORITY\SYSTEM'
SELECT * FROM fn_my_permissions(NULL,NULL)
REVERT

Result

... (34 Results)

REVOKE CONTROL SERVER TO [NT AUTHORITY\SYSTEM]
REVOKE SHUTDOWN TO [NT AUTHORITY\SYSTEM]

EXECUTE AS LOGIN = 'NT AUTHORITY\SYSTEM'
SELECT * FROM fn_my_permissions(NULL,NULL)
REVERT

Result

Extra info

If you are ever unsure about the syntax, an easy one (in my opinion), is selecting what you need inside of the GUI and then scripting it out using the 'script' button.

For example in this answer, I added the grants for the CONTROL SERVER and SHUTDOWN to the login NT AUTHORITY\SYSTEM , Scripted it out, and then removed them in the GUI and scripted them out again to get the revoke statements.
I am against executing stuff through the GUI though, since it is easy to overlook things, and it has proven to be buggy.


Deny view definition at DB level and symmetric keys

From BOL:

  any permission denial overrides all grants

The implication is that when you DENY permissions to an object those permissions override any GRANTed permissions applicable to that object.

Having said that, you don't need to explicitly deny "view definition" permissions on procedures and other code.  If you don't grant VIEW DEFINITION the definitions are not visible, even if the code can be executed.  Tables work slightly differently; you'll need to deny view definition to any tables the role has SELECT rights on.

You can test this using the code below.

Create a test database:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
BEGIN
    DROP DATABASE TestPerms_20160921;
END;
GO
CREATE DATABASE TestPerms_20160921;
GO
USE TestPerms_20160921;
GO

Create a table, and a stored proc:

IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test;
CREATE TABLE dbo.Test
(
    TestID INT NOT NULL
);
GO
IF OBJECT_ID('dbo.TestProc') IS NOT NULL
DROP PROCEDURE dbo.TestProc;
GO
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SELECT 1;
END
GO

Create a test user, and a test role:

IF EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'TestU'
    )
BEGIN
    DROP USER TestU;
END
CREATE USER TestU WITHOUT LOGIN;

IF EXISTS (
    SELECT *
    FROM sys.database_principals dp
    WHERE dp.name = 'dbtestrole'
    )
BEGIN
    DROP ROLE dbtestrole;
END
CREATE ROLE dbtestrole;

ALTER ROLE dbtestrole ADD MEMBER TestU;

Grant SELECT and EXECUTE to the test objects:

GRANT SELECT ON dbo.Test TO dbtestrole;
GRANT EXECUTE ON dbo.TestProc TO dbtestrole;

Impersonate the user so we can see the effect of the GRANT statements above:

EXECUTE AS USER = 'TestU';

/* column names and types **are** visible */
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* access to view the definition is denied */
EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL;

/* we can run the proc! */
EXEC dbo.TestProc;

REVERT;

DENY VIEW DEFINITION on the table, and test again:

DENY VIEW DEFINITION ON dbo.Test TO dbtestrole;

EXECUTE AS USER = 'TestU';

/* no rows returned as we can no longer view the table definition */
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* we can still run the proc */
EXEC dbo.TestProc;

/* we can still see the content of the table */
SELECT *
FROM dbo.Test;

REVERT;

Cleanup:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
DROP DATABASE TestPerms_20160921;


As per MSDN BOL Here In general, DAC tools are capable of reading DACPAC files generated by DAC tools from previous SQL Server versions, and can also deploy DACPAC’s to previous versions of SQL Server. However, DAC tools from earlier versions cannot read DACPAC files generated by DAC tools from later versions. Specifically:

1. DAC operations were introduced in SQL Server 2008 R2. In addition to
    SQL Server 2008 R2 databases, the tools support generation of DACPAC
    files from SQL Server 2008, SQL Server 2005 and SQL Server 2000
    databases.

2. In addition to SQL 2016 databases, the tools shipped with SQL Server
        2016 can read DACPAC files generated by DAC tools shipped with SQL
        Server 2008 R2 or SQL Server 2012. This includes databases from SQL
        Server 2014, 2012, 2008 R2, 2008, and 2005, but not SQL Server 2000.

3. DAC tools from SQL Server 2008 R2 cannot read DACPAC files generated
        by tools from SQL Server 2012 or SQL Server 2017.

Hope it will help out to you.


You can convert the join to a semi-join, using either IN or EXISTS:

SELECT employees.*, companies.*
FROM companies
  LEFT JOIN employees
    ON companies.id = employees.id_company
WHERE EXISTS
      ( SELECT 1
        FROM company_technologies
        WHERE companies.id = company_technologies.id_company
          AND company_technologies.id_technology IN (1,2,3)
      ) ;

Another way would be to use GROUP BY or DISTINCT in a subquery and then join:

SELECT e.*, c.*
FROM companies AS c
  LEFT JOIN employees AS e
    ON c.id = e.id_company
  JOIN
      ( SELECT id_company
        FROM company_technologies
        WHERE id_technology IN (1,2,3)
        GROUP BY id_company
      ) AS ct
    ON c.id = ct.id_company ;

Once you have one or more queries working correctly, you can start worrying about efficiency. I would add 2 composite indexes on the junction table, one on (id_technology,id_company) and one on (id_company,id_technology) (and remove the single indexes.)

Then test all queries with various parameters and table sizes and check which indexes are used and what are the execution plans.


Restrictions on creating new SQL tables?

If this is a production database, you should remove developer's access rights to create tables. This can be achieved via the REVOKE command. You may have further work to do if the developers are adminstrators.

Developers should then create new tables in a developement database and these are migrated to production via the DBA team following a code review and test cycle.

Alternatively, you could create a DDL trigger that could stop users creating tables via a ROLLBACK and create a warning message for the developers as you suggest.

Finally, to clean up test tables, you will need some method to identify them (table name pattern, owner etc). You can then automate a script to drop such tables.


How do I reindex my SQL Server Express database to free up space when it's already full?

Create a new database.

Create the skinnier version of your table.

Create supporting objects (procedures, views, users, other tables, what have you).

Migrate the data (or some subset of the rows) from the original database to the skinnier version of the table in the new database, using methods like:

insert newdb.dbo.table(...) select cols from olddb.dbo.table;
SSIS
BCP
import/export wizard

Stay on top of your new database as it grows. Update your connection strings to point at the new database (or drop the old one and rename the new one to the old name).

Technical problems with specific software or platforms tend to have simpler solutions than throwing hands in the air and switching do different software / platform...


Create a materialized view on commit with PIVOT function

To use on commit refreshes, an MV must also be fast refreshable.

If you're struggling to figure out why you can't set certain refresh properties for an MV, it's best to:

Create it refresh complete on demand
Run dbms_mview.explain_mview on it to see what the issues are

Create the mv_capabilities_table before doing this to store the output from the explain procedure.

Do this and you'll find:

create table t (
  c1 int
);

create materialized view log on t
  with rowid, sequence ( c1 ) including new values;

insert into t values ( 1 );
insert into t values ( 2 );
commit;

create materialized view mv
refresh complete on demand
as
  select one, two
  from   t
  pivot (
    count(*) for c1 in ( 1 one, 2 two )
  );

create table mv_capabilities_table (
  statement_id      varchar(30) ,
  mvowner           varchar(30) ,
  mvname            varchar(30) ,
  capability_name   varchar(30) ,
  possible          character(1) ,
  related_text      varchar(2000) ,
  related_num       number ,
  msgno             integer ,
  msgtxt            varchar(2000) ,
  seq               number
) ;

exec dbms_mview.explain_mview('mv');

select capability_name, possible, msgtxt
from   mv_capabilities_table
where  capability_name like 'REFRESH%' ;

CAPABILITY_NAME                 POSSIBLE   MSGTXT
REFRESH_COMPLETE                Y          <null>
REFRESH_FAST                    N          <null>
REFRESH_FAST_AFTER_INSERT       N          SQL PIVOT or UNPIVOT is present
REFRESH_FAST_AFTER_INSERT       N          subquery or named view in FROM list even after view merging
REFRESH_FAST_AFTER_INSERT       N          inline view or subquery in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT       N          view or subquery in from list
REFRESH_FAST_AFTER_ONETAB_DML   N          see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML      N          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT                N          PCT FAST REFRESH is not possible if query contains an inline view

As the REFRESH_FAST_AFTER_INSERT line says, SQL PIVOT or UNPIVOT is present. So you can't use on commit.

You can get around this by using old-style manual pivots:

drop materialized view mv ;
create materialized view mv
refresh complete on demand
as
  select count ( case when c1 = 1 then 1 end ) one,
         count ( case when c1 = 1 then 1 end ) two
  from   t;

truncate table MV_CAPABILITIES_TABLE;
exec dbms_mview.explain_mview('mv');

select capability_name, possible, msgtxt
from   mv_capabilities_table
where  capability_name like 'REFRESH%' ;

CAPABILITY_NAME                 POSSIBLE   MSGTXT
REFRESH_COMPLETE                Y          <null>
REFRESH_FAST                    Y          <null>
REFRESH_FAST_AFTER_INSERT       Y          <null>
REFRESH_FAST_AFTER_ONETAB_DML   N          CASE expressions present in materialized view
REFRESH_FAST_AFTER_ANY_DML      N          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT                N          PCT is not possible on any of the detail tables in the materialized view

Some fast refreshes are now possible. So you can do:

alter materialized view mv
  refresh fast on commit;

select refresh_method, refresh_mode
from   user_mviews
where  mview_name = 'MV';

REFRESH_METHOD   REFRESH_MODE
FAST             COMMIT


Separate or Combine 2 Tables with Similar Attributes?

I'd say "Customer" and "Employee" are two roles a person can play in your system. Thus, I'd add concept of "Role" to your design. Then you can store customer related attributes in one table, and employee related attributes in another. You will have something like

User (user_id, name, ...)
Role (role_id, description)
UserRole (user_role_id, user_id,role_id, date_from, date_thru)
-- note, dates are quite important(at least date_from should be not null)
CustomerRoleAttribute(user_role_id, NewsUpdateSubscription)
EmployeeRoleAttribute(user_role_id, salary)

Also, to be absolutely correct, Customer and Employee roles require second part - for instance, I'm a customer of AAA company, and an employee of BBB, so my role is defined by relationship of certain type with other user (which is organization in case of "Employment" relationship).

You can find out more about this approach in "The Data Model Resource Book" by Len Silverston


When do you know that you've made a design flaw in a SQL/relational database?

Modeling and performance are related but not quite the same thing.  Performance and scalability will have a lot to do with what DBMS you are actually using.

Queries which seem to be slow and difficult to execute against MySQL might fly when run against Postgres for instance.  This has everything to do with how intelligent the query planner is.  Older versions of MySQL for instance only seem to know how to perform one kind of join:  Nested-Loop.  This will be fast for small amounts of data and simple queries, but can degrade quickly for complex queries.

You may not have a design flaw.  You may have simply hit the performance wall with your current DBMS.  Which database are you using?

In my opinion, an error in the model exists when you have insert, update, or delete anomalies in the data.  Imagine you store a customer name on each invoice and the customer changes their name.  To perform this update should not require updating the history of every order the customer has ever made.  If it does, you've got a problem with your model.


Mysql database .frm and .ibd file read to understand

Jeremy Cole in a series of posts explains InnoDB internals and on disk format in particular.
I'd start from https://blog.jcole.us/2013/01/02/on-learning-innodb-a-journey-to-the-core/ . Get his tool innodb_ruby, it's very helpful in the learning.


I made it work.

The problem is how the bootstrap script tries to start the server.

I tried on different machines and all had the same symptoms when trying to bootstrap the cluster using the script galera_new_cluster.

It has something to do with systemd, I guess.

bootstrap the new cluster "by hand" using

mysqld -umysql --wsrep-new-cluster

did the trick on all machines I tried.

And while starting the other nodes, it is also a good idea not use the "systemd way"

systemd mechanism has a timeout, and if your sync with the other nodes takes a bit longer, systemd spits out an error, but mysql keeps running and does what it is supposed to do.

It's not a bad thing, but it's kind of confusing.

So for now I recommend, starting the other nodes also "by hand"

mysqld -umysql

if all your nodes are synced, open another terminal and

mysqladmin -u<YourPrivilegedUser> -p shutdown

after that you can start and stop your nodes normally, using the "systemd way"

hope it helps.

Ju


The first query is the same as an INNER JOIN, the second query is not valid because it is using ON without a JOIN statement.

From a similar question:

  The comma operator is equivalent to an [INNER] JOIN operator. The
  comma is the older style join operator. The JOIN keyword was added
  later, and is favored because it also allows for OUTER join operations

Source

Testing db<>fiddle


How to avoid implicit type casts in PostgreSQL?

Is this an intentional feature?

Yes, implicit type conversion is an intentional feature. There are pros and cons either way but this is how postgres is designed to work:

  In many cases a user does not need to understand the details of the type conversion mechanism. However, implicit conversions done by PostgreSQL can affect the results of a query. When necessary, these results can be tailored by using explicit type conversion

  Doesn't this violate the assumption that RDBMSs should be typesafe?

No, everything is still typesafe and "SQL is a strongly typed language". Implicit conversions don't change that.

  Is there anything I can do to avoid 'casual' type casts?

Short of messing around with the system catalog, which is usually a very bad idea (and impossible in some cases), there isn't a lot you can do to avoid implicit casting entirely. Even if you include explicit casts everywhere implicit casts might still occur by mistake:

create table t(foo text);
insert into t(foo) values(11::integer);

select * from t;

| foo |
| :-- |
| 11  |

dbfiddle here


The error itself explains what the problem is. There is a .frm file on the filesystem for the table. Go to your MySQL data directory (it's usually /var/lib/mysql on a linux box) and take a backup of the .frm file and remove it from that location. You should then be able to create the table on your slave.


Permissions To Update Legacy DTS Packages

The only way we were able to find a way around this was to grant the user SysAdmin rights. Even with CONTROL at the server level, it is not available.


Sadly mirroring of the distributor isn't supported.  I'm not sure if it would work as the server names wouldn't be correct when the agents would log in, not to mention you'd have to manually setup all the agents on the backup box.  Currently your only HA option for the distributor is Windows Clustering.


Unable to read master log position while setting up MySQL slave

You need to give privileges to replicate the Master.

Below is the example.

in Master DB to give privileges.

CREATE USER 'replication_User'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_User'@'%';
flush privileges;

Run below command on slave db to see master status.

SHOW MASTER STATUS\G;

Run below command on Master to see slave status.

SHOW SLAVE STATUS\G;


TempDB not selectable for DBCC maintenance plan

Tempdb won't be available for you in the Mantainance Plan database list box, but you sure can run it as a script:

DBCC CHECKDB(N'tempdb')  WITH NO_INFOMSGS

According to Mr. Brent Ozar, you SHOULD check tempdb for integrity, and if Brent is saying that, that is good enough for me.

regards


Master-slave replication doesn't work on PostgreSQL 9.6

You told it look for the data in '/srv/postgresql'.  But then you told pg_basebackup to clone the data into '/var/lib/postgresql/9.6/main', not '/srv/postgresql'.

When you started postgresql up again, you (apparently) started up the database instance whose data is still in '/srv/postgresql', which is not the clone you had just made.

Also, turning "archive_mode" on but then providing it with a dummy "archive_command" is not helpful and can only cause confusion and mis-reported errors.  And "pg_basebackup" has a -R option for writing out the recovery.conf itself, it would probably be best to use that.


MySQL indexing issue when trying to search for a part of a string/word

If you can get rid of the leading wildcard (%), do so; then we can discuss better indexes.

Meanwhile, use INDEX(customer_id, date4); it has some chance of speeding up the query.

What version are you running?

Please provide SHOW CREATE TABLE if you want to discuss this further.


potgresql foreign key index unused for some columns on one specific server

It seems that the table publication needed a VACUUM FULL. After freeing up enough space for that and running it, new indices seem to work now.


Counting number of occurences in a time period

Edited based on your sample data.

For an item number to have a total quantity of two or more in a given date range:

 Select MachineRefNo, ItemNumber, Sum(Quantity) NumberOfItems
       From Table
       Where OrderDate Between DateRangeStart And DateRangeEnd
       Group By MachineRefNo, ItemNumber
       Having Sum(Quantity) > 1

For an item number to have two or more records in a given data range:

Select MachineRefNo, ItemNumber, Count(*) NumberOfOccurances
   From Table
   Where OrderDate Between DateRangeStart And DateRangeEnd
   Group By MachineRefNo, ItemNumber
   Having Count(*) > 1


Restore of Replicated Database fails with “Cannot drop the table  because it is being used for replication.”

Use below query to avoid replication related SP in Transactional Replication.

SELECT name 'ROUTINE_NAME','T' 'ROUTINE_TYPE'
            FROM sys.tables
            WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 1 AND name not like 'MSpeer%'


Create the new table
Drop the old table and rename the new one in a transaction:

BEGIN TRANSACTION;
  DROP TABLE dbo.OldTable;
  EXEC sys.sp_rename N'dbo.NewTable', N'OldTable', N'OBJECT';
COMMIT TRANSACTION;

If you have concerns about how long the drop itself will take, you can do it this way instead:

BEGIN TRANSACTION;
  EXEC sys.sp_rename N'dbo.OldTable', N'garbage',  N'OBJECT';
  EXEC sys.sp_rename N'dbo.NewTable', N'OldTable', N'OBJECT';
COMMIT TRANSACTION;
DROP TABLE dbo.garbage;

(The drop doesn't technically have to be a part of the transaction, and shouldn't cause any blocking in any case, so now how long the drop actually takes is largely irrelevant, because there is no chance for it to block anyone.)

If you have indexes, foreign keys etc. you'll also have to deal with those, but the above is about the fastest you're going to get because it's mostly metadata (at worst the DROP will be blocked by existing activity).

Also see Schema Switch-A-Roo part 1 and part 2 if your situation is more complex.


Database with FILESTREAM enabled gives Error 3056 during backup

You have manually create a file in a FS container. Stop doing so.

A FS container is defined here:

  FILESTREAM data must be stored in FILESTREAM filegroups. A FILESTREAM
  filegroup is a special filegroup that contains file system directories
  instead of the files themselves. These file system directories are
  called data containers. Data containers are the interface between
  Database Engine storage and file system storage.

As you see, is nothing but a file system folder. Do not delete or create files in these folder, is simple as that. all interaction must occur via the FILESTREAM API.


Row level restore with Oracle Recovery Manager (RMAN)

The answer to your question is no, however....

It sounds like a flashback query is what you need.  Query the data as of a time when it existed and when it returns the correct data, insert it into the current table.  This solution does require space in the UNDO tablespace sufficient to meet your UNDO_RETENTION requirements.  It also doesn't use RMAN, but is significantly simpler than importing the entire schema.

INSERT INTO T1 (
   SELECT * FROM T1 AS OF TIMESTAMP sysdate-1
   WHERE MyDateColumn = to_date('05/25/2011','MM/DD/YYYY')
);


Scheduling queries to run when sql server is not busy

An Idle condition is defined at the SQL Agent properties, Advanced

https://msdn.microsoft.com/en-us/library/ms189065.aspx

The select it in the schedule type for the job as Rafael said in his comment


What are the differences between "Stored Procedures" and "Stored Functions"?

Officially, PostgreSQL only has "functions".  Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning.  Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc.  That's a holdover from PostQUEL.  Any features that some people (possibly with experience in different database systems) might associate with procedures, such as their relevance to preventing SQL injections or the use of output parameters, also apply to functions as they exist in PostgreSQL.

Now, when people in the PostgreSQL community talk about "stored procedures" or "real stored procedures", however, they often mean a hypothetical feature of a function-like object that can start and stop transactions in its body, something that current functions cannot do.  The use of the term "stored procedure" in this context appears to be by analogy to other database products.  See this mailing list thread for a vague idea.

In practice, however, this distinction of function versus procedure in terms of their transaction-controlling capabilities is not universally accepted, and certainly many programmers without database bias will take a Pascal-like interpretation of a procedure as a function without return value.  (The SQL standard appears to take a middle ground, in that a procedure by default has a different transaction behavior than a function, but this can be adjusted per object.)  So in any case, and especially when looking at questions on Stack Exchange with a very mixed audience, you should avoid assuming too much and use clearer terms or define the properties that you expect.


I want to count all the rows for todays / yesterdays date based on a DATETIME - mysql+php

You need to access column by index :

if ( $nt = mysql_fetch_array($q)){
echo $nt[0];
}

(Note if instead of while - it's not required, but your query returns at most 1 row, so no need for while).
If you want to access column by name, change the query to use aliases:

$q=mysql_query(" SELECT COUNT(*) AS total_number FROM menjava
WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)",$link2);
// now you can
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
}

Also, while debugging it makes sense to output errors if any :

$q=mysql_query(....) or die(mysql_error());


SQL Server Service Account - Things to test post change (from Local System to Network Service)

In Short: Everything and Anything. And Nothing.

But seriously: A few thoughts about changing the SQL Server service account:

ALWAYS use the SQL Server configuration manager. This tool is smart. It knows what permissions need to be assigned to the service account. And generally does it. If you change it from windows services control panel - GOOD LUCK.
The service account is how SQL Server interacts locally and to the network. So moving from local system - you shouldn't care about breaking networked connections - since they wouldn't work anyway. But you would generally want to make sure things like "Will SQL Server start?" happen (that means it can at least read the drives that master/model/msdb/tempdb and the error logs are on). And that verifies the registry.
If you assigned Perform Volume Maintenance Tasks to a SQL Server service account before - that will likely need to be assigned again.
Generally this is a safe move. As long as you do it through the SQL Server configuration manager. Often we'll see folks do this through windows services control panel, something breaks, so they quickly grant their service account local admin rights - and "poof!" everything works.

In your particular case though - you are moving from one special privileged account to another. As you have seen - you were safe and things worked. I would also take this time to question the choice of the service account.

Are you on a domain? Why not use a domain user account per the best practices? The local accounts with special privileges are not the most secure or reliable service account choices. This article from Microsoft explains more there.


You'd probably be better off making this a bit field with NOT NULL or otherwise you also need to validate that only the strings 'Y' and 'N' were entered.

With the bit column arrangement the check constraint would be

CHECK (1 IN (Completion, Loading))

Otherwise with char(1) the check constraint could be

CHECK ('Y' IN (Completion, Loading))

but you'd need two additional column level constraints ensuring Completion IN ('N','Y') and similar for Loading.


An issue I found on a similar problem was that the ‘first’ grant on the object had to be done ‘with grant option’.

Eg for the issue above, this would therefore mean:

GRANT SELECT ON <underlying table owner>.<table_name> TO CST0 WITH GRANT OPTION;

Then running

GRANT SELECT ON <schema>.<view_name> TO CST0;

should work.

Note this this ‘foundation’ grant will need to be repeated for each of the underlying table owner>. which forms part of the view definition.


Opening an Oracle database with a corrupted redo log

What you are looking for is to clear the redo log.

ALTER DATABASE CLEAR LOGFILE GROUP #;


Why Do Replication Deletes Require sysadmin Access

So are you telling me that every user of my database has to have sysadmin access if they are going to be doing deletes?

No, as per BOL's documentation on sp_trace_generateevent:

  User must have ALTER TRACE permission.


Value of prepared statement is listed as error in query string

You likely will need to enclose the parameters in quotation marks if they are strings. Try this:

$query = "INSERT INTO berichten_devices (device_UUID, article_id) VALUES (\'%s\', \'%s\')";


Create table with constraint based on another column

I would do this using a trigger on your rental table. Trigger before insert - set the return_date column value to be the rent_date value in the data being saved, plus look up the value of movie.rent_period based on the movie_id value being saved.


What is the overhead for an array in Postgres?

You can check that using pg_column_size():

select pg_column_size(1::integer) as int_size,
       pg_column_size(array[1]::integer[]) as array_size

returns:

int_size | array_size
---------+-----------
       4 |         28

So the overhead is substantial for just a single value.


Do data checksums apply to large objects?

If enabled, checksums will be calculated for each data page.
you can read more about check some  in the following site: http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-data-checksums/

or the following website also tell about checksum http://www.postgresql.org/docs/9.3/static/app-initdb.html#APP-INITDB-DATA-CHECKSUMS
Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. Enabling checksums may incur a noticeable performance penalty. This option can only be set during initialization, and cannot be changed later. If set, checksums are calculated for all objects, in all databases.


How I can monitor SQL Server 2012 databases to determine the security privileges that are called by different processes

You can use SQL Activity Monitor. This will show the current queries executing in your SQL Server instance with below details

SPID
Login
Database Name
Task State
Command
Application
Wait time
Host Name and many more under PROCESSES tab

Also you can have a look on

Resource waits
Data File I/O
Recent Expensive Queries


MariaDB Encryption - unknown variable 'plugin-load-add=file_key_management.dll'

mariadb from version 10.3 use other command in config:
[mariadb]
plugin_load_add = file_key_management
like show in  here


How to find the most common phrases in a varchar field

So I ended up solving this with a C# application. I thought I would share my code in case anyone else needed something similar. It's using Entity Framework to do the data access.

        using (var ticketsContext = new CMS.Data.Tickets.TicketsContext("name=CMS"))
        {
            using (var analysisContext = new TicketTitleAnalysis.Analysis())
            {
                int lastTicketID = analysisContext.Phrases.Max(p => (int?)p.LastTicketID).GetValueOrDefault();

                var tickets = (from t in ticketsContext.Tickets where t.TicketID > lastTicketID orderby t.TicketID select new { t.TicketID, t.Title });

                foreach (var ticket in tickets)
                {
                    var titleWords = ticket.Title.ToLower().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).Where(w => char.IsLetter(w[0])).ToArray();

                    for (int startingWord = 0; startingWord < titleWords.Length; startingWord++)
                    {
                        for (int numberOfWords = 1; numberOfWords <= titleWords.Length - startingWord; numberOfWords++)
                        {
                            var words = string.Join(" ", titleWords.Skip(startingWord).Take(numberOfWords));

                            if (words.Length > 5)
                            {
                                var phrase = analysisContext.Phrases.FirstOrDefault(p => p.Words == words);

                                if (phrase == null)
                                {
                                    phrase = new Phrase();
                                    phrase.Words = words;

                                    analysisContext.Phrases.Add(phrase);
                                }

                                phrase.LastTicketID = ticket.TicketID;
                                phrase.Count++;
                            }
                        }
                    }

                    analysisContext.SaveChanges();
                }
            }
        }


How to check if specific number of rows exists without count(*)

You can add this in the EXISTS subquery:
LIMIT 1 OFFSET (N-1)

In other words, the subqery first tries to finds (N-1) rows, skip them and return the next (Nth) row. So, it will return something (one row) if and only if there are N+ rows.
You need appropriate indexes, i.e. an index on B(id) in this case, to make it as fast as possible, obviously.


What is the difference between threads connected and threads running

Threads connected means the total number of client processes (threads) connected to the database server. This includes the count for threads running.

Thread running means the total number of client processes (threads) currently executing on the database server. The server is holding these connections while the client is waiting for a reply. These thread may be consuming IO/CPU, while others may do nothing while waiting for a table lock to be released. When the database is finished executing the thread, the client gets a reply, and the thread is changed from status "running" to "connected".


However, one set has a listener created and the other doesn't.

Well that doesn't help you application stay available! You can absolutely have an availability group without a listener, but the useful of such setups are low.

  (From Comments) The listener is the correct approach, but it's real function is to be able to direct intent read only connections to the proper secondary.

That's not quite correct. The listener always points to the primary replica and the main purpose of it is to facilitate transparent failover so that the connection strings do not need to change. One additional use is in read only routing as you've said but the primary use is actually for transparent failover.

  (From Comments) ... but the Cluster Name provides the same seamless redirection to the primary node as the listener does.

The CNO has no idea about what an availability group is, which should be primary, etc. The CNO absolutely DOES NOT always point to the primary replica. You can connect using it only when the core cluster resources (which holds the CNO) happens to be owned by the same node as the AG primary but if it isn't - and the CNO doesn't follow the AG around - then it'll fail. Here's an example:

  The one that doesn't have a listener is working fine. The one with the listener has issues at failover.

The one without the listener might be working fine... does it still work fine on a failover? Are they using some other DNS alias to "act" like the listener may.

The one with the listener, what does their connection string look like? If it isn't using the listener name in the connection string then of course it isn't going to work... they need to point it to the right place, that's why the listener exists!

The connection to the listener may also fail if there are multiple subnets, older client libraries are used, or certain keywords aren't in the connection string. If there are multiple subnets, the client driver should be something that supports the MultiSubnetFailover keyword and this should be set to TRUE.

  It doesn't use the listener to connect, instead it uses the cluster name.

Well, that's 50% of the problem.

  The GIS application accessing this server cannot connect to it after a failover.

If they update their connection string to use the listener and set MultiSubnetFailover = True then I'm betting it'll work... assuming the client library used to connect supports it.

  I tried to have them use the listener but they said they couldn't even connect to that. I assume the listener hasn't been set up correctly.

If SQL Server created the listener for you, I highly doubt it is setup incorrectly... I won't rule out edge cases though.

  Can I just remove the listener since it is not used anyways?

I would do the opposite. Have the GIS team change their connection string to the listener and set multisubnetfailover. It should, then, work... again with previously said assumptions.

  But everything I read says that you need to use a listener, yet the servers without a listener are working fine. I'm so confused.

Yes, you do want to use the listener. The fact that it is working is either because there is some document that says "always have this on node3" or you've been getting lucky that it stays working. They might have used the replica name directly in the connection string also... that would allow it to connect. Depending on the settings for the secondary role it may or may not still connect and work properly... just depends on said settings.


Creating optimized table without duplicates in MySQL

You should take a look at Second Normal Form (2NF), one of the recommended forms designed for optimized database data.

In 2NF, the users should be in one table, and references to the users should be in other tables, as appropriate. Since you're storing data for various dates with values, the table should definitely have the form of user, date, and value.

If the date is significant and has other data not related to that value, you might even end up with three tables, not just two, in order to satisfy 2NF, and this is perfectly normal.

If you're still not convinced, imagine you're now up to 100 days, and you need to figure out the total of the values. Using your scheme, you end up with a query that looks like this:

select field1+field2+field3+field4...field100
from mytable where user = 5

Conversely, to find the total for 100 days using 2NF, you simply do this:

select sum(value) from mytable where user = 5;

This is easier to maintain in the long run, even if the database allowed for unlimited columns, which it doesn't.

As for the Primary Key (PK), it's obvious: you should use a composite PK, consisting of the user's ID and the date value:

create table mytable(
    `user` int,
    `date` date,
    `value` int,
    primary key(`user`,`date`),
    index(`date`)
)

Your PK should ideally use the order you intend to use more frequently, and then index the other field if you're interested in doing faster queries involving the date field.


Why a dropped index is still there, and does it prevent a table from being altered?

Why the dropped index is still there?

In fact that index is no longer there, the column is the one still there and what was once a Clustered Index is now a Heap.

Did I got the dependency error above because of this still-existing index?

No, because the index no longer exists.

If not, is there something else I should check? I'd like to avoid
dropping the table.

You can View the Dependencies of a Table as suggested by Erik Darling.

As you mentioned in your comments, you found a VIEW using this method and if you check the CREATE VIEW doc, you'll see the option SCHEMABINDING could cause the error you mentioned:

SCHEMABINDING Binds the view to the schema of the underlying table or
tables. When SCHEMABINDING is specified, the base table or tables
cannot be modified in a way that would affect the view definition. The
view definition itself must first be modified or dropped to remove
dependencies on the table that is to be modified. [...]


Postgres bypassing index with IN clause with 100+ values?

I wouldn't know of any hard-wired limit in the query planner. And a quick test proves it wrong - for both variants of the IN construct, the one taking a set and the one taking a list:

db<>fiddle here

See the index scans for 103 elements?

Related:

Optimizing a Postgres query with a large IN

There is a related limit: a VARIADIC function takes a maximum of 100 individual parameters. Beyond that one needs to pass an array ...

Efficiently return two aggregated arrays from a m:n table

The only cases where a temporary table regularly improves performance is when you create useful indexes on the intermediary result before you proceed. Else, temp tables are typically (much) slower.


Problem with Oracle Rollback Segment Session

I suspect you're using multiple sessions and the other session cannot "see" the work performed because it is currently uncommitted.

The default Oracle transaction Isolation Level is Read Committed. This means that a  session will not be able to see the work a given session has done until it has explicitly committed the transaction.

The Oracle Server Concepts Guide explains this in detail.


Loading multiValued fields into Solr via flat file, and possibly value position preservation in those fields

Getting multiValued data into Solr via CSV:

The solr documentation describes a "split" function in UpdateCSV. Essentially, it parses a field value using a second CSV parser. See Solr - UpdateCSV - split. The parameters look like so (adjust field name, separator, and encapsulator as necessary):

f.fieldA.split=true&f.fieldA.separator=%2C&f.fieldA.encapsulator='

Getting multiValued data from separate fields to maintain position:

Since asking this question, I've done some reading about dimensional models. It seems that what I was trying to do is poor design, because it places too many expectations on the application, to much complexity in the warehouse, or both.

When trying to preserve the relationships between two field values on a single record, it's better to store them separately as well as together. Here's a comparison of my former input to the new input:

Former CSV input:

name|licenseState|licenseType
Josh|MA,CA|123,456
Fred|MD,OH|789,123

Transformed CSV input:

name|licenseState|licenseType|licenseStateType
Josh|MA,CA|123,456|MA123,CA456
Fred|MD,OH|789,123|MD789,OH123

This way your application can use the licenseState and licenseType dimension values independently, or it can use the licenseStateType dimension values, all without requiring complicated app or warehouse logic.


Cross Database Queries using IBM Data Studio

For you to execute any SQL statement you have to be connected to some database server; Data Studio does not execute SQL statements. Subsequently, for the three part name (<server>.<schema>.<object>) to work the server you're connected to has to know what the <server> part is.
In the simple case of accessing objects in a database that belongs to the same Db2 for LUW database instance, <server> is the other database name, and no additional setup is required.
However, if the other table is in a database managed by a different instance, or if it belongs to a different DBMS (Db2 for z/OS, Oracle, etc.), you will need to set up a federated data source, whose name you will then use for <server>.


Here is Oracle's answer.

Basically your options are these:

Delete transient files no longer needed for current policies.
Change something stored in the Fast Recovery Area to be stored elsewhere This could be temporarily or permanently.
Increase the db_recovery_file_dest_size to be larger than 7 GB.
Decrease your backup window or redundancy.
Change your archivelog retention policy.
Drop guarenteed restore points.


Disaster Recovery Options for Sql Server

During Hurricane Sandy (natural disaster) and recently when moving our data center (managed failover), I was in your situation.

I have implemented Logshipping and was able to efficiently failover during the above scenarios.

The technique that I used was reverse logshipping (swapping roles between primary and secondary) - which allows you to swap the log backups.

Below steps can be scripted with Powershell or TSQL script (I used sqlcmd and xp_cmdshell)

FAILOVER Process:

Kill all user connections on primary server and take a final log backup (WITH NO RECOVERY) putting the primary  into NO RECOVERY state.

a.This means that no users can now connect to the database and the database is ready for failover.
Ship the transaction log to the secondary server  and restore the log WITH RECOVERY bringing the secondary  up.

b.This means that the users can now connect to the secondary server as the database is successful failed-over.

FAILBACK Process:

Kill all user connections on the primary server (original secondary) and take a final log backup (WITH NO RECOVERY) putting the primary (original primary) into NO RECOVERY state.
Ship the transaction log to the primary server (original secondary) and restore the log WITH RECOVERY bringing the primary (original primary) up.

This will utilize less network bandwidth as it just has to ship small T-log backups.

  I do have a question about how log shipping handles Differential backups. Do I have to stop doing differentials for this to work?

A differential or FULL backup does not break LSN chain. ONLY an adhoc log backup will break the LSN chain.


Cluster migration to new hardware, same OS, but SQL Server 2012 to 2016

You will encounter no problem during the migration because even if the database coming from SQL Server 2012 pass to SQL Server 2016 the Compatibility Level stays the same (110).
After the upgrade use Data Migration Assistant and check if your database is compatible and can be actually migrated to SQL Server 2016 (130).
SQL Server 2016 will hold a database coming from SQL Server 2012 without problem. Changing the Compatibility Level is the trick.


To answer your question, in FULL recovery mode, growth of the log file is absolutely normal, and of course it will grow to either the limited size or the size of the disk unless it is maintained. The rate of growth will be determined by the type of / frequency of / load of transactions hitting your database. The rate of growth, will also determined by your auto-growth settings, i.e. grow by fixed value or percentage etc.

Logs are typically maintained by performing a log backup at suitable intervals. This truncates the data within the log file itself allowing the space to be re-used for new transactions.

What is important to note is that the problems you might be facing due to a large log file (i.e. where the size has gotten out of hand due to lack of suitable maintenance) is not necessarily remedied by a transaction log backup alone. You may need to shrink the file to free up disk space, for example.


Need help in optimizing a slow Postgres Query

Select A.Daily,
       count(distinct da.user) as Users
from
    (
        -- calculate all constants needed once
        select date(d) as Daily,
               date(d) - Interval '30' DAY as DailyStart,
               date(d) - Interval '1' DAY as DailyEnd
        from generate_series(current_date - Interval '10' DAY, current_date - Interval '1' DAY, '1 day') d
    -- GROUP BY by unique value makes no sense
    -- ORDER BY in subquery is ignored
    ) as A
                                 -- avoid fn(table.field)
join public.downloads_activity da on da.created_at >= A.DailyStart
                                 and da.created_at < A.DailyEnd
group by 1
order by 1


Specify a different username for default Postgres user

I've read that calling initdb directly in this environment is
  discouraged because the suite of tools packaged with the software such
  as pg_createcluster

Yes, but pg_createcluster can pass through options to initdb, so it makes no difference on the configurability.

From the manpage:

  SYNOPSIS
        pg_createcluster [options] version name [-- initdb options]

Example:

# pg_createcluster 9.5 test -- --username=daniel
Creating new cluster 9.5/test ...
  config /etc/postgresql/9.5/test
  data   /var/lib/postgresql/9.5/test
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433

# pg_ctlcluster 9.5 test start
Redirecting start request to systemctl

# su daniel -c 'psql --cluster 9.5/test -l'
                               List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
 postgres  | daniel | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | daniel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/daniel        +
           |        |          |             |             | daniel=CTc/daniel
 template1 | daniel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/daniel        +
           |        |          |             |             | daniel=CTc/daniel
(3 rows)

# pg_dropcluster --stop 9.5 test
Redirecting stop request to systemctl


We use to do the following:

1- create a new node

2- Copy data from the node that you want to keep

sudo -u postgres \
    /usr/lib/postgresql/9.4/bin/pg_basebackup \
    -h $HOST_TO_CLONE -p 5432 -D $DATADIR

3- Join the cluster:

sudo -u postgres  /usr/lib/postgresql/9.4/bin/bdr_init_copy \
    -d "host=$HOST_TO_CLONE dbname=$DB_NAME port=5432" \
    '--local-dbname="host=$LOCAL_IP dbname=$DB_NAME port=5432" \
    -n $LOCAL_IP -D $DATADIR

After bdr_init_copy finish, you can delete the init node with the following command:

SELECT bdr.bdr_part_by_node_names(ARRAY(SELECT(bdr.bdr_get_local_node_name()));

This works ok in our installation.

Regards


Relation in 2NF with a transitive dependency?

The important thing about the 2NF is that in each (non trivial) dependency the determinant should not be a proper subset of a key. In the example, the determinant of AB->C is the full key, while the determinant of C->D is C, which is no part of any key. So the schema is obviously in 2NF.


How to rank the product higher and the accessories lower? (MySql fulltext search)

You just need to link back to the Product table and sort by some other criteria on there.  You can ask the Full Text engine to rank the results, but that really just ranks how closely the result matches their search, which is not what you are looking for.  I have a sample here using MS SQL Server.

Since you didn't provide a schema, I put together something that would show what I mean.

DROP TABLE IF EXISTS dbo.Product

CREATE TABLE dbo.Product
    (
    ProductID INT NOT NULL IDENTITY(1,1)
    , ProductName NVARCHAR(100) NOT NULL
    , SearchPrecedence INT NULL
    , CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID)
    )

GO

BEGIN TRY
    CREATE FULLTEXT CATALOG FTC_FullText AS DEFAULT
END TRY
BEGIN CATCH
    PRINT 'Full Text Catalog already exists.'
END CATCH

CREATE FULLTEXT INDEX ON dbo.Product(ProductName) KEY INDEX PK_Product;

GO

INSERT INTO dbo.Product
(ProductName, SearchPrecedence)
VALUES ('Iphone', 1)
    , ('Iphone Clean', 100)

GO

/** NOTE: need to wait a bit here so that we give the engine time to build the full text results.
    This code works, but Full Text is not synchronous with writes
    **/

WAITFOR DELAY '00:00:30.000'

DECLARE @Search NVARCHAR(100) = '"*Iphone*"'

SELECT P.ProductID
    , P.ProductName
    , P.SearchPrecedence
FROM dbo.Product AS P
WHERE P.ProductID IN (SELECT PFT.[Key] FROM CONTAINSTABLE(dbo.Product, *, @Search) AS PFT)
ORDER BY P.SearchPrecedence


The issue is here

Index Scan using entrytag_tag_id_row_idx on entrytag
    (cost=0.44..402.17 rows=99 width=4)
    (actual time=0.093..535.664 rows=989105 loops=1)

Your statistics are off for

WHERE "entrytag"."tag_id" = 323456

Your planner thinks there are far fewer tag_id=323456 then there are. You may want to try ANALYZE entrytag;, and trying again. Or upping statistics.

ALTER TABLE entrytag
  ALTER COLUMN tag_id
  SET STATISTICS 1000;

Then try ANALYZE entrytag; and trying again. Sounds like a typical case of bad statistics.

You may want to improve your schema, or denormallize. You're joining, selecting, and ordering million rows. It's not going to be instant.