Computed/Generated columns in MySQL 5 How do I create a table with a formula column in MySQL version 5?

For example: I have a table named Product, which has 5 columns:

id int,
amount int,
sold_out int,
faulty int,
remain_amount is (amount-(sold_out+faulty))

I will not use a trigger or a view.

I am looking for a solution similar to what, in SQL Server, is called a computed column.

Wait for 5.7 where generated fields are supported

mysql> CREATE TABLE sales (
    -> name VARCHAR(20),
    -> price_eur DOUBLE,
    -> amount INT,
    -> total_eur DOUBLE AS (price_eur * amount),
    -> total_usd DOUBLE AS (total_eur * xrate),
    -> xrate DOUBLE);
Query OK, 0 rows affected (0,16 sec)

http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/

Or use MariaDB, where virtual/computed columns are working now. These are a beta feature in MySQL 5.7.


SSIS Package not retaining parameters in SQL Server agent job I have an SSIS package stored in the Integration Services catalogs on a SQL server instance.

This package is used as a step in a SQL Server agent job.

The package has 4 required parameters.

Using SSMS on my local machine if I edit the package I can add all of these parameters and the job runs fine.

However editing the step from SSMS on the server gives the following error:

You can enter this value but every time you return to edit the step the error re-occurs and the parameter values are blank.

Because the person deploying this package will have to use SSMS on the server I could really do with working out what is causing this.

Server
SQL Server: SQL Server 2012 (11.0.3128)
SSMS: 11.0.3128.0
OS: Windows Server 2012 (6.2.9200)

My PC
SSMS: 11.0.3128.0
OS: Windows 8.1 (6.3.9600)

I've discovered what the issue was.

2 of the parameters were connection strings and if a connection string contained an Application Name then the value of the preceding parameter was not being stored.

This worked fine from my machine so I'm assuming a difference in the version of SSMS client is to blame.

For the time being I have simply removed the application name from the connection strings.


Sql Server, Is "Reorganize Files Before Releasing Unused Space" better or worse? I could not find a satisfactory answer. I fully understand that "Shrink" will cause index defragmentation, and that can worsen performance.

But, is not this option to reduce defragmentation?

So, to activate this option, is it better or worse? Please do not increase my confusion. I ask you to give me a clear and simple answer: it is better, or, it is worse.

Then if you want, give me your explanation that I will gladly read and appreciate.

That is the equivalent of shrinking. To prove it, check the box and click the Script button at the top to see the shrink commands that it produces.

Generally speaking, that’s a great learning tool too - whenever you want to learn what something in the UI does, try that script button.


Restore backup in order to write datafile sequentially in disk I have just come to a new company and we have one database which was created with standard initial size and autogrowth parameters for data and log files (1 MB autogrowth for data / 10% for log).

The database is 70 GB right now and I guess the datafile is spread all over the disk in 1 MB chunks, so sequential reads get penalized. There is just one raid for OS and other RAID 5 for database files (data and logs together, unfortunately).

Will a backup and restore help to store the datafile in a better way, physically speaking? Will there be improvement for long sequential reads? Otherwise, what can I do?

Thanks.

EDIT: Actually, I have just discovered another database in the same server, with the same problem but a size of 500 GB.

A restore will not correct the page fragmentation.  The best option would be to take an outage and defrag the entire database using DBCC INDEXDEFRAG or DBCC DBREINDEX.  For the OS file fragmentation if you simply detach the database, move the files to a different physical drive and then move it back you should be good.


One Availability group, two primary nodes I have two nodes A & B, I'm trying to set Node A to be primary for DB1 with Node B as its secondary and then set up Node B as primary for DB2 and Node A as its secondary. (I'm using SQL 14.0.1000.169)

Do I need two Availability groups, two listeners, two failover clusters?
Currently I have one of each.

I was able to successfully create the Availability group for the first scenario but now I'm lost.

As Aaron Bertrand mentioned:

You need one Windows Server Failover Cluster (WSFC) with both server member of the WSFC.

You need AG1 (Availability Group) and Listener1. DB1 will go in this Availability Group.

You need AG2 (Availability Group) and Listener2. DB2 will go in this Availability Group.

Now you have the option of having both database primary in one server or each database primary on separate server.

You should also upgrade from RTM. There will be no service packs for SQL 2017, and so CUs aren't really optional.  You don't have to install every one, but you need to have a regular schedule of applying them, and for a new solution you should always start with the latest CU. See Announcing the Modern Servicing Model for SQL Server.


Partition a table at creation I'm developing a SQL Server table that will store audit trail data.  I know that this table will get large as the system is used over time.  Is it worth partitioning the table by year when the system is first configured?  Or worth waiting a year or so in order to keep the performance as high as possible?

This is obviously opinion based, but I'd go ahead and partition the table at creation time.  I'd recommend RANGE RIGHT, as you apparently want to partition on a DATE and make sure you pre-define enough partitions before the minimum date you currently have and after the maximum date you currently have.  This will mitigate massive data movement when adding additional partitions in the future.

Check out Dan Guzman's Table Partitioning Best Practices


Do I need MySQL Group Replication or a Single Server is enough for Click Counting, etc? I'm planning to create a system which tracks visitors clicks into the database. I'm expecting around 1M inserts/day into the Database.

On the backend, I'll have an analytics system which will analyze all the data that's been collected over the days/weeks/months/years.

My question is: is it a practical approach to have 2 different MySQL Servers + 1 Web server? MySQL Server A would insert the clicks into it's DB and it would be connected to MySQL Server B by group replication, so whenever I create reports, etc on MySQL Server B, it doesn't load Server A heavily.

These 2 Database servers would then be connected to the Web Server which would handle all the click requests and displaying the backend reports also.

Is it a practical solution, or is it better to have one bigger server to handle all the MySQL data? Or have multiple MySQL servers that are load balancing each other? Anything else perhaps?

1M INSERTs/day = 12/second.  This is not very heavy -- for either a web server or MySQL.

While you presented an argument for having "reports" come produced on a Slave, there are ways to mitigate the load.

But creating and maintaining Summary Table(s), reports become light weight enough to not worry about.  The summary table(s) would be keyed by day plus some other dimension(s) and have COUNT(*), SUM(...), etc.  Daily subtotals are (usually) sufficient for weekly/monthly/yearly rollups.

For counting clicks -- assuming you just want to count them -- I recommend having a separate table with nothing but the id of what you are counting and an INT UNSIGNED (4 billion max).  By segregating this from the rest of stuff associated with id, there is much less interference.

Multiple web servers may not be needed, but scaling by adding another web server is relatively easy and painless.  Do, however, keep the number of threads in the web server down to, say, 30.

Each web servers can talk to at least one MySQL server.  I don't (yet) see the need for more than one MySQL server.  By not having multiple MySQLs, that complexity is avoided.

There are other reasons for using Replication (Group Repl or other) -- backups, HA, etc.; I am merely saying that your functional separation is not sufficient justification.

Another note... If click-counting is done with an HTTP request for which the reply is ignored, then there is no real reason to worry about whether they are queued up and delayed.  There will be bursts of more than 12/sec.  My Rule of Thumb is that a sustained rate of more than 100/sec. justifies extra consideration.


mysqldump generates incorrect create view queries I use mysqlpump to backup database:

mysqlpump --single-transaction --add-drop-database --skip-definer \
 --databases mydatabase --result-file /opt/myservice/backup/export.sql

My DB contains several views, and some of them are exported with totally incorrect SELECTs in the CREATE VIEW queries like this one:

CREATE VIEW `careerpulse-staging`.`user_view` AS SELECT
 1 AS `id`,
 1 AS `name`,
 1 AS `department`,
 1 AS `manager_id`
;

Why is it happening and how can I fix that?

When dumping / pumping MySQL databases, the output script generates this as a temporary structure first, and later below at the end of the script, it drops this and create the actual view itself, something like this:

...DROP VIEW IF EXISTS myview ...

If you end up with the final view still selecting 1s, it would be probably because database name mismatch, when you use --databases option in the mysqldump , the script will output

USE database_name

in multiple places (not only at the top of the script), and specifically there is one before the DROP and Recreating the actual view.

This happened to me when exporting and importing databases with different name.


Can SQL Server Management Studio Express be installed standalone? I was wondering if the SQL Server Management Studio Express (mainly for Microsoft SQL Server) be installed on a standalone machine (without any of the SQL Services and stuff) to connect to a remote database?

I run a SQL Server 2012; I have been told by one of my managers that they wish me to create a web UI for specified users to run queries / update the database; this is within my ability of PHP; but I was thinking for ease, I could make a proposition to scrap the need for the Web UI because this will cost a lot of time for our server administrators, but install SMSE on our network machines to connect to/manage our databases?

Is this possible? or would I have to go through the painful task of creating a WebUI which displays schemas, tables etc.

Yes, you can install Management Studio (or Management Studio Express) on a workstation that doesn't have SQL Server services. Just pick Management Tools - Complete and Management Tools - Basic during setup. You should be able to do this with any edition of SQL Server you have except for the most basic Express editions - just be aware that technically you need to possess at least one license when using it to manage production instances (though that may have loosened up over the years).

If you don't have an edition of SQL Server that comes with client tools, or you don't have a license to use the full version of Management Studio, you can always download Management Studio Express. @Bummi recommended the RTM release of SQL Server 2012 Express, but the Service Pack 2 version is a better option - since it provides a full version of Management Studio that no longer has feature limitations or licensing requirements:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

I have more thorough download/install instructions in this answer:

https://dba.stackexchange.com/a/73919/1186


SQL - Convert text to a list of numeric values Looking for SQL code to transform the text string '12,13,14' to this list of numbers

+----+
| 12 |
+----+
| 13 |
+----+
| 14 |
+----+

