Is it possible to set PostgreSQL roles with privileges this way? I'm trying to develop a system, and for the first time I'm using PostgreSQL. I'm trying to organize and separate roles properly, but I'm losing my mind with it (about a week on this now).

Following the title of this question, is it possible to set the following(?):

A user who can do everything with all tables, except perform CRUD
operations on the records of the same. In addition, this user can not create, delete, modify or insert roles.
Another user who can perform all CRUD operations on table records,
but can not perform any structural change operation in database
objects. This guy cannot create/D/M/I roles as well.
A third user who behaves like DBA, able to do anything.

The first user serves as a limited DBA who can make structural changes, but can not snoop data. The second is a system using the database. The third and last is a DBA with full control.

This question comes from another one previously made by me, similar, but not equal. I do not want to know what's wrong with that code anymore.  Also, I believe that here is the most appropriate place to ask things like that.

I want to know if this is possible. If so, I would like to see how. If not, I want an explanation of why.

Thank you for your attention in advance.

What is possible is what's specified in the documentation for GRANT (http://www.postgresql.org/docs/current/static/sql-grant.html).

To make modifications on tables, a user must own the object. When a user owns an object, a user can do whatever they want with it (including restricting themselves any kind of access, along with the ability to grant themselves any access again).

For your limited DBA, there are non-standard ways in which your requirements can be implemented, such as a DBA creating a function with SECURITY DEFINER that validates the executing user, and the command to execute, as something allowed, like the following example:

CREATE OR REPLACE FUNCTION limited_dba_action (p_action text) RETURNS void AS
$body$
BEGIN
  IF p_action !~* '^\s*(CREATE|ALTER)\M' THEN -- add any other valid operations, such as TRUNCATE, DELETE, etc
    RAISE EXCEPTION 'Valid operations through this function are limited to creating and altering objects';
  END IF;

  IF p_action ~ ';\s*[^\s]' THEN
    RAISE EXCEPTION 'Please include only one operation';
  END IF;

  EXECUTE p_action;
END;
$body$ LANGUAGE plpgsql SECURITY DEFINER RETURNS NULL ON NULL INPUT;

GRANT EXECUTE ON FUNCTION limited_dba_action TO your_limited_dba_user;

For your system using the database, this is the standard for non-object-owners (i.e. they can do whatever is being granted to them, but not alter/drop the object).

Finally your DBA is simply a standard superuser.

So yes, what you require being able to do is perfectly possible.


View T-SQL in CLR Assembly I have a production database with numerous scalar functions. My goal is to create set based alternatives for reporting. Unfortunately, I don't have a good way to view the T-SQL actually being executed because the functions reference a CLR assembly. Is there a good way to view the T-SQL inside the assembly for each function?

T-SQL being executed is different than T-SQL that will be executed if the function is run.

To see what is currently executing, you would use either SQL Server Profiler, or Extended Events. Then you can execute a function and see what T-SQL it runs.
To see what the function might execute, that is a bit trickier. You do have the code in the sys.assembly_files system catalog view. The content field contains the binary representation of the assembly. But, to see the code you need to find a way to extract it  to a binary file (no built-in method, and no, not a text file containing that "binary" string) and then decompile it.

String literals should be contained as-is in the assembly, so theoretically you should be able to convert the VARBINARY value in sys.assembly_files.content to NVARCHAR, but there are two problems there:

Queries can be constructed from several literals and/or variables, so it's hard to say if viewing this would be conclusive, and
There are many 0x00 bytes, and those will stop the displaying of what comes after it because it is the "null" terminator for strings, so you need to find a way to convert those to spaces (while also not converting a trailing 0 of one byte followed by a leading 0 of the next byte).

So, perhaps just use SQL Server Profiler or Extended Events to see what T-SQL is submitted when the functions are executed.


How to determine the optimal sort_buffer_size? I read from a sample configuration file which says the following:

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.

I have a couple of queries which use filesort. How do I determine what is the size of the buffer I need for queries to run smoothly without hitting the disk?

There is only one status variable that cares about sort_buffer_size. That's what you have in the message back in the question : Sort_merge_passes. The MySQL Documentation says:

  Sort_merge_passes : The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

Please keep in mind one thing about sort_buffer_size

  If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing

While raising sort_buffer_size can help queries with GROUP BYs and ORDER BYs, you are better off improving the queries that you can improve and adding indexes that can be used by the Query Optimizer.

Question remains: How do you check the Sort_merge_passes ???

Use this code, to check how many Sort_merge_passes happened in the last 5 minutes. It also computes the Sort_merge_passes per hour.

SET @SleepTime = 300;
SELECT variable_value INTO @SMP1
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SELECT SLEEP(@SleepTime) INTO @x;
SELECT variable_value INTO @SMP2
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SET @SMP = @SMP2 - @SMP1;
SET @SMP_RATE = @SMP * 3600 / @SleepTime;
SELECT @SMP,@SMP_RATE;

If you find the Sort_merge_passes and the rate too high, then feel free to increase sort_buffer_size. Suppose you want to raise to 4M. You would run this:

mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4;

You would then add this to my.cnf

[mysqld]
sort_buffer_size = 4M

You would run the code periodically to check for other times Sort_merge_passes spikes.


BLOB on DB2 v9 on z/OS What are cons and pros of using BLOBs on DB2 v9 (or later) on z/OS?

Clarification: are there some limits (like influence on reorg, HA)?

We would like to store large amount of files: 10 millions in the period of up to 6 months. Most of the files should be between 20k and 100k, but there could be exceptions (files greater than 1MB). Most of the files should be XMLs, but some of them could contain digital signatures or other binary data.

There are a couple of things to keep in mind when using Large Objects (LOBs) in DB2.

First, when you create a LOB column, this column only holds information about the related LOB, not the data of the LOB.  The table where you create the LOB column (the base table), must have a ROWID column created on it, which will be used to locate the LOB data.  If you do not explicitly create a ROWID column, DB2 will create a hidden ROWID column for you.  If you are adding columns to an existing table and you want to use an explicit ROWID column, then you should add the ROWID before the LOB.  Otherwise, when you create the LOB, DB2 will create the implicit ROWID, and then your second ALTER to add the ROWID will create a second ROWID column.  DB2 will ensure these two columns are always the same, but it will take up space.  :-)

Secondly, for every LOB column you create on a table, a separate LOB tablespace (sometimes called an auxiliary table space) must be defined.  So, if you plan on having 3 LOB columns on a table, you will have to create 3 LOB tablespaces to store your large objects. These tablespaces must be in the same database as the associated base table.

Also, if your base table is partitioned, then you will have to create one LOB tablespace and one auxilliary table per LOB column per partition.

A single LOB tablespace with a single partition can contain up to 16TB of LOB data.

Third, each auxilliary table must have an index defined on it.

Here's an example of creating an auxiliary table (anything that begins with YOUR_ should be replaced with the correct values):

CREATE LOB TABLESPACE YOUR_LOB_TABLESPACE
  IN YOUR_DATABASE
  LOG NO;
COMMIT;
CREATE AUXILIARY TABLE YOUR_AUX_TABLE
  IN YOUR_DATABASE.YOUR_LOB_TABLESPACE
  STORES YOUR_BASE_TABLE
  COLUMN YOUR_LOB;
CREATE UNIQUE INDEX XYOUR_LOB
  ON YOUR_AUX_TABLE;
COMMIT;

Finally, when you're loading data, if the total length of the LOB column and the base table row is less than 32KB, you can use the LOAD utility to put the data in.  Otherwise, you have to use SQL statements such as INSERT or MERGE.

IBM has some recommendations for page size in LOB table spaces.  Only one LOB can be stored per page in a LOB tablespace, so you will want to do some math to minimize lost disk space based on the average size of the files you're storing.  For example, if you have a 17KB average size LOB, you'd want to use 4KB page spaces.  You'd use five pages (for 20KB), which would only waste 3KB of disk space compared to 7KB with 8KB pages (24 - 17), or 15KB with 16 or 32 KB page sizes.

One pro of using a LOB versus the equivalent VARCHAR (FOR BIT DATA) is that, since the data is stored in a separate tablespace, tablespace scans against the base table will be much faster.

References:  1, 2

Having said all of that, if you're planning on most of your data being XML, then I might mention that there is an actual XML Data Type (and built in XML parsing engine called pureXML).

There is a whole guide you can view online (and in PDF) about using XML with DB2.


Which database for survey app I need to build a very simple mobile survey app. The app will ask for different questions where people can give one of 3 possible answers, which will be always "yes", "no" or "maybe".

To save these questions and answers I need a simple scheme with 2 tables like:

question(id:int, question:varchar, createdAt:timestamp)
answer(id:int, questionId:int, answer:enum, createdAt:timestamp)

I'm asking myself if I should go for a good ol' MySql for that or if I should choose a NoSQL database like MongoDB.

I have following minimal requirements:

Concurrent (non locking), fast inserts should be possible
Fast read queries to do some analytics
Indexing of table data
Several millions of entries should be no problem (hard disk space is the limit :)
Backups should easily be possible
Convenient date/time functions

I think MySQL should do the trick, but I've never tried out databases like MongoDB before. The app will leverage JSON as data exchange format and I know that MongoDB plays quite well with that.

Any advice is much appreciated!

Best regards

PostgreSQL is often overlooked but might be worth your attention.  It supports MVCC for concurrency, can do analytic SQL queries easily, supports indexes, scales up well enough, can do hot backups, supports date functions, and even has JSON data types.  Binary JSON (aka: JSONB, BSON) is being added to newer versions, which will give you the ability to index on JSON content and have improved analytic function & performance.

Check this out article on Phoronix:

Binary JSON Support Added To PostgreSQL, Competes With MongoDB
http://www.phoronix.com/scan.php?page=news_item&px=MTY0MTU


Missing or lost transaction and no errors to show what went wrong I have 2 missing records in SQL Server and I am trying to identify the problem that caused this issue.

New records are inserted using a stored procedure, which will return an error code to the application if something goes wrong.

The Application, SQL Server, System Logs do not show any errors during the time when these 2 transactions should have been inserted.

How do I go about identifying the issue here? My current idea is to find a way to read the Transaction Log (from a backup restore) to see if the transactions are even in the log and if they got rolled back.

Can anyone recommend anything else I can do to troubleshoot this? Are there DMVs or other (SQL Server hidden) logs?

