How does SSMS display varbinary data? When you store a .jpg file in a varbinary(max) column and then view the data in SQL Server Management Studio, it appears like "0xFFD8FFE000..."
Is this Hex Encoded or something?  What is SQL Server doing to generate this string?  Can it be converted to a valid JPG file?

It's binary data with some additional Hex characters for padding, etc per the Microsoft Docs: binary and varbinary

Padding is achieved by using hexadecimal zeros.

It appears that SSMS is converting the binary data to VARCHAR before displaying it.


Non-sequential Primary Key performance in PostgreSQL 9.3 Due to requirement to support distributed environment, each of my clients has been assigned unique node id, it appends the node id with running number as primary key and stores at local database. E.g.

  Client A node id = 200, first row in a table will have primary key
  200,000

  Client B node id = 100, first row in a table will have primary key
  100,000

These records then replicate to centralized database. Since primary key at centralized database is not in sequence, will it cause any serious performance issue when data size getting bigger?

Possible sequence of inserting new data at centralized database:

200,000
100,000
100,001
200,001
300,000
100,002

This may cause a big performance in SQL Server with table is clustered along the PK. However, will this happens in PostgreSQL 9.3?

Notes:

I can't use composite keys as it does not play well at my presentation layers.
The 3 digits running number is just a simplified example, real
running number will be much bigger and sufficient.

I have 3 solutions for you:
Direct reference of sequence and using concat
One possible solution is to reference the seqence in insert statement directly and prepend your node-id. A similar question including answer you can find here:
https://stackoverflow.com/a/17925601/4206293
Using a UUID
Another possible solution is, if you don't need you node-id in the primary-key field, you can use the uuid-ossp extension which provides the type uuid and the functions to generate uuids: http://www.postgresql.org/docs/9.4/static/uuid-ossp.html
Use a trigger for these solutions
For both solutions: you can use a trigger to set the primary key.
Example:
-- table
CREATE TABLE test(
   id character varying(10) NOT NULL,
  "name" character varying,
   CONSTRAINT idx_pk PRIMARY KEY (id)
);

-- seqence
CREATE SEQUENCE test_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Create Function
CREATE OR REPLACE FUNCTION insert_trigger()
RETURNS TRIGGER
AS $$
BEGIN
   NEW.id := '100' || nextval('test_seq')::TEXT;
   RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- add Trigger
CREATE TRIGGER insert_table_trigger
BEFORE INSERT ON pkTable
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

If you now insert a value in the table
INSERT INTO test (name) VALUES ('test text');

you get your primary key '1001'
Using MINVALUE and MAXVALUE
Another way is you use the MINVALUE and MAXVALUE of SEQUENCE to define an numeric space:
CREATE SEQUENCE node100_seq
  INCREMENT 1
  MINVALUE 100000000000
  MAXVALUE 100999999999
  START 100000000000
  CACHE 1;

CREATE SEQUENCE node200_seq
  INCREMENT 1
  MINVALUE 200000000000
  MAXVALUE 200999999999
  START 200000000000
  CACHE 1;

You can  create a sequence and make it global. Like below:
  create sequence seq;

Create a view on this sequence:
  CREATE VIEW seq_view AS SELECT nextval(‘seq’) as a;

Now, We will be using a foreign data wrapper to access the sequence:
  CREATE EXTENSION postgres_fdw;
  CREATE SERVER global_seq FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host
 ‘192.168.XX.XX’, port ‘5432’, dbname ‘mydatabase’);
  CREATE USER MAPPING FOR PUBLIC SERVER global_seq OPTIONS (user ‘User’ ,
  password ‘xxxx’);
  CREATE FOREIGN TABLE seqtable (a bigint) SERVER global_seq OPTIONS (
  table_name ‘seq_view’);

Now, you can create a function like below to access the sequence for each insert:
   CREATE OR REPLACE FUNCTION public.func()
   RETURNS bigint
   LANGUAGE plpgsql
   SECURITY DEFINER
   AS $function$
   declare b bigint;
   BEGIN
   b := (select * from seqtable);
   RETURN b;
   end;
   $function$;

Now, attach the function with table:
   Create table mytab(
   col1 int8 not null default func(),
   col2 int2
   );

Same steps you can follow with other table and use the common sequence with them.
For detailed and more information refer by post: https://medium.com/@shishulkargini/postgres-make-your-sequence-global-a80924a81e29


Oracle ASM vs RAID 10 I have oracle ASM 11gR2 on oracle linux 5.8 and i want to install oracle database.
I have some disks and i want to use the redundancy strategies of oracle ASM or RAID 10 .
I do not know which one is better ? Oracle ASM redundancy or RAID 10 ?

If you can afford RAID, then go for it.. RAID or no RAID depends.. if your hardware provides redundancy then go with ASM and mirror it as you wish.
Tom Kyte has brilliant insight towards this..try visiting his site as well.


Three questions about Database design I'm new to mysql,and I'm designing a  database for my social network app, I got some questions about it.

user_table

comment_table

1.Should I use unique key as much as I could in the database when the value of the field is unique?

2.Should I add some backup field in case I need to add other field in the future? If not,how to add other field in the future?

3.In the comment_table,I use user_id for foreign key,but I heard using too much foreign key will make the database slow,especially in social network.So,should I use comment_author_id instead,and if I need someone's
comment,I have to use

"select * from comment_table,user_table where comment_author_id = user_id"

Here is an graph I found on the internet,it use a lot of foreign key.Is it correct or out of date? Thanks for your time.

Generally, your top priority when designing a relational database is data integrity. Performance is a distant second at best. If you sacrifice data integrity, you may one day defending the charge, "This query returns the wrong answer!" with the rationale, "Yes, but it does so very quickly."

Quite lame as far as excuses go, yes?

However, the overhead of data integrity is small and localized. By localized, I mean that the overhead is generally on the DML (Insert, Update, Delete) operations. The queries will usually perform better.

1.Should I use unique key as much as I could in the database when the value of the field is unique? Absolutely. Always. Never fail. Defining a field as unique creates an index on the field. Odds are good that any unique field will be used heavily in the filtering criteria: where uniquefield = value. Now you have data integrity and fast access.

2.Should I add some backup field in case I need to add other field in the future? If not,how to add other field in the future? No. If you don't know enough to create a field, you don't know the data type, the uniqueness, the nullability, anything needed to properly define a field, including a proper name. It's easy enough to add a field to an existing table once you know all the particulars. There is no need to plan ahead on this.

3.In the comment_table, I use user_id for foreign key, but I heard using too [many] foreign key[s] will make the database slow, ... As I said before, data integrity constraints, such as foreign keys, can make DML a little slower, but foreign keys are completely irrelevant to queries. They are ignored, they play no role at all. So go ahead and define foreign keys, unique constraints and whatever other constraints make sense to keep a tight reign on data integrity.

It makes no sense at all to have excellent response time in a database filled with anomalous data. Fortunately, "response time" in most OLTP databases refers to query time. Proper data integrity checks will, at worst, have no effect on query response time. At best, they will actually make it better.

This is where good analysis comes in. In SQL tuning, you typically speed up query time by sacrificing DML time -- and vice versa. Typically, response time concerns for OLTP databases lean towards queries. However, there are instances where the DML is the primary concern. So which hits your tables most often: DML or queries? Generally, it's not even close -- like 100 to 1 in one direction or the other. If you can speed up the 100 side by moving a little overhead to the 1 side, so much the better.

But never sacrifice data integrity. It's never worth it.

First question:

You cannot be sure that user_name will be unique in 100%, so you must assume duplicates in that column. In that case unique cannot be applied.

Of course, you force unique fields whenever you can, because that makes space for later indexing, which will significantly increase performance.

For user_email field, it have sense to be unique.

Second question:

It is better practice to not add extra field for some future attributes and name it just as backup_field. Exclude those fields from your design, and if needed later, add it and change a code to cover those fields.

Third question:

Your current design for comments is right, only if you have solution that one comments can add two people, then create additional table with adequate foreign keys to author and comment


how to rebuild / reinstall ssrs (reportserver, reportservertempdb) databases? Our server crashed. We got it back up and running however, the mentioned databases have been corrupted.

Is there a programmatic / automatic way of rebuilding or reinstalling the SSRS databases?

If not:

Since we can still select * from the databases, how do I grab the permissions and roles on different reports?
How do we retrieve subscription information?

I’m not 100% sure but I think there is no supported way to rebuild these.

If you have backups of these databases then just restore them. If not you can try creating another empty database and then try to synchronize structure and data and see if that can help.

I’d suggest you use some third party tools for this.


Datetime conversion fails when filtered index is placed on partition column I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.

The index's filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error.
I've managed to reproduce it on a test table.

Does anyone have any ideas on why this happens?

use YOUR_DATABASE ;

go

select @@VERSION ;

-- Microsoft SQL Server 2016 (SP2-CU1) ...

-----------------------------------------------------------------------------

-- CREATE DATETIME PARTITIONED TABLE

set language english ;

drop table if exists dbo.test_of_filtered_idx ;

drop partition scheme ps_test_of_filtered_idx ;

drop partition function pf_test_of_filtered_idx ;

go

set language english ;

go

create partition function pf_test_of_filtered_idx (datetime)

   as range right

   for values ('1999-11-01 00:00:00.000' , '1999-12-01 00:00:00.000' , '2000-01-01 00:00:00.000' , '2000-02-01 00:00:00.000')

create partition scheme ps_test_of_filtered_idx

  as partition pf_test_of_filtered_idx

  ALL to ([primary])

create table dbo.test_of_filtered_idx

(

  id int not null identity (1,1) ,

  dt datetime not null ,

  payload char(127) not null default(replicate('A' , 127)) ,

  constraint PK__test_of_filtered_idx primary key clustered (id , dt)

) on [ps_test_of_filtered_idx] (dt) ;

go

-----------------------------------------------------------------------------

-- INSERT TEST ROW AND CREATE DATETIME FILTERED INDEX

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

go

set language english ;

go

