Sql query for room availability I have the following tables in my database:
1.Roomtypes
2.Reservations

Roomtypes have the following columns
id,
product_id,
type,
total_no_of_rooms,
created_at,
updated_at

Reservation has the following columns
id,
roomtype_id,
customer_id,
check_in,
check_out,
no_of_rooms,
created_at,
updated_at,

i need to get the rooms remaining in roomtype table if someone books a room. that is details no of rooms in a specific date,rooms which are not in reservations. can anyun help me with the query

My best guess, in the absence of a table CREATE script, would be something like:

SELECT rt.id, rt.type, rt.total_no_of_rooms,
    IFNULL(SUM(r.no_of_rooms), 0) as "reserved rooms",
    rt.total_no_of_rooms - IFNULL(SUM(r.no_of_rooms),0) as "rooms available"
FROM Roomtypes rt
    LEFT OUTER JOIN Reservation r ON r.roomtype_id = rt.id
        AND CURRENT_TIMESTAMP BETWEEN r.check_in AND r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

This query should get the number of rooms available for each room type now (CURRENT_TIMESTAMP), but you can replace that with any other time stamp, e.g. '2018-05-17 10:00:00'.

To see how many rooms are available within a data range - check-in at 2018-04-11 10:00:00 and latest check-out at 2018-04-12 09:59:59:

SELECT rt.id, rt.type, rt.total_no_of_rooms,
  IFNULL(SUM(r.no_of_rooms), 0) as "reserved rooms",
  rt.total_no_of_rooms - IFNULL(SUM(r.no_of_rooms),0) as "rooms available"
FROM Roomtypes rt
  LEFT OUTER JOIN Reservation r ON r.roomtype_id = rt.id
      AND '2018-04-11 10:00:00' >= r.check_in
      AND '2018-04-12 09:59:59' <= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

Here's an sqlfiddle example (MySQL 5.6) with create script.


Changing data directory for postgres on CentOS I've got a postgres 9.2.18 installation on CentOS which used the standard installation path. Now I realised that on this particular partition, there isn't sufficient hard-disk space for some bigger queries.

The default data_directory is on:

/var/lib/pgsql/data

$ df -h /var/lib/pgsql
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   50G   39G   12G  77% /

So, I'd like to move the data directory to my home repository (it's a test-DB only for myself)

$ df -h /home/mlu
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-home  395G  171G  224G  44% /home

For doing so, I've tried:

1) Copying postgres data using rsync:
sudo rsync -av /var/lib/pgsql /home/mlu/postgres-data

2) Addressing the new path within postgresql.conf:
sudo -u postgres nano /var/lib/pgsql/data/postgresql.conf
  data_directory = '/home/mlu/postgres-data/pgsql/'

3) starting postgres:
sudo systemctl start postgresql

Starting postgres service doesn't works now, because of the new data_directory path within the postgresql.conf. (It works, if I comment out the directory path again though)

EDIT: Ok, I've figured out, that it's a permission conflict. But shouldn't rsync just have copied the correct permission as well?

sudo service postgresql.service status
Redirecting to /bin/systemctl status  postgresql.service.service
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2018-02-13 18:34:54 CET; 21s ago
  Process: 27416 ExecStop=/usr/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
  Process: 27484 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=1/FAILURE)
  Process: 27473 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 27290 (code=exited, status=0/SUCCESS)

Feb 13 18:34:53 axonlu-ws026.mappuls.int systemd[1]: Starting PostgreSQL database server...
Feb 13 18:34:53 axonlu-ws026.mappuls.int pg_ctl[27484]: FATAL:  could not read permissions of directory "/home/mlu/postgres-data/pgsql": Permission denied
Feb 13 18:34:54 axonlu-ws026.mappuls.int pg_ctl[27484]: pg_ctl: could not start server
Feb 13 18:34:54 axonlu-ws026.mappuls.int pg_ctl[27484]: Examine the log output.
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: postgresql.service: control process exited, code=exited status=1
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: Failed to start PostgreSQL database server.
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: Unit postgresql.service entered failed state.
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: postgresql.service failed.

Not sure why I still get a permission denied, since postgres should have all necessary rights as owner of the folder:

$ ls -l
total 4
drwxrwxrwx. 6 postgres postgres 4096 Aug  3  2017 pgsq

SELinux blocking access? Check your /var/log/audit/audit.log.

It might be necessary to fix the SELinux context for the new dir:

semanage fcontext --add --equal /var/lib/pgsql /home/mlu/postgres-data/pgsql
restorecon -rv /home/mlu/postgres-data/pgsql/


Saving pixels in images in a database I work with scientists who analyze pictures. These pictures have a limited number of colors and a main component of their analysis is to understand how particular pixels are distributed within each image. So basically what I need to save for a full analysis is the position and color of each of the pixels. The images that I need to process are things similar to these:

Note that the shapes are often not squares or rectangles, but could be any shape. However, what is always the case is that:

There is one background color that will dominate 60-90% of the image
There are only 6 different colors in use
Images vary in size from 100x100 pixels to 1500x1000 pixels

We will be processing hundreds of thousands of images. So, this will quickly becomes huge if I want to store every pixel's color (large images have 1.5 million pixels). I haven't dealt with databases of this size before, nor have I dealt with storing pixel data from images, so I was wondering if you can give any advice with regards to:

Database recommendations (favoring PostgreSQL but any open source solution can be considered)
Tricks to store the data without things becoming so huge

One thing I'm thinking is to store the main background color for each image, and that way I only have to store those pixels that are NOT the background color. It would help, but I wonder if there's more that can be done...

Relational databases are not specially good for storing information that you would say "have very variable structure".

A fully normalized structure would probably represent an image of your kind with two tables:

one for pictures (picture_id,background_color)
and another for non_background_pixels_of_pictures (picture_id, x, y, color).

But this would probably be terribly space consuming and inconvenient for image processing. It all depends on which kind of "queries" you would be performing, and which tools you'd be using.

Alternatives (based on PostgreSQL):

Store a reference to a standard image file (the path of the file). If this is done for "scientific" purposes, I guess you want a lossless storage format. This will allow to use very many libraries or programs that work directly with standard image formats (TIFF, JPEG, PNG, ...).
Store the standard image data format as binary data (a "blob", using the bytea type in PostgreSQL, for instance; or the Large Objects Facility).
Use some specific types or extensions for images.

For instance, with certain versions of PostgreSQL, you can use:

a. PostPic:

  PostPic is an extension for the open source PostgreSQL dbms that enables image processing inside the database, like PostGIS does for spatial data. It adds the new 'image' type to the SQL, and several functions to process images and to extract their attributes.

b. PostgreSQL-IE

  [...] To facilitate the development of new CBIR systems, we propose an image-handling extension to the relational database management system (RDBMS) PostgreSQL. This extension, called PostgreSQL-IE, is independent of the application and provides the advantage of being open source and portable. The proposed system extends the functionalities of the structured query language SQL with new functions that are able to create new feature extraction procedures, new feature vectors as combinations of previously defined features, and new access methods, as well as to compose similarity queries. PostgreSQL-IE makes available a new image data type, which permits the association of various images with a given unique image attribute.

NOTE: this seems to have been a doctoral thesis or some other research program... from 2006. Check also their download page.

c. pg_image, A bitmap image datatype for JPEG and PNG images.

  This extension provides an IMAGE datatype for the storage of bitmap images, which verifies that the image data is in a recognized format (PNG or JPEG) and provides accessor functions for the image metadata (format, width, height) as well as the image data itself.
  Planned features include conversion between formats, cropping and scaling, annotating with text, and compositing of multiple images.
  Depends on the GD library for image loading and manipulation.
  Compatible with PostgreSQL 9.2 and 9.3beta2.

NOTE: Looks like it's not being maintained.

Other references:

Storing Images in PostgreSQL @ Stack Overflow. It provides a very good explanation of the bytea vs large objects approach.
Filesystem vs. Postgres for images.
Store Images in the Database
Store, manipulate and analyze raster data within the PostgreSQL/PostGIS spatial database


SQL Database Design Relationship Choices I'd like some advice about how to go about creating a database for the following situation:

I want to track my work hours.
I'll have three entities: Shifts, Category, Location (Example: 6am to 6pm, Nursing, Brisbane Hospital).

I understand the shifts can have one category and one location. So the main question is: Should Category and Location contain a list of shifts? Or I should let the front ent of my app deal with displaying things like "Shifts Per Category" or "Shifts per Location"?
Thank you!

I would personally design it such that there was one more table called ShiftCategoryLocations (or named something a little more user friendly) that had every logical combination of ShiftId, CategoryId, and LocationId. It would also have its own primary key ShiftCategoryLocationId (or whatever name made more sense) so that your WorkHourLog table could reference the 3 dimensions easily by only having a foreign key relationship on ShiftCategoryLocationId. This is known as a dimensions combinations table.
While for small data you could definitely recreate the relations as needed in the application side, but the most efficiency will be if it existed in the database. It also is more reusable and readable in a sense by being refactored into one place, the database.


Saas MySQL Multiple Databases or One Database I am using MYSQL so Database is the Schema, sorry if not using right terminology.

I currently have a Saas product which has multiple databases one for each client with a main databases which we reference for account names and connections to the individual client databases.

I am starting a new Saas product and wondered if this is the right approach for scalability.

I have read many posts on this but a lot are old posts and was wondering if anyone could offer opinion on which approach currently works best for scalability,

What is easier to scale one db or multiple db with sharding/partitioning?

Which approach is better/easier for connection pooling?

Which approach is better for query read/write speeds, is one large database better than 100s of small dbs?

Which approach is better on server CPU/Memory usage one large database better or 100s of small dbs?

I use AWS RDS, Thanks in advance.

