texts: Server Wide DDL Trigger Permissions Issue

texts:
I am trying to implement a server wide DDL Trigger that will audit the DDL changes across the databases on the server using script very similar to the article found here

The trigger will log the DDL events into one table within one database, however I am coming up against permissions issues and some users, even myself with sysadmin privileges, receive the following error message.

Msg 297, Level 16, State 1, Procedure LogDDLEvent, Line 19
The user does not have permission to perform this action.

I've read about using the WITH EXECUTE AS clause and creating a login specifically for the purpose of executing the trigger though this doesn't appear to have resolved the issue.

Could someone advise on the correct method to implement a trigger of this kind and what permissions are required to users or to a specific login to be used in the WITH EXECUTE AS?

EDIT: Some further info

The server is running SQL Server 2008 R2 and the DDL Trigger is set to DDL_EVENTS as follows:

CREATE TRIGGER LogDDLEvent
ON ALL SERVER
FOR DDL_EVENTS
AS

DECLARE     @eventInfo XML
SET         @eventInfo = EVENTDATA()

INSERT INTO Tools.audit.DDLEvent
VALUES
(
      REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName
      , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName
      , (SELECT   CAST(client_net_address AS VARCHAR(128))
            FROM  sys.dm_exec_connections
            WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType
      , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand
      , @eventInfo -- DDLEventXML
)

The above script is how I originally created the trigger and this worked fine for me and some other users, though a user who had db_owner role to a particular database kept receiving the error message above.

After trying to implement the WITH EXECUTE AS I found that the trigger would not work for myself.


texts: Connecting Oracle 11g to Postgres via dblink

texts:
I  have administrator access to our Oracle database , but was wondering if its possible to connect an Oracle 11g database to Postgres database and if so, what steps would I need to follow.

I've seen a few tutorials mention an ODBC driver that needs to be installed, but looks like this driver needs to be installed on the Oracle server itself?


texts: how can I see the arguments passed to a stored proc on Azure?

texts:
The query that I'm using:

SELECT
    deqs.execution_count,
    deqs.last_execution_time,
    dest.objectid,
    SUBSTRING(dest.[text], (deqs.statement_start_offset/2)+1,
                        ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                              ELSE deqs.statement_end_offset
                            END - deqs.statement_start_offset)/2)+1) AS statementtext
    , dest.[text]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where dest.[text] like '%foo%'
order by deqs.last_execution_time DESC

That gives me 10 hits, but unfortunately I can't see what arguments were passed in. In regular SQL land I'd just attach the profiler, but that's not an option. This answer on SO makes me think it's not possible, which is hard to believe -- seeing proc arguments is an important debugging tool.

How can I see the last few procs that were run, along with their arguments on Azure?


texts: Large set of execution plans hangs up SSMS

texts:
I have a stored procedure that takes about 3-5 seconds that I'm trying to understand so I want an execution plan for it.  When I run it in SQL Server Management Studio with Execution Plan enabled, it takes over 15 minutes and then, I get a tab that says "Execution Plan" but the tab is empty. Any further attempts to run any SQL at all (even select * from foo) no longer works.  I have a broken SQL server management studio app, I must shut it down and try again. I've done it three times and wasted 45 minutes, and I am now ready to learn about some alternatives.

First is there a reliable command line way to generate an SQLPLAN file for a particular sql script, maybe from the command line, and then I could investigate using some other tool than SSMS to generate my plans.

Secondly, if there isn't a built-in reliable command-line way to generate and store an SQL execution plan as text or XML, then I'd like to know if there exists some other third party tools that would do a good job on very large SQLPlans, in particular not choking and dying when the GUI-drawing parts of it get overwhelmed.

What do you do when SSMS won't generate and show you an execution plan? I'm using SQL 2008 R2 Standard and its included SSMS version, and I don't have any extra plugins.

Update I'm invoking a stored procedure which creates a cursor, which does subqueries, and does great evil, in a loop, generating in excess of 10K individual subquery statements.  It looks like I really need to refactor this down to generate less of a storm of output.

Update2 It really looks like server-side-tracing (to zero in on problem areas) and then a return to profiling then a return to query plans  might be required. I am zoomed in far too deep in a big picture, and I need to zoom the heck out a bit.


texts: Can we create Fail over clustering for SQL Server 2012 standard edition on amazon EC2?

texts: We are planing to move on amazon's EC2 services. So my question is that can we create fail over clustering on amazon EC2 using SQL Server 2012 standard edition ?


texts:
Windows Server 2016 - Fully Patched
Sql Server 2016 SP2 CU3 -> Upgrade to 2017

In place upgrade fails with error message:

  Wait on the Database Engine recovery handle failed. Check the SQL server error log for potential causes

See this in the event log:

  Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 917, state 1, severity 15. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

I'm able to recover from backup, but I need to get this server upgraded.

What am I missing here?

Here is what I'll be trying in a bit (from here)

1) Execute Diff backup in SQL Safe
2) Create Snapshot in VCD
3) Perform Upgrade
4) When fails add -T902 flag to startup parameters
5) Start SQL Server
6) Install CU 11
7) Remove -T902
8) Start SQL Server


texts: Using a DB2 merge with conditionals for matches?

texts:
Within a PHP script I'm running a working merge but I need to add date conditionals to it, and my updates cause it to no longer work.

The logic I'm looking for is this:

If there's a match AND the expire_date is before today, do an insert
If there's a match AND the expire_date is after today, do an update
If no match, just insert

The matching has been working but I basically want to disregard matches that are expired. What am I doing wrong here?

This is running in db2 for iseries

                    MERGE INTO products AS P
                    USING ( VALUES (
                        CAST(:CUSTOMER AS INT),
                        CAST(:SLSCODE AS INT),
                        CAST(:DTL1 AS INT),
                        CAST(:DTL2 AS INT),
                        CAST(:DTL3 AS INT),
                        CAST(:COUNT AS INT),
                        CAST(:LAST_DATE AS DATE),
                        CAST(:FLAG AS SMALLINT),
                        CAST(:ORDER AS INT),
                        CAST(:PIECES AS VARCHAR(45))
                        )
                    )
                AS S(CUSTOMER,REGION,DTL1,DTL2,DTL3,COUNT,LAST_DATE,FLAG,ORDER,PIECES)
                ON s.CUSTOMER = p.CUSTOMER and s.REGION = p.REGION and s.DTL1 = p.DTL1 and s.DTL2 = p.DTL2 and s.DTL3 = p.DTL3 and s.COUNT = p.COUNT and s.LAST_DATE = p.LAST_DATE and s.FLAG = p.FLAG and s.ORDER = p.ORDER AND s.PIECES = p.PIECES

                WHEN MATCHED AND p.expire_date > current_date
                    THEN UPDATE SET  last_date = s.last_date, order = s.order, pieces = s.pieces, expire_date = s.expire_date

                WHEN MATCHED AND p.expire_date < current_date
                    THEN INSERT VALUES (s.customer,s.region, s.dtl1, s.dtl2, s.dtl3, s.count, s.last_date, s.flag, s.order, s.pieces, s.expire_date)

                WHEN NOT MATCHED
                    THEN INSERT VALUES (s.customer,s.region, s.dtl1, s.dtl2, s.dtl3, s.count, s.last_date, s.flag, s.order, s.pieces, s.expire_date)


texts: How to re-write this query or restructure my table for SPEED

texts:
I have an InnoDB MySQL table of this structure:

column
type

service
varchar(16)

url
varchar(255)

datetime
datetime

It contains logs for requests made to one of multiple services. It contains ~3 million rows, and gets ~3 million more rows a month (but I will delete old data if need be).
I am trying to generate a report, and get the number of requests made to each service within a date range.
Here is my current query:
  SELECT service, COUNT(*) as lastMonthCount
    FROM request_logs
   WHERE datetime > '2021-02-16 10:51:05'
GROUP BY service

This works, but is painfully slow (~28 seconds).
It outputs this:

service
lastMonthCount

API A
3056752

API B
38451

I have indexes on datetime and service. I can see they are of type BTREE.
How can I radically speed up this query, or restructure my table/indexes so I can achieve the same use case another way?


texts: Do Sequences Have the Same Replication Range Limits As Identities?

texts:
With merge replication, if the subscriber runs out of identities for a range when inserts occur into it, the subscriber will throw an error on the insert into the table, because it is the publication must grant more identities - and the insert is happening on the subscriber.  A better practice would be to make the publisher be the receiver of inserts only, but in some environments or situations, inserts can occur to both the publisher and subscriber, thus many people extend the range.

I am curious how sequences compare to identities here, as I can't find any indication that sequences are limited by merge replication setups like identity fields are.

I believe Aaron Bertrand or another MVP has been really big on sequences and I've been trying to push my organization to use them over identities, but it's a long journey.


texts:
On a database I had to execute the following two statements:

GRANT CREATE ANY TABLE TO MyUSER;
GRANT DROP ANY TABLE TO MyUSER;

What I miss is the query which tells me that those grants are given. I don't see them in

select * from USER_TAB_PRIVS ORDER BY 2 ;
select * from USER_ROLE_PRIVS ORDER BY 1, 2;


texts:
I have configured MySQL Cluster (5.6) with two data nodes and managment node.
Problem is that I cannot put special chars - looks like encoding is not set to utf8/utf16.

I logged into each node and set server encoding, database encding etc. to UTF8, but this not helped (it helped on standard MySQL server). Tables are also created with utf8.

Is there any other settings (maybe on managment node?) that should I set up to utf8 (or utf16)?


texts: how many fact tables do I need given I want to build an OLAP for Quotation Line Item and Purchase Order Line Item report?

texts:
My client wants a report that looks something like this on a daily basis.

+---------------+-------------------------+-----------------+---------------------+-------------+
| ProductNumber | QuotationLineItemNumber | QuotationNumber | PurchaseOrderNumber |   Status    |
+---------------+-------------------------+-----------------+---------------------+-------------+
| PRD-1         |                       1 | Q1              | PO1                 | PO received |
| PRD-2         |                       2 | Q1              | PO1                 | PO received |
| PRD-3         |                       1 | Q2              | NA                  | Awaiting PO |
+---------------+-------------------------+-----------------+---------------------+-------------+

My system is able to do the daily processing of QuotationLineItem and PurchaseOrderLineItem fairly okay.

