extension "pg_buffercache" already exists and relation "pg_buffercache" does not exist When I try to create the extension pg_buffercahe using the following command:

➤ psql://postgres@postgresql-95-2:5432/postgres

CREATE EXTENSION pg_buffercache;

...it returns the error:

ERROR:  extension "pg_buffercache" already exists

If I try to retrieve information from the pg_buffercahe using the following command:

➤ psql://postgres@postgresql-95-2:5432/postgres

select count(*) from pg_buffercache where not isdirty;

...I receive the error:

ERROR:  relation "pg_buffercache" does not exist

The following packages are installed on my system:

yum list postgresql95*
Installed Packages
postgresql95.x86_64         9.5.10-1PGDG.rhel7
postgresql95-contrib.x86_64 9.5.10-1PGDG.rhel7
postgresql95-libs.x86_64    9.5.10-1PGDG.rhel7
postgresql95-server.x86_64  9.5.10-1PGDG.rhel7

How can I fix this issue?

➤ psql://postgres@postgresql-95-2:5432/postgres

# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description
--------------------+---------+------------+-----------------------------------------------------------
 pg_buffercache     | 1.1     | public     | examine the shared buffer cache
 pg_stat_statements | 1.3     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

➤ psql://postgres@postgresql-95-2:5432/postgres

# select count(*) from pg_buffercache where not isdirty;
ERROR:  relation "pg_buffercache" does not exist
LINE 1: select count(*) from pg_buffercache where not isdirty;

The extension was created in a schema which is not in your search_path.  So you can't create another copy of it, but also can't find the view.  You can use \dx to find what schema it was installed to.


Optimal drive configuration for SQL Server 2008R2 I have a fairly busy database server running SQL Server 2008 R2 that has the following setup:

SATA RAID 1 (2 Drives)  - OS / Programs
SAS  RAID 10 (4 Drives) - Sql Database Files (data and logs)
SAS  RAID 1 (2 Drives)  - TempDB (data and logs)

Assuming I can't add additional drives into this server, have I made the best use of the configuration I have available? Or should I consider another scheme here where logs are isolated from the data files, for example?

Update:

For those that requested further hardware details:

The SATA drives (used for the OS / Program partition) are: WD 7200 RPM 3 Gb/s 3.5 Inch SATA
The SAS drives used in the other arrays are: Seagate 15K RPM 6 Gb/s 3.5 inch SAS
The RAID controller used is an: LSI 9260-8i SAS/SATA 6 Gb 8 port

Update 2:

Based upon the feedback I've received, it looks like I have the following viable options to choose from - I will award the bounty to someone that can tell me which is likely to be the best in the environment that I've outlined:

Leave everything as is - I probably won't do much better
Move my 2 SAS RAID 1 drives into my existing RAID 10 array to have it composed
of 6 disks in total
Move my log files onto the SAS RAID 1 and/or
relocate TempDB (data or logs) back to the RAID 10

Variants of this question come up semi-regularly:

SQL Server build
Help me choose a RAID level combination for a SQL Server 2008 instance
How should I configure these disks on a SQL Server for a BI configuration?

There are also occasional bun fights about the data/log separation "best practice".

Placing Transaction Log on Separate Volume [solid state]?
SQL Server - Separating data, log, and TempDB files on a SAN

Without more detailed analysis of what this server is doing, the same advice applies as given previously.

RAID 1 for OS
RAID 10 (6 disk) for data/logs/tempdb

There is rarely any point in a split with so few spindles available. A single array with a larger IOPs capacity will typically soak up the lumps and bumps of your workload better than 2 smaller arrays.

One variant that can be worth testing is putting tempdb on the OS drive. Only do so if you have a representative workload that you can replay repeatedly, to ensure a fair comparison of the configuration. If you go for this arrangement in production make sure tempdb growth is restricted so you don't inadvertently consume all free space on the OS drive.

Given that your OS drives are 7200RPM coasters, I'd be surprised if the tempdb on OS drive config bore any benefit.


Postgres Ordering by a Json Array I have the following json that I am storing in a jsonb field. For every record representing a product, it contains various categories and in each category a is priority level.

[
      {
        "priority": 0,
        "description": "",
        "categoryName": "Cars",
        "title": ""
      },
      {
        "priority": 1,
        "description": "",
        "categoryName": "Trucks",
        "title": ""
      },
      {
        "priority": 2,
        "description": "",
        "categoryName": "Vans",
        "title": ""
      }
    ]

I would like to order the data by various categories dynamically using the priority field such that, if I am displaying all the products in the category Cars, I will order by the 0 array element, if I am displaying all the products in the category Vans, I order by the 2 array element.

Currently, to order by the first array element I am able to do so:

ORDER BY OBJECT_DATA->'productCategories'->0->>'priority'

However, I would like to order by first selecting the the array value (categoryName) and then using the array value to select its priority.

As an example: order by Trucks->priority (1)

I am using PostgresSQL 9.6.

Assuming from your code snippet that the displayed JSON is the value of a key "productCategories" in a jsonb column object_data of your table tbl, you can achieve your desired sort order like this:

SELECT t.*
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT elem->>'priority' AS prio
   FROM   jsonb_array_elements(t.object_data->'productCategories') a(elem)
   WHERE  elem->>'categoryName' = 'Trucks'  -- your category here
   ) a ON true                              -- preserve all rows from t
ORDER  BY prio;                             -- NULL values are sorted last

Related answers with detailed explanation:

How to sort objects in an array inside a json or jsonb value by a property of the objects?
Customize jsonb key sort order involving arrays
What is the difference between LATERAL and a subquery in PostgreSQL?
PostgreSQL sort by datetime asc, null first?


SQL Server Management Studio 2014 Backup - Set Property Devices Error I recently upgraded my local SQL Server to Version 2014, which also installed the SSMS 2014.
When using SQL Server Management Studio 2014 to create a backup (both local and remote, Server Versions 2008 - 2014), I get the following error:

  To accomplish this action, set property Devices.

In the dropdown I chose Disk and I've also chosen a path for the backup. The path exists on the server and I've got full permission on this path.
If I use the old SQL Server Management Studio 2012 with the same settings, the backup works without any issues.
It also works using T-SQL BACKUP DATABASE SOMEDATABASE TO DISK = 'D:\SomeDir\SomeDataBase.bak' WITH COMPRESSION

So the issue has to be specific with the Management Tools Version 2014.
The only solutions I found on the internet were due to missing path, which isn't the case for me.
Has anyone experienced and/or solved this issue?

Edit: Added Screenshot (UI in German)

Translation:
- Vollständig: Full
- Datenträger: Disk

Media and security options unchanged.
Master database was just taken as example, happens to every other database.

Update 21.07.2015

The problem seems to be related to me using an English language pack on a German Windows and a German SQL Server 2014 installation.
If the Windows display language is set to German, the issue disappears.
If the Windows display language is set to English, the issue reappears.

Thanks for all contributions.

Here is my Answer for Error message "To accomplish this action, set property Devices. (Microsoft.SqlServer.SmoExtended)"

Make sure the User Account you are logged in as has DBO rights on both, the MASTER Db and the DB you want to backup.
Using the SQL Express Manager, Expand the SQL Server Objects in the left pane and find "Server Objects" then click on "Backup Devices"

Right Click and create a "New BackUp Device.." and call it the same as the DB to be backed up... pointing to a directory on your server (e.g.: C:\MySQLBackups...)

This will create an "empty file" with the .bak extention. (e.g. C:\MySQLBackups\DatabaseName.bak)

Then right-click on this new device created and select "Backup database"...

The rest is straight forward...

This worked for me... Good luck!"


db.serverStatus() got not authorized on admin to execute command My mongo version : 2.6.11
I want to  check db.serverStatus() in my robomomgo
But got errmsg:

{
    "ok" : 0,
    "errmsg" : "not authorized on admin to execute command { serverStatus:   1.0 }",
    "code" : 13
}

How can I add authorization  ??

I find a command can create user with authorization :

db.createUser(
    {
      user: "tester",
      pwd: "password",
      roles: [
         { role: "readWrite", db: "test" }
      ]
    }
);

But I don't know which is db.serverStatus() 's database name??
How can I authorize it??

You need to grant the clusterMonitor role to the user running  the db.serverStatus command. You can grant this role to a user by calling the `grantRolestoUser.

Here's a list of other clusterMonitor roles

use admin

db.createUser(
    {
      user: "clustermonitor",
      pwd: "password",
      roles: [
         { role: "readWrite", db: "admin" }
      ]
    }
);

db.grantRolesToUser(
  "clusterMonitor",
   [
     { role: "clusterMonitor", db:"admin"}
   ]
);


How to drop inheritance? I am new to PostgreSQL. The situation I have is someone created a child table inherits from the parent table. And dropped the child table. But the parent table still has the "down arrow" sign on it.

I checked and there's no other link/relation on the parent table. Is this really an issue?

I was told that the parent table is still in the 'been inherited' status and causing the performance issue. How to resolve this? By removing the 'been inherited' status' from the parent table?

In addition to what @Josh already clarified, this quote from the manual should answer the question in the title:

  [...] an inheritance link can be removed from a child using the NO
  INHERIT variant of ALTER TABLE.

ALTER TABLE child NO INHERIT parent;

Either that or just delete the child table.

Also, you can include all inheriting tables when dropping the parent table using the CASCADE key word:

  A parent table cannot be dropped while any of its children remain.
  [...] If you wish to remove a table and all of its descendants, one
   easy way is to drop the parent table with the CASCADE option.

You can invert this to take advantage of the behaviour and debug your situation. Start a transaction and try to drop the parent table:

BEGIN;
DROP TABLE a;

ERROR:  cannot drop table a because other objects depend on it
DETAIL:  table b depends on table a
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The error message lists inheriting tables that block the DROP command.

Unless you get an error (aborting the transaction automatically), be sure to issue a ROLLBACK if you don't actually want to delete the table:

ROLLBACK;


MySQL: Split column by delimiter & replace numbers with values from another column Using MySQL, split column by delimiter & replace numbers with values from another column.

I have a column named 'path' having paths like '/426/427/428'.
I would like to replace category numbers with category names.
Result would be like '/Computers/Other accessories/Laser printers'.

This mysql query seems to be a good start, but don't know how to take column values from 'categories' table, 'path' column.

select distinct
  SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', numbers.n), '/', -1) name
from
  (select @rownum := @rownum + 1 as n
        from categories
        cross join (select @rownum := 0) r
    ) numbers
where n > 0
order by
   n

Now this query splits the string correctly to:

426
427
428

Next would be to have a result like:

id    name
426   Computers
427   Other accessories
428   Laser printers

Finally should merge the 'name' column to

'/Computers/Other accessories/Laser printers'

string.

Thanks for your help in advance!

I got the following query:

CREATE TEMPORARY TABLE IF NOT EXISTS
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp
    GROUP BY catid
    ORDER BY
       n

);

SELECT * FROM t1;
DROP TEMPORARY TABLE t1;

Result is:

*nr*   *catid*   *name*   *path*
1   426   Computers   /426
2   427   Other accessories   /426/427
3   428   Laser printers   /426/427/428

This is almost good, just need to merge the name column with delimiters.

So final query is:

CREATE TEMPORARY TABLE IF NOT EXISTS
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp
    GROUP BY catid
    ORDER BY
       n

);

/* SELECT * FROM t1; */
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;
DROP TEMPORARY TABLE t1;

We get the following query result:

path_long
/Computers/Other accessories/Laser printers


Always On Instance Service Broker/Database Mirroring transport connection endpoint Error I have a production instance that has 2 AOG. There is no mirroring configured for both groups. Both groups are also in 'Healthy' state.
Upon checking the error log on SQL Server, I noticed that starting from 12/19/20 there has been this error that started at 9:03PM. I went through an dug in further to see that there is a pattern. Every night around 9:03-9:04pm the same error is happening. This does not occur during normal business hours.
Error msg:
An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')`
My thought was to stop and restart the endpoints, but I don't think that would be necessary in this case.
Anyone have any thoughts as to why it might be happening on a daily basis around the same time, and how I may be able to solve this error?
Thanks in advance.

