texts:
In SQL SERVER the

SP_HELP 'table_name'

returns a description of each column including it's type, length, nullable state etc. I'm using this info to auto-generate DTOs (classes) and mappings.

If I want to do this for an inline SQL statement (which includes aliases, joins etc.) I create a view temporarily with the definition being my SQL statement and then run sp_help for this view - which gives me all I need. After that I just delete the view.

The question:

Is there any other - simpler way of getting this info for inline SQL - preferably without going through the view creation?


texts: Set-ClusterParameter : Parameter 'OverrideAddressMatch' does not exist on the cluster object while configuring failover cluster resources

texts:
I configured an availability group in Azure using this guide. I have 2 failover cluster nodes with SQL Server 2016. User 'Install' has administrative permissions on both machines and sql server instances. I used this guide to create an avilability group listener but I failed on the final step. According to the guide I had to log in to primary replica machine as 'Install' user and create IP resource for availability group and then execute configuration script. Configuration script is

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$CloudServiceIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}

But this script always fails with error:

Set-ClusterParameter : Parameter 'OverrideAddressMatch' does not exist on the cluster object 'Resource1'. If you are trying to update an existing parameter, please make sure the parameter name is specified correctly. You can check for the current parameters by passing the .NET object received from the appropriate Get-Cluster* cmdlet to “| Get-ClusterParameter”. If you are trying to update a common property on the cluster object, you should set the property directly on the .NET object received by the appropriate Get-Cluster* cmdlet. You can check for the current common properties by passing the .NET object received from the appropriate Get-Cluster cmdlet to "| fl ". If you are trying to create a new unknown parameter, please use -Create with this Set-ClusterParameter cmdlet.

What should I do to get rid of this error? It seems like permission issue.


texts:
I provide a database system to a number of customers.  The database is installed locally on Oracle.

We periodically upgrade the database structure (either adding new fields, renaming old fields, changing things around), as the product grows.

One of my customers wishes to add their own custom views to the database.  They've assured me they will add the view to a different schema to the live schema.  However, I am concerned that the inclusion of a view might affect future upgrade scripts.

If a view references a table that is to be altered, will the presence of the view prevent the table from being altered?


texts: Why does MySQL consecutive JOIN give an unexpected result?

texts:
I had 3 tables which were Category, Product and Vendor. Then I tried to make a consecutive join 3 tables in 1 query to count the product for each category.

If a product or it's vendor is deleted, it will be excluded from the list. Moreover, if there was no product available, I still need to keep the category with the 0 count result. Because all tables are using the soft-delete strategy, I need to filter deleted items from the Query to get a proper count result. Then, the problem start from here.

A quick sample is below

Category
--|----------|----------|
id|name      |deleted_at|
--|----------|----------|
 1|Food      |      NULL|
 2|Stationery|      NULL|

Product
--|-------|---------|------|----------|
id|cate_id|vendor_id|name  |deleted_at|
--|-------|---------|------|----------|
 1|      1|        1|Bread |      NULL|
 2|      1|        2|Milk  |      NULL|
 5|      2|        2|Pencil|      NULL|

Vendor
--|-------|----------|
id|name   |deleted_at|
--|-------|----------|
 1|Woolies|      NULL|
 2|Cole   |2020-01-18|

Query1 gave an unexpected result

SELECT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product
FROM
    category c
LEFT JOIN product p
    ON p.cate_id = c.id AND p.deleted_at IS NULL
INNER JOIN vendor v
    ON v.id = p.vendor_id AND v.deleted_at IS NULL
GROUP BY c.id;

cate_id|cate_name|total_product|
-------|---------|-------------|
      1|Food     |            1|

Query2 gave a correct response.

SELECT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product
FROM
    category c
LEFT JOIN (SELECT p1.* FROM product p1 INNER JOIN vendor v ON v.id = p1.vendor_id AND v.deleted_at IS NULL)
    AS p ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id;

cate_id|cate_name |total_product|
-------|----------|-------------|
      1|Food      |            1|
      2|Stationery|            0|

I'm not likely sure but I guess the Query1 did the LEFT JOIN between Category & Product tables first, then did the INNER JOIN on the JOINED Record & Vendor, finally leading to an unexpected result. Am I correct?

Is there any way to achieve this purpose without using a sub-query?


texts: Killing long running queries with user notification

texts:
Is there any way to inform user that your query has been running more than x minutes, so admin has killed your session. This is especially to the query or read only user.

I have a function which will do exactly what I want (Im running the function periodically). But, I couldn't notice the user that their connection being killed by admin.

Users are using PgAdmin/psql/DBeaver as client. I am not sure, how can I show the message to them. Is there anyway at all notify the user in their session window?