There is no performance difference between single base and number of bases, because "schema" is just the domain of authority. Grouped together or aparted, tables stored and processed in the same way. From the administrative point of view multiple bases are bit preferrable as far as you can dump the whole data for single client by DB name, not the pattern for the mangled tablenames.

Sure your experience may vary.


Relation between first name and last name I have some troubles understanding the relation between the a person's First name and Last name in a table. (In a Relational Model)

I've read that the relation is of type: 1-1:
They are linked together.

But the same first name can be attributed to multiple persons. And a single person may have multiple first names...

What I understood is:

  Even if "John Doe" has many homonyms around the world, in a database,
  his first name would be directly linked to his last name.

  To avoid this question, we can regroup the last name and first name in
  a single row but that would end with humans problems (Formatting
  errors: Which order, in capital letters ? separed by spaces or
  comas... ?)

  In the case of a mail generation (for example), I should use the following format:
  Title + Last Name (Mr. Doe) instead of (Mr. DOE John).

  Then I should atomize my two values.

This reflexion should seems useless as my research should be based on ID or others criterias but as I'm still learning, I try to understand the best way possible each details of a databases's conception.

I think the example is a very theoretical one. I have never seen any real world examples of separate FirstNames and LastNames tables. Of course you can normalize your database this way, but it wouldn't make much sense in most of the cases.

Back to the relation. I think there are two relations here:

First name - Person 1:1
Last name - Person 1:1

I don't think there is a relation between the first and last name.

We can take a slightly different approach with one surname(last name) and multiple "first" names. Then there can be a many to one relationship between first names and person.

However, in most cases first name and last name are just attributes of one person. To solve the issue of mail generation, you can have 3 columns: first name, middle name and last name. Then you use only first and last names for mail generation and store the middle name for other purposes.


Insert to a table using function with default arguments I'm trying to write a function that makes an insert to the table defined below:

DROP TABLE IF EXISTS pl_table;
CREATE TABLE pl_table
(
    id          SERIAL PRIMARY KEY,
    mandatory   VARCHAR NOT NULL,
    option1     VARCHAR DEFAULT null,
    option2     VARCHAR DEFAULT null
);

The idea is to make only one argument mandatory and other two optional. The function listed below works well for all three inputs being present, but doesn't make an insert if less than three arguments are provided. What should I do to to make the function work for the Case1?

CREATE OR REPLACE FUNCTION pl_test_function(mand_arg varchar
                                          , opt_arg1 varchar DEFAULT NULL
                                          , opt_arg2 varchar DEFAULT NULL) RETURNS void AS
$$
BEGIN
      INSERT INTO pl_table (mandatory, option1, option2) VALUES
             (mand_arg, opt_arg1, opt_arg2);
END;
$$
      LANGUAGE plpgsql STRICT;

SELECT pl_test_function('abc'); -- Case1: doesn't work
SELECT pl_test_function('abc', 'xyz', 'def'); -- Case2: works

Please note that the pl_test_function doesn't return anything in this toy example, but in reality its more complex production cousin returns an integer.

You need to remove the clause STRICT according to the documentation :

  ...RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically...

Use your code like this:

CREATE OR REPLACE FUNCTION pl_test_function(mand_arg varchar
                                          , opt_arg1 varchar DEFAULT NULL
                                          , opt_arg2 varchar DEFAULT NULL) RETURNS void AS
$$
BEGIN
      INSERT INTO pl_table (mandatory, option1, option2) VALUES
             (mand_arg, opt_arg1, opt_arg2);
END;
$$
      LANGUAGE plpgsql ;


Tool for migrate SQL Server 2005 to SQL 2017 There's any tools that can migrate SQL Server 2005 to SQL Server 2017?

You could use the DMA, Data Migration Assistant DMA Microsoft link
You can do both an assessment of the migration, to have a preview of any features that could cause you issues after migration, and it can also do the migration for you.

Personally I only use the DMA to do the assessment and do the migration myself by using either backup/restore or incase there's a no downtime policy, some kind of mirroring.

I do suggest you make some kind of checklist of everything you have to do, and do a trial migration beforehand so there's less pressure when going to production.
Something like this could be useful to you : Migration steps and checklist


Getting number of records in mysql inconsistency When i check the number of records in a MySQL table using "show table status like 'table_name", the number of rows given is different from the number of rows when i run "select count(*)" on the same table.

I would like to know why the number is different, and what each number means.

I found out why from the MySQL Documentation:

  The number of rows. Some storage engines, such as MyISAM, store the
  exact count. For other storage engines, such as InnoDB, this value is
  an approximation, and may vary from the actual value by as much as 40%
  to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate
  count.

  The Rows value is NULL for INFORMATION_SCHEMA tables.

  For InnoDB tables, the row count is only a rough estimate used in SQL
  optimization. (This is also true if the InnoDB table is partitioned.)


Best practice to delete big portion of big mysql table I have a log table in mysql that inserts 1 000 000 rows per day for the past 6 months. (Approximately 180 000 000 rows)

This table requires 54GB of space whereas rest of tables within the same database only require 2GB.

I plan to delete this log table manually using:

DELETE FROM log_table ORDER BY id asc LIMIT 1000000

It is intended to keep approximately 10 000 000 most recent rows.

Should I use some specific procedure such as table optimization, stopping temporarily traffic to the database, use higher / lower limit, make a copy of database to make sure data is not fragmented on the HDD and so on?

Thank you for your help.

While there is more than one way to do that (including the one you mentioned), if you can temporarily stop the traffic to the DB, I suggest this way:

Stop the traffic (At least to that table if possible)
Create a new table with the same fields, but no indexes: CREATE TABLE new_table AS SELECT * FROM myTable WHERE 1=2;
Insert into this new table all the rows that you want to keep: INSERT INTO new_table SELECT * FROM MyTable WHERE id>123456; -- OR, INSERT INTO new_table SELECT * FROM myTable ORDER BY id DESC limit 10000000;
Create indexes on this new table.
Switch the tables by renaming them: RENAME TABLE myTable TO old_table, new_table to myTable;
Drop the old table
Restart the traffic

If the ID of this table is an auto increment one, and you don't care about its value, you will have the chance to reset it to start from 1, so you don't run out of IDs in the near future (This is totally dependent on other factors)

HTH


Add NOT NULL constraint to large table without table scan Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table statement? I created an index on the column hoping it would be used but that doesn't seem to work. May be a check constraint? Other options? Thank you!

Is there a way to prevent a full table scan during the alter table statement?

At this time there is no supported, safe way to do that with PostgreSQL.

Some kind of ALTER TABLE ... ADD CONSTRAINT ... CONCURRENTLY would be nice, but nobody's implemented it. Same with the alternative of adding a NOT VALID constraint that still affects new rows, and that you then VALIDATE later - it'd be good, and it's something everyone knows is needed but nobody's had the time or funding to add yet.

In theory you could directly modify the system catalogs to add the constraint if you know it is true and valid. In practice, well, it's generally not a great idea.

So no, there isn't really a way.


Capturing the Login Failure alerts specifc to bad password attempts I am unable to figure out the way to capture only those failed logins, which have failed with the error:

  Login failed for user X. The password did not match...

Yes I can read that info out from SQL Server error logs and Event Viewer, but the problem is on some servers due to space constraints we are not keeping frequent error logs.

Moreover, what I am trying to achieve here is to find the culprit host or application which is trying to connect with a bad password and later gets the account locked out. Therefore after some time when we try to review the error log messages are full with lock out, but basic password mismatch error log is truncated.

Is there a way to only capture/track and save the information related to bad password attempts for those logins and capturing required details?

You should be able to setup an extended event and use either the generic error_reported event or process_login_finish (I'm not sure if process_login_finish is available in SQL Server 2014).

There's an article detailing a few different options at Using Extended Events to review SQL Server failed logins written in 2014 by Ivan Stankovic.


PREEMPTIVE_OS_WRITEFILEGATHER wait on database RESTORE with IFI on I have an Azure VM with Windows Server 2012 R2 and SQL Server 2014 Enterprise Edition. I have enabled IFI (added SQL Server user NTSERVICE\MSSQLSERVER to "Perform Volume Maintenance Tasks").

I have tried to restore a 3 GB database with files on C drive (SSD) which took about 40 minutes to complete. Checking restore progress, after about 20 minutes it was at 100% complete.

During the restore, I had PREEMPTIVE_OS_WRITEFILEGATHER constantly showing up for my restore.

40 minutes for 3 GB database is too much.

Files being on C drive (not good) and with all the rest of activity that was on C drive - could have this impact my restore that bad taking it to 40 minutes, having in mind this is still a SSD drive?

Is there anything else I could look for in order to find what is causing it to take THAT long?

EDIT:
I have checked the following so far:

1 - checked with EXEC xp_cmdshell "whoami /priv" and I get SeManageVolumePrivilege       Perform volume maintenance tasks          Enabled - so sql server user does have permissions

2 - restarted sqlservice, just to be sure, after the answers below

3 - checked creating a new database with trace flags 3004,3605 ON and it's only logging ldf zeroing, not MDF - so IFI is ON

wBob is right on the money, you need a service restart.  I wanted to enhance his answer and give a script that will also verify that IFI is indeed enabled.

This will determine whether or not you need to kick the service to allow the local policy to take.

USE MASTER;

SET NOCOUNT ON

-- *** WARNING: Undocumented commands used in this script !!! *** --
--Exit if a database named DummyTestDB exists
IF DB_ID('DummyTestDB') IS NOT NULL
BEGIN
    RAISERROR (
            'A database named DummyTestDB already exists, exiting script'
            ,20
            ,1
            )
    WITH LOG
END

--Temptable to hold output from sp_readerrorlog
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL
    DROP TABLE #SqlLogs
GO

CREATE TABLE #SqlLogs (
    LogDate DATETIME2(0)
    ,ProcessInfo VARCHAR(20)
    ,TEXT VARCHAR(MAX)
    )

