texts: FLUSH TABLES stays in the processlist even after execution

texts:
Here is my code from Stored Procedures in Mysql

FLUSH TABLES;
TRUNCATE TABLE tbl_cdrload;
TRUNCATE TABLE tbl_cdrdetails_temp;

Then i execute a query to load a file in to database as below

LOAD DATA LOCAL INFILE filename INTO TABLE tbl_cdrload
FIELDS TERMINATED BY LINES TERMINATED BY '\n';

I executed this set of code one after the other several times using a Windows service. After some time, the Windows service stops automatically. When I open MySQL Administrator, I could see the FLUSH TABLES still running in the processlist. Can anyone explain why so and if this is the reason for the service to stop?


texts:
I'm stuck and not sure if somebody had a similar issue.
I have an old database stored as .bin files. It dates back to 2012. I can see an xml catalogue file which indicates to me that it is some kind of SQL database.
Please see below:
<Table>
<Fields>
    <Field Name="BookID" SQLType="3" DataType="0"/>
    <Field Name="BookName" SQLType="200" DataType="3"/>
    <Field Name="ID" SQLType="3" DataType="0"/>
    <Field Name="ParagraphID" SQLType="3" DataType="0"/>
    <Field Name="TextData" SQLType="203" DataType="3"/>
</Fields>
<ConnectionStrings>
    <ConnectionString>Provider=SQLNCLI10.1;Server=(local);Database=Library;Trusted_Connection=yes;</ConnectionString>
</ConnectionStrings>
<Queries>
    <Query>SELECT * FROM Docs_Pages</Query>
</Queries>
<DataKey>DataKey</DataKey>

Does anybody have an idea how to browse the TextData column?
Any recommendation for a programme that works on mac would be great. If it has to be windows is fine too, as I have a VM with Windows.
Looking forward to hearing from you.


texts:
I will be starting a new job in about 5 weeks, and want to spend some private time really getting to grips with as many of DB2s features as possible, as it is the DB used in the new company.

Am I required to do a local home install of the DB so that I can play around with it and try some things out, or is there somewhere online that I can connect to a public sandbox DB to practice/test/play around with it?

If an install is required, I'll follow this link: https://stackoverflow.com/questions/3289433/where-to-start-learning-db2-programming

Thanks,
Stephen


texts: How do I show warnings when loading data created from mysqldump?

texts:
I've got a large .sql file with large insert into ... values ... statements.  Many of these statements are generating warnings during execution.  How can I get mysql to print the warnings?

If it hit control-C then the import stops and I'm dropped back to the OS command line.

Here is sample output from running the SQL:

Query OK, 9827 rows affected, 5403 warnings (0.20 sec)
Records: 9827  Duplicates: 0  Warnings: 5403

Query OK, 9859 rows affected, 5247 warnings (0.20 sec)
Records: 9859  Duplicates: 0  Warnings: 5247


texts: List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

texts:
Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index.

I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY KEY index.


texts: Create primary keys based upon higher-level entity in access?

texts:
Is it possible to have access create primary keys for items by appending a string to the primary key one level above it?

I am trying to create an inventory management database for my company's server ops team, to keep track of things such as switches, servers, UPS's, etc.  I have things organized to separate our different locations, the different rooms in said locations, the different racks in said rooms, and finally a 1-1 relationship between an occupied rack U and a device.  The primary key for locations would be a simple, 3-letter designation that we use as a site code.  The primary key for a room would be that site code plus "-###" where "###" is the displayed room number.  This is to simplify the attributes for the room/lower levels (i originally had an auto-gen number for pKey, with a separate attribute for display number).  this would continue down, until the rackU and/or device entities.  For them, i'm looking to allow the admins to use a form, select the correct Location, Room, and Rack#, then enter a rackU and serial number/asset tag for the device.  Then the rackU primary key would need to generate itself to be the primary key value of its associated rack, plus the U number.

To simplify, I would be manually inputting each entry down to rackU, and primary keys would look like:

[Loc] = ABC
[Room] = [Loc]-###
[Rack] = [Room]-A/1 (some racks are lettered, some are numbered)

Then, when entering a new device, user would select Location, Room, Rack, and input a rackU number.  I need some way to auto-generate the rackU primary key to be the linked Rack primary key plus the given U number.

It's one of those things where I'm fairly certain I could make it work if I was writing this database from scratch in python or PHP, or if i was using an actual mySQL database, but I'm completely lost as to how to handle this in Access.


texts: WiredTiger panic when attempting to rename .turtle leads to loss of all data

texts:
We've seen this twice this week on separate servers (one staging, one production).

2017-10-19T12:50:37.525-0400 I ACCESS   [conn266] Successfully authenticated as principal ********* on admin
2017-10-19T13:00:42.782-0400 E STORAGE  [thread2] WiredTiger (-28817) [1508432442:782769][1520:8790690042448], file:WiredTiger.wt, WT_SESSION.checkpoint: c:\mongo\data\WiredTiger.turtle.set to c:\mongo\data\WiredTiger.turtle: file-rename: rename: Cannot create a file when that file already exists.
2017-10-19T13:00:42.784-0400 E STORAGE  [thread2] WiredTiger (-28817) [1508432442:784770][1520:8790690042448], checkpoint-server: checkpoint server error: Cannot create a file when that file already exists.
2017-10-19T13:00:42.785-0400 E STORAGE  [thread2] WiredTiger (-31804) [1508432442:785770][1520:8790690042448], checkpoint-server: the process must exit and restart: WT_PANIC: WiredTiger library panic
2017-10-19T13:00:42.785-0400 I -        [thread2] Fatal Assertion 28558
2017-10-19T13:00:42.785-0400 I -        [thread2]

***aborting after fassert() failure

2017-10-19T13:00:42.805-0400 I -        [conn259] Fatal Assertion 28559
2017-10-19T13:00:42.806-0400 I -        [conn259]

***aborting after fassert() failure

The result is that the admin database is wiped out.  To recover we need to disable authentication and recreate all users.  The original data files seem to contain data, but it's no longer visible from the shell commands.

Both servers have been running fine, independently for months.  They are running the same version of Mongo but very different versions of the software which interacts with it.

I suppose I have two questions:

1) What might cause this particular failure?
c:\mongo\data\WiredTiger.turtle apparently shouldn't exist at this point, but it does; what can cause this?  They have extensive security protocols including Bit9 and anti-virus solutions.  I suggested a scheduled defrag job (they're running SSDs so defrag should NEVER run, but hey...) but they can't find any evidence that a defrag happened on either server.

2) What might cause them to experience the same crash a day or two apart?
The only thing in common on the two systems is Mongo.  As an aside, we're going to suggest journaling and replication (their production environment is still in its test rollout, so the downtime and loss of data was acceptable this time).


texts: Why is this Full Outer Join not working?

texts:
I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join.

