How to Insert JSON data to BYTEA column in the table in POSTGRESQL? So, I came across the function script which is converting JSON data to BYTEA and then insert as a record in the table in a BYTEA column. (As I assumed what the code is doing)

In Oracle the function utl_raw.cast_to_raw converts the data to blob data and records the data in the table in Blob column. Giving the following output message, "anonymous block completed"

The following is the code,

CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
DECLARE
    v1 "TBL1"."COL1"%TYPE;
    v2 "TBL1"."COL2"%TYPE;
BEGIN
v1 := utl_raw.cast_to_raw('{
 "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""
     }');

 v2 := utl_raw.cast_to_raw('{
    "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""
                    }');

 INSERT INTO "TBL1" ("SN","COL1","COL2") values(1,v1, v2);
END;
$$

LANGUAGE 'plpgsql';
COMMIT;

[SOLVED] Edit:  So generally the problem was nothing. So all I had to remove was the utl_raw.cast_to_raw line and execute it as it is. No errors, no problems. THANKS

There is no reason to convert anything, just insert the JSON strings (after making them valid JSON):

CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
BEGIN
 INSERT INTO "TBL1" ("SN","COL1","COL2")
 values(1,
    '{
      "APPLICATION": {
         "MEMORY": {
         "OPTIONS" :{
                      "SOMETHING" : "SOMETHING",
                      "format" : "SOMETHING",
                      "System" : "",
                      "IP" : "",
                      "Port" : "",
                      "template" : "",
                      "Path" : "" ,
                      "Name" : "QUEUE",
                      "URL" : ""
        }}}}', --<< add the missing curly braces!
     '{
      "APPLICATION": {
       "MEMORY": {
       "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""
        }}}}'); --<< add the missing curly braces!
END;
$$
LANGUAGE plpgsql;
COMMIT;

You also don't need PL/pgSQL for this. A language sql function would be enough (you would need to remove the begin and end though)


Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper? It seems to be disallowed to add a Foreign Key constraint to a foreign table.  Is there any other way to do this?  My two tables have these constrains on the remote server.

More specific details:

I only really need it as an annotation because some tools look that up for JOINs.

I have two tables on a remote server that I've added to my local using postgresql_fdw's CREATE FOREIGN TABLE.  These I'm using in PostgREST, an automatic API server uses the REFERENCES information in order to make joins.

Actually, I'm further making two additional CREATE MATERIALIZED VIEW to make the lookups fast - but sadly, they too don't have any way I know of to add REFERENCES information?

I'm open to other ways of making (read-only) lookups fast for these tables on the foreign API server and where Foreign Key constraints will work.

The PostgREST team gave a potential solution: faking the foreign key constraints.

They read the pg_catalog.pg_constraint table to get this information.  The idea was that I could manipulate the search_path and create my own pg_constraint table where I could make the fake foreign keys:

CREATE TABLE pg_constraint (LIKE pg_catalog.pg_constraint);

ALTER ROLE postgrest IN DATABASE p2k SET search_path = public, remote, pg_catalog;

I was able to make such a table, and the query they use gave something reasonable back.  However it seems to fail for some other reason.

It's also hackish and quite a bother to create the information in pg_constraint correctly.  So I probably won't accept this answer, just wanted to put it here because it might work for someone else / some similar use case.


How to get MAX and MIN in GROUP BY query? In this query:

SELECT col1, col2, col3
FROM table1
GROUP BY col1

each row includes the first value of col2 and col3 for each unique value of col1.

How to select MAX and MIN for each value of col1.

Something like:

SELECT col1, minimum of col2, maximum of col2, minimum of col3, maximum of col3

Would you believe...

SELECT col1, MIN(col2), MAX(col2), MIN(col3), MAX(col3)
  FROM table1
 GROUP BY col1;

...?

  each row includes the first value of col2 and col3 for each unique value of col.

That assertion is not exactly true.  That may be what you're seeing, but do not assume this to be meaningful and do not write code based on this observation.  Your original query would not be valid at all were not for a MySQL extension that departs from standard SQL handling of GROUP BY.

  MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.

...which is what you did in your initial query, col2 and col3 are not nonaggregated columns (columns without an aggregate function applied, and not in the group-by).  When you do this...

  The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate

http://dev.mysql.com/doc/refman/5.5/en/group-by-extensions.html


How to determine the fully qualified tablenames of aliased tables, when a specifically named column is used in a stored procedure in SQL Server? suppose StoredProcedure1 (SP1) contains the following
SELECT
      A.COL1
     ,A.COL2
     ,B.COL2
     ,C.COL5
    FROM MYBIGTABLE A
    INNER JOIN MYSMALLTABLE B ON A.IDCOL  = B.IDCOL
    INNER JOIN MYOTHERTABLE C ON B.ID2COL = C.IDCOL

Suppose I want to know about all such stored procedures that contain columns called COL2
I realise that I can query the stored procedure definitions in metadata tables and that will return 'SP1' as a result, but how then to return the fully qualified 'MYBIGTABLE' AND 'MYSMALLTABLE' as rows?

You have a couple of DMVs for this. My guess is that sys.dm_sql_referenced_entities can be useful:
USE Adventureworks
GO

CREATE OR ALTER PROC dbo.myProc
AS
SELECT h.AccountNumber, d.LineTotal
FROM Sales.SalesOrderDetail AS d
 INNER JOIN Sales.SalesOrderHeader AS h ON d.SalesOrderID = h.SalesOrderID
WHERE 1 = 0
GO

SELECT * FROM sys.dm_sql_referenced_entities('dbo.myProc', 'OBJECT')

SELECT * FROM sys.dm_sql_referencing_entities('Sales.SalesOrderDetail', 'OBJECT')


SELECT (CASE WHEN N.wbs_item_id IS NULL  THEN 'Grand Total' ELSE N.name END) AS '',
(CASE WHEN N.Materials IS NULL  THEN 'Total(Materials)', ELSE N.name END) AS '',
(CASE WHEN N.Labour IS NULL  THEN 'Total(Labour)' ELSE N.name END) AS '',

Total AS ''
FROM(
SELECT (CASE WHEN F.name='Materials' THEN F.name END ) AS 'Materials',
(CASE WHEN F.name='Labour' THEN F.name END ) AS 'Labour',wbs_item_id,SUM(Total) AS 'Total'
FROM(
SELECT b.wbs_item_id,w.name,w.parent_id,b.Total AS 'Total'
FROM wbs_items w,budgets t
LEFT JOIN  budget_items b  ON  b.wbs_item_id = w.id  AND b.id=t.id
)E,(SELECT name,id FROM wbs_item  WHERE parent_id IS NULL )F

WHERE E.wbs_item_id = F.id)N
    GROUP BY Materials,Labour,wbs_item_id WITH ROLLUP;


SQL Server : Transactional Replication Computed Column I am a subscriber to a transactional replication subscription. I have no control over the publisher.

I have read the following article about schema changes to the publication database:

Make Schema Changes on Publication Databases

However, I am trying to change the schema on the subscription end (if possible). I am wondering if transactional replication supports the addition of persistent computed columns to the subscriber tables.

I would much prefer this solution versus adding a new view for every table that requires a computed value.

You can do this, but keep in mind that your new column will be wiped out if the subscription is ever reinitialized. ie. The DBA at the publisher alters the publication to add a table, corruption, maintenance, etc. and the snapshot needs to be reapplied, any changes you have made to the replicated schema are dropped and recreated from the publication. So any changes you have made will be dropped as well.


Reset IDENTITY value I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added them again. Now my id's go from 68 -> 92 and this crashes my code.

How do I reset the IDENTITY value?

You can reset the identity value by

DBCC CHECKIDENT('tableName', RESEED, 0)

So next time you insert into TableName, the identity value inserted will be 1.

When you delete rows from the table, it will not reset the Identity value, but it will keep increasing it. Just like what happened in your case.

Now when you truncate the table, it will reset the Identity value to its original Seed value of the table.

Refer to : SQL SERVER – DELETE, TRUNCATE and RESEED Identity for a detailed example and some good explanation of Difference between Truncate and Delete


how to confirm or refute that performance degradation is due because of too many indexes maintained We know that indexes improve select statement and at the same time, indexes have a cost of maintenance on every update/insert/delete statement.

I don't know how to measure/evaluate this cost.

Some context: There are a dozen key tables involved with several inserts/updates, having between 20M and 80M rows. 1400 transaction per second average.

In the lasts months, several indexes were added to optimize performance on different parts of the system. This worked right, but now some degradation on another part of the system appears.

I have this theory, but I don't know how to validate or refute it.

EDIT: I've read some other post, but most are SQL Server oriented, I'm running on SYBASE ASE 15.7, as tagged

How to know when/if I have too many indexes?
How to determine if an Index is required or necessary
Where can I find some guidance on index strategies?

Before trying to determine what indexes to disable (not doable in Sybase ASE) or drop, I'd want a better understanding of what 'some degradation on another part of the system appears' means.  I'd want a lot more details as to the type and degree of degradation.

For the sake of discussion I'm going to assume 'degradation' refers to some queries that are now performing poorly, in which case I'd want to look at what work's been done to (re)tune said queries; are query plans showing the 'wrong' indexes being used? are query plans showing the 'wrong' join order?

Yes, the performance degradation could be tied to the increase in the number of indexes, but what I'm thinking is that the degradation doesn't have anything to do with the overhead of updating the indexes (again, assuming 'degradation' is related to queries now performing poorly) but rather the generation of 'poor' query plans.

When optimizing a query, Sybase ASE is going to attempt to evaluate every possible join order, utilizing every possible index, utilizing every possible statistic on all possible columns and indexes. Net result is the optimizer has a LOT of work to do for queries that reference a large number of tables, indexes and column stats.

While it's technically possible that the Sybase ASE optimizer could go off for an hour while trying to optimize some big-arse, gnarly query ... and yes, the optimizer does have some 'smarts' that allow it to quickly eliminate some join/index combinations ... in reality the optimizer has limitations (via configuration parameters) on how long it can search for the 'best' query plan, with the (unfortunate) result being that for really large, complicated queries the optimizer could time-out before it finds the 'best' query plan, thus leaving us with a 'best up to this point' (ie, not-so-efficient) query plan.