The issue is with reporting which leads me to do many joins which is crazy.

I have looked at the Kimball book 3rd Ed of Data Warehouse Toolkit: Dimensional Modeling.

I am convinced that I need to have a separate database instance which is meant for OLAP situations to satisfy the reporting requirements.

Because of this, I need to design dimensional and fact tables.

My question is, it appears that I have at least 2 fact tables. QuotationLineItem and PurchaseOrderLineItem.

How do I generate a report like the above? Because from what I understand fact tables are not supposed to have foreign keys to each other else I will get back a snowflake schema.

EDIT

As requested, these are my source tables which are in a 3NF database schema.

Tables include but not limited to:

quotation_line_items
products
quotations
prices
purchase_orders

The relationships are

products is many-to-many to quotations via quotation_line_items.
products is one-to-many prices
purchase_orders is one-to-many to quotations

EDIT 2

After some clues and further revision, I realized that maybe the solution is to have two fact tables that have common dimensional tables.

The 2 fact tables are:

Quotation Line Item (this is transaction)
Quotation Processing Workflow (this is accumulating snapshot)

The dimensions are:

Product (for Quotation Line Item)
Customer (for both tables)
Sales Rep (for both tables)
Purchase Order (for Workflow)
Quotation Header (for Workflow)

Then I do a drill across the 2 fact table. Although I am not sure how to do that using MySQL and PHP.

Attached is the drill across concept I re-read in the book.

EDIT 3

Okay I just realized that drill across basically is :

both fact tables need to have some common dimensions and the row headers must be from the common dimension table.
perform queries on the 2 fact tables separately
perform an outer join on the 2 result sets.

Based on condition 1, my report does NOT require an identical conformed attribute from a common dimensional table.

Because there is no Quotation Header dimension table for Quotation Line Item Fact table.

Do I simply do separate queries on both fact tables and then do an inner join on the result sets?

Or I am mistaken about my fact tables?


texts:
We seem to have a strange issue from time to time during heavy write workload , a slave in a different data center starts to lag behind the master but when we STOP and START the slave the it starts to catch up quickly. We have a slave in the same data center as the master running the same configuration and does not suffer from this issue as replication lag at maximum reaches 1 second between these two but on the different data center it goes past 10-15 minutes until we have to restart the slave which recovers in a couple of minutes.

The issue occurs during a specific batch job we perform daily and checked that no slow queries are the culprit, CPU and I/O seem to be utilized therefore we know MySQL is doing something but are trying to figure out why a stop and start of the slave would make it work more "efficiently" ?

Update: Issue still seems to occur till this day and only a restart of the slave thread or mysql process mitigates the issue for a couple of days until problem starts to arise again. One thing we noticed is that one of the CPU's on the server (which most likely the slave thread is running on) has a high constant usr time which we find odd when we know it is not currently processing any data.

Would you recommend any tools or commands on how to investigate what exactly the thread might be doing? There doesn't seem to be any slow queries as we set the long time to 0 and log_slow_slave_statements 1 .


texts: msdb..sp_send_dbmail does not work with @query only with @body

texts:
I'm trying to execute the following:

msdb.dbo.sp_send_dbmail
--@body='Hi from sql server in a sproc'
@query='select 1'
,@recipients='mkohn@***.com'
,@subject = 'Test'

When I uncomment @body line, and comment the query... line it works.
With the query it returns:

    Msg 22050, Level 16, State 1, Line 0
    Failed to initialize sqlcmd library with error number -2147467259.

I have a developer edition on my own workstation and it all works no problem

I tried:

Installing .Net 3.5
Adding myself to db_owner and [DatabaseMailUserRole]
Adding [NT Service\MSSQLSERVER] to db_owner and [DatabaseMailUserRole]
Same as above with [NT SERVICE\SQLSERVERAGENT]


texts: How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient

texts:
I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar data about mine name, owner, product etc. from A and B and places them next to similar columns in C. I am using mine name.

The DDL design is as follows:

-- Table "A"
CREATE TABLE "globalminfac_db".minfac (
    ROW_ID INT,
    MINERAL_COMMODITY_GENERAL TEXT,
    FACILITY_PRODUCES TEXT,
    COUNTRY TEXT,
    CITY TEXT,
    LOC_DESC TEXT,
    LOCATION_NAME TEXT,
    OPERATOR_NAME TEXT,
    OWNER_NAME TEXT,
    PRIMARY_OWNER TEXT,
    SECONDARY_OWNER TEXT,
    FINAL_DDLAT REAL,
    FINAL_DDLONG REAL,
);

-- Table "B"
CREATE TABLE "drc_db".OUTLOOK_TABLE (
    COUNTRY TEXT,
    COMMODITY TEXT,
    MINE_NAME TEXT,
    OPERATOR_NAME TEXT,
);

-- Table "C"
CREATE TABLE "drc_db".table2_drc (
    COMMODITY TEXT,
    MAJOR_OPERATOR_OWNER TEXT,
    LOCATION_MAIN_FACILITIES TEXT,
);

However, I'm only able to perform INNER JOIN on these tables and this produces redundant rows where it takes all rows from each table with name "x" and gives each of them a row in my new table. See my SELECT operation below and a screenshot of the sample data. I've already done the initial work of joining the tables using matching columns, which is location/mine name.

SELECT minfac.mineral_commodity_general a_commodity,
       outlook.commodity b_commodity,
       table2.commodity c_commodity,
       minfac.location_name a_locationName,
       outlook.mine_name b_locationName,
       table2.location_main_facilities c_locationName,
       minfac.operator_name a_operator,
       outlook.operator_name b_operator,
       table2.major_operator_owner c_operator,
       minfac.final_ddlat Latitude,
       minfac.final_ddlong Longitude
    FROM "drc_db".minfac_drc minfac
    INNER JOIN "drc_db".outlook_drc outlook
        ON minfac.location_name LIKE concat(outlook.mine_name, '%')
    INNER JOIN "drc_db".table2_drc table2
        ON SPLIT_PART(table2.location_main_facilities, ' ', 1) = SPLIT_PART(minfac.location_name, ' ', 1)

This produces this table below:

As you can see, I have 6 rows listing "Dikulushi mine" with the same coordinates (3 from table A for Silver, Copper and Cobalt; 1 from table B and 2 from table C).

How can I rewrite my query to return the above table, but include only unique location names?

The final goal, to reiterate my earlier point is to have a final table which contains rows and coordinates from all three tables.

I am using pgAdmin4 with Postgres 9.5.14.

Any help with this question or towards making my query more efficient/better would be appreciated.


texts:
I have a number of tables with the primary key (month, year, number) and differing cardinalities differ somewhat. For the tuple (month, year) the history doesn't go back very far, this will probably not grow beyond 50 in the very long term. For every (month, year) tuple  there are not more than 2 million unique numbers. I want to know which combinations of month and years are available. I do this using this query:

select month, year from table group by month, year

This returns the correct result but does not seem to be very efficient. What is an efficient way to obtain this result (utilizing the unique index)?

The tuning advisor suggests to add an index on month-year for this query but this seems wasteful because a larger index is already available.


texts: pg_restore "CREATE CAST" not executed if using public or pg_catalog schemas

texts:
While moving database to another instance I'm using pg_dump and pg_restore with intermediate file.

Dump file was created like this:

pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$SRC_DB_USER sslrootcert=rds-combined-ca-bundle.pem sslmode=verify-full" --no-owner --verbose -Fc --file db.bak

I definitely see that there are casts in the dump