I have 2 tables (which I'l call t1 and t2) with 2 fields each:

t1

Policy_Number Premium
101             15
102              7
103             10
108             25
111              3

t2

Policy_Number   Loss
101              5
103              9
107              20

What I am trying to do is to get the sum of Premium and Sum of Losses from both tables and also the Policy_Number. The code I am using is:

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, t1.policynumber
from t1 full outer join t2 on t1.policynumber = t2.policynumber
group by t1.policynumber

The above code will return the correct sum totals but it will group all records where there isn't a policy_number match under "NULL" policy_number.

I would like my result to look like this

Policy_Number    Prem_Sum    Loss_Sum
    107            NULL        20
    111              3        NULL
    101             15          5

etc.....

I do not want a result that shows a NULL policy_number as shown below (since there is no such thing as a NULL policy_number. This is just the total for when the policy_number from both tables don't match):

Policy_Number    Prem_Sum   Loss_Sum
   NULL            35         NULL

If I Select and group by t2.policy_number instead of t1.policy_number then I get something like below as a record.

Policy_Number    Prem_Sum   Loss_Sum
   NULL            NULL         20

Again, I don't mind seeing NULL under Prem_Sum or under Loss_sum but I don't want a NULL under Policy_Number. I would like my results to be something like

Policy_Number    Prem_Sum    Loss_Sum
    107            NULL        20
    111              3        NULL
    101             15          5

ect.....

I thought the full outer join would accomplish this but I guess I am missing something. I was thinking maybe I could select and group by both t1.policy_number and t2.policy_number as a sub query and then maybe do a CASE in the outer query or something??? I don't think it should be this complicated.

Any ideas or advice?


texts:
I have 2 fields ( about 1000 records) name FROMTIME and TOTIME. Both fields will have time in seconds (for example FROMTIME = 20520 and TOTIME = 39360).

I would like to convert and update these fields to time format (for example FROMTIME = 20520 becomes FROMTIME = 05:42:00.000 and TOTIME = 39360 becomes TOTIME = 10:56:00.000)


texts: How to display the exact query in the output section of a stored procedure when using 'Dynamic SQL' and 'Bind variables' in Oracle

texts:
I have a Procedure as you can see below. In this procedure I have a dynamic querywhich is qry variable and I'm using Bind variable in order to eliminate the risk of injection and improve performance:
create or replace procedure first_test(i_tx varchar2, o_count out number) is

qry varchar2(1000);
begin
    qry := 'select count(*) from z_test_a where c_num = :1';

    execute immediate qry
    into o_count
    using i_tx;

   dbms_output.put_line(qry);
end;

what I want to know is that , how I can see the exact qry after using i_tx. What I mean is that if the input parameter is i_tx = 2 how I can see this select count(*) from z_test_a where c_num = 2 in the output section of the procedure so that I can debug the code in case of error? Is this even possible to see the exact query? In this procedure , what I'm getting right now in the output page is :select count(*) from z_test_a where c_num = :1.
Thanks in advance


texts:
We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the outcomes of the incident was that we were to consider disabling AUTO_UPDATE_STATISTICS, allowing for the the overnight maintenance jobs to handle statistics updates, and keep stats static during the day. The arguement for disabling statistics updates is that it will ensure that we retain the same consistent plans. By default my preference is not to do that, and more proactively monitor memory grants, and memory used, using query store and/or extended events, and to alert when significant issues occur.
I had looked around and couldn't really find any instances where people had blogged/posted about disabling this option and the sucesses of it. My question is are the any noted instances that people have of success of disabling statistics/ please can people relay successful implementations of doing so?
SQL Version: 2016 Standard EE


texts: Full text search using words stored in another table

texts:
Is there a way to do a full-text search using words stored in another table?  This is my scenario:

Table_A contains phrases entered by users in field "UserInput" (and other stuff).  I have a full-text index on this table looking at UserInput.
Table_B with one field "Word" containing a bunch (more than a 1k) words that I want to make sure my users are not using.

I tried this query:

Select *
From Table_A
  Cross Join Table_B
Where Contains(UserInput, Word)

But that does not work. The error says "Incorrect syntax near 'Word'". SSMS also complains saying "Expecting STRING, TEXT_LEX or VARIABLE".

I have also tried CHARINDEX and LIKE in the WHERE statement but those functions return false positives as they do not look for whole words but strings within strings.

If full-text will not work, is there any other approach that can help identify whole words from Table_B.Word in Table_A.UserInput?

UPDATE:

Also tried the following:

Declare @AllWords nvarchar(4000) = '';
Select @AllWords = @AllWords + Case When @AllWords = '' Then Word Else ' OR ' + Word End
From Table_B;
Set @AllWords = '''' + @AllWords + '''';
Select *
From Table_A
Where Contains(UserInput, @AllWords);

This one seems to bring up the right results but I don't have a way to know what words were found in Table_A.UserInput.


texts: Availability Group Listener Loses Connectivity on Failover

texts:
I have 2 SQL server 2016 standard Servers.

Production (SRV1).
For reporting (SRV2)

Both servers are nodes in WSFC.

SRV1 replicates the production database (DB1) to SRV2 with the distributor being on SRV2 (this is for reporting purposes since secondary replicas aren't readable in SQL 2016 Standard).

I have following availability groups in-place:

AG_DIST

SRV2 is primary replica, SRV1 is secondary replica
Hosts DB2  (the distribution database)
Listener name: Dist_lsnr.
This AG ensures that the distribution database is always available.

AG_PROD

SRV1 is primary replica, SRV2 is secondary replica
Hosts DB1 (Production database)
Listener name: PROD_lsnr

All is well when I fail-over WSFC or fail AG_DIST to SRV1. The problem arises when failing over AG_PROD to SRV2.

When this is done the application connections using PROD_lsnr lose connectivity with the PROD database.

When I fail the AG back the applications can again connect to the database.

What am I overlooking here?


texts: I cannot add a new Server Role "SQL Server 2008 R2"

texts:
I want to add a new user-defined Server Role to my SQL Server instance, but the menu doesn't show all options, it only shows this:

Instead of this :

and I also tried the T-SQL command to create the server role but it keep showing the message

  Incorrect Syntax near 'AUTHORIZATION', expecting id ,quoted_id or to.

Please Help ASAP.


texts: How to force SQL Server to write partial query set to table during select

texts:
I'm trying to run a select query to extract a data set from a linked server, to be stored on SQL server.

It seems that SQL server is storing the rows of the partial data set in memory and writing it to the table only when the full data set has been retrieved. Problem is that the data set is large, and the connection times out frequently.

Is there any way to get SQL server to write parts of the data set downloaded thus far to the destination table instead of storing it in memory?


texts: Create table partitions on different filegroups, the first boundary on a Memory Optimised filegroup

texts:
99% of all our transactions are against the latest week's data, a WEEK_SELECTOR column describing which week is being described.
Partitioning our billion+ row table by week would be beneficial, more so if the half million or so rows for the latest week were in an in-memory partition.

On attempting to split the top week off to our memory optimised filegroup with

CREATE PARTITION FUNCTION [TPF](int) AS RANGE RIGHT FOR VALUES (N'1700114')
CREATE PARTITION SCHEME [TPS] AS PARTITION [TPF] TO ([PRIMARY], [memtest])

we get the following error:

Msg 7737, Level 16, State 1, Line 2
Filegroup memtest is of a different filegroup type than the first filegroup in partition scheme TPS

We are now assuming that filegroups must be of the same type for partitions and our plan is not possible, but cannot find documentation to say that it is impossible.

Can someone explain how it can be done, or if not, provide a link saying as much? Thanks.


texts: How to stop the execution of a long running insert query?

texts:
I've executed an INSERT INTO ... SELECT as a job on my MySQL instance and then I realized that number of records that is selected by the WHERE condition is more than my patience. It's already taken 12 hours and there's no sign of the job being done.

My previous experience tells me that there's a big chance to corrupt the data if I just kill / stop the MySQL instance. And I know no other way to do so! Does anyone know a safe way to gracefully ask MySQL to stop whatever it is doing?

And I'm using InnoDb and MySQL 5.6 on Ubuntu.


texts:
I am using SQL server 2008 R2. I have a live database, and a replicated copy of that live database on a different server.

It is using Transactional Publication replication.

When I take our software and point it at the replicated copy it doesn't work. At first it seemed like the database didn't have the correct logins under security.

BUT - that doesn't seem to be the problem. It is more likely that the database is locked down somehow because of the replication? Can someone please point me to some info to confirm that.

In management studio on the server when I am logged in as a local administrator, when I click one of the replicated databases it just gives me the error,

'The database blah is not accessible'

How can I check that the replication is working? Will I be able to back up the database and restore it somewhere else, any other suggestions welcome.


texts: Record not getting filtered using like clause

texts:
Using below query to filter records:

select * from SearchRecord where searchkeyword1  like  '[%'

Tried this also

select * from SearchRecord where searchkeyword1  like  '%[%'

Records in table contain these values (as per below i need last 2 records, why this simple like won't filter records)

"Affiliate" means
"Agent's Rate of Exchange" means
["Aggregate Total Commitments" means
["Agreed Security Principles" means


texts:
After deleting a SQL Server Agent job I continue to get notifications that the job attempted to run and failed. I am receiving the following error message.

  [000] Unable to retrieve steps for job my_job
  [298] SQLServer Error: 14262, The specified @job_id
  ('CC65FEFB-0033-442E-B55E-6209E25C5039') does not exist. [SQLSTATE
  42000]

I have checked the following tables in the msdb database and none of them have the specified job_id except the dbo.sysjobhistory table.

dbo.sysjobs
dbo.sysjobschedules
dbo.sysjobsteps
dbo.sysjobstepslogs
dbo.sysjobservers
dbo.sysjobactivity
dbo.sysjobhistory

Is there anywhere else I can look?
Also, today I have stopped and restarted the SQL Server Agent and will update this post tomorrow with the result.

Thank you in advance!


texts: Call two function within main function PostgreSQL

texts:
i have two functions one with the english language and the other with the french language. I would like to create a third function which will be the main function that will return the first function if the language = 'ang' and the second if the language = 'fr'.
how can i do that ?
see first function : https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a7acc2cc946f2269a56ffea742ae3fca
see second function : https://dbfiddle.uk/?rdbms=postgres_12&fiddle=80458461971c0722fc673de5ec763e16


texts: how to restore differential back daily basis on sql server DB

texts:
Im working with SQL Server DB, what i need is that i have a DB1 and clients site communicate with this DB,Reporting module of the application is also using the same DB (i-e That shows different reports in web pages) client want to point the reporting instance to another DB i-e DB2.

What we need to do is: create a new DB2 i-e copy of DB1 and on daily basis we will take the differential backup of DB1 and will restore it to DB2.

For this i tried to take the differential backup of DB1 using this query,

BACKUP DATABASE [DB1] TO  DISK = N'D:\DB_location\DB1-Diff.bak'
WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'DB1-Differential Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

and to restore it to DB2 i use

RESTORE DATABASE [DB2] FROM  DISK = N'D:\DB_location\DB1-Diff.bak'
WITH  FILE = 1,  norecovery,  NOUNLOAD,  STATS = 10
GO

it runs perfect for the 1st time but next time if gives me error:

Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Please also advise if i can use Transaction Log backup if that is more batter then differential back up.
Can any one help me to solve this issue.

Thanks in advance.


texts: Maximum concurrent connections to SQL Server Express 2017 installed on Windows Server 2016 Essentials

texts:
If I install SQL Server Express 2017 on Windows Server 2016 Essentials, what is the maximum number of users that will be able to connect concurrently across the network to the SQL Server Express instance?

Is is unlimited or does the operating system's limits of 25 users / 50 devices then kick-in?

Also, how does the o/s count concurrent users? e.g. if we have 2 users on different computers connecting to the SQL Server service using the same SQL Server credentials, will the o/s count them as 1 user on 2 devices (hence we can, in theory, get 50 users connecting to the database), or will they be counted as 2 users on 2 devices?

Note that I am not a DB administrator nor a network specialist, just a developer trying to install my first server and trying to understand whether the o/s connection limitations per user only apply to built-in operating system services (like NTFS file sharing) or whether they also affect connections to 3rd party application services like SQL Server Express.

e.g. if I install SQL Server Express on a Windows 2016 server that has 5 user CAL only, will the limit on concurrent connections be the 32K (approximately) set by SQL Server Express or will it be the 5 connections limit set by Windows Server?

Thanks.


texts: Dynamic result in select statement mysql

texts:
I want to get max value as maxvalue column of a column, table returned by the following query.

IE: if the following query returns table name test_tab and column name as
test_col then the following query should return max value of the table test_tab and column  test_col .

which would give me the maximum value of auto incremented columns in my database with table name and column name.

SELECT c.table_name,c.column_name ,'' maxvalue
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE  EXTRA = 'auto_increment'


texts: tuned lacks oracle profile. How could I switch to it?

texts:
I'm trying to configure tuned using this tutorial

And following it I'm trying to switch to oracle profile:

# tuned-adm profile oracle

  Invalid profile. Use 'tuned-adm list' to get all available profiles.

# tuned-adm list

Available profiles:
- desktop-powersave
- spindown-disk
- default
- virtual-guest
- virtual-host
- throughput-performance
- enterprise-storage
- latency-performance
- laptop-battery-powersave
- laptop-ac-powersave
- server-powersave
Current active profile: default

So how to switch to profile that does not exist in the list?


texts: I am using Postgres as my Database. I need to know whether I can extract all the queries that got executed with out logging them as logging all the queries will degrade the performance of database. So, provide a way I can get all the queries that got executed with out logging the queries.


texts: Reporting & Logging Failing on SQL Server Job

texts:
System Info: SQL Server 2014 Enterprise, Windows Server 2012 R2, running on a VM
I have a Maintenance Plan set up to perform a log backup of a database every 17 minutes. The job normally runs fine without any issue, but for the last week at the same time, the job will report a failure on the last part of the Subplan:
Code: 0xC0024104     Source: Reporting Task for subplan-{9D28836F-FCA4-4795-984B-03ADE9020C03}      Description: The Execute method on the task returned error code 0x80131904 (Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.). The Execute method must succeed, and indicate the result using an "out" parameter.

The Reporting & Logging is configured on this Maintenance Plan, and is to output a text file report of the backup to the same drive that the Data files are on. This is the same configuration for several other Maintenance Plans for other databases, and those have not yet reported an issue similar to the above.
What I see is there are no text files for the Subplan being created at the time this errors out, so it's very likely the error is being generated because it cannot create a text report of the Subplan even if it ran successfully.
I am not sure how to address the issue. I have reviewed system logs, VM logs, talked with folks responsible for storage and have not found anything that would indicate a problem with talking to the local disk. I also don't see any points of contention (so far) that may conflict with the Maintenance Plan.


texts:
How to compare two JSON fields in MariaDB?

MariaDB 10.2 and 10.3 - FAILS

SELECT CAST('{"q":2}' AS JSON);

Returns : SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON)' at line 1

MySQL 5.7.19 - works OK

SELECT CAST('{"num": 1.1, "date": "2015-11-01"}' AS JSON) = CAST('{"date": "2015-11-01", "num": 1.1}' AS JSON);

Returns: 1

  Source: http://rpbouman.blogspot.com/2015/11/mysql-few-observations-on-json-type.html

Other similar questions

I found this but my JSON is more complex.


texts:
I need some help interpreting this execution plan -
https://www.brentozar.com/pastetheplan/?id=SJ5NYLLSL

Whenever this query is ran it uses up a huge amount of CPU and kills the server.

I have little experience in interpreting execution and am not sure what to look at/suggest to improve.  I notice the amount of left joins and the size of some of the tables and would suggest adding an index somewhere but am unsure on what table and what fields.
Thanks.


texts: When are we supposed to set default-character-set for the client?

texts:
I'm trying to understand when I'm supposed to set/force default-character-set for the client.

The documentation states:

  You can force client programs to use specific character set as
  follows:

  [client] default-character-set=charset_name

  This is normally unnecessary. However, when character_set_system
  differs from character_set_server or character_set_client, and you
  input characters manually (as database object identifiers, column
  values, or both), these may be displayed incorrectly in output from
  the client or the output itself may be formatted incorrectly. In such
  cases, starting the mysql client with
  --default-character-set=system_character_set—that is, setting the client character set to match the system character set—should fix the
  problem.

Let's assume charater_set_system and character_set_server are different.

My first question:

I suppose that when I'm importing an export files generated by mysqldump the variable default-character-set has no effect as set names is always issued at the very beginning of the export file.
Am I right or there could be corner cases?

The second question:

What about manually running sql scripts? The documentations states
that some characters may be displayed incorrectly. How can I be sure that the data is imported correctly (a part from running an application test i.e. by trial and error)?


texts: How to make ST_DWithin to use meters for distance in Postgis?

texts:
I have already asked a similar question here:

Find the nearest geo points across two tables with Postgis (or without)?

but now I am stuck around the correct geo-reference to calculate the distance in ST_DWithin. What I have did with my tables is creating a column geom in this way:

ALTER TABLE my_table ADD COLUMN geom geometry(Point, 4326);
update my_table set geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

Using this setup will take the function ST_DWithin to use as distance parameter "degrees" instead of "meters".
How can I set the geom to work with meters? Is this the right setup?

ALTER TABLE my_table ADD COLUMN geom geometry(Point, 2163);
update my_table set geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 2163);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);


texts: Is it possible to virtually vertical partition and oracle database table?

texts:
Just like the question asks; is it possible to transparently partition a table vertically, by using virtual partitions? I'd imagine if it was possible, that columns would just be stored in separate table spaces or some such.

In my specific environment, we have an application server that batch writes a large amount of data every 15 minutes or so. There is way more data than our reporting applications need. The primary fact table has roughly 50 columns. We use about 10 of them.

Is there any scenario (even if virtual vertical partitions do not exist), that such a scheme could improve performance? I'd imagine that having your data split across multiple tablespaces (and therefore potentially multiple disks) would improve the seek times.


texts: In SSAS Parent-Child-Hierarchy how to use MembersWithDataCaption

texts:
I have a parent-child-hierarchy with MemberWithData=NonLeadDataVisible. I tried different values for MembersWithDataCaption, but I always get name of datamember equal to name of inner node. I am using SSAS 12.0.5538.0.

Questions:

Where to set MemberWithDataCaption, Parent or Key?
MemberWithDataCaption is a formatstring, how does it work? For
simplicity I set the value "parent" without effect (after deploy and
process).
What are the conditions for MemberWithDataCaption to actually work?


texts: Unable to connect to MySQL cluster database

texts:
I am unable to connect to mysql cluster database after starting my management node, data node, mysql node. I made the necessary changes by adding the loadbalance in the dbURL. But I am not able to access the connection. The mysqld port is 1186. Please assist.

String dbURL = "jdbc:mysql:loadbalance://localhost/bank";
String dbDriver = "com.mysql.jdbc.Driver";
private Connection dbCon;
public boolean connect() throws ClassNotFoundException, SQLException {
        Class.forName(dbDriver);

        // login credentials to your MySQL server
        dbCon = DriverManager.getConnection(dbURL, "root", "");
        return true;
    }

The error I am getting when I try to log in is:

  javax.servlet.ServletException:
  com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
  Communications link failure

How do I test MySQL cluster database? I managed to login using the following in command prompt. Please assist.

  mysql -h 127.0.0.1 -P5000 -u root


texts: Index Spool (Eager Spool), Difference in number of row before and after the operator

texts:
I was optimizing one of the Stored Procedure for which I went through the execution plan of it.
In the execution plan I see the operator Index Spool (Eager Spool).
There I see it is taking 1759 rows as input.

And returning 69494 rows.

Just wanted to know how does it work and how is it possible?
Thanks!


texts: Why does SQL Server Profiler show some completed statements in pairs (twice)?

texts:
In SQL Server profiler I am seeing pairs of completed statements. A pair is shown below. The TextData (query) is exactly the same. The SPIDs are different (86 & 88). The # of reads and writes are a little different.
(as an FYI, I am running a custom template where I am filtering out exec sp_reset_connection statements and showing just the completed statements).
I am seeing a bunch of these pairs of SQL:StmtCompleted and RPC:Completed and every pair contains the same SQL in TextData.
The statement shown was executed once by Entity Framework in the client.
My question: Is SQL Server actually running the queries twice? If yes, does this mean definitely the client is sending the query twice?


texts:
After configuring replication all the data from tables has replicated from publisher to subscriber.  Now my requirement is whenever I insert new record in publisher only that record should replicate rather than reinitialize all the records to subscriber because my table is having millions of records.

Rather than drop and reinitialize snapshot cont we store earlier sync data of table in subscriber and only sync the new record.

Is there any way to do this?


texts: Struggling with MySQL character set, collation

texts:
I am facing problems with inserting Spanish characters from my application.

When I am inserting on the DB the characters are inserting properly, but when I am trying passing the same data in from the application, the data stored in the table isn't correct.

For example, when I insert directly on DB server:

CREATE TABLE  prod_inns(`prod_name` varchar(100) COLLATE latin1_bin DEFAULT NULL)

insert into prod_inns values(Otoño);

I see this:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| Otoño       |
+-------------+

delete  FROM  prod_inns ;

However, when I insert the same value from the application form, I see this instead:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| OtoÃ±o      |
+-------------+

Here are my MySQL settings:

MySQL>  show global variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MySQL>  show global variables like 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

What's wrong with my settings? What should I do to store the data and read the data properly?

Note: I am using mysql-5.7.19


texts:
I am using merge replication with SQL 2012. I have a number of articles in my publication and will have to come up with quite an elaborate set of filters.

If I add a filter on my article it can contain a sub query, but I noticed it doesn't pick up on changes properly.

For instance Table1, and Table2.

Filter on Table1 is

select * from Table1 where id in (select id from Table2)

If more records are added to Table2 I would want the contents of Table1 to be changed accordingly when the sync process next occurs.

I found out that this is not working. Is it because when I tried it Table2 was not an article in my merge replication publication, or is it because merge replication doesn't support subqueries like this and recognising when a filter has changed.

What it would mean is that the sync process would have to scan each filter, and work out what tables the subquery relates to and figure out whether these have changed. Does it do something like this?

UPDATE:

I have tried a number of things, but they don't work.

1) Subquery - doesn't work. Here it confirms that this won't work
    http://support.microsoft.com/kb/324362
2) Subquery calling a function - doesn't work
3) Subquery calling a function which takes a guid parameter which I change every time - doesn't work

