texts: UPDATE adds a new row when I merely want to edit an old one

texts:
Here's my code:

$sql = "UPDATE `news` SET (headline, text, startDate, endDate, imageLocation, imagePlacement, imgStyles, link) ";
$sql .="VALUES('$headline','$text','$startDate','$endDate','$imageLocation','$imagePlacement','$imgStyles','$link') ";
$sql .= "WHERE id = $id";

I've tried REPLACE INTO, INSERT INTO, and all kinds of variations on single quotes, etc., to no avail. I even hardcoded the ID (22) in case that wasn't getting picked up correctly, but every time I try something like this I get a new row, which means I have two entries with slightly different data in each.

The table's primary key is ID and it's set to AUTO_INCREMENT.

Any thoughts on this?

EDIT

Here's the entire code block on the PHP page:

$id = $_GET["newschoice"];

if(empty($headline)) $headline = "none";
if(empty($text)) $text = "none";
if(empty($imageLocation)) $imageLocation = "";
if(empty($imagePlacement)) $imagePlacement = "0";
if(empty($imgStyles)) $imgStyles= "none";
if(empty($link)) $link = "none";

opendatabase();

$sql = "UPDATE `news` SET (headline, text, startDate, endDate, imageLocation, imagePlacement, imgStyles, link) ";
$sql .="VALUES('$headline','$text','$startDate','$endDate','$imageLocation','$imagePlacement','$imgStyles','$link') ";
$sql .= "WHERE id = $id";

if($result = mysqli_query($sql))
  print "
Record successfully added to table!
";
else {
  print "
Problem adding record: ".mysql_error()."
";
}

Then elsewhere there is more to fill the form with the correct table row:

$id = $_GET["newschoice"];

if (isset($id) && $id != "") {

    opendatabase();
    $sql = "select headline, text, startDate, endDate, imageLocation, imagePlacement, imgStyles, link FROM news WHERE id = $id";
    $result = mysql_query($sql);
    $row = mysql_fetch_object($result);
    $left = "";
    $right = "";
    $dbImg = "";
    if ($row->imagePlacement == 0)
        $left = "checked";
    if ($row->imagePlacement == 1)
        $right= "checked";
    if (isset($row->imageLocation)) {
      $dbImg = "imageLocation\">";
    }
    $showCameo = "style=\"display:block\"";
}

  $headline = $_REQUEST["headline"];
  $text= $_REQUEST["text"];
  $startDate= $_REQUEST["startDate"];
  $endDate= $_REQUEST["endDate"];
  $imageLocation= $_REQUEST["imageLocation"];
  $imagePlacement= $_REQUEST["imagePlacement"];
  $imgStyles = $_REQUEST["imgStyles"];
  $link = $_REQUEST["link"];

Apologies if this is hopelessly clunky. I'm not a dba but have had this task thrust upon me.


texts: Availability Group backup preference not changing to primary

texts:
I have an availability group setup and I'm using Ola Hallengren backup scripts. At setup of the Availability group, the default option of Prefer Secondary was set, where it should have been changed to primary.

Once I noticed this, I tried to set the backup preference to Primary, but it doesn't change, it stays on Prefer Secondary.

Has anyone come across this before?  I'm using the user interface (availability group wizard) to make the change.


texts: Trouble With Database Permissions for sp_send_mail

texts:
I am trying to send database mail but I am getting EXECUTE permission denied on the object 'sp_send_dbmail' database 'msdb', schema 'dbo'.. I code I am running is as follows:

SELECT SUSER_NAME(), USER_NAME();
Create USER kyle_temp FOR LOGIN Foo
EXECUTE AS USER = 'kyle_temp';
SELECT SUSER_NAME(), USER_NAME();
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail Profile',
            @recipients = 'test@test.com',
            @subject = 'Test',
              @body = 'Test'
REVERT;
DROP USER kyle_temp

The Foo Login shows that it is mapped to the Foo User in msdb. When I look at the foo user in msdb I see that it has "DatabaseMailUserRole" Checked and has Execute on dbo sp_send_dbmail.

What am I missing?


texts: Is it a good approach to design tables according to the queries I'll perform on it?

texts:
Watching this video, and pretty new to dbms.
The speaker explains that in a row-oriented DB, rows are read in blocks.
So, my understanding is that if I have rows with fewer fields, more rows can fit into a single block and when I query the table it should take less IO operation, resulting in better performances.. Am I right?
Can I extract the rule that I shouldn't design tables according to the entity they represent but, instead, to the frequency I'll read or update that fields?
For example:
table employers:

ID
Name (frequently used)
Badge number (frequently used)
Birth date (rarely used)
Birthplace (rarely used)

Should I split the table into 2?
tbl1: ID | Name | Badge number
tbl2: ID | Birth date | Birthplace


texts:
I have a view which determines the type of a transaction and whether it has been successful...

ALTER VIEW [dbo].[vw_payment]
AS
SELECT t.*,
        (CASE
            WHEN
                (ht.Status = '0')
                OR
                (ebt.TransactionId IS NOT NULL AND ebt.TransferDate IS NULL)
                OR
                (ppt.TransactionId IS NOT NULL AND PaymentStatus IS NULL) THEN 2
            WHEN
                (ht.Status = 'A' OR (ht.Status = 'F' AND ht.ErrorCode NOT IN (501, 500)))
                OR
                (ebt.TransactionId IS NOT NULL AND ebt.TransferDate IS NOT NULL)
                OR
                (ppt.TransactionId IS NOT NULL AND ppt.PaymentStatus = 'Completed') THEN 1
            ELSE
                0
        END) AS Status,
        (CASE
            WHEN ebt.transactionid IS NOT NULL THEN 1
            WHEN ppt.transactionid IS NOT NULL THEN 2
            WHEN ht.transactionid IS NOT NULL THEN 3
            ELSE NULL
        END) AS PaymentMethodId
FROM
    tbl_transaction t
LEFT JOIN
    tbl_transaction_hsbc ht
ON
    t.transactionid = ht.transactionid
LEFT JOIN
    tbl_transaction_bank_transfer ebt
ON
    t.transactionid = ebt.transactionid
LEFT JOIN
    tbl_transaction_paypal ppt
ON
    t.transactionid = ppt.transactionid

What is a good way to avoid having to look up the status without needing multiple left joins and a case statement?

I cannot use an indexed view because of the left joins.

The only alternative I can think of is storing a redundant status in the base transaction table.

When selecting successful transactions, almost 100% of the query is working out the status, and this won't scale to, say, 100 transaction types.


texts: MongoDB Replica Set Can't be connected To

texts:
I just set up a replica set for a standalone Mongo instance using the following changes to my config and rs.initiate().

# in master/slave replicated mongo databases, specify here whether
# this is a slave or master
#slave = true
#source = master.example.com
# Slave only: specify a single database to replicate
#only = master.example.com
# or
master = true
#source = slave.example.com

# in replica set configuration, specify the name of the replica set
replSet = rs0

When I check the status of the replica set from within Mongo it seems to be setup properly:

rs0:PRIMARY> rs.status()
{
    "set" : "rs0",
    "date" : ISODate("2014-11-14T08:18:06Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "Server:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 1980,
            "optime" : Timestamp(1415952019, 4),
            "optimeDate" : ISODate("2014-11-14T08:00:19Z"),
            "self" : true
        }
    ],
    "ok" : 1
}

However when I try to run a test I get the following error:

PHP Fatal error:  Uncaught exception 'MongoConnectionException' with message 'MongoClient::__construct(): php_network_getaddresses: getaddrinfo failed: Name or service not known' in /var/www/html/Site/m.php:10
Stack trace:
#0 /var/www/html/Site/m.php(10): MongoClient->__construct('mongodb://MONGO...', Array)
#1 {main}

Next exception 'MongoConnectionException' with message 'No candidate servers found' in /var/www/html/Site/m.php:10
Stack trace:
#0 /var/www/html/Site/m.php(0): MongoClient->__construct()
#1 {main}
  thrown in /var/www/html/Site/m.php on line 10

The connection code is a simple test:

$uri = "mongodb://MONGOHOST:27017/provider";

/*
 * We recommend explictly configuring a connection timeout (see tips & tricks
 * below). Specify the replica set name to avoid connection errors.
 */
$options = array("connectTimeoutMS" => 30000, "replicaSet" => "rs0");
$client = new MongoClient($uri, $options);
echo print_r($client)."\n";

However, I can connect if I omit the $options array with the following:

The connection code is a simple test:

$uri = "mongodb://MONGOHOST:27017/provider";

/*
 * We recommend explictly configuring a connection timeout (see tips & tricks
 * below). Specify the replica set name to avoid connection errors.
 */
$client = new MongoClient($uri);
echo print_r($client)."\n";

Can anyone point out why I can't connect directly to the replica set?


texts:
It happened I had to work with both SQL Server and Oracle for quite a while (thankfully not at the same time).

What still puzzles me is the approach to storing tables as balanced trees. In Oracle-like RDMS heap is default, in SQL Server (and many others ) the reverse (clustered, IOT) is true. Adepts of each approach claim their way is the only 'correct' and support chosen point of view with bunch of tests/presentations. However, in my opinion, the only point they proved is that implementation of "non-default" approach is poor, and it shouldn't be used for the most cases...

I'm pretty sure both approaches are good enough (just because they still exist on the market and show comparable performance) and have some math underneath, but I've failed to find any good references.

I realize the topic may be too broad to answer, and good links are very welcome, but I really want to know why two seemingly controversial approaches have proven they are both valid.


texts: pg_restore terminates with "out of memory" error

texts:
Trying to restore a 45mb pg_dump file but it keeps failing with an "out of memory" error.

Content of my postgresql-10-main.log:

2018-01-16 06:14:50.692 WAT [32405] postgres@app_development LOG:

could not receive data from client: Connection reset by peer
2018-01-16 06:14:50.692 WAT [32405] postgres@app_development CONTEXT:  COPY assessment_scores, line 15347
2018-01-16 06:14:50.692 WAT [32405] postgres@app_development STATEMENT:  COPY assessment_scores (id, student_id, assessment_id, score, exempt, late, collected, missing, comment, deleted_at, created_at, updated_at) FROM stdin;

2018-01-16 06:14:50.693 WAT [32405] postgres@app_development LOG:  incomplete message from client
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development CONTEXT:  COPY assessment_scores, line 15347
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development STATEMENT:  COPY assessment_scores (id, student_id, assessment_id, score, exempt, late, collected, missing, comment, deleted_at, created_at, updated_at) FROM stdin;