$strings db.bak | grep "CREATE \(TYPE\|CAST\)"
CREATE TYPE public.score_group_name AS ENUM (
CREATE TYPE public.score_group_type AS ENUM (
CREATE CAST (character varying AS public.score_group_name) WITH INOUT AS IMPLICIT;
CREATE CAST (character varying AS public.score_group_type) WITH INOUT AS IMPLICIT;

But when I restore database running pg_restore with params:

pg_restore --verbose -Fc --no-acl --no-owner -n public --single-transaction db.bak > migration.log

there are no queries produced for creating it:

$ grep -e "\(CREATE\|DROP\) \(TYPE\|CAST\)" migration.log
CREATE TYPE public.score_group_name AS ENUM (
CREATE TYPE public.score_group_type AS ENUM (

Source PostgreSQL version:

PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

Target PostgreSQL version:

PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit

PostgreSQL Utils versions:

$ pg_dump --version
pg_dump (PostgreSQL) 9.6.14
$ pg_restore --version
pg_restore (PostgreSQL) 9.6.14

UPDATE
It seems that the root of the problem is -n public parameter, without schema filtering -- CREATE CAST statement is produced, but which schema should I add to the restore? Because -n public and -n pg_catalog filter out such statements.


texts: In mySQL possible to change auto-increment ids that have huge gaps and if so how?

texts:
I am importing a 50 table relational database (I'm not a dba) with 250,000+ records in most of the tables, yet the auto increment is off in most tables, some for example showing the last ID at 5 million (the key) instead of 250,000.

In the past there were lot of imports which caused huge gaps, so I was wondering if it is possible to somehow remove those gaps so everything is nice and orderly so that you know that if there are 452,334 records in a table each with a uniqueID starting from 1 , then row 452,334 would be the last row and not row 3,239,211.

The ids are used in a number of other tables as a foreign key and I am sure I could do it somehow with an enormous amount of code and many lost days. So was wondering if there is some quick fix that I am overlooking or perhaps an app that will do the dirty work for me?

I tried googling but either I am not phrasing what I need correctly or I am insane for even asking this.

Thank you in advance.


texts:
I'm new to database and Oracle, but I need to learn it.

I just following this tutorial to install Oracle 12c, I almost on at the end of the steps.

I have finishing install the database with GUI interface, and now I want to start the database that I have installed with this following command:

sqlplus /nolog

and yea, I have entered to the database, and then I run this following command to connect as sys:
connect sys as sysdba

But it prompt Enter password:

Unfortunately, I don't know what is the default password for that.

I have try the password with CHANGE_ON_ INSTALL base on this following documentation, but it seems not work actually.

So, what is the default password..?, or any way to bypass that password and then change it if I forgot..?

EDIT: I following @Tejash----ORA-00980 instructions to use this command to directly log into DB as a sysdba:

sqlplus / as sysdba

And the result is like this:

What username should I use..?, I have try it with sys and sysdba but it still prompt a Enter password:

EDIT 2: Here is my sqlnet.ora and tnsnames.ora files

and here is my $ORACLE_SID and $ORACLE_HOME values.


texts: How do you install MySQL 5.1.34 on CentOS 5.5?

texts: I am trying to install Mysql 5.1 on cent OS. I researched  a lot but could not find any link which gives step by step procedure. Everywhere, there are vague answers. Can someone please give me steps to be followed to install Mysql 5.1 ?


texts: How to insert multiple IP addresses in a pg_hba.conf file via unix shell script?

texts:
I need to write some IP addresses to the pg_hba.conf file of my Postgresql, in order to allow remote access from those IP to my machine.

I have the following code in shell script to do that for a single IP address

#!/bin/bash

######## PostgreSQL Child Server Host or IP Address
serverhost=$1

######## PostgreSQL Child Server pg_hba.conf File Path
pghbapath=$2

######## Shell Script Command to write the data at the end of the    pg_hba.conf file
sed -i "$ a host    all all     $serverhost/32  md5" $pghbapath

An example command to execute the above bash script is

./reset_ip.sh 192.168.10.27 /root/Desktop/BashScripts/pg_hba.conf

The above is working fine, but I have a lot of IP addresses with me. So, instead of manually running the command each and every time for a different IP address, is there a command which can take multiple IP addresses at once and write them to the pg_hba.conf file in one go?

Edit - Thanks a lot for the answer Luuk. I tried it and it worked. Now, suppose if instead of having a text file to hold all the address, I want to write the multiple IP addresses separated by commas in the command line itself, somewhat like

./reset_ip.sh 192.168.10.27,192.168.10.28,192.168.10.29,192.168.10.30 /root/Desktop/BashScripts/pg_hba.conf

Is it possible? What I basically want is to be able to pass multiple values for a single variable via the command prompt.


texts: Find ID columns with null value in oracle database

texts:
I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database

Any suggestion?


texts: MySQL table relations, inheritance or not?

texts:
Im building a micro CMS. Using Mysql as RDMS, and Doctrine ORM for mapping.

I would like to have two types of pages. Static Page, and Blog Page. Static page would have page_url, and page_content stored in database. Blog page would have page_url, but no page_content. Blog would have Posts, Categories...

Lets say I have route like this:

/{pageurl}

This is page, with page url that can be home, or news, or blog... That page can be either Static page, and then I would joust print page_content. But it can also be Blog Page, and then I would print latest posts as content.

How should I relate these Static Page and Blog Page tables? Is this inheritance, since both are pages, with their URL, but they have different content? Should I use inheritance, so that both Static and Blog page extends Page that would have page_url? Or should I made another table page_types and there store information about available page types?


texts:
I not able to restore DB from maped Network Drive, Only from HDD initialized in Native OS,

my enviroment is MsSql2008 on Win2008R2 Standard (as virtual in VMware ESX4.1),

Win2008R2 Standard doesn't supports adding Networks drive(s) as HDD, RAM, Procesors on Runtime, these resources are accesible after enviroment restarted,

I could be able to import DB from another instance, but there are some another internal rulles that doesn't allows me that (middle and huge DB)

1) backup in OmniBack form or Full_Backup_File

2) no available space on HDD to copy dump on local enviroment for middle and huge DB

3) another crazy rulles about Network trafics from local end_user_PC, reduced DiskArea, for moving files betwees luns....

is there someone that solved this issue, or this issue is strictly rellated with resources from WinOS

EDIT:

Answer from Microsoft Q&A

SQL Server Management Studio -> Server Objects -> Backup Devices -> Add


texts: SQL Server trigger (update or insert with inner join problem)

texts:
ALTER TRIGGER [dbo].[killertrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS
IF (UPDATE([Kill]))
BEGIN
  SET NOCOUNT ON;

  INSERT  killer(serial,[Kill])
    SELECT i.serial,i.[Kill] FROM inserted AS i
    INNER JOIN deleted AS d
    ON i.serial = d.serial
    AND i.[Kill] <> d.[Kill] AND i.[Kill] > 0;
END

However how would I go if I would want it to update if the serial already exist in the killer table. Could do update first and if it returns rowcount 0/null then do insert but I got stuck   WHERE - since I can't use i.serial = d.serial to compare there.


texts: SQL Server: Accurate Row Estimates without Histogram?

texts:
I'm exploring the optimizer in SQL Server, particularly histograms and I'm seeing some strange, results.

If I execute the query below, where I know there is no histogram on the field, SQL Server outputs a row estimate equal to the actual the result. If I were to run the same query where I use DECLARE @i NUMERIC(19,2) = 30.0 instead of a constant WHERE [Rate] < @i, the estimate is 94.8 as I would expect for the shown query since the database has no statistics on the field.

So my question is why is SQL Server outputting the correct result when it has no statistics on the field?

When turning off AUTO_CREATE_STATISTICS as indicated by my answer, I experience some odd behaviour on the row estimates.

A constraint exists on the table restricting [Rate] to be between 6.5 - 200.0. The estimates vary a lot when experimenting depending on if > is used or >= as well as 24.0 or 64 format queries where the predicate shows an implict conversion to typemoney.

For WHERE [Rate] > 6.5 the estimate is 28.44 which then stops at rates above 100.

For WHERE [Rate] > 7 where decimals are excluded, the 28.44 estimate runs all the way up to 255

For WHERE [Rate] >= 6.5 the 28.44 estimate runs up to about 100 then changes to 17.7764 up to 1000 before the estimate drops to 1.

So there doesn't seem to be much consistency. It seems like SQL Server is aware of the constraint based on some of the queries I'm experimenting with, but it also ignores its presence at times.


texts: how to rebuild / reinstall ssrs (reportserver, reportservertempdb) databases?

texts:
Our server crashed. We got it back up and running however, the mentioned databases have been corrupted.

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

If not:

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


texts:
I have MariaDB version 10.2.14-MariaDB. I have a table that looks like this:
CREATE TABLE `card_data_link` (
    `card_id` INT(11) NULL DEFAULT NULL,
    `data_id` INT(11) NULL DEFAULT NULL,
    INDEX `card_id` (`card_id`) USING BTREE,
    INDEX `data_id` (`data_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

It has this data:
card_id data_id
"63"    "21"
"34"    "3"
"34"    "21"
"34"    "21"

It joins to this one:
CREATE TABLE `card_data` (
    `data_id` INT(11) NOT NULL AUTO_INCREMENT,
    `data` TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`data_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=51
;

It has data that looks like this:
data_id data
"21"    "{R}"
"3"     "{3}"

If I run this query:
select
    cdl.card_id,
    cdl.data_id,
    cd.data
from
    card_data_link cdl
left join
    card_data cd on cd.data_id = cdl.data_id
group by
    cdl.card_id
    ,cd.data_id

results:
card_id    data_id    data
"34"        "3"     "{3}"
"34"        "21"    "{R}"
"63"        "21"    "{R}"

Is there a way I can get the second
"34"        "21"    "{R}"

row to show up so it returns:
card_id    data_id    data
"34"        "3"     "{3}"
"34"        "21"    "{R}"
"34"        "21"    "{R}"
"63"        "21"    "{R}"

It's like it's doing a select distinct, even though I'm not putting in distinct. Am I not grouping correctly? Thanks!


texts:
I am using java & MySQL to make an app.
When I check the process list. I see a lot of sleeping processes. It also reaches to a points when no further connection is allowed.

I have read many answers around which tell the command to masskill the process or some suggests checking the code for bug.  I have checked that I'm closing the connections properly using the Apache Commons DbUtils.

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(statement);
        DbUtils.closeQuietly(con);

I am not able to figure out why these processes don't get killed after closing the connection.

My question is

Do too many subquries create multiple sleeping processes in MySQL? I don't see much relation though and didn't find anything on search.
Does passing connection object through methods create extra/multiple processes?

Regards


texts: Postgresql string array vs join for user management schema?

texts:
I am creating a database schema for user management in PostgreSQL. As, it supports array, so I am trying with three approaches.

Which one is best, querying with joins or getting/setting role data with array?

Considering large user base in near future.

Approach 1:

User table

id
user_name

Role table

id
role_name

RoleUser table

role_id
user_id

Approach 2

User table

id
user_name
[integer array of role id]

Role

id
role_name

Approach 3

User table

id
user_name
[string array of role key]

Role

id
role_name
role_key


texts: SQLite, ASCII Characters 0x20 to 0x7E Check Constraint

texts:
I have the following table which I'm trying to limit the column name to ASCII characters ranging from 0x20 to 0x7E. Why is it not working?

CREATE TABLE test
(
    id INTEGER NOT NULL,
    name TEXT NOT NULL CHECK(name NOT GLOB '*[^\x20-\x7E]*'),
    PRIMARY KEY(id)
)


texts: Is it possible to record all the SQL executed by a session?

texts:
There is a web application which is connected to my Oracle database server via JDBC.

When a user does some actions on that web application, I want to know what SQL statements were submitted to database. Is this possible?


texts: Sync oracle DBs using archive log when not connected to each other

texts:
I'm trying to synchronize 2 dbs which are not connected to each other, and located in a separated networks.

The 'copy db' (aka: standby) can be read only, but still should be active.

My intuition (which I'm not sure is correct) tells me I can synchronize the both using RMAN backup / restore, then move the archive files from source (production) to copy (standby), open copy (standby) in read only mode and apply changes when still open (similar to active data guard).

What I did is as follows (according this manual):
I configured data guard according the manual, see link.

On source (production):

$ rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

I moved the files and restored on the copy after configured as standby:

$ rman target=/
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

and got Finished restore at

Now on the copy (after all configurations from the manual) I've tried to open the db:

SQL> shutdown immediate;
...
Oracle instance shut down.
SQL> startup mount;
ORACLE instance started
...
Database mounted.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:\PATH\TO\FILE\SYSTEM01.DBF'

I'm using oracle 11g DB on windows, I've tried also on Linux and reach the same results.

both machine are new, oracle 11g SW installed on both (with sample schemes), and DB was dropped on the copy before the restore.

file was copied after DB was dropped, no changes was made on the source DB except the configuration according to the manual.

I would like to ask:

is what I'm trying to achieve (active data guard without direct connection, manually move of archive once in a while, and auto apply of archive) looks possible?
why the database restore or open is failed?
is there simpler way to do so? (i.e. manual backup / restore without configure production / standby architecture, but still apply the archive of the source to the copy.)

Thanks for any reply.


texts: How to TRUNCATE table if the table doesn't exists

texts:
I want to run a few TRUNCATE statements. But in between some of the database doesn't exists. But SQL stops the rest of the queries.

Is there any way to ignore the error and continue to execute the rest of the statements.

TRUNCATE _aaaaa_tmp;
TRUNCATE _bbbbb_tmp;
TRUNCATE _ccccc_tmp;

Error Code: 1146. Table '_bbbbb_tmp' doesn't exist


texts: How to convert a single MySql table to utf8mb4 (from latin1)

texts:
MySql version 5.6.39

i have one table that now needs to support chinese characters. the other tables in the database do not need utf8mb4 encoding.

what have i done thus far:
1) backed up database!
2) alter table <blah> default character set utf8mb4 collate utf8mb4_unicode_ci;
3) alter table <blah> convert to character set utf8mb4 collate utf8mb4_unicode_ci;
4) (for all columns except numeric) alter table <blah> change col1 col1 varchar(xx) character set utf8mb4 collate utf8mb4_unicode_ci;

what i have not done thus far:
1) alter database <blahDB> character set utf8mb4 collate utf8mb4_unicode_ci;

i've seen several questions/answers here...notibly:
How to easily convert utf8 tables to utf8mb4 in MySQL 5.5  Is it necessary to alter the database when all i want is for one table within it to be utf8mb4?

Right now, when i insert a "special" (unicode) char (0x2015) in the middle of a string into the table, it is stored as '?'. Help!!


texts: Does rebuilding an index update the last_ddl_time attribute in ALL_OBJECTS/DBA_OBJECTS?

texts:
I rebuilt an index and the last_ddl_time was modified, but I am not sure that's what caused it (someone else might have updated it in other way).

las_ddl_time is defined as follows in the Oracle documentation under ALL_OBJECTS:

Timestamp for the last modification of the object resulting from a DDL statement (including grants and revokes)

Thanks.


texts:
OK I'm not into Database management, but I got this problem, also not sure if I'm posting it at the right place or not.
So, my Dad works in a pharmacy and all the work there is on paper and is done manually. He asked me if I can develop an app which He can use. I'm stuck at the part where I gotto decide how to design the table for the system. My app is built on QT/C++ and I'm using Sqlite with it.

Here's the database problem, There are 6 different types of items coming the store, Eg. tables & capsules, injections, ointments, etc. Now for each item in each of these category, we have to maintain the following entries unique key, received date, received amount, issues date, issued amount, expiry date and batch number.
Now this store is resupplied with items almost every week, and issues items to OPD frequently, also each item can receive different batch numbers which in turn has different expiry date.
I want to design a table diagram which satisfies the problem.

~Thanks in advance.

Example:

Lets say we have a medicine CROCINE (which comes under the category tables & capsules) coming in in week 1. The batch number of the received medicine is abc-123 and its expiry date it dd-mm-yyyy, and we received a quantity of 5000 units. Later in the week 1000 units are issued to the OPD. Further in the week another 2000 units are issued to the OPD. So the updated balance of CROCINE in the store is, 2000 units. Now, in the next week, ie. week 2, 2000 units of CROCINE is received with batch number def-456 with a different expiry date. Also the balance is updated. Like this it goes on and on. :p


texts:
We're planning to buy new Oracle Database Enterprise Edition licence. We have two servers (Live and standby).

Our standby server should be configured as a physical standby, and will be used only in case of disaster.

According to this we should buy licence for both servers.

But do we need to Active Data Guard licence? Is it used buy the physical standby?


texts: Large update taking too long; looks like it is updating more records than I have

texts:
I am trying to do an update on a table that has 4 million records. The main table is indexed on SerNum a unique field. I use a temp table to update from that also has a unique index on SerNum. I am told this should happen fast but it's taking hours. It's been running for 2 hours and if I add up the rows affected I get 5,260,000 which is weird because I only have 3,869,000 records in the table. The select into the temp table shows it has 3,390,954. Here is what I have so far can this be more efficient?

USE IMB_TraceData
GO

IF OBJECT_ID('tempdb.dbo.PRIMARY KEY (lname,') IS NOT NULL
    DROP TABLE #MaxDate;
GO

-- ******************************************
-- Create Work Table
-- ******************************************
SELECT * INTO #MaxDate
FROM
    (
    SELECT  *
    FROM    (
            SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY TR_SerNum
            ORDER BY TR_Scan_Date DESC) AS rn
            FROM  Trace_Return_Table
            ) a
    WHERE   a.rn = 1
    ) AS n
