I can't start mysqld.service -> status : Data Dictionary upgrade from MySQL 5.7 in progress in centos 7 I've installed mysql 8 on centos 7

and when i want to start the mysqld service , it says :

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details

and when i run systemctl status mysqld.service it says:

mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor
preset: disabled)
Active: failed (Result: exit-code) since Mon 2020-01-13 06:01:53 EST; 6min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5532 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 5504 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 5532 (code=exited, status=1/FAILURE)
Status: "Data Dictionary upgrade from MySQL 5.7 in progress"

Jan 13 06:01:48 server1.lifeandme.net systemd[1]: Starting MySQL Server...
Jan 13 06:01:53 server1.lifeandme.net systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jan 13 06:01:53 server1.lifeandme.net systemd[1]: Failed to start MySQL Server.
Jan 13 06:01:53 server1.lifeandme.net systemd[1]: Unit mysqld.service entered failed state.
Jan 13 06:01:53 server1.lifeandme.net systemd[1]: mysqld.service failed.

I also tried restart the service and cleaning log files under /var/lib/mysql , but no success

i did the following commands to install lamp on server (history related to lamp installation)

yum install httpd

systemctl start httpd.service

systemctl enable httpd.service

yum install mariadb-server mariadb

systemctl start mariadb

mysql_secure_installation

systemctl enable mariadb.service

yum install php php-mysql

systemctl restart httpd.service

yum remove php php-server

yum clean packages

yum install epel-release

yum install http://rpms.remirepo.net/enterprise/remi-release-7.rpm

yum install yum-utils

yum-config-manager --enable remi-php72

yum install php72

yum remove mariadb mariadb-server

systemctl restart httpd.service

yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

yum install mysql-community-server

systemctl enable mysqld

systemctl start mysqld =>  ran to error

This worked for me:

Clear the mysql data directory at /var/lib/mysql assuming that you have backups or you don't really need that data anymore.

Then sudo service mysqld start


Is this atomic set and get in MariaDB / MySQL: `UPDATE t SET col = @my_var := col + 1 WHERE id = 123`? I want to atomically update a single row and get its value.
I discovered today that MariaDB / MySQL has this additional way of setting user variables using the assignment operator :=, described e.g. here.
I wonder therefore if the following statement, followed by checking the value of @my_var, accomplishes what I want:
UPDATE t SET col = @my_var := col + 1 WHERE id = 123;
It seems it should, but I'd like to confirm with the pundits.

(I know there's also SELECT .. FOR UPDATE.)

I confirm that yes, this is atomic.

This is what I wanted to avoid, but in the end, I wrote a Python script testing this. I'm pasting it below for reference. The prerequisite is a schema my_schema with the following table:
CREATE TABLE `t` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `col` int(10) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `t` (id, col) VALUES (1, -1);

Python script below. Note that you need to comment / uncomment one of the two 'SELECT' lines as needed.
import mysql.connector
import random
import string
import threading
import time

# How many threads to run.
num_worker_threads = 30
# Flag to signal to threads that they should finish.
should_threads_finish = False
values = []

def main():
    global should_threads_finish

    # Start threads.
    print('Starting threads.')
    threads = []
    for i in range(num_worker_threads):
        t = threading.Thread(target=worker, kwargs={'i':i})
        t.start()
        threads.append(t)

    # Let threads run for a while.
    time.sleep(8)

    # Stop threads.
    print('Stopping threads.')
    should_threads_finish = True
    for t in threads:
        t.join()

    # Verify that values queried from DB form a sequence increasing by 1.
    values_sorted = sorted(values)
    for i in range(len(values_sorted)):
        if i != values_sorted[i]:
            print(values_sorted)
            raise RuntimeError('Set and get was not atomic: ' + str(i) + ' vs ' + str(values_sorted[i]))

# What each thread should do.
def worker(i):
    print('Worker ' + str(i) + ' starting.')
    while True:
        if should_threads_finish:
            print('Worker ' + str(i) + ' finishing.')
            break
        # Do work.
        cnx = mysql.connector.connect(host='localhost', database='mysql', user='root', password='root')
        cur = cnx.cursor()

        # Get random name for user variable.
        var_name = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(10))

        # Do the UPDATE.
        cur.execute("UPDATE my_schema.t SET col = @%s := col + 1 WHERE id = 1", (var_name,))
        cnx.commit()

        # Get either the user variable value or actually select from the table.
        cur.execute("SELECT @%s", (var_name,))
        # cur.execute("SELECT col FROM my_schema.t WHERE id = 1")
        row = cur.fetchone()
        values.append(int(row[0]))

        cnx.close()

if __name__ == "__main__":
    main()

As expected, when selecting the user variable, I'm getting no exception, meaning that the sequence of values queried increased exactly by 1. For my use case, this is a good enough check that this is atomic.
Also as expected, when selecting the value directly from the table (not using the user variable), I'm getting exceptions, signifying non atomicity.


memory used by Locks I am kind of curious, one of SQL 2012 enterprise edition with 128 GB of RAM size of database is 370 GB and growing, amount of memory used by locks (OBJECTSTORE_LOCK_Manager) memory clerk showing 7466016 KB. I can also confirm that by looking at perf counter select * from sys.dm_os_performance_counters where counter_name = 'Lock Memory (KB)'

However, when I run query

select count(*) from sys.dm_tran_locks

it shows only 16 locks. So what is using over 7 GB of locks. Is there a way to find out?

Does that mean if once memory for locks has been allocated SQL has not yet deallocated it? In past 1 hour I do not see lock count exceeding 500 but lock memory stays the same.

Max Server Memory is 106 GB, We do not use lock pages in memory and I do not see any memory pressure or any errors in the error log in past 12 hours. Available MBytes counter shows more than 15 GB of available memory.

Activity monitor consistently shows 0 waiting tasks so obviously no blocking.

Considering SQL server lock take about 100 bytes of memory 7 GB is lots of memory and trying to find out who is using it.

I run a server dash board report top transaction by lock count it says "currently no locking transactions are running on the  system. However, lock memory still shows as stated above. DB is most busy during overnight hours.

The lock manager is a such super-hot critical code path (probably the most hot critical code path) that it if it would have to wait on a memory allocation for each lock performance would tank. It probably allocates large memory blocks and manages them on it's own. I wouldn't be surprised if it also reserves memory so that it does not run out of memory in some critical code paths.


Estimating a Size of Joining a Relation with itself I'm studying size estimation of logical query plans in order to select a physical query plan.

I was wondering what is the size of joining (natural join) a relation to itself?
e.g R(a,b) JOIN R(a,b)

Doing a natural self-join is like joining on all the columns, so like joining on the primary key (as the set of all columns is certainly a superset of the primary key.) Every row of the left copy of the table will be joined to exactly one row (its copy) of the right copy of the table.

Therefore, the size of the resulting set R NATURAL JOIN R will be exactly the same as the original R itself (actually the situation is even better, the resulting set is exactly the original R.)

If on the other side, you self-join on (one or more) columns that are not a superset of the primary key, the size can be anything from 0 rows up to the square of size(R).


How to get SQL Server filetable path locator for subfolder by its literal path I have a filetable which have several subtables.

For example

FileTable
->subtable1
->->subtable1.1
->->subtable1.2
->subtable2
->subtable3

etc

How to get SQL Server path_locator for subtable given by something like '/subtable1/subtable1.1' ?

I need it to move files from root to subfolder. I tried this, but its does not work

declare @parent hierarchyid;
set @parent = GetPathLocator('folder2/folder2.1')

update physical_files set path_locator = @parent.GetDescendant(NULL, NULL)
where stream_id = '494D5C8B-AC22-E411-A464-00259060BBB9';

SQL Server 2012 reports:

  Msg 33423, level 16, state 1
  Invalid FileTable path name or format.

I found a solution.
Here is example where filetable name is 'physical_files', target path is 'folder1', target file have stream_id = 'FF9CF452-B522-E411-A464-00259060BBB9'

declare @path varchar(MAX);
set @path = '\folder';

declare @parent hierarchyid;
set @parent = GetPathLocator(CONCAT(FileTableRootPath('dbo.physical_files'), @path));
select @parent

update physical_files set path_locator = @parent.GetDescendant(NULL, NULL)
where stream_id = 'FF9CF452-B522-E411-A464-00259060BBB9';


How to efficiently hit an index in a query that uses IN (mysql) I have a table like this where people can subscribe to certain feeds and the query fetches from the subscription.

Here is a sample table:

+--------+-----------------+-------------------+---------------------+-----------+
| by     | subscription_id | subscription_type | feed_created_time   | content   |
+--------+-----------------+-------------------+---------------------+-----------+
| Peter  |              12 |                 1 | 2012-12-30 10:00:00 | asdf      |
| George |              34 |                 2 | 2012-12-21 00:19:00 | qwerty    |
+--------+-----------------+-------------------+---------------------+-----------+

and its full detail

