texts:
I have a project at the moment that is close to be finished. I have been asked (at the very end) to log a specific event. The event will occur very often (easily a few times each 5sec). Each event creates a record in the log table. The database must be MySQL.

My experience in database consists of making queries, not building the database. And, i'm new to MySQL.

The log table is:

create table LogEvent
{
    id bigint auto_increment primary key,
    area smallint unsigned not null,
    area_zone tinyint unsigned not null,
    value1 float not null,
    value2 float not null,
    reason char(2) not null
}

The id is just to have a primary key. area is from 1 to 100 at the moment, but it can grow. We can consider it as a category. area_zone is like a subcategory of area. Other values are insignificant for my problem.

There will be a lot of inserts but no delete/update. The select queries will always be like select * from LogEvent where area=1 and area_zone=1. All select statements will include area and area_zone. Select queries won't happen often, maybe a few times a week.

It will grow over 1 million records easily. And eventually, millions per area/area_zone.

So, my questions are:

1) I'll need partitioning for sure. But, can I do it per area/area_zone? So, two values partitionning. Or should I use a mediumint and concatenate the two values together in my application (bitwise operation)? That way, it will be a single value partitioning

2) MyISAM or innoDB?

3) Should I put index somewhere? I'm very bad in this domain.

4) Hard disk space won't be a problem, but what about RAM? How much should I have? According to MS SQL Server, it use 28 bytes to store a record, so it should be similar in MySQL.

It's the only table in this database.

I hope I have been clear enough to help you help me. Thanks.


texts: Store Decimal values without Trailing Zeros

texts:
I am using SQL Server 2008 R2 version.
Is there any method to store decimal value in SQL without extra trailing zeros(like Float data type format).
I have created a column with decimal data type with highest precision.
Actual values:
2.50000000
4.55530000
1.85333310

Expected:
2.5
4.5553
1.8533331

Is it possible to store  values in above expected format with highest precision ?


texts:
I'm currently uploading one of Wikipedia's dump files. It has 1GB compressed and ~7GB uncompressed. It has been importing it for 6 hours already. The size of the imported table in MySQL is at the moment 1.5GB(data+indexes). Will it continue to import until the size of the table will reach 7GB?

Note: The data directory of the database in on an external HDD. Considering this is only a development, so I'm not concerned by the real performance.


texts: Practical application of EXISTS vs. LEFT JOIN to find addresses for delivery

texts:
Our end users have many ways of shipping product to our customers. They can use the customer address in one table ([customer]), a specific delivery address in another ([dropship]), or yet another delivery address in another table (for customers with multiple locations) ([delivery]). These all have roughly the same structure, with the same column names for the same data points (e.g. [address] is the address in all tables).

Currently, our logic for finding the delivery address of an order is as follows:

SELECT
    Address = COALESCE(r.address, d.address, c.address)
FROM [order] o
JOIN customer c
    ON o.customerid = c.customerid
LEFT JOIN delivery d
    ON o.customerid = d.customerid
    AND o.delivaddressid = d.delivaddress
LEFT JOIN dropship r
    ON o.orderid = r.orderid;

Based on conversations and not a few blog posts, EXISTS seems to be preferred.

However, this is a new paradigm to me and I'm trying to wrap my brain around it. Do I just replace the LEFT JOINs with

WHERE EXISTS
    (select 1 from delivery d
        where o.customerid = d.customerid
        and o.delivaddressid = d.delivaddress)

This won't allow me to use the data from [delivery] unless I toss it in the FROM clause and create a cartesian product, right?


texts: What happens in this strange SQL Server Mirroring situation?

texts:
Let's assume that two SQL Server instances (S1 and S2) are in synchronous mirroring or in a synchronous Availability Group. Now the following happens:

A write happens on S1 and transaction commit is initiated by the client
The write is hardened on S2 (but not yet hardened on S1)
S1 loses power and the commit log record is never written
S2 loses power (the transaction is hardened already)
S1 starts up and undoes the transaction
S2 starts up and keeps the transaction

Now S2 is further along in time! It has a write that S1 does not have. The two replica have diverged.

What happens in such a situation? What happens when more writes arrive at S1 and take S1 into an incompatible history regarding S2? The write histories would look like a fork.


texts: Invoice days and amount with Distinct Value

texts:
I am having a billing table like as above.

I need to get a billing age wise report by using distinct of Branch, UserId, Customer, BillType.

I need to wirte a Stored procedure for getting the following result.

How to write a query for this


texts: Fetch records only for today's date by using PHP & Mysql

texts:
I have a column in my table as date and time format like YYYY-MM-DD HH:MM:SS
And i want to fetch data which was stored on current date only, everyday I'll be getting data for 24hrs only,


texts: How to recover a DB with a missing .mdf file?

texts:
I have a DB and when I detach it I cannot find its .mdf file. I only have .ndf and .ldf files in my DATA folder. Also, I do not have backup file...

How to recover my DB?
Can I do something without the .mdf file?

P.S. On this server, I have more DBs and most them have .mdf files. Only a few are missing their .mdf files. Why is that?


texts: Update row where associated row does not exist

texts:
Given the table named ROLE:

role_id[PK] NUMBER
role_name   VARCHAR2

Using only SQL (no PL/SQL) how would I write a MERGE (or UPDATE?) statement that would update the role_name as below, but only for rows where another row with the new role_name does not already exist? (By "new role_name" I mean 'prefix-' || role_name)

UPDATE role
SET role_name = 'prefix-' || role_name
WHERE role_name LIKE 'com.example.%';


texts: Postgis building with `make docs`, "cannot parse /html/docbook.xsl"

texts:
When I try to build the docs, I'm getting,

make -C doc html
make[1]: Entering directory '/home/ecarroll/code/postgis/doc'
make -C html/image_src images
make[2]: Entering directory '/home/ecarroll/code/postgis/doc/html/image_src'
make[2]: Nothing to be done for 'images'.
make[2]: Leaving directory '/home/ecarroll/code/postgis/doc/html/image_src'
/usr/bin/xsltproc --param section.autolabel 1 --param section.label.includes.component.label 1 --param chunk.section.depth 0 --param generate.section.toc.level 2 --param funcsynopsis.style kr --param admon.graphics 1 --param admon.textlabel 0 --param simplesect.in.toc 0 --param use.id.as.filename 1 --param chunk.quietly 1 --nonet --stringparam html.stylesheet style.css  \
    --output html/postgis.html \
    /html/docbook.xsl \
    postgis-out.xml
warning: failed to load external entity "/html/docbook.xsl"
cannot parse /html/docbook.xsl
Makefile:258: recipe for target 'html/postgis.html' failed
make[1]: *** [html/postgis.html] Error 4
make[1]: Leaving directory '/home/ecarroll/code/postgis/doc'
GNUmakefile:89: recipe for target 'docs' failed
make: *** [docs] Error 2


texts: AWS RDS convertible RIs do not exist, so how can I gauge whether my instance is enough before I commit for a year?

texts:
I just migrated my small PostgreSQL database to AWS RDS, and I used a db.m4.large instance class.

For the first 12 hours I found the database to be performant, and here's some key metrics:

CPU utilization around 15%
DB connections around 20
Freeable memory around 5 GB
Write IOPS around 5/s, with spikes of 10-15/s
Read IOPS around 0.4/s
Queue depth around 0.015

It looks like to me that I'm well within the limits of this instance class.

My website is very stable and never has significant spikes. The growth is limited and I don't expect that traffic will increase more than 30-50% in a year.

Do you think it's safe to commit to a Reserved Instance in size db.m4.large?


texts: Grant user access only to specific tables

texts: I want to grant a user access only to specific tables and I want to do it with least administrative effort. All tables are located in a database call 'Reporting'. And I only want the user to access tables which contain the word 'Finance'. Any idea how I could do it with least administrative effort?


texts: How to migrate data from Google AppEngine Datastore

texts:
How to migrate data from Google AppEngine's Datastore to other database (not Datastore)?

There is appcfg.py download_data but it produces sqlite3 file with empty bulkloader_database_signature and useless result tables.


texts:
I could have a nullable field or I could do basic normalisation, having another table which may or may not join to a row in this table.
My instinct was to use the second method, as this is the "right" (normalised) way of handling this. However, thinking about this, it can have drawbacks.

The whole entity is not easily visible in its table (you have to - first find and then - jump to its linking tables)
you now have to remember to join the table(s)
It could lead to loads and loads of tables (I don't know if this is an issue for performance)

What do you usually do? It it worth creating a new table just to have an optional property for an entity?


texts: How to pass a variable to a stored procedure?

texts:
Calling the following procedure in DO $$ bloc seems to not work correctly .

Procedure :

CREATE OR REPLACE PROCEDURE pLog(P_VAR_LOG text)
AS $$
BEGIN
  IF D_SetLog THEN

      RAISE NOTICE '[LOG %] %',to_char(sysdate,'DD/MM/YYYY - HH24:MI:SS'),P_VAR_LOG;

  END IF
;

END;
$$ LANGUAGE plpgsql;

I tried to declare the PROCEDURE in the declare bloc but it's not working.

Here is the DO $$ bloc definition :

DO $$
DECLARE

CRITERE1 CURSOR FOR
    SELECT
            DOSSIER.ID_DOSSIER,
            DOSSIER.ED_ETAT
    FROM
        IMM_DOSSIER DOSSIER,
        IMM_CHANTIER CHANTIER
    WHERE
        DOSSIER.ID_DOSSIER = CHANTIER.ID_DOSSIER
        AND DOSSIER.ED_ETAT <> 'CLOS'
        AND CHANTIER.DATE_FIN_REELLE is null
        AND CHANTIER.DATE_FIN_SOUHAITEE is not null
        AND CHANTIER.DATE_FIN_SOUHAITEE < LOCALTIMESTAMP

UNION

    SELECT
            DOSSIER.ID_DOSSIER,
            DOSSIER.ED_ETAT
    FROM (
            SELECT
                DEVIS.ID_DOSSIER,
                MIN(TRAVAUX.DATE_FIN_SOUHAITEE) DATE_FIN_SOUHAITEE
            FROM
                IMM_DEVIS DEVIS, IMM_TRAVAUX_DEVIS TRAVAUX
            WHERE
              DEVIS.ID_DEVIS = TRAVAUX.ID_DEVIS
              AND TRAVAUX.DATE_FIN_REELLE is null
              AND TRAVAUX.DATE_FIN_SOUHAITEE is not null
            GROUP BY DEVIS.ID_DOSSIER
        )
        DOSSIER_CIBLE,
        IMM_DOSSIER DOSSIER
    WHERE
        DOSSIER.ID_DOSSIER = DOSSIER_CIBLE.ID_DOSSIER
        AND DOSSIER.ED_ETAT <> 'CLOS'
        and DOSSIER_CIBLE.DATE_FIN_SOUHAITEE < LOCALTIMESTAMP;

-- Def des enregistrements pour les curseurs
V_C_CRITERE1 RECORD;

V_INSERT INTEGER := 0;

D_CodeRet INTEGER;

D_SetLog    BOOLEAN;

/*****************************************************************************
**  NOM MNEMONIQUE  : pLog
*****************************************************************************/

/*****************************************************************************
**  MAIN
*****************************************************************************/
BEGIN

    D_SetLog    := TRUE
;

    CALL pLog('Suppression des anciennes alertes : ')
;
END $$;

When i execute this sql script i get the following :

SET
DROP SEQUENCE
WARNING:  there is no transaction in progress
COMMIT
CREATE SEQUENCE
WARNING:  there is no transaction in progress
COMMIT
CREATE PROCEDURE
DO

The NOTICE is not being displayed and can't figure out why.


texts: Error creating linked database to oracle

texts:
CREATE DATABASE LINK myLINK CONNECT TO username IDENTIFIED BY password USING 'PROD';

OPT=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.6.1.5)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

