texts: Mongos version 3.0.1 Split Chunk Error with Sharding

texts:
I have 3 config server and two shards. Each shard has a primary, secondary and an arbiter.

This is running with the following mongod settings:

storageEngine=wiredTiger

wiredTigerJournalCompressor=zlib

wiredTigerCollectionBlockCompressor=zlib

The mongos instance created the following log entries below about not being able to get a lock during the sharding balancing. All the data can be queried fine however.

Also all seems fine with sh.status() command:

shards:

{  "_id" : "ib3_prod_rs0",  "host" : "ib3_prod_rs0/ip-10-0-13-119.ec2.internal:27017,ip-10-0-21-50:27017" }

{  "_id" : "ib3_prod_rs1",  "host" : "ib3_prod_rs1/ip-10-0-6-138:27017,ip-10-0-8-202.ec2.internal:27017" }

balancer:

Currently enabled:  yes

Currently running:  yes

    Balancer lock taken at Wed Apr 01 2015 00:00:49 GMT+0000 (UTC) by 54.163.248.60:27017:1427843620:1804289383:Balancer:1681692777

Failed balancer rounds in last 5 attempts:  0

Migration Results for the last 24 hours:

    No recent migrations

How can I debug what is going on?

  2015-03-31T23:41:06.542+0000 I SHARDING [Balancer] moveChunk result: {
  ok: 0.0, errmsg: "could not acquire collection lock for
  cubes_prod.pushqueues to migrate chunk [{ : MinKey },{ : MaxKey }) ::
  caused by :: could not get status from ser...", $gleStats: {
  lastOpTime: Timestamp 0|0, electionId:
  ObjectId('551b29f564e83f84e725241e') } } 2015-03-31T23:41:06.543+0000
  I SHARDING [Balancer] balancer move failed: { ok: 0.0, errmsg: "could
  not acquire collection lock for cubes_prod.pushqueues to migrate chunk
  [{ : MinKey },{ : MaxKey }) :: caused by :: could not get status from
  ser...", $gleStats: { lastOpTime: Timestamp 0|0, electionId:
  ObjectId('551b29f564e83f84e725241e') } } from: ib3_prod_rs1 to:
  ib3_prod_rs0 chunk:  min: { UserKey: MinKey } max: { UserKey: 0 }


texts:
I'm having trouble understanding the logic in use for identifying functional dependancies.

Looking at the sample relation below, I understand fd1 - fd3. But when I look at fd4 and fd5, its logic makes me believe that fd6 and fd7 would also be possible, but it's not according to the book I'm studying.

The logic that operates fd4 and fd5 is to me:

  We conclude that unique combination of values in in columns A and B such as (a, b) is associated with a single value in column E, which in this example is "q". In other words attributes (A, B) functionally determines attribute E, and this is shown as fd4 in the sample relation. We also conclude that attributes (B, C) functionally determine attribute E using the same reasoning described earlier, and this functional dependancy is shown as fd 5 in the sample relation.

So why is fd6 and fd7 not true?


texts: Is there a best practice for recurring calendar events

texts:
I'm not a dba but a php programmer, so that I'm not an SQL pro. Now, I have searched in both programmers and dba stackexchange sites but the answers are too specific or not conclusive.

My question is very simple, I need to create a calendar. just a calendar, like Google calendar, with normal and recurring events.

Now to summarize what I have learn in my research about recurring events, appears to be 2 main schools:

Create a "parent" event and an occurrences table with one row for each repetition of the event.
Create an events table (1 row for a recurring event) and a second table to store repetition pasterns (generally 1 or 2 row per event).

There are good and bad things in each of the 2 solutions:

Easy to query, easy to create exception (like changing only 1 occurrence in the series), easy to store complex patters, BUT will create a lot of rows, hard to export (ex iCal), easy to cause data integrity issues.
Easy to store complex patterns, clean, easy to export, BUT difficult to query.

When a I say hard or easy to query, I mean to query for a specific time range. For example I want to query all events (or occurrences) for this month, having 2 Timestamps.

The complexity of the recurring events should also be very granular. For example, repeat every 2 months on the 2nd Saturday, or repeat every 2 weeks on Friday and Monday, or repeat every Friday to Monday of the first week of every other month.

To summarize I am creating a very basic task here and I'm sure there is a well know DB schema for this. Lets call it a best practice.

So, what is the best Schema to create those events?

BTW: I use MySQL.


texts:
I have several SQL Server instances. All of them are setup in the same domain, and have been working for years. One of these instances serves as an aggregator for the data of the others, and a sync service has been taking care of this for years. What was not setup before I got here was server maintenance, and centralized reporting from the aggregator.

To tackle this, I setup linked servers on the aggregator pointing to each of the other servers to enable central call to remote maintenance procedures.

The linked servers have mapped credentials using both Windows and SQL logins on both sides for various purposes, but now one of them isn't working when I'm trying to set it up.

There's a SQL login being setup on all servers with identical rights, let's call it "Maint".

The aggregator runs a job, which calls a remote procedure. The job uses "Maint" as the user, and the linked server passes this through to login as "Maint" on the remote server. These users have identical passwords. The job always fails with a login failure for "Maint" with the logs indicating mismatched password.

The only other consideration is the job's step is doing a direct EXEC call to the remote procedure, so "EXEC [RemoteServer].master.dbo.ProcName 'paramValue'", if this matters.

Things I've tried:

Setting the passwords again using a copy/pasted string to the aggregator and the test server multiple times.
Setting the Linked Server Properties to pass through current credentials, and alternately explicitly adding "Maint" to the remote login mappings using the copy/pasted password explicitly
Logging in manually to both servers as "Maint" using the copy/pasted password, and had both succeed.
EDIT: While logged into the aggregator as "Maint", linked server queries such as "SELECT * FROM [RemoteServer].master.sys.databases" run as expected.
EDIT: Taking the contents of the job step out and manually executing them against the aggregator appears to start the procedure on the RemoteServer as expected.

Any ideas on what's causing the linked server functionality when run from a job go sideways like this? Other jobs work just fine.


texts: Database Engine Tuning Advisor - The minimum storage space required exceeds the default

texts:
I created a test database with one table

CREATE TABLE [dbo].[TestTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TimeStamp] [datetime] NOT NULL
) ON [PRIMARY]

I have a sql script that consist of one line.

INSERT INTO TestTable ([TimeStamp]) VALUES(CURRENT_TIMESTAMP);

When I run the script in SSMS it works. But when I use it as a workload in DTA the the Analysis is Aborted with this error

  The minimum storage space required for the selected physical design
  structures exceeds the default storage space selected by Database
  Engine Tuning Advisor. Either keep fewer physical design structures,
  or increase the default storage space to be larger than at least 3
  MB.Use one of the following methods to increase storage space: (1) If
  you are using the graphical user interface, enter the required value
  for Define max. space for recommendations (MB) in the Advanced Options
  of the Tuning Options tabbed page; (2) If you are using dta.exe,
  specify the maximum space value for the -B argument; (3) If you are
  using an XML input file, specify the maximum space value for the
   element under

Using Windows 10 (latest update), SQL Server 2016 SP1.

Why do I get this error?


texts: SQLAnywhere: Switch to choose between WATCOM and T-SQL?

texts: Is there any switch available to set/force a script using either Watcom or T-SQL dialect on SQLAnywehre?


texts: create new MySQL database and user using OS root credentials

texts:
I have root permissions on a server (running Redhat):

[root@myserver /]#

... but not on the MySQL instance that's running on the server:

[root@myserver /]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I'd like to create a new database on the MySQL instance (version 5.6.26), and an account with GRANT ALL PRIVILEGES ... on the new database.

Is this possible (e.g. using mysqladmin) using only the OS root account?


texts:
i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns?

1)

SELECT id
FROM Parent p
WHERE NOT EXISTS
(
   SELECT NULL FROM Child c
   WHERE c.parent_Id = p.id
   AND c.x <> c.y
)

2)

SELECT id
FROM Parent p
WHERE NOT EXISTS
(
   SELECT 1 FROM Child c
   WHERE c.parent_Id = p.id
   AND c.x <> c.y
)

3)

SELECT id
FROM Parent p
WHERE NOT EXISTS
(
   SELECT * FROM Child c
   WHERE c.parent_Id = p.id
   AND c.x <> c.y
)

Option 1 is what i use normally, is there are difference at all or is it optimised to the same?


texts: How can I update the roles for all logins using T-SQL?

texts:
I need to update the roles for all database logins, but without using SQL Server Management Studio. I can update a single user with the following example SQL.

EXECUTE sys.sp_addsrvrolemember
    @loginame = N'User1',
    @rolename = N'dbcreator';

But how I can do this for all users? Is there a way to select the names of all the users and then loop through the list and update them all?


texts:
I am trying to figure if SQL Server has a feature to log when a user fires statements as revoke, delete from, etc.

It would be great to see which user has fired the command, date and time, and which command has been run.

Is there a way to accomplish this in SQL Server 2008?

I have seen this but it is not what I am looking for.


texts: How to transfer files at remote location using sql server job?

texts:
I have a job configured in which it transfers the file at remote location.
My command is running absolutely fine from the command prompt but when I configured it in sql server job it gives log on failure unknown user name or bad password.
I am not able to find out the exact reason that why this file is not being transferred.

If there is an error in my code then it should give the same error from the command prompt.
Can anyone help me understand why I am getting the error?


texts: Access 2003 (SQL Server 2000) migration to SQL Azure

texts:
As my old Windows 2003 RAID controller started throwing errors, I am seriously thinking about switching current Access 2003 (adp/ADO) clients to use a Windows SQL Azure solution, in place of current SQL Server 2000.

Does anybody knows if this is a feasable/painless operation?


texts: Copy my data from a existing db to a new db

texts:
I have a database which has my data's that I have been working for months. Now, I want to delete that db. What concern's me is that the data's that I have might come in handy in the future development. So I want to get a backup for this db, where I can get all the data's and table structures in this db as a backup and then delete it.

I am confused about how to do this.


texts: PostgreSQL roles versus users, grant permissions

texts:
I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating an HTML table with a comparison of commands (for users/databases/command prompt, etc) between the two. After reading an answer here I've notice that role seems to be used as a group. With MySQL I have two users, basically public (DELETE, INSERT, SELECT and UPDATE permissions) and an admin user with a couple additional permissions.

So basically in the Windows 7 command prompt (local development only)...

Is a role a user, group or a loosely used term specific to PostgreSQL?
How do I grant only specific permissions to a specific user, for all tables in a database?
How do I grant all permissions to a specific user, for all tables in a database?
How, if at all, does a role compare to a user when using GRANT or REVOKE for user permissions?


texts:
I'm working on a legacy database using bitmaps for searching. I'm using the bitand function to find bitmaps that match a given bitmask but I'm getting the wrong results: the bitand between two numbers is returning a non-zero value when zero would be expected.