(Initially I tried to do kind of logon trigger. Unfortunately, postgres doesn't have the feature yet)


texts: Mysqldump multiple tables with where clauses in single transaction

texts:
Suppose, we have 2 tables: t1,t2 in database db:
Expected the below code to dump tables based on conditions specified in the where clauses:

mysqldump --login-path=login_path \
    --single-transaction db t1 \
    --where="user_id>200" , t2 --where="id>200" > /tmp/users_data.sql

However the following error is popped:

  mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM t1
  WHERE id>200 Unknown column 'id' in 'where clause' (1054)

How to dump multiple tables with where clauses  using mysqldump correctly? If it's not possible, is there a workaround?


texts:
Reading this answer I have a question about it:

How can I create a certificate with a private key in my app database?


texts: Can a trigger with an "insert into" statement cause Intext Exclusive locks for small insert operations?

texts:
We have a trigger that runs AFTER an insert in a table (Nodes) that creates entries in another table (NodeClosures) that selects entries (6 on average) from the NodeClosures table and inserts them as new rows with a different value in a certain column.  The insert operations in the Nodes table occurs one at a time (so no single large batch insert operations, Entity Framework) and the select/insert operation into the NodeClosures table is causing an intent exclusive lock.  I'm uncertain if this is due to lock escalation (not sure how as neither insert operations are inserting more than 6 rows).  Some additional information, Read Committed Snapshot Isolation is being used as well as Delayed Durability Transactions.
I have included the full trigger for reference (MSSQL 2017):

CREATE TRIGGER [tr_dbNode_insert]
ON [dbo].[DbNodes]
After INSERT
AS
BEGIN
    -- Insert self closure
    insert into DbNodeClosures (ParentID, ChildID, Depth, AccountID)
    select Id, Id, 0, AccountID
    from inserted;
    -- Insert parent's closures as it's own + 1
    insert into DbNodeClosures (ParentID, ChildID, Depth, AccountID)
    select closures.ParentID, inserted.Id, closures.Depth + 1, inserted.AccountID
    from inserted join DbNodeClosures as closures on inserted.ParentID = closures.ChildID;

END


texts:
Version in use is mysql-5.5.24.

In the Enterprise version of MySQL innodb_buffer_pool space is released after a while, however this does not appear to be happening in the community version.

I am not seeing free space in innodb_buffer_pool even after all the apps are shut down.

Is there such a difference between the Enterprise and Community versions of MySQL?


texts: Two tables, one foreign key, and two primary keys

texts:
Say there are two tables:
Student:
StudentID(Primary Key)
Name

StudentTeams:
StudentID(Primary Key, Foreign Key)
TeamName

StudentID in StudentTeams table references Student table primary key. What would be the pros and cons of such a structure in terms of relational integrity?


texts: Insert with function result - prevent duplicates

texts:
I have a MS Sql function which has some logic to process the next ID of an element (concats letters with numbers based on some business logic, also based on a select count()).

Somehow, during the last days (in production environment), since the system is being used by more concurrent users we got some doubles.

The existing logic is very simple:

insert into XXXX (A,B,C,D,E)
select dbo.GiveMeNewID(getdate()), @ParamB, @ParamC, @ParamD, @ParamE

Somehow, the GiveMeNewId gave back the same ID in two parallel requests.

This funcion is used at the moment in different places, inside one Stored Procedure and also from an "dynamic" SQL build from a "C# client".

I do not want to implement a "singleton" layer in some middle man software.

Any ideas on how to solve this issue?
Thanks.

P.S: for testing I had the following lines inside the SQL function in order for me to run different the insert in different windows.

/*********************************************************/
declare @dtStart datetime=getutcdate()
while datediff(second,@dtStart,getutcdate()) < 10
begin
    set @dtStart = @dtStart
end
/*********************************************************/


texts:
At work we have a problem that has now occurred more then once.

Problem is that query is in state of converting HEAP to MyISAM and it paralyzes our server( query in this state is not able to kill itself and kill flag is not evaluated until only after the operation which seem to take FOREVER in our case)

The following global MYSQL variables appear to us:

| tmp_table_size      | 17179869184 |
| max_heap_table_size |  8589934592 |

Any ideas what we should do?

Should we perhaps adjust the sizes of tmp_table_size and max_heap_table_size to have the same value?

Would that help?


texts: Trying to compare several similiar lists on one table to produce a concatenated result

texts:
So imagine that four friends are planning on moving into a house together.  They put together a list of stuff that they all own.  Obviously, there is some overlap but there is also unique things among them.

Table LIST_OF_STUFF is a fairly simple table:

LIST_ID   ITEM
47        Baseball
47        Hat
47        Gloves
47        Sparkle Shoes
91        Baseball
91        Sparkle Shoes
91        Bubble Gum
91        Hat
91        Fish Tank
91        Book of Spiders
103       Silver Spoon
103       Fiddle
103       Trumpet
103       Paper Towels
103       Fireworks
103       Mosquito Netting
103       Cure for the Common Cold
295       Baseball
295       Fireworks
295       Trumpet
295       Bubble Gum
295       Hat
295       Gloves
295       Christmas Tree

This table contains lists unrelated to these four people (e.g. shopping lists, my favorite vacation spots, etc.) but the specific LIST_IDs I'm interested in are shown above.  I want to be able to make results similar to the following:

LIST_47          LIST_91          LIST_103...
Baseball         Baseball         (null)
Hat              Hat              (null)
(null)           Fish Tank        (null)
(null)           (null)           Silver Spoon

I initially thought a FULL OUTER JOIN on LIST_OF_STUFF would do the trick but it didn't return all the values on the other lists.  It would only be compared to LIST_47:

SELECT LIST_47.ITEM LIST_47_LIST
     , LIST_91.ITEM LIST_91_LIST
     , LIST_103.ITEM LIST_103_LIST
     ...
FROM LIST_OF_STUFF LIST_47
FULL OUTER JOIN LIST_OF_STUFF LIST_91.ITEM = LIST_47.ITEM AND LIST_91.LIST_ID = 91
FULL OUTER JOIN LIST_OF_STUFF LIST_103.ITEM = LIST_47.ITEM AND LIST_103.LIST_ID = 103
...
WHERE LIST_47.LIST_ID = 47

What is the best method to return such information?


texts: Using #temp to work around Reporting Services blocking issue

texts:
Having utterly failed to resolve why a perfectly acceptable but admitidly large stored procedure was suffering the blocking process lck_m_s when called via a report in SQL Reporting Services I have been forced to implement the following as a work around.

Create a #temp table
Insert the exisiting stored procedure into the

temp table
Select from #temp
Delete #temp

This process results in a report generation time of 10 seconds vs the 2 minutes I was suffering when calling the origional report stored procedure via reporting servces.

This to me feels like a dirty hack but it works and works well from the perspecitive of the users.

Is there a better method I could have used?

Edit:

  I'd like to get some more information on this blocking issue since
  that is the root issue. –  Zane

More info here
Reporting Services TempDB lock blocking report generation
However, the issue mysteriously reappeared and then as I was finishing the hack implementation it disappeared, again!

  Did you try using snapshot isolation? –  Jon Seigel

Yes, but it had no affect, still blocking seen similar to this issue Shared locks taken under Read Committed Snapshot Isolation (RCSI)

  Did you try using the temp table in the stored procedure? –  Shawn Melton

No, I don't understand how to implement it that way round, why would it be of benefit?


texts: Simulate production workload on test environment

texts:
We are on SQL Server 2008 R2 Standard Edition. Some tables are highly fragmented. I want to see if defragmentation will improve performance and is worth the effort and tables/indexes being locked during the process. Therefore I want to restore the full backup on a test environment and simulate a live environment.
What is the best way to follow? How can I capture the events going on in a live environment for some period? What tools are available for this?
Thank you


texts: Number of virtual tables that specified user is able to write queries in PostgreSQL

texts:
I have one question about PostgreSQL that I can't understand. As it seems so far, it needs some of bruteforce in tables. Well, I did that lots of time and I found nothing. So, firstly I will cover my goal.

What I need to do, is to get number of virtual tables that specified user is able to write queries in PostgreSQL. In other words, there could be user foo123 and there could be, let us assume, four tables: table1, table2, table3 and table4. And there could be possibility that user foo123 is able to write queries for table1 and table2, but not for table3 and table4. So, by query doing my task I should get 2 (there is no difference what column name would be - I can think any name for column).

Now I will cover what I tried to do and what I have achieved. For now, I can see that I have to use information from information_schema. But there is one problem. I don't know where I can find users in information_schema and lists of tables, associated with these user by any column. Well, until now I found three tables about granting privileges to do something: information_schema.table_privileges, information_schema.column_privileges, usage_privileges. I think that I have to use information from table information_schema.table_privileges (columns that I should be interested, I think, are grantee and table_name). And it could look like as a path for finding solution, but one small problem. If we would treat grantee as user who is able to do something with virtual table, then there is one thing. For example, database can have lots of users, who are able to use virtual tables in any way. But these users are just in one table: pg_user, i.e. there are no all users listed anywhere that I could take any user to check the of number virtual tables he is able to write queries for. In other words, in table information_schema.table_privileges I see just grantors and users online. For them I can check privilege types, but for no user that is offline.

So, extended version of my question, according to what I have done, could be such questions:

Is my path for finding number of virtual tables for user specified correct or not, i.e. am I doing right or wrong trying to analyse information_schema.table_privileges table?
Why is that so that I can see all database users listed anywhere else, except pg_user. And table pg_user doesn't have any information about privileges using virtual tables.
If my path for finding number of virtual tables is not correct, then in what way I should find that number?

I think that's all I've got. If there are some misunderstanding about my question, I will try to give more information.


texts:
Had three databases connected to SQL server 2012, unfortunately, now I can't see any of them under the Databases part of the tree in SQL Server Management Studio. The files (.mdf) are still on the disk, but when I try to reconnect them, I get this error once I click on the mdf file,

Failed to retreive data for this request (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
  The server principal "IESVE\user" is not able to access the database  "model" under the current security context (Microsoft SQL Server, Error: 916)

I thought this might be a problem with the management studio, but when I try to connect via ADO, I get this error,
[DBNETLIB][ConnectionOpen(Invalid Instance())] Invalid Connection

All of this worked before and the only thing I can think of is a recent windows update.

If anyone's got any ideas on how to fix/diagnose this, I would be very grateful,
Thanks
James

Edit; Running as administrator gets me a bit closer to reattaching the databases, but now I have this error  . I'm wondering if my SQL server version has been downgraded, judging by that error message.

Thanks,
James


texts: MySQL: What is the maximum number of cores used by a single MySQL server?

texts: What is the maximum number of cores used by a single MySQL server in a production setting?


texts: Designing a platform: one database or multiple databases?

texts:
We are building a web platform that incorporates multiple services, each with its own underlying data.  These services are being built independently following the principles of Service-Oriented Architecture, but they transact against potentially related data.  We are considering whether these services should share one big database or each have their own database.  (We are planning to use SQL Server 2008 Enterprise on a Windows 2008 cluster.)

Some of the advantages to each approach we have already considered include:

Single Database

Relating data from different services can be bound together by foreign key constraints
Analytic extracts are simpler to write and faster to execute
In the event of a disaster, restoring the platform to a consistent state is easier
For data that is referenced by multiple services, data cached by one service is likely to be used soon after by another service
Administration and monitoring is simpler and cheaper up front

Multiple Databases

Maintenance work, hardware problems, security breaches and so forth do not necessarily impact the whole platform
Assuming each database is on separate hardware, scaling up multiple machines yields more performance benefits than scaling up one big one

From an operational perspective, is it more advantageous that each service in this platform get its own database, or that they all go in the same database?  What key factors inform an answer to this question?


texts: PostgreSQL - cannot create RULE from stored procedure

texts:
I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue when creating rule from function implemented in PL/pgSQL.

Here is an excerpt from SQL script for rolling out changes:

CREATE TABLE person_x (
  firstname TEXT NOT NULL,
  lastname  TEXT
);

CREATE TABLE person_y (
  firstname TEXT NOT NULL,
  lastname  TEXT
);

CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
  RETURNS INTEGER AS $test_create_rule_from_stored_procedure$
BEGIN
  CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
  DO INSTEAD
    INSERT INTO person_y (
      firstname,
      lastname)
    VALUES (
      new.firstname,
      new.lastname
    );

  RETURN 1;
END;
$test_create_rule_from_stored_procedure$ LANGUAGE plpgsql;

But I get:

psql:rollout.sql:348: ERROR:  42601: syntax error at "person_y"
DETAIL:  Expected record variable, row variable, or list of scalar variables following INTO.
CONTEXT:  compile of PL/pgSQL function "test_create_rule_from_stored_procedure" near line 4
LOCATION:  read_into_target, gram.y:2322

But when I run query for creating rule (the query is the same as in function) directly from SQL script everything is fine.

Is it possible to create rules from stored procedures? Or I need to compose query for creating rule as string and then execute it with aid of EXECUTE?

PS I use PostgreSQL v. 8.3 and I cannot upgrade.


texts: mysqldump is not dumping data by default

texts:
mysqldump is dumping only the table structures, without data, by default.

mysqldump -u user -d database -p

Which is the option to dump is with data?
How can I configure it, to include the data? (I guess it's possible with a setting in /etc/mysql/my.cnf)


texts: How to create a second socket for mysqld

texts:
I would like to create second mysqld instance.

How can I create a second socket file?

I tried copying /var/run/mysqld/mysqld.sock and create an empty file mysqld2.sock but no luck.


texts:
As far as I understand, using or should be avoided as much as possible as rule of thumb in query.

I have two tables having one-to-many relationship.

And I have joined two tables like below.

SELECT A.*, group_concat(B.info separator ','), group_concat(B.info2 separator ',')
FROM A
JOIN B
ON A.id = B.a_id
GROUP BY A.id;

Does using GROUP_CONCAT affect the performance like or does in MySQL? If it does, what would be the efficient way to join those tables?

Any suggestion or advice would be appreciated. Thank you.


texts: I have a relation between two tables.  The foreign key table can have a row related in the primary table.  There's a way to enforce the value of the FK column to be NULL or one of the values of the PK column in the primary table?


texts: Handling Reporting Services and SharePoint Integration considering deprecation

texts:
With one of our clients, we're currently faced with a dilemma. SQL Server Reporting Services Report Server (SharePoint mode) seems to have been deprecated, since the last version to support it is SQL Server 2016.
Now our client wants SharePoint integration with Reporting Services and SQL Server 2016 is currently supported, so there is the option to go that route. I just feel uneasy building something for a client with a technology that is essentially dead.
So, I've been researching on what sort of options there are going forward (SQL Server 2017 onwards). An article by John White pointed out that this is a good thing, because it allows for SSRS Native mode to be more tightly integrated into SharePoint. But his article is from the end of 2016 and points at future improvements that I'm battling to see.
Microsoft's own SQL Stream Blog addresses this issue in November 2016, with mention of three scenarios that they planned to focus on going forward. Namely: Embedding reports in SharePoint pages; Reporting on data in SharePoint lists; and Delivering reports to SharePoint libraries.
What I can find from Microsoft after that date is sparse. I only found one other article on the SQL Server Blog that mentions SharePoint, with the announcement of a "Report Viewer web part".
My question is, has anyone faced this dilemma before? And how what solution did you go with?
I know we can work with PowerShell scripts and create embedded reports on SharePoint, but it doesn't seem very elegant considering what "SharePoint mode" was.
EDIT: I realise I asked a question that warrants an answer that's more of an opinion than a fact. What I am interested in, is approaching Report Server - SharePoint integration from a SQL Server 2017 and newer perspective.


texts:
I have a postgreSQL database on which I am experiencing huge latency issues when trying to update a json field using the following query:

UPDATE collections
SET "layout" = jsonb_set(layout, '{actions}', '[{"actionId":45,"actionName":"my_action"}]')
WHERE id = 1

It takes about 30sec to complete even though it does not seems like a big query.

Furthermore the query seems to block all other incoming queries even the simplest SELECT (on the same collection).

Good to know too, it happens only on our production database. On local databases it's pretty fast (so I'm kind of assuming it has to do with the traffic and locks on that table).

It's also instantaneous on a replica of our production database. Which makes me think it might be related to some things happening under the hood that lock this query.

If anybody has any hints on what could cause those troubles, it would be much appreciated.

Regards,

PostgreSQL: v9.5.20, 8GB RAM, 74/256GB disk used


texts:
This is my mongoose schema -

var userDestinationSchema = mongoose.Schema({

    uid: String,
    update_time: Date,

    location:{                  //<INDEXED as 2d>
        lon: Number,
        lat: Number
    }
});

var userDestinationModel = mongoose.model('userDestinationModel', userDestinationSchema);

How do I query the model for geoNear?

I tried doing this and it ain't working.

userDestinationModel.geoNear([parseInt(req.params.lon,10),parseInt(req.params.lat,10)],
              { maxDistance : 50, spherical : true },
              function(err, results, stats) {
                   console.log(results);
                });

maxDistance : 50

What is the unit? Km?


texts: How to make conditional ordering for two or more columns

texts:
In MS SQL Server 2005 I am writing one query with conditional sort and my problem is that I do not know how can I sort conditional using two columns?

If I wrote code like this it is working normaly

select
    *
from
    table
order by
    case @pkr
           when 'kol' then kol
           when 'nci' then nci
    end

I do not know how to make conditional ordering for two or more columns

select
    *
from
    table
order by
    case @pkr
        when 'KOL-NCI' then kol,nci
        when 'kol-MPCI' then kol,mpci
    end

There is an idea to make dynamic TSQL and use sp_executesql but I am still looking for a better idea?


texts: Reasons for disabling statistics auto update?

texts:
I just learned that a client company I work for has decided to keep  the auto update statistics options off for some of their SQL Servers, and the DBAs manually troubleshooting performance issues when they arise.

However, this kind of does of not make sense to me. Why would you want to prevent the statistics from being updated?


texts: Can I have full backups on multiple disks?

texts:
Currently, I've only got one backup file, IMTDB.bak, and it's on the same HDD as the Database itself. I want to increase the redundancy of this DB backup by essentially "copying" it to another disk, but I get an error "Backup failed for Server, The media is formatted to support 2 media families". I think this means that when I created the backup, it's only meant to put the backup on one drive, and I can't retroactively add more. I want to migrate the backup to this other drive (really just copy it over), but I don't want to have to delete the current backup to do it.

What do I do? Am I safe in just copying IMTDB.bak to a folder on the other drive?


texts: Select unique value whereas the time is highest in the most optimal way

texts:
Given a simple, with a text and time column, I want to select X unique values from the text column, whereas that row contains the highest value for time.

INSERT INTO `test` (`id`, `text`, `time`)
VALUES
    (1, 'test1', 1),
    (2, 'test1', 3),
    (3, 'test1', 2),
    (4, 'test2', 1),
    (5, 'test2', 100),
    (6, 'test2', 20) ;

The query that meets most of my requirements is:

SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    a.`text` ASC
LIMIT 0,50

For small tables, this works perfect. Though in my table (300k+ rows) it makes mysql crash, due to the subquery.

Is it possible to optimize this query? If it cannot be optimized, would it be possible to select the last inserted unique values for text? (the id and time are theoretically uncorrelated, though in 99% of the cases a correlation will be found, whereas the higher the id, the higher the time)


texts: how to create double entry book keeping for member purchases, fees, charges

texts:
My application records member fees, purchases, overdue charges and payments.

I have read many articles that suggest the use of double entry accounting.

Currently here's my table and I want to convert it to double entry or better database schema.

MembersAccount(id, name, address)
Purchases(member_id, amount, product_id, date)
Fees(member_id, amount, fee_type, date)

PurchasePayments(purchase_id, amount, date)
FeePayments(fee_id, amount, date)

Any suggestion is much appreciated.


texts: MySQL slave doesn't reconnect to master after master is rebooted

texts:
I've got a new MySQL replication setup with one master and one slave.

In all of the following scenarios, the slave automatically reconnects to the master and resumes replication where it left off:

mysql service is restarted on the slave host
Slave host is rebooted
mysql service is restarted on the master host (reconnection happens after the default 60-second slave I/O reconnect timeout)

However, if I try rebooting the master host, the mysql service on the slave host neither detects that the master went down nor resumes replication when the master becomes available again.  In this scenario:

I see no entries in the mysql error log on the slave host
Running show slave hosts; on master (once it comes back up) shows no hosts connected

UPDATE:
Looking more closely at the mysql errors logs on the master, I suspect that the master is not completing a clean shutdown because when stopping the service I see the logs:

2013-07-29 17:43:45 5281 [Note] /usr/sbin/mysqld: Normal shutdown

2013-07-29 17:43:45 5281 [Note] Giving 1 client threads a chance to die gracefully
[snip]
2013-07-29 17:43:48 5281 [Note] InnoDB: Shutdown completed; log sequence number 146430781066
[snip]
2013-07-29 17:43:48 5281 [Note] Shutting down plugin 'binlog'
2013-07-29 17:43:48 5281 [Note] /usr/sbin/mysqld: Shutdown complete

130729 17:43:48 mysqld_safe mysqld from pid file /opt/mysql/run/mysqld.pid ended

...while after rebooting, the mysql error log on master ends with:

2013-07-29 17:46:58 6441 [Note] InnoDB: Starting shutdown...

...and is missing the Shutdown complete message.


texts:
I have a big database (200GB+) that contains some log info. And I want to speed up SELECT queries and stored procedures. I have a table with a GeneratedOnUtc datetime column, and have a non-clustered index on it.

I'm thinking to change it to a clustered index.

Reasons for:

Big amount of data (~40 millions rows)
Column is used in multiple Where clauses (between, >, <)
Column is used in ROW_NUMBER() OVER (ORDER BY d.GeneratedOnUtc asc) AS Row queries

Reason against:

Large amount of inserts (~60k per day) may lead to frequent B-tree rebuilds.


texts: building structure for employee office relation

texts:
I have a table defined like this

  empid   fromdate        todate       office_ID
    1     2012-03-01      2014-04-2      1
    1     2014-04-03      ----           2
    2     2012-03-01      2014-04-2      2
    3     2012-03-01      2014-04-2      1

If the employee is currently working at some office then todate will be not known hence it is null. This logic can be changed if you suggest something better.

From this table I want to fetch an employee's location(s) for a given month. For example, for May 2005 where was employee 1? For that I have designed this query

select *
from tbl_emp_office
where (year(From_date)*100 + month(From_date)) < '201505'

This structure is not giving the exact solution to my problem. Can you suggest more suitable structure for this?

EDIT
fromdate has data type Date.


texts: Finding differences between a master and slave when pt-table-sync fails

texts:
I have a table that's showing some differences on a master and slave according to pt-table-checksum (PTC).  I've found some verifying differences beyond that.

Certain tables in the mix have composite primary keys which, while work well enough for PTC do not so much with pt-table-sync (PTS) to try and find the differences.  PTS does not seem to honor the newer options of PTC to restrict the depth of composite key searches.  End result is the a 5.5Mrow table is left spinning for hours w/ PTS.  I'm somewhat convinced there's an infinite loop bug in PTS as the output has just started spitting out the a correction for the same exact record (and nothing else at this point) over and over.

So, really this post is more about alternatives than trying to get this tool to run right.

The most straight forward alternative that comes to mind is flush table with read lock on the master; select * into outfile on master and slave; unlock and diff.  However this table is fairly active and I cannot afford it being locked out on the master that long.

I was hoping to do something like start a transaction in repeatable read isolation level and select out from the master there.  However I can't find a way to have the slave be stopped specifically at the point in the transaction history as on the master.  Things like show master status continue to update after starting a transaction so I can't simply start slave until then.

There's no guarantees about atomicity to "quickly" do "show master status; begin;"

The only other solution of a non blocking method is to write a custom script that might be able to more efficiently do a PTS type thing but with application specific domain knowledge about how the columns of the table in question are generated.

While it might come to that I was hoping to find a more generalized solution for the future when prebuild wheels like PTS turn out not as well rounded as I'd like.


texts: How to avoid timeouts during big DML operations

texts:
I have a big insert script I need to run. Its about 55,000 records and 160 columns. The script is already created and I can't create it again.

The problem I have is that this runs for about 4 hours or so, and during that time the system that uses this database gets really slow and timeout a lot.

I would not care if my INSERT is slower but it shouldn't impact other users.

I was thinking in doing some batch of let's say 500 rows and use the WAITFOR, but was wondering if there could be a better option for doing this.


texts: MySQL select takes 0.2 sec; equivalent UPDATE takes 1.5 minutes!

texts:
This query runs in 0.2 seconds and finds 9 rows:

SELECT S.id, S.frame, S.active,
  GF.groupNum, GF.frameDesc
FROM skus S
  INNER JOIN group_frames GF ON (GF.frame = S.frame)
WHERE GF.groupNum = 204 AND GF.frameDesc LIKE '%lumbar%';

This query runs in 1.5 minutes and updates the same 9 rows:

UPDATE skus S
  INNER JOIN group_frames GF ON (GF.frame = S.frame)
SET S.active = 0, S.updated_by=101355, updated_at = NOW()
WHERE GF.groupNum = 204 AND GF.frameDesc LIKE '%lumbar%';

... while this incredibly-hackish version runs in 0.5 seconds and updates the same rows:

UPDATE skus S SET S.active = 0, S.updated_by=101355, updated_at =
NOW() WHERE S.id IN (   SELECT id FROM (    SELECT S1.id    FROM skus S1
        INNER JOIN group_frames GF ON (GF.frame = S1.frame)
WHERE GF.groupNum = 204 AND GF.frameDesc LIKE '%lumbar%' ) S3)

(using a dummy inner-query to defeat the query-optimizer complaining about the same table being used in a FROM clause)

Why on earth did the second query take so long?!


texts: Re-installing Oracle 19c on Windows 10: Error in Process: ORACLE_HOME\perl\bin\perl.exe. Error while executing ORACLE_HOME\rdbms\admin\dbmssml.sql

texts:
Question edited for clarity.
Goal: install Oracle 19c on windows 10 and make a PDB.
Background: I installed Oracle on 2 computers incorrectly (unzipped the oracle 19c file into C:\App\db_home1, instad of into C:\App\oracle\product\19.3\db_home1). I tried fixing this by uninstalling (manually by following these instructions: https://www.youtube.com/watch?v=EHFbk_6ThAQ ). Now i'm trying to re-install.

Problem reinstalling on computer 1:
When i run setup.exe I get an error on the installation step that says [INS-20802] Oracle Database Configuration Assistant Failed.
When I open "installActions2020-09-05_03-48-30PM" I get this error at the bottom: INFO:  [Sep 5, 2020 4:38:02 PM] [FATAL] Error while executing "C:\App\oracle\product\19.3\db_home1\rdbms\admin\dbmssml.sql". Refer to "C:\App\oracle\cfgtoollogs\dbca\orclglobal\dbmssml0.log" for more details. Error in Process: C:\App\oracle\product\19.3\db_home1\perl\bin\perl.exe
When i go to C:\App\oracle\cfgtoollogs\dbca\orclglobal\dbmssml0.log, it's blank.
Trying Joe's suggestions:
C:\>echo %ORACLE_HOME%
%ORACLE_HOME%

C:\>where sqlplus
'where' is not recognized as an internal or external command,
operable program or batch file.

C:\>C:\App\oracle\product\19.db_home/deinstall/deinstall
The system cannot find the path specified.

C:\>

^ but i have sql plus
^ I never had an env variable named oracle_home. this returns the env variable for the current oracle installation (that isn't complete)
C:\>echo %path%
C:\App\oracle\product\19.3\db_home\bin;C:\Program Files (x86)\Applied Biosystems\QuantStudio Design
'Analysis' is not recognized as an internal or external command,
operable program or batch file.

I think the new installation that i'm trying to install is the only thing the installer is picking up. But I don't know what to do with this message. Where do i run this?

Update: I can connect to the CDB i create, but there's no PDB$SEED.

But I have a seed directory C:\App\oracle\oradata\ORCL\pdbseed

Problem reinstalling on computer 2:
INS-30014: Unable to check whether the location specified is on CFS
Here's what Oracle has to say about this error:

Cause: The location specified might not have the required
permissions.

Action: Provide a location which has the appropriate
required permissions.

All the users (me, admin, all possible users) have full permissions on the D drive.
Another solution I found to INS-30014 is to disable the network adapter. I tried that and it still didn't work. (My ethernet was already disabled, so i disabled the internet and that didn't change anything).


texts:
I'm doing a migration from Oracle to SQL Server and, for one of the tables where we have 3 million records, it is taking a lot of time to complete.

When I check the activity monitor, I can see the a wait type of PREEMPTIVE_COM_SEQSTRMREAD. Please help me understand what could be the issue.

I'm using linked server to connect Oracle from SQL Server.


texts: mysql trigger not working with if statement

texts:
CREATE TRIGGER updateNewEmp
AFTER UPDATE ON BasicDetails
FOR EACH ROW  BEGIN
    if(NEW.MiddleName<>OLD.MiddleName) then
      insert into BasicDetails_audit(MiddleName,`State`,Country,Community)
      VALUES(NEW.MiddleName,NEW.`State`,NEW.Country,NEW.Community)
end if;
end

It throws a syntax error near the END IF statement. Can anyone please help?


texts:
This code returns only posts having a rating. How I can get all posts with null value where the post does not have a rating?

select
    posts.id,
    posts.user_id,
    posts.name,
    posts.created_at,
    ratings.post_id,
    ratings.avg,
    ratings.count
from
    posts
join (
    select
        post_id,
        avg(rating) as avg,
        count(rating) as count
    from
        ratings
    group by
        ratings.post_id
) ratings
on ratings.post_id = posts.id


texts: Is it possible to pass a built in function as an argument to a trigger?

texts:
I ask this question in light of the following trigger producing this error:

ERROR: syntax error at or near "("
LINE 5: ...cessBlogPostApproval"('Blog Post Approval', concat('Your blo...
^

The trigger in question:

CREATE TRIGGER "processBlogPostApproval_AFTER_INSERT"
    AFTER INSERT
    ON public."ApprovedBlogPosts"
    FOR EACH ROW
    EXECUTE PROCEDURE public."processBlogPostApproval"('Blog Post Approval', concat('Your blog post, "', SELECT "Title" FROM public."BlogPosts" WHERE "PostID" == NEW."PostID", '"has been approved.'));

The problem seems to be arising due to the fact that I passed a concatenation function as my second argument, or rather, that I did not pass it correctly. Would appreciate your assistance in identifying which of the two is the cause of the problem.


texts: My SQL Server Upgrade from 5.6.12 to 5.6.22

texts:
I have a MySQL Server 5.6.12. I have this server as a windows service.

When i upgraded this, i did the following.

I stopped the service
I copied 5.6.22 server folder, removed the data folder.
From this folder(without Data Folder), I pasted into
existing 5.6.12 Server Folder.

I can see the server upgraded to 5.6.22.

But when i query the data, it shows only few rows. My Front End(web URL) shows.
500 rows. So I am not pointing to the correct place? Please advise.

What I am missing? The Disk Space in Data Dir says: unable to retrieve.

Thanks
kp


texts:
I have read that update joins in SQLite are not supported, is there a way to convert a SQL update to SQLite?
Update Items
    Set Monitored = '1'

from Items
left join ItemsLoc on ItemsLoc.ItemId = Item.Id

where ItemsLoc.Loc is null

I am looking to update a column from Items table where the table/column ItemsLoc.Loc has a null value


texts: What is Report ID used for in SSRS reports?

texts:
While looking the XML file of my solution I saw this

Does this ID have any practical use or is only for internal use?


texts: Cannot connect to server. Too many symbolic links. Postgres Error

texts:
I get this error when I try connecting to a PostgreSQL server:

psql: could not connect to server: Too many levels of symbolic links
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

What can I do to fix this?

I have Postgres installed on my Mac.


texts: Hosting SQL Server and SSIS/SSRS in same VM

texts:
I have SSIS / SSRS and large number of databases currently being hosted on the same prod server (SQL1, SQL Server 2008R2). Since SQL1 already has SSRS/SSIS installed, would it be a good option to create a new prod server (SQL2 i.e SQL Server 2008R2, for example) to consolidate all the databases, and focusing SQL1 as the server for only SSIS and SSRS?

Please advise.

Thank you


texts: Check progress of database restore running asynchronously

texts:
I have a Powershell script that restores a database using the SqlRestoreAsync method.

I have a decent size database which I'm restoring (~55Gb) and I'm trying to find the progress of the restore process.

I know that if I run the restore through a T-SQL command then I can view the progress of the restore by querying a few DMV's or with the following query:

SELECT r.session_id
    , r.command
    , CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]
    , CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time]
    , CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
    , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
    , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
    , CONVERT(VARCHAR(1000), (
            SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
                        WHEN r.statement_end_offset = - 1
                            THEN 1000
                        ELSE (r.statement_end_offset - r.statement_start_offset) / 2
                        END)
            FROM sys.dm_exec_sql_text(sql_handle)
            )) AS [SQL]
