Delete query from SQL Server 2008 to linked Postgres I want to delete some data from SQL Server 2008 in linked PostpresSQL.
I found this solution:

--DELETE data
DELETE FROM
OpenQuery('MSDASQL','SELECT  name  FROM sometable wHERE name LIKE ''test%'' ')

but how can I delete some data from SQL Server query in PostgresSQL?

Query from SQL Server

select name from myMSSQL_table

DELETE data on linked server if user rights maped properly
run the same as normal SQL DELETE command, with full object names:

DELETE FROM [PGSQL].[demodata].[dbo].[tbl_Master]
SELECT * FROM [PGSQL].[demodata].[dbo].[tbl_Master]

  [Name of Linked Server].[database].[schema].[table]


Transaction Log Shipping Primary Server Status not Shown on Secondary Monitoring Server I setup Log Shipping. I have Secondary Destination Server as Monitoring server also. For some reason when running this system stored procedure or looking at Server-->Reports-->Transaction Log Shipping Status:

EXEC sp_help_log_shipping_monitor

Secondary Destination (also Monitoring): I only see rows for Secondary Destination, do not see any rows for Primary

Primary Server: I see rows for Primary, even though this is not the monitoring server,

My Monitor logins are temporarily SA on both servers, to make sure its not security issue, so not sure why is Primary is not showing on Secondary Monitoring server. Is this the way Log Shipping is supposed to work by functionally ?

Have you configured your log shipping correctly?

I've reproduced the issue on SQL Server 2014. So to fix it, you need to setup the correct connection of your secondary/monitor instances using SERVERNAME\INSTANCENAME. Check this link.

  ROOTCAUSE: When the setting up logshipping, for remote monitor server, we check the output of SELECT @@SERVERNAME with that of the name provided in remote server. So if the two names do not match, for example if you are using an alias or a port number or a tcp protocol, then logshipping status report will not show any configuration information. The report will not even depict the primary\secondary server or any files being backed up, copied or restored depending on whether the monitor is a remote monitor or local to Primary/Secondary.

  This behavior is observed when the monitor server is a Remote SQL Instance and we are not connecting to it via servername\instance name which is also the output of SELECT @@SERVERNAME query executed on monitor server itself, but rather connecting via an alias or a protocol or a port number. This happens in SQL Server 2005 as the Primary/Secondary server instances use Linked Servers to connect to the Monitor Server instance.

Once you properly configure log shipping use the follow command below to check the report:

SELECT @@SERVERNAME;
GO
EXEC sp_help_log_shipping_monitor;
GO


Importing datetime fields in "m/dd/yyyy" format using PostgreSQL COPY command I'm looking to import a series of CSV files that contain a datetime field in the format of m/dd/yyyy h:mm:ss into a PostgreSQL table using the native /copy command.

I get the following error on import:

ERROR: date/time field value out of range: "5/13/2010 0:00:00"
HINT: Perhaps you need a different "datestyle" setting.

I tried setting the datestyle setting to ISO,MDY and SQL,MDY to no avail.I also attempted to modify the field type in the target table to character to force the fields to be read as strings, but that didn't work either.

Any ideas? Ideally I would like to have the data directly read into the target table as dates, but would settle for character strings. Thanks in advance!

Test Case

PostgreSQL Target Table:

CREATE TABLE testtable
(
    count_date timestamp without time zone,
    location character(40),
    count smallint
)
WITH (
    OIDS=TRUE
);

Data:

"count_date","location","count"
5/12/2010 0:00:00,"SEGMENT A",50
5/12/2010 0:00:00,"SEGMENT B",40
5/12/2010 0:00:00,"SEGMENT C",30
5/13/2010 0:00:00,"SEGMENT A",20
5/13/2010 0:00:00,"SEGMENT B",10
5/13/2010 0:00:00,"SEGMENT C",5

I'd recommend always using ISO date format for any kind of data interchange.

Consider Google's Crush Tools convdate for a quick fix:

  convdate --delim ',' --field 1 --preserve_header --input_format '%m/%d/%Y %I:%M:%S' --output_format '%Y-%m-%dT%H:%M:%S' < myData.csv |
  psql -c '\copy foo from stdin with (format csv, header true)'

0:00:00 is probably not a valid time though. Use csvquote if your data has embedded commas or newlines.


Migrating or Synchronizing SSAS Changes Assume you have 2 SSAS databases, DB1 and DB2, that are identical.

Now they branch off. Changes A and B are made to DB1 and changes C and D are made to DB2.

Is there an easy method to migrate changes A and B from DB1 to DB2 without overwriting changes C and D made in DB2?

I'm using SSAS 2005 - does this functionality improve in 2008? Third-party tools that do this?

You might not consider it as "easy", but I've done it a few times via the following method, which had the virtue of being easier than the alternatives of checking every setting in BIDS.

In BIDS you can create a New project and import it from your existing DB1 (and then DB2.)  Now you have Solution folders/files for each DB.  You can use WinDiff to compare the folders and XML files within them and sync them by hand, if you're bold enough and/or the differences are small enough. You have to use some intelligence and intuition which object IDs you may need to copy/change and which to leave alone.

If you can't get that to work, the WinDiff-generated XML differences should at least remind you and/or point you to what has been changed in BIDS and you can make changes to the DB2 project files by hand via the normal BIDS UI.


Exporting MySQL Database into single SQL File in TOAD Using TOAD, is it possible to export an entire MySQL database to a single file?

This feature is not available in TOAD for MySQL. See the answer to the question below:

https://stackoverflow.com/questions/4240173/how-do-i-generate-a-script-of-all-database-objects-in-toad-for-mysql

Kind Regards, MB


How do I select distinct one column and return the other column data that has been distincted? I am newbie to SQL and PHP programming so please be patient with me. :)

For example I have one table with three fields: ID, ITEMNAME, SIZE, and data like this:

+----+--------------+-------+
| ID |   ITEMNAME   | SIZE1 |
+----+--------------+-------+
|  1 | Blue T-shirt |   M   |
+----+--------------+-------+
|  2 | Blue T-shirt |   L   |
+----+--------------+-------+
|  3 |  Red T-shirt |   M   |
+----+--------------+-------+

And I want to display it using SQL command as:

+--------------+-----------------+
|   ITEMNAME   |       SIZE      |
+--------------+-----------------+
| Blue T-shirt | select tag(M/L) |
+--------------+-----------------+
|  Red T-shirt |        M        |
+--------------+-----------------+

The items with the same name will merged and create a select tag with the option of the size column data. How can I do that?

Any other way will be appreciated.

Assuming you are using MySQL

Here is how I'd do the SQL:

SELECT
ID,
ITEMNAME,
CONCAT("select tag(",GROUP_CONCAT(DISTINCT size
                                  ORDER BY size DESC
                                  SEPARATOR "/"), ')') AS SIZE
FROM items
GROUP BY ITEMNAME

Results:

| ID |      ITEMNAME |            SIZE |
|----|---------------|-----------------|
|  1 |  Blue T-shirt | select tag(M/L) |
|  3 |   Red T-shirt |   select tag(M) |

SQL Fiddle

Once you have it in PHP you can then iterate it out.


INSERT performance degradation in SQL Server after a certain number of columns I’m working on an application which has a feature of export of tabular data into a specified database table. The app uses INSERT statements to export its data to a target database.

Insert is done through a batched INSERT statement with 100 rows per one SQL INSERT statement (for now I can’t use BULK INSERT or bcp).

I’ve noticed that export time raises disproportionately when the number of columns in the source data exceeds some number (that number is not a fixed one and depends on size of the values, number of rows in each INSERT and so on).

For example, export of 50 000 rows (500 INSERT statements with 100 rows in each) of random strings with 100 chars each and 100 rows per INSERT takes:

3 sec with 5 columns
6 sec with 10 columns
56 sec with 15 columns
77 sec with 20 columns

Note the difference in export time between 10 and 15 columns. I was expecting that export time of 15 columns will be 9-10 sec, but it’s actually 5 times longer. I was able to find similar performance degradation while testing export of other datasets.

In order to be sure that the problem is not on my side, I ran the sames set of INSERT statements through sqlcmd.exe. I got similar results.

Question: What can I do to make SQL Server to work with large number of columns as fast as with a small one? Or at least “move” the point of performance degradation to a larger number of columns?

Additional details:

INSERT queries were executed on a local SQL Server Express 2014 (64-bit), version 12.0.5000.0;
Database recovery model is set to Simple;
All the INSERT statements were wrapped in a single transaction (I tried to call COMMIT after each INSERT, but the results were pretty much the same);
Target table was created before each test. It was a simple table without any indexes, foreign keys, constraints, etc.;
Hard drive performance doesn’t seems to be the source of the problem because during the first two tests (with 5 and 10 columns) disk write speed of sqlservr.exe process was 10 times greater than during the last two cases.

Tables are created like this:

CREATE TABLE [Test_Table]
(
    [Column 1] VARCHAR(255),
    [Column 2] VARCHAR(255),
    [Column 3] VARCHAR(255),
    [Column 4] VARCHAR(255),
    [Column 5] VARCHAR(255)
)

The data looks like this (each cell actually contains 100 chars long string, all the string in the same row are equal):

+------------+------------+------------+------------+------------+
| [Column 1] | [Column 2] | [Column 3] | [Column 4] | [Column 5] |
+------------+------------+------------+------------+------------+
| R6YZ..uWaQ | R6YZ..uWaQ | R6YZ..uWaQ | R6YZ..uWaQ | R6YZ..uWaQ |
| DMNW..Kh0a | DMNW..Kh0a | DMNW..Kh0a | DMNW..Kh0a | DMNW..Kh0a |
| GKbg..yuap | GKbg..yuap | GKbg..yuap | GKbg..yuap | GKbg..yuap |
| pG+f..64bX | pG+f..64bX | pG+f..64bX | pG+f..64bX | pG+f..64bX |
| O2Q7..fTNF | O2Q7..fTNF | O2Q7..fTNF | O2Q7..fTNF | O2Q7..fTNF |

Here are two examples with reproduce the issue:

http://rextester.com/OZI56670 (10 columns, ~0,09 sec)
http://rextester.com/HLAP4972 (11 columns, ~0,45 sec)

The difference between the repro that you have posted with 10 columns and 100 rows and 11 columns and 100 rows is that the execution plan for the first one uses Simple Parameterization.

The actual execution plan for the 10 columns lists parameters from @1 to @1000.

11 * 100 is 1100. But one thousand seems to be the maximum number of parameters an auto parameterised query can reach.

You are doing 10 inserts for each. In the 10 column case the plan can be compiled once and reused for the other 9 inserts. In the 11 column case each insert statement needs to be compiled individually.