CREATE TABLE `table` (
  `by` varchar(20) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `subscription_type` int(11) NOT NULL,
  `feed_created_time` datetime NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`subscription_id`,`subscription_type`,`feed_created_time`),
  KEY `subscription` (`subscription_id`,`subscription_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I've created the primary key like so to create a cluster which would allow most likely types of pulls to be most efficient. So the "next applicable" is immediately following.

And here is how I'm making the query right now:

SELECT * FROM table
WHERE subscription_id IN (12,34) AND subscription_type=1
ORDER BY feed_created_time DESC;

I would also like to make a more complicated queries like...

SELECT * FROM table
WHERE (subscription_id IN (12,34) AND subscription_type=1 )
   OR (subscription_id IN (34) AND subscription_type=2 )
   ...
ORDER BY feed_created_time DESC;

It works fine, but I noticed that when I do an explain, it's unable to use any indexes. Or, it at least reports that it hasn't chosen to use any indexes despite the primary and subscription key being available. It just creates a temporary table and sorts them.

It has no problem selecting keys when making queries like subscription_id = 12 AND subscription_type=1 but the IN in the query seems to make all keys unusable. How can I make the queries use the indexes?

Reverse the 'subscription' index. As it is, MySQL will probably use the PRIMARY KEY for lookups by a single subscription_id.

Once MySQL hits a RANGE query, which an IN clause is, it doesn't use the rest of the index.

CREATE TABLE `table` (
  `by` varchar(20) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `subscription_type` int(11) NOT NULL,
  `feed_created_time` datetime NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`subscription_id`,`subscription_type`,`feed_created_time`),
  KEY `subscription` (`subscription_type`,`subscription_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Use %H instead of %h.

select STR_TO_DATE('03.05.2010 00:00','%d.%m.%Y %H:%i');

Or, if HH:MM are always 00:00 you can use date part only:

select STR_TO_DATE('03.05.2010 00:00','%d.%m.%Y');

Check it here: http://rextester.com/CCPKR29919

As @a_vlad has pointed out on his comments, %H is used to format hours as 00..23 and %h as 01..12.

Have a look at DATE_FORMAT() on MySql docs.


Can't connect to SQL Server I'm trying to set up Peer-to-Peer Transactional Replication and I'm stuck at "Add a New Peer Node" step - I can't connect to another server.

In server name field I specify serverIP\DBNODE2 and SQL Server credentials.

I'm 100% sure that the credentials are correct, but I'm not sure about the server name part.

When I try to connect I get the following error:

Any ideas what am I doing wrong?

EDIT: I've checked:

Started SQL Server Browser service
SQL Server service for MSSQLSERVER is running
Firewall is off
Remote connections are enabled in Configuration Manager
TCP ports are enabled

Server name is DBNODE2.
SQL Server instance name is MSSQLSERVER.

The only thing, that I don't understand why it's asking server name? This server is in remote geographical location, so I don't think, that it could find the right server without IP address.
I've tried bunch of different combinations and none of them worked:

IP\DBNODE2
IP\MSSQLSERVER
DBNODE2\MSSQLSERVER
IP
DBNODE2
Also tried double backslashes
Etc.

EDIT2:
SQL Server service is running under 1433 port.
I've used Port Query tool, which I've found here.

    =============================================
    Starting portqry.exe -n {IP} -e 1433 -p TCP ...
    IP address resolved to ec2-*******.compute-1.amazonaws.com
    TCP port 1433 (ms-sql-s service): LISTENING
    =============================================
    Starting portqry.exe -n {IP} -e 1434 -p UDP ...
    IP address resolved to ec2-*******.compute-1.amazonaws.com
    UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
    Sending SQL Server query to UDP port 1434...
    Server's response:
    ServerName DBNODE2
    InstanceName MSSQLSERVER
    IsClustered No
    Version 11.0.3000.0
    tcp 1433
    ==== End of SQL Server query response ====

ANSWER: Probably server IP address is not allowed in server name field. The workaround is to modify hosts file like @MarkIannucci decribed below.

If your server's ip address is static, you can modify your hosts file with a reference to DBNODE2's ip address and specify DBNODE2 in your Connect to Server Server Name file.


Splitting a large table to improve performance This is a follow-up to an earlier question. I have a SQL Server 2008 R2 Standard server, that holds a single database, which itself has almost nothing except a large table.

The table is 100+ million rows (35 columns) and growing at around 250,000 rows per day. We need all the data to be "online", and most of the columns need to be searchable in some fashion. The vast majority of activity on the table is reading; apart from the new data being INSERTed during the day, there's no need to change anything.

Users perform a range of queries on the table, ranging from simple look-up-a-record requests to pulling tens of thousands of rows based on a range of criteria. We only have limited control on the queries that are run, and performance is starting to suffer, even with indexing.

A big part of the problem is disk I/O, which we're addressing by retrofitting a SSD-based array. As all database files will be on this new array, the consensus is that having multiple database files won't make any difference, but that splitting the table up into separate tables might be the way to go.

I'm now puzzling over what would be the best approach to this. Two ideas that I'm debating with myself:

Split the table into "tiers"

A table containing the last week's data, which is the one being
INSERTed into each day
Next table containing from last week
back to 3 months previous
Next table containing from 3 months to 6 months
Next table containing anything older than 6 months

I'd then "shuffle" the data down the tiers overnight (the database
is only accessed 8am-10pm, so I have a window overnight to process
data).
Create tables for data ranges

Create a table for a data range - say per quarter. I'd then have
the data INSERTing into the table 2Q2013, and then trip over to
3Q2013, 4Q2014 etc ...

I could use filegroups to make older tables "read only" if this
would improve performance.

Option 1 is the easiest for me to implement, but I'm not sure if this is a completely mad idea. Option 2 is a more work to implement and maintain, but if it's "best practice" for this kind of problem that it's the way I'll go.

Any and all advice or alternative ideas would be gratefully received - I'm away that these kinds of problems are best solved at design-time.

My first question to your situation would be if the Standard Edition is a given or if you had the possibility to upgrade to Enterprise.

Within the Enterprise Edition of SQL Server 2008 R2 you would have the possibility to use Partitioned Tables.

Essentially, they do what you described as a manual process in the background.

Quote from 1:

  Partitioning makes large tables or indexes more manageable, because partitioning enables you to manage and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. By using partitioning, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes in earlier versions of SQL Server. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required, instead of the whole table.

EDIT as op stated that Enterprise is not an option:

When using Standard Edition, you can fall back to using Partitioned Views.

The main difference from the usage side is that you have to maintain the partitioned tables for yourself and you also have to make sure that every new member tables gets added into the view.

The idea builds on your scenario 2 but you don't have to care about where to insert the data, as this is determined by the Partitioned View based on CHECK-Constraints in the member tables.

For a good example, see 2

As for all partitioning scenarios you have to make sure that the partitioning columns you choose fit your query-and-insert behaviour.


After uninstall oracle, there is remaining directory. 'tfa' After uninstall ORACLE is complete, I can see 'product' directory is removed and I checked sqlplus is not available anymore. The final message looked fine.

However, there is a remaining or replaced directory, which is named tfa. I've searched it and it is a kind of tool to help us find out what's going on.

My questions are:

Why isn't it uninstalled?
Is it installed automatically at the same time of ORACLE install?
Can I remove it with rm command?
After uninstall, what kind of things do I have to check?

My ORACLE version is 12c.

TFA is a seperate installation. It is not part of the database home. Uninstalling the database home does not automatically uninstall TFA.
It is installed while executing root.sh, but it is a seperate installation.
No. Uninstall it with $ORACLE_BASE/tfa/$(hostname -s)/tfa_home/bin/uninstalltfa.sh, as root user.
None.


How to mount a pg_basebackup on a stand alone server to retrieve accidently deleted data Had a bit of a brain fade the other day and deleted all the rows in one of the tables in our database and didn't realise until a couple of hours later.

I have a pg_basebackup file system copy created before that and have setup a virtual server with the same OS and same version postgresql server. I just want to have postgresql start and let me query the database to retrieve information from that one table. I do not want to use it for live data or have it try to update itself from the main server. I have tried but failed so must be missing something.

Can someone please give me a simple point by point on what I need to do or point me to some plain documentation that describes the process.

Jeff

Problem solved. For future reference of anyone facing the same situation I have listed the process below

Saved test servers server.crt and server.key
File structure created by pg_basebackup copied across to a test server.
Replaced server.crt and server.key with saved versions

sudo /etc/init.d/postgresql start

Startup failed.

Starting PostgreSQL 9.1 database server
The PostgreSQL server failed to start. Please check the log output:
2018-07-22 16:19:45.055 AEST [8837] LOG:  database system was interrupted; last known up at 2018-07-14 05:59:04 AEST
2018-07-22 16:19:45.126 AEST [8837] LOG:  could not open file "pg_xlog/000000010000024600000093" (log file 582, segment 147): No such file or directory
2018-07-22 16:19:45.126 AEST [8837] LOG:  invalid checkpoint record
2018-07-22 16:19:45.126 AEST [8837] FATAL:  could not locate required checkpoint record
2018-07-22 16:19:45.126 AEST [8837] HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.1/main/backup_label".
2018-07-22 16:19:45.127 AEST [8836] LOG:  startup process (PID 8837) exited with exit code 1
2018-07-22 16:19:45.127 AEST [8836] LOG:  aborting startup due to startup process failure

Tried removing the backup_label file as suggested in the hint.
sudo mv /var/lib/postgresql/9.1/main/backup_label /var/lib/postgresql/9.1/main/old_backup_label

sudo /etc/init.d/postgresql start
Startup failed.

Starting PostgreSQL 9.1 database server

The PostgreSQL server failed to start. Please check the log output:
2018-07-22 16:22:53.689 AEST [8875] LOG:  database system was interrupted; last known up at 2018-07-14 05:59:04 AEST
2018-07-22 16:22:53.735 AEST [8875] LOG:  could not open file "pg_xlog/000000010000024600000093" (log file 582, segment 147): No such file or directory
2018-07-22 16:22:53.735 AEST [8875] LOG:  invalid primary checkpoint record
2018-07-22 16:22:53.735 AEST [8875] LOG:  could not open file "pg_xlog/000000010000024600000093" (log file 582, segment 147): No such file or directory
emphasized text2018-07-22 16:22:53.735 AEST [8875] LOG:  invalid secondary checkpoint record
2018-07-22 16:22:53.735 AEST [8875] PANIC:  could not locate a valid checkpoint record
2018-07-22 16:22:53.785 AEST [8877] [unknown]@[unknown] LOG:  incomplete startup packet
2018-07-22 16:22:54.141 AEST [8874] LOG:  startup process (PID 8875) was terminated by signal 6: Aborted
2018-07-22 16:22:54.141 AEST [8874] LOG:  aborting startup due to startup process failure

/usr/lib/postgresql/9.1/bin/pg_resetxlog -f /var/lib/postgresql/9.1/main

Note: This is where I went wrong in earlier attempts. I did not specifiy the full path to pg_resetxlog and received a message saying "The program 'pg_resetxlog' is currently not installed. You can install it by typing: sudo apt-get install postgres-xc". I installed that, ran it, destroyed my data and somehow lost the postgres role in the process".
Specifiying the full path to the correct version solved the problem.

sudo /etc/init.d/postgresql start

Starting PostgreSQL 9.1 database server                               [ OK ]

Seems to have fixed it
Tested with psql and I can query the databases.


Error handling in Pl/SQL I'm coding something in PL/SQL for the first time and have come up with a situation.