I get following error:

  A connection to a database or other service was requested using
             a connect identifier, and the connect identifier specified could not
             be resolved into a connect descriptor using one of the naming methods
             configured. For example, if the type of connect identifier used was a
             net service name then the net service name could not be found in a
             naming method repository, or the repository could not be
             located or reached

I can connect using sqlplus though and can get response from tnsping.


texts: Using `nextval` and `currval` inside one CREATE TABLE

texts:
Let's say that we would like to model a team of people that can have subteams like so:

CREATE SEQUENCE seq_teams;
CREATE TABLE teams (
    tid INTEGER PRIMARY KEY DEFAULT nextval('seq_teams'),
    sid INTEGER REFERENCES teams(tid) DEFAULT currval('seq_teams'),

    name VARCHAR(40) NOT NULL
);

So, if the team is not a "subteam" of any other team, then tid and sid have the same value. Otherwise, sid is the id of the superteam for team tid.

So, if I want to insert a superteam, I can do just:

INSERT INTO teams (name) VALUES ('superteam 1')

Is there a guarantee that tid and sid will be the same in this case? Is there some better (well known) design that can deal with recursive data structures?


texts: Window Azure SQL Server DB Backup to a local-Premise

texts:
One of my customers stopped subscribing to our services and they INSIST to have their data stored in their local SQL Server. Their data size is quite some amount though.

We did try to use the Azure Sync (Preview) and the process is painful and troublesome.

Question came in mind, is there a traditional way that backup to .bak and restore to their SQL Server? Is there any other way of doing this?


texts: Install Oracle 11gR2 driver for Node.js 0.10.20

texts:
I want to connect Oracle 11gR2 and Node.js 0.10.20. I use this package but I don't understand this part of installation process. Can you explain it to me?

# Replace /opt/instantclient_11_2/ with wherever you extracted the Basic Lite files to
echo '/opt/instantclient_11_2/' | sudo tee -a /etc/ld.so.conf.d/oracle_instant_client.conf
sudo ldconfig


texts:
I runned this in mysql:

UPDATE db SET Insert_priv='Y', Create_priv='Y' WHERE Db='wordpress_fixed';

But this not preventing from inserting more data to database wordpress_fixed. Is it supposed to work that way?


texts: How to migrate large blob table from mysql to postgresql?

texts:
I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the most important table in my app.

Table structure is very simple:

mysql> show create table samples;
.. skipped ...
CREATE TABLE `samples` (
      `File_ID` int(11) NOT NULL,
      `File` longblob,
      PRIMARY KEY (`File_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

but it is very large (> 20 Gb).

I've tried to use --hex-blob parameter of mysqldump - but data in this format is not accepted by PostgreSQL when I tried to use resulting dumpfile as a command file. Another option I've tried is using --tab option to just get a dump and then insert it to PostgreSQL with COPY command - but --hex-blob is not working with --tab and PostgreSQL still does not accept the dumpfile saying there are invalid characters in it.

I'd be very glad to get any advice on this matter - although I'm starting to think that writing a custom migration tool is not a bad idea after all...


texts: Golden Gate realtime replication basic question

texts:
I have a task to replicate unidirectional real time data from source database A to target database B. I do not need all the table from A, only few tables and few columns inside those tables. Say 10 tables and 30 columns from A go into 6 tables on database B.

Is this doable in Golden Gate (GG)? Oracle 11g source and 12c target DB version in same OS version.

In addition to the above, when GG is replicating can it insert a sequence number on a column/table in database B?

Any links you can point, as how to do the above is very much appreciated?


texts: How do I identify the column(s) responsible for "String or binary data would be truncated."

texts:
I am generating some queries automagically with code I wrote to SELECT from a remote Pg database, and insert into a local SQL Server database. However, one of them is generating this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. (SQL-22001) [state was 22001 now 01000]
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (SQL-01000) at .\insert.pl line 106.

How do I find out what column is generating that error and lacks the length for the input? Is there a way to do this without brute force-guessing all the varchar?


texts: How can I cache queries in sqlalchemy just like I do it in mysql by using SQL_CACHE keyword?

texts:
I want to cache sqlalchemy,i have discovered that using keyword SQL_CACHE in mysql can be helpful in caching queries on demand.
But how do i do it in sqlalchemy ?
Is it possible ?


texts: UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies

texts:
I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly.

Step by step:

Assembly:

CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH PERMISSION_SET = SAFE

Type:

CREATE TYPE [dbo].[TParams] EXTERNAL NAME [UDT.TParams].[Sql.UDT.TParams]

Table:

CREATE TABLE [dbo].[Dic:Table:With&Weird:Name](
    [Id] [tinyint] NOT NULL,
    [Group_Id] [tinyint] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Code] [varchar](25) NOT NULL,
    [PersonTypes_IDs] [varchar](50) NOT NULL,
    [Unique] [bit] NULL,
    [SortIndex] [tinyint] NOT NULL,
    [FieldName]  AS (replace([Code],':','_')),
    [GUI:Edit:Type] [varchar](64) NULL,
    [GUI:Edit:Params] [dbo].[TParams] NULL,
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Statistics (it's auto-created by SQL Server):

CREATE STATISTICS [weirdStatistics] ON
      [dbo].[Dic:Table:With&Weird:Name]([GUI:Edit:Params])

Then we have scheduled weekly updates on all statistics with FULLSCAN:

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [weirdStatistics] WITH FULLSCAN

Getting this error after above command:

  Msg 1038, Level 15, State 4, Line 3
  An object or column name is missing or empty.
  For SELECT INTO statements, verify each column has a name.
  For other statements, look for empty alias names.
  Aliases defined as "" or [] are not allowed.
  Change the alias to a valid name.

How do I fix this error, or do I just skip the statistics update?

UPDATE: Opening properties windows of [weirdStatistics] in SSMS, will show error: DataReader.GetFieldType(0) returned null. (SqlManagerUI). Any other statistics for table opens normally.


texts: FK constraint fails while inserting into child on Disjoint tables

texts:
I'm trying to test the concept of disjoint tables in MySQL, so I created some tables:

CREATE TABLE item (
  id int NOT NULL AUTO_INCREMENT,
  type ENUM('label', 'checkbox') NOT NULL DEFAULT 'label',
  PRIMARY KEY (id, type)
  ) ENGINE = InnoDB ;

CREATE TABLE label (
  item_id INT NOT NULL,
  item_type ENUM('label') NOT NULL DEFAULT 'label',
  reviewer INT NOT NULL,
  PRIMARY KEY(item_id, item_type, reviewer),
  CONSTRAINT `fk_1` FOREIGN KEY (item_id, item_type)
                    REFERENCES `item` (id, type) ON DELETE NO ACTION ON UPDATE CASCADE
  ) ENGINE = InnoDB ;

CREATE TABLE checkbox (
  item_id INT NOT NULL,
  item_type ENUM('checkbox') NOT NULL DEFAULT 'checkbox',
  reviewer INT NOT NULL,
  PRIMARY KEY(item_id, item_type, reviewer),
  CONSTRAINT `fk_2` FOREIGN KEY (item_id, item_type)
                    REFERENCES `item` (id, type) ON DELETE NO ACTION ON UPDATE CASCADE
  ) ENGINE = InnoDB ;

I had no problems inserting into the parent table:

INSERT INTO item VALUES(1,'checkbox');

But if I then try to insert into the child table:

INSERT INTO checkbox VALUES(1,'checkbox',1);

I get a "constraint failed" error:

  #1452 - Cannot add or update a child row: a foreign key constraint fails (checkbox, CONSTRAINT fk_2 FOREIGN KEY (item_id, item_type) REFERENCES item (id, type) ON DELETE NO ACTION ON UPDATE NO ACTION)

I've re-read my DDL several times and I can't find what is it that I'm doing wrong.
What am I missing?

I tried this in sqlfiddle.com, which means MySQL 5.6.

Also, since I have a box with 5.0.45 and phpMyAdmin at hand, I tried inserting with the GUI (in case I was messing up something else) but got the same results.


texts: Pros and cons in combining current data and historical data in the same table

texts:
What is the pros and cons in designing a RDB table to store both current data and historical data together? Is it scaleable?

Is this approach better than having one table for current data and one table for historical data?


texts:
Suppose I have two relations R and S and I execute the following query:

Select R.* from R,S where R.a=S.a and is unique R

What is the effect of the is unique clause here ?


texts: Adding a dropped check-constraint what queries the table

texts:
I'm using a Postgres to preist a hierarchical data set; the table I'm using is something like this:

CREATE TABLE layers (
    bucket      text not null,
    uniq_id     serial not null,
    name        text not null,
    parent      text,
    value       text,

    constraint layers_pk primary key (bucket, uniq_id)
);

The name value should be unique in a defined scope, determined by parent value. In order to restrict and guarantee this I decided to use a CONSTRAINT CHECK, by a function relying on SELECT the current state of database.

CREATE FUNCTION layers_name_uniqueness_check(bk text, nm text, pr text) RETURNS BOOLEAN AS $$
    DECLARE
        cnt integer;
    BEGIN
        IF pr IS NULL THEN
            SELECT INTO cnt COUNT(*) FROM layers WHERE bucket = bk AND name = nm AND parent IS NULL;
        ELSE
            SELECT INTO cnt COUNT(*) FROM layers WHERE bucket = bk AND name = nm AND parent = pr;
        END IF;
        RETURN cnt = 0;
    END;
$$
LANGUAGE PLPGSQL;

ALTER TABLE layers
    ADD CONSTRAINT layers_name_uniqueness
    CHECK (layers_name_uniqueness_check(bucket, name, parent))
;

This approach seems working well if provided from the starting point. If I want to apply this CONSTRAINT CHECK to a table that has already some records, this approach will not work boviously, as I'm running query on the table, and the given record is already there.

INSERT INTO layers (bucket, name, parent, value) VALUES ('bisphone', 'A', null, 'Hello');
INSERT INTO layers (bucket, name, parent, value) VALUES ('bisphone', 'A.A', 'A', 'Hello');

ALTER TABLE layers DROP CONSTRAINT layers_name_uniqueness;

ALTER TABLE layers
    ADD CONSTRAINT layers_name_uniqueness
    CHECK (layers_name_uniqueness_check(bucket, name, parent))
;

The Output

[23514] ERROR: check constraint "layers_name_uniqueness" is violated by some row

I need to know that is it possible to fix this by a trick or should I address the problem (preventing duplicated name value in the the determined scope by parent) with a different approch. Any help?


texts:
We are in development stage and to be able to give the developers an integration test environment, we need to give them access to the database, they can share and test their code in the system against.

The application is written in .net (MVC 4) and we are using SQL server 2008 r2 for database. We have restored the database they need to conect on the server, but I'm not quite sure what is the best approach in user management for this case?

Do we need to create an account for each developers, or it would be easier to create a generic account and give them this user/password to use? What would be the advantages and disadvantages of each option?

For example, If more than one person uses an account, would it be possible to differentiate who did what?

Also if we want to go with creating a generic account, is it better to create a SQL authentication account or windows authentic one?


texts:
What's the minimum privilege needed to alter a foreign key constraint?

My migration script stopped working after MySQL 5.5.41 fixed this bug:

InnoDB permitted a foreign key to be created which referenced a parent table for which the user did not have sufficient privileges. (Bug #18790730)

I get this error:

  SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'foo'@'localhost' for table 'core.users' (SQL: alter table `user_baz` add constraint user_baz_user_id_foreign foreign key (`user_id`) references `core`.`users` (`id`) on delete cascade on update cascade)

Which means I need to fix the privileges. What's the minimum privilege I need?


texts: Why we design distributed availability group?

texts:
In my existing environment, there is a design proposed for using distributed AG. Can you please let me know few my queries regarding this?

What is the distributed high availability benefit which we can not leverage with traditional AG? If you can explain one scenario of failover which we can not avail in traditional AG?
In which scenario the design of distributed AG is preferable?


texts: Can't get users default schema to change - SQL Server 2008

texts:
We are migrating from a SQL Server 2008 instance on a shared host to our own box. Our issue is we have a bunch of tables, procs and views that are prefaced with a schema (let's call the schema 'me') other than dbo.

So:

me.Employees
me.vw_EmployeesStuff
me.proc_SaveEmployee

We're having issues now that we have restored the backups from the shared host to our new box.  When logged in as 'me', we need the queries to run like this:

SELECT * FROM vw_EmployeesStuff

And not have to query like:

SELECT * FROM me.vw_EmployeesStuff

We've tried to change the default schema for the user from dbo to me, but it doesn't seem to be 'taking'. Any thoughts or help is much appreciated. TIA


texts: Partition guides and possible solutions?

texts:
We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be delete and archive. The one option to partition each individual tables separately based on dates. Is there any option or solution where based on the linkID in tblLink I do the partition together for the rest of the tables? Is partitioning the right way to move or is there any other options?

All these are InnoDB tables.

CREATE TABLE IF NOT EXISTS `tblLink` (
`linkID` int(5) NOT NULL,
-- other columns
PRIMARY KEY (`linkID`)
);

CREATE TABLE IF NOT EXISTS `tblEmailLog` (
`emailLogID` int(11) NOT NULL AUTO_INCREMENT,
`linkID` int(11) NOT NULL DEFAULT '0',
-- other columns
PRIMARY KEY (`emailLogID`)
);

CREATE TABLE IF NOT EXISTS `tblEventAlert` (
`eventAlertID` int(11) NOT NULL AUTO_INCREMENT,
`linkID` int(11) NOT NULL DEFAULT '0',
-- other columns
PRIMARY KEY (`eventAlertID`)
);

CREATE TABLE IF NOT EXISTS `tblMainData` (
`mainDataID` int(11) NOT NULL AUTO_INCREMENT,
`linkID` int(5) NOT NULL,
-- other columns
PRIMARY KEY (`mainDataID`)
);

CREATE TABLE IF NOT EXISTS `tblSubData` (
`subDataID` int(11) NOT NULL AUTO_INCREMENT,
`mainDataID` int(11) NOT NULL,
`linkID` int(11) NOT NULL,
`eventAlertID` int(11) NOT NULL,
-- other columns
PRIMARY KEY (`subDataID`)
);


texts: mysqli importer file - disable foreign keys

texts:
I am using this script to import a mysql file but I want to set foreign key checks to 0 when he will do the import.

https://github.com/davcs86/php-mysqlimporter/blob/master/php-mysqlimporter.php

Calling the file below with this function

include("Mysqlimporter.php");
$mysqlImport = new MySQLImporter($host, $username, $password);
$mysqlImport->doImport($source . "/var/dump.sql", $dbname, true, true);
}


texts: SQL server agent unable to establish connection

texts:
I have recently installed sql server 2014 enterprise version on windows 2016 server. For some reason sql server agent is not starting but sql server service is up and running. I am not really sure what is happening. From the sql server agent log file , I could see below information. Do you think I should repair the sql server ? I have read else where it might be due to sql server native client installation problem ? Also, in the event log I noticed the permission related issue. I did some research and I tried to provide proper permissions to that APPID and CLSID but still I could not bring that sql agent up. Thanks

========================================================

             2019-10-09 00:54:15 - ? [100] Microsoft SQLServerAgent
           version
           12.0.6024.0 (X64 unicode retail build) : Process ID 55496
           2019-10-09 00:54:15 - ? [495] The SQL Server Agent startup
           service account is WORKGROUP\D03XXX
           2019-10-09 00:54:15 - ! [150] SQL Server does not accept the
           connection
          (error: 0). Waiting for Sql Server to allow connections.
          Operation attempted was: Verify Connection On Start.
           2019-10-09 00:54:15 - ! [000] Unable to connect to server.
            '(local)';     SQLServerAgent cannot start
          2019-10-09 00:54:15 - ! [298] SQLServer Error: 27, Registry
           information is corrupt or missing. Make sure the provider is
           installed and  registered correctly. [SQLSTATE 08001]
           2019-10-09 00:54:15 - ! [298] SQLServer Error: 27, Client
           unable  to establish connection [SQLSTATE 08001]
           2019-10-09 00:54:15 - ! [165] ODBC Error: 0, A network-
            related or instance-specific error has occurred while.
            establishing a connection to SQL Server. Server is not found
             or not accessible.
             Check if instance name is correct and if SQL Server is.
            configured to allow remote connections.

           For more information see SQL Server Books Online.
           [SQLSTATE. 08001]
           2019-10-09 00:54:15 - ! [382] Logon to server '(local)' failed
           (DisableAgentXPs)
          2019-10-09 00:54:15 - ? [098] SQLServerAgent terminated
           (normally)

========================================================

      EVENT LOG snippet

    The application-specific permission settings do not grant Local.
     Activation permission for the COM Server application with CLSID
     {D63B10C5-BB46-4990-A94F-E40B9D520160}
      and APPID {9CA88EE3-ACB7-47C8-AFC4-AB702511C276}
     to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-
      344959196-2060754871-
       2302487193-2804545603-1466107430) from address.
      LocalHost (Using LRPC)
      running in the application container Unavailable SID
       (Unavailable). This security permission can be modified using
      the Component Services administrative tool.


texts:
I want to set up a trigger which will increment a value (based on previous record). But this will also reset the value if the year of date_creation is different of the current year.

ref        ref_handlerdate_creation
x_0001 0001          2016-06-16 00:00:00
y_0002 0002          2016-06-17 00:00:00
z_0001 0001          2017-01-01 00:00:00

PS : I also have an id field which is the primary key and increment itself

I already wrote that

BEGIN

    DECLARE next_handler integer;
    DECLARE current_id integer;

    -- select the current highest id
    SET @current_id := (select max(id) from billing_invoice );

    -- select the current highest ref_handler and increment it
    SET @next_handler := (select (ref_handler)+1 from billing_invoice WHERE id = @current_id);

    -- compare the current year ith the year of the creation_date of the previous record.
        -- true : do nothing
        -- false : reset the handler to 0001
     IF( (SELECT EXTRACT(YEAR FROM date_creation) FROM billing_invoice WHERE (id = @current_id)) = YEAR(CURDATE()) ,SET @next_id := @next_handler, SET @next_handler := 0001);

     -- if it is the first record
     IF ISNULL(@next_handler) THEN
         SET @next_handler := 0001;
     END IF;
 -- then set your new row accordingly
 SET NEW.ref_handler = @next_handler;
 SET NEW.ref= concat(NEW.case_id, '_', @next_handler, '_', DATE_FORMAT(NEW.date_creation, '%Y%m%d'));

END

But the statement which extract the the year is failing I do not know why.


texts: Logical reads time 8KB more than memory on instance

texts:
I am working on an instance of SQL Server and was using Redgate SQL Monitor. I saw a query in the top 10 list with 1 execution which had logical reads more than the memory for the instance by a factor of around 100 and the duration of the query was only a few minutes. How is that possible? I mean how can 8KB pages times the number of logical reads be more than the memory? What is it that I don't understand?
Thanks


texts:
I am trying to import data into a SQL Server. I can import through the Import and Export Data wizard. I cannot import from my machine using BULK IMPORT or OPENROWSET since the file is not on the server machine. How does the IED wizard insert data into its target db? Does it go row by row?

Are there any other possible solutions to importing data from my machine?


texts: List certain user ids and drop them in Oracle

texts:
How do I list certain user ids and drop them?

For instance, I want to list user ids directly connected to database, in Oracle, and not any functional id.

I need to write a script for the automation

I want to write a script to list such direct ids and drop them automaticaly.

How can I do it?


texts:
I have a mysql database (Version 5.6.23-log running on windows) that is set up for use with Teamcity. When running a command against it using HeidiSQL I am getting an error:

  SQL Error (2027): Malformed packet

This error only occurs if the result set is more than a certain number of rows (using LIMIT 14561 rows works, 14562 gives this error).

I believe this error is not due to Heidi since TeamCity is failing to start when running the same command.

I assume it is something to do with the data size becoming too large and something failing but I can't find any way to identify more specifically what the problem is and thus how to fix it.

If anybody can tell me what I can do to prevent this problem or at the very least how I might be able to better diagnose this problem I'd be most grateful.


texts: Problems with insert trigger and primary key

texts:
I'm dealing with a 3rd party app that I can't change or get support on. I've altered an underlying table in several ways to enhance it's functionality

1- I renamed the table PRE_CASE to PRE_TCASE
2- I created a view named PRE_CASE which is a select on to PRE_TCASE with a UNION ALL on to a linked server DB and table named OTHERSYSTEM_CASE

This works to the extent the app is now fooled into displaying data from PRE_TCASE and OTHERSYSTEM_CASE to the end user when it believes it's looking at the table PRE_CASE. It fails however on inserting data. To try and fix this I created a trigger (I only require to insert to PRE_TCASE, never OTHERSYSTEM_CASE)

create TRIGGER INSREPLACE
   ON  dbo.pre_case
   INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.PRE_TCASE
    (col1, col2, col3, etc.)
    SELECT col1, col2, col3, etc.
    FROM Inserted

END

I am getting failures on insert with the error:

Msg 233, Level 16, State 2, Line 1
The column 'ID' in table 'dbo.pre_case' cannot be null.

The insert statement is of the form

INSERT INTO dbo.pre_case (col1, col2, col3)
values (1,2,3)

No value/reference is made to the ID column.

On the underlying PRE_CASE the column ID is an integer primary key (PK, int, not null) with a valid identity seed. Direct inserts onto PRE_CASE work, but not ones onto the new view and trigger. What have I overlooked?

(My end solution will be deployed onto SQL2000, SQL2005 and SQL2008R2 boxes, but I'm testing/developing on SQL2000)

Edit: I created this model to test in a more isolated way. Turns out the error occurs on SQL2000 and not SQL2008R2, personally I'm giving up at this point.

create table t1(id int  IDENTITY(1,1) PRIMARY KEY ,ltext varchar(100));
create table t2(id int IDENTITY(1,1) PRIMARY KEY,ltext varchar(100));

CREATE VIEW tv AS
SELECT id, ltext from t1
union all
SELECT id, ltext from t2

select * from t2;
select * from tv;

INSERT  t1(ltext) values ('test1a');
INSERT  t1(ltext) values ('test1b');
INSERT  t1(ltext) values ('test1c');
INSERT  t2(ltext) values ('test2a');
INSERT  t2(ltext) values ('test2b');
INSERT  t2(ltext) values ('test2c');

CREATE TRIGGER TRG_TV ON tv
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT t2(ltext)
select ltext from inserted

END

-- this errors on SQL2000
INSERT  tv(ltext) values ('testv-a');


texts: DB2 Express-C 10.1 database move to DB2 Work group Server/Enterprise Edition

texts:
I have not done such migration before and I want to know if I am going to face such issues when moving my database (tables,views,table-spaces,functions,procedures,etc.) to other DB2 distribution.

If there will be some problems, will it be better to download the trial version of "Enterprise Edition" for example and to develop my project on it?


texts: How do I query based on percentages of a subset of values?

texts:
I have a table with a column named Type that can have some set of values.  For instance, A, B, C, D, etc.  Behind the scenes this is mapped to an Enum.

I need to query this table to get a percentage of some subset of the values.

For example, I may want to get 400 total rows where 25 percent are Type = 'A', 25 percent are Type = 'B', and 50 percent are 'Type = 'C'. If the total quantity of rows is not 400 it should return up to 400.  Ideally, this would return 100Arows, 100Brows and 200C` rows.

Or, perhaps, I want 100 rows where 50 percent are A and 50 percent are C. Etc.

If it makes the query easier to write, I can pass in a count of each value I want instead of percentage, e.g.: 200 of A, 200 of C.

What is the best way to perform this query?


texts: Copy complete structure of a table in Oracle without using Backup

texts:
When you create do a CTAS (create table as select) of a table you only get the structure, but lose the index, PK, FK, etc.

Example:

create table t1 select * from table2;

How can a copy of the table structure be made that includes these things without doing a backup?


texts:
I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not solved. Here is a sample session with a newly created empty database:

mydb=> CREATE TABLE distributors (
mydb(>     did     integer,
mydb(>     name    varchar(40) UNIQUE
mydb(> );
ERROR:  no schema has been selected to create in
mydb=> show search_path;
  search_path
----------------
 "$user",public
(1 row)

mydb=> create schema public;
ERROR:  schema "public" already exists

Any hint? What should I look for?

Solved.
Thanks to the answer of Daniel Vérité I've solved with the following:

grant usage on schema public to public;
grant create on schema public to public;

Are these the default permissions on the public schema?

I have a single user who can access the database, so I think this anyway cannot raise security risks...

I think I should make the same modification on template1. Is it correct? How can I check if the permissions in template1 are correct (say, default values)?


texts: Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

texts:
Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes?

If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate index exists).


texts: What would this non-'unique index with a unique search condition` query lock?

texts:
I am not sure about this part of the transaction manual

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

Maybe I'm just nitpicking, but I seem to read that there is a special condition in which "only the index record found" is locked (being: "unique index with unique search condition"). For all other we get some sort of range.
But what about 'other search conditions" that are nog a range? For istance, if I lock a query like SELECT * FROM table WHERE table.col = 2, there is no range or gaps in question, even if col is not unique, isn't there? Should we get a "only record found" lock here as well?


texts: SQL Server could not find a login matching the name provided

texts:
I have SQL Serer 2012 up and running. I created a new login (SQL Server authentication with password expiration, requires change, etc. turned off).

I can start SQL Server Management Studio as the user. But when I try to connect in code I get the error 'could not find a login matching the name provided'. I know the code works because it can connect to another instance on the same machine.

But the name is there, and I'm actually logged in as that user.

Note, I cannot use integrated security either, just the same error message.

What's wrong? Many thanks.


texts: MySQL Many to Many with additional data in intermediary table

texts:
I need to create a Many to Many relationship and understand the need for an intermediate table to act as the link between the two. The understanding I have is the Group of ID's in the intermediate table should be unique to not duplicate data; is this correct?
The DB that I am designing is for a deck of cards.
I have two tables: decks and cards. The intermediary table is deck_cards.
The issue I am facing is: a deck can contain many cards but can also contain more than one of the same card.
Below is a screen shot of my current Schema. I am currently storing an additional bit of data in the column amount in the deck_cards table for a given relationship. Is this the appropriate way to approach this situation? Or, is there another intermediary table that is needed to be created?


texts: MySQL Update: why would rows matched change based on the set clause?

texts: I ran an update query joining across a huge number of tables that updated one field, and it matched 29k some-odd rows. Running the same query with only an addition in the Set clause increased the matched rows to 118k some-odd rows. It seems this should only change if the WHERE clause were edited.


texts: Installing and enabling both master and slave semisynchronous plugins on same server

texts:
I want to run a semisynchronous replication setup, and for simplicity I'd like to not have to INSTALL and UNINSTALL plugins when a slave is promoted to a master. So I'm wondering if I'll experience any problems having both master and slave plugins installed and enabled (see here)

It looks like after installing the second plugin, both variables rpl_semi_sync_master_enabled and rpl_semi_sync_slave_enabled are reset to off (the non-default). When I include these lines in my.cnf:

loose-rpl_semi_sync_master_enabled = 1
loose-rpl_semi_sync_slave_enabled = 1

...and restart MySQL, both plugins are loaded and enabled.

Is this going to cause any problems for me?


texts:
My query executes very slowly despite all indexes in place:

SELECT * FROM "entry"
    INNER JOIN "entrytag" ON ("entry"."id" = "entrytag"."entry_id")
    WHERE "entrytag"."tag_id" = 323456
    ORDER BY "entry"."date"
    DESC LIMIT 10'

the explain shows too many loops, why? how to fix this?

Limit  (cost=1241.85..1241.87 rows=10 width=666) (actual time=23576.449..23576.454 rows=10 loops=1)
  ->  Sort  (cost=1241.85..1242.10 rows=99 width=666) (actual time=23576.446..23576.447 rows=10 loops=1)
        Sort Key: entry.date DESC
        Sort Method: top-N heapsort  Memory: 31kB
        ->  Nested Loop  (cost=0.87..1239.71 rows=99 width=666) (actual time=0.168..22494.187 rows=989105 loops=1)
              ->  Index Scan using entrytag_tag_id_row_idx on entrytag  (cost=0.44..402.17 rows=99 width=4)
                  (actual time=0.093..535.664 **rows=989105** loops=1)
                    Index Cond: (tag_id = 323456)
              ->  Index Scan using entry_pkey on entry  (cost=0.43..8.45 rows=1 width=666)
l time=0.020..0.021 rows=1 **loops=989105**)
                    Index Cond: (id = entrytag.entry_id)
Planning time: 0.829 ms
Execution time: 23576.504 ms

Indexes i have on table entry:

('id', 'date', ...other irrelevant cols)
('date', ...other irrelevant cols)

On association table entrytag:

(tag_id, entry_id)
(tag_id, row)  -- this index is used according to the explain

PostgreSQL v 9.5. There are many rows, the db is quite big. The same queries for other tags (with the same number of entries) take fractions of seconds and no such huge row and loop counts.


texts: Does a unique clustered index give array-like lookup performance?

texts:
Suppose I have a MySQL table with a unique integer index id that is auto-incremented for each row.

id | PlanetName
---+-----------
 0 | Mercury
 1 | Venus
 2 | Neptune

Suppose that I am primarily interested in optimizing queries that look up rows by the unique index. For example:

SELECT PlanetName FROM Planets WHERE id = 2

If I were programming in another language and I had the data loaded up into an array, then it would be very quick to perform these lookups by pointer arithmetic. I would like to be able to get the same sort of performance when I carry out the queries in MySQL.

My thinking is:

a clustered index means the data is stored sequentially (i.e., very similarly to how a primitive array is stored in memory)
a unique integer index ensures that SQL does not have to worry about duplicate rows

So if I create a unique clustered index for id, does that mean that MySQL will carry out a pointer arithmetic-type lookup?


texts: SQL Server 2000 - Stripping time from datetime column results using DATEADD and DATEDIFF is returning result with time still?

texts:
I know there are several posts about stripping the time portion from a datetime column. And everyone suggests that the DATEADD and DATEDIFF method is the best for performance and individuals should not adopt the process of cast/converting date values to varchar. So I was wanting to utilize the functions below but I don't understand why the result I receive still has the time value associated to it though the time is all zeroes?

To provide more information as to why I want to understand this result. I have a stored procedure that I wish to specify the following:

DECLARE @DateShippedBegin DATETIME
DECLARE @DateShippedEnd DATETIME
SET @DateShippedBegin = dateadd(dd, datediff(dd, 0, @DateShipped), 0)
SET @DateShippedEnd = dateadd(dd, datediff(dd, 0, @DateShipped) + 1, 0)

WHERE O.date_shipped >= @DateShippedBegin and O.date_shipped < @DateShippedEnd

But when I execute the procedure with a basic date format I receive no results so trying to understand the best concept to adopt for my procedure.

Select DATEADD(dd, DATEDIFF(dd, 0, Getdate()), 0) from orders where order_no =
'1247401'

Example of begginning date value:

2017-05-010 08:40:18.287

Example of query result:

2017-05-10 00:00:00.000


texts: Why does this stored proc cause a clustered index scan, but seek when using OPTION RECOMPILE?

texts:
I think I may know the answer based on my research, but am looking for confirmation on how/why the engine compiles the plan the way it does with

Parameters being passed in:
@ID int
,@OtherID INT

SELECT  b.Column1
        ,b.Column2
        ,b.Column3
        ,b.Column4
        ,b.Column5
        ,c.Column1
        ,b.Column1
        ,e.Column1
FROM    Table1 AS b
        inner join Table2 AS t
        on b.ID = t.ID
        left join [LINKED SERVER].[DB].dbo.Table3 as c
        on b.ID = c.ID
        left join Table4 AS e
        on b.ID= e.ID
where   (b.ID = @ID or @ID= 0)
        And b.ID = @OtherID
        And b.ID IS NOT NULL
        and e.ID = 1

Now I have determined that the cause of the index scan is because of this line: where  (b.ID = @ID or @ID= 0). More specifically, @ID = 0.  To clarify even further, 0 for that ID field does not exist as a value in the underlying table, it was simply something a developer did to allow a user to pull back all of the results by passing in 0 to the parameter and then checking to see if that parameter is 0 so as a result more rows are pulled back (typically, you would just return 1-3 results).

Now, what is extremely odd, is that if I add OPTION RECOMPILE, the engine is able to create a much better plan at the cost of overhead (compilation time) of course:

What I would like to know is how is this possible.  From what I have read online, by using OPTION RECOMPILE, the engine will literally replace the value with the actual value passed into the parameter and it can very easily see that @ID 1234 does not equal 0.  However, if you don't use OPTION RECOMPILE the engine will take the total # of records, which is 120,000, and divide it by the total number of distinct possibilities, 107,000.  This comes out to about 1.1 estimated rows being returned and I confirmed this by looking at the estimated properties of the plan that has the index scan, but why would the engine continue to index scan if the estimation is correct?  I even updated stats just to be sure.


texts:
I've got a problem with my MySQL slave which receives updates from two masters.

Steps to reproduce:

1) I start the slave

2) The slave receives updates for 1-2 hours, after which fatal error crashes the server

  [ERROR] [FATAL] InnoDB: Aborting because of a corrupt database page in the system tablespace. Or, there was a failure in tagging the tablespace as corrupt.
  2020-06-11 16:17:22 0x7fd250386700 InnoDB: Assertion failure in thread 140541265733376 in file ut0ut.cc line 910`

3) I add innodb_force_recovery=4 to my config file and restart mysqld service to make it work again, set innodb_force_recoveryto 0 in order to run mysql_upgrade --upgrade-system-tables - these actions allow me to restart mysql service and restore replication. However, the server fails again in a couple of hours.

Does anybody know how to fix this?


texts: Transactional replication pre and post scripts

texts:
Just a preface that I'm working at the moment as an accidental DBA so sorry for any ignorance.

I need to completely rebuild one of our transactional replications.  The indexing strategy is different on the subscriber database than it is on the publisher, the primary key is the same but the clustered index on NC indexes are different. I am aware that the tables by default are completely dropped on the subscriber.  I believe I can use scripts pre and post snapshot application to drop and create indexes.

Both the clustered indexes on the subscriber and publisher are UniqueIdentifier but different columns.  The table has nearly 1 billion rows and is sadly pretty wide.  Just thought that I'd provide that information in case it makes a difference.

My questions are:

Is using the post execution scripts the best way to go about this?
If the clustered index is different on the subscriber than the publisher am I best to allow the bulk insert to happen with the publisher clustered index or would it be better to set the alternate clustered index before the bulk insert?

Thanks in advance for any help.


texts: Order of logical decoding output on Postgres 11

texts:
Given the following table in PostgreSQL 11.1, what is the order of logical decoding output rows?

CREATE TABLE t1 (
  t1_id INTEGER PRIMARY KEY
  ,name TEXT
)
;
CREATE TABLE t2 (
  t2_id INTEGER PRIMARY KEY
  ,t1_id INTEGER NOT NULL
  ,name TEXT
  ,CONSTRAINT fkt2_t1id FOREIGN KEY (t1_id)
    REFERENCES t1 (t1_id) ON DELETE NO ACTION ON UPDATE CASCADE
    DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE t3 (
  t3_id INTEGER PRIMARY KEY
  ,t2_id INTEGER NOT NULL
  ,name TEXT
  ,CONSTRAINT fkt3_t2id FOREIGN KEY (t2_id)
    REFERENCES t2 (t2_id) ON DELETE NO ACTION ON UPDATE CASCADE
    DEFERRABLE INITIALLY DEFERRED
)
;

Because of DEFERRABLE foreign key, in one transaction, a referencing row (t3, t2) can be inserted before the referenced rows (t2, t1).

BEGIN
;
INSERT INTO t3 VALUES
(1, 1, '3')
;
INSERT INTO t2 VALUES
(1, 1, '2')
;
INSERT INTO t1 VALUES
(1, '1')
;
COMMIT;

Question

What is the order of logical decoding output ?

Referenced rows first, then referencing rows.
t1 → t2 → t3
Same order as statements.
t3 → t2 → t1
Non-deterministic order

I guess No.1 is the answer. But I want to confirm whether postgresql guarantees such order or not, with/without DEFERRABLE constraint condition.


texts: Where to put ReportServer and ReportServerTempDB databases?

texts:
I have a SQL Server 2008 R2 database server that hosts a data warehouse database.  On this server is also installed SQL Server Reporting Services, and its two databases ReportServer and ReportServerTempDB.

For performance reasons I want to separate the data warehouse database and the Reporting Services.  I have another server that is available to install SSRS onto.  Both servers are virtual and so their specifications are easily changed, or other servers could be 'created' if necessary.

What I can't get my head around is where the best place would be to host the ReportServer and ReportServerTempDB.  I can see three options:

leave them with the data warehouse database
put them on the server that will have Reporting Services installed on it
create another server specifically to host those two databases.

With option 1 I worry that separating the Reporting Services from its databases will either impact the data warehouse server, or will impact the reports.  With option 2 I worry that installing the database engine on the server will impact the Reporting Services server performance, and with option 3 I worry that I'm wasting resources creating a server that will hardly do anything, and also impacting the reports by separating the Reporting Services from its database.

I think the key piece of information I need is to know which part of Reporting Services is the most resource intensive.  If it's the Reporting Services application then I can leave the databases with the data warehouse.  If it's the databases that are resource intensive then I need to either put them with the Reporting Services application or create a new server.

Can anybody shed some light please?

Thanks!


texts: View the role assigned to each user (Report Server)

texts:
Is there anyway I could list down the roles assigned to each user on the different folders on ReportServer Database and in that SQL statement I should be able to declare something like
    @User_Name of the reporting user


texts: Using @@ROWCOUNT in multi-operation batch to check either a failure or a success of the whole transaction

texts:
I have the following script defined with hopefully explanations in comments to explain rationale in the right context. The questions are basically:

Is the following usage of @@ROWCOUNT in conjunction with XACT_ABORT OK to handle such a multi-operation transaction where if either one (in this case) fails, all should be rolled back. It looks OK, but I wonder if there are edge cases that should be accounted for.
Is there a handier way of checking existence than that construct? I suppose if there is a unique constraint in Table1Value, there shouldn't be a problem.

The query:

SET XACT_ABORT, NOCOUNT ON;
DECLARE @ROWCOUNT AS INT;
BEGIN TRANSACTION;

-- Insert this if it doesn't exist already.
INSERT INTO Table1
(
   Table1Value
)
SELECT
   @table1Value
WHERE NOT EXISTS
(
    SELECT 1
FROM
    Table1
WHERE
        Table1Value = @table1Value AND @table1Value IS NOT NULL
);

-- Update this table if insertion to the previous one succeeded. That is,
-- the @@ROWCOUNT in the WHERE conditional is more than zero. It's
-- OK if the the value changed was the same as before.
UPDATE Table2
SET
    Timestamp = GETUTCDATE(),
    VersionCounter = VersionCounter + 1
WHERE
    Table1Value = @table1Value AND @table1Value IS NOT NULL
    AND VersionCounter = @versionCounter AND @versionCounter IS NOT NULL
    AND @@ROWCOUNT > 0;

-- The idea here is that the transaction will be committed
-- if both of the previous operations succeeded. Otherwise it
-- will be rolled back. Either way, an explicit return value is
-- used to indicate either a success or a failure.
SET @ROWCOUNT = @@ROWCOUNT;
IF @ROWCOUNT = 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION

-- Interpreted as TRUE if this is more than zero. FALSE otherwise.
SELECT @ROWCOUNT;


texts: Stuck at optimizing "not in clause" for a medium-sized query across two tables

texts:
I have a system to teach English. For each word someone learns, I record:

LearnedWords
   -- Id
   -- UserId
   -- WordId

And for each new text that I give to a learner, I want to indicate what words are new for him.

So, imagine a learner has learned 15000 words. So there are 15000 records for him only in the LearnedWords. Now he chooses to read a new text that has over 2000 words. All I have is textual data here, that is, words.

From these 2000 words, let's say only 300 words are new. How can I find them? The only way that I can think of is to do set operations based on Venn Diagram. That is, using not in clause. So, I create a procedure that takes the 2000 words as input, creates a temporary table and populates it with the given text words, selects learned words from database, and then executes the not in query:

declare @textWords table
(
    Word nvarchar(100)
)

-- populate @textWords with 2000 given words here

declare @learnedWords  table
(
    Word nvarchar(100)
)

declare @newWords table
(
    Word nvarchar(100)
)

insert into @learnedWords (Word)
select Words.[Text]
from LearnedWords
inner join Words
on LearnedWords.WordId = Words.Id
where LearnedWords.UserId = 1 --> user with 15000 learned words

insert into @newWords (Word) -- filters 300 new words for the given user
select Word
from @textWords -- has 2000 records
where Word not in
(
    select Word
    from @learnedWords -- has 15000 records for the given user
)

However, this procedure takes 14 seconds to complete. It's a nightmare. I ran Tuning Advisor and applied recommendations. But it only improved to 13 seconds. I'm stuck at how to meet this business requirement in data layer with a good performance. Any help?


texts: Obfuscate data during migration with MySQL Workbench

texts:
Is it possible to obfuscate sensible data during migration using MySQL Workbench?

We are going to work on site so we need to make a copy for develpment but the (MySQL) database contains sensible user data that need to be obfuscated for obvoius privacy reasons.

I already used MySQL WB migration wizard so it's not new to me, but i can't figure it out if it can obfuscate/alter data during migration apart from columns/tables definition.

Maybe it just can't, maybe there are better tools/ways to do what i need, but using a tool i already know and that can automagically create sql/ssh scripts would be better of course.

Precisation

with 'obfuscation' i mean any avalable way to alter data.
E.g.:

transform user first_name property to something like <user_id>_first_name
change any login password to a standard one
replace fiscal code with a random string
etc


texts:
I am connecting to an Oracle database in ASP.Net using SQLDataSource. My connection string is:

<add name="ConnectionString3"
 connectionString="Data Source=sml; User ID=sml; Password=sml; Unicode=True; Pooling=False;"
 providerName="System.Data.OracleClient"/>

Problem is that when I look at v$session on the database, I can see two locked sessions. They don't clear till I shutdown the ASP.Net server.

i.e. the query

select *
from v$session b
where status='INACTIVE' and schemaname='SML' AND program='w3wp.exe'

always shows two sessions which don't clear till I shutdown the server.

What is the proper way in ASP.Net to connect with an Oracle database and then manage the connections to clear after connecting with database? I meant how can I logout from the database after querying it?

Are there any session settings to do in the Oracle database?


texts: Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

texts:
I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 gb, and the frequently queried data is ~1.25 gb.

What should I set the innodb_buffer_pool_instances to?

innodb_buffer_pool_size / total queried data = 6 pool instances
innodb_buffer_pool_size / frequently queried data = 16 pool instances

Normally I'd pick option #2, but logically it seems number of buffer pools that can be used at any one time is no more than the total number of CPU cores.

Is it a waste to set innodb_buffer_pool_instances to more than the # of CPU cores?


texts: What is the cardinality in this situation?

texts:
I need to find the cardinality for a few tables in an Oracle database. I have been given a word document that has a list of tables described with details as shown in the image:

I have been reading about cardinality.  As far as I understand, cardinality can be used to describe the relationship between two tables.
I have gone over this question and its answers:
https://stackoverflow.com/questions/10621077/what-is-cardinality-in-databases

but I still lack the understanding on how to move on in solving my problem.

I need help in determining what type of cardinality I need to find, and how to find it.

I have also gone over this content, but I am not sure if this is what I need to do.
https://www.mysqldba.co.uk/articles/2008/02/26/how-to-calculate-cardinality

I need to find the cardinality for columns col5, col6, and col7.


texts:
I need you wisdom, I am debating with another person when is it recommended to put the code of a business rule into a database constraints or inside of the application business layer?

There is a particuliar field that is used in several calculations for the machinerie settings in the plant. Today, we faced an issue because the file maintenance people did not specified a required field that somehow was not validated in the UI of the ERP. That field cause a division by zero error in a calculation that crash the PLC machine and halt the production the time to reboot the PLC machine.

So I filled a request to file maintenance to populate all field that were zero value and a second request to the database administrator to create a constraint in the database for the field so the record get rejected if the required field is 0.

The DBA denied my request and told me to validate the particuliar field in the UI or in the Business logic layer. I told him that even if I do that, someone in Microsoft Access or any other tools with the right permissions could assign zero to the particuliar field and it will again halt the production. He answered me: "so what? do you expect me to reprogramming all the business rules from the Business logic layer into database constraints?"

After that, I was wondering the question "when to put a business rule in the business application layer or into a database constraint"?

Thank you!
Sebastien


texts: drop table causes ORA-08103 with another one

texts:
In my process I have 2 tables.

  target table, load table.

The target table holds the current data, and a lot of queries are fired against it.

When new data coming we use a FULL LOAD and the goal is to switch the current data that in the target table with the new data.

The new data exists in the load table.

In goal to do that we are using

  Exchange partition

Those are the steps:

create new load table with only 1 partition -all_rows (values less than maxvalue).
insert all the data from source to the load table by insert statement.
exchange partition of the all_rows (=all the new data) with the target table.

That way, we have the most new data, and there is no space that data is not available.
The problem start when I want to drop my load table after the exchange.
If someone send a query to the target table he can get exception:

ORA-08103: Object no longer exists.

Even the drop was only on the load_table. Same when load table truncate.

What does that mean?
Those two tables will be together forever?
Every truncate or drop I need to be afraid on the second one?
Why they are connected at all?

I'm using oracle 11g.

My actions:

CREATE TABLE tbl_ld
partition by range (col1) (partition all_rows values less than (maxvalue)
AS  SELECT * FROM tbl_tgt WHERE 1=2 ;

insert into tbl_ld
select * from tbl_temp;

alter table tbl_ld exchange partition all_rows with table tbl_tgt;

-- drop or truncate
drop table tbl_ld;
truncate table tbl_ld;


texts: How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?

texts:
I am not a professional, sorry. But perhaps you can help me anyway ...

I have a table with 27 columns and within the rows data with linebreaks. When I try to export selected rows with sql-plus spool, I have several problems. I tried the advices given here: How to make sqlplus output appear in one line?, but they don't help me. I'll explain below.

First the configuration of the SQL plus query:

set term off
set echo off
set underline off
set colsep ';'
set linesize 32767
set pages 10000
set trimspool on
set trimout on
set feedback off
set heading on
set newpage 0
set headsep off
;set wrap off
set termout off
set long 20000

spool D:\tmp_Datenaustausch\Export-141204.csv

SELECT MEDIENNR, ISBN, ISBN2, ISBNSUCH, KATKARTE1, BUCHKARTE1, SIGNATUR1, KATKARTE2,
   BUCHKARTE2, SIGNATUR2, KATKARTE3, BUCHKARTE3, SIGNATUR3, MAB2, FEHLERCODE, FARBE,
   ANZSYKKARTEN, KKSTATUS, BKSTATUS, MABSTATUS, SESTATUS, FARBSTATUS, ASSTATUS,
   REZENSION, REZENSENT, STICHWORTE, UPDDATE FROM Bekartungsdaten WHERE upddate like
   TO_DATE('2014-12-01', 'yyyy-mm-dd');

spool off;
exit;
/

My problems:

Headings are not in one line, "set wrap off" does not help, because it truncates data.
Some fields contain text with linebreaks, sql+ puts the lines of these fields in seperate fields in the csv. I need them in one field with linebreaks like stored in the database.
How do I get quotationmarks at the beginning and end of each field/cell in the csv?

I would be very glad, if some could help me!


texts: Before deleteion trigger for self joined table sub rows

texts:
I want to write trigger before deletion to delete sub-children from same mysql table.
My table is samp_c:
**ID** **Name** **key** **Sub-key**
  1      sample1   ab      0
  2      Sample2   bc      ab
  3      sample3   cd      NULL

So  if i delete ID -1 row ,then it should be deleted ID -2 row also(self joined JOIN samp_c table samp_c.key = samp_c_child.sub-key).
Please help me write trigger


texts: Estimate average and median efficiently in Postgres?

texts: I have a Postgres database with tables in the billion scale. So any aggregate functions such as count() and avg(), as well as "order by random()" are very time consuming. Postgres has pg_catalog which contains lots of useful statistics (such as the histogram bins in view pg_stats) that describe a database. Is there a way to take advantage of the statistics in pg_catalog to estimate the average and median numbers over a numeric column in a Postgres table?


texts: Replicate multiple on-site SQL Server databases to Azure

texts:
I have a SQL Server Express 2014 named instance containing 3 databases
(One for each office). connected to a WinForms application.

Each database is uniquely named after the respective office although containing the exact same tables & table names with different data.

I want to set up transactional replication from the local databases to Azure.
Will having the same table names in different databases create a problem when replicating to Azure?

What would be the best workaround for this? The 3 databases cannot be merged into one and renaming the table names create problems when using the Winforms application.


texts: Are lots of columns or joins better in Django

texts:
I am writing a simple Django app that stores and displays user objects with lots of attributes.

The user may have up to three hundred attributes.

These attributes however, can be grouped off.

For example, we can have one group for "Favourite Dish" and the attributes under that are cost, flavour, cuisine etc. and another group for "Favourite Dog" and under that are cost, flavour, breed etc.

I know that in normal circumstances, you would compartmentalise the data and store each group of attributes in its own table, however this app will display and query all of the data, most of the time.

For example: the main page of this app will have a search box and users should be able to search any of the attributes in the entire model. This would involve a lot of joins if done the "usual" way, but would involve less joins if I just had a table of three hundred columns.

I have considered using NoSQL but there is other data that fits a relational schema much better.

What is the best practice for this?

Thanks :)


texts:
I've got a problem with creating a SQL query in order to get only the most recent record from multiple records. I've already checked different solutions like a subselect; unfortunately that does not work for me.
The query I've created so far puts out about 1300 records. What I need is only the newest one (by wfi.CREATEDDATETIME) of every InvoiceNumber. Here's the example data for ONE InvoiceNumber:

Company | UID  | Approver   | Due | InvoiceDate | InvoiceNumber | Vendor         | Created
S0009   | USR1 | Random Guy | ... | 20.10.2016  | 123456        | Random Vendor1 | 2016-10-30 10:00:22.000
S0009   | USR1 | Random Guy | ... | 20.10.2016  | 123456        | Random Vendor1 | 2016-10-30 10:00:22.000
S0009   | USR2 | Some Guy   | ... | 01.01.2000  | 123456        | Random Vendor1 | 2016-11-01 12:30:16.000
S0009   | USR2 | Some Guy   | ... | 01.01.2000  | 123456        | Random Vendor1 | 2016-11-01 12:30:16.000

The result I need is:

Company | UID  | Approver | Due | InvoiceDate | InvoiceNumber | Vendor         | Created
S0009   | USR2 | Some Guy | ... | 01.01.2000  | 123456        | Random Vendor1 | 2016-11-01 12:30:16.000

Query
SELECT
Upper(WFI.COMPANYID) as Company,
WFI.USERID as UID,
INF.NAME as Approver,
CONVERT(varchar(10),duedatetime,4) as Due,
right(left(Document,20),10) as InvoiceDate,
SUBSTRING(DOCUMENT,22,charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21))) as     InvoiceNumber,
Right(document,len(Document)-(20+charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21)))) as Vendor,
wfi.CREATEDDATETIME as Created

FROM
[TEST].[tst].[WORKFLOWWORKITEMTABLE] WFI
INNER JOIN [TEST].[tst].[Workflowtrackingstatustable] WFS ON WFI.CORRELATIONID=WFS.CORRELATIONID
INNER JOIN [TEST].[tst].[HCMWORKER] HCM on WFI.USERID=HCM.PERSONNELNUMBER
INNER JOIN [TEST].[tst].[DIRPERSONNAME] DPN ON DPN.PERSON=HCM.PERSON
INNER JOIN [TEST].[tst].[LEDGERJOURNALTABLE] LJT ON WFI.REFRECID = LJT.RECID
INNER JOIN [TEST].[tst].[USERINFO] INF ON WFI.USERID = INF.ID

WHERE
DATASOURCENAME Like 'Ledgerjourna%'
AND Datediff(day,Duedatetime,getdate())>3
AND WFS.DOCUMENTTYPE='Special'

ORDER BY
WFI.COMPANYID asc

These tables are standard workflow and employee tables from Dynamics AX 2012 on SQL Server Version 12.0.4100.
The createddatetime could be the same for multiple records. In this case I would like to select just one of them, it doesn't matter which one.


texts: How can I get all rows of data not included in a TVP

texts:
I am trying to do the following:

declare @domains DomainNames;
declare @CompanyId int
declare @CompanyName varchar(255);
declare @deletedList DomainNames;

set @CompanyId = 239;
set @CompanyName = 'dave';
insert into @domains (Domain) values ('ev2.com'), ('ev3.com');

select * from CompanyDomains where CompanyId = @CompanyId and EmailDomain != @domains

where I get back all rows in the table that do not have an EmailDomain value matching any of the values in @domains (and the CompanyId has a set value). Is there a way to do this?

Sql Server 2008 & Sql Azure.

thanks - dave


texts: Is DB Normalization done purely based on the primary key or is it done based on all the candidate keys?

texts:
I have come across two flavours of the normalization procedure while referring the internet and textbooks. viz.

Type 1. Normal forms based only on the primary key.

In this type,

-> 2NF disallows Partial dependencies on the primary key.

-> 3NF disallows transitive dependencies on the primary key.

Type 2. A much more general Normal form based on ALL of the candidate keys

In this type,

Partial dependencies and transitive dependencies on ALL candidate keys are taken into account.

In most of the sites I referred, I have found tutorials and notes based only on the 1st type.
But the textbook 'Fundamentals of Database Systems' by Navathe and Elmasri describes both the types. Even wikipedia's page on 2NF mentions seperately about '2NF and candidate keys' which is based on the 2nd type.

See 2NF and Candidate Keys - Wiki

But neither the textbook nor wiki mentions anything regarding which type is better or which one is practically preferred.

Which type should be followed when nothing is mentioned about the type?
Hope U have understood my question.

Please help me out on this guys.


texts: I am trying to install log4db2 utility in db2 for procedure logging but I am getting error mentioned below

texts:
DB21034E The command was processed as an SQL statement because it was
  not a valid Command Line Processor command. During SQL processing it
  returned: SQL0601N The name of the object to be created is identical
  to the existing name "LOG_CONF_BP" of type "BUFFERPOOL".
  SQLSTATE=42710

How to remove pre-existed "LOG_CONF_BP" of type "BUFFERPOOL" in db2 database? Is there any other way to define logging for my stored procedures?


texts: How to run SQL services on NT SERVICE\MSSQLSERVER account if it is running earlier on LocalSystem

texts:
Recently just for experimental puprose, I have relocated the default (C:...) data\log file paths in server settings to new location (G:\DBA).

Then I've moved the previously existing db's data\log files to the new location (G:\DBA) and restarted the SQL services. From then, whenever I'm creating DB, it is working as expected i.e., data\log files are getting created in new location(cool...).

But the problem is, I see all my previously existing DBs are in RecoveryPending state also got an error like Access denied.

Thinking that service account on which SQL is running (NT SERVICE\MSSQLSERVER) is not having access to new location(G:\DBA), I changed the logon account as Local System for SQL services in Computer Management console. Now I see all db's are good.

But, I want to run my sql services on NT SERVICE\MSSQLSERVER (default instance) service account. I tried by giving full access to G:\DBA\ for NT SERVICE\MSSQLSERVER, but no use. Please help me what all I have to do if I want to run my SQL services back on NT SERVICE\MSSQLSERVER service account. I'm beginner in this field. Please help me out.

Thanks in advance. . .


texts: Building an execution plan takes too long on SQL Server

texts:
I have an SQL Server 2014 instance (12.0.2000.8) and a quite complex SELECT statement with about 20 joins. This query works fine with the same data set on PostgreSQL, Oracle and other databases, and entire execution takes about 1 minute.
But on SQL Server it takes about 40 minutes. I tried to look at the execution plan and I started to wait... I tried to get the execution plan by executing a query from an application session, but there were no execution plan.
Then I got the query and asked in SQL Server Management Studio for "Display estimated execution plan", and I also started to wait. So, it looks like it takes too much time just to build the execution plan. All statistics is gathered with "exec sp_updatestats", and I checked it in sys.stats - everything looks fine. All indexes are in place.
I commented all joins and started to uncomment them one by one, and
  SET STATISTICS TIME ON

shows that with every uncommented join it takes a longer time to parse, e.g. timing for 13 joins:
SQL Server parse and compile time:
   CPU time = 32250 ms, elapsed time = 32729 ms.

so, it's definitely a parsing issue.
select count(*) from sys.index_columns
where object_id in (OBJECT_ID('tables_names'),...')

says there are 128 columns, when
select * from sys.indexes
where object_id in (OBJECT_ID('tables_names'),...')

returns 43 rows with HEAP, CLUSTERED, NONCLUSTERED indexes.
Could you recommend what to look at? Why does it parse so much?
Update: Thank you for "Break the query up" and "Use a FORCE ORDER hint", but this SQL is generated by our application, so it could be a lot of effort to make it possible with the application logic, but in general cases they should be a great solution.
Second update: Applying SP3 did the whole thing - the whole execution query takes less than one second. The performance became better by two thousand times :)


texts: Is there a kind of deadlock that SQL Server won't kill automatically?

texts:
We've been having some blocking problems on SQL Server 2014.

First, let me test my knowledge:

A deadlock, as I understand it, is when process A blocks process B which then also blocks process A, creating a cycle in the graph of blocked processes. It was my understanding that, when this occurs, SQL Server can recognize it and kill one of the processes, the deadlock victim.  I assume SQL Server can also recognize a cycle of three or more processes, too, but maybe not?

What's not a deadlock is when a process locks a resource, then some other processes need it and waits for it to become unlocked. If the first process never completes, then the other processes will just wait (until they timeout). SQL Server will not kill any processes in this case because blocking is normal.

Now, are there any kind of deadlocks that SQL Server either can't detect or will choose not to kill?

We have some users running heavy queries, a job that runs every 5 minutes to update the tables, and a second job that runs a small query to check the health of the first job. (It checks the most recent date and sends an email if the data is stale.)

My DBA tells me that some combination of the above three things is creating a deadlock, but that SQL Server won't kill it. Not a normal block, but an actual deadlock. Is that possible?


texts: Copying a MySQL 5.6 server settings for a new environment?

texts: I have a MySQL 5.6 Server running on a Windows 8 VM on a steel case server.  It's been having issues (The Windows 8, which is then causing the MySQL to have issues).  Therefore I was tasked with putting the MySQL 5.6 Server on a Windows 10 VM for stability reasons.  The amount of ram the server is allowed and other settings have been tweaked over time.  Is there an way I can export these settings, so when I install MySQL on my Windows 10 VM, I can just tell it to use the same?


texts:
I have two tables which have roughly the same data but we could not mix them together due to the nature of the data input and presentation and to keep the table size small.

We run the followign script

SELECT * FROM (
SELECT dataString,dataInsertDateTime FROM tblMainData
WHERE deviceSerial='A111' AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07'
union
SELECT dataString,dataInsertDateTime FROM tblSecondData
WHERE deviceSerial='A111'  AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07')
as table1

The script runs quite slow
I want to mix the data from both tables
I want it ordered by mainDateTime

Thank you


texts: Single listener for multiple Oracle homes?

texts:
I'm in the process of re-hosting two Oracle 12c databases on Unix. In the source environment, the databases were stacked -- they shared the same Oracle home and used the same listener. The listener looked roughly like this:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = foo)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DATABASE1)
      (ORACLE_HOME = /oracle)
    )
    (SID_DESC =
      (SID_NAME = DATABASE2)
      (ORACLE_HOME = /oracle)
    )

In the new environment, each of the databases has its own Oracle home. I have a listener running out of one of the oracle homes. It looks roughly like this (note the ORACLE_HOMEs):

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = newfoo)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DATABASE1)
      (ORACLE_HOME = /oracle/DATABASE1)
    )
    (SID_DESC =
      (SID_NAME = DATABASE2)
      (ORACLE_HOME = /oracle/DATABASE2)
    )

I would like for each environment to have its own listener running, but that would mean one of the databases would need to change its port (we're not using virtual IPs so I can't have two databases on the same host using the same port).

(For the sake of argument, imagine that the client is running tons of legacy software and they can't easily change which ports they're pointed at. I realize asking them to simply modify their tnsnames is the easiest option, but let's set that aside for the moment.)

Question: Is it problematic to use a single listener for multiple Oracle homes? I get that it's a single point of failure, but is there also a performance hit? Something else I'm not thinking of?

Question: Is there some other way I can run a listener out of each of the new Oracle homes using the same port? Again, we're not using virtual IPs, so I'm stuck with new host "foonew" + port 1521.

Thank for any answers or guidance you can provide.


texts: start mongod by service mongod start, attempt to create a lock file on a read-only directory /mgodb

texts:
I changed /etc/mongod.conf's dbpath to /mgodb and then I execute the follow command:

mkdir /mgodb
chown mongod:mongod /mgodb
chmod 0755 /mgodb

Then I run service mongod start, and get errors like:

  initAndListen: 20 Attempted to create a lock file on a read-only directory: /mgodb

I tried to change /usr/lib/systemd/system/mongod.service's User and Group to root, it also get the same error.

But I cat run mongod --fork --config /etc/mongod.conf through command line.


texts:
I've got Snapshot replication set up on my SQL Server 2008 production server, and I just noticed that there are snapshots ranging back to a year ago in the snapshot folder.  How can I change the retention for these snapshots?  Specifically I'd like it to retain snapshots for 5 days.

Here's a screenshot of the folder I'm looking at:


texts:
I am trying to insert rows when target table doesn't match with stage.
I used the following code which seems correct but I am getting error as:

  missing FROM-clause entry for table "S"

INSERT INTO app.CUSTOMERS(NAME, ADDRESS)
SELECT NAME, ADDRESS
FROM STAGE.STG_CUSTOMERS S
ON CONFLICT (NAME) do update
SET NAME = S.NAME
AND ADDRESS = S.ADDRESS;


texts: Is there Windows DLL for PDO_IBM driver available for PHP 5.6?

texts: On PECL, there is the latest Windows DLL in version 1.3.3. Unfortunately, they didn't provide one for version 1.3.4. I'd like to get the DLL from some reputable source. Otherwise I'll have to compile it from sources myself in which case some hint as to what should be taken care of would be much appreciated.


texts:
We have daily backups of a postgres db and it is in the form of a tarred (gz) base backup.
I need to restore this into another db of the same version. However, I am confused with the article that I am reading about doing this.
The version of the postgres DB is 8.2.4.
These are the main steps given :
rm -rf /var/lib/postgresql/10/main/*
cp -r /basebackup/* /var/lib/postgresql/10/main/
rm -rf /var/lib/postgresql/10/main/pg_wal/*

So in my case, the postgresql data directory is /usr/local/pgsql824/data. I believe this must be /var/lib/postgresql/main in the command above. I have untarred the tar.gz base backup into /usr/local/temp. So I suppose that this path is /basebackup in the command above. Please correct me if I am wrong.
Now, there comes the part of the "recovery.conf" file. These are the contents of the file as given by the article :
vim /var/lib/postgresql/10/main/recovery.conf
restore_command = 'cp /wal_archive/%f "%p"'
recovery_target_lsn = '3/72658818'

What is the purpose of the "/wal_archive" if I have already untarred contents of the base backup into /usr/local/temp ? This is what I am not sure about.
This is the link to the full article :
https://severalnines.com/database-blog/point-time-postgresql-database-restoration
I just need a clear explanation on how to restore the base backup to the new database data directory.


texts: General ETL principle - ETL from unknown number of tables?

texts:
AFAIK, ETL integrates data from a fixed number of sources/tables. Is it possible that ETL can integrate data from an unknown number of tables which might be added in the future, given that these tables might be of different types (customer, shipping, employee, accounting etc.) ?

EDIT -

Let A,B,C be tables. We can have the following ETL scenarios - A + B + C = 1 row or A + B + C = 3 rows

I have the first one. Later, we can add tables D,E,F,G...etc all of which might be different from each other in terms of the data they contain.


texts: Designing a join table for max n records

texts:
I know that a basic rule for designing a relational dataset is to not do things like this:

create table Order (
    OrderID int indentity(1, 1) not null, -- PK
    CustomerID int identity(1, 1) not null, -- FK to Customer table
    ProductID1 int not null, -- FKs to Product table
    ProductID2 int not null,
    ProductID3 int not null,
    ...
    ProductID10 int not null,
    primary key (OrderID)
)

Because it results in problems like:

What if the customer orders more than 10 products in a single order? This can't support that.
What if the vast majority of customers only ever order 1 or 2 products? That's a lot of surplus fields.

And the correct answer of course is to normalize:

create table Order (
    OrderID int identity(1, 1) not null, -- PK
    CustomerID int not null -- FK to Customer,
    primary key (OrderID)
)

create table OrderProduct (
    OrderID int not null, -- FK to Order table
    ProductID int not null, -- FK to Product table
    Quantity int not null,
    primary key (OrderID, ProductID)
)

However, what about in cases where there is a known maximum or range of the number of Products that can be associated with a single Order?

For example, where there is a strict limit of 10 Products in an Order, and/or a minimum of 2 Products in an Order?

This normalized structure does not set limits on how many records per Order can be created in OrderProduct - an Order could be created with 0 associated OrderProducts, or 200.

Is there a standard approach to handle this kind of requirement in the database design, or is it something that can only be handled in the program code whilst inserting records?


texts:
I am trying to migrate to MySQL/Percona server 8.0 and GTID based replication and have stumbled upon the issue where CREATE TABLE AS SELECT is not allowed using GTID.

The tables that are created are for materializing complex selects which span several tables and can not be replaced by a simple CREATE TABLE LIKE.

Since these are slow and complex selects they already use the CREATE TABLE AS SELECT LIMIT 0 + INSERT INTO SELECT pattern to get around the metadata lock issues. This in my mind should mean that the code I am using already is replication safe with GTIDs, but all CREATE TABLE AS SELECT are blocked when using enforce-gtid-consistency (even though https://www.percona.com/blog/2018/01/10/why-avoid-create-table-as-select-statement/ suggests otherwise), so I need a workaround.

I have already found that replacing the CREATE TABLE AS with a CREATE TEMPORARY TABLE AS together with a CREATE TABLE LIKE may get around the problem, but it seems like a ugly hack to me so I wonder if there is any better way of getting around the issue?


texts: Properties of grouping in relational algebra

texts:
The properties of relational algebra (commutativity, associativity, distribution) allow us to take a relational algebra expression and transform/rewrite it into another one which is logically equivalent. However, I am struggling to find any such properties as far as the grouping operator is concerned.

I am not sure but I think it is derived from the other relational operators. Is this correct? I would suppose that if this is correct, then it explains why it is harder to find such properties. That being said, Ɣ (GROUP BY) remains a very important operator, so how can one reason about its properties in relational algebra? Has this been done before?


texts:
The text below is from Database System Concepts by Silberschatz. It says that the actual writes take place only after the transaction enters the committed state.
My question: When the transaction has entered the committed state, there's no more statement to be executed, so who will perform these actual writes?

We must be cautious when dealing with observable external writes, such as writes
to a user’s screen, or sending email. Once such a write has occurred, it cannot be
erased, since it may have been seen external to the database system. Most systems
allow such writes to take place only after the transaction has entered the committed
state. One way to implement such a scheme is for the database system to store any value
associated with such external writes temporarily in a special relation in the database,
and to perform the actual writes only after the transaction enters the committed state. If
the system should fail after the transaction has entered the committed state, but before
it could complete the external writes, the database system will carry out the external
writes (using the data in non-volatile storage) when the system is restarted.


texts: Query Store- force plan not working as expected

texts:
Have similar issue as was ask in other question.

In my case none of cases from microsoft docs seems to apply

Can something else be the reason? Schema changes not happening, but after index maintenance new plan is generated and forced one gets ignored.

Query:

has lot of joins and union. there is query hint used in joins (maybe that is the reson?)
select list contains "*"


texts: How to Create relationship with more than one foreign key

texts:
I am using mysql and I have trouble in creating relationships of these table:

CREATE TABLE `db_menu` (
  `***menu_id***` int(255) NOT NULL,
  `menu_name` varchar(255) NOT NULL,
  `menu_code` varchar(255) NOT NULL,
  `menu_price` varchar(255) NOT NULL,
  `menu_image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_reserve` (
  `***user_id***` int(255) NOT NULL,
  `***menu_id***` int(255) NOT NULL,
  `quantity` varchar(255) NOT NULL,
  `total` varchar(255) NOT NULL,
  `date` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_sale` (
  `***user_id***` int(255) NOT NULL,
  `***menu_id***` int(255) NOT NULL,
  `quantity` varchar(255) NOT NULL,
  `total` varchar(255) NOT NULL,
  `date` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_status` (
  `***menu_id***` int(11) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_user` (
  `***user_id***` int(255) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_pass` varchar(255) NOT NULL,
  `user_type` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

my problem is to create relationship of these tables

  db_menu(menu_id) = db_status(menu_id)   db_user(user_id) and

  db_menu(menu_id) = db_reserve(user_id)(menu_id) db_user(user_id) and

  db_menu(menu_id) = db_sale(user_id)(menu_id)

thank you!!!


texts: Progressive child tables scan in PostgreSQL?

texts:
We have append-only events table where events from different devices are being collected.
We use PostgreSQL 9.6 and pg_partman to split the table into monthly partitions.
Data is partitioned using event_time column.
Each monthly table has about 100M rows.

There's a query from application side that is used to show latest events from a device. It looks something like this (simplified):

SELECT event_time, event_data
FROM events
WHERE device_id = 'zzz'
ORDER BY event_time DESC
LIMIT 10

Every search criteria is covered by index, so PostgreSQL does only index scans.

The problem is that it scans all child tables and only then does the LIMIT 10.
Indexes for all these tables are quite big and don't fit into memory, so it takes up to 20 seconds to complete this query.

But in most cases the 10 most recent events are available from one child table (partition) that is named events_p<current_year>_<current_month>.

Is there any way to implement "progressive" scan for child tables inside PostgreSQL without changing application code?

For example:

events
events_p2019_02 <- and so on...
events_p2019_03 <- scan this if less than 10 rows found in _04 and _05
events_p2019_04 <- scan this if less than 10 rows found in events_p2019_05
events_p2019_05 <- scan this first

We don't know in advance the possible date range for that 10 events. Some device may have them during the last day, while others may have them spanned over several months (so in many child tables).


texts:
So, I have a really weird case.
Was doing a migration from sql server 2005 to 2012. Built the server, and at the last minute we found that the server was called abc/xyz. We just wanted abc, so the DBA did a bunch of stuff including dropping servernames and adding entries to the TCPIP and now we have abc!

Now, I come to creating a publisher. Start by configuring a distributor, and it fails. I get the message that the abc cannot be configured as a distributor because it cannot connect to abc/xyz (old server name) since 'distributor_admin' is not defined as a remote login at the server.

If I do select @@servername, the answer is abc. If I do sp_helpserver I see that a linkedserver has been created to repl_distributor to abc/xyx (old server).

I've tried editing repl_distributor to point to an actual server, but thus far, no success.

Any ideas? I'm totally stuck.


texts:
I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the ascii value on each character.

If I am reading the query correctly, effectively, 3 CTEs are being used to prepare a table of 1 column containing 10,000 rows, each with the value '0'.

A fourth CTE is defined as follows:

cteTally(n) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
    FROM E4
)

Subsequently, this CTE is joined to a table containing a column with the strings of interest, with the following select:

SELECT n, SUBSTRING(LastName, n, 1), ASCII( SUBSTRING(LastName, n, 1))

That is, row number n, then the nth character in LastName, then the ascii value of that character.

My questions relate to the over clause in the CTE above.

Essentially, what exactly is it doing?

If we are querying row_number from 10,000 identical rows, why do we need an order by clause at all? Why is the order by put into an over clause rather than as an order by clause for the select statement - especially as the over clause isn't even specifying any partition? (I presume this means the window over which row_number operates is the full 10,000 rows?) And what does it mean to order by select null?


texts: How to verify the index on a table was created "Concurrently"

texts: Normally after I create an index, I check pgAdmin to verify that the index was created correctly. However, I'm not sure how to verify if the "concurrently" argument I passed in went through since I don't see it listed in the SQL Pane as I do with the other properties I've specified. Please help. Currently using version 1.22.1 connected to a PostgreSQL 9.5 server.


texts:
Is it possible to query mysql server to know which configuration file is in use? Sometimes, my.cnf, my.ini.

SHOW VARIABLES; -- does not have enough information here for my.ini path