texts: Auto-incremented ID with sequence and trigger: How to handle manual overrides (causing non-unique error)

texts:
I have a sequence and trigger that provide auto-incrementing IDs to a PROJECT_ID column:

PROJECTS
+------------+-----+
| PROJECT_ID | ... |
+------------+-----+
| LC000001   |     |
| LC000002   |     |
| LC000003   |     |
| LC000004   |     |
| LC000005   |     |
+------------+-----+

CREATE SEQUENCE SEQ_PROJECT START WITH 1 INCREMENT BY 1

CREATE OR REPLACE TRIGGER "PROJECTS_PROJECT_ID_TRIG" BEFORE INSERT ON PROJECTS
    FOR EACH ROW
    WHEN (NEW.PROJECT_ID IS NULL)
      BEGIN
       :NEW.PROJECT_ID := 'LC' ||to_char(SEQ_PROJECT.NEXTVAL,'FM000000');
      END;

Source: Trigger to get next prefixed ID: Is there a better way?

The sequence and trigger work just fine under normal circumstances.

However,  the editing environment in my application is far from normal. Users edit the data by accessing the entire table; it's almost as if the table is treated like big spreadsheet or something (which is kind of frightening when I think about it). Anyway, the scenario is called "editing in the attribute table window". Users can manually edit any column in any row, which means they can override the IDs that are generated by the sequence and trigger.

For example:

A user creates a new row.  The sequence and trigger provide an ID of LC000006.
A user then goes and manually changes LC000001 to LC000007. (I can't think of a good reason why a user would do this, but they have the ability, which means it will probably happen.)
A user creates another new row.  The sequence and trigger attempt to provide an ID of LC000007, but it throws an error:

Database Row Change: An unexpected failure occured. Underlying DBMS error [ORA-00001: unique constraint (USER1.PROJECT_ID_IDX) violated][USER1.PROJECTS]

The error is caused by the unique index on the field.
There are can't be duplicate values.

Practically speaking, I could live with the fact that my users would get an error like this (if someone on the team was unwise enough to manually override one or two PROJECT_IDs). All users need to do is try again, and the sequence/trigger just magically skips to the next number.

But of course, there's the possibility that there are 10's or 100's of IDs that are out-of-sequence (not just one or two). Believe it or not, this is a real possibility, due to the existence of a tool called the field calculator. With this in mind, getting an error message for 10's or 100's of out-of-sequence IDs doesn't seem so practical.

So, I'm curious, can I avoid this problem somehow?


texts: Modelling a database for a travelling business that involves Vehicules, Routes, Subroutes and Prices

texts:
I am working on travelling app, where users can book online vehicles to travel.

On the customer side I would like this flow:

Users have to select a from location and a to location
Enter how many seats they require
Select a travel date
Search for a ride/vehicle.

Which means, I need to search all the vehicles/rides whose available seats are equal or more than the user wants, and who has this route: from location - to location.

And on the merchant/vehicle owner side I would like this flow:

Owner/merchant selects a vehicle
Create a route by selecting a from location --> and a to location
Set price for that trip.

An owner can have many vehicles and each vehicle can take different routes and each route can have a different price. Also whether the route is back and forth.

However, please consider the example below:

A vehicle has a route from A to D and it has a price for that trip. i.e.,

A-----------------D     Price 100

But it can also have sub routes within that route. Example from A to B or B to C, e.t.c.

A-----B------------     Price 25

A-----------C------     Price 75

------B-----C------     Price 25

------B-----------D     Price 75

How can I design a database model for such a relationship between Vehicle and Routes, where a vehicle can have multiple routes and each route can have multiple sub routes so that I can search for all the vehicle routes? If it helps I am using Django. Thank you.


texts: Need to understand parallel query execution error

texts:
Today we experienced a degradation in performance on our production sql server.  Durring the time this occurred we logged several "The query processor could not start the necessary thread resources for parallel query execution" errors.  The reading that I've done suggests that this has to do with how many CPUs to use when executing a complex query.  However when I checked during the outage our CPU Utilization was only at 7%.  Is there something else this could be referring too that I haven't come across yet?  Is this a likely culprit of the performance degradation or am I chasing a red herring?

My sp_configure values for this are as follows:

name                                minimum maximum config_value run_value
cost threshold for parallelism      0       32767   5            5


texts: Select FK constraints with ON UPDATE NO ACTION

texts:
We use Postgres 9.5.

Is it possible to modify this script to show the ON UPDATE action for each foreign key reference at this table? Or get this values somehow?

 SELECT
     tc.table_schema,
     tc.constraint_name,
     tc.table_name,
     kcu.column_name,
     ccu.table_schema AS foreign_table_schema,
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name
 FROM
     information_schema.table_constraints AS tc
     JOIN information_schema.key_column_usage AS kcu
       ON tc.constraint_name = kcu.constraint_name
       AND tc.table_schema = kcu.table_schema
     JOIN information_schema.constraint_column_usage AS ccu
       ON ccu.constraint_name = tc.constraint_name
       AND ccu.table_schema = tc.table_schema
WHERE constraint_type = 'FOREIGN KEY';


texts: I created a sequence but I can't alter the increment number after that

texts:
Create sequence

CREATE SEQUENCE NEXT
INCREMENT BY 2
START WITH 1000
MINVALUE 10;

Alter sequence

alter SEQUENCE NEXT
INCREMENT BY 3
START WITH 1000
MINVALUE 10;


texts: How does Oracle Application Express connect to the database?

texts:
I'm mocking up some options for a simple, internal reporting front-end for an Oracle database.
I have Oracle XE 11R2 on a separate testing machine, and am going through a tutorial on making a simple report in Application Express. To my surprise, Application Express has magically connected to the XE database. I'm guessing this was so easy because the database and Application Express are on the same machine.
However, I'm wondering how this would work in a production/network environment. My former solution was an MS Access front-end, which used an ODBC connection to connect to the database. But I don't imagine Application Express would use an ODBC connection.
How does Oracle Application Express connect to the database in a production/network environment? Would an equivalent to an ODBC connection need to be deployed on each machine that uses the Application Express report?

Note: I'm well aware that I have no idea what I'm doing. I'm just trying to get a grasp on how this all works, so I can make an informed pitch to my I.T. department. Corrections are welcome.


texts: Should I disable autovacuum on a table while I do a bulk update?

texts:
I need to perform a simple update on all rows in a table. The table has 40-50 million rows. Dropping indexes and constraints during the UPDATE results in a massive performance improvement.

But what about autovacuum? Can autovacuum start a VACUUM or ANALYZE in the middle of an UPDATE? If so, it would be useless work that would eat up machine resources. I could disable it on the table prior to the UPDATE and then re-enable it afterwards:

ALTER TABLE my_table
SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);
-- Drop constraints, drop indexes, and disable unnecessary triggers

UPDATE my_table SET ....;

-- Restore constraints, indexes, and triggers
ALTER TABLE my_table
SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);

Does this even work if I don't commit after the first ALTER?

Also, if I disable it during the UPDATE, will it trigger after the update, or will it ignore those updates because it was disabled during them? (My suspicion is that it will run, but I'd rather be sure.)

I'm using PG 9.3 right now, but should be upgrading soon. So any mention of changes in newer versions is appreciated.


texts: Is there a recommended order to create a DV model and star schema?

texts:
I want to create and implement a Data Vault Model. In the presentation layer, I want to create a View based on a star schema. Now I would like to know if there is a certain order in creating the models in practice?
(Is is a commonplace to create first a Data Vault model and then the star schema, or vice versa?) I noticed that I think very "dimensional" when start to make a concept of the requirements.


texts: Help with indexing narrow table with many millions of rows

texts:
I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ million rows. I know this gets asked a lot, but I haven't found an answer that matches exactly what I'm trying to do.

The table

CREATE TABLE [dbo].[Trades]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Type] [int] NOT NULL,
    [Amount] [decimal](18, 2) NOT NULL,
    [Price] [decimal](18, 2) NOT NULL,
    [Exchange_Id] [int] NOT NULL
)

As you can see, this is a table that stores commodity trades. Because of that, there are currently 100+ million rows in the table, with about 10,000 rows being added daily (at the end of the day).

I'm at a bit of a loss as to how to index this table to maximize performance. I don't so much care about insert performance since the table is updated once a day and it doesn't need to be a fast operation. Rows are also never updated once inserted. The big problem is query speed. This table is queried relatively often.

By far the most common queries are on the [TimeStamp] column alone (eg, TimeStamp between two dates), or on the [TimeStamp] column together with the [Exchange_Id] column (eg, trades on a certain Exchange between two [TimeStamp]s).

Currently, there is a unique clustered index on the [Id] column, but I'm not sure if that's the right thing to do based on the circumstances. I've considered making the clustered index on the [TimeStamp] column, but it would have to be non-unique which I've read is generally bad. As for non-clustered indexes, I'm not quite sure how to arrange them or on what columns.

Any help is greatly appreciated.


texts: SQL Server 2012 Intellisense Support for Extended Events

texts:
Using SQL Server 2012 with SP1 for some reason when try to use extended events programmatically using T-SQL. I am not able to see intellisense working? Just trying to check is this something on my end or MS has yet to support intellisense for extended events and few other things.

Not able to find anything specific for extended events intellinsense support in SQL Server BOL

For e.g. creating a new event session no events are popping up in intellisense for SQL Server? I know this was the case was SQL Server 2008 where intellisense was first introduced.


texts: Update function to enter random/unique GUID per row

texts:
I am new to SQL and am trying to do the following.

i am using SQLite browser to edit the current file I have

I do have a SQLite database with around 1500 rows of data that I would like to create a GUID for.

I created the Col with the name GUID and has a default value of NULL.

My update script looks like:

UPDATE "main"."MyLab" SET guid = (select hex( randomblob(4)) || '-' || hex( randomblob(2))
             || '-' || '4' || substr( hex( randomblob(2)), 2) || '-'
             || substr('AB89', 1 + (abs(random()) % 4) , 1)  ||
             substr(hex(randomblob(2)), 2) || '-' || hex(randomblob(6)) )

This gives the same value to all 1,500 rows. I want a unique value for each row.


texts:
I am still new to database administration and I'm trying to optimise a search query.

I had a query which looked like this and took 5-15 seconds to execute in some cases, and also was causing 100% CPU usage:

DECLARE @point geography;
SET @point = geography::STPointFromText('POINT(3.3109015 6.648294)', 4326);

SELECT TOP (1)
     [Result].[PointId] AS [PointId],
     [Result].[PointName] AS [PointName],
     [Result].[LegendTypeId] AS [LegendTypeId],
     [Result].[GeoPoint] AS [GeoPoint]