Do you have in place read-only routing?
Try to review read-only routing URL in the Availability Group Properties / Read-Only Routing.
Maybe the connection at that time is using this feature and the routing URL for the secondary is not right.
Eventually try to verify what encryption algorythm you are using.
SELECT name ,
type_desc ,
state_desc ,
role_desc ,
is_encryption_enabled ,
connection_auth_desc ,
encryption_algorithm_desc
FROM sys.database_mirroring_endpoints;

Usually AES. Veryfy if you are using the same algorythm in both node.


function to check whether the current server is involved in an availability group and if so, whether it is the primary I used to have 2 servers on an availability group, but there was a problem with one  of them servers involved,while patching, and to make a long story short I removed all the databases from the availability group.

The way I managed execution of jobs only in the primary server was mainly through this script which tests whether the database is in the primary replica:

  If sys.fn_hadr_is_primary_replica ('apcore') =1
BEGIN

      EXEC [APCore].[app].[usp_upd_applicationStatusTimeExpired]

END

But now that the current server is the only server in the Availability group,
this script

  select sys.fn_hadr_is_primary_replica ('apcore')

retuns null, and therefore is not accurate.

I have been developing a function that should return a bit 1 in one of the following cases:

1 - we are not part of an availability group

2 - we are part of an availability group and we are the primary server

The question is:

Would this function work for a distributed availability group?
Is there any other situation that I haven't thought of that might prevent this function to return the expected value?

and here is the function:
the function is to be used inside jobs and check whether we should run the job (either because we are in an availability group and we are the primary, or we are not in an  availability group at all).

--=========================================
-- scalar-valued function dbo.check_HADR_status
-- returns 1 when either primary or we are standalone
-- (not part of an availability group)

-- USAGE:
-- SELECT MASTER.dbo.check_HADR_status()
--=========================================

USE MASTER
GO

IF OBJECT_ID (N'dbo.check_HADR_status') IS NOT NULL
   DROP FUNCTION dbo.check_HADR_status
GO

CREATE FUNCTION dbo.check_HADR_status()
RETURNS BIT
WITH EXECUTE AS CALLER
AS
BEGIN
    RETURN (SELECT CASE WHEN EXISTS (select * from sys.availability_replicas) THEN
                            CASE WHEN  (   SELECT COALESCE(a.role_desc,'RADHE')
                                             FROM sys.dm_hadr_availability_replica_states AS a
                                             JOIN sys.availability_replicas AS b
                                             ON b.replica_id = a.replica_id
                                             WHERE b.replica_server_name = @@ServerName) LIKE 'PRIMARY' THEN 1
                                 ELSE 0
                            END
                  ELSE 1
                  END)
END
GO

IF (SELECT MASTER.dbo.check_HADR_status()) = 1
   BEGIN
      PRINT 'RUN THE JOB STEP'
   END
ELSE
   BEGIN
      PRINT 'DON''T RUN THE JOB AS WE ARE IN A SECONDARY REPLICA'
   END

We use Distributed Availability Groups (and some servers that are just in a straight AG) and ran into a similar issue.  We eventually settled on this query as our end all, be all solution. It's probably more complicated than it needs to be, but it does work.

DECLARE @DBName sysname;
DECLARE @IsPrimary BIT = 0;

--Determine if the database selected is online.
;WITH CTE_DAG
AS
    (   SELECT AG.[name] AS DAGName
               , AG.is_distributed
               , AR.replica_server_name AS UnderlyingAG
               , ARS.role_desc
        FROM sys.availability_groups AS AG
            INNER JOIN sys.availability_replicas AS AR ON AR.group_id = AG.group_id
            INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = AR.replica_id
        WHERE AG.is_distributed = 1)
     , CTE_LocalAG
AS
    (   SELECT AG.[name] AS LocalAGName
               , AG.is_distributed
               , AR.replica_server_name AS UnderlyingAG
               , ARS.role_desc
               , D.[name] AS DatabaseName
               , DRS.is_primary_replica
        FROM sys.availability_groups AS AG
            INNER JOIN sys.availability_replicas AS AR ON AR.group_id = AG.group_id
            INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = AR.replica_id
            INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS ARCS ON ARCS.group_id = AG.group_id
            LEFT OUTER JOIN sys.dm_hadr_database_replica_states AS DRS ON DRS.replica_id = ARCS.replica_id
                                                                           AND DRS.group_id = ARCS.group_id
            INNER JOIN sys.databases AS D ON D.database_id = DRS.database_id
        WHERE AG.is_distributed = 0
            AND ARCS.replica_server_name = @@SERVERNAME)
     , CTE_Composite
AS
    (   SELECT L.DatabaseName
               , L.role_desc
               , L.is_primary_replica
               , COALESCE(D.role_desc, 'NONE') AS DAG_Role
               , IsAllPrimary = CASE WHEN L.is_primary_replica = 1
                                         AND COALESCE(D.role_desc, 'NONE') IN ('NONE', 'PRIMARY')
                                          THEN 1
                                    ELSE  0
                                     END
        FROM CTE_LocalAG AS L
            LEFT OUTER JOIN CTE_DAG AS D ON D.UnderlyingAG = L.LocalAGName
        WHERE L.DatabaseName = @DBName)
     , CTE_Grouping
AS
    (   SELECT DatabaseName
               , SUM(IsAllPrimary) AS TotalPrimary
               , COUNT(DatabaseName) AS TotalCount
        FROM CTE_Composite
        GROUP BY DatabaseName)
SELECT TOP (1)
       @IsPrimary = 1
FROM CTE_Grouping
WHERE COALESCE(TotalPrimary, 0) = COALESCE(TotalCount, 0);


What is the default size of text field in Postgres? In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length.

I wonder what is the default size of this field (or minimum size of the field)?

The default minimum size of a text column (representing a NULL value) depends on the rest of the row. In most cases it is absolutely free, if the NULL bitmap has room for another bit.

Does not using NULL in PostgreSQL still use a NULL bitmap in the header?

More about calculating the effective row size:

Configuring PostgreSQL for read performance


SQL server disk write queue dramatically grows sometimes I have SQL Server 2008 and a single database with full recovery model. Usually, the queue length is less than 1. But sometimes it grows up to several thousands (3000!!) for a few seconds. At this time many of write queries ends up with timeout error.

Using Resource Monitor, I found that at this moment sqlserver.exe writes a large amount of data to the main database file (MDF). Thoughh usually it writes to transaction log (LDF).

Using SQL Server Profiler, I found that heavy queries are not running at that moment.

I think, that it is some kind of SQL server's background operation, but I wonder what kind?

Database also has READ_COMMITED_SNAPSHOT ON and the mirroring (synchronous mode) enabled. Can this fact be the cause of my issue?

UPDATE:
I found that writing to log (not to data file) is default behavior of Full recovery mode. And log can only be copied to data file by Backup Transaction Log operation. Still don't understand why SQL server copying log every ten minutes...

Sounds like you have:

A very crappy IO subsystem.
A checkpoint occurring.
Crappy IO subsystem + checkpoint = high disk queue.

  I found that writing to log (not to data file) is default behavior of
  Full recovery mode. And log can only be copied to data file by Backup
  Transaction Log operation.

No, not really. Your information or understanding is at fault.

Regardless of recovery model, SQL Server uses write-ahead-logging (WAL). An update/delete/insert results in a change to the in-memory data page + a record of the change written to the transaction log. The data page modification is not immediately flushed to the data file. To all intents and purposes you can consider the log write to disk to be immediate (beneath the covers the log is flushed every 60K or on a COMMIT).

What you are seeing when the IO spikes is those data page modifications being flushed to disk as a result of a checkpoint operation.

  A checkpoint writes the current in-memory modified pages (known as
  dirty pages) and transaction log information from memory to disk and,
  also, records information about the transaction log. [Source]

Relevant past questions that may help you understand the mechanics:

SQL Server Cache Flush and Disk I/O
When do you remove entries from a write-ahead log (WAL)?


Completion of ERD, uncertainties about generalization/specialization and primary keys I'm practicing making conceptual databases and I would like to know of I'm on the right track. I've been trying to create an ERD for a scenario as follows:

A hotel can take bookings for weddings, conferences and theme nights, as well as regular regular rooms. All bookings have a date, time, booking code, name of staff member that took the booking, deposit paid and payment method.

Weddings bookings record the name of bride and groom, as well as client info (full name, address, phone number and email), who is making the booking and the date of the wedding reception. Number of guests, food menu (3 different alternatives), if the reception is in the evening, if it's open bar and if a photographer is needed. There is two available suites to hold the weddings, Plaza or Ballroom.

Conference bookings require the same client info, as well as name and address of the organisation that holds the conference. Number if participants and which food menu (4 different choices) is desired is also recorded. A conference have access to the same venues as weddings, but can book more than one. There is also 5 smaller conference rooms that can be booked. These can hold 15-20 people, have 2 different layouts and some contain electronic facilities and some have tea/coffee facilities.

