texts: Find columns referenced by virtual column expression

texts:
Does anyone know of any Oracle dictionary views that hold references of 'real' columns to virtual columns?

For example lets say I have a table like this:

create table t (
 c1 varchar2(5)
 ,c2 as (c1 || '*')
 )

I'm after a way of determining that c1 has a dependency of c2 (without trying to parse user_tab_cols.data_default).  I'm writing a schema synchronising tool and I'm trying to work out what I need to do to synchronise a table with a 'model' performing the minimal amount of work.  If a model showed that c1 from the above table changed from varchar2(5) to varchar2(6), I'm hoping to be able to determine that I need to do something like:

alter table t modify( c2 as ( null ) );
alter table t modify( c1 varchar2(6) );
alter table t modify( c2 as ( c1 || '*' ) );

And avoid an ORA-54031.

[EDIT]

It seems I wasn't clear enough in explaining my problem so here goes.

When our developers start working on a bug they create a git branch of the application along with a database schema that is populated with the contents of the master git branch. While working on the bug the developer may make DML changes or may occasionally make DDL changes (as well as code changes of course). Once their bug has been completed and reviewed, their git branch is merged with master. The sync tool allows a complete 'diff' to be done on the database schema - both from a content and a structure perspective. This forms part of the review. The developer uses the sync tool to generate an export of their schema and this export is part of the branch. After the bug branch is merged to master, the master database schema is sync'd from the export done by the developer.

Yes, scripts would be one solution to the problem but it requires a level of discipline that is not present in all developers. The sync tool currently handles the scenario I outlined but imo somewhat clumsily. The reason I asked my original question was that if I was able to determine via a dictionary view the relationship between real and virtual columns, the sync sql my tool generates would be more 'surgical'.

Thanks.


texts: Physical Table being used a Temp Table in a warehosue. Any advantage?

texts:
As I stated in previous posts I've inherited a warehouse based on SQL Server framework.

In my continuous review of existing components and the practices adopted by the predecessors I found something that caught my attention and surprised me: there are several places where physical tables are being used as temporal tables to manipulate large amounts of data. First reaction was to think this practice is very expensive for the DBMS but would like to have more feedback on that.

Some annotations on this topic:

Physical tables being created/droped within SPs ( tables called
"TMP_TableName")
Tables mostly used to manipulate large amount of
data
Mentioned SPs calls several times during daily night processing

Questions:

Does this practice brings up any advantage to the processing routines that I'm not aware of?
Are there best practices about this?
My plan is to update the code to use #temp tables to improve the performance. Comments on that?.
Should I consider to use variable tables? I read the performance is bad when treating with big data.

I appreciate any feedback based on your knowlege/experience you may want to share with all the stackoverflowers.

Thanks in advance,


texts: MDF file full - cannot find the source of its giant size

texts:
All,

I am completely at a loss here and I'm hoping you guys can help me out on this one. I am experiencing the following situation on an SQL Server 2008 R2 database.

MDF-file:
Allocated space: 87392.69 MB
Available space: 256.13 MB

The LDF file is only a few megabytes, most of which are available. So as you can see my database is pretty full at the moment. I tried looking for the source of this, but here is where things get "weird".

When check the top tables by usage the top table was only 3 GB big, followed by a few of 1 GB. I've summarized all those totals and came to something around +- 20 GB of data. There's a giant gap here... and I'm not sure where it's coming from. I've been googling for the last few hours hoping to get some pointers, I've checked if any BLOB data was causing this but that seems to be not the case as far as I can see.

Am I missing something here? Did anyone experience something like this before? I can't find anything related to this so I'm completely at your mercy.

If you need any specific details I'll add them when requested.

Top 10 tables based on Aaron's query. (masked names for security reasons)

Thanks in advance


texts: MySQL trigger/check on INSERT based on multiple factors (location, date and time)

texts:
I have a shift schedule planner for various warehouses. I'm trying to add multiple checks at DB level. If I'm using a composite key (by removing shift_id) to match staff, location,date time it's works but it doesn't solve the shift overlapping problem (e.g if I insert exact same data as shift_id 1 but instead of 09:00 start time is 09:01 will accept as valid shift).

In below example, the INSERT should fail for shift_id 2 because, the shift time is overlapped for same staff_id 1 on same date The next available shift for same staff (on same date and same location or a different one) should be available after 13:00. For shift_id 3 should be another fail because staff_id 1 is already scheduled for exact same date and same hours to another location (location_id 1).