GO
CREATE INDEX IDX_SerNum ON #MaxDate(TR_SerNum)
GO

DECLARE  @UPDATES TABLE (PK_ID int NOT NULL Primary KEY)
DECLARE @x INT

SET @x = 1
SET ROWCOUNT 10000

WHILE @x > 0
    BEGIN
        BEGIN TRANSACTION
        UPDATE TOP (10000) IMB_TBO_Data.dbo.The_Big_One
        SET TR_Scan_Date = n.TR_Scan_Date,
            TR_Facility_Id = n.TR_Facility_Id,
            TR_Operation_Code = n.TR_Operation_Code
        OUTPUT inserted.HT_SerNum INTO @Updates
        FROM IMB_TBO_Data.dbo.The_Big_One t
        INNER JOIN #MaxDate n
            ON t.HT_SerNum = n.TR_SerNum
        WHERE NOT EXISTS
        (SELECT * FROM @UPDATES AS Updates
        WHERE UPDates.PK_ID = t.HT_SerNum)
        SET @x = @@ROWCOUNT
        COMMIT TRANSACTION
    END

SET ROWCOUNT 0


texts: Best Practice - Database design for 'conditional join'

texts:
I need to create a database to store Jobs.
Each Job can have 1-* Step
Each step is classified as one of either Type A, B or C.
Each Type has specific details and other tables that join on them so putting them into the Step table is not an option.

This is depicted in the diagram below;

In this design there is a conditional join between Step and TypeX tables. I have read around and cannot find best practices for this kind of scenario, this feels very messy to me but i can't find a way around it. What would anyone else do?


texts: SSPI error on windows authenticated remote access only

texts:
We are trying to figure out what is wrong with our server. Right now i have many accounts that are sql accounts and windows authenticated (domain accounts).

When i connect with ANY windows authenticated account i get an error

  Cannot generate SSPI context

The account is good. I have also been trying to connect simply using Management Studio and i get the same error. If i use any SQL accounts it works and all windows authenticated account fails.

That being said, if i do a remote desktop to the SQL server itself and try using Management Studio on the server itself, all Windows authenticated account works without error.

The error only shows for remote computer (same domain / ip range) when using windows authenticated connections.

i have found this but it doesn't fix anything. According to the doc no accounts should work if we are getting this error.

Now what changed, the only thing that change is that the server was a virtual machine on VMWare, there was a conversion done to switch it to hypervisor and the instance has been restarted.

Anyone else would know when SSPI error can show based of where the authentication request was called ? i haven't been able to find anything. Could possibly be a bad error. Our servers have been down for the whole weekend we still haven't found anyone with the same issue.

Important notes :
- PC that remote and connect to SQL are in the same domain, same physical location, plugged in the same rack mount network wise.
- Running using IP or DNS doesn't change anything.
- no error logs show up in SQL and neither in Event Viewer on the server


texts: "Zero" downtime and minimal space requirements for upgrade to PostgreSQL 9.2

texts:
Need to migrate from PostgreSQL 8.3.x to 9.2

Constraint 1: 8.4 and above have made datetime format to be integer by default. Wasn't so in 8.3.x. As a result pg_upgrade can't be used. Unless server is rebuilt with --disable_integer_datetime. This implies all future migrations would also have to retain this setting. This isn't acceptable in our case.

Constraint 2: The application using the 8.3.x database can't go down (there isn't any allowed downtime for at least a portion of its function). We could, however, hold off all the db updates for a short while. This gives us a window of about an hour at most.

Proposed solution (or actually - can the experts out there validate if this idea will work or if it makes any sense):
The idea is to move tables over in parallel (processes or threads) using the copy command from the old to the new version. Assuming that both old and new servers are running (on different ports) ...
Question is whether the copy command on the old version can be sent "directly" into the new version? Optimally, if it can be done without an intermediate file.
Has anyone tried anything like this before? Is it practical? Does it even make sense?

Thanks in advance for your replies.


texts: SQL Read Committed Snapshot Isolation in Database with Selects and Inserts Only

texts:
We have database with only 1 table, which conducts Inserts and Selects.
There are No Updates in the table.

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

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

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

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

Select and Insert Queries

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

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


texts: Connection Pools and TempDB Internal Objects

texts:
I am thinking I know the basics of the problem here, but I want to make sure I know the details before trying to explain it to others.

We recently upgraded our third party application software to a new version. This version completely switched the Java Application Stack to a different vendor. After the upgrade we were told by the vendor that other customers who had upgraded were experiencing tempdb growth. I checked my monitoring and found we are beginning to experience the same.

The vendor claimed the only way to clear Tempdb was to reboot the SQL Server. They also don't really know what is happening because they are only looking at the disk space through the database properties, which I assume uses the flawed sp_spaceused sproc.

My monitoring uses the tempdb.sys.dm_db_file_space_usage dm so I am able to get a much more accurate view. I can see the internal object usage constantly climbing and not going back down at all.

I set up additional monitoring by using sys.dm_db_task_space_usage and can see the connections for this software have large allocation number with very little deallocation. It also looks like it opens connections and keeps them open but they will often be sleeping, which is consistent with the behavior of an application pool, from what I understand.

It seems to me when the requests are ending the code stops but the connection stays open and the internal objects are not being deallocated. Can someone confirm this and help me to explain it? I know SQL but have very little knowledge of connection pooling.

Can this be corrected by adding a connection setting to their pools, something like SET XACT_ABORT = ON?


texts: Split Multiple Columns Into Multiple Columns by Delimiter

texts:
I would like to split multiple columns into multiple columns by a delimiter. I am using SQLite, but I am open to importing into another DBMS, if necessary.