Moreover the process of compilation takes longer when SQL Server has the literal values to look at as it spends time working out properties of the group (or at least this used to be the case I'm not sure if this has changed in more recent versions).


PostgreSQL hardware reguirements What is the memory/cpu/other requirements to run PostgreSQL database server efficiently ? Is a way to appoint a hardware configuration for some sort of requirements - for example number of users, number of queries etc ?

PostgreSQL is open source and you can use on almost any Linux/UNIX-like machine, plus Windows and Mac OS X. See the buildfarm for a list of known-working configurations. Precompiled binary installers and/or packages are available for popular platforms. I had some problems compiling it on AIX, but it was because I used gcc instead of IBM's XLC.

The minimum RAM you will need is 32MB, and you'll need least 50KB of disk space. As with any other database everything depends of the queries you are going to do, how many users you will have, what your performance requirements are, etc.

If you can, try to divide your DB between different physical hard drives (eg WAL on one, main tables + indexes on another), tune postgresql.conf to fit your machine, etc.

I recommend that you to read Greg Smith's book PostgreSQL High Performance.


Searching large dataset, best way to speed this up i'm querying a table full of vehicles, however each one of these vehicles MUST have another row within another table. This query is currently taking way too long!
Do I need to index the table, or is there another way I can do the query to improve this?
SELECT *
FROM vehicles v
WHERE v.group = 0
AND EXISTS (
    SELECT count(id) FROM quotes quote
    WHERE quote.vehicle_id = v.id
    AND quote.term = 60
    AND quote.annual_mileage = 8000
    AND quote.deposit = 250
    AND quote.credit_rating = "Excellent"
) IS NOT NULL

vehicles table:
CREATE TABLE `vehicles` (
    `id` INT(11) NOT NULL DEFAULT '0',
    `group_id` INT(11) NOT NULL DEFAULT '0',
    `title` VARCHAR(8) NOT NULL DEFAULT '' COLLATE 'latin1_swedish_ci'
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

quotes table:
CREATE TABLE `quotes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `vehicle_id` INT(11) NOT NULL,
    `credit_rating` VARCHAR(20) NOT NULL COLLATE 'latin1_swedish_ci',
    `deposit` DECIMAL(7,2) NOT NULL,
    `term` TINYINT(2) NOT NULL,
    `annual_mileage` INT(11) NOT NULL
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `vehicle_id` (`vehicle_id`) USING BTREE,
    INDEX `covering_ndx1` (`term`, `annual_mileage`, `deposit`, `credit_rating`, `vehicle_id`) USING BTREE,
    CONSTRAINT `FK_quotes_vehicles` FOREIGN KEY (`vehicle_id`) REFERENCES `database`.`vehicles` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

Edit:
Updated the create SQL above to reflex the changes I have made. This reduces my query from 0.516 to 0.032

Yes, an index on quote.vehicle_id would help this greatly.
As a general rule† it is a good idea to have an index on columns that are foreign keys, and you should define it as a foreign key with:
FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)

This way the database will help verify your data and prevent a family of potential bugs happening.
Also you will find mySQL's query planner/engine produces a more efficient plan if you use WHERE EXISTS. Your sub-select with GROUP BY will force it to run that part once per outer row when more efficient strategies could be available. Also EXISTS can exit early, once the first matching row is found, but a subquery with an aggregate as above will stop that happening so more rows may be read and considered which is not needed:
SELECT *
FROM vehicles v
WHERE v.group = 0
AND EXISTS (
    SELECT id FROM quotes quote
    WHERE quote.vehicle_id = v.id
    AND quote.term = 60
    AND quote.annual_mileage = 8000
    AND quote.deposit = 250
    AND quote.credit_rating = "Excellent"
    )

You still want the index on quote.vehicle_id for this to avoid full table scanning.
† There are valid exceptions to this rule, which is part of why most database engines don't create such an index automatically, but they are fairly rare.


Multiple vs Single COUNT on same col + Multiple vs Single col index I have two very simple questions
1. Multiple vs Single COUNT on same col
I need to perform CASE on an aggregate column like this:
SELECT tbl_students.school, COUNT(tbl_students.id) as students, (
  CASE
     WHEN students > 1000 THEN 'SMALL'
     WHEN students > 10000 THEN 'MEDIUM'
     WHEN students > 100000 THEN 'LARGE'
  END
) as size
FROM tbl_students
GROUP BY tbl_students.school

But MySQL does not allow using the column alias within the SELECT part. So instead of above, I'll have to write:
SELECT tbl_students.school, COUNT(tbl_students.id) as students, (
  CASE
     WHEN COUNT(tbl_students.id) > 1000 THEN 'SMALL'
     WHEN COUNT(tbl_students.id) > 10000 THEN 'MEDIUM'
     WHEN COUNT(tbl_students.id) > 100000 THEN 'LARGE'
  END
) as size
FROM tbl_students
GROUP BY tbl_students.school

My question is "will using COUNT multiple times on the same column actually performs COUNT multiple times? If yes, will it also result in performance issues when there are millions of records?".
2. Multiple vs Single col index
If a table has following three columns:
tbl_students(id, username, school, firstname, lastname)
And has composite unique key for username and school. My question is, do I need to create a separate index for school column if I am searching only in school column?

This is straightforward enough and more efficient:

SELECT  school,
        CASE
           WHEN students > 1000 THEN 'SMALL'
           WHEN students > 10000 THEN 'MEDIUM'
           WHEN students > 100000 THEN 'LARGE'
        END as size
    FROM ( SELECT school,
                  COUNT(*) as students
              FROM tbl_students
              GROUP BY school
         ) AS cts

It would need INDEX(school) or any index starting with school.


Is there a SELECT statement to subtract rows of the same column? Column 1 is the mem_id, Column 2 is the Transaction type C and D, and Column 3 is the Amount Owed. So I want to return Column 3 "Amount Owed", but it has to be C-D in Column 2 for each member. thanks, I hope that helps.

If I understood your question correctly...

SELECT mem_id,
       Sum(CASE TranType
             WHEN 'C' THEN Amount
             WHEN 'D' THEN -Amount
           END) AS Amount_Owed
FROM   YourTable
GROUP  BY mem_id;


Find all items where the 2 latest statuses meet condition I have a table in a PostgreSQL database like this:

ID|Item-FK |timestamp|status
=============================
1 | 123    | ...     | EXPIRED
2 | 123    | ...     | PENDING
...

I want to query all items where the last two statuses are 'EXPIRED' & 'PENDING' like displayed in the sample above - 'PENDING' latest and 'EXPIRED' immediately before that.

CTE add a row number in descending order, that means that row (1) must be PENDING and row (2) must be EXPIRED.

Then you can use 2 EXISTS subqueries in the WHERE clause to ensure both conditions are true.

with ct as
(
select
    id, itemfk, dt, status,
    row_number() over (partition by itemfk order by itemfk, dt desc) rn
from
    t
order by
    itemfk, dt
)
select
    t1.itemfk
from
    ct t1
where exists (select 1
              from ct
              where itemfk = t1.itemfk
                    and rn = 1 and status = 'pending')
      and
      exists (select 1
              from ct
              where itemfk = t1.itemfk
                    and rn = 2 and status = 'expired')
group by t1.itemfk;

db<>fiddle here


Incremental backups using MySQL Workbench I need to start doing regular backups of my databases, and I'd like them to be incremental; that is, only backing up the tables that have been changed. I use MySQL Workbench, and I've used the export function in the past, which works well enough, but I can't seem to find a way to make it incremental and not re-backup the entirety of every database every time. I've got about 90 schemas in total. Any help at all is much appreciated. Thanks!

To be frank, if you are using a GUI tool as your backup strategy, you are doing it wrong.  Backups need to be scripted, automated, and regularly tested or else you might as well not have them.  If your database is very small (a few gigabytes), there is no point in doing incremental backups: backup the entire database with mysqldump and use binary logs in statement format for point in time recovery, or using a slave, shut down the slave an archive the binary files (cold backup).

If you are using InnoDB and really need incrementals, there is Xtrabackup from Percona or innodbhotbackup.

Xtrabackup is a great tool which I highly recommend.  It will perform true incremental backups with minimal impact to the server.


ORA-1110, ORA-1114, OSD-4002, O/S-Error: (OS 33) The Process Cannot Access The File Hi All,

We have a problem today wherein in Oracle we are trying to execute a package (every morning) and this is the only time we encountered the error (ORA-0114).

Any ideas how to fix this? We have already extended the SYSTEM and TEMP tablespace space.

And the crazy thing is, we tried yesterday and recompiled the package (we are using TOAD) and it worked. But today, it didn't work again even though it was already recompiled (same error).

BTW, line 273 was the part INSERT INTO SELECT statement.

I tried to execute the SELECT statement on the query window, and it also gets the same error.

Thanks !

The error is specific to Windows platform, where the file is being accessed by another process, probably non-Oracle processes such as anti-virus, OS backup or something else.

Make sure there is no other non-Oracle process accessing the data file #201 when you are running that procedure.

Resolving ORA-27072 , OSD-04008 and O/S-Error: (OS 33) On Windows Platforms (Doc ID 2016620.1)

Update:

In order to find which application is using a specific file-

Download Handle from here, if you don't have already.
Extract the downloaded .zip file in your drive.
Run handle.exe <path to your datafiles> on command prompt.


What type of replication do I need to push webdata to offline database for analytics? I'm new to replication, as I've been mostly an old school ETL type person.
I have a SQL SERVER database that runs a front end web presence.  There's alot of data there.
I have been tasked to perform analytics on the web database.  However, in order to do so, I must send an extreme number of queries to the database.  This is making the db sluggish.
WHen speaking with another analyst about my problem, they stated that in the past they solved this by creating another instance on another server and set replication so I can use the Replication database and not put the pressure on the web instance.
My question is the type of replication.  When researching types of replication, I see Transactional, Merge, etc....
Is there a specific type of replication that I need for this case?  If I know what I'm hunting for, I can get the steps from there.
Thanks!

It sounds like you will be replicating from 1 db server (the web backend) to 1 reporting server and that the data will all be flowing in one direction. If what I wrote is correct I think that transactional replication will be the best fit to your needs - one of the nice things about replication is that you don´t have to include all the tables (indeed you can filter out columns or rows if required). Start by checking the tables you want to replicate have a primary key defined.


Is there any performance reason to append an unused AUTO_INCREMENT column in MySQL 5.6? In my work environment, MySQL has some weird stuff with performance, and one of the other devs wanted me to research this situation before dropping a column that has the AUTO_INCREMENT attribute.

Scenario

We have a table that has a FOREIGN KEY that is used to INDEX into it.  It also currently has a column with the AUTO_INCREMENT attribute that is defined as a KEY, but that KEY is completely unused.

Question

Just wanting to confirm that MySQL is not doing something weird, and if having no column with the AUTO_INCREMENT attribute on a table is going to slow things down.

Consideration

I would like to drop the unused AUTO_INCREMENTed column, and it seems highly likely this action can only speed things up for us.

Yes and no.

(The following applies to all versions, but only to ENGINE=InnoDB.)

An InnoDB table always has a PRIMARY KEY:

You could specify one; else
A UNIQUE key with non-null column(s); else
A hidden 6-byte number.  (This number does not otherwise exist.)

If you remove your PK, then option 2 or 3 will kick in.  If option 3 kicks in, each row will be 2 bytes bigger -- assuming your id is a 4-byte INT now.  This is a tiny performance hit.

The data in the table is ordered according to the PRIMARY KEY:

With AUTO_INCREMENT or the hidden key, new inserts are being "appended" to the table;
With some other UNIQUE or PRIMARY key, the rows are inserted 'where they belong'.  This is a tiny bit less efficient.

When you need to do maintenance on the table, you often need a unique way to identify each row.  For this reason, alone, I advocate having a visible, explicit PRIMARY KEY.

Which is a "better" PRIMARY KEY:

An AUTO_INCREMENT?
A "natural" key made up of one or more columns that are naturally unique?

Answer:  It depends.  Since that is not the topic of your question, I will defer the elaboration to another Q&A.  Also, since the PK is an index, there could be performance considerations for indexing.


Overhead associated with DBCC CHECKTABLE? Have a logshipped VLDB and don't run DBCC CHECKDB on primary due to the long running periods of high CPU and tempdb usage.   Have read about DBCC CHECKTABLE as a possibility for a more granular data integrity checking option.  Does DBCC CHECKTABLE have overhead levels similar to DBCC CHECKDB?  Can it be run during business hours without adversely affecting server CPU and TempDB performance?

The code behind CHECKTABLE is indeed likely the vast amount of time spent by CHECDB, as mentioned by @Mo64. I.e., the "sum of CHECKTABLE" should roughly be the same as a CHECKDB, one can imagine.
However, by default CHECKTABLE uses a database snapshot. Not a "table snapshot", since there is no such concept. I.e., you can check one table and if modification occurs on some other table at the same time, the original for that data need to be saved in the snapshot file first. Here is one aspect where the "sum of CHECKTABLE" can exceed one CHECKDB.
Anyhow, your question is about overhead. Yes, pretty much the same (or rather, it won't be (much) less for CHECKTABLE. You have the database snapshot and copy-on-write for the whole database (although the snapshot would live for a shorted time when you check only one table). And SQL server should need space in tempdb as well.
Above is from theoretical reasoning, I should add.


Will an ALTER TABLE ADD COLUMN on a slave break replication? I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with one slave.

Obviously, running the necessary ALTER TABLE command on the master is unacceptable since it locks the table for well over a half-hour. I cannot afford this kind of downtime at ANY time of day or night, most unfortunately.

A few sources have suggested some form of the following:

Disable replication to the slave
Run the ALTER TABLE command on the slave
Switch replication back on and wait for slave to catch up
Promote slave to master

Is this a viable solution? It would seem to me that all new tbl_items inserts would be lost between the beginning of step 2 and step 4; that is, is it possible for tbl_items data to replicate throughout the interval that it's missing the new column?

If that's the case, is there an alternate solution that eliminates the possibility of both downtime and data-loss?

I think that, if your purpose is to avoid locking, you should use pt-online-schema-change (a tool from Percona Toolkit) or Gh-ost (from GitHub).

Here is how pt-osc works:

Creates a ghost table identical to the original table, but empty
Runs ALTER TABLE on the ghost table
Creates triggers on the original table, which insert new rows in the new table, and updates and deletes them if they are already there.
It copies the rows in chunks, monitoring the server performance (it can slow down or pause its work if necessary)
Switch the table names

gh-ost is very similar, the main difference is that it creates no triggers, and uses the binary log instead to detect the changes to the original table.

To answer your question more directly - yes, it is possible to add a column on the slave. And most probably it will simply work, as long as it has a default value. But it is very likely that in the future you will regret this choice, because you'll need to do something that will break replication, like adding a column in the master. I recommend to sure to check the documentation and fully understand the dangers, before doing such a thing.


SQL Server 2014 - Disabled All Admin Users I made the most ridiculous thing. I accidentally disabled both remote logins and database accesses for the Administrator accounts (including the one with Windows Authentication (Administrator account) and sa account on the production server! Please tell me a way that there is a way to access the server with administrator privileges! :(

Is it possible from an installation or some kind of command line etc?

Again, Administrator account IS disabled for SQL Server (but I have remote connection to server, the account itself is working...) So I literally cannot access to the sql instance with admin privileges.

Yes, you can get back in.  :)

Go into SQL Server Configuration Manager
Select SQL Server Services
On the right side of the pane, find your instance and right-click for Properties
On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add. (That's a dash then lower case letter m.)
Click OK and restart.
MSDN suggests launching Management Studio as an administrator for 2014.  The traditional way is to use SQLCMD -E.  Either way.

(If it doesn't let you because that account is explicitly denied, close the window, run the command line as a different user, and try again.)

Once you're connected, you should be able to create a new login

CREATE LOGIN [YOURDOMAIN\SomeUser] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [YOURDOMAIN\SomeUser];

or

CREATE LOGIN TempLogin WITH PASSWORD = 'SomeReallyGoodPassword';
ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;

and/or enable the disabled accounts.

ALTER LOGIN yourLogin ENABLE
ALTER LOGIN AnotherLogin ENABLE

Because this is production and this requires a restart, you'll have to do it after hours.  Good luck!


Mitigating MySQL replication delay in single master/multiple slave setup For a particular web application that requires high availability I am thinking of scaling out by setting-up MySQL replication with one "write" master and multiple "read" slaves (as explained here http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-scaleout.html).

The only issue that I have is how to mitigate the replication delay between the master and the slaves? For example, let's say that the user posts an item (writing to the master), and very quickly thereafter wants to view his posted items (reading from one of the slaves). What efficient solution can I put in place to make sure that the a read operation from one of the slaves will always have all the content of any previously completed write operation to the master?

Since "native" MySQL uses asynchronous replication, there is no system-wise way to guarantee nor force a slave to apply a master's statement at any given time.

Semi-sync replication shortens this lag time by at least ensuring your statement is recognized by the slave -- but does not go as far as making sure it has been applied on your slave.

This is a common issue. Ultimately, your application needs to be replication-aware, such that your query "knows" it is being executed on master or on slaves.
In your case, the common solution is to make sure a SELECT query which immediately follows a write operation is executed on master.

You may choose to use synchronous solutions: MySQL NDB Cluster or Galera Replication. Even with Galera you would need a special configuration to make sure a consistent write-and-read is successful.


What is actually happening when postgresql discovers the trigger file and promotes it self from slave to master? I have a streaming replication setup between two postgres servers (master: server A, slave: server B). I'm wondering what's actually happening under the hood when I touch the trigger file and the slaves takes over as master?
One of the things that I wonder, and no one seems to mention anywhere in either tutorials or documentation is, what if I restart the former slave (now master) after this is done? Won't things get messed up, because the .conf file still reflects the old slave setup (e.g. hot_standby = on)? Should I update that .conf, before I can safely restart the server?

Background: The situation I'm in, that made me wonder all of this is that I need to change the HDD's on my master. This is the way I plan to do it:

Failover to the slave (server B)
Change the HDD's on the master (server A) and reinstall it
Start the reinstalled server A as a slave
After server A has catched up with server B, change server A back into being the master, and then turn server B back into being slave again

(BTW. If you got suggestions to a better workflow please tell me)

A replica server (whether running as hot_standby or not) is running in what PostgreSQL calls "recovery mode". As the name suggests, this was originally developed to perform recovery from an unsafe stop: in this situation, it uses the recovery.conf file which defines how to find transaction logs (pg_xlog files) and keeps applying them until no more are available, and then exits from recovery mode into "normal" mode, and starts generating new transaction logs itself. When exiting recovery mode, the recovery.conf file is renamed to recovery.done, so that a subsequent restart goes directly to normal mode.

The changes from this process for a replication slave are:

the transaction log data may not be coming from log files, but streamed from the master server: recovery.conf specifies which
replicas do not exit recovery mode when no more data is available, they wait until the trigger is fired.

So due to the renaming of recovery.conf, restarting your newly-elected master preserves the property of it being a master.

However, one point to note: after recovery has completed and a server transitions from being in recovery to being in normal mode, it changes "timeline". Essentially, at this point you have forked your database: one version is the possible continuation of transaction logs from wherever they were coming from, and one version (the new version) is from the database that has just finished recovery. But you can only apply log files from a master server that is on the same timeline! This is why, after doing a failover, you need to rebuild your old master server from the new master, rather than just starting it to let it "catch up".

(This above paragraph is subject to some doubt as my experience with thee failovers is currently limited, but it's from my understanding of the documentation, and how the system works overall).

Also note that the hot_standby flag means only that a database cluster will accept connections and allow read-only queries even while it is in recovery mode: not a normal situation for a database that is recovering from a crash. Once a server is running in "normal mode", this setting is irrelevant, so it is not a problem to have it set on the old slave/new master.


How to update the time of each row and check if its updated or not Name
Time
Status

T1
9/12/2020 12:12:09
expected

T2
8/06/2020 11:09:12
expected

These are table name and updated time in the DB I want to get the status and time of updation of specific tables .Set the status as "expected" for tables where updation happening daily and "not expected" when when updation miss out daily

Maybe you need in
CREATE TRIGGER tr_bu_check_gap
BEFORE UPDATE
ON {tablename}
FOR EACH ROW
SET NEW.Status = CASE WHEN DATEDIFF(NEW.Time, OLD.Time) > 1
                      THEN 'not expected'
                      ELSE 'expected'
                      END;

?
PS. This is solution for MySQL only. For SQL Server it can be similar, but syntax adaptation needed.


Mongo database error in resource allocation pthread_create failed: errno:11 Resource temporarily unavailable

maximum number of open files 64000
maximum number of processes 64000

It is taking 50seconds to complete query.

How to allocate more sources?

Mostly because of memory utilization. Observed mongodb log and found that the same time at which pthread_create failed, other processes were consuming memory so no room for mongodb.


Right way to get references to an object in Oracle? I want to find all the objects referencing my object.

I came up with this query, Is this the best way to get all references to an object? (I know this doesn't include constraints, I handle that separately)

Direct References to Object

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS
       WHERE object_id IN (select object_id
                           from public_dependency
                           where REFERENCED_OBJECT_ID = :id_object)
       ORDER BY object_name

Also, on the internet, I have come across queries like the following.

Direct and Indirect References to Object

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS
       WHERE object_id IN (SELECT object_id
                           FROM public_dependency
                           CONNECT BY PRIOR object_id = referenced_object_id
                           START WITH referenced_object_id = :objectId)

However, if my understanding is correct, this query doesn't return direct references only, but also indirect dependencies.

For example if I want the references for object_A. If object_B references it, and object_C referenced object_B. This query would return object_B and object_C as references to object_A. Am I right?

After talking with some people with more Experience in Oracle than me, they told me I was right in my understanding.

This query returns the references to an object (except the constraints)

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS
       WHERE object_id IN (select object_id
                           from public_dependency
                           where REFERENCED_OBJECT_ID = :id_object)
       ORDER BY object_name


Performing SELECT on EACH ROW in CTE or Nested QUERY? This is a problem in PostgreSQL

I have a table which stores the tree of users;

    +------+---------+
    |  id  | parent  |
    |------+---------|
    |  1   |   0     |
    |------|---------|
    |  2   |   1     |
    |------|---------|
    |  3   |   1     |
    |------|---------|
    |  4   |   2     |
    |------|---------|
    |  5   |   2     |
    |------|---------|
    |  6   |   4     |
    |------|---------|
    |  7   |   6     |
    |------|---------|
    |  8   |   6     |
    +------+---------+

I can query a complete tree from any node by using the connectby function from the tablefunc extension, and I can separately query the size of tree in terms of total nodes in it, for example

  tree for #1 has size 7
  tree for #5 has size 0
  tree for #6 has size 2, and so on

Now I want to do something like Selecting all possible trees from this table (which is again carried out by connectby), count the size of it and create another dataset with records of ID and size of underlying tree, like this:

    +------------------+-------------+
    |  tree_root_node  |  tree_size  |
    |------------------+-------------|
    |      1           |     7       |
    |------------------+-------------|
    |      2           |     3       |
    |------------------+-------------|
    |      3           |     0       |
    |------------------+-------------|
    |      4           |     3       |
    |------------------+-------------|
    |      5           |     0       |
    |------------------+-------------|
    |      6           |     2       |
    |------------------+-------------|
    |      7           |     0       |
    |------------------+-------------|
    |      8           |     0       |
    +------------------+-------------+

The problem is, I am unable to perform the same SELECT statement for every available row in original table in order to fetch the tree and calculate the size, and even if I could, I dont know how to create a separate dataset using the fetched and calculated data.

I am not sure if this could be simple use of some functions available in Postgres or I'd have to write a function for it or simply I dont know what exactly is this kind of query is called but googling for hours and searching for another hour over here at dba.stackexchange returned nothing.

Can someone please point to right direction ?

Let me know if this helps.  This is using MSSQL (T-SQL) syntax so you might have to adjust it for postgres, but, using a temp table called #tree, containing the two columns id and parent and populated as your example is with

SELECT 1, 0
UNION
SELECT 2, 1
UNION
SELECT 3, 1
UNION
SELECT 4, 2
UNION
SELECT 5, 2
UNION
SELECT 6, 4
UNION
SELECT 7, 6
UNION
SELECT 8, 6:

with cte as
(
    select id as currentnode, id as root
    from #tree
    union all
    select t.id as currentnode, cte.root
    from #tree t join cte on t.parent = cte.currentnode
)
select *
from cte
order by root

The above recursive CTE will yield all nodes reachable starting from some root.  You can do:

SELECT root, count(*)
from cte
group by root

The count(*) query should yield,

root   |  count
----------------
  1    |    8
  2    |    6
  3    |    1
  4    |    4
  5    |    1
  6    |    3
  7    |    1
  8    |    1

You could then subtract 1 from each of those to get the result you were hinting at.  Is this what you were looking for?  Even if not, I feel like a recursive CTE can probably help with what you need.


Does a data type affect performance in a view? I just noticed that in our of our databases we have a view that converts a BIT column to an INT. The underlying table has a computed BIT column which is defined like this:

IncludeInJobTotals as (CONVERT(bit,
case
when FL.[Status]=(5)
  or FL.[Status]=(3)
  or FL.[Status]=(2)
then (1)
else (0)
end,(0)))

In a view that uses IncludeInJobTotals we are casting it as an INT. Are there any performance problems that this can cause? What other issues might there be? Are there any reasons to use INT over BIT?

To answer the general question first, yes, data type in views absolutely can affect performance. Let's mock up some data to illustrate a trivial example:

DROP TABLE IF EXISTS X_162798;

CREATE TABLE X_162798 (
NUM_VARCHAR VARCHAR(10),
NUM_INT INT,
[Status] INT,
IncludeInJobTotals as (CONVERT(bit,
case
when [Status]=(5)
  or [Status]=(3)
  or [Status]=(2)
then (1)
else (0)
end,(0)))
);

-- insert 1 M rows with 999990 of them having a value of 1 for IncludeInJobTotals
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO X_162798 WITH (TABLOCK)
SELECT
CAST(n AS VARCHAR(10))
, n
, CASE WHEN n <= 10 THEN 0 ELSE 5 END
FROM Nums
WHERE n <= 1000000;

UPDATE STATISTICS X_162798 WITH FULLSCAN;

-- index all the columns
CREATE INDEX IX_162798_NUM_VARCHAR ON X_162798 (NUM_VARCHAR);
CREATE INDEX IX_162798_NUM_INT ON X_162798 (NUM_INT);
CREATE INDEX IX_162798_STATUS ON X_162798 ([Status]);
CREATE INDEX IX_162798_JOB ON X_162798 (IncludeInJobTotals);

DROP VIEW IF EXISTS V_162798;

GO

CREATE VIEW V_162798
AS
SELECT
CAST(NUM_VARCHAR AS INT) NUM_INT_CASTED
, NUM_VARCHAR
, IncludeInJobTotals
, CONVERT(INT, IncludeInJobTotals) IncludeInJobTotals_INT
FROM X_162798;

GO

The following two queries return the same results but the performance is very different:

-- this query does a table scan and does an estimate based on density
SELECT *
FROM V_162798
WHERE NUM_INT_CASTED = '1';

-- this query does an index seek and does an estimate based on the histogram
SELECT *
FROM V_162798
WHERE NUM_VARCHAR = '1';

If I do the wrong type of data type conversion the filter is no longer sargable. It cannot use an index on the column and cardinality estimates offer suffer because the histogram cannot be used.

Does it matter in your case where you cast a BIT column to an INTEGER? We can filter on the IncludeInJobTotals and IncludeInJobTotals_INT columns in the view to run a few tests. Based on data type precedence we should expect that a BIT will be converted to an INTEGER in any comparison between a BIT and an INTEGER. SQL Server should be able to do an implicit conversion between them so I think that it will be difficult to find a performance problem.

Since we're dealing with a bit column there aren't that many possible test cases for simple filters. If I filter on either column of the view looking for rows with a 0 value I always get index seeks and a correct cardinality estimate:

-- all queries do an index seek and estimate 10 rows will be returned
SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = 0;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = 0;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = CAST(0 AS BIT);

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = CAST(0 AS BIT);

If I filter on either column of the view looking for rows with a 1 value I always get a table scan and a correct cardinality estimate:

-- all queries do a table scan and estimate 999990 rows will be returned
SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = 1;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = 1;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = CAST(1 AS BIT);

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = CAST(1 AS BIT);

Looking for out-of-range values works as expected too:

-- these queries do an index seek and estimate 1 row will be returned
SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = 2;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = 2;

I wouldn't worry about performance for this type of data type change. It's hard to imagine a scenario in which you would run into performance issues. However, it is important to note that queries using the view can return different results depending on how you use the column. The BIT data type behaves differently from INT:

  Converting to bit promotes any nonzero value to 1.

Going back to the data type precedence list, a VARCHAR value will be converted to a BIT value. This means that one way to get different results is to filter or join on an out-of-range VARCHAR value:

CREATE TABLE TABLE_WITH_THE_NUMBER_2 (NUM VARCHAR(1));
INSERT INTO TABLE_WITH_THE_NUMBER_2 VALUES ('2');

-- this query returns 999990
SELECT COUNT(*)
FROM V_162798 v
INNER JOIN TABLE_WITH_THE_NUMBER_2 t ON v.IncludeInJobTotals = t.NUM;

-- this query returns 0
SELECT COUNT(*)
FROM V_162798 v
INNER JOIN TABLE_WITH_THE_NUMBER_2 t ON v.IncludeInJobTotals_INT = t.NUM;

Perhaps your application will never do this, but what do you want to happen in that scenario? I would focus on correctness here over performance.


How do I replace this where clause with a join? Typically when I see SQL that uses something like:

select * from employees where epmloyeeTypeId in (select id from type where name = 'emp')

I replace the where with this:

select e.* from employees e
inner join type t on t.id=e.epmloyeeTypeId and t.name = 'emp'

Is it possible to do the same with the inverse in case its is a not in (like below) instead of an in clause?

INSERT into Subscriptions(ProjectId, RecordTypeCID, NTID, Active, Added, LastUpdate, UpdateBy)
 SELECT @ProjectId, RecordTypeCID, @NTID, 1, GETDATE(), GETDATE(), @NTID
 FROM @Check CHK
 WHERE CHK.ActiveStatus=1
        And Not Exists (SELECT SubscriptionId FROM Subscriptions
                        WHERE ProjectId=@ProjectId
                        and NTID=@NTID
                        and RecordTypeCID = CHK.RecordTypeCID
                        )

Additional considerations

Can I do this:

INSERT INTO Subscriptions(ProjectId, RecordTypeCID, NTID,Active, Added, LastUpdate, UpdateBy)
SELECT @ProjectId, RecordTypeCID, @NTID,1, GETDATE(), GETDATE(), @NTID
FROM @Check CHK
    LEFT JOIN Subscriptions subs ON subs.RecordTypeCID = CHK.RecordTypeCID
        AND NTID = @NTID
        AND ProjectId = @ProjectId

        AND CHK.ActiveStatus = 1
        AND subs.SubscriptionId IS NULL

Your NOT EXISTS is more efficient in most cases.

LEFT JOIN internally matches all rows then filters to IS NULL. NOT EXISTS doesn't. This row multiplication also happens in all JOIN based code so you may need an extra aggregate (DISTINCT) to fix the output

NOT IN is generally wrong because NULLs cause no match.

You can also use EXCEPT which gives the same plan as NOT EXISTS.

SELECT @ProjectId, RecordTypeCID, @NTID,1, GETDATE(), GETDATE(), @NTID
FROM @Check CHK
EXCEPT
SELECT @ProjectId, RecordTypeCID, @NTID,1, GETDATE(), GETDATE(), @NTID
FROM Subscriptions
WHERE ProjectId=@ProjectId
and NTID=@NTID

FYI, as per the SQL-92 standard (page 191, Case 3a) the SELECT bit of the EXISTS is ignored.


Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts reference the tables that were created with previous scripts. I want to improve the bulk insert operations but also want to keep the indexes to improve query performances. Does it make sense to create the indexes using maintenance plan rather than creating them in the script? Will it improve the bulk insert performance if I create the indexes in the maintenance plan at the end of all scripts run? Thanks.

Removing the impact of updating your non-clustered indexes during the inserts is a great way to improve bulk insert performance and something we do in my own environments.  However, I think a better alternative to you than dropping the table and/or indexes, then recreating them post-insert is to instead disable then rebuild the index.  This process removes some over head of the index creation, but accomplishes the same goal of getting the indexes out of the way.

The command to do this would be:

Disable:  ALTER INDEX foo ON bar DISABLE
Re-enable/rebuild: ALTER INDEX foo ON bar REBUILD

Depending on how you execute your ETL loads, you can build this as two steps and script out the index disable and rebuild (SQL 2008 syntax):

--Disable Indexes
DECLARE @v_sql nvarchar(max);
SELECT @v_sql = '';
SELECT @v_sql += 'ALTER INDEX '+QUOTE_NAME(name,'[')+' DISABLE;'+char(10)
FROM sys.indexes
WHERE object_id=OBJECT_ID('bar') AND type =2;

EXEC sp_executesql @v_sql;

--Rebuild Indexes
DECLARE @v_sql nvarchar(max);
SELECT @v_sql = '';
SELECT @v_sql += 'ALTER INDEX '+QUOTE_NAME(name,'[')+' REBUILD WITH(ONLINE=ON);'+char(10)
FROM sys.indexes
 WHERE object_id=OBJECT_ID('bar') AND type =2;

EXEC sp_executesql @v_sql;

Note, by declaring type=2, we are only disabling/rebuilding non-clustered indexes.  You don't want to disable/rebuild your clustered indexes as part of this process.


SQL Server Create Access Denied even after adding MSSQLSERVER user permissions I am aware that SQL Server instances need write access not for my user account (which is in fact irrelevant to that service's context) but for the user account or security principal under which the SQL Server Service is running.  (In my case NT Service\MSSQLSERVER)

Having done that, and having verified that when I create a new database in this folder, I can write to this folder, and that the database files created by this server instance are created with the user MSSQLSERVER, nevertheless, I still lack the ability to write to any folder on drive E, from the context of MS SQL, unless I also add "write" permissions for "Everyone", adding write access to the "MSSQLSERVER" is not enough.

There must be some other level of NTFS security involved here. It almost reminds me of a domain trust issue.  This is on MS SQL Server 2012, running on Windows 8 Pro.

What do you do next, if adding the MSSQL account name found in the "Log On" tab of the service, does not work?  It's frustrating, but I'm stuck with giving "Everyone" write access to this folder on drive E before any folder is writeable by SQL Server.  Drive E in my case is a regular local hard drive, not a network folder. (I'm not dumb enough to try to run SQL on a network mounted drive.)

The actual problem is that the SYSTEM or other container group had not had the NTFS permissions set for the SQL db folder on the SQL data drive, in my case, 'E:\SQLDATA`

The root E:\ drive was not accessible, so a specific NTFS permission had to be created for the SQLDATA.


Manage max table storage space in case of excess data (size in GB) I am using SQL Server 2008 R2. I have created a database named testDB. I have a lot of tables with some log tables in. Some log tables contain over 100k records.

I want to fix the size of those log tables, and want to move records into another database table in another location, so my database has no problem.

Is there any way to make such above steps which I want for my database?

Is there already built any such functionality in SQL Server?

There isn't a standard option to do this, but you can achieve this several ways.

create a separate db and move your log tables there. set a max file size.
create a trigger which checks row count or table size and limits inserts when value exceeded and/or deletes old data.
create a foreign key constraint on an id column pointing to a table with a set number of rows. When your log table reaches the maximum it will stop.
add a check constraint.


SQL Server email function invalid syntax I have this sp_send_dbmail script. The error log shows "invalid syntax error".

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'PROFILE_NAME',
    @recipients = 'oops@wrongagain.com',
    @subject = ' Search for empty ID result ',
    @query = ' select * from [dbo].[TT_INCI] tt where tt.ts_caseid =''(Auto)'' ',
    @attach_query_result_as_file = 0

Please help

You need to speficy the database where the table is:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBProfile',
@recipients = 'jupiter@love.com',
@subject = ' Search for empty ID result ',
@query = ' select * from [SomeDatabase].[dbo].[TT_INCI] tt where tt.ts_caseid =''(Auto)'' ',
@attach_query_result_as_file = 0

The error message is a bit misleading.


How to install the additional module pg_trgm I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index.

WHERE foo LIKE '%bar%';

pg_trgm is an extension, so:

CREATE EXTENSION pg_trgm;

If you get the following error

  ERROR:  could not open extension control file ".../extension/pg_trgm.control":
  No such file or directory"

then you need to install the module for your operating system

Ubuntu/Debian:

sudo apt install postgresql-contrib

Redhat/Centos

sudo dnf install postgresql10-contrib

Fedora

sudo dnf install postgresql-contrib


how to get data from two tables in mysql query with where Pk of one table is set as foreign key on PK column for other table? The query I am using to retrieve data is as

"select sum(totalbill) as tbdb, sum(discinamount) as tddb, sum (billafterdisc) as tbaddb,"
+ " sum(totalqt) as tqdb,"
+ " sum(payed) as tpdb,"
+ " sum(balance) as tbaldb,"
+ " `customer`"
+ " from `orderacc` join `orders` using (`orderaccid`)"
+ " group by `customer` order by max(`date`) DESC";

I am getting error that orderaccid is not a column in clause.

Well, your orders table doesn't have a column orderaccid. You can use using only when the column names are equal. Use this instead:

from orderacc join orders on orderacc.orderaccid = orders.orderid


Does Oracle Instant Client 12c support LDAP? I've been using Oracle Instant Client 12c as a lighter-weight alternative to the full Oracle installation. It works fine when I resolve service names with tnsnames.ora, but now I would like to configure it to use LDAP. I dropped our corporate ldap.ora and sqlnet.ora files into the TNS_ADMIN directory, but they aren't being used when I try to connect to a database. So I'm wondering, is LDAP even supported by the Oracle Instant Client?

http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html#A8434

  Can LDAP be used with Instant Client?

  Yes, LDAP can be used with Instant Client, as of 10.1.0.4.


Multiple relationships between the same entities Is there a name for the case when two entities can have multiple relationships between themselves?

Nothing I know of. Calling it "multiple binary relationships" is acceptable, I believe.


Improving Mysql updating query performance We are trying to change some data from our Mysql database (v5.7.17) from being evaluated on-the-fly to a field in the database. The concrete field is the detectable_status and it will be calculated each night for the equipment (which inherits from detectable) in order to evaluate it. That's the query updating the field for a concrete client in our application:

UPDATE tdetectable det
JOIN tdetectable_equipment equipment ON det.id = equipment.id_detectable
JOIN torganization org ON det.id_organization = org.id
SET det.detectable_status = (CASE
    WHEN (equipment.setting_up_date IS NOT NULL
        AND equipment.expiration_periodicity_type IS NOT NULL AND
        fdate_periodicity(equipment.setting_up_date,
        equipment.expiration_periodicity_type, equipment.expiration_periodicity_value,1)
        < NOW()) = 1
        THEN 'EXPIRED'
    WHEN (EXISTS(
        SELECT *
        FROM trevision_cycle incorrect_cycle
        WHERE equipment.id_detectable = incorrect_cycle.id_detectable
        AND incorrect_cycle.status = 'NO_OK'
        AND incorrect_cycle.active <> 0 AND incorrect_cycle.id IS NOT NULL))
        THEN 'INCORRECT'
    WHEN (EXISTS(
        SELECT *
        FROM trevision_cycle need_revision_cycle
        WHERE equipment.id_detectable = need_revision_cycle.id_detectable
        AND need_revision_cycle.next_revision_date IS NOT NULL
        AND need_revision_cycle.next_revision_date < NOW() = 1
        AND equipment.id_detectable = det.id
        AND need_revision_cycle.active <> 0))
        THEN 'NEEDS_REVISION'
    WHEN (equipment.setting_up_date IS NULL
        AND (equipment.expiration_periodicity_type IS NOT NULL OR EXISTS(
        SELECT *
        FROM trevision_cycle last_revision_cycle
        WHERE last_revision_cycle.id_detectable = equipment.id_detectable
        AND last_revision_cycle.last_revision_date IS NULL
        AND last_revision_cycle.active <> 0)))
        THEN 'INDETERMINATE'
    ELSE    'CORRECT'
    END)
WHERE det.active and org.id_client = ?

The status is set for every equipment in the client based in different factors (expiration dates, revision dates...). The problem is the query is quite slow. Here it is my benchmark:

Affected rows: 131, Duration: 0,375 sec.

Affected rows: 4.208, Duration: 44,585 sec.

Affected rows: 2.036, Duration: 13,712 sec.

Affected rows: 13.288, Duration: 00:10:04

Also I want to clarify the content of the fdate_periodicity function, which takes a date and adds a periodicity to it (example: 2017-09-10 + 1 YEAR = 2018-09-10):

BEGIN
IF add_to THEN
    RETURN
        CAST((CASE periodicity_type
        WHEN 'DAY' THEN (input_date + INTERVAL periodicity_value DAY)
        WHEN 'WEEK' THEN (input_date + INTERVAL periodicity_value WEEK)
        WHEN 'MONTH' THEN (input_date + INTERVAL periodicity_value MONTH)
        WHEN 'YEAR' THEN (input_date + INTERVAL periodicity_value YEAR)
        ELSE NULL
        END
        ) AS DATETIME
        );
ELSE
    RETURN  CAST((CASE periodicity_type
        WHEN 'DAY' THEN (input_date - INTERVAL periodicity_value DAY)
        WHEN 'WEEK' THEN (input_date - INTERVAL periodicity_value WEEK)
        WHEN 'MONTH' THEN (input_date - INTERVAL periodicity_value MONTH)
        WHEN 'YEAR' THEN (input_date - INTERVAL periodicity_value YEAR)
        ELSE NULL
        END
        ) AS DATETIME
        );
end if;
END

I understand MySql is doing quite a heavy work here, but are this execution times normal for a core-8 + 14Gb RAM machine? What could I do to improve it? Here it is the execution plan (explain):

Thanks in advanced!

Turn it into 4 UPDATES, one per status (EXPIRED, INCORRECT, ...).  Run the updates in the correct order so that if two cases apply, you end up with the desired status.

Each UPDATE would match one of your cases.  For example, for INCORRECT:

UPDATE tdetectable det
JOIN  tdetectable_equipment e  ON det.id = e.id_detectable
JOIN  torganization org  ON det.id_organization = org.id
SET det.detectable_status = 'INCORRECT'
WHERE ( EXISTS(
        SELECT *
        FROM trevision_cycle
        WHERE id_detectable = e.id_detectable
          AND status = 'NO_OK'
          AND active <> 0
          AND id IS NOT NULL -- If `id` is the PK, this test unnecessary
      ) )
      AND det.active
      AND org.id_client = ?

Indexes:

torganization: INDEX(id_client, id)
det:  INDEX(id, active) -- unless you have PRIMARY KEY(id)
trevision_cycle: INDEX(id_detectable, status, id, active)

innodb_buffer_pool_size should be about 10G for that sized machine.

More specifics:

ALTER TABLE trevision_cycle ADD INDEX(id_detectable, status, id, active);

KEY and INDEX are synonyms.  Most datatypes (INT, ENUM, etc, but not TEXT or BLOB) can be indexed.  Since it has more than 1 column, this index is called "composite" or "compound".  The order of columns in an index definition is important; I provided one useful order.


Mysql replication from existing slave to new slave I'm hoping you experts can help advise me, I've got this topology:

Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB

Backup server is a slave of Live DB, and Test DB is a slave of Backup server

The replication from Live to backup server works as expected, the replication from backup to test is where I'm a little confused, if I create a test DB on backup, I can see it replicated to Test DB, but if I do the same on Live DB, it doesn't filter down to Test DB (but does to Backup).

Another thing I notice is that the binlog sizes on Backup are very small, only 150 Bytes, whereas they are close to 100M on Live, (binlog sizes set the same on live and backup)

Maybe this is by design?
Maybe it's not possible to chain slaves together?

I goal of Test DB was (once working) to replicated from our Hosted Datacenter to our internal office, then use the internal slave to update our Dev / QA / Training DBs here (reducing bandwidth usage).

Here are what the related settings are on the backup server:
(/etc/mysql/my.cnf)

log_bin                 = /srv/mysql-binlogs/mysql-bin.log
expire_logs_days        = 7
max_binlog_size         = 100M
binlog_cache_size       = 16M
binlog-format           = MIXED

Specs:

mysql 5.5
Debian Squeeze

Thanks, Your advice would be much appreciated

I missed one very important option:

log_slave_updates

Now the binlogs are growing!

ref: MySQL Slave Relay Logging but not logging Binary Log

http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_log_slave_updates


What happens if I load last 10 rows then next 10 after other rows are inserted? What if I had a PHP script that had a pagination that loads last 10 rows then page two loaded next 10 in DESC order, but before loading next 10 on page 2, other 10 rows were added to the table?
What would happen? And if the same 10 rows were to be loaded again, and I had a TIMESTAMP column in my table, how would I take that to my vantage to avoid that scenario?
Thanks.

And that is one of 3 problems with using OFFSET for "pagination".

The other two are

What is some rows were deleted?
Performance.

The solution to all of them is to "remember where you left off".  Here is my blog on the issues.

Most people who use OFFSET come to this forum bemoaning poor performance.  Not many have (yet) discovered the "bug" you are pointing out.


Difference between pg_column_size(table.*) and pg_column_size(table.col1) + pg_column_size (table.col2) From PG DOC

  pg_column_size(any) : number of bytes used to store a particular value (possibly compressed)
  pg_column_size shows the space used to store any individual data value

Example:

select pg_column_size(5::smallint);    -- 2 bytes
select pg_column_size(5::int);         -- 4 bytes

With input of pg_column_size, it can be a column or row, so I created a test to check it.
Here's my test:

My table

CREATE TABLE index_test
(
  id integer NOT NULL,  -- 4  bytes
  a integer,            -- 4  bytes
  b integer,            -- 4  bytes
  CONSTRAINT index_test_id PRIMARY KEY (id)
)

1/ First query:   sum(pg_column_size(table.rows))

with abc as
(
 select id,a,b
 from index_test where b > 100
)
select pg_size_pretty(sum(pg_column_size(abc.*))) from abc  -- "348 kB", abc.* = record

And query's explanation:

"Aggregate  (cost=427.55..427.56 rows=1 width=24) (actual time=9.171..9.171 rows=1 loops=1)"
"  CTE abc"
"    ->  Seq Scan on index_test  (cost=0.00..180.00 rows=9902 width=12) (actual time=0.039..2.882 rows=9902 loops=1)"
"          Filter: (b > 100)"
"  ->  CTE Scan on abc  (cost=0.00..198.04 rows=9902 width=24) (actual time=0.047..7.151 rows=9902 loops=1)"
"Total runtime: 9.376 ms"

2/ Second query:  sum(pg_column_size(table.id)) + sum(pg_column_size(table.a)) + sum(pg_column_size(table.b))

with abc as
(
 select id, a, b
 from index_test where b > 100
)
select  pg_size_pretty((sum(pg_column_size(id)))  + (sum(pg_column_size(b))) + (sum(pg_column_size(a))))
from abc  -- "116 kB"

And query's explanation:

"Aggregate  (cost=526.57..526.59 rows=1 width=12) (actual time=10.959..10.959 rows=1 loops=1)"
"  CTE abc"
"    ->  Seq Scan on index_test  (cost=0.00..180.00 rows=9902 width=12) (actual time=0.035..2.780 rows=9902 loops=1)"
"          Filter: (b > 100)"
"  ->  CTE Scan on abc  (cost=0.00..198.04 rows=9902 width=12) (actual time=0.039..5.623 rows=9902 loops=1)"
"Total runtime: 11.173 ms"

3/ Results:

First query:  348 KB

Second query: 116 KB    ( pg_column_size(id) = 39 KB ...)

I thought both query had to return the same result, but size of first query = 3 * size of second query, It made me confused. In first explanation, the "width = 24 bytes / row " (instead of 12), I wondered why it is increase, I think it is the clue to the question . Until now, I can not find clear answers for my question, please help me out  .

The 24 byte/row difference rings a bell: it is the same size as the row header in tables and indexes.  What I did not realize up until your question is that the same header size applies apparently to every row you return in a query:

SELECT pg_column_size((1::integer, 2::smallint));
 pg_column_size
────────────────
             30

but

SELECT pg_column_size(1::integer) + pg_column_size(1::smallint) AS pg_column_size;
 pg_column_size
────────────────
              6

When passing a row to pg_column_size(), it calculates the size of the whole row, including the header.

Now playing a bit further with this, there are some interesting facts you can find.

For example, one can check the difference caused by padding the columns up to the nearest 4 bytes (where k is a positive integer):

SELECT pg_column_size((1::smallint, 2::integer));
 pg_column_size
────────────────
             32

Here we got two more bytes than in the first example, the remaining 2 bytes after the smallint got 'wasted'.  This may be important when choosing the order of columns in huge tables - for example, we can squeeze something into that 'empty' space:

SELECT pg_column_size((1::smallint, FALSE, FALSE, 2::integer));
 pg_column_size
────────────────
             32

The other important thing is that columns that have NULL as a value have zero size here:

SELECT pg_column_size((NULL::smallint, 2::integer));
 pg_column_size
────────────────
             28

In contrast, you'll get NULL as a result when trying to add up the column individually:

SELECT pg_column_size(NULL::smallint) +  pg_column_size(2::integer) AS pg_column_size;
 pg_column_size
────────────────
           NULL


Would I lose data if I change a column from varbinary(8000) to varbinary(max)? Is there any data loss if a table is being altered to change an existing column from varbinary(8000) to varbinary(MAX)?

No, you won't lose data.

SQL Server will do an in place conversion, as a transaction. This means if anything fails, it will rollback.

Note, this is more than a simple metadata change so it may take some time because data has to be moved around


How to get when MySQL instance went down last Is it possible to determine when a MySQL instance went down last? Is there a log file or literally anything at all that logs the server status in MySQL? I have trolled through countless articles online and have not found a single thing that offers any sort of help on this. HELP!

The file mysql.log shows startup and shutdown.

SHOW VARIABLE LIKE 'log_error'; says where the file is.  However, also check SHOW VARIABLE LIKE 'log_output';.  (I don't know if this log is controlled by that.)

SHOW STATUS LIKE 'Uptime'; says how many seconds it has been up.


How can SQL Delete using a sub query The following code was added by one of our developers to delete duplicate records from the table:

DELETE  SubQuery

FROM
(
    SELECT  ID
            ,FK1
            ,FK2
            ,CreatedDateTime
            ,ROW_NUMBER() OVER(PARTITION BY FK1, FK2 ORDER BY CreatedDateTime) AS RowNumber

    FROM    Table
)
AS SubQuery

WHERE   RowNumber > 1

When reviewing the code, I assumed that it wouldn't work, however testing it in our test environment (SQL 2014) shows that it does!

How does SQL know to resolve the sub query and delete the the records from table?

The subquery you have in your code is called a derived table. It's not a base table but a table that "lives" during the time that the query runs. Like views (which are also called viewed tables) - and in recent versions CTEs which is another, 4th way to "define" a table inside a query -  they are similar to a table in many ways. You can select from them, you can use them in from or to join them to other tables (base or not!).

In some DBMS, (not all DBMS have implemented this the same way) these tables/views are updatable. And "updatable" means that we can also update, insert into or delete from them.

There are restrictions though and this is expected. Imagine if the subquery was a join of 2 (or 17 tables). What would delete mean then? (from which tables should rows be deleted?) Updatable views is a very complicated matter. There's a recent (2012) book, entirely on this subject, written by Chris Date, well known expert in relational theory: View Updating and Relational Theory.

When the derived table (or view) is a very simple query, like it has only one base table (possibly restricted by a WHERE) and no GROUP BY, then every row of the derived table corresponds to one row in the underlying base table, so it is easy* to update, insert or delete from this.

When the code inside the subquery is more complex, it depends on whether the rows of the derived table/view can be traced/resolved to rows from one of the underlying base tables.

For SQL Server, you can read more in the Updatable Views paragraph in MSDN: CREATE VIEW.

  Updatable Views

  You can modify the data of an underlying base table through a view, as
  long as the following conditions are true:

  Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in
  any other way, such as through the following:
  An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
  A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using
  the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT
  amount to a computation and are also not updatable.
  The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
  TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

  The previous restrictions apply to any subqueries in the FROM clause
  of the view, just as they apply to the view itself. Generally, the
  Database Engine must be able to unambiguously trace modifications from
  the view definition to one base table.

 Actually delete is easier, less complex than update. SQL Server only needs the primary keys or some other way to identify which rows of the base table are to be deleted. For update, there is an additional (rather obvious) restriction that we can't update a computed column. You can try to modify your query to do an update. Updating the CreatedDateTime will probably work just fine but trying to update the computed RowNumber column will raise an error. And insert is even more complex, as we'd have to provide values for all the columns of the base table that don't have a DEFAULT constraint.


Page count differences between sys.dm_db_index_physical_stats and sys.allocation_units In doing research for index maintenance, I found some significant differences between the page counts reported by sys.dm_db_index_physical_stats and sys.allocation_units in some of the clustered indexes on one of our servers.

For one table, allocation_units reports 6706 data_pages and 15203 used_pages, while dm_db_index_physical_stats reports 4079 leaf pages and 15 non-leaf pages.

I don't know when the last time any maintenance was done on the indexes. It is possible that they have not been reorganized or rebuilt since they were initially loaded to the server. Rebuilding the index does sync the stats.

I'd be interested in knowing what caused it to get out of sync and if the extra space reported by allocation_units is actually reserved on the disk.

The query I used the following query for allocation_units. It returned 1 row of IN_ROW_DATA.

SELECT *
FROM
    sys.indexes idx
    INNER JOIN sys.partitions part
        ON idx.object_id = part.object_id
            AND idx.index_id = part.index_id
    INNER JOIN sys.allocation_units alloc
        ON part.partition_id = alloc.container_id
WHERE
    idx.type = 1
    AND idx.object_id = OBJECT_ID('dbo.Table')

Page counts shown by sys.dm_db_index_physical_stats and sys.allocation_units can vary significantly when a variety of DDL modifications are made to the object concerned.
From the Microsoft Docs page on sys.sp_spaceused

There are some situations, for example, after an index is dropped, when the space information for the table may not be current. [...] Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.
When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

sys.dm_db_index_physical_stats has the following definition (in SQL Server 2016):
create function sys.dm_db_index_physical_stats
(
    @DatabaseId         SMALLINT        = 0,
    @ObjectId           INT             = 0,
    @IndexId            INT             = -1,
    @PartitionNumber    INT             = 0,
    @Mode               nvarchar(20)    = NULL
)
returns table
as
    return select *
        from OpenRowset
        (   TABLE
            INDEXANALYSIS,
            @DatabaseId,
            @ObjectId,
            @IndexId,
            @PartitionNumber,
            @Mode
        )

As you can see, it uses the internal table INDEXANALYSIS, whereas  sys.allocation_units uses a different source, ALUCOUNT, as shown in its definition:
CREATE VIEW sys.allocation_units AS
    SELECT au.auid AS allocation_unit_id,
        au.type,
        ip.name AS type_desc,
        au.ownerid AS container_id,
        convert(int, au.fgid) AS data_space_id,
        isnull(ct.reserved_pages, au.pcreserved) AS total_pages,
        isnull(ct.used_pages, au.pcused) AS used_pages,
        isnull(ct.data_pages, au.pcdata) AS data_pages
    FROM sys.sysallocunits au OUTER APPLY OpenRowset(TABLE ALUCOUNT, au.ownerid, au.type, 0, 0) ct
    LEFT JOIN sys.syspalvalues ip ON ip.class = 'AUTY' AND ip.value = au.type

These sources vary in their level of consistency as you can see in the output of your queries.  If you require absolute certainty about the count of pages used by each, you should do the following:

Ensure no open, uncommitted transactions exist that have modified the table/index in question.
Run EXEC sys.sp_spaceused with the @updateusage = 'updateusage' option, or DBCC UPDATEUSAGE.
Run CHECKPOINT to ensure all modifications have been written to disk.
Check the output of sys.dm_db_index_physical_stats and sys.allocation_units; the counts should now be the same.


When designing the topology of a data model, does the database implementation matter? This application seems to be keeping data design separate from data storage. But I am curious if this even makes sense?
If you click the code button you should be able to see clearly what question I am trying to ask. It outputs code for different databases, but shares the same model. What about the difference between SQL and NOSQL databases? https://www.schesign.com/o/examples/example_online_store/master/class/billinginformation

Without knowing anything about the project, yes it does make sense. It is true that different relational database management systems (RDBMS) may support different features. If a certain feature is particular to only a subset of all RDBMS's, then it's theoretically possible to either a) not support that feature when creating the model, b) abstract out the part of the feature that is common across all RDBMS's, or c) support the feature in the model but give an error when trying to convert the model to a sql script for an RDBMS that doesn't support the feature.

Relational databases ("SQL databases") are fundamentally very different from NoSQL databases. In fact, the entities NoSQL databases are largely unmodeled by design. I would be surprised if they add support for NoSQL databases, but then again I don't know anything about the project.


SQL Server End to End Encryption Options I'm familiar with database encryption options for SQL Server such as TDE and ensuring backups / backup locations are encrypted and secured properly.

I'm doing some work for a new client who has asked me to detail options for end to end encryption, in other words, to ensure data sent from database server to application server remains encrypted en route and cannot be intercepted (or rather, interpreted) by any sort of packet monitoring.

I know about using SSL certificates to encrypt connections to the database, though it is something I have seldom configured. I really don't know if that is an adequate option for end to end encryption as surely this cannot be configured using SQL Server alone?

In my mind there has to be something at the application end, or on the network route that enables end to end encryption like this?

My knowledge of network security is basically limited to firewall rules and general routing, so I'm looking for options available to me to either research further or put into practice.

I wary that this question will be closed because it is opinion based, so I'm really asking for solid options within SQL Server itself (or within the remit of a DBA) to configure end to end encryption of data.

You are correct; SSL is the official means to support encryption for data in motion.

  Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt
  data that is transmitted across a network between an instance of SQL
  Server and a client application. The SSL encryption is performed
  within the protocol layer (technet)

Always Encrypted is a bit deeper. It encrypts the data at the client application before it is transmitted, and never stores it in any unencrypted way. So the data that's encrypted via Always Encrypted is "safe" while in transit and at rest (stored as encrypted data), but the packets themselves are NOT encrypted at the network level. (MS Docs)

So if you want to prevent your DBAs and/or report writers from viewing cleartext data (such as credit card numbers or social security numbers), then Always Encrypted is a possible tool to get that level of security.

But if you don't want a packet monitoring tool to see your database username and password, or to see cleartext data as it moves from DB to application, then you must use SSL.


migrate MySQL database with MySQL Workbench CE failed on table with blob data I am using MySQL Workbench CE to migrate one schema from a server to another, all table is migrated successfully, except one table with blob data.

The error message is :

Found record bigger than max_allowed_packet.

I have set the max_allowed_packet to 1M on target server, and restarted MySQL, but still got the same error.

And I checked data in that table:

select max(OCTET_LENGTH(file_Data)) from attachment;
------
680960
select sum(OCTET_LENGTH(file_Data)) from attachment;
------
5165578

How can this be fixed?

You can see max_allowed_packet to 1G in your session

SET max_allowed_packet = 1073741824;

You can do so because MySQL will not immediately allocate it. A packet can grow. Please note how it mentions this in the MySQL Documentation:

  The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

  You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

You should go ahead and add max_allowed_packet to my.cnf

[mysqld]
max_allowed_packet=1G

That way it is globally available for all incoming connections when you restart mysql. You don't have to restart mysql right now. You can just run:

SET GLOBAL max_allowed_packet = 1073741824;

Give it a Try !!!


Active/ Active SQL Server Clustering on AWS I need to design and implement a highly available SQL Server 2012 clustering on Amazon AWS. In the white paper presented by Amazon, they provided an example of Active/ Passive SQL Cluster. Do you know whether Active/ Active is also possible?

Thanks

Active/Active denotes two live datastores that can be simultaneously accessed by the application(s) in question.  SQL Server does not have a solution for this, including AlwaysOn Availability Groups, which at best will only provide you with additional readable replicas.  Outside of Oracle RAC, there isn't a solution for this.


Accessing the insert statement which triggers a trigger inside the trigger's body I have some table and I have enabled auditing on this table. Currently, I am collecting the data about who has inserted records and at what time. Now I also want to log what was the exact query he/she used to insert the record. I created a trigger which at present only logs the user and date to audit data collection table. This is code to the trigger:

create or replace trigger auditer
after insert on user01.sometab
for each row
insert into sys.auditlogs values(user,sysdate)

How can I use the same trigger to access the insert statement and insert that statement into the audit table? Is it even possible?

You can meet all your requirements with Standard Database Auditing or with Fine-Grained Auditing.

For the standard auditing to also capture the SQL statements, you would set AUDIT_TRAIL initialization parameter to DB,EXTENDED or XML,EXTENDED (See "Settings for the AUDIT_TRAIL Initialization Parameter" in Database Security Guide). The audit records would include timestamps and SQL text, and could be queried from SYS.AUD$ table if AUDIT_TRAIL value is set to DB,EXTENDED or DB, or read from OS files if AUDIT_TRAIL is set to XML,EXTENDED or XML. In the latter case you can determine where the audit files are located based on the value of the initialization parameter AUDIT_FILE_DEST:

SQL> show parameter audit

NAME                 TYPE        VALUE
-------------------- ----------- ------------------------------
audit_file_dest      string      /u01/app/oracle/admin/orcl/adump

As far as FGA is concerned, I have already described it in my other post here. You would just need to set the other value for the audit_trail parameter when creating the FGA with DBMS_FGA.ADD_POLICY procedure, e. g. DBMS_FGA.DB_EXTENDED.


postgresql - Importing CSV Data with Custom Type I'm mostly a newcomer to databases and am building a postgres database of student achievement in a series of courses. My database uses a type called "class" that consists of the course, the academic term (year and quarter), and the section number (1,2,3,...), e.g. ('Bio 101', (17, 'F'), 1). I'm trying to copy a CSV containing such data into a table and am getting an error message saying ERROR:  extra data after last expected column. I've tried the pgAdmin III import wizard, COPY and \copy, all with the same results. However, manually copying a line from the file and IMPORTing it (using the appropriate parentheses) works fine. What's going on and what can I do?

Here are the table and some data:

CREATE TABLE public."GradedItems"
(
  class class NOT NULL,
  category text NOT NULL,
  "number" smallint NOT NULL DEFAULT 1,
  topic text[],
  points integer,
  weight double precision, -- Weight of item within category
  CONSTRAINT primary_key PRIMARY KEY (class, category, number)
)

'class','category','number','points'
('LS 30A', (18, 'W'), 2),'Homework',1,60
('LS 30A', (18, 'W'), 2),'Homework',2,36

What is going on is a mismatch on the number of columns names and the number of columns in your data. Without information on how the csv is generated and how you are importing it is hard to say, but presuming the separator on your csv is the comma character, this is what the database insert operation is trying to do:
it reads this
'class','category','number','points'
and learns how to map four columns.
Then, when it gets to the second line, it is splitting the data by the commas, so it interprets that line as

('LS 30A'||(18||'W')|| 2)||'Homework'||1||60

where the || signifies a break between values. It interprets the command as:

set
  class = ('LS 30A',
  category = (18,
  number = 'W',
  points = 2)

...and then it does not know what to do with the rest of the data, as it does not fit.

My suggestion would be to use a different separator. It will depend on how the csv is being generated how you do that.


Postgresql comment on specific role I am trying to store a specific user's description in the Postgres by doing this:

comment on role blog is 'blog user long name';

However, when I look for it in the pg_description table it is not there:

select * from pg_catalog.pg_description where description like '%blog%';

Returns no results. How can I accomplish this?

The role comments are stored in the "pg_catalog.pg_shdescription" table.


Copy package in Production DB to Test DB with dependencies? This is my first question here, so please let me know if this isn't the right place to ask.

I'm a new programmer, fresh out of school. Primarily I develop in Python/C#, with a dash of SQL when needed.

At my job, I've been assigned a task in PL/SQL, which involves modifying some existing procedures. All well and good, some new syntax to learn, but no serious roadblocks.

My problem is, I have no idea how to migrate the existing package/views/tables to our test database, which is basically an un-maintained sandbox environment.

I only need to copy two stored procedures in a package, two views, and 3 tables, so that I can modify the procedures and verify that the output is as-expected. However, I feel like there must be a better way to move this data than "crtl+c, crtl+v" in the editor window, or downloading and uploading table export files via my local machine.

I'm working in Oracle SQL Developer, and really only need to move enough data to execute these procedures and test my work. My job is really the development here, and the more time I spend trying to set up this test environment, the less time I spend coding.

Is there some sort of way to easily copy a package and all its data dependencies from one database to another? Bonus points if it's in Oracle SQL Developer, and easy for a non-DB admin to do. Thanks!

In SQL Developer you can right-click on an object (table, package, procedure, etc) and there will be an option for 'Quick DDL'. Open that and you have options on what to do with the ddl, including saving to a file.  That's better than select/ctl-C/ctl-v, but there is still a better way since you know what you want.

You can use expdb (DataPump export) to export the ddl for the selected objects.  That will create a binary .dmp file that is, essentially, all of the DDL you want.  You will need to read up on the options for limiting what gets exported or you very well could get the entire database, including data.  You then use impdp (DataPump import) to process that dmp file and create/load your objects.  Even when you limit the objects that get exported, you can specify whether or not to include the data. I leave it as an exercise for the student to study the relevant documentation at https://docs.oracle.com/database/121/SUTIL/GUID-501A9908-BCC5-434C-8853-9A6096766B5A.htm#SUTIL2877

Another method you might consider if you JUST want the DDL, without the DML to load tables, would be to write a script using the DBMS_METADATA.GET_DDL procedure, documented at https://docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS66872

Now, you may say "Can't you just give me the script, getting this DDL is a distraction from my real job."  But you are just starting your career and time spent learning to read the docs, learning that these tools even exist, and learning how to use them is time well invested.  When I was just starting my career (as a COBOL programmer in 1981) I would take reference manuals home with me for bed-time reading.  It served me all, as within 3 years I was the lead tech on a project, directing the work of several programmers with more experience but less inate curiosity about how things really worked and about learning new things about the tools they thought they already knew.


Stored Procedures execution permissions So what I am trying to do is to limit the access to a stored procedure so that only another stored procedure may call it. Here is an example:

create procedure Database_Deployment_Proc      -- This procedure can be executed,
                                               -- but "view definition" is restricted
    @databaseName nvarchar(200)
as
begin
    exec Internal_DeployTables      @databaseName   -- Internal_ should be inaccessible
                                                    -- if executed outside Database_Deployment_Proc
    exec Internal_DeployConstraints @databaseName
    exec Internal_DeployIndexes     @databaseName
    exec Internal_DeployProcedures  @databaseName
    exec Internal_DeployFunctions   @databaseName
end

My goal is to deny any access to any database component, with exceptions of when it is called from specific stored procedure. Is it possible?

I understand that this approach is very bad for the query plan, but it will ease a lot of my work for deploying same database to different machines, with different users in different domains (unrelated).

Is it better to keep it in stored procedure or embed it in C# CLR assembly?

Thanks in advance!

Permissions are enough to handle this requirement: just GRANT permissions on the wrapper stored procedure and you should be fine. Don't GRANT permissions on the Internal_* stored procedures and users won't be able to execute them.

Example:

USE tempdb;
GO

CREATE USER ZeroCool WITHOUT LOGIN;
GO

CREATE PROC Select1
AS
SELECT 1
GO

CREATE PROC Select2
AS
SELECT 2
GO

CREATE PROC SelectAll
AS
EXEC Select1
EXEC Select2
GO

GRANT EXECUTE ON object::SelectAll TO ZeroCool
GO

EXECUTE AS USER = 'ZeroCool'

-- Works
EXEC SelectAll
-- Fails
EXEC Select1

REVERT
GO


Replicating a database that uses Fail Over We are trying to setup replication for data coming in from an outside vendor. The issue that we are having is they say that because our vendor is using SQL Fail Over Clustering so they claim that the DB cant be replicated? Is that true and is there any work arounds.

That is not true. I've done transactional replication with FCIs for years. Interestingly I cannot find any supporting documentation on the Microsoft site, however it is supported, and it does work.


Copy Mysql table to another table I have two tables spider.expedient and spider 367.expedient, I want to copy the data from the spider.expedient table to the spider_367.expedient table daily, respecting the data from the previous day and ignoring possible duplicates.

I have run this query but it gives me error by duplicates. Is the correct way to achieve this?

INSERT INTO spider_367.expedient SELECT * FROM spider.expedient

There are two approaches that generally work well.

Option 1: add a clause to make an ineffectual change when duplicates are found:

INSERT INTO spider_367.expedient
SELECT * FROM spider.expedient
ON DUPLICATE KEY UPDATE x=x

Replace x with one of your field names.

Option 2: do a left join and only insert the records that don't match

INSERT INTO spider_367.expedient
SELECT x1.* FROM spider.expedient x1
LEFT JOIN spider_367.expedient x2 ON x1.id = x2.id
WHERE x2.id IS NULL

Replace x1.id and x2.id with your key fields.


Big Data Storage and Analysis I have a client who wants to analyse calling records of customers to make a decision as to whether they qualify for loans or not.
They are in partnership with a mobile company that supplies the call data daily. The customer base of the mobile company is over 13million. The call data, voice and data credit is used to determine eligibility in conjunction with set rules.
Daily call records is about 70 million records and retention period of 90 days.
So am looking for a tool and database to help design the analytics engine to determine eligibility of potential loan requestors.

A couple of points.

When looking at benchmarks, I would double-check to see that it is running the type of load that you are focused on. You mention supplying "the call data daily". Is this via a large batch? How much data per call? I imagine it can't be very much - with say 500 bytes of data per call, that makes 50GB per day = 18TB per year (~5 TB/90 days) - but with aggregation, you should be able to reduce this storage requirement.

Will you need old(er) data for this analysis? How far back do you want to go?

You should also factor in a reasonable path for

a) increasing load,

and

b) upgrading your h/ware