Example:

table0.params (varchar) field contains this text: 12,13,14
table1 has column ID (numeric).

I would like to form a query along the lines of:

SELECT * FROM table1 WHERE ID in table0.params

I am using Microsoft SQL Server Standard Edition (64-bit) version 10.50.1600.1

While this question, for the most part, has been asked and answered what at least feels like hundreds of times on here and StackOverflow, because it concerns int values only I will say that:

Given that you are dealing with only INT values (as the values contained in the delimited string), the fastest way I have come across is to use a splitter that is a) SQLCLR, and b) specialized to only return integers. Since we know what the output type should, in the end be, there is no need to waste the extra cycles on passing back a string (slower than passing back an INT) only to convert that string into an INT. So, for this use case, I created a function that does just this: splits a string and passes back a table of INT values. My testing (so far) shows it to be quite a bit faster than the standard splitter that passes back a table of strings (for the same input values, of course). You can find a pre-done SQLCLR function of this in the SQL# library (that I created). The function is SplitInts. There is also another version of it named SplitInts4k that should be used when the input string is guaranteed to be no more than 4000 characters. The difference is that the non-4k version uses NVARCHAR(MAX) as the input parameter datatype, and that is noticeably slower than NVARCHAR(4000).

You could use it as follows, though it is unclear, if table0 really is a Table and not an input parameter, how it relates to table1 if table0 has more than 1 row:

SELECT t1.*
FROM table1 t1
WHERE [ID] IN (
         SELECT [SplitVal]
         FROM SQL#.String_SplitInts4k(table0.params, N',', 1, 0)
            );

Or perhaps:

SELECT t1.*
FROM table1 t1
INNER JOIN SQL#.String_SplitInts4k(@table0.params, N',', 1, 0) ints
        ON ints.[SplitVal] = t1.[ID];


Native dynamic SQL - execute immediate with column and table names I have to make dynamic query, which binds column names and table name dynamically using native dynamic sql in oracle pl/sql.

Here is my first try:

declare
  komenda varchar2(255);
  v1 varchar2(10):='id_osrodek';
  v2 varchar2(10):='nazwa_o';
  v3 varchar2(10):='osrodki';
begin
  komenda := 'select distinct e.id_student, e.przedmiot, e.:1, o.:2 from egzaminy e
    inner join :3 o on o.:4 = e.:5
    inner join przedmioty p on e.id_przedmiot = p.id_przedmiot';
  execute immediate komenda using v1, v2, v3, v1, v1;
end;

And second:

declare
  komenda varchar2(255);
  v1 varchar2(10):='id_osrodek';
  v2 varchar2(10):='nazwa_o';
  v3 varchar2(10):='osrodki';
begin
  komenda := 'select distinct e.id_student, p.nazwa_p, e.' || v1 || ', o.' || v2 || ' from egzaminy e
    inner join '|| v3 ||' o on o.' || v1 || ' = e.' || v1 || '
    inner join przedmioty p on e.id_przedmiot = p.id_przedmiot';
  execute immediate komenda;
end;

And I wonder why first doesn't work?

Because a bind variable is a value or value address, not a table or column name.

bind variable

  A placeholder in a SQL statement that must be replaced with a valid
  value or value address for the statement to execute successfully. By
  using bind variables, you can write a SQL statement that accepts
  inputs or parameters at run time.

Binding happens after parsing, but a statement can not be parsed without knowing the required tables and columns.


MySQL binlog does not show SQL statements While checking the binlogs, it struck me that I cannot see any SQL statement. Here's an example:

$> sudo mysqlbinlog
    --database=securities
    --start-datetime='2015-11-26 13:00:00'
    --stop-datetime='2015-11-26 14:00:00'
    --base64-output=decode-rows
    --verbose
    mysql-bin.000011

and the output

BEGIN
/*!*/;
# at 1014869455
# at 1014869609
#151126 13:00:16 server id 1  end_log_pos 1014869680    Query   thread_id=1100645   exec_time=0 error_code=0
SET TIMESTAMP=1448539216/*!*/;
COMMIT
/*!*/;
# at 1014869680
#151126 13:00:18 server id 1  end_log_pos 1014869750    Query   thread_id=1100211   exec_time=0 error_code=0
SET TIMESTAMP=1448539218/*!*/;
BEGIN
/*!*/;
# at 1014869750
# at 1014869904
#151126 13:00:18 server id 1  end_log_pos 1014869975    Query   thread_id=1100211   exec_time=0 error_code=0
SET TIMESTAMP=1448539218/*!*/;
COMMIT

However, database securities is being replicated to the slaves without any trouble. Also, what do the # at 1014869904 mean? I would have expected to see statements there.

Remove this clause:

--database=securities


Is there a way to specify an table/index is sorted by more than one column? Suppose I have a table:

CREATE TABLE [dbo].[TestTable]
(
    [Id] INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1),
    [Timestamp] DATETIME2(7) NOT NULL DEFAULT (SYSUTCDATETIME()),
    [Data] INT NOT NULL
);

And suppose I need to look up rows by ID (e.g. for joins) and by Timestamp (e.g. for date-range search).

The usual way to do this would be to add an index on Timestamp, but this seems like a massive waste of resources (almost a complete copy of the table) when Timestamp should be in order in the table in the first place. So two questions:

Is there a way to enforce Timestamp being strictly ascending along with Id? (i.e. to make sure pathological cases like changing the server's system clock, leap seconds, Id's Identity-ness being overruled).
Is there a way for SQL Server to make use of the above enforcement and treat the table as indexed on both Id and Timestamp?

I suspect the answer to (1) may be yes, but that (2) is probably a no, rendering (1) a bit useless, but I'm happy to be proven wrong.

Any version of SQL Server acceptable

You can create a clustered index on both id and timestamp columns and even define different sorting order for each column if you want to:
create unique clustered index idx on TestTable(id asc, [timestamp] asc)
But it won't make much sense in this case for these reasons:

The engine does not know that timestamp increases along with id, so if you run a query with condition on timestamp, it'll still perform a full scan.
All additional columns that are included in clustered index will also be included in all non-clustered indexes.
An older timestamp still could be inserted into the table with newest id.

So you are gaining nothing and only wasting space when you create a new index and slowing down inserts as timestamp order still will have to be maintained.

Creating an index only on timestamp column seems like a waste of resources (2 out of three columns of entire table will be included in an index), but actually it's very common for size of all indexes be greater than the size of the table. Slow queries are also wasting a lot of resources, so just create it if it'll boost select queries and if slower inserts will not be a problem.

To finally answer your question: as you can't implement this logic using indexes and constraints, the only way I see is to create an INSTEAD OF INSERT Trigger. It'll hurt insert performance, but prior to insert you can check if older timestamp already exists in the table and then modify the timestamp, not insert the data, raise an error - up to you.


why is my Database Engine Tuning Advisor trying to create statistics, even when I'm using "auto create/update statistics" I made everything I can in some procedures and queries to tune some processes. So I'm using tuning advisor to see if I missed something, and to my surprise, there's a lot of statistics to be created ( Create statistics...).

But my database is set to auto create and update statistics. why is tunning advisor creating the? why sql server is not creating them during the execution of these queries/procedures?

Auto Create Statistics will not create multi-column statistics, only single-column. Indexes will create multi-column based on the index automatically, though.

So DTA is simply recommending statistics that don't exist as indexes already.

Most people don't recommend DTA anymore. I don't think it has been maintained for a few years now.  Lots of other tools like sp_blitz will recommend indexes a lot more intelligently.


ERROR: no schema has been selected to create in I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not solved. Here is a sample session with a newly created empty database:

mydb=> CREATE TABLE distributors (
mydb(>     did     integer,
mydb(>     name    varchar(40) UNIQUE
mydb(> );
ERROR:  no schema has been selected to create in
mydb=> show search_path;
  search_path
----------------
 "$user",public
(1 row)

mydb=> create schema public;
ERROR:  schema "public" already exists

Any hint? What should I look for?

Solved.
Thanks to the answer of Daniel Vérité I've solved with the following:

grant usage on schema public to public;
grant create on schema public to public;

Are these the default permissions on the public schema?

I have a single user who can access the database, so I think this anyway cannot raise security risks...

I think I should make the same modification on template1. Is it correct? How can I check if the permissions in template1 are correct (say, default values)?

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with:

revoke usage on schema public from public;

This is required when it's not desirable that people peek into other people schemas, even without selecting data from tables (which is granted through different privileges).

If this REVOKE hasn't been done in that database, it may have happened in the template database by which new databases are modelled (see CREATE DATABASE).

When a user has USAGE privilege, but lacks CREATE privilege on the schema, it's a different error when trying to create an object: permission denied for schema public.

To check the privileges inside psql, use \dn+ public.

By default (shown with extended display \x for readability):

# \dn+ public
List of schemas
-[ RECORD 1 ]-----+-----------------------
Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
                  | =UC/postgres
Description       | standard public schema

lack of a rolename before = means it's for all roles (=public)

Without public USAGE privilege

Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
                  | =C/postgres
Description       | standard public schema

Without public USAGE or CREATE privileges

Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
Description       | standard public schema


Mirroring and Buffer Page Write/s Is the "Buffer Manager: Page writes/sec" (* 8 KBytes) a good indicator of how much bandwidth will be used to replicate all databases via Availability Groups?

If not, why are these not equivalent? Is there just some overhead or are they largely unrelated?

Both AG and DBM will replicate primarily the log being generated on the principal. The buffer flush rate has some relation to the the log generation rate, but is not identical (think how 100 updates of the same row will generate 100 log records but will only flush the page once, and also think how a single insert could be described in maybe 256 bytes of log, but it dirties an entire page that, when flushed, writes 8kb) and the counters are not per database to start with.

There are some additional control messages and when AG is present there is extra logging, but not significant. I would say that the log itself is about 95% of the traffic. On one hand keep in mind that both DBM and AG can and will compress the log in traffic. But on the other hand DBM and AG will require fully logged operation (minimal logging cannot occur on a AG/DBM source). With AG the number of replicas also plays a role (at worse the traffic gets multiplied x times number of replicas).

You need to consider not only throughput, but also latency. In synchronous mode commits have to be hardened by the replica and this impact the operation latency. Check your current log flush rate in SQL Server, Databases Object.

Once established both DBM and AG have their own counters: SQL Server, Database Mirroring Object and SQL Server, Availability Replica. The raw traffic (including protocol overhead) can be measured also in SQL Server, Broker / DBM Transport Object. Of course, these require to first establish an AG and hit it with load :)

But for an estimate of number of bytes that will be sent (before compression), the log file virtual io stats, as suggested by mrdenny, is a very good aggregate indicator, but unfortunately it gives no information on the actual shape of the throughput (ie. spikes). Unfortunately there is no direct perfmon counter for that.


Install SQL Server 2017 over 2016 Local DB and 2012 native client I have downloaded Microsoft SQL Sever 2017 and would like to install it. But just to be sure I checked installed applications and I found that there is:

Microsoft SQL Server 2012 native client
Microsoft SQL Server 2016 LocalDB

installed already. ( I noticed that because there was a Microsoft SQL Server folder in Program Files)

Can I just proceed with the installation?
I don't recall having installed SQL server previously so I am not sure why those two are installed in the first place

Also, I would like to have SQL Server management studio installed. Is this a separated install?

The SQL Server 2012 Native Client is a client library for connecting to SQL Server, and SQL Server 2012 is the latest version. Installing SQL Server 2017 will not impact this, and vice-versa. More details here.

LocalDB is a feature of the Express Edition of SQL Server used by developers for testing against SQL Server with minimal configuration and overhead. You can install SQL Server 2017 side-by-side with a LocalDB instance without any issues. More details here.

SQL Server Management Studio has been separated from the SQL Server installation media since SQL Server 2016. You need to download and install SSMS separately from SQL Server. Download link here.


How does SQL Server determine the order it takes locks in while selecting a table? I have two stored procedures that are deadlocking when the system is under load. Proc A is selecting from a table while the Proc B is inserting into the same table. The Lock Graph is showing that Proc A has an S mode page lock that Proc B wants an IX mode lock for, Proc A however is waiting for an S mode page lock for a different page that Proc B already has a IX mode page lock on.

Clearly this could be sorted out by ensuring that both the queries lock pages in the table in the same order, but I can't figure out how to do that.

My question is: How does SQL Server determine what order to lock pages in while doing INSERTs and SELECTs and how can you modify this behaviour?

How does SQL Server determine what order to lock pages in while doing INSERTs and SELECTs a

Undetermined - done by internal processing and depending on query optimizer output.

  and how can you modify this behaviour?

Control your isolation. If you read in order to write, tell SQL Server to immediately get a write lock. Point closed.


WSUS for SQL server patching Can we patch Microsoft SQL server using WSUS?

Is it not recommended?

You'll often want a lot more control over that than WSUS offers. For example, you may have certain servers that you want to patch during maintenance windows. You may also want to be very careful about how you patch clusters, servers participating in log shipping, mirroring, replication, etc.

While they certainly exist, I don't personally know of any shop that uses WSUS to patch SQL Server. That doesn't mean it's not a good idea, but combining the variables above with the fact that there are only so many public updates to SQL Server that propagate to Windows Update in any given year, I just don't know how much you'll really gain by trusting it to do its thing.


Suppressing non-ANSI warnings in SQLCMD script I am working with a script that creates a SQL Agent job in SQL Server 2014. The job's last 2 steps are "Complete with Success" and "Complete with Failure". The script always warns of non-existent steps.

Warning: Non-existent step referenced by @on_fail_step_id.
Warning: Non-existent step referenced by @on_success_step_id.
Warning: Non-existent step referenced by @on_fail_step_id.

  SET ANSI_WARNINGS OFF

has no effect on these as they are, I imagine, not ANSI warnings.

Is there a way to suppress these?

I was able to reproduce the issue. Problem is step 1 is refering to steps that are not created yet. Following code will generate the same error you are getting.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test333',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'domain\account', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'GetDate',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=4,
        @on_success_step_id=2,
        @on_fail_action=4,
        @on_fail_step_id=3,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'select getdate()',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'success',
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'Print ''i am success''',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'failure',
        @step_id=3,
        @cmdexec_success_code=0,
        @on_success_action=2,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'print ''i am failure''',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

I commented out 4 lines on the 1st step. Added a section calling sp_update_jobstep. This code will not throw the error you are getting.  I am only pasting modified code here.

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'GetDate',
        @step_id=1,
        @cmdexec_success_code=0,
        --@on_success_action=4,
        --@on_success_step_id=2,
        --@on_fail_action=4,
        --@on_fail_step_id=3,
        ........
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Add this at the end of creating last step and before running sp_add_jobserver.

EXEC msdb.dbo.sp_update_jobstep @job_id=@jobid, @step_id=1 ,
        @on_success_action=4,
        @on_success_step_id=2,
        @on_fail_action=4,
        @on_fail_step_id=3


Purpose of SQL Server Application Roles I've been reading up on application roles in Microsoft SQL server. It seems an application role is like any other role in that permissions on securables can be granted to an application role, however, the difference being that it has to be "called" by an application using the stored procedure sp_setapprole.

The only reason I can see for using this is for a DBA to allow an application (and its developers) to connect to the database and not having to supply a server login or a database user and therefore the developers cannot connect directly to SQL Server (via SSMS for example) even then, I can't really see a benefit to this as surely the account provided to the application / developers would be granted the minimum required permissions anyway?

Am I missing something here?

Application roles do not eliminate the need to supply initial connection credentials. One must first successfully authenticate to the server/database using a login (SQL or Windows account/group), or contained database user) before an application role can be activated.

Application roles mostly differ from regular database roles in that they:

have no members
are enabled using sp_setapprole with the app role password after successful authentication
are unset using sp_unsetapprole with the cookie returned by sp_setapprole (required for connection pooling)

There is no value in using an application role when a service account (with the permissions needed by the application) is used for application database access.

A use case for application roles is when users connect to SQL Server from the application using their own security credentials (e.g. Windows authentication via Windows group membership) but do not have the permissions the application requires. Activating an application role in this scenario allows auditing of individual user activity (e.g. using ORIGINAL_LOGIN()) without granting permissions beyond CONNECT, although users could be granted permissions for needs outside the application, such as those needed for a reporting tool. This would allow read-only access for reporting while limiting writes via the application.


How to reclaim the unused Space in a database The database is almost of size 55GB. This database should have never grown to this size as some of the maintenance jobs were not running.

I have deleted almost 30GB of data. Now the database size is almost 45GB and Space Available is almost 20GB .

Which shows that Actual size of the database is now almost 25GB and there is some Unused space that needs to be returned to Operating System.

I have executed DBCC commands for both Database and individual files (8 of them). And I only managed to release a couple of GBs.

The Commands I used are as follows

DBCC SHRINKDATABASE (N'MyDB');
GO

DBCC SHRINKFILE('Filename1', 0 , TRUNCATEONLY)
GO
DBCC SHRINKFILE('Filename2', 0 , TRUNCATEONLY)
GO
  and so on....

But this seems to have no affect on database size and the available space.

I read an article which suggested rebuilding indexes and then shrinking database will solve this issue.

But once I have shrunk the database I would have to rebuild indexes anyway as shrinking database would have made a mess of indexes.

My requirement is to only leave 10% space for db to grow and release the unused space. Then I plan to rebuild Indexes and update statistics after that.

What would be the best approach to this problem. Any suggestions or pointers in the right direction are much appreciated.

Thank you in advance

Is there a particular reason you are specifying TRUNCATEONLY?  As per the documentation on DBCC SHRINKFILE:

  TRUNCATEONLY

  Releases all free space at the end of the file to the operating system
  but does not perform any page movement inside the file. The data file
  is shrunk only to the last allocated extent.

  target_size is ignored if specified with TRUNCATEONLY.

If that is not the intended behavior you were looking for (which it doesn't sound like it is), then you could try without that option set.

Also, there are other factors that hinder database file shrinking as well, such as text`ntext\image` BLOB data.  So it is worth looking into the possibility of the existence of data with these data types.


What happen if innodb_log_file become full? Once innodb_log_file ib_logfile0 & ib_logfile1 got full while running big transaction,so what is mysql next action in this situation ?

When innodb_log_file ib_logfile0 is full it start to write innodb_log_file ib_logfile1.

When this is full, it start to over with innodb_log_file ib_logfile0.

The only thing is to have enough space for both files.

The size is determined by `innodb_log_file_size=64M, but be careful mysql when you want to change the size, you need stop the server and delete the old ones. when you restart they will be created new.


Use STUFF function to insert at multiple points in a string? Is there a way that I can replace a substring in SQL server while remaining 'aware' of what I'm replacing so that I can then re-insert that content?

I am building a string of column names to pivot on, and these column names need to be aggregated on in the query.

For example, I have this list of columns as an nvarchar(max) string:

[A], [B], [C]

And I would like to transform this string to look like this:

sum([A]) [A], sum([B]) [B], sum([C]) [C],

I have just found the stuff function but I don't know how to achieve what I want yet.

But I get the feeling that I can use it to insert substrings instead of using replacing substrings.

Looking at the stuff function:

STUFF( source_string, start, length, add_string )

How do you insert into multiple locations at once?

My current solution is this:

declare @cols nvarchar(max);
declare @colsAggregate nvarchar(max);
declare @sql nvarchar(max);

select @cols = stuff((
    select distinct ',' + quotename(sCode)
    from EntityList EL
    where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'),
1, 1, '');

select @colsAggregate = stuff((
    select distinct ', max(' + quotename(sCode) + ') ' + quotename(sCode)
    from EntityList EL
    where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'),
1, 1, '');

You need to get it as rows before you can use STUFF.

DECLARE @rows nvarchar(max) = '[A],[B],[C]';
DECLARE @cols nvarchar(max);

SELECT LTRIM(RTRIM(j.value)) FROM STRING_SPLIT(@rows, ',') j

SET @cols = STUFF((SELECT ', ' + (col)
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ')')
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ') ' + col)
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,2,'');

SELECT @cols;
GO

| (No column name) |
| :--------------- |
| [A]              |
| [B]              |
| [C]              |

| (No column name) |
| :--------------- |
| [A], [B], [C]    |

| (No column name)             |
| :--------------------------- |
| SUM([A]), SUM([B]), SUM([C]) |

| (No column name)                         |
| :--------------------------------------- |
| SUM([A]) [A], SUM([B]) [B], SUM([C]) [C] |

dbfiddle here


Setting password for 9.3 PostgreSQL server I currently have a 9.3 server running (on Ubuntu 14.04) with "trust" set as the authentification method in pg_hba.conf. How can I set the password again. In pgadmin the server currently doesn't allow me to access the console (The PSQL console closes immediately after I open it).

I have the feeling that setting the password using

     $ sudo -u postgres psql

changes the password of a parallel running 9.1 server.

Using

     psql -U postgres

gives an error saying

   psql: FATAL:  Peer-Authentifizierung für Benutzer »postgres« fehlgeschlagen

(peer authentification for user postgres failed)

pg_hba.conf

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

Setting password in pgadmin

After setting the password for the postgres user (1), the ENCRYPTED PASSWORD appears in pgadmin (2), but psql -U postgres still fails.

Previous question:

Authentification failure for PostgreSQL server (9.3)

If your pg_hba.conf is set correctly and you're sure about your password, and you're still getting issues related to "peer authentication" failed, you probably need to reload your postgresql instance

/etc/init.d/postgresql reload 9.3

should reload your settings and allow you to use the password. Hope that helps. =)


Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table? Example you have the following table

CREATE TABLE emp (id,name,salary,manager)
AS
  VALUES
    ( 1  , 'james' ,  10000 , null ),
    ( 2  , 'alex'  ,   5000 , 1 ),
    ( 3  , 'Alice' ,   4500 , 1 ),
    ( 4  , 'Jone'  ,   3000 , 3 ),
    ( 5  , 'Omar'  ,   2200 , 2 );

Anyone who is a manager of your manager is also your indirect manager so Omar has Alex and James as indirect managers. I need a query that will get me the first indirect manager who has twice salary or more than the employee.

So result should be:

ID | Manager
1  | null
2  | 1
3  | 1
4  | 1
5  | 2

This is a query you can try. Sure it can be simplified but is a good start I think.
The key is to traverse all the managers chain recursively retaining salary information in order to compare later.

with recursive t1 as ( -- Traverse emps all the way up in the manager chain
  select id, name, salary, manager, salary indsalary, manager indirect, 1 as level
  from emp
  union all
    select t1.id, t1.name, t1.salary, e.manager, e.salary, t1.manager, t1.level + 1
    from emp e, t1
    where t1.manager = e.id
)
, t2 as ( -- Check salary condition
  select *
  from t1
  where  indsalary >= 2 * salary
)
, t3 as ( -- get the row numbers
  select t2.*, row_number() over (partition by id order by level) rn
  from t2
)
, t4 as ( -- retain only first row per emp
  select * from t3
  where rn = 1
)
-- Add emps that has no manager or don't fullfill the salary condition
select e.id, t4.indirect manager
from emp e
left join t4
  on (e.id = t4.id);

Result:

 id | manager
----+---------
  1 |  [null]
  2 |       1
  3 |       1
  4 |       1
  5 |       2
(5 rows)


MySQL Command to Mass Change Specific DB from MyISAM to InnoDB and vice versa? I have an mu WordPress database and I want to change it from MyISAM to InnoDB to test its performance.

Which MySQL command/commands will do it for all of the tables instead of doing manually one by one?

Is there anything that I should keep my eye on in this application?

There are two methods to mass convert all your MyISAM data into InnoDB

CONVERSION METHOD #1 : In-Place Conversion

You can run ALTER TABLE tblname ENGINE=InnoDB; against all MyISAM tables

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
SQL="${SQL} SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema NOT IN"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -AN-e"${SQL}" > /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
less /tmp/Mass_Convert_MyISAM_To_InnoDB.sql

If you are satisfied with the script's contents, then run it

mysql ${MYSQL_CONN} < /tmp/Mass_Convert_MyISAM_To_InnoDB.sql

I wrote a similar script 3 years ago : When to switch from MyISAM to InnoDB?

CONVERSION METHOD #2 : Dump and Reload

Verace's answer is short and sweet.

Here is the way to do it

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Gather all Databases except MySQL-centric Databases
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name"
SQL=${SQL} NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Dump the Schema Only, changing the Storage Engine on the Fly
#
DUMP_OPTS="--single-transaction --routines --triggers -d --add-drop-database -B ${DBLIST}"
mysqldump ${MYSQL_CONN} ${DUMP_OPTS} | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > Schema.sql
#
# Dump the Data Only
#
DUMP_OPTS="-t -B ${DBLIST}"
mysqldump ${MYSQL_CONN} ${DUMP_OPTS} > Data.sql

Then, you run the two scripts

mysql ${MYSQL_CONN} < Schema.sql
mysql ${MYSQL_CONN} < Data.sql

I wrote a similar answer last year : How to export mysql databases with different ENGINE?

NOTE : If you want to convert just one database, here is the in-place method

DB_TO_COVERT=mydata
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
SQL="${SQL} SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema = '${DB_TO_COVERT}'"
mysql ${MYSQL_CONN} -AN-e"${SQL}" > /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
mysql ${MYSQL_CONN} < /tmp/Mass_Convert_MyISAM_To_InnoDB.sql

GIVE IT A TRY !!!


Why is the "The package can not be saved to the file system since its protection level is server storage" error? When I execute

dtutil /sourceserver localhost /SQL "\Maintenance Plans\MaintenancePlan_daily" /copy file;.\MaintenancePlan_daily.dtsx

from the answer to my question "Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)?" I am getting the error:

  "The package \Maintenance Plans\MaintenancePlan_daily can not be saved
  to the file system since its protection level is server storage. Use
  the encrypt action to change the protection level"

What is the reason and how to btetter solve it?

MS SQL Server 2012 R2 Enterprise on Windows Server 2008 R2 Standard

Update:

I have dozens of SQL Servers 2012 R2 with equally set SQLServerAgent jobs using the "same" maintenance plans.
And this is kind of additional obstacle to using multiserver (target and master) administration, see my questions:

How to debug problems with SQL Server multiserver (Master and Target server) administration?
[Can maintenance plans be used in SQL Server multiserver (Master-Target servers) administration?][3]

I saw in my previous questions the recommendation to avoid using maintenance plans in SQL Server Agent jobs but so far I already have them (maintenance plans) set by others...

Let me reformulate the question:
Why don't I have this error in Windows 7 ?
What and where should I change in Windows Server 2008 R2 Standard (or SQL Server 2012 R2 Enterprise) in order to have the same absence of such error?

Update2:

Microsoft support article "SSIS package does not run when called from a SQL Server Agent job step" states:

"Change the SSIS Package ProtectionLevel property to ServerStorage. This setting stores the package in a SQL Server database and allows for access control through SQL Server database roles"

while I am using the sysadmin user-logins only.

Was it changed from SQL Server 2005-2008 to 2012?

I think you need to specify an "/en" option in order to change the encryption level.  See this link.

http://support.microsoft.com/kb/906562


How to improve Procedure Cache Hit Ratio? From what I can tell Procedure Cache Hit Ratio below 95% is a problem.  On my box, the values hover from 85 to 95%.

How do I fix this issue?  The server seems to have plenty of RAM so that shouldn't be an issue.  What else can it be?

Let me summarize (and round!) the important data points in your spreadsheet:

      Total                                     Use Count 1
      ---------------------------------------   -----------------------
      Total Plans   Total MBs   Avg Use Count   Total Plans   Total MBs
      -----------   ---------   -------------   -----------   ---------
Adhoc      55,987       3,054               3        38,314       2,036
Proc          709       1,502           1,549           135         527

So the first row shows the bad stuff, taking up about 2/3 of your plan cache (things which are mostly only ever used once, with a few very minor exceptions). You need to try and get rid of as much of these as you can. The second row shows the good stuff. These are the things you want in your plan cache (plans with a high amount of reuse). The rest of the data is largely irrelevant IMHO. One other point though: you say that access is exclusively through stored procedures, but if those procedures use dynamic SQL, those statements are cached as AdHoc plans, not Proc plans.

On 2008 or greater, I would say turn on optimize for ad hoc workloads and move on to the next problem - this would take the amount of MBs your single-use plans currently occupy down to next to nothing. Unfortunately, on 2005, your options are quite limited, aside from refactoring those stored procedures to use statement-level OPTION (RECOMPILE) and/or less / no dynamic SQL, or turning on forced parameterization at the database level - which tries to get better plan reuse out of similar queries by treating literals as parameters for plan matching purposes. I hesitate to even mention plan guides because they're not for the timid and - as I discuss later on in this answer - I'm not sure it's worth going down that path unless you know your plan cache is definitely the source of your performance issue.

I asked about @@VERSION because, before SP2, the algorithm for the amount of memory that could be allocated to the plan cache was relatively loosey-goosey. As of SP2 they tightened that up quite a bit (the change is documented and explained in this post and this post). In your case, the plan cache is relatively full, so it is not surprising you're getting cache misses. 26 GB = an upper limit of 5.8 GB; I see ~4.5 GB in the spreadsheet but there may be some calculation or configuration difference here that I don't know about.

This MSDN article talks about the optimize for ad hoc workloads server setting added in 2008, and mentions trace flag 8032, which will allow you to allocate more memory to your caches (presumably in the absence of setting this setting at the server level, which I now recommend to all of our customers, or at least the 99% that are no longer on 2005). I have never tested this trace flag on 2005 SP3 or SP4, and honestly not even sure when it was introduced. I also don't know if it will solve your problem or just shift it, since I think even if you had some % more RAM allocated to caches, you'd still be filling it and having lots of cache misses because of the nature of your stored procedures.

Or, of course, if there is even a problem to solve that relates directly to the plan cache at all. Just because your cache hit ratio isn't as high as you might expect does not mean that it is causing your problem, and of course the converse is that even at 100% cache hit ratio - which doesn't seem realistic given that so many of your plans are single-use and ad hoc - your users may still be suffering from performance problems caused by something else entirely.

My suggestion is to look for better smoking guns than plan cache hit ratio. Get more specifics about your users' performance complaints. Are all queries always slow? Certain queries? Certain times of day / week / business cycle? Are only reporting queries slow? Take a serious read through this admittedly dry and long document on SQL Server best practices - in particular, the section on waits and queues, which can help you formulate a logical approach to identifying, diagnosing and solving performance issues. Making some number on a dashboard look better - a number that you don't even know directly contributes to the problem - might be very satisfying, but if it doesn't solve your users' performance issues, then it hasn't really gotten you anywhere.

These may also be useful in reading up on compilation / recompilation and plan cache reuse. Some of these are focused on 2008 (particularly those about the ad hoc workloads setting), but much of the information is still useful for 2005 and/or to better understand the benefits of upgrading (hint, hint).

http://technet.microsoft.com/en-us/library/ms181055(v=sql.90).aspx
http://www.sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/
http://www.sqlskills.com/blogs/kimberly/clearing-the-cache-are-there-other-options/
http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/
http://www.sqlskills.com/blogs/kimberly/prepared-statements-and-caching/
http://www.sommarskog.se/query-plan-mysteries.html
http://www.sommarskog.se/dynamic_sql.html


PostgreSQL on Windows fails to start - 'could not create shared memory segment' PostgreSQL 9.4 & 9.5 (beta) on my Windows 7 laptop fails to start. In the Windows event log I see the following error:

  2015-11-10 13:53:49 IST FATAL:  could not create shared memory segment   "Global/PostgreSQL.851401618": Permission denied

It was working before for a long time, and suddenly it stopped.

I tried to uninstall and install, without any luck.

dynamic_shared_memory_type is set to windows.

Community Wiki answer generated from question comments by a_horse_with_no_name

Try setting dynamic_shared_memory_type = none in postgresql.conf.

"Permission denied" seems to indicate that the permissions for the user under which the service runs have been changed.


MongoDB insertion in two collections or one to improve insertion speed I have a collection, in which I have too many records (~300 million).
And the insertion rate is also very high and it takes time to insert documents, which is my main problem.

As I have two types of reports. Lets call them report_type_1 and report_type_2. Currently I insert both these reports in same  collection.
Will it make insertion any faster if I use different collections for these two different types of reports?

Simply, what is faster? Inserting 1000 records per minutes in a single collection or inserting 500 records per minute in two different collections?

As I have two types of reports. Lets call them report_type_1 and report_type_2. Currently I insert both these reports in same collection. Will it make insertion any faster if I use different collections for these two different types of reports?

The outcome will depend on the storage engine you are using and whether concurrency control is the limiting factor for your insertion rate. The MMAPv1 storage engine has collection-level locking, while the WiredTiger storage engine (default in MongoDB 3.2+) has document-level concurrency control.

If you are pushing the limits of MMAPv1 locking for your deployment, splitting the reports into different collections may improve your insertion rate unless the underlying issue is a resource bottleneck (slow disk, insufficient RAM, ...).

The WiredTiger storage engine has more granular concurrency control as well as data compression (which reduces I/O but adds CPU overhead). With WiredTiger you should be able to increase insertion throughput to a single collection by adding more insertion threads in your application (at least until your server resources are saturated).

Rather than trying to design around possible MMAPv1 limitations with multiple collections, I would encourage you to test a single collection with the WiredTiger storage engine and multiple insertion threads.

Outside of storage engines, a more general question is whether the different report types should logically be in the same collection. If they have different fields or indexes and you don't query across report types, you may find it more efficient to use separate collections.


structure when storing books in elasticsearch I want to use elasticsearch as a web search engine for books. A book has several editions with different titles in different languages, ISBN's as well as author names in different languages. I want that a book is found by any combination of title language and author name language i.e. the Latin name of Aristotle and the english title of one of his works.

How do I store all possible names of the author, all ISBN's, all titles with respective edition-id's of one book and information about the language in order to get the matching title, it's edition-id and its language as a result to a query?

I believe I need to use 'Nested Type', but I am not sure.

Like that I only find the _id of the book, but not more:
{
    _id: 1
    _source: {
        title: [
            Odyssey
            Odyssee
            Odisea
        ]
        isbn10: [
            2080674722
            5941453868
            2670361734
        ]
        fullname: [
            Homer
            Omero
        ]
    }
}

I found out how to do this with the help of this guide:
The nested type is right and in my case the only option.

curl -XPUT localhost:9200/readgeek/book/1 -d'
{
  "names": [
    {
      "english": "Omero",
      "german": "Homer"
    }
  ],
  "authorid": "1",
  "ratings": "2589",
  "editions": [
    {
      "title": "Odyssey",
      "languageid": "123",
      "isbn10": "1234567890",
      "isbn13": "1234567890123",
      "ratings": "158"
    },
    {
      "title": "Odisea",
      "languageid": "150",
      "isbn10": "1234568890",
      "isbn13": "1234569890123",
      "ratings": "15"
    },
    {
      ...
    }
  ]
}'


What is the difference between "ALTER DATABASE mydb SET search_path" and "SET search_path"? PostgreSQL 9.6. I just found both of these commands in a flyway migration, right next to each other. I feel like they are probably redundant but how do these statements differ? Is there a reason to use both? Apologies if this is a duplicate; I couldn't find an answer.

ALTER DATABASE will change the search_path for every new connection to that database. The change however does not affect current connections - including the one that ran the ALTER DATABASE. The set search_path then changes the path also for the current connection so that it has the same value as the default path.


MySQL Schema Change Management Tools? I am a developer who has worked with SQL Server by way of Entity Framework, I am now using MySQL by way of Node.js

I am stepping into your strange world to ask an operations question I hope you can help me with. I imagine this is pretty basic for many of you DBAs. I am not 100% sure what terms I should be searching for with this.

What tools exists where I can make schema changes to a Development database as I see fit, then at any time I can compare the present state of Development database to the present state of Production database.

For Example

Production vs Development

Now that tool will generate a diff SQL file that simple makes all of the Alters.

Thus

 -  OriginalSchema.sql
 -  001.update.sql
 -  002.update.sql
 -  003...

Finally, does a tool exists that can not only generate said scripts but can also manage the UP (patch) and DOWN (rollback) of running said scripts.

~$ tool compare local remote
~$ tool generate local remote
~$ tool apply remote latest
~$ tool rollback --steps=2

Many Thanks!

It's called Schema migrations and one of hundreds of tools that can manage that is migrate written in GO.

  Database migrations written in Go. Use as CLI or import as library.

I never heard that any of those schema migration tools can do a diff between dev and prd.


How to sync two or more collocation database server? I can't tell if mysql clustering could be a solution(I am thinking about galera or xtradb clustering). But my biggest problem is about the unstable internet connection.

Actually it is planned as multi-tenant setup with a single online mysql server and the tenant is separated by schema, and a tenant can also have their own local database on their location.

But I want to solve this problem first.

For example I have a tenant, but this tenant has a multiple collocation, and want to sync the data on every location and also on the online server.

They can read/write on the first, second, etc collocation server using a desktop app(respective to their location) and they can also read/write on the online server using a mobile app.

Now my problem is how could I sync them, when internet is available?
So data written and updated on the first location will propagate to other server, on the online server on the second server and so on, and vice-versa.

P.S.
Data deletion will be soft-delete.

Thanks.

Suggest you have multiple instances in the central server.  It could be a single installation of mysql, but separate data directories and my.cnf and Port (or use VMs).

After doing that, you can make lots of pairs of "Dual Master" setups where either Master can be written to.  And they can get into serious trouble with auto_increment keys, unique keys, and other things that plague M-M.

No, I really think, you should start over on your 'requirements'.  Currently you are asking for a maintenance nightmare.


Converting UTC time to timestamptz always adds the +01 offset in Postgres In Postgres 9.5, I do the following to convert a timestamptz to the UTC timezone:

SELECT TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC'

this outputs correctly 2016-02-20 14:15:54.000495 as timestamp without timezone.

I would like to store this value in a timestamptz column. However, when I do the following:

SELECT (TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC')::timestamptz

I would expect to get 2016-02-20 14:15:54.000495 as timestamptz.

However, this query gives me 2016-02-20 14:15:54.000495+01 as timestamptz, which sadly doesn't even represent the original time.

Whats the reason of such behavior? How do I make this work correctly?

A simple cast from timestamp to timestamptz assumes your current time zone. If you want the cast to assume UTC (or any other time zone) you have to use the AT TIME ZONE construct again:

SELECT (timestamptz '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC') AT TIME ZONE 'UTC';

You may be missing that the AT TIME ZONE has two different use cases. It can transpose timestamptz to timestamp and vice versa.
Related answer with detailed explanation:

Confused about TIMESTAMPTZ internal conversion

Be aware that your statement does not make sense:

  I would expect to get 2016-02-20 14:15:54.000495 as timestamptz.

The text representation of timestamptz is always displayed as local time according to your current time zone setting, extended with an offset to UTC. The literal you display has no offset, so it is a timestamp literal, not timestamptz. Basics here:

Ignoring timezones altogether in Rails and PostgreSQL


Performing Backup in 2 different ways in parallel? We wish to perform backup in SQL Server in 2 different ways in parallel. Now currently we have a 3rd party tool that is performing the Backups, Full and transaction log, now the business wants to perform also backups on disk via local SQL Server jobs.
The question is that would be possible, the LSNs chain will be affected as I know, and it won't be possible to perform valid restores, is that correct?
Even If we will perform Full backup and Transactional backup on backup server and also full backup and transaction backup to be keep on local disk via SQL jobs.
Would it be possible?

There is no way to take additional transaction log backups without affecting the LSN chain. Even taking COPY_ONLY transaction log backups would be completely pointless, as the log chain of the COPY_ONLY backups would be incomplete. You can read more on that here.

You could take additional FULL backups to a local disk without affecting the log chain (if you don't take DIFF backups, the COPY_ONLY flag is not needed, as it only affects the differential base).

If the third-party tool is unreliable in some way, you could back up to a local or network disk and have the backup tool take copies of the backup files instead of taking database backups.

However, if you have issues with that product, I would rather focus on fixing those issues. Many backup tools support a limited subset of the SQL Server backup options and try to treat databases as "files" to better fit in the general purpose backup model they already have. If this is the kind of issues you're hitting, you could also consider dropping the tool altogether and use it to backup your backup files. The main advantage of such tools is centralized management and scheduling for all backups across all instances, which is something you would lose if you switch to SQL Server Agent controlled schedules.


Is there a backup operator role in Microsoft SQL Server 2008? I have a Windows Server 2008 with Microsoft SQL Server 2008 and VERITAS NetBackup installed. Basically, this NetBackup requires a valid logon ID into the SQL Server that is powerful enough to do a backup and restoration.

I have created a local ID on the OS called backup01 and assign it to the Backup Operator group. Next, I created a login in SQL Server 2008 with for backup01 with Windows Authentication. As for server roles, I assign it as 'public' and 'sysadmin'. With this, the backup and restoration via NetBackup will always work.

However, our security policy discourage the use of sysadmin or administrator equivalent rights to be assigned to non-administrator users.

In this case, where can I find this backup operator role in Microsoft SQL Server? Or is there any guide or tutorial that walks you through on creating a new role? Thank you!

It is not a system level role, it is assigned in each database.  Assign the login "public" and "db_backupoperator" roles in each database it needs to backup.

http://www.sqlbackuprestore.com/backupandrestorerights.htm


"ORA-12899: value too large for column "V_UUID" (actual: 36, maximum: 4000)" when used to generate a Virtual column I have the following function:

CREATE FUNCTION UUID_AS_HEX(bytes IN RAW)
  RETURN CHAR DETERMINISTIC
  IS uuid CHAR(36);
  BEGIN
    RETURN REGEXP_REPLACE(bytes, '([0-9A-F]{8})?([0-9A-F]{4})?([0-9A-F]{4})?([0-9A-F]{4})?([0-9A-F]{12})', '\1-\2-\3-\4-\5');
  END;

and I want to create a virtual column:

CREATE TABLE example_table
(
  uuid               RAW(16)                                   NOT NULL PRIMARY KEY,
  v_uuid             CHAR(36) GENERATED ALWAYS AS (UUID_AS_HEX(uuid)) VIRTUAL
);

throws the following exception:

ORA-12899: value too large for column "V_UUID" (actual: 36, maximum: 4000)

I tried creating the function with RETURN CHAR(36) DETERMINISTIC but it complains about that syntax with

2:14:PLS-00103: Encountered the symbol "(" when expecting one of the following:
; is authid as cluster order using external varying character

How do I create this function where it match the CHAR(36) in the table?

After some experimenting I was able to get it to work by casting the result of the function call:

CREATE TABLE example_table
(
  uuid RAW(16)    NOT NULL PRIMARY KEY,
  v_uuid CHAR(36) GENERATED ALWAYS AS (CAST(UUID_AS_HEX(uuid) AS CHAR(36))) VIRTUAL
);


Linux Slackware MySQL can't move datadir By default datadir was located in /usr/lib/mysql. I decided to move it to a different disk partition: /mnt/C/mysql:

drwxr-x--- 25 mysql mysql 36864 Feb 25 12:52 mysql

I opened /etc/my.cnf, but there was no datadir command there. So, I added one inside [mysqld] block:

datadir     = /mnt/C/mysql

But when I try to start mysqld, it seems to be using the old directory:

bash-4.2# /etc/rc.d/rc.mysqld start
mysqld_safe Logging to '/var/lib/mysql/asus.err'.
touch: cannot touch ‘/var/lib/mysql/asus.err’: No such file or directory
chown: cannot access ‘/var/lib/mysql/asus.err’: No such file or directory
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/bin/mysqld_safe: line 107: /var/lib/mysql/asus.err: No such file or directory
/usr/bin/mysqld_safe: line 144: /var/lib/mysql/asus.err: No such file or directory
mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended
/usr/bin/mysqld_safe: line 107: /var/lib/mysql/asus.err: No such file or directory

I found the portion of /usr/bin/mysqld_safe file, responsible for the error:

# Try where the binary installs put it
if test -d $MY_BASEDIR_VERSION/data/mysql
then
  DATADIR=$MY_BASEDIR_VERSION/data
  if test -z "$defaults" -a -r "$DATADIR/my.cnf"
  then
    defaults="--defaults-extra-file=$DATADIR/my.cnf"
  fi
# Next try where the source installs put it
elif test -d $MY_BASEDIR_VERSION/var/mysql
then
  DATADIR=$MY_BASEDIR_VERSION/var
# Or just give up and use our compiled-in default
else
  DATADIR=/var/lib/mysql
fi

So, it seems to be using the default dir: /var/lib/mysql. I changed it to /mnt/C/mysql, but it didn't help.

What am I doing wrong?

The easiest way is to create a symbolic link to new data directory.
When your new data directory is ready to use, stop mysql server and type:

rm -r /var/lib/mysql
ln -s /mnt/C/mysql /var/lib/mysql

Now you can restart mysql server with standard datadir.


How to recover deleted/updated record in sql server or mysql? I want to know that is there any way that I can can recover the deleted or updated record in the sqlserver and mysql as well?

You always have a choice to use a recovery tools, such as ApexSQL Log for example. Not only that you can recover deleted rows, but you can also choose which rows you are going to recover.

It can recover your data from on-line, backup or detached transaction logs.

I have tried that tool and it could be the life saver.

See: How to recover SQL Server data from accidental UPDATE and DELETE operations


This is a bit late, but the previous answers don't provide the third alternative which is both flexible and normalized.

All tasks should be kept in a Task table, because subtasks after all are just tasks.

The relationship between tasks should be stored in a second table that has columns to identify the parent/sub task relationship based on a foreign-key relationship with the Tasks table, and which together would form a composite primary key.

Tasks

id
title
description
start_date
end_date

TaskRelationships

parent_task_id
sub_task_id

In order to find the parent tasks of a given task you can query the sub_task_id column. To find the sub-tasks of a given task search the parent_task_id column.

This structure has the added benefit of having the flexibility to add as many parent tasks to a task as your wish.


Reclaiming unused space from datafile in oracle Tablespace                        Used MB    Free MB   Total MB
------------------------------ ---------- ---------- ----------

 DATA_S1                               686        295        981

We have a table space named DATA_S1 which contains one data file. As we can see that around 300M space of the data file is unused. If I try to shrink the datafile by 200M by:

alter tablespace TABLESPACE_NAME datafile 'DATAFILE_NAME' resize 800M;

it gives:

ORA-03297: file contains used data beyond requested RESIZE value.

Is there anyway I can reclaim the space from datafile

Prior to resizing, you have to remove fragmentation in the datafile. That means every segment (sets of blocks assigned to each object in that tablespace) needs to bring together all rows inside their db blocks.

For this you have several options:

If you're on 11gR2 you can use

ALTER TABLESPACE <TABLESPACE_NAME> SHRINK SPACE KEEP 700M;

That will remove the fragmentation and shrink the associated datafiles plus it will set the HWM in the "lowest" possible position.

CAUTION: This is a n I/O intensive operation, never do it during business hours or outside a maintenance window for production environments.

Another option that can be used is the creation of a "backup as copy" of the fragmented datafiles and then switch the database to point those "image copies". Then drop the original and redo using the image copy as the original and backing them up "as copy" to the original location. It's a little longer and some more complex, but the "downtime" is just a few seconds while the switch is performed.

Refer to the Oracle Database 11.2 RMAN Reference Manual for more details on this option.

Hope this helps you.

If not, please add more detail to the question. Like version of the software, maintenance window time, accepted downtime, etc.


executing command prompt command in MySql trigger I have one table called table1. In it there are two fields, let say field1 and field2. What I am trying to do is that, when there are records inserted into table1, the values of field1 and field2 get stored in one text file called abc.txt which is on c drive.

I want to do it by calling the command prompt command which is called inside table1's After insert trigger called trigger1.

I have done it in SQL Server but I need it using MySQL. Here is the SQL Server trigger:

create trigger [dbo].[trigger1] on [dbo].[table1]
for insert

As

Declare @field_1 varchar(50),@field_2 int,@cmd varchar(250);

select @field_1=i.field1 from inserted i;
select @field_2=i.field2 from inserted i;

PRINT @field_1
Print @field_2;

SET @cmd= 'echo ' + @field_1+','+cast(@field_2 as varchar)+ '>C:\abc.txt'
EXEC Master..xp_cmdshell @cmd

The MySQL trigger that I have written is:

delimiter #
create trigger trigger1 after insert on table1
for each row
begin
Declare field_1 varchar(50);
Declare field_2 int(50);
Declare cmd varchar(200);

SET field_1 = NEW.field1;
SET field_2  = NEW.field2;

System echo field_1 > C:\abc.txt
end#
delimiter ;

After executing this command it gives an error as

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'echo
field1 > C:\abc.txt
end' at line 11

I am new to MySQL. Maybe I am doing it wrong. Any help is appreciated.

system is one of MySQL's internal commands, interpreted by the mysql command-line tool rather than by the SQL engine, as stated in the manual:

mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. [...]
system    (\!) Execute a system shell command.

[...]

You can't run the system command inside a stored program (and that includes a trigger), because the entire body of the stored program is sent to the server and the server won't recognise system.
However, you can get an output file with SELECT ... INTO OUTFILE FILE:
  SELECT field_1 INTO OUTFILE '/tmp/abc.txt'
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';


What are the user privileges required to for an ssrs report owner to 'send email' Yes, this question is a duplicate to:
  SSRS - rsAccessDenied error but that question is 2
  years old and unanswered...so I thought I might try again.

Our DBA has recently departed leaving behind a number of SSRS report subscriptions to which he is (or was) the 'owner'. Based on the advise found at Changing the SSRS Subscription Owner I have created a new domain account, assigned that domain account full privileges on the SSRS server (at least 'for now') and yet I am still getting the error message:

  Failure sending mail: The permissions granted to user 'DOM\NewUser'
  are insufficient for performing this operation.Mail will not be
  resent.

What privileges, in SSRS, SQL Server and/or Active Directory are required to allow "NewUser" to send email.

Is the new user allowed to send mail on the SMTP server?

You can do this:

Review this and make sure your setup is valid with the correct user (MSDN Link).
Also check the settings in RSreportserver.config (MSDNlink).


Why does SQL Server resume mirroring after server is restarted? I paused database mirroring before I rebooted the Windows server, and I found that the database had resumed mirroring automatically after the reboot.

This is not what I want--I want to resume it manually after I do some verification.  Is it normal for database mirroring to resume automatically after a reboot, even if it was paused prior to the reboot?

Is that normal?

This is the default behavior to automatically resume mirroring when the mirror comes up (provided no errors encountered). It will be in suspended state if there is a REDO error.

  I want to resume it manually after my verification...

If it resumes by itself then everything is good. You dont have to worry. If it does not then there is a problem and you will start troubleshooting.


Which allows for faster queries in a graph database: a node with multiple properties, or a node that points to multiple nodes? I just started using Neo4j and I don't know when it'd be best to add properties to a node vs when to create more nodes. I'd assume for simple things like x and y coordinate data, it's better to use node properties. Then for stuff like user information it's better to use additional nodes.

Is there a threshold where it becomes more efficient to use one or the other? For instance, if all the properties in question don't have any children, is it always better to use node properties rather than additional nodes? Is there some cutoff like 10 or more properties it's better to use multiple nodes?

Here's the specific example I'm working with if it helps anyone understand my question. Users post blocks which in turn have a type, x coordinate, y coordinate, a url destination, and a number of upvotes. The left graph uses a single node for the block with multiple properties, and the right graph uses a new node for each property:

With the given information, I find it difficult to give you a simple answer.  As you have discovered, data can be hierarchical with many different logical groupings that could work.

Here is a checklist of things you can do to get a better answer for yourself:

What are the full set of queries you will want to do?  (Among your
schema options, which one(s) minimizes the number of times you're
going to disk to retrieve data for your set of queries?)
How will your data set scale?  (Among your schema choices which one(s) scale gracefully, minimizing the number of fat nodes or other degenerates
that will not scale well?)
What kind of churn do you expect?  How much are you willing to slow writes in order to accelerate reads?

I hope this helps.


How to add function to particular column in while creating table in Postgres? I want to add a function to particular column while creating a table in PostgreSQL. After creating table, it has to generate auto generated id when we insert values into to the table.

There is serial and bigserial numeric type to autogenerate numbers i.e. autoincrement.
If you want to have custom function then you need to create trigger functions first and assign it to the trigger on insert.

More in the postgresql docs:
http://www.postgresql.org/docs/9.4/static/datatype-numeric.html


How to find out if a row with a specific primary key exists in a table in SQL I have created a table in SQL which is named testTable like this where I have defined the:
PRIMARY KEY (FeatureNbr, DateTime)

CREATE TABLE testTable
(
    [DateTime]    SMALLDATETIME  NOT NULL,
    [FeatureNbr]  SMALLINT       NOT NULL,
    [Value]       FLOAT (53)         NULL,
    [Bool]        BIT                NULL,
    CONSTRAINT UC_testTable UNIQUE (DateTime),
    CONSTRAINT PK_testTable PRIMARY KEY (FeatureNbr, DateTime)
);

I have now inserted 4 rows to the table like this:

INSERT INTO testTable(DateTime, FeatureNbr, Value, Bool) VALUES('10/22/2010 22:47', 0, 0.7556864869907925, 0);
INSERT INTO testTable(DateTime, FeatureNbr, Value, Bool) VALUES('10/22/2010 22:40', 5, 0.7556864869907925, 0);
INSERT INTO testTable(DateTime, FeatureNbr, Value, Bool) VALUES('10/22/2010 21:48', 0, 0.7556864869907925, 0);
INSERT INTO testTable(DateTime, FeatureNbr, Value, Bool) VALUES('10/22/2010 18:48', 20, 0.7556864869907925, 0);

Now assume that this table has 100 million rows or more. What is the fastest way to return if the above rows EXIST by looking at the primary key FeatureNbr, DateTime
We are then looking for exactly the rows which has those primary keys:

0, '10/22/2010 22:47'
5, '10/22/2010 22:40'
0, '10/22/2010 21:48'
20, '10/22/2010 18:48'

I will for example look for 5000 rows like this. Do I need to in some kind of loop look for each row one by one(perheps an exact search is extremely fast in this case? or how do I return a result for all lines or for each search one by one?).
I will know the FeatureNbr which always are all integers between 0-5000. So we have only 1 of each here which makes it 5000 rows. However, - DateTime will be different for each `FeatureNbr.
I have found this line but I don't know how to apply this , -or if this is the correct thing to use?
If IndexProperty(Object_Id('testTable'), 'MyIndex', 'IndexID') Is Null

I actually tried to look at this link: https://www.w3schools.com/sql/sql_join.asp
I describes how to INNER JOIN. It seems that I got the correct result using the below code.

Assuming that testTable is the one with 100 Million Rows
Assuming that testTable2 is the one with 5000 Rows

I could find the exact matches for FeatureNbr and DateTime as a result.
Please tell if I do miss anything there or if I am thinking wrong so I can edit the post.
SELECT testTable.DateTime, testTable.FeatureNbr
FROM testTable
INNER JOIN testTable2 ON testTable.FeatureNbr=testTable2.FeatureNbr AND
                         testTable.DateTime=testTable2.DateTime;


PostgreSQL Complete Database Backup I've been trying to figure out how I can completely backup my PostgreSQL database.
I have not seen any resource that explains how to do a complete backup.

I am aware of the pg_dump and pg_dumpall utilities.  The syntax is simple, but the details are not very clear.

It appears that pg_dump only backs "some" tables for a particular db and none of any type of configuration info. I am guessing that things like permissions and many other things are not backed up. So in the event of a total re-install of a PostgreSQL db, the dump backup would not work without significant manual configuration and setup.

pg_dumpall backs-up some more things than pg_dump but again no clear info is provided in the Postgres docs about what else is left out from the backup dump and needs to be backed-up in addition to.

I need to back up all PostgreSQL configuration info, tables, indexes, permissions, schemes, etc.  In the event of a complete catastrophe, provided that I reinstall the same version of PostgreSQL I should not
have to reconfigure anything.

I don't care for example if the indexes are re-built from scratch of if the backup already stores the indexes... stuff like this doesn't matter to me... I just want the db to be in the exact same state after the restore as it was at time of backup.

I'd appreciate if someone could list the things needed to be backed-up in order to achieve a complete backup.  Of course, I'd appreciate if you would recommend some tools along with some procedure for the various things on the list.  Please explain which items on the grand list the tool & procedure you recommend covers.

Actually, pg_dump backs up all the data, permissions, indexes and whatnot that constitute the database. What is not backed up are the PostgreSQL server's configuration files, globals (roles and tablespaces), and its actual on-disk files and layout.

If you want to back up everything, you should use pg_dump to dump each of your databases (I recommend the custom format), and then grab the config files in your $PGDATA directory: postgresql.conf, pg_hba.conf and pg_ident.conf. If you use tablespaces, you'll need to also keep track of where they link to in the pg_tblspc subdirectory of $PGDATA. I would not use pg_dumpall, it's harder to restore from. The rest of the files in $PGDATA don't need to concern you, since a hot copy will probably look defective to PostgreSQL anyway.

Amendment: you should also do pg_dumpall -g to get your roles and tablespaces backed up as well. Your restore strategy is then as follows:

initdb -D <new data directory>
start up PostgreSQL
psql < (file with output of pg_dumpall -g)
createdb for each database
pg_restore -d <database> <pg_dump file> for each database

Why would you rather use pg_dump than pg_dumpall for everything? For two basic reasons:

pg_restore has features that depend on the dump being in the custom format. The main one right now is parallel data restoration, which can significantly improve restore speed. pg_dumpall can only produce one big SQL script.
The script produced by pg_dumpall cannot be used to selectively restore databases. The script contains instructions to create this database, change to it, load the data, then create the next database. If you have several databases in your cluster it will be difficult to restore them individually from a script generated from pg_dumpall.

That said, if you have only one database in your cluster the above steps may be too much overhead for the amount you gain. Even a simple backup made with pg_dumpall is better than no backups at all. Just remember to get all the other stuff on disk mentioned above as well (postgresql.conf, pg_hba.conf and whatever changes to the layout you made for tablespaces).


Dump of a mysql table on import replaced existing records I took a dump using mysqldump..

mysqldump -u... -p... mydb t1 > mydb_table.sql

Then I imported the dump in another database having same table, but different records..

mysql -u...-p... mydb < mydb_tables.sql

The importing db had records from primary_key 1 to 1000, and the exporting db had 5000 to 10,000..

But on import the existing records, ie 1 to 1000 got deleted..

How?? Why?? If it a default behaviour, what options I can give to dump to not let it happen next time..

The mysqldump, by default, will drop the table. You should specified the --no-create-info option like this:

mysqldump -u... -p... --no-create-info --skip-extended-insert mydb t1 > mydb_table.sql

That way, you only have inserts to deal with. Using --skip-extended-insert will insert one row at a time. This help deal with duplicate issues, but you will have import like this:

mysql -u...-p... --force mydb < mydb_tables.sql

The --force option is for the sole purpose of continuing INSERTs in the event a duplicate key is encountered. In that instance, the offending INSERT's error is ignored and on to the next INSERT.


Help for grouping a field and taking total sum I have a sorted data in a table

F1  F2  F3  F4
A   M   X   1
A   N   Y   2
A   P   X   3
B   M   X   3
B   N   Y   1
B   P   X   5

but I want the sub count of values in F1 column for every row. like this

F1  F2  F3  F4  Sub Total
A   M   X   1   6
A   N   Y   2   6
A   P   X   3   6
B   M   X   3   9
B   N   Y   1   9
B   P   X   5   9

I tried to take sum

select *, sum(count) from table group by F1, F2, F3;
But this is not giving total in the last column.

Please help me

One way is to do a sub-select for the sum of F4 (this is the only explanation I could come up with in order to get 6 and 9)

SELECT x.F1,  x.F2,  x.F3,  x.F4
     , ( SELECT SUM(F4)
         FROM T as y
         WHERE x.F1 = y.F1 ) as sub_total
FROM T as x

If you DBMS support window functions:

SELECT x.F1,  x.F2,  x.F3,  x.F4
     , SUM(x.F4) OVER (PARTITION BY x.F1) as sub_total
FROM T as x


How to log successful login attempts to a database? Is there a way to log database name when auditing logins in SQL Server (2005 and above)?

I basically want to log the username and the database name the user successfully connected to.

I have tried profiler, databasename column is blank.

Your best bet is to use a Logon Trigger

Just make sure your trigger captures SYSTEM_USER and ORIGINAL_DB_NAME() (this is only available in SQL 2008 +).

Just be careful with logon triggers. If you mess them up you can prevent any logins, because when they fail they cause the login to fail and then you have to use a workaround to get into the system and drop the trigger so you can fix it.

Don't let that hinder you though, just know you need to test them out thoroughly before you go throwing them onto a production server.


MS SQL Server: Import Flat File with auto-increment ID So I've downloaded a sample database. It originally didn't have an ID column so I inserted one in through Excel. Anyways, I went through DatabaseName > Tasks > Import Flat File and the usual process, and arrive at the modify columns below.

My issue is, where can I specify that the ID column should be auto-incremented? And if I can't do that here, what should I do as a workaround?

You cannot alter a column to be an IDENTITY column.

What you'll need to do is create a new column which is defined as an IDENTITY.

Then use an update statement to update the new column from the old one (with SET IDENTITY_INSERT ON before and OFF after the update).

Once this is done, drop the old column.

Fianlly, rename the new one to the old name.


Is there any practical difference between using the Lookup Wizard to create a FK relationship, or using the Relationships window? I'm very new to Access, so I'd appreciate patience if this is a basic topic.

I'm creating a lot of tables which I need to link. I have two tables, Keyword and KeywordOrthography. KeywordOrthography has a KeywordFK field that connects to the Keyword.ID field.

As far as I know there are two ways to connect KeywordFK to KeywordID:

Go into the relationships window, drag Keyword "ID" to "KeywordFK", create a One-to-Many relationship (enforcing referential integrity.)
Clear any existing relationships between the two tables, go into Table Design view, and on the KeywordFK field, select Lookup Wizard. Select get values from another table, and import the ID field from Keyword. Then when I go to input a new record into KeywordOrthography, I can select from already existing Keywords to input.

Both methods create a 1-to-n relationship with the table, and the second way makes it easier to input a new record manually, though I'm not sure if it causes any complications when using SQL or other methods when adding new records. On the other hand, the first method is much faster when it comes to linking tables.

Question is: Are these methods equivalent as I've pre-supposed? Is there an advantage to doing it one way or the other?

Creating a relationship through the relationship pane does one thing: it creates a relationship (obviously).

The lookup wizard, by contrast, can do multiple things:

Create a relationship (identical to one created through the relationship pane)
Add a field to the table, set that field to be a combo box based on the relationship storing the foreign key (you can do this manually in table design view as well, but it's not as intuitive)
Dangerous: make that field a multi-valued field so you can store multiple keys in a single row

If you use that last option, you will run into trouble when migrating, since other databases generally don't support multi-valued fields. Also, multi-valued fields are generally a mess to deal with further on, and I highly recommend not using them.

The second option creates somewhat of a mess as well, as the data that's displayed (data from the linked record) is not equal to the data that is stored (the foreign key). This can lead to unexpected behavior when trying to query/filter/export the data (since sometimes you operate on the displayed data, and other times on the stored data), but is not really a problem when migrating it (as it just migrates the foreign keys). You might need to reapply the combo box settings after migrating, though.


mysql select everything before a number I am trying to get the outer and prefix for uk postcodes i.e. NW3 6BX. A space divides the outer part of the post code for which extracting it can be easily achieved by doing:

SUBSTRING_INDEX(delivery_postcode, ' ', 1) AS outer_postcode

I would like to get also the prefix which is the the first one or two letters before the numbers. In this case NW. I would like to use the previous expression replacing  the ' '  with any number. How to do this ?

Thanks,

As there are only two possible results (one or two characters before the digit) you can do a brute force approach using MySQL's limited regex support:

case when delivery_postcode rlike '^.[0-9]'         -- a digit as 2nd character?
     then substring(delivery_postcode from 1 for 1) -- first char
     else substring(delivery_postcode from 1 for 2) -- first two chars
end


CTE To Use Alternate Select When Select Returns No Rows I have created an involved CTE, with no recursion, but with multiple selects being done as it processes the business logic of the requirement. Though, I have been advised that the logic needed for the scenario where no rows are returned, should instead to return all rows found from one of the middle CTEs select instead of the final operation.

How can this be achieved?

Note the following example is basic interpretation of my process and the operation in question occurs after multiple cascaded CTE selects are done an essentially chained together.

Example (as found on SQLFiddle)

In a User Table there are two records where the ids of UserIds are 1 and 2. The first CTEs select, TargetUsers will return no rows requiring the the third CTE, ComputeWithUsers, to use the AllUsers instead.

WITH TargetUsers AS
(
  SELECT *
  FROM Users
  WHERE UserId > 5
)
, AllUsers AS
(
   SELECT * FROM Users
)
, ComputeWithUsers
(
-- This CTE will determine it needs to use `AllUsers` and not `TargetUsers`
)

Or is there a way to make the TargetUsers select smart enough to extract all users when its main operation returns no rows?

Here is the way I would do this, assuming the "do it all in one query" isn't just a pointless, artificial requirement.

SELECT cols INTO #x FROM dbo.Users WHERE UserID > 5;

IF @@ROWCOUNT > 0
  SELECT cols FROM #x;
ELSE
  SELECT cols FROM dbo.Users;

If the "do it all in one query" is a hard requirement and you can't use a #temp table, or you really have complex logic you've dumbed down from us and/or don't want to repeat, then you're going to have to pay for those choices in performance, I'm afraid. This looks like it should be quite efficient, but there are going to be more hits to the base table than most people would hope / expect:

;WITH TargetUsers AS
(
  SELECT cols FROM dbo.Users WHERE UserID > 5
),
AllUsers AS
(
  SELECT cols FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT cols FROM TargetUsers
  UNION ALL
  SELECT cols FROM AllUsers
)
...;

In fact if UserID is the primary key and the filter really is on UserID, this might be better as follows (but you'll have to check the plans to see, depends on a variety of factors):

;WITH TargetUserIDs AS
(
  SELECT UserID FROM dbo.Users WHERE UserID > 5
),
AllUserIDs AS
(
  SELECT UserID FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT UserID FROM TargetUserIDs
  UNION ALL
  SELECT UserID FROM AllUserIDs
)
...
-- and join to dbo.Users to get other columns necessary
-- deferring that to as late as possible

Hopefully your queries are using suboptimal practices due to simplification, and not like that in production, but please give these a read:

Bad habits to kick : using SELECT * / omitting the column list
Bad habits to kick : avoiding the schema prefix