--Turn on trace flags 3004 and 3605
DBCC TRACEON (
        3004
        ,3605
        ,- 1
        )
WITH NO_INFOMSGS

--Create a dummy database to see the output in the SQL Server Errorlog
CREATE DATABASE DummyTestDB
GO

--Turn off trace flags 3004 and 3605
DBCC TRACEOFF (
        3004
        ,3605
        ,- 1
        )
WITH NO_INFOMSGS

--Remove the DummyDB
DROP DATABASE DummyTestDB;

--Now go check the output in the SQL Server Error Log File
--This can take a while if you have a large errorlog file
INSERT INTO #SqlLogs (
    LogDate
    ,ProcessInfo
    ,TEXT
    )
EXEC sp_readerrorlog 0
    ,1
    ,'Zeroing'

IF EXISTS (
        SELECT *
        FROM #SqlLogs
        WHERE TEXT LIKE 'Zeroing completed%'
            AND TEXT LIKE '%DummyTestDB.mdf%'
            AND LogDate > DATEADD(HOUR, - 1, LogDate)
        )
BEGIN
    PRINT 'We do NOT have instant file initialization.'
    PRINT 'Grant the SQL Server services account the ''Perform Volume Maintenance Tasks'' security policy.'
END
ELSE
BEGIN
    PRINT 'We have instant file initialization.'
END


Adding a database to existing distributed availability group I am working in a test situation and have already successfully created a distributed availability group with automatic seeding. I cannot figure out how to add another database to the distributed AG. I successfully added the new database to the primary AG, but the alter availability group statement does not seem to work on the distributed AG. I get the following error:

  Msg 15151, Level 16, State 1, Line 28
  Cannot alter the availability group 'xxxx', because it does not exist or you do not have permission

Attempting to modify the permissions for my group login yields the same message, yet the group is under the sysadmin role. Surely there is a way to add a database after you create the distributed AG?

I am working in a test situation and have already successfully created a distributed availability group with automatic seeding.

Automatic seeding is a property of the replica not a distributed availability group.

  I cannot figure out how to add another database to the distributed AG.

Interestingly I went to find the MSDN BOL page to show this... and there isn't one. That definitely needs to be corrected and I'll follow up on that.

  I successfully added the new database to the primary AG, but the alter availability group statement does not seem to work on the distributed AG.

Distributed AGs don't hold databases, they hold availability groups. Availability Groups hold databases, that's where we need to go.

  Surely there is a way to add a database after you create the distributed AG?

Surely there must be, as to create a distributed availability group the secondary availability groups must be void of all databases.

How to add a database to an existing distributed availability group:

Create the new database on the distributed availability group's primary replica for that availability group.
If you are using automatic seeding, it should seed the database to the other replicas in the primary replicas group. If you're not using automatic seeding, backup and restore the database to the secondary replicas leaving it in a restoring state. Then join the database to the local availability group.
Regardless of the automatic seeding setting, you'll need to backup and restore the database to each secondary distributed availability group. Restore the database and leave it in a restoring state. Then join the database to the local availability group. Do this for each secondary availability groups' replicas.
Have yourself a treat because that's a bunch of work.


InnoDB vs MyISAM with many indexes I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway:
I have a MyISAM table in my database that stands central to my site. There are many reference tables created around it and many other tables that link directly to row ids from this table. The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints. I have about 15 indexes on it (the data is about 70 MiB, indexes are about 80 MiB). There are maybe several hundred writes a day, versus the tens of thousands of reads. My databases are located on an SSD drive.
Now my question is; would be be beneficial to alter this table to a InnoDB table? I am working on extending my systems to include API traffic as well, meaning that the table will even the queried even more and I want to make sure that everything runs as smooth as possible. Because of this, I'm slightly leaning towards converting it to InnoDB. However, since the table requires many indexes, I'm thinking MyISAM might work more effectively. Can anyone help me make up my mind? Thanks in advance1
Edit: (added by jcolebrand from a comment)
I have added the indexes a long time ago and I changed some things to the PHP code base, so maybe some indexes are out-dated, but overall; yes, they are all used. I run many different types of queries on the table. Different query, different index. But my main question here does not concern the table read performance, but how it performs with many indexes. InnoDB indexes are 'leaved'/ stored with the data, instead of MyISAM's key-file principle. I am just worried that this might cause enough of a performance drop not to outweigh the table lock issue.

There are maybe several hundred writes a day, versus the tens of thousands of reads. My databases are located on an SSD drive.

Based on this statement, let's play with some numbers. Lets's say there are 500 writes a day and 20,000 reads a day. That computes out to the following

97.56% reads per day
2.44% writes per day
40 reads / write

As much as I love InnoDB, I would have to choose MyISAM in this case for several reasons

REASON #1

You only have 150MiB of MyISAM tables (70MiB Data, 80 MiB Indexes)

REASON #2

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

REASON #3

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
With innodb_file_per_table enabled, you could go to the individual InnoDB and run     of the the following to physically shrink the InnoDB table mydb.mytable:

OPTIMIZE TABLE mydb.mytable;
ALTER TABLE mydb.mytable ENGINE=InnoDB;

All this can be avoid with MyISAM

REASON #4

InnoDB protects individual rows by performing MVCC for transaction control. The overhead generated for reads in a day would probably be greater than 150 MiB.

I can probably name 2 or 3 more reasons, but let's cut to the chase: Is there anything that can improve performance for MyISAM in your case? Why, yes there is.

Your said the following

  The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints

If you have many varchars, there is something you can do to increase read/write performance. For any MyISAM table mydb.mytable: run this command:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

What will this do, this will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. In your case, let's assume it doubles your 150 MiB MyISAM table to 300 MiB. Where is the benefit? Your MyISAM table can now be read/written anywhere from 20% - 30% faster without changing anything else I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

Which is faster, InnoDB or MyISAM? (May 03, 2012)
Which mysql storage engine to choose? (May 02, 2012)
Best of MyISAM and InnoDB (Sep 20, 2011)
Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)


Follow up to: SQL Server 2008 R2: Problems after computer name change The Original question can be found here:

SQL Server 2008 R2: Problems after computer name change

I need to know if there is a way to modify the script found in the original question to allow for all instances on a computer. I have a situation where I have used an image to duplicate computers and the instances are keeping the original name of the computer.

I can fix it by manually renaming each instance but that takes time and there are 5 instances at least on at least 15 computers. It would be great if there was a way to script it to look at each instance and check the current name of the computer and correct each instance.

Here is an example of the current state of one of the computers:

The first section is the dialog when you start up the management console. Here I have to change the Server Name and the login so that it matches the current computer.

The second section is the place where I can rename the user for the instance in the security section of the instance.

The third section is the Computers Local Security Groups list. As you can see there is a whack of groups for each program that installed an instance and the names are all of the original computer name. I am concerned that this is a problem that will come back to bite me later and I was wondering if I need to make corrections to these group names. Yes the computers' name was originally IMW761 then changed to IMWTechTemp, but not before I had made my Acronis image to duplicate the computer IMW761.

I found a script in the other thread I mentioned, I was hoping that there was a way to modify it and make it do it for all SQL instances on the computer. This is the script:

declare @currentName as nvarchar(128)
declare @newName as varchar(max)
declare @serverName as varchar(max)
declare @serverInstance as varchar(max)

select  @currentName = @@SERVERNAME
select @serverInstance = cast(serverproperty('InstanceName') as varchar(max))
select  @serverName = cast(serverproperty('MachineName') as varchar(max))

set @newName = @serverName

if (@serverInstance <> '')
begin
      set @newName = @serverName + '\' + @serverInstance
end