FROM sys.dm_exec_requests r
WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE');

This query works fine for T-SQL triggered restores  (or SSMS ones), but doesn't show up the restore progress for the restore triggered by the Powershell script.

I can see my database in SSMS's Object Explorer as being in "Restoring" state, so I know it's working, but .. is it running?

The restore process running this in Async mode seems to take a bit more than when running it through T-SQL, so far twice as much and still not finished.

Is there something I'm missing? Any help would be appreciated.


texts: SQL Server AlwaysOn Failover Transparency

texts:
This is using RC0.

I have set up a SQL Server 2012 AlwaysOn (or more accurately, highly-likely-but-not-always-on) Availability Group.

I take the Primary offline.

The Secondary takes up to 25 seconds to assume the Primary role.
Any queries that were executing at the time of failover returns an error to the client.

Can anyone who has tested this new AlwaysOn feature validate my results?

How soon should I expect the failover to execute and complete?
Does an executing query get resubmitted against the new Primary, or am I correct that it causes a "connection failure" type error to the client?
Is the following form of connection string correct, i.e. no "Failover_Partner" (because this is not mirroring)?

ConnectionString=Provider=SQLOLEDB.1;Server=10.10.10.10;User ID=x;Password=y;Persist Security Info=True;Initial Catalog=z