2018-01-16 06:14:50.693 WAT [32405] postgres@app_development ERROR:  unexpected EOF on client connection with an open transaction
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development CONTEXT:  COPY assessment_scores, line 15347
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development STATEMENT:  COPY assessment_scores (id, student_id, assessment_id, score, exempt, late, collected, missing, comment, deleted_at, created_at, updated_at) FROM stdin;

2018-01-16 06:14:50.693 WAT [32405] postgres@app_development FATAL:  terminating connection because protocol synchronization was lost
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development LOG:  could not send data to client: Broken pipe

I checked with HTOP, my RAM is at about 4GB/8GB and SWAP space is unused when this happens.

DB server is localhost.


texts: SQL Managed Instance - Cross Subscription Restores/Migration

texts:
Short Version:

I am trying to restore a backup from one SQL Managed Instance to another SQL Managed Instance.  These are in different subscriptions.  Using Example 3 from here I can at least get it to the point where I see the restore starting in sys.dm_operation_status, but the error message is "Subscription [target] does not contain [source SQL MI]".

Questions:
How can I migrate the database from one SQL Managed Instance to another (in a different subscription)?  My current workaround is to do copy_only to a blob storage and restore that way.

Once we enable TDE (in-progress) we won't be able to do copy_only backups to blob.  What then?

Long Version:

Fairly recently our company made the jump to running our production workload in Azure on SQL Managed Instances (and other PaaS/IaaS) and this went fairly well.  We had a large enough window identified that we were able to do the initial load with a full backup, although I did familiarize myself with the Database Migration Services (DMS).

But now we are expanding out our dev/test/qa environments and of course we now want to restore from production to these environments.  For isolation purposes, each is getting it's own subscription.

These commands work, in the sense that I can at least see the restore operation start in sys.dm_operation_status but it fails with an error.  The error is "Subscription [target] does not contain [source SQL MI]" and leads me to believe that cross subscription restores won't work for us (although cross region would).

DMS does not appear to support the scenario of using Database Migration Services as a source.

Select-AzSubscription -Subscription $SourceSubscriptionID

$GeoBackup = Get-AzSqlInstanceDatabaseGeoBackup -ResourceGroupName $SourceResourceGroupName `
     -InstanceName $SourceSQLMI `
     -Name $SourceDatabaseName

Select-AzSubscription -Subscription $TargetSubscriptionID

Restore-AzSqlInstanceDatabase -FromGeoBackup `
    -GeoBackupObject $GeoBackup `
    -TargetInstanceDatabaseName $TargetDatabaseName `
    -TargetInstanceName $TargetSQLMI `
    -TargetResourceGroupName $TargetResourceGroupName

Any advice, ideas?  I'm willing to use preview versions of the various Azure PowerShell if necessary although I've not found anything to indicate that it's different there.

POSSIBLE WORKAROUND

I did get a link from one of our Microsoft contacts that seemed to indicate that you can stand up a managed instance in the same subscription, restore to it and then move the managed instance to another subscription.  I have NOT attempted this so I don't know.  The link they provided was here although I have my doubts about it's effectiveness.


texts:
I'm currently using MySQLWorkbench 5.2.40 on my PC (8GB rRM, Corei7). I have a table with almost 1.5 million records, which I need to visually look over. Surprisingly, the total size does not exceed 200 MB. The problem, when I try to select all the records to view them (which is necessary for me), I get an error:  Error Code 2008: MySQL Client ran out of memory. I could not solve this issue.
Questions

Are there any recommendation/options other than MySQLWorkbench to view my tables in a way that guarantees there will be no memory issues ?
Will there be any harm to install two DB systems in the same machine ?


texts: Why do maintenance plans exist as a feature?

texts: Do maintenance plans provide any functionality beyond their component parts? It would seem that the only value added is the convenience of grouping subplans into logical bundles, and the simplified GUI for creation. All the actual work is being done by the underlying SSIS tasks which are being scheduled as agent jobs. Is there anything more to it than that?


texts:
I am pretty new to Oracle and the database world. I am not sure if the title makes any sense at all, so I'll just go ahead and explain my situation.

I currently have a table at hand that records order transactions. The columns in this table are TRANSACTION_TYPE, ORDER_ID, QUANTITY, DATE, CREATED_BY, CREATION_DATE. The transaction types are 'PURCHASE' and 'REFUND'. Till now we only had full refunds because mostly the items bought were of single quantity. We're hoping to make it multiple in which case we will possibly have to perform multiple refunds.

The tables unique-key constraint is a combination of TRANSACTION_TYPE, ORDER_ID. I can break this constraint to make it possible to do multiple refunds by expanding the combination to TRANSACTION_TYPE, ORDER_ID, TRANSACTION_REFERENCE (this reference comes from the service that does the actual purchase or refund). But there's the thing, this means that I can do multiple refunds on the same order and the same is possible if it is a purchase. I would like to create a constraint such that the combination of 'PURCHASE' + ORDER_ID is unique in the table so that only a single purchase is possible for a given ORDER_ID by constraint.

I tried searching for this possibility and even tried it in this site. I might be using the wrong terms here. But if anyone here has an answer for this, I would LOVE to hear it out.

Any help to even nudge me to the right direction would be much appreciated.


texts:
A project has the requirement that it shall not be possible to lose data from certain tables. That is, if I "delete" a row, that row's information should somehow be preserved.

Additional information

It is fairly unlikely that anyone would actually ever be interested in the deleted records, so speed of access is not particularly important.
The records being deleted may be the primary key for other records in the database; if they are the primary key, the delete will cascade to the foreign records.
The database will primarily be accessed through an API, so I can put the logic there if that's really the best place.

On SQL Server (2005 or later), what is the best way to accomplish such a thing?

As a note, the internet has provided me with a multitude of solutions. One seems to stand out as the best choice, but I can see some caveats. Rather than asking whether my solution is good, it seems better to just ask for a good solution. If it would be helpful, I can edit in my ideas.


texts: What is the recommended method of storing statements used in timer jobs?

texts:
I've have a function that pulls data from several tables, arranges it nicely and generates an email summary which is to be sent out on a weekly basis to interested parties.
I decided a function was the right way to go for this since I needed to pass in parameters (which ruled out a view) but I didn't need to update the data (which ruled out a stored procedure) but I'm uncertain on how to store the statements that are going to do a few calculations and then call this function.
I believe I have two options:

Store it directly in the command as the first (and only) step of the job:

or

Store it in a Stored Procedure and then call that from the command:

Of the two I prefer the second method, it looks neater and keeps the logic of the job out of the call to the job. However, there is no need to call this process from anywhere else so it doesn't feel right to keep it in a stored procedure.
Is there a documented or recommended way of organising such statements? Am I following it with either of the examples above? If not, what should I be doing?


texts:
I am using Windows Server 2012 x64 and SQL Server 2012.

I am running the Database Tuning Advisor with these settings :

Advisor plan cache.
Database workload master.
Check my database all the tables.

When tool is started after few seconds I am getting :

  SQL Server 2012 Tuning process exited unexpectedly. DTAEngine.

After some "googling" I found that I should install Service Pack 1 for SQL Server 2012. So I did that.

Same result.

If someone has experienced something like this please advise me.

Thx for your time.


texts: Can Postgres index regular expressions stored in a column?

texts:
I've got regular expressions stored as varchars in a column, which I need to match against incoming input. For example, the table might contain:

| field |     value     |
|-------|---------------|
| email | .*@domain.com |

And a query would be:

SELECT *
FROM table
WHERE field = 'email'
  AND 'someone@domain.com' ~* value

I'll be the first to admit this is pretty silly, though it did well enough for about 2 years. The table's now up to a mindshattering 10k rows, and queries slowed to on the order of 3 seconds. I've already moved us to a much more reasonable strategy, so this question is purely academic.

If I had kept this setup, is there any way to make the lookup efficient? I was hoping for some sibling of varchar_pattern_ops, but this query is the reverse of what that solves.

With the idea down, here's the full table, query, and explain.

+------------+-----------------------------+------------------------------------------------------------+
| Column     | Type                        | Modifiers                                                  |
|------------+-----------------------------+------------------------------------------------------------|
| id         | integer                     | not null default nextval('table_id_seq'::regclass)         |
| field      | character varying(255)      | not null                                                   |
| value      | character varying(1000)     | not null                                                   |
| comment    | text                        |                                                            |
+------------+-----------------------------+------------------------------------------------------------+
Indexes:
    "table_pkey" PRIMARY KEY, btree (id)
    "index_table_on_field_and_value" UNIQUE, btree (field, value)

EXPLAIN ANALYZE
SELECT *
FROM table
WHERE (
    (field = 'contact_email' AND 'person@place.com' ~* value)
 OR (field = 'phone'         AND value = '1234567890')
 OR (field = 'unique_id'     AND value = 'abcdef')
);

Seq Scan on table (cost=0.00..613.08 rows=58 width=1) (actual time=744.371..744.371 rows=0 loops=1)
Filter: ((((field)::text = 'contact_email'::text) AND ('person@place.com'::text ~* (value)::text))
      OR (((field)::text = 'phone'::text) AND ((value)::text = '01234567890'::text))
      OR (((field)::text = 'unique_id'::text) AND ((value)::text = 'abcdef'::text)))
Rows Removed by Filter: 11643
Total runtime: 744.395 ms


texts: SSIS - The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

texts:
I am trying to use explisit transactions within SSIS. The database I am working with does not have the DTC enabled, so I cannot use SSIS' inbuilt transaction handling.

I have execute sql tasks to initialise, commit and rollback the transaction, however despite having set "RetainSameConnection" to true, I am still getting the below error:

  [Execute SQL Task] Error: Executing the query "ROLLBACK;" failed with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


texts: How to troubleshoot/debug Oracle permission problems

texts: An application is connecting to Oracle to do stuff. It's not working but I don't get any ORA errors in the application itself. I'm thinking its a permission problem. So my question is what is the best way to check to see if a oracle account  tried to perform an operation that it didn't have permission to do? It would also help to know what operation it tried to perform so I can grant it.


texts: Archiving process not running fast enough


texts: Can Availability Groups provide seamless failover (with no query failures)?

texts:
I have been testing the Availability Groups feature in SQL Server 2012 and am finding that there is approximately 15 seconds of down time when the primary server fails over to the secondary server. All SQL queries executed during this time are failing until the failover transition is complete.

Is there any way to get this down to 0 seconds and prevent queries from failing during the failover transition?

In other words, is there a way to get any queries running during the failure to be redirected to the primary server instead of failing... and is there any way to get new db connections to immediately connect to the secondary server instead of failing to connect during the failover transition?

I currently have 2 servers set up in the Availability Group.