An example table, showing how the table currently is, where “;” is the delimiter:

http://sqlfiddle.com/#!5/f1845

An example table, showing how I’d like the updated table to look:

http://sqlfiddle.com/#!5/d6283f


texts: Postgres production server: more rollbacks than commits?

texts:
I'm studying a little Postgres 9.3 stats, and I've observed that in my servers, this simple query:

select sum(xact_commit) as commits,sum(xact_rollback) as rollbacks from pg_stat_database;

I have curiosity to know the cause why it returns always more rollbacks than commits.

This is also true, for incremental values:

postgres=# select sum(xact_commit),sum(xact_rollback) from pg_stat_database;

     sum     |    sum

------------+-----------

 1045889770 | 148206853

postgres=# select sum(xact_commit),sum(xact_rollback) from pg_stat_database;

    sum     |    sum

------------+-----------

 1045890403 | 14820767

commits = 1045890403-1045889770 = 633
rollbacks = 148207673-148206853 = 820

I expect to have more commits  than rollbacks, in normal conditions.
Any idea?


texts: Pull more records based on IDs in comma separated list returned by GROUP_CONCAT

texts:
Is it possible to pull more records based on IDs in comma separated list return by GROUP_CONCAT? Is a LEFT JOIN possible here? If so, how can it be done. I am constantly failing at figuring this out, in addition, I've googled a lot already.

I have these two tables:

CREATE Table users (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL
);

CREATE Table articles (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT(100) NOT NULL,
  title VARCHAR(50) NOT NULL,
  content TEXT,
  published DATETIME NOT NULL
);

Insert:

INSERT INTO users (username, password)
VALUES ('Bob', '5f4dcc3b5aa765d61d8327deb882cf99');

INSERT INTO users (username, password)
VALUES ('David', '630bf032efe4507f2c57b280995925a9');

INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Science 101', 'Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.',
        '2015-11-30 09:18:43');

INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Health Care', 'Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.',
        '2016-01-10 15:20:43');

INSERT INTO articles (author_id, title, content, published)
VALUES ('2', 'Physics 101', 'Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.',
        '2016-01-17 14:18:43');

Query:

SELECT
  articles.id,
  articles.author_id,
  users.username,
  COUNT(articles.id) AS number_of_articles,
  GROUP_CONCAT(DISTINCT articles.id) AS articles_published
FROM articles
LEFT JOIN users ON articles.author_id = users.id
GROUP BY articles.author_id
ORDER BY articles.published DESC;

Result:

id  author_id   username    number_of_articles  articles_published
3   2           David       1                   3
2   1           Bob         2                   2,1

Expected result:

Bob's articles

Health Care
Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.

Science 101
Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.

------

David's articles

Physics 101
Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.

-----

SQLfiddle


texts:
I am attempting to union together 2 queries in progress DB
Unfortunately, this is returning me a syntax error.
Are union queries unsupported in progress DB?
     SELECT 0, 'All'
     UNION ALL
     SELECT custcode, custname
     FROM pub.pv_customer


texts:
I try to create table from ORACLE setup script
After I run the script

SQL> @home/oracle/Desktop/Setup.sql

Following output appears:

Table dropped.
Table created.
Index created.
INSERT INTO tab1
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Commit complete.
PL/SQL procedure successfully completed.

And the table is created but it's empty.


texts: Graceful Test for Linked Server Availability Inside a TVF or View

texts:
I’ve got a mission critical table valued function that needs to return results from several linked servers simultaneously. If any of those servers are down, it still needs to return results from the other servers. Because it’s function a Try/Catch block is not allowed. Dynamic SQL is not permitted inside functions either.
sys.sp_testlinkedserver seems like the obvious solution. It returns an integer when the linked server is online. However, it throws an error when there’s a problem with he linked server, so it’s useless.
This TVF will replace a similarly unreliable view based on the following query:
SELECT Col_1, Col_2, Col_3, Col_4 FROM Server_A.Some_DB.TheTable
UNION
SELECT Col_1, Col_2, Col_3, Col_4 FROM Server_B.Some_DB.TheTable
UNION
SELECT Col_1, Col_2, Col_3, Col_4 FROM Server_C.Some_DB.TheTable

Again, if any of the servers are down, the query throws an error. This is why I thought that a TVF would be useful so that I can test a server before doing anything that could cause an error.
Are there any graceful ways to test the availability of a linked server that don’t throw errors when the linked server is down?


texts:
I have a database design problem which I need some help with.

I am developing a database to store some engineering lab test results. But the main problem I am running into is that each Test is completely different from each other in terms of inputs and results. For example, Test1 measures Measurement1 at three different Frequencies. Test2 measures Measurement2 at five different Temperatures.

If I am running multple Tests on sample, what is the best way to go about it?

I can create independent tables for each Test and address this in the interface code.
I can have a normalized database with Test table and Results table. Store the columns required for all the Tests in there, eventhough some will be empty for  a particular Test.

Any help on this is much appreciated. Thanks.


texts:
I am facing a really strange problem with my database. I have a table with less than 20 columns. When i run a select * query, it's taking almost 6 to 7 seconds to execute. I couldn't identify the issue and tried selecting each columns one by one. By the way, total number of rows are just 269.
Finally i found 2 columns which causes the issue. Both these columns store some kind of description and the datatype is MEDIUMTEXT.
SELECT LocalDescription FROM test.hotels where Id > 0;
SELECT LocalFacilitiesDescription FROM test.hotels where Id > 0;

It took between 5 to 6 seconds to finish the execution. There is no delay for the rest of the columns.


texts: Does the SELECT statement count into DML?

texts:
Looking at the name Data Manipulation Language (DML) I would assume, that all contained statements are actually for manipulating data.

As far as I know the SELECT statement can only be used to query data, not change it. (Not taking INSERT ... SELECT into account here.)

So is SELECT part of DML and if so, why?


texts: Postgres table partitioning "no partition of relation "parsel_part" found for row" error?

texts:
I'm trying to use new partitioning method of PostgreSQL 10. I have a parent table that contains 1.5 million rows. I want to create partitioning on this already populated table.

I've created a  new master table which has identical columns with real master table from the CREATE SCRIPT;

CREATE TABLE master_part (objectid integer,poly geometry(Geometry,2321), parcel character varying(255), m_date(date)) PARTITION BY RANGE (m_date);

Then I've created child tables, partitioned by the m_date column;

CREATE TABLE parsel_2014_04
  PARTITION OF parsel_part FOR VALUES FROM ('2014-04-01') TO ('2014-04-30');

CREATE TABLE parsel_2014_05
      PARTITION OF parsel_part FOR VALUES FROM ('2014-05-01') TO ('2014-05-31');

CREATE TABLE parsel_2014_06
      PARTITION OF parsel_part FOR VALUES FROM ('2014-06-01') TO ('2014-06-30');

CREATE TABLE parsel_2014_07
      PARTITION OF parsel_part FOR VALUES FROM ('2014-07-01') TO ('2014-07-31');

CREATE TABLE parsel_2014_08
      PARTITION OF parsel_part FOR VALUES FROM ('2014-08-01') TO ('2014-08-31');

CREATE TABLE parsel_2014_09
      PARTITION OF parsel_part FOR VALUES FROM ('2014-09-01') TO ('2014-09-30');

CREATE TABLE parsel_2014_10
      PARTITION OF parsel_part FOR VALUES FROM ('2014-10-01') TO ('2014-10-30');

CREATE TABLE parsel_2014_11
      PARTITION OF parsel_part FOR VALUES FROM ('2014-11-01') TO ('2014-11-30');

CREATE TABLE parsel_2014_12
      PARTITION OF parsel_part FOR VALUES FROM ('2014-12-01') TO ('2014-12-31');

CREATE TABLE parsel_2015_01
      PARTITION OF parsel_part FOR VALUES FROM ('2015-01-01') TO ('2015-01-31');

CREATE TABLE parsel_2015_02
      PARTITION OF parsel_part FOR VALUES FROM ('2015-02-01') TO ('2015-02-28');

CREATE TABLE parsel_2015_03
      PARTITION OF parsel_part FOR VALUES FROM ('2015-03-01') TO ('2015-03-31');

CREATE TABLE parsel_2015_04
      PARTITION OF parsel_part FOR VALUES FROM ('2015-04-01') TO ('2015-04-30');

When I run the script above to create child tables I'm getting the error below;

  ERROR:  no partition of relation "parsel_part" found for row
  DETAIL:  Partition key of the failing row contains (m_date) = (2014-10-31).
  SQL state: 23514

This error seems to be very rare because I couldn't find anything about it.

Maybe some one have seen it before ?


texts: Is there a free software addition to SQLite available for data-replication?

texts: I am aware of the possibility to create my own trigger(ed) solution, but I didn't want to reinvent the wheel :-)


texts: Move database without replication taking new snapshot

texts:
We are upgrading a physical server we have SQL installed on at work, and I need to move SQL to a new machine, but I need to do it with minimum downtime.

I have the new virtual server already available to me, so I was going to setup log shipping from the Old -> new server to handle the actual move of the database(s) -- The server also has replication coming from a different server (the database I am moving is the subscriber).  How could I get the replication moved from the old to new without having to re-initialize replication?


texts:
I've run into an issue with one of my tableau users. His queries have taken down the DB a few times yesterday.
My solution is twofold.

First, I'd like to limit his rights. Is there a way for me to limit the maximum length of his queries before they timeout?
Second, I'd like to setup an alert for the database. I know how to setup an alert, however I'm not sure what thresholds to use.

Is there some way for me to see recent usage in order to estimate where I should set the threshold?

Thanks!


texts:
I have an application in C language which inserts the report data in MyISAM table by using mysql_stmt_execute() function.
Due to load on the table, it gets crashed.
For auto repair of crashed table, I have added following option in /etc/my.cnf file
myisam-recover-options=FORCE,BACKUP

When the table gets into a crashed state, then mysql_stmt_execute() function fails and I can't insert the reports. As I have specified recovery options, expected result should be, table gets repaired and application should work fine.
QUESTIONS

How to repair the table automatically when it gets crashed ?
Why myisam-recover-option can't recover the table when query is executed ?


texts: Oracle 12c RAC Cluster / CRS fails to start after reboot

texts:
Could someone shed some light to help fix the cluster/CRS startup failure