We are building a package to handle insertion tasks but it's supposed to handle the check violations instead of pre-checking the inserted data.

The thing is, check violations in Oracle return the same error code (-02290) and I want to display a relevant message regarding each different violation.

I tried parsing the SQLERRM with an IF THEN ELSEIF structure, but it doesn't seem to catch the other messages. Example:

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
     RAISE_APPLICATION_ERROR(-20001, 'Data already in table. Please check again.');
  WHEN OTHERS THEN
  BEGIN
  IF (SQLCODE = -02290) THEN
    IF (SQLERRM LIKE '%SIZE%')
    THEN
      RAISE_APPLICATION_ERROR(-20002, 'Size too large for product');
    ELSE
      RAISE_APPLICATION_ERROR(-20003, 'Other dimensions are not coherent');
    END IF;
  END IF;
  END;

Second message never gets shown. Is there a mistake in my checking?

You can define your own exceptions. If you have a package for defining business logic, you can define some exceptions in the package header then raise the proper exception when needed. Which means that you need to keep the exception numbers unique so that you can handle the exceptions from the calling procedures.

ssociating a PL/SQL Exception with a Number (EXCEPTION_INIT Pragma)


Storing STRING and BOOL in a STRING Column? I have a form_fields table that represent different HTML form fields (e.g. text, textarea, checkbox, etc.) that a user needs to populate. It also contains meta data about the fields, like whether or not it is required, placeholder text, label, etc.

I also have a table that maps to the columns of form_fields to capture the data that the users inputted, which looks like this:

===================================
form_field_values
===================================
id -> auto increment integer
-----------------------------------
form_fields.id -> FK to form_fields
-----------------------------------
value -> string, user supplied data
-----------------------------------

The problem is the value column. Most of the data I'm storing there is just a string (in the case of text fields, textareas -- which make up about 90% of what I'm trying to capture). In some cases, I need to store a bool value there, like whether or not a checkbox is checked.

Should I store the string "TRUE" or "FALSE" (or "1" or "0") in the value field? Then somehow cast it to a bool when retrieving the data?

Or did I design this form_field_values table wrong? Maybe I need a table that have columns matching form_fields columns, and store the data that way. Problem with this is that I would not be able to associate these columns with the columns in form_fields.

Use tinyint for these and use boolean in your ORM. SO it will automatically mapped from boolean to 0 and 1.

Its not good way to store boolean as string and then checking the value and assigning bool value on basis of string match.

For boolean just 0 and 1 value in your column as tinyint will solve your problem. For other column which arestring and if cardinality of the values in that column is very small as compared to overall data size then you can either use ENUM datatype or normalize table by joining it with another table containing those values.


Default value as expression from other columns I have 2 tables:
users

user_id (int ai pk)
premium

premium_subscription

subscription_id (int ai pk)
user_id (int) -> fk to user.user_id
expiration (datetime)

I would like to implement a boolean value based on the fact if there's a subscription and its expiration date is ahead of the time I made the query.
I made this if statement, that works in a SELECT query. However, I'm not able to implement it as the value of a table.
IF(timestampdiff(SECOND, CURRENT_TIMESTAMP, (SELECT expiration FROM premium_subscription WHERE user_id = *)) > 0, 1, 0);

Is there any way to apply this expression to this column? Thanks in regards! ^^

DEFAULT has a lot of restrictions.
you can use here a daily EVENTs which runs every day and set premium accourdind to the expiration date
DROP EVENT IF EXISTS daily_event;
CREATE EVENT daily_event
ON SCHEDULE EVERY '1' DAY
STARTS '2020-12-12 00:00:01' -- mustbe in the future
DO
    UPDATE users u1
    INNER JOIN premium_subscription ps ON u1.user_id = ps.user_id
    SET premium = IF(NOW() > ps.expiration , 1, 0);

To enable the event scheduler see manual


SQL Server 2012 Explicit transactions taking time with Always on Setup We have a weird issue while we setup the AlwaysOn on Azure VM. We have explicit transactions in our stored procedures, and which are causing some delays and blocking when till the time we get the response from Secondary Server. We are in Synchronous commit mode of AlwaysOn to achieve High-Availability. But it seems we are seeing issues there as when we do the explicit commit the transactions are taking lot of time to complete and causing blockings. The transaction generaly gets stuck at "HADR_SYNC_COMMIT" wait type.

When we removed the explicit transactions from the stored procedures, the execution of the stored procedures was pretty faster and there were no blockings post that.

Anyone aware of any specifics about behaviour of AlwaysOn on Azure VM

Thank you.

Got all the answers to my question mentioned above. If anyone facing the same problem I would like to point them to the answer :
Here's the answer to all the questions why explicit commit takes time :

[A commit transaction (FlushToLSN/StartLogFlush) can trigger similar behavior, even when the block is not full, so a commit transaction does not have to wait for the block to become full.]

Please find more details on following link :
https://support.microsoft.com/en-gb/kb/230785]

Thank you.


Streaming replication setup, sane setup? Not being a DBA as main profession, I would like to know if the following assumptions are true. I have been searching the net for days and I think I have understand it correctly, but I would like to hear the response from people when faced with my exact configuration.

I want to utilize streaming replication, which I have a working setup for.

I have turned off WAL archiving, since I believe that wal_keep_segments will contain enough backlog to get a slave up to date if necessary (700, you might think this high, I just calculated how much "buffer" I could spare for replication backlog, please tell me if this is the wrong approach).

I have the same configuration on master and slave, which includes the following:

wal_level = hot_standby
hot_standby = on  #(this is ignored on master, as I understand it?)

Now, the main question is the first entry here, I could theoretically set this to minimal on the slave, correct? However, I'm thinking for ease of failover, I should keep it this way?

Finally, I never specify restore_command in my recovery.conf, because as I have understood it, this is for log shipping (when rsyncing the WAL files between the two nodes or similar), or if I were to bring back a slave node from a long disconnect and archive_mode was on. 12 GB of WAL segments should be good for at least a week (unless I do some big restore of a DB), and I assume that all segments that are in pg_xlog will be streamed to the slave upon reconnect if it is behind?

And finally, what would be a good way to surveillance that the replication works as it should? I know about repmgr, but it would be nice to be aware of how the surveillance would consider the slave to be out of date.

This is a really broad topic, and not one I am going to cover in detail here. One point to note however: in my experience, SR can occasionally break due to network issues between datacenters (or ever servers), so disabling log shipping isn't a good idea unless you truly don't have the bandwidth to spare between master and slave(s).


Do varchar(max), nvarchar(max) and varbinary(max) columns affect select queries? Consider this table:

create table Books
(
    Id bigint not null primary key identity(1, 1),
    UniqueToken varchar(100) not null,
    [Text] nvarchar(max) not null
)

Let's imagine that we have over 100,000 books in this table.

Now we're given a 10,000 books data to insert into this table, some of which are duplicate. So we need to filter duplicates first, and then insert new books.

One way to check for the duplicates is this way:

select UniqueToken
from Books
where UniqueToken in
(
    'first unique token',
    'second unique token'
    -- 10,000 items here
)

Does the existence of Text column affect this query's performance? If so, how can we optimized it?

P.S.
I have the same structure, for some other data. And it's not performing well. A friend told me that I should break my table into two tables as follow:

create table BookUniqueTokens
(
    Id bigint not null primary key identity(1, 1),
    UniqueToken varchar(100)
)

create table Books
(
    Id bigint not null primary key,
    [Text] nvarchar(max)
)

And I have to do my duplicate finding algorithm on the first table only, and then insert data into both of them. This way he claimed performance gets way better, because tables are physically separate. He claimed that [Text] column affects any select query on the UniqueToken column.

Examples

Consider your query with 8 filter predicates in your IN clause on a dataset of 10K records.

select UniqueToken
from Books
where UniqueToken in
(
    'Unique token 1',
    'Unique token 2',
    'Unique token 3',
    'Unique token 4',
    'Unique token 5',
    'Unique token 6',
    'Unique token 9999',
    'Unique token 5000'
    -- 10,000 items here
);

A clustered index scan is used, there are no other indexes present on this test table

With a data size of 216 Bytes.

You should also note how even with 8 records, the OR filters are stacking up.

The reads that happened on this table:

Credits to statisticsparser.

When you include the Text column in the select part of your query, the actual data size changes drastically:

select UniqueToken,Text
from Books
where UniqueToken in
(
    'Unique token 1',
    'Unique token 2',
    'Unique token 3',
    'Unique token 4',
    'Unique token 5',
    'Unique token 6',
    'Unique token 9999',
    'Unique token 5000'
    -- 10,000 items here
);

Again, the Clustered index Scan with a residual predicate:

But with a dataset of 32KB.

As there are almost 1000 lob logical reads:

Now, when we create the  two tables in question, and fill them up with the same 10k records

Executing the same select without Text. Remember that we had 99 Logical reads when using the Books Table.

select UniqueToken
from BookUniqueTokens
where UniqueToken in
(
    'Unique token 1',
    'Unique token 2',
    'Unique token 3',
    'Unique token 4',
    'Unique token 5',
    'Unique token 6',
    'Unique token 9999',
    'Unique token 5000'
    -- 10,000 items here
)

The reads on BookUniqueTokens  are lower, 67 instead of 99.

We can track that back to the pages in the original Books table and the pages in the new table without the Text.

Original Books table:

New BookUniqueTokens table

So, all the pages + (2 overhead pages?) are read from the clustered index.

Why is there a difference, and why is the difference not bigger? After all the datasize difference is huge (Lob data <> No Lob data)

Books Data space

BooksWithText Data space

The reason for this is ROW_OVERFLOW_DATA.

When data gets bigger than 8kb the data is stored as ROW_OVERFLOW_DATA on different pages.

Ok, if lob data is stored on different pages, why are the page sizes of these two clustered indexes not the same?

Due to the 24 byte pointer added to the Clustered index to track each of these pages.
After all, sql server needs to know where it can find the lob data.

Source

To answer your questions

  He claimed that [Text] column affects any select query on the
  UniqueToken column.

And

  Does the existence of Text column affect this query's performance? If
  so, how can we optimized it?

If the data stored is actually Lob Data, and the Query provided in the answer is used

It does bring some overhead due to the 24 byte pointers.