I was trying to add table constraint to compare shift_start and shift_end but unsuccessfully:

 ADD CONSTRAINT NoDoubleShifts CHECK(
    NOT EXISTS (
            SELECT 1
            FROM Shift S1, Shift S2
            WHERE s1.shift_date + s1.shift_start < s2.shift_date + s2shift_start
            AND s1.shift_date + s1.shift_end > s2.shift_Date + s2shift_start)

MySQl throw errors (I'm guessing that the constraint is not well formatted "A statement was found, but no delimiter betwen it near CHECK").

The second trying solution was to add a trigger BEFORE INSERT in different combinations but didn't work either. Is calculating the time but is preventing me to add any other staff (with different id) during that time slot.

begin
  if exists (select * from shift
             where shift_start <= new.shift_end
             and shift_end >= new.shift_start ) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Shifts are overlapping';
  end if;
end;

Any help and constructive comments or anyone to pint me in right direction will be highly appreciated.

Thanks in advance..


texts: Plans created several times under Forced Parameterization

texts:
People use forced parameterization to save plan compilation cost. However, I found things different here.

The query I will run is as following, and under AdventureWorks2012 database:

DECLARE @Var VarChar(250), @SQL nvarChar(MAX)
SET @Var = NEWID()

SET @SQL =
'SELECT SalesOrderHeader.SalesPersonID,
       COUNT(DISTINCT SalesOrderHeader.CustomerID),
       SUM(SalesOrderDetail.OrderQty)
  FROM Sales.SalesOrderHeader
 INNER JOIN Sales.SalesOrderDetail
    ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
 INNER JOIN Production.Product
    ON Product.ProductID = SalesOrderDetail.ProductID
 WHERE Product.Name = ' + '''' + @Var + '''
 GROUP BY SalesOrderHeader.SalesPersonID'

EXEC (@SQL)

I configured the database to use forced parameterization, flushed the cache using dbcc freeproccache, and then ran the above query 3 times. I used following query to get the plan use stats:

select  bucketid, usecounts, memory_object_address, cacheobjtype, objtype, plan_handle, text
from (select * from sys.dm_exec_cached_plans ) as cacheplan
cross apply sys.dm_exec_sql_text(plan_handle)as text
WHERE text.text LIKE'%SalesOrderHeader%.%SalesPersonID%'

And below is the result I got:

Except row 1 are all about the queries I ran. Row 5 I can understand. This means database engine actually parameterized product.name and generate a plan for later use. That's why the usecounts = 3.

What I don't understand is why we still have row 2,3,4? Does that mean forced parameterization couldn't save anytime (or even cost more) when running the same type of queries?

I would really appreciate any suggestions or ideas. Thanks.

Additional Info:

I understand we can turn on "Optimized Ad hoc Workloads" option for the instance to save some space. However, I feel these ad hoc plan stubs are not supposed to be cached at all.
I know using sp_executesql would make solve above problem. But there are some queries in our production database that cannot be easily changed.


texts:
After seeing the following in the error log on the second node:

121003  7:16:06 [Note] WSREP: Member 0 (joiner) synced with group.
121003  7:16:06 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
121003  7:16:06 [Note] WSREP: Synchronized with group, ready for connections
121003  7:16:06 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
121003  7:17:08 [Note] WSREP: Skipping empty log_xid: COMMIT
121003  7:17:08 [Note] WSREP: ignoring DDL failure: 0 ALTER TABLE bigdata_queue_campaigns DISABLE KEYS
121003  7:17:08 [Note] WSREP: Skipping empty log_xid: COMMIT
121003  7:17:08 [Note] WSREP: ignoring DDL failure: 0 ALTER TABLE bigdata_queue_campaigns ENABLE KEYS

...

121003  7:31:33 [ERROR] Slave SQL: Error 'Table 'reportingdb.norep_zonebannertmp_bk' doesn't exist' on query. Default database: 'reportingdb'. Query: 'TRUNCATE TABLE norep_zonebannertmp_bk', Error_code: 1146

121003  7:31:33 [Warning] WSREP: RBR event 1 Query apply warning: 1, 1141

121003  7:31:33 [Warning] WSREP: Ignoring error for TO isolated action: source: 84dcb35c-0ce4-11e2-0800-4568aec9a7f3 version: 2 local: 0 state: APPLYING flags: 65 conn_id: 1106 trx_id: -1 seqnos (l: 1196, g: 1141, s: 1140, d: 1140, ts: 1349224295344525000)

I cannot login to the first node mysql -u root -p hangs. I don't see any interesting in the error log on this node.

I'm using Percona-XtraDB-Cluster-server-5.5.27-23.6.356.rhel5.

Let me know if you need further information.


texts: Sql. Calculating the size in KB of each row

texts:
I'm building now a new sql 2005 database for my company, and we expect this database to be huge with 100's of millions of records. We already know we need to dedicate a separate server with new hard drives for this task. I need to know how big of hard drives to get. I don't want to just go now and buy big and simple hard drives. I think it's better to get the small but very fast ram drives or other solutions that cost a lot.
I would like to know in a specific sql table what will be the size of each record then i'll multiple it with the amount of records i expect, so i'll know what will be the size of my database in a year/2 years...
Is there a tool that will be able to estimate it?

Thanks.


texts: Configuring PostgreSQL for write performance

texts:
One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed data for that particular instant. The data rate is fairly high; it works out to about a gigabyte a day for one database, about a tenth of that for another one. I don't expect this rate to increase. Read performance is a much lower priority and is currently acceptable.

In the logs I have this message:

LOG:  checkpoints are occurring too frequently (15 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

This value is currently set to 16, which is courtesy of pgtune.

What are the settings I should consider to improve write performance? I would prefer to keep as much safety as possible. Considering the volume of data coming in, I could accept losing some recent data in a failure as long as the bulk of the data were intact.

Edit: I'm using PostgreSQL 9.0 for now, but I plan to upgrade to 9.1. I am not posting the hardware details because while I acknowledge their importance, I ultimately will be needing to make this optimization on several machines with very diverse hardware. If the hardware is essential to the answer, please give me the general information so I can apply the answer to machines with different hardware configurations.


texts: Why does my Group Replication plugin fail in MySQL 5.7.24?

texts:
I've been using the group_replication plugin with MySQL without issues before 5.7.24. With this version, the server starts up without the plugin because of failure.
I confirmed it manually (see steps below) and it's definitely the new version it doesn't work with. I'd consider inspecting the file myself, but a binary doesn't offer much hacking opportunity.

Background on the plugin source: Since it doesn't come included with mysql-server but is well documented, I have obtained it from the Community Edition.

OS: Ubuntu 16.04.3 LTS (Xenial Xerus)

Steps to reproduce:

Have /usr/lib/mysql/plugin/group_replication.so
Verify version is 5.7.23

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Install plugin successfully in 5.7.23

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (1 min 1.16 sec)

Upgrade to 5.7.24 (uninstall everything to do with 5.7.23 do make sure it gets loaded):

dpkg -l | grep mysql
apt-get remove libmysqlclient20:amd64 mysql-client-core-5.7 mysql-common mysql-server
apt-get install mysql-server
$ mysql --version
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

Witness plugin failing to load in 5.7.24:

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
ERROR 1126 (HY000): Can't open shared library '/usr/lib/mysql/plugin/group_replication.so' (errno: 2 /usr/lib/mysql/plugin/group_replication.so: undefined symbol: _Z25group_replication_cleanupv)

Any idea what I can do to fix it, so I can bring replication back up?


texts:
I'm new one with SQL Server, and I have the following question:

My database is not big, about 1 GB, and I make regular backups with the help of third party tool. How can I be sure that if a failure occurs I will be able to restore my database with the minimal loss?


texts: Source - ODBC is not compatible with this version of the DataFlow

texts:
After deploying an SSIS package from a development server to another environment, execution and validation of the package fail with the following error:

  Error: The version of #5) Source - ODBC - Greenplum [object name] is not compatible with this version of the DataFlow

The SQL Server and Greenplum ODBC driver versions are the same on all relevant servers.  The servers are running SQL Server 2016, and SQL Server Management Studio 2017 is being used to deploy the package between servers.


texts: Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions?

texts:
I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the documentation says, given the covering index

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

queries like this can use it for index-only scans:

SELECT y FROM tab WHERE x = 'key';

Now I am wondering if such a covering index could also allow index-only scans when the covering columns appear as conditions. For instance, assume a covering index:

CREATE INDEX tab_x_y_z ON tab(x) INCLUDE (y, z);

Would this allow for index-only scans for the following queries?

SELECT z FROM tab WHERE x = 'key' AND y = 1;

SELECT x, y, z FROM (VALUES ('key1'),('key2'),('key3')) sub(id)
JOIN tab ON tab.x = sub.id WHERE y = 1;


texts:
I just want to understand and avoid mistakes ..
I have this input table , so I designed this database to manage it ,
my question is : Is the design of StudentMarks good or bad in Normalization ?
I need help please .


texts: jsonb_each_text(jsonb) returns jsonb, not text

texts:
According to docs , jsonb_each_text(jsonb) returns

  setof key text, value text

but if you pg_typeof(value) on result it shows jsonb, Eg:

SELECT *, pg_typeof(key) AS type_key, pg_typeof(value) AS type_value
FROM jsonb_each('{"2": "-200.08"}');

 key |   value   | type_key | type_value
-----+-----------+----------+------------
 2   | "-200.08" | text     | jsonb

UPDATE:
I have mistaken docs for jsonb_each_text instead of jsonb_each


texts:
I've been reading about Snapshots, and it says you can send small catalogs lets say, in a monthly routine.

I liked the Idea and i will start to study about this, but can I use snapshot for large databases?

Every week I need to backup development database  (2tb) and migrate it to test environment. It will be a good idea to make this via snapshot? Performance will not be testes, since it's development, we can wait a lot.


texts: Is it possible to restore mongodb after db.dropDatabase() by replacing the contents of folder /data/db with older backup?

texts:
In order to reset replication, I did drop the local mongo database using the following commands:

use local;
db.dropDatabase();

The mongo data directory(dbPath) is /data/db/

I took a backup of this folder before dropping the local database and reinstated the same after issuing db.dropDatabase(). After that restarted mongo service.

Is this the correct way and if not any chance of data loss pertaining to users, DB etc.?


texts: what are the events recorded in the transaction log?

texts:
in regards to what events are recorded in the transaction log , is the below an accurate/comprehensive list?

How are things recorded? are the pages that are marked as modified written to the transaction log?

is there a way to see what is in the transaction log?

The start and end of each transaction
Every data modification
Every extent allocation or deallocation
The creation or dropping of a table or index
Broker event category includes events produced by Service Broker.
Cursors event category includes cursor operations events.
CLR event category includes events fired by .Net CLR objects.
Database event category includes events of data.log files shrinking or growing on their own.
Errors and Warning event category includes SQL Server warnings and errors.
Full text event category include events occurred when text searches are started, interrupted, or stopped.
Locks event category includes events caused when a lock is acquired, released, or cancelled.
Object event category includes events of database objects being created, updated or deleted.
OLEDB event category includes events caused by OLEDB calls.
Performance event category includes events caused by DML operators.
Progress report event category includes Online index operation events.
Scans event category includes events notifying table/index scanning.
Security audit event category includes audit server activities.
Server event category includes server events.
Sessions event category includes connecting and disconnecting events of clients to SQL Server.
Stored procedures event category includes events of execution of Stored procedures.
Transactions event category includes events related to transactions.
TSQL event category includes events generated while executing TSQL statements.
User configurable event category includes user defined events.


texts: SQL row data mismatch for different users

texts:
I am using MySQL (version 5.5.43) as my database.

I have a RoR micro-service that does an update column to a model Active Record class:

model.update_column(status: 0)

The next line is an api call to different micro-service that synchronously does a SQL query:

select * from model where status = 0;

The code runs without any errors but the latter query is not fetching the record that is being updated by the former. There is milliseconds of difference between the update and the read.

Both the services are connected to the same database as different users but same access.

I don't understand why this would happen? the update_column is obviously a commit to the db, then why would the select query not fetch the updated record. What am I missing here?


texts: Oracle Database Security - Audit Trail for specific user and specific module application

texts:
I want to enable audit trail for specific user, but only for sessions when this user connect to database using one specific module applications.

For ex. I want to enable audit trail for user test1 only when user test1 is connected to database using TOAD, but not when the user test1 is connected to database using Golden6.

Is this type of audit possible?

Thanks,


texts:
I have an SQLite DB that has couples of tables. one of them looks like this:
(id integer primary key, location integer, name text, desc text, version integer)
select id,other_colum from table where location in (?,?,?) and name=? and desc=? and version=?

The order is not important and there are more columns that not relevant.
I have a query that mapping data from an external system to inner id. The mapping is done with all the above fields. I’m using an "equal" operator with all fields except location which I have a list of possible values (so I am using operator "in"). The list is not constant in values and length.
I receive data from the external system at a rate of a minute so latency is important.
This query runs 550 times on average each time (throughout range between 500 to 600 depends on the hour).
I started without any index and the query took 400 mili on average, so I added index only on location and it drops to 30 mili. I tried to add columns to that index but it didn’t help.
I created the index using the following query
create index mapping_index on table(location)

I tried to add more columns to the index to improve performance but nothing helped.
The data is mostly read-only, with few insert (20 per day) and few updates (50 per day including soft delete). the updates never touch the columns used for mapping.
There are almost 25000 rows in the table. Also always should be one and exactly one row that matches the mapping but I need to be able to identify errors such as no row or more than one row.
This is the heaviest part of my system and almost everything operation goes through this and I need to improve this.
I’m open to new technologies but prefer to stay with SQLite for simplicity
Edit:
The complete relevant DB schema:
CREATE TABLE t(id INTEGER PRIMARY KEY, otherId INTEGER, location INTEGER, name TEXT, desc TEXT, version INTEGER, createDate INTEGER, removeDate INTEGER);
CREATE INDEX mapping_index ON t(location,name,desc,version);

Result of EXPLAIN QUERY PLAN:
0|0|0|SEARCH TABLE t USING INDEX mapping_index (location=? AND name=? AND desc=? AND version=?)
0|0|0|EXECUTE LIST SUBQUERY 1

I just understand that I wrote something confusing. The whole transaction take something like 30 millisecond. the query itself take between 1 to 2 millisecond (with strange spikes of 100 millisecond). The query run in average 15 times per transaction (500 each time).


texts: For Delete trigger with condition on a joined table

texts:
I am needing to create a trigger to load deleted data records into a history table. However I want to only insert those deleted records based off a condition of a field on another table.

I've created a trigger like this

CREATE TRIGGER [dbo].[bwrTriggerAuditRecord] on [dbo].[Borrower]
AFTER DELETE AS
BEGIN
INSERT INTO AuditLog (TableName,TableID, DeletedOn)
            SELECT 'Borrower' AS TableName ,ssn, GETDATE()
FROM deleted
END

This works fine however, I am needing to add a condition so the trigger only inserts those records based on a field condition located in a joined table

Ideally if it were possible I would want to do something like this:

CREATE TRIGGER [dbo].[bwrTriggerAuditRecord] on [dbo].[Borrower]
FOR DELETE AS
INSERT INTO AuditLog (TableName,TableID, DeletedOn)
SELECT 'Borrower' AS TableName ,
       i.ssn,
       GETDATE()
FROM deleted i
join borrower bwr on i.borrowerid=bwr.borrowerid
join filedata fd on (bwr.FileDataID = fd.FileDataID )
INNER JOIN (select OrganizationID from Organization where code not like '26%') org on (fd.OrganizationID = org.OrganizationId)
where fd.filename not like '26%'
and fd.filename not like '%.copy' and fd.filename not like '%+'

Apparently this wont work since the record on the deleted table doesn't really exist.

My question is, what would be another way to achieve this?
Is there perhaps a BEFORE DELETE trigger I should use? Should I maybe use two triggers to get the information I need so that the deleted record can find the fields I am looking for?


texts: Mongodb: Does the stats command show me the capped size setting?

texts:
I am looking around my mongo instance. One of the collections was created as capped:

db.createCollection("userObjects", { capped:true, size:400000000000 })

But when I look at the db.userObjects.stats(), I don't see any flag that indicates it is capped, or the cap size.

Am I missing something? (Is there another place to look?)


texts: Why does the concatenation order change with a User-Defined Table Type?

texts:
I've encountered what seems to me to be a strange ordering issue when using CONCAT with a User Defined Table Type.

I've put the SQL below that allows me to reproduce this:

I have the following user type:

CREATE TYPE [dbo].[StringList] AS TABLE
(
    [Value] VARCHAR(8000) NOT NULL
)

And the following query to set everything up:

DECLARE @ColumnNames AS TABLE([ColumnName] VARCHAR(MAX))
DECLARE @ValueTable AS [dbo].[StringList] --VariableLineA
--DECLARE @ValueTable AS TABLE([Value] VARCHAR(MAX)) --VariableLineB

INSERT INTO @ColumnNames([ColumnName])
VALUES
('Forename'),
('Surname')

INSERT INTO @ValueTable([Value])
VALUES
('Ellis'), ('Ali')

And this is the query I'm running:

SELECT CONCAT(STRING_AGG(CONCAT([ColumnName], ' - ',  [sc].[Value]), ', '), ', ') -- QueryLineA
        --CONCAT(STRING_AGG([ColumnName] + ' - ' + [sc].[Value], ', '), ', ') --QueryLineB
FROM        @ValueTable AS [sc]
INNER JOIN  @ColumnNames ON 1=1

If the VariableLineB line is uncommented, and the VariableLineA line is commented out, the following string is returned for both query lines if run individually or together:

Forename - Ellis, Forename - Ali, Surname - Ellis, Surname - Ali,

If the VariableLineA line is uncommented and the VariableLineB line is commented out, the following string is returned for QueryLineB, and from both queries if run together, but not QueryLineA on its own:

Forename - Ellis, Forename - Ali, Surname - Ellis, Surname - Ali,

When QueryLineA is run on its own, the following string is returned:

Forename - Ellis, Surname - Ellis, Forename - Ali, Surname - Ali,

Why has the order changed?  What am I seeing here, and is there any way to prevent this from happening?

In terms of usage, the values of @ValueTable would be getting passed into a function, and it's important that the original order (whatever that may be) remains the same.


texts:
Very new to db admin. I am using Windows Server 2012 R2 in a testing environment, trying to teach myself basically. Looking into db replication.

I have a very simple and newbie question.

I am following the guidelines here on Creating a Data Snapshot Using mysqldump but I'm stuck at the first part. shell> mysqldump --all-databases --master-data > dbdump.db

I have a utility on the server called MySQL 5.6 Command Line Client. When I open this it starts with a line mysql>. From here do I need to access 'shell'? How do I access this? When I type any sort of command (i.e mysqldump --all-databases --master-data > dbdump.db) the command prompt simply moves down a line and displays ->

I am doing something very silly I know. Could somebody please give me some tips as to what I'm doing wrong?


texts: Unique key based on many-to-many pivot table

texts:
I have to manage artists and albums tables:

| artists      |  | albums       |  | album_artist |
+--------------+  +--------------+  +--------------+
| id           |  | id           |  | id           |
| artist       |  | album        |  | album_id     |
| created_at   |  | created_at   |  | artist_id    |
| updated_at   |  | updated_at   |  +--------------+
+--------------+  +--------------+

Keeping in mind that this is a many-to-many relation, I do need to find a way to get the album-artist(s) pair unique, since albums may have the same name yet belonging to different artists (f.e. "Greatest Hits" album of 2Pac and "Greatest Hits" of Notorious BIG).

Is there a known way/pattern to address this problem?


texts: difference between root and ALL PRIVILEGES

texts:
I want to create a user with restricted grants compared to root.
If I do

GRANT ALL PRIVILEGES ON * . * TO 'non-root'@'localhost';

am I assigning all the same privileges as for the root user? Or are there some privileges in the root user that are not included in the ALL PRIVILEGES privilege?

For example: Is the new user able to create other users?

Otherwise I'll explicitly list the grant I want but this question is more for my learning.


texts: Use of Trigger on table which can have 100 hits / second. Will it hamper performance?

texts:
Currently in one of our projects we have the following situation.

The server computer is Windows Server 2008 Standard SP2. Processor is Xeon (R) 2.13 Ghz. We are running SQL Server 2008 R2.

We need to implement a database trigger on a table which could have around 100 hits (for fetch and DML) per second.

The trigger will work on INSERT, UPDATE and DELETE.

I wanted to know if the system performance will suffer considerably due these triggers?

The trigger will perform addition of 2 numbers and store the result in a separate table.


texts: How to copy a tablespace to another database without using Data Pump in Oracle 11G

texts:
I have a database on a legacy system that has some data that needs to be exported to another environment. Everything I need is contained in one tablespace. Unfortunately, I am unable to use Data Pump due to previously botched patching (thousands of invalid objects, including streams packages, which can't be recompiled).

Can I use RMAN or other methods to back up this tablespace, move it to another network (by physical media transfer - networks are completely segregated) and restore it to an existing database?


texts: Incremental statistics: find out which partitions have been resampled

texts:
TL;DR: Is it possible to find out which partition(s) have been resampled and which have not when incremental statistics are used? Platform is SQL Server 2014 Enterprise edition.

The long version with some background information is like so.

Assuming a fairly typical DW environment, there is a partitioned table. The partitioning is based on a date column. This is done as staging data is loaded on separate table and after pre-processing, partition switching is used to move data into the production fact table. Oh, and a clustered columnstore index is in use. There are about a thousand partitions used. The DB is running on a virtual machine.

There are some 7.5 gigarows (100 GB) in the fact table. Daily growth is about five megarows. This is way too small a growth rate to trigger automatic statistics update, save trace flag 2371 (which haven't been tried).

The knee-jerk developer reaction to outdated statistics was updating them. For 7.5 gigarows, full update for all the stats takes some five hours. For a single statistics update, the processing performance is around 20 minutes or 90 megarows per second.

As the system is on a VM platform, business rules limit its costs. Neither memory nor IOPS are easily increased. Five hours update job is way too slow to be included on nightly ETL process, so either the stats stay outdated, are updated on unexpected time or will be updated in a maintenance window.

As the SQL Server is version 2014 Enterprise edition, it supports incremental statistics which sound just like the solution. After converting the stats to incremental ones, processing a single stat for single partition takes only 20 seconds. Grand total for newly-switched partition is about five minutes. This sounds great and certainly fits in the ETL process.

The thing that I wonder is how to manage incremental stats in partition switched environment. Assuming the stats are converted and updated as incremental on date D, how does one find out unprocessed partitions on, say, date D+2? Updating stats in ETL process is trivial, as the switching process obviously is aware of the partition id. But if there are partitions that are not resampled, how does one find those?

Last update for stats can be found from sys.dm_db_stats_properties
Partition numbers are available from sys.partitions
Partitioning function values are in sys.partition_range_values

One could pick last update date L for a stat and compare it to today's date T. Then calculate which partition id L points and wheter it is the same as T's. Then proceed with update for all the partition ids [L, T). This sounds tricky and error prone, so is there better a way? A DMV that shows which partitions are used for resampling would be nice, but there isn't one, is there?


texts: How do you increase MySQL performance using current server resources?

texts:
I have a MySQL database and some of my queries became slow.  Query time is not stable. Most of the queries are fast, but some of them (may be that reads and returns less data) take long time.

I know that best practices is add indexes or refactor code, but i'm already added indexes and I don't want to refactor code (at least while I have other variants).

I have 8 Gb free memory and CPU is loaded only to 25% at peak. So I want to use all my resources.

I try to tune MySQL configuration but I havn't experience in such tuning, so i increase productivity no so much that I want. Here is an example:

# Query_time: 3.019647  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 504
SET timestamp=1313380874;
SELECT COUNT(inboxentities.id) FROM inboxentities  WHERE (active=true)AND(deleted=false)AND((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))AND(notification=true);

P. S. inboxentities has 500.000 records. I have index1 (idx_to) : to_ and index2 (idx_complex): deleted,notification,active,to_

Here is result of explain select:

+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+
| id | select_type | table         | type | possible_keys                                  | key    | key_len | ref   | rows | Extra       |
+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | inboxentities | ref  | idx_status,idx_statusToname,idx_to,idx_complex | idx_to | 768     | const |  286 | Using where |
+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+


texts: How to control access security in database

texts:
Our database's security became a problem recently. Here is what we have:

Several basic roles -

read_only (select, view definition)
read_write (insert/update/deleted)
executor (execute SP/function)
ddl_admin (alter/create table/view/SP/function/trigger/almost any thing, it's a risk role and we are going to discard it)
several ssis roles for ssis jobs
etc

Those roles are in every database and in all environments (DEV/QA/STG/PROD).

We also have a bunch of user groups such as tech_leads, QA, developers (based on different projects), reporting, BA and so on.

We grant those users different roles on different environments. Usually people have more privileges on DEV/QA than STG/PROD. We almost only give regular user read only in production databases.

However, there were many occasions that users come to ask specific permissions on different servers. For example, the tech_leads may ask for server control on DEV/QA. Or one developer team ask for read_write permission on STG temporarily for trouble shooting.

After a long time, we got a massive database security.

Now our DBAs trying to think of some better ways to re-regulate the users and roles. We got advised that creating some new roles such tech_leads role, QA_readonly role, QA_leads role, developer role, and granting those roles to relevant groups.

The interesting thing here is, in same environment, say QA environment, the same role will have same permissions across all databases. But the role on PROD environment will be different than QA, meaning has much less permissions on PROD. Every time we modify the role in one database, we have to modify the role on all databases in that environment (we will have scripts for this of course).

I can't tell if the advise is good for long term yet. But another opinion would be keeping most of our basic roles, splitting some of the existing roles and create more tiny roles. For example, removing ddl_admin, and add a role to create SP/trigger/function, a role to create table/view, a role to alter, and a role to create synonym. This way, all of our roles will be static across all databases, and all environments.

I want to know how you guys control your database security? And can you give us any suggestions on what I talked above? Thanks very much.


texts:
In MySQL Workbench 6.1 (OS X), can you make the Result Grid taller?

I know the region expands to fill the space vacated by Action Output/Text Output/History, should you choose to hide that.

But I'd like to shrink the space used by the Queries (i.e. "Query 1") and make the Result Grid taller (at the expense of the Queries tabs). Can it be done?


texts: How can I create a copy of postgis db from one windows machine to another

texts:
I have postgres 9.3 database with postgis extension.
I want to create a new database on different computer and import all the tables from the source database.

In mssql it would be simple , just backup -> restore.

In postgres when I try to backup and restore using pgAdmin i keep getting different problems.

The PostGIS tables (with the geometries columns) are not being backedup
I get errors when resoring that sequence does not exits

Is there a simple way / tool on windows to backup / restore postgres db with gis extension ?


texts: SQL order by query results in any arbitrary (but reproducible) manner

texts:
For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is there an idiom for that?

E.g. I can obviously do a:

SELECT * FROM table_with_N_columns ORDER BY column_1, ... , column_N

I am just asking if there is an idiomatic way to achieve the same effect (for my purposes) without bothering to list every column in the ORDER BY clause. Any ordering will do so long as it is reproducible with subsequent runs of the query.


texts: indexing on multiple nvarchar(max) columns

texts:
I have multiple nvarchar(max) columns in my DB named ShipperName,ConsigneeName, ProdDesc,BillOFLading.

What I need to do is to have UNIQUE INDEX for the combination of these columns. As I am failed to do so because if 900 B limit.

Then I tried(after reading following link Indexing Wide Keys )

ALTER TABLE dbo.ProductDetail
   ADD ShipperNameHash AS HASHBYTES('SHA2_512', ShipperName) PERSISTED;

ALTER TABLE dbo.ProductDetail
   ADD ConsigneeNameHash AS HASHBYTES('SHA2_512', ConsigneeName) PERSISTED;

Then I got following error on following statement:

  Msg 8152, Level 16, State 10, Line 1
  String or binary data would be truncated.
  The statement has been terminated.

ALTER TABLE dbo.ProductDetail
   ADD ProductDescHash AS HASHBYTES('SHA2_512', Product_Description) PERSISTED;

But also above statements created column varbinary(8000). Which of course hasn't solved the problem for me.

Is there any way that I can built UNIQUE INDEX on the combination these columns ?


texts: 'Tempdb' is full warning message in SQL Server 2005

texts:
I am using SQL Server 2005. it's a running plant, Everyday large number of data posted into SQL Server 2005

SELECT
  TOP 5000 [T_idx], [T_TAG], [T_Date]
FROM
  [house].[dbo].[total]
ORDER BY
  T_Date DESC

From SQL Server 2005 I executed above script, and observed below error message:

  5000 row effected

  Msg 9002, Level 17, State 6, Line 1
  The log fie for database 'tempdb' is full. Backup the transaction log for the database to free up some log space.

and the 'tempdb' file size 860 Mb and my hard disk free size 9.4 GB. Is this space is
enough or do I need to free-up some space?


texts:
I'm sort of new to SQL Server so bear if the doubts are silly. I have configured log shipping for my DR purpose. SQL Server 2012 is the version. Today I encountered the following error in my log shipping instance.

   Message
2016-01-25 22:30:16.08  Searching for an older log backup file. Secondary Database: 'TwoSite'
2016-01-25 22:30:16.10  Skipped log backup file. Secondary DB: 'TwoSIte', File: 'E:\Logshipping_Backup\TwoSite\TwoSite_20160125163001.trn'
2016-01-25 22:30:16.14  Skipped log backup file. Secondary DB: 'TwoSite', File: 'E:\Logshipping_Backup\TwoSite\TwoSite_20160125161501.trn'
2016-01-25 22:30:16.15  Skipped log backup file. Secondary DB: 'TwoSite', File: 'E:\Logshipping_Backup\TwoSite\TwoSite_20160125160000.trn'
2016-01-25 22:30:16.15  *** Error: Could not find a log backup file that could be applied to secondary database 'TwoSite'.(Microsoft.SqlServer.Management.LogShipping) ***
2016-01-25 22:30:16.16  Deleting old log backup files. Primary Database: 'TwoSite'
2016-01-25 22:30:16.16  The restore operation completed with errors. Secondary ID: '61a4194e-e591-459f-8466-56e42fd359e9'
2016-01-25 22:30:16.17  ----- END OF TRANSACTION LOG RESTORE     -----

Exit Status: 1 (Error)

I have tried to reconfiguring by taking a new full backup of primary as well. Getting the same error still.

How can this be resolved?

I can give more info if needed.


texts: How to find out the models and prices for all the products (of any type) produced by maker B

texts:
The database scheme consists of four tables:

Product (maker, model, type)
PC (code, model, speed, ram, hd, cd, price)
Laptop (code, model, speed, ram, hd, screen, price)
Printer (code, model, color, type, price)

The Product table contains data on the maker, model number, and type of product (PC, Laptop, or Printer). It is assumed that model numbers in the Product table are unique for all makers and product types.

Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed column, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, 4x) - cd, and its price.

The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen.

For each printer model in the Printer table, its output type (y for color and n for monochrome) – color column, printing technology (Laser, Jet, or Matrix) – type, and price are specified.

Now the query I came up with:

SELECT PC.model, price FROM PC
INNER JOIN Product ON PC.model = Product.model
UNION
SELECT Laptop.model, price FROM Laptop
INNER JOIN Product ON Laptop.model = Product.model
UNION
SELECT printer.model, price FROM printer
INNER JOIN Product ON printer.model = Product.model
WHERE Product.maker='B'

The result should be:

model   price
1121    850.0000
1750    1200.0000

I am doing SQL exercise and not able to get the same result.


texts: Duplicate key value violates a unique constraint

texts:
I've been looking at other posts. It seems many people have had this problem, but I'm somewhat new to Postgres and tried other solutions with no luck.

I have ny_stations table:

CREATE TABLE ny_stations (
id integer primary key,
name text,
latitude numeric,
longitude numeric,
nyct2010_gid integer,
boroname text,
ntacode text,
ntaname text
);

I am trying to insert values from another table, ny_raw_trips:

INSERT into ny_stations (id, name, latitude, longitude)
SELECT DISTINCT start_station_id, start_station_name, start_station_latitude, start_station_longitude
FROM ny_raw_trips
WHERE start_station_id NOT IN (SELECT id FROM ny_stations);

Getting the error:

  ERROR:  duplicate key value violates unique constraint "ny_stations_pkey"
  DETAIL:  Key (id)=(151) already exists.

What am I doing wrong? Also,

SELECT pg_get_serial_sequence('ny_stations', 'id');

...returns nothing. Please let me know if any other info is required.


texts: SQL Server Linked Server - Timeout Error

texts:
I am trying to create a linked server between two SQL Servers (ServerA and ServerB).  ServerA is hosted by Windows Server and ServerB is hosted on a local Windows desktop.  I want to create a linked server from ServerA to ServerB.  I'm doing this using SSMS GUI.  ServerB is a named instance (ie ServerB\MyInstance).  Under "Security", I am using "Be made under this security context" and I enter the "sa" account.  However, I'm getting this error "Login timeout expired".  The weird thing is, I can establish the connection using c:>sqlcmd -SServerB\MyInstance -Umylogin -Pmypw from my desktop.

The credential is the same in both cases.  Can you someone tell me what I'm doing wrong?  Would it be a firewall issue?  Not sure about this since I can connect via sqlcmd.

Thanks


texts: I just deleted 19k hi-res images from a table column and have a few questions about shrinking

texts:
We just moved to a new image-storage method for our application, which means we don't need to store image byte arrays in the database any longer.

To that end, I have deleted (via an UPDATE statement) about 19k byte arrays containing hi-res images (about 8 GB) from a column in a single table. Without the images, I do not expect the database to ever grow back to the size it is now. Again, please note that I did not delete rows here, but have only set field values to NULL.

Other Info:

The column type was VARBINARY
There was no index on the column.

Questions

Should I just go ahead and shrink the database, then rebuild indexes for that table?
Most scenarios I read involve shrinking after a DELETE statement. I didn't delete any rows, I only set the field value to null for all rows. Does this change anything?
The column is now deprecated and we may want to just delete it from the table; I believe this will require the table to be recreated. Should I shrink the database before or after I delete the column?

Edit:
Another possibility just occurred to me. With the image data gone, it might be better to just write a script to completely rebuild the entire database, without the column image column. Thoughts?


texts: SQL Server 2008R2 Full Text Catalog Missing Records

texts:
I create one simple Full Text Catalog on a varchar(500) filed, which mainly store address text like 24 King Street Melbourne VIC 3000. The catalog is set to auto track changes. But I found there are always a few records in the table are not indexed by Full Text Catalog after I fully populate it.

For example, the table has 10,000 records, but the full text has only 9,997 records. So there are three records are not indexed. I rebuild the full text and all records are indexed. But after user start to insert data, there are missing records again. Table records increased to 11,000. But the full text records only have 10,999. Any idea why full text always missing some records?

If I run a manual update on the missing record, it will automatically indexed.

The filed included in the full text is updated by a trigger. It seems full text has issues tracking field that are updated by trigger?

Environment

SQL Server 2008 R2 Service Pack 2 - 10.50.4000

Full Text is set to Auto Track Changes


texts: Access articles from a table if user belongs to qualified group

texts:
The tables below are myuser & mygroup, where each user has membership to 1 or more groups via an M2M table membership.

The articles are accessible by users if one of the condition is satisfied
1) article belongs to an open group
2) article belongs to a group of which the user is a member

Here's me query to select an article, given group name FOO-BAR and user id BAZ-ID. I'm unable to express the condition that BAZ-ID should belong to list of user id's that are part of group FOO-BAR.

P.S. Using PostgreSQL 9.3

SELECT *
FROM article a
INNER JOIN mygroup g ON g.id = a.mygroup_id
INNER JOIN membership m ON m.mygroup_id = g.id
WHERE
  (g.name = 'FOO-BAR')
  AND
  (
    (g.open is TRUE)
    OR
    (?)                  -- `BAZ-ID` IN a list of m.user_id
  )

Tables

  CREATE TABLE myuser
  (
    id serial NOT NULL,
    name text NOT NULL,
    CONSTRAINT pkey1 PRIMARY KEY (id)
  );

  CREATE TABLE mygroup
  (
    id serial NOT NULL,
    name text NOT NULL,
    open boolean NOT NULL,
    CONSTRAINT pkey2 PRIMARY KEY (id)
  );

  CREATE TABLE membership
  (
    id serial NOT NULL,
    myuser_id integer NOT NULL,
    mygroup_id integer NOT NULL,
    CONSTRAINT pkey3 PRIMARY KEY (id),
    CONSTRAINT fd1 FOREIGN KEY (myuser_id) REFERENCES myuser (id),
    CONSTRAINT fd2 FOREIGN KEY (mygroup_id) REFERENCES mygroup (id),
    CONSTRAINT ukey1 UNIQUE (mygroup_id, myuser_id)
  );

  CREATE TABLE article
  (
    id serial NOT NULL,
    content integer NOT NULL,
    mygroup_id integer NOT NULL,
    CONSTRAINT pkey4 PRIMARY KEY (id),
    CONSTRAINT fd3 FOREIGN KEY (mygroup_id) REFERENCES mygroup (id),
  );


texts: Can the subquery be simplified/optimized?

texts:
Here's a simplified version of a query I run. I've removed a bunch of key/value pairs for simplicity. The constraint is that I can't change anything outside of the ARRAY function; but the ARRAY function and everything inside is fair game. I want to remove the subqueries if at all possible; and if not speed up the query as much as possible.

SELECT json_build_object('last', max(day), 'missing_days', ARRAY(
             SELECT distinct to_char(dates, 'YYYY-MM-DD')
             FROM generate_series('2009-12-20', CURRENT_DATE, interval '1 day') AS dates
             WHERE dates NOT IN (SELECT day FROM records))
       ) AS jsobj
FROM records;

I thought I remembered from my database design classes that you should be able to use columns from the records table in the sub-query but I could be mistaken, or that might be database dependent, or I could have been trying the wrong syntax; anyway it didn't work when I tried it.


texts:
I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs?

for rec in select column from table1
loop
execute stored_procedure( rec.column );
end loop;


texts: Why write ahead logs in PostgreSQL are generated every second

texts:
PostgreSQL version 9.2 generates write ahead log (WAL) every second i.e. 60 WALs are generated in one minute.

Is it possible to make them occur less frequently?

Is it possible to increase the WAL file size, so that fewer WALs will be generated in one minute?


texts: I've seen that NoSQL database systems like RocksDB offer a feature called column families. I believe I understand what the concept refers to, but what are the actual (practical) benefits of using them? I presume they can improve look-up performance in some cases, or space locality of key-value entires, at the very least? It wouldn't seem to affect the actual semantics of database access, however, as far as I understand. Is this correct? Is there something I'm missing?


texts: Database Mail sending functionality not working on local system

texts:
I am using Database Mail functionality to send mail from a SQL Server 2008 database via following stored procedure execution:

EXEC sp_send_dbmail @profile_name = 'MyProfile',
                    @recipients = 'abc@companyname.com',
                    @subject = 'Test message',
                    @body = 'Congrats Database Mail Received By you Successfully.'

I have tried with my gmail account profile on my local system it's working properly but not with my company or outlook profile.

Error message:

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 . Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond abc.j.i.ooo:pp). )

Reference

http://blogs.msdn.com/b/suhde/archive/2009/07/12/how-to-configure-sql-server-database-mail-to-send-email-using-your-windows-live-mail-account-or-your-gmail-account.aspx

What would be the problem?

Thanks


texts:
I have a transactions table, each transaction has an address field which references a row in an address table, each address in the address table has a coinID.

I want to get a sum of all transactions for EVERY coin for a specific user. I don't care about ownership, I want to display every coin in the table regardless if users own it or not.

The problem I have is that if a user has 0 transactions or addresses that belong to a specific coin it is completely missing from the result. I need all coins in a coin table that have 0 transactions or addresses to return with a sum of 0.

SELECT
        CASE WHEN SUM(transactions.amount) IS NULL THEN 0 ELSE SUM(transactions.amount) END AS balance
FROM coins
Left outer  JOIN addresses ON addresses.coin_id = coins.id
Left outer  JOIN transactions ON transactions.address = addresses.address
Left outer JOIN users ON transactions.user_id = users.id
WHERE users.email = 'matt@test.net'

I have tried quite a few combinations of queries but no matter what I do I get 1 of two options

rows are only returned for coins that the user has a transaction/address for
rows are returned but contain the balance of all transactions in the database not just the ones belonging to that user.


texts: Average of last 4 runs of a product on SQL Server 2008

texts:
I'm looking to do the exact same moving average from this post: Average of last 4 runs of a product

but now on SQL Server 2008, which doesn't support the extended window aggregate functions.

Is there an alternative I can look into using to create the same solution in SQL Server 2008?


texts: Slony Replication: Will data eventually be 100% replicated if daemon dies for long period?

texts:
I am using Slony to replicate a Postgres database.

If my slony daemon exits for whatever reason, and doesn't start up hypothetically until 1 month later, will all the data that has accumulated in the master be replicated eventually to the slave? Of course, it might take a long time for that to happen, but will any data be lost as a result?


texts: Target database memory exceed available shared memory

texts:
I am trying to install Oracle 12c on a RHEL 6.4 system with 64GB of memory.  When I try to enable automatic memory to 48GB, I get the error:

What do I need to change to allow Oracle to use more than 32189MB?

[INS - 35172] Target database memory (49897MB) exceeds available shared memory (32189MB) on the system.

/etc/sysctl.conf contains the following so I'm not sure why it won't allow me to use more than 32G of shared memory

# Controls the maximum shared segment size, in bytes
kernel.shmmax =  68719476736
# Controls the maximum shared segment size, in bytes
#kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 16777216
#kernel.shmall = 1073741824
fs.file-max = 6815744
kernel.msgmni = 2878
kernel.sem = 250 32000 100 142
kernel.shmmni = 4096
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 3145728
net.ipv4.ip_local_port_range = 9000 65500
vm.min_free_kbytes = 51200
fs.file-max = 6815744

limits.conf contains the following:

oracle          soft    nproc           2047
oracle          hard    nproc           32768
oracle          soft    nofile          32768
oracle          hard    nofile          65536


texts:
I'm building an education data warehouse. I have various dimensions that i would like to track changes to i.e. DimStudent, DimClass, DimCollege, DimExamPaper etc. etc. My solution currently drops keys, truncates tables and recreates keys before loading staging tables, which then go on to load Dimension and fact tables.

Therefore, my question is what are the minimum physical changes i need to put in place to --> truncate staging tables prior to load, track changes between staging tables, dim tables and fact tables, to incrementally load rows to dim tables and fact tables? Do i need to add a table/tables to track changes for EACH dimension or fact table? I have looked at examples but there seems to be a fair amount of work for just one table. I have 11 dimensions, 4 fact tables and 22 staging tables.


texts: Why does most Galera cluster documentation recommend having at least 3 nodes as the typical set up?

texts:
The Galera documentation and most experts implementing this type of setup, recommend having at least 3 nodes in the Galera cluster. What is the reasoning behind this?

Is a cluster setup with only two nodes essentially flawed?


texts: Use cases for NoSQL databases: Time series

texts:
I need to set up a database that will record temporal series data (sensors). A friend of mine has recommended me to use a NoSQL database instead of using Oracle (for instance).

There're also some articles that recommend the same  approach: see Andrew Oliver's article.

Why is not convenient to record a temporal series in Oracle (or any other relational database)? Could you please help me in giving an example?


texts: Percona / MySQL audit log plugin - limiting the content?

texts:
I've been looking to experiment with Perconas Audit Plugin, as it sounds like it could be potentially very useful.
However it appears that it is a very blunt (ALL or NOTHING) tool.

From my perspective, I am simply interested in DDL statements (CREATE, ALTER, DROP etc).

Is there an way to limit what it logs, or is it really just ALL or NOTHING.

I fear I already know the answer, as I can't see any mention in the documentation.


texts: Is there any (hidden) built-in function on MS-SQL to unquote object names?

texts:
Sometimes I store object names (identifiers) in some of our databases, for example in some parameter tables. Because I select records from these tables using the '=' or 'LIKE' comparison operators, I must take care to store these names always with or without brackets.

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = '[TABLE_NAME]';

or

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = 'TABLE_NAME';

However MS-SQL has some functions where you can use object names with or without brackets, for example the OBJECT_ID() function. I've set up a minimal example on dbfiddle.uk.

CREATE TABLE TEST
(
    ID     INT IDENTITY(1,1) PRIMARY KEY,
    OBJECT sysname NOT NULL
);
GO

INSERT INTO TEST VALUES ('[obj1]'),('obj2'),('obj3'),('[obj4]');
GO

Now I can use OBJECT_ID() to check if the table TEST exists in this way:

IF OBJECT_ID('TEST') IS NOT NULL
BEGIN
    SELECT 'TEST EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID    |
| :----------- |
| TEST EXISTS. |

IF OBJECT_ID('[TEST]') IS NOT NULL
BEGIN
    SELECT '[TEST] EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID      |
| :------------- |
| [TEST] EXISTS. |

It doesn't matter if I pass the identifier TEST with or without brackets, parser is smart enough to remove the brackets.

Well, I can simulate this by adding a scalar function that remove brackets from one string:

CREATE FUNCTION UNQUOTENAME(@TXT NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
    BEGIN
        RETURN IIF(LEFT(@TXT, 1) = N'[' AND RIGHT(@TXT, 1) = N']',
                   SUBSTRING(@TXT, 2, LEN(@TXT) -  2),
                   @TXT);
    END;
GO

And then use it in this way:

SELECT dbo.UNQUOTENAME (N'[FIELD]') NAME1, N'FIELD' NAME2;
GO

NAME1 | NAME2
:---- | :----
FIELD | FIELD

SELECT ID, OBJECT
FROM   TEST
WHERE OBJECT LIKE 'obj%';
GO

ID | OBJECT
-: | :-----
 2 | obj2
 3 | obj3

SELECT ID, dbo.UNQUOTENAME(OBJECT)
FROM   TEST
WHERE  dbo.UNQUOTENAME(OBJECT) LIKE 'obj%';
GO

ID | (No column name)
-: | :---------------
 1 | obj1
 2 | obj2
 3 | obj3
 4 | obj4

But my question is:

Is there any hidden built-in function that removes brackets using T-SQL?

dbfiddle here


texts: Numbered stored procedures: introduction and deprecation?

texts:
SQL Server and Sybase ASE both support the highly dubious feature of grouping stored procedures by appending a number:

CREATE PROCEDURE Foo;1 AS ...
CREATE PROCEDURE Foo;2 AS ...

The only tangible benefit is that DROP PROCEDURE Foo will drop all of these procedures together -- and that's it; to call them you still need to append the number explicitly. The drawbacks of allowing a semicolon to be part of a procedure name and having non-obvious DROP semantics should be obvious, and indeed many tools don't (properly) support this format and things like contained databases outright forbid them.

In what version of SQL Server and/or Sybase ASE was this feature introduced, and from what version onwards was it deprecated? I'd consult the documentation, but documentation that goes back far enough does not appear to be available. I've found the following:

SQL Server Books Online 2005 already mentions them as deprecated.
SyBooks Online for ASE 12.5 mentions it as being available there as well, but having a flag to disallow them.

This leads me to think the feature might have existed in very early versions indeed, possibly before the split between Sybase and Microsoft, but still doesn't tell me what the earliest introduction and deprecation was. The asteroid to kill this dinosaur is still in orbit, but how old is the dinosaur?


texts: Function in same Session become increasingly slow when run multiple times concurrently

texts:
PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
Client is Data Grip and same behavior from my report server which uses the driver that comes with Jaspersoft
I am running the same function for reporting with different parameters multiple times under the same session. Also does the same thing using the same parameters.
These are being one run one after the other and not at the same time.
The result only has a few rows but does read from quite a few tables, no writes.
It is just table joins and selects no inputs or updates to the tables themselves (would like to be able to post query but can't for security reasons).
After I run the function a few times it starts to slow down and gets to an unacceptable level. For example one of the functions goes from 1 second to over 90+ seconds (that is where I stopped testing).
Troubleshooting:
I have gone to the server and terminated the session and after that it starts to run normally for a few runs.
The standard report does use a temp table but I have removed that for testing. I have run the following after it starts having the issue to try and fix the issue.
VACUUM all touched tables; -- I know this should not be required as there are no major changes to these tables but was trying pretty much anything.
Language plpgsql.
SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD SEQUENCES;
DISCARD TEMP;

There does not seem to be a good way to terminate idle connections without a separate application or script run through a job.
Further data point: when looking at the execution plans with auto_explain, I see plans with $1 in them from the sixth execution on.


texts:
A 3rd part app is using 'our' database views. Basically flow looks like this:

New Row is inserted to Results table;
Insert new row to Event table (with information about new result).

The 3rd part is saying that sometimes they receive an event (also indexed view) but when they check indexed view for result it returns null. Asked programmers if that situation is possible from code side, they said it is not possible because the program wait with inserting row to Event table until result is finally saved (committing transaction).

From timestamps I see it takes ms from Result to Event. Like Result is saved at .320 and Event is saved at .350.

So my question is: Is it possible that it take so much time for indexed views to be actualized or maybe they are being actualized during insert statement?


texts: Permission required to view a database diagram

texts:
I have recently set up SSDT for our developers to use. We enforce changes to our dev databases are made through SSDT by limiting the permissions that each developers has when connected to the server (db_datareader, db_datawriter). Within SSDT we publish our changes to the database using a deployment script which connects using a log on with elevated permissions.

My question. Given that we have gone to this length to lock down the database (to stop schema drift); is there any way that the developers can view the diagrams on this database without having to have db_owner permission? I know that each developer can create and view his or her own diagrams but i want them to be able to view all of the diagrams, which have been created by many different developers.

I don't think this will help but we are running sql server 2012

Any help will be greatly received.


texts: How to export user with tables and tablespace oracle

texts:
I'm a newbie to Oracle World.

I have to export, from computer A, tables and their data, to computer B.

So, my question is: is there a way to export user, tablespace, tables and their data with one single command?
Or I have to create before a tablespace and a user in the computer B, and then import the tables?

Sorry, the question might seem stupid, but I'm a newbie, I'm a little confused and afraid to not fully understand the  correct procedure/steps.
Thank you


texts: Is it ok to add a table years in a Relational DataBase?

texts:
How can i explain my coworker that this is unnecessary complexity and redundant data?

He wants to have a table of years because many tables have the 'year' value, and he also want to have relation table with names and years, this would add unnecessary inner joins and Foreign  i insist that this is wrong, i just want to make sure this is not a good practice...


texts:
I have a static binlog file to be consumed by downstream application ( Debezium ), however the application only consumes from a MySQL server.

Is it possible to spin up a MySQL server only with the purpose of stream binlogs from a file? Like copying binlog file to /var/lib/mysql?


texts: Replace database Mirroring Principle Server

texts:
I have been asked to look at the steps required to replace a Windows 2012 R2 Server that is used as the Principle Mirroring server due to it being "sized" wrongly at the time of design.
I'm just looking to see what steps to use that would be the best way to achieve this? I was thinking the following but would appreciate if there may be an easier less disruptive way of doing it? Thanks

Manual Failover the databases so the Mirror is now the Principle and the Principle is now the Mirror
Remove Database Mirroring
Remove the "old" windows server that originally was the Principle
Bring online the "new" windows server and setup SQL Server
Re-establish mirroring between the 2 servers through setting up mirroring steps
Manual Failover the databases so the new server is acting as the principle


texts: Poor temporal table performance on older values

texts:
I'm encountering a strange issue occurring when accessing historical records within a temporal table. Queries that access the older entries in the temporal table via the AS OF sub-clause take longer than queries on recent historical entries.

The historical table was generated by SQL Server (includes a clustered index on the date columns and uses page compression), I have added 50 million rows to the historical table, and my queries were retrieving about 25,000 rows.

I have tried to determine the root cause of the issue but have not been able to identify it. So far I have tested:

Creating a test table with 50 million rows with a clustered index to see if the slow down was simply due to volume. I was able to retrieve 25K rows at constant time (~400ms).
Removing page compression from the historical table. That had no effect on the retrieval time but did significantly increase the size of the table.
I tried accessing the rows of the history table directly using an ID column vs the date columns. This is where things were a bit more interesting. I could access older rows in the table at ~400ms where as with the AS OF sub clause it would take ~1200ms. I tried filtering on my test table on the date column and noticed a similar slowdown when compared to filtering on the ID column. This leads me to believe that the date comparisons are behind some of the slowdown.

I want to look at this more but I also want to make sure that I am not barking up the wrong tree. First, has anyone else experienced this same behavior when accessing older historical data in a temporal table (we only noticed slow downs passed 10 million rows)? Second, what are some strategies I can use to further isolate the root cause of the performance issue (I just started looking into execution plans but it is still a bit cryptic to me)?

Execution plans

These are simple retrieval queries: the first accesses older rows, the second accesses newer rows.

Older Rows ~1200ms execution time

Recent Rows ~350ms execution time

Table details

These are the columns in the temporal table. The history table has the same columns but does not have a primary key (as per the history table requirements):

Below are the indices on the history table:


texts: How to query plan cache for all query's using a table variable?

texts: Is there a way to query the sql plan cache for all query's using a table variable?


texts: What does MySQL "USE INDEX()" without any arguments do?

texts:
Does the USE INDEX() have any effect here?

SELECT `posts`.`id` FROM posts USE INDEX() where `posts`.`size` > 1024;

(I have it presently in some SQL because of the way the query is programmatically constructed depending on certain criteria.)


texts: Cloning Postgres schema/tables across multiple servers

texts:
I'm working on a project that has databases across multiple servers, with one server for each business client.  The project has some identical 'base' tables that are present in all the databases (for example, profiles, groups, etc), but each database also has some tables that are specific to that client.  I'd like to be able alter/add columns in the base tables without having to manually do it for each database.  We're currently working with 6 clients, so every time we add a new feature that requires me to add a column I have to manually add the column to 6 different databases.  Obviously, this does not scale well.

Is there a tool that allows for changes to a base table to be replicated across an arbitrary number of databases/servers?  Thanks.

Edit: For clarification, each base table is independent to each client, because those base tables exist on the server that is specific to that client.


texts:
I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example:

BEGIN TRY
  BEGIN TRAN
    -- Run updates 01
    INSERT INTO dbo.Log (Text) VALUES ('Updates 01 - big success');

    -- Run updates 02
    INSERT INTO dbo.Log (Text) VALUES ('Updates 02 - big success');

    -- and so on...
  COMMIT
END TRY
BEGIN CATCH
  ROLLBACK
  INSERT INTO dbo.Log (Text) VALUES ('At a certain step there was a problem: ' + ERROR_MESSAGE());
END CATCH

Of course, in case of errors we lose the success log entries done inside the transaction and are left only with the last (error) message inserted after the rollback took place.
Is there a way to make those INSERT INTO dbo.Log statements "persistant" before the transaction finishes?
I guess, if it were, it would fly in the face of proper transaction control, so I on some level hope this doesn't exist, but maybe somewhere in the category of tricks?

Thank you.


texts: Could this ActiveRecord/SQL statement in my code base be susceptible to SQL injection?

texts:
I was reading up on SQL injection attacks from the Rails Guides and was wondering if the below line could be susceptible to a SQL Injection attack:

accounts.where("lower(name) LIKE '%#{params[:query].downcase}%'")

If I read and understand the guide correctly, could a user do something like

 `'\''; DROP TABLE users;`

Would that drop my table?

------EDIT------

Same for these examples:

Account.where(id: account_id).first
current_user.actions.where(id: params[:clear_action_reminder]).first
edits.where.not(account_id: non_human_ids)
Rating.where(project_id: @project.id, account_id: current_user.id).first_or_initialize

These are of course application specific queries but because there is no ? in the query could these all potentially be disastrous?


texts: Can you link tables based off data in a cell/field?

texts: I have 4 tables that I want to link.  The main table is Contributors and then I have 3 tables for the events, Golf, Breakfast, and Dinner.  I have a field that has a value list with 3 selections in the contributor table.  Can I then have the other tables link off that list?  The other tables show the years they particpated and how much they gave.  Some people will be on all 3 some will only be on 1.


texts:
MonogDB's dynamic schema has made me thinking about replacing MySQL. I eventually want to use MongoDB as the default for all new app developments.

But this project runs on only 1 dedicated server (with 2 hard drives).

Docs about "MongoDB for production" recommends multiple servers. This makes me wonder if MongoDB is only suited for large commercial projects?

Anyways... I am wondering if the live database data can be replicated to the second hard drive as a replica of the original data (to recover from any data corruption on the primary).

I did take a look at mongodump, but I am wondering if there is anything more realtime like the replica set.

Currently my server backs up everything to the second hard drive & rsync is scheduled to sync everything in this drive to a remote machine.

Any thoughts against the use of MongoDB in a single server environment is also appreciated. The database size is around 7GB.

Thanks


texts: Looking for another method to count in a single query

texts:
So, I found this article is very useful Join two tables and return data and count in a single query
and here Single query to return counts over different IDs in a single record for some IDs or parameter to use.

So my question is, if I have more than 10 IDs or parameters for which I need counts,  using COUNT(CASE WHEN ... (as below) becomes awkward and hard to maintain:

SELECT COUNT(CASE WHEN table1.id = 1 THEN table2.id END) as count1,
       COUNT(CASE WHEN table1.id = 2 THEN table2.id END) as count2,
       COUNT(CASE WHEN table1.id = 3 THEN table2.id END) as count3,
       ...,
       COUNT(CASE WHEN table1.id = 10 THEN table2.id END) as count10

Is there a better way to solve this problem?

More details:
I am using MySql database and use Codeigniter to process my data. This my current query:

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class Model_provenance extends CI_Model {
    function __contruct()
    {
        parent :: __contruct();
        $this->load->database();
    }
    function count_koleksi_by_provenance_status() {
        $query = $this->db->select('provenance.provenance_status, cat_provenance.status AS provenance_status,
                                    COUNT(provenance.provenance_status) AS total,
                                    COUNT(CASE WHEN provenance.provenance_status = 1 then cat_provenance.status END) AS count1,
                                    COUNT(CASE WHEN provenance.provenance_status = 2 then cat_provenance.status END) AS count2,
                                    COUNT(CASE WHEN provenance.provenance_status = 3 then cat_provenance.status END) AS count3,
                                    COUNT(CASE WHEN provenance.provenance_status = 4 then cat_provenance.status END) AS count4,
                                    COUNT(CASE WHEN provenance.provenance_status = 5 then cat_provenance.status END) AS count5,
                                    COUNT(CASE WHEN provenance.provenance_status = 6 then cat_provenance.status END) AS count6,
                                    COUNT(CASE WHEN provenance.provenance_status = 7 then cat_provenance.status END) AS count7,
                                    COUNT(CASE WHEN provenance.provenance_status = 8 then cat_provenance.status END) AS count8,
                                    COUNT(CASE WHEN provenance.provenance_status = 9 then cat_provenance.status END) AS count9', FALSE)
                        ->join('provenance', 'provenance.provenance_status = cat_provenance.ID', 'LEFT')
                        ->group_by('cat_provenance.status')
                        ->order_by('cat_provenance.ID', 'ASC')
                        ->from('cat_provenance')
                        ->get();
        if($query->num_rows() > 0) {
            return $query->result();
        } else {
            return NULL;
        }
    }
}

The cat_provenance data table looks like this:

ID | status
------------------
1  | Grant
2  | Legacy
3  | Gift
4  | Purchase
5  | Recompense
6  | Discovery
7  | Search
8  | Exchange
9  | Conversion

The provenance data table has the following columns:

ID
provenanceID
coll_regID
authorID
provenance_status
date_from
date_to
buy_price
provenance_desc
post_by
post_create
edit_by
edit_datetime

I want the final result to look something like this:

Grant (2)
Legacy (5)
Gift (0)
Purchase (0)
Recompense (1)
Discovery (10)
Search (5)
Exchange (3)
Conversion (0)

The key here is I need to see a total for every category, even if that total is 0.

So if I have 20 ID or more in the cat_provenance table, do I have to write this part:

COUNT(CASE WHEN provenance.provenance_status = 1 then cat_provenance.status END) AS count1

or is there another method that I can use so I do not write that part one by one?

Hopefully, this better explains my question.


texts:
I have a client with a SQL2016 DB with a 100Gb MDF and a 4Gb LDF. The DB is set to Simple.
I can't get it done to shrink the LDF to a minimum. How can I shrink the LDF to a normal level?
Does this setup and file sizes effects performance?


texts: How to order and partition by 2+ columns and then select a random sample based on N number of ranks in the sample?

texts:
I am using PostgreSQL.

I have a table with 2 columns in this example:

I want to add a new column with a unique id corresponding to partitions by name and category as shown in the result. Then, I want to take a random sample choosing 2 (or more) unique ids because under each unique id, there will be a lot of other historical data.

I have tried this so far, but I get 1s for everything. I'm missing something really simple here, but how do I correct my mistake? I have to do this operation for several million rows in the real table.

SELECT
  dense_rank() over (partition by name, category order by category) as unique_id,
  *
FROM
 example_table

After this, presumably I'll have to use RAND() somewhere but how do I do this?

This is my naive approach to get the solution in the above pic.

with ranks (
  ...
)

select * from ranks where unique_id = 2 or unique_id = 4


texts: MySQLSyntaxErrorException on ALTER TABLE ... CONVERT TO ... in MySQL Connector/J

texts:
Running Ubuntu 14.04 with mysql-connector-java-5.1.28 installed.

Let's create a table:

CREATE TABLE mytable (value1 VARCHAR(32000), value2 TEXT, value3 VARCHAR(1000), value4 VARCHAR(32000)) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Executing the following ALTER TABLE statement via Statement.execute() fails saying that "Column length too big..*".

According to the documentation ALTER TABLE ... CONVERT TO ... should however perform automatic type conversion as necessary to ensure that the new column is long enough to store as many characters as the original column.
The statement indeed runs in the native MySQL client performing the necessary conversions.

Am I stumbling into a bug in JDBC or is this behavior expected?

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Column length too big for column 'value1' (max = 21845); use BLOB or TEXT instead
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:949)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:795)
    at Main.main(Main.java:32)


texts: Create Table. Foreign key constraint is incorrectly formed

texts:
I have my sql query

SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE IF NOT EXISTS `cms_pages` (
  `id` int(11) NOT NULL,
  `chain_id` int(11) NOT NULL,
  `default_title` varchar(255) DEFAULT NULL,
  `created` int(11) NOT NULL,
  `video` text,
  `video_key` varchar(255) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=1;

but when I run it I get

#1005 - Can't create table `cms_pages` (errno: 150 "Foreign key constraint is incorrectly formed")

There's no foreign key addressing. But still the same issue.
I searched whole stackexchange but unable to find answer.
My DB :


texts: Groups relationships - Detailed or Direct?

texts:
I have a Person model:

PersonId    Name
----------- ----------------
1           Jessica
2           Jennifer

Then I have a ShareModel which have the Personand an Itemassociated.

ShareId     ItemId
----------- ----------------
1           1
2           2

And finally I have a SharePeoplewhich tells all people that have access to that Item.

ShareId     PersonId
----------- ----------------
1           1
1           2

So now a new feature must be added, which is Groups. So I thought about two aproaches and I would like to know which one is the more correct.

So two things I'm clear, one is to add a new IsGroup property to the Person Model.

PersonId    Name             IsGroup
----------- ---------------- -----------
1           Jessica          False
2           Jennifer         False
3           Blondes          True

And another is to have the GroupPeople

GroupPersonId    ChildPersonId
-------------    ----------------
3                1
3                2

OPTION 1 : Should I create the association on the SharePeople with the IsGroup rows directly:

ShareId     PersonId
----------- ----------------
2           3

OPTION 2 : should I create the association always with the persons which are part of the group, and have another column on that will identify from which Group the association was made.

ShareId     PersonId            GroupPersonId
----------- ----------------    -------------
2           1                   3
2           2                   3

The second option is more simple when getting data from tables, it will be fast and will create less impact, however it creates a lot of rows on the database.

The first option is cleaner but I will have to do a lot of logical statements inside my code wheter the row it's a Group or a single Person.

But bottom line is that I really don't know what's better concerning performance, and I also don't whant to do tons of odd code.

Thank you very much,


texts: How to migrate Oracle Database to MariaDB?

texts: Currently we have a server on Oracle version 18. We are planning to migrate to MariaDB. Is there any free service provided by MariaDB or oracle that helps migrate a database from Oracle to MariaDB?


texts: Do not add a comma in front of the string if value is null or empty

texts:
If my result is null or empty I would like it not to add a comma in front of the string. How can I achieve that?

  Update Companies
    set address4 = concat(address4,','+ t.Eircode)
    From companies c
    Inner Join Temptbl1 t
    on c.comp_id = t.Comp_ID
    OR address4 = ''

Update :

Just to make it clear when I make this update

   ,V05 TTX1
    Waterford,X01 B234
    ,B90 E902
    Co Wexford,TD2 PVE2

It adds a comma regardless ,if it was empty or not

What I want to get back is this  :

    V05 TTX1
    Waterford,X01 B234
    B90 E902
    Co Wexford,TD2 PVE2

If there is no value then just add the string without the comma ,in our case V05 TTX1 without , at the start of the string

CREATE TABLE [dbo].[companies](
    [comp_id] [int] NOT NULL,
    [address4] [varchar](32) NOT NULL
)

INSERT INTO [dbo].[companies]
           ([comp_id]
            ,[address4])


texts: Error while upgrading SQL server 2012 to SQL 2017 : Failed to create a new folder 'X:\\xxxx'. The specified path is invalid

texts:
Failed to create a new folder 'X:\MSSQL\DATA\MSSQL11.MSSQLSERVER\MSSQL\LOG'. The specified path is invalid (for example, it is on an unmapped drive).
I checked there is no X drive present in the server. I verified that all the default files for data and log points to the drives which are available in the server.
Not sure , how do I resolve this as i don't see X drive mentioned in any of the properties.
I have checked in Facets and database properties to verify the path.


texts: Bad practice to return more columns than required

texts:
I unfortunately have quite a broad question regarding queries that return more columns than they need.

Simply put, is this bad practice and should it be avoided at all costs? We continuously have debates about this in our development team. Some argue that all queries should only return the exact data they need as this would reduce I/O etc, others argue that it is OK to have a query that returns a few extra columns such as int or nvarchar(50) as this reuses execution plans and makes development faster. We are not using any SELECT * statements anywhere so this is not an issue - it is purely where a few extra columns are explicitly specified.

I know this may trigger a debate rather than an answer but any insight would be helpful.


texts: Dropping a group of schemas with similar name patterns

texts:
Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object.

In my case, I need to drop some schemas, all of the form ceu_shard_test_merge_*, to use shell globbing terminology. So, conceptually, this can be written as

DROP SCHEMA ceu_shard_test_merge_* CASCADE;

by analogy with a Unix shell.

Of course this command doesn't work, so how can one do this with a single command? My understanding is that this cannot be done portably. I'm using PostgreSQL 8.4, but methods for more recent versions of PG are fine too.

It would be nice if the solution had a dry run or dummy option, so one could see what commands were going to be run before actually running them. Perhaps a way to just print the commands?

Also, an indication of how to deal with more general patterns than the example given would be nice.


texts: syntax error on mathematical operation postgresql

texts:
I'm trying to declare this function to be used as a trigger:

create or replace function atualiza_qtfunc()
returns trigger as
$f_at_qtfunc$ --f_at_func = função atualiza quantidade funcionários
declare qt integer;
begin

select tabemissora.qtfunc into qt from tabemissora;

if (TG_OP = 'INSERT') then

UṔDATE tabemissora set qtfunc = qt + 1;

elseif (TG_OP = 'DELETE') then

UṔDATE tabemissora set qtfunc = qt - 1;

end if;

end;
$f_at_qtfunc$ language 'plpgsql'

But I'm getting:

ERROR:  syntax error at or near "UṔDATE"
LINE 12: UṔDATE tabemissora set qtfunc = qt + 1;

What am I doing wrong?


texts: How to recover if SQL Server is stuck in Restoring state?

texts:
My demo server went down and database is now stuck in Restoring mode. I tried to bring it back with

RESTORE DATABASE DB_Name
WITH RECOVERY, REPLACE

but it throws an error:

  Msg 4333, Level 16, State 1, Line 4
  The database cannot be recovered because the log was not restored.

Then I tried to restore the log files from local drive

RESTORE LOG DB_Name
FROM Disk = 'H:\Log\DB_Name_log'
WITH RECOVERY;
GO

Again I got an error

  Msg 3201, Level 16, State 2, Line 7
  Cannot open backup device 'E:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\H:\Log DB_Name_log'.
  Operating system error 123 (the filename, directory name, or volume label syntax is incorrect.)

I did reboot multiple times no use of it. How can I bring back my database to normal state from restoring mode?

I tried with the suggestion provided in below link it's not working for me. SQL server databases stuck in restoring state


texts: How can I clear output of a query in pgAdmin III?

texts: I wonder whether I can clear the output of a query in pgAdmin III (aside from running another query).


texts:
I am using oracle 12c. We have an oracle view ALL_SCHEUDLER_JOBS. As we will loosing the access to this table, I create a view on it as below :

the below function is placed inside a package called PCK_SUP which is in SCH1

function read_scheduler_jobs return t_scheduler_jobs pipelined
    as
    l_scheduler_jobs all_scheduler_jobs%ROWTYPE;
    cursor c
    is select * from all_scheduler_jobs where owner in ('SCH1', 'SCH2');
    begin
      open c ;
      loop
        fetch c into l_scheduler_jobs;
        exit when c%NOTFOUND;
        pipe row(l_scheduler_jobs);
      end loop;
      close c;
      return;
    end read_scheduler_jobs;

create or replace view vis_scheduler_jobs as
select "OWNER","JOB_NAME","JOB_SUBNAME","JOB_STYLE","JOB_CREATOR","CLIENT_ID","GLOBAL_UID","PROGRAM_OWNER","PROGRAM_NAME","JOB_TYPE","JOB_ACTION","NUMBER_OF_ARGUMENTS","SCHEDULE_OWNER","SCHEDULE_NAME","SCHEDULE_TYPE","START_DATE","REPEAT_INTERVAL","EVENT_QUEUE_OWNER","EVENT_QUEUE_NAME","EVENT_QUEUE_AGENT","EVENT_CONDITION","EVENT_RULE","FILE_WATCHER_OWNER","FILE_WATCHER_NAME","END_DATE","JOB_CLASS","ENABLED","AUTO_DROP","RESTART_ON_RECOVERY","RESTART_ON_FAILURE","STATE","JOB_PRIORITY","RUN_COUNT","MAX_RUNS","FAILURE_COUNT","MAX_FAILURES","RETRY_COUNT","LAST_START_DATE","LAST_RUN_DURATION","NEXT_RUN_DATE","SCHEDULE_LIMIT","MAX_RUN_DURATION","LOGGING_LEVEL","STORE_OUTPUT","STOP_ON_WINDOW_CLOSE","INSTANCE_STICKINESS","RAISE_EVENTS","SYSTEM","JOB_WEIGHT","NLS_ENV","SOURCE","NUMBER_OF_DESTINATIONS","DESTINATION_OWNER","DESTINATION","CREDENTIAL_OWNER","CREDENTIAL_NAME","INSTANCE_ID","DEFERRED_DROP","ALLOW_RUNS_IN_RESTRICTED_MODE","COMMENTS","FLAGS","RESTARTABLE","CONNECT_CREDENTIAL_OWNER","CONNECT_CREDENTIAL_NAME" from table(PCK_SUP.read_scheduler_jobs)
readonly;

We have two schemas SCH1 and SCH2. I created the above function and view in SCH1.
If I run "select distinct owner from ALL_SCHEDULER_JOBS", I can see both SCH1 and SCH2.
But, when I do the same from VIS_SCHEDULER_JOBS, I cannot see SCH2. I only see SCH1.

Thanks for your help


texts: Sql server sqllogship.exe...full docs and/or source code?

texts:
Are the internals and all the logic of sqllogship documented?

Eg so i can manage it directly, without the wizard? Or replace it with my own tool? (For example, to rewrite it in powershell or C# -- the goal being to instrument it more fully and allow for different transports for the logs [other than a file share].)

I find this syntax (which is real, from a live SQL 2014 instance, but created by the log ship wiz) to be impossible to fully parse:

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\sqllogship.exe" -Backup DB46C321-3453-48FC-9B20-C1823E2B534A -server WIN-2OOC0JPLR5K

What does the guid point to?

(Would be awesome if this were open sourced... Or better yet, ported to powershell and offered to the community...)


texts:
I inherited a SQL Server environment. In (database engine) SQL Server Agent are two jobs defined that apparently belong to the report server.
First and only step in the first job:
exec [ReportServer].dbo.AddEvent @EventType='CacheInvalidateSchedule', @EventData='c5b50dad-33bb-40b8-a696-59b1a555e8e4'

First and only step in the second job:
exec [ReportServer].dbo.AddEvent @EventType='RefreshCache', @EventData='f1ae0c17-b150-4861-8d9a-8552036123a3'

Any ideas how these two jobs were originally created and what they do? When I check the report server, there are no schedules defined:

The connected report server is empty:


texts: Multi Session Mirroring using 1 witness server, cannot find certificate

texts:
I am trying to use 1 witness server for 2 mirrored databases that reside on different servers.

For example:

  db1 on server(A) mirrored to server(B) using witness server(1), and

  db2 on server(C) mirrored to server(D) using witness server(1)

However, High Safety using witness server(1) only works with db1 mirrored session.
When trying to set the witness on db2 session, I get error message:

  The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness server(1)'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Mirroring is up and working on the first db1 using witness1, and mirroring also works with db2 between server (C) & (D), however the second db on server(2) is unable to connect to the witness server(1). The witness shows this error: Message

  Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.' Client x.x.x.x

I copied the original witness server cert over to both db2 primary/secondary servers. All 3 certs are on each server.  I am not using AD, just SQL Auth, and Certificates.

Microsoft Documentation suggests that a Witness can be used in multiple sessions here: http://msdn.microsoft.com/en-us/library/ms175191.aspx

Any help would be fantastic! (I'm Using SQL Server 2014 Std on Windows 2012R2 x64 machines in AWS)


texts: In mySQL, I shouldn't use semicolon when runing a source command. Why?

texts:
I'm learning SQL by myself. I've set up a mySQL server, and I was trying to import the sakila example database, and the textbook I'm using states that for that effect I should use source pathway; and end it with a semicolon. This is consistent with the initial warning at the top of the client MySQL command prompt.
However, I get several errors.
So, someone recommended me to run the command without the semicolon. When I run it , everything works just fine.
Why is that?


texts:
We are looking to launch a 9 node percona xtradb cluster on Amazon where each ec2 node uses 20k iops disks.

I was afraid if a 9 node cluster will work and mostly what I see is 3 node clusters.

We are write heavy on some tables and also read heavy.

Any suggestions.


texts: "undefined function pg_pconnect()" when connecting to Postgres from CodeIgniter

texts:
I just installed Postgresql and pgadmin in my Ubuntu system. I can create a database from the pgadmin interface and everything seems ok, but when I try to connect to the database from my CodeIgniter application it cannot not connect. It shows an error like:

An uncaught Exception was encountered

  Type: Error

  Message: Call to undefined function pg_pconnect()

  Filename: /my codeigniter application
  path/system/database/drivers/postgre/postgre_driver.php

My database configuration is:

$db['postgres_db'] = array(
    'dsn'      => '',

    'hostname' => 'localhost',
    'username' => 'postgres',
    'password' => '1234',
    'database' => 'finance',
    'dbdriver' => 'postgre',
    'dbprefix' => '',
    'pconnect' => TRUE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE,
    'port'     => 5432
);

How can I resolve this problem?


texts: Creating a copy of production database for reporting purposes

texts:
We previously ran Crystal Reports straight off our production database. This caused a direct impact to the users using the database. We moved to a new (dedicated) server and a new database and ditched Crystal Reports in favor of SSRS. We also have a dedicated reporting server to which our production database is copied over to twice a day. We run queries off of the reporting server as to not slow down the production environment.

Is this the best solution to our problem? The backup restorations kick everyone off the reporting server whenever they occur and management is asking we step up this process (backup restoration) to once every two hours. However, just twice a day gives us issues. For example, as I write this, the database is stuck in "Restoring..." and has been for at least 12 hours. So in the meantime that the DBAs come in (I only write reports) I thought I'd ask. Is this the best possible setup we've got going on?


texts: Fiscal Year dynamic function (Oracle SQL)

texts:
My Fiscal Year starts on 1st October (2017) and ends 30th September (2018)

I want to write a function that will dynamically calculate first day of October.

Currently I have:

select Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'), -3) from dual


texts: MongoDB + XFS - Snapshot backups - Cannot shrink XFS filesystem

texts:
I am using MongoDB 3.4 (with WiredTiger) with several replica sets and it has been great so far.

I want to back up my data using snapshots as per the MongoDB recommendation for production backups.

I am also using XFS, as per the MongoDB warning in logs and my question "Is XFS still the best choice for mongodb?".

I moved my disk to LVM (using rsync to transfer all data to the new volume) and I allocated 100% of the VG storage to MongoDB (i.e. this disk will be used exclusively for MongoDB).

As a result I cannot take snapshots because there is no space in the VG, and XFS cannot be shrunk. Furthermore, if any zealous sysadmin runs resize on that disk, I will never be able to take snapshots again.

Is there a good solution to this problem?


texts: SQL 2005: Can we determine how much the rebuild index maintenance job can grow database log files?

texts:
On SQL server 2005, if all the databases are in Full mode (with hourly transaction log backups), is it possible to determine if rebuilding all indexes of a database can grow log file of a database? And how much can it grow?

If there is no straight answer then any directions would be really appreciated.

Thanks in advance.