Theme nights record the same client details, the number of participants and have access to one of the two venues.

Clients can also book individual rooms, either single or double sized room and most have en-suite facilities. The same client details are stored for this booking as well.

What I have managed to model so far is the one below, am I on the right track here?

I know I am missing cardinality, mainly because I haven't figured out any nice way to do it in draw.io which I'm using at the moment.

I feel rather unsure on how to use the generalization/specialization (the arrows), is this done in a good way?

EDIT: I´ve updated the ERD with Primary keys but I'm not very sure I'm taking the right approach with it...

EDIT 2: I have made some changes to the ERD with some consideration on the answers for this questions. I know some of the issues raised still are there, the one I mainly would consider is combining Meeting_Room and Suite but I'm not sure how to do it while keeping the constraint of the meeting rooms only being available to the conferences.

One thing that got misinterpreted was the Booker entity due to a mistake from my part. It didn't refer to the staff member taking the booking, but the person that did the booking.

  "Weddings bookings record the name of bride and groom, as well as client info (full name, address, phone number and email), who is taking making the booking and the date of the wedding reception."

I removed this entity and put it as a field referring to Person instead as the only relevant information about the person taking the booking is the name (for this scenario).

I think you're creating more tables than necessary.

First, why do you need a table for "bridal couple"? Why not just include the bride and groom fields in wedding_booking table? The only reason to break this out into a separate table would be if you expected to book multiple weddings for the same couple, which seems rather unlikely. If they're getting re-married, it's probably not a second wedding to each other.

Why have tables for "bride" and "groom"? All these do is serve as an extra stepping stone to get from bride and groom id's to their names. Why not just have wedding_booking have fields for bride_name_id and groom_name_id?

Why is name separate from client? Why not just put the name fields in the client table? Okay, you also use name for the employee who does the booking. Really you need an employee table and a client table, with each including a name. With just two fields making up the name, there's little gained by breaking this out into a separate table.

I don't see any reason for room_booking to be a separate table. Booking should simply point to a room.

"Plaza" and "Ballroom" are not fields in suite. They are two different records. Maybe there's "description" that can have the value of "plaza" or "ballroom".

If I understand your intent, every wedding_booking must be linked to a booking. So there's no need to have a client_id in wedding_booking as it's already in booking.

Likewise staff_member in booking appears to be the same thing as booker_id in wedding_booking. It belongs just once, in booking.

Participants, delegates, and guests all appear to be the same idea: how many people are attending this event. I'd combine these into a single field in event_booking.

If a conference can use either a meeting room or a suite, and possibly multiples, it gets awkward to break these into two tables. I'd combine meeting_room and suite into a single table. If weddings and theme nights can book meeting rooms, fine, add a flag in there that says which it is, and then suites are available to all three types of events while meeting rooms are available only to conferences.

Many database designers would challenge me on this, but: I'd seriously consider combining theme night, conference, and wedding into a single table and having a type flag that indicates which it is. Most of what you need to record for these events is the same: how many people are coming, what room or rooms are they using, who's name is it under. A few fields only apply to one or the other, like conferences probably don't have flowers. But if somebody organizing a conference said they wanted to have flowers, would you tell them they can't because it's not a wedding? A conference or a theme night could have an open bar, I would think. Etc. My point is, there's no point limiting the services your hotel can offer to just certain types of events because you think they are unlikely to be wanted at other types of events -- unless there's really some reason why you just can't do it, legal restrictions or something. Also, if you later come up with another type of event you might book -- a birthday party or a bar mitzvah or a politician's victory party or whatever -- you just add another type, you don't have to struggle with creating a whole new table. You can even toss in an "other" type to allow for it so you don't have to scramble to reprogram the first time it happens.

Well, that's probably enough comments for the first round. :-)


Commit and rollback in Oracle stored procedure In my Oracle stored procedure, I have multiple insert and update statements like below:

create or replace PROCEDURE SPTest
AS
BEGIN
  insert into emptest(empid,empname,deptno)
  (1,'ravi',10);

  insert into test1(id,name,sal)
  (1,'raju',4444);

  update emptest set empname='hari' where empid=1;
END;

If I get any error in any statement, I want to rollback all insert and update statements.

How can I implement commit and rollback in this stored procedure?

You can do this by using a save point.
CREATE OR REPLACE PROCEDURE SPTest
AS
BEGIN
   -- We create a savepoint here.
   SAVEPOINT sp_sptest;

   INSERT INTO emptest(empid, empname, deptno)
   VALUES(1, 'ravi', 10);

   INSERT INTO test1(id, name, sal)
   VALUES(1, 'raju', 4444);

   UPDATE emptest
   SET empname = 'hari'
   WHERE empid = 1;

-- If any exception occurs
EXCEPTION
   WHEN OTHERS THEN
      -- We roll back to the savepoint.
      ROLLBACK TO sp_sptest;

      -- And of course we raise again,
      -- since we don't want to hide the error.
      -- Not raising here is an error!
      RAISE;
END;

The commit will typically be left to the caller. This construct just guarantees that either all of the inserts and the update are done, or none of them is.
On the most outer level, Oracle will do a rollback by itself. In other words, if you do EXEC sptest(); on the SQL+ command line, it will be expanded automatically to
DECLARE
BEGIN
   sptest();
EXCEPTION
   ROLLBACK;
   RAISE;
END;

But if a calling procedure has exception handling, this doesn't happen, since the exception may not get to that outer level. So you may end up with the inserts done, the update throwing an exception which may be caught and handled by the caller, so that the update will have failed but the inserts succeeded.
By doing a rollback in the stored procedure to the beginning of the procedure, you ensure that all of them succeed, or none of them do.


How to sync MySQL database between two servers I need to sync a MySQL database between two servers.

If a new record will be added in server A, then server B fetch new record.

If a new record will be added in server B, then server A fetch new record.

You should probably go for Master-Master Replication .

http://www.howtoforge.com/mysql_master_master_replication


How to run procedure in Oracle SQL developer Hello I have a table named CITY in oracle SQL developer, my problem is how to run the procedure

-- start the script

SET SERVEROUTPUT ON
SET LINESIZE 400
SET TIMING ON

CREATE OR REPLACE PACKAGE BODY hotel AS

    -- -----------------------------------------------------
    -- Table city
    -- -----------------------------------------------------
    PROCEDURE fill_city(number_city NUMBER) IS
        city VARCHAR2(100);
        postna_st VARCHAR2(4);

    BEGIN
        FOR st IN 1..number_city LOOP
            city:= dbms_random.string('a',100);
            postal_number:= dbms_random.value(1000,9000);
            INSERT INTO CITY(city, postal_number) VALUES (city, postal_number);
        END LOOP;
    END;

BEGIN
    NULL;
END hotel;
/

SHOW ERRORS;

creating the procedure

CREATE OR REPLACE PACKAGE hotel AS
    PROCEDURE fill_city(number_city NUMBER)
END hotel;
/

SHOW ERRORS;

and now how to execute the procedure?

--EXECUTE fill_city(10000) ;
Begin
 fill_city(10000);
End;

I tried both but with luck.

I recived the following error

 Error report:
ORA-06550: line 2, column 2:
PLS-00201: identifier 'fill_city' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
Elapsed: 00:00:00.018

First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure.

begin
  hotel.fill_city(10000);
end;
/

should correctly invoke your procedure.

Second, you have issues with the naming of your local variables.  Normally, you would not create local variables like city and postal_number that are the same as the names of columns in tables in your database.  That makes it far too easy to introduce errors in your code where you intend to refer to the local variable but scope resolution rules mean that you are really referring to the column name.  For example, if you write the perfectly valid function

CREATE OR REPLACE FUNCTION get_dname( deptno IN NUMBER )
  RETURN VARCHAR2
IS
  dname VARCHAR2(30);
BEGIN
  SELECT dname
    INTO dname
    FROM dept
   WHERE deptno = deptno;
  RETURN dname;
END;

in your WHERE clause, both references to deptno will resolve to the column in the dept table, not to the parameter deptno.  That means that no matter what value you pass in to the function, the SELECT statement will return every row from the table and, thus, throw a too_many_rows error.  Normally, you would come up with a convention on how to name variables and parameters that would not conflict with your table naming conventions.  Prefixing parameters with p_ and local variables with l_ is one common convention.  That turns our function into something like this

CREATE OR REPLACE FUNCTION get_dname( p_deptno IN NUMBER )
  RETURN VARCHAR2
IS
  l_dname VARCHAR2(30);
BEGIN
  SELECT dname
    INTO l_dname
    FROM dept
   WHERE deptno = p_deptno;
  RETURN l_dname;
END;

The other option would be to use explicit scoping prefixes when referring to local variables (i.e. get_dname.dname and get_dname.deptno) rather than altering the names of your local variables.


Inner join with greater or equal VS where condition using a variable Suppose I have a table with a unique column and this columns has only integer numbers.

Eg: ID column with rows from 1 to 999.

My intention is to return only rows when my ID is greater than 500, for example.

Using T-SQL, I have several stored procedures that create a variable and make this cut like this:

DECLARE @VAR AS INT

SET @VAR = 500

SELECT * FROM sample WHERE ID > @VAR

But for debugging purposes I want to use a temporary table to make this validation throughout the whole stored procedure without having to re-declare or reset my @VAR variable:

CREATE TABLE #cut ( ID int )

INSERT INTO #cut VALUES (500)

SELECT * FROM sample A INNER JOIN #CUT B ON A.ID > B.ID

Is there any performance penalty using this approach on SQL Server 2017?

An extra information is that I don't have sysadmin role on this database, therefore there is no chance using the native debugger.

Yes there will be a (marginal) impact on performance, but if it's for debugging purposes I don't think you'll be able to tell the difference.

You can easily test this yourself on your data by adding statistics IO and looking at the query plans

Take this code for example:

SET STATISTICS time ON;
SET STATISTICS IO ON;

WITH randowvalues
    AS(
       SELECT 1 id, CAST(RAND(CHECKSUM(NEWID()))*100 AS int) randomnumber
        UNION  ALL
        SELECT id + 1, CAST(RAND(CHECKSUM(NEWID()))*100 AS int)  randomnumber
        FROM randowvalues
        WHERE
          id < 1000
      )

SELECT *
INTO sample
FROM randowvalues
OPTION(MAXRECURSION 0)