texts: Can I do vacuumlo,vacuumdb and reindex in live production system without any downtime in postgres database

texts:
We are using a Postgres database (9.3) as our production database. It is generating huge lo objects. Due to this, the size is growing very fast. So we need to do database cleaning frequently. To remove these lo objects we are having long downtimes.

To clean the DB, we are performing the below steps in sequence:

vacuumlo database_name
vacuumdb database_name -t pg_largeobject
vacuumdb database_name -t pg_largeobject_metadata
reindexdb -t pg_largeobject database_name
reindexdb -t pg_largeobject_metadata database_name

Can we do the above online (during the night) without having any downtime?


texts: Issue with adding a database to an Availability Group

texts:
I am having an issue with a particular AG where I am adding a DB (test123 below) to the AG and on primary it will show everything OK as follows:

However, on secondary it shows with an exclamation mark as follows and is not added to the list of DBs on the secondary instance:

I also see this message in the error log:

  Unable to access availability database 'test123' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. Nonqualified transactions are being rolled back in database test123 for an Always On Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required.

I'm using automatic seeding to populate the database on the secondary.

I have added the same DB to a different AG and this issue did not happen and it was added successfully.

The DB is in full recovery model and a full backup has been taken as well and it meets prerequisites. AG and listener looks fine, any ideas what can be the cause of this?


texts: Cannot continue the execution because the session is in the kill state. while building clustered index

texts:
I get the following error while trying to create a clustered index

The statement has been terminated.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

The index is:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

BEGIN TRANSACTION
GO

CREATE CLUSTERED INDEX IX_CO_DES_INPUT
ON dbo.CO_DES_INPUT(DESIGN_ID, PRODUCT_INPUT_NUM, INPUT_NAME)
          WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
               ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE dbo.CO_DES_INPUT
  SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

I am using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )
I have run
DBCC CheckDB ('concept-test') WITH NO_INFOMSGS, ALL_ERRORMSGS

and it found no problems
I am afraid that my database is corrupt since I am getting this error. How do I correct my issue and get this index put on the table?


texts: What permission is needed to set Database read-only?

texts:
I am dbowner of a database which I want to set read-only. (through sql management studio). When I try that I get a permission error (297)

Which role is needed to perform that action?


texts: MariaDB Replication - Missing ^M Characters on Slave

texts:
I've got a simple single master->slave replication setup with MIXED binlog format for mariadb 10.2.22-1 on RHEL7.

I believe I'm having an issue where '^M' characters in a longtext column are not being replicated to the slave.

I've checked the encoding matches on both databases (SHOW VARIABLES LIKE 'collat%' and SHOW VARIABLES LIKE 'char%').

Does anyone know what might be causing the slave to strip the '^M' character when the data is being replicated to the slave?


texts: Startup Parameters missing on passive SQL Server node in cluster?