if (@currentName <> @newName)
Begin
      print 'sp_dropserver ''' + @currentName + '''';
      print 'go'
      print 'sp_addserver ''' + @newName + ''',local'
      print 'go'
end
else
Print 'ALL OK'

Any ideas, suggestions or help is greatly appreciated.

You could resort to Powershell for that.

I have tested this on a server with multiple instances and this should work:

foreach ($instance in (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances)
{
    if ($instance -eq  "MSSQLSERVER")
    {
    $serverinstance = "."
    }
    else
    {
    $serverinstance = ".\" + $instance
    }

    Invoke-Sqlcmd -Query "select @@version;" -ServerInstance $serverinstance
}

I replaced your query with something safer on my environment though.

You could use -InputFile to source an sql file


Oracle "matches" multiple columns I am developing a web service where a term of search is given, then I return all the matches from employees table. In MySQL I used to do the following:

SELECT * FROM employees WHERE MATCH(name, lastname, nickname) AGAINST('billy jops');

The query finds records that match "billy jops", "billy" or "jops" in name, lastname or description columns of employees tables.

Now I need to emulate this searching pattern in Oracle or at least a little bit closer.

MATCHES SQL Query

I have read about Querying with Oracle Text, and looks like MATCHES SQL Query is what I need. However, looks like the search index is applied just to an unique column:

create table queries (
      query_id      number,
      query_string  varchar2(80)
    );

    insert into queries values (1, 'oracle');
    insert into queries values (2, 'larry or ellison');
    insert into queries values (3, 'oracle and text');
    insert into queries values (4, 'market share');

    create index queryx on queries(query_string)
      indextype is ctxsys.ctxrule;

    select query_id from queries
     where matches(query_string,
                   'Oracle announced that its market share in databases
                    increased over the last year.')>0

  This query will return queries 1 (the word oracle appears in the document) and 4 (the phrase market share appears in the document), but not 2 (neither the word larry nor the word ellison appears, and not 3 (there is no text in the document, so it does not match the query).

Could you please let me know how to adjust this query to be compatible with more than one column?

One method is to use the MULTI_COLUMN_DATASTORE. Below is an example:

drop table t1 purge;

create table t1 (column1 varchar2(100), column2 varchar2(100));
insert into t1 values ('HELLO', 'WORLD');
insert into t1 values ('SAY', 'HELLO');
insert into t1 values ('SOMETHING', 'ELSE');
insert into t1 values ('WORLD', 'CUP');
commit;

Create the datastore with the desired columns:

begin
ctx_ddl.create_preference('t1_multi_column_datastore', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('t1_multi_column_datastore', 'columns', 'column1, column2');
end;
/

Create the Text index using this datastore:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore');

Or if you want the index maintained on commit:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore sync(on commit)');

Searching for the words HELLO or WORLD:

select * from t1 where contains (column1, 'HELLO or WORLD') > 0;

COLUMN1    COLUMN2
---------- ----------
HELLO      WORLD
SAY        HELLO
WORLD      CUP

Notice that you do not need to specify all columns in the query, but you need to specify an operator between words.


The mysql Syntax for insert data is :

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

In your query you just missed values and you must pass where condition to select a specific row, in your case the last insert id:

 INSERT INTO report_backup(mid, account, dev, start, end, logged_user, added_time) SELECT mid, acc, device, start, end, login_user, date_added from main_tbl;

Correct one is :

INSERT INTO report_backup(mid, account, dev, start, end, logged_user, added_time) VALUES (SELECT mid, acc, device, start, end, login_user, date_added from main_tbl WHERE mid = 'Your Last Inserted Value');

Hope my answer is clear enough to fix your issue.


Unexpected Indexing performance (Everything's equal) I wanted to see the effects of having indexes on calculated columns, so I created a table like so:

CREATE TABLE [Domain\UserName].[CompColIndexing](
    [a] [int] NOT NULL,
    [nonIndexedNonPersisted]  AS ([a]+(1)),
    [nonIndexedPersisted]  AS ([a]+(1)) PERSISTED,
    [IndexedNonPersisted]  AS ([a]+(1)),
    [IndexedPersisted]  AS ([a]+(1)) PERSISTED
) ON [DATA]

I've added 800,000 rows to this, with the value for a cycling through 0 to 9.

The following indexes were added:

CREATE NONCLUSTERED INDEX [IX_DJB_CompNonPersisted] ON [Domain\UserName].[CompColIndexing]
(
    [IndexedNonPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

CREATE NONCLUSTERED INDEX [IX_DJB_CompPersisted] ON [Domain\UserName].[CompColIndexing]
(
    [IndexedPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

Than I ran some ORDER BY clauses to see what performance differences I would get, plannign afterwards to see how changing values of a would affect things.

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY a

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedPersisted

Unexpectedly though, I find that I get exactly the same result for each of the queries:

I was at least expecting the SORT operation on the first query to be slower as that one is unindexed.

What's happening here?

The cardinality is low on purpose, in reality, I actually need to sort on three different values.

I'm using Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

Actual execution plans available at:  https://www.brentozar.com/pastetheplan/?id=S10MTuxGg

The table definition that you have is leading to some really odd optimizer behavior. I suspect that you're running into the issue documented in this SE post. To avoid that issue I'm going to create the table with just the [a] and the [IndexedPersisted] columns.

Query hints can be useful to figure out why the optimizer didn't pick the plan that you expected. Here you expected the index to be used but SQL Server did not use it. Let's view both query plans side by side:

SELECT *
FROM [dbo].[CompColIndexing]
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT *
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

The query optimizer thinks that the sort after the table scan is cheaper than 800000 RID lookups. Maybe it's wrong, so let's run the queries and compare performance metrics for them.

cpu_time    total_elapsed_time  logical_reads   reads
1204        1395                3087            1485
1516        1570                851798          0

I got those numbers by looking at sys.dm_exec_sessions after running the queries in separate sessions with "discard results after execution" checked so I wouldn't have to wait on the rows to be returned to the client.

Those numbers seem reasonable to me. Just because an index can be used does not mean that it should be used, especially if SQL Server will need to read the entire table by using the index. That's the worst use case for an index that I can think of. Indexes can be very useful when selecting a small percentage of rows from the table or when they are covering indexes.

The index is a covering one if I only select the [IndexedPersisted] column. In that case, SQL server thinks that using the index is cheaper than doing a table scan. Code to compare the two methods:

-- force table scan
SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX(0))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

Here are the performance numbers:

cpu_time    total_elapsed_time  logical_reads   reads
1171        1212                2727            1088
343         406                 1798            0

Now that the index is a covering index it is a better access path for the table than a table scan.


Why is DBA_SEGMENTS presenting twice the amount of data than the DBA_TABLES? I tried to compute the audit log table usage using the following steps:

Truncate all the audit log table data (i.e. AUD$)
Perform test case that inserts and update data, also delete statement but it wont delete anything.
Measure the byte used on the dba segment.

select tablespace_name,bytes from dba_segments
where tablespace_name ='SYSTEM'
and SEGMENT_NAME = 'AUD$';

Recompute of statistical data.
Retrieve the byte used in the statistical data.

select NUM_ROWS * AVG_ROW_LEN as BYTE_USED
from DBA_TABLES where TABLE_NAME = 'AUD$';

The value I get from #3 is more than twice that of #5, Why is it allocating too much?

Your select on dba_tables doesn't take into account:

Empty blocks in the table (see initial/next, minextents and freelist-related storage parameters among others)
Empty space in the blocks (due to pct_free mainly)
Block headers (initrans influences this size, among others)

I.e. it doesn't take into account the physical storage of the data at all. dba_segments on the other hand only counts physical blocks for the segment, regardless of how much data they contain.

A table with a pct_free set to 50% and never updated will have statistically half-empty blocks for example, and the calculation you did on dba_tables will reveal that when compared to dba_segments.

See the Logical Storage Structures docs to learn more about how data is stored in your database (and the overhead involved).


What happened to my-*.cnf in MySQL 5.6.8? Up to MySQL 5.6.7, the official RPM was shipping with these files:

/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-small.cnf

In 5.6.8, these files are gone, and the only one left is:

/usr/share/mysql/my-default.cnf

I'm not a MySQL tuning expert, and was used to always start with my-innodb-heavy-4G.cnf.
Now I am left with a single configuration option:

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

Is tuning this only variable the magic key to most optimizations, or are there other relevant parameters that I should tune when installing a new server?

In which case, where can I find an equivalent file, that would contain sensible defaults for a InnoDB-only, 4GB RAM dedicated MySQL server?

You know what they say, about "when all else fails..."

  This release continues the process begun in MySQL 5.6.6 of making
  changes to the default values of server parameters. The motivation for
  these changes is to provide better out-of-box performance and to
  reduce the need for database administrators to change settings
  manually. These changes are subject to revision in future releases as
  we gain feedback.

This is all explained in the Release Notes for MySQL 5.6.8.

  On Unix platforms, mysql_install_db now creates a default option file
  named my.cnf in the base installation directory. This file is created
  from a template included in the distribution package named
  my-default.cnf. You can find the template in or under the base
  installation directory. When started using mysqld_safe, the server
  uses my.cnf file by default. If my.cnf already exists,
  mysql_install_db assumes it to be in use and writes a new file named
  my-new.cnf instead.

  The my-default.cnf template replaces the older sample option files
  (my-small.cnf, my-medium.cnf, and so forth), which are no longer
  distributed.

Without question, innodb_buffer_pool_size is the single most important tuning parameter for a MySQL installation.

For "sensible defaults," start with the defaults.

To put it another way...

  "Tuning" your server can be a stunning waste of time.

-- Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Location 12182). OReilly Media - A. Kindle Edition.


How to convert SQL Server database file to MongoDB? I have .mdf / .sdf files created in SQL Server. I want to convert them to MongoDB. How can I do this?

You could also make use of the OOP paradigm; you could then write a simple program, that will basically do the following.

create Classes for each of your SQL Server relations
create your MongoDB structure acording to those models
load the data into objects
insert it into Mongo

This solution could be feasable or not, depending on your DB's size/structure. That should do it. A straight-forward one-click solution I don't think there's available.


Soft delete to improve performance: all wrong? A few years back I read soft delete would improve performance of deletion, because instead of actually removing the item and impact the clustered (and all other) indices, it would just change a value.

I recently tried this and I got very disappointing performances.

So I searched through the web for a good implementation of soft deletion and the first things I ended up reading was articles/blogs saying "soft deletion is bad".

Apparently it cause most harm than good when the table gets huge...

I don't think what I did makes table way more huge tables because a nightly (each day) maintenance removes (for real) all rows marked for deletion.

So, are soft deletion really bad for performance? Do real deletion cause a whole clustered table to be fully reordered (which would be a lot of disk operations!!!)?

In some cases, soft delete is not meant to be permanent. You can just defer the delete to some background job (say, right before you reorganize / rebuild indexes) so that the originating transaction doesn't have to wait for the deletes to occur (especially if you have cascading deletes, triggers, etc).

In other cases, a soft delete is not a performance requirement at all, but rather a way to avoid permanently deleting data, so there is always a trace of it. In that case, you want the soft deletes to be permanent. (But if you "delete" a lot of data at some point the table will be mostly "deleted" data. If that is the pattern and keeping the data permanently is a requirement then you might think about using an archive table to hold the deleted rows, which you can also perform as a deferred background task, still only requiring the originating transaction to update a flag.)

In both cases, you can improve runtime performance by using filtered indexes. Can't guide you on which columns you need to include, but you can say:

CREATE INDEX x ON dbo.table(col) INCLUDE(othercol)
WHERE deleted = 0;

It may sound counterintuitive, but you may want the inverse index as well, to make your delete operations faster, at least in terms of identifying the rows to delete. In that case you probably just want the primary key there:

CREATE INDEX y ON dbo.table(key) WHERE deleted = 1;

Note that there are many limitations:

How filtered indexes could be a more powerful feature
What You Can (and Can’t) Do With Filtered Indexes


How can I create a notification method when someone adds a user/login to have ‘sa’ access? How can I create a notification method when someone adds a user/login to have ‘sa’ access on SQL Server 2005/2008/2008R2? Thanks for your help.

You would need to setup a job which looks at the list of admins and sends an alert if the list doesn't meet the pre-defined list of admins.  SQL Server Denali has this functionality built in.


Which component of SQL Server architecture handles the user login requests? I was asked this question recently and I couldn't find the answer or any detailed explanation on the internet regarding this. Can anyone please explain or provide a reference link that explains about the component of SQL Server architecture that handles the user login requests?

Thanks in advance!!

There is not a dedicated "component of the SQL Server architecture" for this.  The protocol layer, including the whole login process is handled by the Database Engine.  The SQL Server Database Engine is a single large process that hosts

  "the protocol layer, the query processor (also called the relational
  engine), the storage engine and the SQLOS."

--Microsoft SQL Server 2012 Internals, pp 10 Kalen Delaney, et al

This is in contrast to Oracle, which has the "Oracle Net Listener" which is a separate process. https://docs.oracle.com/cd/B28359_01/network.111/b28316/architecture.htm#NETAG209


Update record with converted epoch timestamp from another column I'm trying to convert epoch data in one column (timestamp_epoch) to a legitimate timestamp in another column (timestamp) for every record in a table.  In reading the PostgreSQL Documentation it appears I can do this using the to_timestamp() function, but I get an error indicating this function does not exist.

UPDATE table
SET timestamp = to_timestamp(timestamp_epoch) AT TIME ZONE 'EST'
WHERE timestamp IS NULL;

Server is a PostgreSQL 12.1 and I'm using pgAdmin4 to run the query.  When the query runs, I get an error indicating the function doesn't exist and that I might need to add explicit type casts... for which I'm lost on how to properly do if that is indeed needed.

ERROR:  function to_timestamp(text) does not exist
LINE 2: SET timestamp = to_timestamp(timestamp_epoch)
                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 56

Any help on converting the epoch data in one column to a legitimate timestamp in another column is appreciated.

Thanks!

Your timestamp_epoch is defined with the data type text (which is what "function to_timestamp(text) does not exist"  is telling you).

You need to first convert your text value to a bigint:

to_timestamp(timestamp_epoch::bigint) AT TIME ZONE 'EST'


MySQL: Index when joining to tables not being used (Performance optimizing question) I need to optimize the following query:

SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE blogposts.deleted = 0
AND blogposts.title LIKE '%{de}%'
AND blogposts.visible = 1
AND blogposts.date_published <= NOW()
ORDER BY blogposts.date_created DESC
LIMIT 0 , 50

EXPLAIN SELECT gives me the following result:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE articles ALL blogpost_id NULL NULL NULL 6915 Using temporary; Using filesort
1 SIMPLE blogposts eq_ref PRIMARY PRIMARY 4 articles.blogpost_id 1 Using where

Why does it first take the articles and then the blogposts? Is it because blogposts have more entries?
And how can I improve the query so that the articlepost can use an index?

Update:
An index is set on blogposts.date_created.
Removing the blogposts.title LIKE condition and the date_published <= NOW() doesn't do anything.

When I remove the "articles.id AS articleid" it can use the blogpost_id Index on articles...
Sounds strange to me, someone knows why? (because I actually need it..)

The new explain looks like this:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  articles    index blogpost_id blogpost_id    4    NULL    6915    Using index; Using temporary; Using filesort
1   SIMPLE  blogposts   eq_ref  PRIMARY PRIMARY 4   articles.blogpost_id    1   Using where

Your where condition blogposts.title LIKE '%{de}%' will cause a full table scan on the blogposts table. It's likely MySQL figures scanning 6915 articles is more efficient.

As to how to improve it, you might add an index on blogposts using the date_created or date_published and add a range the to the where condition (something other than <=NOW())


How to do the complex nested select SQL? I have two tables, table1 is as following, id is primary key, table2_id  is foreign key

id date                 aid table2_id message
1  2014-03-19 16:21:02  121   1       login from xxxx
2  2014-03-20 14:00:32  123   2       decrease budget
3  2014-03-20 18:00:32  121   2       increase budget
4  2014-03-21 16:21:02  121   3       login from xxxx
5  2014-03-21 16:21:02  121   3       login from xxxx
6  2014-03-22 10:21:02  121   2       increase budget

table2 is a type table

id type
1  login
2  change_budget
3  search

I need to analyse the relationship between activity change_budget and other activity. I am not sure how could I deal with the big data solution, but, anyway, following is an idea of mine:

I try to write SQL for each time one aid change_budget, count the all other actions of this aid within one day. if one aid have a change_budget at 2014-03-14 16:21:02, I need all the action of this aid from 2014-03-13 16:21:02 to 2014-03-14 16:21:02. Then, group by the action and count.and selected table will be look as following:

aid login search change_budget
121 1     0      1
123 0     0      -1
121 0     2      1

-1 means change_budget is decrease,1 means increase.
Other number is how many time this action of this aid has happen within oneday before this aid change_budget happens.

Anyone can teach me how to write this?

I have tested this query:

SELECT budget.aid,
       sum(CASE change_type.table2_id WHEN 1 THEN 1 ELSE 0 END) as login,
       sum(CASE change_type.table2_id WHEN 3 THEN 1 ELSE 0 END) as search,
       CASE budget.table2_id WHEN 2 THEN IF(LEFT(budget.message, 8) = 'increase', 1, -1) ELSE 0 END as budget
FROM table1 budget
LEFT JOIN table1 change_type
ON budget.aid = change_type.aid
AND change_type.date BETWEEN budget.date - INTERVAL 1 DAY AND budget.date
AND change_type.table2_id <> 2
WHERE budget.table2_id = 2
GROUP BY budget.aid, budget.date;

It was a fun query, I made it with joins, maybe the subselect version could be easier to understand. I perform SELF join to match budgets with other actions in the range requested. Please note that it filters other budget actions and that you will get an overlap if at least 2 budget actions are performed within 1 day.

To fill the second and third column, a sum of a CASE is performed after the grouping by aid and the reference date.

This is the result:

+-----+-------+--------+--------+
| aid | login | search | budget |
+-----+-------+--------+--------+
| 121 |     0 |      0 |      1 |
| 121 |     0 |      2 |      1 |
| 123 |     0 |      0 |     -1 |
+-----+-------+--------+--------+
3 rows in set (0.00 sec)

Please note that no login for the aid 123 so this query returns the right results. There is a mistake on your example. Alternatively, if you do not want to have into account the aid for the actions, then the result would be:

+-----+-------+--------+--------+
| aid | login | search | budget |
+-----+-------+--------+--------+
| 121 |     0 |      0 |      1 |
| 121 |     0 |      2 |      1 |
| 123 |     1 |      0 |     -1 |
+-----+-------+--------+--------+
3 rows in set (0.00 sec)

To obtain this, remove the budget.aid = change_type.aid AND from my query.

Also please note that no query has been done on table2, so I assume values are hardcoded and do not change. You can expand the query to join the other table if that is not correct.


Find material for improvement in Microsoft BI Which websites, books etc. can you recommend me to improve my basic knowledge in Microsoft Business Intelligence 2008, SSAS, SSIS and SSRS?

Please remember that I'm a newbie in technical BI tool.

If possible, it would be great to retrieve any material for task work that would be similiar as doing lab task at university.

// Fullmetalboy

The book "The Data Warehouse Toolkit" by Kimball is a must for any fledging BI developer, IMO.


Promote a slave to master; fall back to former master if new master has issues? I've been managing mysql db's for some years now, and have gone through several iterations of promoting a slave to take over the role of its former master. My understanding has always been that after you promote a slave to become the new master, you need to basically rebuild the former master from scratch (i.e. from backup) to turn it into a slave of the new master. That 'understanding' has likely been due to all those iterations being due to corruption or some other problem with the original master. It may merely be the misguided assumption of a sysadmin.

I'm in the process of migrating an existing hardware infrastructure into IaaS. I have a slave in IaaS of the existing master, and when the time comes, I'll promote that slave to become the new master for the new infrastructure.

But I've wondered  - is there a way to have the promotion be 'non-destructive' of the original master? Say it becomes clear after deployment that there are performance issues, or what have you - and I realize that i need to fall back to the original master, if only for some hours or days until issues can be worked out. Is it possible to re-promote the former master, in a data-consistent fashion with the new master?

I guess a related question in this case would be, can the former master be immediately turned around to become a slave of the newly promoted master? Thus being able to switch between them as needed?

This would all be under CentOS 5.x, Perconal mysql 5.5.

The reason you have to rebuild the old master from scratch is because as soon as you make one single change to the database on the new master (former slave), then the database on the old master is (by definition) stale, out of date, inconsistent, and obsolete.

Promoting a slave to master, as you likely realize already, is not technically a MySQL concept, since every server has the potential to be both, simultaneously.  Promotion of a slave to master is really nothing more than "choosing which server the application will be permitted to write to."

If your intention is to make the old master into a new slave and preserve data integrity, you would configure the two machines to be each other's slaves before you promote the current slave to master... this is master/master a.k.a. circular replication.  If no data is being directly manipulated on the original slave, then any binlog events the slave may send back to the master would be those that originated on the original master, which would be stamped with the master's server_id, so the master will skip over and disregard them.

When you switch things around with your application, the old master/new slave will immediately start acting on replication events it sees in the binlog that were generated by writes to the old slave/new master.

You have likely read negative things about master/master in MySQL, but there's nothing inherently problematic about it as long as you understand that there is no provision for handling conflicting writes made simultaneously to both systems.  If your application is only ever connecting to 1 of the machines at a time, then conflicting writes cannot occur.


Oracle Wallet Setup ORA-12578 I am having trouble setting up the Oracle Wallet on a new box connecting to a new database.  I have added the following lines to my SQLNET.ORA:

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=c:\oracle\Wallet))

The wallet is created without error, the credentials added, and I can list the credentials back from the wallet without a problem, but when I go to use them I get an ORA-12578 as follows:

C:\oracle\Wallet>sqlplus /@MYDB

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 31 16:57:53 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12578: TNS:wallet open failed

I am on a 64 bit box using the 32 bit 11.2 client to connect to an 64 bit 11.2 database.  Logging in without the wallet works fine.  The error message indicates that I should turn tracing on.  I have done so, but don't see anything obvious.  Does anyone have a suggestion before I contact Oracle support?

I was able to figure out from the trace file that there was line in the sqlnet.ora file that it didn't like.  I narrowed it down to the WALLET_LOCATION line and finally recognized that there was a missing parenthesis at the end of the line.


Overload resolution failed I am using reporting services to generate a list of families in my database. After the first family member in the list, I want to put the family's address:

family, First
123 la la lane
Somewhere, Ca 90210
famliy, Second
family, Third

two, first
345 zippy st
Nowhere, UT 85763
two, second
two, third

At first, I thought my expression would be:

=IIF(First(Fields!person_id.Value) = Fields!person_id.Value,
Fields!last_name.Value & " " & Fields!person_suffix.Value & ", " & Fields!person_title.Value & " " & Fields!nick_name.Value & " (#" & Fields!person_id.Value & ")" & vbcrlf & Fields!address.Value & vbcrlf & Fields!city.Value & ", " & Fields!state.Value & " " & Fields!postal_code.Value,
Fields!last_name.Value & " " & Fields!person_suffix.Value & ", " & Fields!person_title.Value & " " & Fields!nick_name.Value & " (#" & Fields!person_id.Value & ")"
)

This is generating a "Overload resolution failed because no accessible 'IIf' accepts this number of arguments." I am guessing my First() does not evaluate properly.

How would I craft this expression to achieve the above output?

I needed to include the context:
First(Fields!person_id.Value,"FamilyID") = Fields!person_id.Value


List of unreferenced objects I need a list of unreferenced objects, that is, objects that neither have any reference with other objects nor are used in any API (Oracle stored procedures).

This is required for reducing and resizing database size.

Please help me to find a list of these objects.

You need to query DBA_DEPENDENCIES.   But be aware of references that may exist outside of the database itself - references in sql scripts on some user's workstation, or in application code that is not a stored procedure.


Fastest mechanism to move data between tables in SQL server We are building a data warehouse. For some of our target tables, the only way to manage the process correctly is to re-transform all the data every time something changes.

Currently we do this using a 'table swap', ie:

Create new data in temp schema 'B'
Open transaction
Move A.Table to schema C
Move B.Table to schema A
Move C.Table to schema B
Commit

In theory this is all instant, because SQL never needs to physically copy any data. However due to the schema locks that get acquired, we are getting terrible concurrency.

Is there any other way of bulk moving data between tables (move, not copy), or do we have to fall back to doing an INSERT INTO (which might be bulk logged, but will still cause all the data to be written to disk again, which is slow)

The tables in question are several GB, hence the performance issues

SQL 2012

The better way wqould be to fix the broken programming requiring a total re-transform of all data. THat is a terrible non-scaling approach to start with and whoever justified that - I hope they now work in another field, as far away from any project responsibility as possible.

If you use enterprise edition you could possibly use a partition and partition swaps - even with only ONE Partition that at least has some schema lock optimizations. The schema of the table never changes. It has negative implications on statistics, though, before SQL Server 2014.

At the end it really runs down to a non-competent approach that was choosen - those "smart shortcuts" always come back to bite you.


50% CPU usage on MySQL RDS instance (db.m3.medium) with 0 connections We have a MySQL RDS instance which is still utilizing ~50% CPU after stopping our applications and terminating all connections (e.g. from MySQL workbench).

The mysql-error-running.log is showing:

2017-08-03T11:24:05.711179Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4813ms. The settings might not be optimal. (flushed=5623 and evicted=0, during the time.)
2017-08-03T11:26:11.415416Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 11095ms. The settings might not be optimal. (flushed=11538 and evicted=0, during the time.)
2017-08-03T11:33:07.901110Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 15667ms. The settings might not be optimal. (flushed=15298 and evicted=0, during the time.)
2017-08-03T11:33:29.458855Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5004ms. The settings might not be optimal. (flushed=4931 and evicted=0, during the time.)

Can anyone advise on what is causing the persistent CPU usage here? Our applications have been disconnected from the RDS for over 40 minutes and CPU is still averaging 50% usage.

show process list is empty (aside from the connections used when connecting via MySQL workbench)

UPDATE: the RDS was rebooted and CPU dropped back down to expected levels.

A possible reason:

The "Change buffer" queues up index changes for eventual writing to disk.  If you did a lot of INSERTs/UPDATEs/DELETEs on table(s) with non-unique indexes, the CPU could have been busy for some time after the queries "finished".

The reboot may have only coincidentally cleared up the CPU.  The change-buffer work must be done eventually, even after a reboot.


shoud we normalize user profile table with fields like interests,musics,videos,sports, etc? I've read in an article about social networking database schema and saw that they've used varchar for those columns that I mentioned in the question title. It's not normalized! Isn't it better to have a table for sports and then put the foreign keys in user's profile table? In this case we'll have much less redundancy? Please correct me if I'm wrong. Which approach is better have another tables for fav_sports,fav_videos,fav_music, etc or put them all in user's table?

The solution I would choose depends on a few things.  If the number of these columns is relatively low (and not subject of frequent changes) then I would go with the solution you suggested, ie. one table for each attribute and foreign keys on these.  This way you have to define a new table and add a column for every new attribute and modify your queries accordingly.

If this is not your case than EAV suggested by @Zachariha should be considered, but it's worth reading this answer before.  It is very easy to extend (just add a new row to a table) but can go very difficult to maintain - in my opinion mostly because it's hard to follow that who plays in which team.

The scenario you see in your current schema can be the result of either not normalizing data or denormalizing later in order to speed up queries.  Normally, I keep heavily denormalized tables separate from base tables to keep my data safe from violations of referential integrity.


Hint to force locks between 2 Select clauses I have a process that run in parallel on several threads and also on several machines.

In summary, the process has to 'lock' a row in a table named 'Queue' and then retrieves some columns from the locked row and do some other logic operations.

My objective is to do this with the best performance: reliable info and fastest way.

This is how I design the query of the process:

UPDATE  MQ.dbo.Queue
            SET     queue_lock = 1,
                    Queue_Key = 'H10_P19_T0_19032017T193926148',
                    Queue_LockDate = getdate()
            where   Queue_ID = (select top 1 Queue_ID
                                    from MQ.dbo.Queue with (nolock)
                                    where queue_lock = 0
                                    and QueueName_ID = 16
                                    order by queue_id)
            And     isnull(queue_lock,0) = 0

GetQueue (Datasource=mydatabase, Time=1.12 ms, Records=0) in F:\Web\Process\Component\MQ.cfc

Select  top 1 Queue_ID,Queue_Message
            From    MQ.dbo.Queue with (nolock)
            Where   QueueName_ID = 16
                    and queue_lock = 1
                    and Queue_Key = 'H10_P19_T0_19032017T193926148'

The Queue_key consist of:

Host_ID
Process_ID
Thread_ID
Datestamp

This choice (Queue_key) was taken to tolerate parallelism on the process with reliable results.

My problem is that in some cases the second query (Select top 1....) returns 0 records even so it should not, since there are a lot of waiting row in the table 'Queue'.

And the percentage of this case rise when I rise the parallelism.

I know where is the gap in this 2 queries but I search for a way to overcome it and I need your help doing this.

In fact, the gap is in the select clause of the first query (the sub-query): Since select clauses hold only shared lock and 2 shared locks (2 select) don't block each other (are compatible), it is possible that 2 threads running in parallel retrieve the same row and only one update take place (because update hold exclusive lock on the row). So the second query (select top 1 ...) returns result for only one thread.

So I need through a lock hint (maybe updlock or rowlock), to don't allow 2 select clauses to retrieve the same row, without degrading a lot the response time.

Rq: the with (nolock) hint was added to increase the response time since shared lock is compatible with another shared lock (either with this hint or without it).

Can anyone have a suggestion for this type of scenarios?

Have you considered using BEGIN TRANS ... COMMIT TRANS to encapsulate your data?

You can combine that with the WITH (rowlock) syntax:

Begin Transaction Queue1
Begin Try
  update [...]

  Select [...] From [...]
  With (RowLock)

  Commit Transaction Queue1
End Try
Begin Catch
  Rollback Transaction Queue1
End Catch

Select With clause information here.

Exhaustive discussion on SQL Server locking strategies here.


Can and should relational database contain tables that don't have primary keys? So this site says: "All tables in a relational database should have a primary key". Here it also says that every table should have a primary key.

But if I look here and here they say that tables that contain only foreign keys are okay.

So who is right? And which would be the correct answer under a university setting?

I love that your question includes the qualifier "under a university setting." It just highlights that there is a difference in theory and practice.

We have a few tables in production that don't have an indexed primary key and they are lightning fast to read. We do have indexes on the columns that we read on and join from. But in the University, they probably want you to say always include a primary key column.


server trace naming convention I wanted to extract some information from the server trace file that is generated after I send a connect statement to the database. I wanted to know the naming convention for the server trace file. I could figure out that the name is in the below format:

<ORACLE_SID>_ORA_<4_digit_ID>.trc

I wanted to know what is the 4_digit_id that is appended at the end of the trace. As per my research it seems to the process id of the oracle server. I queried the v$process for the pid column, however it seems to be returning multiple values and that too are not in 4 digit. It would be really appreciated if an expert can comment on this.

Is there a way to identify the spid of a failed login attempt? For instance I would like to know the spid for the process when I issue the below command:

sqlplus non_existent_user/random_password

BTW, the server trace is enabled through sqlnet.ora where trace_level_server=16 is set.

PID is the internal (to the database) ID of database processes.

The SPID column contains the OS process ID, that is what you are looking for.


Setting up Log shipping without enabling LS copy job We have a need where we need to set up LS for the servers where we use NAS for backups.

As NAS is shared between Primary and Secondary there will not be a need to use LS COpy job as backups will be duplicated and LS might not work

How can i set up with just LS backup and LS restore configured and make LS a success?

Setup log shipping and disable LS copy job.
As long as you setup backup folder correctly you should be ok.


Ideal environment and cost for running a medium size MongoDb server I know some people will say this question does not belong here but i didnt find a better place on the internet to ask this. Please migrate to an appropriate place if you think it should be.
I am using MongoDB to store product data. The text data(product names,categories,attributes) account for about 30 GB. The images account for about 200 GB. I am paying 184 USD for a quad core processor and 8GB Ram and 500 GB HDD dedicated server(from SingleHop). Is this setup ok for a medium traffic site(5-7K visitors a day ). I have heard that MongoDB best works when it is sharded(because of the global read write lock) but i have never worked with clusters. I have always worked with VPSs. This is my first time with a Dedicated server. Please advise me about the setup and the costs too. I live in India and expect most of my visitors from India.

Thanks.

There really is no "ideal" environment and cost for running MongoDB (or any other database for that matter).  There will be very cheap solutions that will give you enough space, but not enough RAM, there will be middle range options where you have enough RAM most of the time but at busier periods you exceed the memory limitations and the disk is too slow to cope with the increased page fault activity.

As always, it will be a trade of between what you can afford and what is best. In terms of general recommendations:

The cores and space will matter less than your available RAM and whether or not you can keep your working data set (active data plus indexes) in RAM - that is the key to performance.  You won't really be able to tell until you start with real traffic, but if you have decent testing you should be able to estimate it.

I would recommend using MMS to track the stats, it's free and it includes a memory graph that will track your resident memory usage and many other things.

FYI - there is no global read lock and as of 2.2 (release candidate is up for testing as of writing this), the global write lock has been replaced by a database level lock.  Have a look at the relevant concurrency presentations for an in-depth discussion on the 10gen website.

Another thing to make sure of is that you have more than one MongoDB instance, it is highly recommended that you run a replica set (primary, secondary, arbiter minimum) and not a single instance.

Sharding can be used to help scale out horizontally though - that is correct, it allows you to add more resources to your cluster without having to increase the resources available on a particular host.  However, it is not really correct to say that MongoDB runs "best" when sharded - sharding has overheads (you need more servers to run the config DB, mongos processes etc.).

MongoDB runs best when your working set can fit in RAM and your disk subsystems are fast enough to keep up with the amount of data you want to write to disk.  Whether than requires a single replica set or a sharded environment will very much depend on how you use it.


Galera cluster without having to specify all hosts on wsrep_cluster_address While evaluating the galera cluster, on the documentation it mentions that all cluster members should be listed on the "wsrep_cluster_address" setting...

This doesn't seem very scalable to me as adding a new node would mean having to reconfigure all existing nodes.

Is it so? or is there any way of not having to specify all nodes on that variable?

EDIT:

My original answer below is still correct, but the is an easier way to handle this: multicast.  I didn't mention this before because I thought galera could only use multicast for replication, which is probably something else you probably want to configure to save bandwidth with a large number of nodes.  Basically, you can use a multicast address for the cluster connection in wsrep_cluster_address.  Using multicast will allow you to configure a single address on every node.  I'm using multicast for replicating data in my galera cluster, but unicast for the cluster connection.  The page I link to doesn't say anything explicitly about it, but you might need to configure a different multicast address for the galera data replication channel (gmcast.mcast_addr) and the cluster information channel (wsrep_cluster_address).  More information on using multicast with galera is available in a Percona blog post.

Original:

It is not required to have every node in the cluster defined in wsrep_cluster_address.  When a node starts up, it tries to connect to the provided addresses to connect to an existing cluster and then connect to all of the other current nodes in the cluster.  It only really needs to connect to a single node in the cluster to join since it connects to other nodes based on the current state of the cluster.  Because of that, if you specify a few nodes that should always be in the cluster, you should be fine even if there are many other nodes that are not specified.


Wide clustered index vs multiple narrow nonclustered indexes? Say I have a contrived Student table like so:

CREATE TABLE Student (
    Id IDENTITY INT,
    SchoolId INT NOT NULL,
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL
)

Instinctively, I'd make Id the Primary Key (and thus the clustered index). However, I'd find myself searching by SchoolId so I'd make a nonclustered index on SchoolId.

How would this fare against having the Primary Key (and clustered index) to be SchoolId, Id? I will always have the SchoolId if I need to search by Id, so I'll get to use the clustered index anyways, and if I need to search by SchoolId only, the records will be physically next to each other.

If I were to do any type of searching or batch updating, they'd be on SchoolId specific records, e.g. find all kids with name/number/whatever at SchoolId. I'd never do these types of operations across multiple SchoolIds in the same transaction. Does the benefit of having these records physically next to each other make this method much better than simply having a clustered index on Id?

Are there massive downsides to using the latter? I'm still new at this and there's plenty of topics I don't fully comprehend yet (e.g. fragmentation) and how it would factor into a situation like this.

If you will always have SchoolId then you could benefit from making the clustered index a composite key of SchoolId, Id as you won't have to have an additional index on SchoolId to avoid a table scan.  Not having the additional index will let Inserts/Updates/Deletes complete faster as the transaction only has to update one index.

You may find as you create other queries that use the other fields in the where clause that an additional index on those might be beneficial so SQL Server can seek right to that index leaf which will help reduce the number of row returned.


Oracle Subselect Union Multiple Column I want to select and count all cats and dogs at one location;

location | cats | dogs |
new york | 150  | 66   |
paris    | 34   | 12   |

What I've got so far:

SELECT
    cats,dogs
FROM
    (select k.location, count(*) as cats from pet k, location lo where k.location= lo.id and k.pet= 'Cat' group by k.location)
    union
    (select k.location, count(*) as dogs from pet k, location lo where k.location= lo.id and k.pet= 'Dog' group by k.location)

But I only get one column for location and cats...

Try this then instead:

select
  k.location,
  sum((case when k.pet = 'Cat' then 1 else 0 end)) as cats,
  sum((case when k.pet = 'Dog' then 1 else 0 end)) as dogs,
from pet k, location lo where k.location= lo.id and k.pet in ('Cat', 'Dog')
group by k.location
;

I am not sure what you need location lo for, seems you have all the necessary information in pet k.


How is an Index Seek satisfying this query? I'm using the AdventureWorks database and running the following query:

SELECT ct.Name
FROM Person.ContactType ct
WHERE ct.Name LIKE 'Own%'

The execution plan shows that it's using an Index Seek (NonClustered), however, there is only one index on the table; that being the primary key on ContactTypeID.

The table is defined as follows:

/****** Object:  Table [Person].[ContactType]    Script Date: 15/10/2016 20:43:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Person].[ContactType](
    [ContactTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ContactType_ContactTypeID] PRIMARY KEY CLUSTERED
(
    [ContactTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [Person].[ContactType] ADD  CONSTRAINT [DF_ContactType_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for ContactType records.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'ContactTypeID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contact type description.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of GETDATE()' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'CONSTRAINT',@level2name=N'DF_ContactType_ModifiedDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'CONSTRAINT',@level2name=N'PK_ContactType_ContactTypeID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Lookup table containing the types of business entity contacts.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType'
GO

The AK_ContactType_Name index is a unique nonclustered index on the Name field:

CREATE UNIQUE NONCLUSTERED INDEX [AK_ContactType_Name] ON [Person].[ContactType]
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

There are two indexes on this table by default - the primary clustered key, plus the above one.


Reclaim disk space after drop table from database in mysql I have this version of MySQL server:

mysql  Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)

I use a some large tables. When I truncate database with truncate database <name>, I check the hard disk space and I've noticed my disk space is not reclaimed.

This is more information on my server:

SHOW VARIABLES LIKE 'innodb_file_per_table';
'innodb_file_per_table', 'ON'`

Any idea to resolve this ?

You need to "reorganize/rebuild" the table truncated  which can be achieved by below.

mysql> OPTIMIZE table table_name;

for InnoDB tables, it run under ALTER operation.

reference: https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html


Is it possible to execute queries in random order? I have a list of INSERTs, UPDATEs and DELETEs queries.
My problem is that I always receive them in the wrong order (random), and I cannot know their original order.

Are there techniques to group/execute them in a particular order, so that I always end up with the roughly the same end-result as the source, no matter in which order I originally received them?

From your application layer, stamp each query with a timestamp and batch ID. Instead of executing the queries, store them in a temporary table with the format

sql_test | timestamp | batchID

When you know all queries from a batch have been received, kick off a stored proc that retrieves all queries from the batch, orders by timestamp asc and executes them

You'll probably want to add more logic about establishing dependencies between batches and how to deal with failed error,etc


SSIS Merge Join: Delete does not work I've set up a Data Flow Task to sync a flat file with its destination table using a Merge Join with a Left Outer Join as described here (and elsewhere).

Merge Join:

Inserting and Updating works fine, however, deleting a record from the flat file does nothing.

I guess it's something with the Conditional Split: delete ISNULL(gid_csv) && !ISNULL(gid)

or the following SQL Command: DELETE FROM dbo.gusers WHERE gid = ?

The OLE DB Source and the three destinations are accessing the same table.

What do I miss?

The answer is actually pretty easy:

You have to use a Full Outer Join instead of a Left Outer Join in the Merge Join Task.

The referenced tutorials actually told me to do so, I guess I mixed it up with another tutorial that did not include the merge delete.


SQL database stuck in recovery I have SQL server 2017. I have one 3 TB size database there. Somehow due to long running transaction the database got stuck 'IN Recovery' mode after SQL server Restarted. When I checked the sql error logs it says 2 189 255 seconds remaining (Phase 2 of 3) completed which is almost 25 days. My goal is to bring the database online even if I lose some data.

So I've ran below commands but no luck.

  USE [master]
  GO

  RESTORE DATABASE test WITH RECOVERY
  --Msg 3101, Level 16, State 1, Line 6
  --Exclusive access could not be obtained because the database is in use.
  --Msg 3013, Level 16, State 1, Line 6
   --RESTORE DATABASE is terminating abnormally.

  ALTER DATABASE test SET EMERGENCY;
  GO
  --Msg 5011, Level 14, State 7, Line 13
  --User does not have permission to alter database 'DragonDriveConnect',
  the database does not exist, or the database is not in a state that
  allows access checks.
  --Msg 5069, Level 16, State 1, Line 13
  --ALTER DATABASE statement failed.

  DBCC CHECKDB (DragonDriveConnect, REPAIR_ALLOW_DATA_LOSS) WITH
  ALL_ERRORMSGS;
  GO
  --Msg 922, Level 14, State 1, Line 22
  --Database 'DragonDriveConnect' is being recovered. Waiting until
   recovery is finished.

At last I've tried to Delete the database too but that is also not working and giving me error saying Cant Delete.

How do I get out of this situation?

The error you're seeing in the SQL Server Error Log is this one:

  Recovery of database 'CrashTestDummy' (9) is 0% complete (approximately 42 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required

More generically, it will say:

  Recovery of database '{Database Name}' ({Database ID}) is {N}% complete (approximately {N} seconds remain). Phase {N} of 3. This is an informational message only. No user action is required

Because your database was not shut down cleanly on restart, the database must go through "crash recovery". This is required to ensure the database remains consistent. When the database is not shut down cleanly, SQL Server must make sure that transactions written to the transaction log have been reconciled properly against the data files.

All transactions are guaranteed to be written to the transaction log. However, updating data is initially only done in memory. Updates to the physical data files are done asynchronously via a checkpoint. The asynchronous nature of the data file updates are why a crash or unclean shutdown requires extra work on startup.

As indicated by the error message, there are three phases to the recovery. Each of these is essentially a pass through the transaction log:

Analysis
Redo / Roll Forward
Undo / Rollback

Analysis

This phase is simply to review the transaction log & determine what needs to be done. It will identify when the most recent checkpoint was, and what transactions might need to be rolled forward or back to ensure consistency.

Redo / Roll Forward

Completed transactions from the transaction log need to be reviewed to ensure the data file has been completed updated. Without this, changes that were only in-memory might have been lost.

This phase will take those transactions that were committed after the most recent checkpoint and redo them, to ensure they are persisted to the data file.

If you are using SQL Server Enterprise edition, Fast Recovery will allow the database to come online & be available after this phase of recovery. If you are not using Enterprise Edition, the database will not be available until after the Undo phase has completed.

Undo / Rollback

Transactions from the transaction log that were rolled back, or were uncommitted at the time of "crash" must be rolled back. SQL Server must verify that if uncommitted changes were made to the data file, they are undone. Without this, a rolled back change could be partially committed, violating the ACID principles of the database.

This phase will perform the rollback of any transactions that were uncommitted at the time of crash, or were rolled back after the final checkpoint.

So what can you do about it?

While the database is in recovery, attempts to bring the database online via a RESTORE command like this will fail:

RESTORE DATABASE CrashTestDummy WITH RECOVERY;

SQL Server is already attempting to do this. The RESTORE...WITH RECOVERY; will simply put the database through the exact same steps in order to bring the database online in a consistent manner.

Be patient

The right thing to do is just be patient. This part of the message from the error log is the one that you should pay attention to:

  No user action is required

Note, too, that the time remaining is an estimate. In my experience, it is wildly inaccurate. Sometimes the time remaining will grow larger, rather than reducing. Sometimes it will report a very long completion time, and suddenly complete very fast. Its just an estimate.

Can you just "throw away" your transaction log and start fresh?

I advise against it. I would suggest you never, ever do this with a production database. There is a procedure to attach a database without a transaction log, and ask SQL Server to ATTACH_REBUILD_LOG. I won't detail all the steps, but the "punchline" for that procedure is to do this:

CREATE DATABASE CrashTestDummy
ON (FILENAME = 'C:\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\CrashTestDummy.mdf')
FOR ATTACH_REBUILD_LOG;

Running this on a crashed database may result in this error:

  The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
  Msg 1813, Level 16, State 2, Line 5
  Could not open new database 'CrashTestDummy'. CREATE DATABASE is aborted.

In which case, you're stuck. You'll need to use the original transaction log & be patient. Just wait for it to recover.


SQL Server 2016 Log shipping restore job stuck SQL server 2016 log shipping restore job will run every 15mins, Database size is 1TB, Job will not take two minutes, Yesterday job was running for last 13 hours and the database is in "InRecovery" state, The job status is InProgress, couldn't see any blocking on the server and SPID of the restore is in suspended state, anyhow killed the restore job reconfigured the log shipping, I just want to know the possible reasons to create RCA to this issue, I do not see any info in event or SQL error logs since i killed the job manually.We do not have IO or storage issues also.

It probably could be for various reasons-

Compare the volume of data that might would have generated additional log backup size as compared to normal days. Compare the difference between good and bad durations vs amount of data u see.
I am not sure what is the recovery mode- do you allow secondary in read mode by keeping standby ? If yes I have seen issues and slowness based on LS logic to keep switching between no recovery mode and standby mode.
You need to have baseline for other metrics like CPU memory and storage numbers for good vs bad run.


Restoring database always throwing exception I am trying to restore some database backup file into the same database. The .bak file is creating problem always. When I select the .bak file from a network drive like "\\TestPC\ShareFolder\test.bak" then its working correctly.

But the same .bak file if I select from a local drive by copying it to my pc, say C:\TestPath\test.bak then for every time its throwing a exception saying

  Cannot open backup device 'C:\TestPath\test.bak'. Operating system error 3(The system cannot find the path specified.).
  RESTORE DATABASE is terminating abnormally.

I don't know why its throwing, I also changed the SQL Server log on from a Network drive to a local connection also. Still its not working. Can some one hint me how to solve this problem?

If you want to restore a *.bak file onto a machine e.g. TESTSERVER, then that *.bak file needs to be

on a local drive of TESTSERVER, or:
on a share in the network that TESTSERVER can reach (a "public" share)

So if you have the *.bak file on your local PC MyPC, you can try to do a restore from \\MyPC\C$\ShareFolder\Backupfile.bak and this might or might not work.

But you cannot use C:\ShareFolder\Backupfile.bak since that will go to the server's C:\ drive and look for that file - and thus you get the error message and it cannot be found.

After all: your SQL Server instance is running on TESTSERVER - so if that process there executes a restore from the C:\ drive, that'll be the C:\ drive on that server machine - not your local PC! If that server process on TESTSERVER can reach your machine, then you might use the UNC path notation to your local disk - so that might work.


Multi language full text search using postgresql I am trying to implement full text search using postgresql for some images. I am storing some information about my images into a json field in my table. This json has a tags key where I have multiple languages, each one with tags (keywords), something like this:

"tags": {
    "en": ["blue female", "red female"],
    "es": ["hembra azul", "hembra roja"]
}

At this moment I don't have a clear idea how to store the tsvector considering that I have more languages.

One initial idea was to concatenate all those tsvectors into a single one and store it on a column in my table.

The second idea would be to make a different column for each language and store the corespondent vector into that column.

Which on would be the better one?
There is maybe another better approach?

You should definitely use a different column per language.

The main reason is that different languages have different stop words and stemming rules, so if you index something with to_tsvector('spanish', ...), you will not always find it with a to_tsquery('english', ...) and vice versa:

SELECT to_tsvector('spanish', 'hembra azul') @@ to_tsquery('english', 'hembra');
 ?column?
----------
 f
(1 row)

Even better would be not to create a column per language, but only GIN indexes on to_tsvector('english', (tags->'tags'->'en')) and to_tsvector('spanish', (tags->'tags'->'es')). For example:

CREATE TABLE images (
   id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   image bytea NOT NULL,
   tags jsonb NOT NULL
);

CREATE INDEX images_tags_en_idx ON images
   (to_tsvector('english', (tags->'tags'->'en')));

CREATE INDEX images_tags_es_idx ON images
   (to_tsvector('spanish', (tags->'tags'->'es')));

Then you can use the first index with

SELECT * FROM images
WHERE to_tsvector('english', (tags->'tags'->'en'))
      @@ to_tsquery('english', 'female');