GO

DECLARE @VAR AS INT

SET @VAR = 500

SELECT * FROM sample WHERE ID > @VAR

CREATE TABLE #cut ( ID int )

INSERT INTO #cut VALUES (500)

SELECT * FROM sample A INNER JOIN #CUT B ON A.ID > B.ID

You will notice that in the execution plan that the first query does a simple table scan for an estimated cost of 0.005.

For the second construction you'll see that the plan for the insert has an estimated cost of around 0.01, and the query itself added an extra table scan (for your temporary table) and a nested loops join to combine data from both tables.
The estimated cost for this select is also around 0.01.

The output for the IO statistics for the first select is:

  Table 'sample'. Scan count 1, logical reads 3, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.

So this query does 3 reads.

The output for the insert and the select with the join is this:

  Table '#cut'. Scan count 0, logical reads 1, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.

  Table 'sample'. Scan count 1, logical reads 3, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0. Table '#cut'. Scan count 1, logical reads 1,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

So if you add that up the second construct with the join does a bit more IO.

Creating a clustered index on the ID column replaces the table scan with a clustered index seek as can be seen in this plan or creating a nonclustered index on ID results in this plan.

Even though the impact in your situation can vary depending on your actual data and table structure, your construction with a temporary table will always add some IO, a second table access and a join operator which will impact performance negatively (maybe marginally).


Nested-Loop-Join: How many comparisons and how many pages-accesses? Say you have two relations R and S , where R has 1000 tuples and 100 page-accesses, and S has 50 tuples and 25 page-accesses.

Assuming R is the outer relation, then how many tuple-comparisons and page-accesses are done?

And how many page-accesses if R is the inner relation?

for each tuple r in R do
   for each tuple s in S do
      if r and s satisfy the join condition
         then output the tuple (r,s)

So in order to find out how many tuple-comparisons are done, I need to do 1000 * 50 = 50000 because the algorithm is doing this "for each" tuple and we have in total 1000 tuples for R and 50 tuples for S, thus 50000 comparisons in total.

But how to know the page-accesses now? If R is outside, we have (1000 tuples) * (25 page-accesses for S) + (100 page-accesses for R) = 25100 page accesses?

And if R is inside, then: 50 * 100 + 25 = 5025 page accesses

I'm not sure if it is correct like that.. or how is this done correctly pleaseee? :/

We can force SQL Server to do exactly this and see what actually happens.

R has 1000 tuples and 100 page-accesses = 10 tuples/page = 806 bytes/tuple.
S has 50 tuples and 25 page-accesses = 2 tuples/page = 4030 bytes/tuple.

These are the tables:

drop table if exists dbo.R;
drop table if exists dbo.S;
go

create table dbo.R(n int, filler char(785)  not null default '');
create table dbo.S(n int, filler char(3990) not null default '');

Filler columns sizes have been rounded down to allow for row overhead. Note that there are no indexes. I have a "numbers" table which I'll use to populate R and S:

insert dbo.R(n) select Number from dbo.Numbers where Number between 1 and 1000;
insert dbo.S(n) select Number from dbo.Numbers where Number between 1 and 50;

We can check just how many pages are involved:

set statistics io on;

select * from R
select * from S

The messages tab of SSMS shows

Table 'R'. Scan count 1, logical reads 100, ...
Table 'S'. Scan count 1, logical reads 25, ...

We have just the right number of pages. A bit of jiggery-pokery will get the behaviour you want to examine

select *
from dbo.R              -- R will be outer
inner loop join dbo.S
    on r.N = s.N
option
(
  force order,          -- dictate which table is outer and which inner
  NO_PERFORMANCE_SPOOL  -- stop the QO from doing something clever but distracting
);

select *
from dbo.S              -- S will be outer
inner loop join dbo.R
    on r.N = s.N
option (force order, NO_PERFORMANCE_SPOOL);

Which gives this in the messages tab (inner table is listed before the outer table)

Table 'S'. Scan count 1, logical reads 25000, ...
Table 'R'. Scan count 1, logical reads 100, ...

Table 'R'. Scan count 1, logical reads 5000, ..
Table 'S'. Scan count 1, logical reads 25, ...

In SQL Server query execution proceeds row-wise. For each row in the outer table the corresponding row(s) in the inner table will be referenced. Since there are no indexes the only option is to read all the rows (i.e. all the pages) from the inner table every time. For R-join-S we have 1,000 outer rows times 25 inner pages giving 25,000 inner page references plus, of course, 100 outer page references. For S-join-R there are 50 rows times 100 pages giving 5,000 inner page references plus 25 outer page references.

In terms of tuple comparisons you are correct - there will be O(R)xO(S) comparisons - 50,000. This is supported by looking at the query plan. For both queries the "Number of Rows Read" for the inner table references are both 50,000.

If there are indexes the query optimizer (QO) has choices other than a table scan. Rewinds may be used for duplicate outer keys. No pages may be read for non-matching keys. In the extreme case where a constraint says there cannot be any matches the inner table may not even be referenced.


MySQL not using double-column index I've a double-column index which isn't used but would be a perfect match for the query:

mysql> EXPLAIN SELECT COUNT(*) as cnt
    -> FROM foobar USE INDEX(index_status_created_at)
    -> WHERE foobar.created_at < "2017-06-20"
    -> AND foobar.status IN ("status1", "status2", "status3");
+----+-------------+---------------+------+--------------------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys            | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+--------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | foobar        | ALL  | index_status_created_at | NULL | NULL    | NULL | 4374176 | Using where |
+----+-------------+---------------+------+--------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Unfortunately, the status are not evenly distributed, so especially for these 3 status there ar enot many rows and the total result would be under 10% of the total table size.

Do you have any ideas why this index is not used at all?

The name of your index suggests, that the index is on (status, created), correct? Then it's exactly what you mentioned.

An index works best, when it's as selective as possible, or in better words:

The closer the result of number of distinct values / total number of rows is to 1, the better is the index.

When you have very few distinct values, it's often cheaper to just scan the entire table, than to look at the index and then find the corresponding entry in the table. That's most probably the reason, the index isn't used. The fact, that you also filter by date, doesn't help much, because you have created as second column in the index.

The order of the columns in a combined index is very important. Always put the more selective columns first. In your case it might help (very much), if your index would be (created, status) instead (depending on how much rows per day you have and how much rows in total).


Do applications have to specify TCP: in the connection string to availability group listeners? In articles that I have read about applications accessing databases through the availability group listener, they all seem to want to use TCP: prefix before the listener name in their connection strings to force a TCP connection.

My question is: is this really necessary? I've tested a few applications without using this prefix and they all successfully established a TCP connection to the primary server in the AG group. I also tested failover to one of the synchronous secondaries and that seems to have gone smoothly as well.

No, it's not necessary. The articles may be including it for completeness sake, but it's not required at all.

The TCP: prefix isn't unique to AG listeners - you can connect directly to a standalone SQL instance by adding the TCP: prefix, but it doesn't change how the connection is established, unless you're connecting locally.

If you connect to an instance locally it will default to using Shared Memory for the connection. If you instead prefix the connection with TCP: it will force the connection to use TCP as the protocol. You can test verify this using the following script.

select c.net_transport, s.host_name, s.program_name
from sys.dm_exec_connections c
join sys.dm_exec_sessions s
on c.session_id = s.session_id
where s.host_name = host_name()

Open up a connection to a local instance without the prefix and you'll only see Shared Memory connections. Then try with the prefix and you'll see it switch to TCP.