Assuming your degradation issue is related to some queries that have all-of-a-sudden started performing poorly, I'm guessing the additional indexes could have increased the optimizer's workload to the point that it's now timing out before it can find the 'best' query plan.

Under this scenario there are a few options for (re)tuning the queries ... give the optimizer more time to find the 'best' (or at least a 'better') query plan ... provide hints (eg, index hints, abstract query plans) to limit the combinations the optimizer has to consider ... rewrite the query to simplify the optimizer's workload ...

While there may be some other explanations for the degradation you're seeing, the first step would be to get more details about the type and degree of degradation ... something we're not given in this thread ... and (unfortunately) a process that is likely going to be too large/complicated to cover in detail in this medium.


How to setup SQL active/active cluster to achieve Blue / Green instance switching? I am wondering if anyone has ever used a multi-instance cluster (nee 'Active/Active') to achieve blue/green (or A/B) deployment scenarios, and what the best way of configuring it is (using SQL 2012 / Windows 2008 R2)?

To be specific, the scenario I want to achieve is to be able to switch between which cluster instance is being connected to by clients without either the clients or the SQL instances knowing (I stress I'm not talking about node failover here). I'm envisaging that the best way to achieve this is something like:

Setup 2 node cluster, each of which has InstanceA and InstanceB instances
Configure both InstanceA and InstanceB to listen as if they were the default instance on their cluster address (given each instance on a cluster has it's own unique IP)
Use DNS to switch which virtual address clients actually connect to.

This should hopefully enable me to do the following:

Deploy database to instance A, and have clients connect to it via DNS
alias as if default instance
Deploy new version of database to
instance B
Vet new version of database (connecting explicitly to cluster\InstanceB)
Redirect DNS alias to point to instance B's cluster name
Clients now connect to InstanceB without realising anything's changed
Both instances can still failover to the other node in a true outage

Joining the dots, it seems like this should be possible:

http://blogs.msdn.com/b/dbrowne/archive/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance.aspx
https://stackoverflow.com/questions/9313358/connecting-to-a-multiple-instance-sql-server-without-specifying-instance

... but I've never seen a full example. Has anyone done it? Will what's proposed above work? What have I missed?

What you are looking to do is basically have two instances running that you can redirect clients between.  There's no need to do this within Failover clustering as this won't help you any.  Doing this simply requires that you setup two standalone instances of SQL Server, each on a separate Windows server.  Then setup a CNAME in DNS and use that to point people to the correct SQL Instance.

Now for some of the problems.

The data won't be in sync between the two instances.
DNS takes time to replicate and update on the client machine's local cache, so you'll end up with users connected to both systems.

A better solution would be to take a snapshot of the database before doing the schema change release.  If the release is successful delete the snapshot.  If the release fails and you have to rollback you'll need to restore the database from the snapshot.  This will be pretty quick, but there will be an outage while the rollback happens.

What you are looking for basically won't work.


Optimize SUM with time segment WHERE Suppose I have a table A with columns id:int, kpi:int, created_at:datetime. And suppose I would need to fire the following query:
SELECT SUM(kpi) FROM A WHERE (created_at > X AND created_at < Y)

This query would make a full scan. But it can be optimised with a data structure segment tree. It takes O(nlog(n)) to build and O(log(n)) to query (instead of O(n) in case of full scan).
The question is, is there any index or plugin for Postgres (or other SQL Database) that does this?

And why don't you simply create b-tree index on created_at column?
Or compound (created_at+kpi) ? In the second case all the information will be taken during the index scan and no need to read blocks from the table itself.
But if the time range between X and Y is rather big, then the optimizer might think that full table scan is cheaper anyway.
In some particular situations the clustered index may be a good solution, too. For example by days or months, so when you select values, all blocks are read from the same area that belongs to that cluster.


How to change the create and use statement in a large sql file without opening it I need a way to change the schema name which appears in create statement and use statement of mysql dump file. The goal is having the database restored with a different database name on another server. The file is a few gigs in size so opening an editor doesn't work.

How do the experts deal with that?

I also need a way to see the changes before I upload the Mysql to a new server to validate it.

Thanks !

I created a script so that you switch the old DBName with the new DBName

Here is the script

DUMPFILE=junk.txt
DUMPFILENEW=junk.new
DBNAME_OLD=dbAccessData
DBNAME_NEW=rolando

LINE_CDB=`grep -n "^CREATE DATABASE" ${DUMPFILE} | sed 's/\`/ /g' | sed 's/:/ /g' | grep " ${DBNAME_OLD} " | awk '{print $1}'`
LINE_USE=`grep -n "^USE " ${DUMPFILE} | sed 's/\`/ /g' | sed 's/:/ /g' | grep " ${DBNAME_OLD} " | awk '{print $1}'`
(( LINES_IN_THE_MIDDLE = LINE_USE - LINE_CDB + 1 ))
LINES_IN_THE_DUMP=`wc -l < ${DUMPFILE}`

DUMP_SECTION1=${DUMPFILE}.Section1
DUMP_SECTION2=${DUMPFILE}.Section2
DUMP_SECTION2A=${DUMPFILE}.Section2a
DUMP_SECTION3=${DUMPFILE}.Section3

(( X = LINE_CDB - 1 ))
head -${X} ${DUMPFILE} > ${DUMP_SECTION1}

head -${LINE_USE} ${DUMPFILE} | tail -${LINES_IN_THE_MIDDLE} > ${DUMP_SECTION2}

(( X = LINES_IN_THE_DUMP - LINE_USE ))
tail -${X} ${DUMPFILE} > ${DUMP_SECTION3}

echo "sed 's/${DBNAME_OLD}/${DBNAME_NEW}/g' < ${DUMP_SECTION2} > ${DUMP_SECTION2A}" > change_dbname.sh
chmod +x change_dbname.sh
./change_dbname.sh
rm -f change_dbname.sh

cp ${DUMP_SECTION1} ${DUMPFILENEW}
cat ${DUMP_SECTION2A} >> ${DUMPFILENEW}
cat ${DUMP_SECTION3}  >> ${DUMPFILENEW}

rm -f ${DUMP_SECTION1}
rm -f ${DUMP_SECTION2}
rm -f ${DUMP_SECTION2A}
rm -f ${DUMP_SECTION3}

cat ${DUMPFILENEW}

Here is the sample file (junk.txt, 30 lines)

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbAccessData
-- ------------------------------------------------------
-- Server version       5.0.51a-community-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `dbAccessData`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbAccessData` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `dbAccessData`;

--
-- Table structure for table `tblAccountLinks`
--

DROP TABLE IF EXISTS `tblAccountLinks`;

Here is the output

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbAccessData
-- ------------------------------------------------------
-- Server version       5.0.51a-community-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `dbAccessData`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `rolando` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `rolando`;

--
-- Table structure for table `tblAccountLinks`
--

DROP TABLE IF EXISTS `tblAccountLinks`;

Give it a Try !!!


Why use Ola Hallengren DatabaseBackup method with Idera SQLSafe? We are planning to implement Ola Hallengren's Maintenance Solution in our environment.

Our organization on the other end, is planning to purchase Idera SQLSafe (requested a demo first).

I see a parameter @BackupSoftware = 'SQLSAFE' in Ola's solution.

Question:

With Idera SQLSafe tool installed, based on the parameter @BackupSoftware in Ola's solution, I assume even Idera SQLSafe can benefit by Ola's solution. Can I know how does it benefit? - Is it just the compression level?
When we have Ola's solution, is it really necessary for a 3rd party tool?

Ola's solution blends with third party softwares

LITESPEED     LiteSpeed for SQL Server
SQLBACKUP     Red Gate SQL Backup Pro
SQLSAFE Idera SQL Safe Backup

It allows you to leverage automation and has a lot of flexibility in-terms of different parameters e.g. blocksize, compression, etc that you might end up writing when deploying the software to many machines.

See the comparison chart : https://www.idera.com/productssolutions/sqlserver/sqlsafebackup/best-sql-backup-software


Why is Server Configuration in Pgadmin3 inaccessible for me? My Postgres server is running (localhost:5432), but Tools > Server Configuration is greyed out for me as below. I was attempting to access it to edit/locate my postgres.conf file.

adminpack extension

You need to install the adminpack extension on the server.

CREATE EXTENSION IF NOT EXISTS adminpack

Tips:

Verify the extension is available for installation.SELECT * FROM pg_available_extensions ORDER BY "name" ;
Verify installation.SELECT * FROM pg_extension ORDER BY extname ;


"AT TIME ZONE" with zone name PostgreSQL bug? I was answering this stackoverflow question and found strange result:

 select * from  pg_timezone_names where name = 'Europe/Berlin' ;
     name      | abbrev | utc_offset | is_dst
---------------+--------+------------+--------
 Europe/Berlin | CET    | 01:00:00   | f

and next query

select id,
  timestampwithtimezone,
  timestampwithtimezone at time zone 'Europe/Berlin' as berlin,
  timestampwithtimezone at time zone 'CET' as cet
from data ;
 id  | timestampwithtimezone  |       berlin        |         cet
 -----+------------------------+---------------------+---------------------
 205 | 2012-10-28 01:30:00+02 | 2012-10-28 01:30:00 | 2012-10-28 00:30:00
 204 | 2012-10-28 02:00:00+02 | 2012-10-28 02:00:00 | 2012-10-28 01:00:00
 203 | 2012-10-28 02:30:00+02 | 2012-10-28 02:30:00 | 2012-10-28 01:30:00
 202 | 2012-10-28 02:59:59+02 | 2012-10-28 02:59:59 | 2012-10-28 01:59:59
 106 | 2012-10-28 02:00:00+01 | 2012-10-28 02:00:00 | 2012-10-28 02:00:00

I'm using PostgreSQL 9.1.2 and ubuntu 12.04.
Just checked that on 8.2.11 result is the same.

According to documentation it doesn't matter if I use name or abbreviation.

Is this a bug?
Am I doing something wrong?
Can someone explain this result?

EDIT
For the comment that CET is not Europe/Berlin.

I'm just selecting values from pg_timezone_names.

select * from  pg_timezone_names  where abbrev ='CEST';
 name | abbrev | utc_offset | is_dst
------+--------+------------+--------

and

select * from  pg_timezone_names  where abbrev ='CET';
        name         | abbrev | utc_offset | is_dst
---------------------+--------+------------+--------
 Africa/Tunis        | CET    | 01:00:00   | f
 Africa/Algiers      | CET    | 01:00:00   | f
 Africa/Ceuta        | CET    | 01:00:00   | f
 CET                 | CET    | 01:00:00   | f
 Atlantic/Jan_Mayen  | CET    | 01:00:00   | f
 Arctic/Longyearbyen | CET    | 01:00:00   | f
 Poland              | CET    | 01:00:00   | f
 .....

During winter Europe/Berlin is +01. During summer it is +02.

EDIT2
In 2012-10-28 timezone has change from summer time to winter time at 2:00.
This two records have the same value in Europe/Berlin:

204 | 2012-10-28 02:00:00+02 | 2012-10-28 02:00:00 | 2012-10-28 01:00:00
106 | 2012-10-28 02:00:00+01 | 2012-10-28 02:00:00 | 2012-10-28 02:00:00

This suggest that if I use one of abbreviations (CET or CEST) for big data range (summer time and winter time) result will be wrong for some of records. Will be good if I use 'Europe/Berlin'.

I changed the system time to '2012-01-17' and pg_timezone_names has changed also.

select * from  pg_timezone_names  where name ='Europe/Berlin';
     name      | abbrev | utc_offset | is_dst
---------------+--------+------------+--------
 Europe/Berlin | CEST   | 02:00:00   | t

Actually, the documentation says clearly that the time zone name and abbreviation will behave differently.

  In short, this is the difference between abbreviations and full names:
  abbreviations always represent a fixed offset from UTC, whereas most
  of the full names imply a local daylight-savings time rule, and so
  have two possible UTC offsets. Reference

FWIW, that same reference also says

  We do not recommend using the type time with time zone (though it is
  supported by PostgreSQL for legacy applications and for compliance
  with the SQL standard).


Application Server is not able to connect to SQL Server database when TLS 1.2 or 1.1 is enabled In our environment, SQL Server 2012 Servers whenever TLS 1.2 or TLS 1.1 is enabled Application Server is not able to connect to the database.The same issue is observed only on MS SQL Server 2012 related Servers.

Error Message:

  Connection failed:
  SQLState: '01000'
  SQL Server Error: 1
  [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreateCredentials())
  Connection failed:
  SQLState: '08001'
  SQL Server Error: 18
  [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

SQL Server 2012 does not support TLS 1.2 in all SPs or versions. See this table for CU and Hotfix minimum versions https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server


No service broker active on database I am trying to set up query notification however when I try to create a queue and service I get the error 'There is no Service Broker active in the database. Change to a database context that contains a Service Broker.'  When I run the query

select is_broker_enabled from sys.databases where name='db01'

It returns 1 which means that a broker has been enabled on the database

Here are the sql commands I am running to create my queue and service...

CREATE QUEUE DeviceChangeMessages
CREATE SERVICE DeviceChangeNotifications ON QUEUE DeviceChangeMessages ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

Well I think it's quite simple.

You just get the message that Service Broker isn't enabled. But you query the system databases, not your current Database. Just as an idea, might it be, that you run the Script for setting up the notification in your master database while you forget to change the database context to your db01 where the Service Broker is enabled?
Another edge case can be the msdb, recheck if it is enabled to.
Otherwise try to enable/disable the Service Broker in your db01 and retry.

You can use this:

ALTER DATABASE dbo1 SET DISABLE_BROKER
ALTER DATABASE dbo1 SET ENABLE_BROKER


PostgreSQL know table hierarchy Is there a way to know the parent - child relations (in terms of foreign keys) of all the tables in postgresql database? We are planning a full database migration using AWS DMS, we need specify in what order we can migrate our tables.

The documentation suggests to disable constraint checks:

If the target is a PostgreSQL-compatible database, then you can see foreign key violation errors during the CDC phase. To resolve this error, set the session_replication_role parameter to replica.

If you really want to specify the table order manually, use any of the queries from this question to get a list; for example:
SELECT
    tc.table_schema,
    tc.table_name,
    ccu.table_name AS foreign_table_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.constraint_column_usage AS ccu
    USING (constraint_name, table_schema)
WHERE tc.constraint_type = 'FOREIGN KEY';


ORA-31114: XDB configuration has been deleted or is corrupted ORA-31114: XDB configuration has been deleted or is corrupted

How to recreate the XDB configuration? Do I need to remove the existing component first?

By the way I can't run UTLRP scritp for the same reason.
I also cannot perform a datapump export.

Removing XDB

Run the following script to remove XDB

SQL> @C:\Oracle\Ora11\R2\rdbms\admin\catnoqm.sql

It is very important to restart your database after removing XDB and before installing it again.

Installing XDB

The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password

B. XDB user default tablespace

(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior to running the script.)

C. XDB user temporary tablespace

D. SecureFiles = YES or NO

(If YES is specified, the XDB repository will use SecureFile storage.
If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using
Automatic Segment Space Management (ASSM) for SecureFiles to be used.)

Therefore the syntax to run catqm.sql is the following:

SQL> catqm.sql A B C D

SQL> @C:\Oracle\Ora11\R2\rdbms\admin\catqm.sql XDB XDB TEMP YES

Recompile all invalid objects within the database

SQL> @C:\Oracle\Ora11\R2\rdbms\admin\utlrp.sql

Verify XDB Status

Check registry

SQL> select comp_name, version, status from dba_registry where comp_id = 'XDB';

COMP_NAME            VERSION     STATUS
-------------------  ----------  -----
Oracle XML Database  11.2.0.1.0  VALID

Check invalid objects owned by XDB

SQL> select owner, object_name, object_type, status from dba_objects where owner = 'XDB' and status = 'INVALID';

no rows selected


mongodb: update string field to integer I have a collection that has some fields that should be integers. Due to a software bug, some of these values are strings. I need to convert them. Here is an example document:

var coll = db.getCollection('info');
coll.find({ id: "985517" });

Result:

{
    "_id" : "41cbb4e48284c0612a1c4b3ee5e3e2720c01197a",
    "id" : "985517",
    "title" : "009_1a.jpg",
    "ext" : ".jpg",
    "content_type" : "image/jpeg; charset=utf-8",
    "sha1" : "41cbb4e48284c0612a1c4b3ee5e3e2720c01197a",
    "has_thumb" : true,
    "size" : 2917,
    "width_px" : "70",
    "height_px" : "69"
}

I try to convert width_px and height_px values into integers. My first naive try:

var coll = db.getCollection('info');
var bulk = coll.initializeOrderedBulkOp();
var counter = 0;
coll.find({ width_px: { $type: "string" }, id: "985516" }).limit(100).forEach(function(data) {
    var updoc = {
        "$set": {}
    };
    updoc["$set"]["width_px"] = parseInt(data.width_px);
    // queue the update
    bulk.find({
        "_id": data._id
    }).update(updoc);
    counter++;
    // Drain and re-initialize every 1000 update statements
    if (counter % 1000 == 0) {
        bulk.execute();
        bulk = coll.initializeOrderedBulkOp();
    }
    })
    // Add the rest in the queue
if (counter % 1000 != 0) bulk.execute();

The result is dissapointing:

{
    "_id" : "41cbb4e48284c0612a1c4b3ee5e3e2720c01197a",
    "id" : "985517",
    "title" : "009_1a.jpg",
    "ext" : ".jpg",
    "content_type" : "image/jpeg; charset=utf-8",
    "sha1" : "41cbb4e48284c0612a1c4b3ee5e3e2720c01197a",
    "has_thumb" : true,
    "size" : 2917,
    "width_px" : 70.0,
    "height_px" : "69"
}

So it was updated to double, instead of int32. I understand that in JavaScript, there is no separate double and integer type, just "number". But I need to update 100 000 documents and I want to do this efficiently.

But how?

As per MongoDB documentation here From the Documents tab in MongoDB Compass, you can view, insert, clone, modify, and delete documents .

First you have to connect your mongod with MongoDB Compass then you shall able to change your  desire data type of their respective field in MongoDB.

Change Field Type

You can change the data type of a field by using the data type selectors on the right of the field.

  Note: Creating, deleting, editing and cloning documents is not permitted in MongoDB Compass Readonly Edition.

For your further ref here


Dynamic Number of Columns in Pivot Table I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate pivot table for fixed subjectID like

SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks
from [Exam].[ObtainedMarkEntry]) as InvoiceResult
PIVOT
(
    sum(ObtainedMarks) for [SubjectID] in  ([1],[2],[3],[4])
)as finalResult

This works but problem is 1,2,3 is not static it should come from same table

So I did something like

declare @minvalue int, @maxvalue int
declare @myArray varchar(max)
set @MyArray=''

select RowID=IDENTITY(int, 1,1), SubjectID into #tempSubject from [Exam].[ObtainedMarkEntry]
select * from #tempSubject
set @minvalue = (select min(RowID) from #tempSubject)
set @maxvalue = (select max(RowID) from #tempSubject)
while (@minvalue<=@maxvalue)
begin
    declare @sid nvarchar(10)
    set @sid=(select SubjectID from #tempSubject where RowID=@minvalue)
    set @myArray= @myArray + '[' + @sid + '], '
    set @minvalue= @minvalue+1
end
set @myArray= SUBSTRING(@myArray,0,len(@myArray))
drop table #tempSubject

declare @q as nvarchar(max)
SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks
from [Exam].[ObtainedMarkEntry]) as InvoiceResult
PIVOT
(
    sum(ObtainedMarks) for [SubjectID] in  (@myArray)
)as finalResult

Now I am getting error at in(@myArray) can't I assign variable in this section.

Working from Aaron Bertrand's Script to create dynamic PIVOT queries in SQL Server and with a sample data set defined thus:

if object_id('#ObtainedMarkEntry') is not null drop table #ObtainedMarkEntry;
create table #ObtainedMarkEntry(
    StudentID       int not null,
    SubjectID       int not null,
    ObtainedMarks       int not null
);
insert #ObtainedMarkEntry(StudentID, SubjectID, ObtainedMarks)
values
        (1,100,90) ,(1,101,70)
       ,(2,100,65) ,(2,300,55)
;

then this SQL:

declare @columnsSrc nvarchar(max) = N''
       ,@columnsDst nvarchar(max) = N''
       ,@sql        nvarchar(max)
       ,@KeyColumns nvarchar(max) = N'StudentID'
       ,@compatibility int = (
            select top 1 compatibility_level from sys.databases
            where name = db_name()
            order by Name
       );
declare @GroupBy nvarchar(max) =
--case when @compatibility <= 90
--   then case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns + ' with rollup' end
--   else case when len(@KeyColumns)=0 then '' else 'group by rollup (' + @KeyColumns + ')'    end
--end
case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns end

;

select
    @columnsSrc += nchar(10) + N',' + quotename(SubjectID)
   ,@columnsDst += nchar(10) + N',sum(isnull(' + quotename(SubjectID) + N',0)) as '
                                               + quotename(SubjectID)
  from (
      select SubjectID
      from #ObtainedMarkEntry
      group by SubjectID
  ) as x
  order by x.SubjectID
;

set @sql = N'
select ' +
  case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
  STUFF(@columnsDst, 1, 2, '') + '
from (
  select' + nchar(10) +
    case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
'      SubjectID, ObtainedMarks
   from #ObtainedMarkEntry
) as j
pivot (
  sum(ObtainedMarks) for SubjectID in ('
  + stuff(replace(@columnsSrc, ',p.[', ',['), 2, 1, '')
  + ')
) as p' + nchar(10) +
@GroupBy +
';'
;

--print @sql;
exec sp_executesql @sql;
go

yields as desired:

StudentID   100         101         300
----------- ----------- ----------- -----------
1           90          70          0
2           65          0           55


Postresql replication pg_basebackup no pg_hbaconf entry for replication connection I have updated my pg_hba.conf on the master to this.

host    loko     replicator      10.0.2.15/32               trust

and I did reload the configuration.  Then I created a user with this command

 CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'repl'

loko which is the database on my master and replicator as the user.

and running this command on the slave

pg_basebackup -h x.x.x.x -D "C:\Program Files\PostgreSQL\9.4\data" -U
replicator -v -P --write-recovery-conf -X stream

results in

no pg_hba.conf entry for replication connection from host x.x.x.x. user replicator, SSL off

Thanks a lot for any help. Im new to this so I don't have any idea whats wrong with this. I just followed a tutorial.

You have to grant access in replication in pg_hba.conf.
Here's the line you should add:

host    replication     replicator      10.0.2.15/32               trust

You will find documentation here:
https://www.postgresql.org/docs/9.6/static/auth-pg-hba-conf.html

Here's the phrase that explain everything:

  The value replication specifies that the record matches if a replication connection is requested (note that replication connections do not specify any particular database).


MySQL Workbench EER Diagram and ENUM data type I am creating EER model diagram using MySQL Workbench (version : 5.2.47)

The version for MySQL, I am using is 5.5.35-0ubuntu0.13.10.2.

I have created a schema with 'Default Collation' set to 'utf8 - utf8_general_ci'.

I have created a table in EER diagram.

  My basis goal is to add a column with ENUM data type.

But when I set the data type for my column to ENUM('A', 'B') and move ahead (by clicking enter key or tab key), the datatype for that column is replaced with last datatype (in my case VARCHAR(45) as its default for each column we create).

Can anybody help me to sort out this issue ?
Is there any issue in my configuration or method I am using ?

ENUM datatype was not supported in MySQL Workbench v5.x. I had the same problem with MWB v5.2. I just downloaded the latest 6.x (v6.1.4) and was able to create an ENUM which now appears in the Datatype dropdown menu of the table editor.

One word of caution regarding ENUMs, as noted in the MWB docs:

  Caution:
  Care must be taken when entering a default value for ENUM columns because a non-numeric default will not be automatically quoted. You must manually add single quote characters for the default value. Note that MySQL Workbench will not prevent you from entering the default value without the single quotation marks. If a non-numeric default value is entered without quotation marks, this will lead to errors. For example, if the model is reverse engineered, the script will contain unquoted default values for ENUM columns and will fail if an attempt is made to run the script on MySQL Server.


Restore a single database backup (.bak) file into multiple filegroups I have a SQL Server 2014 database DB01 (single filegroup - PRIMARY) and I took a backup of it into a DB01.bak file in SSMS.

I have another database DB02 with 1 primary filegroup (1 file) and one secondary filegroup (2 files). I am trying to restore the DB01.bak file into DB02 with multiple filegroups. Is it possible to do this in SSMS?

No it is not possible to do what you are asking.  SQL is backing up the current state of the database.  What tables are in what filegroup is part of the database information. It is possible to restore the backup to a new database name.  Once the data is on the new server you could research and create the file groups you want, make your changes, etc.   Get everything you the way you want it.   Back it up a final time and then restore it to the database name you want or you can simply rename the database.  Depending on the size it might be faster to restore it.


Oracle 11g setting for backwards compatibility? Edit: Please close this topic.

Unfortunately, this issue can't be resolved as it turned out the question is very off-base to begin with. The actual issue was completely unrelated to the question I asked.

We are experiencing a data inconsistency issue after importing an exported Oracle 9i database into Oracle 11g.

Is there a setting that might facilitate this process better? A "compatibility-mode" settings or group of settings of sorts?

I'm looking for an extra lead that might help our DBAs, who are a little new to these sorts of issues I think, as this import falls outside of their normal process.

Thanks in advance for any help! Happy to provide more information as needed.

Take a look at the COMPATIBLE initialization parameter both here and here. These links are more to do with when you upgrade a database rather than import into a higher version but it should help if, perhaps, you can import your data into a cloned 9i database (so as not to mess up your original database) then upgrade that with the compatibility setting on to 9i.


How do i assign a custom id using a trigger using sequence on a table i want to modify my current table which is currently generating id's using a trigger from 1 using sequence_name.nextval to something like DON-100 or REC-124 etc.I have seen some examples doing this using functions which modify the values after the trigger performs it's job which looks like overhead how can i modify my current trigger to produce these custom id's and is not too complex.

CREATE TABLE Atendant (
   ID NUMBER(10) PRIMARY KEY,
   NAME  VARCHAR2(25 CHAR) NOT NULL
);

CREATE SEQUENCE  Atendant_SEQ  NOCACHE;

CREATE or replace TRIGGER  Atendant_BI
  BEFORE INSERT ON  Atendant
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.ID  IS NULL THEN
    :new.id := "ATN"Atendant_SEQ.nextval;
  END IF;
END;

You're nearly there! A few small changes needed.

Table definition:

CREATE TABLE Atendant (
   ID VARCHAR(20) PRIMARY KEY,
   NAME  VARCHAR2(25 CHAR) NOT NULL
);

Trigger:

CREATE or replace TRIGGER  Atendant_BI
  BEFORE INSERT ON  Atendant
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.ID  IS NULL THEN
    :new.id := 'ATN'||Atendant_SEQ.nextval;
  END IF;
END;
/

Test:

SQL> insert into Atendant (NAME) values ('phil');

1 row created.

SQL> select * from Atendant;

ID     NAME
------ ----------
ATN1   phil

SQL>

So all you were really missing was the data type and doing the concatenation correctly.


execution plan with and clause in where I have this SQL statement:

select *
from _my_table1
where 1 = 0
      and (exists (select 1 from _my_table2 where id =7))

The execution plan is:

|--Constant Scan

Now when I change my query to

Declare @i int = 1
SELECT *
FROM dbo._my_table1
WHERE @i = 0 AND (EXISTS (SELECT 1 FROM dbo._my_table2 WHERE id = 7))

I get the following plan:

  |--Nested Loops(Left Semi Join)
       |--Filter(WHERE:(STARTUP EXPR([@i]=(0))))
       |    |--Clustered Index Scan(OBJECT:([DEV-RWA_IECMS-DE_Staging_20160301].[dbo].[_my_table1].[PK___my_tabl__3213E83FEF434214]))
       |--Clustered Index Seek(OBJECT:([DEV-RWA_IECMS-DE_Staging_20160301].[dbo].[_my_table2].[PK___my_tabl__3213E83F4D4FA6E2]), SEEK:([DEV-RWA_IECMS-DE_Staging_20160301].[dbo].[_my_table2].[id]=(7)) ORDERED FORWARD)

The question is why SQL Server checks for second part of WHERE clause, if the first part returns false. Is there any workaround to force the SQL Engine not to check the second part of where, if the first part returns false.

OPTION (RECOMPILE) is your friend here. SQL Server has no clue what the value for @i will be at execution time, so it compiles a plan that will work for any parameter value.

Forcing recompile will override that behaviour and produce a plan with a constant scan.

Example:

DECLARE @i int = 1;
SELECT *
FROM master.dbo.spt_values
WHERE @i = 0
OPTION (RECOMPILE);

If you capture the actual execution plan, it will contain only a constant scan. Without OPTION (RECOMPILE) it contains the regular plan.


Postgres : Relation does not exist error I used pg_restore to load my postgres db with a dump file.
I connected to my db with my user :

sudo -u arajguru psql dump

select current_user;
 current_user
--------------
 arajguru

Now I was able to see all the newly created tables:

dump=> \dt
               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | Approvals         | table | arajguru
 public | Approvers         | table | arajguru
 public | Conditions        | table | arajguru
 public | Entities          | table | arajguru
 public | EntityDefinitions | table | arajguru
 public | Projects          | table | arajguru
 public | Rules             | table | arajguru
 public | run_history       | table | arajguru
(8 rows)

But when I try to fire a select * query, it gave me this error:

dump=> select * from Approvals;
ERROR:  relation "approvals" does not exist
LINE 1: select * from Approvals;

What can be the reason for this error? Please help.

You created your tables with double quotes, and now the names are case sensitive.

As documented in the manual "Approvals" and Approvals are two different names.

Now that you created the table names in a case sensitive manner, you have to use those dreaded double quotes always.

select *
from "Approvals";

As a general advice: never use double quotes in SQL.


Installing SQL Server Express in parallel of Standard On a server I have a SQL Server 2008 r2 standard performing very bad.

To make a quick & dirty test I would like to try to install express edition.

This server is not administrated by me, it is a server of a customer.

A stupid query is not running on that standard SQL Server, it takes 45 minutes to execute, while on my laptop it takes 2 seconds with the same db.

So I suspect either SQL Server is corrupted or windows is corrupted (other ideas?).

This is why I would like to try to install express (to bypass "SQL Server is corrupted").

Is it possible to install express when standard is there?

You are on  a totally wrong path here. Seriously.

  So i suspect either SQL SErver is corrupted or windows is corrupted (other ideas?).

Why? To be THAT corrupt (2 seconds to 45 minutes) would basically mean not running. Event log full of errors?

Check performance counters. I would say totally overloaded disc subsystem or too little memory - a real normal debug situation, nothing else. Installing Express won't solve anything.

To answer your question.

Yes, it is possible. It is another instance with a predefined name (SQLExpress).

But you are much better on to sit down and do beginner baseline analysis - performance counters etc. SQL Server pretty much can not be THAT currupt as you indicate and still work. I think that is a purely performance issue and should be dealt with it - logically, not with blind actionism.


ORA-12504: TNS:listener was not given I created container orcl2 database with pluggable database pdborcl2. When I try to connect

SQL> connect system@pdborcl2

I get this error:

  ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

My tnsnames.ora look as follows:

ORCL2 =  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = orcl2.localhost)))
PDBORCL2 = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl2.localhost)))

I can normally connect to orcl2 with:

SQL> connect system@orcl2

You have CONNECT DATA instead of CONNECT_DATA (space instead of underscore). Fix that, and try again.


PostgreSQL Error: server closed the connection unexpectedly I cannot connect to PostgreSQL server from PgAdmin III , so I get this error:

Error connecting to the server:  server closed the connection
unexpectedly
This probably means the server terminated abnormally before or while
processing the request.

I get the same error when I try connecting from psql.
I have got this error 2 days ago but after restarting the computer the problem disappears , but this time I have restarted it too many times vainly.
I'm using PostgreSQL  9.0.5 on windows 7 with ArcSDE 10.1 (Arcgis Spatial Database add-on), I have deactivated the antivirus and firewall.
I have tried to reinstall PgSQL but the problem persists.
How I can connect to my Pgsql service and get rid of this error?

It looks like broken data file or broken indexes. If this problem grows, then it can shows some deep system problems - problems with hw (memory, CPU), os (antivirus)... Try to use a commands executed from command prompt for database access repairing a) reindexdb - refresh all indexes, b) pg_dump - recheck a readability of data files.


AWS RDS MyISAM tables from mysql and information_schema and good snapshots I'm wondering about the topic of moving all MyISAM tables to InnoDB in RDS. Mostly because I have this annoying warning every day in the RDS console. One thing is pretty clear, the tables of the customer applications must be altered to InnoDB (if possible). No question about that. But what worries my is the "system" tables. If I run this query:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MyISAM'

I get lot of tables with MyISAm tables that are not from the customer database but the MySQL itself, for example:

information_schema | COLUMNS                   | MyISAM |
information_schema | EVENTS                    | MyISAM |

Let me call it "system tables".

It is kind of mandatory to also ALTER this "system tables" to InnoDB? I did not found any specific of this tables, and the warning message of RDS is not clear about that:

  DB Instance db-byhours-56 contains MyISAM tables that have not been
  migrated to InnoDB. These tables can impact your ability to perform
  point-in-time restores. Consider converting these tables to InnoDB.
  Please refer to
  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.BackingUpAndRestoringAmazonRDSInstances.html#Overview.BackupDeviceRestrictions

Any ideas or comments?

Thanks !

question bonus: what about other kind of engine like MEMORY? I have some customer with few tables like that engine. Can be this engine also a problem to get good snapshots?

The MyISAM tables in the mysql schema are fully supported in RDS snapshots.  It's the MyISAM tables elsewhere that are potentially problematic.

Never alter the tables in the mysql schema.  That will leave you with a server that may never start up again.

  Important

  Do not convert MySQL system tables in the mysql database from MyISAM to the InnoDB type. This is an unsupported operation.

  https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html

The exception for the tables in mysql is because RDS takes care to flush and lock those during a snapshot, so they come out okay on the other side.  This isn't documented but can be observed.

The tables in information_schema are not real tables.  Leave those alone as well.

The data in MEMORY tables is transient, and not persisted to disk, so they won't survive a snapshot or even a restart.  Their definitions should be okay but RDS is really intended for use only with InnoDB.  (The FEDERATED storage engine is disabled.)


What is recommended level of security for a production database? I have a production database on our local server. Currently there are two many logins to the database, about 20, half of them are windows authenticated and the other rest are SQL Server authenticated. I would really like to keep the logins minimum, so that I know who has accesses the database while also maintain security, but also want to keep safety factor, like keep one windows authenticated user so the we have access to the db in case no one has password. My question are

What is the recommended security level of production server? Should it be Windows authenticated only or SQL Server authenticated?
How many logins should one keep? one or more?
Should the default sa login be disabled, since it can be a security threat?

Any other security risk, I need to be aware of? Our application as well as database both sits on the same local server.

Windows Authentication should be chosen over SQL Server Auth. Windows Auth is inherently more secure, as it's controlled by the OS. Granted, there are times when you won't be able to get around a SQL Server Auth principal because of a third party application, or whatever the surrounding circumstances are.
You should have the amount of respective logins for each logical principal that can connect or could be audited. In other words, if every application uses the same login, and your auditing captures login name, it'll be virtually useless.
You should disable the sa account as it can be a security threat. The reason behind this is because it is extremely well-known login name for SQL Server instances. It takes out a lot of the guessing game for hackers. Disable this account, but ensure there are other appropriate principals with sysadmin privileges.

As for other security measures, they are far and vast. Our own member, mrdenny, has written a book on this very subject. A few pointers are you'll want to keep a tight sand box on your principals. In other words, give only permissions that the principal will need in order to perform the functionality, and no more.


Job for mysql.service failed because the control process exited with error code Today I was working on my project which has the SQL and sending and receiving the information from flask to SQL and vice-versa, but all of the sudden without any change of the configuration it should me this error:

  ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost'(111)

I tried to stop the service and work at the safe-mode but once I want to start again the service it gives me:

  Failed to start MySQL Community Server

I tried to swap the extra space but it did not work, I tried to remove the log but did not work, I tried to bind and unbind the 127.0.0.1 but it did not work.

I have attached my cnf below:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /var/run/mysqld
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking
#skip-grant-tables
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address       = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
#max_allowed_packet = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log     = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Edit

Here is the problem that error.log showed me :

2020-03-17T10:32:14.514845Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-03-17T10:32:14.514867Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files

I don't see anyone else answering this but I had the same issue and this could help someone.
The first error should be about network, binding by default an address such as 127.0.0.1 for localhost could trigger that and you should try to connect using the binding address a not 'localhost'.
Now, after entering safe mode I ran into a similar error! Even when the process of mysql is not "Active" there where still running processes after I stopped the safe-mode session and that was preventing the service to start. I was able to locate does processes using this command: sudo service mysql stop && pkill -f mysql receiving this alert:
pkill: killing pid 8912 failed: Operation not permitted
pkill: killing pid 8913 failed: Operation not permitted
pkill: killing pid 9273 failed: Operation not permitted

after that I manually kill the processes using:
sudo kill 8912
sudo kill 8913
sudo kill 9273

and ran again the command: sudo service mysql stop && pkill -f mysql
Finally I started the service again service mysql start and was running normally. I entered safe-mode to reset the password of the root user.


How do you monitor specific SQL statement of a specific user ? We have a few standalone users here which cause high server loads for several 11.1.0.7 Oracle's.
I spoke with them a few times but it seems that their queries are causing high loads and probably bottlenecks

Is there any option in Oracle to monitor or see which SQLs they running ? I'm not talking about only the ones which cause high loads, although they're the most important ones I would like to see all of their queries.

You might want to look into AUDIT ALL STATEMENTS BY user. As per Oracle's documentation, audit all statements by ...  audits all top-level SQL statements executed. Top-level SQL statements are issued directly by a user. SQL statements run from within a PL/SQL procedure or function are not considered top-level statements. Therefore, this clause does not audit the statements executed within PL/SQL procedures or functions. However, the execution of the PL/SQL procedure or function itself is audited. This clause is useful if you want to audit all the statements in a specific environment, regardless of other auditing configurations that are system wide or user specific.


Dont' see option to download/upload some SSRS reports I have a SSRS 2016 instances with 100s of reports on one of my server. I want to migrate these reports from this instance to an existing new instance which already has some reports for different business unit. So basically I am downloading all RDLs and uploading those to a new instance.

While working on this task, I found one weird thing while downloading reports was for some reports download option is not present. My question is how to download these reports and why am I seeing this behavior?

It turns out that I had to give content manager explicitly to all the reports where download option was unavailable even though I was a site admin on that box along with content manager on parent folders of respective reports. This fixed the issue and I was able to see the download option now. It's strange when you have admin rights on entire site but still have to give rights to individual reports.


MySQL is not releasing memory after memory tables are dropped After creating several tables of memory engine and dropping them, mysqld is not releasing all the corresponding memory.

Below are the exact steps to reproduce the problem. Memory usage is observed using htop (RES column).

Disable swap

sudo swapoff -a

Launch mysql with memory limit of 4GB

docker run -d -m 4g --shm-size 4g --name mysql_test \
    -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:5.7.25

docker exec -it mysql_test bash

# test to allocate 3G memory in /dev/shm
dd if=/dev/zero of=/dev/shm/dummy bs=1M count=3K
rm /dev/shm/dummy

# create a named pipe for loading data
mkfifo /var/lib/mysql-files/tmp.pipe

# launch mysql client
mysql -uroot

Create database

create database if not exists mem;
use mem;

-- allow memory table of max size 4GB for this session
set max_heap_table_size = 1024 * 1024 * 1024 * 4;

  mysqld memory usage: 193M

Create table and load data

In mysql shell:

create temporary table memory_tbl ( idx varchar(1000) ) engine=memory;
load data infile '/var/lib/mysql-files/tmp.pipe' into table memory_tbl (idx);

In another terminal:

docker exec -it mysql_test sh -c 'yes 0 | head -1048576 > /var/lib/mysql-files/tmp.pipe'

  mysqld memory usage: 1210M

Back to mysql shell to duplicate the table:

create temporary table memory_tbl2 like memory_tbl;
insert into memory_tbl2 select * from memory_tbl;

  mysqld memory usage: 2227M

Duplicate the table again:

create temporary table memory_tbl3 like memory_tbl;
insert into memory_tbl3 select * from memory_tbl;

  mysqld memory usage: 3243M

Drop tables

drop table memory_tbl;

  mysqld memory usage: 3244M

drop table memory_tbl2;

  mysqld memory usage: 3244M

drop table memory_tbl3;

  mysqld memory usage: 2302M

Try allocate memory

In another shell:

docker exec -it mysql_test bash

# allocate 1.5G memory
dd if=/dev/zero of=/dev/shm/dummy bs=1M count=1536
rm /dev/shm/dummy

# allocate 2G memory
dd if=/dev/zero of=/dev/shm/dummy bs=1M count=2K
# error occurs
# dd: error writing '/dev/shm/dummy': Cannot allocate memory
rm /dev/shm/dummy

I wonder if this is a bug of MySQL server. Otherwise, would there any way to ask MySQL to release the memory without restarting mysqld?

Testing environment:

Ubuntu 18.04.1 LTS
Kernel 4.15.0-34-generic
Docker 18.06.1-ce
mysql-5.7.25
glibc 2.24

Per suggestion in a MariaDB server bug Memory not freed on memory table drop, replacing default glibc with jemalloc solves the problem.

Steps below shows how to launch mysqld with jemalloc:

docker run -it -m 4g --shm-size 4g --name mysql_test \
    -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:5.7.25 bash
apt-get update
apt-get install -y libjemalloc-dev
# adhoc preload jemalloc for ease of testing
# updating /etc/mysql/my.cnf is preferred for permanent setting
LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.1 docker-entrypoint.sh mysqld

Then we may proceed testing from step 3 in another shell. Note that memory usage of mysqld drops by about 1G for each drop table statement.
At the end, memory usage drops to level similar to initial stage and creating a 3G file in /dev/shm is possible.

Update:

The actual library needed is libjemalloc1 in this case.
libjemalloc-dev includes libjemalloc1 as a dependency and that's why it worked but at the price of installing a bunch of files we don't need. Thanks Michael - sqlbot for pointing this out.


listener does not currently know of SID given in connect descriptor in oracle 10g and solaris 10 env I have an Oracle 10g database on a Solaris 10 system. The SID=OTRS, I can connect to the Oracle database from the local server itself. But I am not able to connect from another client. It is returning an error

ORA-12505 TNS:listener does not currently know of SID given in connect descriptor

If I execute the command show parameter listener, it shows

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_OTRS
remote_listener                      string

But my listener.ora file is

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = otrs)
      (SID_NAME = otrs)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_3)
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

and my tnsnames.ora file is

otrs =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ticket)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = otrs)
    )
  )

CONNECT_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID= otrs)
      (PRESENTATION = RO)
    )
  )

If I run the command lsnrctl services , It shows

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 27-DEC-2012 10:42:23

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TICKET)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

The listener is not recognizing the sid OTRS. What is solution to resolve this error?

There are a few problems.

 given the fact that you specified local_listener, you want your database instance to register to a non default listener for which the tnsalias should be LISTENER_OTRS
 the tnsalias LISTENER_OTRS is missing
 the tnsalias otrs is misformed (it has multiple addresses but not the ADDRESS_LIST parameter (in this case, the last piece will be ignored))

fix: remove the local_listener parameter and your instance will register perfectly fine on the listener that sits on port 1521 (the default port). The instance will register during startup or, if the listener is started later, once every few minutes or after issuing 'alter system register;'.

If you did this, lsnrctl status will show the instance name.
Also: don't forget that on *nix, the ORACLE_SID is case sensitive.

The otrs tnsalias could be used to connect to the instance, once it has registered itself to the listener. You can test this using 'tnsping otrs'


Numeric cell with 0 not null I have the following query (in a Oracle Database):

          SELECT SUM (CANTITATE)
             FROM ANEXA2A_TABEL2_ROWS
            WHERE TIP_ACTIUNE = 'COLECTATE'
                  AND TIP_AMB = 'STICLA'
                  AND COD LIKE '%*'

The problem is that if I don't have any row with * in COD, the result is a empty cell and I want it 0. How can I do that?

Thanks!

Use NVL().

 SELECT NVL(SUM(CANTITATE),0)
             FROM ANEXA2A_TABEL2_ROWS
            WHERE TIP_ACTIUNE = 'COLECTATE'
                  AND TIP_AMB = 'STICLA'
                  AND COD LIKE '%*';

That'll make the SUM() function return 0 if it would ordinarily return NULL.


Unrecognized configuration parameter when using set_config(text, text, boolean) in CTE I'm trying to implement a simple ABAC system using row level security, with main policy defined as following:
CREATE policy resource_access ON resource
USING (
  (
    org_id::varchar = current_setting('scope.org.id', true)
  )
  AND
  (
    acl_read && regexp_split_to_array(current_setting('scope.acl'), ',')::varchar[]
  )
)

Issuing queries akin to:
WITH
  acl AS (SELECT set_config('scope.acl', 'ACL', true) "__acl"),
  result AS ( ... )
SELECT * FROM acl, result

With the main reason to use WITH is to avoid multiple statements when queries are later PREPAREd and EXECUTEd by the Postgres driver I'm using.
The result in example above can contain any arbitrary queries required by the application. To ensure that set_config is executed in the query, it's also added to the final SELECT.
However, I still do consistently encounter the following error:
QueryFailedError: unrecognized configuration parameter "scope.acl"

Which appears to be caused by executing the subquery from WITH in isolation from the result query.
So the main questions are:

Is there any elegant way to ensure running set_config before the main query (the one in result) is executed?
Is there any better way to construct queries for the application side, to avoid using WITH, but keeping them as a single SQL statement?

Thank you!

We've ended up using the JOINs over CTEs, which implicitly applies the expected order, and queries looking like:
WITH
      acl AS (SELECT set_config('scope.acl', 'VALUE', TRUE) "__acl"),
      result AS (${query})
    SELECT * FROM bypass FULL OUTER JOIN result ON 1 = 1

In which case, acl CTE runs before the result and ensures that RLS could pick up a correct configuration.
Whilst it might very well be sub-optimal for a larger scale table, it worked perfectly for our scale (10k-100k rows) with no noticeable performance degradation compared to using CTEs without the resulting FULL OUTER JOIN. More importantly, our correctness tests haven't highlighted any deviations from the expected order, despite this behavior (of result running after acl in CTEs block) is implicit.


Is there a way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column I use Postgres JSONB columns to store translations for dynamic content of a multi-language site — locales are keys, translated strings are values ({"en": "Hello", "ru": "Привет"}).

There're some rare cases when I want to ensure that each translation is unique per locale. I know that I can create an index for each locale:

CREATE UNIQUE INDEX ON categories ((title ->> 'en'));
CREATE UNIQUE INDEX ON categories ((title ->> 'ru'));

This solution has one downside that I would need to create a new index for every column which has such constraint if I decide to add a new locale. Is there any way to specify a uniqueness constraint that will hold for every key-value pair of a JSONB object?

EDIT 1 (in response to the Evan's answer). There seems to be some sort of confusion cased by the term "unique/duplicate translation per locale", so I will try to clarify it. "Unique translation per locale" means: for every locale of each record in the table, there is no other record that has exactly the same translation for that locale. "Locale" is the key of the JSONB object that holds translations. "Translation" is the value of the JSONB object.

Here are some examples: the code

INSERT INTO categories(title) VALUES
  ('{"en": "Hello"}'),
  ('{"en": "Hello"}');

should yield a uniqueness constraint violation error because it has duplicate "Hello" translation for the en locale, while the code

INSERT INTO categories(title) VALUES
  ('{"en": "Hello"}'),
  ('{"en": "Hi"}');

should not, because it has different translations for the en locale.

Failed attempt with Exclusion Constraints

I don't believe this is possible to do this with exclusion DDL because I don't believe PostgreSQL supports exclusion constraints on text[]

CREATE TABLE categories(title)
AS VALUES
  ('{"en": "Hello", "ru": "Привет"}'::jsonb);

Helper function

CREATE FUNCTION jsonb_object_keys_as_array(j JSONB)
RETURNS text[]
AS $$
  SELECT ARRAY( SELECT jsonb_object_keys(j) AS d );
$$
LANGUAGE sql
IMMUTABLE;

Exclusion constraint attempt.

ALTER TABLE categories
  ADD EXCLUDE
  USING gist (jsonb_object_keys_as_array(title) WITH &&);

ERROR:  data type text[] has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

I'm asking a more specific question on this right now.

Other solutions

As it pertains to your problem, I believe currently you'll have to resort to a less than optimal solution like,

Implement this with triggers.
Create a function that may or may not use a table or enum to resolve text -> int and then map over text[] returning int[] and use intarray which will permit a GIST index and &&

Or, of course, you can just fix your schema.

Trigger

This method should also be able to make use of a GIST index on catagories(title)

CREATE OR REPLACE FUNCTION resist_duplicate_languages()
RETURNS TRIGGER
AS $$
  DECLARE
    collision bool DEFAULT false;
  BEGIN
    EXECUTE
      FORMAT('SELECT true FROM %I.%I WHERE ctid <> $1 AND title ?| jsonb_object_keys_as_array($2) LIMIT 1', TG_TABLE_SCHEMA, TG_TABLE_NAME )
      INTO collision
      USING NEW.ctid, NEW.title;

    IF collision IS true THEN
      RAISE EXCEPTION 'Can not collide on language %.%(title) languages %', TG_TABLE_SCHEMA, TG_TABLE_NAME, jsonb_object_keys_as_array(NEW.title);
    END IF;

    RETURN null;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER resist_duplicate_languages
  AFTER INSERT OR UPDATE OF title
  ON categories
  FOR EACH ROW
  EXECUTE PROCEDURE resist_duplicate_languages();

Testing

//works
INSERT INTO categories VALUES ('{"fr":"We surrender and eat cheese"}');

//does not work a second time
INSERT INTO categories VALUES ('{"fr":"We surrender and eat cheese"}');
ERROR:  Can not collide on language public.categories(title) languages {fr}


Is it possible to see blocker's host name or machine name using sp_WhoIsActive? I am using sp_WhoIsActive.

When blocking is occurred on a server I want to quickly identify who is the culprit.

Column login_name does not give me much info about blocker because there are hundreds people using company's app and logged in under same login_name.

I am able to see blocking_session_id but this does not tell me what is the blocker's host_name.

There is also a column host_name but this is the one who is a waiter (not blocker)

So is any way I can quickly find the host_name, machine name or any other identity of the user who is the blocker?

Yes, you simply have to correlate the blocking_session_id to the session_id of the blocker.  In the following output, you see that session 68 is blocked by session 66, so you simply have to find the data for session 66.

To find block leaders most quickly, be sure to use the following parameters so that the session blocking the most other sessions will be at the top:

@find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC'

Sample output will be as follows:

sp_whoisactive
      @output_column_list='[session_id][block%][host_name][login_name]'
    , @find_block_leaders = 1
    , @sort_order = '[blocked_session_count] DESC'

session_id    blocking_session_id    blocked_session_count    host_name      login_name
66            NULL                    1                       host1          user1
68            66                      0                       host2          user2

If all of the sessions are coming from an application server, which is quite common, then the host_name is simply going to be that application server and there is no way from the SQL Server side to see what actual client computer is associated with that connection from the application server.  If you want to track something like that down you would need to start logging relevant information at the application server, or, if you can figure it out by looking at the queries, use extended events or traces to see what queries are being run in that session.  No easy way about it unless the application already has the plumbing to turn on some associated logging.

See sp_whoisactive: Leader of the Block

Also of note is that the SQL client does not always set the host_name property, so if it's blank, it's simply because the application configured the SQL connection to omit that information.


get table does not exist in mysql after adding new hard disk I have added a new hard disk to my VM where my mysql server were located on. Suddenly, mysql returns table does not exist for all my databases; even though I can see all tables with show tables command.

mysql> use mydb;
mysql> show tables;
+-----------------------+
| Tables_in_mydb        |
+-----------------------+
| mytable               |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from mytable;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

mysql> select * from mydb.mytable;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

I have tried to restore tables with solutions provided here:

Using mysqlcheck --repair:

root@mylab:/var/lib/mysql# mysqlcheck -umyuser -pmypass --repair --
databases mydb
mydb.mytable
Error    : Table 'mydb.mytable' doesn't exist
status   : Operation failed

Using REPAIR TABLE:

mysql> use mydb;
mysql> REPAIR TABLE mytable;
+---------------------------+--------+----------+-------------------------------------------------+
| Table                     | Op     | Msg_type | Msg_text                                        |
+---------------------------+--------+----------+-------------------------------------------------+
| mydb.mytable              | repair | Error    | Table 'mydb.mytable' doesn't exist              |
| mydb.mytable              | repair | status   | Operation failed                                |
+---------------------------+--------+----------+-------------------------------------------------+
2 rows in set (0.00 sec)

Using ALTER TABLE t1 ENGINE = InnoDB;:

mysql> use mydb;
mysql> ALTER TABLE mytable ENGINE = InnoDB;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

Using mysqldump:

root@mylab:/var/lib/mysql# mysqldump -umyuser -pmypass mydb > /root/mydb.sql
mysqldump: Got error: 1146: Table 'mydb.mytable' doesn't exist when using LOCK TABLES

I have tried repairing with these solutions. Since mysqlcheck had failed before, I tried myisamchk for all files starting with mytable in /var/lib/mysql/mydb/; the command output was is not a MyISAM-table for all of them.

EDIT 1:

I checked database management files in /var/lib/mysql/ which is set as datadir in /etc/mysql/my.cnf; all of them were listed there:

root@lab-188:/var/lib/mysql# ls -lat | grep ib
-rw-rw----  1 mysql mysql  5242880 May 24 12:22 ib_logfile0
-rw-rw----  1 mysql mysql 18874368 May 24 12:22 ibdata1
-rw-rw----  1 mysql mysql  5242880 May 24 11:08 ib_logfile1

EDIT 2:

I also tried following solutions:

Restart mysql service:

Still get the same error.

Fix mysql directory's permissions:

chmod -R 755 /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/

Nothing changed.

Try loading database files in another server:

I copied the whole directory of database, /var/lib/mysql/mydb/, to another server which has mysql too, restarted mysql there and logged in to it.

The database was shown there, but selecting content of its tables returned same error as previous server:

mysql> select * from mytable;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

EDIT 3:

Another solution which I found here, was to disble/enable FOREIGN_KEY_CHECKS which didn't help in my case:

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

You can't move InnoDB tables around by moving files.  There is critical info in ibdata1 that you lose by simply moving /var/lib/mysql/mydb/.

Nor does it work for a database, such as /var/lib/mysql/mydb/.


How do I update last row of a group in MySQL I have a table like this:

+----------------------------+
| user  | date     | current |
+----------------------------+
|    1  | 20010101 |       1 |
|    1  | 20020202 |       1 |
|    1  | 20030303 |       1 |
|    2  | 20010101 |       1 |
|    2  | 20020202 |       1 |
+----------------------------+

I would like to update the current column such that only each user's most recent entry has a 1.

I had planned:

UPDATE table t1 SET current = 0
WHERE EXISTS (
    SELECT id
      FROM table t2
     WHERE t2.user = t1.user
       AND t2.date > t1.date )
)

But this tells me ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

Is there a way around this limitation of MySQL?

Yes, there is a workaround, using JOIN to a derived table:

UPDATE tableX AS t
  JOIN
    ( SELECT user, MAX(date) AS date
      FROM tableX
      GROUP BY user
    ) AS c
    ON  t.user = c.user
    AND t.date < c.date
SET
    t.current = 0 ;

If (user, date) is not enough to guarantee uniqueness and there is some other id column that is either unique or primary key (or the (user, date, id) is unique, that's enough), you can do this to keep only one row per user with current=1:

UPDATE
    ( SELECT DISTINCT user_id
      FROM tableX
    ) AS d
  JOIN
    tableX AS t
    ON  t.user_id = d.user_id
    AND (t.date, t.id) <>
        ( SELECT i.date, i.id
          FROM tableX AS i
          WHERE i.user_id = d.user_id
          ORDER BY i.date DESC, i.id DESC
            LIMIT 1
        )
SET
    t.current = 0 ;


Possibility to implement time to execute / TempDB usage restrictions on some role groups in SQL Server? I have a SQL Environment of which I am sysadmin. This Environment includes the Company data warehouse. I conduct many different jobs from this Environment, and it is also Another sysadmin which has access to it.

Seeing that it contains the data warehouse, some users from the production team has access to it as well.

The underlying raw data is very spread out. The data comes from many different sources, which sometimes requires excessive use of OUTER JOINs, Temp Tables, Procedures etc. in order to fetch the data of interest.

I have seen that some users in the production team has sometimes done massive queries, resulting in TempDB growing uncontrollably by 50+ GB, eventually resulting in the server shutting down (thus far, we have the SQL servers and TempDB on the same drive).

Is there a way to prohibit the production team of using too big queries, while not putting the same restrictions on the sysadmins, as there is sometimes neccessary to conduct TempDB extensive queries given the complex raw data structure?

I do not want to hinder the production team of using/creating Stored procedures, Temp Tables etc. I just want to investigate if there is an option to restrict execution time / TempDB usage.

You cannot limit some users for use the certain amount of tempdb only, instead, you can put the limit to it's max size.

Anyway, putting tempdb at the same disk with your system files is a bad idea.

You can monitor who is consuming your tempdb making smth like this:

    create view [dbo].[vw_tempdb_usage]
    as
    select session_id,
           cast(sum(internal_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_alloc_Gb,
           cast(sum(internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_dealloc_Gb,
           cast(sum(user_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_alloc_Gb,
           cast(sum(user_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_dealloc_Gb,
           cast(sum(internal_objects_alloc_page_count -
                    internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_diff_Gb,
           cast(sum(user_objects_alloc_page_count -
                    user_objects_dealloc_page_count)* 8. /1024 /1024 as decimal(10,2)) as user_objects_diff_Gb
    from sys.dm_db_task_space_usage
    group by session_id
    having sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count +
          user_objects_alloc_page_count - user_objects_dealloc_page_count) /1024 > 0;

go

    CREATE TABLE [dbo].[tempdb_details](
        [dt] [datetime] NOT NULL,
        [spid] [smallint] NOT NULL,
        [db] [nvarchar](128) NULL,
        [loginame] [nchar](128) NOT NULL,
        [hostname] [nchar](128) NOT NULL,
        [program_name] [nchar](128) NOT NULL,
        [login_time] [datetime] NOT NULL,
        [internal_objects_diff_Gb] [decimal](10, 2) NULL,
        [user_objects_diff_Gb] [decimal](10, 2) NULL,
        [cmd] [nchar](16) NOT NULL,
        [txt] [nvarchar](max) NULL,
        [Statement] [nvarchar](max) NULL,
     CONSTRAINT [PK_tempdb_details_dt_spid] PRIMARY KEY CLUSTERED
    (
        [dt] ASC,
        [spid] ASC
    ));

go

    insert into dbo.tempdb_details
               (dt, spid, db, loginame, hostname, program_name,
                login_time, internal_objects_diff_Gb,
                user_objects_diff_Gb, cmd, txt, Statement)
    select GETDATE() as dt,
           p.spid,
           db_name(p.dbid) as db,
           p.loginame,
           p.hostname,
           p.program_name,
           p.login_time,
           v.internal_objects_diff_Gb,
           v.user_objects_diff_Gb,
           p.cmd,
           txt.text as txt,
           CASE WHEN encrypted = 1 THEN '-- ENCRYPTED'
                   WHEN p.stmt_start >= 0
                   THEN substring(txt.text, stmt_start/2 + 1,
                                  CASE stmt_end
                                       WHEN -1 THEN datalength(txt.text)
                                       ELSE stmt_end/2 - stmt_start/2 + 1
                                  END)
              END AS Statement
    from dbo.vw_tempdb_usage v join sys.dm_exec_sessions s
            on v.session_id = s.session_id
         join sys.sysprocesses p
            on v.session_id = p.spid
         cross apply sys.dm_exec_sql_text(p.sql_handle)  txt
    where v.internal_objects_diff_Gb > 1 or v.user_objects_diff_Gb > 1;

Here I created a view using sys.dm_db_task_space_usage that shows me in real time who is using more than 1Gb of tempdb now.
Then I put the query that reads from this view in a job running every 5 minutes,
so it captures every query along with it's owner if it consumes more than 1Gb. Then it's up to you how you want to react to it. My actions are simple: tempdb has a limited size (100Gb) so it's impossible to overflow it. Every violator is just rolling back receiving smth like this:

  Could not allocate space for object 'dbo.Large Object Storage System
  object:  422539438653440' in database 'tempdb' because the 'PRIMARY'
  filegroup is full. Create disk space by deleting unneeded files,
  dropping objects in the filegroup, adding additional files to the
  filegroup, or setting autogrowth on for existing files in the
  filegroup.  error.

At the same time every attempt to overflow it is captured by my job. So I can easily see who it was and what he was doing, and take actions.


novice-design: Are FOREIGN KEYs always necessary? Joins on columns which are not PK I've been wondering if following approach is bad or really very bad:).

Question#1: can we use indicator_id to combine data from indicator_values and indicator_details tables (without using FOREIGN KEY) [we know we can, because we do this, but is it acceptable:)]?

Question#2: if we decide to use indicator_id (which in indicator_details table is not PK) would it have an important impact on performance?

Understanding SQL approach correctly, the best option would be to use iid (iid as FOREIGN KEY) in indicator_values table instead of indicator_id, but as always project has some limitation (like the way how users insert data - more below), thus we trying to find a compromise solution.

I aware of that this could cause data integrity issues, but solution without FK would be much user-friendly (since users don't have to worry about inserting into indicator_values table correct rows - rows with correct iid), especially that in our case data integrity is not crucial. Moreover if the only way (in our case) of inserting data by users is that is
preceded by removing all rows from table, approach with FKs would cause deleting all rows (caused by ON DELETE ...) from indicator_values during indicator_details table update (again - update means: remove all rows then insert new data), so this would be time consuming.

DB:

indicator_values table

+----------+--------------+------+-------+
| vid (PK) | indicator_id | year | value |
+----------+--------------+------+-------+
|     1    | AACA         | 2001 |    10 |
|     2    | bbb          | 2001 |   100 |
|     3    | ccc          | 2001 |    10 |
|     4    | AACA         | 2002 |    20 |
|     5    | bbb          | 2002 |   200 |
|     6    | ccc          | 2002 |   300 |

indicator_details table

+----------+--------------+------------------+-----------------------------+
| iid (PK) | indicator_id |     desc         |          full_text          |
+----------+--------------+------------------+-----------------------------+
|  1       | AACA         | AACA is super    | Lorem ipsum dolor sit amet  |
|  2       | bbb          | bbb is extra     | Maecenas sagittis ultricies |
|  3       | ccc          | ccc is superb    | Praesent tincidunt nec orci |

There is absolutely nothing wrong with joining on columns that are not PKs/FKs. If you are concerned about efficiency then the key is to have appropriate indexes defined to support the join operations you are using. Also, don't assume that the existence of a foreign key implies the existence of an index - some databases automatically create such an index but many do not as it is not required and needlessly takes up space if it would never actually help with the queries you run.

Primary and foreign keys (and unique constrains and other database defined limits) exist for data integrity purposes.

Regarding FKs with ON DELETE CASCADE and people deleting parent rows: it is safer to no define ON DELETE CASCADE and let it error when people try rather than not defining FKs and other data integrity controls due to fear of human error because that is just saving yourself a nasty headache for later. Rows should not be updated by delete-then-reinsert, which is the behaviour that causes this concern, people should instead either perform a check-then-insert-or-update-as-needed or some form of UPSERT operation if the database supports on (MERGE in some like MS SQL Server, INSERT ... ON CONFLICT ... or similar in others, and so forth).


INSERT and DELETE in single operation (moving from one table to other) Stemming from this question, I am going to use that complex sort's results to decide which rows' data to move from one table to another. Though I have not yet tried it, I think there should be no problem to do the "copy" part via a:

INSERT * FROM (...)

Using my results from that SELECT inside the parentheses.

However, if I then user the same query to try to SELECT the same results in order to do a DELETE FROM, the results will no longer include those same rows. So, I need some way to store at least the primary keys (and origin table, since we are talking about 2 origin tables and one destination table) in order to DELETE the correct row from the correct table.

From what I have read and understood, it's not possible to do in a single query, and in any case would require a transaction (which I need to lookup how to correctly do).

So my first approach would be to do the INSERT copy first, and then maybe delete form the two tables any rows containing primary keys appearing in my destination table. It seems a little inefficient since that table would be much larger than the two origin tables, and I figure that from the derived table I got from my subquery, it would be much much more efficient to use the info located there. So, any clues as to how I could best go about this?

Insert everything into a temp table first. Then your insert and delete are simply everything from the temp table rather than your complicated query. This solves your problem of how to do both halves and is probably going to perform better than running your complicated query multiple times.

I do more MS-SQL than My-SQL so I'll link to another answer on Stack Overflow.

https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table

On MS-SQL it is simply

  SELECT .... into #MyTempTable FROM ... WHERE..


System Memory (RAM) for Windows Server 2012 Standard with SQL Server 2012 We are using Windows server 2012 standard server with 2.4 GHz and 16 GB RAM. SQL Server 2012 is installed in it and also our primary application is installed on the server.

Using transaction replication to get the latest data b/w the servers.

The server performance hits badly sometimes when SQL Server holding the entire memory of the server.

Will the performance of the server improve when increasing the RAM to 32 GB?

All of this assumes you're running 64 bit Windows and 64 bit SQL Server...

SQL Server is greedy when it comes to memory usage, and that is by design. It keeps as much data as it can in RAM because it is faster than having to access disk to fulfill a query or other request.

As a result, you may want to set the minimum memory allocation for a SQL Server instance so it doesn't suffer from what I call memory starvation. If this happens, the server's drives will thrash because database usage will require disk accesses.

You'll also want to set the maximum memory allocation as already mentioned. In general, I give 10% to the operating system and the rest is for SQL Server (I have dedicated SQL servers each with 32GB RAM so it's fairly straightforward).

This article from Brent Ozar's website has more details: https://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

I noticed you said sometimes the performance suffers. Do you have other applications collocated on this server? Are there SQL Agent tasks scheduled to run at certain times? Backups can take a lot of resources and other applications sharing resources could end up fighting over them with SQL Server. In that case, you may need to add more memory and carefully tune how it's used, or move the application to another server.


Migrating Oracle smallfile to bigfile tablespace I am wanting to migrate all tablespaces in an existing db to use bigfile tablespace rather than the smallfile tablespace. The Oracle documentation hints that this is possible but doesn't explicitly show how to do it.

Any help/pointers would be greatly appreciated.

You have a few options.

Create your new bigfile tablespaces, then either:

1) Move the tables one by one with:

alter table mytable move tablespace bigfile_tablespace;

Remember to move indexes too!

alter index myindex rebuild tablespace bigfile_index_tablespace;

2) Export the database with Data Pump, drop the existing objects, then reimport using a remap_tablespace clause, for example:

impdp remap_tablespace=OLDSMALLTS1:NEWBIGTS1,OLDSMALLTS2:NEWBIGTS2 directory=mydir dumpfile=mydumpfile.dmp logfile=mylogfile.log

3) Use DBMS_REDEFINITION to move the objects. An example of this can be found in my answer to another question, here.

Once done, drop all of the old objects and then the tablespaces.

If you can afford the downtime, datapump will be the easiest option.


oracle format specifiers: to_number vs to_char SQL> select TO_NUMBER(123.56,'999.9') from dual;
select TO_NUMBER(123.56,'999.9') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

SQL> select TO_CHAR(123.56,'999.9') from dual;

TO_CHA
------
 123.6

SQL>

I am having a hard time in understanding the nuances of Oracle SQL. For example, have a look at the two queries above.

Why does first query fail but second one succeed?

According to the documentation, the first value for a to_number needs to be

  a BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or
  NVARCHAR2 data type containing a number in the format specified by the
  optional format model

Therefore Oracle is implicitly converting 123.56 into a character value.  The problem then comes that the format for the to_number does not match the value being given.  This can be seen because you get the same error for the following two variations:

select to_number(to_char(123.56),'999.9') from dual;

select to_number('123.56','999.9') from dual;

But you don't for this:

select to_number(123.56,'999.99') from dual;

When converting characters to numbers the format specification must match exactly, but when converting numbers to characters the number can be selectively extracted using the format specifier.