Note: I tried the below, but all I get is "connectivity link failure" and it never recovers.  One would have thought the Native client would work better?

ConnectionString=Provider=SQLNCLI11.1;Server=tcp:10.10.10.10,8888;User ID=x;Password=y;Persist Security Info=True;Initial Catalog=z


texts:
Can comeone help me to decipher the right logic of code needed for my query? I just can't find solutions about this or maybe my search queries are just vague. Lol.

I made a query and thought before it was fine but I just realized, just now, the inaccuracy of these. Here are what I've tried:

From a search page, for example we have this variable:

$variable = !empty($_POST["variable"]) ? $_POST['variable'] : "";

QUERY 1

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id
WHERE table2.column LIKE '%$variable%'

RESULT 1

If $variable has no value, only rows with respective data from table2 are displaying.
If $variable has a value, rows with respective data from table2 and matching value are displaying.

QUERY 2

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id
     AND table2.column LIKE '%$variable%'

RESULT 2

If $variable has no value, all rows are displaying.
If $variable has a value, all rows are still displaying.

DESIRED RESULT

If $variable has no value, all rows will be displayed.
If $variable has a value, rows with respective data from table2
and matching value will be displayed.

I already tried using IF, CASE and LEFT OUTER JOIN but still no luck. Am I forgetting something with here? Or I'm doing it wrong (which is the truth)?


texts: The best way to query a partitioned table in Oracle

texts:
I have a table in an Oracle database, partitioned by a field. In our application we need to query data that is scatered across 2 partitions. Can you please tell me which method is better:

making multiple selects with UNION ALL like:
select * from table where field = 'A' UNION ALL select * from table where field = 'B'
making one query:
select * from table where field in ('A','B')


texts:
Here is the table

CREATE TABLE date_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER,
  start_at DATE,
  end_at DATE,
  is_exception BOOLEAN
);

ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH &&)
WHERE (NOT is_exception);

I have a table with working ranges and here is a flag is_exception which indicates that this range is an exception from the regular range and I want to make a constraint that all regular ranges do not overlap each other and exception ranges belong to the regular range.

I tried to use <@ operator but have found that it is not commutative.

ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH <@)
WHERE (is_exception);

Is there any option to do this? Shall I create custom operator for this case?

UPDATE

Test cases:

user_id     start_at        end_at      is_exception    is_day_off
1           2019-03-01      2019-03-05  FALSE           FALSE       // is fine
1           2019-03-01      2019-03-05  FALSE           FALSE       // FAIL. Range intersects with range above
1           2019-03-02      2019-03-03  TRUE            FALSE       // is fine since exception is inside "regular range above"
1           2019-03-04      2019-03-04  TRUE            FALSE       // is fine since exception is inside "regular range above" & does not intersect another exception
1           2019-03-03      2019-03-03  FALSE           TRUE        // is fine since day off is inside "regular range above"
1           2019-03-05      2019-03-05  FALSE           TRUE        // is fine since day off is inside "regular range above" & does not intersect another day off

1           2019-03-02      2019-03-03  TRUE            FALSE       // FAIL. Exception should NOT intersect with another one
1           2019-03-03      2019-03-03  FALSE           TRUE        // FAIL. Day off should NOT intersect with another one

1           2019-03-15      2019-03-15  TRUE            FALSE       // FAIL. Exception is NOT inside "regular range above"
1           2019-03-10      2019-03-10  FALSE           TRUE        // FAIL. Day off is NOT inside "regular range above"


texts: How to recover data from corrupted innoDB table from a crashed mySQL server?

texts:
Some background:

I had a MySQL server running on Ubuntu Server. I don't remember which versions they were running but it was likely a Ubuntu 12.04 with MySQL 5.5. During the course of a day, connections from the client application to the server kept dropping. On the next day, the server crashed and mysql wouldn't start again. I was also getting lots of disk errors so I decided to attempt to recover the data on a new machine.