Connections to AG listeners however (even if you're connecting from the primary replica), will always use TCP, prefix or not.


What folders should I check to see if these MongoDB applications are installed on my server? I have a CentOS (Linux) server and looked at the /bin directory, but don't find the applications listed below. What folders should I check to see if these applications are installed on my server?

I'm reading a MongoDB book and it states:

After you install or extract MongoDB successfully, you will have the applications show in table 2-1 available in the bin directory (in both Linux and windows).

Table 2-1

--bsondump
--mongo
--mongodb
--mongodump
--mongoexport
--mongofiles
--mongoimport
--mongooplog
--mongoperf
--mongorestore
--mongos
--mongosniff
--mongostat
--mongostop
--mongorestore

You can find these programs using the Linux find command as such:

find / -name 'program_name' -print 2> /dev/null

Where 'program_name' is for example, bsondump.

find / -name 'bsondump' -print 2> /dev/null

The 2> /dev/null prevents find from printing all the paths it looks at.


How can I configure MySQL Innodb to handle 1000s of inserts per hour? I have a very high traffic website where it is possible that 1000s of new records are inserted every hour.

This one error is crippling the site:

PDOException: SQLSTATE[40001]: Serialization failure: 1213
Deadlock found when trying to get lock;
try restarting transaction: INSERT INTO {location_instance}
(nid, vid, uid, genid, lid) VALUES (:db_insert_placeholder_0,
:db_insert_placeholder_1, :db_insert_placeholder_2,
:db_insert_placeholder_3, :db_insert_placeholder_4);
Array ( [:db_insert_placeholder_0] => 1059 [:db_insert_placeholder_1] =>
1059 [:db_insert_placeholder_2] => 0 [:db_insert_placeholder_3] =>
cck:field_item_location:1059 [:db_insert_placeholder_4] => 1000 )

I would be very surprised if MySQL could not handle this type of load. So, my questions are then, is this a database issue and how can I configure MySQL to be able to handle this much traffic?

I have a copy of my website set up on a development server with scripts that simulate the load of content being added to the website. I am running Ubuntu, LAMP stack, with 16GB of RAM.

Admittedly, I am not very knowledgeable about databases. In fact, I am starting with the default my.cnf that comes with it after 'apt-get install' finishes.  Tables are all Innodb. What starting configuration settings and approach would you recommend to begin solving this problem?

Let me know what more information you may need.

Thanks

You are dealing with a deadlock, not a performance bottleneck issue.

If you have a thousand new records per hour, you are far far far away from reaching MySQL limits. MySQL can handle at least 50 times your load.

Deadlocks are cause by application code and are not the database server's fault. Deadlocks can not be fixed on the MySQL server side, except in some specific situations.

InnoDB can show you detailed deadlock information by running SHOW ENGINE INNODB STATUS at the MySQL prompt, or with mysql -uroot -p... -e "SHOW ENGINE INNODB STATUS".

However, this only shows the last deadlock that occurred, there is no deadlock log.

Thankfully, there's a tool pt-deadlock-logger which takes care of that problem, it takes care of polling InnoDB status and saves all the detailed deadlock information before it's refreshed with a new deadlock.


SSIS package with BULK data load I analyzed a C# program over the weekend for a customer that basically does the following:

Truncate Table
Fetch Data (source on these is either remote database or local database where job runs)
SQLBulkCopy (.NET class, into destination table of database where job runs)

For the most part (1) and (2) are done through stored procedure calls. I believe SSIS will be more efficient in handling this process. My end result is to determine if SSIS can do a better job and then the most efficient way of designing the package.

Question(s)
In cases where there are a large number of records would SSIS get better performance with the BULK INSERT task when (2) and (3) are the same database on the same instance? Or just change the procedure call in (2) to be a INSERT INTO, taking out the need for (3)?

In cases where (2) is a remote source (another SQL instance on the same network), does SSIS handle pulling data from a remote source more efficiently than doing it through the .NET SQLClient? I would not think there is a huge difference.

As well, when (2) is a remote source that is rather large amount of data (8 million +)would it be better to have a package on the remote instance export the data; then move that file to the destination server and bulk load it?

For 8 million rows, I don't think there will be a lot of difference between SSIS and the SQLBulkCopy class. In SSIS, you do have to make sure you are using the fast load option in the OLE DB Destination, otherwise it will surely be slower.
If the source is local, you could try a SQL Server Destination, it might be a tad quicker than the OLE DB Destination.


Trigger table name as variable into stored procedure I have several triggers that activate a procedure, I want to identify the table that uses the trigger and use it as a variable to a procedure.

Another option is to pass a variable (the name of the table) from a trigger to a procedure

Is it possible in mysql > 5.7?

I try not to create many procedures, I want a single procedure that creates me other tables from the trigger variable.

What reserved MySQL variables should I use?

Regards.

MySQL provides no way to do what you want.

The obvious suggestion would be to modify the triggers and the procedure to add one more parameter, which is the table name. But since it's obvious, you probably don't want to do so. Probably because the triggers and the procedure run in production, and they cannot be updated atomically (so if you modify the procedure first, triggers will fail; but if you modify a trigger first, it will fail).

If that it the reason, I suggest to use a user variable. In the triggers, you can do something like:

SET @table := 'wood_table';

And in the procedure, you will be able to check the value of @table.


User Grant in cassandra DB I would like a user abc to access only a single keyspace and no other keyspaces in the Database.
How can I achieve so?

Just as described in documentation:

GRANT <permission> ON <keyspace> TO <user>;

You need to setup authentication/authorization before, and create user/role.


how to add shard into current cluster without sharing any chunks my current cluster sh.status() is as below:

  shards:
    {  "_id" : "rsDev",  "host" : "rsDev/10.60.90.224:27003,10.60.90.224:27004" }
    {  "_id" : "rsRaw",  "host" : "rsRaw/10.60.90.224:27001,10.60.90.224:27002" }
  active mongoses:
    "3.0.14" : 1
  balancer:
    Currently enabled:  yes
    Currently running:  no
  databases:
    {  "_id" : "mike",  "partitioned" : true,  "primary" : "rsDev" }
        mike.PDiff
            shard key: { "_id" : 1 }
            unique: false
            balancing: true
            chunks:
                rsDev   23
                rsRaw   22
            too many chunks to print, use verbose if you want to force print

Now I want to add a new shard into this cluster by

db.runCommand({addShard: "rsPro/10.60.90.224:27005", maxSize:1})

After google about maxSize, it is a soft limit for balancer.
If I add the new shard rsPro using above command, it start to balance all chunks.
If I don't stop the balancer, is there any way to prevent any chunk moving to the new shard rsPro ?

There is other solution, if you want to migrate you data to new machine "on-the-fly" and have only short interruption when you transfer your clients to use that new machine.  With this you can replicate data to new location

If you want to add new shard to cluster and still keep balancer running... you can always use TAGs.. Tag your current shards and define that $MinKey - $MaxKey chunk area belongs to that TAG. Check here what I'm talking about


Recovery model of current database I want to know how can I change the recovery model of my current database. I am having sysadm rights.Operating system is windows, Db2 10.5.

can some one let me know the command please

In DB2, the SQL Server's recovery model simple equivalent is called circular.

<p>C:\WINDOWS\system32>db2 get db cfg for sample |findstr "LOGARCHMETH1 LOGARCHMETH2" <\p>
<p> First log archive method                 (LOGARCHMETH1) = OFF </p>
<p> Second log archive method                (LOGARCHMETH2) = OFF </p>

If both logarchmeth1 and logarchmeth2 are set to OFF, the database uses circular logging and is not rollforward recoverable.


Join subquery by year and month I want to collect data from differed tables grouped by year and month.
I'm using subquery for each sub data.
Using mysql.

    SELECT YEAR(td.db_date) AS stat_year, job.total_jobs
    FROM time_dimension AS td

    LEFT JOIN (

        SELECT YEAR(j.published_at) AS stat_year, COUNT(*) AS total_jobs
        FROM jobs AS j
        GROUP BY stat_year

    ) AS job ON job.stat_year = stat_year

    GROUP BY stat_year

When I'm joining data with id - it works perfect. But with this dates...

For time_dimenstion I'm using this https://gist.github.com/bryhal/4129042

Subquery return this data

2014 | 2334
2015 | 2000

But whole query starts with the first year and return same data but wrong matched with year

1 | 2334
2 | 2000

There is one problem with aliases usability in different parts of a query - aliases defined in the SELECT clause are only available in GROUP BY, ORDER BY and HAVING, not in WHERE or JOIN .. ON, so as you do not add the table specificator to the second stat_year, it probably takes the same column and the = is trivially true.

You can check that with EXPLAIN EXTENDED <query>; SHOW WARNINGS; which will tell you how MySQL "sees" your query (canonizing all column references with db and table names).

You need to replace it by the actual expression - ON job.stat_year = YEAR(td.db_date) - the one in the GROUP BY can stay the same as they will be equivalent after the join is done.


Does update lock the row or the entire table? Basic question: When I update a row in a database table, does it lock the current row or the entire table?
I am using MySQL. When I update column which is not unique.
Is the locking different from one database engine to another? I have read this article but I am still confused.

The links go into gory details, but this question seems to need a simple yes/no answer.
For ENGINE=MyISAM or MEMORY, the only lock is a table lock.
For ENGINE=InnoDB:
Think of it this way -- It locks every row it had to look at.

No index on the column -- It had to check every row, so all rows are locked.  That effectively locks the entire table.

UNIQUE index on the column -- Only one row need be touched, hence, locked.

In between...  A non-unique INDEX on the column -- It must lock all the rows with that value.  (Possibly the 'next' row will get locked, too.)

PS: the PRIMARY KEY is a UNIQUE index in MySQL.
Some other vendors have different index definitions, and some do have "table locks".  Some "escalate" a bunch of row locks to a "table lock".
Bottom line:

Add an INDEX, either UNIQUE or not, as appropriate.
If you need to look at the row before potentially UPDATEing it, use a transaction and SELECT ... FOR UPDATE.


MySql Federated table and changes propagation i have 10 mysql servers and one central mysql server that's keeping track of some numerical indexes. Idea is that each time one of those 10 servers request and id, that id is marked as used and no other server can use it. For that reason i have implemented a Federated table system which works almost perfectly for me - each server is writing changes into local table and it's automatically reflected on the table of the main server. Like that each of those 10 servers can see what's the next available id without even knowing about each other.

Now, the problem is that on the central server, to Federated, "target" table i have added one additional column. It turns out i have to replicate that same change on all those 10 servers manually! My expectation was that once i add new column on "federated target" table, all dependent tables on those 10 servers will get updated automatically to match their "parent" structure like they did once created for the first time.

Is there a way to enforce this in MySql? Basically i wanna have 1 table and structural as well as data changes of that table to be reflected automatically on all those 10 servers.

I recommend you use standard master/slave replication with each node being slave to every other node (the multi-source) option. This isn't master-master as log-slave-updates should not be enabled.

Use the server configuration auto_increment_increment to 10, and have each node having auto_increment_offset to a unique number I think will satisfy you requirement that each node has a different, non-conflicting numbering of ids as an alternate to centralized issuing of numbers.

You application will need to ensure that the same deletes or updates of a row do not happen on simultaneous nodes. Inserts should also use the auto_increment field only.

Federated tables is a bit of a hack that isn't particularly maintained.


Does MariaDB support schema changes inside a transaction? MySQL doesn't support schema changes, inside a transaction so there's no way to roll back after during a failed migration involving schema changes.

This capability is supported by Postgres and SQLite. Any chance MariaDB supports it?

No.

"DDL statements (CREATE, ALTER, DROP) [...] cause an implicit COMMIT"

Thanks horse with no name.


return age in years and months from date of birth Using MySQL, I return the age in years and months as separate fields in a query.

This may well be a premature optimization but the duplicate calls to datediff and from_days in this query are upsetting.

Is it possible to get the from_days(datediff(now(), user_birthdate)) factored into a single call without a nested query?

SELECT
    user_id,
    year(from_days(datediff(now(), user_birthdate))) as years,
    month(from_days(datediff(now(), user_birthdate))) as months

or... would a nested query be a win over this current query?

Like this:

SELECT
    user_id,
    YEAR(age) AS years,
    MONTH(age) AS months
FROM
    (SELECT
        *, FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(user_birthdate)) AS age
    FROM
        users) AS users_temp

If someone can give me some hints about the best way to test the relative performance of these two methods empirically, that would be useful too

You can do it with variables, like this:

SELECT
  user_id,
  year(@fecha:=from_days(datediff(now(), user_birthdate))) as years,
  month(@fecha) as months

But have caution with the execution order. If you assign the value in SELECT don't expect same value in WHERE part.


Query runs in parallel but it is showing as blocked by itself I have a query which runs in parallel with a MAXDOP of 8.
When i looked at sp_who2 i see that the same session ID is being repeated several times(>8) with different connection id.

I used the below query and see that the wait type is still CXCONSUMER wait type.
But i see 130 different exec_context id.

SELECT
    dot.session_id,
    dot.exec_context_id,
    dot.task_state,
    dowt.wait_type,
    dowt.wait_duration_ms,
    dowt.blocking_session_id,
    dowt.resource_description
FROM sys.dm_os_tasks dot
    LEFT JOIN sys.dm_os_waiting_tasks dowt
        ON  dowt.exec_context_id = dot.exec_context_id
            AND dowt.session_id = dot.session_id