Depending on the executions / min not being crazy high, I would say that this is  negligible, even with 100K records.

Remember that this overhead only happens if an index that includes Text is used, such as the clustered index.

But, what if the clustered index scan is used, and the lob data does not exceed 8kb?

If the data does not exceed 8kb, and you have no index on UniqueToken,the overhead could be bigger . even when not selecting the Text column.

Logical reads on 10k records when Text is only 137 characters long (all records):

  Table 'Books2'. Scan count 1, logical reads 419

Due to all this extra data being on the clustered index pages.

Again, an index on UniqueToken (Without including the Text column) will resolve this issue.

As pointed out by @David Browne - Microsoft, you could also store the data off row, as to not add this overhead on the Clustered index when not selecting this Text Column.

  Also, if you do want the text stored off-row, you can force that
  without using a separate table. Just set the 'large value types out of
  row' option with sp_tableoption.
  docs.microsoft.com/en-us/sql/relational-databases

TL;DR

Based on the query given, indexing UniqueToken without including TEXT should resolve your troubles.
Additionally, I would use a temporary table or table type to do the filtering instead of the IN statement.

EDIT:

  yes there is a nonclustered index on UniqueToken

Your example query is not touching the Text column, and based on the query this should be a covering index.

If we test this on the three tables we previously used (UniqueToken + Lob data, Solely UniqueToken, UniqueToken + 137 Char data in nvarchar(max) column)

CREATE INDEX [IX_Books_UniqueToken] ON Books(UniqueToken);
CREATE INDEX [IX_BookUniqueTokens_UniqueToken]  ON BookUniqueTokens(UniqueToken);
CREATE INDEX [IX_Books2_UniqueToken] ON Books2(UniqueToken);

The reads remain the same for these three tables, because the nonclustered index is used.

Table 'Books'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'BookUniqueTokens'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Books2'. Scan count 8, logical reads 16, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Additional details

by @David Browne - Microsoft

  Also, if you do want the text stored off-row, you can force that
  without using a separate table. Just set the 'large value types out of
  row' option with sp_tableoption.
  docs.microsoft.com/en-us/sql/relational-databases/

Remember that you have to rebuild your indexes for this to take effect on already populated data.

By @Erik Darling

On

MAX Data Types Do WHAT?

Filtering on Lob data sucks.

Memory Grants and Data Size

Your memory grants might go through the roof when using bigger datatypes, impacting performance.


LEFT and RIGHT JOIN multiple table, show all null data on mariadb 10.3 i want to join 3 table with null value,
here i have 3 table folowing
tbl_reporting                               tbl_criteria                   tbl_student
|===============================|           |========================|     |===============|
| id | id_criteria | id_student |           | id |   name    |weight |     | id |   name   |
|===============================|           |========================|     |===============|
| 1  |     2       |      3     |           |  1 | worrying  |  3    |     | 1  | Nina     |
| 2  |     1       |      2     |           |  2 | naughty   |  2    |     | 2  | Adam     |
| 3  |     1       |      1     |           |  3 | usually   |  2    |     | 3  | Dodi     |
| 4  |     2       |      2     |           |  4 | good      |  1    |     | 4  | Zarah    |
| 5  |     1       |      1     |           |  5 | obey      |  1    |     | 5  | Udep     |

and the result i want, like this

result
| id | student_name   | criteria_name | weight |
|==============================================|
| 1  | Dodi           |    naughty    |   2    |
| 2  | Adam           |    worrying   |   3    |
| 3  | Nina           |    worrying   |   3    |
| 4  | Adam           |    naughty    |   2    |
| 5  | Nina           |    worrying   |   3    |
|NULL| Zarah          |      NULL     |   0    |
|NULL| Udep           |      NULL     |   0    |
|NULL| NULL           |     usualy    |   0    |
|NULL| NULL           |      good     |   0    |
|NULL| NULL           |      obey     |   0    |

i try myself.. but not what i expected.
here i code self : link

thanks

SELECT r.id, s.name student_name, c.name criteria_name, c.weight
FROM tbl_reporting r
JOIN tbl_student s ON r.id_student = s.id
JOIN tbl_criteria c ON r.id_criteria = c.id
UNION ALL
SELECT NULL, s.name, NULL, 0
FROM tbl_student s
WHERE NOT EXISTS ( SELECT NULL
                   FROM tbl_reporting r
                   WHERE r.id_student = s.id )

UNION ALL
SELECT NULL, NULL, c.name, 0
FROM tbl_criteria c
WHERE NOT EXISTS ( SELECT NULL
                   FROM tbl_reporting r
                   WHERE r.id_criteria = c.id );

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=989338bb30f2f5b4d7be4667763e9522


postgres: Upgraded RDS Postgres from 9.4 - 9.6, id fields went from SERIAL to INT So, on my RDS postgres, that is currently running as a DMS CDC from our previous DB, our ID fields lost the serial type, and it got moved to INT without a NEXTVAL. So, using the example of our table public.pageviews, I tried

ALTER TABLE public.pageviews ALTER COLUMN id TYPE SERIAL;

and it is giving me that SERIAL is not a valid data type.

When I run

ALTER TABLE public.pageviews ALTER COLUMN id SET DEFAULT nextval('pageviews_id_seq');

I am getting the following:

  ERROR:  null value in column "c_key" violates not-null constraint
  DETAIL:  Failing row contains (null, 2018-01-03 14:17:28.952708,
  master, 1528336, ALTER TABLE, 0, , public, ALTER TABLE
  public.pageviews ALTER COLUMN id SET DEFAULT nextval...). CONTEXT:
  SQL statement "insert into public.awsdms_ddl_audit
        values
        (
        default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
        )" PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement

  ********** Error **********

  ERROR: null value in column "c_key" violates not-null constraint SQL
  state: 23502 Detail: Failing row contains (null, 2018-01-03
  14:17:28.952708, master, 1528336, ALTER TABLE, 0, , public, ALTER
  TABLE public.pageviews ALTER COLUMN id SET DEFAULT nextval...).
  Context: SQL statement "insert into public.awsdms_ddl_audit
        values
        (
        default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
        )" PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement

On that, I am not a PG DBA by trade (MSSQL that got lucked into this job) so I am not familiar enough with how to really make my way around things, on pgadmin, I have tried to set the default value for ID to a nextval, but I am not getting anywhere with that.

I'm on windows and have really no experience with psql commands (learning them, but the curve is steep)

While it appears that DMS supports migrating the SERIAL datatype, DMS doesn't support migrating default values like your SET DEFAULT nextval('pageviews_id_seq')

If you are running ongoing replication, then all the data in the read replica target is created in the source database, that is where the nextval from that sequence will be applied.

It kind of makes sense, because DMS is trying to explicity insert a value into that id column, and you shouldn't explicitly set a column that is supposed to have its value generated by a sequence.

If you're using DMS to migrate a database with the intention to switch off the old one then you'll have to

switch off DMS, then on the new database:
create the sequence object pageviews_id_seq, setting the sequence to start at a value higher than the max id in the table, so it's nextval doesn't collide with an existing id
alter the column to apply the SET DEFAULT nextval('pageviews_id_seq')

In your specific case, there is a trigger on the source database (see link below to docs) which looks like this

CREATE OR REPLACE FUNCTION <objects_schema>.awsdms_intercept_ddl()
  RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
  AS $$
  declare _qry text;
BEGIN
  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
         SELECT current_query() into _qry;
         insert into <objects_schema>.awsdms_ddl_audit
         values
         (
         default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
         );
         delete from <objects_schema>.awsdms_ddl_audit;
end if;
END;
$$;

It is trying to insert the default value for the first column in the table awsdms_ddl_audit.

According to the docs the table should look like this

create table <objects_schema>.awsdms_ddl_audit
(
  c_key    bigserial primary key,
  c_time   timestamp,    -- Informational
  c_user   varchar(64),  -- Informational: current_user
  c_txn    varchar(16),  -- Informational: current transaction
  c_tag    varchar(24),  -- Either 'CREATE TABLE' or 'ALTER TABLE' or 'DROP TABLE'
  c_oid    integer,      -- For future use - TG_OBJECTID
  c_name   varchar(64),  -- For future use - TG_OBJECTNAME
  c_schema varchar(64),  -- For future use - TG_SCHEMANAME. For now - holds current_schema
  c_ddlqry  text         -- The DDL query associated with the current DDL event
)

ckey is supposed to be a bigserial, so this should be inserting an incremented bigint automatically for you ( similar to IDENTITY on MSSQL ). The error says that it is failing when trying to insert a NULL, so can you check the datatype for that c_key column, I suspect in your DB the datatype isn't right.

!! Note that in the docs it says you need to do these thing in order
1. create the aws_ddl_audit table
2. create the trigger function
3. attatch the trigger to the ddl_command_end then execute it

The trigger monitors all create , update and drop table statements, which would include creating the aws_ddl_audit table if you create the table after the trigger.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Limitations