So the only other option mentioned is a view, but I cannot get the view to work either.

The first problem is that if I replicate a view it just gets replicated as a view and the underlying table needs to be there. But Simon I read your other post and that is not what you are saying.

I think what you are saying is that I should be able to select a table in my replication publication, and then filter it based on a view like this,

select cols from vwUserFilter

But when I look in management studio I see a filter for my table which looks like this,

SELECT <published_columns> FROM [dbo].[Berm] WHERE

And that part of my filter is fixed, I cannot edit [dbo].[berm] and replace it with a view.

So what I think you are suggesting is that I can change the above to,

SELECT <published_columns> FROM [dbo].[BermView]

And the BermView is a view which filters the Berm table. Is that right, and if so how?


texts: mysql CREATE TRIGGER after insert on Update column with count

texts:
it gives error. I don't understand why.

CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate`
FOR EACH ROW
BEGIN
UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near '' at line 1

-EDIT-

agian error...

mysql> DELIMITER ; CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate`  FOR EACH ROW  BEGIN  UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id); END; DELIMITER ;
mysql>
mysql> show triggers;
Empty set (0,00 sec)

_ LAST ATTEMPT'S ERROR _

mysql> DELIMETER $$                                                                 -> CREATE TRIGGER estatecat_piece
    -> AFTER INSERT ON estate FOR EACH ROW
    -> BEGIN
    -> UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id) where estatecat_id=new.estatecat_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMETER $$