I am worrying about this issue and what SQL Server did in this situation. (I have experienced issues recently with timeouts, but since a recent reboot, this problem was resolved....)

You can use ApexSQL Log to read the transaction log (online, backup, detached). Even in the trial, it shows in GUI all transactions it finds in logs. That can help you troubleshoot the issue.


Error when using OPENROWSET from a View as a nonsysadmin [SQL Server 2012] I am running into an issue regarding the usage of a non-sysadmin account calling a view which contains a OPENROWSET call that pulls from a stored procedure. I was wondering whether anyone has a possible fix to what I am currently trying or a suggestion that can get me out of the current workaround of using OPENROWSET in a view (possibly a linked server setup?).
I currently make a call to a procedure that looks like this:
SELECT *
FROM
OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=NO;UID=accountName;PWD=accountPassword;',
           'SET FMTONLY OFF;
            SET NOCOUNT ON;
            EXEC [My Stored Procedure] @params = ''field1,field2''
            WITH RESULT SETS
            (
                (
                    field1 NVARCHAR(1000),
                    field2 NVARCHAR(1000)
                )
            )
        ')

and the results are returned into a view. This all works just fine and executes when running this as a sysadmin. The problem is we now wish to give a non-sysadmin user access to this view, however, after granting SELECT permissions to the user for that view and trying to access the view as that user, this error comes up:
'Access to the remote server is denied because no login-mapping exists.'

I may have narrowed this down to the fact that the view itself calls OPENROWSET. OPENROWSET is used because I call on a stored procedure that pulls data from an API and produces a rowset. The stored procedure also calls extended stored procedures so this is not something easily converted to a function.
My only current way of getting this to work, in the newly created account, is by granting it sysadmin permissions. This is obviously not the desired route to take for security reasons but it helps confirm that this should work.
There are a vast number of questions regarding SQL Server + OPENROWSET but based on a few good hours of research I cannot seem to find a solution specific to this case. Any help would be very appreciated.
I arrived at my current setup through the referencing of answers like these:

https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table
https://stackoverflow.com/questions/2896445/why-can-we-not-execute-a-stored-procedure-inside-a-function-in-sql-server
https://stackoverflow.com/questions/916784/how-to-call-stored-procedure-in-a-view?noredirect=1&lq=1

and many more.
End note, I have already considered having the stored procedure(s) update/create local tables, possibly running on a nightly schedule to make sure the data is up to date, and creating views based off of the values in those updated tables. However, it reduces much of the duplicate code and re-configuring of stored procedures if I create the view to call the stored procedure on-the-fly with the parameters I wish to send to the procedure.

To respond to my own question, was able to get it working using a loopback linked server and using OPENQUERY instead of OPENROWSET. The non-sysadmin account now has the controlled access it needs.
Here is the new query the view uses:
SELECT *
FROM
OPENQUERY([LOOPBACKSERVERNAME],
           'SET FMTONLY OFF;
            SET NOCOUNT ON;
            EXEC [My Stored Procedure] @params = ''field1,field2''
            WITH RESULT SETS
            (
                (
                    field1 NVARCHAR(1000),
                    field2 NVARCHAR(1000)
                )
            )
        ')


Log rotation of diag logs? In on of our Oracle 11.2.0 installations the directory database\diag\rdbms\$DB\$DB\alert is filled with hundreds of xml files (log.xml, log1.xml ...). Can Oracle be configured to do log rotation there? And where are these files used?

Oracle won't do this natively. You'll have to use an external utility to do this.

On Unix systems this is usually achieved with logrotate.


FDW Permissions error using MD5 encrypted password When creating Foreign Data Wrappers in Postgres 9.6, if I use the following:

CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'database', port '5432');

-- permissions
ALTER SERVER foreign_server OWNER TO data_owner;

-- Allow a user to access the foreign server if required
GRANT USAGE ON FOREIGN SERVER foreign_server to data_owner;

-- 4. Create foreign user mapping - using md5 encrypted password
CREATE USER MAPPING FOR data_owner SERVER foreign_server
OPTIONS ( USER 'jeff', PASSWORD 'md5de0366066f8d96ac5bb4872b1d77b0cb!');

-- 5. Create foreign table
IMPORT FOREIGN SCHEMA foo EXCEPT
(bar )
FROM SERVER foreign_server INTO foreign_tables_schema;

Why do I get an error, when I had the password as plain text everything is fine, but when I use the encrypted md5 password I cannot import the foreign tables.

Any thoughts?

Can you show us where you got the idea that this should work?

There is no provision for supplying passwords as md5 hashes to postgres_fdw.  Maybe there ought to be, but someone will have to implement it for it to exist.


SQL Error (1093): Table 'j1' is specified twice, both as a target for 'UPDATE' and as a separate source for data I get the above error while I execute the below query

UPDATE jobs AS j1 SET j1.job_last_status = 10, j1.updated_at = '2020-05-04 09:59:11'
WHERE j1.id IN (SELECT jh.job_id
FROM jobs_history AS jh
INNER JOIN jobs j ON j.id = jh.job_id AND j.job_last_status =7
WHERE jh.job_status = 7
GROUP BY jh.job_id
HAVING COUNT(jh.job_id) =2)

UPDATE jobs j1, ( SELECT jh.job_id
                  FROM jobs_history AS jh
                  INNER JOIN jobs j ON j.id = jh.job_id
                                   AND j.job_last_status =7
                  WHERE jh.job_status = 7
                  GROUP BY jh.job_id
                  HAVING COUNT(jh.job_id) = 2 ) j2
SET j1.job_last_status = 10,
    j1.updated_at = '2020-05-04 09:59:11'
WHERE j1.id = j2.job_id

or the same in JOIN form:

UPDATE jobs j1
  JOIN ( SELECT jh.job_id
         FROM jobs_history AS jh
         INNER JOIN jobs j ON j.id = jh.job_id
                          AND j.job_last_status =7
         WHERE jh.job_status = 7
         GROUP BY jh.job_id
         HAVING COUNT(jh.job_id) = 2 ) j2 ON j1.id = j2.job_id
SET j1.job_last_status = 10,
    j1.updated_at = '2020-05-04 09:59:11'


sqlplus startup - failure in processing system parameters I am trying to do the startup command

This is what I have done:

set oracle_sid=ORCL
sqlplus /nolog
SQL> conn /as sysdba
startup

It shows this error message:

  failure in processing system parameters
  could not open parameter file "C:\Program\11.2.0\DBhome_1\Database\initorcl.ora"

I checked and found out that "initoracl.ora" doesn't exist inside this path:

C:\Program\11.2.0\DBhome_1\Database

I found a init.ora file in:

C:\Program\11.2.0\dbhome_1\dbs

I tried doing this command:

startup pfile="C:\Program\11.2.0\dbhome_1\dbs\init.ora"

It shows this error:

  invalid value given for the diagnostic_dest init.ora parameter
  the specified ADR Base Directory does not existed [C:\program\11.2.0\dbhome_1\database\]
  specific directory does not exist
  additional error information
   the filename, directory name, or volume syntax is incorrect.

What should I do?

Set the ORACLE_SID  and ORACLE_HOME  variables properly.  They must be in upper case letters.

Second, share the content of directory. ORACLE_HOME/dbs


Limiting rows from a joined table I have 2 tables Part and PartDtl.  PartDtl contains one row for each order that has been placed for that part.

PartNum | Description | ...
1234    | 1234 desc   | ...
2345    | 2345 desc   | ...

PartNum | Qty | DueDate     | ...
1234    | 12  | 2015/11/15  | ...
1234    | 66  | 2015/11/17  | ...
1234    | 45  | 2015/11/18  | ...
1234    | 33  | 2015/11/16  | ...
2345    | 77  | 2015/11/12  | ...
2345    | 43  | 2016/01/01  | ...

I use the follwing sql to get the data:

select
  p.PartNum,
  pd.Quantity,
  pd.DueDate
from
  Erp.Part as p,
  Erp.PartDtl pd
where
  p.Company = pd.Company and
  p.PartNum = pd.PartNum and
  pd.StockTrans = 'TRUE'
order by 1, 3, 2

The problem is that some parts have lots of orders placed and I only need to see the first few.  For example:

PartNum | Qty | DueDate
1234    | 12  | 2015/11/15
1234    | 33  | 2015/11/16
1234    | 66  | 2015/11/17
2345    | 77  | 2015/11/12
2345    | 43  | 2016/01/01

I haven't been able to figure out how to have the first 3 orders due of each part.

I have tried a sub-query, but haven't figured out the syntax.

Can you show me the way, please.

nb - there are other fields which are shown in the sql which I don't think are relevent to this query.  I'm using a recent MS SQL-Server database.

This should work:

SELECT      *
FROM        (SELECT         ROW_NUMBER() OVER (PARTITION BY p.Partnum
                                               ORDER BY     p.Partnum
                                                            , pd.DueDate
                                                            , pd.Quantity) AS rownumber
                            , p.Partnum
                            , pd.Quantity
                            , pd.DueDate
                FROM        Part       AS p
                INNER JOIN  PartDtl    AS pd
                    ON      p.Company  = pd.Company
                        AND p.PartNum  = pd.PartNum
            ) AS sub
WHERE       sub.rownumber <= 3
ORDER BY    sub.Partnum
            , sub.DueDate
            , sub.Quantity

You were using old SQL syntax, I converted the old join type to an INNER JOIN.
Also changed the numbers in the ORDER BY clause, as this might cause problems when you change the order of your selected columns.


phpmyadmin - Login issue, takes me back to the login page after logging in I'd setup phpmyadmin quite a while ago and it worked well and I got everything setup and working etc... However, I went to login recently and it always just takes me back to the login page. The credentials are correct, but never the less I always end up at the login screen.

I can't find anything in my error logs, and in the access logs everything is status code 200, except the POST which is 302 followed by 304s. None of that really stands out.

I'm using Nginx+PHP5-FPM+MySQL if that changes anything.

What types of things might I have changed that would cause this sort of activity?

Turns out the issue was memcache. I'd added it for other reasons and set PHP to use it for sessions. Nothing broke (or so I thought) and all seemed well. Turns out it's this very reason phpMyAdmin was not working. phpMyAdmin does not support memcache being used for sessions in PHP, it requires files.


Use random string variable in a insert This is one of my first tries to code something. I'm trying to develop a code, to add first name, last name, and date, randomly.

I have this for now:

declare @date date,
        @val1 int,
        @val2 int