create index IXF__test_of_filtered_idx__dt

  on dbo.test_of_filtered_idx

  (dt) include (id)

  where dt >= '1999-12-10 00:00:00.000'

    and dt < '2000-01-20 00:00:00.000' ;

go

-----------------------------------------------------------------------------

-- CHECK

set language english ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('12/15/1999 00:00:00.000' /* native american datetime format MDY */) ;

go

-- GET ERROR

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

-- Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.

-- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

go

-----------------------------------------------------------------------------

-- DROP INDEX AND GET ERRORFREE INSERTION

set language english ;

go

drop index IXF__test_of_filtered_idx__dt

  on dbo.test_of_filtered_idx

;

go

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

go

-----------------------------------------------------------------------------

The error comes from converting the string 2000-01-20 00:00:00.000 (in the filtered index definition)

Changing the filtered index to use unambigous datetime format works.

create index IXF__test_of_filtered_idx__dt
  on dbo.test_of_filtered_idx
  (dt) include (id)
  where dt >= '19991210 00:00:00.000'
    and dt < '20000120 00:00:00.000' ;

It definitely seems buggy to me  that the filtered index range is interpreted according to the settings of the client and so the same date can be either included or not included in the filtered index dependant on the settings of the client at insert time (as in below demo)

CREATE TABLE T
  (
     dt DATETIME
  )

CREATE INDEX IXF__test_of_filtered_idx__dt
  ON T (dt)
  WHERE dt >= '1999-01-06' AND dt < '1999-02-06';

SET LANGUAGE ENGLISH

GO

INSERT INTO T
VALUES     ( DATEADD(DAY,5,'1999-01-01')); --6th Jan

GO

SET LANGUAGE RUSSIAN

INSERT INTO T
VALUES     ( DATEADD(DAY,5,'1999-01-01')); --Still 6th Jan

GO

SET LANGUAGE ENGLISH

GO

SELECT *
FROM   T

Returns two rows - both with the same date

+-------------------------+
|           dt            |
+-------------------------+
| 1999-01-06 00:00:00.000 |
| 1999-01-06 00:00:00.000 |
+-------------------------+

But

SET LANGUAGE ENGLISH

GO

SELECT *
FROM   T
WHERE  dt >= '1999-01-06'
       AND dt < '1999-02-06';

Uses the filtered index and just returns one of them

+-------------------------+
|           dt            |
+-------------------------+
| 1999-01-06 00:00:00.000 |
+-------------------------+

Running DBCC CHECKTABLE (T) WITH EXTENDED_LOGICAL_CHECKS at this point then fails for either language.

Msg 8951, Level 16, State 1, Line 4
Table error: table 'T' (ID 1045578763). Data row does not have a matching index row in the index 'IXF__test_of_filtered_idx__dt' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 4
Data row (4:24:1) identified by (HEAP RID = (4:24:1)) with index values 'dt = '1999-01-06 00:00:00.000' and HEAP RID = (4:24:1)'.
DBCC results for 'T'.
There are 2 rows in 1 pages for object "T".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'T' (object ID 1045578763).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (tempdb.dbo.T).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In your filter condition you have varchar values so before working with datetime values they must be converted to datetime (Data type precedence). And this conversion depends on language settings.

That's why you get error when the command below is implicitly run:

set language russian ;
SELECT CONVERT(DATETIME, '2000-01-20 00:00:00.000');

To avoid this sort of errors explicitly use the datetime type in your filter expression or use an ISO 8601 date time format for your constants:

create index IXF__test_of_filtered_idx__dt
on dbo.test_of_filtered_idx
    (dt) include (id)
where   dt >= CONVERT(DATETIME, '1999-12-10 00:00:00.000', 120)
    and dt <  CONVERT(DATETIME, '2000-01-20 00:00:00.000', 120);

ADD:
Why isn't presented one value in filtered index from @MartinSmith's answer?

CREATE TABLE T
  (
     dt DATETIME
  )

CREATE INDEX IXF__test_of_filtered_idx__dt
  ON T (dt)
  WHERE dt >= '1999-01-06' AND dt < '1999-02-06';

SET LANGUAGE ENGLISH

GO

INSERT INTO T
VALUES     ( DATEADD(DAY,5,'1999-01-01')); --6th Jan

GO

Because after conversion from varchar to datetime:

'1999-01-06' = 1st JUN

'1999-02-06' = 2nd JUN


How arrange columns in sp_whoisactive by Adam Machanic I really like using sp_whoisactive v11.11 by Adam Machanic. But when then info is displayed, the DB name is at the very end. I would like it to in the very first columns so I don't have to scroll everytime... Does anyone have suggestions to arrange columns

Edit the line with @output_column_list declared on it and put [database_name] between [dd%] and [session_id].

When I scripted it out it was on line 110.

Change the code. Or ask Adam Machanic to change it.


postgres how to drop all the table in a schema through command I use postgresql and I need to drop all the tables which are present in the schema. How can I delete from command prompt.

You want to use the CASCADE option of DROP SCHEMA. From the documentation:

  CASCADE  - Automatically drop objects (tables, functions, etc.) that are
  contained in the schema, and in turn all objects that depend on those
  objects

BE CAREFUL - emphasis above mine.

Obviously you'll need to recreate the schema afterwards.

To just drop all tables in the current schema, you can use this script:

DO $$ DECLARE
    tabname RECORD;
BEGIN
    FOR tabname IN (SELECT tablename
                    FROM pg_tables
                    WHERE schemaname = current_schema())
LOOP
    EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(tabname.tablename) || ' CASCADE';
END LOOP;
END $$;

Change WHERE schemaname = current_schema() if you want to drop all of the tables in a different schema.

If you just want a really simple way to do this.. Heres a script I have used in the past

select 'drop table '||table_name||' cascade constraints;' from user_tables;

This will print out a series of drop commands for all tables in the schema. Spool the result of this query and execute it.

Likewise if you want to clear more than tables you can edit the following to suit your needs

select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')

Source:https://forums.oracle.com/forums/thread.jspa?threadID=614090


Connect to sql plus from command line using connection string Let's say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c.

So how do I need to connect correctly? I used many options like:

sqlplus x/y@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED)))'

sqlplus (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=b)))

Submitting these command lines usually gives me back error messages like:

  ORA-12560: TNS:protocol adapter error
  ORA-12514: TNS:listener does not currently know of service

Many thanks in advance!!

did you try

sqlplus username/password@host:port/service
sqlplus x/y@c:a/b

modern versions of sqlplus (version 11 or newer) understand this syntax and you don't need a tnsnames.ora file.

This works for me on Oracle 11G

sqlplus orcl_usr/orcl_usr_pass@host/sid


rename datafile when file does not exists I am trying to clone our production database in test virtual machine. Our production database works with ASM. I would like to use plain file system in test database.

I created database, services, pfile etc. I gave production database control files to test database.  Right now I am trying to rename datafiles in test database. I get following error.

 SQL> startup mount;
  .....
 SQL> alter database rename file '+NEWDATA/XX/XX01.dbf' to      'M:\oracle\datafiles\XX\XX01.dbf'; alter database rename file
 '+NEWDATA/XX/XX01.dbf' to 'M:\oracle\datafiles\XX\XX01.dbf';
* ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01141: error renaming data file 162 - new file
'M:\oracle\datafiles\XX\XX01.dbf' not found ORA-01110: data file 162:
'+NEWDATA/XX/XX01.dbf' ORA-27046: file size is not a multiple of
logical block size OSD-04012: file size mismatch O/S-Error: (OS 4) The
system cannot open the file.

My backup is normal rman backup pieces not backup as copy. I am trying to rename nonexistent files. After this rename process , I will try to restore database from rman backup. But this backup is not piece by piece copy.

You wrote:

  After this rename process , I will try to restore database from rman
  backup. But this backup is not piece by piece copy.

Then this is not the method for doing this. In order to use the ALTER DATABASE RENAME FILE, the file have to already exist at the new location. Instead of this, you can (and should) handle this in RMAN:

sqlplus / as sysdba
startup mount
exit

Which you have already done, then:

rman target /

And run the following:

run
{
  set newname for datafile '+NEWDATA/XX/XX01.dbf' to 'M:\oracle\datafiles\XX\XX01.dbf';
  set newname for datafile '+NEWDATA/XX/XX02.dbf' to 'M:\oracle\datafiles\XX\XX02.dbf';
  set newname for datafile '+NEWDATA/XX/XX03.dbf' to 'M:\oracle\datafiles\XX\XX03.dbf';
  # ..., list all datafiles this way
  # alternatively you can use the datafile number: set newname for datafile 1 to '.../...dbf';

  restore database;
  switch datafile all;
  recover database;
}

When you rename your data files, you have to satisfy following conditions normally.

Database in mount stage , OR Tablespace is offline
data file should exist

My condition satisfy first one. Database is in mount stage. But when I try to rename data files, it asks for file. I do not have correct file since I will try to restore it from backup. But oracle only asks for correct size in file (ORA-27046: file size is not a multiple of
logical block size). This file do not have to be correct file. Therefore create dummy file with correct size, multiple of block size. How to achieve this.

in windows use powershell

$datafile = "M:\oracle\datafiles\XX\XX01.dbf"
$f = new-object System.IO.FileStream $datafile, Create, ReadWrite
$f.SetLength(20MB)
$f.Close()

in windows fsutil

fsutil file createnew <filename> <length>
fsutil file createnew M:\oracle\datafiles\XX\XX01.dbf  20M

in Linux

fallocate -l 20M app/oracle/datafiles/XX/XX01.dbf

After creating file, you can use normal alter database rename command.

 alter database rename file '+NEWDATA/XX/XXX.dbf' to 'M:\oracle\datafiles\XX\XXX.dbf';


postgres partition trigger and checking for child table I found some sample code for creating a partition table.

Basically, setup the master table and partition on date field concatenated with the type of entity it is then, inherit from the master.

The first call of the trigger is fine, but subsequent ones fail because the 'if' condition to check for an existing child table yields false but upon trying to create the table fails for an already existing table.