texts:
I built a failover cluster on windows 2016 for sql 2016 using SMB fileshare as shared storage. This is just for learning, not production (I'm a SharePoint guy btw)

On the passive node, once installed, the sql service startup parameters were never populated on the server. If I went to the startup parameters in service properties, they were missing. So failing over to this node would never start the server.

Has anyone seen this?
I fixed the issue by adding the identical startup params from the active node to the passive node and it all works now.


texts:
I have a query where I will be using the same value twice for one of the parameters. This query will be run under IBM Data Studio to see the results. Below is a sample query:

SELECT *
FROM SYSADM.STATISTICAL_TABLE S
WHERE S.END_RECORD_TIMESTAMP BETWEEN
    CONCAT(?, '-12.00.00.000000') AND
    CONCAT(?, '-22.00.00.000000');

When run in Data Studio or any similar graphical query building environment I am prompted to enter values for both parameters, even though the values will always be the same for both.

I believe it is possible to used named parameter markers in other RDBMSes to indicate that the value should only be given once. Example:

SELECT *
FROM SYSADM.STATISTICAL_TABLE S
WHERE S.END_RECORD_TIMESTAMP BETWEEN
    CONCAT(?:queryDate, '-12.00.00.000000') AND
    CONCAT(?:queryDate, '-22.00.00.000000');

Is there a way to use named markers in DB2 for z/OS to indicate that the value will be the same so graphical tools only prompt for one input? On queries that have 20+ inputs, this can be time consuming.

Is there a better way that I should be doing this?


texts: mysql, performance issue with FOR UPDATE query

texts:
I have a query like this:

SELECT id,domain_name FROM domains WHERE test_stage=1 order by id LIMIT 200 FOR UPDATE

This is happening up to 500 times multithreaded within a second, so quite fast and it's overloading my database server sometimes.
I think it's got something to do with an internal state of mysql, usually it's handled quickly but sometimes it just builds up and up.

Server is mysql 5.5. (I wish I could use a newer one but it's not possible to upgrade)
I have an index on test_stage,id
test_stage is an enum with just a few entries, id is autoincrement integer with a few million entries.

Anyone has an idea how to do that sort of query faster?
I know later mysql servers allow to "skip" locked rows, sadly I am with 5.5.

The purpose is selecting a number of "jobs" for a multithreaded worker.

P.S.
The only reason why my server is not crashing is because I have a CPU check added. As soon as 10 cores are fully loaded it stops adding more threads.

Update:
The server is quite strong, 120GB memory (mostly for mysql) and it's far from being maxed out.

Item(s) coming in up to 10,000 times a second.
1-20,000 arrive in each batch.
The client threads are limited to 600 threads by php and additional logic (runs on apache).
The threading uses  PHP 7.2 on serverside .
max_connections = 1500
Processing one item takes, on average 1-2 ms when everything works fine.
The table domains is current 20MM rows; it is purged never

CREATE TABLE `domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(64) NOT NULL,
  `test_time` datetime DEFAULT NULL,
  `website_seo` varchar(512) DEFAULT NULL,
  `context` varchar(15) DEFAULT NULL,
  `test_stage` enum('a','b','c','d','e','f') DEFAULT 'a',
  `type` enum('a','b','c','d','e','f') DEFAULT NULL,
  `counter_z` smallint(6) DEFAULT NULL,
  `counter_a` smallint(6) DEFAULT '0' ,
  `counter_b` smallint(6) DEFAULT '0' ,
  `counter_c` smallint(6) DEFAULT '0' ,
  `info_a` varchar(256) DEFAULT NULL,
  `info_b` varchar(256) DEFAULT NULL,
  `extrapolate_a` varchar(16) DEFAULT NULL ,
  `extrapolate_c` varchar(16) DEFAULT NULL,
  `extrapolate_int` smallint(6) DEFAULT '0' ,
  `max_successful` smallint(6) DEFAULT NULL,
  `counter_successful` smallint(6) DEFAULT NULL,
  `timeout_counter_successful` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain_name` (`domain_name`),
  KEY `domain_name_test_time` (`domain_name`,`test_time`),
  KEY `test_time` (`test_time`),
  KEY `test_stage` (`test_stage`),
  KEY `counter_c_t` (`counter_c`,`test_time`),
  KEY `test_stage_test_time_counter_c` (`test_stage`,`test_time`,`counter_c`),
  KEY `test_stage_id` (`test_stage`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19437107 DEFAULT CHARSET=latin1

I'd like to add: The issue is not apache or the number of threads, it's mysql not delivering the data.
My problem is that sometimes the "FOR UPDATE" select which selects a bunch of rows, and then updates them to be used  is delaying.
It can happen with 1000 rows being unprocessed, it can happen with 100,000 rows unprocessed.
After a while it suddenly works again, usually mysql can catch up and then you don't see anything in "processlist" anymore.
When it happens you see around 100-200 of them waiting and CPU spikes up to 10 cores (then the queue is locked from further additions)

Update:
I've had issues with similar problems before, the only solution then was to actually move all "open" requests into a temporary table and make the queue selections from there, synchronizing it back into the full table.
It's alot of additional work but then mysql is fast, I hate to do it again :(


texts: Need Help With BCNF Decomposition Problem

texts:
I'll try and keep it as quick and concise as I can: I'm currently a novice developer who's super eager to learn Database Normalization and Optimization so I can up my skill in Full-Stack development, so bare with me please. I'm trying to get good at spotting the different kinds of functional dependencies so I can get better at making optimal/appropriate relational models/schemas, and I'm currently doing an assignment on 3NF and BCNF decomposition (possibly NF4?) that I'm struggling with.

The Assignment has 2 parts and I'm fairly certain that I've properly completed the first Part 1, but I'm completely confused on the Part 2.

Here is the relation we are normalizing called LOTS:

Part One was to break the Relation down into BCNF form if possible, and here is my work for that:

The current R1 relation is the LOTS relation for Part 2.

Here is the prompt for Part2:

It seems to me that, since the AREA attribute has unique sets of values that can determine the COUNTY_NAME that the new introduces functional dependency is AREA -> COUNTY_NAME, which would leave the LOTS relation in 3NF. Thank you for anyone whiling to help a new comer learn. Cheers!


texts: Shell Script / Tool to check the MySQL Replication Status?

texts:
Possible Duplicate:
  Monitoring replication on mysql

I am running with MySQL Master/Slave Replication on Linux.

Is there any tool/Shell script which monitors the Replication Status and send the email alerts when there is Replication failure.


texts: Oracle. How to copy BLOB, LONG RAW and BFILE column between tables

texts:
I have a table name mytable in Oracle 9i.

columns of mytable are

id number(9,0)
C1 LONG RAW,
C2 VARCHAR2(256),
C3 BLOB,
C4 BFILE

when I try to do this:

create this_other_table as
select * from mytable;

I get this:

ORA-00997: illegal use of LONG datatype

I guess the rest of columns that are of some kind of binary data (BLOB, AND BFILE, besides LONG RAW) will yield errors also.

Would someone please shed some light on how to do this?


texts: Is there replication by "pushing" instead of "pulling"?

texts:
Here is the situation:

pg 9.4
pg instance "A" in intranet which is often loosing connection with
WAN because of satellite problems (primary). This server has dynamic public ip.
pg instance "B" accessible from WAN (standby) and has static public ip.

I would like "A" to replicate all changes to "B". Is it possible? Standard streaming replication works by standby server connecting to primary. But in my case it won't be possible because it is using dynamic ip.

Replication is needed for availability. When instance A is down, users would access instance B.

Users can't use instance B as primary server because connection to WAN is not reliable and slow.

What options are there?


texts: Properly pivot an XML column with XQuery

texts:
I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and rows format.
To get started, this is what the XML column looks like in SQL:
<Model xmlns:dsc="ModelDescriptive" xmlns:draw="ModelDraw" xmlns:display="Display" xmlns:thd="Model3D">
  <dsc:Model name="15DE">
    <dsc:Defs>
      <dsc:Materials>
        <dsc:Material ref="E-HD-AnoAno" baseRef="E-HD" type="rod" productionGroup="OPTION" />
        <dsc:Material ref="E-3L44A-CLEAR" baseRef="E-3L44A" type="piece" productionGroup="OPTION" />
        <dsc:Material ref="R-TH01" baseRef="R-TH-TH" type="rod" productionGroup="ASSEMBLY" />
        <dsc:Material ref="M-TH05" baseRef="M-TH" type="meter" productionGroup="MOLDING" />
      </dsc:Materials>
    </dsc:Defs>
  </dsc:Model>
</Model>

…and here is my query :
 SELECT
      x.y.value('local-name(..)', 'VARCHAR(MAX)') ParentElementName
     ,x.y.value('local-name(.)', 'VARCHAR(MAX)') Attribut
     ,x.y.value('.', 'VARCHAR(MAX)') Value
     ,Row_number() Over(Partition by x.y.value('local-name(..)', 'VARCHAR(MAX)') order by x.y.value('local-name(..)', 'VARCHAR(MAX)') ASC)  as rn
 FROM @xmlDescriptive.nodes('//*[text()], //@*') AS x(y)
 WHERE x.y.value('local-name(..)', 'VARCHAR(MAX)')= 'Material'
 ORDER BY x.y.value('local-name(..)', 'VARCHAR(MAX)')

I get an interesting result using the Nodes method of XQuery and filtering only on the material from the bill of material (BOM).
However, I would like to present the data in a standardized fashion.
The first result I get looks like this:

Then I transform this result to rotate it using the Pivot method. Here is my method of trying to pivot my information:
 SELECT [ref], [baseRef], [type], [productionGroup]
 FROM
 (
     SELECT
          x.y.value('local-name(..)', 'VARCHAR(MAX)') ParentElementName
         ,x.y.value('local-name(.)', 'VARCHAR(MAX)') Attribut
         ,x.y.value('.', 'VARCHAR(MAX)') Valeur
         ,CAST(Row_number() Over(Partition by x.y.value('local-name(.)', 'VARCHAR(MAX)') order by x.y.value('local-name(..)', 'VARCHAR(MAX)') ASC) as varchar(10)) as rn
     FROM @xmlDescriptive.nodes('//@*') AS x(y)
     WHERE x.y.value('local-name(..)', 'VARCHAR(MAX)')= 'Material'

 ) d
 pivot
 (
   max(Valeur)
   FOR Attribut in ([ref], [baseRef], [type], [productionGroup])
 ) piv

And I find my result in are unordered. The column ref does not match my baseRef, type and productGroup columns:

I am a little confused. I would like to have my result formatted this way but ordered. What is wrong with my reasoning?
I would like my result to be presented like this:


texts: Start up replication after AlwaysOn failover

texts:
I have a 4 node AlwaysOn configuration with 2 nodes at the main site, 2 at the DR site and a FSW. The 2 main site nodes are configured for Automatic/Synchronous fail-over, the 2 DR nodes are Manual/Asynchronous and transnational replication from one DB is configured to a remote subscriber.  I configured my replication following this MSDN article and tested that replication works fine:

Configure Replication for Always On Availability Groups (SQL Server)

My issue is with fail-over to the DR site to simulate the 2 primary site nodes being unavailable but not completely lost. The 2 DR nodes come online and I can resume data movement between them without issue. Changes to the configured publication DB also synchronize between the 2 DR nodes but nothing is delivered via replication to the subscriber.

I'm looking for a suggestion to get replication working without ejecting the 2 primary site nodes from the Availability Group. The intent of the exercise would be to fail back to either of the main site nodes when they are available again, but not delay replication while the primary site nodes are offline.


texts: DB replication and recovery of a previous version in mysql

texts:
I am trying to get my head around how to have a simple recovery solution for mysql databases in case of accidental deletion.

Let's just say for argument's sake that you are an admin of a 800 gig database and one day you or someone accidentally deletes a table or a hundred rows. Here are two potential solutions I thought of, and the problems I see with them.

1) Database sync is enabled with a replication server. Meaning you can restore the data from your replication slave server by doing a dump of the table and then restoring it on the main server?

Problem: if you delete the table on the master server, this would get replicated on the slave, meaning you lose the table in both, am I right?

2) You enable the binary log and restore somehow using this.

Problem: My understanding of the recoveries with the binary log, is that first you have to restore a full backup from the previous day, and then use the binary log to rebase the changes back to the point previously before you deleted the table or rows. I think this is a huge problem if your DB is 800 gigs, because restoration could take a whole day, and that would be a whole day without the server running - not feasible.

What I want is a simple and reliable solution to restore to a previous version at a specific date and set the servers accordingly so in case something like this were to happen, recovery is simple and doesnt take an entire day or hours. Is it not possible to use the binary log to roll back without doing a full backup restore? Are there any other solutions to this problem?


texts:
I've been working on giving a development team the ability to have read-only access to a SQL environment, I'm at the last step. I need them to be able to see users/logins and roles. I noticed that with view defintion granted on any given DB, it allows their login to view the users/roles for each DB, however even granted on master/msdb/model it does not allow the login to view the server wide logins/roles. What would be the best way to accomplish this? I have tried view defintion and I have tried view server state, neither has worked for server logins to be visible to the user.

Note: I don't want them to have any more access beyond that so I don't want them to be assigned a predefined role.


texts: Rank function creating duplicate partitions? (two rank 1's for the same key)

texts:
The below query is outputting two rows. Given that I am partitioning on ref_number and also have ref_number in the where clause, I believe I should only ever get a single row back.

WITH rank_cte AS (SELECT  ref_number, RANK()
        OVER(
            PARTITION BY
                ref_number
            ORDER BY
                Logged_Date DESC
        ) AS "Rank1"
From my_table )

SELECT * FROM rank_cte
WHERE Rank1= 1 and ref_number = 'abcd'

What is likely to have caused this? SQL Server must think they are similar enough for the where clause to return both rows, but different enough to partition them.


texts: How to completely remove EnterpriseDB Installation of Postgresql on Ubuntu?

texts:
All,

I'm trying to install/run PostgreSQL 9.3, but am receiving a multitude of errors which seem to point to a failed installation of postgresql from the enterpriseDB package.

For example, trying to start the server with this command:

sudo /etc/init.d/postgresql-9.3 restart (or start)

...yields the following:

Restarting PostgreSQL 9.3:
-su: /opt/PostgreSQL/9.3/bin/pg_ctl: No such file or directory
PostgreSQL 9.3 did not start in a timely fashion, please see /opt/PostgreSQL/9.3/data/pg_log/startup.log for details

Except there is no /opt/PostgreSQL/ folder, and I've been told that creation of that was attempted during the EnterpriseDB installation...why the start command is trying to go there, I do not know!

I've started another thread which gave me a very temporary solution, but that doesn't seem to help after I do a restart of my machine.

So the question, how do I completely remove the EnterpriseDB failed installation?

I've tried these commands, but the issue still lingers:

sudo apt-get autoremove postgresql-9.3-postgis pgadmin3
sudo apt-get --purge remove postgresql\*

Any thoughts or recommendations are welcome!

Thank you!

-m


texts: Restoring system databases on Availability group replicas

texts:
I'll start this off by saying I'm very new to availability groups.

Looking at information on availability groups, it looks like you can't add system databases to a group. MSDN seems to indicate that we should manually replicate logins, jobs, etc.

Are there any consequences of restoring system databases from backups taken on a primary replica to a secondary replica in an availability group?


texts: Read concern level of majority error while upgrading Sharded Cluster from 3.2 to 3.4

texts:
I have upgraded MongodDB sharded cluster having two replica sets from 3.2 to 3.4. Current storage engine is MMAPv1. After successfully upgrading all the secondary, primary, config server and mongos to 3.4, when I run config server using following command.

sudo mongod --configsvr

I keep getting following Error.

  SHARDING [shard registry reload] Periodic reload of shard registry failed :: caused by :: 115 could not get updated shard list from config server due to Current storage engine does not support majority readConcerns; will retry after 30s

And also I am unable to connect mongos with config server. When I try to connect it using following command

sudo mongos --configdb [ip-of-my-config-server]:27019

It gives me following error.

  BadValue: configdb supports only replica set connection string

I suppose mongos is unable to connect to config server due to the majority readConcerns error on config server.

MongoDB manual says "When reading from the replica set config servers, MongoDB 3.4 uses a Read Concern level of "majority"."

And to use a read concern level of "majority", WiredTiger must be used as storage engine.

So it seems I have to switch to WiredTiger storage engine to make it work. But when I was going to switch to WiredTiger storage engine of a secondary replica set member, according to manual

"This procedure completely removes a secondary replica set member’s data"

So I am stuck halfway. Situation is

Config server is giving error regarding majority readConcerns.
I have to switch to WiredTiger to get rid of it.
Switching to WiredTiger will remove data from secondary members.
Data will not be replicated back to secondary members during this switching to WiredTiger procedure because of config server error and eventually I will be ended up losing all the data (Please correct if I am wrong).

Questions are:

Can I make MongoDB 3.4 to use a Read Concern level of "local" when reading from the replica set config servers?
How can I switch to WiredTiger engine without losing data in my scenario?


texts: Is filling up plan cache causes a decrease in space allocated for data cache?

texts:
SQL Server uses allocated server memory for different kind of purposes. Two of them are plan cache and data cache which are used to store execution plans and actual data correspondingly.

My question: Do these two caches have different allocated space section in Buffer pool, or in contrary, they have just one section in Buffer pool which they share between each other?

In other words, if plan cache is filling up, does space for data cache is reducing as well?


texts: Mysql 5.6 Replication Lag fluctuating between 0 and n

texts:
I have one master and 7 slaves. During high load on my master, I see replication lag and it keeps fluctuating between 0 and n (where n keeps increasing with time and I have seen n grow more than 1 hour). Fluctuations happen in a matter of seconds i.e. sec:1 - Lag:0s, sec:2 - Lag:2000s, sec:3 - Lag:0s, sec:4 - Lag:2002s,

When seconds_behind_master is 0; show slave status\G says: "Slave has read all relay log; waiting for the slave I/O thread to update it".
When seconds_behind_master is n; show slave status\G says: either "Reading event from the relay log" or "System Lock".
On Master "show processlist" tells the replication thread has status "Sending binlog event to slave" always.

With the above points, I have figured that my SQL thread is not lagging and it's the IO thread which is the culprit. I read that network slowness can cause this issue, but network is not a bottleneck, as I have verified the bandwidth used between master and slaves is only 50%. When I turned on slave_compress_protocol, network usage went down but I was still seeing the replication lag grow in a fluctuating fashion.

I want to know what can be other causes apart from network which can cause this issue. I have gone through: https://www.percona.com/blog/2013/09/16/possible-reasons-when-mysql-replication-lag-is-flapping-between-0-and-xxxxx/ and couldn't attribute my lag to any of the points mentioned in the post.

Also, when the load on master stops, replication lag stops fluctuating and starts decreasing steadily from n and finally catches up.

Thanks.

Edit:

Can it happen that due to heavy load on master (% CPU utilisation is hitting 100%), IO thread is waiting intermittently to read from the binlogs)?


texts:
On a SQL Server 2014 12.0.4237.0 system, the following errors have been logged periodically (11 times in past 66 days):

  2018-03-22 09:28:11.170 Error: 50101, Severity: 16, State: 10.
  2018-03-22 09:28:11.170 Internal Stored Procedure Failure -  ID_TransUserActionCreate - SQL Error Number 0

As yet I haven't found any reference to an error with code 50101, and Google, amazingly, returns absolutely nothing for "ID_TransUserActionCreate".

As far as "what's happening at the time this is logged," I don't know yet as there are several databases on the system and we haven't yet started collecting detailed information to answer that question.  So this question is to ask if anyone knows, or has a good idea, what this message means and/or if it is something I should be concerned with.


texts: Pointing DB to new Oracle home in primary and standby oracle database

texts:
I needed to apply a security patch to production database. To minimize the outage time, I cloned the oracle home and applied the patch to this newly created oracle home. With production DB there is a physical standby database. I cloned the oracle home in standby database too and applied the same patch.
Now , I need to point both the databases to respective new Oracle home. Guide me on how to do this when there is a physical stand by involved.

I went through multiple oracle blogs etc. the steps provided are to apply the patch simultaneously on both primary and stand by database. However , in my case i have cloned the Oracle homes in both the environments. I could not find any useful info on doing this way. Any help/hints are appreciated.


texts: Cannot browse for Backup files using SSMS

texts:
I'm facing a strange problem.
When I try to backup or restore any database using a SQL Server user (not sa) that belongs only to "public" server role, i have the following error when i click the browse button on the "Select backup destination" window:

D:\SQLServerData\Backup
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
...

The user is db_owner for the database I try to backup, and SQLServer service is running with administrator account, so it should have no problems with access to the D:\SqlServerData\ folder.

I tried to add the user to any other server role, but none worked except "sysadmin". But having the user in sysadmin server role means that the user can do anything in any database, and I do not want that.

Any idea on what's going on?


texts: DB2 - Using stored procedure variable in a "CREATE TABLE as (..)" Statement

texts:
migrating from Netezza to DB2 we face the following problem:

CREATE OR REPLACE PROCEDURE DoStuff()
BEGIN
  DECLARE vStarttime TIMESTAMP;
  SET vStarttime = now();

  CREATE TABLE foo AS (
    SELECT
    x,
    y,
    vStarttime as t_change
    FROM
    bar
  );
END

With the basic idea being that t_change should hold the time at which the procedure was called. However, this fails because apparently vStarttime "is not valid in that context". We opted to first create the table, and then call an update to fill in the timestamp.. while this works, however, it feels very hacky, and we would like to avoid the additional update statement for performance reasons. Is there a better solution?

Note: foo is actually a quite complex temporary table. I omitted most of the details to keep it as simple as possible.


texts: I work in a small company, that runs an ERP that holds about 600MB of data.  My task at hand is, to find a way to always have real time backup of the erp (Oracle 11g) that can be retrieved easily (so that the company keeps working).  Right now, the erp is hosted on one IBM Server (Windows 2008 R2), and we keep backups of the previous day.  The company that supports the erp, says "Go IBM Storage, it is awesome and we know how to do it", but my question is: is an IBM Storage device really safe? Sure, it has 2 controllers, 2 psus, disks in raid, but can i depend on it? if something happens on the device itself (not some disk, or a psu, or even the pc that will access it itself as there will be 2 of them), ain't the company out of business (without even the backups of the previous day?).  I found and proposed to the support company the Active Data Guard (Oracle software for data mirroring), but they don't use it at all (my guess is that it is cheaper than storage solution and they won't make much by selling just one pc and some software).  What would you suggest?


texts: Authentication type differences in SQL Server

texts:
I want to know what is the difference between authentication types(NONE, WINDOWS, INSTANCE) in
sys.database_principals table.
I can't find clear explanation in documentation.

Thanks


texts: Get all distinct values of specific column in all tables in a given schema (Postgres)

texts:
I have a schema in which all tables have the same basic set of columns. Now I want to query a specific column in all existing tables dynamically to get the distinct list of values of that column. The result should be a list consisting of all "table_name" and "unique_value" combinations (of that queried column in that table).
The result should be this (with the given test set below):
table_name | query_me
-------------------------
sql_test_1 | a
sql_test_1 | b
sql_test_1 | c
sql_test_2 | a
sql_test_2 | f
sql_test_2 | g

Important: I do not want to hard-code the table names I will query, I really want to query "all existing tables (with a given name scheme)".
I unfortunately have absolutely no clue how to do this in (Postgre)SQL. Can somebody help?
test case
CREATE TABLE IF NOT EXISTS public.sql_test_1 (
    id serial PRIMARY KEY,
    query_me varchar(10) NOT NULL,
    val TEXT
);
CREATE TABLE IF NOT EXISTS public.sql_test_2 (
    id serial PRIMARY KEY,
    query_me varchar(10) NOT NULL,
    val TEXT
);

INSERT
    INTO
    public.sql_test_1 (query_me, val)
VALUES
    ('a', 'hey'),
    ('b', 'ho'),
    ('c', 'silver'),
    ('a', 'let''s'),
    ('b', 'ride');

INSERT
    INTO
    public.sql_test_2 (query_me, val)
VALUES
    ('a', 'hey'),
    ('f', 'ho'),
    ('g', 'silver'),
    ('a', 'let''s'),
    ('f', 'ride');

expected result see above.


texts:
I would like to find a MYSQL Client program which can be config as FULLY
DARK THEME. Many of them can not be dark theme in displaying result in grid.


texts:
We are working on a multi-database application (read and write to multiple databases). The datamodel is identical on the databases.

We are inserting a timestamp (12 fraction digits) value in a datetime2(6) column in MSSQL however MSSQL rounds the value making it different to other databases where the extra fraction digits are ignored.

Example:

DECLARE @t TABLE(x DATETIME2(6))
INSERT @t SELECT '2017-03-28 14:00:59.4106489'
SELECT x FROM @t

Result: 2017-03-28 14:00:59.410649
Expected: 2017-03-28 14:00:59.410648

DB2 provides the expected result by throwing away the 7th fraction digit.

How can we make MSSQL not round the datetime2 value ?

EDIT

The application writes a java.sql.Timestamp object with 12 fraction digits to DB2 and MSSQL. In DB2 the column is a TIMESTAMP(6) and in MSSQL a DATETIME2(6). DB2 truncates from 12 fraction digits down to 6. MSSQL rounds down to 6.


texts:
You can display the results of EXPLAIN PLAN with

explain plan for select * from foo;
select * from table(dbms_xplan.display);

How can I similarly display the results for an AUTOTRACE in my program?


texts: How to set Persian calendar,currency,numbers,date and time and text in oracle database?

texts:
I have installed oracle database 12c on my laptop.

I want to be able to store text,numbers,calendar,currency,... in my local language (Persian) in database tables and also show contents in Persian when retrieving data from tables.

I'm also using APEX 5 that I want these capabilities in there too?

Is it possible? Does Oracle support these in both database 12c and APEX 5?
How can I set these settings and where?

I'm using Windows 8.1 enterprise x64.


texts: PostgreSQL behaviour in presence of two different type of indexes on the same column

texts:
How does PostgreSQL behave if I create two (or more) indexes of different type on the same column?

In my case I want to compare B-Tree indexes against GIN on a tsvector column.
I know that GIN are specifically there for tsvector. What's curious is that PostgreSQL doesn't complaint if I create a B-Tree index, but I see that the query planner doesn't use it. I can also create a GIN index (without removing the B-Tree index) and now the planner uses the newly created index. The column now has two indexes but only one of them is used.

What's the criteria used for selecting an index type against another, even in presence of more than two indexes? Why doesn't PostgreSQL tell me that a B-Tree index on a tsvector is useless and won't be never used by the planner?

Update
The GIN index is used only on certain check conditions like my_tsvector IS NOT NULL but (obviously) not on conditions like my_tsvector @@ '...'::tsquery.


texts: Calculate new percentage when new record is added

texts:
I have this MySQL that I am using to calculate a percentage for a segment in a call. When a new record is added, I am able to get the correct percentage for each row.

$sumGreeting = $dbcon->prepare("
    SELECT  SUM(greeting_msg + provide_agent_name + clear_and_aud +
                confirm_caller_name + confirm_caller_tel +
                ask_alt_number) AS SumGrt
    FROM  table
    GROUP BY  coach , id

");
    $sumGreeting->execute();

I want to calculate the final percentage each time a new record is added. Taking in mind the previous or existing records. At the moment I'm getting this result below:

I'm using this code:

    $initRows = 0;
    $result = 0;
    while ($row = $sumGreeting->fetch(PDO::FETCH_ASSOC))
    {
         $initRows = $row['SumGrt'];
         $result = ($initRows/12)*100;
     echo var_dump($result);
    echo $result ." <br />";
    }

The Expected results will be the final percentage of all the columns.

This section has a total of 12 points.

Thanks in advance


texts: Update remote MySQL database upon changes in local SQL Server database

texts:
I have a Web store running with MySQL on the remote host. And I have inventory management software using Microsoft SQL Server on my local system.

I want to update product stock in the web store when product stock changes in my inventory management software.

Can I do this with a SQL query?


texts: Can I revert a transaction after a certain number of days

texts:
I have a Sql script file which does a bunch of operations. I have transaction inside the script so if something happens the whole script is rolled back.

The question I have is that say the script executed successfully and while testing the application certain errors were found.

Now I want to revert all the operations that happened inside the script after say x days. This is a transactional db and I don't have to worry about any new data addition since this is just a staging environment..All I care about is to revert all the structural or data modifications that the SQL Script did by itself. Can someone please tell me what the options are for that.

Do I need to write the exact opposite statements in the rollback script for the operations that the original script did.

Example:
Main Script      -Insert Into Customers values(1,'Test','USA')
Rollback script  - delete from  Customers where id = 1 ..and so on for other transactions(I have simplified this I have over 300 operations in the SQL Script file)

Thanks

Thanks


texts: MySQL index on BIT column strange behaviour

texts:
Using MySQL 5.7

Given the following table:

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `col1` bit(1) DEFAULT b'0',
  `col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index1` (`col1`,`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

And with this rows:

INSERT INTO `test` (`id`, `col1`, `col2`)
VALUES
    (1, 0, 1),
    (2, 0, 2),
    (3, 0, 3);

query1 - I get no results for this query:

SELECT * FROM test
WHERE
col1 = '0' AND col2 IN (1, 2, 3);

Explain for this query:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table

query2 - But I do get results when I remove the single quotes for 0:

SELECT * FROM test
WHERE
col1 = 0 AND col2 IN (1, 2, 3);

Explain for this query:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  test    NULL    ref index1  index1  2   const   3   50.00   Using where; Using index

query3 - And most interesting is that I also get the expected results if I ignore the index:

SELECT * FROM test
USE INDEX()
WHERE
col1 = '0' AND col2 IN (1, 2, 3);

Explain for this query:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  test    NULL    ALL NULL    NULL    NULL    NULL    2   50.00   Using where

As far as I know there is no CAST or CONVERT to BIT, so this could explain why col1 = '0' could be a problem, but if I ignore the index then it works in both cases: col1 = '0' and col1 = 0

I know that adding an index on a BIT column would not make much sense since cardinality for this column is going to be quite low, but it could make sense when is a Multiple-Column index.

Questions:

Why I get results when I ignore the index?
Should I still declare Multiple-Column index when one of the columns is of type BIT? I would say yes, as long as you don't quote the values on the query :D


texts: SQL Server system views VERY slow at one computer

texts:
I have a weird problem and do not know how to debug or solve this issue.

We have a Microsoft SQL Server 2012 stored at a HyperV VM in the network. Clients in the same network access this SQL Server. The VM and the clients are part of a local domain. The database access is done via the ADO libaries (Delphi programs). The NT Authentification is used. Everything works fine.

One computer is in the network, but not in the domain. The whole TCP/IP stuff seems to be correctly setup. The server can be connected, and database access works fine and fast. All ADO based (Delphi) programs work fine and fast. But queries to the system tables are extremely slow and need up to 5 seconds.

The query is as simple as

SELECT * FROM sys.dm_exec_connections

or

SELECT * FROM sys.dm_exec_sessions

I have no idea what to do about this.

Update: I have done following further checks:

exec sp_WhoIsActive shows nothing during the query.
exec sp_who2 mentions "Awaiting command"
Logging in with "sa" is not changing anything.
In Delphi/ADO (outside domain) it is slow, in MS SQL Studio (outside domain) it is fast. Both tested with and without NT authentication.

I found the problem. Every time I connect to the server using the machine name (e.g. SERVER1\ABC), the system-view queries are slow, but everything else is fast. If I connect via IP-Address 10.0.0.x\ABC, then the system-view queries are fast too. That's very weird. At least I have a workaround. (Note: The SQL server is inside the domain)


texts:
The SSD I/O capacity is around 450MB. When I copy files from one folder to another I can see in the Windows Resource Monitor that the sum of read/write speed gets around 450MB. Why the same wont happen with dbeng9 or dbsrv9, while I am executing a read, transform and insert procedure?

These are the parameters:

C:\Program Files\Sybase\SQL Anywhere 9\x64\dbsrv9.exe
C:\Tributos\Dados\Asa9\Almirante\Integra.db -xtcpip -gl all -c 5000M -cl 5000M -ch 5000M -gp 8K -gb high -gt 4 -cs -n cnv_tributos

The process is not using more than 25% from each one of the 4 cores, so I don't know where is the bottleneck.

The Sybase manual informs that the per-seat license(the one i am using), is suposed to use all cores for processing requests. In adiction the "-gt 4" purpose was to force the use of 4 cores.
It looks like a default behavior as if the server process was expecting more conections or more querys to be executed, but that wont happen, only i am using the database during this process.

I need to use the maximum capacity from the machine, because I work with a ETL (Extract Transform and Load) scenario all day.
These procedures runs for hours, if i could make then run 4 times faster it would make my day.

Copying Files ->
Procedure Running ->

SSD -> Samsung SSD 840 EVO 500GB ATA Device

Sybase document reference.

Function (-gn)
Set the maximum number of active requests (both user and system) that the database server can handle concurrently.

Syntax
{ dbsrv9 | dbeng9 } -gn integer ...

Applies to
All operating systems and servers.

Description
Set the number of active user and system requests that the database server can handle concurrently. If the maximum number of active requests are already in use when the database server receives an additional request, the new request must wait until another request is completed.

Each connection uses a thread for each request, and when the request is completed the thread is returned to the pool for use by other connections. As no connection can have more than one request in progress at one time, no connection uses more than one thread at a time. An exception to this rule is if a Java application uses threads. Each thread in the Java application is a database server execution thread.

The default is 20 threads for the network database server and for the personal database server, except on Windows CE where the default is 3 threads.

On Windows NT/2000/XP, you may want investigate the Performance Monitor readings for Requests: Active and Requests: Unscheduled. If the number of active requests is always less than -gn, you can lower -gn. If the number of total requests (active + unscheduled) is often larger than -gn, then you might want to increase -gn. The Performance Monitor is not available for UNIX or Linux platforms.

Function (-gt)
Set the maximum number of requests that can be run simultaneously by the database server. This option is only useful on multiprocessor systems.

Syntax
{ dbsrv9 | dbeng9 } -gt integer ...

Applies to
All operating systems and servers except NetWare.

Description
With per-seat licensing, the network database server uses all CPUs available on the machine (the default). With CPU-based licensing, the network database server uses only the number of processors you are licensed for. In addition, the personal database server and runtime database server are both limited to a single processor.

I tried to set both parameters to 4 and to 1 , the results had no change.


texts: How can I efficiently delete records when a counter reaches zero in PostgreSQL?

texts:
Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this in PostgreSQL?
The naive way involves two SQL statements and two searches in the table: a SELECT to fetch the counter value, followed by a DELETE or an UPDATE.
One alternative involves an UPDATE … RETURNING, followed by a DELETE only if the returned value of the counter is zero.  However, this performs an unneeded record change in the case of a counter having a value of one, optimizing the uncommon case (counter has a value higher than one) at the expense of the expected common case.
Another alternative, which indeed optimizes the expected common case, involves a DELETE … WHERE … AND counter = 1, followed by an UPDATE when no deletion takes place.
Both alternatives may require a wasteful second search for the record in the table.
Can perhaps the two table searches be always avoided and the operation's efficiency increased by using a cursor? I haven't seen an example for this use case in the PostgreSQL documentation.


texts: AlwaysOn Secondary readable - can't connect with applicationintent=readonly

texts:
With an SQL AlwaysOn, I Set my readable secondary to read intent only.
When I try to connect with parameter applicationintent=readonly my connection is established with the primary server.

Here is my parameters:

Listener:  Lst_Test
Avaibility group: AG-TEST
Primary: PROD-TEST
Secondary: DRP-TEST

Here is my script for create route :

    ALTER AVAILABILITY GROUP [AG-Test]
MODIFY REPLICA ON
'PROD-TEST' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://PROD-test.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG-Ocean]
MODIFY REPLICA ON
'DRP-TEST' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://DRP-test.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG-Test]
MODIFY REPLICA ON
'PROD-TEST' WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('DRP-TEST','PROD-TEST')))

ALTER AVAILABILITY GROUP [AG-Test]
MODIFY REPLICA ON
'DRP-TEST' WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('PROD-TEST','DRP-TEST')))


texts: Postgresql: How to take incremental backup with wal-e

texts:
I am new to postgresql. I've connected to my demo database with psycopg2 and inserted data with python faker. I want to take incremental backup with the tool WAL-E and want to see how it actually works. But the few tutorials aren't helping that much as they're not for naive users.

Can someone help explaining the steps simply how to backup data with WAL-E? That would be a great help ! I'm using psql 10.4 in Ubuntu 18.04.

Thanks


texts: Memory limitations on db2 express-c version 10.1 seems stuck at 2 gig, why?

texts:
I just updated from 9.7 to 10.1, to leverage the new 4gig memory limitation.  However, my app only appears to be using 2.  So i searched for a way to find out how memory is used and found this command:

D:\DB2\scripts>db2pd -dbptnmem

Database Member 0 -- Active -- Up 0 days 04:40:54 -- Date 09/16/2012 14:37:08

Database Member Memory Controller Statistics

Controller Automatic: N
Memory Limit:         2097152 KB
Current usage:        2057664 KB
HWM usage:            2096960 KB
Cached memory:        390464 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
APPL-DOMINOS              2752        160000        1024
DBMS-DB2                109888        168832           0
FMP_RESOURCES            22528         22528           0
PRIVATE                  20480         86656        9344
DB-MYAPPLI             1901760       1901760      380096
LCL-p5088                  128           128           0
LCL-p5088                  128           128           0

The line that really stands out is

  Memory Limit:         2097152 KB

What does this mean?  Why does my app not use 4 gig?  How can I change it so that it does?  Where does one configure this?


texts: How can I show the values from my input that don't exist in a table?

texts:
Could you help me on this one?

See I have this order_table where I have to find the order numbers that don't exist in the table.

Let's say I have columns id, order_number in the table named order_table.

What I want is to show in my result all the values that don't exist in order_table.

Like for example:

I have order_number values 43454235423, 785686847856, 1243215421, 6437647, 978423454. I then do

SELECT *
FROM order_table
WHERE order_number = ('43454235423', '45423534252','785686847856', '1243215421',
                      '6437647', '978423454','5634636','4354235235')
--return order_numbers that don't exist in the table as 'status'

This is my sample query result:

order_number  status
------------  ---------
43454235423   not found
785686847856  not found
1243215421    not found
6437647       not found
978423454     not found


texts:
I have a table with two columns, Parent and Child.

I need to get the list of all descendants associated with the parent records.

Source Table
+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |
|  b     |     c |
|  c     |     d |
|  d     |     e |
|  e     |     f |
|  f     |     x |
+----+-----------+

Expected Result:
+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |  // As b is the child of a, all the descendants of b
|  a     |     c |  // are also descendants of a.
|  a     |     d |
|  a     |     e |
|  a     |     f |
|  a     |     x |
|  b     |     c |  // As c is the child of b, all the descendants of c
|  b     |     d |  // are also descendants of b.
|  b     |     e |
|  b     |     f |
|  b     |     x |
|  c     |     d |
|  c     |     e |
|  c     |     f |
|  c     |     x |
|  d     |     e |
|  d     |     f |
|  d     |     x |
|  e     |     f |
|  e     |     x |
|  f     |     x |
+----+-----------+

Any ideas how to get all the descendants records of a parent?
I have tried self join using T1.Child = T2.Parent but the logic did not work.

I am using Teiid VDB which doesn't support Recursive WITH clause.


texts: refreshing 500k rows per day, what primary key to use?

texts:
Usually I create a PK of type int/bigint for every table. But in this case I have some doubts.

I have a table which is updated many times a day, the total number of rows is around 500k, but they get refreshed few times a day.
Using update would be expensive, so for every batch of data to update I just delete the old rows (by a NC key) and bulkupload the new data.

Now my doubt is... I can just choose to have a bigint PK and forget about it, it will increase by 500k a day, and it will takes forever before to run out of values, but is there a better approach?


texts:
Are SQL Server style SARGs relevant to PostgreSQL queries in general and more specifically on table constraints?

I have a parent table with many inherited children, partitioned by a date column.
Would it be better to change the constraints from

CHECK (date_part('year', end_of_dt)::integer = 2016)

to

CHECK (end_of_dt between '2016-01-01 0:00:00' and '2016-12-31 23:59:59.999')


texts:
Is there a way to visualize which users are mebers of a role in PostgreSQL / pgAdminIII, like

role: council_stuff
members:
    Harry
    Ben
    Steve
    Melinda


texts: SQL Connector for Azure Key Vault - multiple key vaults and keys per instance

texts:
In SQL Server 2016 Enterprise Edition, is it possible to link a single instance to more than one Azure key vault / EKM provider? If so, how would I add an additional provider? Any security considerations?

Also, is it possible to encrypt each individual database with different encryption keys, or is the same encryption key used for all databases?


texts: Omitting [INTO] in the INSERT ... SELECT statement

texts:
Does the INTO keyword only makes the query more readable, or does it bring another benefits as well? The queries beneath both seem to run equivalently .

insert ignore
tbl (type, details)
values ('1', '2');

&

insert ignore
into tbl (type, details)
values ('1', '2');

http://www.sqlfiddle.com/#!2/96146de


texts: Remove all Null values from Sql table at once

texts:
I am very new to Teradata and SQL environment. I am trying to remove all the null values from sql table in teradata using
ISNULL(COLUMNNAME,0). But it gives an error that "the data type does not match the defined data type name". Also, would like to know if there is any way of removing all NULL at once rather than column wise writing the above query again and again.


texts: Do Oracle databases use Java internally?

texts: My question is fairly simple. Is Java used internally by Oracle in their DBMSs?


texts:
I have a few questions regarding MySQL server running under Windows Server 2008 R2:

How do I check the number of allowed connections?
How do I turn on query logging to see performance of queries executed?
As I understand MySQL is multithreaded, will increasing the number of allowed connections increase performance on a Quad Core system?
Will having a cluster setup increase performance?
How can I setup load shedding with multiple MySQL servers in a cluster?

Thanks


texts: Disk Space Considerations for Rebuilding a large Clustered Primary Key Index

texts:
We have a table in our database with over 200 million rows and a Clustered Primary Key Index which is over 100GB. The Primary Key is only so large because many more millions of rows have been archived from the table and the Index has not been rebuilt since. By rebuilding I'm hoping to reduce the size of the index considerably and free up a lot of space in our database.

The problem we have is the disk only has 20GB of free space and I'm worried how much will be temporarily required when rebuilding the index.

What is the best way to do this? Do I bite the bullet, rebuild the index and hope there is enough space? Or do I drop the PK and rebuild it from scratch?

Archiving Details
The archiving job is done on a datetime column and archives everything older than a year. This should run in parallel with the primary key which is an autoincrement since the datetime column is also autopopulated when a row is created.

DB, Table and Index Details
Row Count: ~212,300,000
Data space: ~109,000 MB
Index space: ~517 MB
Recovery model: Full

Data file free space: 5,991 MB / 151,000 MB
Data file drive space: 18.7 GB

Log file free space: 2275 MB / 25000 MB
Log file drive space: 79 GB

tempdb free space: 7476 MB / 19232 MB

There is only one index on the table, which is the clustered primary key:

Key Columns: 1x bigint
Sort: Ascending

We're using SQL Server 2008 R2 Standard Edition.


texts:
I have a Production server and DR server where the databases are mirrored, both on SQL Server 2012 SP1. I want to apply SP3. I can do this on a weekend and I don't want a failover.

Should these be the steps:

Pause data flow from Production to DR.
Apply SP3 to the DR server.
Apply SP3 to the Prod server.
Restart the dataflow


texts: Unable to delete Log Shipping configuration

texts:
I need some help with my SQL 2005 server.
I am trying to setup log shipping, I've successfully done so for all user-databases, except one.
From the looks of it, it once had log shipping configured until the secondary server was decommissioned before log shipping could be deleted. The old DBA deleted the old jobs.

So what's left is a database that is configured for log shipping, but the LSBackup job has been deleted.
Which means, every time I try to open the log shipping configuration wizard, I am told that the job does not exist and to delete the configuration (Seems sensible).
But I can't delete it because it is trying to connect to the secondary server... which does not exist so just times out and fails.

Any suggestions would be much appreciated!


texts:
Following this answer regarding limiting resources per query in PostgreSQL, I've been advised to set a timeout value for query execution:

SET statement_timeout TO '1min';

This is great, but sometimes I want to relax the restriction for a specific script.

Is there a way to set the timeout as a psql argument? I've tried psql --set statement_timeout=50, unsuccessfully.
Is there a way to set it from a Python script which uses psycopg2?


texts: Identity Column in Concurrency, Multithreading, Parallel Processing

texts:
Our company would like to understand the effects:

If we have single table with Primary Key Identity column, int primary key IDENTITY(1,1),
and the Application is conducting multi-threading parallel processing, trying to conducts 1000s of simultaneous inserts on one table.

Would the identity primary key column fail, with a primary key violation in any way, for example: processors are trying to input the same identity number?

Or was SQL designed to be successful even in multithreading parallel insert?

Note: IDENTITY_INSERT is OFF


texts:
Historic Reporting Server running SQL 2008 R2.

I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours.

For every table that had high fragmentation I have reduced the fill factor from the 100% default for specific indexes which has worked well. But my largest 3 tables are partitioned.

Unfortunately in SQL 2008 R2 you cannot amend the fill factor on a specific partition. The fragmentation on the active partition is 99% on several of these indexes when the job runs.

I don't want to increase all partitions as they are huge, already take 150GB of 250GB db. Does anyone have any suggestions on a workaround or do I have to wait for the next major release hoping this fairly large omission in partitioned table manitenance is corrected.


texts: Is there any fastest way to shrink LOB data in SQL Server

texts:
We have a database of total size of 3886 GB. Out of which 778 GB is free. So total 3108 GB worth of data is present.

Out of which 2458 GB is "LOB Data" and 400 GB is worth "Regular".

Since the database is hosted on AWS, so in order to save cost, we are trying to shrink data file in order to free up space. Son in order to accomodate data, we already have two drives of 2 TB each and both are almost FULL and my manager do not want to create another drive and put new files over there.

But when we tried to shrink 2 GB at a time, it was taking lot of time (saw dbcc lobcompact running in sp_who2) and we are sometimes seeing blocking as well. After googling, I came across below blog post from Paul S. Randal and he also states LOB data makes shrinking go slow.

https://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/

My concern is:

Is there any way to make shrink go faster in this case or there is no way at all?
What other people are doing in such scenarios when it comes to cloud and they want to save some cost by reducing storage size?
Is my understanding correct that shrinking files can some way save costs to organization or am I wasting my time and worsening things up? If yes, then how can I convince him?


texts: Oracle 12c Express Enterprise Manager on Centos 6.5 is not reachable

texts:
I have Centos 6.5, and installed Oracle 12c. As dbca utility says after configuring new db, EM Express should be available on https://hostname:5500/em
In fact, it is not. Neither on localhost nor by ip.
I also did SQL> EXEC dbms_xdb_config.sethttpport(5501); because DBMS_XDB.GETHTTPPORT() showed "0", and set 5500 was impossible due to the error - 5500 seemed busy.
After that I tried both ports and different host names - nothing works.

DB is up and working, here is output of the lsnrctl status(I removed listener.ora to be sure that it's not wrong, but the output is the same):

 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JAN-2015
 10:27:42

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

 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the
 LISTENER
 ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date
 18-JAN-2015 10:27:20 Uptime                    0 days 0 hr. 0 min. 21
 sec Trace Level               off Security                  ON: Local
 OS Authentication SNMP                      OFF Listener Log File
 /u01/app/oracle/diag/tnslsnr/hpsa/listener/alert/log.xml Listening
 Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hpsa)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hpsa)(PORT=5501))(Presentation=HTTP)(Session=RAW))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=hpsa)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/main/xdb_wallet))(Presentation=HTTP)(Session=RAW))
 Services Summary... Service "main" has 1 instance(s).   Instance
 "main", status READY, has 1 handler(s) for this service... Service
 "mainXDB" has 1 instance(s).   Instance "main", status READY, has 1
 handler(s) for this service... The command completed successfully

Also, here is my /etc/hosts:

 127.0.0.1  localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1     localhost localhost.localdomain
 localhost6 localhost6.localdomain6
 192.168.56.101 hpsa hpsa.hpsa hpsa.localdomain node1

hostname:

 [root@hpsa ~]# hostname

hpsa.hpsa

All that I have now for https://192.168.56.101:5501/em : browsers show that they are connected to the host and trying to retrieve data, but in few long minutes I am getting connection reset. Firefox also showed ssl_error_rx_record_too_long

What am I doing wrong?


texts:
I created the following query to list the DBCC CHECKDB LastKnownGood timestamp for all Databases on a Server.  Is there a better way to do this?

IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL
BEGIN
    DROP TABLE #Results
END

CREATE TABLE #Results (ParentObject varchar(100), Object varchar(100), Field varchar(100), Value varchar(100))
GO

INSERT INTO #Results
EXEC sp_msForEachdb @command1 = 'DBCC DBINFO (''?'') WITH TABLERESULTS'

ALTER TABLE #Results ADD ID INT IDENTITY(1, 1)
GO -- required here

SELECT r.Value as [Database], r2.Value as CheckDB_LastKnownGood
FROM #Results r
INNER JOIN #Results r2
ON r2.ID = (SELECT MIN(ID) FROM #Results WHERE Field = 'dbi_dbccLastKnownGood' AND ID > r.ID)
WHERE r.Field = 'dbi_dbname'
ORDER BY r.Value


texts:
I am having a strange problem with SQL Server Profiler. We have a .NET program running and collecting traces every hour and dumping into a central database.

In a few instances, I see that the trace is closing, but not deleting the file (in original folder) and moving to the archives folder.

EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2

The above two commands are running most of the time, but when the first command fails, it's a problem to run renamefiles.cmd.

I have to manually stop the trace and delete the files manually again and start the trace back.

Contents of renamefiles.cmd:

@ECHO OFF
if '%1' == '' goto usage
if '%2' == '' goto usage
if '%3' == '' goto usage

set fileDes= "D:\Audit\archives\%1%"
set fileSource="D:\Audit\%1%"
ECHO %fileDes%

COPY /Y /B %fileSource%  %fileDes%

if '%3' == '1'  goto dbchange
if '%3' == '2'  goto WRchange
:dbchange
ren %fileDes%  DBaudit_%2%.trc
del %fileSource%
goto done

:WRchange
ren %fileDes%  WR_DBaudit_%2%.trc
del %fileSource%
goto done

REM: How to use screen
:usage
echo.
echo Usage: MyScript Server Environment
echo file: file source to copy
echo timestamp: time stamp
echo.
echo Example: MyScript.cmd filesource timestamp tracetype
echo.
echo.
goto done

:done


texts:
is possible, is there internal procedure to moving with database files if database runs, is not detached on Sql Server 2012/2008, .... EDIT.... database is online, accesible for users, services running in Native OS
moving from one disk to another, initialized in Windows OS as HDDs
or only is possible to create a replica in another SQL instace and if done then to switch between DB files
my question is only about how to shorten the time until the instance is unavailable (detach, copy, attach, testing of consistency)


texts: Replicating a SQL Server database & Optimization

texts:
We got a database of approximately 100-120 GB on a server with 128GB ram, while the db is sitting on an 256 GB SSD drive.

The bulk of the database space is spent on the tables below:

Page_contents (page_content_id, member_id, page_id, added_on, status)
Custom_fields (page_content_id, custom_field_key, custom_field_name)

and then statistics like

messages, visits, clicks table where we store the raw data (page_content_id, timestamp, page_campaign_id) on each table per status (visits on visits table, messages on messages etc)

Those tables take up pretty much 90% of the db space.

We had a blow up in production recently due to a failure on the raid controller and we are looking to see if the database can be replicated to a second equivalent server.

Questions:

What's the best way to do that on a SQL Server 2012 database?
Should we expect performance degradation?
Is the setup described above adequate for our size you think? We expect the database to keep growing.
Is there a way to optimize the schema above that you can think of. I understand you have little information.

EDIT: To answer some of the questions in the comments

The SQL Server is 2012 Enterprise edition. I am in control of what I can do and how I use the two boxes. I can decide to drop one box and double the power on the other one. It's really up to me.

I would like to leave questions 3 & 4 in case someone can think of optimizations of the existing schema. If there are questions to answer to clarify things I l happily do so.

My requirement is for the database to be accessible at all times. I don't mind losing some data (1 hour) in terms of the analytics, but can't sustain more than 5 minutes loss of data for the other two tables. If I can increase the performance as well as prevent downtime given the requirements above I would prefer that option for obvious reasons.


texts: SQL Server Compression Estimate Is Significantly Lower When Estimating Current Compression Type

texts:
UPDATE - I originally said 'estimate significantly lower when set to 'NONE'. I'm amending that to say 'estimate significantly lower when set to [the compression in use].

When I execute the sp_estimate_data_compression_savings procedure on various tables with the 'data_compression' parameter set to 'NONE', it shows large size differentials on the non-clustered indexes.

The 'size_with_current_compression_setting' is correct and reflects the actual size of the indexes. The 'size_with_requested_compression_setting' though is much smaller.

If the request is for table/index size with compression set to its current compression type, why is it showing that the indexes would be far smaller than they actually would be if I chose that compression type?

Here's an example of what I'm seeing with real values, names changed:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'NONE';

Estimate Row Compression:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'ROW';

Index Sizes:

Current Compression Settings:

----- UPDATE: After Degragmenting

New Compression Estimates:

Just for kicks, I compressed the indexes with 'NONE':

ALTER INDEX [INDEX_2] ON dbo.FOO_TABLE REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE);

Compression estimates again:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'NONE';

Finally, I set the compression level for all 4 indexes to 'ROW' (e.g.):

ALTER TABLE dbo.FOO_TABLE REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW);

ALTER INDEX INDEX_2 ON dbo.FOO_TABLE REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW); // (And INDEX_3 & INDEX_4)

Now, the estimate for 'NONE:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'NONE';

Now, the estimate for 'ROW:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'ROW';

Checking fragmentation again:

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'[DATABASE]'),
OBJECT_ID(N'dbo.FOO_TABLE'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Here's the DDL for the table:

CREATE TABLE [dbo].[FOO_TABLE](
    [COL1] [char](6) NOT NULL,
    [COL2] [char](4) NOT NULL,
    [COL3] [datetime] NOT NULL,
    [COL4] [datetime] NOT NULL,
    [COL5] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [COL6] [int] NULL,
    [COL7] [int] NULL,
    [COL8] [char](4) NOT NULL,
    [COL9] [datetime] NULL,
    [COL10] [char](5) NULL,
    [COL11] [char](1) NOT NULL,
    [COL12] [char](1) NOT NULL,
    [COL13] [datetime] NULL,
    [COL14] [char](1) NOT NULL,
    [COL15] [char](1) NOT NULL,
    [COL16] [int] NULL,
    [COL17] [char](1) NOT NULL,
    [COL18] [datetime] NULL,
    [COL19] [char](8) NOT NULL,
    [COL20] [datetime] NOT NULL,
    [COL21] [timestamp] NULL,
 CONSTRAINT [pk_meterset_volume] PRIMARY KEY CLUSTERED
(
    [COL1] ASC,
    [COL2] ASC,
    [COL3] ASC,
    [COL4] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [INDEX_2] ON [dbo].[FOO_TABLE]
(
    [COL16] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [INDEX_3] ON [dbo].[FOO_TABLE]
(
    [COL3] ASC,
    [COL1] ASC,
    [COL9] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [INDEX_4] ON [dbo].[FOO_TABLE]
(
    [COL5] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


texts: MariaDB Remote machine Data at rest encryption

texts:
I'm work on setting up a MariaDB local instance on  laptops that will spend most of their time disconnected/unable to connect to the internet.

One of the requirements of the project is to encrypt the local instance of the data. which is hosted on a central server and replicated out to individual nodes when they reconnect to the internet.

I have set up MariaDB's Data at rest encryption using the file key management plugin  but it's a little disconcerting that the keyfile is available on the local machine. What alternatives are available; besides having the front end app encrypt the data before inserting it into the db?


texts: How to avoid Network issues for SQL Server 2012 AlwaysOn

texts:
I have SQL Server 2012 and have setup AlwaysOn for High Availability. The infrastructure is on Azure VM's. After moving to this setup, we are frequently observing Network issues which either causes failover or secondary replica getting disconnected and re-establish the connection again.

We have worked on changing the default delay and threshold values. Which did not help us much as immediately after that we did observed failover next day.

Can anyone assist me on how we can prevent network issues to affect the Always on nodes.


texts: Need script for new login creation in sybase ase

texts:
I have 470 new database accounts in SYBASE ASE

I certainly don't want to do it manually since it is definitely time consuming.

Here by I have old script which I try to add logins

Can someone help to me write this script in another way?

Data present in this file will be like as below  /tmp/addthese.txt:

i111111
i222222
i333333
i444444
i555555
i666666
i777777
i888888
i999999

SCRIPT :
source /cis1/sybase/SYBASE.sh

set passs=`cat /cis1/prod/dba/input/. ----`

isql -Usa -SPDSQTS1 <<EO_SQL
$passs

declare a varchar(10)
declare n int
declare count int
declare Iuser varchar(10)
declare date varchar(20)
declare id int

count=1

while n<427

a=sed -n 'count{p;q;}' "/tmp/addthese.txt"

select Iuser=name from syslogins where name='a'

if [Iuser!=a]
sp_addlogin a,Sybase123
go

use qts_db
go
sp_adduser a,a,eng
go
use engcomdb
go
sp_adduser a,a,eng
go
use sybasedb
go
sp_adduser a,a,eng
go
use engdb
go
sp_adduser a,a,eng
go
use msgdb
go
sp_adduser a,a,eng
go

use master
go
sp_modifylogin 'a',"authenticate with","ldap"
go

select id=suid from syslogins where name='a'
select date=crdate from syslogins where name='a'

use qts_db
go

insert into dbo.profiles(login_name,userid,security_level,delta_ts,delta_sy_user) values ('a',id,'ADMIN','date',id)
go

insert into dbo.users(userid,site_id,delta_sy_user,delta_ts,email,displayname,lastname,firstname)
values(id,'SYBASE',10407,'date','a',',  : id','', '')
go

end

count=count+1

end


texts:
I have a table which has columns:

txn_id
from_user_id
to_user_id
price

I want a query that produces two rows for each source row. The output would look like:

focal_user_id (new)
txn_id
from_user_id
to_user_id
price

The focal_user_id value is generated from from_user_id for the first row output, and generated from to_user_id for the second row.

I need to do this in SQL rather than code because then I can make use of WHERE and LIMIT, as well as do some joins based on the new focal_user_id column.


texts:
I have a table that has a field user and a field group (among others). Each user is in exactly one group, not more, not less. Whenever I query for a user, I also know his group. Would it be faster to have a multi-column index and query for both, user and group, instead of just using a single-column index and querying only for the user? If yes, what index type would I choose? My idea was, that searching for the group first and then searching for the user only in those results would be fastest, but I'm not sure if any of the index types can actually do that.

I also thought about separating users and groups by putting them in different tables but as both numbers can grow indefinitely, I couldn't think of a way to do that without having an indefinite number of tables.


texts: Query type classification of TPC-DS queries

texts:
I have asked this question on stackoverflow.com. But I have not received any answer yet. So, I am hoping to get an answer to this question here.

I am currently working on optimisation of Apache Spark, and using TPC-DS benchmark to evaluate the results. The optimisation is specifically done for static set of queries, i.e., queries that are known in advanced. I read in TPC-DS specification section 1.4.1 that there are four query classes:

Reporting queries
Ad-hoc queries
Iterative OLAP queries
Data mining queries

But it is nowhere specified which of the 99 queries fall into which class. Can anyone tell me where can I find the classification of the those queries into the given query classes?