set @date = convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate()))

    insert into tblexecution ( fname,lname,brtday )
    values ('name1','Name2',@date)

This way, I need to change manually the name1 and name2.

is there a way to use, values of another table, in a variable, for this table? I don't know how to explain it but I have a table called Adventureworks2008.person.person (you guys know this database) with a lot of random names ( Fields > FirstName,LastName ), and I want to use this values, in my @val1 and @val2 variables, to be something like this:

declare @date date,
        @val1 int,
        @val2 int
set @date = convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1,
 getdate()))

    insert into tblexecution ( fname,lname,brtday )
    values (@val1,@val2,@date)

so I just loop ( or press f5 a lot ).

Edit1:

With this, I have at least a random int:

select right(rand(),2)

Edit2:

I could do this, But I think this is a poor job:

insert into  execution..tblexecution
select distinct  firstname, lastname,
    convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1,getdate())) as brtday
    from adventureworks2008.person.person

Edit3:

I could make this:

declare @date date,
        @val1 nvarchar(200),
        @val2 nvarchar(200)
set @date = convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate()));
set @val1=(select  top 1 firstname
               from adventureworks2008.person.person
              order by newid() );
set @val2=(select top 1 lastname
               from adventureworks2008.person.person
              order by newid() )

    insert into tblexecution ( fname,lname,brtday )
    values (@val1,@val2,@date)

is this the best way to use random variables in a query?

insert into dbo.tblexecution (
     fname
    ,lname
    ,brtday
)
select top 100
     [FirstName]
    ,[LastName]
    ,convert(date, dateadd(day, (abs(CHECKSUM(newid())) % 3650) * - 1, getdate()))
from [AdventureWorks2008R2].[Person].[Person]
order by newid()


Very slow performance from MySQL I have the following procedure:

drop procedure  if exists deletebatches;

create procedure deletebatches()

BEGIN
declare acount int default 0;
declare done INT default 0;
declare abatchno varchar(10);
declare abatchpart varchar(20);
declare cursor1 cursor   for select batchpart, batchno from batchestodelete;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
SET DONE = 0;
open cursor1;
readloop: loop
fetch cursor1 into abatchpart, abatchno;
if done = 1 then leave readloop;
end if;

delete from stockbatches where batch_batch = abatchno
and batch_part = abatchpart;

select curtime();
end loop readloop;

close cursor1;

end;

call deletebatches()

This query runs for so long that I have not had the patience to let it finish.  I cancel it after an hour or so.

I also created a query to delete from stockbatches where batch_batch + batch_part in (select batch_batch + batch_part from batchestodelete) and it took 1,278 seconds (over 21 minutes!).

There are only 776 records in the batchestodelete table and about 1 million in the stockbatches table which has indexes on batch_part, batch_batch and both fields combined.

Can anybody offer an explanation as to why this is taking so long for what is a relatively simple query?

Here are some pieces of information various people have asked from me.

Show full process list shows:

2614 root localhost:4785 Sleep 4786
2621 root localhost:4792 new_web_files Sleep 4789
2967 root localhost:1283 new_web_files Query 3 updating
     delete from stockbatches where batch_batch = abatchno
     and batch_part = abatchpart 3014 root localhost:1336 new_web_files
     Query 0 show full processlist

Show create table stockbatches shows:

STOCKBATCHES CREATE TABLE stockbatches (
    batch_part varchar(20) collate latin1_general_ci NOT NULL,
    batch_batch varchar(10) collate latin1_general_ci NOT NULL,
    batch_qty decimal(18,2) default NULL,
    batch_cond varchar(2) collate latin1_general_ci default NULL,
    batch_release varchar(3) collate latin1_general_ci default NULL,
    batch_curedate varchar(10) collate latin1_general_ci default NULL,
    batch_lastmod datetime default NULL,
    batch_lastaction varchar(1) collate latin1_general_ci default NULL,
    PRIMARY KEY (batch_part,batch_batch),
    KEY partno (batch_part),
    KEY batchno (batch_batch),
    KEY batchandpart (batch_part,batch_batch)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Show create table batchestodelete shows:

batchestodelete CREATE TABLE batchestodelete (
    batchpart varchar(20) NOT NULL,
    batchno varchar(10) character set latin1 collate latin1_general_ci default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Why are you using a cursor for this type of operation?

Most RDBMS are optimised for set-based operation, a cursor or a loop are iterative operations which perform row-by-row operations.

Whenever possible, you want to use declarative set-based code.

Try this:

DELETE sb
FROM stockbatches sb
INNER JOIN batchestodelete btd
ON sb.batch_batch = btd.batchno
AND sb.batch_part = btd.batchpart


Can I have full backups on multiple disks? Currently, I've only got one backup file, IMTDB.bak, and it's on the same HDD as the Database itself. I want to increase the redundancy of this DB backup by essentially "copying" it to another disk, but I get an error "Backup failed for Server, The media is formatted to support 2 media families". I think this means that when I created the backup, it's only meant to put the backup on one drive, and I can't retroactively add more. I want to migrate the backup to this other drive (really just copy it over), but I don't want to have to delete the current backup to do it.

What do I do? Am I safe in just copying IMTDB.bak to a folder on the other drive?

Since you are not familiar with the T-SQL BACKUP DATABASE command, I thought I'd add some details about that.

You probably want to run something along the lines of the following statement through the Windows Task Scheduler service, since you don't have access to SQL Server Agent (I see from your other questions, you use SQL Server Express).

BACKUP DATABASE [xyz]
TO DISK = 'C:\somepath\mybackupfile.bak'
MIRROR TO DISK = 'D:\somepath\myotherbackupfile.bak'
WITH FORMAT
    , INIT
    , SKIP
    , STATS = 1;

You might want to strongly consider having the MIRROR TO clause point to some location that is not on your local machine, since if you lose your local machine completely, you may not be able to access either backup file.  Specifying a MIRROR TO clause requires you to specify the FORMAT keyword in the WITH clause the first time you run that backup statement.

You can use the name of a Windows Share, such as \\SomeServer\SQLBackups\MyBackupFile.bak as long as the security permissions on the share allow the Windows Scheduler service access.

The WITH FORMAT, INIT part tells SQL Server to overwrite any existing backups that may be in the backup files. You could change this to WITH NOINIT once you've completed the first mirrored backup if you want multiple backups (i.e. backups from different points in time) saved in those files.  NOSKIP tells SQL Server to not check for backup expiration, among other things.   STATS = 1 will display the output in 1 percent increments.  You can change this number to anything you like.  I use 1 for very large databases since it gives some indication of progress.

To have this run through the Windows Task Scheduler service, you'll need to save that command (once you have tested it in SQL Server Management Studio) to a file on your disk; let's call it C:\somefolder\BackupMyDB.sql.  You'll then want to add the following command to the Windows Scheduler:

<path to sqlcmd>sqlcmd -S localhost -E -i C:\somefolder\BackupMyDB.sql

You'll want to have that task "run as" you.

Once you've done all that, you want to very seriously consider attempting to restore the backup onto another machine so you understand how to do that.  Having backups is only one part of a disaster recovery plan; the arguably more important part is testing that plan.

The restore process would use a command something like:

RESTORE DATABASE [xyz]
FROM DISK = 'D:\somepath\myotherbackupfile.bak'
WITH RECOVERY
    , STATS = 1;

Be warned, running RESTORE DATABASE on the machine where your current database resides can overwrite the current database without confirmation or warning, so please ensure you carefully evaluate the name of the database, [xyz] in my example, and the other options you use.  (This actually won't overwrite the existing database unless you add the REPLACE keyword to the WITH clause - I just want to emphasize being careful.)


ORDER BY items must appear in the select list if SELECT DISTINCT is specified SELECT DISTINCT *
    FROM (
        SELECT a.Title, a.ID FROM TableA a
        LEFT JOIN TableB b
        ON a.ID = b.XID) c
    ORDER BY
        CASE WHEN c.ID > 10 THEN c.Title
        ELSE c.ID
        END
    ASC

And if I move DISTINCT to nested select, then both ID and Title should be in same type, e.g: INT, but they are not.

Both ID and Title are in the selected columns, what is the problem?

Also with this change CASE WHEN 1 <> 1 query runs without problem.

The real issue is caused by data type conversion. If you add this expression to the select list:

CASE WHEN c.ID > 10 THEN c.Title
        ELSE c.ID
        END

you will see another error. Something like: "Conversion failed when converting the nvarchar value 'Some title' to data type int". According to data type precedence when you compare varchar with int values (SQL Server will need to compare Title with ID to sort your final result set) varchar should be converted to int because int has higher precedence.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15


How to resolve TNS error in Oracle 11g when connect with Navicate) I want to connect Oracle11g with Navicate, but when I make a connection he display a error

  Ora-12514: TNS: Listener does not currently know of service requested in connect descriptor.

Today i solve my problem connection error oracle 11g with navicate

i just change the port number and my TESTDB is connected.

so if any body have same issue kindly check your DB port.


Having four different tables or having one table with one more field? I have a website with 4 translation (That is Arabic with 4 different Persian translation), and I had exported each of these translations to different tables.
Which one of these ways are faster and better:

1- Merging or Importing all of this translation (tables) to one table,
and name it nahj_all and adding a flag field to table to clarify
what translation is it (for example flag with numbers 1, 2, 3 and
4).

OR

2- Importing each translation to different tables and get diffrent
name to them according to translation name (or number), for example
nahj_feiz, nahj_jafari, nahj_dashti, nahj_shahidi

I have these tables in MySql and I want to convert database to sqlite
Which one is better for sqllite?
Which way is better for MySql?

Having all languages in one table is an overhead. Each time you will have to load the data of all languages and use part of (for one language), or you will add a condition to your query to load one language's data, which is also an overhead. In my opinion, having each language is a separate table is lighter. However, if the load on your website is low, and your translation data is not big, the difference will be negligible.


Stored procudure for a search feature I am trying to write a stored procedure to search by DB for whatever is inserted. see below:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_login_tb`
(
 IN p_pass varchar(100),
 IN p_email_id varchar(100),
 IN p_name varchar(45),
 IN p_staff_id varchar(45),
 IN p_status_id varchar(1)
)
BEGIN

    set @sqlquery = 'select * from kupo_101.login_tb where login_tb.id is NOT NULL';

    IF (p_pass is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.pass = "',p_pass,'"');
    ELSEIF (p_email_id is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.email_id = "',p_email_id,'"');
    ELSEIF (p_name is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.first_name = "',p_name,'"');
    ELSEIF (p_staff_id is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.staff_id = "',p_staff_id,'"');
    ELSEIF (p_status_id is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.status = "',p_status_id,'"');
    END IF;

    prepare stmt from @sqlquery;
    execute stmt;

END

MariaDB [kupo_101]> CALL get_all_login_tb('qewr13241wwe','','','','');
+----+--------------+------------------+----------------------+----------+--------+
| id | pass         | email_id         | first_name           | staff_id | status |
+----+--------------+------------------+----------------------+----------+--------+
|  2 | qewr13241wwe | iimama@gmail.com | Imama Igein Blessing | 10122ASD | Y      |
|  3 | qewr13241wwe | barvo@gmail.com  | ucee oluchi          | 10122AFF | N      |
+----+--------------+------------------+----------------------+----------+--------+
2 rows in set (0.00 sec)

But when use other filed to search it shows empty Set.

CALL get_all_login_tb('','','','10122ASD','');
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

It's because you're calling the stored procedure with empty string values rather than NULL values. The IFs and ELSEIFs in the SP body are testing for NULL, not empty string.


Combine datetime + string to make a proper datetime I need to take a DATETIME column (Column A) that contains only a date, then add a VARCHAR(5) column (Column B) which contains a 24-hour representation of a time (i.e. 1330) and combine them to get a valid DATETIME to be used in a DATEDIFF command.

So for example, Column A contains 2013-07-01 00:00:00.000 and Column B contains 1330. The result should be 2013-07-01 13:30:00.000.

Edit: just to claify things, this is an existing DB that stores the data this way, it is out of scope of my project to completely change how things work to make it store the data in a proper way. If I could I would but I can't so I am stuck with the multiple columns. Also the data in Column B will always be a 4 digit time as the input page has checks to make sure time is a valid military time format.

This will also deal with values < 10 AM that don't have all 4 digits. (Also that column should probably be CHAR(4) instead of VARCHAR(5) - or better yet, stop separating these and store a proper datetime value in the first place).

DECLARE @t TABLE(ColumnA DATETIME, ColumnB VARCHAR(5));

INSERT @t VALUES('20130701', '1330'),('20130701', '930');

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), ColumnA, 112)
    + ' ' + LEFT(RIGHT('0' + ColumnB, 4),2)
    + ':' + RIGHT(ColumnB, 2))
FROM @t;


powerful VPS but slow MS SQL- why? VPS (Contabo) : 10 cores (Xeon E5 2.2GHz), 60 GB RAM, SSD, MS SQL Web Edition (configured to use 10 CPUs), Windows Server 2016
When running SQL takes ~32GB of RAM,

When I execute SQL a stored procedure (CPU~19%, Disk 1MB read, 05.MB write) it is slower than on my laptop (i7,16 GB ram,SSD and SQL Express....).

What is wrong? How to improve performances on my VPS?

SQL a stored procedure (CPU~19%, Disk 1MB read, 05.MB write) it is slower than on my laptop (i7,16 GB ram,SSD and SQL Express....).

Probably because your laptop is faster than your server.

Many i7s are significantly faster than a 2.2Ghz Xeon for single-threaded operations.  And not all "SSD" drives are the same.  The flash storage devices on modern laptops can be much faster than what might be used by your hoster.

The Xeon has more cores, and so can perform more work overall.  So a realistic load test should show that the server can support more concurrent users.  But for a single user, you shouldn't expect the server to be faster

  How to improve performances on my VPS?

So that's normal SQL Server performance analysis and optimization.  Good news is that you can do the testing and optimization work on your laptop :)


Replication from EC2 Master to RDS Slave - Connecting to master I am migrating my company's database from an EC2 instance to an RDS instance. I have already migrated a snapshot of data and am now trying to set up replication to get the data that has been added since the snapshot.

I am following the instructions at this link:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

When I call mysql.rds_start_replication , SHOW SLAVE STATUS\G shows the following:

               Slave_IO_State: Connecting to master
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

From my Googling on the subject matter, I'm led to believe this is a network/firewall problem. I've checked that my RDS has a security group allowing all network traffic with the source machine (both private IP and Elastic IP) and the source machine has a security group allowing all network traffic with the RDS instance IP address.

The master server has a user set up with replication privileges.

I'm sure there must be something easy I'm missing here.

I've tried running flush hosts on the master server.

How do I get the replication to proceed successfully?

Some additional information:

The master server is version 5.5.33
The slave server is version 5.6.13
The master server is using old_passwords=1
The slave server is using old_passwords=0

I've tried setting SESSION old_passwords=0 on the master, creating a new replication user with a new, long hash format, but still can't get replication to start.

The error I get is:

        Last_IO_Errno: 2049

which seems to relate to the authentication protocol.

Do I need to completely restart the MySQL master server, with old_passwords=0 in the my.cnf file?

Since you control my.cnf on EC2, you have two options, neither of which require restarting mysql
OPTION #1 : Change old_password Protocol for EC2

Stop replication to the RDS Slave
As root@localhost run this on EC2 : SET GLOBAL old_passwords = 0;
Start replication to the RDS Slave

OPTION #2 : Lower the Security for EC2

Stop replication to the RDS Slave
As root@localhost run this : SET GLOBAL secure_auth = OFF;
Start replication to the RDS Slave

For more information, please read the old_passwords and secure_auth MySQL Documentation.
Give it a Try !!!
UPDATE 2017-07-20 16:32 EDT
Neither of these methods work for MySQL 5.7 as both options are deprecated.


MySQL thousands connections after convert MyISAM to InnoDB on fail-over structure I've acquired a fail-over structure and I migrated my databases to new structure converting all tables of my database from MyISAM to InnoDB engine, but, after import all databases, I have a problem with concurrent connections, with one access to my service, the MySQL are opening thousands connections and growing, the connection number only decreases when I restart the MySQL service, I think that isn't my server hardware because it has 32 cpu cores and 64gb RAM, I don't understand what is happening but I need to solve this quickly.
All databases and tables are using InnoDB engine, and my application are closing all connections that it opens.

Anyone had the same problem when migrate databases like this?

If you are using transactions, please ensure that you use either a COMMIT or ROLLBACK statement before closing the connection.

If this is already the case, you probably need to increase the thread_cache_size value in order to reuse as much as possible the existing connection threads instead of creating new ones from start.

Please note the following values:

mysql> SHOW GLOBAL VARIABLES LIKE 'Connections';
mysql> SHOW GLOBAL VARIABLES LIKE 'Thread_created';
mysql> SHOW GLOBAL VARIABLES LIKE 'Max_used_connections';

If Thread_created/Connections> 0.01, you have to increase the value of thread_cache_size.
The upper limit should be the value of Max_used_connections.


What run-time configuration parameters are used for concurrent index creation Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value.

mhildreth=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 16MB
(1 row)

mhildreth=# set maintenance_work_mem = '256MB';
SET

mhildreth=# CREATE INDEX CONCURRENTLY ON my_table(columns);

mhildreth=# set maintenance_work_mem = DEFAULT;
SET

What would be used for maintenance_work_mem of the index creation?

In your example, the maintenance_work_mem you have set of 256MB should be seen by the subsequent CREATE INDEX CONCURRENTLY command, because you have changed this GUC inside your session.

In fact, the docs suggest bumping up maintenance_work_mem (as you showed) for just such a purpose.


MySQL slow_log table: querying takes forever I'm running a query on the slow_log:

SELECT start_time FROM mysql.slow_log WHERE start_time < TIMESTAMP '2016-01-31 00:00:00'

Without a WHERE clause (using a LIMIT), the selects are fast.

However the LIMIT gives me the oldest logs, ORDER BY has the same problem for me.

Is this a MySQL thing, or does it have to do with the fact I'm running a Google Cloud SQL database?

Answer originally left as a comment by Michael - sqlbot:

Mostly it's a MySQL thing.  The slow query log "table" -- by default -- is actually just a CSV file read by the "CSV storage engine," which emulates a table but uses a simple CSV file as its tablespace.

This allows appending to the slow query "table" to be a low overhead operation, but of course a CSV file has no indexes, so it has to be scanned from the beginning.

I haven't used Google Cloud SQL so I can't offer an authoritative answer on what options you might have, there, such as changing it to a MyISAM table and indexing it.

See Selecting General Query and Slow Query Log Output Destinations in the product documentation.


T SQL Table Valued Function to Split a Column on commas I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.

Ex: "one,two,three,four" would return a new table with only one column containing the following values:

one
two
three
four

Does this code look error prone to you guys? When I test it with

SELECT * FROM utvf_Split('one,two,three,four',',')

it just runs forever and never returns anything. This is getting really disheartening especially since there are no built in split functions on MSSQL server (WHY WHY WHY?!) and all the similar functions I've found on the web are absolute trash or simply irrelevant to what I'm trying to do.

Here is the function:

USE *myDBname*
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR)

RETURNS @SplitValues TABLE
(
    Asset_ID VARCHAR(MAX) NOT NULL
)

AS
BEGIN
            DECLARE @FoundIndex INT
            DECLARE @ReturnValue VARCHAR(MAX)

            SET @FoundIndex = CHARINDEX(@delimiter, @String)

            WHILE (@FoundIndex <> 0)
            BEGIN
                  DECLARE @NextFoundIndex INT
                  SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1)
                  SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@NextFoundIndex-@FoundIndex)
                  SET @FoundIndex = CHARINDEX(@delimiter, @String)
                  INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)
            END

            RETURN
END

I wouldn't do this with a loop; there are much better alternatives. By far the best, when you have to split, is CLR, and Adam Machanic's approach is the fastest I've tested.

Next best approach IMHO, if you can't implement CLR, is a numbers table:

SET NOCOUNT ON;

DECLARE @UpperLimit INT = 1000000;

WITH n AS
(
    SELECT
        x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM       sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    CROSS JOIN sys.all_objects AS s3
)
SELECT Number = x
  INTO dbo.Numbers
  FROM n
  WHERE x BETWEEN 1 AND @UpperLimit
  OPTION (MAXDOP 1); -- protecting from Paul White's observation

GO
CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number)
    --WITH (DATA_COMPRESSION = PAGE);
GO

... which allows this function:

CREATE FUNCTION dbo.SplitStrings_Numbers
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   (
       SELECT Item = SUBSTRING(@List, Number,
         CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
       FROM dbo.Numbers
       WHERE Number <= CONVERT(INT, LEN(@List))
         AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
   );
GO

I believe all of these will perform better than the function you have, when you get it working, especially since they are inline instead of multi-statement. I haven't investigated why yours isn't working, because I don't think it's worth it to get that function working.

But that all said...

Since you are using SQL Server 2008, is there a reason you need to split in the first place? I would rather use a TVP for this:

CREATE TYPE dbo.strings AS TABLE
(
  string NVARCHAR(4000)
);

Now you can accept this as a parameter to your stored procedures, and use the contents just like you would use a TVF:

CREATE PROCEDURE dbo.foo
  @strings dbo.strings READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT Asset_ID = string FROM @strings;
  -- SELECT Asset_ID FROM dbo.utvf_split(@other_param, ',');
END

And you can pass a TVP directly from C# etc. as a DataTable. This will almost certainly outperform any of the solutions above, especially if you are building a comma-separated string in your app specifically so that your stored procedure can call a TVP to split it apart again. For a lot more info on TVPs see Erland Sommarskog's great article.

More recently, I've written a series on splitting strings:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

And if you are using SQL Server 2016 or newer (or Azure SQL Database), there is a new STRING_SPLIT function, which I blogged about here:

Performance Surprises and Assumptions : STRING_SPLIT()
STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
STRING_SPLIT() in SQL Server 2016 : Follow-Up #2


executing a stored procedure on a monthly schedule I'm looking to create an invoicing procedure that executes automatically once a month.   The procedure name is INVOICE_PROC.

I have tried to use a trigger to execute it but I can't get it to work. Would a trigger even be the best way to do this?

Use Oracle's DBMS_SCHEDULER to schedule a job to run monthly.


SQLCMD to connect to Linked server I'm getting a login error with the below sqlcmd to connect to a linked server:

SQLCMD -E -dlinkedserver -ig:\temp\script.sql -o g:\temp\test.txt

My linked server which is an oracle connects through the app account from the linked server.

Is there anything wrong with the above code, it tried to login to the linked server, run a scripts, and log to a file.

If you want to use the linked server defined in a SQL Server instance, then you need to connect to the SQL Server instance that hosts the linked server, and make sure that your commands reference the linked server objects using 4-part naming. The SQLCMD program has no idea what a linked server is, so it can't connect to a linked server directly.

Let's say your linked server is named MyLinkedServer then your .sql file should look something like this:

INSERT SQLServerDatabase.dbo.TableName(columns) -- SQL Server table
  SELECT columns FROM MyLinkedServer..[Schema].[TableName]; -- Oracle table

Or:

INSERT SQLServerDatabase.dbo.TableName(columns) -- SQL Server table
  SELECT columns FROM OPENQUERY
  (
    MyLinkedServer,
    N'SELECT columns FROM [Schema].[TableName]' -- Oracle table
  );

And your SQLCMD would not say anything about the linked server name at all, because it gets information about the linked server from inside the script:

SQLCMD -E -S Server\Instance -i g:\...\file.sql -o g:\...\log.txt

Of course there are other ways to run queries against Oracle if you don't need to rely on (a) SQLCMD or (b) the linked server defined in the SQL Server instance.


Does using LIMIT improve the performance and is it noticeable? I want to understand the following.
Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by.
Letting aside any optimizations to the query itself e.g. indexes etc.
Is there any significant performance benefit using LIMIT? I assume that all the query (and results) must be processed before LIMIT is applied, so using a LIMIT to retrieve a subset of the results, does this offer any significant/noticable improvement?

If you want to take advantage of LIMIT to improve performance, you need

understand the data you are retrieving
proper indexing the correct sequence of columns
take responsibility for refactoring the query
using LIMIT before JOIN

These principles can go a long way if you can orchestrate them.
I learned these concepts by watching this YouTube Video (listen carefully through the French accent)
I used those concepts to answer a very tough StackOverflow question about getting the top 40 articles from some tables : May 12, 2011 : Fetching a Single Row from Join Table.
In my answer to that question (May 16, 2011), I wrote the following query and tested it thoroughly:
SELECT
  AAA.author_id,
  AAA.date_created,
  IFNULL(BBB.title,'<NO_TITLE>') title,
  IFNULL(CCC.filename,'<NO-IMAGE>') filename,
  IFNULL(CCC.date_added,'<NO-IMAGE-DATE>') image_date
FROM
(
  SELECT
    AA.id,
    AA.date_added,
    BB.author_id,
    BB.date_created
  FROM
  (
    SELECT
      A.id,IFNULL(MAX(B.date_added),'1900-01-01 00:00:00') date_added
      FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A
      LEFT JOIN article_images B ON A.id = B.article_id
      GROUP BY A.id
  ) AA
  INNER JOIN articles BB USING (id)
) AAA
LEFT JOIN article_contents BBB ON AAA.id=BBB.article_id
LEFT JOIN article_images CCC
ON (AAA.id=CCC.article_id AND AAA.date_added=CCC.date_added)
ORDER BY AAA.date_created DESC;

Please notice the line in the query with the LIMIT
      FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A

This subquery is buried three levels deep. This allowed me to get the last 40 articles using LIMIT. Then, I performed the necessary JOINs afterwards.
LESSONS LEARNED

Doing LIMIT inside subqueries may not always be the answer because of the cardinality of indexes, the data content, and the result set size from the LIMIT. If you have all your "ducks in a row" (Have the four principles in mind for your query), you can get surprisingly good results.
Make your queries as simplistic as possible when doing LIMIT by gathering keys only.


Automate DBs restore through TSQL by given folders I searched in the internet but cannot find clear and direct way. I have a folder which include 100 databases as full backup, another folder contains all logs for these 100 DBs.

I need a script to do the automate restore for these DBs?

The names for backup files are different, for example

Adventure_Work_06162015_220000.bak
Adventure_Work_06012015_150000.bak

and so on, appreciate your help and cooperation.

I would suggest to use Restore Gene written by Paul Brewer.

There is a T-SQL [sp_RestoreGene] and PowerShell version [ps_RestoreGene] of it and you can use it / customize it as per your needs.

SQLServerCentral.com has an excellent article by the author on how to configure and describes the working of it as well - Restore Gene : Automating SQL Server Database Restores.


which DBMS should i go for? I am interested in learning a DBMS. As a beginner which DBMS should i go for ?

I know few DBMS by name like Oracle,Sql Server,My Sql  .  How are these DBMS different from each other ?

As a beginner which DBMS should i go for ? I needed to learn DBMS as i am working with JavaEE and had to link the project with a database.

Since you want to be application developer who understands a DBMS.

In my opinion there exists, five foremost databases in minds of developers. These are

Oracle
DB2
SQL Server
My SQL
PostgreSQL

Why these five, first three because of market share. Last two because of usage share in open source applications.

  Also you stated that you are working with JavaEE.

Then I suggest you choose one of the following. In the following order.

PostgreSQL Open Source
DB2 (Universal) Express Edition
Oracle Express Edition
MySQL
SQL Server Express Edition

I put SQL Server last since you are working with Java. Other databases are more appropriate. Note that I am Certified in SQL Server (2000,2005,2008). Therefore this is not a bias in my opinion but I acknowledge that others are more strong with Java.

I put MySQL in last rows since I believe you should start with a DBMS which supports SQL Standard more, and first three are better (out of box) than MySQL in this regard.

There are not much difference for learning application development with first three. If you are to be primarily open source developer choose PostgreSQL. If you are to be primarily commercial developer, look your local career site. Which DBMS wanted more, choose it. For example Oracle is more popular in my local area.


DENY VIEW DEFINITION TO PUBLIC I have a SQL account with no permissions other than having the public role.  I have run the following command to deny permissions to the system views:

  use master
    DENY VIEW ANY DEFINITION TO public;

However when I query view like sys.databases I still get metadata returned.  My understanding was this should not be the case?

This is because VIEW [ANY] DEFINITION doesn't have anything to do with retrieving results that are exposed to public by default; this has to do with viewing the definition of objects, for example this should no longer be allowed:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.databases'));

Try:

DENY SELECT ON sys.databases TO public;

Or, more broadly:

DENY SELECT ON SCHEMA::sys TO public;

Note that this may cause other issues, for example I am not sure what will happen if the user tries to connect to SQL Server using Management Studio - Object Explorer probably will not load completely; IntelliSense and other features may stop working as well, since they rely on metadata access.


DELAYED User at MySQL mysql> show processlist;

| Id    | User    | Host                    | db           | Command        | Time | State              | Info             || 27098 | DELAYED | localhost               | placeholder  | Delayed insert |  111 | Waiting for INSERT |                  |

I'm finding this process as suspicious, but nothing in ps, netstat, cron.How to get more info about that process? Thanks.

Once someone executes INSERT DELAYED, a system thread will open in order to track and mitigate INSERTs of this kind. MySQL 5.6 deprecates it and 5.7 does not support it.
What is more, in terms of MySQL Replication, Slaves will completely ignore an INSERT DELAYED command from a Master and execute them as a standard INSERT.
Please read the MySQL 5.5 Documentation on this for further info.
UPDATE 2016-07-05 11:30 EDT
Since you are interested in process/client, which originates this "INSERT DELAYED" query, you should look at the thread states. The MySQL 5.5 Documentation describes them well:

States associated with a connection thread that processes an INSERT DELAYED statement from the client:

allocating local table : The thread is preparing to feed rows to the delayed-insert handler
thread.

Creating delayed handler : The thread is creating a handler for DELAYED inserts.

got handler lock : This occurs before the allocating local table state and after the waiting for handler lock state, when the connection thread gets access to the delayed-insert handler thread.

got old table : This occurs after the waiting for handler open state. The delayed-insert handler thread has signaled that it has ended its initialization phase, which includes opening the table for delayed inserts.

storing row into queue : The thread is adding a new row to the list of rows that the delayed-insert handler thread must insert.

waiting for delay_list : This occurs during the initialization phase when the thread is trying
to find the delayed-insert handler thread for the table, and before attempting to gain access to the list of delayed-insert threads.

waiting for handler insert : An INSERT DELAYED handler has processed all pending inserts and is waiting for new ones.

waiting for handler lock : This occurs before the allocating local table state when the connection thread waits for access to the delayed-insert handler thread.

waiting for handler open : This occurs after the Creating delayed handler state and before the got old table state. The delayed-insert handler thread has just been started, and the connection thread is waiting for it to initialize.

States associated with a delayed-insert handler thread that inserts the rows:

insert : The state that occurs just before inserting rows into the table.

reschedule : After inserting a number of rows, the delayed-insert thread sleeps to let other threads do work.

upgrading lock : A delayed-insert handler is trying to get a lock for the table to insert rows.

Waiting for INSERT : A delayed-insert handler is waiting for a connection thread to add rows to the queue (see storing row into queue).


Error 3154 while restoring a backup using WITH REPLACE I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak.

I have a database with the name test2 which is the same database, but the data changed.

I want to restore test.bak over test2 database.

I am always getting the error:

  Error 3154: The backup set holds a backup of a database other than the existing database.

I tried:

I right-cliked on test2 -> Restore database -> From device

I chose test.bak and checked With Replace but I get the error.
Then I tried to right-click on test2 -> Restore file and filegroups

I chose test.bak and checked With Replace but I get the error.

I can delete my old database and then restore my backup with the right name, but when I was using SQL 2008, I had no problem restoring over an existing database.

It seems that since I use SQL2012, I get this error a lot!

You should use WITH REPLACE and in general avoid using the point-and-click thingies in Management Studio - they're inflexible and often have bugs.

This worked for me:

USE [master];
GO

CREATE DATABASE test;
GO

CREATE DATABASE test2;
GO

BACKUP DATABASE test TO DISK = 'c:\temp\test.bak' WITH INIT, COMPRESSION;
GO

RESTORE DATABASE test2
  FROM DISK = 'c:\temp\test.bak'
  WITH REPLACE,
  MOVE 'test' TO 'c:\temp\test2.mdf',
  MOVE 'test_log' TO 'c:\temp\test2.ldf';

Also you should make sure when you backup databases you use WITH INIT and/or don't point the device at a file that already contains a backup (since it might not be the same database you're backing up now - especially if you reuse names like test...).


mysql: How do you determine the ideal 'open_files_limit' setting value? I am facing this issue:
https://serverfault.com/questions/104618/out-of-resources-for-mysqldump

Questions

I know I need to increase open_files_limit, but to how much?
Can I call a command in mysql that tells me how much this should be?

You may find this surprising, but mysqld actually determines the correct value for you upon startup.

The MySQL Documentation says the default value for open_files_limit is 0. Yet, when you run the command SHOW VARIABLES LIKE 'open_files_limit';, it does not come back with 0. It returns what mysqld would comfortably run with.

The MySQL Documentation says the maximum value for open_files_limit is 65535. I have seen some systems with the setting above 100000 upon startup (132332 to be exact). That being the case, there are some instances where you cannot set open_files_limit any higher.

Just run the command I mentioned

SHOW VARIABLES LIKE 'open_files_limit';

If the number comes back less that 65535, you can experiment with higher values up to 65535.

Keep in mind two more things

If you are using InnoDB with innodb_file_per_table enabled, you have to set innodb_open_files (Default is set to 300).
Keep your eye on the error log. It will post a warning if your setting for open_files_limit is too high.


How can I get MySQL to accept both resume and resumé on a primary key? If I add both then the uniqueness constraints fail, even though my field is in UTF-8. What can be done? I'm running 5.5.16

mysql> show create table foo_person;
+------------+------------------------------------------------+
| Table      | Create Table                                   |
+------------+------------------------------------------------+
| foo_person | CREATE TABLE `foo_person` (
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+------------+------------------------------------------------+

mysql> INSERT INTO foo_person (`name`) VALUES ('resumé');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO foo_person (`name`) VALUES ('resume');
ERROR 1062 (23000): Duplicate entry 'resume' for key 'PRIMARY'

mysql> select * from foo_person;
+---------+
| name    |
+---------+
| resumé  |
+---------+
1 row in set (0.00 sec)

It seems that MySQL does not support Accent Sensitive collations. (See http://dev.mysql.com/doc/refman/5.6/en/charset-collation-implementations.html)

Therefore your only option is to go with a binary collation, e.g. utf8_bin. However, that would also make the column case sensitive, which is different from your current collation.

See also https://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql


Within a trigger is there a way to tell if an update or insert came from a trigger or not? In PostgreSQL 9.3.5, I'm trying to prevent a trigger from executing again due to an update or insert within the current trigger or another trigger that executes upon update or insert. So I want to know what the source of the update is so I can prevent recursive triggering.

Unless I missed it, I looked here and didn't read anything about this in the manual:
https://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html

I don't think this would work because I am thinking it will always return the trigger I am currently in, not the source (trigger or not) of the udpate or insert:

TG_NAME

  Data type name; variable that contains the name of the trigger actually fired

Currently I am doing a lot of old.col_nm <> new.col_nm etc. to try and determine via the values that change if it was one of my triggers or not. It would be nice if I could just say "if an update or insert came from a trigger return new;" and skip the bulk of the trigger.

There are several ways for determining if the trigger is recursing. Checking the trigger depth (pg_trigger_depth()) is not one of them, because you don't know if the trigger itself is recursing or is being fired from another trigger.

The only direct way to know analysing the execution stack during an exception:

DECLARE
  context text;
BEGIN
  BEGIN
    SELECT 1/0;
  EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS context = PG_EXCEPTION_CONTEXT;
  END;
  -- analyse here the context variable to find if the last item in the stack
  -- is this same function
END;

Exception handling is costly, but so are the other options, which require leaving a session marker for avoiding recursion, either using a temporary table, or a session variable:

BEGIN
  IF to_regclass('pg_temp.' || quote_ident(TG_NAME)) THEN -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  EXECUTE format('CREATE TEMP TABLE %I (a int) ON COMMIT DROP', TG_NAME);
  -- general body of trigger
  EXECUTE format('DROP TABLE pg_temp.%I', TG_NAME);
  RETURN NEW; -- or whatever appropriate
END;

or using a session variable:

BEGIN
  IF current_setting('x.' || TG_NAME, true) IS DISTINCT FROM '1' THEN
    -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  PERFORM set_config('x.' || TG_NAME, '1', true);
  -- general body of trigger
  PERFORM set_config('x.' || TG_NAME, '', true);
  RETURN NEW; -- or whatever appropriate
END;

All of the options above have a significant performance impact. A possibly better performing option could involve a table for this purpose:

CREATE TABLE trigger_execs (
  pid int NOT NULL DEFAULT pg_backend_pid(),
  tgname name NOT NULL,
  PRIMARY KEY (pid, tgname)
);

which can be used like:

BEGIN
  IF EXISTS (SELECT 1 FROM trigger_execs
             WHERE pid = pg_backend_pid() AND tgname = TG_NAME) THEN
    -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  INSERT INTO trigger_execs (tgname) VALUES (TG_NAME);
  -- general body of trigger
  DELETE FROM trigger_execs
  WHERE pid = pg_backend_pid() AND tgname = TG_NAME;
  RETURN NEW; -- or whatever appropriate
END;


Does changing a Postgresql table's tablespace compact it like "vacuum full" or "cluster"? I need to reclaim disk space in a table in a Postgresql 9.3.2 database to be used for other tables.  The standard suggestions to accomplish this are: "VACUUM FULL" or  "CLUSTER" or copy the contents into a flat file and copy them back in.  When moving an existing table to a new tablespace with an "ALTER TABLE" command, the table's contents are copied.  Can anyone tell me if they are compacted during the "ALTER TABLE" process?

The comments so far are roughly correct, but to give an authoritative answer from looking at src/backend/tablecmds.c:

If you're only performing ALTER TABLE ... SET TABLESPACE ... ;, then ATExecSetTableSpace() will be invoked to handle the SET TABLESPACE, and it uses copy_relation_data() to perform a WAL-logged block-by-block copy of the table. However, if you were to specify additional actions to the ALTER TABLE command which require table rewriting, then the new copy of the table should be built (and compacted) in the new tablespace via ATRewriteTable().


What is log sequence number ? How it is used in MySQL? Can anybody give an example or scenario of log sequence number(LSN) used in MySQL

From mysqlperformanceblog

  Log Sequence Number (LSN) : Log Sequence Numbers correspond to given
  position in the log files and typically incremented for each log
  record. Innodb uses number of bytes ever written to the log files
  however it could be something different. LSNs are often extensively
  used in recovery check pointing and buffer management operations. When
  checkpoint (both fuzzy and not) happens you get something like “all
  changes up to LSN=X are now flushed to the data space” this means you
  can discard or archive logs for LSN earlier than that. When doing log
  recovery checking LSN in the log record can tell if you this change
  needs to be applied or it already was applied (when doing recovery you
  do not know which dirty pages were flushed from the buffer pool).

  The LSN do not relate much to transactions – changes from different
  transactions are intermixed in the log files and many LSNs can
  correspond to changes from the same transaction.

For other references please have a look at

1 How to know the current log sequence number in 5.0.X.

2 How to check if Innodb log files are big enough


WAL archiving from a standby backup strategy Postgresql 9.3 I am trying to figure out if wal archiving from a standby is possible and a viable option for a backup strategy on Postgresql 9.3.

The idea is to be able to take regular base backups and wal archives from a standby server in a remote DR site.

Unfortunately, the archive_command functionality is not available while the server is in standby_mode mode so I am wandering if the wal archiving could be done successfully using alternative ways like rsync, lsyncd etc.

Has anyone tried to this path can you share your experience?

Postgresql provides a pg_receivexlog utility that can be used instead of archive_command to do wal log archiving.

From the Documentation:

  pg_receivexlog is used to stream transaction log from a running PostgreSQL cluster. The transaction log is streamed using the streaming replication protocol, and is written to a local directory of files. This directory can be used as the archive location for doing a restore using point-in-time recovery


oracle decreasing vs increasing column varchar2 performance I know it seems stupid question but why increasing the column of a table tend be fast but decreasing it takes time depend on the size of the table?

Is it because while decreasing oracle check each column size if it can be decreased? However increasing it, no need to do some checking?

Example:

Drop table ex_employee
/
create table ex_employee (id number(14) null, name varchar2(10) null)
/
insert into ex_employee
select level, level||'A' from dual
connect by LEVEL <= 20000000
/

Running the below takes 0.2 seconds

alter table ex_employee modify name VARCHAR2(100)

however decreasing it takes arround 15 seconds.

alter table ex_employee modify name VARCHAR2(10)

Is it because while decreasing oracle check each column size if it can be decreased? However increasing it, no need to do some checking?

Correct.

When you decrease the length, Oracle needs to check the values in all rows in the table if any violates the new (reduced) length - it effectively reads all rows in the table.

This isn't necessary when you increase the length - no existing value can exceed that.


How to get archive re-do log file name containing the given SCN in Oracle? Scenario:
Let us consider oracle 3 node RAC. RMAN Managed Recovery is configured between production and standby DBs. Let us assume that the SCNs of 3 nodes are S1, S2, S3 at some point of time respectively. Let Smax=max(S1,S2,S3)

Is there any possibilities to get the archive file name containing the given SCN, Smax in oracle so as to stop applying logs at standby database as soon as the archive logs of the file containing the given SCN, Smax are applied?

If you want to stop recovery at a specific SCN, just run recovery until that specific SCN:

RMAN> recover database until scn 3087500;

If you want the name of the files:

RMAN> list archivelog scn between 3087500 and 3087500;

List of Archived Log Copies for database with db_unique_name GUN_O7CA
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
2       1    22      A 12-FEB-18
        Name: /fra/GUN_O7CA/archivelog/2018_02_12/o1_mf_1_22_f83lq5l2_.arc

6       2    31      A 12-FEB-18
        Name: /fra/GUN_O7CA/archivelog/2018_02_12/o1_mf_2_31_f83lqm9v_.arc


In PostgreSQL, will VACUUM reset the dead tuple count (n_dead_tup in pg_stat_all_tables)? In PostgreSQL, will VACUUM reset the dead tuple count (n_dead_tup in pg_stat_all_tables)?

The situation I'm seeing is that vacuum is apparently being run manually, once daily, and the n_dead_tup / n_live_tup data shows that there are a large, and increasing number of dead tuples.

PostgreSQL 9.3 here.

Yes, it resets the value. But it is from the stats collector, not vacuum.

postgres=# create table x (a int);
CREATE TABLE
postgres=# insert into x values (1),(2),(3),(4);
INSERT 0 4
postgres=# delete from x where a%2 =0;
DELETE 2

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_all_tables where relname='x';
-[ RECORD 1 ]-------+----------
relid               | 588915846
schemaname          | public
relname             | x
seq_scan            | 1
seq_tup_read        | 4
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 4
n_tup_upd           | 0
n_tup_del           | 2
n_tup_hot_upd       | 0
n_live_tup          | 2
n_dead_tup          | 2
n_mod_since_analyze | 6
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

postgres=# vacuum x;
VACUUM

postgres=# select * from pg_stat_all_tables where relname='x';
-[ RECORD 1 ]-------+------------------------------
relid               | 588915846
schemaname          | public
relname             | x
seq_scan            | 1
seq_tup_read        | 4
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 4
n_tup_upd           | 0
n_tup_del           | 2
n_tup_hot_upd       | 0
n_live_tup          | 2
n_dead_tup          | 0
n_mod_since_analyze | 6
last_vacuum         | 2016-01-12 16:49:32.248731+08
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 1
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0


Which authentication is better for MS-SQL Server 2019 : integrated security=SSPI vs uid=sa;pwd=xx | windows auth vs sa auth In my different web applications I use both ways, however, I would like to learn which method is better:
1: server=localhost;database=x; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes;

2: server=localhost;database=x;uid=sa;pwd=y;

In my web application, for each database query, I open a connection and then close the connection.
So I am pretty much using the regular style such as:
    using (SqlConnection connection = new SqlConnection(srConnectionString))
    {
        connection.Open();
        using (SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection))
        {
            DA.Fill(dSet);
        }
    }

You can safely assume that there are hundreds or even thousands of queries at any given second.
My questions:
Are there any performance wise difference between 2 authentication methodology?
Are there any security difference between 2 authentication methodology?
I don't allow remote connections to the SQL Server. So only local connections are allowed.
Operating system Windows Server 2019

Security Difference
You can find that on the Connecting Through Windows Authentication doc:

Windows Authentication is the default authentication mode, and is much
more secure than SQL Server Authentication. Windows Authentication
uses Kerberos security protocol, provides password policy enforcement
with regard to complexity validation for strong passwords, provides
support for account lockout, and supports password expiration. A
connection made using Windows Authentication is sometimes called a
trusted connection, because SQL Server trusts the credentials provided
by Windows.

Performance Difference
The existing performance difference between these two methods isn't enough to advise you to choose SQL Server Authentication over Windows Authentication to gain any performance improvement in detriment of security. Therefore, if you have the option to use Windows Authentication, use it.
As J.D.'s comment says, storing credentials is not the safest option and even the performance disadvantage of Windows authentication was mitigated with SQL Server connection pooling as mentioned by Dan Guzman. He also added:

I tested 10K connection open/close requests in my test lab on bare
metal. The average milliseconds per connection were: SQL auth with
polling: 0.10838721, Windows auth with pooling: 0.12424151, SQL auth
with no pooling: 2.66011692, Windows auth without pooling: 3.2432628.
Consider that query execution rather than connections will likely be
the long pole in the tent.


ZipCode to ZipCode Storage I'm working on a project that requires me to allow users to store/edit properties of what they call "path". A path can be any Zip Code to any other Zip Code.

The system they have now, utilizes one table for 3 digit to 3 digit system for default properties, and then if exceptions to the 3to3 are needed, there's another table that does the 5 digit to 5 digit.

123 > 345
12345 > 34567

Making changes is interesting for them, because it becomes a game to figure out exceptions (5 to 5) in relation to the 3 to 3 properties. Add in the fact that going from zip 12345 to zip 98765 isn't the same as doing the reverse 98765 to 12345.

So, they'd rather have one table that has the immediate detail of "every 5 digit zip code to every 5 digit zip code". By my calculations that means close to 2 billion rows (43000 x 43000. since there's about that many valid zips in the US).

That just seems like a bad idea to me, but without going right back to the situation they already have, I can't see a way around it.

I thought about storing them as one single combination, but then having the extra fields for either direction. Like this:

Zip1: 12345
Zip2: 98765
1to2 Property 1:
2to1 Property 1:
1to2 Property 2:
2to1 Property 2:
1to2 Property 3:
2to1 Property 3:

This would reduce the number of rows to under 1 billion. I would just setup the Zip1 to Zip2 as the primary key, and then have a unique index for the reverse Zip2 to Zip1.

Has anyone ever seen a neat trick or idea to storing data like this?

Partial answer:  Given that you need to look up ($z1, $z2):

WHERE zip1 = LEAST($z1, $z2)
  AND zip2 = GREATEST($z1, $z2)

For prep, you have only the PRIMARY KEY(zip1, zip2) and you have filled the table only with smaller zip to larger zip.  And adjusted the 1to2, etc, columns accordingly.


Moving DB Log destination? I currently have SQL DB and Log files going to the same drive.  I would like to move the Log files to their own drive.   Is there an easy way to achieve this and will it require downtime/reboot of the server?

Please don't detach and attach your database. If you detach it and something happens to it, you now have zero copies of your database. Much safer approaches:

Backup/Restore
USE master;
GO

BACKUP DATABASE floob TO DISK = 'c:\wherever\floob.bak' WITH INIT, COPY_ONLY;

ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE floob FROM DISK = 'c:\wherever\floob.bak'
  WITH REPLACE,
  MOVE N'floob_log' TO 'c:\new_path\floob.ldf';

Offline/Online
(You should still take a backup prior to doing this.)
USE master;
GO

ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE floob MODIFY FILE
  (name = N'floob_log', filename = 'c:\new_path\floob.ldf');

ALTER DATABASE floob SET OFFLINE;

Now go to Windows Explorer, copy the ldf file to the new location, and rename the original. Make sure the SQL Server service account has adequate permissions on the new folder. Then:
ALTER DATABASE floob SET ONLINE;

ALTER DATABASE floob SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Once the database is back up and running, you can safely delete the log file from the source location.

Both methods will need a brief downtime (how much depends on a lot of factors, including size of the database and speed of I/O both on the drives themselves and whatever path is between them), but neither will require you to reboot the server.


Converting RTF in a text column to plain text in bulk I have a legacy system with about 10 million rows in a table. In that table there is a column of type text, most of them are standard text but about 500 thousand rows have RTF markup in them. I need to convert the RTF formatted text in to plain text.

My current method is I have a C# program that loads the query in to a DataTable using a SqlDataAdapter and uses the winforms RichTextBox control to do the conversion.

void bw_DoWork(object sender, DoWorkEventArgs e)
{
    count = 0;

    rtbRTFToPlain = new RichTextBox();

    using (SqlDataAdapter ada = new SqlDataAdapter("select note_guid, notes from client_notes", Globals.SQLConnectionString))
    using(SqlCommandBuilder cmb = new SqlCommandBuilder(ada))
    {
        DataTable dt = new DataTable();
        ada.UpdateCommand = cmb.GetUpdateCommand();

        ada.Fill(dt);

        int reportEvery = dt.Rows.Count / 100;
        if (reportEvery == 0)
            reportEvery = 1;
        foreach (DataRow row in dt.Rows)
        {
            if (count % reportEvery == 0)
                bw.ReportProgress(count / reportEvery);

            try
            {
                if (((string)row["notes"]).TrimStart().StartsWith("{") == true)
                {
                    rtbRTFToPlain.Rtf = (string)row["notes"];
                    row["notes"] = rtbRTFToPlain.Text;
                }
            }
            catch
            {
            }

            count++;

        }
        bw.ReportProgress(100);

        this.Invoke(new Action(() =>
            {
                this.ControlBox = false;
                this.Text = "Updating database please wait";
            }));
        ada.Update(dt);
    }
}

This is working great for small tables, however this is the first time I had to run it on a table with such a large data-set (some of the rtf files can be several megabytes in size with embedded pictures) and I am getting OutOfMemory errors with my C# program.

I know I can chunk my query down in to a smaller batches, but I wanted to see if there is a better way that I was missing to strip off RTF formatting.

Should I just do the same thing as my current solution but only query out data smaller chunks at a time, or is there a better way to do this?

I ended up making a CLR function to convert it.

I found this library, I then tweaked it a tiny bit to remove things I did not need like logging and Drawing methods, which allowed me to mark it as safe.

I then just made this small class.

using System.Data.SqlTypes;
using Itenso.Rtf.Converter.Text;
using Itenso.Rtf.Support;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString RtfToPlainText(SqlString text)
    {
        if (text.Value.StartsWith(@"{\rtf"))
        {
            RtfTextConverter textConverter = new RtfTextConverter();
            RtfInterpreterTool.Interpret(text.Value, textConverter);
            return textConverter.PlainText;
        }
        else
            return text;
    }
}

And ran this in SQL

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

CREATE ASSEMBLY ConversionsSqlExtensionsAssembly
from 'E:\Code\ConversionsSqlExtensions\bin\Debug\ConversionsSqlExtensions.dll'
WITH PERMISSION_SET = safe
go

CREATE function RtfToPlainText(@value nvarchar(max))
returns nvarchar(max)
AS EXTERNAL NAME ConversionsSqlExtensionsAssembly.StoredProcedures.RtfToPlainText

And it is fast and works great!


User cannot create schema in PostgresSQL database I'm trying to setup a "deployment" user which can create and alter tables on an existing database in addition to selecting, updating, inserting and deleting records.

Here is what I've tried so far:

-- Create deployment user
CREATE ROLE deploy_user WITH LOGIN PASSWORD 'deploy_user';

-- Grant connect and create
GRANT CONNECT, CREATE ON DATABASE my_database TO deploy_user;

-- Grant create schema privilege
ALTER ROLE deploy_user CREATEDB;

-- Change db owner to deployment user
ALTER DATABASE my_database OWNER TO deploy_user;

-- Grant CRUD operations
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO deploy_user;

None of the above grants work. What I end up with is a user which can login but that's it. I cannot select, insert, update, delete on any tables. I cannot make changes to the schema either.

Can anybody help out?

You need to provide at least CREATE permission in schema:

GRANT CREATE ON SCHEMA public TO deploy_user;

or ALL:

GRANT ALL PRIVILEGES ON SCHEMA public TO deploy_user;

See manual at http://www.postgresql.org/docs/9.4/static/sql-grant.html


How to convert Rows to Columns I've a table Columns

and a second table Response in which all data is saved.

Now I want to create a SQL View in which the result should be like this

I tried using pivot

select UserId ,FromDate, ToDate, Project, Comment
from
(
  select R.UserId ,R.Text , C.ColumnName
  from [Columns] C
  INNER JOIN Response R ON C.Id=R.ColumnId
) d
pivot
(
  max(Text)
  for ColumnName in (FromDate, ToDate, Project, Comment)
) piv;

but that didn't worked for me, I also referred this https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server but was not able to implement it. Any ideas how to achieve the same in SQL View?

Scripts for Tables:

CREATE TABLE [dbo].[Columns](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](1000) NULL,
    [IsActive] [bit] NULL,
 CONSTRAINT [PK_Columns] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into [Columns] values('FromDate',1)
insert into [Columns] values('ToDate',1)
insert into [Columns] values('Project',1)
insert into [Columns] values('Comment',1)

CREATE TABLE [dbo].[Response](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint]  NOT NULL,
    [ColumnId] [bigint]  NOT NULL,
    [Text] [nvarchar](max) NULL,
    [IsActive] [bit] NULL,
    CONSTRAINT [PK_Response] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into [Response] values(1,1,'1/1/2012',1)
insert into [Response] values(1,2,'1/2/2012',1)
insert into [Response] values(1,3,'p1',1)
insert into [Response] values(1,4,'c1',1)
insert into [Response] values(2,1,'1/1/2013',1)
insert into [Response] values(2,2,'1/2/2013',1)
insert into [Response] values(2,3,'p2',1)
insert into [Response] values(2,4,'c2',1)
insert into [Response] values(2,1,'1/1/2014',1)
insert into [Response] values(2,2,'1/2/2014',1)
insert into [Response] values(2,3,'p3',1)
insert into [Response] values(2,4,'c3',1)
insert into [Response] values(3,1,'1/1/2015',1)
insert into [Response] values(3,2,'1/2/2015',1)
insert into [Response] values(3,3,'p4',1)
insert into [Response] values(3,4,'c4',1)

You are not explaining why your PIVOT query did not work for you, although it is not too hard to guess if your Response sample is representative enough of the data in that table in general. You have two sets of responses for User 2 but your query picks up only one.

As your expected output shows you want the query to return both sets, you need to teach it to distinguish between various sets of answers by the same user. One way is to use the ROW_NUMBER analytic function:

SELECT
  UserId,
  FromDate,
  ToDate,
  Project,
  Comment
FROM
  (
    SELECT
      r.UserId,
      r.Text,
      c.ColumnName,
      SetNo = ROW_NUMBER() OVER (PARTITION BY r.UserId, r.ColumnId ORDER BY r.Id)
    FROM
      dbo.Columns AS c
      INNER JOIN dbo.Response AS r ON c.Id = r.ColumnId
  ) AS derived
  PIVOT
  (
    MAX(Text)
    FOR ColumnName IN (FromDate, ToDate, Project, Comment)
  ) AS p
;

The query above makes a few assumptions:

every set of answers consists of the same set of ColumnIds;
related responses are arranged together (based on the order of Id), or, at least, if one FromDate response comes later than another FromDate response, then its related ToDate response also comes later than the ToDate response related to the previous FromDate.

If there are special cases in the real data (e.g. some sets may be incomplete), a different approach may be required.


Is it possible to reorder tables in phpMyAdmin? By default, database tables are ordered in alphabetical order.

Can this be reordered?

For example:

default alphabetical: company, profile, user
common User Flow: user, profile, company

Maybe the desire to order it from a UX standpoint isn't best practise.

But navigating it several times a day top-down would seem quicker and easier than a dictionary (FCFS).

I currently:

reordering columns by drag-and-drop (or ALTER TABLE)
hiding tables containing static data (rarely changed)

Not exactly, but you might be able to improve things regardless.

For instance, in a database structure tab, you can sort based on any of the columns; table name, row count, collation, etc. In your specific example, you could simply sort the tables in reverse order (by clicking the "Table" header to invert the default sort order), though of course that isn't a universal solution.

From the navigation frame, you can use the favorite tables feature (which puts selected tables in a quick-access dropdown menu), the show/hide tables feature to hide tables you rarely access (sounds like you're already doing this), and the filter dropdown to quickly find and access an arbitrary table by typing a few keystrokes.

I don't it's not really a complete solution to re-ordering the display of tables, but at the moment those are probably the best solutions available.

As a side note, this would make an interesting feature request for the project issue tracker.


Methods for right-sizing SQL Server virtualization? I am starting to plan a migration of SQL Server 2008 R2 Enterprise Ed. to 2017 (edition TBD). One of the more important tasks assigned to me is to determine the specs for the new VMWare hardware my employer will be purchasing.

To do so, I need gather information about the current workload (on Windows Server 2008 R2 SP1 with SQL failover clustering on 5+ year-old hardware) and translate that to virtualized performance on new hardware - a VMware host which will use SQL Server 2017 and, probably, Availability Groups.

I've set up a PerfMon data collector based off of a blog post by David Klee ( link ) which will start tonight at midnight as a starting point but am still trying to wrap my head around the analysis...

i.e. "How do I take the PerfMon results from the workload running on the old hardware and translate that into requirements for new hardware ?".

Apart from PerfMon what are my other options for right-sizing the new hardware?

Please try Microsoft Assessment and Planning Toolkit.  This will give you current inventory and workloads.  At least run this tool for 24 hours to collect overall performance.
https://www.microsoft.com/en-us/download/details.aspx?id=7826


Using NEXT clause to set periodic materilized view refresh in oracle and verifying refresh I am not able to find the right documentation on setting specific timings for materialized view refresh for oracle. I read up the documentation and some examples there, however there is no definite documentation on using NEXT clause. What would I do to set up refresh every day at 8am and 8pm for example? How would I achieve refresh 3 times a day?

Here's what I am using in my case. I understand this will start refresh at 7am tomorrow and next every 12 hours?

alter materialized view MY_VIEW
refresh fast
start with (sysdate+1) + 7/24
next trunc(sysdate) + ((trunc(to_char(sysdate,'HH24')/12)*12)+12)/24

I also want to know if there is a way to know how to verify if the materialized view was refreshed once the timings are set.

Update:
I tried to refresh my MV every hour, which didi not work. I am wondering why is that. Here's what I used:

CREATE MATERIALIZED VIEW  "MYVIEW"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "APEX_xxxxxxxxxxxxxxxxxx"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate+1/24
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS select *
from <mydblinktable>

I'd use DBMS_SCHEDULER instead, with a job for each refresh. It's then easier to manage, and view previous executions.

CREATE OR REPLACE PROCEDURE REFRESH_MY_VIEW
AS
BEGIN
    DBMS_MVIEW.REFRESH('MY_VIEW');
END;
/

Refresh at 8am every day:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'REFRESH_MY_VIEW',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'REFRESH_MY_VIEW',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=8; byminute=0; bysecond=0;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'Refreshes MY_VIEW at 8am'
    );
END;
/

Refresh at 8pm every day:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'REFRESH_MY_VIEW',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'REFRESH_MY_VIEW',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=20; byminute=0; bysecond=0;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'Refreshes MY_VIEW at 8pm'
    );
END;
/

The *_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS data dictionary views provide all the information you need to track what executed, when, and if any errors occurred. Documentation link here.

As pointed out in a comment, you can add multiple by hour clauses, comma-separated, as follows: repeat_interval => 'freq=daily; byhour=8,20; byminute=0; bysecond=0;'


Identifying values that are not existent in other table I've got two tables that have one to many associations on a pmid.  So if one table has an pmid, the second table should have multiple rows with the same pmid.  However, something went sideways and I'm missing my latest batch of pmids in the second table.  These queries, should help illustrate the problem, but I can't figure out how to get the ids from the first table that are actually missing in the second table.

select count(*) from abstract_mesh am; #2167101

select count(*) from abstract_mesh am
join abstracts a on am.pmid = a.pmid; #2133848

select 2167101 - 2133848;  #33253

select count(*) from abstract_mesh where pmid is NULL; #33253

So as you can see there are 33,253 rows in abstract_mesh that have no pmids.  I simply want to identify which pmids I should be interested in from the abstracts table.

These are pmid values that are in abstracts but not in abstract_mesh

select a.pmid from abstracts a
left join abstract_mesh am on a.pmid = am.pmid
where am.pmid is null;

or

select a.pmid from abstracts a
left join abstract_mesh am using (pmid)
where am.pmid is null;