Can't Delete Orphaned MySQL Temp Table One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes).

ls -l *sql*
-rw-rw---- 1 mysql mysql  8570 Mar 13 12:05 #sql-ib32694.frm
-rw-rw---- 1 mysql mysql 98304 Mar 13 12:05 #sql-ib32694.ibd

mysql> drop table `#mysql50##sql-ib32694`;
ERROR 1051 (42S02): Unknown table 'hdb.#mysql50##sql-ib32694'

Running the alter table on the original table again:

ERROR 1050 (42S01) at line 1: Table 'hdb/#sql-ib32694' already exists

???

I followed all the instructions here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

also tried innodb_force_recovery = 4

So I deleted those files manually (getting the typical entry in error.log for missing tablespace), recreated them in another DB, copied them over to this and nothing changes.

No success. I had some issues like that some time ago and I managed to remove it. This tiny little table is really stubborn though.

I can delete it, but then I get the same error message like for orphaned tables (in the error.log)

Could not find a valid tablespace file for 'hdb/#sql-ib32694' ...

Also - different to what you are writing here

  Don't worry about the temp table name being used again. Look at the name of the temp table

  #sql-7a87_230c32.ibd

  There are 10 hexadecimal digits. The chances of reusing that temp table name is 1 in 1610 or 240 which 1,099,511,627,776. I think you will be OK in this.

when I do the ALTER TABLE on the table I ran it on what created the temp file, I get the error message (after I deleted the table)

ERROR 1050 (42S01) at line 1: Table 'hdb/#sql-ib32694' already exists

so it seems MySQL is using the same temp table name when you do an alter table.

No master/slave here.

Since it is a temp table, you are OK to just delete the files.

rm -f *#sql-ib32694.*

Is this harmful to InnoDB ? No. There was a data dictionary entry still inside ibdata1. However, that entry is either missing or invalid (due to the tablespace_id). There is actually a one in 1,099,511,627,766 chance of it causing a problem.

See my older posts on why it is not that harmful and how to deal with

Jun 04, 2014 : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave
Dec 23, 2014 : #1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

Please keep in mind that temp tables (regardless of storage engine) are transient in nature. A temp table will disappear under these circumstances:

When a query is finished using it
When a DB connection terminates normally
When a DB connection terminates abnormally

If a temp table still exists due to a crash, rest assured that the data dictionary no longer acknowledges its existence. Please delete them and keep a good conscience.

If you are worried about, your only recourse when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow. The lesson here

Use CREATE TEMPORARY TABLE (...) ENGINE=MyISAM; if you have to use temp tables in your app.
Any system-generated InnoDB temp tables can be deleted after a crash.


How to apply Static Data Masking With Replication of Database We used to have multiple environments for applications such as Production, Staging, UAT, Dev, and Sandbox. It is a regular task for the DBA to refresh the lower (staging, test, UAT etc.) environments with a backup of the production database backup. Sometimes we need to share the database backup with the external vendors as well.
How to Apply Static Data Masking With Replication of Databases , i.e for every change the replication database to to apply mask and save masked records in staging, test, UAT etc.
Is there any tools or process or methods available ?

These are out of the box features of SQL Server so you'll essentially need to follow the Microsoft Books Online and other resources on setting them up. Replication has a lot of small steps and basic prerequisites but it's essentially a wizard, so it's rather simple if you just follow the documentation carefully.
Here's some helpful references for setting up Replication:

Microsoft Books Online - SQL Server Replication

Quick walkthrough on setting up Replication

More in depth walkthrough for setting up Replication

And here's some good resources on implementing Static Data Masking:

Microsoft Books Online - Static Data Masking for Azure SQL Database and SQL Server

SQL Server Static Data Masking Example

Static Data Masking in SSMS 18

Please note that Static Data Masking is only available in SQL Server 2019 (or equivalent Azure SQL Database version). So if you're on an older version of SQL Server, the alternative option available is called Dynamic Data Masking.


Isn't global temporary table global? I've doing some tests in SQL Server and Oracle.

When I created a temporary table in SQL Server I don't needed grant select on the temporary table for the specified user. Any user get to query the temporary table.

In Oracle I need to issue grant for the users.

Is this the behaviour? I always need to issue grant on the temporary table?

After this I thought that one way to have the same behaviour of SQL Server would be grant to public role.

A global temporary table is global, because its definition is persisted an other sessions can use the table as well, given that they have the necessary privilege on it. The content of the table is of course specific to the session using it.

Creating Global Temporary Tables

  Global temporary tables are permanent database objects that are stored
  on disk and visible to all sessions connected to the database.

Yes, you need to grant the privileges on it for other users or roles, if you want this to be accessible to them.

Oracle did not have other any other type of temporary table, up until 18c, which introduced Private Temporary Tables, which are similar to temporary tables in SQL Server:

Creating Private Temporary Tables

  Private temporary tables are temporary database objects that are
  dropped at the end of a transaction or session. Private temporary
  tables are stored in memory and each one is visible only to the
  session that created it.


How to identify a client application whose host_process_id points to sqlserver.exe I am in the process of trying to identify a client application which uses one of the SQL Servers I support (2008 R2).  Periodic queries against sys.dm_exec_sessions regularly return sessions whose login_name is, variously, either one of two non-system SQL-authenticated accounts, or a single, specific, non-system local Windows account, on the machine that hosts the SQL Server instance.  The value in program_name is 'Microsoft SQL Server'; the value in host_name is the name of the server in question; and the value in host_process_id points to the sqlserver.exe process on the same server (as cross-referenced via Task Manager).

My question is, what mechanism  could be initiating these sessions using the host_process_id associated with the engine executable itself?  I realize program_name is configurable by any application, but I didn't think that was true for host_process_id.  A SQL Agent job would connect from a separate executable, as would sqlcmd or the data collection utility.  To my knowledge, we don't use Service Broker for any user applications.  Could it be coming from another engine instance as a linked-server connection?   I feel like I'm missing something that should have been obvious.  How can I track this down?

There are multiple session_ids, all greater than 50, and all with user-created login names, not 'sa' or any account typically used by an internal process. The sessions also run queries against application tables in user databases.

Since the sessions are using user-created logins and are accessing application tables in user databases, and since the host_process_id aligns with the sqlserver.exe process, it sounds like there is a SQLCLR assembly running code.

Use the following query to look for SQLCLR Assemblies in all databases:

DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
UNION ALL
' END + 'SELECT DatabaseName = ''' + QUOTENAME(d.name) + ''', a.*
FROM sys.assemblies a
WHERE a.name <> ''Microsoft.SqlServer.Types''
'
FROM sys.databases d
ORDER BY d.name;
SET @cmd = @cmd + ';'
EXEC sys.sp_executesql @cmd;


Removing "duplicates" within a time range I have a table that contains number plate data with a date and time stamp, collected by a plate-reading CCTV camera at a site entrance. Vehicles stopping at the site entrance will create multiple entries of the same plate for a short period, with slightly different entry times, as in the example below:

PLATE  | Date_in   | TIME_IN
DE54RPY|2013-08-29 | 14-24-30
DE54RPY|2013-08-29 | 14-24-36
DE54RPY|2013-08-29 | 14-24-42
DE54RPY|2013-08-29 | 14-24-48

I found another solution on this site that suggested using this to filter out only the minimum timestamp for each plate:

select b.*
from (
    select plate,date_in,MIN(time_in) time_in
    from tblin
    group by plate,date_in
) A
inner join tblin B
    using (plate,date_in,time_in)

The issue arises when a vehicle leaves the site and returns a while later, producing more entries in the table. These later entries will also be removed by the example solution above.

I need to find a way of filtering out a single instance (minimum or maximum) of a plate detection within a certain time period (e.g: a few minutes), and I'm not sure how to approach this. Could anybody here help me?

The easy way to make this work is to add an AUTO_INCREMENT to your transaction table and then to look for instances where the license plate value changes.  Consider this schema and query:  (you can also see it on SQL Fiddle)

This solution works by taking advantage of the fact that records are inserted in order and that a sequential number can be used to find adjacent records.  The records you're interested in are the ones where the sequence is disrupted.  In your case, that happens when the license plate value changes from one record to the next.

CREATE TABLE PLATE_VIEWS
( `SYSKEY` int not null AUTO_INCREMENT
, `PLATE` varchar(7)
, `DATE_IN` datetime
, `TIME_IN` varchar(10)
, PRIMARY KEY (SYSKEY)
)
;

INSERT INTO PLATE_VIEWS
    (`PLATE`, `Date_in`, `TIME_IN`)
VALUES
    ('DE54RPY', '2013-08-29', '14-24-30'),
    ('DE54RPY', '2013-08-29', '14-24-36'),
    ('DE54RPY', '2013-08-29', '14-24-42'),
    ('BDDX987', '2013-08-29', '15-55-05'),
    ('DE54RPY', '2013-08-29', '17-05-33'),
    ('DE54RPY', '2013-08-29', '17-05-48')
;

Now this query gets you the first capture of each plate:

SELECT
  A.SYSKEY
, A.PLATE
, A.DATE_IN
, A.TIME_IN
FROM PLATE_VIEWS A
LEFT OUTER JOIN PLATE_VIEWS B
ON A.SYSKEY = B.SYSKEY + 1
WHERE A.PLATE <> B.PLATE
   OR B.PLATE IS NULL

If you can't change the schema of your table to add an AUTO_INCREMENT column, then start by creating a temporary table that includes the auto increment and fill it with the range of plate scans that you are interested in.  Then run the above query against the temporary table.

EDIT: In and Out

OP says there are two lanes, one for coming in and one for going out.  If each lane has a camera and both capture transactions to the same table, you can catch the situation where a car leaves and comes back without any different cars coming in the meantime.  If we change the column names to be more generic (DATE_SEEN instead of DATE_IN etc.) and add a LANE column which says "in" or "out", then you can change the query like so:

SELECT
  A.SYSKEY
, A.PLATE
, A.DATE_SEEN
, A.TIME_SEEN
, A.LANE
FROM PLATE_VIEWS A
LEFT OUTER JOIN PLATE_VIEWS B
ON A.SYSKEY = B.SYSKEY + 1
WHERE A.PLATE <> B.PLATE
   OR A.LANE <> B.LANE
   OR B.PLATE IS NULL