1)
/oragrid/app/grid_home/bin/crsctl start crs

CRS-4640: Oracle High Availability Services is already active

CRS-4000: Command Start failed, or completed with errors.

2)
/oragrid/app/grid_home/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services
daemon

CRS-4534: Cannot communicate with Event Manager

3)
/oragrid/app/grid_home/bin/crsctl start res ora.crsd -init

CRS-2672: Attempting to start 'ora.gpnpd' on 'xxxxxxxxxx'

CRS-2674: Start of 'ora.gpnpd' on 'xxxxxxxxxx' failed

4)
/oragrid/app/grid_home/bin/crsctl start cluster

CRS-2672: Attempting to start 'ora.gpnpd' on 'xxxxxxxx'

CRS-2674: Start of 'ora.gpnpd' on 'xxxxxxxx' failed


texts:
I have a question on index

Our Application is throwing the following SQL error

  Index 'INDEXNAME' on table 'dbo.TABLENAME' (specified in the FROM clause) does not exist. Error: Unknown Error Errorcode: 308

But when I queried the DMV I am able to see the index. Also, a particular column is used in two different non clustered indexes, is this a valid set up? Any suggestions to fix this issue.

I appreciate your help.


texts: Amazon RDS replication with external slave

texts:
I've recently configured a replication with an external MySQL master and RDS as slave but now I need to reverse that config and use RDS as master and the other instance as slave.
Actually I use a vpn and the other instance is outside AWS.

I changed the server host and password but I got this error:

  141103 11:35:44 [ERROR] Error reading packet from server: Binary log
  is not open ( server_errno=1236) 141103 11:35:44 [ERROR] Slave I/O:
  Got fatal error 1236 from master when reading data from binary log:
  'Binary log is not open', Error_code: 1236 141103 11:35:44 [Note]
  Slave I/O thread exiting, read up to log 'mysql-bin.000007', position
  284079763 141103 11:37:09 [Note] Error reading relay log event: slave
  SQL thread was killed

Perhaps I didn't get the right log filename and postion? but I can't find them in to RDS

Many thanks for your help


texts: I have set up my replication but I want to delete some outdated data from my master tables. Is it possible that say if I have 10 records at master and I delete 5 of them but I want those 10 records to remain on the slave?


texts: Is there a TSQL command or another way to rename AOAG (Always On Availability Group)?

texts:
We have an AOAG named with "space" and I need to rename it to not have "spaces". It was created before my time. The 3rd party vendor tool does not work with AOAG that has spaces. There are about 40 databases in the AOAG (SharePoint).

The property is greyed out in SSMS. Is there another option? As this is production, I am looking for a solution with minimal downtime.

Thanks much,
Debbie


texts: MySQL Host 'host_name' is blocked - Which Database?

texts:
We are getting the error that a host is blocked on MySQL

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

I understand how to clear this error (Flush Hosts),

However we are a shared hosting company and would like to know to which database the connections are failing in order to rectify the issue and notify the customer. We have many databases and many customer websites.

We are running MySQL 5.5 - so the 'host cache' table does not exist (not sure if that would help anyway)


texts:
While trying to answer Create integer id columns from existing string columns (integer coding?) it turned out that I needed my own version of postgres to play with. I already had a decent postgres version installed:

stack=# select version();
                                                version

--------------------------------------------------------------------------------
------------------------
 PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.1.1 20190503 (R
ed Hat 9.1.1-1), 64-bit

but it did not have the pgcrypto module:

stack=# CREATE EXTENSION pgcrypto;
ERROR:  could not open extension control file "/usr/pgsql-11/share/extension/pgcrypto.control": No such file or directory

so I installed:

sudo dnf install postgresql-contrib

but I still get the same error. If I look in:

]$ ls /usr/pgsql-11/share/extension/
plpgsql--1.0.sql  plpgsql.control  plpgsql--unpackaged--1.0.sql

Is there any other package I need to install, or am I missing something else?

UPDATE:

rpm -qa postgresql\*
postgresql-contrib-11.3-1.fc30.x86_64
postgresql11-11.4-1PGDG.f30.x86_64
postgresql-11.3-1.fc30.x86_64
postgresql11-server-11.4-1PGDG.f30.x86_64
postgresql11-libs-11.4-1PGDG.f30.x86_64

I get an error for dnf install postgresql11-contrib:

[...]
   Failed to synchronize cache for repo 'fedora-modular'
   Error: Failed to synchronize cache for repo 'fedora-modular'

pdgd seems to be the latest availible:

rpm -qa | grep pgdg-fedora
pgdg-fedora-repo-42.0-4.noarch

UPDATE2:

Apparently something where temporarily down, it now worked to:

sudo dnf install postgresql11-contrib
[...]
Downloading Packages:
postgresql11-contrib-11.4-1PGDG.f30.x86_64.rpm  285 kB/s | 602 kB     00:02

rpm -qa postgresql\*
postgresql-contrib-11.3-1.fc30.x86_64
postgresql11-11.4-1PGDG.f30.x86_64
postgresql11-contrib-11.4-1PGDG.f30.x86_64
postgresql-11.3-1.fc30.x86_64
postgresql11-server-11.4-1PGDG.f30.x86_64
postgresql11-libs-11.4-1PGDG.f30.x86_64

stack=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION


texts: What is the best way to analyze free space in a postgres database and the grow of usage?

texts:
In Oracle you can in datadictinary analyze free space by using SQL like:

select
   b.tablespace_name, b.file_id, b.file_name, b.bytes/1024 total_KBytes,
   sum(a.bytes)/(1024) sum_free_Kbytes, max(a.bytes/(1024)) max_free_Kbytes
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;

How can I do this in postgres? What are the best tools to view and monitor database space in postgres?


texts:
I have a project where data is being updated by 14 different users that will run over the course of a few months, collection approximately 5.5 thousand rows.. To avoid giving write access to each other's db I have given each their own DB with r/w access. At the end of the collection period I plan to collect them all to a single DB where they all will have read access only, still as individual tables.
My question comes to the following problem. In addition to 14 user tables brought into the read only DB, a last table will be created that will geo-reference each of the other tables as data collected at a specific location for use in POSTGIS.
All of the tutorials and docs I've looked at talk about referencing specific column values in another table, but never the whole table. Roughly the GPS geo-referencing table would look something like this:
COORDINATES    TABLE
x_lat,y_long   table_l
z_lat,w_long   table_m
a_lat,b_long   table_n

However from what I've been able to find online the REFERENCES column type is limited to something resembling the following:
COORDINATES    TABLE(col_z)
x_lat,y_long   col_z_value

Is the only solution to, when bring all the tables from different the individual DB's, to simply bring them into a single table where each row can be referenced by the individual device ID or user ID? I wanted to avoid having to do it that because 5500 rows x 14 tables will be rather large.
Based on the above context I am open to other solutions as well.
Thanks very much for any input!


texts:
I am trying to select a value on one of two fields based on conditions so I am using Select & Case. I am also selecting the sum of sales and the customer name also using case. Here is my current code:
SELECT
    CASE
        WHEN
            (C.USRTAB03 <> 'See Line Items'
                AND D.COMMENT_1 = 'SPA Item')
        THEN
            C.USRTAB03
        ELSE SUBSTRING(D.COMMENT_1, 10, 15)
    END AS SPA_NO,
    CASE
        WHEN
            B.SOPTYPE = 4
                AND SUBSTRING(B.SOPNUMBE, 1, 3) = 'RBT'
        THEN
            SUM((E.UNITPRCE * B.QUANTITY) - B.XTNDPRCE)
        WHEN
            B.SOPTYPE = 4
                AND SUBSTRING(B.SOPNUMBE, 1, 3) <> 'RBT'
        THEN
            SUM(B.XTNDPRCE * - 1)
        ELSE SUM(B.XTNDPRCE)
    END AS SPA_SALES,
    CASE
        WHEN (A.CUSTNMBR = 'XYZ' OR A.CUSTNMBR = 'ABC') THEN RTRIM(A.CUSTNMBR) + ' - ' + C.USRTAB01
        ELSE A.CUSTNMBR
    END AS CUSTOMER_NO
FROM
    SOP30200 AS A
        LEFT OUTER JOIN
    SOP10106 AS C ON A.SOPNUMBE = C.SOPNUMBE
        LEFT OUTER JOIN
    SOP30300 AS B ON C.SOPNUMBE = B.SOPNUMBE
        LEFT OUTER JOIN
    SOP10202 AS D ON C.SOPNUMBE = D.SOPNUMBE
        AND B.LNITMSEQ = D.LNITMSEQ
        LEFT OUTER JOIN
    SOP30300 AS E ON E.SOPNUMBE = SUBSTRING(B.ITEMDESC, 1, 12)
        AND E.ITEMNMBR = SUBSTRING(B.ITEMDESC, 16, 35)
        LEFT OUTER JOIN
    LOCATION AS F ON C.USRTAB01 = F.SALESOFF
WHERE
    A.GLPOSTDT BETWEEN (GETDATE() - 90) AND GETDATE()
        AND (A.SOPTYPE = 3 OR A.SOPTYPE = 4)
        AND A.VOIDSTTS = 0
        AND (C.USRTAB03 <> ''
        AND SUBSTRING(D.COMMENT_1, 1, 8) = 'SPA Item')
        AND B.QTYTOINV <> 0
GROUP BY CASE
    WHEN
        (C.USRTAB03 <> 'See Line Items'
            AND D.COMMENT_1 = 'SPA Item')
    THEN
        ''
    ELSE SUBSTRING(D.COMMENT_1, 10, 15)
END , C.USRTAB03 , D.COMMENT_1 , A.CUSTNMBR , B.SOPTYPE , B.SOPNUMBE , C.USRTAB01 , B.ITEMDESC
ORDER BY CUSTOMER_NO

Here is one example of the results:
SPA_NO  SPA_SALES   CUSTOMER_NO
060517-1MJB 405.00000   ADI-HAPA
060517-1MJB 459.00000   ADI-HAPA
060517-1MJB 211.20000   ADI-HAPA
060517-1MJB 607.50000   ADI-HAPA

The results are correct but I am trying to Group by the SPA_NO so it is only listed once with the total SPA_SALES. Certain conditions make me look in two different locations for the Part Number identified as E.ITEMNMBR so the sales totals above are for each part number.


