Race condition on incremental backups I am planing to automate to take incremental backups on a range of sql servers from 2000 to 2012, on a variety of windows too.  This will be done with a vbs script that runs on task scheduler.  I want to know if there is a problem to occasionally start a new incremental backup when the previous one is still running or I must be sure that there is a lapse of time doing nothing.  The rest of details are covered, I keep all the files, and I test (restoring on other server) all the backups.

I want to know if there is a problem to occasionally start a new incremental backup when the previous one is still running

For SQL Server 2000

You would not be allowed to start new differential backup unless the previous one finishes.

For SQL Server 2005 and above

You can start new differential backup while old one is still running but new one would be blocked by already running differential backup. The new diff backup will only start after old one has finished.

This Blogs.msdn article and this SE thread would help you in understanding similar behavior


Oracle Database Vault: Changing DV Owner and DV Account-Manager passwords hangs PX Deq: Execute Reply/row cache lock When changing the password of the users mentioned above the sessions hangs. And it seems to be a deadlock. When changing the password it seems that two sessions are spawned:
Blocking session
C##DVAM_ROOT
1 26 58821  Concurrency row cache lock
Waiting session:
C##DVAM_ROOT
0 405 45629 fuvf4b27452y5 Idle PX Deq: Execute Reply

fuvf4b27452y5 -> /* SQL Analyze(405,0) */
SELECT /*+PARALLEL(1) NO_STATEMENT_QUEUING */ *
FROM X$KXFTASK /*kpdbReplayDDL,PDB_Replay_DDL*/

When an user with with dv_patch_admin role changes the password it works. When disabling Database Vault it works.
I didn’t find this in oracle support.
This is an Oracle Database 19.9 Multitenant.

We opened a SR at Oracle support. It was identified as a bug and forwarded to development.
Workarounds:

Give DV_PATCH_ADMIN role to user changing pwd.
Close pdb and execute in cdb.


What happens if two mySQL queries both try to lock the same row at the same time? So this isn't exactly my strong suit, but here's my basic understanding of the situation:

If two threads of the same application both try to execute the same query at the same time, mySQL will experience deadlock.  The way to prevent deadlock is by locking the table (or row) before writing to it.  But what if the locking requests happen simultaneously?

e.g. you have a table like:

#my_table
---------------
| key | value |
---------------
|  1  |  foo  |
---------------
|  2  |  bar  |
---------------

Then I write a query in my application that does something like

LOCK TABLES my_table WRITE;
UPDATE my_table SET value = RAND() WHERE key = 2;
UNLOCK TABLES;

and by some miracle two instances of this query fire in the exact same millisecond (nanosecond?) what would happen?  It's an InnoDB table if that matters.

This is not a deadlock.

One transaction will simply block -- waiting to acquire the lock. The other transaction will proceed. As soon as the other transaction is done -- either by commit or rollback, the first transaction will proceed.

A deadlock happens when a transaction has acquired a lock on object A, and attempts to acquire a lock on object B at the same time as another transaction has already acquired a lock on object B and is attempting to acquire a lock on object A. Both transactions will then block, waiting on each other. That's the definition of a deadlock: two transactions blocked waiting on a lock that the other has.


Left join multiple tables for millions of rows on multiple columns I am using PostgreSQL via pgadmin3 on Ubuntu 16.04 with 7.7GiB Memory and 4x.3GHz processor.  My only aim with this database is to merge multiple datasets (tables) and extract the final table.  I am wondering what slows my join operation down the most: Having many rows, merging on multiple columns, or mergng multiple tables.

I have the following tables:

combinations (121M rows)
----
source | target | year
bigint | bigint | smallint

coauthorships
----
source | target | year
bigint | bigint | smallint

adjacencies
----
source | target | year     | auth_first_order | auth_second_order | com_first_order | com_second_order
bigint | bigint | smallint | double           | double            | double          | double

Eventually there will be more tables.  Each row in each table is uniquely identified by (source,target,year). All tables have non-clustered indices on (source, target, year)

My initial idea was to construct a view and export the view to disk. This is the definition of view master:

SELECT comb.source,
 comb.target,
 comb.year,
 coauth.publications,
 adj.auth_first_order,
 adj.auth_second_order,
 adj.com_first_order,
 adj.com_second_order
FROM combinations comb
 LEFT JOIN coauthorships coauth ON comb.source = coauth.source AND comb.target = coauth.target AND comb.year = coauth.year
 LEFT JOIN adjacencies adj ON comb.source = adj.source AND comb.target = adj.target AND comb.year = adj.year;

Then I proceeded to export the view like so:

psql -U postgres -d department-networks -c "COPY master TO stdout DELIMITER ',' CSV HEADER;" > ~/master.csv

How can I improve the merge design? I have some freedom over the table look like. Given that the tables are not indexed, which method should I use? From pgadmin's help page it looks like both hash and GiST seem appropriate. Should I merge the keys so that I don't merge on multiple columns but just one? Should I join some tables separately so that in the end I only merge two tables? Should I not use postgres at all? I'm grateful for any help in this regard.

EDIT:

Here is the query plan:

"Merge Left Join  (cost=2615359.20..10115676.76 rows=146636992 width=52)"
"  Merge Cond: ((comb.source = adj.source) AND (comb.target = adj.target) AND (comb.year = adj.year))"
"  ->  Merge Left Join  (cost=2048532.06..8406982.78 rows=146636992 width=20)"
"        Merge Cond: ((comb.source = coauth.source) AND (comb.target = coauth.target) AND (comb.year = coauth.year))"
"        ->  Index Only Scan using combinations_source_target_year_idx on combinations comb  (cost=0.57..5107371.45 rows=146636992 width=18)"
"        ->  Materialize  (cost=2047466.59..2106034.64 rows=11713611 width=20)"
"              ->  Sort  (cost=2047466.59..2076750.61 rows=11713611 width=20)"
"                    Sort Key: coauth.source, coauth.target, coauth.year"
"                    ->  Seq Scan on coauthorships coauth  (cost=0.00..191745.11 rows=11713611 width=20)"
"  ->  Materialize  (cost=566827.14..581496.20 rows=2933811 width=50)"
"        ->  Sort  (cost=566827.14..574161.67 rows=2933811 width=50)"
"              Sort Key: adj.source, adj.target, adj.year"
"              ->  Seq Scan on adjacencies adj  (cost=0.00..51115.11 rows=2933811 width=50)"

@EvanCarroll: Good point, I wanted to add this as question. The tables are not indexed as I don't know which method to use. Question edited. – MERose 16 secs ago