WHERE dot.session_id = 96
ORDER BY exec_context_id;

If the query is running in parallel does it have different spid when i the load gets distributed to different worker thread.?

Looked at Kendra Little's write up.
Very helpful one.

I used the query to see different schedulers used in my parallel processing query.

I see that same shcheduler_id/worker address is used to multiple times.Is it normal.?

Also my MAXDOP is 4,but i see 5 different scheduler_ids which is weird.

Multiple parallel threads for the same query all share the same session_id, or spid.
Kendra Little did a great write-up showing that here.
Erik Darling also has a great, if only tangentially related, post about it here
If your query is blocking itself, that indicates one thread is taking longer to complete its work; this might mean you have out-of-date statistics on the tables in question, or simply that the data distribution is skewed such that one thread is receiving far more work than the other threads.
Also, @Josh Darnell mentioned on a very similar question, when you see multiple connection_ids for the same spid, it is because they have an application using Multiple Active Result Sets (MARS).
In reference to the number of threads being 5, even though the Maximum Degree of Parallelism is set to 4, the 5th thread is a coordinator thread that is waiting for results from the 4 parallel threads.  It is completely normal for the scheduler_id to be the same for multiple queries since schedulers are related to processors, not threads.  See the Microsoft Docs for details.  This query will show you the count of requests for each scheduler:
SELECT dos.scheduler_address
    , dos.scheduler_id
    , dos.active_workers_count
    , RequestCount = (
          SELECT COUNT(1)
          FROM sys.dm_exec_requests der
          WHERE dos.scheduler_id = der.scheduler_id
      )
FROM sys.dm_os_schedulers dos
ORDER BY dos.scheduler_id;

Results from my laptop look like this:
╔════════════════════╦══════════════╦══════════════════════╦══════════════╗
║ scheduler_address  ║ scheduler_id ║ active_workers_count ║ RequestCount ║
╠════════════════════╬══════════════╬══════════════════════╬══════════════╣
║ 0x00000017F7280040 ║            0 ║                    6 ║            4 ║
║ 0x00000017F72A0040 ║            1 ║                    7 ║            5 ║
║ 0x00000017F72C0040 ║            2 ║                    7 ║            5 ║
║ 0x00000017F72E0040 ║            3 ║                    6 ║            6 ║
║ 0x00000017F7A00040 ║      1048576 ║                    1 ║            0 ║
║ 0x00000017F7880040 ║      1048578 ║                    1 ║            1 ║
║ 0x00000017F1980040 ║      1048579 ║                    1 ║            1 ║
║ 0x00000017F19A0040 ║      1048580 ║                    1 ║            1 ║
║ 0x00000017F19C0040 ║      1048581 ║                    1 ║            1 ║
║ 0x00000017F78A0040 ║      1048582 ║                    1 ║            0 ║
║ 0x00000017F78C0040 ║      1048583 ║                    1 ║            0 ║
║ 0x00000017EB020040 ║      1048584 ║                    1 ║            1 ║
╚════════════════════╩══════════════╩══════════════════════╩══════════════╝


MySQL: Privileges to view Views/Stored Procedure for user Regardless of Host I've created stored procedures on a (remote hosted - DreamHost) MySQL Database using the HeidiSQL client.

One problem i seem to have is that viewing/editing the content of stored procedures is only possible when connecting from the same "host".

For example, i connect primarily from home, via a BT connection (I'm a BT customer)

But when i try connecting in from work (a different provider/ISP) i can do everything, run queries, edit tables etc, but cannot edit/view the Views/Stored Procedure queries despite connecting with the same username.

I'm assuming the "@") may be something to do with it?

Is there anyway i can prevent this from being a problem?

You may check grants for user and see if it differs for hosts.
In addition to

show grants for username@host

You may check grants for specific databases:

use dbname;
show grants for username;

Also, there are few more places to check for extensive list of privileges:

SELECT * FROM FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE GRANTEE LIKE '%userame%';
SELECT * FROM FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE LIKE '%userame%';
SELECT * FROM FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
WHERE GRANTEE LIKE '%userame%';

Most likely  INFORMATION_SCHEMA.USER_PRIVILEGES will shed a light on your issue.


Student Attendance, null not returning My question is based on the answer given here

 Attendance:
id | student_id | date       |
------------------------------
1  | 2          | 2013-10-24 |

Students:
student_id | student_name   |
-----------------------------
1          | Johnny Johnson |
2          | Bobby Tables   |
3          | Suzie Smith    |
To get a report of all the kids at your school and their attendance status for the day, you could run a query like:

SELECT s.student_id, s.student_name
,  CASE WHEN a.id IS NOT NULL THEN 'Absent' ELSE 'Present' END as attendance_status
FROM Students s
LEFT JOIN Attendance a on s.student_id=a.student_id
WHERE a.date = '2013-10-24'
Which would return:

Result :
student_id | student_name   | attendance_status |
-------------------------------------------------
1          | Johnny Johnson | Present           |
2          | Bobby Tables   | Absent            |
3          | Suzie Smith    | Present           |

I have a similar query but it is not returning null values in this case present but its only retunring absent

Result that I am getting :
student_id | student_name   | attendance_status |
-------------------------------------------------      |
2          | Bobby Tables   | Absent            |     |

My query looks like SELECT
student.StudentID,
student.Name,
CASE WHEN attendance.StudentID IS NOT NULL THEN 'Present' ELSE 'Absent' END as Attendance_Status
FROM
student
Left JOIN attendance ON student.StudentID = attendance.StudentID
where
attendance.Date = '2015-03-30'

To get desired results attendance.Date = '2015-03-30' should be part of LEFT JOIN, not WHERE:

SELECT ....
FROM
student
Left JOIN attendance ON student.StudentID = attendance.StudentID
AND attendance.Date = '2015-03-30'


SQL Server 2012 SP2 update failure I have been having failures to install SQL Server 2012 SP2 on a 2-node active\passive cluster. I have uploaded detail and summary logs for the latest run on the passive node here:
https://www.dropbox.com/s/4tlmv3hmzvbb9l4/Log%20Files.7z?dl=0

Due to not being able to pinpoint the error I have tried the following:

rebooting and running the patching from the GUI and command line with and without pre-extracting the files.
I have also ensured cluster services and SQL role are on the same active node, and brought the Available Storage group online although it does not have any unassigned disks in.
I have also ensured all default directories are present on the shared disks, and checked the registry for any features which are pending an update. I did not find any as I had seen reference to these problems.
I have also tried using 2 different admin accounts to run the setup even though I could not see any evidence of this problem

The GUI mimics the detail log showing all verification successful, the patching start; whereby after the patching begins it then ends with no error message. A completely blank feature list box is shown and a blank text box for log file locations, although these are obviously generated in the default location.

It seems to me the patching starts, but fails before any features begin to update without a specific error.

Any guidance is greatly appreciated.

Figured out the problem last weekend. After trying to run a repair on my SQL Server 2012, it reported that the user Administrator doesn't have the proper privileges in order to proceed with the installation, so I had to do the following:

Provide Full Control through Group Policy Management Editor in Computer configurations -> Policies -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment to:

Back Up files and directories
Debug programs
Manage auditing and security log

More on this error here:
SQL Server installation fails


HAVING STATEMENT error SQL command not properly ended I have updated this question sicne I have made progress, my HAVING statement is still not working correctly. I am getting a ORA-00933: SQL command not properly ended..

I believe its my HAVING clause. Any Ideas?

RECAP:

I am trying to capture progress of customers who did not have purchasing activity between 2013-2015 and track their buying activity from Oct-2015 forward to track salesman activity..

SELECT INV.COMPANY
     , INV.CUSTOMER_ID
     , INV.ADDRESS_ID
     , INV.NAME
     , INV.ADDRESS1
     , INV.ADDRESS2
     , INV.CITY
     , INV.STATE
     , INV.ZIP_CODE
     , INV.COUNTRY
     , CICM.CLIENT_VALUE as contact_type
     , CICM.VALUE
     , CICM.DESCRIPTION
     , INV.REGION_CODE
     , INV.SITE
     , LD.LSTINVDATE
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') THEN SUM(INV.SALES) ELSE 0 END AS TWO_YEARS
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-15') AND SYSDATE THEN SUM(INV.SALES) ELSE 0 END AS PROGRESS
     , SUM(INV.SALES) AS SLSAMNT

FROM IFSINFO.HB_INVOICING_ALL INV
LEFT JOIN IFSINFO.HB_LAST_INVOICE_DATE LD
    ON (INV.COMPANY = LD.COMPANY)
    AND (INV.CUSTOMER_ID = LD.IDENTITY)
LEFT JOIN IFSAPP.CUSTOMER_INFO_COMM_METHOD CICM
    ON (INV.ADDRESS_ID = CICM.ADDRESS_ID)
    AND (INV.CUSTOMER_ID = CICM.CUSTOMER_ID)
WHERE INV.CUST_GRP = '4'
AND INV.COMPANY = '20'
HAVING SUM(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') = 0
--HAVING MAX(LD.LSTINVDATE) < (SYSDATE - INTERVAL '2' YEAR)
GROUP BY INV.COMPANY
       , INV.CUSTOMER_ID
       , INV.ADDRESS_ID
       , INV.NAME
       , INV.ADDRESS1
       , INV.ADDRESS2
       , INV.CITY
       , INV.STATE
       , INV.ZIP_CODE
       , INV.COUNTRY
       , CICM.CLIENT_VALUE
       , CICM.VALUE
       , CICM.DESCRIPTION
       , INV.REGION_CODE
       , INV.SITE
       , LD.LSTINVDATE
       , INV.INVDATE

The HAVING clause is the cause of the error as it not syntactically correct. You have (added parentheses to show how it is parsed):

HAVING (SUM(..) BETWEEN x AND y) = 0

and the expression inside the parentheses returns a boolean result.

Additionally, the SUM(INV.INVDATE) doesn't make much sense. What result should one expect if we add dates?

And as @mustaccio correctly noticed, the HAVING clause goes after GROUP BY, not before.

It seems you want something like this in the HAVING clause:

HAVING SUM(CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13')
                                              AND TO_DATE('30-Sep-15')
           THEN INV.SALES ELSE 0
           END) = 0

which I would prefer rewritten as:

HAVING SUM(CASE WHEN INV.INVDATE >= DATE '2013-10-01'
                 AND INV.INVDATE < DATE '2015-10-01'
             THEN INV.SALES ELSE 0
           END) = 0

The INV.INVDATE should be removed from the GROUP BY clause as well and the TWO_YEARS and PROGRESS expressions should also be corrected as the  SUM(...) expression suggested SUM(CASE WHEN ... THEN x ELSE y END) instead of CASE WHEN ... THEN SUM(x) ELSE y END.


Tokudb 5.7 [Warning] The partition engine is deprecated and will be removed in a future release I am running upgrade from Tokudb 5.6.30 to 5.7.17 (Percona distribution) and I am getting this warning

  [Warning] The partition engine, used by table ‘my_table’, is deprecated and will be removed in a future release. Please use native partitioning instead.

Does it mean that I can still use it in 5.7.17 tokudb, but to use native partitioning in a future releases (8.0) I will have to rebuild this table to use innodb. In other words, is Percona or any other distribution going to support partioning in Tokudb beyond 5.7?

It remains to be seen.

Oracle's MySQL 8.0 is

Moving partitioning support into the InnoDB engine.
Removing MyISAM.
Building a "Data Dictionary" (in InnoDB tables) that replaces .frm files, etc.

This leads to action items for the "forks" -- MariaDB, Percona, Aurora, etc.:

Continue to keep MyISAM, TokuDB, Aria, etc.?  Or abandon?
Abandon Partitioning for that engine?  Or re-implement it?
Implement usage of the Data Dictionary for that engine?  Or keep old mechanism?

Attend Percona Live in April; there will be plenty of Percona, etc, folks there to address the question (if they have decided by then).


How does mysql deal with queries that touches myisam and innodb tables? In our mysql database, we use both myisam and innodb tables, though there are more myisam tables.
QUESTIONS

If a query involves both myisam and innodb tables, will the query use table lock or row lock on innodb tables?
Is this a good practice?

There are three aspects to doing a JOIN query that is can be alarming
ASPECT #1 : Locking Behavior
Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up behaving like MyISAM in terms of table-level locking instead of its normal row-level locking.MVCC and ACID compliance cannot be applied to the MyISAM data. It could possibly be stagnated for InnoDB tables as well.
ASPECT #2 : MyISAM's Involvement
If any MyISAM table is being updated via INSERTs, UPDATEs, or DELETEs, the MyISAM table involved in a JOIN query would be locked from other DB Connections, forcing the JOIN query to wait until the MyISAM table can be read. In light of this, if there is a mix of InnoDB and MyISAM in a JOIN query, the InnoDB tables would be subject to intermittent locking of PRIMARY KEY entries (in the Clustered Index).
Keep in mind that MVCC will still allow READ-UNCOMMITTED and REPEATABLE-READ transactions to work just fine and let certain views of data be available for other transactions. The same cannot be said for READ-COMMITTED and SERIALIZABLE transactions.
ASPECT #3 : Query Optimizer's View of the JOIN
MyISAM
MySQL will rely on the index cardinality of tables to determine an optimized EXPLAIN plan. Index cardinality is usually stable for a MyISAM table until it is inundated with INSERTs, UPDATEs, and DELETEs. Consequently, you will needs to periodically run OPTIMIZE TABLE against the MyISAM table.
InnoDB
InnoDB's index cardinality is NEVER STABLE !!! If you run SHOW INDEXES FROM innodbtable;, you will see the index cardinality change each time you run that command. That's because InnoDB will do dives into the index to estimate the cardinality. Even if you run OPTIMIZE TABLE against an InnoDB table, that will only defragment the table. OPTIMIZE TABLE will run ANALYZE TABLE internally to generate index statistics against the table. That works for MyISAM. InnoDB would usually ignore it. You could disable innodb_stats_on_metadata to get stable EXPLAIN plans, but that would just cause you to do ANALYZE TABLE maintenance on InnoDB tables as much as you would a MyISAM table.
EPILOGUE
Believe it or not, there is still an open ticket on InnoDB/MyISAM joining during a SELECT FOR UPDATE. If you read it, it sums up the resolution as follows : DON'T DO JOINs THAT MIX InnoDB and MyISAM, please.


Is it possible to install a MS SQL Express Studio, where the studio tool also is included? I am facing the following problem:

I want to have a Express (=free) version of MS SQL Server and there I want to be able to see the SQL Management Studio interface. I have found only installations where there is the SQL Server Configuration Manager which has more limited list of features and of course I want to use a feature that does not exist in it.

So as the title says:

  Is it possible to install a MS SQL Express Studio, where the studio tool also is included?

You are probably better off with the newer version of Management Studio Express (which will work against 2008 R2, 2008 and 2005):

http://www.microsoft.com/download/en/details.aspx?id=22985

The reason is that if you use the 2008 version and try to manage a 2008 R2 instance, for example, you may run into issues. Always better to have the client tools >= the highest version of the engine that you need to support.

Note that Management Studio Express is missing some of the SSMS functionality, such as managing agent, so if you have to manage other editions you may be better off investing $49 (or less, depending on vendor) into developer edition, which comes with the full version of SSMS:

http://bit.ly/oPq7NY


How to turn off all RAISE NOTICE on production server? I put a lot of RAISE NOTICE in functions for debugging purpose. I think this will affect performance, so they should be removed on production environment. Is there any way to opt-out those logging/assertion functions only under specific conditions?

You could wrap them in an IF clause that tests a custom configuration variable. This will work but it's a bit verbose and each statement in PL/PgSQL has a cost.

I suspect you'll be better off lowering the log level in the RAISE messages so you log on a level like DEBUG that isn't captured by log_min_messages by default. That way you can just turn it on by changing client_min_messages or log_min_messages.

See the expanded form of the RAISE statement, eg:

RAISE DEBUG USING MESSAGE = 'TheMessage';

See the documentation for the RAISE statement.

Alternately, you can leave your messages at RAISE NOTICE and increase the log level, but that may hide other information you'll want to see in the logs. Better to log with a lower priority initially.

So long as the notices aren't doing lots of expensive string formatting or being called huge numbers of times I suspect you won't have too much trouble with their performance impact while logging to file/client is suppressed by log levels.


Using IF clause or while clause in OUTER APPLY I want to select dates between @startdate and @enddate but I get an error when a month doesn't have 31 days.

DECLARE @StartDate date, @EndDate date
set @StartDate = '2019-04-01'
set @EndDate = '2019-04-30'

(select T1.dday
from [@SALESORDERFORECASTH] T0
    INNER JOIN (SELECT t.*
FROM [@SALESORDERFORECASTD]
OUTER APPLY
(
    VALUES
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-01' as varchar)),120),U_Delivery_1),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-02' as varchar)),120),U_Delivery_2),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-03' as varchar)),120),U_Delivery_3),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-04' as varchar)),120),U_Delivery_4),
        .
        .
        .
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-30' as varchar)),120),U_Delivery_30),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120)
        +cast('-31' as varchar)),120),U_Delivery_31)
    ) t (DocEntry,dday,U_Delivery)) T1 ON T0.DocEntry = T1.DocEntry
    AND T1.dday <= @EndDate