CREATE TRIGGER estatecat_piece
AFTER INSERT ON estate FOR EACH ROW ' at line 1


texts: First Normal Form: Definitive Definition

texts:
I am trying to get a definitive version of what is First Normal Form. Everything I read has a slightly different spin.

Many authorities, such as Date, say that by definition a relation is always in First Normal Form, while others give a list of requirements. This means that there are from zero to many requirements for 1NF.

I suppose the difference is that between tables and relations: tables can be a complete mess, while a relationship follows certain restrictions. The fact that a relation is represented as a table in SQL thus creates some confusion.

I am focusing specifically on 1NF as it relates to SQL databases. The question is: what properties are required to ensure that a table is in the first normal form?

Many authorities suggest that if a table represents a relation, then it is already in 1NF. This pushes the definition of 1NF back to the definition of a relation.

Here are some properties of a table in 1NF:

Column Order is insignificant [1]
Rows Order is insignificant
All rows are the same length (ie, row data matches column headers)
There are no duplicate rows (this can be guaranteed using a surrogate primary key, but the PK is itself not required)
There are no repeating columns
Each column contains a single value (atomic)

[1] Technically attributes are unordered, but in a table, the row data must be in the same order as the column headers. However, the actual order is insignificant.

On multiple data:

The concept of atomic data is that an item cannot be further broken down. This concept has been qualified in that although technically everything can be broken down ad nauseum, the data in question cannot be practically broken down any further, depending on how the data will be used.

For example, a full address or a full name should normally be broken down further, but the components such as the given name or town name should probably not be broken down any further, despite the fact that as strings they can be.

As regards repeating columns, it is a poor design column to have nearly repeating columns, such as phone1, phone2 etc. In general, repeated data indicates the need for an additional related table.

Dependence

There should not be any relationship between rows, other than that they conform to the same headers.

There should also be no relationship between columns, but I believe that is the subject of higher normal forms.

The question is: How much of the above is in the definition of 1NF? Does the independent rows bit also come into it?


texts: How does SQL Server order results when joins are used?

texts:
How does SQL Server figure out the order of the records in the result set of query execution?

I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I am selecting the order also changes. When I execute the below SQL with a SELECT * I get the same records but in a much different order.

 SELECT TOP (900)
    AD.ATTACHMENTID,
    AD.NAME,
    AD.ISINLINE,
    AD.INSERTEDDATETIME,
    ATMT.ATTACHMENTBLOB,
    U.UFID
  FROM ATTACHMENTDETAIL AD WITH (NOLOCK)
  INNER JOIN MESSAGEATTACHMENT MA ON MA.ATTACHMENTID = AD.ATTACHMENTID
  INNER JOIN ATTACHMENT ATMT ON ATMT.ATTACHMENTID = AD.ATTACHMENTID
  INNER JOIN MESSAGE MSG ON MSG.ID = MA.MESSAGEID
  INNER JOIN MESSAGEDETAIL MD ON MD.MESSAGEID = MA.MESSAGEID
  INNER JOIN [USER] U ON U.ID = MD.USERID
  LEFT OUTER JOIN XmlExtractionMapping XM ON MA.MESSAGEID = XM.MessageId
    WHERE AD.FILEBOXTOKEN IS NULL
    AND (XM.XMLEXTRACTIONDATE IS NOT NULL OR
         (MSG.MESSAGESOURCEID = 1 AND MD.FolderId <> -4))
    AND AD.ISINLINE = 'FALSE'


texts: What will be the best mysql configuration for 12 core processor 128 gb ram ssd 160gb hdd

texts:
What will be the best mysql configuration for 12 core processor, 128 GB ram sized 160gb HDD.
Mysqltuner.pl result!

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.71
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 79M (Tables: 124)
[--] Data in InnoDB tables: 5M (Tables: 339)
[!!] Total fragmented tables: 346