FROM (
    SELECT
        [Extent1].[GeoPoint].STDistance(@point) AS distance,
        [Extent1].[PointId] AS [PointId],
        [Extent1].[PointName] AS [PointName],
        [Extent1].[LegendTypeId] AS [LegendTypeId],
        [Extent1].[GeoPoint] AS [GeoPoint]
    FROM [dbo].[GeographyPoint] AS [Extent1]
    WHERE 18 = [Extent1].[LegendTypeId]
)  AS [Result]
ORDER By [Result].distance ASC

This table has one clustered index on the PK, and one spatial Index on the geography type column.

So when I executed the above query, it was performing a scan operation.

So I created a non-clustered index on the LegendTypeId column:

CREATE NONCLUSTERED INDEX [GeographyPoint_LegendType_NonClustered] ON [dbo].[GeographyPoint]
(
    [LegendTypeId] ASC
)
INCLUDE (   [PointId],
    [PointName],
    [GeoPoint])
    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]
GO

and changed the query to:

DECLARE @point geography;
SET @point = geography::STPointFromText('POINT({0} {1})', 4326);

 SELECT TOP (1)
     [GeoPoint].STDistance(@point) AS distance,
     [PointId],
     [PointName],
     [LegendTypeId],
     [GeoPoint]
     FROM [GeographyPoint]
 WHERE 18 = [LegendTypeId]
 ORDER By distance ASC

And now SQL Server performs a seek instead of the scan:

which in my view has increased the efficiency of the query, but when I deploy this to production, I still get the same results (high CPU usage, and average of 10 seconds to execute the query).

  Note: No data is inserted, updated or removed from this table – only
  search/read.

Is it something I am doing wrong?
How can I fix this?

EDIT

Index Seak details

EDIT 2:

I changed the query, to use the method: 'Nearest Neighbor' from link: https://msdn.microsoft.com/en-us/library/ff929109.aspx, and now this is the result, This Query is also taking 3-5 seconds for the search - similar to the Second Query, (but not tested on Production)

Spatial Index settings:

CREATE SPATIAL INDEX [SPATIAL_Point] ON [dbo].[GeographyPoint]
(
[GeoPoint]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, 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]
GO

EDIT 3
I followed instructions by @MickyT, dropped the Index on [LegendTypeId],and executed the following query:

DECLARE @point geography;
SET @point = geography::STPointFromText('POINT(3.3109 6.6482)', 4326);

SELECT TOP (1)

    [PointId],
    [PointName],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint] WITH(INDEX(SPATIAL_Point))
WHERE
   [GeoPoint].STDistance(@point) IS NOT NULL AND
    18 = [LegendTypeId]
ORDER By [GeoPoint].STDistance(@point) ASC
OPTION(MAXDOP 1)

Statistics for this query are

And then I executed this Query again:

DECLARE @point geography;
SET @point = geography::STPointFromText('POINT(3.3109 6.6482)', 4326);

 SELECT TOP (1)
     [GeoPoint].STDistance(@point) AS distance,
     [PointId],
     [PointName],
     [LegendTypeId],
     [GeoPoint]
     FROM [GeographyPoint] --WITH(INDEX(SPATIAL_Point))
 WHERE 18 = [LegendTypeId]
 ORDER By distance ASC

Statistics for this query are


texts: Installing OE (Order Entry) schema in Oracle Express

texts: I'd like to know if it's possible to install OE (Order Entry) schema in Oracle Express. If so, how can I do that?


texts: MySQL: reducing ibdata file size for MyISAM tables

texts:
My Question is actually very similar to this one and also includes a good answer for a case with InnoDB Engine tables:

What is the best way to reduce the size of ibdata in mysql?,

I have noticed that drop schema do not shrink ibdata files , so i have looked for a methods to configure the DB so that the size will be reduced after deleting a schema.

i have found many links talking about InnoDB and the way to save table per file so that the .frm file it self will contain the table data and it will be reduced.

But what happens with MyISAM tables (with more than 5G table size).


texts: MySQL / PostgreSQL: how to call a script when a row inserted / updated

texts:
Let's assume, I have two DBs, either MySQL or PostgreSQL (sorry, but we have not yet decided what DBMS to use).

Every second multiple rows are being inserted or updated on the first DB. For each inserted/updated row I want to call a Python script, which connects to another DB on another host, makes some queries there; if everything is fine the script writes these rows (which have been inserted or updated) to the second DB.

After some research I see the following options to call the script:

Use triggers and do everything in SQL (it looks like the connection to another DB should be not a problem for PostgreSQL, but may be a problem for MySQL if not using federated storage engine)
Use UDF (user-defined functions)

I also read a bit about different patterns of architecting databases, like Saga or publish/subscribe, but didn't find any concrete practical examples of how to do this.

Two of my questions are:

Are there any other possibilities to implement the wanted solution?
The two options I mentioned (triggers and UDF): do they have any hidden traps? Which option is better in terms of performance?


texts: Can we track data changes on all SQL Server tables based on primary-foreign key relationships?

texts:
Let's say I have a Customers table with primary key as Customer_ID and 10 more SQL Server tables with Customer_ID as foreign key. Without keeping triggers on all 11 tables, is there a way to track data changes based on Customer_ID column?

The scenario is more like, if there is a change in data on any of those 11 tables, I need to collect data from all 11 tables and send it over to one of our services and I am looking at efficient solution for doing that without triggers on every table. We are using SQL Server 2012.


texts: Intermittent errors sending email from SQL Server Database Mail

texts:
I've been seeing some SMTP errors in my database mail logs.

So I have a set up a test SQL Agent job that runs every ten minutes (it's only action is to SELECT NULL). It is set to send email notifications whenever the job completes. The addresses are internal email addresses. I have configured database mail to retry failed emails up to 4 times.

In the last day, almost half the emails encounter an error at least once but eventually get delivered. 15 of the emails failed completely. A little more than half get sent without any error at all.

Here's a list of each job execution since midnight, along with the number of errors encountered by each attempt to send mail: http://screencast.com/t/wMqolB7b
(note: there are gaps in the mailitem_id sequence because we have other jobs that also send emails).

The errors are a mix of the following two messages:

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2016-03-08T05:41:21). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 192.168.60.26:25). )

-or-

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2016-03-08T05:42:42). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

I see no pattern for the errored or failed emails. The emails themselves are all identical... it is only the time of the notification that changes.
I have verified the SMTP account settings and they are correct. The
same SMTP account settings are set on two other servers, along with a
similar test job. None of the emails on these other servers are failing at
all.
I see no evidence of problems on the Exchange server
I see no useful related info in the server's event logs

BIG QUESTION: Why are these emails failing?

Smaller question: What steps can I take to further troubleshoot this to ultimately answer the big question?


texts: Oracle: Can a tablespace stay in backup mode forever?

texts:
Recently I discovered in a legacy Oracle 8.1.7.3.0 database that the tablespace holding de AUD$ table has been in backup mode for six months. Dunno who issued the begin backup command on it six moths ago.

The scheduled backups are cold backups that will not issue a end backup command on any tablespace. The database can be shutdown and started up with no problemo.
The datafiles belonging to the AUD$ tables have a modification date of just right now.
When you query the AUD$ table you can see that it's being updated almost every minute. This table is inserted data continously.
I understand that REDO information is generated when a datafile is in active backup mode but that tablespace has been un backup mode for six months for Pete's sake.

My questions:

Can this tablespace cotinue in active backup mode forever with no consequences?
Is there a risk of losing data?
Is this a disaster waiting to happen?
What exactly is happening here?


texts: Restoring SQL DB from down SQL Server (on domain) to existing SQL server (not on domain)

texts:
If and when my SQL server goes down and takes too long to get back up, can I restore my DB on another SQL server and run my software from there (I can point which SQL server to run from)?

Will all metadata and indexes and foreign keys be restored too?

Also my current SQL server is on our domain, the other one I have is on its own workgroup... Would it still be possible? Or would I need to join it to the domain first?


texts: Postgresql - Can we create streaming and logical replication replication from same server

texts:
I have a postgresql setup with 1 master and 3 slaves. Now I want to setup a replication for only one database to reporting server.
My problem statements:

I can't use bucardo (no approval for creating triggers on prod
tables)
pglogical only works on Master node.(the publisher).
I want  to replicate from slave (this is my end goal)


texts: SSMS fail to connect localhost SQL Server 2008 R2 suddenly

texts:
Until today when accessed by remote desktop to my sql server could connect using localhost, but suddenly I cant (without any update or change in my server), now I can connect using localhost\instance but cant using localhost.

This is the error, and also it affects connections to several linked servers in this machine

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

Checkpoints:

Firewall - disabled
Allow remote connections - yes
TCP connection allowed - yes
Server running - yes
User running services - domain administrator

Also in my computer I can access to this server using SSMS and only the IP.

In our development environment the user running sql server services is local administrator and everything go fine, but our network team doesn't change anything in our domain that affect administrator user.

SO: Windows Server 2008 R2 Datacenter SP1


texts: What is the impact of a large number of boolean columns in a table on storage and index performance in PostgreSQL?

texts:
I have a lot of boolean attribute for entities in a table and am in need to store even more of them. Currently I have 17 such attributes (Other fields are four bigint fields). I am in need of adding about dozen or so more attributes.

Should I just go ahead and create boolean columns for each of the required attributes or are there better ways? How would that impact storage?

These fields will be searched over on in multiple combinations (so multi-column indexes are not going to help).

Thought (premature) about saving storage: How about I use a 64-bit integer and use each bit as a boolean value? I can use the bit operators for matching but will such operation use indexes since scan is not based on ordering or equality of integers?


texts: SQL Server storage - evaluate the number and types of read write operations for the existing deployment

texts:
Training for SQL Server, one of the questions in Learning material says:
Before migrating on-premise SQL Server deployment (single server) to Azure VM, you need to evaluate "number and types of read/write operations" of the existing deployment
Which tool should you use ?
Options (pick correct one)

SQL Server Profiler trace using TSQL template
SQLIOSim
Database Engine Tuning Advisor
SQL Disk Usage Standard Report

Best probably would have been "Performance Monitor", but its not in the list
I think correct one is 4) "SQL Disk Usage Standard Report", but Learning Material says that correct  answer is 2) "SQLIOSim"
But, In my opinion, SQLIOSim is for testing the disk subsystem, it is not for measuring existing SQL Server instance metrics
Who is right - me, Learning material, or you (your option) ?
And what they mean by "types" of read/write operations ?


texts: Analyze needed after a refresh materialized view?

texts:
Are we supposed to run ANALYZE myview; on a PostgreSQL 9.6+ materialized view after running a REFRESH MATERIALIZED VIEW CONCURRENTLY myview;?