What I tried:

I tried to recover the data with a Ubuntu 12.04 running MySQL 5.5 but I couldn't get the service running. Before troubleshooting it, I tried the same steps on a Ubuntu Desktop running mySQL 5.1 that I had available, and they worked. Here's what I did:

I copied /var/lib/mysql from the crashed server, to the new one
I ran chown -R mysql:mysql /var/lib/mysql
I ran service mysql start

The service started and everything seemed to work, except on one table. This is a table with BLOB data.

After some trial and error, I narrowed the problematic queries to record ids above a certain number (roughly, the last 100 records of a total of 7000 records).

Sample of some queries and outputs:

> select field1 from table1 where field1 = 6900
> (completes successfully)
> select field1 from table1 where field1 <= 6900
> (completes successfully)
> select field1 from table1 where field1 = 6901
> Error 2013 (HY000) at line 1: Lost connection to mySQL server during query

Then, I started trying to dump the data in an attempt to recover it:

> mysqldump -u root -p database1 table1 > table1.sql
> Lost connection to mySQL server during query when dumping table 'table1' at row 6642

I looked into mysqldump's options and decided to try to add -f and the output was different:

> Couldn't execute 'UNLOCK TABLES': MySQL server has gone away (2006)

To make sure the problem's in this table, I tried:

> mysqldump -u root -p --ignore-table=database1.table1 database1 > database1.sql

... and the command completed successfully.

I tried to delete one of the offending records and the delete command completed successfully. I guess I could delete them all, from the first problematic one and minimize losses but is there any hope of recovering them?

What would you suggest I do from here?

Sidenote: I was using Navicat to manage the server and make backups, and strangely enough, restoring that particular table with a recent backup also fails with the connection lost error, which leads me to believe that this table has been having problems for a while now, maybe due to disk errors and not specifically due to the crash.


texts: Getting ApplicationName (sqlserver.client_app_name) to show up in an xevent

texts:
I am trying to examine some T-SQL with extended events like I used to with SQL profiler. I have the following event session:

IF EXISTS (SELECT name FROM sys.dm_xe_sessions WHERE Name = 'PySoup tracing')
BEGIN
    DROP EVENT SESSION [PySoup tracing] ON SERVER
END

CREATE EVENT SESSION [PySoup tracing] ON SERVER
ADD EVENT sqlserver.rpc_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text )),
ADD EVENT sqlserver.sp_statement_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text )),
ADD EVENT sqlserver.sql_batch_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text )),
ADD EVENT sqlserver.sql_statement_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text ))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\PySoup tracing.xel')--,
--ADD TARGET package0.ring_buffer(SET max_events_limit=(1000000))
GO

ALTER EVENT SESSION [PySoup tracing] ON SERVER
    STATE = START;

I thought that the ACTION clause was supposed to list the columns that were returned by the event. However, I don't see sqlserver.client_app_name column when I view the event data in the GUI.

What am I doing wrong?


texts:
Table definition:

CREATE TABLE schema.mylogoperation (
  id_mylogoperation serial,
  data DATE,
  myschema VARCHAR(255),
  column_var_2 VARCHAR(255),
  user VARCHAR(255),
  action TEXT,
  column_var_1 TEXT,
  log_old VARCHAR,
  log_new VARCHAR
  constraint pk_mylogoperation primary key (id_mylogoperation)
)
WITH (oids = false);

12 million rows

I tried to explain analyze:

explain analyze
SELECT
    column_var_1,
    column_var_2
    column_var_3,
    user,
    action,
    data,
    log_old,
    log_new
FROM schema.mylogoperation
WHERE
    myschema = 'schema'
AND column_var_2 IN ('mydata1', 'mydata2', 'mydata3')
AND log_old <> log_new
AND column_var_1 LIKE 'mydata%';

indexes ( pk_mylogoperation only)
QUERY PLAN
Seq Scan on myschema  (cost=0.00..713948.14rows=660 width=222) (actual time=380.308..4467.364 rows=48 loops=1)
  Filter: (((log_old)::text <> (log_new)::text) AND (column_var_1 ~~ 'mydata%'::text) AND ((schema)::text = 'schema'::text) AND ((column_var_2)::text = ANY ('{mydata1,mydata2,mydata3}'::text[])))
  Rows Removed by Filter: 12525296
Total runtime: 4467.425 ms

Then I tried to create a some index for better performance:

CREATE INDEX idx_mylogoperation_1    ON schema.mylogoperation (myschema, column_var_2);

reindex table schema.mylogoperation;
analyze schema.mylogoperation;

pk_mylogoperation + idx_mylogoperation_1
QUERY PLAN
Index Scan using idx_mylogoperation_qry1 on mylogoperation  (cost=0.56..589836.84 rows=658 width=223) (actual time=331.679..4997.507 rows=48 loops=1)
  Index Cond: (((myschema)::text = 'schema'::text) AND ((column_var_2)::text = ANY ('{mydata1,mydata2,mydata3}'::text[])))
  Filter: (((log_old)::text <> (log_new)::text) AND (column_var_1 ~~ 'mydata%'::text))
  Rows Removed by Filter: 7441986
Total runtime: 4997.580 ms

Then I tried again to create a some index for better performance:

CREATE INDEX idx_mylogoperation_2 ON schema.mylogoperation USING gin (column_var_1 gin_trgm_ops);

reindex table schema.mylogoperation;
analyze schema.mylogoperation;

pk_mylogoperation + idx_mylogoperation_1 + idx_mylogoperation_2
QUERY PLAN
Bitmap Heap Scan on idx_mylogoperation_var_1  (cost=1398.58..2765.08 rows=663 width=222) (actual time=5303.481..5303.906 rows=48 loops=1)
  Recheck Cond: (column_var_1 ~~ 'mydata%'::text)
  Filter: (((log_old)::text <> (log_new)::text) AND ((myschema)::text = 'schema'::text) AND ((column_var_2)::text = ANY ('{mydata1,mydata2,mydata3}'::text[])))
  Rows Removed by Filter: 248
  ->  Bitmap Index Scan on idx_mylogoperation_var_1  (cost=0.00..1398.41 rows=1215 width=0) (actual time=5303.203..5303.203 rows=296 loops=1)
        Index Cond: (column_var_1 ~~ 'mydata%'::text)
Total runtime: 5303.950 ms

The question

the cost decreased but the time was practically the same, why?

Notes:

I do not want to make changes to the select operation, just in the database structure.
This test was performed on a server that is in use. But creating these indices was efficient? Or rather do not use them.
I am using Postgres 9.3.22 on Linux 64-bit Red Hat.


texts:
I Have a table like fallowing

    work_id      group    effort
   -----------   -----   --------
       1           A        10
       1           B        5
       2           A        7
       2           C        20
       2           B         3

I need a case when query that returns like fallowing

   work_id       1.group   effort1   2.Group  effort2    3.group  effort3
   -----------   -----     --------  -------  ------    -------  ------
       1           A        10        B        5
       2           A        7         C        20        B         3

goups are grouped based on another parameter that in another table. But when i write my query like

case when <condition1> then group end as 1.group
case when <condition2> then group end as 2.group
case when <condition4> then group end as 3.group

...(Same for efforts as well)

it returns

work_id       1.group   effort   2.Group  effort    3.group  effort
-----------   -----   --------   -------  ------    -------  ------
    1           A        10        null     null      null     null
    1           null     null     B        5
    2           A        7         null     null    null     null
    2           null     null                          C      20
    2           null     null      null      null     B         3

Can anyone help?


texts: SSDT Schema Compare doesn't work while a BULK INSERT is in progress

texts:
I'm working at a large ETL and DW project where we use TFS/source control together with both SSIS and SSDT.

Today, I found out that while an SSIS package is performing a BULK INSERT into a database table, it is not possible to perform an SSDT Schema Compare against that database. This is unfortunate, as some of our packages take quite a long time to complete. We want to use the Schema Compare function to detect changes to the database structure in order to save them in our SSDT project for version control of the database.

Looking a little more into this, I found that the Schema Compare function in SSDT executes an SQL script that calls the OBJECTPROPERTY() system function on the tables in the database. Specifically in my case, any calls to OBJECTPROPERTY(<object_id>, N'IsEncrypted') seems to be blocked, when <object_id> refers to a table that is currently being bulk inserted.

In Visual Studio, the SSDT Schema Compare simply times out after a while and claims that no differences have been detected.

Is there a workaround to this issue in SSDT, or should I perhaps try to file a MS Connect bug report?

Alternatively, since the BULK INSERT happens from an SSIS package, is there perhaps some way to make this insertion without locking OBJECTPROPERTY-calls on the table? Edit: In SSIS OLE DB Destinations, we can remove the check mark from "Lock Table", which does what it says, but this might hurt performance in some situations. I am much more interested in a solution that allows the SSDT Schema Compare to do its job, even if some objects are locked.


texts: Locking issue with concurrent DELETE / INSERT in PostgreSQL

texts:
This is pretty simple, but I'm baffled by what PG does (v9.0).
We start with a simple table:

CREATE TABLE test (id INT PRIMARY KEY);

and a few rows:

INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (2);

Using my favorite JDBC query tool (ExecuteQuery), I connect two session windows to the db where this table lives. Both of them are transactional (ie, auto-commit=false). Let's call them S1 and S2.

The same bit of code for each:

1:DELETE FROM test WHERE id=1;
2:INSERT INTO test VALUES (1);
3:COMMIT;

Now, run this in slow motion, executing one at a time in the windows.

S1-1 runs (1 row deleted)
S2-1 runs (but is blocked since S1 has a write lock)
S1-2 runs (1 row inserted)
S1-3 runs, releasing the write lock
S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???
S2-2 runs, reports a unique key constraint violation

Now, this works fine in SQLServer. When S2 does the delete, it reports 1 row deleted. And then S2's insert works fine.

I suspect that PostgreSQL is locking the index in the table where that row exists, whereas SQLServer locks the actual key value.

Am I right? Can this be made to work?


texts: German umlaute represented by questionmarks when latin1 is used

texts:
I'm almost certain this has come up before but I couldn't find anything regarding it.

My problem is that I've a database and a database server (and every table and column inside) set to latin1/latin1_general_ci.

Curiously when I connect with a client (for example HeidiSQL) client specific variables regarding character sets are set to utf8mb4, even more curious: äüöß are shown correctly.

When I execute SET NAMES latin1; and check again, the variables are set to latin1, as they should be, but the Umlaute are now displayed as question marks. This is also true if they where inserted AFTER SET NAMES latin1; was executed.

Another database server is completely set to utf8, except the database my program works with, which is again set to latin1. If I execute SET NAMES latin1; there it works as expected.