Red flag. Stop your query. Add your indexes. Rerun it. Assuming you have no indexes, add these and then paste the result of \d table for coauthorships and adjacencies`.

CREATE INDEX ON combinations ( source, target, year );
CREATE INDEX ON adjacencies ( source, target, year );
CREATE INDEX ON coauthorships ( source, target, year );
VACUUM ANALYZE combinations;
VACUUM ANALYZE adjacencies;
VACUUM ANALYZE coauthorships;

Also why are you using bigint if you only have millions of rows?


What is a database mission-statement? What exactly is a database mission-statement?  My question was prompted after submitting an academic-assignment requiring a "mission-statement" for ERD-modeling.  I've yet to encounter this requirement out in the wild as a software engineer, and I presume a mission-statement is akin to a thesis-statement, but I remain unsure after my Google-search.  Any DBA professional example or guidance explaining the ideal "database mission-statement" would be appreciated- thanks!

I thought that Jeffrey Yao did a fantastic job answering this question here. I've edited it a little bit and sometimes include my own mission statement on my resume, but here's my "slightly" modified version of Jeffrey Yao. This is not specific to mariadb, but I don't think it has to be.

  "Mission: I am committed to providing a robust and secure database
  system for my customers and as such, I proactively maintain and
  monitor the database system to prevent any potential issues;
  accurately diagnose and forecast the database system health and
  capacity; quickly respond to support request(s) and persistently seek
  innovative ways to improve DBA work efficiency and quality."


SQL Mirroring Locking/Blocking Contention We want to setup Mirroring High Performance Asynchronous. Server A Principal contains database which mirrors to ServerB. Mirroring does not allow read-queries unless  snapshot is created.

1) If we utilize snapshot for reporting queries, and high volume OLTP transactions are sent from Server A to Server B, Will Report queries create locking/blocking contention, which will prevent Server B Mirror from receiving updates?

2) If so, can I place the Server B Mirror database in Read-Committed Snapshot Isolation (RCSI), to prevent locking/blocking (while ServerA is in Read committed) ?  Or does Mirror Database always have to be in same transaction isolation level as Principal ?

1) If we utilize snapshot for reporting queries, and high volume OLTP
  transactions are sent from Server A to Server B, Will Report queries
  create locking/blocking contention, which will prevent Server B Mirror
  from receiving updates?

It will depend. If you are reading a page that has been changed since snapshot was created you are reading the page from sparse file. In that case there is no contention. But if you are reading from a page that did not change since snapshot was created you are reading from source database file. Which may cause contention at the file level since both the source database and the snapshot will be accessing the same MDF.

These are two good articles you might find helpful.

Database Snapshots by  Basit Aalishan Masood-Al-Farooq
SQL Server 2005 Snapshots by Andrew Calvett.

  2) If so, can I place the Server B Mirror database in Read-Committed
  Snapshot Isolation (RCSI), to prevent locking/blocking (while ServerA
  is in Read committed) ? Or does Mirror Database always have to be in
  same transaction isolation level as Principal ?

You cannot do that. As Mirror Database is in restoring state, you cannot make any change. Allowing different configuration goes against the principal or database mirroring where it guaranteed to have same configuration and data so roles of Principal and Mirror can be switched at any time.

Not related to your question but you might find this useful especially the comment in fact, you can’t execute this on the mirror, because the database is in the restoring state.

How to Enable RCSI for a Database with Database Mirroring by Sanjay Mishra


data fetch with multiple filters in a paginated way We have a very huge table with around 10 partitions in our system. The table data has multiple references in other tables.
The database administrator has advised us to avoid performing any join query against the table. How can I efficiently fetch the data in a paginated way from the table where as the filter that I am passing  contains the references of other tables as well. The database is postgres.

If you're not allowed to join, then why bother with an RDBMS?  Not that you could save money on the DB, but you could loose the DBA.  Even with things being the way they are, I'm not sure there is an efficient solution to fetch large amounts of data in a "paginated way".  Depending on your client, you could use a bidirectional cursor, allowing the user to "page" forward an backwards.  This "cursor" has to fit in memory somewhere.

Now, to respect the DBA's wishes, you would create your filter by fetching the id's from the "other" tables, and use those in your where clause with "=" or "in".  If you need the "in", you would lose the possibility of using bind variables, so you'd preferably stick to one item from each of the "other" tables.


Backing up SQL Server 2008 database in different recovery model We currently have a production environment with 10 SQL Server 2008 databases in Full recovery mode (for obvious reasons). We back these databases up fully at a frequent interval, but due to the Full recovery mode this results in a larger than advertised file size. For example, the .bak file reads 14GB but due to the transaction log the file is actually 60+GB.

We also have a dev environment that we would like to restore the production data to at a weekly interval. However, the dev environments have much less space and cannot handle the full 60+GB backup - they need a Simple recovery mode backup.

Right now, we have a manual process of taking the Full recovery production backup, restoring it on a large enough server, setting the recovery model to Simple, shrinking the database and log, and backing up in Simple mode. Then we restore this new Simple backup to the dev environment. Obviously this is not a very easy solution to automate, and just feels wrong.

I'd imagine there is an easier way to automate this same process and backup the production databases into Simple recovery mode without adding more space to the dev environments.

Any help would be appreciated.

For example, the .bak file reads 14GB but due to the transaction log the file is actually 60+GB.

Your problem is an inadequate maintenance and backup/recovery plan. You are not taking log backups with enough frequency, this is why your log grows through the roof. Start taking log backups to allow truncation and then shrink the log. You'll have a better recovery plan and a smaller database log file.

As for the question: no, there is no way to change the restored database file size(s). The restored database will always have the exact same layout as the original backed up database. Recovery mode has nothing to do with this, your restore would need 60GB on any recovery model. You're only mixing the recovery model in the discussion because you shrink the log by switching to SIMPLE. Read the linked article for more details on this aspect.


Why can't start pgbouncer process when setting logfile to /var/log/ path? This pgbouncer.ini setting can't start pgbouncer:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = someuser

No pid file generated, no log file created. No process running(ps aux | grep pgbouncer).

But if set logfile and pidfile to:

logfile = pgbouncer.log
pidfile = pgbouncer.pid

pgbouncer can be started by run pgbouncer -d pgbouncer.ini. It will create log file and pid file at the same path of pgbouncer.ini.

I am using normal user doing it.

Either you don't have permission to write to /var/log/pgbouncer as the user you are running as, or the directory doesn't exist and pgbouncer doesn't or can't create it.

A third possibility is that the permissions on that directory are TOO relaxed, but I don't think pgbouncer checks that.


How to get only the first error with psql --single-transaction I'm typing something like:

psql --single-transaction --quiet my_db < my_file.sql

When the script fails I get many lines saying: current transaction is aborted, commands ignored until end of transaction block.

In linux I can use | more to pipe the result, but in Windows thats may take a lot of time (to show the first error, because the pipe is not a pipe).

My question:

Is there a way to get only the first error?

The .sql file is generated by me, I can add some sets or commands if necesary
Y try --file (I obtain the same behavior)

You may just ask it to stop on the first error:

$ psql -v ON_ERROR_STOP=on --single-transaction ...


Exporting MySQL table data: "into outfile" fails, directory stays NULL after editing variables in cnf files I'm pretty new to SQL, MySQL and Docker. I'm running a Docker MySQL container (I believe).

My problem:

In MySQL, the command

select * from [tablename] into outfile [csv-filename];

fails with the message

  The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

What I've tried:

select @@GLOBAL.secure_file_priv;

outputs a value of NULL.

Then, I changed/added the line

secure_file_priv= "/home/"

into both of the following files within the container:

/etc/mysql/my.cnf
/etc/mysql/conf.d/docker.cnf

Still, "select @@GLOBAL.secure_file_priv;" in MySQL returns NULL and "(...) into outfile (...);" fails as described above.

Any clues are much appreciated!

It could be you missed the [mysqld] section for the configuration file however here is a complete example:

$ mkdir conf
$ mkdir home
$ mkdir datadir
$  echo -e '[mysqld]\nsecure_file_priv= "/home/"' | tee conf/native.cnf
[mysqld]
secure_file_priv= "/home/"

$ cat conf/native.cnf
[mysqld]
secure_file_priv= "/home/"

$ docker run -v $PWD/conf:/etc/mysql/conf.d \
             -v $PWD/home:/home  \
             -v $PWD/datadir:/var/lib/mysql \
             -e MYSQL_ROOT_PASSWORD=my-secret-pw \
             --name mysql \
             mysql:5.7

Another terminal:

$  docker exec -ti mysql  mysql -pmy-secret-pw -e 'select @@GLOBAL.secure_file_priv'

mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /home/                    |
+---------------------------+


Hourly Based Column Need select *
from ( select a.wc_name as Team,
              g.name as Buyer_name,
              e.style_no,
              e.item_no,
              d.receipt_date,
              sum(c.QTY)
       from workcenter a,
            job b,
            Job_Receipt_Details c,
            Grn_Receipt d,
            item e,
            "ORDER" f,
            buyer g
       where C.Job_Id=b.job_id
         and a.wc_id=b.wc_id
         and e.order_no=f.order_no
         and f.buyer_id=g.buyer_id
         and c.item_no=e.item_no
         and c.grn_no=d.grn_no
       group by a.wc_name,
                g.name,
                e.style_no,
                e.item_no,
                receipt_date )
where to_date(to_char(receipt_date,'DD-MON-YY'),'DD-MON-YY')=to_date(to_char(sysdate,'DD-MON-YY'),'DD-MON-YY')

Need hourly base separate column
Current OutPut:

Expected:

What you're trying to do is to pivot your data, which - if you're on Oracle 11g or higher - you can do like so:

SELECT team,
       buyer_name,
       style_no,
       item_no,
       receipt_date,
       hour1,
       hour2,
       hour3,
       ...
FROM   (SELECT wc.wc_name AS team,
               buy.name AS buyer_name,
               itm.style_no,
               itm.item_no,
               trunc(gr.receipt_date) receipt_date,
               to_char(dt, 'hh24') receipt_date_hr,
               jrd.qty
        FROM   INNER JOIN job j ON wc.wc_id = j.wc_id
               INNER JOIN Job_Receipt_Details jrd ON j.job_id = jrd.job_id
               INNER JOIN Grn_Receipt gr ON jrd.grn_no = gr.grn_no
               INNER JOIN item itm ON jrd.item_no = itm.item_no
               INNER JOIN "ORDER" ord ON itm.order_no = ord.order_no
               INNER JOIN buyer buy ON ord.buyer_id = buy.buyer_id
        WHERE  receipt_date >= trunc(SYSDATE)
        AND    receipt_date < trunc(SYSDATE) + 1)
PIVOT (SUM(qty) FOR receipt_date_hr IN ('00' AS hour1,
                                        '01' AS hour2,
                                        '02' AS hour3,
                                        ...));

Note that I have changed your outer query's where clause to where receipt_date >= trunc(sysdate) and receipt_date < trunc(sysdate) + 1 to enable the use of any indexes involving the receipt_date column.

If there are no indexes on that column which might be used, then you could further simply it to where trunc(receipt_date) = trunc(sysdate).


Correlated Subquery in SQL Server 2014 I am running a correlated subquery to find out the listing of vendors (by Vendor Name) that are in different cities, states, i.e. we want to know the vendors that do not have a common city and state with other vendors. It seemed like a self-join was the thing to do.

Only hints if possible, please.

The Vendors table is:

Vendors(VendorID P, VendorCity, VendorState, VendorName,...)

This is what I have:

Select VendorName, VendorCity, VendorState from Vendors AS V1 where
         VendorCity, VendorState NOT IN (Select VendorCity, VendorState FROM
          Vendors AS V2 where V2.VendorID <> V1.VendorID)

This is the error message I get:

  Msg 4145, Level 15, State 1, Line 2 An expression of non-boolean type
  specified in a context where a condition is expected, near ','.

I don't see why there is a reference to Boolean types, since this is not an EXISTS or other related query.

The syntax for IN(...) is:
test_expression [ NOT ] IN
    ( subquery | expression [ ,...n ]    )

With:

test_expression
Is any valid expression.
subquery
Is a subquery that has a result set of one column. This column must have the same data type as test_expression.
expression[ ,... n ]
Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

This means that only 1 column is allowed on both side. Even a list of expressions is considered as 1 dummy table with 1 column for expression values similar to:
SELECT exp FROM (values(exp1), (exp2), ...) as X(exp)

With 2 columns you can use a subquery with EXISTS:
Select VendorName, VendorCity, VendorState
FROM Vendors AS V1
WHERE NOT EXISTS (
    SELECT 1
    FROM Vendors AS V2
    WHERE V2.VendorID <> V1.VendorID
        AND VendorCity = VendorCity
        AND VendorState = VendorState
)

Overall this subquery always return 0 row or 1 or more rows with 1 because it only needs to know if there is 1 or more rows with the same City and State.
The value is not important because it only looks at the number of row (=> if 1 or more rows EXISTS), hence the SELECT 1. The test has already been done in the inner WHERE clause.
I guess that VendorId is the PK. An index on VendorCity and VendorState would help.
Since you are learning and if you don't already know it, you can also look at the usage of APPLY (CROSS APPLY and OUTER APPLY). I let you try it and play with it.


How to change an incorrect datatype for a birth date field from VARCHAR to DATE I made a mistake when creating my users table with a birthdate field and instead of putting the DATE datatype, I put a VARCHAR!
So now my users table looks like this:
CREATE TABLE IF NOT EXISTS users
(
  id INT    UNSIGNED NOT NULL,
  birthdate VARCHAR (200) NOT NULL,

  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

and it is populated as follows (sample):
INSERT INTO users (id, birthdate) VALUES
  (1,'1991-01-23'),
  (2,'yyyy-01-23'),
  (3,'1991-mm-23'),
  (4,'1991-01-dd'),
  (5,''),
  (6,'1991-01-d3'),
  (7,'1983-05-23'),
  (8,'1991-0m-23'),
  (9,'19yy-01-23'),
  (10,'y991-01-23');

Now I want to update every incorrect birthdate to NULL, or set a default value like 2020-01-01. see my sqlfiddle here.

ALTER TABLE users ADD COLUMN DOB DATE;

UPDATE users
    SET DOB = STR_TO_DATE(birthdate, '%Y-%m-%d')
    WHERE birthdate REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}';

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=96c9978ccc667b9289cb3b7b223f28d7


Find the value OPTIMIZE FOR UNKNOWN is using If I have an OPTIMIZE FOR UNKNOWN in my stored procedure, am I able to see what value the database decided was optimal?

OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector.

If you run DBCC SHOWSTATISTICS, it's the value listed in the "All density" column of the second result set:

In this example, I'm using the StackOverflow demo database. The density vector for the Reputation column is 5.962674E-05.

If you take that value, times the number of rows in the table, 5.962674E-05 * 5277831, you get 314.69985680094. That's the number of rows SQL Server will expect to come back for any given Reputation filter.


Oracle Forms 10g to 11g migration Error I having problems from first steps, while migrating Oracle Forms from 10g to 11g. Some of libraries dose not want to compile. the compiler just hangs up when I try to compile them, and Forms builder hangs up when I try to open them. Error signature

AppName: frmcmp.exe  AppVer: 11.1.2.1    ModName: orapls11.dll
ModVer: 11.1.0.7     Offset: 00002541

I checked libraries relations, everything looks fine. Does anybody had similar problems?

There is Oracle bug. When sometimes opening librarys in Oracle Builder 11g which is compiled with Oracle Builder 10g The 11g Builder crashes. I solved this problem by converting librarys to pld in Oracle Builder 10g and then converting them back to pll with Oracle Builder 11g.


Detect multiple valid date periods for same id, interleaved with non-valid periods My table structure and some sample data (test table), as well as my desired output (results) are available here.
When I do
SELECT distinct colony, min(date_check), max(date_check)
from test
where protection ='Y'
group by colony

I only detect 1 event by colony.
Should I use a function or is there another way ?
ps : if you think of a better title to help users, don't hestitate, I struggled.

Without CTEs and window functions:
SELECT colony, MIN(date_in) date_in, date_out
FROM ( SELECT t1.colony, t1.date_check date_in, MAX(t2.date_check) date_out
       FROM test t1
       JOIN test t2 ON t1.colony = t2.colony
                   AND t1.date_check < t2.date_check
                   AND t1.protection = 'Y'
                   AND t2.protection = 'Y'
                   AND NOT EXISTS ( SELECT NULL
                                    FROM test t3
                                    WHERE t1.colony = t3.colony
                                      AND t1.date_check < t3.date_check
                                      AND t3.date_check < t2.date_check
                                      AND t3.protection != 'Y' )
       GROUP BY t1.colony, t1.date_check ) subquery
GROUP BY colony, date_out
ORDER BY colony, date_in;

With CTE and window functions:
WITH
cte1 AS (SELECT *,
                CASE WHEN protection = 'N' OR LAG(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'Y'
                     THEN 0
                     ELSE 1 END AS row_in,
                CASE WHEN protection = 'Y' OR LEAD(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'N'
                     THEN 0
                     ELSE 1 END AS row_out
         FROM test),
cte2 AS (SELECT *, SUM(row_in) OVER (PARTITION BY colony ORDER BY date_check) group_no
         FROM cte1)
SELECT colony, MIN(date_check) date_in, MAX(date_check) date_out
FROM cte2
WHERE protection = 'Y'
GROUP BY colony, group_no
ORDER BY colony, date_in;

https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=bf769987d7211b73e89c0564bc6902a5


What does "blocked=-2" mean? I'm trying to create an index on a pretty big table (over 8 million rows).

The index creation was taking over 10 minutes, so I selected from master..sysprocesses from my process spid:

spid    kpid    blocked waittype    waittime    lastwaittype    waitresource        dbid    uid cpu physical_io memusage    login_time                  last_batch              ecid    open_tran   status      sid             hostname        program_name                                    hostprocess     cmd             nt_domain   nt_username     net_address     net_library loginame    context_info    sql_handle  stmt_start  stmt_end    request_id
62      11836   -2      0x0003      11762       LCK_M_S         TAB: 5:427148567:0  5       1   0   0           3           2016-01-03 10:03:39.217     2016-01-03 10:15:41.013 0       1           suspended   0x010500...     MyPC            Microsoft SQL Server Management Studio - Query  9636            CREATE INDEX    MyDom       Saul            xxx             LPC         MyDom\Saul  0x000...        0x0100...   522         776         0

What is that "blocked = -2"?  Why is my index creation not working?  And how do I repair this?

This is documented in sys.sysprocesses (Transact-SQL):

You can find more information and possible actions to take in the duplicate question:

Dealing with Negative Session ID Denials in SQL Server
How It Works: Orphan DTC Transaction (Session/SPID = -2)
Orphaned MSDTC transactions

In some cases, after proper investigation, it may be appropriate to configure SQL Server to presume that any in-doubt distributed transactions have aborted, using sp_configure.


Database deployment script for SQL Server 2008 has different behaviors Using Visual Studio 2008, I have compared schemas between remote SQL Server database with  scripts hosted in a TFS server.

The goal was to generate a migration script that would update the remote database with developers latest script changes located in TFS.

The next step was to get a full backup from remote database, and restore it in my local database, and then execute the migration script in order to test it.

After small tweeks (dealing with constraints) and I have managed to execute the migration script locally and my local database (that is a copy from remote database).

The last step was to get this script and run it in remote database, but for my surprise, the script gets interrupted in a procedure:

Altering dbo.sp_my_procedure_here...
Msg 207, Level 16, State 1, Procedure sp_my_procedure_here, Line 616
Invalid column name 'code'.

Within this mentioned procedure, there is a CREATE TABLE ##TEMP (code INT), a execute command to run a SQL statement contained in a string variable for executing INSERT INTO ##TEMP VALUES (999), a SELECT @myReturnCode = code FROM ##TEMP and a DROP TABLE ##TEMP.

The statement SELECT @myReturnCode = code FROM ##TEMP is not working in the remote database. Since there is no procedure execution (this is only a script modifying database structure). Why the migration script would fire "invalid column" on the remote database?

The remote SQL Server database is version 10.0.2531

The local SQL Server database is version 10.50.2500

Both databases are set to compatibility mode for "SQL Server 2000".
The same migration script was executed in SSMS 2008.

ALTER PROCEDURE [dbo].[sp_my_procedure]
AS
SET NOCOUNT ON

CREATE TABLE ##TEMP (code INT)
DECLARE @sql Varchar(4000)

SET @sql = 'DECLARE @myReturnCode int
               SET @myReturnCode = 999
               INSERT INTO ##TEMP VALUES (@myReturnCode)'
EXEC (@sql)

IF (@@ERROR <> 0)
BEGIN
   RAISERROR('There is something wrong with insert',1,1)
   DROP TABLE ##TEMP
   RETURN -1
END
SELECT @myReturnCode = code FROM ##TEMP
DROP TABLE ##TEMP
SET NOCOUNT OFF

Why are you using ##global temp tables instead of #local? It is possible that when you are changing the procedure there is already a global temp table in existence with the same name - they're global so it could have been created by anyone. Use a local temp table and that problem will go away.

Also, please don't use the sp_ prefix. Stored procedures shouldn't really need to have a prefix - what is the point of adding sp_ to the beginning of every stored procedure? They still sort the same (e.g. in Object Explorer), it just makes it that much harder to find the one you're after. If you absolutely must use a prefix for some reason, pick something other than sp_.


FATAL: terminating walreceiver due to timeout 1/ DESCRIPTION:

Machine 1 (slave): Centos 6.6 , x64 , installed PostgreSQL 9.3  (on Local)
Machine 2 (master): Centos 6.6 , x64 , installed PostgreSQL 9.3  (on Cloud)

Machine 1 (slave) and machine 2 (master) are in a cluster (streaming replication). Sometime, I see "FATAL:  terminating walreceiver due to timeout" in slave log.

Here is full detailed logs:

Slave

2015-03-03 02:01:53 UTC 19693   LOG:  database system is ready to accept read only connections
2015-03-03 02:01:53 UTC 19699   LOG:  started streaming WAL from primary at 0/8000000 on timeline 1
2015-03-03 02:02:15 UTC 19695   LOG:  redo starts at 0/8F04530
2015-03-03 02:39:26 UTC 19699   FATAL:  terminating walreceiver due to timeout
2015-03-03 02:39:26 UTC 19695   LOG:  invalid record length at 0/8F080F8
2015-03-03 02:39:41 UTC 21065   LOG:  started streaming WAL from primary at 0/8000000 on timeline 1
2015-03-03 03:19:12 UTC 21065   FATAL:  terminating walreceiver due to timeout
2015-03-03 03:19:12 UTC 19695   LOG:  invalid record length at 0/9D488F8
2015-03-03 03:19:27 UTC 22489   LOG:  started streaming WAL from primary at 0/9000000 on timeline 1

Master

2015-03-03 02:02:40 UTC 1718   LOG:  database system is ready to accept connections
2015-03-03 02:02:40 UTC 1724   LOG:  autovacuum launcher started
2015-03-03 02:02:42 UTC 1726 [unknown] [unknown]LOG:  invalid length of startup packet
2015-03-03 02:02:42 UTC 1726 [unknown] [unknown]LOG:  connection failed during start up processing: user= database=
2015-03-03 02:35:45 UTC 1788 pgAdmin III - Query Tool enterprisedbERROR:  column "username" does not exist at character 18
2015-03-03 02:35:45 UTC 1788 pgAdmin III - Query Tool enterprisedbSTATEMENT:
        select datname, username, client_addr, client_port, query from pg_stat_activity;
2015-03-03 02:41:03 UTC 1748 walreceiver enterprisedbLOG:  terminating walsender process due to replication timeout
2015-03-03 02:51:42 UTC 3184 ::1 psql.bin enterprisedbERROR:  unrecognized configuration parameter "replication_timeout"
2015-03-03 02:51:42 UTC 3184 ::1 psql.bin enterprisedbSTATEMENT:  show replication_timeout;
2015-03-03 02:51:54 UTC 3184 ::1 psql.bin enterprisedbERROR:  relation "pg_setting" does not exist at character 15
2015-03-03 02:51:54 UTC 3184 ::1 psql.bin enterprisedbSTATEMENT:  select * from pg_setting;
2015-03-03 02:58:33 UTC 3388 [unknown] [unknown]LOG:  invalid length of startup packet
2015-03-03 02:58:33 UTC 3388 [unknown] [unknown]LOG:  connection failed during start up processing: user= database=
2015-03-03 02:58:57 UTC 3390 [unknown] [unknown]LOG:  incomplete startup packet
2015-03-03 02:58:57 UTC 3390 [unknown] [unknown]LOG:  connection failed during start up processing: user= database=
2015-03-03 03:15:04 UTC 3967 [unknown] enterprisedbFATAL:  database "enterprisedb" does not exist
2015-03-03 03:20:53 UTC 2884 walreceiver enterprisedbLOG:  terminating walsender process due to replication timeout

2/ QUESTION:

What is about "FATAL:  terminating walreceiver due to timeout" problem ? How can I fix it ?

You need to tune the wal_receiver_timeout parameter to your needs.


Always-On Read Only Routing vs External Load Balancer We are migrating a SQL Server 2008R2 with mirroring and transactional replication to SQL Server 2016 with AlwaysOn. So far, so good but we still have one question.

Is it better to use always on feature "read only routing" or to use an external load balancer to balance connection between replicas ?

I read a lot but I did not find any feedback. What I found is the following :

We already use an external load balancer with our current platform and it's working well
Since Sql server 2016, we can do round robin in sql server ror.
we must keep in mind that in case of persistent connection (or connection pooling), connection will be reused so it will point to the very same server. In other words, connections are balanced, not workload. That's the same for both solutions.
ApplicationIntent=ReadOnly and MultiSubnetFailover=True are useful with both solutions.

To what I understand here are the advantage and drawback of each solution :

Always On ROR : Can "group" instances, failover easy to configure, knows easily wich instance is primary and instance states. Very recent (untested?) feature.
External Load Balancing : easier to manage (does not need a dba to do network work), tried and true, can do other load balancing method than Round Robin (weighted round robin, least connection ...).

Did I miss something? Does somebody have any insight or feedback on always on ROR?

Is it better to use always on feature "read only routing" or to use an external load balancer to balance connection between replicas ?

That's going to 100% depend on your needs. For example, if you're just wanted to have readable workloads hit a secondary, then inside of SQL Server should be fine. Now, if you wanted to hit the secondary that is closest (geographically) to your internal users depending on where they are... then you'll need some fancy rules and external load balancing.

Regardless which you choose, unless creating your own health check or probe the external load balancer will not know which secondary servers are healthy or unhealthy.

  does not need a dba to do network work

Neither of the ways requires a DBA to do "network" work, you setup the URL (SQL) and the list (SQL) and magic starts to happen.

  Does somebody have any insight or feedback on always on ROR?

Use it where you need it, it's just another tool in the collection. You may find that connecting directly to the secondary is better for your use cases and scenarios that using ROR... or you may write your own health checks for the external load balancer (if it supports it) to only route to secondary servers that take into account locality of the user.


What are negative keys used for? Somewhat new to using standard SQL databases (currently working with MySQL mostly) I haven't run across many usages of this as of yet.

When and why is it useful to have negative (or rather signed) keys indexing a table?

All a primary key is is a value that we have determined is the value that is of utmost importance in a record. Whether that key is a signed int, an unsigned int, a string, a blob (actually, there are limits) or a UUID (or whatever name it takes today), the fact still stands that it is a key, and that it is the thing of utmost importance.

Since we're not constrained to use only positive oriented numbers for our keys, it makes sense to consider that a signed int will only go to ~2 billion, whereas an unsigned int will go to ~4 billion. But there's nothing wrong with using a signed int, setting the initial value to ~ -2 billion and setting an increment of one. After ~2 billion records you'll hit "zero" and then you'll continue to ~2 billion.

As to why it would be helpful to have "negative keys" in a table, that's the same question as "why is it helpful to have keys in a table". The "value" of a key has no impact on its status as a key. A key is a key is a key.

What is important is if the key is valid.

As to why it would be useful to allow keys that were negative, I can suggest some reasons:

What if you wanted to indicate returns in a sales system as negative sales order numbers, that matched the positive sales order number, thus making correlation easy (this is naive, and poorly designed, but it would work in a "spreadsheet" sense).

What if you wanted to have a users table, and indicate that the ones with negative numbers were system controlled (SO does this very thing, for chat feed users).

I could go on, but really the only reason why the number being negative is of importance is if you or I assign importance to it. Aside from that, there is no great reason for the value of a key to have any bearing on the key itself.


mariadb datadir change location raspberry pi I've got a raspberry pi 3 and have an external HDD which I have yet to mount but what directory I make depends a little on how best I can move the datadir location.

So at the moment I have the basic install from apt-get install MariaDB and ive added a new user and then also installed phpmyadmin, everything is good.

But I'd like the data to be stored on the external hdd so it doesn't burn up the sd card.

I've read through a few online instructions, all with bad grammar/spelling and older versions of the software. For example they say to edit /etc/MySQL/my.cnf but the .cnf files seem to be different and I'm not 100% which files (or all files) need changing with the datadir and socket edits.

Which .cnf files need to be edited please?

Edit
If I add to /etc/MySQL/my.cnf the following
    [client-server]
    datadir=/my-new-directory
    socket=/my-new-directory/MySQL.sock
    port=3306
    socket=/my-new-directory/MySQL.sock

as described at https://www.tecmint.com/change-default-mysql-mariadb-data-directory-in-linux/

This would then be overwritten by the other .cnf files that are read after.

Be aware that although mariadDB loads configuration details from various my.cnf files it can also load them from other files with different names.

That means that if you make a change in one of the my.cnf files, it may get overwritten by another file of a different name. To make the change stick, you need to change it in the right (last loaded) config file - or, maybe, change it in all of them.

So how do you find all the config files that might be loaded? Instead of looking for my.cnf files, try running:

grep -r datadir /etc/mysql/

This will find all the places in which datadir is mentioned. In my case, it produces this answer:

/etc/mysql/mariadb.conf.d/50-server.cnf:datadir     = /var/lib/mysql

When I edit that file (/etc/mysql/mariadb.conf.d/50-server.cnf) to change the value for datadir (in my case to /media/USBHDD2/shared/mysql ), it works, whereas changing it in my.cnf does not.

However, you then have to make sure that the hard drive is mounted before mariadDB starts up, otherwise the startup will fail. And also make sure that the ownership of the directory and files on your mounted drive is mysql:mysql - a problem if the filestructure is ntfs rather than EXT4. see my own problem and solution here


Get the start and end dates of the previous month the query is run with PostgreSQL/intervals I am looking for a way to get the start and end datetime for the previous month the query is run that I can reuse inside a query.
I was looking at interval but I would like the query to work out that if I run the query in March 2021 that I am looking for data from '2021-02-01 00:00:00' to '2021-02-28 23:59:59'.
Is this possible using interval or something similar or does it need an intermediate step?

Assuming you want to apply a condition on a column in the table you can use:
where timestamp_column >= date_trunc('month', current_timestamp) - interval '1 month'
  and timestamp_column <  date_trunc('month', current_timestamp)

date_trunc('month', current_timestamp) - interval '1 month' gives you the start of the previous month. In March this would be 2021-02-01
date_trunc('month', current_timestamp) gives you the start of "this month" so in March this would be 2021-03-1 as the comparison for the upper limit is done using < it will include everything on the last day of February (including 23:59:59.9999999 which your desired condition would not include).


Indexing varchar column to search for first letter I'm trying to optimize a legacy web-app where users can ask for a list of all records whose name starts with a given character.

The query to optimize is:

SELECT oggetto.id_oggetto, titolo, anno, specifiche from oggetto inner join oggetto_formato on (oggetto.id_oggetto = oggetto_formato.id_oggetto)
            where attivato=1 and oggetto.titolo like 'C%' and id_formato IN ('6', '7', '4') order by titolo LIMIT 650,50;

Sorry for the italian....
oggetto means item and titolo is title. I want to speed up the lookup on oggetto.titolo.

EXPLAIN EXTENDED tells me the following:

+----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+
| id | select_type | table           | type  | possible_keys                           | key             | key_len | ref                          | rows | filtered | Extra                       |
+----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+
|  1 | SIMPLE      | oggetto         | ALL   | PRIMARY                                 | NULL            | NULL    | NULL                         | 42585|   100.00 | Using where; Using filesort |
|  1 | SIMPLE      | oggetto_formato | ref   | PRIMARY,FK_oggetto_formato_2,id_oggetto | PRIMARY         | 4       | agorateca.oggetto.id_oggetto |    1 |   100.00 | Using where; Using index    |
+----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+

I created the following index:

CREATE INDEX title_initial ON item (title(2));

The result of EXPLAIN EXTENDED for the above query after creating the index is:

    +----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+
    | id | select_type | table           | type  | possible_keys                           | key             | key_len | ref                          | rows | filtered | Extra                       |
    +----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+
    |  1 | SIMPLE      | oggetto         | range | PRIMARY,iniziale_titolo                 | iniziale_titolo | 9       | NULL                         | 2880 |   100.00 | Using where; Using filesort |
    |  1 | SIMPLE      | oggetto_formato | ref   | PRIMARY,FK_oggetto_formato_2,id_oggetto | PRIMARY         | 4       | agorateca.oggetto.id_oggetto |    1 |   100.00 | Using where; Using index    |
    +----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+

Could anyone confirm my opinion that the index is set up correctly and is doing its job?

I'm not sure what DBMS you are using but in general yes.  It looks like your index is doing what it is supposed to.  As a general case the optimizers can take advantage of an index in a "like" query if there are no pattern (_%[] etc) characters on the left hand side.

So this will use an index

oggetto.titolo like 'C%'

But this could not

oggetto.titolo like '%C'


MySQL cache is not getting hit by subqueries I noticed something weird on MySQL query_cache behavior and I would like to know if this is a normal behavior.

Let's say I have an item table

 ID | Item
-----------
 1  | Item_1
 2  | Item_2
 3  | Item_3

The query that will be used here is : SELECT id FROM items

The first time I use it I have a +1 in my status Qcache_inserts, the second time I use it I have +1 to Qcache_hits. Perfect it's working fine.

Now if I use this query : SELECT COUNT(1) FROM (SELECT id FROM items) my_table

The subquery SELECT id FROM items is supposed to already be present in the cache, but I can't get any hit.

Aren't subquery simple thread that executes first ? Then why it is not hitting my query cache ?

MySQL current 5.1 and 5.5 versions do not cache subqueries. Only whole queries. Subqueries are not processed as a separate item and the execution planned created is for the whole query.

MariaDB (a MySQL fork), version 5.3 has an optimization feature that does exactly that: Subquery cache.

If I am not wrong a similar feature will be incorporated in MySQL 5.6.


Can I use row-level-security for application users? For my webservice I use a PostgreSQL database which supports row-level-security to prevent users accessing rows of a table they are not supposed to read/access. I would like to use this functionality to prevent a user accessing data from another user (either due to a bug, altered webservice request, SQL injection, etc).

I understand the purpose is actually meant for a database user, but I am wondering if it is wise or common to create a shadow-user in the database for every "application" user as well. (the database is of course not directly accessed and rather through a WSGI, therefore the shadow user)

FYI: This is technically a XY-problem. For example, my actual intention is to prevent that a user requested his profile and accidentally or corner-case data from another profile gets send back. Row-Level access looked promising

Row security policies are general SQL expressions, so you are not required to use database users. However, the policy has to know what the current user is, and this is easier with current_user than with some other setting. (It would be possible to use current_setting('my_app.app_user'), but that requires you to SET this value in every connection, and is not protected against changes made by injected SQL code.)


Lost tables in Drupal site database I have Drupal site on my local server build using pressflow

I had problems with the xammp 1.7.4 server, so I delete everything except mysql and htdocs folders.

I installed the server again, but the drupal site didn't work again.

There are 186 tables in my Drupal database, but phpmyadmin shows only 119 InnoDB tables

The rest of the tables are MyISAM, but they didn't show in phpmyadmin and it looks like Drupal can't find them too

I don't know where is the actual problem and how to fix it. Help me please.

You may need to check out the configuration of InnoDB.

You can start by running

SHOW VARIABLES LIKE 'have_innodb';
SHOW ENGINES;

These will indicate whether or not InnoDB is running properly.

If InnoDB is disabled, it is more than likely that the log files need to be recreated.

As long as you keep ibdata, delete ib_logfile0, delete ib_logfile1, and set innodb_log_file_size to 170M in my.ini, you should be able to start mysql and have mysql rebuild new ib_logfile0 and ib_logfile0 to 170M each as suggested by the article you mentioned in the chat room.


How to find MongoDB index statistics Is there any way we can figure out what all indexes are cached into RAM ? I want to know page related stats for individual indexes (# of hits and miss )

You can get overall (instance wide) hit info from the db.serverStatus() command, and in particular:

http://docs.mongodb.org/manual/reference/command/serverStatus/#serverStatus.indexCounters.hits
http://docs.mongodb.org/manual/reference/command/serverStatus/#serverStatus.indexCounters.misses

These numbers are totals seen for the lifetime of the process, so if you want to get a rate or see the trend over time, then you will need to record them periodically and diff the values accordingly.  Thankfully, if you install MMS Monitoring, it will do that for you and graph the results.

However, there are currently no per-index statistics available.  The relevant feature request can be found here for tracking and voting purposes:

https://jira.mongodb.org/browse/SERVER-2227

Update: January 2016

The stats referenced above have been removed from the output of the server status command, so are no longer available.  However, the referenced feature request is now complete and is available in version 3.2 in the form of the $indexStats aggregation operator (the linked docs also contain sample output). For completeness, here's an example I put together:

Prior to any queries I have just 2 indexes, the default _id and indexme, both with 0 ops:

> db.foo.aggregate( [ { $indexStats: { } } ] ).pretty()
{
    "name" : "indexme_1",
    "key" : {
        "indexme" : 1
    },
    "host" : "localhost:27017",
    "accesses" : {
        "ops" : NumberLong(0),
        "since" : ISODate("2016-01-12T19:03:01.358Z")
    }
}
{
    "name" : "_id_",
    "key" : {
        "_id" : 1
    },
    "host" : "localhost:27017",
    "accesses" : {
        "ops" : NumberLong(0),
        "since" : ISODate("2016-01-12T18:59:24.292Z")
    }
}

Then run a couple of finds to bump the ops on indexme and check again:

> db.timecheck.find({indexme: 33})
> db.timecheck.find({indexme: 55})

> db.timecheck.aggregate( [ { $indexStats: { } } ] ).pretty()
{
    "name" : "indexme_1",
    "key" : {
        "indexme" : 1
    },
    "host" : "localhost:27017",
    "accesses" : {
        "ops" : NumberLong(2),
        "since" : ISODate("2016-01-12T19:03:01.358Z")
    }
}
{
    "name" : "_id_",
    "key" : {
        "_id" : 1
    },
    "host" : "localhost:27017",
    "accesses" : {
        "ops" : NumberLong(0),
        "since" : ISODate("2016-01-12T18:59:24.292Z")
    }
}


What does double number sign ## mean in SQL? A meta post gives a nice snippet of SQL

declare @search nvarchar(max) = ##searchfor:string?carefully %listen##
select id [Post Link]
     , score
     , creationdate
     , lastactivitydate
     , closeddate
     , owneruserid as [User Link]
from posts
where body like concat('%', @search, '%') collate SQL_Latin1_General_CP1_CI_AI
or title like concat('%', @search, '%') collate SQL_Latin1_General_CP1_CI_AI

What does double number sign ## mean in there?

I guess that is some kind of string syntax, but I cannot even guess a keyword for that. googling "number sign ## in SQL" returns "prefix temporary tables", which is not the case.

## surrounds a query parameter in the Stack Exchange Data Explorer (SEDE).
The portion after the ? is the displayed default parameter value.
From the SEDE Tutorial written by Monica Cellio:

Note: the particular syntax used for parameters is specific to SEDE. Everything we've said so far about SQL is true for all flavors of SQL; this is different.
As shown in this query, you refer to a parameter by surrounding its name with doubled pound signs, such as ##MinScore##. Optionally you can specify the data type (int (a whole number), float (a number with a decimal value, like 2.5), or string): ##MinScore:int##. If you specify a type then SEDE will validate values against that type, so that if you're expecting a number for score and somebody types "unicorn" the query won't run. If you don't specify a type, your query may receive unexpected inputs.

## has no meaning in standard SQL. Though not the case here, it is used in Transact-SQL as a prefix to identify a global temporary object:

Rules for regular identifiers
An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.


DB2 - what happens when SQL archive log file reches its maximum value (S9999999.LOG) we have DB2 in archive mode.
archive log files are created and deleted from the disk.
What will happen when archive log file reaches its maximum value for the name (S9999999.LOG) will it start to count from the beginning (S0000001.LOG)?

According to the link that @mustaccio put in the comments:

The database manager resets to S0000000.LOG if:

A database configuration file is changed to enable rollforward
recovery
A database configuration file is changed to disable
rollforward recovery
S9999999.LOG has been used.


How can I see the current Database Mail configuration? Our SQL Server (2008) instance is configured to send mail and everything is working correctly, but I can't figure out how to view the existing configuration, in particular the SMTP server.

From SSMS I can only start the configuration wizard, and I can't find anything online - plenty of info on how to set it up but nothing on how to view the current settings.

How can I view the existing settings?

+1 to @jsauni for the wizard

You can also use the following system functions.

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

The wizard is probably easier to follow but these will give you everything at once without having to go back and forth through the wizard.  I find it a bit cumbersome. :)


JSON field saves as longtext datatype I'm trying to create JSON column in 10.3.16-MariaDB but every time I save the configuration in PHPMyAdmin, the column saves as longtext datatype.

No errors shown :|

Where could be the problem?

System specs: Apache/2.4.39 (Win64) OpenSSL/1.1.1c PHP/7.3.7 and PHPMyAdmin 4.9.0.1 versions

MariaDB does not have a datatype of JSON.  Instead it gives you LONGTEXT.

Be aware that there are a lot of differences between MySQL and MariaDB in the area of JSON.  Furthermore, different versions of each have different levels of functions, etc.


SQL Diff Backup not using latest full We're running a full SQL backup each day, then differentials each hour. This has been working on multiple servers for a long time now. I'm not aware of any changes that have been made but, on one server, in particular, the differentials are just getting bigger and bigger, instead of resetting back to a smaller size after a full backup. Normally on other servers, the first differential backup is a few hundred MB, but we're getting a 6GB file every time.

Screenshot of the current backup files attached

The SQL commands being run to perform the backups are:

For full:
BACKUP DATABASE <DBName> TO DISK = <Path> WITH INIT;

For diff:
BACKUP DATABASE <DBName> TO DISK = <Path> WITH DIFFERENTIAL, INIT

Any help would be greatly appreciated.

After further digging, it appears someone had left a transaction open, which was the underlying cause of this issue.

I managed to find there was a transaction open by running:

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

I then used this article https://blog.sqlauthority.com/2010/10/01/sql-server-get-query-running-in-session/ which provided the below SQL to find what was being run.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (YourSessionID)
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

It appeared to be an empty transaction that didn't have any active SQL running so I was able to restart the SQL process to resolve this.


How to run a long query from a trigger without upset I have some log records that are inserted into a table in my database. When they land, I would like to process them asap. The processing could potentially be a bit lengthy.

I would just write a trigger to do the processing, but I'm worried it will cause problems if it takes a while.

Is there a way to call a stored procedure asynchronously from a trigger? Or perhaps a better way of doing this altogether?

I would recommend you to use "SQL Server Service Broker". It allow you to run queries asynchronously not only in triggers.

Here is the link to documentation: https://msdn.microsoft.com/en-us/library/bb522893.aspx

Here is a link to a tutorial that works through how to set this up: http://www.sqlservercentral.com/articles/Service+Broker/73398/


Concurrent Users and Foreign Keys For this question, please assume the following:

I am using MySQL
I have a Location table: Location(ID, Name, Address, TravelDetails, ...)
I have an Event table: Event(ID, Name, LocationID, StartTime, ...)

LocationID is a Foreign Key to Location

The system is an ASP.NET MVC Web Application
The system has multiple concurrent users.

Here is an example scenario that explains my confusion:

In my application, I select "Create New Event". I am given a Drop Down List to select a Location. I am shown the Location Name, but the LocationID of the selected item is what is stored in the Event.

While I am creating my Event, someone else opens a Location to edit some of its details. They could change the Location Name and all other details so that the Location is now effectively a completely different Location (basically, the editor created a new Location without deleting the location first). Those changes don't propagate to my Event's Location Drop Down List until I refresh the page, so I don't have the most up-to-date list of Locations.

With the changes made to the Location, the identity of the Location I wanted to use for my Event could be completely different - it might not represent the same Location as it did originally. Now my Event could be completely wrong because the Location is different than I had intended.

My question is this:

How should I handle this? Is this a problem worth worrying about? Who is to blame? Is it my fault as the developer for not locking down fields such as Location Name (but what if there was a typo in the name...?) or implementing safety checks to inform the user if a Location he is trying to use was changed since he started creating his Event? Or is it the fault of the editor for not being "smart" about completely changing the original meaning/purpose of a Location?

I want to assume users will be smart when making edits, but that is always a bad assumption.

Some discipline is needed.  A location_id must always refer to a particular location, even if the name of the building changes or you make a correction to the address.

The UI must make it obvious whether to the user as to whether he is editing a "location" or creating a "new" location.

When providing the "pull down" in the UI, the id is hidden and uniquely identifies it to the rest of the data; the name, address, etc is useful for humans to know which 'location' it is.

The UI needs a straightforward way to change an even from one location to another -- in case the original association to an event was erroneous or in the case that the even is moved to a different venue.

Nothing if foolproof.

You need scripts that check for double-booking.  They should provide sufficient info to help the admin figure out whether it was a typo, a confusion of two similar location names, etc.


SQL Server AS: Set ImpersonationMode via PS Try to create a database in SQL Server Analysis Server (SQLAS, SSAS) version 2012 SP2 if that matters and set Impersonation Mode via Powershell. (try to avoid XMLA script).

Here are my attempts. Does anybody have the right command handy?

Simple create

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$DBASserver = New-Object Microsoft.AnalysisServices.Server
$DBASserver.connect("mysqldbserver.dom.com")

$database=$DBASserver.databases
$database|select-object name, id

$DBASserver.databases.add("Finance")

$db = $DBASserver.databases.item("Finance")
$db.update()
$db.description = "Finance Department Data Mart"
$db.update()

Getting info on the current mode: (works)

$db = $DBASserver.databases.item("Finance")
$dbmode = $db.DataSourceImpersonationInfo
$dbmode

Update: Setting the mode by getting it from another db works. But creating the Mode object new doesn't
Here working by reading mode from another DB

$db1 = $DBASserver.databases.item("MyDBManualSet")
$db2 = $DBASserver.databases.item("MyNewPSDB")
$dbmode = $db1.DataSourceImpersonationInfo
$dbmode
$db2.DataSourceImpersonationInfo = $dbmode
$db2.update()

Here my attempts to set the mode ImpersonationMode to ImpersonateServiceAccount (nothing works)

$newMode.ImpersonationInfo = new ImpersonationInfo(ImpersonationMode.ImpersonateServiceAccount)
$db2.DataSourceImpersonationInfo = $newMode

or

$newMode= new ImpersonationInfo(ImpersonationMode.ImpersonateServiceAccount)
$db2.DataSourceImpersonationInfo = $newMode

here the documentation I can't piece together  MSDN SQL documentation info

Got it working:

$ImpInfo=New-Object Microsoft.AnalysisServices.ImpersonationInfo
$ImpInfo.ImpersonationMode = 'ImpersonateServiceAccount'

$db.DataSourceImpersonationInfo = $ImpInfo
$db.update()

here a full working PowerShell (PS) example

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$DBASserver = New-Object Microsoft.AnalysisServices.Server
$DBASserver.connect("mysqldbserver.dom.com")

$database=$DBASserver.databases
$database|select-object name, id

$DBASserver.databases.add("Finance5")

$db = $DBASserver.databases.item("Finance5")
$db.update()
$db.description = "Finance Test 5"
$db.update()

$db = $DBASserver.databases.item("Finance5")
$dbmode = $db.DataSourceImpersonationInfo
$dbmode #This doesn't post info, maybe a refresh issue.

$ImpInfo=New-Object Microsoft.AnalysisServices.ImpersonationInfo
$ImpInfo.ImpersonationMode = 'ImpersonateServiceAccount'

$db.DataSourceImpersonationInfo = $ImpInfo
$db.update()
$db.description = "Finance Test 5 now changed ImpersonationInfo"
$db.update()
$dbmode = $db.DataSourceImpersonationInfo
$dbmode


1 minus percentage returns negative number I have a sidewalk_inspection table:

create table user.sidewalk_insp
(
    insp_id int,
    deficiency_length number(10,2),
    sidewalk_length number(10,2)
);

insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 1, 0.0, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 2, 2.5, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 3, 5.0, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 3, 7.5, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 4, 11.0, 10.0);

select * from user.sidewalk_insp;

   INSP_ID DEFICIENCY_LENGTH SIDEWALK_LENGTH
---------- ----------------- ---------------
         1                 0              10
         2               2.5              10
         3                 5              10
         4               7.5              10
         5                11              10

I can calculate the deficiency_percent by dividing deficiency_length by sidewalk_length:

select
    insp_id,
    deficiency_length,
    sidewalk_length,
    deficiency_length / sidewalk_length as deficiency_percent
from
    user.sidewalk_insp;

   INSP_ID DEFICIENCY_LENGTH SIDEWALK_LENGTH DEFICIENCY_PERCENT
---------- ----------------- --------------- ------------------
         1                 0              10                  0
         2               2.5              10                .25
         3                 5              10                 .5
         4               7.5              10                .75
         5                11              10                1.1

Rather than calculate the deficiency_percent (a.k.a. 'percent bad'), I want to calculate the condition_percent (a.k.a. 'percent good'). I can do this by subtracting the percentage from 1:

select
    insp_id,
    deficiency_length,
    sidewalk_length,
    1 - (deficiency_length / sidewalk_length) as condition_percent
from
    user.sidewalk_insp;

   INSP_ID DEFICIENCY_LENGTH SIDEWALK_LENGTH  CONDITION_PERCENT
---------- ----------------- --------------- ------------------
         1                 0              10                  1
         2               2.5              10                .75
         3                 5              10                 .5
         4               7.5              10                .25
         5                11              10               -0.1

Unfortunately, due to a quirk of the data (user input errors), the deficiency_length is occasionally longer than the sidewalk_length. While, in theory this is impossible (a sidewalk's deficiency should never be longer than it's length), in reality, it is something that happens that is beyond my control (the application is not customizable).

The result is a negative number in the condition_percent column (see insp_id #5) which is not a valid percentage. Instead of outputting a negative number, I think the best way to handle negative percentages would be to output a value of 0 (0%).

What is the simplest way to do this?

Or you could just use GREATEST with 0:

select
    insp_id,
    deficiency_length,
    sidewalk_length,
    greatest(0, 1 - (deficiency_length / sidewalk_length)) as condition_percent
from
    user.sidewalk_insp;

The above function returns 0 or calculated condition percent, whichever is greater.


Why does Publisher need to be able to connect to Remote Distributor? I have Transactional Replication set up with a Remote Distributor and a Pull Subscription. So as I understand it, at one end I have the Publisher, and at the other end I have the Distributor, Log Reader Agent and Subscriber. Therefore, all activity is initiated from the Subscriber end. The Log Reader Agent monitors the Publisher, and the Distribution Agent monitors the Distribution database at the Distributor.

Given this architecture, why during the configuration stage does the Publisher need to be able to connect to the Subscriber / Distributor? If the Subscriber / Distributor server can "see" the Publisher, why isn't this enough, as it seems all activity is initiated at the Subscriber / Distributor end?

I believe whenever transactional replication is initialized, the Publisher snapshots the articles in the publication and pushes it across to the subscriber. The remote Distributor and pull subscriber are used for the transactions after replication has been setup and initialized.


Dutch characters in DB2 problem Ok, first of all, I have really limited knowledge about databases.

Problem that I have is next: I inserted few rows with data that contains Dutch characters, such as names of different countries. Some countries have at the end of their names Dutch language special characters ë, or like Ukraine have two dots on i (Servië, Oekraïne, Tunesië, Somalië, etc...).
These characters in database looks like this: ë -> A≈ and ï -> AZ.

Of course I would like that these Dutch characters shows in database.

Well, the trick is that a database can only specify which "locale" it is used for at creation time. When you create a database you either specify what you want by specifying the codeset, territory and collation (example CREATE DATABASE MYDB AUTOMATIC STORAGE YES ON '/data' DBPATH ON '/dbdir' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM), or you let DB2 guess based on some system settings.

You can check what yours is set to by running db2 get db cfg for <db name>. In the case of what was used above you would see the following:

Database territory                                      = US
Database code page                                      = 1208
Database code set                                       = UTF-8
Database country/region code                            = 1
Database collating sequence                             = IDENTITY
Alternate collating sequence              (ALT_COLLATE) =

Note, that you cannot change these settings after a database is created. One option would be to export the data from your database (using db2export), drop and rebuild the database the with language settings you want, and then re-import the data (using db2import).

Another option would be to see if there is a way to represent your character strings as pure "bit" data and store the bit sequence rather than the "string" or declare a field as VARCHAR FOR BIT DATA and store your strings as bit sequences (though I'm not 100% positive on that one...).

Also check out the links below as they have more information on the language thing and how to deal with multi-code-page results. Note that some of this information requires DB2 to be at least at version 9.5 or higher.

Choosing the code page, territory, and collation for your database
Unicode character encoding
Character conversion between different code pages

as well as check out the whole Multicultural support parent/sibling links related to that list link (the Character conversion between different code pages).


SQL Server : what licence is required? I want to use SQL Server for a big project and was wanting to know what licence is needed, Enterprise or Standard?

I have 2 servers and I'm looking to have the first as a primary and the second as a mirror (as a reporting server and DR server), from what I can see on Microsoft's comparisons I should be able to do standard but am I missing anything?

I understand I would need to manually promote the mirror in the case of DR, but would enterprise make it easier/faster?

You should call a Microsoft licensing expert and talk to them. They would be best person to resolve all your queries. Microsoft licensing is bit comlex and varies per environment.

  I have 2 servers and I'm looking to have the first as a Primary and the second as a mirror (as a reporting server and DR server), from what I can see on Microsoft's comparisons I should be able to do standard but am I missing anything?

You can use standard edition in SQL server database mirroring but you cannot use Snapshot functionality to get report as Mirror server is always restoring and you need to create database snapshot on mirror to run reports but snapshot is enterprise only feature. If you use transaction log shipping with Secondary database in standby mode you can run reports although when log backups will be restored users will be disconnected.

  I understand I would need to manually promote the mirror in the case of DR, but would enterprise make it easier/faster?

Whether you need to manually promote mirror to principal or it does automatically depends on your mirroring configuration. If you have witness with automatic failover it will take care of automatic failover in most cases IF it is able to form QUORUM. Also database comes online after second phase of recovery in Enterprise edition but after 3rd phase in Standard edition. There are 3 phases of recovery analysis redo and undo.

PS: Please tell me if any further clarification required


how many cores uses gbak (firebird) on restoring? I'm restoring db, and I see that firebird uses only one core of my CPU for gbak and fb_smb_server:

Is it normally? I've 4 cores in my CPU.
CpuAffinityMask parameter in firebird.conf is commented out.

As indicated in the tickets linked by Val Marinov in the comments (CORE-1365, CORE-2992 and CORE-3958), a restore in Firebird is currently entirely linear and will only use a single CPU core. There is nothing you can do to change this, short of sponsoring/funding an improvement to Firebird to get this into a next version with priority.


Optimal database structure for fast inserts I am using MySQL Percona server.
This table needs to do fast bulk inserts using 'INSERT INTO ... VALUES' for smaller requests (about 100k records) and using 'LOAD DATA INFILE' for larger (up to 10kk records).
Also, there will be selects with full table scan (using indexes there will be like 10-30 million of records out of total 100kk records), they also need to be done as fast as possible.
I tried myisam and got around 50k records inserted per second, with InnoDB that was only about 25k. This numbers are OK, but i would be glad, if i could improve them.

How would you organize such table and what setting to mysql server you would apply? We've got a server with 128G of RAM, i think it might be helpfull.

I did some research on this matter, but still feel myself uncompetent. If you have managed similar tables - please, share your expirience, thank you.

The problem with MySQL's InnoDB engine is that the storage architecture is essentially using index-organized tables, which will unfortunately experience performance issues for large numbers of inserts when the target table is large.  The only case in which this can be mitigated is if you have sequential inserts, that is, the primary key of the set of inserts follows a sequence.

For high ingestion rates of data, different technologies are recommended, such as Cassandra which uses a log-structured merge tree, or in your case, I might recommend TokuDB, which has been acquired by Percona, and which uses a technology called a fractal tree index, which is essentially creative way to cache, pre-sort, and batch insert into your index-organized table (of course, there's much more to it than that, but no need to go into the details here.)

Long story short, if you have moderately high ingestion rates, you need a heap organized table structure implemented by databases like Oracle and PostgreSQL, and if you have very high ingestion rates, you need to use a database like Cassandra or TokuDB.


MariaDB multiple auth modules for a user? MariaDB has pluggable authentication, like PostgreSQL. Unlike PostgreSQL, I can't figure out how to support two forms of auth for a user. For instance, in PostgreSQL we can have the following in our pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             all                     md5
local   all             all                                     peer

This permits

all users to connect using password auth
all users that are local to connect using the peer method

How do I make it so all local users can connect over Unix sockets if they're authenticated with the operating system, and with a password in the event one is set in the database?

MariaDB provides

mysql_native_password
unix_socket

It seems the mysql.user only supports one "plugin"

MariaDB [mysql]> SELECT user,host,plugin FROM user;
+------+-----------+-------------+
| user | host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+
1 row in set (0.00 sec)

As you said, a given user can only support one plugin. It is described in more details in the MySQL manual.


Is there any other way apart from sa to login to a migrated instance where the target environment is in a different domain? We are migrating sql server instances from one domain to a different domain. New AD accounts/groups will be used in the new domain so SQL logins and database users in the source SQL instances will be unusable in the target instances.
Migration is by taking a snapshot backup of the host server and restoring to the target environment, which means the SQL instances have come across with the server images.
Having logged in to the migrated host server with new credentials, we now need to login to the SQL server to configure the security with new AD accounts.
Is there any other way of login in to the SQL server apart from doing so as sa with SQL authentication?
I am unable to use SSMS even in single user mode, as there is no valid AD login that can be used.
Does anyone have any other ideas please? Thank you so much.

To avoid this, I usually prefer to set up a fresh installation of an instance in the new domain. Then I restore the databases. Then I script proper instance configurations/agents/other things, adapting them to the new domain.
As suggested by @Ronaldo, you can enter in this emergency situation using:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-2017
Please, take a look at this guide in case of domain change:
https://social.technet.microsoft.com/wiki/contents/articles/24960.migrating-sql-server-to-new-domain.aspx
It's quite old but the general overview is fine.
This guide to regaining access is also fine:
https://www.sqlservercentral.com/articles/regaining-access-to-sql-server-after-changing-the-domain


DDL changes Tracking Other than DDL triggers and change data capture, is there any way to track history of DDL changes in SQL server. I also want to know the client IP which made the respective DDL change. Kindly guide me .

I have used this script from David  Rueter, its pretty good have changelog script used and login name also.

https://github.com/DavidRueter/SQLVer/
http://www.sqlservercentral.com/articles/Version+Control+Systems+(VCS)/119029/


how to get History of queries executed with username in SQL I can get executed queries by using:

SELECT deqs.last_execution_time AS [Time]
    ,dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

but I am also looking for username column, who executed these queries.

I would suggest you to create a server side trace or enable SQL Audit to track down activity from users that you don't trust.

Remember that DMV data gets reset if the DMV is cleared out, SQL Server is restarted, etc.

The closest you can get is using below query:

/******************************************************
Script : Findout Who did what ?
Author : Kin Shah .. written for dba.stackexchange.com
*******************************************************/
USE master
go
SELECT sdest.DatabaseName
    ,sdes.session_id
    ,sdes.[host_name]
    ,sdes.[program_name]
    ,sdes.client_interface_name
    ,sdes.login_name
    ,sdes.login_time
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.ObjName
    ,sdest.Query
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
    ) sdest
where sdes.session_id <> @@SPID
--and sdes.nt_user_name = '' -- Put the username here !
ORDER BY sdec.session_id


Is bulk-logged recovery model ever a good choice if point-in-time restore is not needed? Is the Bulk-Logged recovery model in SQL Server ever preferable to Simple if point-in-time restore is never needed? I have read many articles about transaction log models, but cannot find a clear answer to this. The database in question has a lot of activity during nightly loading, but the data doesn't change after that. I am doing a full backup daily. Wouldn't Simple be the best choice here, while also potentially using less storage?

No, use simple

Bulk-Logged is a special case of FULL, not an enhanced version of SIMPLE.

Bulk logged requires log backups otherwise the transaction log grows (as per full). This takes more space.
Simple does not require log backups: the log will (normally) be cleared by the DB Engine.

Bulk logged with minimally logged operations also requires the data files to be present. Full does not because each row changes is logged. This introduces another complexity.

So use SIMPLE if you can restore to the last full database backup

If you have a nightly load, then using DIFF backups is probably enough to give you extra restore capabilities.

Note, I don't think I've ever used bulk logged recovery since SQL Server 6.5


Best architecture solution for heavy data mining system, that can run on cloud We are currently hosting our MySQL database on a dedicated server with lots of memory and processors. We would like to push it to a different architecture that would allow us to scale it easily, specially on the part of computing. The service that we are currently testing analyses mobile data. We would like to be able to use cloud system to perform all the computations depending on the current need. Can you point us in a right direction in terms of system architecture. What kind of server scenario would be best for such tasks.

The question is quite open to discussion with such few details. What kind of calculations are performed with what density? What kind of an application server are you using? What amount and what kind of data comes in and goes out? What's the acceptable latency between the application server and the database / clients and application server? Thus, are there any requirements to keep the application server in-house / will it need to be clustered? Will there be any business logic implemented in the database as stored procedures and stored functions? If so, will it be in the native SQL dialect, or will it be in any external language like C/C++/Python/Java etc?

I won't be able to single out and suggest a provider or strategy, due to the lack of details and due to the fact that (for the last few years) I'm generally managing DB servers deployed in-house or on dedicated servers on the cloud.
But first of all, I strongly suggest you phase out MySQL.

Having the power of Oracle or the ease of use of MSSQL would be nice, of course, but I won't push such a proprietary system with (possibly) high cost of licencing.

Instead I strongly suggest moving towards PostgreSQL. Especially for stability and scalability issues.
But the strategy to pick (will it be rented service, a platform or rented dedicated servers? Will there be a cluster, and if yes, which kind? What should be the population of the cluster, and what kind of network topology should be implemented) is all up to the details.
It's kind of hard to suggest more than saying "build a cluster of PostgreSQL DB servers, consisting of a couple of dedicated hardware, within the compounds of a trusted infrastructure provider" with so few details. And maybe: "Keep an acceptable amount of business logic in stored functions and procedures if the application server will be residing in-house and the network latency between it and the DB cluster will be an issue."
Maybe you need more, or maybe you don't even need all that hassle.

And if by an unlucky decision, you end up sticking with MySQL, do not even think about using MyISAM.


Give privilege to new created databases in MySQL I have user "U" who I want to give him full access to all the existing databases except "db1". I want him to only have select privilege for that db. What I did is I gave "U" all permissions for each db one by one and select permission on "db1". What I want to do is to give "U", the create database privilege and full access to any created database by him. Is there anyway that I could do it? I don't want to add privileges every time someone creates a db.

It is impossible to do this using permissions only .

The only way is to create a stored procedure as described here

If you want to avoid stored procedures, a workaround is:
GRANT ALL PRIVILEGES ONtestuser_%. * TO 'testuser'@'%'; (as suggested here); however, this has the problem that the users must then be very careful in naming their databases.

For example if user aaa creates database bbb_xyz, it can then be accessed exclusively by user bbb but not by user aaa.


SSIS database source imports as BLOB incorrectly Friends,

I am a beginner user to SSIS (Microsoft SQL Server Integration Services). While I have experience coding in other languages (Python, MATLAB, R, and others), I have never experienced a language whose data types are so confusing to me.

I have uploaded data to a database that I created using a Data Flow object. The data type of the column in SSIS is a Unicode string [DT_WSTR] and the data type of the column in the database is ntext.

After uploading the data to the database, I am retrieving it from that database in a new Data Flow object. However, when it loads the data from the database into SSIS, it is now a Binary Large Object Block (BLOB). It seems near impossible for me to be able to do any sort of transformation to the column with it as a BLOB object.

Is there any way to either convert the BLOB object to DT_WSTR? Or to a useable datatype?

Any help is appreciated!

The table definition that it appears you let SSIS create defined a column as ntext. NTEXT, besides being deprecated, is a large object type so it's reasonable that when you extract data from that source, SSIS will simply call it BLOB because it doesn't matter to the data flow engine why it's large, it just is.

You can either fix your table to be a non LOB type and then update the loader process to account for it.

Approximately

ALTER TABLE dbo.MyTable ALTER COLUMN Foo nvarchar(4000);

Or you can perform an explicit cast when you extract data from the table to make it into a "regular" string.

Approximately

SELECT CAST(T.Foo AS nvarchar(4000)) AS Foo FROM dbo.MyTable AS T;


Why mongodb never end some connection My mongod runs out of connections from time to time. But when I restart the instance, it becomes normal.

I check the log and found some connections were created and never be ended. This cause the connection depleted quickly.

My mongo cluster consists of  of 3 shards, each with 3 replicasets. So there are 9 mongod instance and 5 mongos instance. The problem occurs in one of the mongod instance.

I am running mongo version 2.4.

Here is part of the log:

Tue Sep  1 23:24:09.245 [initandlisten] connection accepted from 10.1.1.2:35569 #987213 (291 connections now open)
Tue Sep  1 23:24:09.687 [conn987214] end connection 10.1.1.1:36381 (339 connections now open)

Find out how many connections are there to the database vs how many available.  This command in Mongo Shell will tell you exactly that:

db.serverStatus().connections


How postgresql full text search disable file parser? select to_tsvector('english_nostop', 'u,s') @@ to_tsquery('english_nostop', 'u<->s');

return t, while
select to_tsvector('english_nostop', 'u.s') @@ to_tsquery('english_nostop', 'u<->s');

return f
Debug with this:
SELECT alias, token, lexemes FROM ts_debug('english_nostop', 'u.s');
alias | token | lexemes
-------+-------+---------
file  | u.s   | {u.s}

and
audiobook=> SELECT alias, token, lexemes FROM ts_debug('english_nostop', 'u,s');
alias   | token | lexemes
-----------+-------+---------
asciiword | u     | {u}
blank     | ,     |
asciiword | s     | {s}

How can remove such behavior. To make . like ,?   The alias file seem to think u.s as special one.
Here is my configure:
CREATE TEXT SEARCH DICTIONARY english_stem_nostop (
Template = snowball
, Language = english
);

CREATE TEXT SEARCH CONFIGURATION public.english_nostop ( COPY = pg_catalog.english );
ALTER TEXT SEARCH CONFIGURATION public.english_nostop
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, hword, hword_part, word WITH english_stem_nostop;
ALTER TEXT SEARCH CONFIGURATION public.english_nostop DROP MAPPING FOR asciihword, hword;

And we can't simply remove file, this will remove the total text:
ALTER TEXT SEARCH CONFIGURATION public.english_nostop DROP MAPPING FOR file;
SELECT alias, token, lexemes FROM ts_debug('english_nostop', 'u.s');
alias | token | lexemes
-------+-------+---------
file  | u.s   |

Which is wrong too.
And if we ALTER MAPPING for file, we got
SELECT alias, token, lexemes FROM ts_debug('english_nostop', 'u.s');
alias | token | lexemes
-------+-------+---------
file  | u.s   | {u.}

Still not my purpose.

You would have to make your own parser.  This is not easy to do.  You would also have to decide what features of the parser to keep and what to throw away.  It isn't clear to me that the file token type is the only one that will give you problems.  There is a test module 'test_parser' which could serve as an example of creating your own, but it much too simple, while the default one is much too complex.
create extension test_parser ;
CREATE TEXT SEARCH CONFIGURATION testcfg ( PARSER = testparser );

A more pragmatic solution might be to just push the text through replace(col_name,'.',' ') before passing it to to_tsvector.  If you were using plainto_tsquery, etc., you would also want to pass the query through that, but since you are just using to_tsquery, you would not need to do that.


How to make DDL changes to large tables and cause minimum server and object contention? I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database but it's hard for me to implement them because the database can afford very minimal contention. (Essentially if a heavy query is running for more than a minute or two it has to be killed.)

Even during a maintenance window, these changes would be just way too long and would exceed my allocated time (at most 1 hour, since there aren't really any off hours).

Types of changes I want to make are create indexes, add persisted computed columns, create indexed views, and general index tuning. If there was a way to do any of these operations iteratively and pause between iterations then I could get away with the total time taking a while because at least I can allow other processes to run in between, instead of a backlog being built up.

The only idea I can think of is if I maintained a copy of the database on a separate server where I can make DDL changes, then re-point my applications to that server. Then update the first server with the DDL changes so it's in sync, and then the next time I need to make an update, I can repeat the process.

Edit: I'm on SQL Server 2016 Enterprise Edition.

The best thing I have found to help in big index creation is to (1) have enough RAM, (2) have a slow time before the index creation starts, and (3) perform a SELECT...INTO of the index fields into a temp table with an ORDER BY of the desired index order right before creating the index. This can speed the process by up to 75% in some cases (which I have not fully identified to date).

In addition, if you can add an otherwise unused high-performance drive or drive array (SSD preferred) and create the index on a filegroup on that drive array, this can greatly improve the index creation performance.

For modifying an existing index, use the above tips to create a new index, then DROP the existing index after examining index usage on the new index for a time. (Why? I've modified some indexes in ways that should have increased performance, only to find overall performance degraded. When I create a new index, I can monitor if SQL Server wants to use it, and/or check performance by forcing the index.)

Of course, since you have SQL Server 2016 EE use the ONLINE=ON option. This minimizes locking when creating indexes, and is best for non-clustered index creation or alteration. Clustered indexes will reorganize the whole table, so it will take some time no matter what happens.


Checks to be done, before/after changing the windows login password? Here we have a scenario where a Service account , added under security as sysadmins for windows login, going to have its password change as per windows policy.

Since windows team, server admins are going to change the password, where should i check or what pre-activity should i consider before the password is changed.

This account (windows domain) is running the SQL services as well.

Read this MSDN page for details about what to do on the SQL Server box.  This is taken from that page:

The SQL Server Database Engine and SQL Server Agent run on a computer as a service using credentials that are initially provided during setup. If the instance of SQL Server is running under a domain account and the password for that account is changed, the password used by SQL Server must be updated to the new password. If the password is not updated, SQL Server may lose access to some domain resources and if SQL Server stops, the service will not restart until the password is updated.
Change the password of the accounts used by the Database Engine and the SQL Server Agent in SQL Server 2012 by using SQL Server Configuration Manager.

If applications or other services are connecting to the SQL Server using that domain account, they will also need to know the updated password.  You may want to look at enabling logon auditing to help understand where those applications are.


Mysql Cluster vs Percona/Maridb Cluster We want to setup a mysql cluster with master-master sync replication, to provide HA so that one db for write from one app, other for write from other app. It is a webapp with high write and medium read

We are considering adding mysql cluster and considering percona vs mysql cluster.

Can MySQL ndb cluster import innodb dump ?

Can ndb cluster support MySQL async and 2-way sync replication ?

Can ndb guarantee consistency since it does not write to disk? I.e. What if there is 2 node cluster and both go down and data is not flushed to disk

can ndb cluster support 4-5 table join, ? Will the tables be in same node or will there be any network latency due to distributed joins

Does ndb cluster support MySQL-fabric Python api ?

Does ndb cluster support multi-master bi-directional sync?

Does percona cluster support above items ?

It's hard to say which is "better" -- Percona's PXC or MariaDB w/Galera.  Both use Galera as the underlying "cluster" technology and HA.  Percona is based on Oracle's 5.6, MariaDB is slightly forked back around 5.5 timeframe.  But both are great.

In any case, you really need 3 servers to get HA.  The 3rd is the tie-breaker in case one goes down.

Furthermore, if HA is a big priority, the think about tornadoes, earthquakes, floods, etc.  The 3 machines should not be in the same geographic location.

Bottom line:  Flip a coin for picking between them.  (I'm eager to hear from those who have a strong bias, but if you do, please state whether you have ties to one or the other.)

Don't use NDB Cluster -- it is too far from the main path of things.  There is a lot of setup required, and it has a bunch of limitations.


About UUID and generators in PostgreSQL I'm thinking to use UUID as PK for all tables into my new PostgreSQL database, for such a goal I will be using uuid_generate_v5 and uuid_ns_dns or uuid_ns_url functions, but I have some doubts:

is there any problems using UUIDs only in a database (i.e. performance)?
is there any pros and cons respect to use uuid_generate_v4?
When to use uuid_ns_dns or uuid_ns_url? (I haven't found info about it)
can name parameter for uuid_generate_v5 function be any string (even an empty string) or it depends on namespace parameter? Examples, please.

Answers to your questions:

There are no problems, but the performance will be somewhat worse than using a sequence generated bigint primary key, because adding one to an integer number has to perform better than anything more complicated. Also, a uuid takes twice the storage space (16 bytes).
You should benchmark if that makes a noticeable difference for you.

Apart from the performance impact, it makes no difference.

It does not matter, they just return different constants.

The name parameter can be any string. That function is IMMUTABLE, so you have to make sure that you use different names if you want different UUIDs.

Use UUIDs if you need to generate artificial primary keys in a decentralized fashion, for example in the application code or in different database if you use sharding. If the primary keys are generated in a single database, use a sequence.


Figuring out database timezone Does anybody know of a way to alter a time zone on a SQL database?

Is there any tool that could find the objects using the date and time?

Assuming SQL Server

getdate gives local server time
getutcdate gives utc / gmt

There is no timezone setting or changes possible inside SQL Server