Or is it useless (maybe index statistics are already updated on refresh?)


texts: In-place up-gradation of SQL server on Azure with pre-image installed.

texts:
We have migrated our SQL server 2012  infrastructure from on-premise to Azure cloud recently where in all the production VM's are build with pre-images of SQL 2012 enterprise edition.

So,here is the question and issue that we are currently facing :

We want to upgrade SQL server from 2012 to 2016 and wanted to know if there is a way to perform in-place upgrade with pre-images installed without having to  actually migrate ? .

What we heard from our infrastructure team is that we can't do an in-place
up-gradation if it comes as a pre-image and will need to spin of another VM and perform migration which would incur cost again.

Any suggestions or workarounds without having to put migration efforts are much appreciated and thank you for all the help that you render to folks like me and to SQL community.


texts:
I'm trying to improve the below code.  The unique identifier in this code is result_label_seq, but I still need to make sure that it's uniquely identified with facility_id and service_code.  The display_seq shows in what order that this is printed on screen, basically in which row.  Row 1, 2, 3, 4....this can go all the way up to 20.

What I'm wanting to do is assign the display_seq + 1 for each entry of the result_label_seq that I have. This is currently how I'm having to write it.

 SELECT * FROM anc_result_seqs WHERE service_code = 'CBC' AND result_label_seq ='87484' AND facility_id = C'

 UPDATE anc_result_seqs SET display_seq = '1' WHERE service_code = 'CBC' AND result_label_seq = '87484' AND facility_id = 'C'
 UPDATE anc_result_seqs SET display_seq = '2' WHERE service_code = 'CBC' AND result_label_seq = '87485' AND facility_id = 'C'
 UPDATE anc_result_seqs SET display_seq = '3' WHERE service_code = 'CBC' AND result_label_seq = '87560' AND facility_id = 'C'
 UPDATE anc_result_seqs SET display_seq = '4' WHERE service_code = 'CBC' AND result_label_seq = '87579' AND facility_id = 'C'

 .....(continued)

Is there a way to do this in Oracle SQL?


texts: Usage of ApplicationIntent Parameter in SSIS connections

texts:
I ran into a puzzling configuration anomaly with a SSIS package establishing a OLEDB connection to a SQL Server 2012 availability group. The DBAs administering the AG requested that all developers target a secondary node by specifying the AG listener name and the DB part of the AG, and using ApplicationIntent=ReadOnly as an additional parameter in their connection strings. The routing to the secondary works with sqlcmd using the -K switch but SSIS packages were still being routed to the primary. An example of the connection string they requested is below. This was utilized as a project level config item in the SSISDB catalog.

Provider=SQLNCLI11.1;Server=xxx.xxxx.xxxxx.xxx.com;UID=YourNameHere;Database=AGDB;ApplicationIntent=ReadOnly;Application Name=SomethingForProfilerAndXEToFilterOn

During debugging, the OLEDB connection was created from scratch using the SQL Server Data Tools GUI. The notable difference in the connection string was the the addition of a space to have parameter now read Application Intent=ReadOnly. Connections to the secondary are now successful.

It appears that the OLEDB driver accepts both variations but only the one with the space is implemented properly with SSIS packages. Is this an anomaly with SSIS or with the SQL Server Native client/OLEDB driver?


texts: Creating and dropping partition in PostgreSQL "on the fly"?

texts:
In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative partitioning for largest tables. Tables partitioned by forecast identifier. Partition creating and dropping is made "on the fly" when users create or delete forecasts. However, I doubt if creating partitions in this way is good idea.
EDITED. We do not use creating partition by BEFORE INSERT trigger. We create or drop section on backend after user start creating or deleting forecast on frontend of our web-application.
EDITED 2. Backend in our web-application is web-server, that works with PostgreSQL 12 database.


texts: JSON in MySQL : get data from an object within a range of keys

texts:
What I have:

A mysql table, say table1
table1 contains two columns viz. id and data
id is int but the twist is data is JSON type and its keys are comparable
the table1 contains only one row (for the sake of this question)

table1

id
data

1
{'1': 'content1', '2': 'content2',    '3':'content3',.......,'10000':'content10000' }

What I want to have:
I want a query such that it returns me key-value pairs within a range of keys, say, 100 to 200.
What I'm getting on searching on internet:
Everywhere I got only the answers where one can get rows which have the values within a range, but here the case is I want values of keys within a range.


texts: Scheduling jobs on a SQL Always On HA cluster

texts: When scheduling jobs using SQL Server Agent for AOHA cluster, does one use a Master-Target config as you would as if the servers were separate?  Or is there a better way to distribute the jobs to all of the servers so that they can be picked up by one of the replicas in case the primary server fails?


texts: Import a large MySQL dump 68GB on RDS instance

texts:
I tried to import a large MySQL dump.sql (68GB) on RDS AWS instance MySQL 8.0 (db.t3.2xlarge: 32GB RAM).

The import takes more than six hours:

More than 500 tables
I have 2 large tables: the first one 57GB, the second 21GB
InnoDB for all tables
The commands used are the following:

mysql -u root -p{db_pwd} -h {db_host} -f -e "\

SET foreign_key_checks=0; SET unique_checks=0; SET autocommit=0; \

source /tmp/dump.sql; \

SET foreign_key_checks=1; SET unique_checks=1; SET autocommit=1;"

How can I optimize my import with removing indexes, or by means of other strategy, on these large tables?

I tried with:

   innodb_flush_log_at_trx_commit = 0
    innodb_autoinc_lock_mode=2
    innodb_write_io_threads = 16
    innodb_log_file_size = 1073741824 (1 Go)
    innodb_log_buffer_size = 8388608  (8 Mo)
    innodb_buffer_pool_size = 3/4 RAM

but apparently no impact.


texts: Exporting Images from Sql server database into local drive

texts:
My comand:
EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -N'

above query run and successfully download the file but images preview not shown
post downloaded that file size is 1 kb only


texts: Remote connection to the instance with window account and NTLM is failing

texts:
I have the windows server with 4 SQL server 2012 instances
This is the test server. SPNs are not registered for any of those instances.

I can successfully connect remotely to 3 of those instances using my windows account, however for one of it I got following error:
The target principal name is incorrect.  Cannot generate SSPI context.

SQL Server login connections are working ok.

We've tried registering/de-registering the SPNs, also created new MSA account for that instance.

From what I've read - when the SPNs are not registered - the NTLM should be used. NTLM is working correctly for remote SQL Server logins.

What else can be checked? Is there any other option forcing this instance to try to use Kerberos connection?

This is the SQL Server 2012 patched to the 11.0.7462.6.


texts: Is it possible to edit data inside an unencrypted SQL Server backup file (*.bak) without SSMS?

texts:
I have found out that there is a difference error (Remaining Qty <> (Purchase - Sales)) in one of the items in my database. There are more than 5000 items and more than 100,000 transactions has happened up to now. Same procedure runs every time for this calculation.
But only a single item has an error and it has happened only one time. It does not seem to be happening from the application side since it has run for more than 100,000 times. The database server is not physically protected and does not connect to the Internet. Only I can log in to the server. No exceptions has happened in the procedure since I log the errors.  I do not understand how the above error happened.
I have seen that it's possible to open unencrypted SQL Server backup files using Notepad and read its data. Is the following scenario possible or not?

Boot the server using a live CD and copy the database .mdf file
Edit and change the data of it (Example: some numeric values)
Replace the original .mdf file on the server with a hacked database

If the above scenario is not possible, is there a chance this has happened due to an error from the SQL Server side or through communication error (not from the application code side).


texts: Role to grant a DBA who should not access confidential Database

texts:
I am a junior DBA and I am in a bit of a pickle. I need to grant elevated privileges to our new DBA but he hasn't signed consent forms yet so I cannot grant him access to a specific database in our server.
How can I grant someone enough access to fulfill a DBA role but not access to one specific database?
As in, I don't want to add the person to sysadmin role which will give him access to this confidential database, but I also want him to be able to work (do his dba job that he was hired for).
Thank you


texts: How to SELECT "closest" rows from another table?


texts: Best What's new book about just changes and new features in SQL Server 2012

texts:
I want to read about new changes and new features in SQL Server 2012.I found some E-Book in the internet but they have basic topics (like create Tables,Views,...) I want a book that just cover new features.Is there any book?

thanks


texts: Change drive letter (which contains system dbs)

texts:
Is it possible to safely change the drive letter for a volume which holds only system databases?

What precautions should be taken and how should it be done? I know I can just go to computer management > storage and change drive letter but can have negative consequences on SQL Server operation?


texts:
What am I missing here?

I have two tables:

CREATE TABLE source
(
  "sourceid" serial,
  "state" character varying(255) REFERENCES states (state),
  "leadtime" character varying(255),
  CONSTRAINT source_pkey PRIMARY KEY (sourceid)
)
CREATE INDEX
   ON source (state ASC NULLS LAST);

CREATE TABLE updates
(
  "updateid" serial,
  "sourceid" integer REFERENCES source (sourceid) ON DELETE CASCADE,
  "skip" boolean,
  CONSTRAINT update_pkey PRIMARY KEY (updateid)
)
CREATE INDEX
   ON updates (sourceid ASC NULLS LAST);

source is the parent table and updates the child. I want any records deleted from source to cascade down and delete all records in updates that have the same sourceid.

I have also tried adding the REFERENCES constraint to the sourceid field of the source table.

Thanks!
Brad


texts:
I am completely new to MySQL. I am not a programmer or anything.

I was installing the program months ago, and forgot my root password, tried to fix things and it failed.

I did a system restore and now I get two messages.

One is short:

"Log file path must be defined before calling the WriteToLog method"

The second is long:

  at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at System.Xml.XmlTextReaderImpl.Read()
   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.Load(String filename)
   at MySQL.Utility.Classes.MySQLWorkbench.MySqlWorkbenchConnectionCollection.LoadXmlFile(Boolean saving)
   at MySQL.Utility.Classes.MySQLWorkbench.MySqlWorkbenchConnectionCollection.Load()
   at MySQL.Utility.Classes.MySQLWorkbench.MySqlWorkbench.LoadExternalConnections()
   at MySQL.Utility.Classes.MySQLWorkbench.MySqlWorkbench.set_ExternalApplicationConnectionsFilePath(String value)
   at MySql.Notifier.Notifier.InitializeMySqlWorkbenchStaticSettings()
   at MySql.Notifier.Notifier..ctor()
   at MySql.Notifier.NotifierApplicationContext..ctor()
   at MySql.Notifier.Program.Main(String[] args)

Have I destroyed my MySQL, can I fix this myself? Or should I call someone?