Could someone explain/tell me what is going on here? What am I doing wrong?

The variables I'm talking about are:

character_set_client [Global/Default: latin1]
character_set_connection [Global/Default: latin1]
character_set_database [Global/Default: latin1]
character_set_results [Global/Default: latin1]
character_set_server [Global/Default: latin1]
collation_connection [Global/Default: latin1_general_ci]

After some toying around it seems like character_set_results is the problematic one here. Setting it to latin1, while leaving the other ones at utf8mb4, results in question marks being displayed. Still no idea why this happens.

edit

Result of SHOW CREATE TABLE adressen:

CREATE TABLE `adressen` (
  `ID` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `Strasse` longtext,
  `Strasse2` longtext,
  `Hausnummer` longtext,
  `Postleitzahl` longtext,
  `Ort` longtext NOT NULL,
  `Postfach` longtext,
  `IDLand` int(9) unsigned NOT NULL,
  `Laengengrad` float NOT NULL,
  `Breitengrad` float NOT NULL,
  `Adresszusatz` longtext,
  PRIMARY KEY (`ID`),
  KEY `FK_Adressen_Land` (`IDLand`),
  CONSTRAINT `FK_Adressen_Land` FOREIGN KEY (`IDLand`) REFERENCES `laender` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=137205 DEFAULT CHARSET=latin1

SELECT STRASSE, HEX(STRASSE) FROM adressen WHERE STRASSE LIKE 'ä%':
    Ä[...];C4[...]

According to Wikipedia's article about ISO8859 that's latin1 alright.

edit2

After some further reading I found that both, HeidiSQL and .NET handle text in utf8 / unicode [at least by default]. The solution to this question is thus to convert everything to utf8mb4_unicode_ci as per this answer.


texts: If count >= row then insert row (substitute for variables and else/if)

texts:
I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense.

So I've got a table that looks something like this:

post_id   |  meta_key    | meta_value
--------------------------------------
1         | requirement  | 3
2         | requirement  | 1
3         | requirement  | 1
1         | adder_id     | 3
1         | adder_id     | 2
1         | adder_id     | 69
2         | adder_id     | 42
2         | complete_date| 20190309040445

On an insert trigger I want to run a query that will for each post_id that dose not have a "complete date":

INSERT INTO this_table ( post_id, meta_key, meta_value )
VALUES
( var_post_id, 'complete_date',NOW());

But only if

SELECT COUNT(*)
FROM this_table
WHERE post_id = var_post_id
AND meta_key = 'adder_id'

is >=

SELECT meta_value
FROM this_table
WHERE post_id = var_post_id
AND meta_key = 'requirement'

So post_id two is the intended effect (requires 1, has 1, got a complete date) while post_id one is an example of something that should trigger an update (requires 3, has 3, waiting on a complete date) and post_id three should be ignored (requires 1, has 0, just ignore).

Now this would be easy enough with an if/else and 2 or three variables but is this even possible with a mySQL statement? Is there an easier way of going about it within mySQL or should I try and find a PHP solution instead?

Thanks for any assistance, I don't usually ask for help here unless I'm properly stumped and this one really has me going.


texts: MySQL [Bad handshake] error after update

texts:
I have MASTER-SLAVE configuration on 2 servers. The MySQL replication is set over SSL (I use self-signed certificates).

Everything had been working well. But today some packages (mysql also) were updated via apt update, upgrade. Current version is: mysql  Ver 14.14 Distrib 5.7.28, for Linux (x86_64)

Both server and client have the same mysql version now, but slave can't connect to the master. In master's log I see the following error repeating many times:

2019-11-19T16:49:55.109509Z 1130 [Note] Bad handshake
2019-11-19T16:50:55.296184Z 1432 [Note] Bad handshake

What should I check in order to fix this problem? I've already tried to reissue certificates, but it didn't help.

Thanks.


texts: how to decrease column values by 20 percent using mysql

texts:
i have a procutd with this price: 60.33. When i increase the price to 20% i got like result 72.39 that is correct. but when i am tryng to decrease to 20% i dont have the same price that i had before. i am having 60.37 some one can help me to tell me what i have to do exactly?

This is the script sql i made to that:

round(listprice +(listprice *(20/100)),2)//Increase 20%
round(listprice -(listprice *(20/100)),2)//Decrease 20%


texts:
I am facing an issue with the performance. The selection of data from table is taking more than 20 sec to retrieve close to 300k records. Created a clustered index on col1 and col2 but no improvement.

Select Distinct Col1,Col2,Col3 + CHAR(13) + CHAR(10) Col3
From Table
Order by Col1,Col2

Update

Created indexes on col1 and col2 but col3 data is very big, more than 8000 characters, so SQL-server will not allow index on such column.

I am not sure how group by will help in removing the distinct.

As I mentioned it is simple select from table, yes 300k records has to be returned from table.

CREATE TABLE Tablex(
[Col1] [nvarchar](5) NULL,
[Col2] [nvarchar](20) NULL,
[Col3] [nvarchar](max) NULL) ON [PRIMARY]

Create Clustered Index ix_FCL_Tran On Tablex (Col1 Asc, col2 Asc)

Let me know if any more details is required.

Update
No change after adding option fast here is the execution plan


texts: Startup failing with ORA-14694 after running utl32k.sql in upgrade mode

texts:
Here are the steps

Creating the db on Oracle 12c (12.2.0.1.0)
Creating the admin views and tables
Starting the db in upgrade mode and running utlk32.sql
Starting up is failing with these errors codes and messages

  ORA-00603: ORACLE server session terminated by fatal error
  ORA-01092: ORACLE instance terminated. Disconnection forced
  ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

Can someone please help me to fix this
The following are the detailed steps of the commands:

orapwd file=$ORACLE_HOME/dbs/pwdorclpdb1.ora password=mypwd entries=5
sqlplus /nolog
CONNECT / AS SYSDBA
startup nomount
SET SQLBLANKLINES ON

Create the database

CREATE DATABASE mydb
 USER SYS IDENTIFIED BY mypwd
USER SYSTEM IDENTIFIED BY mypwd
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mydb/redo01.log') SIZE 100M,
        GROUP 2  ('/u01/app/oracle/oradata/mydb/redo02.log') SIZE 100M,
        GROUP 3 ('/u01/app/oracle/oradata/mydb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET AL32UTF8
DATAFILE '/u01/app/oracle/oradata/mydb/system01.dbf' SIZE 325M REUSE
         AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
        '/u01/app/oracle/oradata/mydb/system02.dbf' SIZE 325M REUSE
         AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
        '/u01/app/oracle/oradata/mydb/system03.dbf' SIZE 325M REUSE
         AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/mydb/sysaux01.dbf' SIZE 325M REUSE
 AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
DEFAULT TABLESPACE def_t DATAFILE '/u01/app/oracle/oradata/mydb/def_t.dbf'
  SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp_t
   TEMPFILE '/u01/app/oracle/oradata/mall/temp_t.dbf' SIZE 100M REUSE
   AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
UNDO TABLESPACE wcs_undo_t
   DATAFILE '/u01/app/oracle/oradata/mydb/undo_t.dbf' SIZE 200M REUSE
   AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

Create dictionary tables and views

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/sqlplus/admin/pupbld.sql

Create users

shutdown immediate
startup
alter session set "_ORACLE_SCRIPT"=true;
CREATE USER user1 IDENTIFIED BY mypwd DEFAULT TABLESPACE def_t TEMPORARY TABLESPACE temp_t QUOTA UNLIMITED ON def_t;
GRANT CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM,
CREATE TABLE, CREATE TRIGGER, CREATE VIEW, CREATE MATERIALIZED VIEW TO user1;

Apply Oracle12c extended type

PURGE DBA_RECYCLEBIN;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;
@?/rdbms/admin/utl32k.sql
show parameter MAX_STRING_SIZE
NAME               TYPE        VALUE
------------------ ----------- --------------
max_string_size    string      EXTENDED

SHUTDOWN IMMEDIATE;

The issue - All previous commands were successful.

STARTUP;
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size            2365587776 bytes
Database Buffers          838860800 bytes
Redo Buffers                8151040 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
Process ID: 20774
Session ID: 2 Serial number: 387


texts: SSRS breadcrum just breaks into multiple lines

texts:
This is a very annoying thing happening to the reports I am developing. The top breadcrumb sometimes works fine while at times breaks into multiple lines.  I have only IE 11 in my environment, Is it the browser issue? If not then please help me fix this.

Below is the screenshot.


texts: Rule of thumb: AUTOALLOCATE or UNIFORM size for tablespaces

texts:
Is there a rule of thumb for creating a tablespace with extent management AUTOALLOCATE or UNIFORM? If the latter applies, how to estimate a reasonable extent size?

Even more, what should apply for default tablespaces like SYSTEM, SYSAUX, UNDO, and TEMP?


texts:
I have three tables in MySQL - gallery, gallery_likes and gallery_info.

I want to obtain photos from gallery based on the folowing query:

SELECT g.photo_id, g.name
FROM gallery g
LEFT JOIN gallery_likes AS gl ON gl.info_id = g.info_id
LEFT JOIN gallery_info AS gi ON gi.id = g.info_id
WHERE gl.user_id = 456 AND gl.photo_id < 113535
ORDER BY gi.insert_date DESC
LIMIT 10

However, ORDER BY in this setup is very slow. Query takes 5s. If I use ORDER BY g.info_id instead, it takes 200ms.

I have index on id, info_id, photo_id and insert_date columns


texts: Multiple left join where not in another table

texts:
I have the following tables (there is more detailed data in each one, I just put the minimum to create a sample):

__________  _____________  _____________  _____________
| person |  | provider  |  | client    |  | user      |
| id     |  | id        |  | id        |  | id        |
| name   |  | person_id |  | person_id |  | person_id |
----------  | name      |  | name      |  | name      |
            -------------  -------------  -------------

I'm trying to create a list where I got all the providers together with all clients but not the users (only the ones that are providers and/or clients).

I started with this query:

SELECT person.id, person.name,
IF(client.id IS NOT NULL, 'Cliente', '') as cliente,
IF(provider.id IS NOT NULL, 'Fornecedor', '') as fornecedor
FROM person
LEFT JOIN provider ON person.id = provider.person_id
LEFT JOIN client ON person.id = client.person_id

But with this I also list users that are not providers nor clients, so I add this part to the code:

JOIN user ON person.id = user.person_id
WHERE user.id IS NULL

But now I lost the people that are User + provider/client.

BASIC DATA (Mock data)

person {
    id: 1,
    name: "test 01";
    id: 2,
    name: "test 02";
    id: 3,
    name: "test 03";
    id: 4,
    name: "test 04";
}
client {
    id: 1,
    person_id: 3
    name: "cli 01";
}
provider {
    id: 1,
    person_id: 3
    name: "prov 01";
    id: 2,
    person_id: 4
    name: "prov 02";
}
user {
    id: 1,
    person_id: 1
    name: "user 01";
    id: 2,
    person_id: 3
    name: "user 02";
}

The Data that I want is:

person_id: 3 -- it's a client
person_id: 4 -- it's a client and a provider

How could I list all providers + clients and not the users (but show the users that have provider/client)


texts: What's the meaning of the Collation Code (blank, 90, 100, 110) in SQL Server 2012

texts:
During the installation of SQL Server 2012, in the Collation tab, i can choose between French_CI_AS and French_100_CI_AS.

What do they mean and which one is newer? When there's no Code, is it newer or older than the 100?


texts: "Connection to host lost" using telnet to MySQL

texts:
I am trying to setup MySQL Workbench to manage a remote database and I keep getting "waiting for initial communication packet" error. So I try to telnet to the box on port 3306, I get in, but then I get the following message and the connection gets dropped:

"5.5.13-enterprise-commerical-advanced-log?||Dn}I3aooo;'QC=D!umysql_native_password
Connection to host lost

What can be the issue here?


texts: Where is my audit as Oracle sys audit is enable

texts:
On an Oracle 11gr1 database, SYS audit is enabled:

SQL> show parameter audit

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
audit_file_dest                      string                           /oracle/admin/jde9dv/adump
audit_sys_operations                 boolean                          TRUE
audit_syslog_level                   string
audit_trail                          string                           DB_EXTENDED

But when I query the SYS.AUD$ table there is nothing returned:

SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
         0

When I go to /oracle/admin/jde9dv/adump folder, there is a lot of files but none contain my recent SYS activity ...

Where are my audit records ?

Thanks


texts: How do I get useful SQL Server database performance statistics?

texts:
We currently have several shared SQL Server servers and instances in our organization. They are going with a different architecture where business units will get their own SQL Server instances. We have SQL Server 2008R2.

In the past, databases were put on servers in a more or less random fashion -- business use and disk space were the main drivers.

I've been tasked with coming up with a plan for allocating our 50+ databases across 3 servers (one instance per server). My criteria for divvying them up is as follows:

Business critical db's should be spread across the servers. i.e. avoid lumping a bunch of business critical db's all one server.
For the remainder of the non-mission critical db's, consider:
a. Disk space usage
b. I/O usage -- memory, CPU, etc.

1 and 2a are easy for me to figure out. I know how to get data file sizes.

My question is with that last bit - 2b. I do not know how relevant this is, what specific stats to look at, or how to get them.

Frankly, I'm not 100% sure how to phrase this question! I think I basically want to know is, "How 'busy' is this db -- however you define 'busy'?" Or maybe, "How does this db impact the performance of the server?"

I mean, disk space is important -- but it could (in theory) have a ton of rows or binary objects in there that are not written very often, so it has little impact on the performance of the server. I think I would want to know how much impact a db within a date range.

Is there a way to get these kind of general stats? I'm not looking for expensive queries, detailed user stats, or query optimization here. I'm aware of the SSMS reports, but they seem to be more real-time. The end goal is to divide up our current databases into evenly balanced buckets, and I would think performance is one way to do that.

Thoughts? Wisdom? Any other ideas on how to divvy up the db's?

Thanks! Tom


texts: "[ftdc] serverStatus was very slow" - MongoDB 4.0.4

texts:
I'm seeing very frequent messages like this in mongod.log and doesn't give an exact reason for the server status was very slow.
looks like ftdc logs more information but interpreting ftdc log is very tedious method and is not human readable format.
Due to this issue mongo daemon stops working abruptly and becomes stale which results in replication issues and loosing quorum and hence DB inaccessible.
Error log:
2021-04-21T04:56:39.729+0000 I COMMAND [ftdc] serverStatus was very slow: { after basic: 2165, after asserts: 3087, after backgroundFlushing: 8785, after connections: 11612, after dur: 12922, after extra_info: 19620, after globalLock: 27972, after locks: 41890, after logicalSessionRecordCache: 51379, after network: 55947, after opLatencies: 62013, after opcounters: 67473, after opcountersRepl: 70586, after repl: 74168, after security: 78481, after storageEngine: 78629, after tcmalloc: 78629, after transactions: 78629, after transportSecurity: 78629, after wiredTiger: 78813, at end: 79004 }

Question :
how to troubleshoot further ?
Enabling verbose 5 logs does gives more information ?
Is it truly a memory issue ?
what's the next step to avoid this issue ?


texts: Is there any semantic like table partitioning in MongoDB? My data is not that much that it would need sharding. but data is not important(but not completely useless) after several months.


texts: Oracle: How can I track the progress of an index rebuild?

texts:
How can I track the progress of a command such as this?

alter index myindex rebuild reverse online;


texts:
From cmd.exe, when I run:

C:\oracle\client\BIN\sqlplus.exe username@server

then type my password, I get in. However, if I run the same command from PowerShell and type the same password I get:

ERROR:
ORA-01017: invalid username/password; logon denied

After searching for a bit and making triply sure that I'm typing my password right I still can't figure out why SQLPlus isn't working for me from PowerShell. What am I missing?


texts: Difference between an Entity and a Relation

texts:
I am currently reading about entities and relations and I can't understand the difference between them? According to Wikipedia we have an example of a relation like in the pic:

This looks exactly like an entity set. What's the difference between an entity and a relation?


texts: SQL SERVER Database IO performance impact of splitting database tables into FileGroups and multiple Files per filegroup

texts:
SAN LUN ambiguity wrt performance

MSDN says to split read/write operations into a drive, sequential writes to another drive. (PRIMARY filegroup use typically data with read, update, write activity while LOG filegroup is sequential write mainly)
Documentation predates Storage Area Networks.

I get that grouping storage to read/write activity would optimise the heads on disk - minimise disk thrashing, sort of thing... but how is this dealt with when a "Disk" on the OS is an allocation to a storage pool derived from many disks?

The "bottleneck" of IO is then the IO controller on the SAN, and it controls the data flow from the data on striped groups of disks?
Meaning, LUN1 exposed to Server1 OS as 2 500GB drives, each split into 2 logical drives D:, E:; F:,G: of 250GB each: A database having a 750GB table created on a a filegroup of 4 files (D:\f1.mdf, E:\f2.ndf, F:\f3.ndf, G:\f4.ndf) will have parrallelism across all the logical drives (OS file driver optimised?) and SQL can use more file handler threads? but all the IO still comes through the LUN controller maxing at 1GB/sec.

Or is the DISK seperation then implied to be LUN level splits?
Meaning LUN1 250GB, LUN2 250GB, LUN3 250, LUN4 250... each exposed to the OS as a seperate physical drive, mapped to a logical drive each, and the SAME FILEGROUP split would ONLY then realise the benefit of IO optimised throughput?
I guess my question is then dependent on whether the SAN storage controller can sustain a 1GB/sec throughput on each LUN.

Or does it mean a storage controller for each LUN?

Can someone more experienced advise whether the gotcha of aligning the striping to the NTFS blocksize is participant in this discussion? OS NTFS allocation size blocks... oh! and the network packet size - is this also an influencing factor in terms of data throughput to the client? IPv6 vs IPv4 - is this a factor also?


texts: mongodb crashed with the error or permission denied

texts:
Our mongodb server was running for the last two months without any error. Suddenly it crashed and below is the log.

2017-08-11T13:41:41.320+0100 E STORAGE  [thread1] WiredTiger (13) [1502455301:320924][1281:0x7f11bce8e700], log-server: /var/lib/mongo/journal: opendir: Permission denied
2017-08-11T13:41:41.320+0100 E STORAGE  [thread1] WiredTiger (13) [1502455301:320991][1281:0x7f11bce8e700], log-server: dirlist journal prefix WiredTigerPreplog: Permission denied
2017-08-11T13:41:41.321+0100 E STORAGE  [thread1] WiredTiger (13) [1502455301:321013][1281:0x7f11bce8e700], log-server: log pre-alloc server error: Permission denied
2017-08-11T13:41:41.321+0100 E STORAGE  [thread1] WiredTiger (13) [1502455301:321021][1281:0x7f11bce8e700], log-server: log server error: Permission denied
2017-08-11T13:41:42.000+0100 I -        [ftdc] Assertion: 13538:couldn't open [/proc/1281/stat] errno:13 Permission denied
2017-08-11T13:41:42.000+0100 W FTDC     [ftdc] Uncaught exception in 'Location13538 couldn't open [/proc/1281/stat] errno:13 Permission denied' in full-time diagnostic data capture subsystem. Shutting down the full-time diagnostic data capture subsystem.
2017-08-11T13:41:50.006+0100 I NETWORK  [initandlisten] connection accepted from 127.0.0.1:45196 #205521 (23 connections now open)
2017-08-11T13:41:50.006+0100 I NETWORK  [conn205521] end connection 127.0.0.1:45196 (22 connections now open)
2017-08-11T13:42:36.004+0100 E STORAGE  [thread2] WiredTiger (13) [1502455356:4361][1281:0x7f11bb68b700], file:WiredTiger.wt, WT_SESSION.checkpoint: WiredTiger.turtle: fstat: Permission denied
2017-08-11T13:42:36.004+0100 E STORAGE  [thread2] WiredTiger (13) [1502455356:4466][1281:0x7f11bb68b700], checkpoint-server: checkpoint server error: Permission denied
2017-08-11T13:42:36.004+0100 E STORAGE  [thread2] WiredTiger (-31804) [1502455356:4480][1281:0x7f11bb68b700], checkpoint-server: the process must exit and restart: WT_PANIC: WiredTiger library panic
2017-08-11T13:42:36.004+0100 I -        [thread2] Fatal Assertion 28558
2017-08-11T13:42:36.004+0100 I -        [thread2]

***aborting after fassert() failure

Please tell me why it happened and what should I do to avoid this crash in future. I am using mongodb version 3.2. How come the file permission changed while the server is running.


texts: Difference in DELETE performance between physical (snapshot based) and logical (basebackup) clones

texts:
We have a relatively large PostgreSQL database (500G - 1TB) and want to perform a big migration task.
The migration consists in a bunch of ALTER, UPDATE, DELETE, SELECT.

We have performed this migration on logical (via base backup) clone of the production database for quite some time by using wal-e base backups and the whole process took ~24hrs.

We then did the same using a physical copy of the volume to be as live-like as possible and - surprise! - it took 50hrs. Not a one-off and can be repeated.

It feels like there's something 'physical' that's not then logically replicated.

In particular we have noticed that some (2M statements) DELETE statements that take ~300-500ms each on a logical clone, take ~1s on a physical clone.

What could explain this behaviour and what do you think we can do to prevent it?


texts: Best solution to switch staging Table to main Table, avoid access when main table is empty

texts:
I have a large table (10 million rows) on SQLServer 2014 which is rebuilt regularly by a procedure which writes the data in a staging table, then switches the data into the production table.
Currently I do TRUNCATE the Main table then INSERT into it from the Staging table.

-- Empty Main table
TRUNCATE TABLE [dbo].[MainTable];

-- Switch data from Staging table into Main table
INSERT INTO [dbo].[MainTable] SELECT * FROM [dbo].[MainTableStaging];

This truncate-insert process takes approx. 2 seconds.
Very important I must make sure nobody can read the main table while it is empty.

First question:
Can I 'LOCK' the Main table between the TRUNCATE TABLE and the INSERT to avoid any read returning empty records ?  Using TABLOCKX ? Any other way ?

Second question:
What would be the pro/cons of using ALTER TABLE instead of INSERT ?

-- Empty Main table
TRUNCATE TABLE [dbo].[MainTable];

-- Empty Main table
ALTER TABLE [dbo].[MainTableStaging] SWITCH TO [dbo].[MainTable];


texts: MySQL database design for Twitter-like timeline

texts:
I am working on an application which requires to show different posts on user's timeline(something like Twitter).

A User's timeline consists of following:

Reports which user created
Reports which other user created and fall under his/her 'alert location' radius
Reports posted by other users which he/she follows (Even if that report does not fall inside 'alert location' radius)
Report Shared/Re-tweeted by user he follows (Even if that report does not fall inside 'alert location' radius)
Reports which other user created and fall under his/her 'current location' radius when a user is travelling.
Follow notification- When someone started following him/her
Message notification- When someone sent a message to him/her

Note:- Once a report appear in user's timeline then it will not disappear even if user move out of location radius or changes 'alert location'. It will only disappear when it is deleted or flagged as inappropriate.

Requirement: I need an optimal DB schema(with SELECT query) so that I can show above mentioned posts on user's timeline.

Here is my current DB structure:

Table: users
   UserID (PK)
   email
   password

Table: alert_locations
   alertLocationID (PK)
   user_id (FK)
   latitude
   longitude
   radius

Table: followers
   follower_id (FK)
   following_id (FK)(follower_id + following_id) = PK

Table: reports
   reportID (PK)
   reported_by_id (FK)
   location
   latitude
   longitude
   parent_id (FK- For retweet relations)

Now, taking into the account the above cases of timeline I am thinking of timeline table with structure shown below:

Table Name: timeline
          timelineID  (PK)
          reference_id  (Can be report_id or follower_id or message_sender_id)
          user_id   (FK)
          title
          type       (1:For Report, 2:Follow, 3:Message)

So, when a report is created, for each user a new row in timeline table is inserted who is eligible to view that report. Using this table I can query all types of posts for user's timeline.

But as I see, this approach seems to have scaling issues and it does not look wise to insert 'n' amount of rows in timeline table every time a new report is created.

Is there any other better solution to achieve this?


texts: Fault Finding SSIS "Unexpected Termination" - SQL Server 2016

texts:
I have been getting an "Unexpected Termination" error from time to time on a bunch of SSIS jobs that I am running on a SQL Server 2016 instance (Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) - 13.0.2164.0 (X64)).  The server is a virtual server, running Windows Server 2012 R2, 4 processors and 52gb of ram.

I have a Master package/sub package project that is running around 200 packages in parallel. I have the Project set to run the default number of parallel processes, being 6.

The failures, when they do occur, appear to be pretty consistent.  Basically,   what is happening is the first 6 sub packages kick off.  It appears to me that when it goes to pick up the next package the termination occurs.

I have checked the logs and I can't see anything written that is abnormal - there are no on warning or on error messages that are any different to if the packages run to completion.  I can't see any other abnormal events in the servers event logs.

Does anyone have any clues on what may be happening, or any tips on how to fault find this issue?

Thanks


texts: How can I auto_increment main and sub category conditionnaly?

texts:
So I have a table of items as so

|mainCtgry |subCtgry|
---------------------
|     1    |   1    |
---------------------
|     1    |   2    |
---------------------
|     1    |   3    |
---------------------
|     2    |   1    |
---------------------
|     2    |   2    |
---------------------
|     3    |   1    |
---------------------
|     4    |   1    |
---------------------

So mainCtgry and subCtgry are the primary keys, you can't see it in the scheme but both are related to a string column let's say mainStr and subStr

My question is how can I auto-increment subCtgry based on the last subCtgry value related to mainCtgry (so subStr). Let be more clear if I enter for exemple cat into mainStr and generic into subStr then mainCtgry will be 1 and subCtgry will be 1, now if I enter cat into mainStr and Birman into subStr I would like my mainCtgry to still be 1 and subCtgry to be 2

Is this possible to automate this kind of behaviours in MYSQL?


texts: Test to see when table is candidate for compression (row or page)

texts:
Does anyone know a tool like this page https://columnscore.com/ where one can determine if a table is a good candidate for row or page compression.
Also I am trying to understand the benefits of compression. I have been reading about it, but still have these questions.

Will compression make more 8KB fit in the buffer cache? When does data become uncompressed i.e. in a select?
will backups become faster?
Is the reason that compression ads cpu overhead because e.g. inserts need to be compressed before the insert?
Is there any good reason to compress tables without investigating how the tables is being used (i.e. static vs. volatile).

A colleague has started to compress many tables (page). I don't know the analysis behind this decision. I know we sometimes have resource semaphore waits during some peak hours. I actually don't think he knows what he is doing. I can see that our sos scheduler yield waits have gone slightly up.
I think the compression is causing more harm and it seems that maintenance tasks overshoot more now (e.g. backups, checkdb...).


texts:
I'm COPY-ing 300 tables / 400G to PG10@win2016server using a few threads to speed things up, but because the table files are incremented in 64-kb units (or something there-about) they get fragmented on disk which hurt read performance.

Ideally I would like to make sure that files are expanded in larger increments (eg. 8 mb), alternative would be to make initial size = max segment size (1gb). Is there any way to accomplish this?

And I know, single threaded copy would solve the issue at hand, but I would still have the same problem after the copying, as tables grow continually and still only in small fragmented chunks.

EDIT:
As a "proof" of sorts, i created a simple table

CREATE TABLE testtable
(
  id bigint primary key,
  somestring varchar(4000)
);

BEGIN
  FOR i IN 1..2000 LOOP
   insert into testtable values(i, repeat('Jeg kan en sang der kan drive dig til vanvid.', 40));
 END LOOP;
END

which produces a table of 4096kb, which contig.exe reports to be of 25 fragments (in this instance), or 160kb on average. For 4 mb this is not an issue, for 400gb it is.


texts: Dumping stored procedure result to a text file programmatically

texts: How can I save stored procedure execution result to a text file? I need to do this from SQL script (not Management Studio nor external utility) and I need to preserve correct characters encoding (Unicode in this case).


texts: Need a way to prevent log shipping from kicking users out

texts:
We have SQL Server 2012.  A service we use requires us to use log shipping, however we are tired of having users being kicked offline every 15 minutes.

Are there any creative ways to get around this (I believe it cannot be stopped) but perhaps setting up replication & log shipping is that possible?

Somehow having two instances of the same database so when one goes down for log shipping the secondary is automatically used.

Just looking for any creative way to get around users being kicked off during log shipping.


texts: Editing GeoJSON parsing code to parse JSON code instead in SQL

texts:
Apologies if this is an easy question to some, I have spent hours trying to figure this out and it has me stumped.
So, I know GeoJSON takes the following format:
{
   "type": "FeatureCollection",
   "features": [
  {
    "type": "Feature",
    "geometry": {
       "type": "Point",
       "coordinates":  [ -85.6442940021,37.6116333423 ]
    },
    "properties": {
    "National Park":"Abraham Lincoln Birthplace NHS",
    "$ Obligated":"$34,584",
    "State":"KY"
    }
  },

For this, I have a code in SQL as follows which will parse the GeoJSON which is contained in 1 row of a table out into the appropriate columns in which I need:
CREATE TABLE new AS SELECT type, TEXT((prop->>'National Park')::TEXT)National_Park,
TEXT((prop->>'Obligated')::TEXT)Obligated,
TEXT((prop->>'State')::TEXT)State,
geom FROM (SELECT elem->'type' AS type, elem->'properties'
AS prop, elem->'geometry' AS geom
FROM (SELECT json_array_elements(data)
elem FROM table)
f1)f2);

My result is then
| type | National Park | Obligated | State | geom |
| -------- | -------------- | ------| ------| ------
| FeatureCollection   | Abraham Lincoln Birthplace NHS | $34,584 | KY | "Point", "coordinates":  [ -85.6442940021,37.6116333423 ] |

So my struggle is, I now have JSON files which are in the structure:
[
  {
    "id":1,
    "name":"Johnson, Smith, and Jones Co.",
    "amount":345.33,
    "Remark":"Pays on time"
  },
  {
    "id":2,
    "name":"Sam \"Mad Dog\" Smith",
    "amount":993.44,
    "Remark":""
  },

I'd like to edit the same code above to parse out the JSON in the same way as the GeoJSON, except I'm not sure how to as a JSON isn't broken up into named arrays such as properties, etc which I use in my original code


texts: Set oracle workspace from java application

texts:
I'm connecting to an Oracle database 12c which uses Oracle Workspace Manager from a java application.  I'm trying to execute the following command from my java application

EXECUTE DBMS_WM.GotoWorkspace('WORKSPACE');

using this code

String setWorkspace = "EXECUTE DBMS_WM.GotoWorkspace(?);";
CallableStatement cs = conn.prepareCall(setWorkspace);
cs.registerOutParameter(1, OracleTypes.CHAR);
cs.setString(1, "'WORKSPACE'");
cs.execute();

and I'm receiving this exception

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:210)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:53)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3923)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5617)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)

I can execute this command from SQL Developer, and I can execute a regular query like this

ResultSet rs = stmt.executeQuery(query);

from my java application.  I get the same exception trying to execute the above command with executeQuery()

I've done some reading and found out that DBMS_WM is a public synonym.  The code I'm using to run the execute statement is used for stored procedures.  However I haven't been able to find any examples of executing a public synonym from a java application.  How do I call a function from a public synonym from java?

Update

I changed my java code to

    String setWorkspace = "CALL DBMS_WM.GotoWorkspace('WORKSPACE');";
    CallableStatement cs = conn.prepareCall(setWorkspace);
    cs.execute();

and now I get

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended


texts: Looping through values stripping 1 character at a time

texts:
I want to loop through values and strip 1 character at a time from the values and display the result.

So if I have a table with values:

ID
___
34679
13390
89906

I want the result to look like this

Id
----
4679
679
79
9
3390
390
90
0
9906
906
06
6