texts: SQL Server 2012 Performance - Reads per second

texts:
I am sure this has been asked multiple times - but I could not find a good answer anywhere. Hence this question.

I have a azure cloud service that reads data from a SQL Server 2012 Database. The primary use of this data base is Read-Only. The data gets populated once every month and the cloud service only reads from it (SELECT) (Yes - I could move this to a key-value store - but as of now I cannot change the way things are)

What would be a good way to estimate the number of SELECT operations that this database can handle per second. The machine on which this runs is a 4 core, 7GB ram azure vm. I am just looking for some ballpark numbers to reasonably estimate the number of users my service can handle. The table is clustered on the column which I will filter (WHERE) results on.

Would greatly appreciate help with this.

--EDIT--

Specifying more details based on a reply below:

What level of performance is acceptable?

My end-user SLA should be around 2 seconds. So I expect the DB to return in approx 100 - 500 milliseconds

How many rows are being returned from these Selects?

On an average I might be returning around 50 rows per select call. Total data being < 200KB

Size of the database

Around 15M records totalling around 150GB

I understand that this is a vague question - would be great to get some numbers from folks based on their experiences.


texts: SQL Server mirroring / failover alternative

texts:
I am looking at building at reliable database with Microsoft SQL Server, with mirroring and automatic failover. The SQL Server here is standard version, not enterprise one so High Availability Solutions is not supported.

Just wonder if there is any third-party tool we can use as an alternative?

It's SQL Server 2014 I'm using.


texts: Can a contained database SQL User be mapped to a remote user in a linked server?

texts:
Using SQL Server 2017 Standard CU8 on Windows Server 2016.  I've create a contained database user (user defined only within a database with Containment type: partial) with type SQL user with password (so, not a Windows-based user account).  In a linked server definition, on the Security tab, in the Local Server login to remote server login area, is there any way to specify this user as a local user (in order to specifically map it to a remote user)?  I've tried just the username, and also prefixed with the database name (separated by \ and by .) but always get an error Error: ‘contained_user’ is not a valid login or you do not have permission.

In a way this makes sense, since a linked server is an instance-level object and having a contained database dependent on it would be somewhat contrary to the containment idea, but I haven't seen this limitation documented anywhere (such as https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=sql-server-2017#Limitations or https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedsrvlogin-transact-sql?view=sql-server-2017) so wondered if I was just getting the syntax wrong.

By the way, a Windows login-based user created within a contained database CAN be associated with a linked server.

thanks!

Martin


texts: Unable to join on conrelid and confrelid columns in pg_constraint

texts:
I'm trying to get a list of foreign key constraints in the following form:

pk_table | pk_col | fk_table | fk_col

without using pg_get_constraintdef (which would force me to resort to string manipulation).

Is there any reason why the following query doesn't work?

SELECT
  x.conname,
  x.contype,
  x.conrelid,
  x.confrelid,
  pk.tablename,
  fk.tablename
FROM pg_catalog.pg_constraint x
INNER JOIN pg_catalog.pg_tables pk ON x.conrelid!=0 AND x.conrelid=(pk.tablename :: regclass :: oid)
INNER JOIN pg_catalog.pg_tables fk ON x.confrelid!=0 AND x.confrelid=(fk.tablename :: regclass :: oid)
WHERE
  x.connamespace in (SELECT oid FROM pg_catalog.pg_namespace n WHERE n.nspname in ('b2b', 'public'))
  AND x.contype='f'

Error:

ERROR:  relation "sql_implementation_info" does not exist

My hypothesis was that PG is getting confused due to the presence of multiple namespaces, so I tried restricting the result-set to my known schemas, i.e. b2b, public, but this still doesn't work.


texts: Convert Column values into row values with new row headers

texts:
Starting table

Col1|col2|col3
---------------
1   |EDTU|Value1
2   |EDTP|Value2
3   |aaaa|value3
4   |bbbb|value4
5   |cccc|value5

I only need values for EDTU and EDTP in below format with new row header

Expected result:

XXXX  |YYYY
----------
value1|value2

Note :- using where clause if col2 = 'EDTU' we get Value1.


texts: What is the best SSIS Tasks for String Parsing database fields

texts: What is/are the best SSIS Tasks to use in order to search for string patterns.  This relates to standardizing address information.  My developer does not have a confident grasp on using Regular Expressions in his code.


texts: How configure security to query second same server database

texts:
I have a database which holds all my permissions and a central database which holds all the logins for all my application. I need to create view that queries both database which reside on the same server. When I am in Sql Server Management Studio I can run the query with no problems. However, when I integrate it into my application I get a run-time exception.

Cannot open database "abc" requested by the login. The login failed.
Login failed for user 'WORKGROUP\HOME$'

The error is obviously the permissions but I don't usually have to give access to this role. If the query is on the same server shouldn't the regular connection string and database permissions be sufficient?


texts: Mysql 5.5.45 crashing after refreshing web page rapidly(Too Many Connection warning)

texts:
I am using ec2 aws instance for my wordpress webiste.after every 5 to 10 minute and randomly my website going down i tried lots of things like increasing buffer memory ,memory limit but that not working for me so any one know how do i resolve this issue bellow snpiet is the error log

Mysqld log :- http://pastebin.com/8Gp0V5g3

error log:- http://pastebin.com/cpEHHUwm

160201 14:44:06 [Warning] /usr/libexec/mysql55/mysqld: Forcing close of thread 104  user: 'wordpress2'

160201 14:44:06 [Warning] /usr/libexec/mysql55/mysqld: Forcing close of thread 73  user: 'wordpress2'

160201 14:44:06  InnoDB: Starting shutdown...
160201 14:44:07  InnoDB: Shutdown completed; log sequence number 2756685
160201 14:44:07 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete

160201 14:44:07 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160201 14:44:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160201 14:44:08 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.46) starting as process 14406 ...
160201 14:44:08 [Note] Plugin 'FEDERATED' is disabled.
160201 14:44:08 InnoDB: The InnoDB memory heap is disabled
160201 14:44:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160201 14:44:08 InnoDB: Compressed tables use zlib 1.2.8
160201 14:44:08 InnoDB: Using Linux native AIO
160201 14:44:08 InnoDB: Initializing buffer pool, size = 128.0M
160201 14:44:08 InnoDB: Completed initialization of buffer pool
160201 14:44:08 InnoDB: highest supported file format is Barracuda.
160201 14:44:08  InnoDB: Waiting for the background threads to start
160201 14:44:09 InnoDB: 5.5.46 started; log sequence number 2756685
160201 14:44:09 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160201 14:44:09 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160201 14:44:09 [Note] Server socket created on IP: '0.0.0.0'.
160201 14:44:09 [Note] Event Scheduler: Loaded 0 events
160201 14:44:09 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
Version: '5.5.46'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

show full table wp_options as bellow:-

my.cnf file setting in /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#max_connections = 1000
#wait_timeout = 120
#interactive_timeout = 120
#max_allowed_packet = 64M
#innodb_buffer_pool_size = 800M
#innodb_buffer_pool_size = 384M
#max_user_connections = 500
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

error after refreshing the page rapidly.

User privileges wordpress user as bellow:-

Connection's:-


texts: Efficient count query for consecutive time windows

texts:
I have a SQL Server Database: in one table, one of the columns represents the time of an event (of type datetime2(7)). Another column represents a product ID. My goal is to specify a flexible time unit and a time window by defining a start and an end time, and count the number of unique product IDs for each time window. A product ID may thus only appear in one time-window (e.g. the first time-window in which it occurence).

An example:

    EventDateTime            ProductID
2013-01-01 07:44:57.840         5
2013-01-02 07:45:10.093         5
2013-01-03 07:45:21.557         1
2013-01-04 09:07:21.253         2
2013-01-05 09:07:42.680         3
2013-01-06 09:07:49.007         5

How would the SQL-Query look like for this task? By flexible time unit I mean I would like to specify 'years', 'months', 'days' or 'hours'.

The parameters would thus be: time_start, time_end, time_unit.
The result should be a table:
1 -> Count of rows with event time in the first time window
2 -> Count of rows with event time in the second time window
, and so on.


texts:
I wanna store some vendors (like McDonald ,etc) and it's branches in a table.
a vendor may,or may not have branches.

I have created two tables:

1- A vendors table which have name,logo, etc.

2-A venues table which have location_id, vendor_id, etc.

relationship between venue and vendor is many to one.

suppose I have added a vendor name TastyBurger which is a vendor with lots of branches in all over country.

the other day another store comes up with name TastyBurger which isn't a branch of above vendor. and we create another vendor record for it as well.

the other day the first vendor comes up to save another branch.Now here is the question: How can I determine which vendor record was associated with this?

I made effort to make a real life example to make sense. But if not,just ask to explain more.


texts: To generate a script to drop or map the orphan users in a SQL Server Instance

texts:
I am very new to SQL Server. I have a script which find the orphan users in all the databases and generate the script to either drop or map the user to correct login (If available).
Since i want to run the script in vast number of servers, I thought of using PowerShell to help me. But i got 2 problem now:

Error with the script
Error with Power-shell execution

Problem 1: Following script itself returns output, but still it shows some error. I tried to Debug, But i was unable to do so.
DECLARE @orphan_users TABLE (
    Server NVARCHAR(MAX),
    DBName sysname,
    [user_name] sysname,
    type_desc nvarchar(60),
    default_schema_name sysname NULL,
    create_date datetime,
    modify_date DATETIME,
    [sid] VARBINARY(85),
    cr_type int
);

INSERT INTO @orphan_users (Server,
    DBname,
    [user_name],
    type_desc,
    default_schema_name,
    create_date,
    modify_date,
    [sid],
    cr_type
)
EXEC sp_msforeachdb
'use [?]
SELECT
    @@SERVERNAME
    ,DB_NAME() DBname
    ,name
    ,type_desc
    ,default_schema_name
    ,create_date
    ,modify_date
    ,[sid]
    ,Datalength(sid) AA
from sys.database_principals
where type in (''S'')
and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') )
and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'' ,''MS_DataCollectorInternalUser'')';

DECLARE @db_users TABLE (
    [LogInAtServerLevel] NVARCHAR(MAX),
    [UserAtDBLevel] NVARCHAR(MAX),
    DBname sysname
);