texts:
I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be too big to me) and how I can optimize it. Here is my query:

SELECT
    L.id,
    L.role,
    L.first_name,
    L.last_name,
    L.birth_date,
    L.team_name,
    L.team AS team_id,
    L.photo,
    L.email,
    L.played,
    L.vote_average,
    L.goal_sum AS goal,
    L.score + IFNULL(L.c_mvp, 0)*10 AS score,
    L.y_card_sum AS y_card,
    L.r_card_sum AS r_card,
    IFNULL(L.c_mvp, 0) AS mvp,
    IFNULL(unbeaten, 0) AS unbeaten
FROM (
    SELECT * FROM (
        SELECT
            dc_player. *,
            dc_team.name AS team_name,
            COUNT( dc_match_entry.player_id ) AS played,
            AVG( dc_match_entry.vote ) AS vote_average,
            SUM( dc_match_entry.goal ) AS goal_sum,
            SUM( dc_match_entry.vote ) *10 + SUM( dc_match_entry.goal ) *10 - SUM( dc_match_entry.r_card ) *10 - SUM( dc_match_entry.y_card ) *5 - SUM( dc_match_entry.own_goal ) *10 AS score,
            SUM( dc_match_entry.y_card ) AS y_card_sum,
            SUM( dc_match_entry.r_card ) AS r_card_sum
        FROM
            dc_player,
            dc_match_entry,
            dc_team
        WHERE
            dc_player.id = dc_match_entry.player_id AND
            dc_player.team = dc_team.id
        GROUP BY
            dc_player.id
    ) LOL
    LEFT JOIN (
        SELECT
            COUNT(dc_match.mvp) c_mvp,
            mvp AS player_id
        FROM dc_match
        WHERE
            dc_match.mvp IS NOT NULL
        GROUP BY dc_match.mvp
    ) ROFL
    ON ROFL.player_id = LOL.id
    ORDER BY
        LOL.score DESC
) L
LEFT OUTER JOIN(
    SELECT
        dc_player.id,
        COUNT(dc_match.id) AS unbeaten
    FROM
        dc_match,
        dc_match_entry,
        dc_player
    WHERE
        (
            (dc_player.team=dc_match.host AND
            dc_match.guest_score=0
            ) OR
            (
                dc_player.team=dc_match.guest AND
                dc_match.host_score=0
            )
        ) AND
        dc_player.id = dc_match_entry.player_id AND dc_match.id = dc_match_entry.match_day
    GROUP BY
        dc_player.id
) I
ON
    L.id = I.id
GROUP BY
    id

I can provide table structures too if needed.


texts: Make a backup file or save a DB in PostgreSQL

texts:
I have deployed for my data base in production a new groups of users;

An admin user with full access to the DB ;
A read only user ;
A super admin user to manage all the DBs ;

The matter is for the backup file or the data base save file, can i just use the read only user to save the DB or i shall use the super admin user to insure all data will be saved.


texts: How to insert in a table with only an IDENTITY column?

texts:
Given a table with only an IDENTITY column, how do you insert a new row? I've tried the following:

INSERT INTO TABLE
(Syntax error)

INSERT INTO TABLE VALUES()
(Syntax error)

INSERT INTO TABLE (Id) VALUES()
(Syntax error)

I am testing something and only need the IDENTITY column. It's not for production.  Otherwise, such a table can be used as a sequence generator, where no other columns are needed.


texts: Schema qualification necessary for query plan reuse?

texts:
While reading this article about Plan Caching in SQL server, I came across a tidbit I was unaware of:

  ... for reuse it is necessary that the objects that the batch references do not require name resolutions. For example, Sales.SalesOrderDetail does not require name resolution, whereas SalesOrderDetail does because there could be tables named SalesOrderDetail in multiple schemas. In general, two-part object names (that is, schema.object) provide more opportunities for plan reuse.

I'm looking for some clarification about how important it is to use two-part object names, since "In general, two-part object names provide more opportunities for plan reuse.", yet it starts by saying it's necessary.

More specifically, most of the stored procedures I deal with are in the dbo schema and reference only dbo objects, without specifying the dbo prefix.  Are these prevented from reusing cached query plans, even with everything using the default schema?


texts: Estimating impact of indexes SQLite database size

texts: I'm trying to estimate the database size (on disk) for a SQLite DB that includes a number of indexed columns. These columns are of (SQLite) type Integer and String. It's straightforward enough to estimate the size per row with these columns, but I'm not sure how to account for the extra per-row padding due to the indexes. What's the best approach for this?


texts: Restore DB Backup with limited amount of disk space

texts:
I want to restore a SQL Server database using a backup (.bak) of about 60GB.

Is it possible to do this on a machine with only 100GB of free disk space?

Or do I need at least the 60GB for the .bak file and the 60GB for the final database on SQL Server?

Are there any ways of just copying the .bak file into the server?

Network shares are not visible in the SSMS - only local drives like USB devices.


texts:
I have a table "raw_data" in which I have a field "data" containing a json array like:

{'a' : 1,
'b' : 2,
'c' : 3}

I would like two different things:

Append a new item like {'d' : 4} at the end of the array, that would give me :
{'a' : 1, 'b' : 2, 'c' : 3, 'd' : 4}
Update the key 'c' with the value 5 in this array, that would give me:    {'a' : 1, 'b' : 2, 'c' : 5}
Upsert the already existing key 'd' with the value 6 in this array, that would give me:    {'a' : 1, 'b' : 2, 'c' : 3, 'd' : 6}

I found this post, but couldn't manage to understand how it applies to my specific case


texts: Postgres Create table is hanging for 2 days

texts:
We are running a database that nearly 11 TB. We are using PostgresQL 9.5.
After the hard restart in adding new replica we are facing the issues that we can't create new table most of indexes are corrupted.
We can't kill the process that running create table the only thing I could do is restart the database.  I tried to insert new data it's working. Only create table having issue.

Do you know what's the possible reason could lead into that?

Updated.
add pg_activity


texts:
I have to transpose a class Diagram into a Physical Data Model (more precisely MySQL database structure).

In my class diagram, I have a lot of entities (A, B, C, D) which inherit from an other entity, named PARENT.

PARENT entity have a relationship PARENT_OTHER of cardinality (0.* - 0.*) with an entity named OTHER.

I had thought of two methods :

My first method is to use "Concrete Table Inheritance". I don't transposed the PARENT entity into a table but I have one concrete table for each children entities : T_A, T_B, T_C, T_D.
However I must create the PARENT_OTHER relationship for each tables T_A, T_B, T_C, T_D.

Schema :

So, if I have N childrens entities, I have 2 * N Tables... (without the table T_OTHER).

The second method is to conserve the inheritance (Class Table Inheritance).I have one table by children : T_A, T_B, T_C, T_D and one table for PARENT entity : T_PARENT.

Schema :

So, if I have N childrens entities, I have N + 2 Tables... (without the table T_OTHER).

Out of the number of tables, the second solution offer a bit more of abstraction... I can look directly in the table T_PARENT_OTHER for all children without joins.

Some people say me that I should avoid inheritance in RDBMS (Relational Database Management System), like MySQL DBMS, and I suppose that it's true.

I think this is common problem in database modelisation.

I would like to know what is the best way in this kind of situation ?

Are there other ways ?

Thanks in advance and please excuse my english.


texts: what is the consequence of setting enableQueryTimeouts to FALSE?

texts:
MySQL Connector-J has a property enableQueryTimeouts which is TRUE by default. However, in the documentation it is mentioned as :

  When enabled, query timeouts set via Statement.setQueryTimeout() use a shared java.util.Timer instance for scheduling. Even if the timeout doesn't expire before the query is processed, there will be memory used by the TimerTask for the given timeout which won't be reclaimed until the time the timeout would have expired if it hadn't been cancelled by the driver. High-load environments might want to consider disabling this functionality

So, I want to disable enableQueryTimeouts to gain high performance. However, if I disable enableQueryTimeouts, then

How a query will timeout?
Will there any consequences ?

Please help


texts: Querying Table for Index Fragmentation - Multiple Different Results for Same Index

texts:
I am using the following query to check index fragmentation on a table. Each index returns a 3 rows and each one has a different value for "avg_fragmentation_in_percent". The second row seems to be the accurate value when I check the index fragmentation via the Properties menu of the actual index.

I am curious about the other values that are returned because one row says 100% fragmentation and another row says 0% fragmentation. Does anyone have an idea why I am seeing this?

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'wfm');
SET @object_id = OBJECT_ID(N'wfm.dbo.Lab_Employee_Time_Off');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;

SELECT OBJECT_NAME(ips.OBJECT_ID) AS 'TableName'
 ,i.NAME AS 'IndexName'
 ,ips.index_id
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 ,ips.avg_page_space_used_in_percent
 ,ips.page_count
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
ORDER BY i.name, avg_fragmentation_in_percent DESC

END;
GO


texts: Unable to create a Filtered Index on a Computed Column

texts:
In a previous question of mine, Is it a good idea to disable lock escalation while adding new calculated columns to a table?, I am creating a computed column:

ALTER TABLE dbo.tblBGiftVoucherItem
ADD isUsGift AS CAST
(
    ISNULL(
        CASE WHEN sintMarketID = 2
            AND strType = 'CARD'
            AND strTier1 LIKE 'GG%'
        THEN 1
        ELSE 0
        END
    , 0)
    AS BIT
) PERSISTED;

The calculated column is PERSISTED, and according to computed_column_definition (Transact-SQL):

  PERSISTED

  Specifies that the Database Engine will physically store the computed
  values in the table, and update the values when any other columns on
  which the computed column depends are updated. Marking a computed
  column as PERSISTED allows an index to be created on a computed column
  that is deterministic, but not precise. For more information, see
  Indexes on Computed Columns. Any computed columns used as partitioning
  columns of a partitioned table must be explicitly marked PERSISTED.
  computed_column_expression must be deterministic when PERSISTED is
  specified.

But when I try to create an index on my column I get the following error:

CREATE INDEX FIX_tblBGiftVoucherItem_incl
ON dbo.tblBGiftVoucherItem (strItemNo)
INCLUDE (strTier3)
WHERE isUsGift = 1;

  Filtered index 'FIX_tblBGiftVoucherItem_incl' cannot be created on
  table 'dbo.tblBGiftVoucherItem' because the column 'isUsGift' in the
  filter expression is a computed column.   Rewrite the filter
  expression so that it does not include this column.

How can I create a filtered index on a computed column?

or

Is there an alternative solution?


texts: If shared memory, named pipes, TCP/IP network protocols are all enabled which has priority?

texts:
If you have all 3 protocols enabled in SQL Server 2016, which protocols are prioritized on the server instance?