I'm using Oracle 11gR2 and I can reproduce the problem like this:

create table BITAND_TEST (BITMAP binary_double);

-- insert the bitmap of 2^57 in the table
insert into BITAND_TEST values (144115188075855872);

-- bitand(2^57, 2^50) evaluates to 0
select bitand(144115188075855872, 1125899906842624) from dual;

-- but in this statement it returns non-zero
select  *
  from  BITAND_TEST
  where bitand(BITMAP, 1125899906842624) <> 0; -- I'm using 2^50 here

Is this a type casting problem? Which types would you use to store a bitmap?


texts: Modify legacy table schemas that have no primary key

texts:
I've been tasked with updating our database (SQL Server 2008) so that every table in it has a proper primary key defined. Due to various reasons over the years we have somehow built up some legacy tables which have no primary key at all and my colleague wants everything to have a key as then he can use a database comparison tool when deploying changes plus he wants indexes available for everything.

I would agree that having a primary key defined for every table is a must but at this point I'm wondering how much benefit it would provide. Some tables with missing keys are very large, some containing millions of records, and some are simply there to store data; insert often but get the data back not so much. Some also appear to have duplicate data in them, meaning a laborious task of cleaning up the data first, and possibly have no unique key available unless every field in the table was used.

So what I would like to know is:

Is it worth having a key on a table even if it means making a composite key of every table field? Or perhaps an auto-incrementing number would be more beneficial instead?
Is there going to be a performance cost to adding keys to tables which already contain a vast amount of data? For example if a clustered index was going to be added that would mean more space taken up to store the index too; on a table which is basically used for reference data is that worth it?


texts: Mysql: privileges to get access to "mysql" database

texts:
Tell me please - what minimum set of privileges the user should to have to get access to the "mysql" database on the server.

For example to get ability to execute query:

select * from mysql.help_topic

Thanks!


texts: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

texts:
I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777).

When i tried to open the file mysql.sock, its giving me an error saying the file may be corrupt.

Please help me to log on to the mysql server.


texts: Does SQL Server have a Date_Format function?

texts:
All I found is a list of predefined DateFormat that I can choose from, like this

As I remember in MySQL (and PostgeSQL too?) you can define your Date Format:

DATE_FORMAT(now(),'&m_%Y') --for 02_2012 etc.

Does SQL Server have the same thing? I see people have to write a function to do such thing, does it have a built-in one?

EDIT:

I just found the DatePart function. It can take Month as number, but always returns 1 digit, even I use  datePart(MM, getdate())


texts: Large database - too much time spent opening and closing tables

texts:
I have a MySQL server (5.5.31) with 450 databases of some 200 tables each. (Yes, 90,000 tables)

When we're under high load, there's over 100 concurrent queries, of which 70% are either opening or closing tables. They're all only open for small queries.

The machine is heavily swapped, so obviously I can throw some RAM at it.

But before that, is there anything in the config I should be looking at? We've used various tuning tools, but they seem heavily geared to a more normal database setup where you might realistically have all tables open at any one time. Last time we changed our settings to match those recommended by a tuning tool, the entire system came to a halt.

What particular tuning should I be looking to to be able to get maximum performance before we throw RAM at it?


texts: Does two phase locking apply to individual transactions, or the schedule as a whole?

texts:
Problem
I have conflicting perceptions of when a schedule violates 2PL or not.
My first perception is that 2PL only applies to the level of a transaction at a time, since this is what I learned in a Database course:

I understand the above as meaning that locks and unlocks between the same transaction as being allowed to interleave, so long as all locks within one transaction precede all unlocks of the same transaction.  For the example schedule below:
l1(A), l1(B), w1(A), w1(B), u1(A), l2(A), u1(B), r2(A), u2(A)

where:
li(X) means transaction i locks object x
wi(X) means transaction i writes to object x
ri(X) means transaction i reads from object x
ui(X) means transaction i unlocks object x
this schedule would be 2PL because all unlocks of each transaction happen after all locks within the same transaction.
However, I got penalized on a homework for this train of thought on a similar schedule (which is different from the one above) because the locks of the entire schedule as a whole did not occur before all the unlocks of the schedule as a whole.
Question
Does the 2PL rule apply to the schedule as a whole, or to each transaction individualy?


texts: MySQL - How to select rows until condition is met

texts: I have a table that has a numeric field (call it NUM), I want to select rows until the sum of all NUM selected reaches some threshold. Is there an efficient way to do this?


texts:
I initially installed SQL Server 2008 R2 with Windows authentication mode and have successfully changed it to mixed mode (per http://msdn.microsoft.com/en-us/library/ms188670.aspx).

After this I am able to log in to my SQL Server with SQL Server authentication. But from another computer which is on my network, I am not able to connect my server.


texts: We have a Java application that uses a MariaDB database that is dedicated to this application. The application needs to insert 1 million records within 5mins so ideally we would like the database to support a throughput of 5000 tps. Is there a calculation to work out the IOPS required to support a particular throughput? I am trying to understand the relationship between database tps and IOPS.


texts:
I have 3 servers which there is a load-balancer in front of them and sends the requests to these servers randomly.

The script is the same in all of these 3servers, but I need to sync the DB in all of them, or replicate the DB or whatever you suggest, I don't know it's name exactly! There are a lot of things I've heard:

MySQL Replication (master-master)
MySQL Replication (master-slave)
MySQL Cluster
MySQL Replication (Group replication)
Percona Server
MySQL Galera
Any other scripts like this PHP Package or this

what is the difference of these? which one is the best for my situation?

These are my needs and considerations:

I have 3 Geographically distributed servers which I want to sync them.
I want to use InnoDB (Not preferred to use NDB Cluster engine of MySQL Cluster), also I have some in-memory tables, but not that much important to use memory tables, I can switch to InnoDB.
I want something hidden to the application layer, I want to INSERT/SELECT and etc... like before, without changing any line of my codes.
I want something master/master replication or sync or whatever for when each of my master servers goes down no failure happens, So it must have automatic node failure detection in it.

Would you suggest me a solution, for example percona server or anything else?
I think if I choose percona, I must go with Percona XtraDB Cluster, not normal Percona Server!


texts:
I have a large database application on SQL Server 2012 Standard Edition which receives data from hardware in chunks of 500-1000 (100 byte) rows every few seconds through a stored procedure. Would a multi-threaded application with each thread using a database connection, inserting the block of rows work faster than a single thread connection serially calling the stored procedure multiple times? What is the best way to insert the data for the highest performance? Are there clustered index designs which would work better in this scenario?

Thanks, Tony


texts: Weird issue with the order of clauses seeming to matter

texts:
It's not an issue for me I was more just looking for an answer as to WHY it does this.

I have 2 queries both the same with the exception that one has a where clause in a different place (but no so that it should affect the results).

apologies I deal with sensitive information so these images don't really do this justice

Query 1:

Query 1 Results:

Query 2:

Query 2 Results:

I guess you will just have to take my word that both queries are exactly the same (aside form the IN statement moving to the top, underneath the date query, in the first query its at the bottom) but there is no subqueries or anything so I don't understand why both queries don't yield the same results lol

EDIT : Full queries with changed names;

While Re-writing the full query I noticed a hidden OR, god this is embarrassing, the last line is;

AND NOT myTable2.field LIKE 'Other Thing' OR 'Some OtherThing'

so When I added the line to the bottom it obviously became part of the OR LOL!


texts: Are database query optimizers aware of storage performance differences?

texts:
As I understand it, the query optimizer in SQL Server (or any other RDBMS, really) isn't aware of the performance of the storage underneath the database, and will make decisions as if all storage has equal cost. Is that accurate, or is there some knowledge of storage performance that's taken into account?

In a totally contrived example, let's say that my table rows are stored on an SSD drive in my SAN with instantaneous access times, where my indexes are stored on SAS drives that are extremely overloaded, resulting in disk saturation and constant disk queues. When the RDBMS generates the execution plan, is it any more likely to favor a table scan than an index operation (or possibly a skinny index and associated table lookups, as opposed to a covering index, because it's less IO on the SAS disks)?

I suspect the answer is a solid "not a chance is the optimizer that smart or even aware of disk performance", but I just wanted to see if anybody out there knows for sure. I'm using SQL Server, but I'm interested in any database system.


texts: Postgresql Physical Reads and Buffer Reads

texts:
in PostgreSQL how to identify the number of page reads from disk or Shared Buffer
How to find the Hit Ratio for the Shared Buffers
Any queries to support this.


texts:
We have two servers: one for the developer (me) and one for the tester.

While working on the app, I sometimes make little changes in tables's schemas. (Like a new field, or a field type)

I'm trying to find a way to update the TEST's schema from the DEV without altering the TEST data.

I tried to dump the DEV structure only, execute it on the TEST db, and then fill it with the TEST data. But the new schema is replaced with the old TEST schema...

Do you have a way to do that ?
Or will my tester be forced to fill the database everytime we update?

Thanks...


texts:
I plan to change the compatibility level on the databases in an sql server 2016 sp1 instance from 100 to 130. When upgraded from sql 2008 to sql 2016 the compatibility level was changed from 80 to 100.
I also am in the planning stages of installing the SP2 and the latest cumulative update.
What is best practice for changing the compatibility level - before or after the SP2 and cumulative updates are installed?


texts: postgres - pg_dump and pg_restore without roles

texts:
I'm trying to restore a dump without having the appropriate roles on the receiving database.
As mentioned here but also here, you need to have the --no-owner as an option, either in pg_dump or pg_restore or both.
I've used the following command line to create my dump:
"C:\Program Files\PostgreSQL\9.3\bin\pg_dump.exe" --no-owner -Ft --dbname=postgresql://avo******:AV0******?@127.0.0.1:5432/BI_DB > K:\BI\backup\sort\bck_%timestamp%.tar

The restoring line is as follows:
"C:\Program Files\PostgreSQL\9.3\bin\pg_restore.exe" --host localhost --port 5432 --username "postgres" --dbname "BI_TEST2" --no-password  --no-owner --role=postgres --exit-on-error --verbose "D:\D\avo\backup\bck_04042017_1410.tar"

As you can see, both have the --no-owner option but eventually, I have the error below:

What terribly bugs me is the log below:
pg_restore: [programme d'archivage (db)] Erreur pendant le traitement de la TOC (« PROCESSING TOC ») :
pg_restore: [programme d'archivage (db)] Erreur à partir de l'entrée TOC 2633 ; 0 0 ACL adm avo******
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR:  role « avo****** Â» does not exist

Why does it say it needs a role even if the --no-owner was specified?
Did I miss something?
I'm running on Postgres 9.3


texts: I want to Create a brand new slave for my MySql Percona server 5.7

texts:
I have a Master Master Replication setup using mysql Percona Server 5.7, but for some unknown reasons, seems like my passive server is broken "thats not the issue", the replication is bad and the data been bad in the passive for a while.

I have decided to create a new Passive server from the current active.
What is the best way to create a new passive server "from the active one" without or with minimum downtime? given that:

bin logs set on Active and current passive
Relay log is set
log expire days = 3
sync-binlog = 1
binlog-format = ROW
log-slave-updates = ON

please help


texts: I need to confirm if a particular table ever existed in our SQL Server. Is there an existing script or method one can use to list all dropped databases in an SQL Server instance?


texts: When was data last inserted/updated/deleted in a table?

texts:
I have found this solution, but this information is lost everytime the SQL Server service is restarted, or in other words, it is not persistent.

Is there another way I can find out when a table was last updated even if the server has been shut down after the fact?


texts:
I have two tables events and eventdate, where eventdate has a row event that corresponds to the id column in events. (So an event can have multiple dates). `

Now I am trying to get the unique events of the first five eventdates after a certain date. I tried this query:

SELECT ed."event"
    FROM public."eventdate" as ed
    GROUP BY ed."event"
    ORDER BY ed."start_date" ASC
    LIMIT 5
    OFFSET 0

but the following error is thrown:

  ERROR:  column "ed.start_date" must appear in the GROUP BY clause or
  be used in an aggregate function
  LINE 4:         ORDER BY ed."start_date" DESC

To be clear, I expect a list of ids, the list should have at maximum 5 entries, and no duplicates (a duplicate would just be "removed").

Given the following set:

id | event | start_date
1  | 1     | 0
2  | 2     | 0
3  | 1     | 2
4  | 4     | 3
5  | 3     | 4
6  | 1     | 5
7  | 5     | 6
7  | 6     | 6

The result would be:

event
1 (if I'd count the ids here it would be "2")
2
4
3

so basically the invariant would be: summing the count of each group = 5


texts: How to reset the autoincrement field in Mysql data base?


texts: Restore large MySQL dump thorugh EasyPHP

texts: I am trying to upload large files on easyphp but as there is limitation on this software and large databases cannot be uploaded. Is there some other software that allows to import large databases? Please help.


texts: Converting VARCHAR to TIMESTAMP with milleseconds yields "ERROR: date/time field value out of range"

texts:
I have a VARCHAR column with the value '2018-07-05T16:14:27.1427' and I convert it to timestamp with this command:

select last_run,
       to_char(to_timestamp(last_run,'yyyy-MM-dd HH24:mi:ss') - interval '2 hour','yyyy-MM-dd"T"HH24:mi:ss"Z"') as new_last_run
from schedule_tasks;

Until the version 9.6.10 of PostgreSQL, all works good, but now I will migrate to 10.5 and the same command returns ERROR. The same issue happens on PostgreSQL 11.

What I realize is that, on 9.6, the first value of milliseconds when I have 4 digits is added to seconds; for example, 2018-07-05T16:14:27.1427 converted is 2018-07-05T16:14:28.427.

I don't know if this behavior is normal in the newer versions and I will have to handle it, or if this is a bug.


texts:
Does Microsoft SQL Server natively support some sort of replace function using wild cards? I gather that Regular Expressions are not available natively.

I note that there is a PATINDEX function which can be used to bolt together a solution — is there one something which does it more simply?

For example REPLACE(data,'[xy]','q') to replace x or y with q.


texts: How to ensure that SQL Jobs run in the correct order within a minimal maintenance window?

texts:
From this question I learned about a script which creates jobs for backing up databases, checking database integrity, optimizing indexes, and cleaning files.  According to the documentation, this should happen in a particular order:

  Integrity check after index optimization. This is because index rebuilds sometimes can fix database corruption. Full backup after index optimization. Then the following differential backups will be small. Full backup after the integrity check. Then you know that the integrity of the backup is ok. This means first index optimization, then integrity check and finally full backup.

The script created a job per task.  How can I schedule these jobs without having them interrupt each other and ensure that they are in the correct order?


texts: Why temdb and log file size is not reduced by SQL Server

texts:
With a series of question Part1, Part2 and Part3. With the answers and research on internet I came to know that

TemDb is use for holding different values (ex.: Temp variable, Temp table, CTE, Hash result, Hasg aggregation etc.)
Log is use for storing data which comes from buffer and then it is written to the disk and once it is done SQL clears log entry with CHECKPOINT.

In both cases, the result stored is temporary and SQL take care of removing entry from temp database or (any database) log file. So, at any given instance when SQL Server is not running (not running any user defined query or function) then both temdb and log file size should be automatically shrink to minimum. [Assuming that SQL Server has its own process which uses temdb and database log file]
But when I see my log file, it shows me 80 GB and temdb as 45 GB when I am doing nothing on SQL Server.

So, why temdb and (all database) log file size does not reduce when query execution is done?

Also, how SQL Server query executes, I mean its workflow (ex.)

  Query Execution > Buffer Pool > Buffer pool writes entry in Log file/ Buffer pool use temdb > log file writes to disk


texts: I would like to grant create, select, update, drop permissions only for user USER_NAME for a particular schema SCHEMA_NAME. It is possible?


texts: Is it bad to create many mysql temporary tables simultaneously?

texts:
I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better (extremely more efficient and faster than PHP arrays; obviously due to their mechanism).

My tentative solution is to form a temporary table upon each request (i.e. connection) to import and process the data. However, I am not sure if there is a drawback for creating many temporary tables simultaneously?

Can this make problem for the mysql server? Or I can use it as an alternative to PHP arrays in numerous simultaneous requests?


texts: Like '%' search for value withing longblob column in MySql is slow

texts:
MySQL version is 5.7.25

I have table like

CREATE TABLE `applications` (
  `id` varchar(25) NOT NULL,
  `application` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

'application' is approximately 50kb xml text.

the table contains around 120k records.

When I am doing search

Select * from `applications`
where
application like '%<l:name>8085%</l:name>%'

the query fail by 30 minutes timeout.

if I do

Select A.* from (
Select * from `applications`
Limit 0,1000
)A where
A.application like '%<l:name>8085%</l:name>%'

the query finished in 6 seconds.

so by doing pagination I can get the query result in 120*6 = 720 seconds

Interesting that same query with Limit 0,10000 finished in 120 seconds, with Limit 0,100000 failed by 30 minutes timeout

from this SO post I understand that probably longblob query performance is not linear from number of rows.

I finally finished with getting query results by doing pagination and concatenating results.

Select A.id from (
Select * from `applications`
Limit 0,10000
)A where
A.application like '%<l:name>8085%</l:name>%'

Select A.id from (
Select * from `applications`
Limit 10000,20000
)A where
A.application like '%<l:name>8085%</l:name>%'
......

The question is how to get results more convenient way.

The search string varies and is unknown in advance, updating column type to TEXT is not an option right now, since that would require application refactoring that no one wants at this point. The query is part of manual update, so the execution time is not really a problem.

  lennart:

    Beside the point, but even though no one is updating the table, you may miss certain rows. You need to do an order by together with limit , if you want to be guaranteed to examine all rows.

Adding ORDER BY and checking for updates make sense, thank you.


texts:
This is my first time installing Oracle 9i.

I want to create a simple database.

When I go to "Oracle Enterprise Manager Console" and add a database to the tree, I cannot access the database as I don't have a valid username/password.

After a lot of searching, I found that I need to create a new database repository. While I was creating the new repository, it asked for USERNAME, Password, and Service.

username : SYS/sys
Password : change_on_install
service  : <host>:<port>:<SID>
         : SYSDBA

This is not working.  I need proper guidance on how to get out of this.


texts:
I need to read the DB2 DAS Parameters via any method other than DB2 Command "get admin configuration".

I need to read the parameters like EXEC_EXP_TASK or SCHED_ENABLE from file or database.

I cannot find any reference of any files on DB2 where these parameters would be available or any table from where I can fetch these parameters via select query.

Any Pointers?


texts: Capturing queries run by user on SQL Server using extended events

texts:
Using Extended Events on SQL 2016 - how can we capture all queries run by users against a specific database. This helps to us understand what developers/reporting analyst/etl developers are running queries on production instance.

Expected Result:
UserName, TSQL Query Ran, Completion Time


texts: How to upgrade PostgreSQL minor releases without a dump/restore?

texts:
Our production server is running PostgreSQL v8.2.3 on CentOS5.2.  We're planning to upgrade to the latest version in 8.2 series, that is v8.2.20.  Also, in future, we've planned to upgrade to the most recent minor release as soon as possible (as and when released by PostgreSQL).

Initially, we've installed v8.2.3 from source.  PostgreSQL is installed in /usr/local/pgsql/ and data directory is available in /usr/local/pgsql/data

PostgreSQL Versioning policy says:

  Upgrading to a minor release does not
  require a dump and restore; merely
  stop the database server, install the
  updated binaries, and restart the
  server.

Now, without a dump/restore, what are the recommended ways/steps involved in upgrading PostgreSQL to latest version for minor releases?

EDIT: Well, I should have asked my question this way.  Since the doc says install the updated binaries, my question is whether the latest version can be installed as-is. Will my data directory /usr/local/pgsql/data/ be disturbed in any way? Or should this data directory be moved temporarily until the latest installation  is completed.  In addition,  iss there anything, as an admin, I need to take care, like, taking a backup of /usr/local/pgsql/data/postgresql.conf, etc.?


texts:
I have a database named COUNTY. COUNTY has a fragmented table GEO.
I executed OPTIMIZE TABLE GEO against the GEO table, but this returned the  error

  "Table does not support optimize, doing recreate + analyze instead
  OK".

Then I executed  ALTER TABLE GEO ENGINE='InnoDB'; and then went for ANALYZE TABLE GEO. But still it has fragmentation.

At last I dropped it and created it again. Still it has fragmentation.
Can anyone please help me, how can I defragment it?


texts: Preventing unique key constraint violation when pre-existing row has 'expired'

texts:
I have the following table schema;

CREATE TABLE LOCKS (
    lock_uid INTEGER NOT NULL,
    file_uid VARCHAR2(32 char) NOT NULL UNIQUE,
    owner VARCHAR2(255 char) NOT NULL,
    expires DATE NOT NULL,
    PRIMARY KEY (lock_uid),
    FOREIGN KEY (owner) REFERENCES user(username)
);

The table is responsible for recording file locks that users of my application might hold on files in the system. The unique constraint on the file_uid column will prevent more than one lock being held for any file.

I would like some advice on how to allow people to obtain a lock on a file when there is already a lock on the file, but it has expired (ie, expires < sysdate).

My initial thought is to use a trigger on insert so that if there are any file locks that have expired in the table, it will remove them before trying to insert the new lock. This will prevent the duplicate key constraint violation which would occur otherwise.

Can anyone think of a better way? Ideally I would enforce this purely via declarative constraints on the table but it doesn't seem possible.


texts:
Anyone have any suggestions for how to optimize this query?
Running SQL Server 2008..

The object is to look at accounts that were created yesterday, compare their email addresses and phone numbers to existing accounts to identify if it is a duplicate account to one already in the system.

SELECT DISTINCT Customer.CustId
    , Customer.PriceClassID
    , Customer.Name
    , CustomerEDI.S4Future01 as First
    , CustomerEDI.S4Future02 as Last
    , Addr1
    , Addr2
    , City
    , State
    , Zip
    , Country
    , Customer.emailAddr
    , Customer.Phone
    , lastorder
    , CONVERT(varchar(8000), snotetext)
FROM Customer
WHERE
    EMailAddr IN (
        SELECT EmailAddr
        FROM Customer
        WHERE EmailAddr != ''
        GROUP BY EmailAddr
        HAVING COUNT(*) > 1)
        AND EMailAddr IN (
            SELECT EMailAddr
            FROM Customer
            WHERE Crtd_DateTime >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
            )
    OR Phone IN (
        SELECT Phone
        FROM Customer
        WHERE Phone != ''
        GROUP BY Phone
        HAVING COUNT(*) > 1)
        AND Phone IN (
            SELECT Phone
            FROM Customer
            WHERE Crtd_DateTime >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
            )
ORDER BY EMailAddr
    , Phone ASC
    , Name
    , Last
    , First ASC;


texts: Starting SSIS Package through SSIS Catalog

texts:
I've developed an SSIS package to load files but it is behaving very strangely. I can kick off the package running start_execution on the SSISDB only when I log directly onto the server. Kicking it off from my PC will throw a warning saying the For Each File enumerator is empty. My account and the SQL SQL service account both have access to the file share where the SSIS package is pointing. Anyone else encountered a similar issue?

UPDATE:

The SSIS package will execute successfully if executed remotely through a SQL agent job.


texts: How to deal with linked server in SQL queries

texts:
I am creating a new database which will connect to several databases via linked servers. The problem I am trying to solve is that I have development, test and production environment with different linked server names and I also want to prevent future rewriting of queries if some linked server will move or renamed.

What is the best practice?

My Idea is to make a constant table which will hold linked server names and create function with select for every "remote" table which will call the linked server via dynamic SQL. But the problem is that i don't want to be rewriting all the columns from remote tables to each functions.

Variant 1

Function example (but i want to avoid table definitions in return statement)

CREATE FUNCTION dbo.fnDatabase1_GetTable1()
RETURNS @ret TABLE (col1 INT)
AS
BEGIN

DECLARE @LinkedServer NVARCHAR(255) = (SELECT Value FROM dbo.Constant WHERE Name = 'LinkedServer1')
DECLARE @Query NVARCHAR(MAX) = 'INSERT INTO @ret SELECT * FROM ' + @LinkedServer + '.Database1.dbo.Table1'
EXEC(@Query)

END

Variant 2

I was also thinking about openrowset, but the linked server argument cant be passed as a parameter :/

SELECT *
FROM OPENQUERY ([Server1\Instance1], 'EXECUTE [Database1].[dbo].[spDatabase1_GetTable1]')

Variant 3

The other elegant way could be with the view with cross apply statement, for example but I am not able to get linked server as a parameter to the FROM statement and view does not allow dynamic SQL:

CREATE VIEW dbo.vwDatabase1_Table1
SELECT Constants.LinkedServer
FROM Table1
CROSS APPLY (
    SELECT Value as LinkedServer FROM dbo.Constant WHERE Name = 'LinkedServer1'
) Constants

so it seems for me that I am trapped, what is the way you do it?

PS: Can the SQL Server project in Visual Studio help me with this problem ?


texts: Sum of points and rank according to points

texts:
I am creating a game where I am calculating the user points on the basis of user activities. Everything is working fine - I got the sum of points and on the basis of points I calculated the rank. I have two columns for points calculation: pointsup (contains up points) and pointsdown (contains down points)

select sum(pointsup - pointsdown) as points
from table1 t1
join table2 t2
    on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'
group by t1.id
order by points desc

Everything is fine, but the problem comes when I have to rank the users with same points. In this case, I have to consider the registration date: who registers first will come up first on the ranking. The registration date is in another table table3. I have tried to join this third table but the sum is behaving oddly, showing wrong sum calculation.

Can anyone help please?


texts: Restore single database (or table-space) using PITR with PostgreSQL?

texts: Is there any possibility restore a single database with WAL files.


texts:
Assume I have a table like this:

+----+-----------+-----------+
| id | file_name | file_type |
+----+-----------+-----------+
|  1 | file1     | 1         |
|  2 | file2     | 1         |
|  3 | file3     | 1         |
|  4 | file4     | 2         |
|  5 | file5     | 2         |
|  6 | file6     | 2         |
|  7 | file7     | 3         |
|  8 | file8     | 3         |
|  9 | file9     | 3         |
+----+-----------+-----------+

I'm trying to get 2 rows for each file_type, so I'm following this hints:

https://blog.sqlauthority.com/2014/03/08/mysql-generating-row-number-for-each-row-using-variable/
http://www.sqlines.com/mysql/how-to/get_top_n_each_group

I'm trying the following query:

SELECT file_name, file_type,
  @type_number := IF(@current_type = file_type, @type_number + 1, 1) AS type_number,
  @current_type := file_type
FROM test
ORDER BY file_type

But I'm getting the following results:

+-----------+-----------+-------------+----------------------------+
| file_name | file_type | type_number | @current_type := file_type |
+-----------+-----------+-------------+----------------------------+
| file1     | 1         |           1 | 1                          |
| file2     | 1         |           1 | 1                          |
| file3     | 1         |           1 | 1                          |
| file4     | 2         |           1 | 2                          |
| file5     | 2         |           1 | 2                          |
| file6     | 2         |           1 | 2                          |
| file7     | 3         |           1 | 3                          |
| file8     | 3         |           1 | 3                          |
| file9     | 3         |           1 | 3                          |
+-----------+-----------+-------------+----------------------------+

As you can see the type_number column should have values from 1 to 3, but it is 1 on every rows.

Where I am wrong?


texts: SSMA error "Assembly 'SSMA4OracleSQLServerExtensions.NET' was not found in the SQL catalog"

texts:
everyone.
I am attempting to do a simple test migration of a table with 4 columns and 1 row from Oracle to SQL using SSMA.
Brand new VM with Windows Server 2019 Datacenter and brand new SQL 2019 Dev ver 15.0.4083.2.
Installed SSMA for Oracle 8.18.0 and SSMA for Oracle ExtensionPack_8.18.0.
When I do sync with database after converting schemas I get this error:
Errors: CREATE or ALTER ASSEMBLY for assembly 'SSMA4OracleSQLServerCollections.NET' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.
I made sure that 'clr strict security' option of sp_configure is set to 0 but I still get the same error.
I see 3 assemblies in the GUI of SSMA:
Microsoft.SqlServer.Types
SSMA4OracleSQLServerCollections.NET
SSMA4OracleSQLServerCollections.NET
I checked and all the files are present in "C:\Program Files\Microsoft SQL Server Migration Assistant for Oracle\bin".
Tried to reinstall SSMA and got the same error.
Tried it on a different server – same error.
Does anyone know what am I missing?
Thank you.


texts: Increase timeout for cassandra native transport threads?

texts:
From the Cassandra documentation here

  native_transport_max_threads

  The maximum number of thread handling requests. Similar to rpc_max_threads, but this property differs as follows:

  The default for native_transport_max_threads is 128; the default for rpc_max_threads is unlimited.
  There is no corresponding native_transport_min_threads.
  Cassandra stops idle native transport threads after 30 seconds.

I wonder if there's any way to increase the timeout before cassandra kills idle threads? I can't find anything in the docs, but someone might know of an un(der)documented setting?


texts: Adding an ID column based on two other columns

texts:
I have a query which returns output as follows:

 [Location] [Reference] [Year] [Int1] [Int2]
    England           1   2015     13    201
    England           1   2015     12     57
    England           1   2015      4     14
    England           2   2015     18     29
    England           2   2015     18     29
    England           1   2016     32     67
    England           1   2016     43     11
    England           2   2016     10     56

Where the int1 and int2 columns represent some calculated data regarding the locations.
I am trying to represent the data in a report and realized I could use an ID column of the form:

 [ID][Location] [Reference] [Year] [Int1] [Int2]
    1   England           1   2015     13    201
    2   England           1   2015     12     57
    3   England           1   2015      4     14
    1   England           2   2015     18     29
    2   England           2   2015     33     12
    1   England           1   2016     32     67
    2   England           1   2016     43     11
    1   England           2   2016     10     56

Which is incremented for every row which has the same Reference and Year values.

I have so far tried to achieve this using the Ranking functions as in the example below but with little luck.

SELECT Location, Reference, Year, Int1, Int2,
DENSE_RANK() OVER (PARTITION BY Year ORDER BY Reference ASC) AS rnk
FROM Value

Returns

 [Location] [Reference] [Year] [Int1] [Int2][rnk]
    England           1   2015     13    201    1
    England           1   2015     12     57    1
    England           1   2015      4     14    1
    England           2   2015     18     29    2
    England           2   2015     33     12    2
    England           1   2016     32     67    1
    England           1   2016     43     11    1
    England           2   2016     10     56    2

So my question would be is there a way to use the rank functions, Rank, DENSE_RANK etc in order to achieve this? Failing that what might my best option be?

It has been suggested that a CURSOR may help me order the data in this way but after reading up on its use I would like to determine whether a set based approach is available.

I tried to create an SQLFiddle for this question but it seems I can't create a SQL Server version at the moment. I've created the this one using MySQL 5.6 so you at least have access to the schema. Apologies.


texts: How are the system health extended events files rolling over?

texts:
I see the default system health session events within SQL Server are 5 MB each with 4 files in total.

We are trying to fetch data from the system health in historical events but for some critical production servers they just keep rolling over very fast. For some they stay there even for weeks

How is the mechanism working in SQL Server of rolling over the files and is there a way I can check when and how this is happening?

Edit@ Per Answer

Event is not restarted
Server is also not restarted and it happens per scheduled once a month

Therefore i am thinking there could be possibly lot of unwanted events being captured in system health. How can i find them and filter them out in this system health ?

Also, should we be increasing file sizes say 200 MB 4 files or just keep small size files like 10 MB 80 files? Which one is better?

Update@ I've already marked this an answer, but while checking another thing that popped out:

The files are not filling in sequential order ; say i increased to 10 files to 10 MB each. we see 1-6 files couple of days old seems all filled with 10 MB, while file 7 and 8 for the same day is 2 MB and file 9 and 10 filled up completely 10 MB. I guess this is expected, just curious why it's like this?


texts: database functional dependency candidate keys

texts:
How do you know when you found all the candidate keys in a functional dependency?

I know how i would go about finding a candidate key, just don't know how to tell if there are more.

Thanks.


texts: how to optimize grouped select from an already grouped select query

texts:
I already had a specific query to get a number of shops, but I couldn't sum the cost field, I kept on getting an error on the sum function, but it worked when I did this;

select tab.shop_id, tab.shop_name, tab.payment_schedule, SUM(tab.num_clicks)
as num_clicks, SUM(tab.cost) as cost
from (select c.shop_id as shop_id, s.shop_name as shop_name,
      s.pay_type as payment_schedule,
      count(c.id) as num_clicks,
      count(c.id)*click_value as cost
      FROM Clicktracker c
        INNER JOIN Shop AS s ON c.shop_id=s.shop_id
      where c.domain='mobi.mysite.org'
      GROUP BY s.shop_id, click_value) as tab
GROUP BY tab.shop_id;

I'm not really good at all at sql; Is there a better way to write this query?

descr of shop table;

shop_id     int(10)
shop_name   varchar(20)
pay_type    float(11,2)

description of the clicktracker table;

id              int(10)
shop_id         int(10)
product_name    varchar(384)
shop_name       varchar(384)
price           float(11,2)
domain          varchar(255)
click_value     float(11,2)

to say the least, that is the desc of the tables (took me a lot of editting)


texts: Is there any way to detect and get information on a push-subscription on subscriber's side?

texts:
I've got a database in Azure SQL. There may be a push-subscription from on-premises database. I have no access to on-premises servers where distribution/publication/subscription is created and configured.

There are many many ways to get information about subscription from dictributor or publisher's side, they are easily googled. At the same time, I did not manage to find a way to detect subscription existence/health at the subscriber's side.

My current way of detection is indirect. Namely, I know the table where new records appear often and just count the number of the records and then again a minute after. This does not help in the night, when no activity in the database, and this is inconvenient due to necessity to wait a minute or more.

What I need is a script that clearly and directly shows me, if the subscription exists, and probably/if possible some additional information:
 * if it is active (does push-side work properly at the moment)
 * which articles are configured, etc


texts: How to find which tables and views a user has access to?

texts:
We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID.  Over the years we have granted back tables and views one at a time based on user need.

We had to do it this way because the vendor provides read/write access to the public role out of the box, so we had to create a role for this user, remove all access and grant back only what they needed.

Today we are going to create a replicated database containing only these tables and views so the user can run reporting on without degrading the production system.  The issue is I'm not sure what tables and views this user has access to, because many people have granted access one at a time over the years.

Is there a query to check this users access?

PS Im an apps developer so please feel free to explain this as elementary as needed.

EDIT - How the role and access was originally created

------------------------------
-- CREATE ROLE db_finrep_deny
------------------------------

DECLARE @RoleName sysname
set @RoleName = N'db_finrep_deny'
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
    DECLARE @RoleMemberName sysname
    DECLARE Member_Cursor CURSOR FOR
    select [name]
    from sys.database_principals
    where principal_id in (
        select member_principal_id
        from sys.database_role_members
        where role_principal_id in (
            select principal_id
            FROM sys.database_principals where [name] = @RoleName  AND type = 'R' ))

    OPEN Member_Cursor;

    FETCH NEXT FROM Member_Cursor
    into @RoleMemberName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName

        FETCH NEXT FROM Member_Cursor
        into @RoleMemberName
    END;

    CLOSE Member_Cursor;
    DEALLOCATE Member_Cursor;
End
GO
-- dropping the role itself
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_finrep_deny' AND type = 'R')
DROP ROLE [db_finrep_deny]
GO

------------------------------
-- Grant access (read/write/select/upd) to only select tables
------------------------------
CREATE ROLE [db_finrep_deny]
AUTHORIZATION [dbo]
GO
exec sp_addrolemember 'db_finrep_deny', 'finrep'
GO

-- run the dynamic sql generated by this statement
select  'DENY SELECT, INSERT, UPDATE, DELETE ON '
        + ss.name
        + '.'
        + st.name
        + ' TO db_finrep_deny;'
from sys.tables st
inner join sys.schemas ss on st.schema_id=ss.schema_id
where st.name not in ('TABLE1','TABLE2','TABLE3','TABLE4')
order by ss.name, st.name;

grant select on dbo.vwFINMASTER to db_finrep_deny;

Aaron - this is what I get when I run your query, it shows only views, but no tables

(No column name)    name    permission_name
dbo     dtproperties    REFERENCES
dbo     vwFINMASTER     SELECT
dbo     vwLEDGERVIEW    SELECT


texts: How to apply a patch to pgAdmin III 1.18.1 on Windows?

texts:
A follow-up question to this one: How to apply a patch in PostgreSQL on Windows?

There is a lot of .patch files distributed everywhere, but no hints how to apply them (at least no hints that wouldn't presume that I am on Linux). Is there any option to install the patch through some GUI? Or do I need to use UNIX-like command line? Or to recompile pgAdmin in Visual Studio? If I need some scripting, point me to some manual, please.

I have pgAdmin III 1.18.1 and PostgreSQL 9.3.4 on Windows XP (32 bit).

EDIT: I'm trying to recompile pgAdmin in Visual Studio (now without the patch - I don't know how to integrate it to the solution, I just try to learn how to recompile it). I reduced the errors raised while trying to rebuild it from 101 to 14, but I have no idea how to handle the rest. So I would appreciate either some tutorial how to recompile pgAdmin in Visual Studio and handle the errors, or any other way how to do it. Or that it is not possible (if so, please back it with some source) and installing Linux and learning with its command line is the only way to apply the patch.

EDIT2: I learnt how to understand the patch code (from a manual here) and apply it manually on the file in the sourcecode. Still it looks as though that compilation is necessary, and there are tens of wxwidgets related errors (it just seemed for a while there are just 14 of them).


texts: Mysqldump, exclude data from tables by query

texts:
It's possible to ignore tables in mysqldump using:

mysqldump -u username -p database --ignore-table=database.table1 > database.sql

Is it possible to ignore certain records in tables while dumping?


texts:
I have two tables in my database like these:

CREATE TABLE `main` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sub` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `main_id` int(11) NOT NULL DEFAULT '0',
  `count` int(11) NOT NULL DEFAULT '0',
  `done` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY (`main_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have queries like these:

INSERT INTO `main` (`id`, `type`, `status`) VALUES (NULL, '1', '1');
INSERT INTO `sub` (`id`, `main_id`, `count`, `done`) VALUES (NULL, '1', '2', '0');
update sub set done = done + 2  where main_id =1

After these three , my app select the record using a query like this:

SELECT * FROM `sub`  where main_id =1

My problem is that sometimes (not always) the select query returns the data with delay. For example it returns the data after 10 minutes while thousands of other records are updated with no delay.

P.S: There is no caching layer on the app.
P.S: There is no caching in MySql.


texts: Post installation of SSIS for existing SQL Server instance with different build versions

texts:
I would like to install SSIS into existing SQL Server 2014 Fail-over cluster instance with latest cumulative update 7 which was applied. I know how to do it but I want to know if consistency of versions between SSIS (from installation disc) and SQL Server 2014 (already updated) will be kept at the same latest server version (latest version is 12.0.2560).

In to detail, I mean I have latest CU7 update applied but I want to install SSIS from image where is first RTM version release 12.0.2000 but currently I have 12.0.2560 CU7 Server version which is newer. Will be there conflict of versions or SSIS has no impact which version of SQL Server is actually installed? I can also download image of SQL Server 2014 with SP1, but build version is higher than actual CU7 (SP1 is 12.0.4100 build version and CU7 with fixes 12.0.2560 build version). Reason why I don't want to install SP1 even when build version is higher has less fixed bugs than actual CU7.

So in result does post-installation of SSIS downgrade SQL Server version to which installation media contains or it will be self-upgraded to actual server installed version?


texts:
I setup an SQL profiler to run, but I am not seeing any values for updates/inserts.  Is there a way to see them?

All I see is stuff like this:

insert into some_tbl(id, col1, col2, col3) values ( @P0 , @P1 , @P2 )

I would love to actually see the values being inserted rather than just a generic variable, but can't seem to figure it out?


texts: mysqldump takes forever to dump tables from a remote server

texts:
I'm trying to pull the database schema from a remote server, but mysqldump is too slow to output (125+) tables' structures.

If I enter the said server and run mysqldump --compact --compress --no-data --add-drop-database --databases --user=myuser --password=123 mydb > dump.sql it's almost instant.
If I run the same thing over ssh it takes 2.5 secs (ssh myserver mysqldump [...] > dump.sql)
However, if I use the --host so I can use the database credentials only, instead of the SSH credentials, it takes 2min40s to run!

What makes mysqldump so slow when running over its own protocol? It dumps each table (all at once) every few seconds, then waits, dumps the next one...
Is there anything I could do to make things faster?

EDIT: I wrote about that db adventure, to show the final solutions.


texts:
I have a database which was 11TB in size. I recently truncated over 5TB of data from this database.

(I am fully familiar with all the reasons why you would not normally shrink a database please)

I'm curious about what the DBCC SHRINKFILE command is actually doing because when I run the command to shrink a file which is about 650000MB in size with 45% free space it doesn't seem to actually move any pages around.

The code in question is:

USE [CAF]
GO
DBCC SHRINKFILE (N'FILENAME' , 650239)
GO

When I monitor the performance metrics of the server on executing DBCC SHRINKFILE I see the following

% Active time of the disk immediately jumps to 100%.
The sqlservr.exe and system processes both start reading at a continuous 2MB/s
each (sometimes peaking at 5MB/s).
The disk queue length jumps to exactly 1
Disk iops jump to about 250 (note the disks in tests can achieve 6000iops)
CPU remains idle

Even if I tell DBCC SHRINKFILE to shrink the file by a single MB and let it run for 30 minutes, it does not complete.

Everything I've read suggests that DBCC SHRINKFILE should take pages from the end of the file and move them to free space near the beginning but even at the worst performance it should take no more than a few minutes to move 1MB worth of pages

What is DBCC SHRINKFILE actually doing, it just doesn't make sense to me?


texts: I have SQL Server 2012 machine housing a database running an off the shelf application with a users table. This users table contains PII enterable fields. Per requirement none of our databases can house PII. I have a job set up to scrub any rows not null that runs daily. I am wondering if there is a way to set up a job or trigger that can alert administrators anytime data is entered into one of these PII rows and perhaps have the scrub job run automatically anytime one of the PII fields is updated with anything other than NULL?


texts:
I'm a developer and sometime I face with restoring a DB with a different collation than the server.
Our application uses the tempdb too and as result some joins fail due of the different collation.
So my question is : does exist a tool or a standard approach to convert the DB without doing the entire process manually?
Often the collation is very similar changes by a different default on a newer edition of Sql Server such as SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CP1_CI_AS, in these cases the process of export/import data should not be necessary.

Thanks


texts: Return all rows from table1 and corresponding rows from table2 even if null

texts:
I'm new into SQL so please bear with me. The question itself is confusing as I don't really know how to ask it. Thing is, I have two tables, table1 lists all printers. Table2 lists their page counters, with year/month parameter. What I want to show is ALL printers with their respective page counters filtered by year/month, and NULL if there is no page counter for that specific printer on that specific time frame. To try and clarify a little, this is what I have:

Table1:
id
model
location

Table2:
id
printerid
counter
year
month

SELECT
   tb1.id,
   tb1.model,
   tb1.location,
   tb2.counter
FROM
   table1 tb1 LEFT JOIN table2 tb2 ON tb1.id = tb2.printerid
WHERE
   tb2.year = 2017 AND tb2.month = 1

I know the WHERE clause is messing things up, and this query only returns printers that have counters on that year/month parameter. If I remove the WHERE from the query I get duplicates (if a printer has (and will have) counters on different year/months).What I want is something like this:

Printer      Pages      Year      Month
printer1     100        2017      1
printer2     200        2017      1
printer3     null       null      null

Instead I'm getting this (without WHERE clause):

Printer      Pages      Year      Month
printer1     100        2017      1
printer2     200        2017      1
printer3     null       null      null
printer1     150        2017      2

Or this (with WHERE clause):

Printer      Pages      Year      Month
printer1     100        2017      1
printer2     200        2017      1

Any help would be much appreciated


texts: DB server VM's CPU configuration - 1x2Ghz or 2x1Ghz - which is better

texts:
We want to set up DB server in cloud hosting for MS SQL Server Express edition - OLTP workload for "just" 6 concurent users - mostly short and small transactions.

Hosting company is charging each 1Ghz of CPU. Concerning CPU configuration, would you go 1 x 2Ghz or 2 x 1Ghz (logical cores in VM)?

Similar question is answered here: CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server?

My question is a bit different / specific in a way I'm choosing between just one core with higher speed or two with lower speed. So the scale is far different.


texts:
This is rather a generic question.

I've always understood that SQL Server likes RAM, the more the better.

If for example I had a SQL Server with just a single 2GB database and 8GB RAM, would there be any performance benefit to upgrading to 16GB RAM?

I guess what I'm getting at is that if SQL Server can fit the entire database into the RAM it currently has, would more RAM make any difference?

Thanks


texts: How do I use mariabackup to do point in time restores?

texts:
So I've read about the basic way that I can backup my mariadb using mariadb backup...

What I don't understand is how to make it point in time......

So I want to restore using mariadbackup full and incrementals and then what do I use to get to the point in time of the crash....

SO how do I incorporate the binary logs? or redo logs?

This is where I'm lost.


texts: How to setup SSL in SQL Server 2012 with own certificate?

texts:
Environment: Windows 7 Professional x64 in a domain, running SQL Server 2012. I have local admin rights, so messing up the system is easy. My SQL Server instances are SQLEXPRESS and MSSQLSERVER. FQDN should be for example my-pc.mydomain.local.

Problem:

I don't know the right way to setup SSL on this SQL Server. I actually only want to setup SSL with a certificate created of my own.

Can anybody help me out here? Most of the documentation online only describes the way of going through a CA and have different knowleadge as a prerequirement.


texts: Constraining foreign keys to two parent tables

texts: For the model above I want to make it so a robot must have a power supply and motor with matching voltages.  Is there a way to constrain the robot entity so it cannot have a power supply and motor that don't match voltages? Or is there a better way to model this so I don't need a constraint?


texts: On an idle instance, why does setting max memory to 256mb cause %CPU to jump

texts:
Five 2012 instances on a 2-node cluster using AVGs. Two of them doing nothing yet, no user databases or users.  Customer insists on specific max mem settings while waiting for additional memory approval.  We lowered max mem from 1gb to 256mb on both and each instances CPU usage jumped to 20% and stayed there. Raising it to 512mb brought it back down to basically nothing. Regardless of mem setting each instances memory usage stayed right around 200mb.

Any ideas?  I'm still doing research for the technical answer they are requesting.


texts: Using a real certificate with rethinkdb cert chain bundle

texts:
I know the rethinkdb guide uses a self signed cert as an example. If I wanted to use a real certificate that I purchased, how can I add the bundle to the server conf? I add the certificate I purchased and key to the config:

driver-tls-key=/etc/ssl/star.cert.key
driver-tls-cert=/etc/ssl/star.cert.crt

Openssl s_client gives me the following

Verify return code: 21 (unable to verify the first certificate)

With this as the certificate chain:

depth=0 OU = Domain Control Validated, OU = PositiveSSL Wildcard, CN = *.s0nr.co
verify error:num=20:unable to get local issuer certificate
verify return:1
depth=0 OU = Domain Control Validated, OU = PositiveSSL Wildcard, CN = *.s0nr.co
verify error:num=21:unable to verify the first certificate
verify return:1
---
Certificate chain
 0 s:/OU=Domain Control Validated/OU=PositiveSSL Wildcard/CN=*.s0nr.co
   i:/C=GB/ST=Greater Manchester/L=Salford/O=COMODO CA Limited/CN=COMODO RSA Domain Validation Secure Server CA

How can I use this cert properly?


texts: Database connections and related issues?

texts:
If my tomcat is configured to accept 100 incoming requests concurrently, how many connections should be present in DB connection pool ? In other words, if there a known optimal ratio of number of connections / number of incoming requests ?
Is it possible that application servers have made more requests / transactions per second than the db is configured to handle ? If yes, then what happens ? Are transactions queued ? dropped ?


texts: Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

texts:
I have created the table donor in the schema reference as per:

CREATE TABLE reference.donor (
    donor_code smallint PRIMARY KEY,
    donor_name character varying NOT NULL,
    donor_type smallint REFERENCES reference.donor_type (type_id),
    alpha_2_code char(2) REFERENCES reference.iso_3166_1 (alpha_2_code)
);

I have populated the table as per:

INSERT INTO reference.donor (donor_code, donor_name, donor_type, alpha_2_code)
SELECT donor_code, donor_name, donor_type, alpha_2_code
FROM reference.donor_template;

When I run:

\dt+ reference.*

inside psql I see the reference.donor table:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description
-----------+----------------+-------+----------+-------+-------------
 reference | donor          | table | postgres | 16 kB |
 reference | donor_template | table | postgres | 16 kB |
 reference | donor_type     | table | postgres | 16 kB |
 reference | iso_3166_1     | table | postgres | 48 kB |
(4 rows)

But when I run \dt+ donor* (or \dt(+)) I don't see the reference.donor table:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description
-----------+----------------+-------+----------+-------+-------------
 oecd_cl   | donor          | table | postgres | 16 kB |
 reference | donor_template | table | postgres | 16 kB |
 reference | donor_type     | table | postgres | 16 kB |
(3 rows)

Why can I only see the reference.donor table if I run \dt+ reference.* or \dt+ *.donor?
I was expecting \dt (or \dt+) to display it, but it does not.

My search_path includes the schema reference & the user postgres has all permissions on the schema reference and all tables in the schema as per:

GRANT ALL ON ALL TABLES IN SCHEMA reference TO postgres;

Just to clarify, I have two donor tables, but they are in two different schemas i.e., oecd.donor & reference.donor. (I can see oecd.donor without any problems when I use \dt(+) inside psql).


texts: Adding a varchar to a datetime column and dealing with nulls

texts:
I have two columns in my table a varchar(8) titled Trans_Time which holds data in an XX:XX:XX time format and a DateTime column named Trand_Date which holds just a the date component in XX-XX-XXXX format.

I am trying to combine the two columns to get a full date and time (i.e. if Trans_Time is 08:10:14 and Trand_Date is 04-04-2007 the output should be 04-04-2007 08:10:14).

My problem is that some times the Trans_Time is null and this produces an output of null.

Is there some way I can treat null as 00:00:00?


texts: using statistics in order to decide whether or not to add a filtered index

texts:
I have many queries on table dbo.tblSAPDespatchItem, some of them
have the following condition in the where clause:

WHERE   b.lngDespatchItemStatusID = 2
AND     b.decvalue > 0

for example

Partial list of a query:

            ,despatchDate= CAST(CAST( a.dtmUpdated AS DATE ) AS DATETIME)
            ,strbxorderNo=A.strBxOrderNo

    FROM    dbo.tblSAPDespatch a
    INNER JOIN dbo.tblSAPDespatchItem b on a.lngDespatchID = b.lngDespatchID
    INNER JOIN [dbo].[tblBorder] C on C.strBxOrderNo = A.strBxOrderNo

    INNER JOIN dbo.tblBAccountParticipantEmail D on C.lngOrderedByParticipantID = D.lngParticipantID

    INNER JOIN dbo.tblBAccountParticipant E on d.lngParticipantID = e.lngParticipantID
    INNER JOIN dbo.vwtblItem F on f.strItemNo = b.strItemNo
    INNER JOIN @DIS DIS ON A.lngDespatchStatusID = DIS.lngDespatchStatusID
    INNER JOIN dbo.tblBMarket H on H.sintMarketID = c.sintMarketID

    WHERE   b.lngDespatchItemStatusID = 2
    AND     f.strStyle not in ( 'VOUCH', 'GG001', 'GG002' )
    AND     d.strEmail = @email
    AND     a.sintMarket = @marketId
    AND     a.[dtmUpdated] BETWEEN @BeginDt AND @EndDate
    AND     b.decvalue > 0

Can I use statistics to assess the benefit of adding a filtered index?

Using the scripts from this question here:

How to script statistics in Sql Server? (using T-SQL)

I get all stats on that table:

CREATE STATISTICS [_WA_Sys_00000006_33B97E11] ON [dbo].[tblSAPDespatchItem]( [strItemNo])
CREATE STATISTICS [_WA_Sys_00000007_33B97E11] ON [dbo].[tblSAPDespatchItem]( [strLocation])
CREATE STATISTICS [_WA_Sys_00000008_33B97E11] ON [dbo].[tblSAPDespatchItem]( [sintOrderItemSeqNo])
CREATE STATISTICS [_WA_Sys_0000000E_33B97E11] ON [dbo].[tblSAPDespatchItem]( [lngUserID])
CREATE STATISTICS [_WA_Sys_0000000F_33B97E11] ON [dbo].[tblSAPDespatchItem]( [dtmUpdated])
CREATE STATISTICS [_WA_Sys_00000010_33B97E11] ON [dbo].[tblSAPDespatchItem]( [decValue])
CREATE STATISTICS [_WA_Sys_00000011_33B97E11] ON [dbo].[tblSAPDespatchItem]( [sintOrderSeqNo])

then running the following command:

dbcc show_statistics ('tblSAPDespatchItem','_WA_Sys_00000010_33B97E11')

I get the following picture:

from the picture:

SELECT CONVERT(BIGINT,STR(CAST('1.9777E+07' AS REAL)))
SELECT CONVERT(BIGINT,STR(CAST('2.199613E+07' AS REAL)))

This gives me:

select 21996130 + 19777000
-- 41,773,130

Basically nearly 42 million rows I don't need to deal with

and when comparing to the actual values in the table, they approximately match:

sp_count 'tblSAPDespatchItem'
-- 175,918,697

select count(*)
from tblSAPDespatchItem with (nolock)
where decvalue <= 0
--19,396,110

select count(*)
from tblSAPDespatchItem with (nolock)
where decvalue <= 0
or decvalue is null
-- 43,238,039


texts: Postgres how to get column data types for a view

texts: I want to get meta information of a view in postgres. Specifically the column data types. Any suggestion for how to do that?


texts: Moving to version 2016 from 2008 and Breaking Change Alert from Upgrade Advisor

texts:
DBMS: SQL Server

Upgrading from version 2008 to 2016.

I have this part of the code that is being flagged by the Upgrade adviser as Breaking Change.

  Error:  uses a constant expression which are not allowed in the ORDER BY clause in database compatibility level 90 or later

What am I missing here. To my understanding, I should not use constants here but like variables. Any help would be appreciated.

    DECLARE ForeignColumn1 CURSOR LOCAL STATIC FOR
    SELECT
      a.COLUMN_NAME
    , ROW_NUMBER() OVER (ORDER BY a.COLUMN_NAME) AS RowNumber
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
    WHERE 1=1
    AND a.CONSTRAINT_NAME = @Constraint
    AND a.TABLE_NAME IS NOT NULL
    AND a.COLUMN_NAME NOT LIKE
    CASE
        WHEN @SkipSite > 0 THEN
             @CurrSite
        ELSE
             ''
        END
    ORDER BY RowNumber


texts: MySQL query - Passing variable into subquery's derived table

texts:
Is there a way to pass a variable into a subquery, and use that variable in a derived table in the subquery?
I am trying identify "active" organizations, and there are multiple actions that constitute "activity". One is creating an invoice, represented here (others not shown here for the sake of readability). However, because there are more types of actions, I cannot do the simpler approach of just querying the Invoice table, like in thisWorks column.
Therefore, I am trying to create a derived table, where multiple sources can be queried, but also filtering the @start and @end timestamps, as seen in the thisDoesntWork column.
Here is a simplified version of what the query looks like:
select
    @row:=@row+1 as row,
    @sixPrior:=date_sub(date_sub(date_sub(curdate(), interval dayofweek(curdate())-2 day), interval @row-1 week), interval 6 month) as sixMonthsPrior,
    @start:=date_sub(date_sub(curdate(), interval dayofweek(curdate())-2 day), interval @row-1 week) as start,
    @end:=date_sub(date_add(curdate(), interval dayofweek(curdate())+5 day), interval @row-1 week) as end,
    @sixPrior as sixCheck,
    @start as startCheck,
    @end as endCheck,
    ( select count(distinct(organizationId))
      from Invoice
      where createdTime between @start and @end
    ) as thisWorks,
    (
        select count(distinct(organizationId))
        from (
            select organizationId
            from Invoice
            where createdTime between @start and @end
        ) d
    ) as thisDoesntWork
from Organization o
join (select @row:=0) r
limit 10;

In the example above, thisDoesntWork, has the same value for every row (this first value). I assume it's got something to do with @start and @end never getting updated values.
Is there any way to filter a derived table, with values that change each row, from an outer table?
I'm also open to alternative approaches, if derived table restrictions will never allow it to work.
I'm also using MySql 5.7, so LATERAL JOIN is not a possibility


texts:
Given the table PerformerTours with the columns:
FirstName, LastName, Date_1, City_1, State_1, Date_2, City_2, State_2, Date_3, City_3, State_3, Date_4, City_4, State_4

and the data
Donna, Summers, 01/01/20, Atlanta, Georgia
James, Taylor, 02/04/20, Miami, Florida, 04/11/20, Seattle, Washington
Bob, Dylan, 01/11/20, Buffalo, New York, 09/24/20, Nashville, Tennessee, 10/22/20, Chicago, Illinois, 12/12/20, Denver, Colorado
Kenny, Rankin, 06/30/20, Las Vegas, Nevada, 07/04/20, Boston, Massachusetts, 08/18/20, Cincinnati, Ohio

how can I unpivot the data to be as defined below using just Oracle SQL:
Donna, Summers, 01/01/20, Atlanta, Georgia
James, Taylor, 02/04/20, Miami, Florida
James, Taylor, 04/11/20, Seattle, Washington
Bob, Dylan, 01/11/20, Buffalo, New York
Bob, Dylan, 09/24/20, Nashville, Tennessee
Bob, Dylan, 10/22/20, Chicago, Illinois
Bob, Dylan, 12/12/20, Denver, Colorado
Kenny, Rankin, 06/30/20, Las Vegas, Nevada
Kenny, Rankin, 07/04/20, Boston, Massachusetts
Kenny, Rankin, 08/18/20, Cincinnati, Ohio


texts: Logon procedure instead of logon triggers

texts: I want to restrict a sql server authentication user's logging in from Management Studio by using a logon procedure instead of a logon trigger. How can I do that?


texts: Comparison Tool for MySQL data base schemas

texts:
We'd like to update an old database schema for a new software release that requires changes in database schema (additional colums, column renames, table renames, datatypes, ...). Is there any comparison tool for MySQL data base that can help to adapt the old schema to the new one without the loss of any stored data where possible? (free software preferred)

The changes can be made by hand, yes. But we're looking for a more comfortable way and to catch all the changes made.


texts: Why did creating this new index improve performance so much when existing index included all columns in new index?

texts:
I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of Logs and each Log can have up to 125 LogItems

The query in question is complicated so I'm skipping it (if someone thinks it's important I can probably post it), but when I ran SSMS Estimated Query plan, it told me a new Non-Clustered index would improve performance up to 100%.

Existing Index: Non-clustered
Key Colums (LogItem): ParentLogID, DateModified, Name, DatabaseModified

Query Plan Recommendation
CREATE NONCLUSTERED INDEX [LogReportIndex]
ON [dbo].[LogItem] ([ParentLogID],[DatabaseModified])

Just for fun, I created this new index and ran the query and much to my surprise, it now takes ~1 second for my query to run, when before it was 10+ seconds.

I assumed that my existing index would cover this new query, so my question is why did creating a new index on the only columns used in my new query improve performance? Should I have an index for each unique combination of columns used in my where clauses?

note: I don't think this is because the SQL Server is caching my results, I ran the query about 25-30 times before I created the index and it consistantly took 10-15 seconds, after the index it is now consistantly ~1 or less.


texts:
I have a simple but big table (tens of millions of rows). The performance is quite good except when I need to order the table by the column with format datetime.

A simple SELECT query to fetch the last 10 updated rows takes tens of seconds.

Is there a trick to order big table in a fast way?


texts:
I am using rollup() function in group by expression to show the result query,
but my output like this:

Year    Month        AmountOfMoney
====    =====        =============
1998    March        130.00
1998    April        450.00
1988    -            580.00
1999    January      120.00
1999    April        550.00
1999    -            670.00
-       -            1250.00

how to make it order by total, then years, then (year,month)

Year    Month        AmountOfMoney
====    =====        =============
-       -            1250.00
1988    -            580.00
1999    -            670.00
1998    March        130.00
1998    April        450.00
1999    January      120.00
1999    April        550.00


texts: Changing location of binlog prevents starting up

texts:
I've copied data/binlog files between MariaDB (10.3) instances and wanted to change the location of data/log files on the new machine. I adjusted the paths in the my.cnf file. On start on the new machine, the mysqld continues to refer to the old locations, ignoring the paths specified in the my.cnf file - this obviously results in an error like so:

mysqld [ERROR] mysqld: File '<old-location>/mariadb-bin.000160' not found (Errcode: 2 "No such file or directory")
mysqld [ERROR] Failed to open log (file '<old-location>/mariadb-bin.000160', errno 2)
mysqld [ERROR] Could not open log file
mysqld [ERROR] Can't init tc log
mysqld [ERROR] Aborting'

I grepped and found that the ibdata1 file has a reference to the old path - is this something that needs to be updated with mysql CLI prior to the migration?

As far as I can tell, this is not the same as the problem described here: https://stackoverflow.com/questions/7660410/try-to-change-bin-log-directory-mysql-bin-index-not-found-errcode-13

The new instance continues to refer to the old path, despite the my.cnf change. Neither this is a permissions problem (like with apparmor or file ownership).


texts: Incorrect GrantedQueryMemory(KB) Value in SSMS Dashboard report

texts:
Just checking by to see if someone else has witnessed the same and tried a fix or has been fixed:

Issue:

I ran below query on stackoverflow2013 database

select
   ParentId as [Post Link],
   count(id)
from posts
where posttypeid = 2 and len(body) <= 200
  and (body like '%hank%')
group by parentid
having count(id) > 1
order by count(id) desc;

and when checking in SSMS dashboard report for GrantedQueryMemory the value shows around 62 GB which does not seem correct when collecting same from dmv

From dmv it shows 62 MB

It looked to me like a bug so reported here


texts: Do you have to run nodetool repair on every node?

texts: Do you have to run nodetool repair on every node in a cluster, or do you only need to run it on one node, and from there Cassandra will take care of the rest?


texts: Comparison operators give wrong results with keyset pagination

texts:
I implemented keyset pagination on these tables

CREATE TABLE profiles (
    id serial primary key,
    username varchar(255),
    followers bigint,
    tsv tsvector
);

CREATE TABLE profile_categories (
    username varchar(255),
    category varchar(255),
    primary key (username, category)
);

with the following query:

SELECT profiles.*, categories,
       ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers) AS rank
  FROM profiles
  JOIN plainto_tsquery($1) AS q ON tsv @@ q
  LEFT OUTER JOIN profile_categories ON profiles.username = profiles_categories.username
  WHERE followers IS NOT NULL AND
    followers > 0 AND
    (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers), -id) < ($2, $3)
  ORDER BY (
    ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers),
    -id
  ) DESC
  LIMIT 50;

The idea is to use ts_rank_cd to rank documents with full-text search queries, and to scale the rank by the logarithm of the followers.

The problem is that when I query for the second page by supplying the last rank and id ($2 and $3) of the previous page, I still get the last result from the previous page as the first result (so I effectively get only 49 new results)! I have no idea how this is possible since I'm using strict inequality operators.

EDIT Even if I cast the ranks to decimal the same problem persists -- I get duplicates results at the end of one page and at the start of the next:

SELECT profiles.*, categories,
       (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal AS rank
  FROM profiles
  JOIN plainto_tsquery($1) AS q ON tsv @@ q
  WHERE followers IS NOT NULL AND
    followers > 0 AND
    ((ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal, -id) < ($2, $3)
  ORDER BY (
    (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal,
    -id
  ) DESC
  LIMIT 50


texts:
I'm executing a query to return the products of a specific sale in Magento database.

It's working fine for a sale that contains only one product, however, I'm having Subquery returns more than 1 row when the sale contains more than one product/row.

Query:

SELECT  name,
        qty_ordered,
        item_id,
        original_price,
        discount_percent,
        price,
        tax_percent,

        (SELECT mcpev.value
        FROM magento_sales_flat_order_item msfoi,
        magento_catalog_product_entity_varchar mcpev,
        magento_eav_attribute mea
        WHERE order_id = 1
        AND msfoi.product_id = mcpev.entity_id
        AND mcpev.attribute_id = mea.attribute_id
        AND mea.attribute_code = "cip") AS cip, /*Select CIP*/

        (SELECT mcpev.value
        FROM magento_sales_flat_order_item msfoi,
        magento_catalog_product_entity_varchar mcpev,
        magento_eav_attribute mea
        WHERE order_id = 1
        AND msfoi.product_id = mcpev.entity_id
        AND mcpev.attribute_id = mea.attribute_id
        AND mea.attribute_code = "cip7") AS cip7, /*Select CIP7*/

        (SELECT mcpev.value
        FROM magento_sales_flat_order_item msfoi,
        magento_catalog_product_entity_varchar mcpev,
        magento_eav_attribute mea
        WHERE order_id = 1
        AND msfoi.product_id = mcpev.entity_id
        AND mcpev.attribute_id = mea.attribute_id
        AND mea.attribute_code = "gtin_13") AS gtin13 /*Select GTIN13*/

FROM    magento_sales_flat_order_item
WHERE   order_id = 1

I do not see why this error occurs, despite that the root select only (without the inner ones) returns 3 rows, and the inner selects returns also 3 rows .

How can I resolve the issue?


texts:
Using Postgres 9.3, I have problem with a select over few tables.
I have simcards stock and I would like to get something like this:

| network | supplier | total topups (EUR) | 1st topup qty|
| three   | three    | 500                | 34           |
| three   | core     | 300                | 4            |
| o2      | o2       | 100                | 3            |

| network | supplier | total topups (EUR) | 2nd topup qty|
| three   | three    | 200                | 4            |

| network | supplier | total topups (EUR) | 3rd topup qty|
| three   | three    | 200                | 4            |

my table schemas are:

simcard(id, iccid(unique), network_id, supplier_id)
topup(id, date, amount, simcard_id)
network(id, name)
supplier(id, name)

at the moment I am able to select all first topups, but I don't know how to display them in a format as I would like (network| supplied by| topups sum| 1st topup qty)
Then I have no idea how to select 2nd, 3rd topup for every simcard. I tried with limit 1 offset 1 or offset 2 but it didn't work.

select
    distinct topup.iccid
    , s.created_date
    , n.name as "network"
    , p.name as "supplier"
from
(
select
    s.iccid as "iccid"
    , t.date

from
    simcard_topup as t
    join simcard_simcard as s
        on t.iccid_id = s.id
--where
--  CAST(t.date AS DATE) = date '2015-03-13' - integer '14'

group by
    s.iccid
    , t.date
order by
    t.date asc
) as topup
join simcard_simcard as s
    on topup.iccid = s.iccid
join simcard_network as n
    on s.network_id = n.id
join simcard_supplier as p
    on s.supplier_id = p.id


texts: mysqldump: ERROR 2006: MySQL server has gone away when attempting to push data to RDS instance

texts:
I'm attempting to migrate a MySQL database (10M+ records) from an EC2 instance into an RDS Instance using a script:

#!/bin/bash

mysqldump --databases my_db \
    --compress \
    --order-by-primary \
    --single-transaction \
    -u dba \
    -ppassword | mysql \
        --host=host.us-east-1.rds.amazonaws.com \
        --port=3306 \
        --max_allowed_packet=500M \
        -u dba \
        -ppassword

The script connects and begins to transfer data as expected, but then errors out after about 5 minutes with the following error:

ERROR 2006 (HY000) at line 1406: MySQL server has gone away
mysqldump: Got errno 32 on write

The script works fine for smaller databases, and for the larger database it transfers several million records before the error.

I'm not sure how to get past this issue.  I'd love to get some suggestions (parameter adjustments on EC2 or RDS, how to get more detail on error etc.)


texts: FORMAT returns large row size and data size

texts:
I am surprise with one of my findings that using a FORMAT () does have very big impact on the row size and data size. It is almost 250x more of the size of not applying FORMAT ().

My question is:
1) Why does using FORMAT() have such big impact on the size? To me, is just 1.23 vs $1.23 difference, which is probably 1 character difference. And does it matter with such huge size?

2) Why do we still be encouraged to use FORMAT() in SQL server instead of using string concatenation as below. Since below is only 2X data size, and using format returns 250x data size. OR is that data size is not a critical measurement?

SELECT '$' + CONVERT(varchar(10), UnitPrice) FROM Sales.SalesOrderDetail;

3) Does having data size of 464MB means i will be returning 464MB of data to client?

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

Below is my findings with AdventureWorks2012 database.

SELECT UnitPrice FROM Sales.SalesOrderDetail;

Actual Number of Rows: 121317
Estimated Number of Rows: 121317
Estimated Row Size: 15B
Estimated Data Size: 1777KB

SELECT '$' + CONVERT(varchar (10), UnitPrice) FROM Sales.SalesOrderDetail;

Actual Number of Rows: 121317
Estimated Row Size: 26B
Estimated Data Size: 3060KB

SELECT FORMAT(UnitPrice, 'c') FROM Sales.SalesOrderDetail;

Actual Number of Rows: 121317
Estimated Row Size: 4011B
Estimated Data Size: 464MB


texts: How to get the SQL Server Activity Monitor's output using T-SQL?

texts:
Is there any script to get the output just like Activity Monitor?

Or
Which DMV's can I use to get the output as Activity Monitor displays on the screen?

It should be able to display the stored procedure name as well as the statement from the stored procedure which is currently running and the bloking related info as well.


texts: moving to SQL Enterprise 2014 from Standard 2012

texts:
I just need your help in understanding of having SQL Server Standard 2012 and 2014 Enterprise version.

We do have CRM application on-prim. which runs based on SQL server. we do have right now 2012 standard version installed. We do have to runs 100's of reports which is based of SSRS and hitting every time to OLTP system.

We are thinking to get Enterprise 2014 which has "High Availability " or something like that option which is I guess live copy of the OLTP database.

If we do this, I believe it will be easier to run all SSRS report from copy as compared to direct OLTP DB.

Please let me know if this is viable option or there are other more practical approaches as compared to this!

Thanks.


texts: Functional Dependencies and Normal Forms

texts:
I'm trying to grasp the concepts of functional dependencies and normal forms, specifically first (1NF), second (2NF), and third (3NF) normal forms. The link here was extremely helpful in deciding the distinction between the three forms, but I have a much more abstract problem.

The accepted answer to this post gives wonderful examples of tables and which normal form they are in. However, my question poses the following:

For (W X Y Z), determine the form of: WX->Y, WX->Z, Y->Z. (Note: 'WX' should be underlined as the combined primary key, but I couldn't find that in the formatting, so it's bold)

I don't get it; how can I even begin to do this? The textbook I'm referencing only gives concrete examples, and I don't understand the theory well enough to apply it to the abstract. Any insight?


texts: tempdb on raid1 isolated or raid10 shared with high write db

texts:
PROPOSAL 2

8x600gb raid 10 mdf array

4x600gb raid 10 ldf array

  PROPOSAL 3

8x600gb raid 10 mdf array

2x600gb raid 1 ldf array

2x600gb raid 1 tempdb mdf/ldf array

  READ/WRITE STATS ON CURRENT SAN

db.2

reads= 36m

writes= 341m

db.s

reads= 315m

writes= 343k (as in 343,000)

TEMPDB

reads= 69m

writes= 39m

RAID 1 as opposed to current RAID10 is a question mark as our current SAN uses RAID10 for everything (mdf raid 10 , ldf raid 10).

As can be seen above:
DB.2 is very write intensive in both its log file and mdf I would presume.
DB.s is very read intensive but this maybe left on current san.

I have been looking studies that benchmark using the same test RAID1 and RAID10.

From what i understand based on a score of 1 to 5 (http://www.pcguide.com/ref/hdd/perf/raid/levels/comp-c.html) :

RAID1 reads = 2 out of 5

RAID1 writes = 3 out of 5

RAID10 reads = 4.5 out of 5

RAID10 writes = 3.5 out of 5

If this is to be believed and performance monitor shows the ldf array with no reads but consistent writes then putting the LDF on a RAID1 array should have little performance impact making RAID1 for log files acceptable. Fine, great.

Looking at a RAID1 array for TEMPDB however we can tell by the read write stats (above) that the read write values are heavy on both counts.

Therefore moving tempDB MDF from raid10 array1 and the ldf away from raid10 array2 to merge on a raid 1 single array on a new san then becomes a question of the how well tempDB READS perform on RAID1 (as writes would not be a problem as literature suggest raid1 and raid10 writes speeds are similar).

In short under proposal 3 my TEMPDB reads may suffer but would it be better or worse than contended reads when sharing disk space with a write intensive database like db.2 ???

This is the final question in need to answer.

Now bearing in mind on the current san db.s currently hosts 315m reads which is hosted on the same drives (mdf/ldf array) as db.2 and its relative 341m writes.

In this context relative stats from tempdb 69m reads and 39m writes should not have nearly as big an impact as db.s reads could possibly have. And it is perfectly possible that the drop from raid10 to raid 1 will have an impact on the write performance of tempdb.

So again i refer to this article regarding the isolation of tempdb.
http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

Q: How can i gauge of the drop from RAID10 to RAID1 but isolating tempDB on its own array is better than sharing a RAID10 array with a database that has 315m writes but little reads ? (assuming db.s remains on current san)

SIMPLY:

tempdb 69m reads on raid1 isolated

versus

tempdb 69m reads + db.2 36m reads on a shared raid 10 array.

Thank you.


texts: Microsoft SQL Server MERGE deadlock issue

texts:
I am using Microsoft SQL Server version:

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 6.3  (Build 14393: ) (Hypervisor)

I have a stored procedure using a MERGE statement that is named [UpdateReports]

ReportType is a user-defined type like below:

When I run the UpdateReports procedure, it works good in normal situations.  But when it is executed while a full backup is running on the database, Update Statistic (I think many reads of DB), this UpdateReports procedure deadlocks and it takes about 10 minutes to complete.

Is it because I am using HOLDLOCK ?

I searched this issue and many people saying MERGE is not stable....

Could you please let me know if there is any good alternative instead of MERGE?

If I can use IF/ELSE, UPDATE/INSERT instead of MERGE.... I am worried about concurrency