Fail to connect to server I am getting this error when attempting to connect to a SQL Server 2008 R2 machine:

  Cannot connect to JUPITER. A network-related or instance-specific
  error occurred while establishing a connection to SQL Server. The
  server was not found or was not accessible. Verify that the instance
  name is correct and that SQL Server is configured to allow remote
  connections. (provider: Named Pipes Provider, error: 40 - Could not
  open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

After restarting the service on server, everything works fine.

Why does this problem arise? How to solve this issue?

Thanks in advance

Is this a named instance? If so, you need to ensure that the SQL Server Browser service is started. If it is not, you will not be able to connect.

Also, can you ping this server remotely? How about telnet to port 1433 (default port for the default instance)?

Check your SQL Server error log to see if there is any unusual activity and errors.

That's a start to troubleshooting this problem.


Backup and off-site restore setup I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one.

Our current situation is ~100 GB of InnoDB and MySAM data in 10 DBs (largest single ~60GB) with no replication. Nightly mysqldump backups by AutoMySQLBackup lock the database for 20 minutes.

We removed Percona XtraBackup due to difficulty in restoring the databases to test if the backup was actually viable and if we could recover from a disaster with them. Also we often transfer several of the databases to a different location for development testing. It didn't appear easy (or even possible?) to restore a single database from the XtraBackup dump on our Red Hat server to a Mac OS X machine for local development. Of course it is very easy to do that with a mysqldump.

Our new setup will have a MySQL 5.5 master replicating to a MySQL 5.5 slave on a different server in the same rack. We're looking to have daily backups, no downtime due to locking etc, and be able to move the backups around easily and restore on different servers/platforms. Perhaps this solution suggested by the prolific Rolando is best?

  SUGGESTION #2 : Use MySQL Replication on the Different Server

  In consideration of your first need (Low Cost), if you have access to
  a commodity server with adequate diskspace, setup MySQL Replication to
  that external server. That way you can run backups on the Slave as
  follows

  STOP SLAVE;
  XtraBackup
  START SLAVE;

  You can do this with zero impact (no server load, no disk I/O) on the Master

Naturally we would replace the XtraBackup with a mysqldump. Are there any better ideas for our situation?

Taking a backup from a slave is exactly how I do it, and it works very well. I can't imagine a better way to take a backup without causing downtime of the master and your application.

I am intrigued by your statement about not being able to restore a backup onto a mac osx machine (this could probably be a different question). An alternative is to setup a virtual machine on your local mac machines that run the same OS your production servers run. A tool that makes managing those machines easy is Vagrant.

Mimicking the production environment has the added benefit of allowing you to test any restore procedure with a fairly high degree of certainty that it would work on the production servers should you need to.


Inserts in MySQL 8 are slower than Inserts in MySQL 5.7 I've started to use MySQL 8 (8.0.12), and I'm realizing that the "inserts" are slower in this version than MySQL 5.6 or MySQL 5.7. It doesn't matter if the table is MyISAM or InnoDB, with just one "insert" with only one record, the server takes about 0.2 ~ 0.3 secs with MySQL 8. The same "insert" in MySQL 5.7 takes just 0.003 secs or less.

I already discarded Windows Defender or another Antivirus Program, the writing speed of the hard disk is not the problem because I have a Virtual Machine in the same server, the VM has MySQL 5.7 and the result is pretty good too: 0.003 secs or less.

I've tested the same "insert" in different computers with different S.O. and the result is always the same: MySQL 5.7 (or 5.6) takes 0,003 secs or less, meanwhile MySQL 8 takes 0.2 ~ 0.3 secs in every insert with just one record. The "creates" are slower too.

What seems strange to me is that simple "selects" or "selects with joins" are faster in MySQL 8 than previous versions. I think the problem is with the queries that write on disk, but not a limitation with the disk, maybe an assigned limitation in my.ini or in another MySQL settings.

Here I leave you the scripts with the slow performance in MySQL 8:

CREATE TABLE `ciiu_test2` (
`id` INT(11) NULL DEFAULT NULL,
`codbut` VARCHAR(11) NULL DEFAULT NULL,
`ciiu` VARCHAR(8) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

Execution time in MySQL 5.7: 0.016 sec
Execution time in MySQL 8.0.12: 0.140 sec

INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (1, '18237', '2750');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (2, '18238', '9491');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (3, '18245', '9411');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (4, '18248', '2221');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (5, '18264', '3520');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (6, '18265', '4645');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (7, '18268', '6202');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (8, '18276', '6512');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (9, '18290', '4210');

Execution time in MySQL 5.7: 0.002 sec
Execution time in MySQL 8.0.12: 0.681 sec

Please, don't recommend me optimize the "inserts" with multiples records. Specifically, I need to get the same times in MySQL 8 with every "insert" that I had once in MySQL 5.7.

I thank you in advance for the help you can give me with this issue.

A difference between 5.7 and 8.0 is that binary logging (to be used for replication and PITR) is on by default in MySQL 8.0. To run without binary logging in 8.0, start the MySQL server with --disable-log-bin.

More details The Binary Log

  The binary log contains “events” that describe database changes such
  as table creation operations or changes to table data. It also
  contains events for statements that potentially could have made
  changes (for example, a DELETE which matched no rows), unless
  row-based logging is used. The binary log also contains information
  about how long each statement took that updated data. The binary log
  has two important purposes:

  For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The
  master server sends the events contained in its binary log to its
  slaves, which execute those events to make the same data changes that
  were made on the master. See Section 17.2, “Replication
  Implementation”.
  Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that
  were recorded after the backup was made are re-executed. These events
  bring databases up to date from the point of the backup. See Section
  7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.


Sql schema design advice This question contains no concrete programming question, i would only like to hear some ideas, on how it would be appropriate to design my database.
So recently I started designing an application where the main goal is the following:

There are users
The users can create reports (they some get points for creating one)
They can provide additional data (additional points)
They can view others reports
They can unlock additional data about other peoples records, using
their own points

The point of my question is: How to store and/or calculate the users current points?

1. First method
The schema, and points-calculating method I first thought of looks like this:

The users (can) have reports, the reports (can) have report details, which are connected to a report detail type, which contains the information about how much points it costs to unlock, or to create a detail from a given type. The user also (can) have transactions which contains the unlocks by the user.
From this schema i could create a query which returns, how much score the user have created, and how much he/she has spent with transactions. I could the substract the two, and i have the current score.
The problem with this: If the score for one report detail type gets modified the score for all users will be modified. Which i don't want to.

2. Second method
The second idea i had is to simply store the users points in a column in the users table, and whenever he/she tries to create/unlock something, update it. The schema could be identical, but there would be an extra column in the users table. For some reason it feels really unprofessional, and it also feels like, that i would store data, where it doesn't belong.

3. ???

I would really appreciate any ideas, on which one of these would be better and why! If I'm started thinking about the problem in a completely wrong manner, I also would like to know about a better solution! Thanks for any help!

The third option is to keep a transaction log of points transactions.

Use the schema from your first option to record the "going rate" for points.  Whenever you have a transaction that either gives or takes points away from a user, write a record (or multiple records) into a points transaction log table.

The transaction log table will have a FK to USERS and a date/time to indicate when the transaction took place.  It could also have FK's to the items which generated the points, such as FK's to your TRANSACTIONS and REPORT_DETAILS tables, that's up to you.

Most importantly, the transaction log table will have a column for the number of points awarded(+) or spent(-).  To get a user's point balance, you run a sum of all transactions for the user up until the current time.  The important thing is to keep a record of how many points were given or taken for that user, at that time regardless of what may happen to other users or at other times.

If performance becomes an issue, you can keep the user's current point total on the USERS table, but this should be denormalized data based on the transaction log details.  If you do this, then you need to have controls in place to make sure you're managing this redundancy appropriately.


Oracle: i want to setup some variables for login.sql I use this login.sql for my oracle session

select           'You are:          '||upper(user) "Welcome to Oracle on AIX 7.1" from dual
union all select 'Logged into:      '||upper(sys_context('userenv','db_name')) from dual
union all select 'Database Version: '||banner from v$version where rownum <= 2;

Instead of using AIX is possible to get some variable?
So when i connect on linux server it doesn't return AIX
I know variable is &variable,but how to set a variable using
sql command "select from..etc"?

Unless you absolutely have to have the platform as part of the alias, I would simply pull the platform from v$database:

select 'You are:          '||upper(user)  "Welcome" from dual union all
select 'Logged into:      '||upper(sys_context('userenv','db_name')) from dual union all
select 'Database Version: '||banner from v$version where rownum <= 2 union all
select 'On Platform:      '||platform_name from v$database;


Calculate value differences of rows before and after bounded by multiple referenced rows Supposed I have a user travelling table like this:

 id |  start_time  |  end_time  |  location  |
----+--------------+------------+------------+
  1 |      1       |     2      |    Loc_A   |
  1 |      3       |     4      |    Loc_B   |
  1 |      5       |     6      |    Loc_A   |
  1 |      7       |     8      |    Loc_C   |
  1 |      9       |    10      |    Loc_A   |
  1 |     13       |    15      |    Loc_B   |
  1 |     16       |    17      |    Loc_C   |
  2 |      1       |     3      |    Loc_D   |

Supposedly we are using "Loc_B" as the reference point - the point that the user must pass. We will have 2 nodes @ "Loc_B". Left tree contains all the rows before the current reference row, right tree contains all the rows after the current reference row (i.e Loc_B @ row 2) till the next reference row (i.e Loc_B @ row 6).

For the 1st node (row number 2):

left tree [ Loc_A ]
right tree [ Loc_A, Loc_C, Loc_A ].

For the 2nd node (row number 2),

left tree [ Loc_A, Loc_C, Loc_A ]
right tree [ Loc_C ].

Time diff is calculated as follow: if row is in left tree, then reference row's start time minus the inspecting row's end time. The sign of value will be minus. If row is in the right tree, then diff is equal to the inspecting row's start time minus the reference row's end time.

Time diff value for 1st node will be:

left_tree [ -1],
right_tree [ +1, +3, +5].

Time diff value for 2nd node will be:

left_tree [ -7, -5, -3 ],
right_tree [ +1 ].

Then the final diff value column, we have to compare 2 consecutive nodes with each other in pair and compare the node[0]'s right tree with node[1] 's left tree. Taking the absolute minimum value and append the sign based on its original value (or whether it is in left or right tree)

For user id - 2, diff will be NIL since he didn't pass through the reference point (Loc_B)

 id |  start_time  |  end_time  |  location  |  diff  |
----+--------------+------------+------------+---------
  1 |      1       |     2      |    Loc_A   |   -1
  1 |      3       |     4      |    Loc_B   |   N/A
  1 |      5       |     6      |    Loc_A   |   +1
  1 |      7       |     8      |    Loc_C   |   +3
  1 |      9       |    10      |    Loc_A   |   -3
  1 |     13       |    15      |    Loc_B   |   N/A
  1 |     16       |    17      |    Loc_C   |   +1
  2 |      1       |     3      |    Loc_D   |   NIL

Other note: left_tree and right_tree can be empty in case of Loc_B is the first or last record (partition by id).

May I know whether it is possible to do this?

SELECT *
      ,CASE WHEN location = 'Loc_B' OR right_tree = 0 AND left_tree = 0 THEN NULL::int
            ELSE CASE WHEN @right_diff < @left_diff THEN right_diff ELSE left_diff END
       END AS min_diff
FROM  (
   SELECT *
         ,CASE WHEN right_tree > 0 THEN start_time - right_end ELSE 1000 END AS right_diff
         ,CASE WHEN left_tree  > 0 THEN end_time  - left_start ELSE 1000 END AS left_diff
   FROM  (
      SELECT *
            ,first_value(end_time)   OVER (PARTITION BY right_tree ORDER BY start_time) AS right_end
            ,first_value(start_time) OVER (PARTITION BY left_tree  ORDER BY start_time DESC) AS left_start
      FROM  (
         SELECT *
               ,count(location = 'Loc_B' OR NULL) OVER (PARTITION BY id ORDER BY start_time) AS right_tree
               ,count(location = 'Loc_B' OR NULL) OVER (PARTITION BY id ORDER BY start_time DESC) AS left_tree
         FROM   travel
         ) a
      ) b
   ) c
   ORDER  BY id, start_time;

Produces your result exactly.

1000 is just some "higher value than any other". Since your actual problem seems to operate with times and intervals infinity would be the perfect choice.

Related answer with detailed explanation how groups (right_tree and left_tree here) are formed:
Select longest continuous sequence

SQL Fiddle.


INSERT INTO + Selects - PostgreSQL 9.2 I wanna add some data into two columns in a table.

Would be something like:

INSERT INTO dm.billables_links (billable_id) VALUES ((SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Ali%')),

INSERT INTO dm.billables_links (mobiuser_id) VALUES ((SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Ali%' AND name_last LIKE 'Daon%'))

Question:

Note that the "billable_id" selected on the first INSERT must be related to the "mobiuser_id" selected on the second INSERT.

How can I do such a thing?

Table dm.billables.links columns:

billable_id, mobiuser_id

SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Ali%' AND name_last LIKE 'Daon%'

950898

SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Ali%'

13270372

Fast and dirty:

 INSERT INTO dm.billables_links (billable_id,mobiuser_id) VALUES (
    (SELECT billable_id FROM dm.billables WHERE account_id = 32152
    AND billable_id IN (select billable_id from junk.billable_id),
    (SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%'));

or a JOIN, if an relation between "billable_id"(junk.billable_id) and "id"(public.ja_mobiusers) exists.


ASM diskgroup drop from another ASM instance I have created long time back one ASM RAC setup and I created one diskgroup with some name say xyz. I think that instance is gone but the diskgroup was not dropped exclusively using command:

drop diskgroup xyz including contents;

Now that I have created a new setup and want to re utilize these disks, I am getting below error:

CREATE DISKGROUP XYZ EXTERNAL REDUNDANCY DISK ...

ORA-15003: diskgroup "xyz" already mounted in another lock name space

How can this diskgroup be removed? Any help is appreciated.

Check the diskgroup status in both instances.  If it is showing mounted in both instances then:

dismount the diskgroup in all the instance except one.  For instance, if you have a 3 node RAC setup, then dismount the diskgroup in nodes 2 and 3.
Drop the diskgroup from the  1st node.


ORA-01506: missing or illegal database name I  connected as sysdba and tried below:

SQL> startup;

ORA-01506: missing or illegal database name

SQL>

I tried multiple scenarios changing the Oracle_home and path variables but no luck.

These are my env variables:

c:\>echo %ORACLE_HOME%
C:\oraclexe\app\oracle\product\11.2.0\server\

c:\>echo %ORACLE_SID%
XE

c:\>path
PATH=C:\oraclexe\app\oracle\product\11.2.0\server;C:\oraclexe\app\oracle\product
\11.2.0\server\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Wi
ndows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\ATI Technologies\A
TI.ACE\Core-Static;C:\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_1
1_2;C:\Users\DINESH\AppData\Local\Programs\Python\Python37\Scripts\;C:\Users\DIN
ESH\AppData\Local\Programs\Python\Python37\

Also, I tried to restart the services manually but no luck.

The Content of initXE.ora is:

*.SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora',*.db_name='XE'

I tried:

SQL> create pfile='C:\temp\initXE.ora' from spfile;

File created.

SQL> create spfile from pfile='C:\temp\initXE.ora';

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2251776 bytes
Variable Size             159384576 bytes
Database Buffers           50331648 bytes
Redo Buffers                5189632 bytes
ORA-00205: error in identifying control file, check alert log for more info

Still getting an error.

$ oerr ora 1506
01506, 00000, "missing or illegal database name"
// *Cause:  No db_name INIT.ORA aprameter was specified.
// *Action:  The database name must be given in the db_name
//          INIT.ORA parameter.

Your ORACLE_SID is XE.
When you issue startup, your process looks for a file %ORACLE_HOME%\database\spfileXE.ora, or if it does not exist, then %ORACLE_HOME%\database\initXE.ora.

You get the above error when the db_name parameter is missing from them.

You can edit initXE.ora with any text editor and add the missing parameter: *.db_name='XE'.
spfile should not be edited directly, first create pfile from it:

create pfile='C:\temp\initXE.ora' from spfile;

Add the missing parameter to `C:\temp\initXE.ora', then create the spfile from it:

create spfile from pfile='C:\temp\initXE.ora';

After that, try startup again.

  ORA-00205: error in identifying control file, check alert log for more info

That is a completely different error, meaning the files defined in the parameter control_files are inaccessible.


AUTO_UPDATE_STATISTICS and FULLSCAN in SQL Server 2008 R2 Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2?

If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date?

note: The need for the FULLSCAN comes by proved suboptimal plan generation when using non-FULLSCAN statistics.

Auto updates always use sampling with the percentage being calculated from the number of rows in the index.

From your SO post:

 Just FYI, the two indexes were PKs of tables having 280M+ and 55M+ rows, respectively.

I guess this is a good example of why SQL Server won't allow a FULLSCAN auto update. The impact of reading that many rows unplanned could cause as many headaches as it solves.

Two options:

Disable auto update and schedule the full scan.
Nudge/hint/sledgehammer the query optimiser in to using the preferred plan, without relying on statistics being 100% accurate.

John Sansom was trying to lead you down the second route with his comment on your SO post, your response suggests you're not keen on the idea?

  I don't really see what that has to do with the actual question...
  anyway, consider that the suboptimal plan used two clustered-index
  scans whereas in the optimal one they are not present (replaced by
  seeks on the same indexes).

If it's not viable to full scan frequently enough to keep the statistics current and maintain the good execution plan, there are other options. The right options can be suggested given the right information on the problem execution plan. A couple of suggestions to start:

Ascending Keys and Auto Quick Corrected Statistics - A not uncommon statistics issue with large tables is ascending data, dates and identity columns for example. Filters on data created after the last statistics update can result in execution plans expecting very low selectivity when in fact large numbers of rows exist.

Hints and/or plan guides - OPTIMIZE FOR and RECOMPILE may be applicable to your scenario. KEEPFIXED PLAN could be used in conjunction with forcing a recompile immediately following a scheduled statistics update.

Plan Forcing - Capture your good plan, attach it to the problem query.

Edit: update following comment regarding this being a vendor application

This is exactly why plan guides exist, giving the DBA a chance to influence execution plans when modifying source code is not possible. There are two approaches (both code samples taken from BOL):

Create an object (if the target is a stored procedure) or SQL Plan (if matching a single statement) guide, as outlined in Understanding Plan Guides and either apply OPTION hints or attach the good execution plan by capture.

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide
    @name = N'Guide1_from_XML_showplan',
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = @xml_showplan;
GO

Create a guide from the cached execution plan, while it's "good", using sp_create_plan_from_guide

USE AdventureWorks2008R2;
GO
SELECT WorkOrderID, p.Name, OrderQty, DueDate
FROM Production.WorkOrder AS w
JOIN Production.Product AS p ON w.ProductID = p.ProductID
WHERE p.ProductSubcategoryID > 4
ORDER BY p.Name, DueDate;
GO
-- Inspect the query plan by using dynamic management views.
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO
-- Create a plan guide for the query by specifying the query plan in the plan cache.
DECLARE @plan_handle varbinary(64);
DECLARE @offset int;
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

EXECUTE sp_create_plan_guide_from_handle
    @name =  N'Guide1',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;
GO
-- Verify that the plan guide is created.
SELECT * FROM sys.plan_guides
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO


How to create an "empty" partial index, or equivalent, in Postgres? I have a boolean column that will be false for >99.9% of the rows. I need to efficiently fetch all rows where that column is true.

What's the best option? Creating an index on the column? Creating a partial index where the column is true? But I don't know what columns the partial index would / should hold.

This doesn't parse, but is there any way to do something like: CREATE INDEX mytable_cond ON mytable () WHERE cond = TRUE; ? Where the index holds literally zero columns?

You are saying "rows" where I think you mean "columns".

It is not legal to specify an empty list of columns in an index.  Just pick a column, preferably one with a short data type.  If nothing else, just repeat the same column in both places.

CREATE INDEX mytable_cond ON mytable (cond) WHERE cond = TRUE;

Be careful how you formulate our index and queries.  A cond = true query cannot use a partial index defined where cond is true and vice versa, those are not considered equivalent operations.


Availability groups multiple instance, multiple listeners In my work environment, I have two database machines:

DB01
DB02

Each database machine has three SQL Server instances:

MSSQLSERVER
FBG
Central

I've managed to set up availability groups with SQL Server listener for the 1st SQL Server instance on both machines, and created SQL Server replication and it's working fine.
Now, I would like to set up a new availability group for the second SQL Server instance FBG on both servers, and I did, but the problem is when I create the listener for the second availability group it goes for the first availability group, I must type listener name / instance name to get it working.
I wish to type only the availability group listener name. For example:

the first availability group name is AVG01 and its listener AvgList01
the second availability group name is AVG02 and its listener AvgList02

When I connect to AVG02 using the name AvgList02 it connects me to AVG01
although I'm not using its listener name.
I must use it this way to get it working AvgList02\FBG where the FBG is the name of the SQL Server instance.
The question
Where is the issue, and how to fix it?

When I connect to AVG02 using the name AvgList02 it connects me to AVG01 although I'm not using its listener name. Where is the issue, and how to fix it?

Consider port configuration play key role in general. In your case most probably following might happened.

Both availability groups's primary replica is same node (DB01 or DB02)
There must be SQL Browser service running in each SQL Server
Default SQL ports (1433) must have been assigned for listeners

When client tries to connect on listener name the IP would be bounded based on DNS response (in multi sub-net first successful IP bound) which lead client connection to pointing to primary replica.

Connection from AvgList02 to AG01 (AVG01):
When the request hitting primary replica (after IP bound from listener name), next part is port (consider wrong port as per point 3) the connection would be established to the service which is running on particular port number (1433).

Connection from AvgList02\FBG to AG02 (AVG02):
Similar to above case (after IP bound from listener name), since the instance name (FBG) mentioned in connection SQL Browser would take action to bound the connection with respective SQL Service that match with instance name mentioned in connection.

Summary:
I personally recommend to use custom port for SQL Server especially when multiple instances running in same server, and disable SQL Browser service, let every connection hard-code custom port number in connection string to connect respective instance without depending on SQL Browser service.

For availability group implementation, consider custom ports and avoid port conflict in following sections:

SQL ports (Default: 1433)
Endpoint ports (Default: 5022)
Listener ports (no default)


Insert/update data into relational database tables only after administrator approval - how to approach it? I'm building a relational database that holds 21 tables, which is connected to my website application through PHP.

The website has search functionality which allows users to search this database. Also, users can enter new data, through a special form. Because these entries must pass rigorous control by administrators, I want to make some kind of approval system, available only to the administrators, which can see user input before it appears in the search results, and allow it, return it to the user to make changes, or delete it.

My question is what would be an approach for this situation from a relational database design standpoint, and what is the best practice?

P.S. I know my question is probably wide, but I could not found an answer by searching the internet or other posts on this website.

You basically have two options:

Extend each table with a 'Status' column, with possible values 'New', 'Approved' and 'Rejected'. Maybe another column for remarks by the administrator, and one to identify the owner of the record (which might already exist). All modules except for the import module should always filter on Status = Approved, which also influences your indices. This approach minimizes the impact on your data model, but that's probably the only advantage.
Build separate import_* tables for each table. They'll have the same structure as the tables in 1. Once a record is approved, it's copied over verbatim to the 'real' table. Creating and maintaining the tables is more work, but data is better separated and you don't need to change the logic of your core application.

There's no built-in waiting queue for INSERT statements in MySQL (or any database for that matter). What you describe is generally handled in application logic, and the database is just there for storage.


How can I alter the output for the sql desc command? When I do desc table, I get back the columns Name, Null? and Type.  What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique.  I realize that this is a combination of columns labeled UNIQUE with the column (if present) labeled as the primary key for that table.

As an alternative, I have tried:

SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE';

which I found somewhere, however I get 0 rows returned when I replace the TABLE_NAME_TO_DESCRIBE with the name of my table.

I am running from sqlplus.  Any way to accomplish this?  Either specific (returning of UNIQUE constraint) or general (how to alter desc command results) answers appreciated.

SELECT utc.column_name "Name"
   , utc.nullable "Null?"
   , concat(concat(concat(utc.data_type,'('),utc.data_length),')') "Type"
   , ui.uniqueness
FROM user_tab_columns utc
LEFT JOIN user_ind_columns uic ON uic.table_name = utc.table_name
   AND utc.column_name = uic.column_name
LEFT JOIN user_indexes ui ON ui.table_name = utc.table_name
   AND uic.index_name=ui.index_name
WHERE utc.table_name='TABLE_NAME_TO_DESCRIBE';


Postgres Restore Not Restoring Bytea Data This is a very urgent situation.

We have a postgres 9.4 database installed on a CentOs machine.

We are trying to take a backup from a Heroku Postgres database and restore it to the database on CentOs machine.

The commands we are using:
pg_dump -Fc -h ec2-99-99-99-99.compute-1.amazonaws.com -p 5762 -U xyz -d dbname > file.dump

The dump created by pg_dump seems to be missing data from columns that have the type of binary (these columns contain images in binary format).

When we restore this backup to the database on CentOS using following command, all the images from the image i.e. binary type columns are missing:

pg_restore -d onlinedb ~/file.dump

We have to go live in the morning and are completely bummed by this unexpected issue.

Output of \d attachments (attachments is the problem table) command:

             Table "public.attachments"
      Column      |          Type          | Modifiers
------------------+------------------------+-----------
 id               | integer                | not null
 configuration_id | integer                |
 style            | character varying(255) |
 file_contents    | bytea                  |

More information:
The heroku postgres database from which we are creating backup is PostgreSQL 9.2.6.
The one we are restoring to is PostgreSQL 9.4.

We finally were able to restore the backup. We tried so many things that I am not sure what the fix was, but our conclusion is that the problem was the bytea_output setting in postgresql.conf.
We think Heroku was using value of 'escape' while our database server was using 'hex' and this caused the problem.


(Ola Hallengren) Deleting log backups older than last FULL backup When I deploy Ola Hallengren's backup script into my servers, I always set the @CleanupTime parameter for log backups to zero. This way, every time the log backup job runs, it checks for log backup files older than the last full backup and deletes them. However, this is also true for full COPY_ONLY backups! The log backup job should only check for the last non-COPY_ONLY full backup in order to delete old log backup files.

Just wondering if I'm the only that has run into this, or if what I'm suggesting makes sense. If I need to take a full copy only backup in the middle day to IDK refresh a TEST database, that copy only backup shouldn't affect the regular daily backup sequence. Let me know your thoughts, please.

Part II of II (had to split my answer)

Doing it better

Considering your RPO and RTO defined by your business, you now might want to alter the parameter of the @CleanupTime to something higher than 0. Why? Because the value 0 will only work together with the built-in fail-safe mechanism.

Let's use the following timeline:

08:00 BACKUP LOG
09:00 BACKUP LOG
10:00 BACKUP LOG
...
20:00 BACKUP DATABASE
21:00 BACKUP LOG
...

At some point-in-time your Transaction Log backups (and Full backups?) are copied to a network drive and backed up from there by means of a backup solution, possibly combined with some kind of tape and/or disk storage. As soon as the first BACKUP LOG ... occurs after the last BACKUP DATABASE ... your previous BACKUP LOG ... files are gone...

Questions to ask yourself

What happens if your network backup fails?
When does this (tape) backup occur? Guaranteed?
When does the full database backup occur?
What do you want to be able to restore?
What RTO do you have?
What RPO does your business require?

Looking at the above questions consider using a different cleanup time (e.g. @CleanupTime=48) to keep additional hours worth of Transaction Log backups on your database server's disks.

Benefits

You are no longer relying on Ola's fail-safe mechanism
Your data is still on disk, even if you create a COPY_ONLY backup
Your data is still on disk, even if the network goes down and ...

... you create a BACKUP DATABASE ...
... a COPY_ONLY backup

Building a solid foundation

At any given point-in-time something will fail. You have to ensure that you can accommodate for any failures down the line and still guarantee to the stake-holders that your solution will be 99,.....% fool proof.

How I do it

Working with Ola's solution is an absolute breeze, IF you make one or two thoughts on how you want to recover a database and based on your businesses RPO and RTO.

My personal implementation is to have a the following schedules/retention policies:

Production systems

Backup TLog

Hourly @ xx:05  (non-SAP systems)
Quarter Hourly @ xx:10, xx:25, xx:40, xx:55 (SAP systems)
Retention : 48 hours

Daily Differential Backup

Monday - Saturday @ 20:00 (non-SAP systems)
Monday - Saturday @ 22:00 (SAP systems)
Retention : 168 hours

Weekly Full Backup

Sunday @ 20:00 (non-SAP systems)
Sunday @ 22:00 (SAP systems)
Retention : 336 hours

Test systems

The test system will be backed up during production hours. This can be 10:00 in the morning or at 14:00 in the afternoon for full and xx:15 for the Transaction Log backups.

Why I do it this way

...or my thoughts behind these decisions ...

By distributing the backup times to different slots, I am evenly distributing the disk I/O on the storage systems. No lumping of massive I/O on the disk at the full hours (FULL) or quarterly hours (TLOG).

I differentiate between SAP and non-SAP because of the sizes of the databases.

Test systems are allowed to thrash the storage during the day. No high performance impacts.

The DIFF and FULL backups occur before the tape backups start and are normally finished before the tape backups start.

The retention policies allow me to reach the RTO and RPO laid out by the business even if the (tape) backup solution is down for a day.

Backups will still work and be compliant with RTO and RPO even if the network (as a whole or only partially) is down for a day.

Your thought process

Your @CleanupTime setting was probably based on a wrong understanding of Ola's script.


Nightly EDW Build Wait Stats We are trying to determine our best MAXDOP and Cost Threshold settings for an EDW box.  I setup a job to clear the wait stats before the build started and then capture the wait stats after the build was complete.

Our server is Windows 2008 and SQL 2008 Enterprise.  (Yeah I know, it is are oldest box and we are working on that).  There are 8 CPU and 132GB of memory for the box and 118GB max memory for SQL.

The wait stats after the run are below.  Would you tweak cost threshold (Current 50) or change maxdop (Current 4).

Rather than change MAXDOP or Cost Threshold based on CXPACKET waits (especially logged by that script -- it's a bit incomplete), I'd rather look at the plan cache.

In there you can see execution plans for queries (assuming your cache isn't too volatile), whether they went parallel, and what their cost is.

That way, you're changing things based on your actual queries, and not just on aggregated numbers.

If you're not sure how to do that, start here with sp_BlitzCache (full disclosure, I'm one of the authors). We'll tell you all sorts of stuff about your plan cache from the XML in there.

EXEC dbo.sp_BlitzCache @SortOrder = 'cpu'

A generic run will get you information about your top 10 plans by CPU (though you can choose other sort orders like reads, duration, and more).

And generate overall warnings, plus some info about the age of your plan cache.

As long as your plan cache is reasonably stable, you should be able to see fairly typical plan costs.

Since you have CTFP set to 50, you may see some serial plans with a cost near but under that, and start A/B testing them for performance with parallelism. One way to do that is Trace Flag 8649.

If you see queries with a lower cost benefit from going parallel, you may want to consider dropping CTFP down to a lower number so they stand a better chance of going parallel.

Quick edit: if your plan cache isn't helpful for some reason, you should probably invest in a monitoring tool if you're serious about performance tuning this server.

Hope this helps!


Storing Changes To Records Everything I am talking about relate to relational database, specific MySQL.

I have a number of tables in a database and for a moderate number of them, I am going to want to store a history of the records values when it changes.  I have seen this done in a couple of different ways:

One Table/One Field - Basically there is one table that store the history of all the table that need history storage.  All change are recorded in one field as a text data type.
Table Per Table/One Field - Same as above except the each table has its own history table (ie. Projects/ProjectsHistory, Issues/IssuesHistory, etc...).
Table Per Table/Field Per Field - This is like the above in the each table has it own histroy table but also the history table has pretty much the same definition as the regular table with an additional of additional history related fields (updateDatetime, updateUserId, etc...).

What are some of the advantages and disadvantages to these different methods of storing record history?  Are there other methods that I have not thought of?

Since you've got many tables with varying numbers of columns, #1 would be out since you'd have a massive table with the aggregate of all your columns, and lots of nulls.

Between #2 & #3 I think you have a decision to make regarding the design complexity you want to manage. My view is that it would be easier to maintain an exact archive replica for a given table, and store the whole rowstate (with modified time). Think of a case where you update more than one column of a row. In that case #2 would log an entry for the change of the columns separately, even though it was the same transaction. I'd go w #3 for reducing complexity, and capturing point in time row state.


PostgreSQL join subquery can't restrict query I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances.

I'm trying to run a query that will return an individual users 'amount owed' across all groups (to which they belong).

At the moment the query looks like this:

SELECT
    SUM(owe)
FROM (
    SELECT
        (expenses.amount/count(*))
    AS owe
    FROM expenses
    LEFT JOIN user_expenses
    ON (
        expenses.id = user_expenses.expense_id
        )
    WHERE expenses.paid_by != 1
    GROUP BY expenses.id
    )
AS total;

The problem is this query retrieves ALL expenses across the board. What i'd like to do is restrict by using:

WHERE user_expenses.user_id = 1

How can I add that to the aforementioned query (in a subquery) to restrict the result to expenses that only that user is associated with, but still get all users involved in an expense so i can do the math for amount each member owes?

TABLE SCHEMA (if that might help):

USERS
id
name
username
email

EXPENSES
id
created_at
updated_at
title
amount
group_id
paid_by
img_url
note

USER_EXPENSES (join table)
id
expense_id
user_id

GROUPS
id
created_at
name
desc

USER_GROUPS
id
user_id
group_id

Some more sample info for clarification:
if i run this query -

SELECT
    e.title,
    e.amount,
    (e.amount/count(*)) AS owe, count(*) AS members
      FROM expenses e LEFT JOIN
           user_expenses ue
           ON e.id = ue.expense_id
      WHERE e.paid_by != 1
      GROUP BY e.id;

i get something like this (with dummy data):

If I run the following:

SELECT
    e.title,
    e.amount,
    (e.amount/count(*)) AS owe, count(*) AS members
      FROM expenses e LEFT JOIN
           user_expenses ue
           ON e.id = ue.expense_id AND ue.user_id = 1
      WHERE e.paid_by != 1
      GROUP BY e.id;

I get this (note the members count):

You want to filter out groups of rows rather than individual rows. That is, you want to keep only the groups that have ue.user_id = 1. Therefore use HAVING, rather than WHERE or ON, to add that condition, because HAVING is used for group filtering:

SELECT
    SUM(owe)
FROM (
    SELECT
        (expenses.amount/count(*))
    AS owe
    FROM expenses
    LEFT JOIN user_expenses
    ON (
        expenses.id = user_expenses.expense_id
        )
    WHERE expenses.paid_by != 1
    GROUP BY expenses.id
    HAVING COUNT(ue.user_id = 1 OR NULL) > 0
    )
AS total;


How to use explode mysql function in where condition with cakephp I am using cakephp 2.6. I have payment_method fields in mysql table which store the values like 54,52,59,57 .

Now my question is that how can i check if new posted payment method id is available in  the payment_method value.

Here $data['PaymentMaster']['payment_method'] = 52

$paymentDetail = $this->PaymentMaster->find('first',array('conditions'=>array('NOT' => array('payment_method'=>$data['PaymentMaster']['payment_method']))));

I want to check if value not in database then add in to database other wise fire an error that Error: The rule is contradicting with the other rule, please check the rule.

Any help would be appreciated. Thanks in advance

by Unique constraint on your payment_method field, you can make sure that payment_method is unique. If the peyment_method is already exists, it will throw a SQL Error.

  Duplicate entry 'x' for key 'peyment_method'

The SQL for that would be:

ALTER TABLE YourTableName ADD UNIQUE (payment_method)

But if you mean you need to make sure this payment_method exists and the payment_method_id is in another table, you need foreign key.

ALTER TABLE YourTable ADD FOREIGN KEY `methodidCheck`(payment_method)
REFERENCES payment_method_table(payment_method_id);

Hope I got your question right


RMAN - Fail To Restore Oracle DB with CDB and PDB I'm having some issues restoring an Oracle 12C database from one server into another one.
After trying this procedure:
1>Connect to the ORIGIN Database server
2> Create an rman script file:
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
DELETE EXPIRED
ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
DELETE NOPROMPT EXPIRED BACKUP;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG TAG DAILY_BKP DELETE INPUT MAXSETSIZE=5G KEEP UNTIL TIME 'sysdate+3';
spool log to '<bkp_logfile>';
EXIT;

Run the command:
export ORACLE_SID=;rman target / @

After the backup finished, copy the files to the DESTINATION database server. Also copy the backup control files.

Connect to the DESTINATION database server

Create pfile:

     *.audit_file_dest='<Path1>\adump'
        *.audit_trail='db'
        *.compatible='12.2.0.0.0'
        *.control_files='<Path2>\control01.ctl','<Path2>\control02.ctl'
        *.db_block_size=8192
        *.db_domain=''
        *.db_name='CAODB01U'
        *.db_recovery_file_dest='<Path2>'
        *.db_recovery_file_dest_size=10737418240
        *.diagnostic_dest='<PAth Diagnostic>'
        *.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBXDB)'
        *.enable_pluggable_database=true
        *.memory_target=2729m
        *.open_cursors=300
        *.processes=500
        *.remote_login_passwordfile='EXCLUSIVE'
        *.undo_tablespace='UNDOTBS1'

rman> target /

rman>startup nomount pfile='< pfile >'

rman>restore controlfile from '< Path of datafile >';

rman>Alter database mount;

rman>catalog start with '';

rman>restore database;

rman>recover database;

rman>alter database open resetlogs;

After the last step the message i get the message:
> RMAN-00571:
> =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
> =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 05/20/2021 12:21:30
> ORA-01152: file 1 was not restored from a sufficiently old backup
> ORA-01110: data file 1: '<Path DB >\SYSTEM01.DBF'

As ive seen in all the procedures to restore it, that should be the way to do it , but it doesnt seems to be working. Does anyone have an idea of what im doing wrong?
Also, when i try with
restore database;
recover database;
it tells that there are some datafiles missing, but there shouldnt be missing because its supposed to be a full backup.

The error message is a bit confusing. Normally this is solved by feeding a few extra archives to the recovery process and issue a cancel.
Do this using sqlplus.
Let it recover an archive and when it asks for the next archive issue 'cancel'. Most of the times the database will open.


Creating Linked Server - Impersonation of Windows Authentication I am trying to set up a linked server on Server A to Server B using windows authentication.

Using SSMS, I can connect to both Server A (from ServerB) and Server B (From Server A) using Windows Authentication and the username DOMAIN\User.

Additionally, when I log into Server A from Server B or vice versa and run the following command:

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

the result tells me the auth_scheme is KERBEROS

The SQL Server service on ServerA is running as the same domain account as I am trying to log in as:

Get-ADUser UserAccount -Properties trustedfordelegation|select trustedfordelegation

returns False

When trying to set up the linked server on Server A, I get an error message which is different depending on which option is selected in the For a login not defined in the list above, connections will: section.

To me this suggests that the config in the Local server login to remote server login mappings: is being ignored.

I have set the Local Login to DOMAIN\User and clicked the Impersonate box

Server A is SQL Server 2008R2 and Server B is SQL Server 2016 SP1

Can someone confirm what I need to do to be able to set up the linked server to connect using the DOMAIN\User account?

Check to see if service account on source server is trusted for delegation. If it is not, enable it. You can identify which account needs this setting with the following query

SELECT servicename,service_account FROM sys.dm_server_services WHERE filename LIKE '%sqlservr.exe%';

you can do so via powershell

Get-ADUser serviceaccount -Properties trustedfordelegation|select trustedfordelegation
Set-ADUser -Identity serviceaccount -TrustedForDelegation $true

or with AD users and computers: on the properties of service account, delegation tab.

A restart of SQL Server may be required after making this change.


Database Link from Oracle 10g on Windows over to MySQL database Didn't see any answers on this subject so hoping for a miracle! :-)

I've got an Oracle 10g database running on Windows Server 2003 and I'd like to create a database link to an external MySQL database which is running in the Amazon Web Service RDS cloud.

We may have the option of upgrading the Oracle version to 11g if that would help?

Has anyone done anything like this before?

Thanks for your help! Josh

This can be done by conneting to an ODBC data source through a dg4odbc listener.

There is a reasonable explanation available here:

http://hs2n.wordpress.com/2012/04/03/oracle-create-database-link-to-mysql-database/


Mysql: Any way to properly index 3 ENUM columns with the same options ? (A OR B OR C) I have 3 enum() columns with the same option values inside.
I first tried to use the "set" datatype which originally was meant to do that (hold multiple values from a set) but it seems that datatype isn't managed for 15+ years and isn't even supporting an index.
Is there a nice way to index those 3 columns so I can use them in searched without destroying query performance ?
SELECT * FROM TABLE WHERE a='x' OR b='x' OR c='x'
I thought about creating a virtual field which uses a boolean logic (&) on the 3 enum field-numbers and combines them into a large number but that's quite a hack and not nice to maintain.
Has someone solved this sort of task elegantly ?
(I do not want to use a support table and JOIN it, I want to stay with a single table)

Because you need to use OR logic in your WHERE predicate, there's not a way to create one index that covers every case unfortunately.
Fortunately you're basically on the money with a virtual field or rather you should create a generated column, and then you can create an index on that single column so your queries are performant.