-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 13h 37m 35s (94M q [1K qps], 9M conn, TX: 46B, RX: 22B)
[--] Reads / Writes: 56% / 44%
[--] Total buffers: 34.0M global + 2.8M per thread (1000 max threads)
[OK] Maximum possible memory usage: 2.7G (2% of installed RAM)
[OK] Slow queries: 0% (0/94M)
[OK] Highest usage of available connections: 34% (346/1000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/35.1M
[OK] Key buffer hit rate: 97.9% (1B cached / 38M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10M sorts)
[!!] Joins performed without indexes: 3360
[!!] Temporary tables created on disk: 49% (4M on disk / 9M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (214 open / 15M opened)
[OK] Open file limit used: 4% (225/5K)
[OK] Table locks acquired immediately: 95% (94M immediate / 99M locks)
[OK] InnoDB data size / buffer pool: 5.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)


texts: SQL Server Table Reports Disk Usage 0 Rows but reserved 46 GBs

texts:
I've executed report on database "Disk Usage by Table"
And I have couple of weird tables, where there are no rows, but disk used couple dozens of gigabytes.
What caused such behavior? What to do with this?


texts:
I am using postgresql-9.5. Creating an extension postgres_fdw seems to be taking too long without throwing up any error.
create extension postgres_fdw;

It keeps on running till I terminate the query and it is not getting created. Already have contrib module installed on the server.
Any possible reason?


texts: Multiple database directories with postgresql

texts:
I installed postgresql from source on Ubuntu 14.0.4. It is located in /usr/local/pgsql. At first, I created a data directory at /usr/local/pgsql/data and then ran

initdb -D /usr/local/pgsql/data

I noticed inside this directory was a postgresql.conf.

Later I decided I wanted to put the data directory in the /var directory, since it is variable data. So I created a directory at /var/local/postgresql/9.4.2/main (I guess I should have named it data, but when using the repositories it uses main and I decided to follow that). Then I ran initdb again but this time specifying a new data directory:

initdb -D /var/local/postgresql/9.4.2/main

And pretty much it created the same data files as it did before, including the postgresql.conf. This time I opened up the postgresql.conf at /var/local/postgresql/9.4.2/main and gave it a default data directory:

data_directory = '/var/local/postgresql/9.4.2/main'

Now, at this point I wanted to start the postmaster daemon but I want the daemon to read the data directory from /var/local/postgresql/9.4.2/main rather than me specifying it as a flag. Unfortunately, it doesn't work:

$ pg_ctl start
pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information.

How can I get this to work?


texts: Difference between fetched and returned tuples in postgresql

texts:
I'm facing a hard time trying to figure it out some performance problems in my database. I'm using a bunch of resources online to learn what to monitor and how to interpret that information.

From the above, I'm unable to find a clear explanation of what is the difference between pg_stat_database.tup_returned and pg_stat_database.tup_fetched.

In pgAdmin4, there is a beautiful chart called "Tuples out" where these two concepts are contrasted, but I don't know how to interpret the info. In the official documentation only says that:

tup_returned: Number of rows returned by queries in this database
tup_fetched: Number of rows fetched by queries in this database

What exactly does "fetched" and "returned" mean?

I'm using postgresql 10.


texts: Getting around the constraint "column must appear in the GROUP BY clause or be used in an aggregate function"

texts:
I'm using Postgres, which enforces the constraint that all columns in a SELECT...GROUP BY must appear in the GROUP BY clause or be used in an aggregate function.

Lets say I'm modelling peoples' cars, and I want to work out a person's name, license number, and how many cars they have. Here's my example as an SQL Fiddle.

I would have the following schema:

CREATE TABLE person(
  id integer PRIMARY KEY,
  name text
);

CREATE TABLE license(
  person_id integer REFERENCES person(id),
  expiry_date date
);

CREATE TABLE car(
  owner_id integer REFERENCES person(id),
  registration_number TEXT
);

Here's the query:

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.id;

I know, because of my own business logic, that one person can only have one license, so when I join to the person, even though it's GROUP BY'd, I should be able to find their license number. However because it isn't part of the GROUP BY statement and doesn't use an aggregate function, I can't access it.

How should I write this query in an idiomatic way. I've seen vague things about LATERAL JOINs, window functions, and subqueries, but I'm not sure the best way to answer this question.


texts: Synonym being recreated randomly - possible to create some kind of trigger/log?

texts:
We have a couple of databases with synonyms to link each other, one of the synonyms is randomly being recreate incorrectly with an additional set of square brackets in the database name. I've checked and rechecked every script we have and none seem to be the culprit.

At this point I'm trying to see if I can log any transaction which drops/creates a synonym, is it possible?


texts: Efficient way to transpose large categorical column to one-hot table row

texts:
I have a varchar column containing ~ 8000 unique values and 500k rows.  I need the column transposed so that the column headers are the unique values and the values in the rows are 1 where the row has an entry with the header and 0 elsewhere.  This is sometimes referred to as one-hot-encoding.

I have tried pivoting the table to columns but, unsurprisingly, even on a very small subset of rows the operation is very inefficient. Is there an efficient way to do this in sql?

I would like to transpose the column Category,

Data1   Data2   Category
  0.1      24          A
  0.23     10          B
  0.21     11          B

to this output:

Data1   Data2   A   B
  0.1      24   1   0
  0.23     10   0   1
  0.21     11   0   1


texts: Mass update all cells in a column with a substring of it's original value

texts:
I need to trim the last seven characters off of all the cells in a column.
This query returns the results that I need
SELECT test_date, SUBSTRING(test_date FROM 1 FOR CHAR_LENGTH(test_date) - 7) as test from results;

I now need to take the computed result from the substring and replace the original values.
Here's what the data looks like, if it helps.


texts:
This is running SQL Server 2012 SP2 Enterprise Edition.

I have a nightly ETL job that, for a specific SP, is taking way too long to run.  During my investigation I found that it was a specific update as part of the SP that is estimating a single row in its execution plan, hence the bad plan.

A bit of background on this SP, it does some ETL and loads into a table then 5 different update statements run as part of the SP and update this table, it's one of these updates that's causing the problem.

More annoyingly, this SP does have parameters but they aren't used anywhere in the actual SP. I am waiting for dev to remove these and obviously it needs fixing elsewhere.

To try and solve I have put some rebuild statistics in the SP after the ETL part, as during investigation doing this then rerunning the offending update statement returned a good plan but when this runs as part of the nightly ETL it keeps returning the bad plan (SQL Sentry monitor is paying for itself here!!), even with recompile, I have also tried creating a new SP and calling that after the update stats instead of the updates text, wondering if it was some sort of scope issue, but to no avail.

The frustrating part is that if I execute this SP manually all is ok, it runs in less that 60 seconds. If I leave this to run as part of ETL it takes roughly 3 hours to run.  I am at a loss as to why this is happening...  I am wondering if this is something to do with the way the SP is called as the devs have dynamically called the SP by using Execute T-SQL and then manually building the SP name?


texts: How can I find the source of postgresql per-connection memory leaks?

texts:
I'm using postgresql 9.5.4 on amazon RDS with ~1300 persistent connections from rails 4.2 with "prepared_statements: false".  Over the course of hours and days, the "Freeable Memory" RDS stat continues to go down indefinitely but jumps back up to a relatively small working set every time we reconnect (restart our servers).  If we let it go too long, it goes all the way to zero and the database instance really does start to go into swap and eventually fail.  Subtracting the freeable memory over days from the peaks when we restart we see that there are 10's of MB per connection on average.

Digging into the per-pid RSS from enhanced monitoring, we see the same slow growth on example connection pids but the total RSS seems to just be a proxy for actual memory usage per connection (https://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/).

How can I either:

Change the default.postgres9.5 parameters below to avoid unbounded memory growth per-connection
Determine what queries cause this unbounded growth and change them to prevent it
Determine what type of buffering/caching is causing this unbounded growth so that I can use that to do either of the above


texts: Unique constraint violated even though the table is empty

texts:
The item_type table is empty, yet on this:

INSERT INTO item_type (id, name) VALUES (1, 'PHYSICAL')

I get error:

Error: ORA-00001: unique constraint (MYDB.SYS_C0013475) violated

SQLState:  23000
ErrorCode: 1

It seems like it's simply a PK constraint on ITEM_TYPE table, though I can't say for sure as I'm Oracle newbie.

But if it is, why does it prevent inserting into an empty table?

Note: I have been purging all database objects frequently to port the data again (due to administrative rights I cannot delete this db and recreate it).

UPDATE

SquirrelSQL apparently lies about table DDL, this is DDL I got via SQL query to Oracle:

  CREATE TABLE "MYDB"."ITEM_TYPE"
   (    "ID" NUMBER(*,0) NOT NULL ENABLE,
    "NAME" NVARCHAR2(2000),
     PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"

It's been generated by SQLAlchemy (Python ORM) from class declaration. This has worked without a hitch on PostgreSQL, yet on Oracle it gives me this kind of problem.


texts: SQL Server: Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'

texts:
I've just put in a request for our storage engineers to add more physical space for the LUN with tempdb. In this case, it was definitely needed. However, I'm disturbed by the SCOM report which flagged tempdb as using a 'DEFAULT' filegroup.

Can anyone clarify for me what 'DEFAULT' means in this context, since we don't have a filegroup called 'DEFAULT'?  We have a single filegroup for tempdb called 'PRIMARY'.


texts: SQL Server Agent logs in as guest on a linked server

texts:
I have a job called @test_credentials which runs the following query
select * from openquery(SERVER2,
'select USER_NAME(),* from openquery(SERVER1,''SELECT USER_NAME() '')')

and outputs those results to a file.

Job '@test_credentials' : Step 1, 'test_credentials' : Began Executing 2015-08-31 17:53:45
guest                                                                                LinkedServerUser
(1 rows(s) affected)

The linked server definitions for both SERVER1 and SERVER2 are using
the linkedserveruser to login to one another (security context option).
Both linked server definitions are defined with the same options
The linkedserveruser user account exists on both servers and is not disabled in anyway.
The service account running the AGENT is identical for both SERVER1
and SERVER2.

So my question is this:
Why is SQL Server Agent logging in as guest on SERVER2 when executing this job?


texts:
Create Security Policy without schemabinding?

Is this a bad idea? I have an indexed view in another database that I need to use in my security predicate for row level security. I can either turn off schemabinding and use a synonym, or I have to start using transactional replication to copy to a table in the main database. I control both dbs and they are on the same server.


texts: Why would you index text_pattern_ops on a text column?

texts:
Today Seven Databases in Seven Weeks introduced me to per-operator indexes.

  You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as long as the values are indexed in lowercase.

CREATE INDEX moves_title_pattern ON movies (
    (lower(title) text_pattern_ops);

  We used the text_pattern_ops because the title is of type text. If you need to index varchars, chars, or names, use the related ops: varchar_pattern_ops, bpchar_pattern_ops, and name_pattern_ops.

I find the example really confusing. Why is it useful to do this?

If the column is type text, wouldn't the other types (varchar, char, name) be cast to to text before being used as a search value?

How does that index behave differently from one using the default operator?

CREATE INDEX moves_title_pattern ON movies (lower(title));


texts: Combining multiple queries into a single query

texts:
I've got checkbox group field with 4 days of the week - Thursday, Friday, Saturday & Sunday. I populate this field based on whether 4 separate text fields for each corresponding day of the week are populated or not.

At the moment, I'm doing this by running 15 separate queries! This is every combination of Thursday, Friday, Saturday & Sunday I can think of, which then updates the value for this checkbox group field.

The queries are listed below for info. My question is, can anyone think of a way to update this checkbox group field with a single query rather than 15 separate queries? Thanks for any help. Regards, Stephen.

UPDATE exp_channel_data SET field_id_285 = "Thursday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Saturday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Sunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSaturday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSaturday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSaturday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Saturday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 <> "");


texts: SQL Server AS: Set ImpersonationMode via PS

texts:
Try to create a database in SQL Server Analysis Server (SQLAS, SSAS) version 2012 SP2 if that matters and set Impersonation Mode via Powershell. (try to avoid XMLA script).

Here are my attempts. Does anybody have the right command handy?

Simple create

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$DBASserver = New-Object Microsoft.AnalysisServices.Server
$DBASserver.connect("mysqldbserver.dom.com")

$database=$DBASserver.databases
$database|select-object name, id

$DBASserver.databases.add("Finance")

$db = $DBASserver.databases.item("Finance")
$db.update()
$db.description = "Finance Department Data Mart"
$db.update()

Getting info on the current mode: (works)

$db = $DBASserver.databases.item("Finance")
$dbmode = $db.DataSourceImpersonationInfo
$dbmode

Update: Setting the mode by getting it from another db works. But creating the Mode object new doesn't
Here working by reading mode from another DB

$db1 = $DBASserver.databases.item("MyDBManualSet")
$db2 = $DBASserver.databases.item("MyNewPSDB")
$dbmode = $db1.DataSourceImpersonationInfo
$dbmode
$db2.DataSourceImpersonationInfo = $dbmode
$db2.update()

Here my attempts to set the mode ImpersonationMode to ImpersonateServiceAccount (nothing works)

$newMode.ImpersonationInfo = new ImpersonationInfo(ImpersonationMode.ImpersonateServiceAccount)
$db2.DataSourceImpersonationInfo = $newMode

or

$newMode= new ImpersonationInfo(ImpersonationMode.ImpersonateServiceAccount)
$db2.DataSourceImpersonationInfo = $newMode

here the documentation I can't piece together  MSDN SQL documentation info


texts: Why is this MySQL query performing slowly despite correct indexing?

texts:
I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation.

So my table A looks like this:

CREATE TABLE `A` (
  `id_b` int(10) DEFAULT NULL,
  `id_c` int(12) DEFAULT NULL,
  UNIQUE KEY `Uniqueness` (`id_c`,`id_b`),
  KEY `id_b` (`id_b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Every entity in B is related to 1-12 entities in C with an average of 8 and a mode of 10.

Every entitiy in C is related to 100-500 entities in B.

Now for a specific entitiy c I want a list of all other entities in C that it is connected to through B and how many entities in B they are connect through.
Or more directly: I have a specific c, that is connected 100-500 b's which are each connected to a handful of other entities in C. I want to know which C's the b's are connect with, and how often.

My simplified query looks like this:

SELECT COUNT(*), OtherA.id_c FROM A
INNER JOIN A OtherA
ON OtherA.id_b = A.id_b
WHERE A.id_c=[MY_SPECIFIC_C_ID]
GROUP BY OtherA.id_c

And takes over a second to execute. As described above, the inner join should yield at max a few thousand results and the group should reduce it to a few hundred. As far as I can tell I indexed correctly, so I am confused why this query is taking so long.

EXPLAIN yields the following result:

row1
id--------------1
select_type-----SIMPLE
table-----------A
type------------ref
possible_keys---Uniqueness,id_b
key-------------Uniqueness
key_len---------5
ref-------------const
rows------------462
Extra-----------Using where; Using index; Using temporary; Using filesort
row2
id--------------1
select_type-----SIMPLE
table-----------OtherA
type------------ref
possible_keys---id_b
key-------------id_b
key_len---------5
ref-------------THISDB.A.id_b
rows------------8
Extra-----------Using where


texts: How do I make an Alert property in SSRS work just like I want it to?

texts:
How do I make an Alert property work just like I want it to?

What is the capacity of Action properties in SSRS? Ok, so, just like a hyperlink in html, in SSRS, most objects have an Action property that you can set to any URL, such as the URL of another report. So this leads me to more questions. There are several ways of setting up hyperlink. You can make it so that when you click on it, it opens an entirely new browser tab, or you can set it up so that the next page overrides the current page displayed in the browser. Is there a way of setting the Action property so that you tell it exactly what you want? But wait, there is more, I have learned (and maybe you have too) that an SSRS page on a 2008 server does not display correctly in a Chrome browser (the report is shown in a tiny iframe for some reason). So is there some way to make the Action property such that it will only open Internet Explorer?

I have two reports.  One I want to make sure that the action does not open any net tabs (I am guessing now that this is the default anyway.  The other report, is going to be sent out as an email alert subscription.  So the Action properties in this second report needs to open a browser.  I want to know if I can structure the Alert such that it opens Internet Explorer instead of Chrome since Chrome does not work.

Here is the next question. Another developer showed me a report he made that is embedded in an iframe. I wonder if that is even necessary if you do not have any need for a separate web page's content for a report. Is there any need for that if I am just going to have one ssrs email alert with Action property objects that will open up another browser showing a direct link to an SSRS report?


texts: Slow SQL Server Database (approx 2TB) - high CPU due to index fragmentation?

texts:
I've posted this over at ServerFault but didn't have much luck getting a response.

I have a fairly large SQL Server database (~2TB). Most of the data is in one table (~6 billion rows).

Two weeks ago I dropped two non-clustered indexes on the large table and migrated the data onto a single 6TB RAID SSD array. I then recreated the two indexes which took quite some time (assuming because I currently have the data (for table and indexes) and log on the same array and it seems that with the RAID I can't have fast sequential AND random r/w at the same time).

Anyway after recreating the indexes it ran very well for about a week. During the week I have been slowly running a clean on the large table which just removes old unneeded rows. So far I've removed about 300 million out of the 6 billion, and at a guess I still have a lot more to go.

Now after about a week of running like this it is now running very slow and I'm not sure which would be best to do.

Current situation:

Dual Xeon
192GB RAM
Windows Server 2012 with SQL Server 2012
CPU is hitting 100% (16 cores) - was only using about 50% prior to the slowdown
IO doesn't seem to working too hard (no queue)

Large Table currently has (I don't have any fragmentation info prior to now):

1x Clustered index: 48% fragmentation
1x Non-clustered index: 36% fragmentation
1x Non-clustered index: 10% fragmentation
I used to have two more indexes on this table but dropped them a while ago

What do you think would best fix my problem

Rebuild the non-clustered indexes on the same array (assume that this should fix the problem but will take a long time to do as it did before. Will probably have the same problem in the near future as I'm still cleaning the table)
Rebuild the non-clustered indexes on a new RAID array (should fix as above but may be faster)
Move the non-clustered indexes to a new RAID array (quickest option)
Recreate the two old indexes on a new RAID array (not sure if this relieves CPU or IO pressure)

Do fragmented indexes cause higher CPU usage?

Is there anything else I could be missing?

TIA


texts: Advice on table processing for invoice-allocation-payment structure for invoices of zero amount

texts:
Please may I have some advice/opinions on the best process to use in the following situation.

To deal with subscription payments in a small charity I use a cash matching system comprising

Table invoice PK invoice_id;

Table cash_payment PK cash_payment_id;

and linking Table payment_allocation PK allocation_id, FK invoice_id, cash_payment_id.

This gives me a many to many relationship between invoice and payment. (the invoice table is only used for admin, no real invoices are sent out)

All is good except for the following situation.

Some members are honorary and do not have to pay subscriptions. Hence there is no requirement to create a record for them in the invoice table. If they don't pay then all is OK.

However, some of these members choose to pay the subscription anyway, resulting in a record in the 'cash_payment' table that does not have a corresponding invoice record or one in Table 'payment_allocation' (as there is no invoice to allocate it to) .

I therefore have two choices. Either

Generate an invoice for everyone, including those for whom the amount will be zero and record all payments made, linking them to the appropriate invoice.
This will result simply in an over payment being recorded for those honorary members who choose to pay unnecessarily.
Do not generate an invoice for any zero amount. When an unnecessary subscription payment happens I insert a record in Table cash_payment with a new payment_type of contribution but without any corresponding record in the payment_allocation table,
This will avoid zero amount invoices but might make it more difficult to report on cash flow and things like over/ under payment.

I'm tending towards choice (1) Does anyone have any hints regarding which might be the better method?


texts: Securing an Internet Database Connection

texts:
I am looking to setup a website and database that are connected via the internet, and am looking to secure the connection. The connection will probably not be over a VPN, so shall be openly exposed.

From the research I have done, I see that you can use SSL and IPSec. The user I am setting up will only have public, db_datareader and db_datawriter roles. The password will be set to expire every set period of time.

Is there anything else I should be aware of to ensure a secure connection from my website to database?


texts:
Problem

Dynamically created users are not able to query a table that contains Large Objects as it did not originally create it.

Background

I have an Java Atom Hopper application deployed to AWS across 2 instances, both using a Postgres 9.5.2 database hosted in RDS.

Vault Credentials

The database credentials are stored in Vault, which generates a new login and password for the databases in Postgres. This user is created with the following permissions.

CREATE ROLE "{{name}}" WITH INHERIT LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';GRANT ALL PRIVILEGES on DATABASE "DATABASE_NAME" to "{{name}}";GRANT databaserole to "{{name}}";

This means each time the Java application starts, it sends a request to Vault, to get a new username and password.

eg. username: token-1234-5678
=> \du

       Role name     |                  Attributes                   |   Member of
    -----------------+-----------------------------------------------+----------------
    token-1234-5678  |  Password valid until 2016-09-11 09:57:14+00  | {databaserole} (java instance 1)
    token-abcd-efgh  |  Password valid until 2016-09-11 09:57:14+00  | {databaserole} (java instance 2)

The tables inside of the database are all owned by databaserole

Schema |          Name           | Type  |     Owner
-------+-------------------------+-------+----------------
public | table1                  | table | databaserole
public | table2                  | table | databaserole
public | entries                 | table | databaserole

Inserting Data

When I insert a new row into table entries, one of the fields is too large and therefore converted into a Large Object, but its owner is set to the login of the app that inserted it, rather than database role.

=> \lo_list
            Large objects
  ID   |     Owner       | Description
-------+-----------------+-------------
 17286 | token-1234-5678 |

As the Java application on instance 2, has a different login to instance 1 eg. token-abcd-efgh, it means it cant execute select * from entries; as it is not the owner of the Large Object.

I am aware that I can change the owner of the object after it has been created, but this is not feasible due to the number of insertions.

Question

How can I set it so that any Large Object has the owner of databaserole rather than the login of the application that created it?

TLDR

As I have an multiple instances of an app, with unique and dynamically created logins. How can I setup Postgres so that the Large Objects always have the ownership of the role's member parent, so that any login that is a member of this parent role can view the object?

SOLVED

I added another transaction to run the follow sql everytime hibernate tries to insert on that table.

SET ROLE databaserole


texts: specify the correct element to pick in JOIN mysql


texts: How to remove multiple redundant indexes?

texts:
I have a legacy app.
I wanted to find out which tables are the largest ones using script from Percona blog. It turned out some of my tables have indices that are larger than actual data by factor of 3 and more.
I've dumped schema off the MySql server and found that some tables have lots of what seem to be redundant indices.

Here's how one of the worst tables is created:

CREATE TABLE `pictures_relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pic_id` int(10) DEFAULT NULL,
  `url_id` int(10) DEFAULT NULL COMMENT 'id урла, с которого файл был/будет скачан',
  `is_stub` tinyint(1) NOT NULL DEFAULT '0',
  `item_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  `item_type` varchar(8) NOT NULL,
  `is_file` tinyint(1) NOT NULL,
  `is_hide` tinyint(1) NOT NULL,
  `is_cover` tinyint(1) NOT NULL,
  `image_only` tinyint(1) DEFAULT NULL,
  `is_custom` tinyint(1) NOT NULL DEFAULT '0',
  `skip` tinyint(1) NOT NULL DEFAULT '0',
  `order` int(11) NOT NULL,
  `author` int(11) NOT NULL,
  `add_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `is_hide` (`is_hide`),
  KEY `is_cover` (`is_cover`),
  KEY `add_date` (`add_date`),
  KEY `item_id_2` (`item_id`,`module_id`,`item_type`,`is_file`,`is_cover`),
  KEY `order` (`item_id`,`module_id`,`item_type`,`is_file`,`order`),
  KEY `item_id` (`item_id`,`module_id`,`item_type`,`is_file`,`is_custom`),
  KEY `pic_id` (`pic_id`),
  KEY `item_id_3` (`item_id`,`module_id`,`item_type`,`is_file`,`skip`),
  KEY `item_id_4` (`item_id`,`module_id`,`item_type`,`is_file`),
  KEY `pic_id_2` (`pic_id`,`skip`),
  KEY `skip` (`skip`,`pic_id`),
  KEY `stub_url` (`is_stub`,`url_id`),
  KEY `stub_skip_url` (`is_stub`,`skip`,`url_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6794944 DEFAULT CHARSET=utf8;

I can clearly see that indices order, item_id, item_id_2, item_id_3 and item_id_4 are mostly similar, but they differ a bit. Using convenience views for easily detecting redundant indexes from this DBA.StackExchange answer helps, but not much — it does show that item_id_4 is redundant to all others, but doesn't help with splitting/merging them into more sane indexes.

The question is:
Is there a Rule of Thumb to refactor these indexes into something sane, with less duplication?

Like, if I have idx1('a', 'b', 'c'), idx2('a', 'b', 'd'), idx3('a', 'b', 'e'), then I have to make it into idx1('a', 'b'), idx2('c'), idx3('d') and idx4('e')?


texts: MongoDB geospatial query with sort - performance issues

texts:
I have query (which is very slow ~2,5s):

db.markers.find({ latlng: { '$within': { '$box': [ [ -16, -140 ], [ 75, 140 ] ] } } }).sort({_id: -1}).limit(1000)

When I run explain for this query I get

{
   "cursor" : "GeoBrowse-box",
   "isMultiKey" : false,
   "n" : 1000,
   "nscannedObjects" : 242331,
   "nscanned" : 242331,
   "nscannedObjectsAllPlans" : 242331,
   "nscannedAllPlans" : 242331,
   "scanAndOrder" : true,
   "indexOnly" : false,
   "nYields" : 1383,
    "nChunkSkips" : 0,
    "millis" : 2351,
    "indexBounds" : {
        "latlng" : [ ]
    },
    "lookedAt" : NumberLong(262221),
    "matchesPerfd" : NumberLong(242331),
    "objectsLoaded" : NumberLong(242331),
    "pointsLoaded" : NumberLong(0),
    "pointsSavedForYield" : NumberLong(0),
    "pointsChangedOnYield" : NumberLong(0),
    "pointsRemovedOnYield" : NumberLong(0),
    "server" : "xx:27017"
}

When I remove sort({_id: -1}) explain gives me (fast query 5 millis):

{
    "cursor" : "GeoBrowse-box",
    "isMultiKey" : false,
    "n" : 1000,
    "nscannedObjects" : 1000,
    "nscanned" : 1000,
    "nscannedObjectsAllPlans" : 1000,
    "nscannedAllPlans" : 1000,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 5,
    "indexBounds" : {
        "latlng" : [ ]
    },
    "lookedAt" : NumberLong(1000),
    "matchesPerfd" : NumberLong(1000),
    "objectsLoaded" : NumberLong(1000),
    "pointsLoaded" : NumberLong(0),
    "pointsSavedForYield" : NumberLong(0),
    "pointsChangedOnYield" : NumberLong(0),
    "pointsRemovedOnYield" : NumberLong(0),
        "server" : "xx:27017"
}

I have  2d index on latlng, desc index on _id and compound indexes.

db.markers.ensureIndex({latlng: '2d', _id:-1})
db.markers.ensureIndex({ latlng: '2d' })
db.markers.ensureIndex({ _id: -1 })

What I want to achieve is to get markers from a particular area sorted from newest.

Any ideas or suggestions how to do that in much less time than 2.5 seconds?


texts: Is a value that does not change calculated for each row?

texts:
Consider an SQL query with the following WHERE clause:

WHERE expires >= DATE_FORMAT(NOW(), '%Y%m%d')

Will the result of DATE_FORMAT() be recalculated for each row, or will it be calculated once and used for each row? If the latter, I can calculate the value in Python and then inject it into the SQL sent to MySQL.

Due to application restraints, the value is actually a LONGTEXT column and this question is a simplification of the problem. The data must be stored in this column type due to the EAV nature of the table.

This question pertains to MySQL 5.7, but if other database management systems, particularly SQLite or Postgres have different behaviour then I would appreciate knowing that as well. Thank you.


texts: MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

texts:
I had a table with ~0.5M rows and tried to execute a simple query, something like:
UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999

but the result was

ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER

I have MaxNoOfConcurrentOperations=100000 and slightly more than 100000 rows which satisfy the where condition, so I have changed to this query:
UPDATE table
SET col1=-90 + RAND()*90, col2=-180+RAND()*360
WHERE col1=9999 ORDER BY ID LIMIT 100000

...and got the same error. And same error with LIMIT 1000.
I have updated rows using a query with LIMIT 100 in loop, but I think that it is a bad solution.
Questions

Why does MySQL Cluster behave this way?
How can I update many rows in a single table?


texts: How to determine if a column is the first part of an index?

texts:
Is there anyway to tell with SQL if an indexed column is the first part of an index.

The closest thing looks to be information_schema.columns.  But that seems to only tell me if it's a PRI or MUL key (not if it's the only member of that index or what position its in if it were a composite key).


texts: Monitor Oracle query plans for PARTITION LIST (ALL)

texts:
Is there any way I can monitor all the queries running on an Oracle instance and report which  queries are using PARTITION LIST (ALL)?

What I'm essentially asking for is a way to automatically EXPLAIN PLAN on every query, then search the plan results for queries that are not eliminating partitions properly.


texts: How do I resolve Postgresql error, "PL/Perl function must return reference to hash or array"?

texts:
The follow code used to work in Postgresql 8.4.11 with perl v5.10.1:

=# select * From testfunction();
      testfunction
------------------------
 http://www.google.com/

However, after doing a dump and load into Postgresql 9.1.3 with perl v5.14.2 I get:

ERROR:  PL/Perl function must return reference to hash or array
CONTEXT:  PL/Perl function "testfunction"

For reference, here is the function:

CREATE OR REPLACE FUNCTION testfunction(OUT text) AS $$
  use URI;
  return URI->new('http://www.google.com/')->canonical;
$$ LANGUAGE plperlu;

Again, the version of perl changed from v5.10.1 to v5.14.2; however, the return from Data::Peek is the same across both versions:

$ perl -MData::Peek -MURI -e'DPeek(
    URI->new(q[http://www.google.com])->canonical
);'

Output on both versions of perl:

\PVMG("http://www.google.com/"\0)


texts: Create view with column name aliases or hard change column names on a big database with a very low amount of changes?

texts:
Background:
I have to work with an big enterprise geodatabase on an SQL Server Express 2012 server with around 170 tables where many of these tables have 10+ columns. Some column names are repetitive, e.g. FEAT_ID, GEOM, what means that very many tables have equally named column names.
The application program my company accesses these big databases with doesn't support column name length > 31. Only two of all 170 tables own one column with a column name which does not meet the requirements (so overall two column names). I have to shorten these two column names. I guess this could be done by introducing aliases. So it's a very minor change to a big database.
Below is the first problematic table. The problematic column name in this table is FT_ADDRESS_AREA_BOUNDARY_ELEMENT as its length is 32

USE [foo]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [foo].[baron](
[FEAT_ID] [uniqueidentifier] NOT NULL,
[FT_ROAD_ELEMENT] [bit] NOT NULL,
[FT_FERRY_ELEMENT] [bit] NOT NULL,
[FT_ADDRESS_AREA_BOUNDARY_ELEMENT] [bit] NOT NULL,
[FT_RAILWAY_ELEMENT] [bit] NOT NULL,
[COUNTRY_LEFT] [nvarchar](3) NULL,
[COUNTRY_RIGHT] [nvarchar](3) NULL,
[CENTIMETERS] [numeric](9, 0) NOT NULL,
[POSITIONAL_ACCURACY] [numeric](1, 0) NULL,
[ADA_COMPLIANT] [bit] NOT NULL,
[GEOM] [geometry] NOT NULL,
CONSTRAINT [PK_baron] PRIMARY KEY CLUSTERED
(
 [FEAT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Further below is the second problematic table with the too long column name MAIN_OF_DETAILED_POSTAL_POINT_ID:

USE [foo]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [foo].[baroness](
    [FEAT_ID] [uniqueidentifier] NOT NULL,
    [FEAT_TYPE] [numeric](5, 0) NOT NULL,
    [NAME] [nvarchar](254) NULL,
    [LANG_CODE] [nvarchar](3) NULL,
    [COUNTRY] [nvarchar](3) NULL,
    [POSTAL_CODE] [nvarchar](254) NULL,
    [NOTATION] [numeric](2, 0) NULL,
    [ISO_SCRIPT] [nvarchar](4) NULL,
    [MAIN_OF_DETAILED_POSTAL_POINT_ID] [uniqueidentifier] NULL,
    [POSTAL_DELIVERY_TYPE] [nvarchar](1) NULL,
    [POSTAL_POINT_TYPE] [numeric](1, 0) NULL,
    [NC_MAIN] [nvarchar](254) NULL,
    [NC_MAIN_OFFSET] [numeric](1, 0) NULL,
    [NC_SUB] [nvarchar](254) NULL,
    [NC_SUB_OFFSET] [numeric](3, 0) NULL,
    [GEOM] [geometry] NULL,
 CONSTRAINT [PK_baroness] PRIMARY KEY CLUSTERED
(
    [FEAT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [foo].[baroness]  WITH CHECK ADD  CONSTRAINT [FK_Postal_Point_MAIN_OF_DETAILED_POSTAL_POINT_ID_2_Postal_Point] FOREIGN  KEY([MAIN_OF_DETAILED_POSTAL_POINT_ID])
REFERENCES [foo].[baroness] ([FEAT_ID])
GO

ALTER TABLE [foo].[baroness] CHECK CONSTRAINT [FK_Postal_Point_MAIN_OF_DETAILED_POSTAL_POINT_ID_2_Postal_Point]
GO

Questions:
Is it worth creating a view (or multiple?) or should I just "hard change" these two columns and track it appropriately? I have no experience with views at all. When I tried to create an appropriate view with Microsoft SQL Server Management Studio, I received an error message that column names should be unique. Do I have to create aliases for every repetitive column name in the whole database when creating a view? Should I create a view with only the two changed tables and one with the rest of the database and merge them? I could also imagine that the performance would suffer if I create a view where I have to select the whole database at once.


texts:
We have SQL Server 2016. We tried to manually fail-over our availability group (has 2 replicas no listener), and an error message was given while failing-over.

Windows cluster service is up and running with no errors in cluster services. I tried adding NT AUTHORITY\SYSTEM  as sysadmin on the SQL server nodes.

The problem still persists.

Error message :

Manual Failover failed (Microsoft.SqlServer.Management.HadrTasks)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Hadr.FailoverTaskWorkItem.DoWork()
   at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

===================================

Failed to perform a manual failover of the availability group 'XX' to server instance 'XXX'. (Microsoft.SqlServer.Management.HadrModel)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.16000.28+((SSMS_Rel).161022-0456)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.HadrModel.Task.Perform(IExecutionPolicy policy, CancellationToken token, ScenarioTaskHandler taskDelegate)
   at Microsoft.SqlServer.Management.Hadr.FailoverTaskWorkItem.DoWork()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.DoCustomAction(String script, String toplevelExceptionMessage)

===================================

Failed to move a Windows Server Failover Clustering (WSFC) group to the local node (Error code 5963).  The WSFC service may not be running or may not be accessible in its current state, or the specified cluster group or node handle is invalid.  For information about this error code, see "System Error Codes" in the Windows Development documentation.
Failed to designate the local availability replica of availability group 'XX' as the primary replica.  The operation encountered SQL Server error 41018 and has been terminated.  Check the preceding error and the SQL Server error log for more details about the error and corrective actions. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.2164&EvtSrc=MSSQLServer&EvtID=41018&LinkId=20476

------------------------------
Server Name: XX
Error Number: 41018
Severity: 16
State: 0
Line Number: 1

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

We have 3 nodes, 2 local and 1 Azure.
I have added SYSTEM as sysadmin to all three nodes. Before failing over I change the availability mode to synchronous and make sure there is no data loss.


texts:
Is there any way to select the default value a procedure/function has for a specific parameter in the procedure/function ?
user_arguments has a field called default_value (long) but I do not see any value in the field.
Thanks.


texts:
We are designing a system which holds statistical parameters about a particular user. As the users use the system the statistics change and the updated statistics are stored in the database. So for a new user a record is inserted and after that the records are only updated. So I expect this database to be update heavy.

Also when we see the user online we want to query his record from the database so that we can use the stored data to do some characterization.

The fields of the database which are important are

user Identifier to uniquely identify the user.
last accessed time to know when the record was last updated.

If a user is idle for more than 60 days we want to prune the entry from the database. We currently use postgres as our server.

I have been working on how to implement a efficient way to prune the database while keeping update and query performance satisfactory.

Current Design plan:

EDIT: Estimated Number of Records: 100 million.

Set up partition on postgres. Each partition corresponds to one day of usage.
The partitioning criteria would be the date of the last accessed time. The primary key for the partition would be UserId.

If a record is accessed frequently in the same day, it would map to the same partition but across different days i would have to delete the entry from the old partition and move it to the new partition using a update trigger.

A cron job would run daily and delete the old required partition and add the new partitions. A stale entry would probably remain in the partition and truncate table on the required partition would drop all the entries.

The query would be done based on the UserID, if the partition logic is correct we would find the userid in only one of the partitions. So the query would take place across all partitions but find only one record

I need the pruning performance to be good. This is because the table has primary key by User ID. It has no indexes based on time. Since the last accessed time keeps changing it cant be made part of any index. Also there is no logical clustered index field for this table.

If partitioning is not done, this would mean i would have to do a full table scan and since last accessed time is not part of the key, it would bring every record to the database eventually and this would replace existing frequently used pages used by our system. I would want to avoid a full scan on the table.

As pointed out, moving between partitions would require a delete and a insert into the other table. Space is not a issue. I can add the partition date as primary key along with the UserID field of the database and modify the query method to take the latest record. The query would be done on the primary keys and hence the computation would only be on the index files. I could avoid the delete and let the entries be in the database with the cron job clearing the table eventually.

But nevertheless insert into another partition would always happen on moving a record across paritions. A single table approach would solve this but then the delete performance would suffer.

Any help or suggestions on this would be appreciated


texts: What is the strategy of query evaluation in DBMS when Limit is used in query?

texts:
I have following tables in database

post(post_id , user_id , post_text , parsed_text) ;
Comment(comment_id , post_id , user_id , comment_text) ;
friend(id , user_id , friend_id ) ;
group (id , user_id ,group_id ) ;
like  (id, user_id , page_id ) ;

SQL to retrieve data from posts

 $posts =  mysql_query("select * from post where user_id in
(select f_id  from ((select friend_id as f_id from friend  where user_id = 'a')
union (select group_id as f_id  from group  where user_id = 'a')
union (select page_id as f_id   where user_id = 'a'  ))) order by post_id limit 0 , 30");

In these table there are BTREE index on post_id in post table , comment_id in comment table and  id in like , friend , share table ?

How DBMS evaluates this query to get 30 rows .

Is there any way to retrieve data from database from bottom  of the table ? Bottom in the sense id is auto increment so i need to retrieve data from database as

If table has id 'N' then it should start retrieving data as
first 'n-1' th'tupple ,second  'n-2' th tupple ,third 'n-3' th tupple and so on
without using 'ORDER BY' .

This can reduce time complexity for above query .


texts:
I want to pass one validation DML statement on the trigger. Condition is below DML statement

select distinct t1.cid
from table1 t1, table t2
where t1.cid=t2.cid
      and
      t1.status<>'5'

if table1.status is  not 5, then trigger will block delete transaction.

CREATE TRIGGER LOAD_DROPID_DEL on WMWHSE2.table2
INSTEAD OF DELETE
AS
Begin
    declare @caseid VARCHAR(10)
    select @caseid = deleted.cid from deleted
    If exists(select * from table1 t1,table2 t2 where t1.cid=t2.cid AND t1.STATUS<>'5' AND t2.cid=@caseid)
    begin
        Rollback Transaction
        RAISERROR ('Deletions not allowed after loaded ', 16, 1)
    end
end

Will the above trigger will give correct solution?


texts: evaluate execution plan with different statements

texts:
I have 2 different sql statements similar to this:
SELECT * FROM complicated_view v WHERE v.day = DATE '2020-01-25';
SELECT * FROM complicated_view v WHERE v.day = DATE '2021-01-25';

while both of them could be executed with the same execution plan, they don't. One is slow, the other one is fast. Now I'm trying to figure out why. What I'm currently looking for, is a way to evaluate an SQL statement identified by its sql_id with different execution plans, so I can see which step the optimizer estimates to be pretty costly. Something like this:
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash1');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash2');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id2', plan_hash_value => 'plan_hash1');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id2', plan_hash_value => 'plan_hash2');

Unfortunately, there is no function "evaluate_plan" in dbms_xplan, it's a functionality I'm missing and hope to get a hint on how to get this information.
That way I could identify the step that the optimizer considers to be too costly and work on that.
Is there any functionality in oracle that can give me this information?
oh, and here is the advanced question: Can I do this and in addition also add the optimizer_feature enable, something like this:
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash1', OPTIMIZER_FEATURES_ENABLE => '19.1.0');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash1', OPTIMIZER_FEATURES_ENABLE => '12.2.0.1');

we're getting very different runtime behaviour due to this setting.
I'm not looking for help on how to make my view better. I'm not asking WHY the statement is slow. I'm looking for help on HOW to work on problems like this. I'm looking for a way to evaluate an execution plan for a statement in a scenario where it COULD be executed with the execution plan, but has never been, since another execution plan was always used.


texts: Connecting to database using Toad in windows

texts:
I am trying to connect to a database using Toad on windows 7.

But I am not able to connect because I dont have SQLNET Editor, TNSNames Editor and LDAP Editor installed.

Please help me with this. I am not able to find out in which package these 3 come in.

I have Oracle network client, SQL Client 2008 and SQLite Expert Professional installed.

What extra I need to install?


texts: Collect information about currently running queries only when client timeout occurs

texts:
We would like to be able to collect information about all currently running queries when (and only when) a client timeout occurs in SQL Server 2008.

I know I can collect information about the client timeouts using the "Attention" Event Class in SQL Profiler.

And I know I can gather information about the currently run queries using sys.dm_exec_requests.

But how can I connect these two, i.e. query for all currently active queries only when an Attention Event Class appears?

Would this be possible using extended events (I have never used them, so am not sure about their capabilities)? Or rather some other mechanism?

Any help is appreciated.