INSERT INTO @db_users ([LogInAtServerLevel], [UserAtDBLevel],DBname)
EXEC sp_msforeachdb
'use [?]
SELECT
    susers.[name],
    users.[name],
    DB_NAME() DBname
from sys.database_principals users
inner join sys.database_role_members link
   on link.member_principal_id = users.principal_id
inner join sys.database_principals roles
   on roles.principal_id = link.role_principal_id
inner join sys.server_principals susers
   on susers.sid = users.sid';

SELECT
    a.Server,
    a.DBName,
    a.user_name,
    b.name,cr_type,
    c.UserAtDBLevel,
    CASE
        WHEN (b.name IS NULL) and (cr_type > 17)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
        WHEN (b.name IS not NULL) and (cr_type < 17) and (c.UserAtDBLevel is null)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; ALTER USER ' + QUOTENAME(a.user_name) + ' WITH LOGIN = ' + QUOTENAME(b.name)+';'
        WHEN (b.name IS not NULL) and (cr_type < 17) and (c.UserAtDBLevel is not null)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
        WHEN (b.name IS NULL)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
        WHEN (b.name IS not NULL) and (cr_type > 17)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
    END as [remediation]
FROM ((@orphan_users a
    left join sys.server_principals b on a.user_name = b.name)
    left join @db_users c on c.DBname=a.DBName and c.LogInAtServerLevel=b.name)
ORDER BY a.user_name;

and results are

But with the error, similar error appear for all databases in the instance

Incorrect syntax near 'tempdbfrom'. Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'modelfrom'. Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'msdbfrom'. Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'dbamonfrom'. Msg 102, Level 15, State 1, Line 3

Problem 2: Since the Script is accurate (at least in my Environment) so i tried to run it with power-shell as follows. It only returns the errors, no Output
Invoke-SqlCmd `
    -InputFile "D:\temp\up.sql" `
    -ServerInstance 'abc\sql2012' `
    -IncludeSqlUserErrors

I have tried all the means from redirection of output using > to start and stop transcript. None of them seemed to capture the result that the query gives, However the errors are captured accurately..
It would be a great help for me if any one of the problem is solved.


texts: Does a Target Service need to do anything for a conversation that is ended?

texts:
We have two queues, SSBInitiator and SSBTarget.  In my example situation, I have ran the following code to create a conversation:

BEGIN DIALOG CONVERSATION @ConversationHandle
                FROM SERVICE [//BLAH/Events/SSBInitiator]
                TO SERVICE '//BLAH/Events/SSBTarget'
                ON CONTRACT [//BLAH/Events/SSBContract]
                WITH ENCRYPTION = OFF;

Now lets say that, for some reason I've decided this conversation is unnecessary: either the user cancelled the request, or something else happened.  I know I can end the conversation using either:

end conversation @ConversationHandle

or

END CONVERSATION @ConversationHandle
      WITH ERROR = 1 DESCRIPTION = 'Ending Convo cause reasons';

Now, with the conversation ended and no messages have been sent on it, does the Target service need to do anything with the conversation?  IE, is there additional processing that needs to happen to make sure the conversation is completely closed?


texts: Why is using a CTE so much faster than using a #Temp Table in this query?

texts:
I have 2 different queries for identifying dups (below). The only difference between the 2 queries is that one uses a CTE and the other uses a #Temp table.

Does anyone know why the CTE is so much faster than the #Temp table (0:20 seconds VS. 1:22)?

I would rather use the CTE but I need to run 2 STATEMENTS using the CTE (DELETE from CTE and then INSERT INTO table FROM CTE) but SQL Server only allows you to write one STATEMENT on the CTE.

Query 1:

WITH DUPS AS(
   Id,
   Column1,
   Column2,
   Column3,
   RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
   FROM mytable
)
  Select top 1 * FROM DUPS WHERE RN > 1

Query 2:

 SELECT
           Id,
           Column1,
           Column2,
           Column3,
           RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
       INTO #DUPS
       FROM mytable

       Select top 1 * FROM #DUPS WHERE RN > 1


texts:
Question like this:

Consider the relation scheme ABCDEF satisfying the following functional dependencies:

BC --> ADEF

CD --> B

E --> D

BC is a key and designated as primary. Explain why the relation is in 3NF.

It seems not a 3NF to me, because E-->D is a transitive dependency, but the question asked like this it must to be a 3NF ? So how do I explain it's a 3NF?


texts: ORA-00937 when calculating a sub-value in an aggregate select statement

texts:
The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question.

I want to use an aggregate-function in a select-statement which needs some additional calculation to yield the result. My main goal is to keep the whole calculation in one statement since the result is to be displayed verbatim in the backend software.

Let there be a table T defined by this DDL

create table T(
  ID number,
  NUM number,
  TXT varchar(32)
);

insert into T (ID, NUM, TXT) values (1, 1, 'Text1');
insert into T (ID, NUM, TXT) values (2, 2, 'Text2');
insert into T (ID, NUM, TXT) values (3, 0, 'Text3');

Now the objective is to sum all values of the num-attribute for all selected rows. I can do this by using

select sum(num) from T

Now for the tricky part, assume there is a second table from which we calculate an additional value to be combined with the result of our first select. The select involved will return a single number by being an aggregated value by itself. (To keep the schema simple for this demonstration, I use the same table, but the described problem remains regardless of the second table being used)

select case (select count(*) from T) when 0 then 1 else 0 end from dual;

When combined, we will leave out the select ... from dual part which is only used to get the intermediate result displayed. Together the query will be

select (case (select count(t1.num) from T t1) when 0 then 1 else 0 end)*sum(t2.num) from T t2;

where we assign aliases to each table to make the usage clear.

The result of this query is an Oracle exception: ORA-00937: not a single-group group function. Since the case-expression yields a single value and no reference is made to any resources outside the expression, I wonder what basic principle I violated by this select-statement. In addition to that, MySQL and PostgreSQL yield the desired result.

As I was searching for a solution, I also tried this select-statement:

select (case when (exists (select 1 from T where NUM = 0)) then 0 else 1 end)*sum(NUM) as p from T where NUM > 0;

Here I disposed of the inner aggregate-function count(t1.num) to rule out problems with nested aggregations. This statement gave the same Oracle error message, while the calculation of the additional value caused no trouble:

select (case when (exists (select 1 from T where NUM = 0)) then 0 else 1 end) as p from dual;

Question: Can someone please explain what goes wrong? (And especially I wonder if Oracle does not implement standard SQL, or of MySQL and PostgreSQL extend the standard).

Technically the solution to my problem would be a join where both, the aggregate and the additional value are computed separately, and then combined in the outer select-statement. My focus does not lie on how one can solve this problem, but on am I missing something in Oracle, or is Oracle missing something?.


texts: Creating a time-series database for a power plant scenario

texts:
Considerations

I need to create timeseries tables in a SQL database, which will hold data about power generated by a power plant. I need four columns there:

(
    power_plant_id int FK,
    power_produced int,
    power_produced_when datetime,
    record_created_when datetime
)

The reason why I need the last column, is because sometimes we re-measure the power that was produced, and instead of UPDATEing the row, we just add a new one and then we query for most recently created rows, e.g.:

power_plant_id    power_produced    power_produced_when    record_created_when
--------------    --------------    -------------------    -------------------
             1               123       01-01-2019 01:00       25-08-2019 01:11
             1               124       01-01-2019 02:00       25-08-2019 01:11
             1               125       01-01-2019 03:00       25-08-2019 01:11
             1               555       01-01-2019 01:00       30-08-2019 01:11
             1               444       01-01-2019 02:00       30-08-2019 01:11

so the query for 01-01-2019 from 01:00 till 03:00 should return:

power_plant_id    power_produced    power_produced_when    record_created_when
             1               123       01-01-2019 01:00       25-08-2019 01:11
             1               555       01-01-2019 01:00       30-08-2019 01:11
             1               444       01-01-2019 02:00       30-08-2019 01:11

The questions

Now to the questions:

What do you think about column names, are they descriptive? Is there
a better naming option?
What do you think about they way I retain and retrieve data? Is "append-new-and-query-the-freshest" good strategy, or should I go for UPDATE? What are pros and cons with one over another?
One of my colleagues pointed that if we miss a row in the table, we have no way to find out, because we don't keep information about what's the interval between rows. The solutions for that which I have in mind are:

Set up additional table intervals which holds mapping table_name <-> datetime interval
Set up scheduled scripts which query the database to retrieve the data from timeseries tables, combine it with table intervals and see if there are no 'holes'

Is this a good way to solve that problem?

If you can think about some good read about this topics, I'll be super happy to accept it :)


texts: Database functional dependency BCNF help

texts:
I understand most of BCNF and have been able to complete numerous problems with it, I'm just having trouble applying it to the following situation:

R(A,B,C,D,E) FDs: { A->D; BE->A; CD->E }

Here's what I have so far:

Relationship1: (A,D)
Relationship2: (A,B,E)
Relationship3: (B,C,E)

However with this setup the CD->E functional dependency isn't considered. Should that be taken into account in a new relationship, or should D (or maybe even A?) be added to Relationship 3?

For what it's worth, I determined that:

BCE+ = ABCDE
BE+ = ABDE
AC+ = ACDE

I'm just not sure where to go from here. Thanks in advance for the help.


texts: Best-practice use of Resource Governor in SQL Server 2017

texts:
I am working on a server that houses both an OLTP workload and a data warehousing/reporting workload.  The OLTP system requires sub-second (in the milliseconds) response time while the reporting workload has a threshold of minutes.  The issue is some users will run multiple reports during peak transactional activity in our OLTP environment, thereby slowing the OLTP queries down.  On their own, the OLTP queries tend to run 20-30ms,  but when many reporting/data warehouse queries are fired up simultaneously the OLTP queries begin to run in the 4-5 second range.  Our bottleneck appears to be CPU, as this is where all of our waits are.

I would of course like to separate the workloads on two different servers, but I’m wondering if we could get a quick improvement via use of Resource Governor.

I’m considering two approaches:  (1) set a minimum allotment of CPU to the OLTP system so that it is guaranteed 25% resources, or (2) max and cap out the reporting\data warehouse queries so they cannot take more than 50% of the CPU.

Does anyone here have any practical experience with Resource Governor that would guide me in one of these two directions? Or is there another usage pattern that you’d suggest instead?