texts: Postgresql Deadlock - what does "while rechecking updated tuple" mean?

texts:
Question: Can someone please decipher while rechecking updated tuple for me in the following:

2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:ERROR:  deadlock detected
2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:DETAIL:
    Process 31391 waits for ShareLock on transaction 770573675; blocked by process 25788.
    Process 25788 waits for ShareLock on transaction 770573695; blocked by process 31391.
    Process 31391: update my_table row_to_update set col_to_update = now() from (select id from my_table where col_to_update is null order by id limit $4 for no key update) matched_row_to_update where row_to_update.id = matched_row_to_update.id returning row_to_update.id as id, row_to_update.col_to_update as col_to_update, row_to_update.other_col as other_col
    Process 25788: update my_table set col_to_update=$1 other_col=$2 where id=$3
2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:HINT:  See server log for query details.
2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:CONTEXT:  while rechecking updated tuple (36328,33) in relation "my_table"
2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:STATEMENT:  update my_table row_to_update set col_to_update = now() from (select id from my_table where col_to_update is null order by id limit $4 for no key update) matched_row_to_update where row_to_update.id = matched_row_to_update.id returning row_to_update.id as id, row_to_update.col_to_update as col_to_update, row_to_update.other_col as other_col

I found the it here but couldn't figure it out what exactly is happening there. what is the lock? and how can I debug/resolve it?

To be clear I understand what a deadlock is. I'm asking, based on the error message what is happening, or very simply what the error message means. For example, https://bit.ly/2TaWh6c or https://bit.ly/2Sk0lg0 or https://bit.ly/2E2v9g9 describe different situations with different error message. But I couldn't find anything about the error I'm getting.

Full story:
We have a service (multiple instances working on single DB) which does the following:

Thread #1
1. Fetch some rows from DB to process in transaction #1 (Process 31391).
2. Process fetched rows, update them.
3. Save all processed/updated rows back to DB in transaction #2 (Process 25788).

Thread #2.
1. Periodically check rows in DB which are in process mode for too long and release them by checking and setting col_to_update to null in transaction #3 (since we're using JPA/Hibernate, effectively the query is the same as Process 25788).

Previously we had deadlock errors with the message: while updating tuple (xxx,xxx) in relation my_table between two transactions with the same query (query of Process 31391). We managed to solve it by adding order by id for no key update.

Now we're getting the deadlock above which I don't understand. Even an answer to the following question would help: what's the difference between updating tuple and rechecking updated tuple?


texts: What will happen with MySQL transaction if one of the queries fails?

texts:
I have 500 independent insert queries. I put them in a transaction to run faster. I am runing queries with PHP.

What will happen if one of queries fails? Will the transaction be rolled back automaticaly or not?

Maybe this question has been asked before, but I cannot find an answer. If there is one, please share it with me.


texts: Correct Code Page or Collation in SQL Server for Portuguese characters

texts:
I'm inserting data (Bulk insert and merge update) for a Portuguese data source (CSV files) into my database. Although the DB collation is Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data and data stored in NVARCHAR columns, the portuguese charaters are not correctly being saved.

i.e : Original Data

Regularização de Importação de mercadorias das ZEEs com Pagamento  diferido, com Isenção

Data in database after insert

RegularizaÃ§Ã£o de ImportaÃ§Ã£o de mercadorias das ZEEs com Pagamento  diferido, com IsenÃ§Ã£o

Here the import statement:

CREATE PROCEDURE [dbo].[SP_Import_Temp_CPC]
-- Add the parameters for the stored procedure here
@FilePath [nvarchar](1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

TRUNCATE TABLE Temp_CPC;

-- Insert statements for procedure here
EXEC ('BULK INSERT Temp_CPC
FROM ''' + @FilePath + '''
WITH ( FIELDTERMINATOR = ''<,>'', ROWTERMINATOR =''\n'', FIRSTROW = 2, KEEPIDENTITY, CODEPAGE = ''ACP'',TABLOCK,ORDER(ITEMID ASC) );');
END

I've tried with "ACP,RAW and OEM" but get the same result.

Am I doing something wrong here?


texts:
I Copied some database backups from one server to another server(new instance no databases are present).I tried restoring using below command

Restore database dbname
from disk='g:\e2bak\dbname.bak'
with
stats=10

It failed with below error

  Directory lookup for the file "G:\MSSQL$s73g\MDF\dbname.mdf" failed
  with the operating system error 3(The system cannot find the path
  specified.)Msg 3156, Level 16, State 3, Line 1

Also server level default database settings are to store data and log in

G:\MSSQL10_50.SG\MSSQL

I can overcome the error by specfying move option. But my confusion here is

1.Why database .bak file is looking for G:\MSSQL$s73g\MDF\dbname.mdf",this is the path
  of dbname database files in production from where we copied the .bak file

2.Also when I restored the database with move option and took a backup in same server,
  I was able to restore the Database with below command

Restore database dbname
from disk='G:\e1ebk\dbname.bak'
with replace,stats=10

Can you please help me understand why .Bak file location is pointing out to some other directory(which doesn't even exist in our server and existed only on production server) and in second case why this worked without move option.


texts: How could DBAs be more 'programmer friendly'?

texts:
The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing about the divide between DBAs and programmers in some workplaces.

What could DBAs do differently to work better with programmers on issues like this?

Should we:

Study the tools and languages our programmers are using to understand their difficulties they face, particularly when working with well designed databases?
Encourage programmers to be better educated about databases and the advantages of having business logic at the database level?
Change the way we define interfaces to our data - such as by using more programmer friendly transactional APIs (eg for issues such as backwards compatibility)?


texts:
What is the fastest/best approach to load data from SYBASE to SQL server DB. I have tables with 500 million records. I tried using SSMA and BCP, both are taking over 24hrs. I tried using datastage (etl) methods but not of much help.
Thanks.


texts:
My database setup is modern (My)SQL.
I've got a Content Table, including ids.
Now, users should be able to comment on content.
To achieve this, arrays have come to my mind.
Now I see several options of representing them :

A second table Comments with Content id and Comment
One new table for each Content, to store the comments(advantage : no need to store content id again for each comment)
A Text/String column, using a certain separator to seperate comments & comment infos
Same as above, but using a BLOB

Which one should I use ? What other options haven't I thought of ? Thanks for taking your time to answer !


texts: Is it possible for SQL statements to execute concurrently within a single session in SQL Server?

texts: I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on exactly what a session is capable of. In particular, if it is possible for this stored procedure to execute twice concurrently in a single session, a significantly higher isolation level is required for a transaction within that procedure due to the two executions now sharing a temporary table.


texts: Which design could be better for scale a MongoDB query for this usecase

texts:
Context
Imagine that you have two MongoDB collections, one called nodes and the other called mirrors.
Each mirror is replica of a node. So, in case the node is removed (for whatever reason), you should delete its corresponding mirrors.
Currently, the query being performed to obtain mirrors whose node doesn't exist (so they should be deleted) is:
{
  '$lookup': {
    'from': 'nodes',
    'localField': 'node',
    'foreignField': '_id',
    'as': 'node'
  }
},
{
  '$match': {
    'node': {
      '$size': 0
    }
  }
}

This query performs well in an environment where mirrors' collection has 50 million or fewer documents (using a limit of around 500, of course).
As node and _id are indexed, this query could do the job, but as the size is increasing, it is starting to be so slow.
Question
Which could be the best solution to avoid or make this query faster? Could be a new collection where documents are the joined collections? Could be some sort of trick that I'm not realizing?
Please, take into count that these collections do not have other fields indexed neither I could index more fields as this is a very big database. The last time when I tried to index a field,  mongo lasted 3 days to perform the change.
I'm looking for the best practices, but I can make some commitments as I know that this kind of case is not common in our logic, nor the database was thought for this type of operations.


texts: keeping database columns consistent with documentation

texts:
For a project, the client wanted to be able to add fields to any item and have them be searchable. To make them searchable, that would require the fields to take a column, but that would make the database design, in documentation, inconsistent with the columns present in the database.

So we ended up implementing a virtual column system; this made is so that 4 tables were needed for this feature:

main table containing the basic columns
a category table
a table to hold the virtual column information
a table used to define what the virtual column was called and what it holds.

When a column is added to a category, any row in with that category has an entry made in the virtual column table, which is foreign linked to the column type and the row it was made for.

Is this a bad idea for design or performance?


texts: What could be causing this rogue CPU spike on MS SQL Server running on EC2?

texts:
We've just had a rogue CPU spike on one of our main SQL boxes, and we're struggling to work out what caused it. We use Red Gate SQL Monitor to track various performance metrics, and we saw a couple of interesting performance monitor things around around the time of this spike, but they all appear to have been caused by the CPU usage spike - so the originating cause of the spike is a complete mystery.

All other metrics - I/O performance, network, memory usage - were completely normal during this spike, and restarting the MSSQLSERVER process on the box appears to have cleared it.

We've seen spikes like this before, but historically they have always coincided with a sudden increase in website traffic or somebody running a particularly intensive SQL maintenance job or similar; this one is a complete outlier and we've never seen anything like it. The box in question is running:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4297.0 (X64)
Nov 22 2013 17:24:14
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

and it's hosted on an i2.2xlarge instance on Amazon EC2. We're looking into a number of things that could have caused it, but one potential explanation would be CPU throttling or contention on the underlying hosting infrastructure. Has anyone encountered similar issues with either SQL Server, or specifically with SQL hosted on EC2 instances?

EDIT: We'd provisionally dismissed memory consumption as a potential cause of this, because we're monitoring both SQL Server's total memory usage, and the amount of 'free' memory being reported by MSSQL itself within SQL Monitor. Memory usage metrics for the 24-hour period surrounding the spike are here; there's no sign of anything changing or growing in the period immediately preceding the spike. Does this data shed any further light on the matter?


texts:
I am migrating data between two databases.  From the first database I am exporting a csv file that has the following columns:

Customer_Name
Contact_First_Name
Contact_Last_Name
Phone1
Phone2
Fax

I need to take this data and put it into the following tables for the new database:

Table: customer_contact
    contact_id  INT IDENTITY(1,1)
    first_name  VARCHAR(50)
    last_name   VARCHAR(50)
    customer_id INT
    PK = contact_id

Table: contact_phone
    contact_id INT
    phone_num  VARCHAR(15)
    in_use     BIT
    PK = contact_id, phone_num

Table: contact_fax
    contact_id INT
    fax_num    VARCHAR(15)
    in_use     BIT
    PK = contact_id, fax_num

My plan is to BULK INSERT the data from the csv into a temporary table, and then use that to do INSERTs into each of the other tables.

Since the main identifier for each contact if their first and last names, how do I insert the corresponding data into the phone and fax tables?  I am using SQL Server.


texts: Does Trace Flag 8295 applies to SQL Server 2012?

texts:
I've been searching about the undocumented Trace Flag 8295.
It's been introduced with KB 2476322 in SQL Server 2008 and "'Creates a secondary index on the identifying columns on the change tracking side table at enable time'. This allows creation of a secondary index for changetracking tables, which we heavily use for replication between Configuration Manager sites. "
I have a SQL Server 2012 server and it has changetracking enabled on many tables. I've seen some SortWarnings go by on queries that tries to get informations via the "CHANGETABLE (tableName, tracking key)" function.
We usually join the CHANGETABLE informations with the underlying table via the primary key, but then select only a subset of the informations.
I was thinking about turning on the Trace Flag 8295 but I'm wondering if it's gonna do anything since I'm on SQL Server 2012 and not 2008?
How can I see the effects of it? I don't see any additional indexes on the change_tracking tables.

select OBJECT_NAME(ind.Id),OBJECT_NAME(i.parent_object_id),ind.*
from sys.sysindexes ind
join sys.internal_tables i on i.object_id=ind.id
where i.name like 'change_tracking%'


texts: Would daily/weekly db memory cache clearing be of use?

texts:
So memory use is hitting limits. Would clearing the database memory cache on a daily/weekly schedule be of use?

(Ignoring the fact I probably should be looking at reducing size of database/indexes)

Background:

Bunch of SQL Server database (all on one SQL Server instance), one server, and some memory.
Tons of data is written to database, but most of this is just relevant to the current day, its a monitoring system thing. So over a couple of days, the cache will be eaten up by info of the previous day, which may or may not be needed/viewed by anyone for some time, or ever.

So as it writes data, and checks what's in database, it compares against data of the current day. Once the day rolls over, it not comparing against that data any more.

So my thinking is that by clearing the memory at the end of the day, the memory use in the next day will only be relevant of what needed?
Would this be suitable case of FreeSystemCache/DropCleanBuffers

And yes - long term would be better to look a reducing size of database/indexes, however that also falls into what the data being stored is for, which actually makes archiving of data somewhat tricky without some interlinked program logic.


texts:
I'm working on a geo-replicated web platform, composed of an ecosystem of microservices.
We need to improve and rework the user activity tracing pipeline and I'm looking for "the best" database to achieve that.
Our platform relies entirely on kubernetes, for that reason we would exclude every technology that is not compatible with this approach.
Each log is quite simple and potentially made of the following data:

timestamp
user_id
action_type
description
some metadata, their format can be adapted to choice of the database (json, key-value, and so on)

Goals

high available write operation
excellent write-scale capability
excellent storage scalability

Plus

geo-replication
cloud native
hot-warm architecture/some kind of rotation

Non goals

complex data aggregation
complex search query
batch processing

Based on my knowledge, researches and experience, good candidates would be:

cassandra: should satisfy all the goal and geo-replication
cockroach: I've never used it before, but based on the documentation all the goals should be satisfied + geo-replication and is cloud native
influxDB: Not sure about that, I've been using influxdb for a while and though it should satisfy all the goals and all the plus, maybe is not the best choice for this kind of data

what I would not choose:

elasticsearch: it does a lot of things I don't need, is tricky to be maintained and set up, is very resource-consuming
mongodb: the write scalability can be achieved only with sharding, this configuration is hard to be maintained and evolved, the shard key is tricky to be changed. Not fully HA due to the master-slave election mechanism
all the classic SQL with a single master

UPDATE:
good candidate for functionality but is a monster (and I don't know how it works with kubernetes)

HDFS


texts: Constraint to enforce "at least one" or "exactly one" in a database

texts:
Say we have users and each user can have multiple email addresses

CREATE TABLE emails (
    user_id integer,
    email_address text,
    is_active boolean
)

Some sample rows

user_id | email_address | is_active
1       | foo@bar.com   | t
1       | baz@bar.com   | f
1       | bar@foo.com   | f
2       | ccc@ddd.com   | t

I want to enforce a constraint that every user has exactly one active address. How can I do this in Postgres? I could do this:

CREATE UNIQUE INDEX "user_email" ON emails(user_id) WHERE is_active=true;

Which would protect against a user having more than one active address, but wouldn't, I believe, protect against all of their addresses being set to false.

If possible I'd prefer to avoid a trigger or a pl/pgsql script, as we don't currently have any of those & it would be difficult to set up. But I would appreciate knowing "the only way to do this is with a trigger or pl/pgsql", if that is the case.


texts: Cannot drop primary key or add primary key

texts:
This db2 query:

alter table mylib.mytable add primary key(field1, field2, field3)

returns

  [SQL0624] Table MYTABLE in MYLIB already has a primary key'.

However

alter table mylib.mytable drop primary key

returns:

  [SQL0539] Table MYTABLEin MYLIB does not have a primary or unique key'.

How can I remove the existing primary key and create the new one?

The file was created in green screen, not by executing a SQL CREATE TABLE. The database version is V7R2.


texts: Insert data into oracle table with loop?

texts:
How to use sql to insert line detail data into header with the output like below.

line detail
MO_ID  SEQ_NO  QTY   LEVEL
121M   2        3
121M   3        9
125M   2        6
125M   3        7
125M   4        1

Header
MO_ID  LEVEL SEQ_NO   QTY SERIAL_NO
121M    T      1           101
121M    T      1           102
125M    T      1           201
125M    T      1           202
125M    T      1           203

Insert line detail into header so output will be:

MO_ID  LEVEL SEQ_NO   QTY SERIAL_NO   Counter Seq
121M    T      1           101         001     1
121M           2       3               001     2
121M           3       9               001     3
121M    T      1           102         002     1
121M           2       3               002     2
121M           3       9               002     3
125M    T      1           201         003     1
125M           2       6               003     2
125M           3       7               003     3
125M           4       1               003     4
125M    T      1           202         004     1
125M           2       6               004     2
125M           3       7               004     3
125M           4       1               004     4

... and so on until the last serial is reached in header


texts: Primary node becomes secondary before application is deployed and moved into AAG

texts:
We have a small database that was created on a primary node. But by the time the application was deployed and it was time to move the db into the AG I noticed that it became the secondary node.  For client connectivity I gave the listener name. The application has been installed successfully. Is is still possible to move the database into the AG (because the former primary is now the secondary)

Please advice


texts:
I'm with a simple task to migrate databases.

I'm doing the backup in the primary database, setting it to OFFLINE, and then, migrating it to the secondary.

No problem at all.

but I notice something strange.

I check if someone is using the database with sp_WhoIsActive, and it shows nothing.

Then I use alter database X set offline.

Then it doesn't change it at all, and the query keeps running. Then I check again to see if something is using the database, and for my surprise, it shows something running for 22hrs, 20hrs, or 44min.

Why doesn't sp_WhoIsActive show it?

Example:

1st run:

Then Alter etc etc set offline. Then it locks, and I check again with
sp_WhoIsActive

Bam. Something running.


texts: How to deal with datetime ranges in a table with separate columns for date and time?

texts:
In a table that stores events, the date and time are two separate columns:

CREATE TABLE events (
    pk int serial,
    detail text,
    ev_date date,
    ev time without time zone
);

If I filter for dates:

ev_date BETWEEN start_date::date AND end_date::date AND
ev_time BETWEEN start_date::time AND end_date::time

I am missing all events within the two dates that occurred outside the time range of every day.

Thus, the start time is only relevant for the start date and the same for the end date.

Anyone with an advice on how to do it efficiently for millions of events?


texts: Missing Polybase enable configuration on SQL Server 2017

texts:
I installed SQL Server 2017 Enterprise (14.0.3162.1) on my box. With the polybase (Scale-out Group). But when I try to enable through:

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE WITH OVERRIDE;

It shows:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0]
The configuration option 'polybase enabled' does not exist, or it may be an advanced option.

So I used:

sp_configure 'show advanced option', 1;
GO
RECONFIGURE
Go

and check by:

select * from sys.configurations where name like '%poly%'

only:

polybase network encryption
allow polybase export

shows up.

How can I enable this feature in this case?

Please let me know if I missed any.


texts: Creating new database and insert values in Oracle 10g xe hr account?

texts:
I am new to Oracle 10g xe. When I log into hr account, how do I start creating new database and insert values? My case study is a five aside football league, where the organizer wants to know following questions:

alphabetical list of all the teams and their players.
how many time players have been awarded player of the match?
list different types of penalty points which can be awarded?
produce a list of all teams playing all other teams indicating who is at home and who is away.

and lot more.

How do I start creating a new database from scratch?


texts:
We have a server with 8 CPUS across 2 NUMAs with hyperthreading enabled. Currently Maxdop is set to 8, but actually should be set to 4 as per the Maxdop section of this article:

https://support.microsoft.com/en-us/kb/322385

So we need to change it to 4.

However my question is what is the impact of having maxdop set to 8? So going parallel across two NUMAS ? The reason I ask is that we just had a strange issue where queries were very slow to return and PLE dropped off rapidly.

Even when nothing was running against SQL PLE didn't not improve. CXPACKET wait types went up. Then all of a sudden CXPACKET wait type dropped completely and PLE began to rise and now has returned to normal.

Throughout this time small queries were being executed against a database, however it was not the case that one query finished causing the CXPACKET wait type to drop off and PLE to rise again - we don't know what causes that.

A possible explanation is the incorrect MAXDOP setting.

Can anyone explain to me the impact of parallel executions across NUMA nodes, is it just the same as exhausting worker threads and slower access time when using foreign memory ?

Thank you


texts: Total number of read/writes in a day in MySQL

texts: Aside from using standard count queries with a date, is there a global way to get the number of writes (or reads for that matter) in a 24hr period on a table, or an entire schema?


texts: How to efficiently hit an index in a query that uses IN (mysql)

texts:
I have a table like this where people can subscribe to certain feeds and the query fetches from the subscription.

Here is a sample table:

+--------+-----------------+-------------------+---------------------+-----------+
| by     | subscription_id | subscription_type | feed_created_time   | content   |
+--------+-----------------+-------------------+---------------------+-----------+
| Peter  |              12 |                 1 | 2012-12-30 10:00:00 | asdf      |
| George |              34 |                 2 | 2012-12-21 00:19:00 | qwerty    |
+--------+-----------------+-------------------+---------------------+-----------+

and its full detail

CREATE TABLE `table` (
  `by` varchar(20) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `subscription_type` int(11) NOT NULL,
  `feed_created_time` datetime NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`subscription_id`,`subscription_type`,`feed_created_time`),
  KEY `subscription` (`subscription_id`,`subscription_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I've created the primary key like so to create a cluster which would allow most likely types of pulls to be most efficient. So the "next applicable" is immediately following.

And here is how I'm making the query right now:

SELECT * FROM table
WHERE subscription_id IN (12,34) AND subscription_type=1
ORDER BY feed_created_time DESC;

I would also like to make a more complicated queries like...

SELECT * FROM table
WHERE (subscription_id IN (12,34) AND subscription_type=1 )
   OR (subscription_id IN (34) AND subscription_type=2 )
   ...
ORDER BY feed_created_time DESC;

It works fine, but I noticed that when I do an explain, it's unable to use any indexes. Or, it at least reports that it hasn't chosen to use any indexes despite the primary and subscription key being available. It just creates a temporary table and sorts them.

It has no problem selecting keys when making queries like subscription_id = 12 AND subscription_type=1 but the IN in the query seems to make all keys unusable. How can I make the queries use the indexes?


texts:
In the following reference, there is an algorithm outlining a transaction recovery algorithm using redo and undo. The algorithm is as follows:

UNDO and REDO: lists of transactions

UNDO = all transactions running at the last checkpoint
REDO = empty

For each entry in the log, starting at the last checkpoint
  If a BEGIN TRANSACTION entry is found for T
    Add T to UNDO
  If a COMMIT entry is found for T
    Move T from UNDO to REDO

It then gives the example:

but this is clearly wrong?? Since T2 has been committed, T must be in the REDO list. Am I missing something?


texts: Which Index for WHERE UPPER("my_col"::text) LIKE "FOO" search on bytea

texts:
I use the unusual bytea column type in a PostgreSQL DB because the data in this column is like a blob. I mostly contains text, but sometimes it contains binary data.

Which index would fit if the query like this gets done?

WHERE (UPPER("my_bytea_col"::text) LIKE '%FOO%'


texts: Send data from SQL to a serial port or IP address

texts:
I've been asked to integrate SQL transaction data with a video monitoring system in real-time. The video system's application is only capable of receiving data by listening on a serial port or a specific IP address and port.

Is it possible to write/send data from SQL to a serial port or IP? How?

The transaction data is stored in a Microsoft SQL Server 2005 database.

I can't find anything around this on Google or this site. Every search returns how to pick up data from serial and write to SQL dbs, not the other way around.

I may be going around this the wrong way - any related relevant ideas are welcomed.


texts: How to speed up the database encryption / decryption process in Oracle 11 Standard Edition?

texts:
I have an 11-year old huge database designed in Oracle 11g Standard Edition. It requires to be encrypted for maximum security.
We have used dbms_crypto library to perform the encryption & decryption but it is consuming a lot of time decrypt & display.
Our customers need to wait in ques for hours till our public-facing systems & applications can decrypt the 'data-at-rest' & 'data-in-transit'.

We are not going for the Oracle updated & Enterprise edition. So, any help & inputs for our current situation are extremely welcome!


texts: Why are global temporary tables truncated when the connection is closed?

texts:
I'm a relatively new Oracle database developer coming from SQL Server.

I noticed that global temporary tables (created with ON COMMIT PRESERVE ROWS) lose their contents when the session times out, but the tables themselves still exist and have to be manually dropped before rerunning whatever script I have that creates them.

In SQL Server, a temporary table (which isn't global in any sense) is dropped and completely gone when the session is closed.

What is the logical or technical reason for Oracle keeping temporary tables around - but truncating them - when the session is closed?

(If there is a database or server option to avoid this behavior, it is irrelevant to my use-case since I'm only a developer and will be creating scripts that are run on an outside database.)


texts: Oracle Fine-Grained Access (FGA) auditing excluding one source

texts:
I want to audit DML statements coming from other places than application user "tomcat" (OS_USER attribute). I want to focus only on developers or support team changing data in any way.
DML Trigger doesn't log exact query which was executed, just query type (INSERT, UPDATE, DELETE).
With straightforward usage of FGA I cannot filter out application user "tomcat". I am familiar with those questions:
Oracle auditing on specific user activity
Can this be accomplished with Oracles Fine Grained Auditing?
but information is logged in trace files, viewing which requires more effort, and I would like auditing to be accessed by dictionary view. Is it possible?


texts: Compact Large Objects in Reorganize Index task

texts:
I'm setting up a maintenance plan in SQL Server 2008 R2.

On the Reorganize Index Task it has an option for "Compact Large Objects".  What does that mean and do I want to turn it on?


texts: How do I run a database heavy query over jdbc without timing out (Oracle)?

texts:
For one of our applications (Pentaho Data Integration), we need to run queries through the oracle jdbc. Some of these queries are computation expensive and can take an hour or more to return any rows to the client. When I try to run one of the long running queries with default jdbc parameters, I inevitably get: "IO Error: Socket read timed out" after ~30 minutes even though the database is still processing the query (verified in V$SESSION and V$SQLAREA). I am using the newest ojdbc8.jar direct from Oracle and running Oracle Enterprise 12.1.0.2.0.

EDIT:
The output of these queries is being stored in a separate database. The nature of our environment is such that we have an arbitrary number of queries that could be created/run so using stored procedures would be cumbersome.


texts: SQL table design for primary keys (best practice)

texts:
I have a User table that is made up of the the following columns:

ID (Auto-generated GUID)
UserName
Password
FirstName
LastName
Email
AddressID (GUID)

This is linked to the table Address (in a one-to-one [1-1] relationship) which contains the following columns

ID (auto-generated GUID)
Street
Other address fields

That seems to be the logical way of doing this (for me). But someone told me it may contain redundant column (AddressID) and that I should use EntityID instead of Address.ID.

There would be one less column, but the Address table would have its own ID column.

Which practice do you recommend? Is there one faster at execution? What's best in the context of parent/child relations? Is there an impact on performances?


texts: StartsWith query using LIKE is returning the wrong results on a non ascii column

texts:
I want to query a table for entries that begin with a specific fragment of text.

I am under the impression that the LIKE operator is a way to do this, so if I wanted to find all rows that have a Column2 that starts with "a" then I would do that with a query such as the following

SELECT Column1, Column2, Column3
FROM Table1
WHERE Column2 LIKE 'a%';

I have found that using a query like this returns unexpected results.

Here is an example:

CREATE DATABASE TestDB;
GO

USE TestDB;
GO

CREATE TABLE TestTable
(
    Id INT NOT NULL,
    Romanian NVARCHAR(MAX) COLLATE SQL_Romanian_CP1250_CI_AS NOT NULL,
    English  NVARCHAR(MAX) NOT NULL
);
GO

SET NOCOUNT ON;

INSERT TestTable SELECT 1, N'da', 'yes';
INSERT TestTable SELECT 2, N'ani', 'years';
INSERT TestTable SELECT 3, N'a avea', 'to have';
INSERT TestTable SELECT 4, N'a fi', 'to be';
INSERT TestTable SELECT 5, N'șase', 'six';
INSERT TestTable SELECT 6, N'șapte', 'seven';
INSERT TestTable SELECT 7, N'opt', 'eight';
INSERT TestTable SELECT 8, N'zece', 'ten';
INSERT TestTable SELECT 9, N'nu', 'no';
GO

SELECT *
FROM TestTable
WHERE Romanian LIKE 'a%';

USE master;
GO

DROP DATABASE TestDb;
GO

I was expecting this to return the following

+----+-------------+----------+
| Id | Romanian    | English  |
+----+-------------+----------+
| 2  | ani         | years    |
+----+-------------+----------+
| 3  | a avea      | to have  |
+----+-------------+----------+
| 4  | a fi        | to be    |
+----+-------------+----------+

but it returned this instead

+----+-------------+----------+
| Id | Romanian    | English  |
+----+-------------+----------+
| 2  | ani         | years    |
+----+-------------+----------+
| 3  | a avea      | to have  |
+----+-------------+----------+
| 4  | a fi        | to be    |
+----+-------------+----------+
| 5  | șase        | six      |
+----+-------------+----------+
| 6  | șapte       | seven    |
+----+-------------+----------+

The version number I have is 13.0.1601.5


texts: Quorum for 3 nodes AlwaysOn Availability Group

texts: If I am going to set an Alwayson Availability group with 3 nodes, with read-only routing. Do I need to configure a Quorum ?


texts:
I'm new with Cassandra, and even more - I never have such task for habitual RDMS systems, like MariaDB or Oracle.

So, how can I store file in to Cassandra's table? Preferably using Python-driver, but few examples with raw CQL (cqlengine?) also appreciated.

We have large amount of small files, less then 10MB, with currently stored in Cassandra, and I'd like to understood how it's can be done.

For example - using next table:

cqlsh:testkeyspace> desc table files_uuids;

CREATE TABLE files_uuids (
  id uuid,
  file blob,
  PRIMARY KEY ((id))
) WITH
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.100000 AND
  gc_grace_seconds=864000 AND
  index_interval=128 AND
  read_repair_chance=0.000000 AND
  replicate_on_write='true' AND
  populate_io_cache_on_flush='false' AND
  default_time_to_live=0 AND
  speculative_retry='99.0PERCENTILE' AND
  memtable_flush_period_in_ms=0 AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'LZ4Compressor'};


texts: Galera Replication error: log block numbers mismatch

texts:
I am attempting to get Galera replication working between two nodes.

I am finding these errors in my innobackup.backup.log file:

  xtrabackup: error: log block numbers mismatch:
  xtrabackup: error: expected log block no. 671400745, but got no. 679592737 from the log file.
  xtrabackup: error: it looks like InnoDB log has wrapped around before xtrabackup could process all records due to either log copying being too slow, or  log files being too small.
  xtrabackup: Error: xtrabackup_copy_logfile() failed.

I am not sure where to do from here, I have googled a bit and didn't find anything that seemed like it could help me in my particular instance.

Any advice will be most appreciated.


texts: SELECT records that contains just ENGLISH A - Z 1 -9 charachters

texts:
I have a non English language website. Recently someone published a lot of English spammy topics.

I need a SQL statement to select topics that contains only English language.

Knowing that some of our original (not spammy) website topics contains some phrases in English we do not want to bring those for us.

I tried:

SELECT * FROM `posts` WHERE `title` NOT LIKE '[^\x20-\x7E]'

The result was all posts.

For example:

"Where are you from?"    ---> Only English NOT ALLOWED
"من اين انت؟"                     ---> Only Arabic ALLOWED
"مامعنى كلمة where بالانكليزي"  --> English & Arabic ALLOWED

We just need to browse which like the no. 1 without results like no. 3


texts: T-SQL OUTPUT DELETED columns by information schema

texts:
I have two tables:

Whenewher I'm removing some records from TableFirst I need to insert deleted record data into TableSecond. But they have different number of columns.

Is it possible to output deleted values of common columns using information schema(except Id of course) in delete statement?

DELETE FROM TableFirst
OUTPUT DELETED.* INTO TableSecond
WHERE Id = 1

I'm trying to avoid naming each column (I'll have up to 50 columns in my project).


texts: mysql - is it possible to create a partition expression based on two keys / indexes?

texts:
if i have a table with, lets say 500 million rows, and among others there are two main individual indexes on that table. the table looks like:

CREATE TABLE `t1` (
    id_1 bigint unsigned not null,
    id_2 bigint unsigned not null,
    col3, col4 ... colN ...
    KEY `index1` (`id_1`),
    KEY `index2` (`id_2`),
    ...
) ENGINE=InnoDB;

100% of the queries on the table are using WHERE clause on index1 OR index2.

Taking this into account, and assuming it is now very reasonable time to partition that table, into 100 partitions, I would like to ask your help to understand these issues:

Is it possible to create a partition expression in such a way that after the partitioning, for any query that using one of these indexes:

SELECT * FROM `t1` WHERE id_1 = 123;
-- or
SELECT * FROM `t1` WHERE id_2 = 456;

the engine will scan one partition only?
issue 1 must happen without changing the indexes. I mean, without making back references in them, because it will be too much RAM consuming. So, this must not happen:

KEY `index1` (`id_1`,`id_2`),
KEY `index1` (`id_2`,`id_1`),

Am I thinking in a wrong direction? How would you solve this issue with Amazon RDS 7.5 GB RAM instance ?

feel free to edit this question, to make it more possible to be answered, thanks

reference to stackoverflow.com


texts: DB2 will not INSERT into Created Temp Table that I created

texts:
I normally use SQL Server but for this project I'm having to learn DB2.  If I can get the below code to work I'm set for the whole project.  Below is a elementary example of what I cannot get to work.  All I want to do is make a Created Global Temp Table (CGTT) and append data to it.  The same code below will work if I use a Declared Global Temp Table, but for my purposes I must have a CGTT.

CREATE GLOBAL TEMPORARY TABLE TEST_TBL(
    KEY_ID BIGINT,
    SOMETEXT VARCHAR(10)
);

INSERT INTO USERID.TEST_TBL
VALUES(123456,'TEST TEST');

SELECT * FROM USERID.TEST_TBL;
SELECT COUNT(*) FROM USERID.TEST_TBL;

The above executes fine with no errors.  When the insert is executed it even tells me "1 row updated".  However, the select * and count(*) both give me 0 results;  I'm wondering if this is a permissions issue, but can I have permissions to create a CGTT but not insert to it??

Any help provided would be most appreciated.
Thanks in advance.


texts: Restoring a MySQL database to a failed master

texts:
I have a master-master configuration in MySQL with two servers. One server should stay live on the network to serve requests (call it server A) and the other should be taken offline to push new code changes (server B).

My idea originally was that after running STOP SLAVE on both servers, that server B could be shut down, updated, and even have a new database schema put in. After this, I thought that I could simply START SLAVE on server B and have the entire database from server A replicated/mirrored back over to server B. However, this is not the case. Restarting the slave and doing a CHANGE MASTER TO (...) and syncing up the log files does not replicate old changes like I want it to: it only replicates new writes from that point on.

I am looking for a way to bring server B up to speed with the latest database from server A, and then have server B continue to replicate changes in a master-master setup. Then I can continue the sequence of server upgrades by doing the same process but keeping server B online only.

Any solutions which require locking the tables will not work since I need to do this change without any downtime. Is this possible?


texts:
I have two databases that are mirrored to another server using database mirroring.

The mirror server has to be down for some reason for few days.

Now, the production server is having principal databases in (PRINCIPAL/DISCONNECTED) State.

Clients can access those databases.

Questions

So what happens when they keep on adding data to these databases??
Will the data get committed or waits till the mirror comes up?


texts: Can I query Microsoft GP database and save the result as MDF file?

texts:
I have a request as below for auditing purpose on Microsoft Dynamic GP database.

Database files are required as .mdf and .ldf files for certain companies
Only need the certain period time of data, not entire database from day 1 till now

My questions:

Is this even possible to provide only certain period time of activities from database as .mdf file? I mean if the .mdf file is required, it must be the entire database right?
If it is possible, how? Query the database for everything happened in that period of time and save it as .mdf file and .ldf file? Or somehow somewhat cut the .mdf file? I don't think so...
or if I provide entire database with only that period of transaction log?

I posted this question on overflow, It should be posted here.


texts: How to get the best write throughput during a MySQL import with Amazon RDS?

texts:
tl;dr - How can we get the maximum EBS throughput for a db.r3.2xlarge instance? We're only seeing about half of what it can supposedly do.

Apologies for the long length of this question. We've had quite a lot of back and forth on this, and I want to give as much information as possible.

We have a MySQL database that contains 3 large tables, ~300GB in total. We're looking to move these tables from a server in our office to an AWS RDS instance.

The data itself is not production data. It's used internally only and is more data warehouse kind of stuff, so we don't need to worry about keeping things in sync, etc. As such, we're taking the mysqldump and mysql ... < dump.sql approach.

We're not having issues with the actual import per se, our issue is that it's going slower than we'd expect. We've eliminated obvious factors, i.e. we're importing from an EC2 instance that contains the SQL dumps, bin log is disabled, auto-commit is off, etc, so we'd like to understand why this is.

We initially started with a db.r3.xlarge instance with 500GB of storage and 5000 provisioned IOPS. During the import, we saw write IOPS hovering around the ~2800/s mark, and EBS write throughput hovering ~58MB/s. Network was barely at ~1.3MB/s and CPU was ~13%.

As I come from a developer background, my infrastructure knowledge is OK but limited, so I wanted to understand what was the bottle neck. Based on the numbers I was seeing, I assumed it was disk IO, and after doing some research I believed this page backed up that theory.

The db.r3.xlarge comes with 500Mb/s dedicated EBS throughput, which works out to be 62.5MB/s, matching roughly the number we were seeing. So to get the throughput to increase, we assumed upgrading to the db.r3.2xlarge with its 1000Mb/s dedicated EBS throughput would give us write throughput of ~125MB/s.

This is where the confusion starts. We provisioned a db.r3.2xlarge instance with 800GB of storage and 8000 provisioned IOPS, but only saw an EBS write throughput of ~63MB/s, sometimes hitting ~70MB/s. Write IOPS were now ~3400/s and CPU was ~5%.

Based on this, my only assumption is that disk IO is now not the bottle neck, and something else is throttling the imports. Is this assumption correct, or is there something else going on re: EBS throughput we're not understanding?

We also triggered two separate table imports on two separate MySQL connections, and although the CPU and EBS write throughput, etc, went up, it only went up by a factor of ~5%.

As stated before, the import is happening fine, it just takes a while. We just want to understand why we're seeing the numbers we are seeing.

Thanks for your time.

Edit 1: After doing some more reading, I'm further inclined to believe that our import is being throttled by something. Our queue length never goes above 10 and hovers ~8. From what I've read, this means we're not generating enough IO to fully utilise what we have.


texts: Redefining a table with a user-defined type using DBMS_REDEFINITION

texts:
The application which we developed has one schema, USER1, which has tables, types, packages etc.
I have to move some of the user-defined types to a different schema, The reason for doing this is to enable to editions for user1 to suppport online patching of the application.
USER2. USER1 has few tables which have a dependency on these user-defined types. We recreated the types in USER2, but before I drop the types in USER1, we have to remove the table's dependency on them. To do this, I am thinking of using DBMS_REDEFINITION by creating an intermediate table using the user defined type in USER2 (instead of USER1)

I'm using the following to cast the user defined type in USER1 and to the new one created in USER2,

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE( 'user1', 'department', 'department_in'
         , 'dept_id dept_id, dept_name dept_name
         , cast(emp as user2.employee_t) emp'
         , dbms_redefinition.cons_use_rowid);
END;

But, I'm getting the following error:

  Error : ORA-22907: invalid CAST to a type that is not a nested table or VARRAY

Below are the details:

User1 has the following objects:

CREATE TYPE employee_t AS OBJECT
(employee_id NUMBER
,employee_name VARCHAR2(30)
,salary NUMBER
,dept_id NUMBER);

CREATE TABLE department
(emp employee_t
,mgr varchar2(40)
,dept_id number
,dept_name varchar2(30));

When we try to enable editions for User1, we get, we use the force option table will be come invalid.

  ERROR at line 1:
  ORA-38819: user USER1 owns one or more objects whose type is editionable and
  that have noneditioned dependent objects

ALTER TYPE RESET for the type gives following error, when there are dependent tables.

  ERROR at line 1:
  ORA-22374: cannot reset the version of a type with table dependents

I tried the following,
a. Recreated the type in User2;

CREATE TYPE employee_t AS OBJECT
(employee_id NUMBER
,employee_name VARCHAR2(30)
,salary NUMBER
,dept_id NUMBER);

b. Created a temp table in user1 using the type in user2

CREATE TABLE department_tmp
(emp user2.employee_t
,mgr varchar2(40)
,dept_id number
,dept_name varchar2(30));

c. Migrated the data using the script:

begin
for cr in (select * from department)
loop
dbms_output.put_line(cr.emp.employee_name);
insert into department_tmp values (user2.employee_t(cr.emp.employee_id,cr.emp.employee_name,cr.emp.salary,cr.emp.dept_id),cr.mgr,cr.dept_id,cr.dept_name);
commit;
end loop;
end;
/

d. dropped the type employee_t and table department in user1
e.renamed the table departmemt_tmp to department
f. enabled editions user1, edition was enabled successfully.

Here I have taken a simple case, there are many such tables in our application (some with nest tables also), the above approach will not be easy. Hence I was exploring the possibility of using DBMS_REDEFINITION.

Please let me know if there is any way to achieve this.


texts: Stored Procedure for Vacuum in PostgreSQL

texts:
I want to create a stored procedure to trigger vacuum, I know there are many ways to automate vacuum, but I would like to use the stored procedure (Postgresql 9.6).

The problem Im facing is, I can't run the vacuum inside the BEGIN/END so there is a workaround, we can use set autocommit=on.

But when I add this autocommit inside the stored procedure, its throwing error.

Sample SP:

create procedure test(table_list varchar(1000))
LANGUAGE plpgsql
AS
$$
DECLARE
*** some values ***
BEGIN
EXECUTE 'set autocommit=on';
*** Rest of the command to get the table names one by one and vacuum in a loop***
END;
$$

Is this possible? Or any alternate way?


texts: How to remove wildcard character from field

texts:
I have fields that contain the character *. I've been trying to remove them but my commands fail with an error...

Example:

UPDATE name SET name = REGEXP_REPLACE (name, '*', '') WHERE category_id = 168;

I'm also struggling to remove other characters like |


texts: Creating a dedicated HD parition at beginning of disk for data files, benefits and draw backs?

texts:
I am thinking of creating a dedicated partition on the harddisk for the mysql database data files in /var/lib/mysql , while considering those 2 points:

a dedicated partition will avoid any file fragmentation that might occur, even if fragmentation occurred it would be very limited.
benefit from the faster throughput and lower latency at the beginning of the mechanical harddisk.

What are the (other) benefits and possible drawbacks of such system ?

Edit:

More details about my situation:

I have 1 harddisk, actually it is 2 harddisks but in RAID1

It will contain: the database, the webserver, the os and other services.

Os will be Debian 6/7. Filesystem will be EXT3/EXT4


texts: Is it acceptable to use deadlocks as a strategy?

texts:
We recently upgraded our SQL production environment from 2008R2 to 2017. I am finally at a point where I want to try optimistic locking in the form of using snapshot isolation. I read Brent Ozar's article suggesting to SET ALLOW_SNAPSHOT_ISOLATION ON at the database level, and then alter queries to take advantage of the new to us feature. To my surprise, my manager who is very knowledgeable in sql, did not want to move in this direction. I understood him to say that we depended on the deadlocks to prevent us from reading dirty data, and saw no reason to solve a problem that did not exist. I did not know how to respond to him. I have never considered deadlocks as anything other than something to try and eliminate, not depend on as a way of life. I get that they do an important function, but are a sign of something that can be improved.

My question is how many others rely on deadlocks to such an extant that you would not consider trying to eliminate them?


texts: CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server?

texts:
We are beginning to provision a set of physical servers for a virtual cluster of SQL Server 2016 nodes within VMware. We will be utilizing Enterprise Edition licenses.

We plan on setting up 6 nodes, but there is a bit of a debate on what the ideal way to provision the physical servers with regards to CPU clock speed versus CPU core count.

I know this is largely dependent on transaction volume and number of databases stored among other software-specific factors, but is there a general rule of thumb that is advised?

For instance, is a dual 8-core, 3.2 GHz physical server (16 cores) more preferential to a dual 16-core, 2.6 GHz server (32 cores)?

Has anyone come across a white paper that further delves into this type of topic?


texts: MongoDB can't add new replica set member [rsSync] SEVERE: Got signal: 6

texts:
I have a replica set and I added a new member to the set. The initialSync begins for the new  member and rs.status (on primary) shows STARTUP2 as status. However, after a long enough time, there's a cryptic fassert error coming on the new instance.

Log dump is as following:

2014-11-02T22:53:23.995+0000 [clientcursormon] mem (MB) res:330 virt:45842
2014-11-02T22:53:23.995+0000 [clientcursormon]  mapped (incl journal view):45038
2014-11-02T22:53:23.995+0000 [clientcursormon]  connections:27
2014-11-02T22:53:23.995+0000 [clientcursormon]  replication threads:32
2014-11-02T22:53:25.427+0000 [conn2012] end connection xx.xx.xx.xx:1201 (26 connections now open)
2014-11-02T22:53:25.433+0000 [initandlisten] connection accepted from xx.xx.xx.xx:1200 #2014 (27 connections now open)
2014-11-02T22:53:25.436+0000 [conn2014]  authenticate db: local { authenticate: 1, nonce: "xxx", user: "__system", key: "xxx" }
2014-11-02T22:53:26.775+0000 [initandlisten] connection accepted from xx.xx.xx.xx:1058 #2015 (28 connections now open)
2014-11-02T22:53:26.864+0000 [conn1993] end connection xx.xx.xx.xx:1059 (27 connections now open)
2014-11-02T22:53:29.090+0000 [rsSync] Socket recv() errno:110 Connection timed out xx.xx.xx.xx:27017
2014-11-02T22:53:29.096+0000 [rsSync] SocketException: remote: xx.xx.xx.xx:27017 error: 9001 socket exception [RECV_ERROR] server [168.63.252.61:27017]
2014-11-02T22:53:29.099+0000 [rsSync] DBClientCursor::init call() failed
2014-11-02T22:53:29.307+0000 [rsSync] replSet initial sync exception: 13386 socket error for mapping query 0 attempts remaining
2014-11-02T22:53:36.113+0000 [conn2013] end connection xx.xx.xx.xx:1056 (26 connections now open)
2014-11-02T22:53:36.153+0000 [initandlisten] connection accepted from xx.xx.xx.xx:1137 #2016 (27 connections now open)
2014-11-02T22:53:36.154+0000 [conn2016]  authenticate db: local { authenticate: 1, nonce: "xxx", user: "__system", key: "xxx" }
2014-11-02T22:53:55.541+0000 [conn2014] end connection xx.xx.xx.xx:1200 (26 connections now open)
2014-11-02T22:53:55.578+0000 [initandlisten] connection accepted from xx.xx.xx.xx:1201 #2017 (27 connections now open)
2014-11-02T22:53:55.580+0000 [conn2017]  authenticate db: local { authenticate: 1, nonce: "xxx", user: "__system", key: "xxx" }
2014-11-02T22:53:56.861+0000 [conn2015]  authenticate db: admin { authenticate: 1, user: "root", nonce: "xxx", key: "xxx" }
2014-11-02T22:53:59.310+0000 [rsSync] Fatal Assertion 16233
2014-11-02T22:53:59.723+0000 [rsSync] 0x11c0e91 0x1163109 0x114576d 0xe84c1f 0xea770e 0xea7800 0xea7af8 0x1205829 0x7ff728cf8e9a 0x7ff72800b3fd
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0x11c0e91]
 /usr/bin/mongod(_ZN5mongo10logContextEPKc+0x159) [0x1163109]
 /usr/bin/mongod(_ZN5mongo13fassertFailedEi+0xcd) [0x114576d]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl17syncDoInitialSyncEv+0x6f) [0xe84c1f]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl11_syncThreadEv+0x18e) [0xea770e]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl10syncThreadEv+0x30) [0xea7800]
 /usr/bin/mongod(_ZN5mongo15startSyncThreadEv+0xa8) [0xea7af8]
 /usr/bin/mongod() [0x1205829]
 /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a) [0x7ff728cf8e9a]
 /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7ff72800b3fd]
2014-11-02T22:53:59.723+0000 [rsSync]

***aborting after fassert() failure

2014-11-02T22:53:59.728+0000 [rsSync] SEVERE: Got signal: 6 (Aborted)

.

The worst part is that when I try to re-start the mongod service, the replication begins afresh trying to reSync all the files which are already there. This seems bizarre and useless.

Can someone please help me understand what is going on?


texts: Data sharing between client and server two ways

texts:
One of my Silverlight applications is running. This Application is using one central database (SQL Server 2008) and three other client databases (SQL Server 2008) in server other locations.

Our requirement is that with minor changing or without changing the Silverlight application,
when we make changes in the central database (insert, update and delete in multiple tables) these changes automatically occur in all client databases. And when any change is made in any client database all client as well as central database should be updated automatically.

For this I read some about Sync Framework and SQL Server 2008 Change Tracking. But I have no idea about how will I do this. Please help me. How can we achieve this?


texts: how to decide whether table segregation is benificial or not

texts:
I have an input data feed that is significant in size. Presently, I have one table with all entries input in it.

I want to find out how to decide whether splitting the table into smaller tables would be beneficial or just an overhead. I need some insight on how to do this analysis, and if I perform a test for small data how can I scale the results?

The thing with segregation is that query time will surely reduce but at the same time there will be an increase in data input time and memory allocation will increase as well so I don't know whether it's worth it or not.


texts:
High performance mysql book suggests that for sharding a blog application, one may want to put comments data across 2 shards: first, on the shard of a person posting comment, and on the shard where the post is stored.

So this raises the question how to reliably duplicate this data. Which of the following data duplication options across shards is recommended?
Option 1: Make 2 separate inserts from the PHP script.
Pros: a) Logic is in application layer.
Cons: a) User is held for 2 inserts. b) This logic will need to be duplicated in every client trying to insert similar data.
Conclusion: Seems reasonable.

Option 2: Form federated tables and use some trigger to handle the insert of duplicate.
Pros: a) App layer doesn't need to worry about multiple inserts
Cons: a) Every shard need to have federated connection to every other shard; b) Federation will work on machines in LAN, but what about at 2 different sites. c) what if connection to federated server fails.
Conclusion: Doesn't seem like a sound idea.

Option 3: Messaging such as RabbitMQ
Pros: a) Different clients can insert data at one place, and all subscribers can consume the insert.
Cons: a) Complex; b) may impose overhead in order to host messaging server, and clients; c) not sure how will it work with a look-up service to locate appropriate shards
Conclusion: Not sure

Option 4: your suggestion?

I will greatly appreciate your help.


texts: How to chop off X number of characters across multiple records?

texts:
Hello all!
I have a field that contains geographical locations in the city-state format:

Boston, MA
Los Angeles, CA
Helena, MT
...

The field spans for some 230 records. My task is to chop off the four final characters in each record. How do I go about it? Any ideas?
Thank you!


texts:
A simple table creation on one of my MySQL databases takes forever:

mysql> CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (16.58 sec)

The machine is quite idle:

01:21:26 PM       CPU     %user     %nice   %system   %iowait    %steal     %idle
01:21:27 PM       all      0.50      0.00      0.21      0.00      0.00     99.29

Any ideas how to investigate this?

EDIT: Following DTest's advice, this is the execution profile:

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000044 |
| checking permissions | 0.000024 |
| creating table       | 8.668129 |
| After create         | 0.000014 |
| query end            | 0.000005 |
| freeing items        | 0.000028 |
| logging slow query   | 0.000004 |
| logging slow query   | 0.000206 |
| cleaning up          | 0.000006 |
+----------------------+----------+


texts: Master - Master setup on Postgresql 10 + Ubuntu 18.04 + Pgpool II

texts:
Does anybody have experience in configuring Pgpool II with Postgresql 10 on Ubuntu 18.04?

I am trying to setup Master - Master setup on Postgresql 10 + Ubuntu. I am trying to use Pgpool II

I will have two or more mater DB servers running on different IPs and my objective is synced with each other.

I am looking for an open-source solution/s Your thoughts, suggestion and experiences are kindly welcome.
Cheers


texts:
I am a student and I am developing several projects as a part of my academia.

While developing the database for one of the projects, we came across a situation where we thought about whether ERD is necessary or not. Right now, not every one of us are in agreement on developing the ERD first, and then developing database from it.

The majority of people prefers developing the database on the fly verbally according to the system requirementd on paper directly.

Now, I am strict follower of Database principles. I think the database should be developed from the ERD only. So, I just want to know the following:

Is the industry following these principles?
Am I just wasting my time on developing an ERD?
What are the benefits of developing an ERD?


texts:
After creating shards in replica set and also setting up config servers, when I try to add a collection to my shards then I get below error :

  {
    "ok" : 0.0,
    "errmsg" : "ensureIndex failed to create index on primary shard: Cannot accept sharding commands if not started with --shardsvr"
  }

I checked it and have started my shard server with --shardsvr. Still I face the same issue.

Current MongoDB Version : 3.4.6 (In which I am trying to create shards)


texts: MySql Insert signal if where clause fails

texts:
As part of a chained sequence of transactional queries, I'm inserting values that exist in a temporary table into a new table.

Every query is validated for errors in PHP using:

if (!mysqli_query($dbc, ....) {$errors[] = mysqli_error($dbc);}

If an error occurs it gets pushed into the errors array and the entire sequence is not commited.

With this approach I'm having trouble validatiesg queries that DO NOT CAUSE AN ERROR but DO NOT MEET WHERE CLAUSE CONDITIONS

Is there a way to get MySQL to throw an error signal when the WHERE CLAUSE conditions are not met?


texts:
I'm trying to run SQL Server Failover cluster in Azure.

I have NODE1 and NODE2 in a cluster and another SERVER in the same network trying to connect to the database server using the instance from the cluster.

NODE1 can connect to cluster instance when NODE1 is the active node.
NODE2 can connect to cluster instance when NODE2 is the active node.
SERVER can connect to cluster instance if using NODE1 network name and NODE1 is the active node.
SERVER can connect to cluster instance if using NODE2 network name and NODE2 is the active node.

1434 udp and 1433 tcp are on in both nodes. SQL Browser is running, Portqry 1434 returns the SQL instances only if I use the nodes network name but not the cluster network name.

I've googled and tried everything you can imagine (disabled firewall, changed network card loop flags, used different ports, add endpoints in azure for a variety range of ports, etc...)

To make it short... I want to know if there's some sort of restriction in Azure that doesn't allow me to make this work.


texts: Find top by values by percents using range in SQL Server

texts:
What will be easier way to group records by range of percents.

Here what I try to accomplish

Select top percents between 50 and 75 * from myTable order by PKI

Here is way I did this but I have felling that it cloud be much much easier

select
    top(25) percent
     *
from myTable
where id not in (
            select
                top(25) percent
                 ID
            from myTable
            order by PKI
            )
order by PKI

This should be top 50 - 75 percents of my table ?

Is there better (nicer) way to do this.


texts:
i have one dockerized postgres database (The host has 1GB of ram and 2 CPU) with some tables, on a specific table, that it's used to read and write data continuously (every 5 seconds), i have strange issues, as you can see, the ratio between heap_read and heap_hit is quite low:

The table, has not so many data, only 300K rows, and the structure of the table it's the following:

Here it's the index stats:

I mean, the system seems to work fine...but i need to know if i have to do some specific tweaks in order to make it work better and have the  heap_read/heap_hit ratio close enough to 0.99?

Thanks


texts:
I have asked this question on SO before, but did not get a good answer.

I would like to be able to export a MySQL schema to a set of Latex tables.

phpMyAdmin does this, but it is buggy and does not export table comments in LaTeX.

Are there other linux-compatable, open-source options?


texts: Rebuild index error: "The job was stopped prior to completion by User"

texts:
I have a rebuild index job that is set to run every night. The job used to run fine, but last night for some reason the SQL Server Agent Job was stopped. The rebuild index job was running on 9 different databases and it used to take less than 2 minutes, but it stopped all of a sudden last night with an error in the last step.

Here is error message:

  The job was stopped prior to completion by User [User_name]. The job
  was invoked by Schedule 37 (daily).

  Executed as User: User-Name. The step was cancelled (stopped) as the
  result of a stop job request.

I checked on SQL Server logs, there are no error messages. Where can I find exact root cause of job stopped?

Any suggestions?


texts: Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?

texts:
When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user):

postgres=> create database rich1;
CREATE DATABASE
postgres=> \l+
                                                                            List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |          Access privileges          |   Size    | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-------------------------------------+-----------+------------+--------------------------------------------
 postgres  | pgsys    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                                     | 7455 kB   | pg_default | default administrative connection database
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin               | No Access | pg_default |
 rich1     | pgsys    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                                     | 7233 kB   | pg_default |
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin                         | 7345 kB   | pg_default | unmodifiable empty database
                                                             : rdsadmin=CTc/rdsadmin
 template1 | pgsys    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgsys                            | 7345 kB   | pg_default | default template for new databases
                                                             : pgsys=CTc/pgsys
(5 rows)

As you can see, the database is put into the pg_default tablespace, but if I specify the default tablespace in the tablespace clause (also still logged in as pgsys) I get a permission error:

postgres=> create database rich2 tablespace pg_default;
ERROR:  permission denied for tablespace pg_default

Here's the permissions for that user:

postgres=> \du pgsys
               List of roles
 Role name | Attributes  |    Member of
-----------+-------------+-----------------
 pgsys     | Create role | {rds_superuser}
           : Create DB

This is PostgreSQL error, but I should mention that this an AWS Aurora instance in case that makes a difference.


texts: Mariadb: Cannot decrease max_connections to 1, ERROR 1231 (42000): Variable 'max_connections' can't be set to the value of '1'

texts:
For doing updates on databases I reduce the max_connections variable to 1 (the remaining for root access). I found it the fastest solution to lock the database for other users during updating. This worked fine for me till yesterday. And I cannot find any restriction why it could be forbidden to set the value to 1.

within my update script I used the command:

mysql -u root -p -e "SET @@global.max_connections = 1;"

and I get the errot:

ERROR 1231 (42000) at line 1: Variable 'max_connections' can't be set to the value of '1'

I tried to find the minimum number I'm allowed to set:

MariaDB [(none)]> show variables like 'max_conn%';

-> | max_connections    | 151   |

MariaDB [(none)]> set @@global.max_connections=1;

ERROR 1231 (42000): Variable 'max_connections' can't be set to the value of '1'
MariaDB [(none)]> set @@global.max_connections=10;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set @@global.max_connections=9;

ERROR 1231 (42000): Variable 'max_connections' can't be set to the value of '9'

MariaDB [(none)]> show variables like 'max_conn%';

-> | max_connections    | 10    |

And I'm the single user on that database:

show status where `variable_name` = 'Threads_connected';

-> | Threads_connected | 1     |


texts: How to do Oracle database cluster / Failover without shared storage

texts:
I have 2 or 3 RHEL 6 servers and I don't want to have a dedicated storage device. I think maybe you can replicate changes from one node of a cluster to another in real-time.

I want to cluster them so if 1 instance fails it can failover to another instance. Maybe even 2 instances running and a load balancer server.

Is there any solution to this kind of cluster?


texts: Postgresql update a partitioned table, where update affects multiple partitions

texts:
If I have a partitioned table partitioned on PKey column, can I update (or merge etc) the table like below (or more complex jonining to another table) which will cause multiple partitions to be modified?
If yes does it depend on which postgresql version?

PartitionedTable

PKey    SomeOtherValue
1       10
1       11
2       10
2       11
3       11

UPDATE PartitionedTable
    SET SomeOtherValue = SomeOtherValue + 1
WHERE SomeOtherValue > 10


texts: What are the advantages of having a centralised SQL database server for development?

texts:
I have an idea to use for my fellow developers and basically wanted feedback.

The idea is to have a centralised SQL Server database server for all developers to use.

Each developer would have either own database on the server for the specific project (for example, SW_ProjectName).

Therefore if a developer was off for a week and we needed to hit a deadline we could easily hook into their database by changing our local config connection string.

In addition to this their would be a test database (for example ProjectName_Testing) which the developer would use to run test their sprint before deploying to Staging.

Can anybody think of any advantages and disadvantages for this approach instead of having everybody having their own local databases for development.


texts:
I haven't done much with MongoDB. But in trying to understand the data that we do have in MongoDB, it makes sense to start with looking at the DDL in the database. What is the simplest way to pull the DDL out of the MongoDB database. I can log directly onto the Linux server and run commands, plus I have the Robo 3T client.
Thanks


texts:
I have a database that has been locked in transition for over 24 hours.  I found a similar thread that discusses how to find the commands that are running but I do not know how to unlock the database.  How can I resolve this transition state?

Query to get currently running commands:

use _MYDB_
go

select
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    request_sql_text = st.text,
    s.program_name,
    most_recent_sql_text = stc.text
from sys.dm_tran_locks l
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
left join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
left join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
where l.resource_database_id = db_id('_MYDB_')
order by request_session_id;

Results:


texts:
I need limited Global permission like Select Execute and grant privileges  to one user.

how can i give, can any one help me.


texts: Can a temporary table be shared across sessions?

texts:
I have revised a procedure that builds a dynamic SQL statement to create and populate a temporary table. The syntax was something like this:

...'create #temp_' + CAST(GETGUID() AS VARCHAR(36)) ...

I asked a colleague if he knew why a unique identifier was being concatenated to the table name. He told me that in the past there were cases where temporary tables were shared across sessions and the data was messed up.

I know this is not possible since MSDN clearly states:

  You can create local and global temporary tables. Local temporary
  tables are visible only in the current session, and global temporary
  tables are visible to all sessions.

Now (remembering the case) some colleagues think we should use the syntax above as a good practice but I do not like it and need proof there is no such bug or if it was it is already fixed.

Could anyone with experience in SQL Server 2005/2008 tell me if there ever was such case?


texts: When Creating a Foreign Key Relationship

texts:
When creating a primary/foreign key relationship there are the following options:

Check Existing Data On Creation [Yes/No]
Enforce for Replication [Yes/No]
Enforce Foreign Key Constraint [Yes/No]

What are the uses for all these when you select 'Yes' or 'No' ?

Because when deleting the record with the primary key, I have a problem if there are any foreign key relationships, it doesn't let me delete.  So how do you delete all related records to primary key at that same time? or how do you ignore other foreign records when primary is deleted and allow the delete?

I searched online but can't find the answer to my questions.


texts:
This is a bit strange so bear with me please.

After installing a new SQL Server 2008 R2 on a new server (Windows 2012 Standard) just like the production one, I tried to connect my web aplication (Java/JDBC 4.0 Driver) the same way I always do with the production server but this time my application was frozen for at least 15 minutes!

At the moment I thought: "Maybe it's something to do with privileges" but I was wrong because that user is the db_owner and has all the privileges on that database.
So I decided to use the SQL Server Profiler to list all the queries arriving and executing when I login on the application. The usual "select" here and there but then I noticed there was no "update" query trying to set the LastTimeLogin, and at the same time my web application stopped working. I let the time pass so I can see what else was happening on the database. And guess what? Nothing happend for 15 minutes (aprox) :(

After that an exception occurred:

  Exception: Error saving LastLoginTime for account: demo
  org.package.db.DBException: Update record 'demo'
  [com.microsoft.sqlserver.jdbc.SQLServerException: connection timeout]

Then I went back to the web browser and everything was working normally. The application could "insert", "update", "delete", "select".

I have to mention, locally the SQL Server works perfectly with Management Studio and remotely via TSQL (linux console client). The firewall is open for any SQL Server request.

I hope someone can help me.


texts:
it is about one year that I have changed my server from Oracle 10g to 11.2g (and also changed the hardware and Windows from 2003R2 to 2012).

The problem is that with more than doubled cpu power, about 3 times the ram and faster disks, my new server is slower than before!!!
I'm investigating on it but having the standard edition one license I haven't diagnostic pack or something else...

One thing that sounds me bad is that on old server, if I set SGA+PGA memory to 16GB, oracle allocate all 16GB immediatelly and I can check it also in windows task manager.
Now, my memory_target = memory_max_target = 20GB, but in windows task manager oracle doesn't use more than 10GB of ram. How is it possible? Can I force it to use more ram?

This is my pfile

sicor.__db_cache_size=5796528128
sicor.__java_pool_size=58720256
sicor.__large_pool_size=12582912
sicor.__pga_aggregate_target=12377391104
sicor.__sga_target=9097445376
sicor.__shared_io_pool_size=0
sicor.__shared_pool_size=3154116608
sicor.__streams_pool_size=25165824
*.db_block_size=8192
*.distributed_lock_timeout=300
*.job_queue_processes=1000
*.memory_max_target=21474836480
*.memory_target=21474836480
*.open_cursors=1500
*.parallel_automatic_tuning=FALSE
*.parallel_max_servers=80
*.parallel_servers_target=32
*.parallel_threads_per_cpu=2
*.pga_aggregate_target=0
*.processes=1200
*.sessions=1325
*.sga_max_size=12884901888
*.sga_target=0

This is the querty for DB Cache Advice starting from 1

5536____682312__1_______39052864
5984____737528__0,9056__35366617
6528____804576__0,8461__33043015
7072____871624__0,8149__31825392
7616____938672__0,789___30813474
8160____1005720_0,766___29912665
8704____1072768_0,7442__29064260
9248____1139816_0,7219__28193363
9792____1206864_0,6997__27324768
10336___1273912_0,6778__26468501
10880___1340960_0,6558__25610722

So it seems that doubling cache will improve drammatiacally the performance..

How can I set it in order to use at least 24-28GB of ram?
Thanks


texts: FreeTextTable - Query as a set operation vs. having to cursor

texts:
Does anyone know if it is possible to join to a free text table to query for many search terms in one operation vs. having to cursor over?

For example if I have a function that gets the top 1 closest match using FreeTextTable:

create function FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL (
     @classCodeDescription  varchar(4000)
    ,@state                 varchar(10)
    ,@classCode             int
    ,@effectiveOn           datetime2)
returns table as
return

select KEY_TBL.RANK, cd.CLASS_CODE_DESCRIPTION_ID
from CLASS_CODE_DESCRIPTION cd
  join CLASS_CODE cc on cc.CLASS_CODE_ID = cd.CLASS_CODE_ID
  inner join freetexttable (
      CLASS_CODE_DESCRIPTION, DESCRIPTION, @classCodeDescription, 1) as KEY_TBL
      on cd.CLASS_CODE_DESCRIPTION_ID = KEY_TBL.[KEY]
where cc.CLASS_CODE = @classCode
  and cc.STATE = @state
  and cc.EFFECTIVE_DATE <= @effectiveOn
  and cc.EXPIRATION_DATE > @effectiveOn
  and cd.EFFECTIVE_DATE <= @effectiveOn
  and cd.EXPIRATION_DATE > @effectiveOn

I then have a table that I would like to join against, to avoid having to do a painfully slow cursor through (there can be thousands upon thousands of rows)

declare @tbl table (
 classCode      int not null
,description    varchar(255) not null
,state          varchar(10) not null default 'FL'
,effectiveOn    datetime2 not null default '1999-01-01 00:00:00')

insert into @tbl (classCode, description)
values (9410, 'Municipal Employees'), (6, 'Farm: Fish Hatchery & Drivers')

I then do the query:

select *
from @tbl t
  cross apply dbo.FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL
  (t.description, t.state, t.classCode, t.effectiveOn)

But receive the error:

  Msg 4129, Level 16, State 1, Line 13 The inline function
  "dbo.FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL" cannot take
  correlated parameters or subqueries because it uses a full-text
  operator.

I have tried it several different ways, but ultimately I am unable to trick out the engine because they all really boil down to the same thing under the covers.

Is there a way to run FreeTextTable in a set operation vs. having to cursor through it?


texts:
This question is related to monitoring of SQL Server.

I want to send an email alert with all open transactions on SQL Server in HTML report whenever there is sustained 50-60% CPU usage for more than 3 minutes. How to achieve this using TSQL or stored procedure so that I can schedule as a job and keep polling on regular basis.
Similar as above, I wanted to get notified whenever there is sustained 70-80% memory usage on the server for more than 3 minutes.
The reason for this, even though I have set the max server memory setting for sql server, there could some component eating up memory which can cause external memory pressure.
In such situations, DBA team should get an email alert notification even before users calls in and open a ticket.

I have anyone can provide sample code that will be great help. Based on that I can extend the functionality of monitoring like collecting trace etc...


texts: Unattended/silent install of Oracle XE Express fails

texts:
I am experimenting with silent installs of Oracle 10g XE on a fresh XP VM.  I have an iss file, copied from the standard one from oracle, called "foo.iss"  Here is my command:

>OracleXEUniv.exe /s /f1”C:\xe\foo.iss” /f2”C:\xe\foo.log”

I just followed what I found on the Oracle support site.  This command causes a temp folder to be created, which contains an installer file: "Oracle Database 10g Express Edition.msi"

So it looks good.  But the process then just silently fails.  No log is created in the install folder (C:\x) except for setup.log which only has:

[ResponseResult]
ResultCode=-3

In C:\windows is the larger log file, OracleDatabaseXEServerInstall.log

Is has alot in it, and ends with:

1: Standard project type, let scripting engine clean up setup files...skipping action
Action ended 17:43:27: ISSetupFilesCleanup. Return value 1.
MSI (c) (34:BC) [17:43:27:420]: PROPERTY CHANGE: Adding ISSETUP_UISEQUENCE_PROCESSED property. Its value is '1'.
MSI (c) (34:BC) [17:43:27:420]: Note: 1: 2262 2: Upgrade 3: -2147287038
1: Not using skins for this installation.  Could not find skin file .
MSI (c) (34:BC) [17:43:27:650]: Note: 1: 2262 2: DuplicateFile 3: -2147287038
MSI (c) (34:BC) [17:43:27:700]: Note: 1: 2262 2: ISRequiredFeature 3: -2147287038
MSI (c) (34:BC) [17:43:27:920]: Note: 1: 2205 2: C:\DOCUME~1\dlwiii\LOCALS~1\Temp\_is33\Oracle Database 10g Express Edition.msi 3: ISAlias
MSI (c) (34:BC) [17:43:27:920]: Note: 1: 2228 2: C:\DOCUME~1\dlwiii\LOCALS~1\Temp\_is33\Oracle Database 10g Express Edition.msi 3: ISAlias 4: SELECT * FROM ISAlias
1: Ready to launch program block.
1: User aborts the installation, ready to launch __OnAbort.
MSI (c) (34:BC) [17:43:30:434]: Destroying RemoteAPI object.
MSI (c) (34:B8) [17:43:30:454]: Custom Action Manager thread ending.
=== Verbose logging stopped: 8/27/2011  17:43:30 ===

Any idea what I am doing wrong?  The command line looks suspect, as when I run the install program with just /? I get the following, which shows no /f1 nor /f2 options at all:

I am simply playing with this, but would really like to solve this, as unattended installs look like a great option to ensure consistent database deployments.

Cheers,
Daniel


texts: Select multiple rows based on date interval from two fields

texts:
I am still learning SQL and I would like to do something that I dont know if it is possible. I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date.
It would be something like this:

Original:

    Product|price|initial_date|final_date
    A|20.50|2014-08-10|2014-10-01
    B|50|2015-01-15|2015-02-20

Resulting view:

 Product|price|Date
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01

I would like to do it, because this dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.

Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.

By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.


texts: What is the right way to manage users data?

texts:
I'm trying to create a simple register login script with user profiles, I created a table with the following structure:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| username   | varchar(32)   | NO   |     | NULL    |                |
| password   | varchar(32)   | NO   |     | NULL    |                |
| first_name | varchar(50)   | NO   |     | NULL    |                |
| last_name  | varchar(50)   | NO   |     | NULL    |                |
| email      | varchar(1024) | NO   |     | NULL    |                |
| active     | int(1)        | NO   |     | 0       |                |
+------------+---------------+------+-----+---------+----------------+

How should I store other data like:

country, user level, registration date, domain name, email activation key, last login date, and so on ...

I have 2 options (maybe there are more I'm not aware of):

creating these as columns in this table
creating another table that is related by the user id

My questions are:

which one is better if later I want to add more data to be stored ?
If I take the first approach with one table is there going to be a problem when adding more columns later if we already have active users ?
and if we take the second approach how should I structure the data table ?

And thank you.

UPDATE:

What do you think about the WordPress approach in this matter ? WordPress has a usermeta table with this structure ?

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

So what do you think about this way of storing users data ? should this be considered in an app that will have lots of users ?


texts: Step through multiple arrays in parallel with FOREACH

texts:
Is it possible to loop over multiple array with FOREACH in PL/pgSQL? In my case, 3 arrays with identical dimensions. How can I pass the elements like:

for(int i = 0 ; i < array1.length ; i++){
  my_method(array1[i], array2[i], array3[i]);
}

Here is my attempt:

select dugong.session_hugeInsert( 3, '5,2,3,1', '4,3,3,2');

NOTICE:  The transaction is in an uncommitable state.Transaction was rolled back.
NOTICE:  query "SELECT Question_ID, UserResponseID_List, UserResponseList" returned 3 columns 42601
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function dugong.session_hugeinsert(integer,text,text) line 61 at SQL statement

My function:

 CREATE OR REPLACE FUNCTION Dugong.Session_hugeInsert(
    Quiz_ID_ int,       --FK
    QuestionList_ TEXT,
    UserResponseList_ TEXT
)
RETURNS int AS $$
DECLARE
    Session_ID integer;
    QuestionList INT[];
    Question_ID integer;
    QuizQuestionisDone boolean;
    UserResponseList INT[];
    UserResponseID_List INT[];  -- Get from database
    UserResponseID integer;
    tmp int;

BEGIN
    IF EXISTS ( SELECT 1 FROM dugong.quiz WHERE quiz_id = Quiz_ID_ ) THEN

    QuestionList = string_to_array(QuestionList_, ',');
    UserResponseList = string_to_array(UserResponseList_, ',');
    FOREACH UserResponseID in ARRAY UserResponseList
    LOOP
        tmp := Dugong.UserResponse_Add();
        UserResponseID_List :=array_append(UserResponseID_List, tmp );
    END LOOP;  -- Memo: Got UserResponseID_List array

    FOREACH Question_ID, UserResponseID, tmp IN ARRAY Question_ID, UserResponseID_List, UserResponseList
    LOOP
        RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i] ;
    END LOOP;
    RETURN 200;

    ELSE
        RETURN -1;
    END IF;
END;
$$ LANGUAGE PLPGSQL;

My first solution does not seem very good:

i:=0; j:=0; k:=0;
FOREACH Question_ID IN ARRAY QuestionList
LOOP
    j := 0;
    FOREACH UserResponseID IN ARRAY UserResponseID_List
    LOOP
        k := 0;
        FOREACH tmp IN ARRAY UserResponseList
            LOOP
        IF (i = j AND j = k) THEN
           RAISE NOTICE '%, %, %', Question_ID, UserResponseID, tmp;
        END IF;
        k := k+1;
            END LOOP;
    j := j+1;
    END LOOP;
i := i + 1;
END LOOP;

Please let me know the proper way to go.


texts: Get inserted row count when master table isn't modified

texts:
I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables.
Now, I need to create a PL/pgSQL procedure that SELECTs some data from the (master) table and re-inserts it with some changed values.
Normally I'd use the ROW_COUNT diagnostics variable and be done with it.
Trouble is that ROW_COUNT is always 0, because nothing gets inserted into the master table (the trigger catches and redirects inserts en route).
My next idea was to do something like
WITH inserted AS (
  INSERT INTO master (...) SELECT ... FROM master WHERE ... RETURNING master.field
) SELECT count(*) FROM inserted INTO rowcount;

but, again, rowcount always ends up being 0, because nothing was ACTUALLY inserted into the master table.
If I do
WITH insertable AS (
  SELECT ... FROM master WHERE ...
), blah AS (
  SELECT count(*) INTO rowcount FROM insertable
) INSERT INTO master SELECT * FROM insertable;

then Postgresql complains that:

ERROR: INTO used with a command that cannot return data
SQL state: 42601

Etc...
In case of my last code extract, how would I get the row count of inserted into a rowcount variable in my procedure that I can then do stuff™ with? Or is there any other way of retrieving the number of rows that were inserted into the (tables inheriting from) master table?


texts:
I have a new job where I am re-learning SQL (oh the joy) after years of not using it.

I currently have a table that looks something like this:

RoomNumber,
BlockID,
ModelEndDate,
DepartureDate,
DATEDIFF(DAY, StartDate, EndDate) AS NumDays

So essentially there is a model applied to each room, which has an enddate. If a resident leaves early we set the DepartureDate and calculate how many days left in the Model.

EDIT Some background on how this actually is calculated (as I should have probably included it). Each room is occupied at different periods throughout the year, and has associated models to show what those dates are. E.g. one room may have the following:

ModelStartDate,
ModelEndDate,
Period

As a fairly standard rule of thumb, each room usually has three periods. I currently just calculate the total number of free days by either working out the difference between the start and end dates or (if departure is mid-model) I would work out the difference between the DepartureDate and ModelEndDate. I then group by Room giving me the totalfreedays.

What I now need to do is work out how these days are spread by month per block (many rooms to a block). So, if somebody was to Depart very early in the year, they would free up a load of days in each month for that room. For financial reporting I want to be able to say how many free days, per month, per block (summing all rooms within the Block). Ideally I would like a table that looks like the following:

BlockID,
DaysFreeSept,
DaysFreeOct,
DaysFreeNov
etc

I am having a little trouble understanding how I should accomplish this, I have looked through a few answers on here but they are mighty confusing.

Cheers for any help you can give me.

This is on SQL Server 2008 R2 (10.50)


texts: Why would you need to put a domain name when install a Oracle database?

texts:
When you install an Oracle database or instance, you are able to assign a domain name.

An Example:

SID: orcl

Domain: world.com

Service: orcl.world.com

Why would it be necessary to add or configure a domain?


texts: Convert raw or hex value to signed 2's complement via Oracle SQL

texts:
Is there a way to convert either HEX or RAW value to signed 2's complement via Oracle SQL?
example:
hex: 8508 > dec: 34046 > dec from signed 2's complement: -31480
The number that I am trying to get within my SQL statement is -31480.


texts: MYSQL reduce column length without changing datatype

texts: Can we change the size of a LONGTEXT column in mysql without changing the datatype?


texts: Whether to use or not to use: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

texts:
In one Database table, created_at & updated_at are defined as:

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',

And in another database table, it's defined as:

`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',

created_at -> stores datetime when the data is created.
updated_at -> stores the datetime when data is updated.

Which definition among above two would you prefer & why?


texts: I have 10 tables in schema icmdb2admin each table has one column named pdfname i need to update the values in the pdfname column with L1.pdfname.V1

texts:
DECLARE
    i record;
BEGIN
    FOR i IN SELECT 'icmdb2admin.' || tablename as tbp
             FROM pg_tables
             WHERE schemaname = 'icmdb2admin'
               and tablename <> 'department_master'
               and tablename <> 'year'
               and tablename <> 'user_login'
               and tablename <> 'filetype'
               -- and other conditions, if needed
    LOOP
         EXECUTE  UPDATE 'i.tbp' SET "pdfname" = 'L1.'|| pdfname || '.V1';
    END LOOP;
END;


texts:
I want to run a query all X seconds on my PostgreSQL database (9.5 on Windows Systems).

I read that you can use Background Jobs to achieve this.

The pgAgent Jobs (https://www.pgadmin.org/docs/1.4/pgagent-jobs.html) are a good explanation on how to do this.

However on the installation page (https://www.pgadmin.org/docs/1.4/pgagent-install.html) they mention the security concerns about storing the password on the server / connection string.

Is there another way to run a query all X seconds on the database?
Maybe there is an extension that works like oracle jobs?


texts: ISNULL on NTEXT causes fatal exception on SQL Server

texts:
I have a weird error that occurs on a client's SQL Server 2005 box.  We have provided them with a dataview that is populated with a query with a CTE with several joins / subqueries that does some text value interpretation on an NTEXT field that contains an XML blurb

I know that we could have used used other field types and / or methods, but it is what it is for good reason that I hope I would not have to explain here.

The problem is that for some reason, this query creates a fatal exception on the SQL Server.

Herewith a condensed version of the business end of the actual T-SQL that causes the error

declare @@dtDateStart datetime, @@dtDateEnd datetime
set @@dtDateStart = getdate()
Set @@dtDateEnd = dateadd(year, 1, @@dtDateStart)
;with  qItems
    (date_created, SYSTEM_ID, LegalEntity) as
(   select i.date_created,
        i.externalKey as SYSTEM_ID,
        case when i.HasLegalEntity > 0 then
            substring(i.message, charindex('<LegalEntity>',i.message)+13, charindex('</LegalEntity>',i.message)-charindex('<LegalEntity>',i.message)-13)
         else '' end as LegalEntity
    from (select *, charindex('<LegalEntity>', message) as HasLegalEntity from dbo.INT_Error_Queue) as i
    where i.date_created between @@dtDateStart and @@dtDateEnd)
select
    QItems.date_created,
    QItems.SYSTEM_ID,
    Qitems.LegalEntity
from QItems
WHERE
(isnull(Qitems.LegalEntity, '') not in (select key_value from Special_app_Settings where key_name = 'EntityName'))

At first I suspected that it has something to do with the where clause, because the [key_value] on [Special_app_Settings] is of type nvarchar(4000), and [i].[message] on [INT_Error_Queue] is of type [ntext], but I am not sure whether that would cause the error I have.  If I remove the where clause, the error does not occur.

Herewith the dump from the SQL Server Log:

03/13/2013 10:30:24,Server,Unknown,A user request from the session with SPID 101 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
03/13/2013 10:30:24,Server,Unknown,Error: 17310<c/> Severity: 20<c/> State: 1.
03/13/2013 10:30:24,spid101,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
...
03/13/2013 10:30:20,spid101,Unknown,* BEGIN STACK DUMP:
03/13/2013 10:30:20,spid101,Unknown,*
03/13/2013 10:30:20,spid101,Unknown,* *******************************************************************************
03/13/2013 10:30:20,spid101,Unknown,SqlDumpExceptionHandler: Process 101 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
03/13/2013 10:30:20,spid101,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\SQLDump0039.txt
03/13/2013 10:30:20,spid101,Unknown,Using 'dbghelp.dll' version '4.0.5'

Does anyone have any idea as to WHY this occurs?