Couldn't find any specific documentation on this and don't really understand how it works that well.


texts:
I have a scalar function which was getting used inside several queries. Scalar function was called inside WHERE and INSIDE CASE statement. This created huge IO and CPU performance issue due to the fact that function was getting executed for each row (Function calls and joins few views).
I have now changed Scalar function to Inline table function. This works perfectly but system has a limitation that it cannot have 2 select statement inside same query.

CASE
WHEN (QUALIF_HIS_INFO.QUALHIS_STATUS = 'Completed' AND
  QUALIF_HIS_INFO.DATEOFEXPIRATION IS NOT NULL) THEN 'Completed'
WHEN STATUS_VIEW.REQUIREMENT_STATUS IS NULL THEN 'Unassigned'
WHEN STATUS_VIEW.REQUIREMENT_STATUS = 'Pending' AND
  (TRAINING_REQUIREMENT.OBSOLETE = 1 OR
  (SELECT 1 from TRAINING.ISREQUIREMENTASSIGNED_2(USER_SETTINGS_1.USER_ID, QUALIF_HIS_INFO.QUALHIS_REQUIREMENT)) IS NULL) THEN 'Unassigned'
ELSE STATUS_VIEW.REQUIREMENT_STATUS
END QUALIFICAT_HIST_I_2_QUALH_STAT,

Is there anyway I can use CROSS APPLY or anything else which will eliminate use of SELECT statement here?

Any help will be much appreciated as I seem to be stuck here from quite a while now.

Function accepts 2 parameters.


texts: MySQL router making mysql general log not useful

texts:
I am running MySQL 5.7 on  a 3 node cluster using InnodBCluster. It is making the user of the general_log quite challenging. The mysqlrouter check ins are being logged in the mysql general_log. These status updates are appearing in the log nearly every second for every router that is pointed at the cluster.
2808634 Query select I.mysql_server_uuid, I.endpoint, I.xendpoint, I.attributes from mysql_innodb_cluster_metadata.v2_instances I join mysql_innodb_cluster_metadata.v2_gr_clusters C on I.cluster_id = C.cluster_id where C.cluster_name = 'cluster' AND C.group_name = '7b6cc6c6-b4d6-11ea-bb3a-023d3853a38a'
2808634 Query COMMIT
2808634 Query show status like 'group_replication_primary_member'
2808634 Query SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM on_group_members WHERE channel_name = 'group_replication_applier'
    8 Query BEGIN
    8 Query COMMIT /* implicit, from Xid_log_event */
2808633 Quit

I'm guessing this is how mysqlrouter is internally knowing where to route connections. Is there a to not write this to the general_log? Other than turning off the general_log?
I use the general_log to audit of who logs in and what they run. I have found there are tools to do this other than the general_log such as:
MySQL Enterprise Audit https://dev.mysql.com/doc/refman/5.7/en/audit-log.html
Percona Audit Log https://www.percona.com/doc/percona-server/5.7/management/audit_log_plugin.html
Which I may need to investigate further.


texts: What is the request_id in sys.dm_exec_requests and how does it change?

texts:
According to MSDN, the request_id in sys.dm_exec_requests is the

  "ID of the request. Unique in the context of the session. Is not
  nullable."

It always appears to be 0, from casual observation.

Can someone tell me how, for a given session, this id can ever be more than 0? Is it possible to reproduce this with some sql?

Thanks!


texts: Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes

texts:
I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash.

For example the hash of test is

36f028580bb02cc8272a9a020f4200e346e276ae664e45ee80745574e2f5ab80

Which DATATYPE should I set the column that stores this value? I've heard about using BINARY instead of VARCHAR.
How should I index this column so that searches by hash are fastest?
Which database management system (like PostgreSQL, DB2, SQL Server), or document software (like MongoDB) should I use that most efficiently allows me to store and retrieve records by hash? I only have experience with relational database management systems like MySQL, but maybe a document store like MongoDB would be the better way to go.


texts: How to choose the last recorded added without sorting

texts:
I've got this simple table: RecordID (int PK), UserID (int FK), DTime (datetime), Length (int)

I need to run a simple query:

SELECT TOP(1) * FROM uTable WHERE UserID = @userid ORDER BY DTime DESC

The query is run using a Stored Procedure on SQL Server 2008 R2.

The objective is to get the last record for @userid in terms of DTime.

While checking the execution plans I've noticed the sorting (ORDER BY) is almost 80% of the cost to run this query (which is the most "expensive" query on my DB for now) - how could I change the query so I won't use ORDER BY and thus eliminate that heavy cost.

Thanks!


texts: ClusterControl creating MySQL NDB cluster - how to set the right address to mysql-cluster deb bundle .tar?

texts:
I was trying to create a MySQL NDB cluster using ClusterControl. Everything was oky until it was trying to download the mysql-cluster deb bundle .tar i guess. Here is the job log:
[13:19:21]:
Downloading http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.5/mysql-cluster_7.5.18-1ubuntu20.04_amd64.deb-bundle.tar failed:
[13:19:19]:
Downloading software to /var/cache/cmon/packages/ubuntu/mysql-cluster_7.5.18-1ubuntu20.04_amd64.deb-bundle.tar

If I try to manually download that file I get a Page not found error. I also took a look upon common issues listed on ClusterControl's website but I couldn't find a fix to this error.


texts: Why isn't the primary (clustered) key being used in this query?

texts:
I have a SQL Server 2008 R2 table whose schema structure looks as follows:

CREATE TABLE [dbo].[CDSIM_BE]
(
    [ID] [bigint] NOT NULL,
    [EquipmentID] [varchar](50) NOT NULL,
    [SerialNumber] [varchar](50) NULL,
    [PyrID] [varchar](50) NULL,
    [MeasMode] [varchar](50) NULL,
    [ReadTime] [datetime] NOT NULL,
    [SubID] [varchar](15) NULL,
    [ProbePosition] [float] NULL,
    [DataPoint] [int] NULL,

    CONSTRAINT [PK_CDSIM_BE]
    PRIMARY KEY CLUSTERED ([ID] ASC, [EquipmentID] ASC, [ReadTime] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON) ON [MonthlyArchiveScheme9]([ReadTime])
) ON [MonthlyArchiveScheme9]([ReadTime])

CREATE NONCLUSTERED INDEX [idx_CDSIM_BE__SubID_ProbePosition]
ON [dbo].[CDSIM_BE] ([SubID] ASC, [ProbePosition] ASC)
INCLUDE ([EquipmentID], [ReadTime], [BECorr])
    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 [MonthlyArchiveScheme9]([ReadTime])

CREATE NONCLUSTERED INDEX [IX_CDSIM_BE_ProbePosition]
ON [dbo].[CDSIM_BE] ([ProbePosition] ASC)
INCLUDE ([SerialNumber], [SubID])
    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 [MonthlyArchiveScheme9]([ReadTime])

CREATE NONCLUSTERED INDEX [IX_CSDIM_Readtime]
ON [dbo].[CDSIM_BE]([ReadTime] ASC)
INCLUDE ([EquipmentID])
    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 [MonthlyArchiveScheme9]([ReadTime])

And I'm executing this simple query:

Select Max(Id)
From dbo.CDSIM_BE

There are ~2.5B rows in the table.

The query plan shows an index scan being done on the IX_CdSIM_BE_ProbePosition index. I'm wondering why SQL Server simply wouldn't use the clustered (and primary) index and immediately go to the last row in the table and retrieve the Id value, since that must be the maximum.


texts:
Good afternoon,

I am trying to perform a transactional replication in sql server 2016, and I have the following problem.

The scenario is as follows:

A publisher server and a subscriber server, the transactional replication process runs successfully,
the problem comes when, a failover occurs, the application goes from writing from the publisher server to
write to the subscriber server.

My question is, how should the subscriber server be configured so that when a failover of the publishing node occurs, the data written in the subscriber is replicated later to the publisher.

Should I return to the publisher subscriber, and subscriber to the publisher for this process?

Thank you for reading.
 I will be attentive to your opinions.


texts: Why is count(*) slow, when explain knows the answer?

texts:
This query: select count(*) from planner_event takes a very long time to run - so long, I gave up and killed it before it finished. However, when I run explain select count(*) from planner_event, I can see a column in the output with the number of rows (14m).

How come explain can get the number of rows instantly, but count(*) takes a long time to run?


texts: Moving a large table to another filegroup

texts:
I have a 30 million row table, which is causing performance issues. I thought of moving it to a new filegroup for better performance.

My questions are:

This table has many foreign key references. If I just move the clustered index to another filegroup, will it still work right? Or do I also need to move the references to the new filegroup?

How about leaving the nonclustered indexes on the old filegroup?

Please suggest on my approach and my question.


texts: Splitting a String and ORDER BY DESC its matching Values

texts:
From a Query like:

SELECT id
FROM table
ORDER BY
        column = 'A B C D E F G' DESC,
        column = 'A B C D E F' DESC,
        column = 'A B C D E' DESC,
        column = 'A B C D' DESC,
        column = 'A B C' DESC,
        column = 'A B' DESC,
        column = 'A' DESC
GROUP BY id

And the Answer I got was to use

SELECT id
FROM table
ORDER BY
        SUBSTRING_INDEX(column, ' ', 1),
        LENGTH(column) DESC
GROUP BY id

The problem that it seems that I can't define a specific string to it And it orders the full-records only, What I want is something to order the matches of the ORDER BY at top by its highest-matching value as the ladder of defined string.

In all cases, Is there any performance difference between my first Query of ladder column = . . . . DESC,. . . And any other solution? And what is the other solution for such a Query to select element in a specific string parts order.

The table is

[id  -  column       ]
[1   -  A B C D E    ]
[2   -  A B C        ]
[3   -  A B C D E F G]
[4   -  X Y Z        ]
[5   -  A            ]
[6   -  B            ]
[7   -  A B C        ]
[8   -  B A C D      ]
[9   -  A B G C D E F]
[10  -  A B C D E F G H I J K]
[11  -  Z A B C D E]

And I have this string A B C D E F G that I want to get all the rows matching its parts, So I want to order like ORDER BY PARTSOF('A B C D E F G') DESC

to get the result of

[id - column]
[3  - A B C D E F G]
[1  - A B C D E]
[2  - A B C]
[7  - A B C]
[5  - A]
. . . EVERYTHING AFTER IS BY ID . . .
[4  - X Y Z        ]
[6  - B            ]
[8  - B A C D      ]
[9  - A B G C D E F]
[10 - A B C D E F G H I J K]
[11 - Z A B C D E  ]

Juse like this SQLFiddle http://sqlfiddle.com/#!9/9f4c815/2/0


texts:
Database:Oracle 11gR2
OS: Windows 2008 Server R2