This will catch a car that leaves and comes back.  (See SQL Fiddle)

This query also shows when cars leave.  If you want to ignore when cars leave, just change the WHERE clause like so:

WHERE (A.PLATE <> B.PLATE
   OR A.LANE <> B.LANE
   OR B.PLATE IS NULL)
  AND A.LANE = 'IN'


How to determine which one is my default instance in MS SQL Server? I have two instances in my server and I want to know which one of them is the default. Can you, please, point out ways I can recognize it.
By saying a default instance I mean the one that listens for 1433.

Thanks for your support.

login in to your server and run this

 SELECT SERVERPROPERTY('InstanceName')

for sql server 2008 and above if it returns null then it's a default instance else named one

  InstanceName: Name of the instance to which the user is connected.

  Returns NULL if the instance name is the default instance, if the
  input is not valid, or error.

  NULL = Input is not valid, an error, or not applicable.

  Base data type: nvarchar(128)

Ref

Another one using SEVERPROPERTY(),

Run the below code

SELECT SERVERPROPERTY('ServerName')

SELECT SERVERPROPERTY('MachineName')

if both returns same value ie machine then it's your default instance else
the first one return with your machinename\instance name

After your edit

if you want to check the SQL server service which listens to port 1433. Then you have to check that in SQL server configuration manager. Check-in SQL server network configuration in that protocols for SQL server and click properties for TCP/IP and see the port used under IP addresses


mysqldump insert data to exists table hellow

Dumped x.sql file from db and then
trying  to find way to insert data from x.sql file to existent tbl in another server.
But mysql execute all the concept of the x.sql ( that include "drop table if exists tbl and create new")

mysql --host= --user= --password= schema tbl < x.sql

there is away may be to dump tbl (with out "drop and create new" commands only insert )?

If you can rebuild the x.sql, run mysqldump with suitable option such as --no-drop-tables, --no-creates (or whatever they are).  Then look at the dump file to make sure there are no surprises in it.

If you can't rebuild x.sql, then edit it to get rid of drops/creates/etc.

If there are no USE statements in x.sql, you can say mysql ... new_db < x.sql (after doing CREATE DATABASE new_sql).  Then play SQL games to copy the data from new_db.foo to old_db.foo.  (Provide more details if you need help in this area.)


Get all CONVERT(datetime) styles (formats) I know this is kinda stupid to ask but is there a Transact-SQL function or anything that returns all CONVERT(datetime) styles (formats)? Something like fnDateFormats will return all date formats:

Output:

Dateformat | SQL code
MM/DD/YYYY | 101

and so on.

There is no such built-in function – or system table, or view – that would give you a list of all styles (formats). Otherwise people would not have been creating custom-made style lists (like this one, for instance) – as a way of a reminder, I guess, because the same information can be found in the online documentation, even if slightly differently arranged.

Go ahead and create one for yourself. New styles are sometimes introduced with new major releases, but those do not happen too often, so maintaining such a list should not be much of a burden.

Another thing is whether you really need to have such a table. Unless you are still using a pre-2012 version, you can format your datetime values arbitrarily with the FORMAT() function.


Restoring a database, changing recovery model I regularly make a backup of my production database, then restore the same onto a test database, so developers can always have recent data for testing.
The production database uses full recovery model, but the test database would be better off with a simple recovery model.
So I thought to change the script, and after the database reload, running on the test database the commands:

USE master ;
ALTER DATABASE TESTDB SET RECOVERY SIMPLE ;

I read that the change in recovery model only has effect after a backup is taken.
However, if after the performing the above I do

SELECT name, recovery_model_desc
  FROM sys.databases

I can see that the recovery model is reported as simple for TESTDB.
Of course to be sure I could take a backup of TESTDB (and discard it immediately), but I would avoid it if possible.

Do I need to take a backup to actually change the recovery model of my test database to simple?

I read that the change in recovery model only has effect after a backup is taken. However, if after the performing the above I do

This is mainly for when you change from simple recovery to full. After changing from simple to full you need to take full backup to take database out of Pseudo simple recovery. Pseudo simple recovery means even if database is in full recovery model it will behave like simple and automatic checkpoint would happen and you will not get point in time recovery. The moment you take full backup you tell SQL database engine that Now I would manage log truncations by taking transaction log backup.

  Do I need to take a backup to actually change the recovery model of my test database to simple?

For your case since you are changed recovery model to simple so you DONT need to take any backup to actually make it behave in simple recovery. I am not talking about normal backup you take for your dev databases. You should continue taking normal full backup of your dev databases.


Is there a benefit/drawback to adding LIMIT 0,1 when performing SELECT on unique key field? I am using a MySQL database and perform many select like these:

SELECT MyField FROM MyTable WHERE MyKey = 'some_value';

The purpose is to retrieve a field a value if the corresponding row exists. MyKey is the only key in the table and it is indexed.

My question is: is there any benefit or drawback to adding LIMIT 0,1 to my statement?

If MyKey is NOT NULL (see Jannes answer) and defined as a UNIQUE index in the table DDL, the only benefit of adding LIMIT 0,1 is potential clarity to viewers of the query that this is only going to return a single result. Without them having to look at the DDL to know that it is unique. However, other Database designers will be looking at the DDL anyway.

If there is any overhead of adding LIMIT 0,1 it would be very, very minuscule.

Either way, adding it would be up to you. Do you need the clarity (how often do users view your queries)?


How can I restore a 10.91G table in Oracle Express? I've received a .dmp file of an enterprise database, but currently only have access to a free version of Oracle. I would ultimately like to import the data to PostgreSQL. But currently am wondering if there's a way to restore the largest table (10.91G) in chunks if need be, so I can export its contents to csvs to import into PostgreSQL. Currently if I try to selectively restore the 10.91GB table with

impdp system/pw tables=TEST.BIG_TABLE directory=test_data dumpfile=test_data.dmp log=log.log;

I get

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-31694: master table "SYSTEM"."SYS_IMPORT_TABLE_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-39776: fatal Direct Path API error loading table "SYSTEM"."SYS_IMPORT_TABLE_
01"
ORA-12953: The request exceeds the maximum allowed database size of 11 GB

The largest Tablespace I've been able to create so far was of SIZE 10500M, the command:
CREATE TABLESPACE test DATAFILE 'test.dbf' SIZE 10600M ONLINE; produced error ORA-12953: The request exceeds the maximum allowed database size of 11 GB

The results of select tablespace_name, sum(bytes) from dba_data_files group by tablespace_name are:

tablespace | sum(bytes)/1000000
-----------+-------------------
SYSAUX     | 692.06016
UNDOTBS1   | 398.45888
USERS      | 104.8576
SYSTEM     | 524.288
TEST       | 11010.048

The space a table takes up and the space it needs can differ.

I'd recommend using impdp with the sqlfile option to put the create table script into a file. Edit that to try to maximise the use of space (set pctfree to 0). Remove any primary key or unique constraint, as any index will eat into your available storage.

The INITIAL will be the amount of space initially allocated. Start with whatever is in the sqlfile output and nudge the value down until the create statement succeeds. Then use impdp with TABLE_EXISTS_ACTION=TRUNCATE so it will insert the data into the already created table.

Cross your fingers that there's enough room. If not, you can look at applying a WHERE clause as part of the impdp and split the data into two.


audit on object by user in Oracle Is there some way to audit, for example, a select in a specific table just by one specified user? something like this:
audit select on foo.table by soe;

this command return:
ERROR at line 1:
ORA-01708: ACCESS or SESSION expected

also, it's not possible using the suggestion:
audit select on foo.table by session by soe;

ERROR at line 1:
ORA-00933: SQL command not properly ended

We can use oracle fine grained audit (dbms_fga package).
But as always - any audit costs some additional work for database ))
Here is an example:
SYS@mydb> BEGIN
  2     DBMS_FGA.add_policy (object_schema        => 'scott',
  3                          object_name          => 'EMP',
  4                          policy_name          => 'EMP_SEL',
  5                          audit_column         => null,
  6                          audit_condition      => 'USER=''HR'''
  7                         );
  8  END;
  9  /

SYS@mydb> conn oe/oe
Connected.
OE@mydb> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20.02.1981 00:00:00       1600        300         30
....
      7902 FORD       ANALYST         7566 03.12.1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

14 rows selected.

OE@mydb> conn hr/hr
Connected.
HR@mydb> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00        800                    20
...
      7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

14 rows selected.

HR@mydb> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
         1

SYS@mydb> select *
  2    from
  3       xmltable( '/ROWSET/ROW/*'
  4                 passing xmltype( cursor (select * from dba_fga_audit_trail) )
  5                 columns
  6                 col varchar2(35) path 'name()'
  7                 ,val varchar2(100) path '.'
  8            )
  9  /

COL                                 VAL
----------------------------------- ----------------------------------------------
SESSION_ID                          120780131
TIMESTAMP                           25.11.2020 15:29:04
DB_USER                             HR
OS_USER                             ed
USERHOST                            WORKGROUP\ED
OBJECT_SCHEMA                       SCOTT
OBJECT_NAME                         EMP
POLICY_NAME                         EMP_SEL
SCN                                 22979107
SQL_TEXT                            select * from scott.emp
STATEMENT_TYPE                      SELECT
EXTENDED_TIMESTAMP                  25-NOV-20 03.29.04.646000 PM +08:00
INSTANCE_NUMBER                     0
OS_PROCESS                          5380:16020
STATEMENTID                         18
ENTRYID                             2
DBID                                2904675769

17 rows selected.

As you see the audit log contains only 1 row now from user HR, but not from OE.
Cleanout:
begin
   dbms_fga.drop_policy (object_schema        => 'scott',
                         object_name          => 'EMP',
                         policy_name          => 'EMP_SEL'   );
end;
/


Get closest previous date I need to get the posts closest to a date.

SELECT id
     , datetime
  FROM dates
 WHERE id != 150003
   AND datetime > FROM_UNIXTIME(1549622615)
 ORDER BY datetime
 LIMIT 1

Returns a correct result:

{"id":"150015","datetime":"2019-04-13 17:47:55"}]

but when I change to "datetime < FROM_UNIXTIME(1549622615)" I get the result:

{"id":"1","datetime":"1970-01-10 12:06:07"}