group by T1.dday)

In this code I want to select T1.dday between 2019-04-01 to 2019-04-30 but in VALUE have 31 days. Leads to this error:

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Can I use If or while or when clause to check date in this VALUES?

I don't quite understand your table structure or your query, but a much more sane way to generate a small date range (< 100 days) between a start and end date - given that you don't have a calendar table (even though you should, and shouldn't easily be able to argue against one) - is as follows:

DECLARE @StartDate date = '20190401',
        @EndDate   date = '20190430';

;WITH days(d) AS
(
  SELECT @StartDate
  UNION ALL
  SELECT DATEADD(DAY,1,d) FROM days WHERE d < @EndDate
)
SELECT d FROM days;

If your range can be more than 100 days, you can use OPTION (MAXRECURSION n).

Now you can use that CTE to anchor / join / apply etc. to the rest of your query. I can help do that but you'll need to show table structure, sample data, and desired results. Basically, you need to provide an MCVE if you want quality help - this takes out guesswork, minimizes back-and-forth, and helps identify edge cases you may not have considered yet.


Is this normal binary logging behaviour - maintaining one ever growing file despite expire_logs_days? I have a Percona MySQL DB running on a dedicated server.

I have in my.cnf

# REPLICATION #
log_bin=/var/lib/mysql/binlogs/mysql-bin
expire_logs_days=7

My issue is that the log file mysql-bin.000001 is ever growing in size.

I'm not sure if the expected behaviour is for it to start a new file each day on 7 day rotation, or to "trim from the top", but it doesn't seem to be doing either, as it's the only file been created and continues to grow in size beyond the 7 day boundary.

Any advice or clarification is much appreciated.

If the file hasn't exceeded max_binlog_size or if a log flushing event hasn't occurred then it won't start a new file. expire_logs_days only removes non-active binary logs (i.e. files other than the one that shows up under a "show master status;").


group by and having query need help? I am using the pub database in SQL Server 2005. Which stores have at least 5 orders? Should diplay the store name and store id. I have come up with a query something like this:

select
    stores.stor_name
    , sales.stor_id
from
    sales
join
    stores
on  sales.stor_id =stores.stor_id
group by
    sales.stor_id
having
    (count(sales.stor_id)>=5)

When answering please mention what i have done wrong

ypercube already gave the right answer, but here's another alternative:

Instead of GROUPing by all columns (leading to a bloated GROUP BY this, that, that, and_that_too), the following can be used:

select
    sales.stor_id,
    MIN(stores.stor_name) AS stor_name
from
    sales
join
    stores
on  sales.stor_id =stores.stor_id
group by
    sales.stor_id
having
    (count(sales.stor_id)>=5)

Though I confess we just move the bloating around... But this at least clarifies the mechanism of the query, by simplifying the GROUP BY.


Sync data between 2 databases Oracle 10g I have 2 databases DB_LIVE and DB_NEW, both have different credentials. I have a Data Read access on DB_LIVE and I am owner of DB_NEW which is a completely blank and raw db provided to me.

I want to copy a table from DB_LIVE to DB_NEW and later keep syncing them. Another caveat is I only have PL/SQL developer and no SQL*Plus. Can someone please provide me pointers to it.

On the machine where you installed your database you should have SQL*Plus installed.

For your question. Create a database link from DB_NEW to DB_LIVE and use your Read Data user. Now you can query the data.


How to insert multiple IP addresses in a pg_hba.conf file via unix shell script? I need to write some IP addresses to the pg_hba.conf file of my Postgresql, in order to allow remote access from those IP to my machine.

I have the following code in shell script to do that for a single IP address

#!/bin/bash

######## PostgreSQL Child Server Host or IP Address
serverhost=$1

######## PostgreSQL Child Server pg_hba.conf File Path
pghbapath=$2

######## Shell Script Command to write the data at the end of the    pg_hba.conf file
sed -i "$ a host    all all     $serverhost/32  md5" $pghbapath

An example command to execute the above bash script is

./reset_ip.sh 192.168.10.27 /root/Desktop/BashScripts/pg_hba.conf

The above is working fine, but I have a lot of IP addresses with me. So, instead of manually running the command each and every time for a different IP address, is there a command which can take multiple IP addresses at once and write them to the pg_hba.conf file in one go?

Edit - Thanks a lot for the answer Luuk. I tried it and it worked. Now, suppose if instead of having a text file to hold all the address, I want to write the multiple IP addresses separated by commas in the command line itself, somewhat like

./reset_ip.sh 192.168.10.27,192.168.10.28,192.168.10.29,192.168.10.30 /root/Desktop/BashScripts/pg_hba.conf

Is it possible? What I basically want is to be able to pass multiple values for a single variable via the command prompt.

If you have a file which holds all the addresses (1 at a line) you can do:

cat ipaddresses.txt | while read line;
do
  ./reset_ip.sh $line /root/Desktop/BashScripts/pg_hba.conf;
done

or, with gawk:

gawk '{ system("./reset_ip.sh $1 /root/Desktop/BashScripts/pg_hba.conf") }' ipaddresses.txt


How to quote database names with spaces when using queryout with bcp? I'm attempting to use bcp on Microsoft SQL Server 2000 to export a couple of columns from a large database table.

The database names contain spaces. Here's an example that works that contains an export of the whole table:

bcp "database name with spaces.dbo.table name with spaces" out
    C:\database_name_with_underscores.csv -c -q -t ","
    -S "server_address" -U username -P password -k

I'd like something along the lines of:

bcp "SELECT TOP 10
        [column name with spaces],
        [another column name with spaces]
    FROM [database name with spaces].dbo.[table name with spaces]"
    queryout C:\database_name_with_underscores.csv -c -t "," -S "server_address" -k

...but I've not been able to get it to work using combinations of the -q flag and brackets [] or quotes ". I receive errors similar to:

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Invalid object name 'database name with spaces.dbo.table name with spaces'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Statement(s) could not be prepared.

Any help is appreciated.

The solution I settled on was to specify the database name using the -d flag.

bcp "SELECT char(34) + REPLACE([Code], '""""', '""""""""') + char(34),
    char(34) + REPLACE([Description], '""""', '""""""""') + char(34)
    FROM [Table Name]"
    queryout C:\Users\jwallace\out.csv -c -t ","
    -d "Database Name With Spaces"
    -S "10.0.0.1,1848" -U username -P password -k


How can I rename an auto-named constraint when I get this error 15248? Method:

Open SSMS. Find table. Open constraints. Find constraint name. Right click, rename. Give new name.

EXPLODE. (well, it just fails, at least)

You can use sp_rename manually.

Note that this is fixed in SSMS 2012 (I don't remember if I have SP1 installed or not).

EXEC sp_rename
    @objname = N'[myschema].[DF__AccountTy__Allow__7E3AEC52]',
    @newname = N'DF_whatever',
    @objtype = 'OBJECT';


Improve reading speed of MySQL table storing stock prices (100 Mil. entries) I have a table which stores stock prices in a MySQL database with the following structure:

CREATE TABLE IF NOT EXISTS `tbl_prices` (
  `price_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stock_id` int(11) NOT NULL,
  `datasource_id` int(11) NOT NULL,
  `price_time` bigint(20) NOT NULL,
  `price_time_frame` bigint(20) NOT NULL,
  `price_open` double NOT NULL,
  `price_close` double NOT NULL,
  `price_low` double NOT NULL,
  `price_high` double NOT NULL,
  `price_volume` double NOT NULL,
  PRIMARY KEY (`price_id`),
  UNIQUE KEY `unique_entry` (`stock_id`,`datasource_id`,`price_time`,`price_time_frame`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

The table mainly acts as a storage, meaning that all data for each stock and time frame is loaded into memory on application start. New stock prices are continuously written to the database.

A common query for loading all prices for a given stock looks like this:

SELECT price_open, price_high, price_low, price_close, price_volume, price_time
FROM tbl_prices
WHERE stock_id=1 AND datasource_id=2 AND price_time_frame=864000
ORDER BY price_time;

EXPLAIN:

EXPLAIN SELECT price_open, price_high, price_low, price_close, price_volume, price_time
FROM tbl_prices
WHERE stock_id=1 AND datasource_id=2 AND price_time_frame=864000
ORDER BY price_time;

results in:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  tbl_prices  range   unique_entry    unique_entry    24  NULL    113036  Using index condition

The table contains more than 100 million entries.

Everything works fine but loading of the prices from the table uses a considerable amount of time. How could I speed up the table architecture?

Regards,

This does not answer the whole question but as a first hint, I would suggest changing the order of the columns in the definition of the unique key, from:

`unique_entry` (`stock_id`, `datasource_id`, `price_time`, `price_time_frame`)

To:

`unique_entry` (`stock_id`, `datasource_id`, `price_time_frame`, `price_time`)

This puts first all the column that come into play in the where predicates, and then the ordering column, so this gives the database a better chance to take full advantage of the index.

Also: do you really need select *? The more columns you get, the bigger the payload the database has to process (and return). You should enumerate the columns that you really want. Ideally, you would only extract columns that belong to the index: this makes the index covering for the query, and the database might be ble to execute the whole query by looking at the index only.


Is it possible to track the progress of when I drop a the primary key clustered index on a table? I have a large primary key clustered index I need to drop. I believe dropping it will also trigger index rebuilds on the nonclustered indexes (which are also pretty big). Is there a way to determine the progress of these processes?

If you are using Enterprise Edition or equivalent, you can drop the primary key ONLINE:
ALTER TABLE Production.TransactionHistory
DROP CONSTRAINT PK_TransactionHistory_TransactionID
WITH (ONLINE = ON);

This will allow you to monitor progress using the Profiler Event Class Progress Report: Online Index Operation or the Extended Event:

progress_report_online_index_operation
Occurs during an online index build process to indicate the progress of the build operation. Each stage of an online index build operation generates an event. Use this event to monitor online index builds.

It provides the following event fields:


How do I prove this relation is in the `3FN`? Consider the relation R(A,B,C,D,E) and the set of functional dependencies F={A->D,D->C,BC->AE}
I need to prove this relation is in the 3FN, however I don't think it is; in fact, I don't even think it is in the 2FN.
My reasoning is the following:
I think my candidate key is AB, as AB->A,B, through the reflective property. Because it determines A, it also determines D and C, by transitivity. By pseudo transitivity AB->AE, so AB also determines E; I can't find any subset of AB that also determines every other non key attribute, so I think AB is a candidate key.
Because AB is the candidate key, the relation is in the 3FN if it's in the 2FN and to be in the 2FN, for every non key attribute X, AB->X is elementary, i.e., there can't be a subset of AB that also determines X; this condition already fails in AB->D, as A->D.
Even if it was in the 2FN, it can't also be in the 3FN, as there can't be any non key attribute that depends on another non key attribute, but there cleary are, e.g., D->C.
Am I doing anything wrong, as I can't see how this relation is in the 3FN, but I have to prove it is so.

A relation is in third normal form when for all the FDs X -> Y of a cover F of the dependencies of the relation we have that either X is a superkey, or Y is prime. So we can prove that R is in 3NF if we can show that this condition is true for all the dependencies of F.
The relation has three candidate keys (you can easily see that the closure of each of them contains all the attributes of the relation):
AB
BC
BD

So the only non-prime attribute is E. So, BC->AE has a determinant which is a superkey, while A->D and D->C have a determinate which is a prime attribute.
So all the dependencies satisfy the condition and the relation is in 3NF.


Incremental backup split across files I have to move a database from one cloud provider to another. It has to be done in an hour window. The DB is 16Gb, and the line speed between the current host, and Azure (The final destination) equates to a 30 hour backup file copy. Far exceeding the time we have.

I wanted to do transnational replication, and once the main data volume is replicated over a few days, simply switch the website to use the replicated database, and kill the source.

Unfortunately, the database we are inheriting has numerous issues. Views are broken (Reference deleted tables/columns). And a lot of columns have no primary keys. (Please note, not my database. Well, it is now.. but..)

So, replication fails to even start because of the views, and then won't replicate tables with no PK.

My other option would to be do backups, and incremental backups. But do incremental backups split into separate files? If so, I could backup to a folder on the source, then move the large initial backup, and then move the incremental updates. Is that an option? Will incremental backups give me smaller files? I'd then be able to move the files over a period, and then on go live day, run the final backup, and move the small incremental file.

Absolutely, that is an option. Do the full backup, and then log backups. At the receiving end, restore all but the last using NORECOVERY.

Automating regular log backups and then restoring at the other end is what we call "log shipping", and there's a feature to do that using Agent jobs in SQL Server. Might, or might now, be relevant for you depending on your setup. Possibly not because of your cloud environment (it uses a share to store the backup files, which the originating server needs access to.

Whether to then stripe one backup over several files (not sure if you had this in mind), depends on whether it is beneficial to have several files for the transfer process. I doubt you gain much with striping for the log backups.


Open .mdf and .ldf files in Microsoft SQL Server without attach I want to open .mdf and .ldf files in Microsoft SQL Server without attach.

Are the following steps correct?

Add new database with MYDBNAME name.
Copy the files MYDBNAME.mdf / MYDBNAME_log.ldf to this path C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA (make sure to stop SQL services (SQL Server & SQL Server Agent))
Start SQL services again.

I tried these steps put after creating a new database, MYDBNAME.mdf and MYDBNAME_log.ldf created in DATA folder automatically. (I want to copy these file to DATA folder manually)

How can I add new database MYDBNAME without creating MYDBNAME.mdf and MYDBNAME_log.ldf files in DATA folder?

You can't add a database that way, you will need to attach the database using the files you have as follows:

Copy the files to the correct location.
Attached the database with EXEC sp_attach_db '<name>', '<name.mdf>', '<name.ldf>' or using the relevant GUI command in SSMS (if the files are not in the SQL instance's default directories you will need to provide a full path rather than just the filename).

In the question you say "without attach" which might mean you don't want to use this method (or that you can't use the attach command in SSMS for some reason, or <other reason> - could you explain that part?