I'm not trained DBA, just have to run things on an Oracle database temporarily, which is used to perform OLAP processes.

I need to automatize user activity logging (about 30 users) and saving this data (as a text file) if possible.

What I need to log:

SQL_TEXT,
PARSING_SCHEMA_NAME,
FIRST_LOAD_TIME,
DISK_READS,
ROWS_PROCESSED,
ELAPSED_TIME,
SERVICE,
MODULE,
IP_ADDRESS

It would be great to automatically save this logs on an hourly basis.

I've made a research about RMAN, but it seems complicated for me at this stage.

Thanks.


texts: Getting error 1064 in automatically-generated code from MySQL workbench

texts:
I have started using mySQL workbench (Version 6.3.4.0) and I have used the EER Diagram tool to design a schema. So far, so good. However, when I try and run the Forward Engineering, I get the error below.

  "ERROR: Error 1064: You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near 'COMMENT '') ENGINE = InnoDB' at line 8"

I've not edited it at all, this is purely the automatic code.

If I remove the "COMMENT ''" from the primary key, then it runs, but it doesn't create any tables on my server.

-- Table `mrcatalogue2`.`Location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mrcatalogue2`.`Location` (
  `chromosome` VARCHAR(2) NULL COMMENT '',
  `position` VARCHAR(10) NULL COMMENT '',
  `hg19_coordinates` VARCHAR(15) NOT NULL COMMENT '',
  PRIMARY KEY (`hg19_coordinates`)  COMMENT '')
ENGINE = InnoDB;


texts:
One of requirments for database which I am building is to predict database size to prepare hardware for production environment. Aapplication has 2 main tables wchich are partitioned. Tables are in 8 Filegroups (16 files, 32 partitions). Both tables store data for last month (after one month data will be deleted). We have to be prepared for around 12 milions rows daily in one table and around 36 milions rows in second table. I made a workload test for these tables and:

For 1 milion rows data size of filegroups which cointains data was around 13 GB
For 12 milion rows data size of filegroups which contains data was around 48,5 GB

Log size has increased only by 40mb.
According to this data have two questions:

My idea was to make daily workload and multiple it by 30. But according to above data it is not working like that. 13*12 != 48,5
Why log has increased only by 40 mb of data ?
Is there any difference in size If we store data in AlwaysOn solution ?

To measure file size I have been used below query:
SELECT [sizing].[DbName],
       [sizing].[FileName],
       [sizing].[type_desc],
       [sizing].[CurrentSizeMB],
       [sizing].[FreeSpaceMB],
       [CurrentSizeMB] - [FreeSpaceMB] AS [SizeStored]
FROM
(
    SELECT DB_NAME() AS [DbName],
           [name] AS [FileName],
           [type_desc],
           [size] / 128.0 AS [CurrentSizeMB],
           [size] / 128.0 - CAST(FILEPROPERTY([name], 'SpaceUsed') AS int) / 128.0 AS [FreeSpaceMB]
    FROM [sys].[database_files]
    WHERE [type] IN ( 0, 1 )
) [sizing];


texts: MySQL database cannot accept SSL connections (Certs are UTD)

texts:
Not sure if this is the right place for this kind of question, but I have a MySQL Database running on an Azure VM. Everything was working great, until last week it seemingly randomly stopped allowing SSL connections. From everything I've seen looking through the certificates on the VM and the ones assigned to the VM through the Azure portal, none of them are expired.

Even if I try to connect to the database while Remoting into the VM (so making it a local connection), it won't allow SSL. Not sure where to look from here, so any help would be greatly appreciated!

Edit: Just wanted to add the fact that if I try to connect with No SSL it does connect fine. I just don't know why SSL stopped working all of the sudden. I also wasn't able to find any mention of the SSL cert in the config file, which seemed off to me.


texts: How to store data having a fixed number of columns and a variable number of rows

texts:
I have posted this question in the stack overflow site and a more experienced user than me suggested I post it here.

I am quite new at collecting data and am collecting a new dataset using R from online media articles together with each article's disqus comments and other related information. My "record" will have 12 columns of information (see image below). My confusion lies with the fact that these media articles might have anything from 0 to 500 comments (maybe more). I would like to save this data as efficiently as possible and for now the only idea to my inexperienced knowledge is to save each in its own .csv file although I'm not sure about this.

And that is where I'm stumped. My first thought was to divide each record in two: the first part would contain six columns and will always have one row and the second part will contain another six columns with a varying number of rows. A sort of ID will link the two together. This would possibly be the "dirty" solution.

What I would like to kindly ask the Community is about newer technologies (for examples: Mongodb, NoSQL. Or ust SQL?) to store this sort of data. Eventually, this data will not be online as it will be queried or processed using some or all of its elements of data.

The image below shows an example of part of a data record starting out with 1 row but rows increase according to the number of comments in an article. This particular record would have 23 rows in the last 6 columns if all data is entered.

It would greatly help me to have an idea on a way forward rather than having to test or research a technology only to find that it is not what I need. Any comments or suggestions are most welcome.


texts:
One of our divisions is running SQL Server 2012 SP1 Enterprise edition for no particular reason (I know, I know... the decision was made before my time and they are resistant to change). What I'm curious about, since I have a lack of experience with Enterprise ed., is why database snapshots would be created when no one at the division has any idea why either. Unbeknownst to me, they've been manually deleting these for years.

As I understand it database snapshots can only be created via T-SQL (though I believe DBCC CHECKDB may create them behind the scenes) so what exactly is happening here? Is there a good way to track down the process that is creating the snapshots? Is there a setting I've overlooked?

The instance in question is a dedicated SharePoint instance.
The server itself has space constraints making the snapshots that much more of an issue.


texts: Transformation of row data based on preceding row for special cumulative sum

texts:
Following the answer to this question, I have managed to produce the following output to get a running sum of values:

id      creation                    operation       value       running sum
SyJw-c  2016-09-01 00:11:08.307419  positive_op_1   1.33        28.82
SyJw-c  2016-08-21 08:32:54.431662  negative_op_1   -1          27.49
SyJw-c  2016-08-18 07:38:33.878365  positive_op_2   1           28.49
SyJw-c  2016-08-14 18:12:03.599797  negative_op_1   -1          27.49
SyJw-c  2016-08-02 15:44:29.693303  positive_op_1   1.33        28.49
SyJw-c  2016-07-31 12:08:50.659905  override_op_1   4.66        27.16
SyJw-c  2016-06-26 06:53:54.537603  negative_op_1   -3.5        22.5
SyJw-c  2016-05-31 13:34:08.005687  negative_op_1   -1          26
SyJw-c  2016-05-31 13:34:04.776970  negative_op_1   -1          27
SyJw-c  2016-05-31 11:27:09.502983  override_op_2   28          28

BUT my case is more complex. Not only do I need to sum up the values, I need to be able to perform a conversion first to some rows based on the running sum of the row right beneath it.

Let me first explain the motivation:

Currently I have a table with incremental, decremental and override operations. I would like to port the data to a table with only incremental and decremental operations such that I would be able to straightforwardly sum up the values. I am not looking to maintain the old table, simply a way to migrate the data into a simpler model and henceforward to append data to the new table only.

Taken the "raw" table above, I would like to write a query (I am running on postgresql 9.5) and get a table as closely resembling the below. (Conversely, I would like to know that what I am attempting is impossible)

Note the override operators are interspersed between the normal operators, they may appear more than twice as in the example, also, all initial operators (the earliest in the table) are override with an initial value that should be taken into account as in the example below. Moreover I had shown only data belonging to one group (same id) but the general idea is to perform this migration for all groups. Lastly I show the math in parentheses, I don't need that in the result, it is for the example only:

id      creation    oper      transformed_op   value   transformed_value       running sum
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.98
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_2                    1       1                       10.65
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.65
SyJw-c  2016- ...  ovr_op_1   new_rel_op_1     4.66    (4.66-22.5) = -17.84    4.66
SyJw-c  2016- ...  neg_op_1                    -3.5    -3.5                    22.5
SyJw-c  2016- ...  neg_op_1                    -1      -1                      26
SyJw-c  2016- ...  neg_op_1                    -1      -1                      27
SyJw-c  2016- ...  ovr_op_2   new_rel_op_2     28      (28-0) = 28             28

The table is shown ordered from last to first. The 22.5 is 26 - 3.5. This subtraction should be done (this_value - previous_sum) depends on the value of the transformed_op. When the original op had been an override op I would like to perform some action based on the running sum of the row proceeding it (ordered by creation desc), in this case subtract that running sum value from the value in the value column.


texts: MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?

texts:
I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table.   In a report I'm writing, an object can be selected to view statistics against.  The interface shows all objects in order of decreasing importance (ie, hits).

The schema for the two tables (slightly trimmed down, but you get the gist):

CREATE TABLE IF NOT EXISTS `event_log` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) DEFAULT NULL,
  `object_id` int(5) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `user_id` (`user_id`),
  KEY `object_id` (`object_id`)
);

CREATE TABLE IF NOT EXISTS `lookup_event_objects` (
  `object_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_desc` varchar(255) NOT NULL,
  PRIMARY KEY (`object_id`)
);

The query I'm having trouble with is below.  It works fine with my table of ~100 entries, but the EXPLAIN worries me a little.

    explain SELECT
            el.object_id,
            leo.object_desc,
            COUNT(el.object_id) as count_rows
        FROM
            event_log el
            LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
        GROUP BY
            el.object_id
        ORDER BY
            count_rows DESC,
            leo.object_desc ASC

Returns:
Using index; Using temporary; Using filesort

So -- what's wrong with my schema and/or query for MySQL to fall back on temporary and filesort?  Or is it as optimized as it can get using ORDER BY?


texts: MySQL has gone away with two connections

texts:
I'm using MySQL 5.6.19 on OSX 10.9.5 (DMG installation from mysql.com) and I'm receiving the infamous MySQL has gone away when I try to open two connections to this server.

If I try connecting from PHP:

SQLSTATE[HY000] [2006] MySQL server has gone away

In mysql on Terminal, it won't connect either, but shows another error:

$ mysql -u root
ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32

I already changed a bunch of parameters in my.cnf, here is how it's now:

[mysqld]
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
connect_timeout = 300
wait_timeout = 300
net_read_timeout = 300
net_write_timeout = 300
max_allowed_packet = 128M
max_connections = 50
table_open_cache = 1024
innodb_flush_log_at_trx_commit = 1

None of those parameters helped. max_allowed_packet is a famous solution, but even that isn't helping. This is really weird because I'm not even using the database, no queries running, nothing, I just open the first connection, and with the second I'm receiving the error.

Any idea?

EDIT 1