I have tried 3 ways to check for a child tables existence and none yield correct answers.

These 2 statement demonstrate the contradiction:

SELECT count(*) FROM pg_class WHERE relname='DRIVER_2016_12_28';
count(*)
0

select count(*) from DRIVER_2016_12_28;
count(*)
1

Master table:

CREATE TABLE partition_test
(
  pk SERIAL PRIMARY KEY NOT NULL,
  id VARCHAR(36) NOT NULL,
  organizationid VARCHAR(36) NOT NULL,
  lat REAL NOT NULL,
  lon REAL NOT NULL,
  basetype VARCHAR(16) NOT NULL,
  name VARCHAR(64) NOT NULL,
  updatetimestamp TIMESTAMP NOT NULL
) WITHOUT OIDS;

trigger code:

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
BEGIN
  partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
  partition := NEW.baseType || '_' || partition_date;

  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
    EXECUTE 'CREATE TABLE ' || partition || ' (check (updatetimestamp = ''' || NEW.updatetimestamp || ''')) INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (id)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (organizationid)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (lat)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (lon)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (updatetimestamp)';
    RAISE NOTICE 'A partition has been created %',partition;
  END IF;
  EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER telemetry_partition_insert_trigger
BEFORE INSERT ON partition_test
FOR EACH ROW EXECUTE PROCEDURE telemetry_insert_trigger();

insert example:

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

first output

  [2016-12-28 12:15:12] [00000] A partition has been created
  DRIVER_2016_12_28 [2016-12-28 12:15:12] completed in 33ms

second:

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

  [2016-12-28 12:16:15] [42P07] ERROR: relation "driver_2016_12_28"
  already exists [2016-12-28 12:16:15] Where: SQL statement "CREATE
  TABLE DRIVER_2016_12_28 (check (updatetimestamp = '2016-12-28
  12:16:15.467012')) INHERITS (partition_test);" [2016-12-28 12:16:15]
  PL/pgSQL function telemetry_insert_trigger() line 11 at EXECUTE
  statement

The question: How can I effectively check for the existing of the child table so that I don't get this error? If you'd like to point out existing problems with my trigger, that'd be great too

I would advise to use always quote_ident when generating dynamic sentences, to make sure your idents don't have upper/lowercase problems, or that you can use identifiers with spaces or special chars, and that you avoid SQL injection if your idents are parameters out of your control.

That is, your trigger function should look like:

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
  start_ts TIMESTAMP ;
  end_ts TIMESTAMP ;
BEGIN
  partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
  partition := NEW.baseType || '_' || partition_date;
  start_ts := partition_date::timestamp ;
  end_ts := start_ts + interval '1 day' ;

  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
    EXECUTE 'CREATE TABLE '    || quote_ident(partition)
        || ' (check (updatetimestamp >= ''' || start_ts || ''' '
        || '     AND updatetimestamp <  ''' || end_ts || ''')) '
        || ' INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (id)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (organizationid)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lat)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lon)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (updatetimestamp)';
    RAISE NOTICE 'A partition has been created %', partition;
  END IF;
  EXECUTE 'INSERT INTO ' || quote_ident(partition) || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Check a usage example for quote_literal on the PostgreSQL documentation.

As a matter of preference, I prefer to have the names of all tables lowercase and with no special chars (so that I don't have "Table Names with Double Quotes", but table_names_without_quotes), because I think the code is less noisy and more readable. But, again, this is a matter of taste.

As a side note, you're partitioning on date. A patch has already been committed for 9.7 that makes this a lot easier with declarative partitioning. You may consider building devel and waiting for a release in 2017.

From the docs..

CREATE TABLE measurement (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);


Why does data get corrupted in "large" text files - Reasons to use database I am just starting to learn about databases and using mysql.  I read that databases are more reliable than having large ascii text files and that ascii text files are more prone to corruption as they get bigger.

Can someone explain why this is the case and how databases safeguard against this in a high level?  Thank you.

I can only speak to MS SQL Server not MySQL I'm afraid.  SQL Server breaks up a large database into small pieces called PAGES that are 8k in size. There are a couple of advantages here.  With a large text file any time you make a chance you re-write the entire file.  With a SQL Server when you make changes you only write down the PAGES that have changed.  This means that instead of writing megabytes an gigabytes or even terabytes you are writing down information in 8K chunks.  Less writing means less chance of causing corruption.

So what happens once you have some corruption?  Well in a text file you won't realize that the corruption exists until you happen to be looking at the corrupted portion of the file or the file will no longer open.  This means that over time more and more corruption will build up over time.  In SQL Server each page has a CHECKSUM built in.  This CHECKSUM is checked under several conditions.  Every time that page is written, when backups are taken with the WITH CHECKSUM option and when DBCC CHECKDB is run.  As a side note DBCC CHECKDB checks for a number of different possibilities of corruption not just the CHECKSUM.  Because of all this if you (or your DBA) is careful they will find the corruption more quickly.

Which leads us to repairing the corruption.  In a text file if your network admin is taking proper backups then you can hopefully restore your file back to before the corruption.  If you caught the corruption early enough.  If you didn't there may not be a backup old enough.  Also your only option is to restore the entire file.  This means any changes made to your file since the corruption occurred will be lost.  Databases provide a number of restoration methods.  If you are taking regular backups you can of course just restore the database.  This isn't much different than your text file.  However at least in SQL Server there are also options to restore parts of a database even down to the PAGE level.  And in fact with certain types of high availability there is even the option of automatically fixing a page as soon as corruption occurs.

Here is some additional reading for you.

SQL Skills postings on page checksums.

Understanding Pages and Extents

DBCC CHECKDB and database integrity

Sorry this is MS SQL Server and not MySQL but I imagine some of the principals are the same or at least very similar.

This question seems too broad, but to address the one aspect you're mentioning -- file corruption, with text files you do not have random access to pieces of data within them. Every time you update a single character somewhere in the middle of a text file you essentially have to read the entire file and write a new, updated file. I guess this may increase chances of corrupting it.

However, databases offer you so many other benefits it's hard to decide where to start. As a matter of fact, database files are just as much (or may be just a little bit less) susceptible to corruption as any other files, by the forces outside the database control, such as disk bit rot, system crashes, etc. However, databases are typically instrumented to detect and recover from such corruption.


SQL Server 2014 complex Stored Procedure Sorry for the Title but I haven't found a better explanation.

I need to generate a TrackingID number for shipments using data from 3 different tables:

Sales
ShippingAddress
Carriers

I built a query that increments the appropriate trackingNumber and returns it together with some extra columns. (In the real query data are stored in a temp table and other queries follow, but now the issue is here.)

Here it is

DECLARE @OrderID NVARCHAR(10) = 'B3'

UPDATE cc
SET NextSeed= IIF(nextSeed IS NULL, NewStartSeed + 1,
                  IIF(nextSeed = LastSeed, NewStartSeed, NextSeed + 1)),
    LastSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NewStopSeed, LastSeed),
    NewStartSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NULL, NewStartSeed),
    NewStopSeed = IIF(NextSeed=LastSeed OR NextSeed IS NULL, NULL, NewStopSeed)
OUTPUT
    COALESCE(deleted.NextSeed, inserted.nextSeed-1) CurrentSeed,
    inserted.Prefix, inserted.Suffix,
    inserted.ShippingService, inserted.CarrierCode
FROM
    Sales s
JOIN
    ShippingAddress ss ON s.shippingAddressID = ss.ShippingAddressID
JOIN
    Carriers cc ON cc.ShippingService = s.ShippingService
                AND cc.carriercode = s.carriercode
LEFT JOIN
    SurchargeAreas ssa ON ss.PostalCode = ssa.PostalCode
WHERE
    s.OrderID = @OrderID

Now I have to add an extra table that contains, for the different carriers, the PostalCodes with Surcharge:

SurchargeAreas Table

What I need is:

if Customer's PostalCode is in SurchargeArea for the selected Carrier I need to change carrier/shippingService to default Carrier/shippingService to avoid extra shipping costs.

I added to the above query the following extra condition:

and ssa.postalcode is null

That detects if the customer's postalcode is in a surcharge area, but returns zero result, and therefore does not select the default shipping service, does not increment the Seed and does not return a result.

Here is the sqlfiddle with all tables and some sample data to better understand.

In the example, the PostalCode of OrderID "B4" is in a surcharge area, while B1, B2 and B3 are not.

Therefore setting

@OrderID = 'B1' or 'B2' or 'B3'

returns data, while

@OrderID = 'B4'

does not return anything, while I would expect something like

31001     RS    NL   postnlrm    postnl

since postnlrm has 'default' column set to 1

Can you suggest how to achieve what I'm looking for?

Thanks.

Because you're essentially asking for the condition of joins to be changed through a lookup ("if my postalcode is in a surcharge area, change my carrier code joins to this, otherwise this"), I would recommend that you split this update into two sets. Here's some pseudocode to help get you started:

DECLARE @OrderID nvarchar(10)='B4'
DECLARE @mytable TABLE (CurrentSeed, Prefix, Suffix, ShippingService, CarrierCode)

-- Update Surchargeable Areas with Defaults; this assumes only ONE default for a postal code
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN SurchargeAreas     ssa on  ss.PostalCode=ssa.PostalCode
JOIN dbo.Carriers           cc  on cc.carriercode=ssa.carriercode
      and cc.[Default] = 1  -- terrible practice using a SQL keyword as a column name, by the way!!
WHERE s.OrderID=@OrderID;

-- Update non-Surchargeable Areas
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN Carriers cc ON cc.ShippingService = s.ShippingService
       AND cc.carriercode = s.carriercode
LEFT JOIN SurchargeAreas        ssa on  ss.PostalCode=ssa.PostalCode
AND cc.[Default] = 0
WHERE s.OrderID=@OrderID
          AND ssa.ID IS NULL;

Cant you  COALESCE and return default courier  for the area, if courier is NULL or if a surcharge area from the surchargarea table before u join it?

I.e
Left join (select top 1 coaleacse blah from surchargeare) x
On x.postalcode=ss.postalcode.

Sorry if it is not clear, i am not at a desk


Using Triggers in SQL Server new to SQL, I'm looking for a way to setup a Trigger that will update Table B when a new row is inserted on Table A. For basic understanding here are some of the columns in my tables:

TABLE A:
+--------------+--------------+
|order_id (PK) |order_details |
+--------------+--------------+

Table B:
+-------------+--------------+-------------+
|item_id (PK) |item_details  |order_id (FK)|
+-------------+--------------+-------------+

I'm using a stored procedure like this one to insert data into Table A. Obviously this procedure doesn't work but I wanted to show where my problem starts and maybe there is a better way besides a Trigger. The problem has been getting the order_id to use within the second INSERT.

/*Parameters for the first Insert Statement*/
@customer_id INT,
@track_num NVARCHAR(50),
@pckg_num NUMERIC,

/*Parameters for the second Insert Statement*/
@manuf_name NVARCHAR(50),
@model_name NVARCHAR(50),
@qty NUMERIC,
@notes NVARCHAR(MAX)

/*Parameter I need for the second statement that doesn't exist until the first statement is committed*/
@order_id INT

AS

BEGIN
/*First Insert*/
INSERT INTO Orders
    (customer_id, track_num, pckg_num)
VALUES
    (@customer_id, @track_num, @pckg_num)
/*Second Insert*/
INSERT INTO Items
    (manuf, model, qty, notes, order_id)
VALUES
    (@manuf_name, @model_name, @qty, @notes, @order_id)

END

I'd like to fire the trigger within the same procedure that would then update Table B with item details passed to the procedure from my web app and including the PK (order_id) from the newly created row in Table A as FK in Table B.

I've tried doing this other ways with no luck, came across Triggers and thought it seemed like a good choice. If you have other suggestions about different methods, please feel free! :) Thanks in advance!

Two main ways, neither involving a trigger (generally it's better to avoid triggers if you can) -

1) Use an OUTPUT clause with the first INSERT statement to pick out the generated identity value:

DECLARE @NewOrder TABLE (order_id INT);

INSERT INTO Orders
    (customer_id, track_num, pckg_num)
OUTPUT
    inserted.order_id INTO @NewOrder
VALUES
    (@customer_id, @track_num, @pckg_num);

INSERT INTO Items
    (manuf_name, model_name, qty, notes, order_id)
SELECT
    @manuf_name, @model_name, @qty, @notes, order_id
FROM @NewOrder;

2) Rather than declaring Orders.order_id with the identity attribute, create and use a sequence object for it (requires SQL Server 2012 or later):

CREATE SEQUENCE OrderIDSeq AS INT
    START WITH 1;

CREATE TABLE Orders (
    order_id INT
        PRIMARY KEY
        DEFAULT (NEXT VALUE FOR OrderIDSeq),
    //... other columns as before
);

GO

CREATE OR ALTER PROCEDURE AddOrder(
    @customer_id INT, @track_num NVARCHAR(50), @pckg_num NUMERIC,
    @manuf_name NVARCHAR(50), @model_name NVARCHAR(50),
    @qty NUMERIC, @notes NVARCHAR(MAX)) AS
BEGIN
    DECLARE @order_id INT = NEXT VALUE FOR OrderIDSeq;

    INSERT INTO Orders
        (order_id, customer_id, track_num, pckg_num)
    VALUES
        (@order_id, @customer_id, @track_num, @pckg_num);

    INSERT INTO Items
        (order_id, manuf_name, model_name, qty, notes)
    VALUES
        (@order_id, @manuf_name, @model_name, @qty, @notes);
END;

"A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server." docs

This is why you do noot need to 'fire a trigger'. Its an event that happens because of something else, i.e. inserting a record in table A.


Why did PostgreSQL choose an elephant as it's logo? Why did PostgreSQL choose an elephant as it's logo? It wasn't clear from logo wiki.

UPDATE: Now wiki updated :) Thanks!

In the email thread postgreSQL and history of relational databases, Oleg Bartunov states that the logo idea probably originated from a PostgreSQL logo thread on the pgsql-hackers mailing list. Quote:

  [...]
  but if you want an animal-based logo, how about some sort of elephant?
  After all, as the Agatha Christie title read, elephants can remember ...

  David Yang


How do you document your databases? I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using.

How do you document your database? (SQL-Server)
What tool do you use?
Documentation Storage Format for database schema/meta-data?

Word documents
Excel spreadsheet
Plain Text

Documentation process or policies?

I am not talking about reverse engineering / document a existing database, but mainly on the documentation best practices while you develop your system/database.

I have been using extended properties since they are very flexible.  Most standard documentation tools can be driven off MS_Description, and then you can use your own with custom-built tools.

See this presentation: #41-Get a Lever and Pick Any Turtle: Lifting with Metadata

And this code: http://code.google.com/p/caderoux/wiki/LeversAndTurtles

For MySQL in particular, we always use MySQL Workbench. We create our database designs in the designer, then export it as a runnable SQL script. Applying all the changes in the design and then running the generated script ensures that the design and the actual database are perfectly in sync with each other, and that the documentation will not become outdated as easily.


How do I dump only the data for all tables using mysqldump I have a new database with schema and primary key, FK, index constraints created for all tables. How do I dump only the data from an existing database using mysqldump, so that I can import only the values alone and exclude everything else.

mysqldump --no-create-db --no-create-info  ...options...

See http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

Use this in putty.........

mysqldump -u username -p'password' $db_name > $db_backup.sql


Dynamic Parameters In Pivot I know the premise of a PIVOT() this syntax produces it perfectly for me:

Select *
FROM
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot
(
  SUM(Amt)
  ,for annual IN ([2014],[2015],[2016])
) p

However, what if I am using a stored procedure and passing a year1 and year2 to the pivot as variables?  This will not work as incorrect syntax, but could this be achieved?

Create Procedure dbo.PivotParams
As
Declare @year1 varchar(100), @year2 varchar(100)

Set @year1 = '2014'
Set @year2 = '2016'

Select *
FROM
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot
(
  SUM(Amt) for annual BETWEEN @year1 and @year2
) p

Also the output should use COALESCE to prevent any NULL values in any of the pivot columns.

Sure, you can generate the list of years in dynamic SQL, but I suggest passing them as numbers (just one less thing that can go wrong if people pass in bad data, and I'm sure you didn't really mean to hard-code the values inside the procedure, defeats the purpose):

DECLARE @year1 smallint, @year2 smallint

SET @year1 = 2014;
SET @year2 = 2016;

DECLARE @years nvarchar(max) = N'', @select nvarchar(max) = N'';

SELECT  @years += N',' + QUOTENAME(y), @select += N',
  ' + QUOTENAME(y) + N' = COALESCE(' + QUOTENAME(y) + N',0)'
FROM
(
  SELECT TOP (@year2-@year1+1)
    y = @year1 - 1 + ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects ORDER BY [object_id]
) AS y;

DECLARE @sql nvarchar(max) = N'Select field1' + @select + N'
FROM
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM dbo.Testing -- ALWAYS use schema prefix!
) x
pivot
(
  SUM(Amt) for annual IN (' + STUFF(@years, 1, 1, N'') + N')
) p;';

PRINT @sql;

-- EXEC sys.sp_executesql @sql;

Slightly related:

Script to create dynamic PIVOT queries in SQL Server

Here is a dynamic SQL pivot procedure I wrote. It allows you to pass in any table or any query (SELECT statement) containing values and columns that you want to pivot. It does use temporary tables, so the tempdb might grow a little.

/*
******************************************************************
**  Copyright c 2016 Kurt Keydel
******************************************************************
Object Name :       uspBuildPivotReport
Object Type :       Stored Procedure
Created By  :       KKeydel
Usage       :           REPORT

Description :
This procedure takes a table or query with expandable
columns and creates a crosstab or matrix report
with aggregate values (such as SUM, COUNT, MIN, MAX, etc.)

The utility of this report is that the actual number of columns
for aggregate values does not need to be known ahead of time.
This procedure will take all passed in pivot values and
construct the columns automatically.

Parameters  :
@Table_or`_Query:
        The name of the table or query containing the
    raw data to be pivoted. If passing in a query,
    it must be contained within single quotes. Depending
    on the version of SQL Server, a table name may not
    need to be bracketed by single quotes.

@CrosstabColumnSource:
        The name of the column containing the crosstab or
    dimensional names, e.g. months of the year, regions,
    or product types.

@AggregateOperator:
        The aggregate function to be used to calculate
    values under the crosstab columns, e.g. SUM,
    AVG, MIN, MAX, COUNT, etc.

@DataColumnName
        The column containing the values to be calculated.

@SaveToTableName(Optional)
    The name of an output table in which to
    insert the crosstab results.
*/

    create procedure dbo.uspBuildPivotReport (

     @Table_or_Query varchar(5000)
    ,@CrosstabColumnSource varchar(255)
    ,@AggregateOperator varchar(50)
    ,@DataColumnName varchar(100)
    ,@SaveToTableName varchar(255)=NULL
        )
    AS
    SET NOCOUNT ON
    BEGIN

    declare @sql varchar(max)
    declare @RowColumnList varchar(8000)
    declare @crosstabColumnAssignment varchar(max)
    declare @crosstabColumnArray varchar(8000)
    declare @FullColumnList varchar(8000)
    declare @switch int

    IF object_id('tempdb..##RawData') is not null drop table ##RawData

    if charindex('select',@Table_or_Query) > 0
        BEGIN
            select @sql = 'SELECT * INTO dbo.##RawData FROM (' + @Table_or_Query + ') t1'
            select @switch = 1
        END
        ELSE
        BEGIN
            select @sql = 'SELECT * INTO dbo.##RawData FROM ' + @Table_or_Query + ' t1'
            select @switch = 0
        END

    exec (@sql)

    select @RowColumnList = ''
    select @RowColumnList = @RowColumnList + COLUMN_NAME + ', '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = '##RawData'
    AND COLUMN_NAME != @CrosstabColumnSource
    AND COLUMN_NAME != @DataColumnName

    select @RowColumnList = REVERSE(SUBSTRING(REVERSE(@RowColumnList), CHARINDEX(',', REVERSE(@RowColumnList)) + 1,LEN(@RowColumnList)))

    select @FullColumnList = ''
    select @FullColumnList = @FullColumnList + COLUMN_NAME + ', '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = '##RawData'

    select @FullColumnList = REVERSE(SUBSTRING(REVERSE(@FullColumnList), CHARINDEX(',', REVERSE(@FullColumnList)) + 1,LEN(@FullColumnList)))

    select @crosstabColumnAssignment = ''
    select @sql = 'SELECT DISTINCT ' + @CrosstabColumnSource + '  from ##RawData order by ' + @CrosstabColumnSource

    create table #Columns (
     RowID int identity(1,1)
    ,Value varchar(100))

    insert #Columns(Value)
    exec(@sql)

    select @CrosstabColumnArray = ''
    select @CrosstabColumnArray = @CrosstabColumnArray + '[' + value + '], ' from #Columns
    select @CrosstabColumnArray = REVERSE(SUBSTRING(REVERSE(@CrosstabColumnArray), CHARINDEX(',', REVERSE(@CrosstabColumnArray)) + 1,LEN(@CrosstabColumnArray)))

    select @crosstabColumnAssignment = ''
    select @crosstabColumnAssignment = @crosstabColumnAssignment + ' ISNULL([' + Value + '],''0'') as ''' + value + ''', ' from #Columns
    select @crosstabColumnAssignment = REVERSE(SUBSTRING(REVERSE(@crosstabColumnAssignment), CHARINDEX(',', REVERSE(@crosstabColumnAssignment)) + 1,LEN(@crosstabColumnAssignment)))

    select @sql = 'SELECT ' + @RowColumnList + ', ' + @crosstabColumnAssignment + char(10) + char(13)

    if @switch = 1
    select @sql = @sql + 'FROM ' + char(10) + char(13) + '( SELECT ' + @FullColumnList + ' FROM (' + @Table_or_Query + ') t1) t2' + char(10) + char(13)
    else
    select @sql = @sql + 'FROM ' + char(10) + char(13) + '( SELECT ' + @FullColumnList + ' FROM ' + @Table_or_Query + ') t1' + char(10) + char(13)

    select @sql = @sql + 'PIVOT '  + char(10) + char(13)
    select @sql = @sql + '(' + char(10) + char(13)
    select @sql = @sql + @AggregateOperator + '(' + @DataColumnName + ') ' + char(10) + char(13)
    select @sql = @sql + 'FOR ' + @CrosstabColumnSource + ' IN ( ' + @crosstabColumnArray + ')) as PVT'

    declare @sql2 varchar(max)

    select @sql2 = 'SELECT * INTO ##Crosstab FROM (' + @sql + ') c'
    exec(@sql2)

            IF ISNULL(@SaveToTableName, '') != ''
            BEGIN

                if exists(select * from Sysobjects where xtype = 'U' and name = @SaveToTableName) select @SQL = 'drop table ' + @SaveToTableName
                exec (@SQL)
                select @SQL = 'SELECT DISTINCT * INTO ' + @SaveToTableName + ' FROM ##Crosstab'
                exec (@SQL)

            END
    select * from ##Crosstab

    drop table ##Crosstab
    drop table ##RawData
    END


Error on changing ownership of database from Files Page in database properties dialog box I've restored a database and created a new user(same user from which I've logged in) as db_owner. Now when I wanted to see database diagram got an error

  Database Diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use files page of Database Properties dialog box or the Alter Authorization statement to set the database owner to a valid login, then add the database support objects

Now when I tried to change ownership from Files page of Database Properties I am getting error as

  Set Owner failed for Database 'ABC' . (Microsoft.SqlServer.SMO)
  Additional Info: an exception occurred in SMO
  The proposed new database owner James-PC\James is mapped as user James in this database.
  Parameter name: James-PC\James"

Please help me fix the issue so that I may be able to see database diagrams on this computer after restoring the database backup from another system.

The database owner is the login in sys.server_principals that owns the database, as defined by its SID.  After a restore it may easily be the case that the login that was used to restore the database is not the login that was the previous owner of the database.  (This is even more likely to happen when the database if moved between servers.)

So, there could be three settings that you are having trouble with

The database owner_sid is not equal to the database's db_owner sid. You can compare this for database ABC by:

SELECT owner_sid FROM sys.databases where name = 'ABC';

SELECT sid from ABC.sys.database_principals WHERE name = 'dbo';
Next you are getting a message saying that your proposed database owner_sid also exists as a user in sys.database_principals.  We know this from the error message about mapping the proposed owner.

So your steps are:

 USE ABC;
 DROP USER James;
 ALTER AUTHORIZATION ON DATABASE::ABC TO [James-PC\James];

You dropped the user which is not needed, since you are making it the dbo.  Once that is out of the way, then your login can be made the owner of the database.

EDIT: You can also use SSMS to delete the James user from ABC database.  Then you could return to the Database properties File tab to set the database owner. (Sorry, I tend to think scripts.)

right click database
properties
select files
correct/set owner (to yourpc\you, or browse and select by clicking "...")

this worked for me in in SQL server management studio v17.1


SSIS - Excel destination does not allow characters more than 255 My SSIS package has a ado.net source, which returns columns with width more than 2000 characters,but those columns get truncated when redirected output to excel destination.

Any idea, how to resolve this problem..?

I am using excel 2007, as excel destination.

SSIS Excel Data Source:

  Error with output column “Comments” (5691) on output “Excel Source
  Output” (5596).

The column status returned was:

  “Text was truncated or one or more characters had no match in the
  target code page.”

Reason for this error:

The error is due to the lengthy data in the excel.
SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will written a logic to build the table's logic for the package. If your lengthy data in not in the 8 row then your ssis wont respond it.

Considering am having a column named "Name"
First 8 rows is of length <255
9th row is of length > 255.
While executing the package you will get the above error, its because your input value will be truncated and SSIS wont allow for that. In this case,
Two types of logics can be followed,

Option1 : Its tricky one, just copy the 9th row to the top (may be as 1 row).Now try to create your package using import/export wizard. Your ssis will create column with width nvarchar(Max) which will accept upto 2 GB.

Option2: Change the Preparation SQL task query, change the data type as varchar(Max) and modify the excel source ->advanced editor and change the output columns type and length and external column type and length.

Please read the blog

By changing destination to the Microsoft Excel 97-2003 and use the xls for file extension.


Configure MariaDB client with host-specific credentials? How can I configure my MariaDB client (mysql) so that I don't have to enter a password to connect, regardless of which host I am connecting to?

I can't just put a password in the [client] section of ~/.my.cnf because that would apply all hosts.

For example, I'd like to be able to connection with mysql -h myhost mydatabase, ie. without specifying username or password. Even better would be the ability to specify a "host alias" instead of the actual (awkwardly long) hostname.

It sounds like this is possible in MySQL with the mysql-config-editor but MariaDB does not have this feature.

In ~/.my.cnf make sections like:

[clientmy_host_name_1]

[clientmy_host_name_2]

Then make an alias (assuming Linux-like OS - depending on shell, you can perhaps put the alias in ~/.bashrc_local):

alias mysql="mysql --defaults-group-suffix=\`hostname --short\`"

Now you can connect without specifying the password to any of the hosts for which you have stored the credentials in ~/.my.cnf. (These would be my_host_name_1 and my_host_name_2 in my example above.)

I have used the mysql_config_editor years ago it works, very well. And it is an option for you. other options will be to create some alias as long as you are using linux/mac:
something like
alias mysqlserver1='mysql -ulalala -plalalapass -hserver1'


Databases: are there universal datatypes? While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in the future?  While switching, I would like to avoid changing datatypes of the columns that are NOT available in the new database to appropriate datatypes that are available in the new database.

In other words, are there any ANSI datatypes that will work in all the databases with the same size?  That is, similar to "ANSI SQL", are there anything like "ANSI SQL Data types"?

For the most basic datatypes, nearly all DBMS either support the ANSI data types directly or at least allow to specify them.

The following statement:

create table products
(
   product_id    integer not null primary key,
   product_name  varchar(100),
   price         decimal(10,2)
);

works on nearly every DBMS. The data types will however be translated to the DBMS-specific ones (e.g. integer will be changed to number in Oracle, or varchar to varchar2).

When it comes to more "advanced" datatypes like CLOB, BLOB, XML, Json, or geospatial types then this doesn't hold true any more (CLOB, BLOB is pretty common, but e.g. not understood by Postgres although you can easily create a domain or a user data type with that name to make it more compatible).

The basic data types as defined by the ANSI standard are:

CHARACTER
CHARACTER VARYING (or VARCHAR)
CHARACTER LARGE OBJECT
NCHAR
NCHAR VARYING
BINARY
BINARY VARYING
BINARY LARGE OBJECT
NUMERIC
DECIMAL
SMALLINT
INTEGER
BIGINT
FLOAT
REAL
DOUBLE PRECISION
BOOLEAN
DATE
TIME
TIMESTAMP
INTERVAL

Not all DBMS suppor all of them (MySQL doesn't have an interval data type, MySQL and SQL Server don't have a real boolean type, Oracle does not have a boolean type at all or a real DATE or TIME data type, Postgres doesn't have nchar - that list could go on and on). But the most common ones usually work quite well.

From my experience, the following ones work without (major) problems across different DBMS (although that is my experience - this is by far not a complete or definite list!)

VARCHAR
INTEGER
DECIMAL
DATE (with surprises: Oracle has it but includes a time)
TIMESTAMP (does something different than expected on SQL Server and MySQL)

If you stick with those, you are pretty much "universal", but anything else will give you some (unpleasant) surprises when you go from one DBMS to another.

There are some universal data types compatible across database systems as you can see here:

https://nils85.github.io/sql-compat-table/datatype.html
For example this SQL query works on all database systems:
CREATE TABLE test (
col1 INT PRIMARY KEY,
col2 SMALLINT,
col3 VARCHAR(126) NOT NULL,
col4 CHAR(255),
col5 DECIMAL(9,2),
col6 FLOAT(24),
col7 DOUBLE PRECISION)


When should I use a unique constraint instead of a unique index? When I want a column to have distinct values, I can either use a constraint

create table t1(
id int primary key,
code varchar(10) unique NULL
);
go

or I can use a unique index

create table t2(
id int primary key,
code varchar(10) NULL
);
go

create unique index I_t2 on t2(code);

Columns with unique constraints seem to be good candidates for unique indexes.

Are there any known reasons to use unique constraints and not to use unique indexes instead?

Under the hood a unique constraint is implemented the same way as a unique index - an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query.

So for a database that supports both features the choice of which to use will often come down to preferred style and consistency.

If you are planning to use the index as an index (i.e. your code may rely on searching/sorting/filtering on that field to be quick) I would explicitly use a unique index (and comment the source) rather than a constraint to make that clear - this way if the uniqueness requirement is changed in a later revision of the application you (or some other coder) will know to make sure a non-unique index is put in place of the unique one (just removing a unique constraint would remove the index completely). Also a specific index can be named in an index hint (i.e. WITH(INDEX(ix_index_name)), which I don't think is the case for the index created behind the scenes for managing uniqueness as you are unlikely to know its name.

Likewise if you are only needing to enforce uniqueness as a business rule rather than the field needing to be searched or used for sorting then I'd use the constraint, again to make the intended use more obvious when someone else looks at your table definition.

Note that if you use both a unique constraint and a unique index on the same field the database will not be bright enough to see the duplication, so you will end up with two indexes which will consume extra space and slow down row inserts/updates.

UNIQUE Constraint is preferred over UNIQUE Index. When the constraint is not unique you need to use a regular or non unique index. Constraint is also another type of index. Index is used for faster access.

Unique Indexes can have where clauses. For example, you can create indexes for every year based on the date column

WHERE Sale_Date BETWEEN '2012-01-01' AND '2012-12-31'


intermittent TNS-12533: TNS:illegal ADDRESS parameters I am running a windows XP (SP3) VM in virtualbox, on which I host Oracle XE.

It has been running fine like this for a couple of years, but recently I am seeing some strange problems.

My client software fails, and the log files say :
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

So I log on to the DB, and try running sqlplus / as sysdba;

which gives me the following:
ora-12560 tns protocol adapter error

So following the debugging steps from several websites, I try this:
tnsping db
and the result is :
TNS-12533: TNS:illegal ADDRESS parameters

So I decide to test another SID:
tnsping xe
which works fine.

As you can see from the tnsnames.ora file, those two SIDs are configured identically.

I've not touched that file in a couple of years and don't intend to do so now.

So I restart the windows machine, and now everything works.

Why did the error message indicate a problem with the file, when upon restart it doesn't complain?

Why would this suddenly stop working?

I checked the alert log and found this:

ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process

I've tried wiping some files from the hard disk (there was 1GB free space, which seems like plenty) but it hasn't worked.

Any ideas?

The contents of my tnsnames.ora file include:

XE =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = windows-virtual)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
  )
)

DB=
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = windows-virtual)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = DB)
   )
 )

Try any one or both

Try using IP for HOST
Also remove (SERVER = DEDICATED) row from tnsname.ora file.


Simple confusion with DATALENGTH() DATALENGTH() function returns the length of the data in BYTES.

The table is like this:

create table tb3
(
id int,
id2 varchar(100),
nome varchar(200)
)

| id      | id2   | nome  |   |
|---------|-------|-------|---|
| 1       | 1     | r     |   |
| 1       | 11    | r1    |   |
| 1       | 111   | r11   |   |
| 1       | 1111  | r111  |   |
| 1       | 11111 | r1111 |   |
| 11      | 11111 | r1111 |   |
| 1111111 | 11111 | r1111 |   |

but when i run it like this:

select DATALENGTH(id), DATALENGTH(id2), DATALENGTH(nome) from tb3

| (No column name) | (No column name) | (No column name) |   |
|------------------|------------------|------------------|---|
| 4                | 1                | 1                |   |
| 4                | 2                | 2                |   |
| 4                | 3                | 3                |   |
| 4                | 4                | 4                |   |
| 4                | 5                | 5                |   |
| 4                | 5                | 5                |   |
| 4                | 5                | 5                |

Why it says each varchar character is using only 1 byte?
If i'm not mistaken, a varchar uses 2 bytes + number of characters.

The 'DATALENGTH()' function returns the length of just the data, those extra two bytes are considered "meta-data" - information about the data and/or its storage.

You get the same value if you pass the function a string literal (DATALENGTH('12345') returns 5) - this wouldn't be the case if the function accounted for storage overheads as well as the string's content.

To account for storage overhead the DATALENGTH function would need to know or be able to derive where a value has come from, and the structure of that location, if the data is compressed, etc., and accounting for all that would make the function far less efficient.

kindly refer below articles to understand the concept of row storage and extra byte overhead :

https://www.sqlskills.com/blogs/paul/misconceptions-around-null-bitmap-size/

https://stackoverflow.com/questions/53608000/char-and-varchar-datatype-storage-at-page-level-in-sql?noredirect=1#comment94077524_53608000


How do I list all databases and tables using psql? I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool.

When I log in with psql --username=postgres, how do I list all databases and tables?

I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, so I know they should be listed.

Please note the following commands:

\list or \l: list all databases
\dt: list all tables in the current database using your search_path
\dt *.: list all tables in the current database regardless your search_path

You will never see tables in other databases, these tables aren't visible. You have to connect to the correct database to see its tables (and other objects).

To switch databases:

\connect database_name or \c database_name

See the manual about psql.

It is possible that you have inserted the tables into a schema that is not in your search path, or the default, ie, public and so the tables will not show up using \dt. If you use a schema called, say, data, you can fix this by running,

alter database <databasename> set search_path=data, public;

Exit and reenter psql and now \dt will show you the tables in schema data too.


Backup throws permission error I'm trying to backup SQL Server database to upload it to my database server. I am using Microsoft SQL Server Management Studio, Database -> Tasks -> Back Up...
And I got the following error:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

Any idea of what's happening?

You should make sure that for the database you're trying to back up, you have at least the db_backupoperator database role assigned to the user that's trying to run the backup.
That should be sufficient.

What is happening is simple - read the error message. The user you are connected to the database server with has not enough permissions.

Database backup in SQL Server requires quite some permission - you likely only got permissions assigned to the database "as needed" and that is not enough.


SQL Server split mdf into multiple files I have a database called example.mdf with a total size of 1GB which suffers from performance issues. I checked the allocated hardware and it is higher than required, I double checked the design and every thing looks normal, when I look at the .mdf files in their physical location (C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\example.mdf) I found that the database is split into multiple files example_1.mdf, example_2.mdf,  example_3.mdf, up to example_7.mdf.

I have another database file on the same SQL Server that has the same issue.

Why does this happen? Does this affect the performance? How can I prevent or stop SQL Server from splitting my .mdf files? Moreover I need to combine back the already split files.

Who ever created the database did this on purpose.  Assuming that all the database files are part of the same file group (the database properties will tell you this) then all you need to do is do a DBCC SHRINKFILE any use the EMPTYFILE parameter.

DBCC SHRINKFILE (Example_1, EMPTYFILE)
GO
ALTER DATABASE MyDatabase REMOVE FILE Example_1
GO

Do that for each of the files and all the data will be put back into a single file.  You can then delete the extra files.

Now odds are this isn't hurting your performance any, so you'll need to continue investigating why you are having performance problems.

Not sure if you can merge files in existing database but you can create new database and then use some of the third party database comparison tools to migrate data and structure to new database. Just be careful when migrating schema not to migrate the part that tells SQL Server to split files ;)


If database is compromised and downloaded, does it matter if user passwords are hashed? I'm currently implementing as much security into a web app as possible, but I can't stop thinking about the database itself. For all the security I implement, such as salting and hashing passwords in the users table, if hackers manage to download the database entirely (by getting access to the server for example), they'll be able to see all the data in the entire database?

I've been reading about all the database breaches of well-known brands within the past couple of years, and that these databases are for sale on the dark web.

I understand hashing passwords will slow down / stop hackers having access to plain text login credentials that may be the same for other websites the user has signed up to, but my question is, what about the personal information stored in the database that isn't hashed. Like which products you've bought over the past couple of years, or your employment history, or your contacts, or something really embarrassing such as which adult websites you've visited and/or bookmarked.

Considering you can't hash a user's email address if it's being used as part of the login credentials, some hacker could query the database using the userID for example, get personal information from other tables, and then send a blackmail email to that user.

The two potential solutions I can think of are:

Encrypt as much of the information in the database as possible - but can't encrypt it all otherwise we wouldn't be able to perform quick insight queries on our user base for example.
Encrypt the actual database file itself on the server, so if it were downloaded, they wouldn't be able to have access without knowing the key.

So, 1 isn't much of a solution because I can't encrypt everything. And would 2 actually work i.e. would a web app using django for example be able to write to the database if its root file on the server was an encrypted file? I know MS SQL Server has an always-encrypted feature, but I'm using MySQL.

Sorry for the essay-length question - my head is spinning with all sorts of possibilities, and it's driving me nuts!

Thank you!

There are two concepts at play here:

Data at rest
Data in motion

Data at rest

As you correctly noted, if someone gets access to your database and the data itself is not encrypted, then hashing and salting passwords makes no difference.

  Encrypt the actual database file itself on the server, so if it were downloaded, they wouldn't be able to have access without knowing the key.

You should enable on-disk encryption in some way or form. SQL Server offers Transparent Data Encryption (TDE), where as soon as data leaves memory and is persisted to storage, it is encrypted. This is called "encryption at rest".

This feature may not be available in your RDBMS, so you might rely on the storage vendor's on-disk encryption. Otherwise, you can investigate file-system level encryption.

The important thing is to keep the database administrators away from the encryption keys, and to ensure that database backups are handled with the same security considerations as the database server.

Data in motion

As for your question around applications having access to encrypted data, this is handled differently. I'll return again to SQL Server because that's what I know.

  Encrypt as much of the information in the database as possible - but can't encrypt it all otherwise we wouldn't be able to perform quick insight queries on our user base for example.

The application uses a certificate to connect to the server, so that only it can see the data it has access to. It's called Always Encrypted, and unless someone has that certificate, the data remains inaccessible.

The database server you end up using should take these two concepts into account. Here's the documentation for SQL Server, but the other more established players should have similar features.

Simple passwords are crackable.  Read about "dictionary password cracking".  Hackers have them -- they take the passwords in your system, look them up, and voila, there's the plaintext.  This is why everyone admonishes you to use upper and lowercase, digits, and punctuation.  And >= 8 characters.  A 'dictionary' to handle that many possibilities is impractical.


Why do MySQL tables crash? How do I prevent it? I'm the Administrator of a Moodle site which had its Users table corrupted and it became unusable.

Luckily, a simple REPAIR TABLE mdl_user made it work back again. The thing is that I don't know why it actually crashed and made it unusable, and I want to make sure that I'm better prepared next time.

I'm not a thoroughly experienced DBA — I'm just a developer who does a lot of stuff, so please bear with me.

I could just restore a backup, but I guess there are ways to prevent crashes.

Those tables are utf8_general_ci and using MyISAM.

Why does a MySQL table crash? What can I do to prevent that from happening?

It is rather easy for a MyISAM table to crash.

In the header of every MyISAM table is a counter that tracks how many open file handles there are against the table.

If you start up mysql and the number in the header does not match the number of actual file handles against, mysqld treates the table as crashed.

If a simple REPAIR TABLE mdl_user makes it work again every time without loss of data, this may indicate that you have a very highly trafficked site that writes to mdl_user.

If dozens of tables require this REPAIR TABLE, I would convert all the tables to InnoDB. However, if the mdl_user table is the only table with this problem, there is something you can do (for this example, let say the database is moodle);

If you want all tables left as MyISAM

STEP 01 : Create a Repair Table Script

echo "REPAIR TABLE moodle.mdl_user;" > /var/lib/mysql/MoodleStartUp.sql

STEP 02 : Declare the Repair Script as the startup file

Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/MoodleStartUp.sql

STEP 03 : Restart mysql

Every restart of mysql will trigger the Repair Table Script

If you want all tables to become InnoDB

Run this code to make a bulk conversion script of MyISAM tables to InnoDB, and view it

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql ${MYSQL_CONN} -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql

Once you are satisfied with the conversion script's contents, then run it

mysql ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql

UPDATE 2012-03-15 14:00 EDT

@Kevin, While using MyISAM, what if running out of Disk Space is your isuue?

Here is something to consider: According to MySQL 5.0 Certification Study Guide,

bulletpoint #11 says the following on Pages 408,409 Section 29.2:

  If you run out of disk space while adding rows to a MyISAM table, no
  error occurs. The server suspends the operation until space becomes
  available, and then completes the operation.

When you run out of disk space, do not just shutdown or kill mysql. The count of the open file handles in any currently used MyISAM will not have been cleared. Thus, the MyISAM table is marked crashed. If you can free up disk space in the data volume with mysqld still running, mysqld will soldier on once disk space is made available.

I found one good line to convert all tables to InnoDB:

mysql -u root -p dbName -e "show table status where Engine='MyISAM';" |
    awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}'  |
    mysql -u root -p dbName


Oracle password has expired, however I have no way of changing it When I connect to my local development database, I get an error saying the password has expired.  I've been trying to change it with sqlplus to no avail:

C:\>sqlplus TPMDBO/password@localhost/global

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 6 15:41:02 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired

Changing password for TPMDBO
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied

Password unchanged

I get this error no matter what new password I type in.

My main question is how I can reset this password.  However, I'm also curious if there's a way to make the password never expire.  This is a local dev database, I really don't care about the security or anything and none of the data on it is important.

If the account is locked, as far as I know, the user can't change it. The Oracle output in your question shows the account is not locked, but expired.

Log in to your database as a user who has privilege to alter other users (for example, SYS) and issue the following command:

ALTER USER tmpdbo IDENTIFIED BY new_password;

You can change the expiration behaviour. There already was a question about that on Stack Overflow:

Make Oracle Password Never Expire

log into sqlplus as username:SYSTEM
give previous password

sql>password
old_password:<oldpassword>
new_password:<new password>
retype new password:<new password>
password is set


Error 3203 restoring SQL Server backup: The data is invalid I am trying to restore a .bak file (backup taken on SQL Server 2008 R2, compatibility level 100). I am restoring the backup on SQL Server 2012 edition. I get the below error. Unfortunately the original DB from where the DB was backed up is no longer available. I am new on this project. I was given the bak file on a thumb drive.

Any suggestions on how I can restore the DB to 2012 ?

  Restore of database 'TstDB' failed.
  System.Data.SqlClient.SqlError: Read on ***13(The data is invalid.)

This is all is the error message. When I run RESTORE VERIFYONLY, I get the below error:

  Msg 3203, Level 16, State 1, Line 2
  Read on "C:****" failed: 13(The data is invalid.)
  Msg 3013, Level 16, State 1, Line 2
  VERIFY DATABASE is terminating abnormally.

RESTORE HEADERONLY was successful. When I run restore database with stats=1 I get the below:

  10 percent processed.
  20 percent processed.
  [...]
  70 percent processed.
  80 percent processed.
  Msg 3203, Level 16, State 1, Line 2
  Read on "C:\ccc.bak" failed: 13(The data is invalid.)
  Msg 3013, Level 16, State 1, Line 2
  RESTORE DATABASE is terminating abnormally.

Try running a RESTORE FILELISTONLY or RESTORE HEADERONLY. If these run successfully, that would prove that SQL Server is successfully accessing the .bak file. (And rule out some sort of permission issue.)

If that is successful, try running your RESTORE VERIFYONLY and RESTORE DATABASE with STATS=1. If it continues to fail in the same spot each time, it would probably indicate that your backup is corrupt, and you're out of luck.

If it fails in a different spot each time, it could be an issue with reliability of the location where your .bak is saved.

Have you tried to detach it from the 2008 R2 server, copying the .mdf and .ldf files over to the 2012 server and re-attaching them to it?

It doesn't solve the backup error I know. It would get the database over to the 2012 server and if it attaches successfully would tell you that its good.


Using column alias in a WHERE clause doesn't work Given a table users with two fields: id and email.

select id, email as electronic_mail
from (
        select id, email
        from users
) t
where electronic_mail = ''

Postgres complains that:

ERROR:  column "electronic_mail" does not exist

The example is just to demonstrate the arising problem. My actual case is more complex, I iterate though an array of elements in a json column, taking a single scalar value from each. (I can share some code if that helps.)

I really don't get what would be the complication, probably I'm unaware of something. I was under the impression that aliased columns can be employed in a WHERE clause without problem?

The manual clarifies here:

  An output column's name can be used to refer to the column's value in
  ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses;
  there you must write out the expression instead.

That's according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied.
Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless). See:

PostgreSQL: How to return rows with respect to a found row (relative results)?

Best to avoid column aliases that conflict with input column names a priori.

Aside: the subquery in your example is just noise since the WHERE clause is part of the outer query, so the example can be simplified to:

select id, email as electronic_mail
from users t
where electronic_mail = '';  -- doesn't work

I know this is a very old post - but just came across it - what I have done in the past is to "transform" the field - say using trim(a.b) as C,

this seems to force the alias to take.


Identity Column in Concurrency, Multithreading, Parallel Processing Our company would like to understand the effects:

If we have single table with Primary Key Identity column, int primary key IDENTITY(1,1),
and the Application is conducting multi-threading parallel processing, trying to conducts 1000s of simultaneous inserts on one table.

Would the identity primary key column fail, with a primary key violation in any way, for example: processors are trying to input the same identity number?

Or was SQL designed to be successful even in multithreading parallel insert?

Note: IDENTITY_INSERT is OFF

Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number?

This is covered in the SQL Server product documentation:

  CREATE TABLE (Transact-SQL) IDENTITY (Property)

  Identity columns can be used for generating key values. The identity property on a column guarantees the following:

  Each new value is generated based on the current seed & increment.
  Each new value for a particular transaction is different from other concurrent transactions on the table.

The second point is the guarantee you need.

It is valid no matter how many user processes or threads access SQL Server concurrently.

To state it explicitly: yes, identity allocation is thread-safe.

A call to CMEDSeqGen::GenerateNewValue allocates the next value to a caller, and is protected by a latch (a lightweight synchronization object). It is not affected by user transactions, which means you can end up with gaps in case of rollbacks. Concurrent transactions can generate identity (and sequence) values without having to wait on a lock.

For more details, see my answer to a related question.

Quite obviously IDENTITY would be uptterly useless in a multi user scenario if it did not (which for SQL Server is the same as a multi threaded scenario as a thread will come with a connection each), so yes, SQL Server handles it.

Read up the basics of locking. On top, IDENTITY is not quaranteeing to be "without holes". Insert, rollback transaction -> the identity number is not going to be reused for another insert. So, fundamentally you only need an atomic counter, which is trivial and actually implemented these days as assembler instruction. This is a trivial problem.

It also is a big misunderstanding by people - when they use for example IDENTITY for invoice numbers and then complain that the numbering scheme leaves holes ;)


Check Oracle Service is Started from Batch Command Uses: Oracle9i Standard Ed;

I have a schedule which performs a Dump Import and afterwards the System Reboots itself, which is happening without any problem.

After the System had rebooted, I have another task scheduled to run on System Startup; which has yet another PLSQL scripts to be run.

I witnessed after the Schedule task on System Start-up had ran, an error is thrown as

TNS: protocol adapter error

The cause was determined as The oracle Services had not Started.

So if I could check the Oracle Services has started or not prior to the query, I would solve the problem.

Or else I could use SLEEP Command in the batch file, but cant determine the exact time it takes to startup the server.

I welcome any suggestions on doing this with an alternate way as well.

Below is the Script file I run on System Start-up

@echo off
@echo %time%
echo Disable Triggers
sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#4.sql<quit.txt
@echo %time%
echo Run Drop Contraints Sql
sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#5x.sql<quit.txt

REM File Check
IF EXIST C:\Factory\participant-inclusion\Auto\step#5.sql (
 @echo %time%
 echo Run Drop Contraints
 sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#5.sql<quit.txt
 @echo %time%
 echo Modify Type
 sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#6.sql<quit.txt
) ELSE (
 @echo File Not Found
)

@echo %time%
echo Default Prefix
sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\Prefix-Updation-ALL-JKB.sql<quit.txt
@echo %time%
echo And Finally Done!
pause

Many Thanks,

You can actually start that service, while you are waiting on it to start in another session. Even if both starts occur at the same time, Windows should be able to handle that correctly, and end with the service being up. Or, you sleep. What time ? Put something, and test. Ain't that hard, no ?


WARNING Error checking mysqldump version Error retrieving version from mysqldump.exe: mysqldump: unknown option '--no-beep' (exit 2) When i go to do a manual export from mysql database to workbench, this error occurs,  i dont know solve

--no-beep is an option for the mysql command-line query tool, not for mysqldump.

I would guess that you have entered the no-beep in your my.cnf configuration file under the [client] section, which applies to both mysql and mysqldump clients. You need to move no-beep to the [mysql] section of the config file, so it applies only to the mysql client.

wb_admin_export.py (used by mysqldump) looks at PATH variable to find mysqldump and get version number. make sure its mysqldump from mysql bundle, not mysql workbench...


MySQL insists on trying to use InnoDB even when told not to, please help We're trying out MySQL 5.5, and have installed it on one of our slave servers as a test run. Our main server is 5.0, and we have zero optimization here for InnoDB, so I want everything to just be MyISAM for now. I've disabled InnoDB everywhere I can think, but the replication is throwing errors like:

Error 'Unknown storage engine 'InnoDB'' on query. Default database: ''. Query: 'CREATE TEMPORARY TABLE ... SELECT ...

The query never specifies the engine is InnoDB. default-storage-engine is set toMyISAM. innodb is set to OFF. Why is it trying to create this termporary table as InnoDB? This is 5.5, not 5.6, so we don't have the default-tmp-storage-engine variable yet. Please help, this is driving me insane.

To disable InnoDB, add this to /etc/my.cnf on the MySQL 5.5. slave:

[mysqld]
skip-innodb

and run this on the Slave:

service mysql restart

According to MySQL Documentation on CREATE TABLE :

  You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

  CREATE TABLE new_tbl SELECT * FROM orig_tbl;
  For more information, see Section 13.1.17.1, “CREATE TABLE ... SELECT Syntax”.

  Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

  CREATE TABLE new_tbl LIKE orig_tbl;

  The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.

You must specify the ENGINE upon creation

CREATE TEMPORARY TABLE ... ENGINE=MyISAM SELECT ...

to get around this problem

InnoDB is the default storage engine in MySQL 5.5.5. You need to specify otherwise in the my.cnf by using:

default-storage-engine = MYISAM

-- redacted inaccurate content --


Breaking out of a recursive query in Postgres 11 I'm having some trouble getting the correct data out of a tree structure, where I only want the first nodes in a branch matching criteria to be the result of the query.

I have this table of domains:

create table domains(
    id bigint,
    domain_name varchar,
    parent_id bigint,
    valid boolean
)

insert into domains values
    (1, 'example.com', null, false),
    (2, 'a.example.com', 1, true),
    (3, 'b.example.com', 1, false),
    (4, 'c.b.example.com', 3, true),
    (5, 'd.a.example.com', 2, true)

This gives this tree, with the valid domains in green:

Based on this, I want a.example.com and c.b.example.com to be returned as valid domains. Had the top level domain example.com been valid, only that should be returned. ("valid" is just a flag determined elsewhere.)

I have a recursive query that fetches the whole domain tree:

WITH RECURSIVE valid_domains AS (
  SELECT id, domain_name, valid FROM domains
  WHERE parent_id IS NULL

  UNION ALL

  SELECT d.id, d.domain_name, d.valid FROM domains d
  JOIN valid_domains vd ON d.parent_id = vd.id
)
SELECT * FROM valid_domains

...but then my stumbling block is figuring out how to filter on valid domains and break that branch when I hit one that's valid, without returning any valid domains below it.

I've looked at window functions, but haven't been able to mesh that with the recursive query thus far. Am I taking the wrong approach by making this recursive in the first place?

WITH RECURSIVE cte AS (
  SELECT id, domain_name, valid
  FROM domains
  WHERE parent_id IS NULL
UNION ALL
  SELECT domains.id, domains.domain_name, domains.valid
  FROM domains
  JOIN cte ON domains.parent_id = cte.id
  WHERE NOT cte.valid -- stop recursion when valid node reached
)

SELECT id, domain_name
FROM cte
WHERE valid

fiddle

You were almost there:

WITH RECURSIVE valid_domains AS (
  SELECT id, domain_name, valid FROM domains
  WHERE parent_id IS NULL

  UNION ALL

  SELECT d.id, d.domain_name, d.valid FROM domains d
  JOIN valid_domains vd ON d.parent_id = vd.id
)
SELECT *
FROM valid_domains
WHERE valid
LIMIT 1;

I suppose you need to filter only valid answer and then just use LIMIT to get only the first result...

You might want to add an ORDER BY clause, though...


DBCC loginfo contains thousands of VLF entries with status 2 I've inherited this huge SQL Server 2000 prod database and suddenly the transaction log ran out of space.  The transaction log gets backed up every hour but I think over the weekend some existing jobs to re-index some tables grew the log file to the point where it ran out of space.  The first thing I did was backup the log (the database is in full recovery mode) and then ran dbcc sqlperf to check the log space used percentage.  It was still at over 99%.  So I did some more research and found another dbcc command, loginfo.

When I saw that output, there were almost 15K rows returned with all of status 2 and some of the CreateLSN seemed dated a while back.  I checked for any open transactions and there weren't any.

It seems like the active portion of the log file is permanently keeping the file very large forcing any new transactions to request more space.  As far as I can tell, there's no replication going on requiring the VLFs to stay active.

if this is happening only for one db, check the default initial size of the log file for that DB. I suspect the minimum size has to be large enough so that after restoring or even after backing up the log SQL server increases the log size to specified minimum size and initialize all VLF with zero.

Alternately, if you have already taken a full backup of the db try to create dummy DDL stmt, backup the DB again, restore it on another machine, and confirm the DDL operation you did. now you can detach and attach the DB w/o using the existing log file that will create new log file with the default size you have in your instance.


Difference between transaction log and redo log in MySQL I have read about MySQL. In my opinion, I see two log types as very similar. MySQL saves how and when data is changed in log. Information is used for recovery of MySQL. I am confusing the function of two log types.

REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL.

The transaction log is, by default- it can be changed- the two files located in $DATADIR called ib_logfile0 and ib_logfile1. It serves the same functions as the REDO log in other databases- storing writes in a safe way and recovering in the case of a crash, although there are some details in implementation that differ in functionality from other RDMS. It is the main component for InnoDB to be a transactional engine.

Do not confuse the transaction log with the binary logs in MySQL. The binlog, by default, is on the $DATADIR and is *hostname*-bin.index and several *hostname*-bin.00001, etc. It is particular confusing for people coming from other databases, because it is used for other things that other databases use the REDO log for: replication and point in time recovery. The main difference is that the transaction log is InnoDB-only, the binary log is (mostly) transaction-independent, as it is for all storage engines, transactional or not. MyISAM will write (if enabled) to the binary log. InnoDB will write to the transaction log and the binary log.

More info on the manual: REDO log, binary log.

In SQL Server , the TLog files stores both undo and redo information.
But in MYSQL and Oracle, the TLog/Redo Log stores only redo information.

Binary Loging in MySQL is a way to have point in time recovery. Enabling binary logging is equivalent to having full recovery model in SQL Server.


Oracle database 18c – ORA-12541: TNS:no listener I have installed Oracle 18c in my local machine and i have created a user names as

USER_NAME := c##scott
Passkey : oracle

When i used to connect to that user using cmd. The following error is populating out.

Command: sqlplus c##scott/oracle@agndev

ERROR:
ORA-12541: TNS:no listener

After checking the listener error I have rechecked my listener file

Listener File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\ora_base)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\ora_base\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = AGNDEV))
    )
  )

Tnsnames.ora file:

# tnsnames.ora Network Configuration File: D:\ora_base\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

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

    AGNDEV =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = AGNDEV)
        )

  )

I am new to 18c can anyone say the solution to this error.

Thank you.

The error message clearly says there is no listener listening on the requested address.

Check if it is running (lsnrctl status), and start it if necessary (lsnrctl start). On Windows you will need to do this in a command prompt started with administrator privileges, because it also start a Windows service. Or you can start the Windows service called something similar to OracleOraDB18Home1TNSListener.

You have the wrong value set for the parameter KEY in your listener.ora file. It should be EXTPROC1521 as below:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


Windows Login can't access SQL Server even with SYSADMIN role I'm working on AD permissions and have removed my user account from the Domain Admins security group. Now I'm having difficult accessing my SQL Server database with my Windows login. The login is in the sysadmin server role group and has mapped permissions to databases on the server. Any ideas why I can't access the server itself?

I will say that if I add a security group that my Windows login is a part of and give that group read permissions...I can then access the server.

Any advice would be appreciated.

Thank you!

Create a regular account that goes in a group that has sysadmin on the database. Then restart your SQL Server management studio and log on using windows authentication. You can start SSMS using the "Run-as" to log on with a different account than you used to log onto your desktop

This gives you Separation of responsibilities between domain admins and database administrators.