My guess is that if a telco is looking for an upgrade, money will be no object (:-)).

If I were you, I would be looking at a system which can handle aggregate queries well - I would be looking at performing these aggregates on a daily, weekly, monthly and accounting-period  basis.

Since you are (apparently) adding data on a daily batched basis, you should be able to perform these aggregations on the fly with your data load process.

You appear to wish to use Open Source databases, and therefore I would recommend PostgreSQL - it is (IMHO) a much more capable database technically than MySQL.

It has CTEs, Window (aka Analytic) functions, CHECK constraints (astoundingly, MySQL doesn't have these) and Materialized Views (all natively). MySQL is working on some of these, but they are in beta - check out dev.mysql.com.

I would be looking at storing the raw data in as compressed a format as possible and working day-to-day on aggregates at least on a daily basis per phone number. This will allow you to respond quickly to most queries while still keeping all your data accessible if required.

Hardware, I'd be using RAID 1+0 - I'm guessing that like most database apps, your system will be disk bound - make sure you get the best that you can afford on that score! I believe that a fairly average (your definition may vary!) server in terms of CPU and RAM should be able for this, but again, YMMV! I'd be looking to start with at least 16GB of RAM (upgradable to at least 64) with reasonably modern CPUs.

Finally, remember (and this is critical) try to benchmark on your load with your hardware and your software. Benchmarks are tricky - basically there are lies, damned lies and statistics. After that, there are (other people's) database benchmarks! :-)

Best of luck with your project and welcome to the forum. Your question is a (bit) over-general here. You might like to take a look at the help centre - take the the tour.


Upsert Row If Columns Don't Match I have a table with unique identifier sid, somecolumn and boolean changed that is needed by other program to detect change in row, I'm using insert into ... on conflict (sid) DO UPDATE ... to insert data that's not already in there, but I also want to update somecolumn and set changed to true, only if somecolumn doesn't match excluded one.

to make it bit clearer

if sid doesn't exist, insert data
if sid exists and somecolumn matches excluded `somecolumn, do nothing
if sid exists and somecolumn doesn't match, update somecolumn with new value
and set changed to true

is there clean way to do this? I'd prefer to do this without making multiple queries,  I'm inserting hundreds of values in each query.

You can add a WHERE clause to the UPDATE part of an INSERT ... ON CONFLICT

insert into the_table (sid, somecolumn)
values (...)
on conflict (sid) do update
  set somecolumn = excluded.somecolumn,
      changed = true
where the_table.somecolumn <> excluded.somecolumn;

If you need to deal with NULL values in somecolumn use

where the_table.somecolumn is distinct from excluded.somecolumn