when there are many posts closer to the datetime.

Any insights why?

When you change it to datetime < FROM_UNIXTIME(1549622615) you also need to change the sort order to ORDER BY datetime DESC to get the closest date.


Why is BULK INSERT Considered Dangerous? I would like to understand why cyber-security teams in general (more than one organization I've dealt with) is dead set against granting BULK INSERT (e.g. TSQL) rights to applications and database programmers? I can't believe the "filling up the disk abuse" excuse, unless I'm missing something, as the end result is no different than an application doing something like:

for (long i = 0; i < LONG_MAX; ++i)
    executeSQL("INSERT INTO table VALUES(...)");

and INSERT is a common DML command that anyone with basic write permission can execute.

For an application's benefit, BULK INSERT is far more efficient, faster, and relieves the programmer of the need to parse files outside of SQL.

Edit: I originally asked this question on the information security site for a reason - it is not the DBA's that are against using BULK INSERT, it is the "Information Assurance" (IA for short - the Cybersecurity folks) that are forcing the issue. I'll let this question stew for another day or two, but if the bulk operation does indeed bypass constraints or triggers, I can see that being an issue.

Given that there are probably just as many unfounded fears as there are unknown risks, I would think that it is difficult to really say why a policy is in place without asking whomever created the policy why they are concerned.

However, I would guess that it probably has something to do with what BULK INSERT / SqlBulkCopy / BCP / OPENROWSET(BULK ...) allow someone to do, namely:

disable constraints (CHECK, DEFAULT, and FOREIGN KEY I believe)
disable triggers (if there are audit triggers in place, by-passing them would probably be deemed undesirable; for more explanation on this particular issue, please see @DVKs answer)

The various options are described in the following documentation:

SqlBulkCopyOptions Enum
BULK INSERT

I did not mention the table locking issue noted by @RDFozz since that is not specific to BULK INSERT: anyone can table a TABLOCK / XLOCK or set the TRANSACTION ISOLATION LEVEL to SERIALIZABLE.

UPDATE

I came across two additional pieces of information that might help narrow this down:

The issues of being able to disable Triggers, disable Constraints, and set IDENTITY_INSERT ON might not be an overwhelming reason to see ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS (starting with SQL Server 2017), or the bulkadmin server role as a threat. The reason is that in order to do any of those three things just mentioned, the User needs to have ALTER TABLE permissions on that Table, or on the Schema that the Table exists in. Ownership chaining does not cover DDL modifications. So, if the User doesn't have ALTER TABLE, then the ability to do these three things is a non-issue.
What hasn't been discussed so far, and what might ultimately be the security issue is that both BULK INSERT and OPENROWSET(BULK... access external resources, outside of SQL Server. When accessing SQL Server via a Windows Login, that Windows account will be impersonated (even if you switch the security context using EXECUTE AS LOGIN='...') for doing the file system access. This means that you can only read files that you have been given permission to read. Nothing wrong with that.

BUT, when accessing SQL Server via a SQL Server Login, then the external access is done in the context of the SQL Server service account. This means that someone with this permission could read files that they should otherwise not be able to read, and in folders that they should not have access to.

At minimum, if SQL Server was set up to run as an account created just for SQL Server (the preferred method), then such a User could read only those files that the "SQL Server" account has access to. While this is a limited problem, it still allows for reading files such as SQL Server log files (and I did test the following example and it does work):

SELECT tmp.[Col1]
FROM   OPENROWSET(BULK
   N'C:\Program Files\Microsoft SQL Server\MSSQLxx.InstanceName\MSSQL\Log\ERRORLOG.1',
                  SINGLE_NCLOB) tmp([Col1]);

Most people won't have access to the MSSQL\Log folder, so this would be a way to circumvent existing security restrictions.

And, if SQL Server is running as the Local System account, then I suspect that the scope of the problem only increases, and that a User with this permission would be able to read a wide range of system-related files.

AND, this is likely why the other methods of doing bulk imports — BCP and SqlBulkCopy — do not require the bulkadmin permission / role: they are initiated outside of SQL Server and will handle file system permissions on their own. In those cases, SQL Server never reads the file (or reaches outside of SQL Server), it just receives the data to import from the file that is being read by the external process.

Possible implications aside, it was said in the question:

  For an application's benefit, BULK INSERT is far more efficient, faster,..

Ok, go on...

  and relieves the programmer of the need to parse files outside of SQL.

Whoa Nelly. Let's stop right here. T-SQL is usually not the best choice of languages for parsing. It is often best to do the parsing before inserting stuff into the DB. One way to do this is to use Table-Valued Parameters (TVPs). Please see my answer to another question (here on DBA.StackExchange) that deals with topic of pre-parsing and validation along with efficient bulk importing of said data:

T-SQL: CSV->table pipeline with custom parsed numeric data, lookup values


After many successive and successful queries SQL Server starts refusing connections I have a GraphQL server ( Node v14.15.1 ) which uses typeorm to connect to a SQL Server 12.0.4100.1, also it runs inside a docker container, connection parameters are:
  let connection = await createConnection({
    type: "mssql",
    host: HOST,
    port: PORT,
    database: DB,
    username: USER,
    password: PASSWORD,
    // NOTE: Use typeorm-model-generator for Entities
    logging: false,
    synchronize: true,
    requestTimeout: 300000,
    connectionTimeout: 300000,
    entities: ["./models/mssql"],
    pool: {
      max: 1000, min: 1,
      idleTimeoutMillis: 3000,
      evictionRunIntervalMillis: 1500000,
    },
    options: {
      encrypt: false,
    }
  })

Problem:
After numerous sucessful queries the SQL Server starts refusing connections with the error:
ConnectionError: Failed to connect to adress: port - connect ECONNREFUSED adress: port\n
    at ConnectionError (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/errors.js: 13: 12)\n
    at Connection.socketError (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/connection.js: 1290: 56)\n
    at _connector.Connector.execute (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/connection.js: 1116: 21)\n
    at Socket.onError (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/connector.js: 140: 7)\n
    at Socket.emit (events.js: 198: 13)\n
    at emitErrorNT (internal/streams/destroy.js: 91: 8)\n
    at emitErrorAndCloseNT (internal/streams/destroy.js: 59: 3)\n
    at process._tickCallback (internal/process/next_tick.js: 63: 19)'

The problem happens when query a specific type which looking down the hierarchy has a lot of resolvers, also the amount of data varies.
The more data the more likely that the problem will occur.
What also interesting is that it is more likely that the problem will occur if the program is running inside a container or as PM2 service.
I've tested the same data where connections are refused inside a container and not refused in VS Code debugger or when run from terminal.
I tried fiddling with the pool options which gave modest results.
I've also checked that I do not exceed the amount of connections allowed by the DB.
I've checked the logs on the SQL Server, there are no issues or errors in them at the time I make requests. I've just now added support for tracking log attempts on the SQL Server and it now registers some 776 successful logins from this application in period 15:03:46.01 - 15:04:48.07 and after that I assume the I get the errors.
EDIT:
I've tried changing the Network Packet Size option in Sql Server options and packetSize connection options of typeorm -> mssql -> tedious which didn't work but revealed some new details. Since I had to reset the server apply new Sql Options, immediately after the reset the queries successfully finished.
So I tried tracking the resources and saw that the server ramps up to 100% processor capacity pretty quickly and after that has problems opening new connections and if I allow a too large max in pool options (in my case 500) of connections it start refusing connections.
When I bring down the max property, however, a new type of error arises which manifests as connection timed out from unknown reason this time stemming from tarn.js.
What I think is happening is that the tarm.js(underlying connection pool) has a default timeout for createTimeoutMillis/ adding new connections to pool, and this option is not revealed through the typeorm API and while it waits on the server to unfreeze and allow a new connection to be allotted it times out.

What node.js version are you on?
Apparently node version: v.12.6.0 required me to use packetSize: 8192 to avoid the Connection lost - read ECONNRESET error
If that is no the problem try to change the Network Packet size in the SSMS Properties

If you said that the connections mostly works but fails for different data is because of the size of the stream of data.


How to set Persian calendar,currency,numbers,date and time and text in oracle database? I have installed oracle database 12c on my laptop.

I want to be able to store text,numbers,calendar,currency,... in my local language (Persian) in database tables and also show contents in Persian when retrieving data from tables.

I'm also using APEX 5 that I want these capabilities in there too?

Is it possible? Does Oracle support these in both database 12c and APEX 5?
How can I set these settings and where?

I'm using Windows 8.1 enterprise x64.

The magic key word is NLS (National Language Support). It will define the dateformat, calender format etc.

You can query your current NLS-settings with this command:

SELECT * FROM nls_database_parameters;

If you want to change it in a session or an instance you can query the current settings using this views:

SELECT * FROM nls_session_parameters;
-- or
SELECT * FROM nls_instance_parameters;

You need to set this NLS settings, maybe the proper ISO as character set too.

NLS_LANG=ARABIC_SAUDI ARABIA.UTF8
NLS_LANGUAGE=PERSIAN
NLS_CALENDAR=PERSIAN

You have some possible solutions to setting the NLS. If you want to use it globally I would suggest to set it in the initialization parameters.

You can read more about setting the NLS in the docs.

Oracle 12c should support persian, as stated in this whitepaper in the section "New Locale Coverage".

By the way, if you need a larger multi language support, you may want to use AL32UTF8 instead of normal UTF8 language sets. You can read something about it here and here.


Claiming disk space after removing table field I am running sql 2008 r2 and the db was working fine and fast for last 3 years untill about 3 months ago we added ntext field on very active and used table.
Now we are starting to get out of server space because of the huge expanding size of this table.

I read that shrinking ,we do not want to loose the indexing of db because it was working fast for years and we do not want to get fragmentation expending.

We decided to delete that field and all its values:
Is there a way to delete the ntext field and all its values and release space without removing indexing ,without shrinking, without loosing db performance?

I am attaching the db size query output to show you size expanding of last 5 months.

We decided to delete that field and all its values: Is there a way to delete the ntext field and all its values and release space without removing indexing ,without shrinking, without loosing db performance?

I would recommend to use (from BOL : )
DBCC CLEANTABLE
(
    { database_name | database_id | 0 }
    , { table_name | table_id | view_name | view_id }
    [ , batch_size ]
)
[ WITH NO_INFOMSGS ]

DBCC CLEANTABLE reclaims space after a variable-length column is dropped. A variable-length column can be one of the following data types: varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. The command does not reclaim space after a fixed-length column is dropped.

!! CAUTION !! (use a careful batch size - its advisable to use this parameter if your table is massive):

DBCC CLEANTABLE runs as one or more transactions. If a batch size is not specified, the command processes the whole table in one transaction and the table is exclusively locked during the operation. For some large tables, the length of the single transaction and the log space required may be too much. If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows. DBCC CLEANTABLE cannot be run as a transaction inside another transaction.
This operation is fully logged.

A simple repro will prove that DBCC CLEANTABLE is better than SHRINKING (and no worry of fragmentation :-)
-- clean up
drop table dbo.Test

-- create test table with ntext column that we will drop later
create table dbo.Test (
    col1 int
    ,col2 char(25)
    ,col3 ntext
    );

-- insert  1000 rows of test data
declare @cnt int;

set @cnt = 0;

while @cnt < 1000
begin
    select @cnt = @cnt + 1;

    insert dbo.Test (
        col1
        ,col2
        ,col3
        )
    values (
        @cnt
        ,'This is a test row # ' + CAST(@cnt as varchar(10)) + 'A'
        ,REPLICATE('KIN', ROUND(RAND() * @cnt, 0))
        );
end

--drop the ntext column
ALTER TABLE dbo.Test DROP COLUMN col3 ;

--reclaim the space from the table
-- Note that my table is only having 1000 records, so I have not used a batch size
-- YMMV .. so find a maintenance window and you an appropriate batch size
-- TEST TEST and TEST before implementing in PROD.. so you know the outcome !!
DBCC CLEANTABLE('tempdb', 'dbo.Test') ;


Some MySQL slave errors? Master version: mysql-server-5.5.14-1.el5.remi
Slave version: Percona-XtraDB-Cluster-server-5.5.24-23.6.340
Binlog format: ROW based

I'm using PRM to setup HA for MySQL.

Sometime, MySQL slave stopped with some errors:

Error executing row event: 'Table 'reportingdb.tvc_ads_tag_date' doesn't exist'

[Warning] Slave SQL: Could not execute Update_rows event on table reportingdb.7k_banner_channel_tmp; Can't find record in '7k_banner_channel_tmp', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000854, end_log_pos 859094925, Error_code: 1032

120828  0:36:13 [Warning] Slave SQL: Could not execute Write_rows event on table reportingdb.7k_bookings_ver; Duplicate entry '1518' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001022, end_log_pos 509479, Error_code: 1062

Error 'Error in list of partitions to DROP' on query. Default database: 'reportingdb'. Query: 'ALTER TABLE v3_ban_date_cpm7k DROP PARTITION pcurrent_201298'

and the mysql.sock is missing but the MySQL processes still exist. Pacemaker cannot restart due to the below errors:

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

I have to login via TCP (-h 127.0.0.1) and stop MySQL first, then let corosync start MySQL again.

In summary, the question is:

How do I solve the above slave errors and prevent them from happening in the
future?
Why is the mysql.sock missing?  How do I make the Pacemaker better handle this situation?

It's better not to skip by hundreds. Just loop one-by-one:

until mysql -e "show slave status\G;" | grep -i "Slave_SQL_Running: Yes";do
  mysql -e "stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;";
  sleep 1;
done


What is the effect of having an opened transaction in MSSQL for too long? I'm just wondering what happens if you begin a transaction in a DB and forgot to commit or rollback it. Will the server be down? Lets say you left it for 3 days.

There are also users who are using it assuming that the other users did not know that there is an unclosed transaction (let's just assume that the users are just inserting data on the database). What are the consequences of this action?

Having an open transaction by itself will have almost no consequence. A simple

BEGIN TRANSACTION
-- wait for a while, doing nothing
-- wait a bit longer
COMMIT

will, at worst, hold a few bytes of status values. No big deal.

Most programs will do actual work within the transaction and this is another matter. The point of a transaction is so you can be sure that several facts within the database are true simultaneously, despite there being other users writing to the same database concurrently.

Take the cannonical example of transferring money between bank accounts. The system must ensure that the source account exists, has sufficient funds, the destination account exists, and that both debit and credit happen or neither happens. It must guarantee this while other transactions happen, perhaps even between these two accounts. The system ensures this by taking locks on the tables concerned. What locks are taken, and how much of other peoples' work you see, is controlled by the transaction isolation level.

So if you do a lot of work there is a good chance other transactions will be queued waiting for the objects on which you hold locks. This will reduce the system's overall throughput. Eventually they will hit timeout limits and fail, which is a problem for overall system behaviour. If you use an optimistic isolation level your transaction may fail when you try a commit because of other's work.

Holding locks takes system resources. This is memory which the system cannot use to process other requests, reducing throughput.

If a lot of work has been performed the system may choose to perform lock escalation. Instead of locking individual rows the entire table will be locked. Then more concurrent users will be affected, system throughput will drop further and the application impact will be greater.

Data changes are written to the log file, as are the locks which protect them. These cannot be cleared from the log until the transaction commits. Hence very long transaction may cause log file bloat with its associated problems.

If the current work uses tempdb, which is likely for large workloads, the resources there may be tied up until the end of the transaction. In extreme cases this can cause other tasks to fail because there is no longer enough room for them. I have had cases where a poorly coded UPDATE filled tempdb so there was insufficient disk left for a report's SORT and the report failed.

If you choose to ROLLBACK the transaction, or the system fails and recovers, the time taken for the system to become available again will depend on how much work was performed. Simply having a transaction open will not affect the recovery time, it is how much work was performed. If the transaction was open but idle for an hour recovery will be almost instantaneous. If it was writing constantly for that hour the rule of thumb is that recovery time also will be about an hour.

As you can see long transaction can be problematic. For OLTP systems best practice is to have one database transaction per business transaction. For batch work process input in blocks, with frequent commits, and restart logic coded. Typically several thousand records can be processed inside a single DB transaction, but this should be tested for concurrency and resoruce consumption.

Do not be tempted to go to the other extreme and avoid transactions and locks entirely. If you need to maintain consistency within your data (and why else would you be using a database?) isolation levels and transactions serve a very important purpose. Learn about your options and decide what balance of concurrency and correctness you are prepared to live with for each part of your application.


How to make MySQL table name case insensitive in Ubuntu? I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default.

Now, I want to make MySQL work as case insensitive in Ubuntu.

Is it possible? If yes, how can I do it?

Open terminal and edit /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf

Underneath the [mysqld] section.add:

lower_case_table_names = 1

Restart mysql

sudo /etc/init.d/mysql restart

Then check it here:

mysqladmin -u root -p variables


LOAD DATA INFILE fatal error encountered I use this query for loading sql data from my pc to localhost mysql database.(version 5.0.10 working witj XAMPP)

The query is as below:

cmd = new MySqlCommand("LOAD DATA LOCAL INFILE '" + filename + "' IGNORE INTO TABLE " + tblname + " CHARACTER SET UTF8", conn);

And I tried this also (enclosed tblname)

cmd = new MySqlCommand("LOAD DATA LOCAL INFILE '" + filename + "' INTO TABLE `" + tblname + "` CHARACTER SET UTF8", conn);

And one line of my sql data (loaded without problem by phpmyadmin) :

INSERT INTO `atelier` VALUES(1,'Chateau Carbonnieux -1','26.12.2013 00:00:00',23,10,0,'4 macarons differents','mamie','15.12.2013 11:09:14','sabrina','18.12.2013 05:29:26');

The variable filename is : "E:\Macdata\20131228\atelier.sql" (with double backslah)

And the tblname is the table name read directly form database is: "atelier".

When the program reaches the line  cmd.ExecuteNonQuery() 30 seconds after i get "Error 0 fatal error ecountered attempting to read the resultset."

This data file is loaded without problem with phpmyadmin.

What can be the reason ? MySql version ? Any help ?

As I'am a newbie in mysql, i used this link for creating my query command. Often they send this link for answer. But in this link there is no remarq to enclose the filename with ' otherwise it does not work it gives an error. http://dev.mysql.com/doc/refman/5.1/en/load-data.html

You don't use LOAD DATA INFILE for what you are trying to do.

LOAD DATA INFILE loads a delimited text file (such as CSV) full of raw data, that is sitting on the server, into a table.

What you have is a dump file, which is full of queries -- not raw data.

You have three basic options:

use phpmyadmin to apply the file
use the mysql command line client to load the file
open the file yourself in your code (however you'd open a normal text file for reading), read each line in, and execute each line as a query from your code.

A dump file contains the queries to recreate data structures, so its contents are simply executed as queries.  That's what phymyadmin is doing when you "load" it that way.

If the file contains DDL that drops and creates tables, you'll have to do a little more work, because you'll need to buffer what you read from the file, and only execute what you've collected so far each time you find a line ending with the ; delimiter -- or any other delimiter that may be specified in the dump file (a line beginning with DELIMITER changes the expected delimiter for subsequent statements until a subsequent DELIMITER statement is found).

But using the mysql.exe command line client on your local machine would probably be the easiest.

C:\>mysql [options] < "E:\Macdata\20131228\atelier.sql"

With a file structured like this, you can't specify what table it goes into.  If the file contains USE statements to change to the appropriate database, that will happen when those statements are encountered.  If it doesn't, you'll need to add the name of your schema right before the < in the command line.


Remote Database Backup I am attempting to find a reliable service that does remote MySQL Database Backup.

Price is no object, unless it's absurdly expensive. I understand this question doesn't really have an answer, and I apologize for that.

Iam just looking for suggestions on ways I can check out for myself.

If this isn't the right place for this question, you can, you know, insult me or something :)

Thank you!
Quintis

If you have a consistent connection to the external source then setting up replication would be the way to go.  This way you ensure that all changes are automatically sent to the 'backup' (aka slave) server.  You could still run a regular backup script to dump the data to an external HD as a failsafe in case someone 'drops' all the tables int the database.  The book High Performance MySQL is a good source of information on replication setups.


Is it safe to use innodb_flush_log_at_trx_commit = 2 I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?

You can lose up to one second's worth of transactions. The default value is 1, which helps keep InnoDB ACID Compliant.

According to the MySQL Documentation on innodb_flush_log_at_trx_commit

  If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is
  written out to the log file once per second and the flush to disk
  operation is performed on the log file, but nothing is done at a
  transaction commit. When the value is 1 (the default), the log buffer
  is written out to the log file at each transaction commit and the
  flush to disk operation is performed on the log file. When the value
  is 2, the log buffer is written out to the file at each commit, but
  the flush to disk operation is not performed on it. However, the
  flushing on the log file takes place once per second also when the
  value is 2. Note that the once-per-second flushing is not 100%
  guaranteed to happen every second, due to process scheduling issues.

  The default value of 1 is required for full ACID compliance. You can
  achieve better performance by setting the value different from 1, but
  then you can lose up to one second worth of transactions in a crash.
  With a value of 0, any mysqld process crash can erase the last second
  of transactions. With a value of 2, only an operating system crash or
  a power outage can erase the last second of transactions. InnoDB's
  crash recovery works regardless of the value.

  For the greatest possible durability and consistency in a replication
  setup using InnoDB with transactions, use
  innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master
  server my.cnf file.

  Caution

  Many operating systems and some disk hardware fool the flush-to-disk
  operation. They may tell mysqld that the flush has taken place, even
  though it has not. Then the durability of transactions is not
  guaranteed even with the setting 1, and in the worst case a power
  outage can even corrupt the InnoDB database. Using a battery-backed
  disk cache in the SCSI disk controller or in the disk itself speeds up
  file flushes, and makes the operation safer. You can also try using
  the Unix command hdparm to disable the caching of disk writes in
  hardware caches, or use some other command specific to the hardware
  vendor.

Based on this, values other than 1 put InnoDB at risk of losing 1 second's worth of transactions, or a transaction commit's worth of data.

The documentation also says use sync_binlog=1.

According to the MySQL Documentation on sync_binlog

  A value of 1 is the safest choice because in the event of a crash you
  lose at most one statement or transaction from the binary log.
  However, it is also the slowest choice (unless the disk has a
  battery-backed cache, which makes synchronization very fast).

Your safest choice is

[mysqld]
innodb_flush_log_at_trx_commit=1
sync_binlog=1

If you do not mind possible data loss (up to 1 second's worth) then you can use either 0 or 2 at your own risk if the rewards (faster write speed) are worth it.


Postgres wait_event_type Extension I have a function that was written in Language SQL (not plpgsql) that is scheduled to be called nightly from pgAgent.  It was running fine until last night.  The function usually takes under 3 minutes to execute, but when I came in this morning it was still running 5 hours later.

I queried pg_stat_activity and see that the call is hanging on a wait_event: Extension, wait_event_type: Extension.  I have been trying to research this issue, and the only information I have found is that the query is waiting on a Postgres extension.  The only extensions I currently have installed are plpgsql, postgres_fdw, tablefunc, adminpack, and pgAgent.  The only extensions that seem likely are pgAgent - which is calling the function at the right time, postgres_fdw - foreign tables are used in the function, but querying the foreign tables outside of the function seem to be working fine.

How do I find which extension is causing the wait, and what are the next steps for solving this issue?  There is a lot of information out there about resolving locks, but I was unable to find anything about the Extension wait_event other than a brief description.

I discovered that the wait_event_type: Extension was referring to it getting locked up in a query passed to the foreign data wrapper.  However, the problem does not seem to be the Postgres_FDW, but rather using Language SQL to write my function and not using LANGUAGE plpgsql.  I recalled a post either here, on StackOverflow, or another forum which mentioned a bug in the SQL procedural language in Postgres that would unexpectedly lock up queries intermittently. I rewrote the function in plpgsql, which was very simple, and it has been working flawlessly for over 3 months now.  I wish I could find the thread that mentioned the bug, but they had said it had been reported to Postgres, and the likelihood of them fixing it was slim.  Just use LANGUAGE plpgsql for your functions and stay away from the LANGUAGE SQL.


Strange Situation with sp_WhoIsActive I'm with a simple task to migrate databases.

I'm doing the backup in the primary database, setting it to OFFLINE, and then, migrating it to the secondary.

No problem at all.

but I notice something strange.

I check if someone is using the database with sp_WhoIsActive, and it shows nothing.

Then I use alter database X set offline.

Then it doesn't change it at all, and the query keeps running. Then I check again to see if something is using the database, and for my surprise, it shows something running for 22hrs, 20hrs, or 44min.

Why doesn't sp_WhoIsActive show it?

Example:

1st run:

Then Alter etc etc set offline. Then it locks, and I check again with
sp_WhoIsActive

Bam. Something running.

Nothing is wrong -- you just have to read the documentation. By default, you don't see sleeping SPIDs unless they're blocking another request.

The SPID you're looking at is likely a sleeping session.

Try running

EXEC dbo.sp_WhoIsActive @show_sleeping_spids = 1

More to your problem, you might want to set the database to single user before trying to set it offline.


3rd party dll in SQL Server CLR I need to use a third party DLL in a trigger c# code in SQL Server CLR

But when I try to add reference it just shows some DLLs from SQL Server.

How can I add my third party dll to SQL Server?

You can only add references to those assemblies which have been registered with Sql Server. If they are not registered, they will no show up in the Add References dialog.

There are a number of steps you'll need to do register a DLL, firstly you'll need to reconfigure your database:

ALTER DATABASE [MyDatabase] SET TRUSTWORTHY ON;
sp_configure 'clr enabled', 1;
RECONFIGURE;

Once this is done, Sql Server is CLR enabled. Next, you'll need to register your assembly:

CREATE ASSEMBLY [MyAssembly] AUTHORIZATION [MyUser]
FROM 'C:\CLR\MyAssembly.dll'
WITH PERMISSION_SET = SAFE

If this last script runs correctly, the assembly is now registered, and will appear in the Add References dialog.

What you will need to consider though, is the application security of your Sql Server CLR configuration:

Prefer to register an assembly as SAFE, only in exceptional circumstances should you use EXTERNAL_ACCESS or UNSAFE.
Don't expect to be able to do everything you can on Full-trust CLR (i.e., not the CLR hosted by Sql Server) - the SQLCLR is a sandboxed runtime.
Don't try and load assemblies dynamically, as Assembly.Load() is purposefully restricted.
You may need to ensure the 3rd party library is signed with a public key if you plan on using UNSAFE.
Code executing runs in the context of the identity of the service running Sql Server (I think!)
Database access made from a hosted assembly (e.g. via context connection = true;) runs in the context of the connected user, so you need to make sure you are aware what access that library has to your data.


MySql Moving from development to production into a different user I have a project that need to be moved to production (for the first time). The database includes few tens of tables and procedures/functions. They were all created under the ROOT user (yes, bad practice I know).

Now, I need to move the whole thing to production and obviously NOT under the MySQL ROOT user.

I thought to do the following, but would appreciate an expert opinion before I engage into it:

Get a backup using mysqldump -u root -p -R MySB > dump.sql,
Edit the file and replace all instances of root with correct_user (where correct_user would be the MySQL user owning all the tables and routines),
Create the same user within the production DB,
Restore from the dump file using mysqldump -u correct_user -p -R MySB < dump.sql.

Can anyone tell me if my plan is correct or if there is a better (and more formal) way to do it?

Many thanks.

In my opinion, the logic is right in your way. I will add a suggestion to make it more practical.

Get the backup without routins. --skip-triggers, and don't add -R is it is false by default
Make a backup of routines only: --no-create-info --no-data --no-create-db -R
Edit the backup file of the routine, and create the user in the new DB
Restore the data backup file
Restore the routines backup file.


Having a backup of the master key, how can I restore a database that was encrypted by master key? while setting up AlwaysOn - Availability Group on a test system,
I generated all the restores from live and restored them to this new server.

then I backup all these databases to a network share to make them ready for being added to the availability group.

when then adding then to the availability group I get this message below, for a couple of them. (2 or 3 databases out of 50)

Bad news:

I don't have the master key for that live server - the server where the databases come from.

Good news.

I have a backup of the master key in the original live server.

I also have backups of certificates:

Not so good news:

I haven't got the decryption password for that master key backup file

This question seems to be related:

What do I need to restore an encrypted MSSQL database?

Question:

How can I restore those databases only having a backup of the master key in the original server?

P.S.
I don't have the decryption password for that master key backup file - above pic

Maybe I will have to regenerate the master key.

The screenshots show a backup of the service master key for the instance but that is not the same as the database master key that the AAG wizard is asking for. See Encryption Hierarchy in SQL.

Your situation sounds similar to adding SSISDB to an AlwaysOn AG, in that a password protected DMK exists on the database, and so you must provide that password in order for the DB to be restored and accessible on the other replica.

If you don't know the password, you need to go back to the live server and alter the DMK password:

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '####';

Once this has been done, record the new DMK password in a safe place, like a password safe. Backup the DMK to this location if possible as well, so that you can recover the DMK if need be.

Once this is done, take a new backup use that to join the secondary in the AG, providing the password when prompted. An alternative to this is to use a symmetric key created by a certificate instead of a password which is protected by the DMK, and then simply backup and restore that certificate on all replicas to enable encrypted databases to be joined to the AG with relative ease. More info here.


Where is the generic plan stored when using PREPARE statement in PostgreSQL? According to the document, if a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. My question is where the generic plan is saved? Is it stored in a file in disk? If yes, where is it located and what is the file name?

The generic plan is stored in the in-memory plan cache of the back-end that created the plan. See doxygen for plancache.c or the source.

Plans are not written to disk or read from disk, and are not shared between different backends (therefore, different sessions/connections). The documentation should really read:

  if a prepared statement is executed enough times in a single session, the server may eventually decide to save and re-use a generic plan in the per-connection plan cache rather than re-planning each time

You could probably modify the plancache code to serialize plans to disk, but it's not a trivial job to do it in a useful way. Reading them in on another backend correctly is much, much harder, when you consider the consequences of transactional DDL. Writing a proper persistent plan cache with correct plan invalidation etc would be a major job for an experienced PostgreSQL developer who already knew the optimizer/executor/planner/rewriter code well. If you wanted to tackle this you should expect to spend months studying the code before you could propose a reasonable approach, let alone implement it. (I've just been doing that with row-security for the AXLE project, so I'm all too aware of the complexity of this code).

As I said in your last question, let go of the idea that you can save plans, you can't, and concentrate on sorting out the issue that's causing the wrong plan to be chosen.


How to configure encrypted SQL Server connections to prevent MITM attacks? It has come to my surprise that SQL Server by default does nothing to prevent man-in-the-middle attacks when configuring your SQL Server to enforce connection encryption.

Forcing encryption on your connections is dead-simple. You go to Sql Server Configuration Manager, expand SQL Server Network Configuration, Protocols for [your instance], right-click TCP/IP, change Force Encryption to 'Yes', restart SQL Server and you're done.

Per Microsoft TechNet (https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx), this will generate a Self-Signed Certificate. Fine, no problem, But I expect my clients to complain that the certificate is not trusted.  However, they do not. They happily connect to it. I then realize that 'Trust server certificate' is checked by default in SQL Management Studio. Fine, but the most shocking realization is: even if you uncheck "Trust server certificate", SSMS does not complain, warn, or reject the connection.

So by default SQL Server is completely susceptible to MITM attacks because clients do nothing to verify the certificate from SQL Server. The certificate shouldn't have to be signed by a CA: the certificate should have to be configured as a trusted certificate on the client. If that's not true, what is the "Trust server certificate" checkbox for?

When I saw this question, I opened SSMS with the intent to read the help documentation on the "Trust server certificate" option only to discover that this is an undocumented feature with no description of what it does on MSDN. My guess is that it can be used to trust expired certificates or those which can't be validated by walking the chain of trust. Since there is no chain of trust for a self signed certificate, SSMS trusts it when it verifies the signature using the public key.

I think your suggestion would make an excellent server configuration option for high security implementations of SQL Server. It seems like you want a configuration option to reject all self-signed certificates, since avoiding those is the only real way to mitigate a man in the middle attack. It is exactly the concern that you raise that causes me to recommend configuring the Certificate tab with a domain or public CA certificate.


MySQL Update Ignore not working - errors and does not update all rows I am trying to run an update on a table which I know will cause some duplicate key errors. I am therefore using UPDATE IGNORE as I understood this would ignore these errors and still update the rest of the table, but this is not the case - it just fails with an error:

[SQL] UPDATE IGNORE `activity-customer` set `customerid` = 12345 where `customerid` = 56789

[Err] 1062 - Duplicate entry '12345-180221' for key 'PRIMARY'

SQL_MODE is not set to STRICT (it is empty)

This is using MySQL 5.5.47.

I'm very confused! Any help appreciated!

I managed to track this one down. There was a trigger on the table which was updating / inserting into another table.

The update/insert on this trigger did NOT have an ignore and it was actually that which was throwing the key error, not the original update.

Adding the ignore into that trigger code resolved the problem.


SQL Read Committed Snapshot Isolation in Database with Selects and Inserts Only We have database with only 1 table, which conducts Inserts and Selects.
There are No Updates in the table.

Table has lot of Concurrency; with many simultaneous Insert and Select queries occurring. Multiple Selects can cause Lock escalation, which lead to Page to Tables locks, etc .

Would Read Committed Snapshot Isolation reduce the locking/blocking in table with only Selects and Inserts? Current isolation level is Read Committed.  My assumption is No. Inserts would not block Selects, since Selects Only Read Committed Data. Selects should not block Inserts, since Inserts are occurring on tail end of table with Identity Clustered and NewSequential Id.

[My thought: If we actually had Updates, RCSI would help since, before every begin transaction, those lines would be stored in tempdb for the Read queries.]

create table dbo.Customer
(
    Customerid int primary key identity(1,1),
    CustomerGuid uniqueidentifier default (newsequentialid()),
    PersonName varchar(255)
)
create nonclustered index ix_CustomerGuid on Customer(CustomerGuid) include (PersonName)
create nonclustered index ix_PersonName on Customer(PersonName) include (CustomerGuid)

Select and Insert Queries

select CustomerGuid from dbo.Customer where PersonName = @PersonNameSelectVar
select PersonName from dbo.Customer where CustomerGuid = @CustomerGuidSelectVar

insert into dbo.Customer(CustomerGuid, PersonName) (@CustomerGuidInsertVar, @PersonNameInsertVar)

Of course they can block each other. The values you're inserting for PersonName can end up anywhere in the index, unless your customers only place orders alphabetically.

Whenever you perform an insert, the value for PersonName gets inserted to the

Leaf of the clustered index
Key of ix_PersonName
Leaf of ix_CustomerGuid

These inserts will lock intermediate (for keys) and leaf pages (for includes/the clustered index), and may cause page splits on full pages.

Even though your Id and GUID columns are sequential, which should direct most of the INSERT work to the end of those indexes where you're theoretically less likely to be selecting data from, PersonName can be anywhere in the indexes.

That can lead to blocking for any query that needs to touch PersonName.

Though singleton inserts should be brief, they can certainly pile up a bit, and may be hampered by any data synchronization (Mirroring, AGs), or just writing out to the transaction log (WRITELOG waits).

In an INSERT/SELECT only workload, RCSI or SI would behave like the READPAST hint, where you'd skip past reading locked rows being inserted.

Read Committed waits for write locks to commit or rollback, which can cause blocking in the same way that they could cause blocking waiting for an update or delete to finish.


SQL Server select with regex Can we use regular expression to select the item from database?

The table item is like below

Table column|name|
10.01.02    | a  |
100.2.03    | b  |
1021.10.04  | c  |

Now my problem is that i need to select the code and get the substring like below

Table column|name|
10.01       | a  |
100.2       | b  |
1021.10     | c  |

any suggestion with regular expression or substring?

Sql Server doesn't really support regular expressions too well. If all you need is to pick a substring from the beginning to the second dot, combine LEFT() and CHARINDEX(), like so:
-- Test table with data
create table stringtest(data varchar(32), name char(1))
insert into stringtest values('10.01.02', 'a')
insert into stringtest values('100.2.03', 'b')
insert into stringtest values('1021.10.04', 'c')

select
  data
  , left(data, CHARINDEX('.', data, CHARINDEX('.', data, 1)+1)-1) as 'subdata'
  , name
from stringtest

-- Output:
10.01.02    10.01    a
100.2.03    100.2    b
1021.10.04  1021.10  c

The extraction
left(data, CHARINDEX('.', data, CHARINDEX('.', data, 0)+1)-1) as 'subdata'
works as follows. First, the inner CHARINDEX('.', data, 0) will find the 1st dot. As index is 0, it starts from the beginning of the string. +1 will point to next character after the dot  and pass that as the starting index to the outer CHARINDEX(). The outer will find index of the second dot. Finally, LEFT() will return a substring from zero to the 2nd dot.
Edit
As per ypercube's request, let's handle some errors. First, add some data with invalid values:
insert into stringtest values('1021', 'd')
insert into stringtest values('1021.10', 'e')

And let's tune the query to include nested case statements like so,
select
  data
  , case CHARINDEX('.', data, 1) -- find first dot
    when 0 then 'No dot'
    else
        case  CHARINDEX('.', data, CHARINDEX('.', data, 1)+1) -- find second dot
        when 0 then 'No two dots'
        else left(data, CHARINDEX('.', data, CHARINDEX('.', data, 1)+1)-1)
        end
  end
  , name
from stringtest


Help improving this query using IN Have a look at this query.
Is there anyway of making the IN-statement a JOIN instead?

Yes, I'm quite new to this. Thanks in advance for any help.

SELECT * FROM company AS t1, employee AS t2
WHERE t1.id IN
    (SELECT id FROM multinational WHERE market = 'EMEA')
AND t2.ID = t1.employee_id
ORDER BY salary DESC
LIMIT 0, 24

Try this:

SELECT c.*, e.*
FROM company AS c
  INNER JOIN employee AS e on e.ID = c.employee_id
  INNER JOIN multinational AS m ON (m.company_ID = c.id and m.market = 'EMEA')
ORDER BY salary DESC
LIMIT 0, 24


Regenerating a cube relational schema in Visual Studio I have an Analysis Services cube project that my company received from an outside contractor, and I'm trying to get it so that developers can work on it on their local machines.

I'm pretty sure that the original developers generated the schema for the backend database from the cube project and then worked against that. Therefore, I'd like to generate the schema from Visual Studio, rather than just exporting the database in SQL Server Management Studio. Currently, I'm receiving an error saying that the dimensions are bound to user tables when I try to generate the relational schema.

Is my approach reasonable, and if so, how would I go about doing this? If my approach isn't reasonable, why not?

(NOTE: I'd asked this question a few days ago on StackOverflow.  When I came back to it, it occurred to me that this would be a better place for it, so I moved it here.)

That's not how it works. A .dsv (data source view) is generated by defining which tables/queries you want to use in your cube.

The flow is:

create one or more .ds data sources by defining how to connect to the source databases
create a dsv (data source view) by adding tables and named queries defining how to get data from your .ds
create dimensions and cubes by defining how the data from your .dsv needs to look in the multidimensional model.
process your dimensions and cubes to load them with data from the source system.

Technically I suppose you could recreate the source systems schema by analyzing the xml in the .dsv file as it contains the data types and table names/queries, and you could theoretically map those back and recreate the source database(s), but that would require a lot of manual work, or you writing something to parse that out of the xml.
If your DSV contains named queries you would still need to analyze those sql queries and reconstruct the underlying table.
Depending on how complex your named queries are this could become near impossible.

After all that work you would end up with a schema and no data, so your developers would maybe be able to modify the visual studio solution, but have no way to load it with data to test their modifications.


Uninstalling MySQL on Solaris I have installed MySQL 5.5.31 in Opensolaris. When I tried starting the MySQL server, it says
 bash: mysql: command not found
So, I decided to uninstall the whole lot and try doing again.
But I could not find any spporting documents online to do so, because either they refer to .rpm file unistallation pkgrm.
I used .tar extension and pkgrm is not supported in my machine.

Check MySQL service use following  command:

ps -ef | grep mysql

If  MySQL  process is running then first stop mysql process.

kill -9 mysql_pid

or

/etc/init.d/mysql stop

Remove mysql base and data dir. By default it's location is /usr/local/mysql.
Use  following  command:

rm -r mysql

Start with fresh installation:
Ref this link or this for MySQL  binary installation.


Database system vs database management system I am now taking database course in school but have worked on projects using MySQL before.

I am a bit confuse with database system and database management system. For database management system, MySQL, MS Access are some of the examples.

Can someone give me a clear explanation between the two with some examples? Most are saying they are the same.

Most are saying they are the same.

They are the same, pretty much. Most people use the two interchangeably.

You could try make a case for "database system" to cover simpler arrangements where there is just a database and not management tools (for instance in memory database libraries and/or like sqlite is often used), but there are so many grey areas here defining the difference is going to be difficult to do in a consistent manner.


How do you you suppress a specific warning in the postgresql log output? Is there any way to suppress a specific warning in the postgresql log output?

Basically, I have an application that often does a spurious commit before beginning database operations, and it's leading to thousands of messages in the output of:

2018-12-02 01:37:22 PST [5147-152] <user>@<database> WARNING:  there is no transaction in progress

I realize this isn't an ideal situation, but everything seems to be working otherwise, but the logs are getting hugely cluttered with this specific warning.

How can I prevent postgresql from emitting this one, specific warning? I don't want to disable warnings entirely, because I do like to check for any other warnings.

There is no way to suppress a specific message.  You can change "log_min_messages" to suppress logging of the entire class of WARNING messages, which you pointed out you don't want to do.  But you can target a bit more specifically by using ALTER USER ... IN DATABASE ... SET LOG_MIN_MESSAGES ... to do this just for one specific user and database, if that helps you.

If you want to target more finely than that, and you are willing to compile your own PostgreSQL to do it, you could easily modify the source code to demote this specific message from a WARNING to a NOTICE (or lower), but that is a pretty heavy-handed approach.  There are two different places in the source that produce this message for COMMIT, and I'm not sure which one would apply to you.

Other than that, using log mining tools which can be configured to ignore these messages may be your best option.


Using WITH clause with INSERT statement in POSTGRESQL I have a requirement in which I need to get one column from another table and insert that column data with some other data into another table.

Example:

If the cust_id='11' then I need to get the cust_code from cust table (let's say it returns cust_code='ABCD') and then use that cust_code with some other data to insert into table_1 like so:

WITH get_cust_code_for_cust_id AS (
    SELECT cust_code FROM cust WHERE cust_id=11
)

INSERT INTO public.table_1(
    cust_code, issue, status, created_on)
    VALUES (SELECT cust_code FROM get_cust_code_for_cust_id, 'New Issue', 'Open', current_timestamp)

But this query does not work as we haven't called the get_cust_code_for_cust_id query.

My preference is some query with WITH clause but any other answer will also be appreciated.

(Not tested in Postgres) Is this what you need?

INSERT INTO public.table_1(cust_code, issue, status, created_on)
SELECT cust_code, 'New Issue', 'Open', current_timestamp
FROM cust
WHERE cust_id=11


Windows could not start the SQL Server service error I have a problem when I run my web project. I get this error :

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Then I found in SQL Server Configuration Manager that SQL Server Express stopped. When I'm trying to run SQL Server Express, another error appears:

  the request failed or the service did not respond in timely fashion. Consult the event log or other applicable error logs for details

When I open services.msc to start SQL Server (SQLEXPRESS instance) then this error happens:

  Window could not start the sql server (SQLEXPRESS) on local Computer. For more information  review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417

Any solution to solve my problem? The version is SQL Server 2008 R2 Express.

Open SQL Server configuration manager (start-->Run-->SQLServerManager10.msc) and try to start the service. If there is a problem starting the service, locate the SQL server Error  log (in the SQL service configuration manager advanced tab there is a location or path of the error log) and post the output in the forum.


how to get column count in postgreSQL i have a query below

select emp_id,
  sum(case when leave_type = 'Planned' then 1 else 0 end) Planned,
  sum(case when leave_type = 'Not Informed' then 1 else 0 end) NotInformed,
  sum(case when leave_type = 'Informed' then 1 else 0 end) Informed
from table where activity_type='Leave'
group by emp_id

it gives below result

how to get count for above columns for 567 it will display 3
and for 619 it will display 4 in separate column please help

Add a COUNT() column:

select emp_id,
  sum(case when leave_type = 'Planned' then 1 else 0 end) Planned,
  sum(case when leave_type = 'Not Informed' then 1 else 0 end) NotInformed,
  sum(case when leave_type = 'Informed' then 1 else 0 end) Informed,
  count(*) as Total
from t
where activity_type='Leave'
group by emp_id;

emp_id | planned | notinformed | informed | total
-----: | ------: | ----------: | -------: | ----:
   619 |       2 |           1 |        1 |     4
   567 |       0 |           3 |        0 |     3

db<>fiddle here


Calling a web service from T-SQL (Stored Procedure) using MSXML EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
    IF    @hResult <> 0
    BEGIN
          EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
          SELECT      hResult = convert(varbinary(4), @hResult),
                source = @source,
                description = @desc,
                FailPoint = 'Send failed',
                MedthodName = @methodName
          goto destroy

When I Call a web service Through Sp IN SQl Server In Above Mention I'm getting error as Shown In The Pics

Not sure about that exact error, but I would advise against using the OLE Automation stored procedures (i.e. sp_OA*) as they have been deprecated since SQL Server 2005 was released and have various "issues".

I you need to interact with a Web Service, your best bet is to use SQLCLR. I have more info regarding this option in the following answer, also here on DBA.SE:

Bringing web service data into SQL server


Oracle Select Query Over Database Link Suddenly Hangs Endlessly I have a query that's been in use for years and today it started hanging endlessly. I took the query apart and found the piece causing it to hang is a simple join on a primary key that should run like lightning. If I remove problem table the query runs in less than a second.

Synthetically it looks like this: (The problem table is remote2)

SELECT fields
FROM local_table1, local_table2, ... (more tables)
     remote_table1@dblink remote1, remote_table2@dblink remote2
WHERE
      ...clauses to tie the local tables together with each other and remote_table1...
      remote2.primary_key = remote1.primary_key_of_remote2
      AND remote2.afield <> 'avoid this value'

It's acting like it stuck on some kind of DB lock, but neither myself nor the DBA can find it. Is there some kind of special queuing mechanism used by remote database links that is locking this up?

We learned that in rare circumstances a distributed transaction fails and leaves some kind of "half lock" on one side of the distributed transaction. Because the other side was gone it never resolved. At least one of the common tools that looks up locks didn't see these locks. However, we were able to find them (I think we queried v$lock, I don't remember, it's been over a year since it last happened). To fix it we determined what process was running that session and our DBA killed the OS process with a kill -9. It was a rare problem and I haven't seen it in about 18 months, so it's possible that it is only something that happens on earlier versions of Oracle . (However, it usually happened only once or twice a year at most, so it could be we've just been lucky.)


How to execute stored procedure with output parameter in Oracle PL/SQL? This is the table I have created:

CREATE TABLE Toy
(Toy_No NUMBER PRIMARY KEY,
 Toy_Name VARCHAR(30) NOT NULL
 );

This is the sequence I have created:

CREATE SEQUENCE toy_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

Then I tried creating a simple stored procedure with output parameter:

CREATE OR REPLACE PROCEDURE insert_toys(toy_id OUT NUMBER,toy_name VARCHAR(30))
AS
BEGIN
toy_id := seq_toy.NEXTVAL;
INSERT INTO Toy (Toy_No,Toy_Name)
VALUES (toy_id,toy_name);
END;

But I'm getting a compilation error.
Where can be the mistake possibly be ?

There are several mistakes.
1. When specifying parameters of procedure, you don't need to specify size. E.g. it should be CREATE OR REPLACE PROCEDURE insert_toys(toy_id OUT NUMBER,toy_name VARCHAR ), not  .... VARCHAR(30))
2. You created sequence CREATE SEQUENCE toy_seq, but trying to use sequence with different name toy_id := seq_toy.NEXTVAL;  (toy_seq vs seq_toy)
3. Parameter name is the same as field name (Toy_Name). Even though it's not a compilation error, qualifying variables is always much better compared to relying  on resolution rules :

INSERT INTO Toy (Toy_No,Toy_Name)
VALUES (insert_toys.toy_id,insert_toys.toy_name);

The procedure takes 2 parameters, and should be called like that.

set serveroutput on;
declare new_id NUMBER;
BEGIN
  insert_toys(new_id,'name2');
  dbms_output.put_line(new_id);  --print value of new id
END;


select two tables and print the columns side by side with matching key in hive I have the below tables:

table_1

name_1  id_1    value_1
avi        1    21
adi        2    X

table_2

name_2  id_2    value_2
avi        1    21
adi        2    22

metadata_info: can be read from a file or hard coded

table_1_col  table_2_col
name_1       name_2
id_1         id_2
value_1      value_2

I want the below result:

table_1_col  table_2_col  table_1.name_1  table_2.name_2  table_1.id_1  table_2.id_2  table_1.value_1  table_2.value_2  flag
name_1       name_2       avi             adi             1             1             21               21               0
name_1       name_2       avi             adi             2             2             X                21               1

I am trying to do the entire process in a shell script, and I want to run multiple Hive queries.

SELECT 'table_1' AS table_1
    ,'table_2' AS table_2
    ,'id_1' AS table_1_pk
    ,'id_2' AS table_2_pk
    ,t1.id_1 table_1_pk_value
    ,t2.id_2 AS table_2_pk_value
    ,'name_1' AS table_1_col
    ,'name_2' AS table_2_col
    ,t1.name_1 AS table_1_value
    ,t2.name_2 AS table_2_value
    ,CASE
        WHEN t1.name_1 = t2.name_2
            THEN '0'
        WHEN t1.name_1 <> t2.name_2
            THEN '1'
        END AS flag
FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.id_1 = t2.id_2;


Syncing updates to master in master/slave setup [semi-sync replication] Background info

I have two MySQL 5.5 servers set up in a Master/Slave configuration with Semi-synchronous replication.

The database is being used for a high-profile WordPress site.

Problem

It seems to work great except for one thing:

When the master is down and changes are made to the slave, they are not written back to the master when it comes back up.

What I've done so far

I tried the answer [here], but it broke my replication completely.

Questions

Is there a more standard way of setting this up or is it generally not supported?
Should I just disallow writes on the slave?
Is there a simple way to notify the master of changes, and sync them upstream?

You are almost there. I guess you have missed the auto_increment_increment & auto_increment_offset variables.

Set auto increment increment to the total number of servers.

Set auto_increment_offset to a number between 1 and auto increment increment for each server.

example:

Server 1: auto increment increment = 2, auto_increment_offset = 1

Server 2: auto increment increment = 2, auto_increment_offset = 2


pg_restore fails on foreign key contraints I need to restore a PostgreSQL DB from a backup that was created using "custom format" so that a single table could be extracted from it later. (Per the documentation individual tables cannot be automatically extracted from a plain text backup.)

Because the backup was created as a binary file I do not have the option of editing it, and can only restore it with pg_restore.  Because it is not loaded in the DB, I don't have the option of setting the constrains disabled. (catch-22)

When I try to use pg_restore to restore the custom dump, it fails because of foreign key constraints.

I have looked at perhaps a dozen or more posts on pg_restore and foreign key constraints, but they all require that the DB be already loaded in PostgreSQL so that I can disable the constraints.

I need to restore the entire DB from scratch, both the structure and the data. The backup already exists. I am not looking for a different way to dump it in the first place (although I'd like to learn about that if that is the only option). I have not found anything in the documentation for either pg_dump or pg_restore that addresses this.

I'm finding it difficult to think that PostgreSQL would allow a non-restorable dump to be created. Surely there is a simple answer to this?

The Question: Is there a way to restore a custom format dumped PostgreSQL DB, both schema and data, that contains numerous foreign key constraints?

---- EDIT:

I am more than slightly surprised, but as zsheep suggested, it appears the backup was damaged.

Starting from scratch, I can now go through the entire process of backup, remove the existing DB, and restore from backup (confirming at each step) with no failures or errors:

pg_dump -Fc -f DBnnn-BLANK my_db

psql -U my_user -d postgres

drop database my_db;\q

createdb -T template1  my_db

pg_restore -d my_db DBnnn-BLANK

Yes there is.

Have you tried the pg_restore --disable-triggers??? yet

If that fails to work which would be odd

You can restore just the schema with the --schema flag,  then modify the table "drop the constraints" in question" then restore the table contents

couple of useful articles
Different Dumps

and my long ago written wiki article on backups


One way Synched Dev Database from Production Database I've got a scenario to implement in our environment of SQL Server Database. Now we have around 10+ different application which uses the same DB for their day to day activities. The strange thing is, along with the production tools, the dev apps also using the same DB which is very risky (Only recently they agreed that) after years of its working.

So what Im looking for is to have a solution with a new replica of the DB as DEV database which is always to be one way synch with the production DB. That means if any changes on the production DB (Schema or Data or anything) it should immediately reflected on the DEV DB. But if we have any changes on schema on DEV Db, it should not get auto synched to Production DB. It should synch only when we manually do that( But how, I have no idea with these synching)..

So how can we handle these situation wisely. Im very poor in these DBA activities. Sorry if this is a simple thing even for beginners.

You've got yourself one heck of a requirement, none of which I would ever support.  Regardless, one solution I can think of is to enable transactional replication on the production side, which can replicate DML and DDL statements asynchronously.  In our production environment, we are at 5 src latency.  I would also double check documentation to ensure all DDL statements you need are supported.

To capture DDL statements on the development side, you can create DDL triggers to log the schema changes. As long as you can distinguish between a replicated change and development change, you can then extract the changes from the log table and apply then to production.


Save Results of DBCC ShrinkFile When we execute the following command:

DBCC SHRINKFILE('MyDB_log', 1)

We get the following results in SSMS:

DBID | Field | CurrentSize | MinimumSize | UsedPages | Estimated Pages
-----|-------|-------------|-------------|-----------|----------------
 11  |   2   |    128      |   128       |  128      |     128

The Question:

How can we create a query that outputs these results to a text file, without using SSMS output window.

I have tried this:

CREATE TABLE #x
(
    [DBID] int,
    FileID int,
    CurrentSize int,
    MinimumSize int,
    UsedPages int,
    EstimatedPages int
)
INSERT #x
  EXEC('DBCC SHRINKFILE(''MyDB_log'', 1)')

SELECT *
  FROM #x

DROP TABLE #x

But I get the following error:

Msg 8920, Level 16, State 2, Line 1
Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.

I have also tried the following:

DECLARE @Statement AS VARCHAR(2000);
SET @Statement = 'bcp "DBCC SHRINKFILE(''MyDB_log'', 1)" queryout C:\Test.txt -c -UDBAdmin -P1234 -S192.168.123.123';

exec xp_cmdshell @Statement

I get:

Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column

The easiest way I see it is a batch file using sqlcmd:

sqlcmd -S .\SQL2008R2 -E -Q "dbcc loginfo" >> log.txt

The redirect operator appends data at the end of the file.

-Q parameter was used for closing the sqlcmd session immediately.


Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame System:

I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total).

Issue:

During the Index Rebuild (~20 minutes), we fail to insert data into the DB

Question:

How can I reduce the downtime during the Maintenance Plan? I was thinking about ensuring the Transaction Log size is small (Truncating it 4 times a day), but I don't think that will help.

I'm also uncertain if Rebuilding the Indexes more often will help. (Since I don't know if this is a "bulk" operation that will benefit from recently Re-Indexed Tables).

Note: Upgrading to SQL Server 2005 Enterprise for the Live Re-Indexing feature is not an option.

Why are you rebuilding the indexes? Do you have any evidence that the rebuild is required/needed/beneficial? Have you considered when to use Reorganize vs. Rebuild?

Consider using one of the many index maintenance scripts like the ones from Ola Hallengren or Michelle Ufford. These scrips have intelligence in them to do appropriate maintenance action according to the index fragmentation and will reduce the offline duration by rebuilding only when truly required (hopefully never).

IF a rebuild is needed then there is nothing you can do w/o going to an Enterprise Edition and using online index rebuild.

Oh, one more thing: do not shrink the log. You are only causing it to take more time when it grows back.


What is the difference between UPDATE ... LIMIT and SELECT ... FOR UPDATE? I have read an article, there is one sentence about MySQL:

  As one might expect, lock contention is an issue and we use several
  strategies to mitigate it including a separate table for each priority
  level , use of UPDATE … LIMIT instead of SELECT FOR UPDATE for the
  dequeue selection query, and carefully tuned schemas and secondary
  indexes to fit this type of workload.

So, what is the difference between UPDATE ... LIMIT and SELECT ... FOR UPDATE when considering lock?

Pattern 1:

BEGIN;
SELECT stuff ... FOR UPDATE;
    fetches `stuff` and prevents (via a lock) other connections from messing with row(s).
work with `stuff`.
UPDATE the rows SELECTed (or a subset of them)
COMMIT;

Pattern 2:

(autocommit=1)
UPDATE Queue... WHERE ... LIMIT...  -- Grab row for self
SELECT ... WHERE ...           -- Make sure I got it, and fetch id
if got it     -- Perhaps someone else got it
then
    BEGIN;
    work on application things
    COMMIT;
endif
DELETE FROM Queue WHERE ...   -- remove from queue

Pattern 1 locks the row(s) longer, but allows more flexibility in what is done.

Pattern 2 has two quick locks.  But, because of other threads potentially messing with the same row(s), you have to check that you really got the row.

Since you mentioned queuing, think of it this way...  You have two structures: (1) the queue, and (2) your application data.  In general it is better to handle them separately.  The way to do that is to avoid having both in the same transaction.

Having both in the same transaction causes locks to last longer for both.  And it forces rollback of both, even if that is inappropriate.

Pattern 1 is better when the SELECT and the UPDATE are both working on application stuff.

Something like Pattern 2 is useful with you need to hang onto something "too long".  (That is, more than a few seconds.  Or from one html page to another, where you have lost the connection.)


Get Results from DMV Query into SQL Server Table today I began to troubleshoot an issue that I was asked to resolve, and for the first time I found myself querying in the SSAS database. I obtained a very useful set of results (who was connecting to what applications/cubes and for how long), but for the life of me I cannot figure out how to get the results from that query into a database table so that I can track the data. Any help would be much appreciated, and thank you in advance for any assistance you are able to provide.

Here is the query I am running:

Select connection_id , connection_user_name ,
connection_impersonated_user_name , connection_host_name ,
connection_host_application , connection_start_time ,
connection_elapsed_time_ms , connection_last_command_start_time ,
connection_last_command_end_time , connection_last_command_elapsed_time_ms ,
connection_idle_time_ms , connection_bytes_sent , connection_bytes_received ,
connection_data_bytes_received FROM $system.discover_connections

This returns all of the information I am currently interested in seeing.

In SSAS, there's no such thing as a table; it's all multi-dimensional stuff. What you need to do is get the results into a table in a database engine instance.

Here's one way to do it:

Create a linked server to SSAS on your management SQL Server database engine instance. (Note: you'll need to install the SSAS drivers if the two instances aren't on the same box.)
Create a job in SQL Agent that periodically queries the SSAS DMV over the linked server and dumps the results into a relational table.


MDX misunderstanding: complex FROM clause I have a MDX query which syntax is familiar to me:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS,
       NON EMPTY { ([Projects].[Id].[Id].ALLMEMBERS * [Priorities].[Id].[Id].ALLMEMBERS ) }
FROM [Reports]
WHERE [CreatedOn].[Date].&[2010-01-01T00:00:00]:[CreatedOn].[Date].&[2010-02-01T00:00:00]

But I can rewrite the query such way:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS,
NON EMPTY { ([Projects].[Id].[Id].ALLMEMBERS * [Priorities].[Id].[Id].ALLMEMBERS ) } ON ROWS
FROM (SELECT [CreatedOn].[Date].&[2010-01-01T00:00:00]:[CreatedOn].[Date].&[2010-02-01T00:00:00] ON COLUMNS
FROM [Reports])

New query returns the same results, but FROM section looks obscure to me. How query can have two FROM section? Where can I find information about this syntax?

Recent versions of SSAS (IIRC 2008+) support subqueries in MDX in much the same way that SQL does.  What you see here is a subquery being used to produce a subcube that is then queried by the outer query.  There's an article on it here.