new Install and migrate from mysql to mariadb I have a server landscape,
running mysql 5.7 on debian 8 in some
GTID based replication configuration.

All is high load servers and 200+GB databases.

Now I got some new servers.
I like to run that new servers on debian 9 and the recommended mariadb 10.2.X
expanding it later to a MariaDB Galera Cluster.

I've read some, how to migrate from mysql to mariadb, but anyhow none of them seems to fit for me.

Different GTID handling and innodb format between mysql and mariadb does scare me a bit.

After all I have to run some tests.

So, my idea is:

on one of the masters (mysql 5.7),

-flush tables with read lock

-make a snapshot (LVM) of the database partition

-show master status to get the GTID executed

-unlock tables

-copy that snapshot to a new server to /var/lib/mysql

-install mariadb on the new server

would mariadb upgrade that data in /var/lib/mysql

Any recommendation or another ideas on that?

Ju

So, for the after world.

I did it by following my idea and it worked out well.

A few minor things has to be taken into account.

After copying data over from one server to the other.

The *.pem and the auto.cnf files in /var/lib/mysql/ have to be deleted on the new machine.
Deleting auto.cnf is really important because it holds the servers ID and you don't wanna end up running several servers with same server ID, what will cause you problems afterwards.

On a fresh installed machine, before installing mariadb, there isn't a user mysql nor group mysql on the system, so change the /var/lib/mysql and it's sub folders to be owned by root:root.

I copied the entire /etc/mysql folder from source machine to destination machine, to make the mariadb install script believe there was a running mysql installation before on that machine.
In the config file /etc/mysql/mysql.conf.d/mysql.conf I changed the value of the server_id variable to be unique in the entire server landscape.

After that you are ready to fire the mariadb installation on the new machine using apt.
While installing, mariadb install script detects the mysql stuff and tries to run the mysql-upgrade on it. That will probably fail, as in my case, but that's not a big deal.

It is because the install script tries to run mysql-upgrade as root user on your freshly installed mariadb server with old data in place. But there are differences between mysql and mariadb in system tables and in what root on a local machine can do and what not, and system tables needs to be upgraded by the upgrade script.

I never run things as root user on the database, so I have my own user with rights do do everything on each of my databases, and that user credentials come with the data from the old machine. So I run the update script just from the command line using my user credentials instead of the root credentials.

And it turned out to run well, complaining about some missing stuff, but that is what the update script is written for. To detect missing or changed stuff and correct it.
Restart mysql and there we are.
All my users and 230+GB of data right into access managed now by mariadb on a debian 9 machine.

Hope it helps someone.

Ju


WHEN MATCHED clause fails if there's a NULL in the ON columns I'm using a query with merge that works fine if all columns in the ON clause have a value but WHEN MATCHED omits if there's a NULL mark in one of the columns:

MERGE PEPS AS peps
USING (Lots of Cols..
       FROM PEPS_temp)
AS temp (Lots of Cols..)
ON peps.[Name] = temp.[Name] AND
   peps.[LastName] = temp.[LastName] AND
   peps.[TypeEntity] = temp.[TypeEntity] AND
   peps.[Source] = temp.[Source]
WHEN MATCHED THEN
UPDATE SET peps.[Category] = temp.[Category]
WHEN NOT MATCHED THEN
INSERT (Lots of Cols..) VALUES (temp.[Col], temp.[Col], temp[Col]....)

Case: I have target table that I update manually first its Category column so I set Category='EX BLOCKED' the source table is the updated list so the clause WHEN MATCHED is supposed to update the Category column to 'BLOCKED' if it exists in the source table, if not WHEN NOT MATCHED inserts the new rows that do not exist.

Query runs successfully and when checking the lists I detect duplicates:

;WITH Duplicados
     AS (Select ROW_NUMBER() OVER  (PARTITION BY Name, LastName, TypeEntity, Source ORDER BY ( SELECT 0)) RowNum, Name, LastName, TypeEntity, Source
         FROM [PEPS])
SELECT * FROM Duplicados
WHERE  RowNum > 1 AND Source='OFAC SDN';

All the duplicate rows contain NULL marks in at least one of the columns of the ON clause.

Example:

The predicate for MATCH doesn't fail, it behaves exactly like it is supposed to. NULL is not a value, NULL is a state that indicates there is no value.
Therefore, any predicate with any comparison to NULL will resolve to 'UNKNOWN' and not to 'TRUE', causing the row to be considered as a non-match.
To deal with that, you will need to decide on the logic and how you would like to evaluate the rows in case you have NULLs.
For example, if It is only for last name, and you want to consider it a match, you may solve it by extending the predicate to handle the NULL:

MERGE PEPS AS peps
USING (Lots of Cols..
       FROM PEPS_temp)
AS temp (Lots of Cols..)
ON peps.[Name] = temp.[Name] AND
   (peps.[LastName] = temp.[LastName] OR peps.LastName IS NULL) AND
   peps.[TypeEntity] = temp.[TypeEntity] AND
   peps.[Source] = temp.[Source]
WHEN MATCHED THEN
UPDATE SET peps.[Category] = temp.[Category]
WHEN NOT MATCHED THEN
INSERT (Lots of Cols..) VALUES (temp.[Col], temp.[Col], temp[Col]....)


Optimize SQL query in form of a Procedure I have a Oracle SQL query with multiple "IN" statements. It takes around a minute to execute in an average.

My task is to optimize this query in form of PL/SQL procedure so that the user gets the output resultset as a ref cursor.

Please suggest. Thanks!

SQL Query:

select sp.column1, sp.column2,sp.column3,gen.column1,nvl(gen.column2,0)+nvl(gen.column3,0) GENSIZE,gen.column4,gen.column5
from table1 sp,
(select * from table2 where table2.column1 in (
select table1.column4 from table1 where table1.column2 in (
select table3.column1 from table3 where table3.column2='124578'))) gen
where gen.id=sp.id;

As far as returning a ref cursor the following should work;

CREATE OR REPLACE PROCEDURE get_table1_data(p_column2 IN table3.column2%TYPE,
                                            p_tables_recs  OUT SYS_REFCURSOR ) AS
BEGIN

   OPEN p_table_recs FOR
      SELECT sp.column1
           , sp.column2
           , sp.column3
           , gen.column1
           , NVL(gen.column2, 0) + NVL(gen.column3, 0) gensize
           , gen.column4
           , gen.column5
      FROM   table1 sp
          , (SELECT *
             FROM   table2
             WHERE  table2.column1 IN (SELECT table1.column4
                                       FROM   table1
                                       WHERE  table1.column2 IN (SELECT table3.column1
                                                                 FROM   table3
                                                                 WHERE  table3.column2 = p_column_2))) gen
       WHERE  gen.id = sp.id;
END get_table1_data;


How does LEFT JOIN with WHERE clause works? Given the query below

    SELECT * FROM tableA
    LEFT JOIN tableB ON tableB.id = tableA.id
    WHERE tableA.name = 'e'

.

tableA                        tableB
-----------                   -----------
id    name                     id    school
-----------                   -----------
1     a                         1     AA
2     b                         2     BB
3     c                         3     CC
4     d                         4     DD
5     e                         5     EE

.

What baffles me is the process that is happening behind.

Does the system JOINs first both tableA and tableB before they select only the row from the WHERE clause?

If yes, then what about this query

SELECT * FROM
(
    SELECT * FROM tableA
    WHERE name = 'e'

) A LEFT JOIN tableB ON tableB.id = A.id

Does this query select first using the WHERE clause before it JOINs the other table?

Sorry I can't find the answer on the web. I wanted to lessen the MySQL resource bandwidth. Does the second query is the best for this situation?

The query optimizer will decide and it is pretty smart.

SELECT * FROM tableA
LEFT JOIN tableB
  ON tableB.id = tableA.id
WHERE tableA.name = 'e'

There are many cases where doing the join first is more efficient.  If name is indexed and 'e' is somewhat unique then do that first is more efficient.

This is different

SELECT * FROM tableA
LEFT JOIN tableB
  ON tableB.id = tableA.id
WHERE tableB.school = 'EE'

That changes the left join into an inner join but since there is a match in your data you would get the same results.


Single-column user defined variable based on a comparison I have a database with over 300k lines and each row is a separate timed event.  I have a column of system spec values for error, followed by a stochastic draw on an actual error for that event.  I can change the confidence level for the actual error by multiplying the actual error by a constant.

I have tried this query and while it returns a 1 in each error code, what I am looking for is a way to develop a user defined variable called “myerror” that is filled by each rows error * the constant.

sample query statements:

((select(actual_error * “constant”) <= lowest_threshold) as level_1_error,
((select(actual_error * “constant”)  between this and that) as level_2_error,
((select(actual_error * “constant”)  between higher_this and higher_that) as level_3_error,
…
…
…
((select(actual_error * “constant”) >= highest_threshold) as max_error,

What I’m trying for is something like:

Case when (select(actual_error * “constant”) <= lowest_threshold  set @user_error  =  “level_1_error”
          when (select(actual_error * “constant”)  between this and that) set @user_error  =  “level_2_error”

but, alas…..can’t seem to get it to work…..I’ve tried putting the set statement in a sub-query and just using a sub-query to select a different error level also…..

help bitte sehr.

I'd recommend you to create static, temporary or dynamic table which contains the adjacent border ranges for each error level normalized with “constant” value to avoid additional calculations (schema):

CREATE TABLE error_level_ranges ( level, lower_bound, upper_bound )
AS
SELECT 1,-1,2     -- 2 as "lowest_threshold"/constant,
                  -- assume -1 is below min possible value of actual_error
UNION ALL
SELECT 2,2,10     -- 10 as "that"/constans, assume "this"="lowest_threshold"
UNION ALL
SELECT 3,10,25    -- 25 as "higher_that"/constant, assume "higher_this"="that"
-- ...
UNION ALL
SELECT 99,100,999 -- 100 as "highest_threshold"/constant, assume levels count < 99
                  -- and 999 is over max possible value of actual_error

and then

SELECT t1.*, t2.level
FROM error_events t1, error_level_ranges t2
WHERE t1.actual_error >= t2.lower_bound
  AND t1.actual_error < t2.upper_bound
/* or alternatively, dependent by a real logic
WHERE t1.actual_error > t2.lower_bound
  AND t1.actual_error <= t2.upper_bound */

If you have a lot of error categorization schemes with different level counts and their borders, you must add a field (named similar to categorizing_scheme) into error_level_ranges table and proper additional expression like AND t2.categorizing_scheme=@current_scheme to conditions section.


How to install olaptrain sample schema on oracle 12c? I need to install the sample schema available from the Oracle website:

http://download.oracle.com/otndocs/products/warehouse/olap/olaptrain_install.zip

I am trying to run the commands as given here:

http://www.oracle.com/technetwork/database/enterprise-edition/readme-098894.html

But I get this error:

ORA-01537: cannot add file 'dm.dbf' - file already part of database

Given below is the copy of the screen:

SQL> ALTER SESSION SET CONTAINER = pdborcl;

Session altered.

SQL> @install_olaptrain.sql

Specify file system directory containing this installation program.

Directory: C:\Users\Ashish\Desktop\olaptrain_install

Specify a password for the OLAPTRAIN user.
Password:

Begin installation
... creating tablespaces
  declare
*
ERROR at line 1:
ORA-01537: cannot add file 'dm.dbf' - file already part of database
ORA-06512: at line 31

How can I resolve this error?

The problem is solved after changing the location for datafile in 'install_olaptrain.sql', and

changing the 'ho impdp' statement to the given below string resolves any login error in Oracle 12c:

ho impdp olaptrain/manager@//localhost:1521/my_pdb dumpfile=OLAPTRAIN12232009.DMP directory=OLAPTRAIN_INSTALL

But then "ORA-06512: at "SYS.UTL_FILE", line 536" occurs.


Impdp 12c takes table original size I want to import one of my schema "vvs1" to r&d database with content=metdata_only. expdp works perfect but when i am going to import on r&d server it takes tables original size.

for example:-
I have 1 table vvs.xyz with size of 10 GB. i am taking structure backup only. now import on r&d server it creates a table structure with 10 GB size.
i want only table with default size while created.

expdp syntax:

expdp vvs/1 full=Y CONTENT=METADATA_ONLY directory=DATAPUMP_DIR dumpfile=full.dmp logfile=full.log KEEP_MASTER=Y

impdp syntax:

impdp vvs/1  CONTENT=METADATA_ONLY directory=DATAPUMP_DIR dumpfile=full.dmp logfile=full_impdp.log schemas=vvs

main database :-Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

r&d database:-
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Typically caused by the segment attributes of the tables.

You can use TRANSFORM=SEGMENT_ATTRIBUTES:N, so the tables will be created with default options.

You can use TRANSFORM=SEGMENT_CREATION:N as well, so your segments will not be created at all, unless deferred segment creation is disabled in your database.

TRANSFORM

DEFERRED_SEGMENT_CREATION


Retrieve a count from a one-to-many relationship even if there are no rows at the "many"-end of the relationship I have the following table, company_likes:

                     Table "public.company_likes"
   Column   |              Type              |       Modifiers
------------+--------------------------------+------------------------
 company_id | integer                        | not null
 user_id    | integer                        | not null
 created_at | timestamp(0) without time zone | not null default now()
Indexes:
    "company_likes_pkey" PRIMARY KEY, btree (company_id, user_id)
Foreign-key constraints:
    "company_likes_company_id_foreign" FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
    "company_likes_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

The structure of the companies table is not important as you can deduce whatever needed for the purpose of this question from the foreign keys details in the \d company_likes output.

I am trying to fetch the number of likes for a set of companies. I tried the following:

select company_id, count(company_id) as likes_count from company_likes where company_id in (1,2,3,4) group by company_id;

And per this StackOverflow answer, I have also tried this:

select company_id, count(company_id) as likes_count from company_likes
left join companies on companies.id = company_likes.company_id
where company_id in (1,2,3,4)
group by company_id;

However the output in both cases is an empty result. I would have liked something like this

 company_id | likes_count
------------+-------------
 1          | 0
 2          | 0
 3          | 0
 4          | 0

What query should I use to get a result like this?

Try

select
  C.id
, count( CL.company_id ) likes_count
from companies C
  left join company_likes CL on C.id = CL.company_id
group by C.id
order by C.id ;

See dbfiddle (Postgres 9.6).

-- test tables and data
create table companies ( id int4 primary key ) ;

insert into companies ( id ) values (1),(2),(3),(4);

select * from companies ;

id
1
2
3
4

create table company_likes (
  company_id int4 references companies( id )
);

insert into company_likes ( company_id ) values (1),(4),(4),(4),(4) ;

select * from company_likes ;

company_id
1
4
4
4
4

Query

select
  C.id
, count( CL.company_id ) likes_count
from companies C
  left join company_likes CL on C.id = CL.company_id
group by C.id
order by C.id ;

id  likes_count
1   1
2   0
3   0
4   4


In AWS Aurora's performance insights. What qualifies as "Other" and how do I investigate it? I have a disproportionate amount of queries that are marked as "Other" in performance insights with no additional details. AWS doesn't seem to provide any details about what this means, and asking on the forum doesn't seem to work.

Is there a way to get details about what falls into Other?

Our AWS rep says that "Other" is the sum of all other executing queries. There's also another category called "Unknown" which is queries that AWS hasn't mapped yet.


MS SQL 2017 Batch insert performance degradation This one's a bit strange so bear with me:

I'm seeing extreme insert performance degradation after an insert was interrupted in a dirty fashion (the transaction was not committed and the connection was not closed properly).

Pre incident, insert performance was roughly 100m rows/hour with 500k batches (about 20 seconds/batch).

Post incident, batch inserts with more than ~80k rows will never complete (left it running for more than a day) whereas an insert with 20k rows completes in ~95 seconds but that results in an insert performance of only around 18m rows/hour. The machine the SQL server is installed on does not experience any load. As far as I can tell the server is idling.

What trips me up about this is that dropping the database or even reinstalling the server instance does not resolve the issue. I simply can't reproduce the performance I was seeing pre incident. On top of that, I had observed this exact phenomenon with Azure SQL instances, except that dropping the database "solved" the issue there.

The way this issue presents itself indicates that the problem is not actually with the SQL server but rather on the insert side / the driver. However I've tested basically all possible ways to connect to a MS SQL instance from python with the same result.

The SQL Server 2017 instance is updated and running on a fairly powerful dedicated machine with NVMe drives located in the same datacenter as the client machine.

The inserts are executed using:

ubuntu 16.04
Python 2.7
pyodbc 4.0.23 (also tested pymssql+freetds and pypyodbc)
Microsoft ODBC driver 17.2.0.1

Okay, so it is a very specific issue with PyODBC afterall:

I create tables with IGNORE_DUP_KEY = ON as ensuring no duplicate insertions would be much more expensive in our application and previously inserted IDs will get updated shortly afterwards anyways. The incident described in the original question (in combination with an unrelated bug) left the client application in a somewhat erroneous state, trying to re-insert previously inserted IDs. But even this should not be an issue normally.

I'm not sure yet why but PyODBC seems to take offense to duplicate primary key insertions. It becomes completely unresponsive (ignoring configured timeouts and ctrl+c) and using 100% CPU. SQL Server logs show it repeatedly kills its connection to the server and reconnects. I'll post this issue over on their github.


How to handle -904 Invalid identifier error in oracle store procedure My proc is like this

CREATE OR REPLACE PROCEDURE TEST_INSERT
    IS
      TotalUpd pls_integer;
      err_code number;
      err_msg varchar(500);

      invalid_excep EXCEPTION;
      PRAGMA EXCEPTION_INIT(invalid_excep,-904);
    BEGIN
      insert into test_proc_tbl(un_id) /* this column doesn't exist */
        select 'asd' from dual;

        TotalUpd := SQL%ROWCOUNT;
        commit;

    EXCEPTION

        WHEN invalid_excep THEN
            INSERT INTO err_log(src,typ_ex,ERROR_DESC,ROW_COUNT,RUN_STATUS,UPDATED_DATE)
            select 'TEST','STORE PROCEDURE',to_char(err_code) || '-' || err_msg,TotalUpd,'ERROR',SYSDATE from dual;
            COMMIT;

        WHEN OTHERS THEN
            err_code := SQLCODE;
            err_msg := SUBSTR(SQLERRM,1,200);

            INSERT INTO err_log(src,typ_ex,ERROR_DESC,ROW_COUNT,RUN_STATUS,UPDATED_DATE)
            select 'TEST','STORE PROCEDURE',to_char(err_code) || '-' || err_msg,TotalUpd,'ERROR',SYSDATE from dual;
            COMMIT;

    END;

I am trying to insert Invalid identifier error in my log table, purpose of doing this some of my base table will be recreated twice in a week, other team can miss or modify column name. Here my problem is Exception doesn't catch invalid identifier error, how to fix this?

The way you wrote your procedure, ORA-00904 is a compile time error, you can not catch that with a runtime exception handler.

Simplest workaround is to use dynamic SQL. Instead of:

 insert into test_proc_tbl(un_id) /* this column doesn't exist */
        select 'asd' from dual;

Use this:

execute immediate ' insert into test_proc_tbl(un_id) select ''asd'' from dual';

This will compile regardless of the existence of un_id column, and you can catch the error. However, you will lose dependecy tracking between the table and your procedure.


PgPool: distro vs upstream? I'm running PostgreSQL 9.6 and would like to trial pgpool. The yum repository shows the following options:

pgpool-II-96
postgresql-pgpool-II

Which version should i use ? The documentation on PgPool references the first option, but I've found posts on here that say I should use the PostgreSQL specific repository.

Also are there any useful tutorials on getting this up and running for a first time user, i find the main documentation at pgpool.net confusing?

When I start pgpool i get the following output.

2018-01-31 17:51:13: pid 41278: DEBUG:  I am 41278
2018-01-31 17:51:13: pid 41278: DEBUG:  initializing backend status
2018-01-31 17:51:13: pid 41278: DEBUG:  authenticate kind = 0
2018-01-31 17:51:13: pid 41278: FATAL:  unable to read data from DB node 0
2018-01-31 17:51:13: pid 41278: DETAIL:  EOF encountered with backend

It just depends on what one you want,

pgpool-II-96 is provided by pgpool.net
postgresql-pgpool-II is provided by your distro.

There is no right answer. Likely, I would go with distro-provided version. It's usually better integrated and I value that more than being more up to date.

As for,

  FATAL:  unable to read data from DB node 0

That's another question, but it sounds like a connectivity issue.


Performing query based on another query's result First I have to run the query (1) using the pagedetailid number to get the adblockid. Then I have to put that adblockid into the other query (2) to get the result. What I would prefer is a single query.

select distinct Flyerid, adblockid, OriginalPagedetailid, Pagedetailid,
category, brand, [Sales Price], Size, Format, manufacturer
from prdcnen where pagedetailid = '67197228466'

select distinct Flyerid, adblockid, OriginalPagedetailid, Pagedetailid,
category, brand,[Sales Price], Size, Format, manufacturer
from prdcnen where Adblockid = 1521368536511158

How can I merge these two statements?

One possible way is an inner self join.

SELECT DISTINCT
       p1.Flyerid,
       p1.adblockid,
       p1.OriginalPagedetailid,
       p1.Pagedetailid,
       p1.category,
       p1.brand,
       p1.[Sales Price],
       p1.Size,
       p1.Format,
       p1.manufacturer
       FROM prdcnen p1
            INNER JOIN prdcnen p2
                       ON p2.Adblockid = p1.Adblockid
       WHERE p2.pagedetailid = '67197228466';


Cluster Validation on Remote Node I have a 3 Node always availability group:

2 Nodes are In house – Synchronous Mode.
1 Node is remote - Asynchronous Mode.

Every now and then the Remote Node loses connectivity and goes offline from the cluster.  I would like to run a validation test on that node (dr site). Will this affect the other 2 nodes which are in synchronous mode?

I would like to run a validation test on that node (dr site). Will this affect the other 2 nodes which are in synchronous mode?

No not the data flow as such. You can run cluster validation of all the resources but storage with no downtime. Although few of MS blogs suggest you can run all cluster validation test but storage on WSFC without downtime, still I would suggest you to run the validation when load is relatively very less.

Please note that when you run validation of shared disks cluster manually failover the disks to each node to check the failover and availability and hence it will cause downtime apart from this all other failover tests are online. I assume your AG is one having local disks attached to each node so I do not think you have to worry about downtime but again run validation when load is relatively very less.

I would suggest you to read Validating a Cluster with Zero Downtime. Quoting from the blog

  Across these dozens of tests, only a few of them will impact running cluster workloads and these are all within the storage category, so skipping this entire category is an easy way to avoid disruptive tests.  Listing All Disks and Potential Cluster Disks will not impact anything.  Validating Disk Access Latency, File System, Microsoft MPIO-based disks, and SCSI device Vital Product Data (VPD) can impact the disk’s performance as tests are performed against the disk which is in use, however they will cause no downtime unless the disk latency becomes so slow that it triggers an alert.  Several tests will actually trigger failovers and move the disks and groups to different cluster nodes which will cause downtime, and these include Validating Disk Arbitration, Disk Failover, Multiple Arbitration, SCSI-3 Persistent Reservation, and Simultaneous Failover.  So if you want to test a majority of the functionality of your cluster without impacting availability, exclude these tests.


SQL Server 2008 - Log file out of control & cant shrink it Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

I am having trouble with my database, the log files is currently 250GB in size, I need to shrink this down somehow.

I have tried backing up the transactional log files, performing a full backup, running various shrink scripts but to no avail.

What are the correct steps to make this happen?

Can I somehow just truncate the whole log file then perform a full backup?

Thank You,
Paul

Do you need point it time recovery?  If not then change the database's recovery model to Simple.

Does the database have mirroring or replication configured?  That can also cause the transaction log to blow up quite large.


Do Log_Slave_Updates in MySQL 5.7 fail the replication in Linux? I have a Master Master replication setup in RHEL for 2 Nodes.One server is acting as Master as well as slave for another Server ( which is also Master and Slave )
Let us say A server and B server

I have enabled log_slave_updates on B server so that all the statements receiving from master ( Server A) should get logged to binary logs of server B .

I was expecting Replication , will get fail because :

         1. Created a Table on Server A.
         2. It get replicated to Server B.

Now Binary log from Slave B will go to Server A and this should break the replication saying "table already exist". but it didn't

What is  the logic here ?

Edit Part :

Server id on both servers are different

ASPECT #1

Please make sure ServerA and ServerB have log_slave_updates enabled.

ASPECT #2

This setup will not break replication because every time a transaction is logged in the binary logs, the server_id of the transaction is written as well.

Let's say server_id=10 for ServerA and server_id=20 for ServerB

So, when you run this on ServerA

INSERT INTO mytable (col) VALUES (1);

The transaction will include server_id 10. When ServerB looks into its relay logs and sees server_id 10, it will compare that with its own server_id (20). Since there is no match, it is OK to execute.

ServerB records that transaction in its binary logs. ServerA will pick that transaction up from its relay logs. When ServerA looks into its relay logs and sees server_id 10, it will compare that with its own server_id (10). Since there is a match, it refuses to execute.

I discussed this before in my past answers

Mar 27, 2012 : Master-Master Replication in MySQL
Oct 15, 2012 : Will changing server_id on master break replication?


SQL Server authentication failed after server promote we recently promoted a Windows 2008R2 server to domain controller.
We then found that it hosted a SQL Server 2008R2 Express database instance.
If we try to login to the DB with the domain admin credentials we get a login error 916.
We are also unable to enable the 'sa' user due to error 15151.

Any help will be greatly appreciated.
Alex.

Found it!
I was able to create a privileged user while putting the DB in single-user mode.
Here is the link: http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/


How to copy a SQL Agent Job on same Server? I want to make a few duplicate copies of an existing SQL Server Agent Job on the same server and edit them to do the same action on different databases that exist on the same server.

These are multiple step agent jobs which would be a bit more work to recreate manually. I know I can create a script from an agent job to run on a different server, but I don't think that will work on the same database server. How can I achieve this without recreating the agent job from scratch and without replacing the existing job?

Ultimately I want to end up with about 5 duplicates of the original job...

I'm certain that this isn't the most elegant solution, but it will work. You'll need the dbatools PowerShell module, which you can install from the PSGallery with install-module dbatools or via the other methods at https://dbatools.io/install

Once dbatools is installed, from a PowerShell prompt/console, run the following:

Get-DbaAgentJob -SqlInstance YOURINSTANCE -Job "YOUR_SOURCE_JOB_NAME" | Export-DbaScript -FilePath c:\tmp\MyJobToCopy.sql

You can then open up that SQL file in Management Studio, change every instance of YOUR_SOURCE_JOB_NAME to the "new" name you want the copy of the job to have, and execute the SQL. Lather, rinse, repeat for the other 4 copies you need.

This could definitely be better as a self-contained script to do the name changes and query execution all in one go. But for a one-off, this will get you where you need to go.

If you were copying this job out to 5 different instances with the same name everywhere, it'd be a lot cleaner & easier thanks to Copy-DbaAgentJob (also from dbatools).


Define block device of an ASM disk I have a bunch of asmdisks:

/dev/oracleasm/disks/DATA01

/dev/oracleasm/disks/DATA02

..

/dev/oracleasm/disks/DATA07

Probably, they were created as:

oracleasm createdisk DATA01 /dev/sda1

oracleasm createdisk DATA01 /dev/sda2

But I'm not sure.

How to define relied block device for created ASM disk?
How to be sure that DATA01 is made of /dev/sda1?

Newer versions of ASMLib support oracleasm querydisk -p DATA01 which displays the underlying physicaldisk.

oracleasm-querydisk

Example:

# oracleasm querydisk -p SSDDATA1
Disk "SSDDATA1" is a valid ASM disk
/dev/sdl1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/sdar1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/sdbn1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/sddf1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/mapper/mpathqp1: LABEL="SSDDATA1" TYPE="oracleasm"
#

Older versions may not have the -p option and they display only the major and minor numbers which you can use to find the physical disk:

Mapping ASM disks to Physical Devices

Example:

[root@orcldb2 ~]# /etc/init.d/oracleasm querydisk VOL1
Disk "VOL1" is a valid ASM disk on device [8, 97]

[root@orcldb2 ~]# ls -l /dev | grep 8, | grep 97
brw-rw----   1 root disk     8,      81 Nov  4 13:02 sdg1

This example I copied from the above blog post seems to be not totally accurate (grep 97 returns minor number 81?), the concept however works.


Insert on duplicate key update using MongoDB Can somebody tell me how I should build MongoDB "updateOne" query to get analog of the following SQL query:

INSERT INTO `collection`
(`hash`, `total`, `base_fare`)
VALUES ('aaa', 200, 150)
ON DUPLICATE KEY UPDATE
`base_fare`=IF(VALUES(`total`)<`total`,VALUES(`base_fare`),`base_fare`),
`total`=IF(VALUES(`total`)<`total`,VALUES(`total`),`total`);

What I tried:

db.collection.updateOne(
    {"hash": "aaa", "total": {$lte: 200}},
    {$set: {"hash": "aaa", "total": 200, "base_fare": 150}},
    {upsert: true}
)

But this query insert new document into the collection if existed document with hash "aaa" has field "total" greater that 200. I understand that this is default behavior.

Is any solutions to get behavior equal to MySQL's INSERT ON DUPLICATE KEY UPDATE?

Thanks

As per MongoDB documentation here Since upsert:true the document is inserted based on the filter and update criteria.

As I am able to see that you are using  { upsert: true } in query statement.

For example:

Update with Upsert

The restaurant collection contains the following documents:

{ "_id" : 1, "name" : "Central Perk Cafe", "Borough" : "Manhattan", "violations" : 3 },
{ "_id" : 2, "name" : "Rock A Feller Bar and Grill", "Borough" : "Queens", "violations" : 2 },
{ "_id" : 3, "name" : "Empire State Pub", "Borough" : "Brooklyn", "violations" : "0" }

The following operation attempts to update the document with name : "Pizza Rat's Pizzaria", while upsert: true :

try {
   db.restaurant.updateOne(
      { "name" : "Pizza Rat's Pizzaria" },
      { $set: {"_id" : 4, "violations" : 7, "borough" : "Manhattan" } },
      { upsert: true }
   );
} catch (e) {
   print(e);
}

Since upsert:true the document is inserted based on the filter and update criteria. The operation returns:

{
   "acknowledged" : true,
   "matchedCount" : 0,
   "modifiedCount" : 0,
   "upsertedId" : 4
}

The collection now contains the following documents:

{ "_id" : 1, "name" : "Central Perk Cafe", "Borough" : "Manhattan", "violations" : 3 },
{ "_id" : 2, "name" : "Rock A Feller Bar and Grill", "Borough" : "Queens", "violations" : 2 },
{ "_id" : 3, "name" : "Empire State Pub", "Borough" : "Brooklyn", "violations" : 4 },
{ "_id" : 4, "name" : "Pizza Rat's Pizzaria", "Borough" : "Manhattan", "violations" : 7 }

The name field was filled in using the filter criteria, while the update operators were used to create the rest of the document.


Postgres permission for EXPLAIN I would like to give user a permission to perform EXPLAIN but not to execute the SELECT itself. Is this doable in PostgreSQL?

Create a function like the one below;
    CREATE OR REPLACE FUNCTION public.explain_this (
      l_query text,
      out explain text
    )
    RETURNS SETOF text AS
    $body$
    BEGIN
      RETURN QUERY EXECUTE 'explain (ANALYZE FALSE) ' || l_query;
    END;
    $body$
    LANGUAGE 'plpgsql'
    VOLATILE
    RETURNS NULL ON NULL INPUT
    SECURITY DEFINER
    COST 100 ROWS 1000;

And then arrange privileges;
  ALTER FUNCTION public.explain_this(l_query text, out explain text)
    OWNER TO some_user_with_select_only_privileges;

  GRANT EXECUTE
    ON FUNCTION public.explain_this(l_query text, out explain text) TO user_to_execute;


How to decrease sql server backup time I have a 200 GB SQL Server database (including a file stream) which is taking more than 20 hours to backup on a nightly basis. What is the most cost effective way of decreasing this time?
I could add more hardware. Are backups IO, CPU or memory bound?
Or do I need to isolate the database which is currently on shared infrastructure?
Or can I make some coding changes to remove file stream data into blobs?

Referencing the post Backup to multiple files for faster and smaller SQL Server files

Problem

Have you ever wished you could get your backups to run faster? Well there may be a way, by writing to multiple files. Creating SQL Server backups is pretty simple to do; you can use the SQL Server management tools or you can use T-SQL commands to issue the backup. But sometimes with large databases it takes a long time to run and it is difficult to copy this very large file across the network or even to your backup tapes.

Solution

Write your database backup to multiple files. In addition to writing your database backup to one file you have the ability to write to multiple files at the same time and therefore split up the workload. The advantage to doing this is that the backup process can run using multiple threads and therefore finish faster as well as having much smaller files that can be moved across the network or copied to a CD or DVD. Another advantage to writing to multiple files is if you have multiple disk arrays you can write your backup files to different arrays and therefore get better I/O throughput. One thing to note is that to maintain the I/O throughput you should keep the writing of your backup files and the reading of the database file on different disk arrays.

Backup to multiple files using T-SQL

BACKUP DATABASE [Northwind] TO
DISK = 'C:\Northwind_file1.bak',
DISK = 'D:\Northwind_file2.bak',
DISK = 'E:\Northwind_file3.bak',
DISK = 'F:\Northwind_file4.bak'
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT

A 200gb database is not really that large and (in my opinion) shouldn't take anywhere near 20 hours.  I would also do some testing on your backup target latency without even dealing with SQL Server.


Error in "BCP IN" operation I have a table in 2 different database.

In Database_A, there is a table created as:

CREATE TABLE tbl_Employee(
    E_Id int null
    ,E_Name varchar(24) not null
    ,Joining_Date datetime not null
    )

In Database_B, the same table is created as:

CREATE TABLE tbl_Employee(
    E_Id int null
    ,E_Name varchar(24) not null
    ,Joining_Date datetime null
    )

The difference is, the column Joining_Date is NOT NULL in Database_A but it allows NULL in the Database_B.

Now when I export data from the table Database_A.dbo.tbl_Employee
using command

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -n -T

and import into Database_B.dbo.tbl_Employee using command

bcp "Database_B.dbo.tbl_Employee2" in "D:\test.txt"-S Server_Name -n -T

It raises an Error:

  SQLState = S1000, NativeError = 0
  Error = [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in
  BCP data-file

I can not alter the column to NOT NULL on Database_B

Try using with -c which is character instead of -n native

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -c -T

bcp "Database_B.dbo.tbl_Employee" in "D:\test.txt"-S Server_Name -c -T

Or an alternative is to create a format file.

Create format file syntax is:

bcp Database_A.dbo.tbl_Employee format -null -f D:\BCPFormat.fmt -n -T

Export as normal with native:

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -n -T

Import with -k keeps nulls and format file, no -n flag needed.

bcp Database_B.dbo.Employee in "D:\Test.txt" -S Server_Name -f D:\BCPFormat.fmt -k -T


Is it normal to constantly have 30+ connections to ‘master’ database in MS SQL Server 2012? In troubleshooting why I was seeing so many connections to one of our internally used databases in SQL Server 2012.

A query I found online that had been helpful to me was this:

 SELECT DB_NAME(sP.dbid) AS the_database
         , COUNT(sP.spid) AS total_database_connections
 FROM sys.sysprocesses sP
 GROUP BY DB_NAME(sP.dbid)
 ORDER BY 1;

However, although after fixing an issue, I've seen connections to our own custom database go back to normal levels, I'm always seeing the number of connections to the master database always at over 30 connections.

Is this normal for SQL Server 2012?  Anyone else getting 30 or more connections to master when running this query?

30 system processes is absolutely not unheard of, and actually sounds better than normal.

However, you should not be using sysprocesses, which is deprecated and only exists for backward compatibility reasons - see the important note right at the top of the doc. (If you're using it solely for the database column, see this recent question, and the comments.)

You should be looking at sys.dm_exec_sessions - this view has a column called is_user_process that allows you to identify your processes and distinguish them from system processes (the old < 50 trick is no longer reliable on modern versions of SQL Server).

Just as an example, on my local dev machine - where I can assure you nothing is going on - I have the following rows:

sys.sysprocesses                                    62
sys.dm_exec_sessions                                68
sys.dm_exec_sessions where is_user_process = 1      26

The majority of those 26 user processes are just connections to the instance (which is on a VM guest) held open by Visual Studio Code on the host.

The other 42 are normal, sleeping, background system processes like LOG WRITER, LOCK MONITOR, XE DISPATCHER, 'CHECKPOINT', etc. It's not like you can get rid of these.

You could also compare the output of the undocumented sys.sp_who2:

EXEC sys.sp_who2;                       -- 64 rows in my case
EXEC sys.sp_who2 @loginame = N'active'; -- 40 rows in my case

Or limiting to actual running processes in sys.dm_exec_requests.

But for deeper insight into what processes are doing on your system, you're better off using something like Adam Machanic's sp_whoisactive or a proper monitoring tool (disclaimer: I work for one of those).


Migrate existing SQL Server database file to smaller files I have a database that is about 1TB in size. It's currently on a 2TB hard drive and I would like to replace it with a few 512GB SSD. I need to split the existing database file (.mdf) into multiple smaller files with file size limited at just below 512GB each.

Ive been doing some research online. It is not clear to me whether I should add filegroups or just add files to my primary filegroup.

How do I move data from the existing file (1TB) to the smaller files (512GB) safetly?

Has anyone done anything similar?

Just adding files to the existing file group will not redistribute the existing data.

I would go the route @Max is suggesting and ensure that the new files you are creating are the same size to maximize the proportional fill feature. And before you begin take a full backup.

Once you've migrated all of the tables, indexes, etc over to the new file group you should be able to shrink the mdf down to be under the 512GB point or smaller if you are not going to use the default file group anymore.

At that point take another full backup and then restore that to the new disks with the MOVE TO option in the restore command. This will let you take the new ndf files in the new file group and put them across the SSDs.

Depending on how you are managing the database you might want to set the new file group to be the default for all of the new objects that are created.  Otherwise they will still default to the old file group and since it only has one file in it the objects/data in it won't benefit from the proportional fill feature that SQL Server has.

Moving file groups reference


Select only those records that have different/multiple values for a particular column Below is an example of my membership table. There some records having multiple values in the email field. I only want to select those records that have multiple email values:
Member table
ID   LASTNAME    FIRSTNAME    EMAIL
567  Jones       Carol        carolj@gmail.com
567  Jones       Carol        caroljones@aol.com
678  Black       Ted          tedblack@gmail.com
908  Roberts     Cole         coleroberts@gmail.com
908  Roberts     Cole         coler@aol.com
908  Roberts     Cole         colerobersc@hotmail.com

I would like the result to be:
567  Jones       Carol        carolj@gmail.com
567  Jones       Carol        caroljones@aol.com
908  Roberts     Cole         coleroberts@gmail.com
908  Roberts     Cole         coler@aol.com
908  Roberts     Cole         colerobersc@hotmail.com

Notice that Ted Black is missing because he only has one entry for email address.
I should clarify that my membership table has more than 4 columns. There are additional columns for phone and address, etc. And there could be multiple entries for a member because he/she has more than one phone number or address. I only want to capture those individuals who have multiple email addresses.
This is part of a database cleanup and a primary key will be added. I should clarify further that some persons could have multiple entries with same email address. At this phase I don't want to capture those multiple entries with the same email address but only those have who multiple entries with different email address.

You could do something like:

select distinct x.id, x.lastname, x.firstname, x.email
from t as x
join (
    select id
    from t
    group by id
    having count(distinct email) > 1
) as y
    on x.id = y.Id


How to interpret ternary relationship? I have tried to search online on how to interpret these ternary relationships but haven't found any clear answer. Since I don't understand how to interpret it I am having trouble changing it into relations as well. In an example like this:

How exactly would I interpret this? For instance:

The line with an arrow going into Works_in3 from the Employee entity, is that pointing to the Departments entity or the Locations entity? So is it implying that an Employee can only work in one department or is it implying that an employee can only work in one location or does it imply both?

This looks to me that an employee works in at department at a location since a point in time.  I don’t recognise the diagram type as an ERD so I can’t tell what type of relationships they are but I expect that they are one to many. i.e. the works_in3 table is a link table to facilitate a many to many relationship of employee and department. As for location I expect this is where the employee worked in this department, again representing a many to many relationships, as one location could house many employees performing many roles for departments. But that’s just my opinion.


Restore mysql database with some columns content enclosed by " and another not We have to restore a database in MySQL but some columns have content enclosed by " and another not like in this sample:

00.168.93.70,null,ES,0,htc,33a48a1515b20691,"htc;HTC One (REL4.4.2)",m7,53e4b0f1e733a4a95e,-Free-2.1.0.6.f,4.4.2,unknown,13,"Internet app",com.app.android.free.debug,null,"HTC One",test@gmail.com,"31.5000,34.7500",APA91bE1E-7-tO1fgbYCs5kbiUapri1zm5Bda5ehyXmTgTOabx5GQHprbYhfB62w0BgdJ7Nbe3qevehFmhCmGXwArSkCr8qpyjoL_WIGq7IA0tsyC4pmpxT0RO9gJ2K3ErfaMQevHviUR4ZJrm_,"en_EN,zh_CN"
unknown,unknown,unknown,0,htc,33a48515b20691,"htc;HTC One (REL4.4.2)",m7,5385a31e733a4a95f,Free-2.1.0.6.f,4.4.2,unknown,0,unknown,com.app.android.free.debug,unknown,"HTC One",test@gmail.com,unknown,APA9zm5Bda5ehyXmTgTOabx5GQHprbYhfB62w0BgdJ7Nbe3qeveAtIxxi0X3mCr8qpyjoL_WIGq7IA0tsyC4pmpxT0RO9gJ2K3ErfaMQevHviUR4ZJrm,

How Can I restore it properly ?

LOAD DATA INFILE 'x:/folder/filename.csv'
INTO TABLE tablename ( field1,    -- load value into the field directly
                       field2,    -- when it is enclosed, d-quotes are saved
                       ... ,
                       @dummyN,   -- load value with wrapping into UDV
                       ...)
SET fieldN = TRIM(BOTH '"' FROM @dummyN) -- extract value from UDV and store into the field

This allows to remove d-quote chars when they are wrappers and store when they're a part of value, if this occures at the same time (for different fields).


Prevent Firebird's default instance from starting I want temporarily to use another named instance. Can I prevent the default instance from starting when the PC starts? I am using Windows 8.1

There are two options

Manually change the startup type of the service
Remove (and optionally reinstall) the service

Manually change the startup type of the service

You can disable it in the Services configuration. Use Windows-key+R or open run from the start menu, enter services.msc, find the Firebird instance (Firebird Server - DefaultInstance), right click, Properties and change the Startup type from Automatic to Manual:

Remove (and optionally reinstall) the service

You can also remove the service entry completely, to do this, you need to start the command prompt as Administrator. Under Windows 10 this would be Windows-key+X, A. I believe this doesn't work under Windows 8.1, there you may need to use Start, search for cmd or Command prompt, right click and select Run as Administrator.

In the command prompt go to the location of your Firebird install (or for Firebird 2.5 and lower, its bin-folder), and run

instsvc stop
instsvc remove

If you want to have the service entry, but just not automatically started, you can reinstall it with:

instsvc install -demand

This will install the service with startup type Manual (just using instsvc install or instsvc -auto will install it as Automatic); see instsvc for more information.


IBM Bluemix SQL Database service data store management I'm working on IBM Bluemix SQL Database service with premium plan. According to our application the sql service can have many schemas.

Is there any way to seperate data storage of each schema to different physical storage and manage them as individual(file, file group etc.)

For example: The tables and data on SchemaUser1 stored in SQLDBFile1 and SchemaUser2's tables and data stored in SQLDBFile2 Is it possible to create schemas like this? or the only way to separate datastore is creating a new sql database service.

Unfortunately, schemas have no clue about their physical background such as tablespaces; they mean only logical namespaces.
But it does not mean you cannot do the schema->tablespace relation yourselves by using the IN tspacename clause in create commands provided that the referred tablespaces already exist.


Postgresql Master/Slave Using Patroni Can I promote a slave node to be a master for insert/update/delete using Patroni once the original master goes down?

The whole point of Patroni is that you don't have to do that yourself, but that Patroni does it for you automatically.

The nodes communicate regularly via a distributed consensus store that decides who is online and who isn't, and arbiters who of the available nodes should become the leader.

You can also tell Patroni manually to switch leader, even if all nodes are up (a "switchover").


Find if any of the rows partially match a string I want to see if a table contains any sub-string of a given string.

Let's say I have a string

somedomain.com

In database I have:

blabladomain.com
testdomain.com
domain.com

I need to make a query that will return "domain.com" as it is a substring of "somedomain.com".

I don't know if this is even possible in MySQL.

CREATE TABLE `site_core_antispam_banned_domain` (
`domain_id` int(11) NOT NULL AUTO_INCREMENT,
`domain_name` varchar(255) NOT NULL,
PRIMARY KEY (`domain_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You can use LOCATE() – as in the other answer – or LIKE:

SELECT *
FROM site_core_antispam_banned_domain
WHERE 'subdomain.com' LIKE CONCAT('%', domain_name, '%') ;

The above will look for domains that match any part of a given string. If you specifically wanted domains matching the right side of the string (for instance, if the domain to match against was somedomain.com.ro and you were only interested in *.com.ro results), you could make the pattern more specific:

SELECT *
FROM site_core_antispam_banned_domain
WHERE 'subdomain.com' LIKE CONCAT('%', domain_name) ;


how to replicate articles from different schemas? from sp_addarticle (Transact-SQL)

I get an example as how to add an article to a publication.

this adds the table dbo.Product to the publication AdvWorksProductTran

DECLARE @publication    AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @table = N'Product';
SET @filterclause = N'[DiscontinuedDate] IS NULL';
SET @filtername = N'filter_out_discontinued';
SET @schemaowner = N'Production';

-- Add a horizontally and vertically filtered article for the Product table.
-- Manually set @schema_option to ensure that the Production schema
-- is generated at the Subscriber (0x8000000).
EXEC sp_addarticle
    @publication = @publication,
    @article = @table,
    @source_object = @table,
    @source_owner = @schemaowner,
    @schema_option = 0x80030F3,
    @vertical_partition = N'true',
    @type = N'logbased',
    @filter_clause = @filterclause;

How would I do if I had also the following tables from different schemas to add to this publication?

I want to add these two tables to the publication, how do I do it using sp_addarticle?

my_schema01.Product
my_schema02.Product

The meta-answer to this is to use SSMS to publish the article and examine the replication scripts.  The answer is that the @source_owner parameter to sp_addarticle has the target schema.  This dates from before schema/owner separation in SQL 2005, where an object owner and a schema were the same thing.

And the script you list uses does not publish dbo.Product, it publishes Production.Product


Is a Named Instance more Secure than the Default Instance? I realize that you can change the default instance from the default port of 1433, and equally you can make named instances use static ports as opposed to dynamic ports, but in general are named instances more secure than the default instance against external intruders?

Here's why I would think that's the case:

Named instances are (again, by default I realize) utilizing dynamic ports.  If you move the SQL Browser service to listen on a different port than 1434 then that would be even better, I imagine.
A default instance is pretty easy to figure out as the data source.  It's just the server's name.  But the named instance is an extra step to retrieving the data source with the serverName\instanceName schema.

What are some thoughts on this?  Am I way out in left field and it makes no difference?

Not really.

The SQL Browser service is always port 1434: can't be changed. So you need to know the port first before you disable (can't resolve it). Clients then have to know the port which can be packet sniffed.

You can make any SQL Server more secure my enforcing SSL encryption, reducing Surface area, network zoning, proper Group Policy and SQL Server security etc. But not by using Named Instances


MySQL 5.7 statistics - Change db I have a MySQL 5.7 database. I was looking in statistics in server status that shows type of queries / commands / tasks executed in the database. It shows that the query command "change db" by far was the most frequent one.

What does the "change db" value mean in Server > Status > Statistics? I only have one database (except system databases) why is the query running so often, almost twice select?

You are probably using a 3rd party package.  And it probably does a "USE" at the beginning of every connection, possibly much more often.

The frequency of SHOW commands also smells like a sloppy 3rd package that can't remember what it is working with.

Another possibility is a monitoring package.  However, I would expect to see even more SHOWs.

One way to track things down is to turn on the "general log" for a short while, then look at that log to see which "user" is doing what.


Enforcing distinct number of trailing spaces I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to Microsoft SQL Server.

Existing is a table with a primary key clustered on two columns, code_type char(4) and code char(30). I was able to build the table fine, but attempting to move over the data I ran into a conflict because the table contained ('cha', ' ') and ('cha', '') and speaking with another developer it sounds like this has a use case. Not really looking to retrain the users, what options are there to enforce a distinct number of spaces at the database level?

The database is for a single application so in the end I can rely on control of how data is inserted into the table but would like to enforce an intended distinction in the table definition.

If ('cha', '') exists in the table, I'd like to be able to insert ('cha', ' ') as it has more trailing spaces, but not ('cha', '') because it has an equal number of spaces (0) as an existing entry.

This question on UNIQUE Index outlines the behavior on unique index, SQL is applying right padding of spaces to make two strings equally sized before making the comparison per ANSI/ISO SQL-92 Section Section 8.2 General Rule #3. I've confirmed this behavior with the char type as the link used varchar. I'm not committed to using a key, just some way to enforce a distinct number of trailing spaces.

There are quite a few characters guaranteed not to show up in the second column. The field is either letter abbreviations or legal document citations. The legal document citations seem to be contain alphanumeric characters plus '(', ')', '-', '.', ' ', and '/'.

I wouldn't call this a good solution but it seems to work. You can create a computed column with an appended character and create a unique index on code_type and the computed column. For the appended character you can use a character which will never appear in your data or any string of at least 31 character length if you'd like to avoid making assumptions like that. It's probably easiest to go through an example.

This is the problem that you're having:

CREATE TABLE dbo.T226714 (
    code_type CHAR(4) NOT NULL,
    code VARCHAR(30) NOT NULL,
    PRIMARY KEY (code_type, code)
);

-- fails due to PK error
INSERT INTO dbo.T226714 VALUES ('cha', ' ');
INSERT INTO dbo.T226714 VALUES ('cha', '');

Instead, do something like this:

DROP TABLE dbo.T226714;

CREATE TABLE dbo.T226714 (
    code_type CHAR(4) NOT NULL,
    code VARCHAR(30) NOT NULL,
    code_with_end_char AS CAST(code + 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS VARCHAR(61))
);

CREATE UNIQUE INDEX UI ON dbo.T226714 (code_type, code_with_end_char);

-- works
INSERT INTO dbo.T226714 VALUES ('cha', ' ');
INSERT INTO dbo.T226714 VALUES ('cha', '');

-- can't insert this as a third row due to the unique index:
INSERT INTO dbo.T226714 VALUES ('cha', ' ');


In Oracle, how to get a list with the names of schemas that a user has read permission? In Oracle, i need list which schemas a particular database user (or the current database user) has read permission.

Based on this information, also need to get all the tables and views are in each schema, provided that the particular database user has read permission.

you can use the following command if you want to see the users that has X privilege for example SELECT ANY TABLE privileges

SELECT * FROM USER_ROLE_PRIVS WHERE GRANTED_ROLE like '%SELECT ANY TABLE%';


Cursor to insert .pdf files into a table I need to insert all .pdf files from a folder, in a table, according to its number.

I could get a list of files ( I have all .pdfs inside a folder ):

exec master.sys.Xp_dirtree 'The_Folder_where_PDFS_are',0,1

And I could insert it inside a table:

CREATE TABLE PDFTest(NumExample integer,image_data VARBINARY(MAX));

DECLARE @NumExample integer
SET @NumExample = 2456789

INSERT INTO PDFTest(NumExample ,image_data)
SELECT @NumExample ,image_data
FROM OPENROWSET(BULK N'C:\DBA\3937052016_Requisição.pdf',SINGLE_BLOB) AS ImageSource(image_data);

Select * from PDFTest

But as you can see, files are named after a number ( 3937052016 ). This number is the ID of another table, so, if the PDF file starts with 123, I need to insert it into the ID 123. I just would like a start point. I'm trying to create a cursor, looping throug all files, and using left() or substring () function to get the number, inserting it into a variable, and comparing it , like insert this pdf into table where left/substring(pdf_name)=id.

I need something like a string like this :

Insert into tablex ( pdffield )
values ( 123_PdfFIle.pdf ( obviously using openrowset )
 where left/substring(123_pdf to get the pdf's code )) = id`

I'm on the right patch? Is there a way to do this with less efforts?

I created a directory called c:\TestPdf and created two txt files with some text in them (you would use your pdf files)

123456789.txt
987654321.txt

From your example, my assumption is you want file 123456789.txt inserted as ID 123 and you want file 987654321.txt inserted as ID 987.

This dynamic SQL worked for me - you would need to test against your data

set nocount on
IF OBJECT_ID('dbo.PDFTest', 'U') IS NOT NULL
  DROP TABLE dbo.PDFTest;
Create table PDFTest (NumExample integer,image_data VARBINARY(MAX))
DECLARE @FileTable TABLE (FileName VARCHAR(max),DepthColumn INT,FileColumn INT)

INSERT INTO @FileTable
EXEC master.sys.Xp_dirtree 'c:\testpdf',0,1

DECLARE @cmd VARCHAR(max)
DECLARE @FileName VARCHAR(max)

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT FileName
FROM @FileTable

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @FileName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = '
INSERT INTO PDFTest(NumExample ,image_data)
SELECT ' + substring(@FileName, 1, 3) + ',image_data
FROM OPENROWSET(BULK N''c:\testpdf\' + @FileName + ''',SINGLE_BLOB) AS ImageSource(image_data)
'

    PRINT @cmd

    EXEC (@cmd)

    FETCH NEXT
    FROM _CURSOR
    INTO @FileName
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR

SELECT *
FROM pdftest


How to influence Oracle backup output size? I have two environment one Prod one Dev. Dev is created by Prod data and both have a similar database size. However, the backup output size varies greatly. Prod is about 300 M while Dev always above 1G. Both using incremental Level 1 backup.

Why would this be happening?

Incremental Level 1 backup in RMAN means all data blocks changed since last Incremental Level 1 or 0 backup (0 is the most basic level, it always contains all data).

If you use CUMULATIVE keyword on Dev, the incremental level 1 contains all data blocks changed since last Incremental Level 0 backup. Which means more of them. One possible explanation.

RMAN only cares whether the block was written to, there are no efforts here to de-duplicate basing on the data. Maybe the Prod received less of such block writes, and Dev more. In particular, if you import into Dev, this definitely counts here. Another possible reason.

Manual says, that unused-never-written-to blocks are always skipped but, in contrast, the unused blocks are skipped only in a very specific set of circumstances:

  The COMPATIBLE initialization parameter is set to 10.2 or higher.
  Note that if COMPATIBLE is 10.2, then only tablespaces created with 10.2 compatibility will be optimized to exclude blocks that do not currently contain data. If COMPATIBLE is 11.0.0 or higher, however,
  then the first backup that produces backup sets after COMPATIBLE is
  set to 11.0.0 or higher will update the headers of all locally managed
  datafiles so that all locally managed datafiles can be optimized.
  There are currently no guaranteed restore points defined for the database.
  The datafile is locally managed.
  (sic!) The datafile is being backed up to a backup set as part of a full backup or a level 0 incremental backup.
  The backup set is created on disk or Oracle Secure Backup is the media manager. Thus, when backing up to a media manager other than
  Oracle Secure Backup, RMAN does not skip datafile blocks that do not
  currently contain data.

So, Dev could simply back up unused blocks, and there are plenty of reasons why Prod would have no symmetry here. Another possible reason.

RMAN will do Level 0 (i.e. full) if it believes you erroneously requested Level 1. Manual says:

  The database performs checks when attempting to create a level 1
  incremental backup to ensure that the incremental backup is usable by
  a subsequent RECOVER command. Among the checks performed are:

  A level 0 backup must exist for each datafile in the BACKUP command as
  the base backup for an incremental strategy. Level 0 backups must not
  have status UNAVAILABLE. If no level 0 backup exists, then RMAN makes
  a level 0 backup automatically.
  Sufficient incremental backups taken since level 0 must exist and be
  available such that the incremental backup to be created is usable.

Especially the last point may be tricky. Another possible reason.


Is this many to many relationship correct? I'm reading this tutorial, and I have question about it.

Look at this picture:

'Reading' the logical Relationship I read that a Project must have one or more Employee. But the physical implementation has that a Project could have zero or more ProjectEmployee.

Or maybe I am wrong and the physical implementation is correct.

What do you think?

The physical model is what is created in most physical implementations of a many-to-many relationship.  However, it does not correctly replicate the rules in the logical model.

The physical model allows for zero or more in both directions.  The logical model indicates one or more from project to employee.

The reason that many physical implementations of any relationships do this is that in many DBMSs the SQL declarative referential constraints (i.e. foreign key constraints) don't allow for the rule to be suspended while data is inserted into two tables at one time.  The SQL standard provides for "deferrable constraints" and some DBMSs implement these, but many don't and the ones that do haven't always, so not everyone uses them, even if they are available.  Old habits can die hard.

For the ones that don't implement deferrable constraints, think about it this way: For there to be any kind of mandatory relationship in both directions (i.e. 1:1,M or even 1:1) then you would have to insert the parent and the child at the same time.  While you can create a transaction that ensures the parent and child are created in one logical unit of work, the referential integrity rules aren't built to "suspend judgement" until the whole transaction finishes.

This is why in practice, physical implementations without deferrable constraints are essentially always optional at the child end.  This lets you insert the parent and then insert the child.

The practical way around this is generally to write additional application logic to ensure that there is at least one child per parent when 1:1,M is the rule or to just leave it and hope that users do the right thing.  These work-arounds aren't necessary if you do happen to have access to deferrable constraints.


Is it acceptable to use deadlocks as a strategy? We recently upgraded our SQL production environment from 2008R2 to 2017. I am finally at a point where I want to try optimistic locking in the form of using snapshot isolation. I read Brent Ozar's article suggesting to SET ALLOW_SNAPSHOT_ISOLATION ON at the database level, and then alter queries to take advantage of the new to us feature. To my surprise, my manager who is very knowledgeable in sql, did not want to move in this direction. I understood him to say that we depended on the deadlocks to prevent us from reading dirty data, and saw no reason to solve a problem that did not exist. I did not know how to respond to him. I have never considered deadlocks as anything other than something to try and eliminate, not depend on as a way of life. I get that they do an important function, but are a sign of something that can be improved.

My question is how many others rely on deadlocks to such an extant that you would not consider trying to eliminate them?

Snapshot and Read Committed Snapshot isolation levels do not prevent deadlocks from happening. They make the engine using row versioning, so readers do not block writers and writers do not block readers as SQLServer does by default. That improves concurrency , but requires changes to the applications that rely on  "readers block writers and writers block readers" . I guess you misunderstood your manager , and your system relies on that SQLServer default blocking behavior, not on deadlocks which in most cases can be avoided by application design.

If I happen to work with SQLServer on a new system, I'll surely switch it to use row versioning instead of locking.

However, for an existing system, it is usually safer and cheaper not to fix what is not broken (especially when it's not broken).


Unable to get output variable of Oracle procedure using Sql linked server I have created a Heterogeneous connection between SQL Server and Oracle and tried to call a oracle procedure from SQL Server.I got below error when i tried to execute the procedure.

  OLE DB provider "ORAOLEDB.Oracle" for linked server "TESTORACLE"
  returned message "ORA-06502: PL/SQL: numeric or value error: character
  string buffer too small ORA-06512: at "TESTUSER.PKG_TEST", line 1744".
  Msg 7215, Level 17, State 1, Procedure SQLSTOREDPROCEDURE, Line 75
  [Batch Start Line 0] Could not execute statement on remote server
  'TESTORACLE'.

I have gone through the code and found that the error occurs only when assigning the value for OUT parameter.

SQL SERVER PROCEDURE

CREATE PROCEDURE [dbo].[SQLSTOREDPROCEDURE]  AS
DECLARE @op_update_status Nvarchar(50)
DECLARE @op_error_message Nvarchar(500)
BEGIN
 SET NOCOUNT ON;
 SET XACT_ABORT ON;
 SET @p_process_name = 'PROCESSNAME'
 EXECUTE ('BEGIN  TESTUSER.PKG_TEST.P_SET_VALUES(?,null,?,?); END; ',@p_process_name,@op_update_status ,@op_error_message ) AT [TESTORACLE];

 RETURN 0;
END

ORACLE PROCEDURE

PROCEDURE  P_SET_VALUES  (   p_process_name      VARCHAR2,
                             p_processed_dt      DATE,
                             op_update_status OUT VARCHAR2,
                             op_error_message OUT VARCHAR2
                         ) AS

 v_update_status VARCHAR2(50);
 v_error_message VARCHAR2(500);

 BEGIN

    USER2.PKG_TEST2.P_SET_VALUE  ( p_process_name,'TEST STATION',p_processed_dt,v_update_status,v_error_message) ;
     op_update_status := v_update_status;
     op_error_message := v_error_message;

END;

Then I googled and alter the procedure by specifying the OUTPUT parameter in SQL Server.

EXECUTE ('CALL  TESTUSER.PKG_TEST.P_SET_VALUES(?,null,?,?)',@p_process_name,@op_update_status OUTPUT ,@op_error_message OUTPUT) AT [TESTORACLE];

After that I get below error,

  Msg 7215, Level 17, State 1, Procedure
  SQLSTOREDPROCEDURE, Line 75 [Batch Start Line 0] Could not execute
  statement on remote server 'TESTORACLE'.

Please help me to overcome this error.

Thanks.

UPDATE

Even though Sql server return a error message, oracle procedure was called. Checked with dummy insert in application log table.

You have wrong string length in your SQL Server proc, look at this:

v_update_status VARCHAR2(50);
v_error_message VARCHAR2(500);

DECLARE @op_update_status Nvarchar(50)
DECLARE @op_error_message Nvarchar(500)

Original oracle parameters are of length 50 and 500 bytes, but you use NVARCHAR and your SQL Server variables are 100 and 1000 bytes long (UCS-2 uses 2 bytes to store each symbol)

Update: I've found Oracle data types and saw that varchar2 is not unicode data type, Oracle uses nvarchar also, so all what you need is to change your Nvarchar type to varchar in your SQL Server code


MySQL UNION with Multiple ORDER BY I have tried to make this work in multiple forms but none have been successful. I need to read an int from my table to order my data as desired. I have previously gotten this to work as multiple queries but Im hoping to do it all in one now.
    SELECT * FROM (
        (SELECT * FROM table_a WHERE int_a <= *myint* ORDER BY int_a DESC)
        UNION
        (SELECT * FROM table_a WHERE int_a >= *myint* ORDER BY int_a DESC)
    ) uniontable

MySQL 5.7

I'm not exactly sure what you re trying to achieve, but if you want the rows from the first leg to be ordered first, then you can add an attribute that determines from which leg the row belongs:
select myint from (
    select 1 as origin, myint from table_a where myint < 5
    union
    select 2 as origin, myint from table_a where myint > 5
) as t
order by origin, myint desc;

myint
4
3
2
1
9
8
7
6

Fiddle


Updating and/or inserting several rows in the same SQL statement I am implementing an account "ability" type thing - a users account is given the abilty to do one or many things in the system, like (simple examples) cancel orders or pay invoices.  I have a table like this:

ac_id     ability        active
  1        CANCELORDER      Y
  1        PAYINVOICE       Y
  1        EDITOREDR        Y
  2        PAYINVOICE       Y
  2        PAYWAGES         Y
  2        CANCELORDER      Y

ac_id is a link to the account table.

When a user account is first created with some abilities it is easy to insert the abilities into the table with an insert statement.

When a user account's ability list is changed updating seems to be tricky.  Here's what I think needs to be done:

list      DB
ABC       ABC
GHI       DEF
JKL       JKL
MNO       MNO

If list contains the new list of abilities for an account and DB contains the current list for that account we need to

remove DEF from the DB list because it is not in the new list.
add GHI into the DB list.

The first is straightforward SQL like:

update userabilitytest set
  active = 'N'
where
  account_id = 1 and
  active = 'Y' and
  ability not in (''ABC', 'GHI', 'JKL', 'MNO' )

(which sets the active flag to 'N' rather than actually deleting).

However, I can't think of a way of doing the 2nd part. I have it working by figuring out whether the ability is already in the DB for that user in my application code, which does insert for each not-yet-in-the-db ability.

It would be nice to push this into SQL and run on the server.

Any ideas?

Having been pointed in the right direction by @Phil, it looks like the following merge will work:

MERGE userabilitytest
USING (VALUES
        (1, 'EDITACCOUNT'),
        (1, 'EDITORDER'),
        (1, 'BOOHOO'),
        (1, 'FooBar')
        )
      AS foo(account_id, ability)
ON
  userabilitytest.account_id = foo.account_id and
  userabilitytest.ability = foo.ability and
  userabilitytest.active = 'Y'
WHEN MATCHED THEN
  UPDATE SET ability = foo.ability
WHEN NOT MATCHED THEN
  INSERT (account_id, ability)
  VALUES (account_id, ability);

Being able to use a "values" list and ascribing it to "foo" makes all the difference - a table with the new values in is not actually necessary.

Also, some sort of statement where UPDATE is is needed, but the one that is there at the moment does nothing as ability is matched in the ON part. Commenting the UPDATE statement out causes an error.


DB Structure: one table to hold all domain values? One database I recently start working with was designed with one main table containing all possible values, and foreign keys in other tables are specifying the nature of each of those items.

CREATE TABLE entity
(
     indexid INTEGER PRIMARY KEY NOT NULL,
     entitytype INTEGER,
     entityname VARCHAR,
);

For example, this main table contain a field named 'entityname' possibly containing records such as:

the upc code of the item
the short name of the item
the long name of the item
the location of the item
the vendor for this item
the quantity in stock
(and many others)

Each record in this table got a entitytype number as a foreign key referring in another table what each records mean, such as

entitytype=10 mean that this entity is a UPC code type, and then

entitytype=12 mean that this entity is a vendor name type, etc...

Obviously there is dozen of such types for entities.

I feel like it is a bit unintuitive. There is this post here (See One table to hold all domain values section) where the author suggest the approach as I expressed it in my example may not be the best way to do this, and I would tend to agree with his opinion on the matter.

I also think it is difficult to make efficient queries, and I feel it is not how this should be done. I would rather have more tables and keep the whole more intuitive. I have a time window where I could redesign this and was thinking about long term and best practices.

So can this approach be considered a good practice in the long term and I just need to deal with it?

That design structure is commonly referred to as entity-attribute-value, or EAV.  Some people hate it, others see the inherent beauty in its design.  There is nothing wrong with the design, as long as it's carefully thought out, and implemented properly.

Some of the more common "problems" people associate with EAV are:

Good chance for poor performance
non-intuitive design leading to difficult application maintenance
virtually impossible to reliably enforce referential integrity

Good references abound:

https://softwareengineering.stackexchange.com/questions/93124/eav-is-it-really-bad-in-all-scenarios
Attribute and Value tables on SQL - Is this a good practice?

Porting a database from EAV structure to a more typical relational design will be very involved.  Any and all code that touches the data will likely need to be changed.  You could use triggers on the existing tables to allow you to slowly port code from the EAV model into a relational model, or possibly you could add a series of views over the re-modeled data to support reporting or other read-only aspects of the client application once you convert to a relational model.


Databases created with different collations - why? We have a SQL Server 2014 instance, which houses many related databases. New ones are added via our script regularly. When our app is used to create the database - the databases are created with collation SQL_Latin1_General_CP1_CI_AS  as expected.

But there are now databases with Latin1_General_CI_AS collation. They were not created by our app (we looked at the database creator). For example, "Pete" is the db owner and some DBs he created would have Latin1_General_CI_AS collation and others SQL_Latin1_General_CP1_CI_AS.

Can anyone point out what I could investigate please - should the default collation not always be the same as the SQL instance?
What if I created a database remotely - from PC1 with Collation1 and PC2 with Collation2 - would the database be created with different collations?

Any ideas how this could happen - multiple collations for one user?
Thanks

The COLLATE clause of CREATE DATABASE can be used to specify a collation different from the Default Server collation.  Also, if the databases were backed up from another server, they would be restored with the collation they had on the source server.

If they are created using CREATE DATABASE and a collation is not specified via the COLLATE keyword, then the default collation would be used.


How to install TokuDB on MAMP I'm by no means a DB admin. Just a hobbyist actually, and more of a programmer than a database person. I have MAMP 3.05, PHP 5.5.10, Mac OSX 10.9.3. I have a big suite of PHP applications and a gigantic database (several tables with over 1M records) using InnoDB. I hear that TokuDB is a big performance improvement over InnoDB, so I'm trying to install it, and having some trouble.

After some install steps, the doc tells me to start the new mysql instance using a command called service. This command is not present on my machine. Looking around, it seems that most linux installations have this command, but mine doesn't, and I can't figure out where to get it. Plus this idea of starting a new instance from the command-line is bothering me: I sort of thought that I could install this and continue to use MAMP as I always have, letting it start the mysql server, rather than me having to start it manually.

What is the correct way to install TokuDB to work with MAMP?

Edit: In my terminal window, uname -m reports x86_64.

According to the official Tokutek documentation, TokuDB is only available for 64-bit Linux.


MySQL select * from table without conditions does not return rows right away I have a table with about 4 million rows and a bunch of columns on each row and I'm doing a simple select * from table in the mysql client and it doesn't return data right away. A count(*) of the same table returns a count in 3 seconds.  I'm using the windows command line mysql client mysql.exe, running on AWS.  I'm running a simple select * from the table created by the create table as select.  No where clause conditions, no indexes, just a flat table with 4 million rows and a bunch of columns in a row. It seems like some sort of processing of the output is happening before it shows the first row. I'm new to MySQL so I'm trying to understand the behavior.

mysql> select count(*) from test;
--------------
select count(*) from test
--------------

+----------+
| count(*) |
+----------+
|  4074384 |
+----------+
1 row in set (2.96 sec)

mysql> select * from test;
--------------
select * from test
--------------

Does anyone know how to get the output to start streaming right away?

Edited version of create table as select:

create table test as
select
pimxref.ap_ref,
...
        AND ipm.product_status = 'A'
--------------

Query OK, 4074384 rows affected (2 min 35.67 sec)
Records: 4074384  Duplicates: 0  Warnings: 0

Python script that runs the same query returns results instantly:

import mysql.connector
cnx = mysql.connector.connect(user='x', password='x',
                              host='x',
                              database='x')

cursor = cnx.cursor()

cursor.execute("select * from test")

for row in cursor:
    print(row)

cursor.close()
cnx.close()

If someone knows an option for the Windows mysql client to make it behave in the same way it would be great.

p.s. This is the version I am running:
Server version: 5.7.17-log MySQL Community Server (GPL)

What language are you using in your client?  What connector?  There may be a setting there.

Guessing that it is python, I see this:

https://dev.mysql.com/doc/connector-python/en/connector-python-api-cext-buffered.html

and guess that this would do the trick:

    cnx.buffered(True)

before the lengthy SELECT *.  It is not likely to help SELECT COUNT(*) since the is nothing to 'buffer' until it is finished.

Since you added details about using the Windows cli mysql.exe utility, See if mysql --quick achieves your goal.  If that doesn't provide the speed you require, perhaps consider using a loop, with a LIMIT clause, and "remember where you left off".  This would make each call to mysql.exe relatively quick, at the expense of having to call it many times, once for each window of rows.


SQL Server 2014 - Add Node inherits wrong configuration I'm setting up a simple 2-node failover cluster with SQL 2014 on Server 2012 R2 in Core mode (no GUI). I have the cluster configured with identical machines, and I have the first node configured and working.

When I go to add the second node however, the wrong configuration information is used. Specifically, the shared and instance binary locations point to the OS drive (I installed them on a different drive on node 1). I tried manually specifying the locations in the configuration file, but setup won't accept these parameters when the install mode is AddNode. Did I do something wrong, or is this a bug?

It appears to be a bug/feature. It was discussed previously on SQL Server Central http://www.sqlservercentral.com/Forums/Topic1578488-2799-1.aspx where Ed Vassie said:

  Put it another way... On an ADDNODE install you cannot specify the
  location for the SQL binaries, they always go to the system drive.

Ed Vassie is a reliable source as the original developer of FineBuild, a tool specifically for rolling out repeatable installs of SQL Server including in clustering mode. He lives and breathes this part of SQL Server.


SQL Server database forum design sample I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find any free samples to compare my work to it.

As some people requested in the comments, this is what I did so far: For a Forum, I considered a User (Table), each user have a post (Table) and each post can start a topic (Table).

Table User : UserID (PK), Nickname, Email, Password, AvatarImage.

Table post : PostID (PK), UserID (FK), DateTime, Description.

The question that I don't have an answer for:-

How can I provide the possibility that a post can start a topic? Should I make a new table? what columns? or something can be done with the post table with the help maybe of stored procedures?
How can I make a topic after a topic (like answering a question)?

You might have something like this. If the post is a new topic, ParentID and ReplyToID would be NULL. For forums it is often desirable to show when a post is a reply to another post, but still indicate which topic it belongs to, so a second column (ReplyToID) can indicate this. When it is a reply to the original parent this can be NULL or have the same value as ParentID.

CREATE TABLE dbo.Posts
(
    PostID      INT IDENTITY(1,1) PRIMARY KEY,
    UserID      INT NOT NULL FOREIGN KEY REFERENCES dbo.Users(UserID),
    ParentID    INT NULL REFERENCES dbo.Posts(PostID),
    ReplyToID   INT NULL REFERENCES dbo.Posts(PostID),
    Content     NVARCHAR(MAX), -- more accurate than "description"

    -- ... other columns ...

    -- "DateTime" is not a good choice for a column name
    create_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modify_date DATETIME NULL -- or same default as create_date
);


View serializable schedule or not Could you help me understand why the schedule on the picture is view serializable without any "blind write"?

Also, should the results be the same if two schedules are view equivalent?

I did create serial version of the schedule and compare but they do not seem view equivalent to each other.

Could you tell me if a concurrent schedule is view serializable,  would it and a serial version of it have the same result in the end?

Me and my lecturer have different opinions, and it is very hard for me to understand hers. She thinks it is view serializable but I do not. I am not sure who is correct.

Definitions

From GeekForGeeks:

  If two schedules are view equivalent to each other, they are called as being view serializable.

  Initial Read If a transaction T1 reading data item A from initial database in S1 then in S2 also T1 should read A from initial database.
  Updated Read If Ti is reading A which is updated by Tj in S1 then in S2 also Ti should read A which is updated by Tj.
  Final Write operation If a transaction T1 updated A at last in S1, then in S2 also T1 should perform final write operations.

  Initial Read If a transaction T1 reading data item A from initial database in S1 then in S2 also T1 should read A from initial database.
  Updated Read If Ti is reading A which is updated by Tj in S1 then in S2 also Ti should read A which is updated by Tj.
  Final Write operation If a transaction T1 updated A at last in S1, then in S2 also T1 should perform final write operations.

  If this three conditions are satisfied, these two schedule are called view equivalent to each other.

I assume that X refers to the same table row everywhere.

In that case, the two transactions in your picture are not serializable, and indeed a lost update would occur. Any serial execution of the transactions would produce a different result from the nested execution you are showing.

To clarify the term "serializable", here is how the SQL standard defines it:

  The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable.
  A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution
  is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

Wikipedia has this definition for view-serializable:

  View-serializability of a schedule is defined by equivalence to a serial schedule (no overlapping transactions) with the same transactions, such that respective transactions in the two schedules read and write the same data values ("view" the same data values).

That seems mostly equivalent, except that the SQL standard only talks about the effects, while Wikipedia talks about reading values as well. But if you read a value and that has no effect, you might as well not read it, so the distinction is mostly philosophical.

And the schedule in your question satisfies neither of these definitions.


Permissions for Cluster service account in SQL server Recently i faced an issue where the SQL server 2012 being hosted in a 2 node failover cluster failed to start. And since the cluster and windows event log didn't help much, i started checking the SQL server error log, which had the below error every time i tried to start the SQL server role in failover cluster manager

  Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Could not find a login matching the name provided. [CLIENT: ]

so i went ahead created the login NT AUTHORITY\SYSTEM and also added it to the Sysadmin role. and it worked!,

Now i'm skeptical whether what i did is right or not security wise.

I've heard that creating NT AUTHORITY\SYSTEM within SQL server is not safe, If that is the case, Should i change my cluster service to a domain account?, Will this affect anything?
I added NT AUTHORITY\SYSTEM to Sysadmin role - Is this really required or can i provide only the required permission explicitly to the login

Yes, it failing is expected. It is documented here.

AlwaysOn Availability Groups and SQL Failover Cluster Instance and Privileges
When installing the Database Engine as a AlwaysOn Availability Groups or SQL Failover Cluster Instance (SQL FCI), LOCAL SYSTEM is provisioned in the Database Engine. The LOCAL SYSTEM login is granted the ALTER ANY AVAILABILITY GROUP permission (for AlwaysOn Availability Groups) and the VIEW SERVER STATE permission (for SQL FCI).

To answer now:

1.I've heard that creating NT AUTHORITY\SYSTEM within SQL server is not safe, If that is the case, Should I change my cluster service to a domain account?, Will this affect anything?

I'm assuming you're talking about the actual cluster service. If that's the case, then no do not change it. You could change it in WS2003 when domain accounts were used but since 2008 this is not possible. If you do this most likely your cluster will break.

2.I added NT AUTHORITY\SYSTEM to Sysadmin role - Is this really required or can I provide only the required permission explicitly to the login

Sysadmins is not required. Per the link/quote above, VIEW SERVER STATE is required.


Database log unavailable I am using SQL Server 2005 and I see the following message in event log (event id 9001)

"The log for database xxxx is not available."

After doing some research I found that it could be a corrupted database or possibly I/O related problem.
I tried running DBCC CHECKDB and it did not report any problem. At the end it shows following message:

"CHECKDB found 0 allocation errors and 0 consistency errors"

I just want to know if there could be any damage to the database or log and what other steps I have to perform to verify it?

Things to check:

Is your Log file size set to a limit?
Have you run out of disk space on the drive where the log file resides (seems unlikely)?
Is this a physical or virtual server?
What service pack are you running?
What, if any, are the preceding errors in the error log?


What will happen with MySQL transaction if one of the queries fails? I have 500 independent insert queries. I put them in a transaction to run faster. I am runing queries with PHP.

What will happen if one of queries fails? Will the transaction be rolled back automaticaly or not?

Maybe this question has been asked before, but I cannot find an answer. If there is one, please share it with me.

Deadlock or lock wait timeout == implicit transaction rollback.

All another errors will rollback only the statement, not whole transaction.

See InnoDB Error Handling in the documentation.


No results for match...against in boolean mode search with fulltext index (MySQL) We have a column with a fulltext index, and are matching 'asp.net' against the column in boolean mode. We know there are records where this column contains the value 'asp.net', but the query is returning no records.

We have not modified any MySQL configuration directives pertaining to fulltext indexes. I believe the default minimum word length for fulltext indexes is 3 or 4 (certainly less than 7), so this query should still return at least one record.

Is there something else we're missing? Any help is appreciated!

I ended up asking a DBA, and he said that the comma is recognized as a word separator in MySQL. So, that meant that it was looking for "asp" and "net", both of which are under the 4 character minimum for words to be included in a fulltext index. I added the configuration directive ft_min_word_len=3 to my.cnf, restarted mysqld, and rebuilt the fulltext index on the table. Using MATCH(column-name) AGAINST ('+asp +net' IN BOOLEAN MODE); now locates the proper records.

Thanks everyone for your help!


This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY I am creating log shipping for two databases, db1 and db2.
It will be from SQL Server 2008 R2 to a newer version, SQL Server 2012.
On SQL Server 2008R2, they are both dbi_version = 661:
DBCC TRACEON (3604);
GO
DBCC PAGE (db1, 1, 9, 3);
GO
DBCC TRACEOFF (3604);

I restore them in standby mode:
RESTORE DATABASE db1 FROM DISK = 'Q:\db1.bak' WITH STANDBY = N'R:\SQLLog\db1.undo'

One of them restores ok, with upgrade, and another one is giving me error:

This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.

WITH STANDBY is only supported when both SQL Servers are the same version.

You can only use WITH NORECOVERY

An alternative if you need read access to the destination databases, is to use replication.


Extract all unique chars from all texts across the table I have a huge mysql table (20 columns by 320.000 rows) that contains a lot of text from a lot of languages and the problem is that I need to identify all the unique characters in that table. For example, if character "," appears one time or more than one time in that table I need this query to show it to me once.

The query should return a list with all the unique characters from this database. I don’t need the query to count them.

Can you help me finding this solution?
Thanks in advance, Catalin

MySQL has no way to split a cell into a bunch of values.  (Yeah, it can split a piece of a cell off -- see LEFT(), etc.  But not an arbitrary set.)

You would be better off doing the task in PHP or some other language -- more powerful than SQL.  (Yeah, it could be done in a Stored Procedure, the the code would be really messy.)

Here is some pseudo code for PHP (etc):

declare $hash as an associative array
foreach row of SELECT * FROM tbl
    foreach cell
        split cell into characters
            $hash[$char] = 1
dump the _keys_ of $hash

(Getting the counts is a trivial enhancement.)

Be sure to work in "characters", not "bytes".  The details of that depend on the language.


PostgreSQL interval division This has come up a couple of times, e.g., in the postgresql newsgroup and the wiki. In general, the relationship between different intervals may not be well defined - a month can be different numbers of days depending upon which month (and year) is under consideration. But sometimes it is necessary to calculate how many intervals occur between two points in time, e.g. (simplified example):

CREATE TABLE recordings(tstart timestamp, tend timestamp, interval ticklength);

SELECT (tend - tstart) / ticklength AS numticks
FROM recordings;

This isn't allowed in PostgreSQL because it is division between two intervals, which wouldn't have well defined general behaviour for the above reasons. A workaround exists for when an interval can be converted to seconds, but what is the best way when this is not the case, e.g, when the interval is of the order of milliseconds?

As @a_horse_with_no_name mentions in his comment, division can be achieved by conversion to milliseconds.

SELECT
  (1000*EXTRACT(EPOCH FROM tend - tstart)
    + EXTRACT(MILLISECOND FROM tend - tstart))
  / (1000*EXTRACT(EPOCH FROM ticklength)
    + EXTRACT(MILLISECOND FROM ticklength)) AS numticks
FROM recordings;


What exactly is SQL Server doing? We have a control table called Generators which has a NextID and varchar label. I found some bad code in a stored procedure which assigns a value w/o a where clause - something like below.

declare @NEXTID int
select top 1 @NEXTID = nextid from generators order by genname desc
select @NEXTID  -- result is 511 which is the last value in the table

select @NEXTID += nextid from generators
select @NEXTID  -- result is a summation of all fields/rows

select @NEXTID = nextid from generators
select @NEXTID  -- result is 511 which is the last value in the table

select * from generators where nextid = 511
select * from generators  -- confirms that this is the last row

It is clear that the @NEXTID gets set to the last item in the clustered index.  What isn't clear to me is, "what exactly did SQL server just do?"  Profiler doesn't show me much, but what I want to know is, did @NEXTID get set for every row and then get overwritten or did the compiler realize what was happening and went right to the LAST value?  Basically this:

select top 1 @NEXTID = nextid from generators order by genname desc

PS: I didn't create the data model.

EDIT

This confirms that SQL Server overwrites the variable (proving Randolph West's answer).

vs

Under the covers, SQL Server will return the entire rowset from the SELECT, and then assigns every value (really quickly) to the variable. Of course, the last value in the SELECT will be what is finally assigned.

Here's where it gets tricky. The order of what is returned, with no WHERE or ORDER BY clause, can depend on any number of conditions. Usually it's returned in the order of the clustered index, but that's not guaranteed.