texts: Data base structure of a subscriber list

texts:
I am building a application that allow different user to store the subscriber information

To store the subscriber information , the user first create a list
For each list, there is a ListID.

Subscriber may have different attribute : email phone fax ....

For each list,  their setting is different , so a require_attribute table is introduced.
It is a bridge between subscriber and List

That store Listid ,subid , attribute, datatype

That means the system have a lot of list, each user have their own list, and the list have different attribute, some list have email , phone , some may have phone, address, name mail.. And the datatype is different, some may use 'name' as integer , some may use 'name'
as varchar

attribute means email phone, it is to define for which list have
which subscriber attribute
datatype means for each attribute, what is its datatype

Table :subscriber :
Field :subid , name,email

Table :Require Attribute:

Field : Listid ,subid , attribute, datatype

The attribute here is {name, email}

So a simple data is

Subscriber: 1 , MYname, Myemail

Require Attribute :

 Listid , 1 , 'email', 'intger'

 Listid , 1 , 'name', 'varchar'

I found that this kind of storage is too complex too handle with,  Since the subscriber is share to every body, so if a person want to change the datatype of name, it will also affect the data of the other user.

Simple error situation:

Subscriber:

list1,  Subscriber 1 , name1, email1

list2, Subscriber 2  , name2 , email2

Require Attribute :

     List1 , Subscriber 1 , 'email', 'varchar',

     List1 , Subscriber 1  , 'name', 'varchar',

     Listid , Subscriber 2 , 'email', 'varchar',

     Listid , Subscriber 2, 'name', 'integer',

if user B change the data type of name in require attribute from varchar to integer, it cause a problem. becasue list 1 is own by user A , he want the datatype is varchar, but list 2 is won by user B , he want the datatype to be integer

So how can i redesign the structure?


texts:
While updating data in my table i am getting below error.

 date/time field value out of range: "2017-2-29"

I want to skip this error and want update statement to continue further.

How can we do this with update statement.


texts: Error 18456 error severity 14 state 5 SQL Server 2012 SCCM2012 SP1

texts:
We had a primary site for SCCM 2012 SP1 running fine. I then installed CAS for SCCM on another server. The prerequisites required me to change the collation on the existing server. I did this and ran a rebuild database command at the same time. The SCCM database for the site dropped, along with a reporting database and temp reporting database. I had to re-add some users to the instance and marry up with the reattached databases (the ones that had dropped). I am now getting a SQL Server error as follows:

  Error: 18456, Severity: 14, State: 5.
  2013-11-20 16:28:44.73 Logon
  Login failed for user 'DOMAIN\MACHINENAME$'. Reason: Could not find a
  login matching the name provided. [CLIENT: ]

SCCM user auto discovery isn't working as new users in AD are not being written into the database, probably due to this. I have recreated the local machine user and checked NT AUTHORITY\SYSTEM, NT AUTHORITY\NETWORK SERVICE, and DOMAIN\MACHINENAME$ users match between the databases and the server and the SIDs all match. To do this, I used:

select * from sys.database_principals
select * from sys.server_principals

All connections are local and using windows authentication. The owner of the SCCM database is 'sa' and the SID matches. TCP/IP and named pipes are enabled, the ports for connection are static. The 3 users above are set as sysadmin on the server.

Any ideas?


texts: close existing connections during a restore

texts:
is there an option to close existing connections when doing a database restore in transact sql (equivalent to the box that we can check in SSMS)?

can we use with restricted user to do it an if so, what should we do to remove this option once the restore operation finish

Thanks in advance


texts: Database export taking 12 hours and still running

texts:
I'm running an export for a company I haven't worked with before.
Started export last night at 7:00pm and it's still running 12 hours later.
It's an agile 9.3.3 database, oracle 12c.
So far the dmp file is 18.7gb.
The oracle tables look like this

Does it make sense for this export to take so long?
Thanks,
Laura


texts: Suppress some output from PL/SQL script in Toad for Oracle

texts:
Question:
In PL/SQL for Oracle running in Toad, can I suppress script output except for certain lines I want to see? e.g. I don't want create table, insert record, drop table. I do want my own dbms_output.put_line messages.

Example:
Here's my current output file. (This is simplified but it shows the kinds of messages I'm getting.)

  PL/SQL procedure successfully completed.
 Commit complete.
 Table created.
 1 row created.
 1 row created.
 1 row created.
 Dropping table: ER_TEST001S_RES
 Checking results for test001s
         PASS: All records on _res expected results table have matches on actual results table for test001s
         PASS: All records on _res actual results table have matches on expected results table for test001s
  PL/SQL procedure successfully completed.
 Commit complete.

My preferred output would look like this.

 Checking results for test001s
         PASS: All records on _res expected results table have matches on actual results table for test001s
         PASS: All records on _res actual results table have matches on expected results table for test001s
 Checking results for test002s
         PASS: All records on _res expected results table have matches on actual results table for test002s
         PASS: All records on _res actual results table have matches on expected results table for test002s

Context:
I'm using PL/SQL to test a calculation program that runs as a database procedure. My script inserts test records, runs the calculation procedure on them, creates tables of expected results, and compares expected to actual results. (I drop/create the test objects and expected results tables on each test run to make it quick and easy to run all tests at once when moving from dev to test environments.) Currently I have an action in Toad's Automation Designer to run the scripts for all tests, with 'set serveroutput on' and the output going to a file where I check whether any tests failed.

Goal:
Groom the script output to show only lines sent via dbms_output relating to pass/fail status of the test steps. I can't find the right search terms to get a lead on this. Is it possible?


texts:
MysQL is frequently going down. Error logs contain:

[ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it  | 149 occurrences
[ERROR] Native table 'performance_schema'... has the wrong structure                    | 500+

I did a research on the internet. It is suggested to fix it by running a mysql_upgrade script like this:

/etc/init.d/mysqld stop
mysqld_safe --skip-grant-tables &
mysql_upgrade
/etc/init.d/mysqld stop
/etc/init.d/mysqld start

It is strongly advised to create a backup before running any upgrades. But my databases do not look healthy.

There is no snapshot option for this server.

Questions:

How do I preserve the information?
How would I go about it?
Is it okay to do mysqldumps for such databases?


texts:
When I query select * from sys.service_queues in SQL Server 2008 sp3, I  am getting:

InternalMailQueue
ExternalMailQueue
_LakeSide_DbTools_DeadlockQueue
QueryNotificationErrorsQueue
EventNotificationErrorsQueue
_LakeSide_DbTools_LockQueue
syspolicy_event_queue
ServiceBrokerQueue

I need to remove _LakeSide_DbTools_DeadlockQueue and _LakeSide_DbTools_LockQueue from MSDB database permanently. I installed that lakesidetool and manually removed all procedures, now stuck in removing this services. I used their uninstall tool, but it didn't help.
http://www.sqlsolutions.com/products/sql-deadlock-detector/Uninstall.html


texts:
Using InnoDB. I've read this article about the InnoDB locking model, but I could not find the answer to this question:

Do MySQL locks expire? If a process grabs a lock, will that process still hold the lock tomorrow?

My use case is what if my program has a rogue thread running, maybe it's killed, maybe it's alive. Is there any guarantee that that the lock will be released...ever?


texts: Convert right side of join of many to many into array

texts:
When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row.
Example with 3 tables:
CREATE TABLE items (
  id    serial primary key,
  title text
);

CREATE TABLE tags (
  id    serial primary key,
  title text
);

CREATE TABLE items_tags (
  item_id int references items(id),
  tag_id  int references tags(id),
  primary key (item_id, tag_id)
);

When selecting items with their tags I can do it this way:
SELECT i.id, i.title, i.title
FROM items i
INNER JOIN items_tags it
ON it.item_id = i.id
INNER JOIN tags t
ON t.id = it.tag_id;

And the result will come up as:
(1, "item n1", "sport")
(1, "item n1", "soccer")
(2, "item n2", "adventure")
(2, "item n2", "mountain climbing")
(2, "item n2", "sport")
(2, "item n2", "nature")

What I'd like to have is this:
(1, "item n1", ["sport", "soccer"])
(2, "item n2", ["adventure", "mountain climbing", "sport" , "nature"])


texts: Page Lookups Percentage - SQL Server performance monitoring

texts:
I'm currently doing some work on monitoring SQL Server performance.  I've found the following script[1] which calculates 'Page Lookups Percentage' - the suggestion being that a "good" value is something less than 100.

SELECT 1.0*cntr_value /
(SELECT 1.0*cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec')
AS [PageLookupPct]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec'

Now I've run this script on about a dozen different servers - mostly virtual but a couple physical, mostly 2008R2 Enterprise but a couple of 2005, some live and some development, and even a physical 2008R2 Express edition that has only ever been a witness server and is currently dormant (no mirrors set up with active failover).  On only one of these servers has the value been below 100. In most cases it has been in the 1000's or at least in the mid 100's.  On the dormant witness server the value was a ridiculous 20,000 !

Does this indicate that we have widespread problems in this area, or is there some innocent explanation for why this figure should be so high on so many different servers?  We are experiencing slow performance on some of these servers, so it would be useful for me to get a better understanding of what this figure is telling me.

[1] - http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/


texts: Meaning of various user account privileges in phpMyAdmin

texts:
Reference the attached screenshot:

I created a non-root user, and gave it all privileges to a database. It now has All Privileges of Type -- global and database-specific. What's the difference between these 2 "Types"?
Why three root User names? What's the purpose of having so many?


texts: What is different bettween Cassandra NoSQL data model and SQL data model

texts:
can any one tell. cassandra data model and sql data model
how we can define row and column with cassandra data model?


texts:
I have a  table t1 with 2 columns, col1 has all possible combinations of strings of length 13 with alphabets of {1,2,3} and col2 has some integer X. Thus table has 3^13 rows in total.

For each of the strings I would need to know which other strings are exactly within a hamming distance of say 3 and get the sum of col2. So each of the strings have 2288 ( (13,3)*2^3) other strings with a hamming distance of 3.

My solution to this was to create table t2 with all possible mappings for each of the strings. So this table now has 3^13*2288 rows in it with columns col1 similar to t1 and col2 has all the strings within desired distance of string in col1. Then I would index both of tables on col1 and join them together on col1 and sum col2 on tbl1 and group over col2 in t2.

SELECT t2.col2,sum(t1.col2) FROM t1 JOIN t2 ON t1.col1=t2.col1 GROUP BY t2.col2

Problem is that when I try to execute this query it runs for ages and after 10 mins I get a timeout message from server.

My server is community version of MySQL 5.7 with standard configuration.

Whats wrong with this solution and/or configurations because it takes so long process this?


texts:
I have two tables, both with ~30,000 rows.  Anywhere where b.comments is not null, I want to overwrite a.comments with b.comments' data.  Seems simple enough - but my query has been running for 45 minutes and I don't think it's working.  There about 3,000 values in b that are not null - so a max of 3,000 updates.

I know that syntactically this is fine, but I'm wondering if there is a different way I should be writing this query as I cannot seem to get it to run unless I also add in something like ...and batch_id = 12345 to get the number of records down to 1.

UPDATE temp3 a
INNER JOIN temp2 b
ON a.batch_id = b.batch_id
and a.batch_li = b.batch_li
SET
a.comments = b.comments
WHERE
b.comments is not null;


texts:
Executing this request:

update table t1 set t1.column = 0 where t1.column2 = 1234

Getting this error:

  column "t1" of relation "table" does not exist

This request runs fine in MySQL.
Why do I get this error in PostgreSQL?


texts: SqlServer Passing parameters within Nesting procedures

texts:
I have these 2 procedures:

Create procedure jolly (@job_par varchar(100)='p%') as
select * from emp where job like @job_par

create procedure test1 @proc_name varchar(30)
as exec @proc_name

if i execute like this

exec test1 @proc_name= jolly

i get all the employees with job that begins with 'p', and it's ok.

But how can i call the procedure test1 to make me return all the employees with job='ACCOUNT'??

i tried like this but it gives me errors:

exec test1 @proc_name= jolly @job_par='ACCOUNT'

How can i pass the parameter for the second procedure?

Thank you.


texts: Join query result of 2 different tables based on common column

texts:
I want to join the result of two different queries from two different tables based on a common column (which is named differently in the two tables) but I cant seem to figure out the easy way to do it. I'm using psycopg2 on python 3.5.

From the first table, I need the Count of meetings. So I do:

SELECT   user_id,COUNT(1) as num
FROM     tasks
WHERE    created_at BETWEEN %s and %s
GROUP BY user_id """,(DAYS_AGO_7_CONFIG,YESTERDAY))

This gives me

UserID | Number
1      |  123
2      |  12

UserID is my mutual column. Now I made userID into a list by list comprehension, then I do:

SELECT id, given_name,family_name,email FROM users WHERE id in +str(userID))

and I get the id, given_name, family_name, and email fields from the second table.

Now I need to add the Number column into this result, which I'm doing by very inefficient non-DB means (pandas library in python)

I'm pretty sure this entire thing can be done in one query (perhaps with union?), but I cant seem to work it out.

Intended output:

id | given_name | family_name | email | Number


texts: ElasticSearch: is this problem suitable for ElasticSearch DB?

texts:
I have some hardware instruments each one recording a signal and generating sequences of timestamp/value pairs. Timestamp is a basic UTC posix time and value is a double.

I'd like to record such pairs in a database for later retrieval. Because we want to scale up across multiple servers we ruled out relational databases and opted out for a NoSQL solution.

The core usecase will be to retrieve a set of values from a given time period (starttime, endtime).

Is ElasticSearch a good solution? What about MongoDB?


texts: Problematic slashes and questions marks in unique index values

texts:
What is it about the Phone values inserted below that SQL Server is treating them as identical in a unique index?

CREATE TABLE Phone
(
  Id int identity(1, 1) primary key,
  Phone nvarchar(448) not null
)
go

create unique index IX_Phone on Phone(Phone)
with (data_compression = page);
go

insert into Phone Values ('?281/?263-?8400');
insert into Phone Values ('281/263-8400');

select * from Phone;

drop table Phone;

I receive an error message:

  Msg 2601, Level 14, State 1, Line 13
  Cannot insert duplicate key row in object 'dbo.Phone' with unique index 'IX_Phone'. The duplicate key value is (?281/?263-?8400).


texts: How exactly does sp_msforeachdb work behind the scenes?

texts:
I need to troubleshoot an issue I am having, and I need some help understanding how sp_msforeachdb works in order to overcome my issue.

What happens is every time I run sp_msforeachdb, I get an error Msg 102, Level 15, State 1, Incorrect syntax near '61'

An example of my code is as follows:

EXEC SP_msforeachdb 'SELECT ''?'' AS Database
                     FROM ?.sys.objects
                     WHERE name like ''%aetna%''

However, it does not matter what query I have as a parameter to sp_msforeachdb. Every time I get the same error. I do have a database that starts with '61s1d', so that makes me think it has an issue with the DB name, but I honestly do not know what is going behind the scenes on sp_msforeachdb.

Things to note.

It is the only database that starts with a number
I can try to use code like "If database is like '%61%' don't do ......", but still the same error.
I cannot test changing the database name--too many things connected to it.
If I create a test db that starts with '51', then I also get the error for that database

How can I overcome this?


texts: Around 50 % of the tables in a certain SQL Server database have either zero, one or two rows - is this a sign of a good design?

texts:
First off, I'm not a database programmer, nor admin or anybody who is responsible for this area, so I might be wrong in many of the following questions/assumptions.

I've recently encountered a DB schema of about 250 tables. It's a database for an eshop. I've looked into many of these tables and was surprised about how many of them were completely empty or had just one or two rows. So I checked all the tables with:

select count(*)
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
where i.rows in (0, 1, 2);

and

select count(*)
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1);

I found out that about 50 % of all the tables are tables with 0, 1, or 2 rows.

I understand that there are some rules for creating databases (normal forms), but to me this just seems too much. For example, wouldn't it be a bit more efficient to store some of this data in one table then in two or more and then having to use joins? The way I imagine this is the database would store data in one table together (close to each other) on the disk, so they could be retrieved fast. But if I use 2 or more tables (therefore I need to use joins when retrieving the data), the data could be stored on different places on the disk, therefore taking more time to retrieve them?

The database is not a big one I suppose, the largest tables have around 1 million rows, I think this could be considered small nowadays. So it might be that on this amount of data, it doesn't make a big difference whether I use joins extensivelly.

I know my question is rather broad. Since I don't have more knowledge about the database design, I can't really be more presice, yet I'd appreciate some of your thoughts on this.

Thank you


texts: Transaction log backup file larger than expected?

texts:
I've recently taken over a SQL Server database that didn't have any transaction log backup routine running for at least 2 years. The first log backup was very large (3.8 gb on a 1.14 gb database) as you would predict, however subsequent log backups are still larger than I would have expected (somewhere in the region of 3 mb).

I also restored the database to a QA and ran several sequential log backups without there being any activity on the database and the log file backups were still ~3 mb.

What would account for the size and consistency of the transaction log backups with nominal activity on the database itself?

EDIT:

Here is the script we're using to run the TLOG backups, this is being executed every 10 minutes:

BACKUP LOG [mydatabase]
TO  DISK = @fullpath
WITH  RETAINDAYS = 2
    , NOFORMAT
    , NOINIT
    , NAME = @bckName
    , SKIP
    , REWIND
    , NOUNLOAD
    , STATS = 10;


texts: When are the unnecessary row versions being removed from the version store

texts:
Sorry for my bad English :)

I need a help with understanding version store`s cleaning. I have the following situation.

12:00 Start a select statement under the snapshot isolation level and not commit.
12:01 Start an update statement (update a single row) under the read committed isolation level and commit.
12:02 Start a select statement under the snapshot isolation level and not commit.
12:03 Start an update statement (update a single row) under the read committed isolation level and commit.

Now if we look at the version store such as

SELECT *
FROM sys.dm_tran_version_store

we will see there 2 rows (one row for each update statement).

Now if we commit the first transactions that starts at 12:00, no row is being removed from the version store (even pass a minute).

The question is WHY???.

In my opinion on of the rows have to be removed from the version store, because there are no any active transactions, that have to access to that version.

I tried also to update no 1 row, but 100 rows (Total more then 100 KB) but I have the same situation, the version store is not being removed.

Please help me to understand when does the version store is being clean.
Thanks a lot for help.


texts: Why does Type Resolution fail when using SELECT DISTINCT instead of SELECT on PostgreSQL?

texts:
Here is a minimal reproduction:
SELECT 1
UNION ALL
SELECT DISTINCT NULL

Fails with:
UNION types numeric and text cannot be matched

According to the PostgreSQL documentation on Type Resolution for UNION, CASE, and Related Constructs and the following chapter on SELECT Output Columns, the type of null should be derived from the preceding select.
In fact it works, if I remove the DISTINCT:
SELECT 1
UNION ALL
SELECT NULL

Why does the DISTINCT modifier interfere with type resolution?


texts: Avoid Sort on Indexed Group By caused by DATE_TRUNC

texts:
Is there a way to avoid Sort operation for GROUP BY, caused by the DATE_TRUNC function not being recognized as tstamp, which is a part of the ix index? That function doesn't really affect the order maintained by the ix.

CREATE INDEX ix ON tbl(col, tstamp, num)

  SELECT col,
         DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC') AS trunc,
         SUM(num)
    FROM tbl
   WHERE col IN (?, ?, ...)
     AND tstamp >= ?
     AND tstamp <  ?
GROUP BY col, trunc

Precision parameter for DATE_TRUNC is dynamic, it could also be 'DAY' or 'YEAR'... so I cannot index that function without creating an index for each variation.

The execution plan I get:

Finalize GroupAggregate
  Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
  ->  Gather Merge
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate
              Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
              ->  Sort
                    Sort Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
                    Sort Method: external merge  Disk: ...kB
                    -> Parallel Index Only Scan using ix on tbl
                         Index Cond: ((col = ANY (...)) AND (tstamp >= ...) AND (tstamp < ...))
                         Heap Fetches: 0

Execution plan I want would look something like this:

GroupAggregate
  Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
  ->  Index Only Scan using ix on tbl
        Index Cond: ((col = ANY (...)) AND (tstamp >= ...) AND (tstamp < ...))
        Heap Fetches: 0

(I get that if I don't use DATE_TRUNC)


texts: How to handle reporting of an extreme amount of databases in SQL Server 2005?

texts:
I'm looking for some advice on how to handle reporting for our environment.  We currently have 16 servers with 20 instances of SQL Server 2005.  We have over 6,600 databases and growing across these instances (1 database per customer).  The majority of our databases run in size from 200mb to 7gb with about 60 databases running in size from 11GB to 110 gb at the largest.

We are using a SAN for storage and we are running into issues with the running of the reports affecting IO.

One idea we had was to pull the 60 larger databases and then use transactional replication to replicate these databases and run reporting on the replicas.

That would then leave all the smaller databases to run without the stress of the larger databases.  In the future we don't believe there will be any more of the the larger databases based on our company goals.

Any thoughts?


texts: how to find vertica community edition and how to export / copy data

texts:
I have the hardest time downloading anything other than enterprise edition from vertica.com. It is supposed to have a community edition on https://my.vertica.com/community/ but all the links just show the enterprise editions for download.

I have an older download (forgot how I got to that earlier) vertica-ce-6.0.1-0.x86_64.RHEL5.rpm which may be the latest, not sure, enterprise edition latest seems to be 6.1.0 at the time of writing.

I installed that and pushed 12 million rows to it from an older enterprise edition version using CONNECT TO VERTICA and EXPORT TO VERTICA... however when I tried to now push that data out from the community edition to another test installation that also is community edition, psql says:

ERROR 2678:  Command EXPORT is not supported

Googling that brings up nothing, and the my.vertica.com forums also have no match. Am I the first to see it really?

Whats the next efficient way to dump the data from one community edition vertica installation to another if EXPORT TO VERTICA and COPY FROM VERTICA should not be supported in the community edition?

The reason why I am looking at vertica is that it appears to respond much faster than mysql when there are more than a billion rows in the table, i.e. the result set of a query comes in within 30 seconds instead of 5 minutes on identical hardware.


texts: Generate a sequence of timestamps but skip given periods

texts:
I have a table things_to_do in PostgreSQL. For example:

thing_id
---------
 1
 2
 3
 4
 5
 6

And I want to add a timestamp to every row:

SELECT thing_id, '2016-02-10 19:57:00'::timestamp
                  + '0:01:00'::interval * row_number() OVER () AS estim_date
FROM things_to_do
ORDER BY 1;

 thing_id |    estim_date
----------+---------------------
 1        | 2016-02-10 19:57:00
 2        | 2016-02-10 19:58:00
 3        | 2016-02-10 19:59:00
 4        | 2016-02-10 20:00:00
 5        | 2016-02-10 20:01:00
 6        | 2016-02-10 20:02:00

Basically, I'm doing one thing every minute. But I don't want to work after 20:00 and not before 09:00. I would like my request to skip the night.

The question is:

Is it possible to give my request a period of time and skip that time? Some sort of modulo, maybe?

The final result I would like to have:

 thing_id |    estim_date
----------+--------------------
 1        | 2016-02-10 19:57:00
 2        | 2016-02-10 19:58:00
 3        | 2016-02-10 19:59:00
 4        | 2016-02-11 09:00:00
 5        | 2016-02-11 09:01:00
 6        | 2016-02-11 09:02:00


texts:
I need a clarification on how below SQL Server service pack patching works.

Few of my servers are at below patch for SQL Server 2014 which has latest meltdown patch

  12.00.5214    2014.120.5214.6   4057120 Security update for SQL Server 2014 Service Pack 2 GDR: January 16, 2018 – Security Advisory
  ADV180002  CVE-2017-5715 CVE-2017-5753 CVE-2017-5754

and some with below

  12.00.5532    2014.120.5532.0   3194718 MS16-136: Description of the security update for SQL Server 2014 Service Pack 2 CU: November 8,
  2016

2014.120.5532.0 version seems to be on higher build than one 2014.120.5214.6 but that patch seems old when checked as published in OCT 2016.

Do I still need to apply meltdown patch for those servers with build 2014.120.5532.0 or are they covered for meltdown.

Need some better understanding on this.


texts: How to find out which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to?

texts:
When I query sys.dm_tran_locks, a couple of questions arise:

I can see there are some locks that are placed at KEY, PAGE, EXTENT, RID level (resource_type column). How to find out to which table or object these particular keys, pages, extents, rids belong to?
For the OBJECT level locks (resource_type = 'OBJECT') - what is the proper way to define object name? Is the resource_associated_entity_id column the object_id of the object? Or is there some caveat?
Some OBJECTS have resource_associated_entity_id = -1261057897 (negative number) - how to find out the object name for these?


texts: parse rs.status() output to display one secondary node for mongodump destination

texts:
I wish to run mongodump to secondary (not primary). So I need as output hostname and port of one secondary.

# ./mongo  -udbadm admin --port 27100 -p --quiet --eval "printjson(rs.status().members.map(function(m) { return {'name':m.name, 'stateStr':m.stateStr} }))"
Enter password:
[
        {
                "name" : "example-1.domain:27200",
                "stateStr" : "SECONDARY"
        },
        {
                "name" : "example-2.domain:27200",
                "stateStr" : "PRIMARY"
        },
        {
                "name" : "example-3.domain:27200",
                "stateStr" : "SECONDARY"
        }
]

With shell script tools is no problem for me to extract one secondary with hostname and port.
My question howto do this job the MongoDB way? What tools do you recommend me?
I heard that jq is better for JSON data. But don't know howto extract only one secondary host.


texts: Are there any free online collaboration database schema tools?

texts: I am interested in a free and simple MySQL tool which can offer online collaboration functionality in creating database general schema. Do you know any?


texts: Extract Data Between Delimeter In NVarchar Field

texts:
I have a nvarchar(1000) data type field that holds data like this
name_Test_1
 name_Test1_2
 name_Test2_3

Now the data I want to extract is what is between the _ so the data I would want extracted would be

Test
Test1
Test2

I tried the below syntax, but since the string has two _ in it then the data I have returned is 1, 2, 3 - how should this query be changed so that I get the middle string?

Declare @Red Table (fname nvarchar(1000))

Insert Into @Red (fname) Values
('name_Test_1'), ('name_Test1_2'), ('name_Test2_3')

Select
RIGHT(fname,CHARINDEX('_',REVERSE(fname))-1) as RightHalf
FROM @Red


texts:
I'm creating a query that has a column of dates stored as a VARCHAR in the format DD-MMM-YYYY.

Could you tell me how I can convert this into date format please?

I don't want to alter the original table in any way, as it's not mine to tinker!


texts: MySQL - Out of memory: Kill process 3046 (mysqld)

texts:
We run a MySQL (v5.6.17) dump backup every 4 hours, and today noticed:

Apr 19 20:09:30 mysql1 kernel: Out of memory: Kill process 3046 (mysqld) score 970 or sacrifice child
Apr 19 20:09:30 mysql1 kernel: Killed process 3046, UID 27, (mysqld) total-vm:12617880kB, anon-rss:7932424kB, file-rss:24kB
Apr 19 20:09:30 mysql1 mysqld_safe: Number of processes running now: 0
Apr 19 20:09:30 mysql1 mysqld_safe: mysqld restarted

See the entire log at: https://gist.github.com/nodesocket/daf77fb923b4bf6bef1f

The server is running 4 cpus and 16GB of memory with SSD disks. I thought the OS would swap instead of just crashing mysqld completely.

I suppose upgrading the server to 32GB of memory, would probably help, but wondering if we can optimize any settings in our my.cnf before we commit more physical resources and money.

##############################################
# my.cnf
##############################################
[mysqld]
datadir=/mysql/data
socket=/mysql/mysqld.sock
symbolic-links=0
default-storage-engine=InnoDB
slow_query_log=1
long_query_time=2
slow_query_log_file=/var/log/mysql_slow.log
expire_logs_days=30
max_connections=50

bind-address=XXX.XXX.XXX.XXX
port=3306
max_allowed_packet=4M
net_retry_count=5
max_connect_errors=100
wait_timeout=14400
connect_timeout=10

open_files_limit=65535
innodb_open_files=65535
key_buffer_size=256M
innodb_buffer_pool_size=4096M
innodb_log_buffer_size=4M
group_concat_max_len=16k
max_sort_length=16k
max_length_for_sort_data=16k
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
innodb_thread_concurrency=8
thread_concurrency=8
thread_cache_size=128
thread_stack=1M
read_buffer_size=1M
join_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size=1M
table_open_cache=32768
table_definition_cache=65535
tmp_table_size=33554432
max_heap_table_size=33554432
innodb_log_file_size=1024M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=0
log-warnings
innodb_flush_neighbors=0

user=mysql
old_passwords=0

[mysqld_safe]
syslog
pid-file=/var/run/mysqld/mysqld.pid


texts:
I configured MySQL service on a netscaler 10.5. When I perform some MySQL benchmarks using mysqlslap, the first time it runs correctly, the 2nd time I have an error message:

When I run it the first time:

mysqlslap -hlb_mysql --concurrency=10 --iterations=1 --number-int-cols=2
          --number-char-cols=3 --auto-generate-sql --auto-generate-sql-load-type=read
          --engine=InnoDB
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 0.044 seconds
        Minimum number of seconds to run all queries: 0.044 seconds
        Maximum number of seconds to run all queries: 0.044 seconds
        Number of clients running queries: 10
        Average number of queries per client: 0

When I run it the 2nd time:

mysqlslap: Cannot run query CREATE TABLE `t1` (intcol1 INT(32) ,intcol2 INT(32) ,
           charcol1 VARCHAR(128),charcol2 VARCHAR(128),charcol3 VARCHAR(128))
           ERROR : No database selected

When I wait a few minutes, it runs correctly again. After executing, I see about 16 persistent connection on the database originating from the NetScaler.

Any ideas how I could solve this problem?


texts:
I have mysql 5.5 on my ubuntu 12.04 server. This command:

mysql -u root -p

works perfectly, but this gives me error:

mysqldump -u root -p mydb_name > a.sql
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

In my.cnf file, the socket is set to:

socket = /home/mysql/mysql.sock

so I have no idea where /var/run/mysqld/mysqld.sock is coming from. Thanks.


texts: MySQL is pretty slow after 50-100k rows whats happen?

texts:
i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues.

i testing it of on a developer server ( 2 cores, 4gb ram ) but i still got issues problems, here is my stored procedure i have typed.

DROP PROCEDURE IF EXISTS insertDistributorProduct;

DELIMITER $$

CREATE PROCEDURE insertDistributorProduct(
    distributorUUID VARCHAR(36),
    distributorTitle VARCHAR(256),
    distributorDescription TEXT,
    distributorSKU VARCHAR(128),
    manufacturer VARCHAR(64),
    manufacturerSKU VARCHAR(128),
    productEan BIGINT(20),
    productCostPrice INT(11),
    productStock INT(11),
    productStockExpected DATE
)
BEGIN
    DECLARE _manufacturerUUID VARCHAR(36) DEFAULT NULL;
    DECLARE _distributorProductUUID VARCHAR(36) DEFAULT NULL;

    SET _manufacturerUUID = ( SELECT m.manufacturerUUID FROM manufacturer m
            WHERE LOWER(m.manufacturerTitle) = LOWER(manufacturer) LIMIT 1 );

    -- If manufacturer not exists insert it
    IF ( _manufacturerUUID IS NULL ) THEN

        SET _manufacturerUUID = ( SELECT ma.manufacturerUUID FROM manufacturerAlias ma
                WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer) LIMIT 1 );

        -- _manufacturerUUID not found in manufacturer and _manufacturerAlias so insert
        IF ( _manufacturerUUID IS NULL ) THEN

            SET _manufacturerUUID = UUID();

            INSERT INTO manufacturer(manufacturerUUID,manufacturerTitle,added)
            VALUES(_manufacturerUUID,manufacturer,NOW());

        END IF;

    END IF;

    SET _distributorProductUUID = ( SELECT dp.distributorProductUUID FROM distributorProduct dp
            WHERE dp.distributorUUID = distributorUUID
              AND LOWER(dp.distributorSKU) = LOWER(distributorSKU) );

    -- Update distributorProduct if its all ready found
    IF ( _distributorProductUUID IS NOT NULL ) THEN

        UPDATE
            distributorProduct dp

        SET
            dp.distributorTitle = distributorTitle,
            dp.distributorDescription = distributorDescription,
            dp.manufacturerUUID = _manufacturerUUID,
            dp.manufacturerSKU = manufacturerSKU,
            dp.productEan = productEan,
            dp.productCostPrice = productCostPrice,
            dp.productStock = productStock,
            dp.productStockExpected = productStockExpected,
            dp.updated = NOW(),
            dp.activated = 1

        WHERE
            dp.distributorProductUUID = _distributorProductUUID

        LIMIT
            1;

    -- Insert new product to distributorProduct if not exists.
    ELSE

        INSERT INTO distributorProduct (distributorProductUUID,distributorUUID,manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,added)
        VALUES(UUID(),distributorUUID,_manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,NOW());

    END IF;

END
$$

My problem is that every time I get over 50-100k rows, this script takes longer and longer time, and I have to try to look up my index but still nothing happens.

how can I debug and perfume my database more? every day i need to run this producer like 1-1,4milion times between 4-5 hours max, its update all our products but right now it's useless.

The first time 60k rows takes 15min, and after that 60k more ( total 120k ) this time its taken like 30-45min to run, and I still don't know why it happened.  What did I do wrong?

Everything I tested here is running in virtual box software, I don't have to test this on real hardware.


texts: MySQL 5.7 not accepting values of innodb_buffer_pool_instances greater than 1

texts:
I've been tweaking the values of the buffer pool size (innodb_buffer_pool_size) and the number of buffer pool instances (innodb_buffer_pool_instances) in the my.ini initialization file to optimize heavy load handling with MySQL. I can successfully change the buffer pool size, both dynamically and with the my.ini file, but no matter what I change the innodb_buffer_pool_instances value to, it is always 1 when I query it with SELECT @@innodb_buffer_pool_instances;

What I've already tried:

I made sure to set the buffer pool size > 1GB. I've set it to 24GB and verified that it is indeed that big.
Made sure I'm using the correct my.ini file location.
After changing the value in my.ini I restart the MySQL server.
I've even tried restarting my computer after changing the value, but it still always remains 1.

What am I missing? Why can't I change the value of innodb_buffer_pool_instances?


texts: What happens if agent job owner account is deactivated in Active directory?

texts:
We have a number of jobs owned by a DBA.
The DBA is now leaving the company so their account will be deactivated in Active Directory.

What affect will this have on the agent jobs?


texts:
I'm about to refactor inherited scripts deployed on multiple servers that backup many other servers. They are a mixture of cmd, vbs and sql. I'm pondering what benefits would bring doing it with Python since I find it friendlier to script?
Mainly i'm thinking that with PS I can use something that could be considered native like Invoke-Sqlcmd or sqlps.
Is it just a matter of taste or might I stumble upon something nasty and unforeseen by using python?


texts: Upgrade PostgreSQL 9.3 without moving the DB and messing PostGIS on OSX OpenGeo Suite original installation

texts:
I looked for the answer, but could not find a specifically fitting one, so would appreciate an answer or a pointer to one...

Q: How do I upgrade PostgreSQL 9.3  to the current (now 9.6 ?) without having problems with PostGIS (mine is 2.1.7 and I would like to upgrade to the current PostGIS 2.3.2 as well)?

My configuration is as following:

select version();
PostgreSQL 9.3.4 on x86_64-apple-darwin14.3.0, compiled by Apple LLVM version 6.1.0 (clang-602.0.53) (based on LLVM 3.6.0svn), 64-bit

psql --version
psql (PostgreSQL) 9.5.3

SELECT PostGIS_full_version();
postgis_full_version
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="2.1.7 r13414" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.2, released 2015/02/10 GDAL_DATA not found" LIBXML="2.9.1" LIBJSON="UNKNOWN" TOPOLOGY RASTER
(1 row)

About PostGIS:OpenGeo Suite 4.8 PG-9.3.4 PostGIS-2.1.7

OSX Yosemite 10.10.5, MacBook Pro (15-inch, Early 2011), MacintoshSSD + HD hybrid installation, system on the SSD, 8GB memory

I originally installed via Boundless OpenGeo Suite 4.8 (it was free at the time) but cannot find anything about upgrading except this general page. I suppose they went commercial.

Q2:Is there a free alternative, preferably without having to redefine and move everything?

Q3: Is following the Upgrading PostGIS instructions going to mess my currently working install?

The idea is to have a free and powerful GIS-PostgreSQL bundle that works fast on big spatial data sets. Preferably something that students could install on their own. As of now we do visualisations of results via QGIS.


texts: How to manage many-to-many relationships when there is also a hierarchy among the many?

texts:
I have these entities – employee, task, phase, project – and these relationships:

project 1:m phase
phase 1:m task
employee m:m task, phase, project

The first two relationships form a simple part-of hierarchy.

The semantics of the third relationship are slightly complex.
An employee, in any given week, can be assigned to any or all of the following:

zero or more tasks
zero or more phases
zero or more projects

(Being assigned to nothing at all, though, is not recorded!)
In other words, the level of detail of an employee assignment can vary.
Semantically, at least, it is impossible to be assigned to a "part" entity without also being assigned to the greater entity. However, being assigned to a "whole" does not imply assignment to any of the "parts".

My current table design is to have a table for each entity and a junction table for the m:m relationship:

employee   [emp_id,   etc]
task       [task_id,  phase_id, etc]
phase      [phase_id, proj_id,  etc]
project    [proj_id,  etc]
assignment [emp_id,   task_id,  phase_id, proj_id, week]

However, given that a task always belongs to a phase and a phase always belongs to a project, how should the foreign keys in assignment be valued? Do I choose one of these?

Only the id corresponding to the lowest known entity in the project-phase-task hierarchy gets a value, the higher entity ids get nulls.
Problem: Those nulls don’t seem right.
All the ids the corresponding to the lowest known entity and up get values.
Problem: The non-null ids seem redundant (non-normalized).

Or should assignment be like this

[emp_id, assign_id, level, week]

where level indicates to which table (task/phase/project) the generic assign_id refers?
Problem: Theoretically, assign_id might need to be of three different types.
(Though, actually, they're all long integers, in my situation.)

Is this a “design pattern”? That is, is it an example of semantics that the relational DB model doesn’t readily handle?
Does it have a name?
What is the best practice for handling the situation?

I'm using MS Access but I'm interested to know how solutions might vary across different relational DBMSs.


texts:
I'm using lead() to calculate the difference in values between two records in the same table.  This works fine until I need to not report on one of the records based on some other value in the window.

Given the following table.

>  t_id   |     update_time     |     last_reset      | bytes_tx
> ----------+---------------------+---------------------+-----------
> 3680000 | 2019-04-28 19:58:05 | 2019-02-21 15:08:27 | 188960431
> 3680000 | 2019-04-28 20:13:06 | 2019-02-21 15:08:27 | 188991801
> 3680000 | 2019-04-28 20:28:08 | 2019-02-21 15:08:27 | 189022965
> 3680000 | 2019-04-28 20:43:07 | 2019-02-21 15:08:27 | 189057066
> 3680000 | 2019-04-28 20:58:06 | 2019-04-19 12:18:17 | 89088869
> 3680000 | 2019-04-28 21:10:11 | 2019-04-19 12:18:17 | 89114302
> 3680000 | 2019-04-28 21:13:03 | 2019-04-19 12:18:17 | 89120288
> 3680000 | 2019-04-28 21:28:05 | 2019-04-19 12:18:17 | 89151994
> 3680000 | 2019-04-28 21:43:06 | 2019-04-19 12:18:17 | 89183203
> 3680000 | 2019-04-28 21:58:03 | 2019-04-19 12:18:17 | 89215093

I can get what I want from this using lead() to report the difference of the current record, and the upcoming record.

select
    lead(update_time,1) over(partition by t_id order by update_time) - update_time as interval,
    lead(bytes_tx,1) over (partition by t_id order by update_time) - bytes_tx as bytes_diff
from table

 interval | bytes_tx
----------+----------
 00:15:01 |    31370
 00:15:02 |    31164
 00:14:59 |    34101

That works well right up until last_reset changes, which zeros my bytes_tx count resulting in a negative integer being returned.

I'd like to simply skip records where lead(last_reset) isn't equal to current last_reset, but I can't use a window function in the WHERE clause.

I'm essentially looking for the equivalent of

select
    lead(update_time,1) over(partition by t_id order by update_time) - update_time as interval,
    lead(bytes_tx,1) over (partition by t_id order by update_time) - bytes_tx as bytes_diff
from
   table
where
  lead(last_reset,1) over (partition by t_id order by update_time) = last_reset

And I'm pretty stumped right now.

Any clues for the clueless?


texts: WHEN MATCHED clause fails if there's a NULL in the ON columns

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

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

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

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

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

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

Example:


texts: Encryption/Decryption with stored procedure / view

texts:
I'm working on a project that uses Entity Framework for programmers to access the data. We've recently found need to add fields that were determined that need to be encrypted. This will be the first fields that require encryption.

We plan on using Symmetric Key for cell level encryption for that specific field. For the developers to continue to use Entity Framework, I wanted to make a stored procedure to handle inserts for this table which would encrypt the cell needed, and a View to open the key and decrypt the field.

I plan on encrypting the stored procedure & view so the meta data isn't visible by looking at the definition.

Is this a sound plan? Am I overlooking anything?


texts:
I wrote this function that is supposed to iterate through a text array and convert each element (which is a typecasted integer) to hex, append to a text variable, then return the hex. However, I get nothing back.

Does anybody see any issues with the function that would cause it not to return anything?

CREATE OR REPLACE FUNCTION array_convert_to_hex(text[])
RETURNS text
AS
$$
DECLARE
   arrInts ALIAS FOR $1;
   retVal text;
BEGIN
   FOR I IN array_lower(arrInts, 1)..array_upper(arrInts, 1) LOOP
    retVal := retVal || lpad(cast(to_hex(arrInts[I]::integer) as varchar), 2, '0');
   END LOOP;
RETURN retVal;
END;
$$
LANGUAGE plpgsql
   STABLE
RETURNS NULL ON NULL INPUT;


texts: Select date based on the daylight saving periods

texts:
Imagine that I have this table:

   Id   |      Name       |       Timestamp
________________________________________________
    1   | Bill Gates      | 2017-01-10 12:00:00
________________________________________________
    2   | Mark Zuckerberg | 2017-07-23 17:00:00

If I use a simple query during the winter time like this:
SELECT Timestamp FROM TableName WHERE name="Bill Gates" and try to print it using PHP or any other language I will get this output "2017-01-10 12:00:00".
If I use the exact same query but during the summer time (DST) I get this output "2017-01-10 13:00:00"

The Question: Is there any way to select the timestamp using the correct DST for each row based on the timestamp?
For example, if I select both rows from the table above, I will get the timestamp from line 1 as winter time and the timestamp from the second line as summer time. Can this be done with SQL only?

Thanks.


texts: update column based on sum of data from another table

texts:
I have two tables :

Table1 (col1, col2, col3, col4, val1, status)
Table2 (col1, col2, col3, col4, val2)

For Table1 and Table2, The columns (col1, col2, col3, col4) are the composite primary key.

Table2 may have duplicated rows, for that reason I would like to group by (col1, col2, col3, col4) and sum(val2)

After that, I would like to update Table1.val1 by the value of sum(Table1.val2) when Table1.col1 = Table2.col1 and Table1.col2 = Table2.col2 and Table1.col3 = Table2.col3 and Table1.col4 = Table2.col4 and status= 'V'.

I did something like that:

UPDATE Table1
SET val1 = (
  select t_sommevbrute.sumvalbrute
  from (
    Select  col1,col2,col3,col4,SUM(val2) sumvalbrute
    From Table2
    Where col3 = 2014
      And col2=51
      And status= 'V'
    GROUP BY col1, col2, col3, col4
    ) t_sommevbrute
  WHERE Table1.col1 = t_sommevbrute.col1
    and Table1.col2 = t_sommevbrute.col2
    and Table1.col3 = t_sommevbrute.col3
    and Table1.col4 = t_sommevbrute.col4)

Could someone help me please?
Thank you


texts: Tool to create a TSQL script from a SQL Server 2008 trace file

texts:
Are there any tools available to create an executable tsql script from a SQL Server trace file?

Is there an easy manual way?


texts:
I tried to start the listener and do a dbstart, but it looks like it is still saying "Oracle Not available". Maybe the dbstart didn't work. Any suggestions would help me.

[user@fedora16 bin]$ echo $ORACLE_HOME
/home/user/app/user/product/11.2.0/dbhome_1
[user@fedora16 bin]$ echo $ORACLE_SID
orcl
[user@fedora16 bin]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /home/user/app/user/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

ADR_BASE_LISTENER = /home/user/app/user/product/11.2.0

[user@fedora16 bin]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /home/user/app/user/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /home/user/app/user/product/11.2.0

[user@fedora16 bin]$ dbstart $ORACLE_HOME
[user@fedora16 ~]$ lsnrctl start orcl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAR-2012 09:04:19

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

Starting /home/user/app/user/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /home/user/app/user/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /home/user/app/user/product/11.2.0/diag/tnslsnr/fedora16/orcl/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     orcl
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                04-MAR-2012 09:04:19
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/user/app/user/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/user/app/user/product/11.2.0/diag/tnslsnr/fedora16/orcl/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully

[user@fedora16 bin]$ ps -ef | grep  '\(ora\|smon\|lsnr\)'
user  2405     1  0 08:46 ?        00:00:00 /home/user/app/user/product/11.2.0/dbhome_1/bin/tnslsnr orcl -inherit
user  2553  1611  0 08:49 pts/0    00:00:00 grep --color=auto \(ora\|smon\|lsnr\)

[user@fedora16 bin]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 4 08:46:29 2012

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

Enter user-name: scott
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0


texts:
I'm sure someone must have hit this or a problem like this before but I'm not finding anything on a google or DBA exchange search.

I have a table storing key/value pairs.  There are also 2 limiting columns in the table, host and environment, both nullable. The way that the limiting tables work is that if there is an entry in the host column then the key/value applies to that host, similarly if there is an entry in the environment column then that entry takes effect only for that environment. If a key/value is sought with a host or environment then the limited values take precedent over the values where host=NULL or environment=NULL.  Furthermore, host takes precedence over environment.

So as an example, here are some table entries:

id      env     host     key    value
1       NULL    NULL     fruit  apple
2       prod    NULL     fruit  banana
3       prod    www      fruit  mango
4       test    NULL     fruit  peach
5       NULL    test     fruit  orange

Given that example data, what I'm after is a SQL statement (or statements) that will return these data based on these criteria:

env     host   key    data returned
NULL    NULL   fruit  1, fruit, apple
prod    NULL   fruit  2, fruit, banana
NULL    www    fruit  3, fruit, mango
junk    junk   fruit  1, fruit, apple  // NULL/NULL values are used as a fallback
junk    www    fruit  1, fruit, apple  // no match, fallback
prod    test   fruit  5, fruit, orange // host=test takes precedence

Does that make sense?

I can break the search up into multiple selects, try each one, check to see if I get a hit, etc, but I'm hoping to achieve this as efficiently as possible.

Ideally I would like the type of search that would be easily extensible if more limiters are added later (e.g. group, namespace, etc).

Anyone?


texts:
In SQL Server I used the following code to drop a table with a constraint:

IF OBJECT_ID('EMPLOYEES') IS NOT NULL
  BEGIN
         ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK
         DROP TABLE EMPLOYEES;
  END

How can I accomplish the same thing in Mysql?


texts: SQL regex to identify alphanumeric values

texts:
I am trying to exclude values like 3708.DK in sql. I'm using pattern like LIKE '%0-9.%' and '%[0-9.A-Z]%' and its not working .

Please suggest.


texts:
Brand new to databases, using postgresql on linux. I've done a decent amount of reading on the issue and looked through similar questions on here, but just want to confirm the most efficient way of organizing everything.

So say for example we have entries for a million people, who each have a name, phone number, address, and between 20 to 50 "skills". Would the correct way be to have one table with the basic person properties along with a unique identifier, and then create a second table with two columns containing the person's ID and skill name?

Seems a little redundant to me, but the other option of having a variable number of skill columns in the person table seems even worse.

Any tips to maximize speed and minimize memory usage?


texts: Altering table Foreign Keys produces an error (errno:150) (MySQL)

texts:
This is the first database I set up so I apologize if my problem is something obvious.

I'm playing around with my table design and tried to create some link tables with FKs to avoid redundant data. When I try to sync my EER model with the DB I get an error which makes no sense to me.

Executing SQL script in server
ERROR: Error 1005: Can't create table 'jonijz1_Test.#sql-d7d_1291db' (errno: 150)

ALTER TABLE `jonijz1_Test`.`UserGroupLink`
ADD CONSTRAINT `fk_UserGroupLink_UserGroup`
  FOREIGN KEY (`UserGroupID`)
  REFERENCES `jonijz1_Test`.`UserGroup` (`UserGroupID`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_UserGroupLink_Users1`
  FOREIGN KEY (`UserID`)
  REFERENCES `jonijz1_Test`.`Users` (`UserID`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION

SQL script execution finished: statements: 5 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

What am I doing wrong?
And on a sidenote: would a design like this make any sense or am I approaching this wrong as well?


texts: How can I set columns for an entire table in one UPDATE based on the SUM of a join table column?


texts: MS SQL "migration" to older version - problem with Attach

texts:
On SQL 2019 I try to change compability level to 2008 (to be sure that I will be able to attach on older version, at least I was in hope this will be possible).

I detach this db.
When I try to attach on destination SQL I still get

As so far I found few related topics like this following:
Restore DB to older version
But there are talks about Restoring backup, but I ask about Detach/Attach and compability level.
So Here are my questions:
Question 1: Is there any way to attach newest mdb file on older SQL server ?
if NO
Question 2: What for is feature "Compatibility level" ?
After reading both following ansers I have:
Question 3: Do you think that with tools like dbeaver or devart it will be possible to export/import data between NEW<>OLD SQL versions , or would you propose other tools for this kind of job to do ?


texts:
I´m currently evaluating pgbarman for production use.

I set up a testserver (postgres 9.6) and barman using synchronous streaming backup.
I know the synchronous streaming works, the WAL file in ~barman/server/streaming is getting updated.

I can recover using barman recover but in the output only transactions until the point where postgres switched the xlog file are present.
So streaming is enabled, I write something to the DB and then I recover, the last write is lost.
If I write, then issue pg_switch_xlog() and recover, the write is there.

From this I conclude that the info in the currentlu "open" WAL file is not present in the backup.

How can I get barman to include information from a WAL file that was not switched by postgres at the time of the database failure the in the backup?

Edit: I found out that I can get the information into my recovery directory by putting the partial WAL file into the pg_xlog directory. I´d still like a "clean" solution though...


texts:
I was in the process of installing SQL Server 2012 x64 Express Management Studio and encountered one heck of an issue.

I was most of the way through the installation process when my computer locked up and I had to do a hard restart.

Now, the computer seems to think that I have components of SQL Server 2012 installed when in reality I do not.

I tried going to the control panel and removing the components but most of the way through the removal process I get the following error:

  "Microsoft SQL Server 2012 Service Pack 1 Setup

  The following error has occurred:

  SQL Server Setup has encountered an error when running a Windows Installer file.

  Windows Installer error message: This installation package could not be opened. Contact the application vendor to verify that this is a valid Windows Installer package.

  Windows Installer file: {A7037EB2-F953-4B12-B843-195F4D988DA1}
  Windows Installer log file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log>\20140813_121519\sql_ssms_Cpu64_1.log

  Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.
  For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.3128.0&EvtType=0xDC80C325"

I've spent a couple hours on several days looking around for a fix to this but I haven't been able to locate a solution as of yet.


texts: administrator permissions on a SSAS database

texts:
I had a third party company coming over and building ssas databases to produce reports.

They did, but now the DBA wants to have a look but I haven't got permissions, to even see those databases, as you can see on the picture below.

Can I grant myself administrator permissions?

I am system administrator. there is a AD group called SQLDBA and

When right-cliking on the server-properties-security-Add-
then adding my AD group SQLDBA it says I don't have the permissions to alter the server object as you can see on the picture below.

I had previously added that sqldba AD group as an administrator to that box.

TITLE: Microsoft SQL Server Management Studio

Either the 'mycompany\MMiorelli' user does not have permission to alter the 'myserver\instance' object, or the object does not exist.
 (Microsoft.AnalysisServices.AppLocal.Core)

BUTTONS:

OK


texts: count duplicate records and select first 2

texts:
I have troubling issue where I want to only select the first 2 chunks of every dbspace created.
query used:
SELECT
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
GROUP BY
1,2,3,4
ORDER BY 1,2

Output:
dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

dbsnum  1
chknum  965
name    rootdbs
fname   /dev/informix/chunk1280

dbsnum  1
chknum  1099
name    rootdbs
fname   /dev/informix/chunk1281

What I want is:
dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

I am trying to put logic together to select the first 2 chunks created for example from each dbspace that exists. rootdbs has a total of 4 chunks I only want the first 2.
I can achieve this using a korn shell script:
for i in `echo "output to pipe cat without headings select unique(dbsnum) from sysdbspaces" | dbaccess sysmaster 2> /dev/null | sed '/^$/d'`
do
fname=`echo "output to pipe cat without headings select a.fname from (select
first 2
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
AND a.dbsnum = '$i'
--AND a.is_temp = 1
GROUP BY
1,2,3,4
ORDER BY 1,2) a" | dbaccess sysmaster 2> /dev/null | sed '/^$/d'`
echo "$fname"
done


texts: Give alias / column definition list to ROWS FROM()

texts:
This obviously doesn't work:
SELECT regexp_matches[1], regexp_matches[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
);

Error: [42702] column reference "regexp_matches" is ambiguous

How can I give an alias to the function calls?
SELECT u[1], l[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
) AS (u text[], l text[]);

Error: [42601] ROWS FROM() with multiple functions cannot have a column definition list
Hint: Put a separate column definition list for each function inside ROWS FROM().

SELECT u[1], l[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g') AS (u text[]),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g') AS (l text[])
);

Error: [42601] a column definition list is only allowed for functions returning "record"

I'm out of ideas. This has to be possible.
Documentation:

Syntax of ROWS FROM() (7.2.1.4. Table Functions)
regexp_matches returns setof text[] (Table 9.9. Other String Functions)


texts:
I have a table like

id product qty
1  apples  3
2  oranges 1

I want to get a query that will return

1  apples  1
1  apples  1
1  apples  1
2  oranges 1

How do I accomplish that? I've seen several answers on splitting a column into rows like "a,b,c" but not on qty like this. Any help is appreciated.


texts: Does a replica set with only two machines make sense?

texts:
A lot of MongoDB tutorials, when they talk about replica sets, give examples of two machines: one originally initialized as primary, and another one originally created as secondary.

As I understand it:

If a primary member unexpectedly dies, the secondary one won't elect itself the new primary anyway, since it won't have the majority (which is two in a replica set of two machines).
It is impossible to read the data from the database if the primary member is unavailable, even with read_preference=ReadPreference.SECONDARY.
Recreating the primary member won't work anyway: it cannot be added to the existent replica set, since it lacks the primary member; it cannot rs.initiate(); either, since it will create a different replica set with a different ID.

Therefore, is there a real life situation where having only two members in a replica set would make any sense? Or such situation is only theoretical, and one would expect to see at least three members in every replica set in practice?


texts: Getting "not a single-group group function"

texts:
Below is the query that I am running, but I am getting not a single-group group function error? Please explain why is this error coming and a possible solution to this.

SELECT 1                          AS "Contract",
  COUNT(DISTINCT(cmv.mdl_ver_id)) AS "Number Of Scenarios",
  COUNT(DISTINCT(cmvs.site_id))   AS "Number Of Sites",
  CASE
    WHEN cmve.driver_equip_id is null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END                             AS "Number of Driven",
  CASE
    WHEN cmve.driver_equip_id is not null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END                             AS "Number of Driver"
 FROM cost_contract_mdl ccm,
  cost_model_ver cmv,
  cost_mdl_ver_site cmvs,
  cost_mdl_ver_equip cmve
WHERE ccm.mdl_id   =cmv.mdl_id
AND cmv.mdl_ver_id =cmvs.mdl_ver_id
AND cmv.mdl_ver_id =4281021;


texts: Debugging Galera TX conflicts when no SQL is logged

texts:
I'm trying to debug an intermittent transaction conflicts in my cluster.

With the debug output enabled, I'm getting following logged:

*** Priority TRANSACTION:
TRANSACTION 1691804310, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
1 lock struct(s), heap size 360, 0 row lock(s)
MySQL thread id 2, OS thread handle 0x7ff60d7ff700, query id 92862321 Update_rows_log_event::find_row(123
304972)
UPDATE `media_mediafile` SET `name` = '_Ready_for_Client_Review', `volume_id` = 3, `parent_id` = 1204347, `path` = '.projects/xxx', `pathhash` = '2370382906', `is_dir` = 1, `total_files` = NULL, `size` = 2048, `mtime` = 1513689903, `present` = 1, `needs_rescan` = 1, `unscannable` = 0, `info` = '{\"thumbnails\":[]}', `is_showroom` = 0, `custom_fields` = '{}', `bundle_id` = NULL, `bundle_index` = 0 WHERE `media_mediafile`.`id` = 1204359

*** Victim TRANSACTION:
TRANSACTION 1691804283, ACTIVE 1 sec 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 502342, OS thread handle 0x7ff61c1d9700, query id 92862280 localhost root cleaning up
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 590 page no 3074 n bits 120 index `PRIMARY` of table `xxx`.`media_mediafile` trx table locks 1 total table locks 3  trx id 1691804283 lock_mode X locks rec but not gap lock hold time 1 wait time before grant 1

180102 17:53:04 [Note] WSREP: cluster conflict due to high priority abort for threads:

180102 17:53:04 [Note] WSREP: Winning thread:
   THD: 2, mode: applier, state: executing, conflict: no conflict, seqno: 123304972
   SQL: UPDATE `media_mediafile` SET `name` = '_Ready_for_Client_Review', `volume_id` = 3, `parent_id` = 1204347, `path` = '.projects/xxx', `pathhash` = '2370382906', `is_dir` = 1, `total_files` = NULL, `size` = 2048, `mtime` = 1513689903, `present` = 1, `needs_rescan` = 1, `unscannable` = 0, `info` = '{\"thumbnails\":[]}', `is_showroom` = 0, `custom_fields` = '{}', `bundle_id` = NULL, `bundle_index` = 0 WHERE `media_mediafile`.`id` = 1204359<F0><B8>KZ^S

180102 17:53:04 [Note] WSREP: Victim thread:
   THD: 502342, mode: local, state: idle, conflict: no conflict, seqno: -1
   SQL: (null)

It's clear that the second TX is:

  UPDATE `media_mediafile` SET `name` = '_Ready_for_Client_Review', `volume_id` = 3, `parent_id` = 1204347, `path` = '.projects/xxx', `pathhash` = '2370382906', `is_dir` = 1, `total_files` = NULL, `size` = 2048, `mtime` = 1513689903, `present` = 1, `needs_rescan` = 1, `unscannable` = 0, `info` = '{\"thumbnails\":[]}', `is_showroom` = 0, `custom_fields` = '{}', `bundle_id` = NULL, `bundle_index` = 0 WHERE `media_mediafile`.`id` = 1204359<F0><B8>KZ^S

but I'm unable to identify the first transaction since no SQL is logged. There's a lock description, but I'm not explicitly locking rows in any part of my app.


texts: Default Postgres privileges not showing on \ddp

texts:
As far as I understand there are set of default Postgres public privileges. I have created user and granted him privilege using:

CREATE USER foo WITH PASSWORD 'foo';
\connect bar;
GRANT SELECT ON ALL TABLES IN SCHEMA public to foo;

and I can connect to DB with no issues. Then I taught I must have connection privilege in the default privilege set, since I didn't grant connect on database explicitly.

When I do \ddp I get 0 rows, and as far as I understood it is a way of checking default privileges per this page.

Can someone help me understand how connecting to the DB is permitted when I haven't explicitly allowed it?


texts: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. not consistent

texts:
We have seen this problem before and fixed it by looking at the KERBOS configuration program. But now I have something odd. Both I and another DBA have the same SysAdmin perms to both boxes. But when we are on the same server trying to run a query off the same linked server, mine works and yet his gives him the error above. What can be causing that? I would have assumed if all things are equal we'd either both be successful or both get the same error.

This is when we are running it in SSMS from our Terminal server. But when we both actually RDP onto the server we are running this from (not the linked server server) it works fine without any problems.

The security has nothing in the mapping and the Be made using the login's current security context is checked. Here is the create statement for the linked server:

@srvproduct=N'SQL Server'
@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
@optname=N'collation compatible', @optvalue=N'false'
@optname=N'data access', @optvalue=N'true'
@optname=N'dist', @optvalue=N'false'
@optname=N'pub', @optvalue=N'false'
@optname=N'rpc', @optvalue=N'true'
@optname=N'rpc out', @optvalue=N'true'
@optname=N'sub', @optvalue=N'false'
@optname=N'connect timeout', @optvalue=N'0'
@optname=N'collation name', @optvalue=NULL
@optname=N'lazy schema validation', @optvalue=N'false'
@optname=N'query timeout', @optvalue=N'0'
@optname=N'use remote collation', @optvalue=N'true'
@optname=N'remote proc transaction promotion', @optvalue=N'true'


texts: SQL Query to select all under certain age using only date of birth

texts:
How can I select all that are under 20 and female from a SQL database?

The table has a datetime for the dob and strings for name, surname, and sex. That is the whole table.

I am having trouble calculating the age. I am not even sure this is possible.

I am using SQL Server LocalDB and Visual Studio 2015.


texts: How is it possible that a key of char+bigint has better performance than key of bigint+bigint?

texts:
I would like to store some user related data into a table. The data is identified by MD5 hash.

I have 2 options:

option 1 -
have a table with 2 columns: hash_id (unsigned bigint) and md5 (char(32)). Store the actual data in another table where the key is hash_id (unsigned bigint) + user_id (bigint).

option 2 -
Store the data in a table where the key is md5 (char(32)) + user_id (bigint).

Option 1 looks more "normalized", but I prefer option 2 so I wanted to know if using it will cause a major performance penalty.

In order to check, I defined both tables and tried the following operations:

Insert 100K entries to an empty table
Use 10K ids/MD5s that already exist in the table and for each of them run a select query and an update query (to imitate scenario in which I want to set data in an entry that already exist so I need to update it).
Use 10K ids/MD5s that don't exist in the table and for each of them run a select query and an insert query (to imitate scenario in which I want to set data in an entry that doesn't exist so I need to create it).
Delete 10K existing entries

In all tests besides the last one, the results for the second table (the one with the actual MD5) were much better than results for the first (the one with the hash_id).

How is it possible? I assumed that using a char key, which is longer - 32 bytes, would be worse than using a bigint - 8 bytes. What am I missing here?

I use MySQL + InnoDB.

Thanks.


texts:
I'm trying to upgrade PostgreSQL on Ubuntu Server from 9.6 to 10. This is the first time I'm doing this. The server doesn't contain any useful data yet but I want to make sure I can do this properly next time.

I followed the instructions in the manual. Here's what I did:

Installed the new packages for version 10
Stopped both versions on the server
Ran the pg_upgrade script:

sudo -u postgres /usr/lib/postgresql/10/bin/pg_upgrade -b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/10/bin -d /var/lib/postgresql/9.6/main/ -D /var/lib/postgresql/10/main/

This is the console output:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/lib/postgresql/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.6/main/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

This is the contents of pg_upgrade_server.log:

command: "/usr/lib/postgresql/9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.6/main/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....postgres: could not access the server configuration file "/var/lib/postgresql/9.6/main/postgresql.conf": No such file or directory
 stopped waiting
pg_ctl: could not start server
Examine the log output.

I'm totally confused now. What should I do?


texts: Database normalization, should I combine these two tables?

texts: I am sort of new to database normalization and working with a database that I did not create. The database has a table for users, and a separate table for user addresses. Both tables use the user id as their primary key, and every user is required to have an address. Since every user is required to have an address, should the information be added to the user table for proper normalization?


texts: Exporting/Importing data from SQL Server 2014 to PostgreSQL 9.5 with tab delimited files

texts:
Not sure if anyone has run into this problem before and I am going a little bit crazy.

I am exporting a table from SQL Server 2014 using BCP to a tab delimited text file and then uploading the file to PostgreSQL 9.5 using COPY.

exec master..xp_cmdshell 'bcp tempdb.dbo.##MyTable out "C:\BCPFiles\MyTableFile.txt" -c -t -T'

This works fine.

create temp table mytable (col1 int, col2 varchar(100), col3 varchar(100));
COPY mytable FROM '/tmp/MyTableFile.txt' WITH (FORMAT text);

ERROR:  invalid input syntax for integer: "1    text1      text2"

It looks to me like COPY doesn't recognize the tab, even though when I view the file it is tab delimited. There are no NULL values in this file btw.

CSV is not a good option for me because of the text has commas in it and from what I am reading PG doesn't like double quotes


texts: Nonclustered index is faster than clustered index?

texts:
Both tables have same structure and 19972 rows in each table.
for practicing indexing, i created both tables having same structure and created

clustered index on persontb(BusinessEntityID)

and

nonclustered index on Persontb_NC(BusinessEntityId)

and table structure

BusinessEntityID int
FirstName varchar(100)
LastName  varchar(100)

 -- Nonclusted key on businessentityid takes 38%
SELECT  BusinessEntityId from Persontb_NC
WHERE businessentityid BETWEEN 400 AND 4000

-- CLustered key businessentityid takes 62%
SELECT BusinessEntityId  from persontb
WHERE businessentityid BETWEEN 400 AND 4000

Why clustered index takes 62% and non clustered 38%?


texts: Is it recommended to have redundant foreign key columns?

texts:
Imagine I have the models A, B, C e D, where A have many Bs, B have many Cs and C have manyDs:

Without redundance

A
|  id | ... |
------------
| ... | ... |

B
|  id | a_id | ... |
--------------------
| ... |  ... | ... |

C
|  id | b_id | ... |
--------------------
| ... |  ... | ... |

D
|  id | c_id | ... |
--------------------
| ... |  ... | ... |

Would be recommended to have more columns in C e D with the reference to A and B?

With redundance

C
|  id | a_id | b_id | ... |
---------------------------
| ... |  ... |  ... | ... |

D
|  id | a_id | b_id | c_id | ... |
----------------------------------
| ... |  ... |  ... |  ... | ... |

It's a redundance, but I usually do this to make simpler queries, I can make less JOINs. I think it probably have better performance too.

Is it recommended? (At least when the columns are immutables) Is there a better solution for this?


texts: RMAN rename datafile created in an archivelog

texts:
I'm trying to restore an Oracle database (11.2.0.4.0) to a test system with a different file layout. The production database is being backed up using RMAN, no catalog. We do a full backup of all the datafiles on Sunday (last one being Mar 9th) and then only backup the archivelogs every night during the week.

On 3/9 we had 11 datafiles. On 3/12 I created a new tablespace/datafile. When doing the restore today my general process has been:

Install Oracle software only, no initial database
Restore the SPFILE to a PFILE from the 3/9 backup and edit it. This is where I change the DB_DOMAIN and the new locations of the control files on the test server.
Startup nomount with the new PFILE.
Restore the control file from the 3/9 backup.
Mount the database. Using sqlplus I rename the redo logs to their new location.
Since the restored control files aren't aware of the backup pieces for 3/10 - 3/14, I run this for each missing day: CATALOG BACKUPPIECE '/path/to/backuppiece.bkp'
Finally I run this RMAN command to restore/recover the database (getting the SCN from LIST BACKUPSET):

RUN
{
SET UNTIL SCN 426265698;
SET NEWNAME FOR DATAFILE 1 TO '/var/oracle/data/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/var/oracle/data/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/var/oracle/data/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/var/oracle/data/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/var/oracle/data/orcl/foo01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/var/oracle/data/orcl/foo02.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/var/oracle/data/orcl/foo03.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/var/oracle/data/orcl/foo04.dbf';
SET NEWNAME FOR DATAFILE 9 TO '/var/oracle/data/orcl/foo05.dbf';
SET NEWNAME FOR DATAFILE 10 TO '/var/oracle/data/orcl/foo06.dbf';
SET NEWNAME FOR DATAFILE 11 TO '/var/oracle/data/orcl/foo07.dbf';
SET NEWNAME FOR DATAFILE 12 TO '/var/oracle/data/orcl/foo08.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

So this process generally has been working fine; datafiles present in the 3/9 backup restore to their new location. The problem is the tablespace I created on 3/12. When the recovery process gets to that point, RMAN outputs:

creating datafile file number=12 name=/opt/oracle/oradata/orcl/foo08.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/14/2014 13:24:41
RMAN-20505: create datafile during recovery
ORA-01119: error in creating database file '/opt/oracle/oradata/orcl/foo08.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

Now, I know that I could work around this by just creating the directory at the old location, letting RMAN restore into it, and moving the datafile later. But I'd like to understand why my last rename command isn't working, or what I need to change to get it to rename when restoring the archivelog.


texts: create database table with a owner in my SQL

texts: Can someone tell me what is the SQL command to create a table with specific owner?


texts: Database for efficient retrieval of data from large data set

texts:
I am designing a new system that will be dealing with large data set. This system is expected to have over 10 million records. A user of the system needs to check for the availability of the name he wants to register. If nothing is returned, that means the name is available, the user can then proceed to register the name.

The database needs to be optimized for search. I was considering using mysql, but i don't know if there are better options. Thanks


texts: Get one row for each value in xml column

texts:
I have a table, called product_variants, with one integer column and one xml column containing a set of foreign key values.

If I ran,

select product_id, options_xml from product_variants

This would be a typical result

product_id  |  options_xml
132         |  <options><option_id>1</option_id><option_id>8</option_id></options>

I would like to somehow select a new row for each option_id.

The result I want,

product_id  |  option_id
132         |  1
132         |  8

Thank you in advance!


texts: Database table update using single mysql query

texts: I need a single update query to update the gender in a table - swap 'F' with 'M' and 'M' with 'F'.


texts: PostgreSQL, what is the hostname address of my default database?

texts:
I just created a PostgreSQL database on my laptop (following these instructions), which is running Linux CentOS 7.

Now I would like to understand what is the hostname address of my default database. I thought it was localhost but it's not.

I want to get to know the name or the IP address to use with the psql -h command.
Basically, running this psql -h HOSTNAME command should let me have the same result of the psql command alone, that is letting me connect to my database.

What is the hostname address of my default database?

> service postgresql status

states:

  ● postgresql.service - PostgreSQL database server Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2017-03-30 14:40:30 EDT; 1 day 1h ago Main PID: 2256 (postgres

while both psql -h 127.0.0.1 and psql -h 127.0.0.1 -U davide produce this message:

  psql: FATAL: Ident authentication failed for user "davide"`


texts: Unable to backup after recovery model change

texts:
Previously I had the recovery model as SIMPLE. I have altered it to FULL.

While trying to perform BACKUP LOG, I am getting an error saying

  BACKUP LOG cannot be performed because there is no current database backup.

How can I get the backup of transaction logs?


texts: Update staging table records in SQL Server 2017 with INSERT trigger and table-valued function, using INSERTED records

texts:
I am loading hundreds of order files (positional flat files), via SSIS, into staging tables, and ultimately into production tables.  As part of the staging table population process, I would like to update 2 fields in the staging table that are not part of the original flat file (NewListPrice and NewPriceVersion), however I need to use 3 columns of the newly-inserted record in my function (Company, CustomerNumber and SLIN/UPC).   I have created both a table-valued function and an INSERT trigger on my staging table, but it is definitely not working as intended.

CREATE TRIGGER [Staging].[INSERT_New_Pricing_tg]
    ON [Staging].[OrderDetail01_tb]
    AFTER INSERT
    AS
    BEGIN
        SET NOCOUNT ON

        UPDATE Staging.OrderDetail01_tb
            SET NewListCost = A.CustomerPrice,
                NewPriceVersion = A.NewPriceVersion
            FROM (SELECT LU.CustomerPrice,
                            LU.NewPriceVersion
                    FROM inserted I
                    CROSS APPLY (SELECT * FROM dbo.Find_New_Price_fn(I.Company, I.CustomerNumber, I.UPC)) LU) A
    END

CREATE FUNCTION [dbo].[Find_New_Price_fn](@COMPANY varchar(2) = NULL, @CUSTOMER_NUMBER varchar(6) = NULL, @SLIN varchar(20) = NULL)
    RETURNS @NEW_PRICE_DETAILS TABLE (CustomerPrice varchar(10) NULL, NewPriceVersion varchar(50) NULL)
    AS

    BEGIN
        IF (@COMPANY IS NULL) OR (@CUSTOMER_NUMBER IS NULL) OR (@SLIN IS NULL)
            RETURN
        ELSE
            BEGIN
                DECLARE @ITEM_NUMBER varchar(6),
                        @CARTON_UPC varchar(20)

                -- look up the item number in the Cross-Reference table, using the SLIN
                SELECT @ITEM_NUMBER = LTRIM(RTRIM(ItemCode))
                FROM dbo.ItemXRef_tb IX
                WHERE MarkerNumber = '8001' -- customer master group number
                    AND Company = @COMPANY
                    AND ReferenceItemCode = @SLIN

                IF @ITEM_NUMBER IS NULL
                    RETURN
                ELSE
                    -- find the item in the Items table, based on the item number returned
                    SELECT @CARTON_UPC = LTRIM(RTRIM(CartonUpc))
                    FROM Items_tb
                    WHERE Company = @COMPANY
                        AND ItemNumber = RIGHT('000000' + LTRIM(RTRIM(@ITEM_NUMBER)), 6)

                    IF @CARTON_UPC IS NULL
                        RETURN
                    ELSE
                        -- find the new price of the item using the CartonUPC of the item
                        INSERT INTO @NEW_PRICE_DETAILS(CustomerPrice, NewPriceVersion)
                            SELECT RetailerNetPrice,
                                    PriceVersion
                            FROM PriceFile_tb PF
                            WHERE StoreID = CONCAT((SELECT CAST(ExternalPrefix as varchar) FROM Lookups.CompanyPrefixes_tb WHERE CompanyAbbreviation = @COMPANY), RIGHT('000000' + LTRIM(RTRIM(@CUSTOMER_NUMBER)), 6))
                                AND ProductUPCCode = LTRIM(RTRIM(@CARTON_UPC))
                                AND PriceEffDateFirst = FORMAT(GETDATE(), 'yyyyMMdd')
            END
            RETURN
    END

I am fully aware, and completely expect, that not all records will return NewListPrice and NewPriceVersion values.  I have tried calling the function directly with values I know will return a result, and values I know that won't return a result, and the function works as expected in these cases, however it does not work when I put the function call in my trigger.  I am not sure where I am going wrong with this.


texts: "Error in query (1030): Got error 168 from storage engine" when creating table

texts:
I'm trying to create a table on a separate directory, like this:

CREATE TABLE `abcdef` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DATA DIRECTORY='/media/sdc/mysql/';

But it returns the error:

  Error in query (1030): Got error 168 from storage engine

In the log it says:

2018-09-17T09:43:22.412636Z 2 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-17T09:43:22.412686Z 2 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.

The directory /media/sdc/mysql/ exists and I ran the following commands already:

chown -R mysql:mysql /media/sdc/mysql
chmod -R 777 /media/sdc/mysql

I'm using MySQL 5.7.

I have another server running MySQL 5.6 where I created the same table with no issue. In fact I'm replicating it on a new upgraded server.

So why can't MySQL use /media/sdc/mysql?


texts: Loop through Self JOIN on table until the operand column is NULL completely

texts:
For the problem I have a single table which store a Member's ID and His Referee's ID. This is something like a Multi-Level-Marketing plan where you join a company for some tasks and then someone join from your reference and appear under your downline and so on and so forth. The scheme I am using is not necessarily a binary tree, there can be many users under a single user.

I want to retrieve the entire tree under a specific user, I am using below query to retrieve the records but this is not the solution I want.

SELECT t1.user AS lvl1, t2.user AS lvl2, t3.user AS lvl3,
       t4.user AS lvl4, t5.user AS lvl5
FROM reference t1
LEFT JOIN reference t2 ON t2.referee = t1.user
LEFT JOIN reference t3 ON t3.referee = t2.user
LEFT JOIN reference t4 ON t4.referee = t3.user
LEFT JOIN reference t5 ON t5.referee = t4.user
WHERE t1.referee = 1

This query will get me the tree structure under user 1 but only 5 level deep.

Now I want to retrieve the entire tree upto last level but cant figure out a way for same. Tree depth can be anything depending on the users referrals so I cannot even guess the depth (That wont make sense anyway).

All I want right now is to perform the JOIN and keep watching the selected column (in our case user) from last JOIN to check if all the values in it are NULL. If the column from last JOIN is NULL then it means the tree is completed and query should return the result.

Can anyone suggest a workaround?
I can make changes in the table structure if necessary but that should not effect the behavior of other operations.


texts:
Trying to delete an existing table using powershell

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server(".\CEF_2014_1")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("tempdb")

#drop the Table
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "listeningport")
$tb.Drop()

but getting an error

PS C:\Users\craig.efrein\Dropbox\Scripts\Powershell> .\drop_table.ps1
Exception calling "Drop" with "0" argument(s):
"Drop failed for Table 'dbo.listeningport'. "
At C:\Users\craig.efrein\Dropbox\Scripts\Powershell\create_table.ps1:11 char:11
+         $tb.Drop <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

If I create the table first, and then drop it, that works.

Am I filling the $tb object correctly?

$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "listeningport")

As a second question, how do I check for the existence of the table before deleting it in powershell?


texts: Finding the partitions of a set which respect to some rules


texts: SQL Server 2017 in Azure Cloud config DB Mail

texts:
I have set up a SQL Server 2017 in Azure cloud. I have created a new GMX email address and enabled it for pop3 external programs

I have set up db mail on the SQL Server but I am getting the following auth error

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2020-01-25T12:14:37).

  Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: Authentication required).  )

I have tried ports 25 and 587 with/without SSL enabled on server mail.gmx.com.

I have checked the network for Azure ports and AllowInternetOutBound is enabled and set to any.

Any assistance would be great, thanks in advance


texts: Is clustering a table beneficial, given this update pattern?

texts:
I have a table essentially equivalent to this example:

create table my_table(i integer, x text, y integer, z integer);

create index idx_i_x on my_table(i, x);
create index idx_i_y on my_table(i, y);
create index idx_i_z on my_table(i, z);
create index idx_i_x on my_table(i, x, y, z);

All queries have column i in the where clause.

It seems that this table is a good candidate to benefit from
table clustering (https://www.postgresql.org/docs/9.6/static/sql-cluster.html)

Stats on my_table: for each value of i, there will be from 5 to 10 million rows.

Updates on the table follow the pattern:

update my_table set i = 4 where i = 0;

delete from my_table where i = 6;

All updates or deletes are "bulk", i.e. they
involve all rows for a given value of i.

Given what is written in the docs (https://www.postgresql.org/docs/9.6/static/sql-cluster.html) :

  Clustering is a one-time operation: when the table is subsequently
  updated, the changes are not clustered.

it seems as if it doesn't matter that rows are not reclustered for updates of i, since when I execute this update (for example) :

update my_table set i = 4 where i = 0;

all rows that had i = 0 are still clustered together.


texts: Checking for BCNF in a relational database through FDs?

texts:
Okay so I know that for BCNF, when listing all non-trivial FD's that everything to the left has to be a candidate key.

So I have this relation:

Person(Id, TFN, Name, Phone)

Where Id and TFN(Like SSN) are both Primary Keys.

So the minimal FDs I have is:

ID->TFN
ID->Name
ID->Phone
(TFN->ID is redundant right?)

So it looks to be in BCNF but I was thinking, does Phone->Name? Because Phone would be unique and point to a Name too, along with that a person could have more than 1 phone number, is that the right approach? Given that is true, it would not be in BCNF because the left Phone is an attribute correct? Or at the very least would Phone be considered a candidate key? But if it is a candidate key, it is on the left and therefore still BCNF.

I'm going around in circles with this thought process..

So if that is all correct then the way to decompose the Person relation would be to remove phone and make a new relation

PersonPhone(PhoneNo, id*)

Where id is a foreign key on Person. Then that would make Person BCNF?

Thanks


texts: SQL - Design question for almost identical tables

texts:
I am doing a project for my badminton club.

Intro

I want to create a database for players registration, such as the following:

CREATE TABLE Registration(
    PlayerName_fkey text,
    SchoolName_fkey text,
    Category text,
    TournamentNumber integer,
    TypeOfPlay text,
    Selected boolean
);

Note: It is not displayed here, but each column besides "Selected" is basically a foreign key to another table.

Question

For the registration section, I have entries for single play and doubles play.

The registration table would be almost identical, except the doubles have two players instead of one. I have the following choices:

Create two tables, one for single players, one for double players
Create one table and add an extra column double_partner which is always null for singles players.

Both options seem bad to me and I was wondering if there was another better option out there.

Thanks in advance!


texts: select rows where timestamp is a multiple of n seconds

texts:
I am logging live data to a database where it is then selected by a php script and posted to a graph on a webpage.

A new datapoint is added every second and I have been graphing the most recent 300 points for a 5 minute history on the graph using the following:

SELECT * FROM table_name ORDER BY timestamp DESC LIMIT 300;

What I would like to do is add functionality to zoom out the data, losing resolution but showing a longer history but selecting every 5th row for example. However if I just select the most recent 300 5th entries the graph 'snakes' across the page as the values I am asking for are different each time as shown:

query 1 entries: [8:00] [7:55] [7:50] [7:45] [7:40] ...
query 2 entries: [8:01] [7:56] [7:51] [7:46] [7:41] ...

where what I want to do is

query 1 entries: [8:00] [7:55] [7:50] [7:45] [7:40] ...
query 1 entries: [8:05] [8:00] [7:55] [7:50] [7:45] ...

I have been looking for a way to do something like

SELECT * FROM table_name WHERE timestamp IS MULTIPLE OF 5 SECONDS

but have had no luck so here I am.