Restarting MySQL solves this for two or three days, then it happens again.


texts: Remove Mac OSX Lion pre-installation of PostgreSQL

texts:
I'm running Lion 10.7.5, and I'm trying to install Postgresql 9.2.4 for use with Django. I have hit countless roadblocks and errors, so I would like to start with a totally clean slate.

I did a brew uninstall postgresql and removed any lingering folders from my attempted 9.2.4 installation. Now, I would like to completely remove the Mac installation psql -V 9.0.10.

What is the best method for doing this? or, is this definitely not recommended?

Note: I do not have a postgresql uninstaller app, so I'm interested in which files I need to manually delete (unless there's a better way of course.)


texts: Postgres compound index not delivering expected improvements

texts:
I have a table with a definition something like this….

CREATE TABLE foo (
  A CHAR(6),
  B TIMESTAMP,
  Lots more fields……
)

It contains about 600 million records.

Originally it had these two indexes on it….

CREATE INDEX i1 ON foo (A);
CREATE INDEX i2 ON foo (B);

Most of the queries I run look something like this…

SELECT * FROM foo
WHERE A = ‘ABCDEF’
AND B > ‘some date’
AND B < ‘some other date’

I wanted to improve the performance so ( following advice on this forum) I added this compound index (previous two indexes still remain)….

CREATE INDEX i3 ON foo (A,B);

When I do an EXPLAIN it’s apparent that the new index isn’t being used, but index 'B' is being used.

This leads to some questions…
1)  Why isn’t the new index being used?
2)  Can I disable my first two indexes to try and force the compound index to be used? (I don’t want to DROP the indexes because they’re likely to days to re-create them)
3)  Does the order of the WHERE clauses make any difference? Would a compound index on A,B be any different from one on B,A?
4) how can I improve performance on this query?


texts:
Our organization switched to a new tool and now our text files are generated with only a trailing CR instead of a CR & LF.  Now sqlldr thinks all of the data in the file is 1 row.  How can I fix this?

Here is our typical setup:

    ------------------------------
    -- CREATE THE CONTROL FILE:
    ------------------------------
    SET HEADING OFF PAGES 0 FEEDBACK OFF VERIFY OFF TERM OFF;
    SPOOL &&client-ws.ctl

    SELECT  'load data ' || CHR (10) ||
            'infile * ' || CHR (10) ||
            'TRUNCATE ' || CHR (10) ||
            'into table TMP_ID_LIST' || CHR (10) ||
            'FIELDS TERMINATED BY ''    '' ' || CHR (10) ||
            '( ' || CHR (10) ||
            'ID )' FROM DUAL;

    SPOOL OFF;

    ------------------------------
    -- LOAD FROM FILE INTO TABLE:
    ------------------------------
    HOST sqlldr control=&&client-ws.ctl  data=&&input_file log=&&client-ld.log userid=&&userid/&&password@&&db silent=header direct=y

In the paricular case i'm trying to solve, it is only 1 column.  Example data:

    b55369fd-3919-4baf-808c-ceacfe5afaac
    b8c54bf7-7fba-4569-bd75-54f7fc9d5210
    75c48437-8acf-4c34-ab76-a0d5c282d81f
    d62272fe-5132-42ec-aea8-f084151168d4

Halp pweez :D - Thanks!


texts: Is there any performance difference between a group by statement and nested select statements in sql?

texts:
I am curious to know about the differences between using group by statement and nested selects to achieve the same result.
for example
consider I have two tables such as the following:

tblPurchase
ID
SellerID
BuyerID
Date
Price

tblPerson
ID
Name
Mail
Mobile

Now i want to show the list of purchases along with the associated names of the seller and the buyer
Since sellerID and BuyerID refers to the same table, i need two nested selects to fetch their names, so i would write:

Select tblPurchase.ID, tblPurchase.Date,
       select (tblPerson.Name from tblPerson where tblPerson.ID = tblPurchase.SellerID ) as [Seller's Name],
       select (tblPerson.Name from tblPerson where tblPerson.ID = tblPurchase.BuyerID ) as [Buyer's Name]
from tblPurchase

this is the first method, and i guess it can be done using group by statement as well ( though myself couldn't do it, since both fields refer to one able, i got stuck at the join section!)

Is there any difference between these two statements ? since i think group by itself is actually translated to the same nested selects later and then executed.


texts: Open source & cheap 'data at rest' encryption solutions

texts:
So I am exploring some options about database encryption. The best options are commercial (TDE). I am looking for an open-source implementation. Recent releases of MySQL and MariaDB have data-at-rest capabilities:
MariaDB
https://mariadb.com/kb/en/mariadb/why-encrypt-mariadb-data/
MySQL 5.7.11 comes with InnoDB tablespace encryption
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
What is important in this implementation (for companies) is: are these compliant with PCI-DSS / HIPAA etc?
From MariaDB:

The MariaDB file_key_management plugin enables the configuration of keys in a file. The key file is read at system start and no additional access is needed during runtime. The security of the encryption depends on access restriction to the key file. The key file can itself be encrypted, providing additional layer of protection.

From my point of view this will mean providing the decryption of the key during start (and OS reboot)? So whenever we (re-)boot a system does this mean we need to manually provide this key? Having this key readable on the server itself will defeat the use of data-at-rest encryption in the first place.
In MySQL 5.7.11+

The InnoDB tablespace encryption feature in non-enterprise editions of MySQL use the keyring_file plugin for encryption key management, which is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).
MySQL Enterprise Edition offers the keyring_okv plugin, which includes a KMIP client (KMIP v1.2) that works with Oracle Key Vault (OKV) to provide encryption key management. A secure and robust encryption key management solution such as OKV is critical for security and for compliance with various security standards. Among other benefits, using a key vault ensures that keys are stored securely, never lost, and only known to authorized key administrators. A key vault also maintains an encryption key history.

Now I am wondering, can this be made compliant with security standards? When using this data-at-rest, will root or mysql user have access to the keys since they could read encryption keys from memory?


texts: Auto Update Statistics Duration and Interruptions

texts:
Our production SQL Server 2008 R2 sometimes begins to perform slowly: all queries begin to work too slow (and sometimes all queries just hang and wait something then continue). This usually lasts several minutes.

I’ve read that sometimes auto update statistics can slow down the server:

Is there a reason to update statistics manually?

I see that sometimes auto update statistics occurs for rather large indexes (1.7 GB size, table of 5.5M rows). I can see the date of the most recent update for statistics via STATS_DATE, but I can’t see the duration of this process.

Could you, please, suggest how can I see the duration of the statistics auto update? Or how can I determine that auto update statistics causes problems?

Thank you.

P.S.
After we’d encountered these performance problems, I began to monitor wait stats. The highest wait type is CXPACKET. It varies from 36% to 60% of all waits. And in the morning, when working day begins, it has the maximum value during the working day (users usually work with application 12 hours a day). The second highest wait type is SOS_SCHEDULER_YIELD (15%-20%).
I found some posts in the Internet where it’s mentioned that CXPACKET wait time reveals that auto update stats makes problems. But I haven’t found the real proof of such statements. It is clear for me that CXPACKET can show problems with parallel execution of SQL queries (from http://www.dbadiaries.com/reducing-sql-server-cxpacket-wait-type).


texts:
I want to assign a value to a new column based on a condition for other rows associated with a user_id.
A user can see the same step with different devices at different times. However, only the first step must be taken into consideration.
e.g. step2 was seen by both mobile and desktop, but was first seen by mobile at 2021-03-16 14:03:16.
Likewise step4 was seen by both desktop and tablet, but first desktop.
To sum up, each step first seen by
step1 = mobile
step2 = mobile
step3 = mobile
step4 = desktop

Since the only device change happened at step4, then assign desktop > mobile to all records for that user_id.
How can I get the first step per user and device as in the expected result below?
Sample data:
+---------+-------+---------------------+---------+
| user_id | step  |     created_at      | device  |
+---------+-------+---------------------+---------+
| user1   | step1 | 2021-03-16 14:03:16 | mobile  |
| user1   | step2 | 2021-03-16 14:04:07 | mobile  |
| user1   | step2 | 2021-03-16 14:03:47 | desktop |
| user1   | step3 | 2021-03-16 14:03:55 | mobile  |
| user1   | step3 | 2021-03-16 14:04:00 | mobile  |
| user1   | step1 | 2021-03-16 14:04:02 | desktop |
| user1   | step2 | 2021-03-16 14:03:16 | mobile  |
| user1   | step3 | 2021-03-16 14:04:07 | mobile  |
| user1   | step4 | 2021-03-16 14:04:08 | desktop |
| user1   | step4 | 2021-03-16 14:04:09 | tablet  |
+---------+-------+---------------------+---------+

The expected result:
+---------+-------+---------------------+---------+---------------------+
| user_id | step  |     created_at      | device  | device_concatenated |
+---------+-------+---------------------+---------+---------------------+
| user1   | step1 | 2021-03-16 14:03:16 | mobile  | mobile > desktop    |
| user1   | step2 | 2021-03-16 14:03:16 | mobile  | mobile > desktop    |
| user1   | step3 | 2021-03-16 14:03:55 | mobile  | mobile > desktop    |
| user1   | step4 | 2021-03-16 14:04:08 | desktop | mobile > desktop    |
+---------+-------+---------------------+---------+---------------------+


texts: Changing master for channel gives syntax error

texts:
I'm trying to set up a multi-source replication

When trying to set a master for a specific channel using this query

CHANGE MASTER TO MASTER_HOST='192.168.0.203', MASTER_USER='rep', MASTER_PORT=3306, MASTER_PASSWORD='pass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107 FOR CHANNEL 'master-203';

I get the following error

  Error Code: 1064. You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'FOR CHANNEL 'master-203'' at line 1

I can't figure out what's the problem. The query is pretty much a copy-paste from the official documentation

My server is running MariaDB 10.1.21

What could be wrong?


texts: I want to create a mirror in SQL Server 2008, but the database is in the status "restoring ..."

texts:
I'm trying to create a mirror in SQL Server 2008, and did the following:

On the principal server

USE master;
GO
ALTER DATABASE Test
SET RECOVERY FULL;
GO;

BACKUP DATABASE Test
TO DISK = N'D:\Test.Bak' WITH FORMAT
GO
RESTORE VERIFYONLY
FROM DISK = N'D:\Test.Bak'
GO

BACKUP LOG Banco
TO DISK = N'D:\TestLog.Bak' WITH FORMAT
GO
RESTORE VERIFYONLY
FROM DISK = N'D:\TestLog.Bak'
GO

On the mirror server:

USE master;
GO;
RESTORE DATABASE Teste
FROM DISK = N'D:\Test.bak'
WITH NORECOVERY,
MOVE 'Test_Data' TO
N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Data.mdf',
MOVE 'Test_Log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Data.ldf'
GO

USE master
GO
RESTORE LOG Test
FROM DISK = N'D:\TestLog.bak'
WITH NORECOVERY
GO

I can not select, users can not change, change nothing!

how do I fix this?


texts: Postgres installation on MacOSX - problem with psql

texts:
I'm currently trying to install Postgres (using the installation package from http://postgresapp.com), i.e. downloading, unpacking, dragging the app into the Applications folder, launching the app. So far, so good.

However, when I then try to use the psql command I receive -bash: psql: command not found. I tried to follow the solutions in similar questions, but trying to locate the binaries through which psql or locate psql and so on all didn't work.

Am I doing something wrong? Could someone please provide guidance on how to make it work? Thanks!


texts:
I am planning to build a new AG setup in my QA environment with the following configuration. What I want to know is, can I have 2 heartbeat networks between the nodes for redundancy also, how many NIC's can be used in this scenario? The whole setup will be virtualized.

On both the nodes :

One public network for client connectivity
2 heartbeat networks for redundancy
One network for Database Mirroring

For this setup, I am assuming these are the IP addresses which are required

1 static IP for each of the node
1 static IP for the WSFC
1 static IP for the AG listner
4 static IP's for creating the 2 heartbeat networks

The nodes will be standalone instances with independent storage. The end result I am looking for is to create redundancy for the heartbeat network. Can I create one of it using a different IP range or from a different subnet? The nodes will be built at the Atlanta Datacenter where I am working right now. Please advice.

Thanks!


texts: Restoring SQL Server database on another box

texts:
I have a SQL Server database that is running these scripts to create backups. It's running full backups once a week, daily differentials, and hourly logs. These are being FTPd to another box where I would like to be able to restore them for verification purposes. Right now I'm doing this simply hand picking the appropriate backups using the names of the files, which have the date/time encoded into them. That seems pretty hacky to me.

On the main SQL Server box where the backups are being created, I can open SMS, right click on the database, and select

    Tasks -> Restore -> Database

I get a nice grid listing of options including the full, diffs, and logs that I can restore.

I can't seem to recreate this on the remote box though. I figure that the other SQL Server probably doesn't have the metadata required to create this list since the backups didn't originate from that box. Is there anyway to restore that meta data as well? I tried restoring msdb but it doesn't seem to help.

Here are the steps I'm taking:

(1) Restore the master database on the new server.
(2) Restore the msdb database on the new server.
(3) Restore the full backup of the database on the new server with NORECOVERY

RESTORE DATABASE AwesomeDatabase99
FROM DISK = N'C:\Path\To\FULL_20150226_111927.bak'
WITH
    MOVE N'AwesomeDatabase99' TO N'C:\Path\To\AwesomeDatabase99.mdf',
    MOVE N'AwesomeDatabase99_log' TO N'C:\Path\To\AwesomeDatabase99_1.ldf',
    NORECOVERY
GO

I was hoping at this point I would be able to get the full grid of restore options containing the diffs and logs inside MSM but it still doesn't want to show up. The paths to the backup file are the exact same on the new server as they were on the server where they were created.

It seems like the system should be able to take all the backup files and tell me which ones go together. Is that possible?


texts: MySQL Supported versions of MySQL Enterprise TDE

texts:
We are bidding for a project involving developing a portal using Liferay, which requires that we use MySQL version 5.5 which is the current compliant version of MySQL with Liferay. We need also use the Enterprise TDE in order to fulfill a number of project requirements.

I am aware that Enterprise TDE is included in version 5.7.12 but what about support for older versions? I was unable to find a formal documentation about support for earlier versions. Any ideas?


texts: postgres how to drop all the table in a schema through command

texts: I use postgresql and I need to drop all the tables which are present in the schema. How can I delete from command prompt.


texts: mysql.rds_history query locked for 70 seconds

texts:
SELECT  count(*)
    from  mysql.rds_history
    WHERE  action = 'disable set master'
    GROUP BY  action_timestamp,called_by_user,action,mysql_version,
        master_host,master_port,master_user,master_log_file ,
        master_log_pos,master_ssl
    ORDER BY  action_timestamp
    LIMIT  1

This query appears in my slow log as locked for 110 seconds at the exact moment I am doing an ETL query that usually takes ~10 seconds.  Intermittently the ETL job stalls for 90+ seconds which seems to correlate to this query that Amazon seems to be running on my T2.micro instance. Is there anything I can do to prevent my query from being affected by this?


texts:
I've been given an oracle DMP file that was created on a solaris system which I need to restore to a windows system.

I've created the DB with the correct blocksize, but when I try and do the restore it complains that it can't create the folders.

I've created the folder in Windows and in my imp command i've specified the directory, but it still fails.

How can I resolve this?

Thanks

Here is the full error message

IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/home/oradata/d_test01/tools01.dbf'
ORA-27040: file create error, unable to create file

What do i need to specify in the restore command to change the restore location.  As I said previously the backup was created on a solaris based system and I have trying to restore it on Windows

All i've been given is the dmp file.  It was created by a consultant who no longer does work for us.

Hope someone can help.


texts:
I am a brand new user to the MySQL cluster world (literally hours). I was following a tutorial just to get a simple cluster set up (one management node on one machine, a second machine with a SQL node and a data node). I seem to have a functioning management node and I believe I have a functioning SQL node, but my data node acts like it's connecting, but the management node says it isn't connected.

Management Node Config:

[ndbd default]
NoOfReplicas= 1
MaxNoOfConcurrentOperations= 10000
DataMemory= 3072M
IndexMemory= 256M
TimeBetweenWatchDogCheck= 30000
DataDir= /var/lib/mysql-cluster
MaxNoOfOrderedIndexes= 512

[ndb_mgmd default]
DataDir= /var/lib/mysql-cluster

[ndb_mgmd]
NodeId=1
HostName= 192.168.0.5

[ndbd]
NodeId= 2
HostName= 192.168.0.6

[mysqld]
NodeId= 4
HostName = 192.168.0.6

# choose an unused port number
# in this configuration 63132, 63133, and 63134
# will be used
[tcp default]
PortNumber= 63132

SQL Node Config:

[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.0.5
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=192.168.0.5

Data Node Config:

[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.0.5

[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.5

When I run sudo /usr/local/mysql/bin/ndbd I get:

2012-04-22 22:56:44 [ndbd] INFO     -- Angel connected to '192.168.0.5:1186'
2012-04-22 22:56:44 [ndbd] INFO     -- Angel allocated nodeid: 2

But when I check its status on the management node, I get:

Node 2: not connected

I'm sure as a total noob I'm just missing something, but I would really appreciate if someone could point me in the right direction.


texts: Query to report disk space allocation and used space

texts:
We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage).

I'd like to write a query (report) for a single database indicating the "Currently allocated space" and "Available free space" attributes under the Tasks > Shrink > Database option in SQL Server Management Studio.

Then I'd like to convert those numbers to TB and total each database to get a rough estimate of how much space we have left.  Can these fields be accessed via a T-SQL query?  If so, what would the query look like?


texts: Inventory Database Design? Get historical data from DB for reporting & accounting

texts:
Following is the design of my db:

Product:

ID
Name
Quantity in Hand (as on date - total across all format)
Reorder_point
Remarks

Warehouse:

ID
Name
Address
Remarks
is_default

Inventory:

ID
Product FK
Warehouse FK
Quantity
Purchased_on (This field is to be used to consider product ageing)
Purchase Rate
Tentative Sales Rate
MRP

Purchase Order/Sales Invoice Table (Both are similar)

ID
Total
Date
Warehouse FK
Vendor FK/Customer FK
Amount Paid/Amount Received

Line Item for Purchase/Sales

Product FK
Unit FK (We have a unit table to maintain conversion from one unit to other, eg: Dozen to Nos, etc)
Quantity
purchase_price/sales_price

Now, I wanted to know:

Is this a good DB design, or does it have an inherent fault somewhere?
Suppose two products, varying only in flavor - how would that be reflected here? (They may not need separate product ID/SKU)
Most importantly, I can always get the inventory in hand, but let's say, today is 2nd May & I need to know inventory details as on 15th April, how can this db design do it? Or is there a better DB design to handle that?

Edit

As of now let's assume purchase and sales happen immediately (as in, there's no purchase order & sales order in between).

Now, the issue with the inventory table is I can't calculate the historical value of the inventory. As in, lets say the value of the inventory in 3rd May s $500000, how can I find out the inventory value in 10th April from the data I have here?


texts: I ran a full backup on SQL server 2008, but the transaction log stayed in tact. I though a full backup would make a new transaction log. What do I need to do to get my trn to a normal size?


texts: Filegrowth recommendations for Azure Managed Instance

texts:
On Azure SQL Managed Instances, im seeing new database getting 16MB filesize growth on database and logfiles (i changed log to 128MB). I haven't been able to find online docs from MS on recommendation here. 16MB is smaller then what i would normally run with,but since i'm new to Azure MI i not sure if i should make changes here.

SELECT [type_desc], [name], [growth], [growth] * 8 / 1024 AS GrowthInMB FROM sys.database_files

type_desc   name     growth GrowthInMB
ROWS        data_0  2048    16
LOG         log     16384   128
ROWS        data_1  2048    16
ROWS        data_2  2048    16
ROWS        data_3  2048    16
FILESTREAM  XTP 0   0

Anyone with actual field experience willing to chip in?


texts:
I am building a Web Application which is attached to a Database which is in a Web Server(which is at unknown remote location). I want to execute a Stored Procedure daily at a specific time. I searched and found this.

I am able to connect to my Database. But not to Server Agent.

Please help me to solve this problem. Thanks in Advance.


texts:
I am trying to execute following on sqlDeveloper

SELECT salary FROM emp ORDER BY salary DESC LIMIT 4, 1;

But it is throwing following error:

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 1 Column: 44


texts: UFT to SQL Server conecction which is located at different domain

texts:
I need to connect to a SQL Server through UFT (Unified Functional Testing) but the problem is SQL Server is present at different domain whereas QTP (HP QuickTest Professional) is present in a different domain.

I am able to connect to SQL Server manually from my domain using

runas /netonly /user:mydomain\TestUser
    "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe

Where mydomain is the domain in which the SQL Server is present. This is working fine when I connect it manually, but it failed to establish a connection when I tried to connect it through QTP.

Connection string that I'm using:

provider=sqloledb.1;server=10.130.15.11,1421;uid=testuser;password=mypassword"

The password in the connection string is different from the password that I provide after executing above runas command. I also tried with passing both the domains in UID in connection string in QTP but still not able to connect.