texts: Is there a way to query all error messages in Postgres?

texts:
Postgres has a catalogue of possible error messages here:

http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html#ERRCODES-TABLE

However, I would like to query this information from a system view instead of looking it up on web page  (in order to check that I handle all messages correctly in my code).

Is there a way to ask Postgres via a pg_something view or table what error messages it can raise? Also, where does Postgres store the translation (if any) of these messages?


texts: MySQL Replicate Databases without Tables or Content

texts:
I would like to replicate all the databases from a MySQL master to a slave used for archiving data older than 30 days.  It isn't common for this data to be accessed so I would like it removed from the master.  I only need to replicate the database table structure without any content.  But only two tables (tablea and tableb) out of the 10 tables need to be created in the database structure.

The table content will be imported using an archive script that is using pt-archiver which is why I don't need the content replicated to the slave.  However when the main database is removed from the master it should also remove the database from this archive slave.  Is this possible using replication?


texts: Best way to store physical stock data in a SQL db

texts:
I work for a retail company and we are in the process of building a new data warehouse. One element we need to store is physical stock data as to how many units of any sku are available at a certain location at a certain time. the variables we want to look at are, therefore: skus, sites, day, hour and quantity.

The problem is that there are approximately: 100k skus and 200 stores. If I store data for every hour I would get 100k x 200 x 24 = 480,000,000 lines a day. I understand that not every sku has a quantity > 0 at every site. Let's assume sparsity is as high as 80% and when the quantity is 0 we don't store it so we remain with 96,000,000 rows a day. Moreover, even though we have websites and sales won't stop at night we may assume that night hours will have very little changes in quantities and that not every sku is sold every hour. Let's assume that on average a sku is sold only on 3 different hours a day. This brings us down to 12,000,000.

This is 2.5% of what we initially looked at, however, it is still a considerable amount. 12,000,000 rows every day is going to occupy a lot of space after a few months. A partial solution to the problem is what Kimball suggests in The Data Warehouse Toolkit which is to store data for each hour for the last month or so and then only daily for the last n months and then remove older records. Considering that we are already assuming that on average we sell a sku only on 3 different hours per day switching to daily data from hourly data will reduce only by two thirds. Let's assume I want to save stock data for the last six month. One month at hourly level and five months at daily level (let's also assume 30 days months to ease the math) this is going to be: (12,000,000 X 30) + (4,000,000 X 150) = 960,000,000 rows.

I really can't think of a better way to do this, had anyone had to work with physical stock data before? Any useful lesson you learned? Is there a more efficient way to store this data?

Thanks,


texts: How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?

texts:
I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job.

I have a remote Oracle 11g R2 database on unix and a local one on Windows.

I read that you can install the oracle scheduler agent from the 11g client install for machines that don't have Oracle installed but this is not needed for running remote jobs if Oracle is present on both machines.  With the remote agent installation, you run schagent and provide parameters to register the agent to the remote machine but I cant find any instructions on the web regarding how to register remote agents when both machines have Oracle installed or what to use as the agent name in this case.

I have added an entry to tnsnames.ora for the remote DB and can tnsping, etc.

If I run the dbms_scheduler.create_database_destination procedure, it requires an agent name but where can I find this for the database or how can I check that it is running on Unix or Windows?


texts:
One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes).

ls -l *sql*
-rw-rw---- 1 mysql mysql  8570 Mar 13 12:05 #sql-ib32694.frm
-rw-rw---- 1 mysql mysql 98304 Mar 13 12:05 #sql-ib32694.ibd

mysql> drop table `#mysql50##sql-ib32694`;
ERROR 1051 (42S02): Unknown table 'hdb.#mysql50##sql-ib32694'

Running the alter table on the original table again:

ERROR 1050 (42S01) at line 1: Table 'hdb/#sql-ib32694' already exists

???

I followed all the instructions here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

also tried innodb_force_recovery = 4

So I deleted those files manually (getting the typical entry in error.log for missing tablespace), recreated them in another DB, copied them over to this and nothing changes.

No success. I had some issues like that some time ago and I managed to remove it. This tiny little table is really stubborn though.

I can delete it, but then I get the same error message like for orphaned tables (in the error.log)

Could not find a valid tablespace file for 'hdb/#sql-ib32694' ...

Also - different to what you are writing here

  Don't worry about the temp table name being used again. Look at the name of the temp table

  #sql-7a87_230c32.ibd

  There are 10 hexadecimal digits. The chances of reusing that temp table name is 1 in 1610 or 240 which 1,099,511,627,776. I think you will be OK in this.

when I do the ALTER TABLE on the table I ran it on what created the temp file, I get the error message (after I deleted the table)

ERROR 1050 (42S01) at line 1: Table 'hdb/#sql-ib32694' already exists

so it seems MySQL is using the same temp table name when you do an alter table.

No master/slave here.


texts: Why can I use named pipes to connect to SQL Server even if it's been disabled?

texts: Why can I still use named pipes to connect to SQL Server even if it's been disabled? I'm on SQL Server 2016 doing the testing. I have recorded my operation in this youtube video: https://www.youtube.com/watch?v=4lvkbwsspZc Hope someone can point me the right direction.


texts: How to test a function that selects from real tables?

texts:
I've got a mega function that everybody is afraid to modify. So each fix adds a dozen of new IF lines. To me it feels that this function screams for proper testing.
Instead of the real function I'm including a really simplified example, because I believe that the crux of the challenge is that it's not only a function of its explicit parameters, but also of the state of some of the actual tables in the database.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [statistic].[daysByProject](
    @DateFrom DATE,
    @DateTo DATE,
)
returns
@ret table(ProjectID INT, IsFine TINYINT, Days smallint)
BEGIN

insert into @ret(ProjectID, IsFine, Days)
select ProjectID, IsFine, Days
from dbo.SomeRealTable

RETURN
END

Is it possible to add tests to such a function? I've even heard of including tests in a transaction, apparently something akin to this
/* alter function */

/**
  * Set up test case
  * Run
  * Raise error if test fails
  * Repeat with all test cases
  */

/* Roll back on error */

Could something like that be possible so that (unless explicitly working around this) developers could only modify the function in a way that satisfies the requirements of test cases?


texts:
I'm able to find duplicate records using the following query, however, I'm not sure how to delete duplicates records, and ignoring any records that starts with 0 in starttime field.

SELECT `StartTime`, COUNT(`CallDetailRecordID`) AS cnt FROM `CallDetailRecord`
GROUP BY `StartTime` HAVING cnt > 1

Sample output

 StartTime     cnt
-------------  --------
        0        198
1340511506737         2
1340511958364         2
1340512141687         2
1340512191631         2
1340512244925         2
1340512670902         2


texts: What is the standard isolation level for a db used for money data?

texts:
When using a database server for monetary/financial data, I can only assume that using transactions is mandatory.
But what I am not sure is which is usually the isolation level? Is a READ-REPEATABLE used? Or only a serialized level?


texts: Unable to display parent-child heirarchy

texts:
I am creating a parent-child hierarchy in my dimension.
The AdventureWorks2012DW sample database is used.
I have set the ParentEmployeeKey, NameColumn property to LastName as per the wrox sql server 2012 book.
I have attached the Dimension Browser Image.

I am  getting the Employee Key instead of LastName


texts:
I have a large MYSQL innodb database (115GB) running on single file mode in MySQL server.

I NEED to move this to file per table mode to allow me to optimize and reduce the overall DB size.

Im looking at various options to do this, but my problem falls in there only being a small window of downtime (roughly 5 hours).

Setup a clone of the server as a slave. Set the slave up with file_per_table, take a mysqldump from the main DB, run in the slave and have this replicating.

I will then look to fail over to the slave.

The other option is the usual mysqldump, drop DB and then import.

My concern is around the time to take the mysqldump and the quality of the dump being such a large size. I have BLOB data in the DB also.

Can anyone offer advise on a good approach?

Thanks


texts: Find every hashtag that has been mentioned in at least three post captions on every day of 2017 (relational algebra)

texts:
I have this schema:
(this is just part of it, I don't think the rest is necessary for this)
Post(pid, uid, when, location, caption)
Hashtag(pid, tag)
Hashtag[pid]  contained in  Post[pid]

"when" contains the date that can be used as when.year, when.day, etc... my prof wasn't very specific.
I need:

Find every hashtag that has been mentioned in at least three post
captions on every day of 2017. You may assume that there is at least
one post on each day of a year.

I don't think we are allowed to use aggregate functions in this. I am not sure, but we haven't used before and the problem statement says to stick to the other ones (join, intersection, union, rename, etc...).
I got this far:

Now I need to check if these tags have appeared at least thrice a day, every day of 2017.
Help?


texts:
I have 3 table

Partcar
BarbellWheels
Assembly

now, im confused how to manage this table to got connection, cause Partcar and Assembly is many-to-many relationship. But, BarbellWheels will merging with Partcar in Assembly. How to make this 3 table have many-to-many relationship, Partcar and BarbellWheels with Assembly.

EDIT :

there is 5 data on Partcar and must combine to 1 in Assembly and BarbellWheels will combine in Assembly with max 2 data on it. So, this is the link

any advise? need some help here

EDIT :
I forgot to mention the relationship between them. So, 2 tables partcar and BarbellWheels is Many-to-Many with Assembly.


texts:
I am not a pro in the database sector so pardon me if i am asking a basic question.
Scenario
I have a table in which all the recorded are stored chronologically.
Number of rows of that table is 1,445,248 (1.4 Million).
Desire
I want to optimize my query which looks like following: -
Select * from SalesInvoice where S_Date Between 20190401 And 20190501

Note

Yes, i need all the columns in the table and instead of typing out all the columns, i just used the all symbol (astrix).
S_Date is stored as numeric instead of DateTime (because it was developed a long time ago in this format)
The total time taken to execute this query is only 4 seconds but because of some calculations performed it is looping through ~80-90 time.

Question
I want to know, is there any way through which i can start the searching of rows from Nth position.
My Approach

Edit - Currently i am using SQL Server 2008 R2 but i will switch to MySQL that is why the query is written in SQL Server 2008 R2 format.
Step 1: Get first index of data by following query: -

Select Top 1 Id from SalesInvoice where S_Date Between 20190401 And
20190501 order by S_Date ASC

Step 2: Start Searching from the returned Id lets say 500,000

My Assumption if this technique exists
According to me, this technique might save table scanning time by starting the search from that ID which removes scanning of 500,000 rows.
BUT
I don't know how to start the lookup from a particular ID.
If anyone can provide me with a code snippet that will be very helpful


texts: What is the name of the process for creating a new DB from an exported snapshot?

texts:
I need to set up an automated process that:

creates a snapshot of an AWS RDS (MySQL) DB
uses that snapshot to create a new RDS instance that will be used as an "analytics playground"

I'm looking for the proper name to call the process described in step 2 above. So I ask: what would the DBA community call the process of using a DB snapshot (export) to create a brand new database? Materializing? Restoring? Backing up? Something else?


texts:
I'm trying to find what query (or queries) need to be run in order to determine if there is any sort of a problem with an availability group.  I have been using:

SELECT @@SERVERNAME AS 'primary_replica', cs.database_name, rs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states rs
join sys.dm_hadr_database_replica_cluster_states cs on rs.replica_id =
    cs.replica_id and rs.group_database_id = cs.group_database_id
WHERE rs.is_local = 1

It turns out that the synchronization health (select * from sys.dm_hadr_availability_group_states) can be NOT_HEALTHY, but the query above does not show any problem with the replica.  I have tried looking through all of the various DMs that contain information, but it is a maze or words and I can't find my way to the SQL cheese that must be there somewhere.

What is the one query that will indicate that there is a problem that needs to be addressed?  I don't need it to be specific or to designate the cause, I just need to know that there is a problem.  I know that during failover some of the states will be NOT_HEALTHY, and we account for that by requiring x minutes of unhealthiness before sending an alert.


texts: Creating a query to pull very specific information using four tables

texts:
I have four tables, with a structure like this:

testData

This table holds all test data for every user.  Each row associates one response for a question, divided up by test.  A user can have many tests, a test can have many unique questions, a question can only have one response.

testUUID                               questionUUID                           answerUUID
===================================================================================================================
0d09eea5-4015-4777-92db-44cf48c9b3d6   fd7485fd-5c99-4d26-bdbd-a559b9e47d14   00044311-b9fe-411f-b125-61a40c090068
...(~2 million rows)

answerData

This table stores the actual answers to the questions.  There is a similarly structured questionData table, but I can work that in later.

uuid                                   answerText              answerCorrect
============================================================================
00044311-b9fe-411f-b125-61a40c090068   this is an answer       1
00044234-c820-432a-2003-239ffbbac982   this is another answer  0

testHistory

This stores data about each test a user has taken.

uuid                                   userUUID    (and lots of other columns including score)
==============================================================================================
0d09eea5-4015-4777-92db-44cf48c9b3d6   01b7f715-4142-4436-b527-26b5158578f7

userData

This stores data about the user.

uuid                                   userFirstName (etc.)
===========================================================
01b7f715-4142-4436-b527-26b5158578f7   tehbing

I need to figure out.... across all tests that a user has taken, find the questions that are missed the most so they can study those particular questions more often.  Ideally, questionUUID should be in one column and a count for the amount of times a question was missed in another column.  I can do this in PHP, but I feel that this is something the database can do much faster if structured correctly.

Indexes for the two biggest tables

testData table
Keyname         Type    Unique  Packed  Column          Cardinality Collation   Null
testQuestion    BTREE   Yes     No      testUUID        39354       A           No
                                        questionUUID    1692249     A           No
testUUID        BTREE   No      No      testUUID        34535       A           No
questionUUID    BTREE   No      No      questionUUID    11281       A           No
answerUUID      BTREE   No      No      answerUUID      54588       A           No

testHistory table
Keyname         Type    Unique  Packed  Column          Cardinality Collation   Null
PRIMARY         BTREE   Yes     No  uuid                44965       A           No
userUUID        BTREE   No      No  userUUID            5620        A           No
                                    totalQuestions      5620        A           No
                                    questionsMissed     44965       A           No
                                    testScore           44965       A           No
                                    testTimeStarted     44965       A           No
                                    testTimeCompleted   44965       A           No
testArchived    BTREE   No      No  testArchived        4           A           Yes


texts: I have a SQL Server 2016 instance version is SP1-GDR - 13.0.4202.2. I want to apply the Cumulative Update packages but they will not install. When you step through them, you cannot select any options to install they are all greyed out. I am trying to install CU5 - 13.0.4451.0.  Any help would be appreciated


texts: Query not returning results if selecting by indexed columns (Mysql 5.6)

texts:
We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column then we get the expected results.

For example,

SELECT product_id FROM product where product_id = 1;

doesn't return any results.

But if we do this:

SELECT * FROM product where product_id = 1;

We get the expected results.

The table engine used to be MyISAM, however, when we changed it to InnoDB we started having these issues. We had to change it back to MyISAM because a lot of websites were down because of the above issue.

The server specs:

Ubuntu 16.04 LTS
Mysql 5.6
27GB RAM
300 GB Free space while using 100 GB

My.cnf:

innodb_log_file_size = 3G
innodb_buffer_pool_instances = 21
key_buffer_size = 500M
innodb_buffer_pool_size = 22G
sort_buffer_size = 512K
read_rnd_buffer_size = 512K
thread_cache_size = 9
thread_cache_size = 9
innodb_file_per_table = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_at_shutdown = 1

We didn't have any errors on the application side and no error logs in Mysql. The server had 6 GB of Free memory so we weren't running out of memory. The above recommendations were made by mysqltunner.pl

Results from Mysqltunner:

[OK] Slow queries: 0%
[OK] Thread cache hit rate: 93%
[OK] Table cache hit rate: 95%
[OK] Open file limit used: 0%
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 20.0G/21.3G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 3G *     2/20G should be equal 25%
[OK] InnoDB buffer pool instances: 21
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.95%
[OK] InnoDB Write Log efficiency:  93%
[OK] InnoDB log waits: 0.00% (0 waits / 68 writes)

Edit 1:

Query used to convert MyISAM to InnoDb

ALTER TABLE `product` ENGINE=InnoDB;

Table Schema:

 CREATE TABLE `product` (
  `product_id` int(11) NOT NULL,
  `product_type` int(11) NOT NULL DEFAULT '1',
  `product_quantity` float NOT NULL DEFAULT '0',
  `product_model` varchar(32) DEFAULT NULL,
  `product_sku` varchar(255) DEFAULT NULL,
  `product_label` tinyint(4) NOT NULL,
  `product_flavor` tinyint(4) NOT NULL,
  `product_new` tinyint(1) NOT NULL,
  `product_sale` tinyint(1) NOT NULL,
  `product_image` varchar(64) DEFAULT NULL,
  `product_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `product` ADD PRIMARY KEY (`products_id`),

-- Added the FullText key when we changed it to InnoDB
ALTER TABLE `product` ADD FULLTEXT KEY `model_index` (`products_model`);


texts: How does MySQL manage a delete then select of the same row within a transaction

texts:
Considering MySQL ver 5.7.
Take the following example:
Begin Transaction
Delete from payments where id = 1
Select from payments where id = 1
End Transaction

In such a scenario should I expect the select statement to return no rows, since I've just deleted that row?  Or will it still return a row because the transaction has not yet been committed?
And if the select does not return a row, then can I assume that any cascade deletes on child tables are also safely deleted before I do my select?
(To answer the inevitable question of "why would you do this?" the answer is simply that I'm hoping to re-use the same script in multiple different scenarios, so I want to know if it's safe to do so or if I have to write separate scripts.  In one scenario the delete will execute, in another it will not.)


texts:
I need to do a SELECT query where I get the value of the field "money".  The field doesn't actually exist in the database.  I just need the query to return this field with a fixed value; in this case a value in USD.

How do I return a constant value in a SELECT statement?


texts: Update statement with strange side effects to data

texts:
The problem:
I am trying to add some logic to a database refresh job that will de-identify PHI-related fields, and have come across a strange side effect. I am attempting to exclude a group of patients, in order to allow that set to see their true information, and mask all of the other sets. When I run my update statement, it also updates a large portion of that set I was trying to exclude. Why is that, and how can I change my update statement to avoid that behavior?

The data model:

Patients table - ID as the key, and GroupID to differentiate groups of patients. Also has an IsDeleted field (bit) to indicate soft deletion.t item
Phone table - ID as the key, and has three business fields: AreaCode, PhoneNumber, and Extension. Also has an IsDeleted field (bit) to indicate soft deletion.
PatientPhone - Has an ID as the key, but also uses the combination (PatientID, PhoneID) to link these tables.

The relationship:
Patient to PatientPhone - 1 to Many
Phone to PatientPhone - 1 to Many

In order to validate before and after, I have been using this query to sample the phone numbers by grouping the first couple of digits. It gives me a wide distribution of values, as I would expect:

SELECT
P.GroupID, left(PH.AreaCode, 2) as ShortAreaCode, left(PH.PhoneNumber, 2) as ShortPhoneNumber,
    left(PH.Extension,2) as ShortExtension, count(*)
FROM dbo.Patient P
JOIN dbo.PatientPhone PP
ON P.ID = PP.PatientID
JOIN dbo.Phone PH
ON PP.PatientID = PH.ID
WHERE P.GroupID = 12345
GROUP BY P.GroupID, left(PH.AreaCode, 2), left(PH.PhoneNumber, 2), left(PH.Extension,2)
ORDER BY count(*) desc, P.GroupID, ShortAreaCode, ShortPhoneNumber, ShortExtension

I then run this update statement against the Phone table to default every other patient's phone number to 555-5555:

   UPDATE
        PH
        SET AreaCode = '555', PhoneNumber = '5555555', Extension = NULL
        FROM dbo.Phone as PH
        INNER JOIN dbo.PatientPhone as PP
        on PH.ID = PP.PhoneID
        INNER JOIN dbo.Patient as P
        on P.ID = PP.PatientID
        WHERE P.GroupID <> 12345 --This is the GroupID where we want to retain phone numbers

However, when I re-run my validation query, I see a couple of problems:

Thousands of records I did NOT want to update were updated.
Many of the other groups of Patients were NOT updated.

(Sample of the output afterwards):

In theory, the data model allows for phones to be related to more than one patient. We did have some issues in the past, and most of the phone numbers have been denormalized. I need help to understand:

How I can explain this side effect of the update statement
How I can adjust my update logic to exclude any 'shared' phone numbers, so that the set of patients not in one particular GroupID will have their phones defaulted

I would tend to believe the data (phones being shared) is likely the cause of this problem, but I am having trouble proving that. Can someone help me with this? Thanks for your time.


texts: Select rows within group before condition is met


texts: Database choice for structured big data to be queried with SQL

texts:
I have a system that harvests and registers a total of 500K (master table) and 100 million (detail table) into a two table master/detail database, per day. The detail table has around 50 fields, two of them are varchar and the remaining numerical. The writes are made in real time.

Users of this data can search data using a full match for the varchar fields and using a combination of any of the other numerical columns as filters. They can use one field as filter but they can also use 40. It's their choice. An internal API generates the SQL accordingly.

I'm using an AWS Redshift cluster to store and query this data. The writes are buffered and they come in through an S3 COPY command.

Performance is deteriorating now that I'm reaching 2 billion records in the detail table. A simple query like

SELECT a_date ,count(1)
FROM detail_table
WHERE (a_date BETWEEN a AND b)
AND token in ('alpha','beta','beta1','beta2','beta3')
GROUP BY a_date

is taking around 15 seconds. Can anyone suggest another system, or validate Redshift, as the most efficient architecture/engine for this?


texts: SQL Server 2012 IO Latency and Wait Types

texts:
I'm working on a system that has a resource intenstive houskeeping job. It hits two different data files on the same LUN. One is for relational data, one for blobs. During the job, read latency on the relational data file averages over 250ms, while on the LOB data it is only 5ms. This is measured using sys.dm_io_virtual_file_stats (two samples 12 hours apart to encompass the houskeeping - during the other 12 hours, latency is 20ms).

I'm sampling wait stats using "who is active". In this I don't see any PAGEIOLATCH waits with large enough wait times to explain the 250ms. sys.dm_os_wait_stats backs this up with an average of 5ms for PAGEIOLATCH_EX and 15ms for PAGEIOLATCH_SH. What I do see is long waits for LCK_M (which is explainable, due to the nature of the housekeeping).

My question is: can the LCK_M waits contribute to the io_stall_read_ms times in the virtual file stats?

In case the details of the housekeeping matter, it is removing smallish batches of data using multiple cascading deltes, including ~1MB of LOB data per item. The system is using AlwaysOn with sync commit.


texts: Dumping and restoring on db server - postgres

texts:
Is there a way to execute a backup and restore of PostgreSQL DB from App server without calling pg_dump from App server and executing pg_restore also on App server. In this way I'm executing operations from App server on DB server and making unnecessary network traffic.

I would like to execute dumping and restoring directly on DB server, but to initiate it from my application.

My current situation if that I make extra network traffic, wait more time and also I need to install PostgreSQL client on my app server.

Any hints?


texts: Deadlock during Slony uninstall and concurrent access of DB

texts:
I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process.

I was trying to uninstall node from a cluster, as per slony's documents this requires access exclusive locks on all replicated tables. When the slony process was uninstalling the cluster, at the same time the application inserted a row in one of the replicated tables which I think resulted in a dead lock:

  DETAIL:  Process 22593 waits for AccessExclusiveLock on relation 16392 of database 16388; blocked by process 11808.
  Process 11808 waits for AccessShareLock on relation 18366 of database 16388; blocked by
  process 22593.

Can any body confirm that what I am thinking is correct and suggest a solution for this.


texts: Sort by Start Date Desc if End Date is null, if not Sort by End Date

texts:
I have a SQL Server (RDBMS) table with a list of Activities. Each activity has a Start and End Date.

The requirement is to sort them is  order such that the ones with a NULL end date display first (which suggest "current" - start date descending), but the ones with values for both start and end to be sorted at the end of the "current" rows and sorted by end_date descending.

Lastly, all values with null for start and end dates must be at the end of the result set.

Values with an end date but a null start date should be sorted in the second section of the sort among the END date desc piece.


texts:
Is reading a single record always an atomic operation?

I mean, when e.g. a read (select) and write (update) query are simultaneously carried out on the same record, is it possible for the read operation to read and inconsistent record? (some field values from the old version of that record and some new values resulting from the write operation be returned)


texts: Upgrade SQL Server 2014 Failover Cluster to SQL Server 2016 Availability Group on Windows Server 2016

texts:
In our current set up we have 2 node SQL failover cluster in data center DC1 with mirroring enabled. And similarly in DC 2 a 2 node SQL failover cluster. DC1 acts as primary via DB mirroring in one point of time and DC2 mirror.
 Above runs on Windows server 2012R2

How can I convert the above setup from sql cluster to get cluster so that can leverage always on. I know this is sort of s big question but needed to know how can this be achieved with some overview and I can do some poc on that. We are thinking to upgrade to sql 2016 on Windows 2016 if possible.

Please guide for any real time experiences or blogs I can go through to do such migration.

Edit-- Per Answer below which needs the DR server at different DC to be out for route seems to be last option but i will still test to figure out the timings.

Also, is it possible to have setup like configure LS on current prod server with 4 node new built multi site cluster for alwayson AG? And during cutover we can simply stop the LS job on prod and convert the LS secondary on 4 node cluster to alwayson AG configuration


texts: Subscription uploading is not happening every time

texts: I have two SQL Servers (2008 R2), and merge replication between them, one being the publisher and the other subscriber. I noticed that downloads are happening every minute, but uploads are happening every 10-15 minutes. I wonder why is this happening. There is very little activity on the subscriber, in average one change per minute. Does the subscriber batches uploads and only uploads them only when the batch reaches certain size? What can I do to determine the cause of this behavior.


texts: I am using Amazon REDSHIIFT and want to make my table data case insensitive?

texts:
How can I change my Tables data stored in REDSHIFT case insensitive?

For example: Bell, BELL, bell in a column should return all the rows when queried.


texts: MySQL encryption for 'database at rest' on cloud services

texts:
We are migrating our system to a cloud environment (AWS), and have been given new client requirements for having our MySQL data-at-rest encrypted.

Currently it is unclear how far this should go. It would be very simple to enable disk encryption, however it is being argued internally that this actually only mitigates against disks being stolen (physically or otherwise) but not against server breaches.

It seems there is only limited options to implement DB layer encryption with MySQL (or more accurately, it's drop in replacements like MariaDB or Percona) and there is some flaws with it currently (such as binlogs and caches are not encrypted). Also, app layer encryption would be a massive overhead as the vast majority of queries search on fields that are considered sensitive, and thus will need to be encrypted.

What is the current accepted approach to encrypting sensitive data on a MySQL database in a cloud environment? Please note this is not referring to RDS.


texts: View locks acquired during query execution (SQL Server)

texts: Query execution plan does not show the locking details by default, is it possible to view the locks, along with the type, acquired during the execution of a query?


texts:
We have a service will collect data and call api to save to a mongodb's collection name alldata

And the problem is ,it keeps write data frequently,then the mongo will shutdown (I found on internet said the collection limit is 16mb)

So we have to delete the old data by hour
But I want to keep the old data at least one week
How to design this ??

should we use many collections to save??  If we use many collections,how to query many of them??
Or should we back up ?

I heard that mongodb is appropriate to bigdata,but I don't know how people usually use it ?
Please give me some advice    Thank you.


texts: MS SQL Linked Server to HANA 2.0 - error 10 authentication failed

texts:
I have some trouble setting up a linked server to a SAP HANA database.

On the SQL-Server machine (SQL-Server 2019) i installed the ODBC drivers and set up a 64 System DSN. When I click on the "Test Connection" it succeeds. Driver version is 2.04.167.62361

I then Setup the Linked server as Follows:

EXEC sp_addlinkedserver
   @server='SAP',
   @srvproduct='HANA',
   @provider='MSDASQL',
   @datasrc='HANA64';  --> Name of the ODBC DSN
EXEC sp_addlinkedsrvlogin
 @useself = 'FALSE',
 @rmtsrvname = 'SAP',
 @locallogin = NULL,
 @rmtuser = 'Readonly',
 @rmtpassword = 'my_pwd';

It creates the Linked Server. When I test the connection in SSMS I get:

  [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;10 authentication
  failed

All research on the internet did not get me further. Any Ideas?

My ODBC DSN Connection looks like follows:


texts: How did we get condition filtering value of 5 percent in this plan instead of 10 percent? MySQL 8.0

texts:
Assume the below schema, this is just created to test some features, and not a valid design for production. When I run the below query and check it's optimizer trace, I find lot of plans.

select
e.emp_no,
concat(e.first_name, ' ', e.last_name),
d.dept_name,
t.title_name,
es.salary,
es.insurance,
es.pf,
ea.city,
ea.state,
ea.phone
from employee e
join emp_salary es on e.emp_no = es.emp_no
join emp_title et on et.emp_no = e.emp_no
join title t on t.title_no = et.title_no
join emp_address_phone ea on ea.emp_no = e.emp_no
join emp_dept ed on e.emp_no = ed.emp_no
join department d on d.dept_no = ed.dept_no
where
(e.hire_date > '2004-01-01' or e.hire_date < '1980-01-01') and
(es.pf > 4.25 or es.pf < 1.4) and
(t.title_created > '2006-01-01') and
(ea.country = 'Spain' or ea.country = 'Samoa' or ea.country = 'India');

In the first plan that is..

d->t->et->ed->es->ea->e

..there is a condition filtering value when ed is joined to d->t->et that I am unable to make sense of.

I recreated this join order, with a straight join, I tried explain, and found the same.

+----+-------------+-------+------------+--------+-----------------+----------+---------+-------------------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys   | key      | key_len | ref                           | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+-----------------+----------+---------+-------------------------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL    | PRIMARY         | NULL     | NULL    | NULL                          |  100 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t     | NULL       | ALL    | PRIMARY         | NULL     | NULL    | NULL                          | 1000 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | et    | NULL       | ref    | emp_no,title_no | title_no | 5       | employee_cond_filt.t.title_no |    5 |   100.00 | Using where                                        |
|  1 | SIMPLE      | ed    | NULL       | eq_ref | emp_no,dept_no  | emp_no   | 5       | employee_cond_filt.et.emp_no  |    1 | **5.00** | Using where                                        |
|  1 | SIMPLE      | es    | NULL       | eq_ref | emp_no          | emp_no   | 5       | employee_cond_filt.et.emp_no  |    1 |    55.55 | Using where                                        |
|  1 | SIMPLE      | ea    | NULL       | eq_ref | emp_no          | emp_no   | 5       | employee_cond_filt.et.emp_no  |    1 |    27.10 | Using where                                        |
|  1 | SIMPLE      | e     | NULL       | eq_ref | PRIMARY         | PRIMARY  | 4       | employee_cond_filt.et.emp_no  |    1 |    55.55 | Using where                                        |
+----+-------------+-------+------------+--------+-----------------+----------+---------+-------------------------------+------+----------+----------------------------------------------------+

I understand

Filtering Factor (ea) = 0.1 + 0.1 + 0.1 - 0.01 - 0.01 - 0.01 = 0.27 (ea.country = 'Spain'  or ea.country = 'Samoa' or ea.country = 'India' )
Filtering Factor (t) = 0.3333 (t.title_created > '2006-01-01')
Filtering Factor (es) = 0.3333 + 0.3333 - 0.1111 = 0.5555 (es.pf > 4.25 or es.pf < 1.4)
Filtering Factor (e) = 0.3333 + 0.3333 - 0.1111 = 0.5555 (e.hire_date > '2004-01-01' or e.hire_date < '1980-01-01')

But attached condition for ed is just (ed.dept_no = d.dept_no). Should the filter percent be 10 or the factor be 0.1 in that case?

Full Explain and Full Trace for reference.


texts: What is the best approach to parsing a large amount of strings?

texts:
I have a table with over 400,000,000 records and I am looking for advice on how to parse it quickly.

TheNameTable
(
  NameID  int primary key,
  TheName varchar(500)
)

The names are stored like this:  "FirstName, LastName"  (not my table, just what I have to work with)

I need to extract a unique list of last names.  My initial thoughts are to process the table in a series of batches (say 50,000 records at a time), using the NameID to control the batch ranges.  I would then use SQL's built in string functions to break the string at the "," and keep the right half of the string.

right(TheName,charindex('.',reverse(TheName))-1)

I have a feeling this will still take a LONG time.

Anyone out there have any other ideas?

Would it be worthwhile to simply export the data and process the file outside the database?

The Solution I went with:

As suggested, I created two computed columns.  One for first name, one for last name.  They are not persisted, as I do have limited space.

alter table TheNameTable
add LastName as substring(TheName, charindex(',',TheName)+1,1000)

alter table TheNameTable
add FirstName as left(TheName,charindex(',',TheName)-1)

I asked the admins for a temporary increase in RAM, they bumped the VM up to 32GB.

I created a new table, with columns for both FirstName and LastName.  I put a unique compound index on the columns, but specified IGNORE_DUP_KEY = ON.

I just inserted the first 1,000,000 records.  It filtered out 125,000 duplicates.  The whole statement took 9 seconds to run.

That is the speed I was looking for!


texts:
We have deployed REDIS to hold some frequent query result to reduce number of connections made on Database server. We need to find out Max_used_connection after deploying REDIS. So we plan to reset mysql status variable Max_used_connection.
While googling i found to reset Max_used_connections we need to use FLUSH STATUS.
Is it safe to execute Flush status in production ?
MySQL docs says:

FLUSH STATUS -  resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections.

I don't know impact of resetting counters for key caches.
Is it possible to reset Max_used_connections alone ?
Thanks in Advance.


texts: Selecting difference between two tables returns different number of results than counts of same two tables

texts:
I'm using SQL Server and when I run
SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2
it returns 1610 entries. When I query

SELECT COUNT(*) FROM TABLE1
SELECT COUNT(*) FROM TABLE2

I get 6399 and 4583, the difference of which is 1546, not 1610 as I would expect from the first query. What could be causing this discrepancy between the number of results returned and the difference of the individual table counts?


texts: Why can I select all fields when grouping by primary key but not when grouping by another column

texts:
How is this a valid statement (where id is the primary key of the table):

select * from table group by id ;

and this is not:

select * from table group by name ;

  ERROR:  column "pgluser.id" must appear in the GROUP BY clause or be used in an aggregate function

Fiddle.

The question is why is the first a legal query, ie why grouping by primary key is valid?


texts:
I have a TOP server (SQL Server 2012) with a merge replication to around 4 other servers.
When I subscribe to the publication, everything is downloaded fine to the subscriber.

The insert of one of the tables synced with the TOP server but not with a different table in the publication. On another server I have none of the inserts making it to the TOP server.

How can I troubleshoot this kind of problem when SQL Server isn't detecting any problems?


texts:
I am scheduling a report using SQL Server Reporting Services Subscription option.
Suppose my rdl file is corrupted or my stored procedure has got some problem.
Then the report won’t generate in its next turn.
What can I have to get the intimation that the report does not generated properly?

Can I configure to get a mail on report generation failure


texts: locking question around INSERT during UPDATE query

texts:
Say I have a large table products

id, color, cost

and I want to run a query to update the cost of all 'red' items

UPDATE products
SET cost = cost + 1
WHERE color = 'red'

Let's pretend that there are so many rows that the query takes 5 minutes to finish.

What sort of lock will be placed on the table? Am I right in thinking that as the query executes, write locks will be acquired on all the rows where color = 'red', so these records cannot be updated until the UPDATE query finishes. They can still be read however (and reading them will always provide old values until the entire UPDATE transaction finishes)

What happens when the UPDATE query is started, and it is still working out which rows to lock as it's scanning the table for records with the color red? It would still allow updates to some records with the color red ?

But it won't lock the whole table, so other records can be written or updated whilst the UPDATE query is going on? What happens if a new record is inserted with a color of red. Does the price of that product get updated? surely not?


texts: How to check other table for value during insert

texts:
In PostgreSQL is it possible to check another table's data to see if there is data that fits a certain condition during an INSERT statement?

I have attempted to use WHERE NOT EXISTS as I want to not go on with the INSERT if I don't find the data I'm looking for in another table but I get the following error:

Database Model

Query

INSERT INTO car_model(model_name, car_make, date_released)
            VALUES("Classic Car", "Car Brand", "2000-01-01")
WHERE NOT EXISTS (
   SELECT * FROM car_make
   WHERE company_name = car_make AND date_released >= date_incorperated)

Error:

ERROR:  syntax error at or near "WHERE"
LINE 3: WHERE NOT EXISTS (SELECT * FROM car_make WHERE company_name ...
        ^


texts: SQL Server stored procedure causing 100% CPU usage

texts:
I'm using SQL Server 2008 R2

We have a single stored procedure that causes 100% CPU usage when ran. The said SP is ran once a month and performs heavy calculation for approximately 5 minutes.

Since the SP is ran once a month, we don't particularly care if it takes say 30 minutes or even longer to complete the process, but taking up all of the available CPU resource is problematic. We'd like to limit the CPU usage for this particular stored procedure.

Is there such an option in SQL Server?


texts: Compare each column in a row and return an error if any of them is different

texts:
I'm attempting to find any identifiers with any values that are different in the same row.

+------------+--------+--------+--------+--------+
| Identifier | Value1 | Value2 | Value3 | Value4 |
| f001       | a1     | a2     | a2     | a2     |
| f002       | a4     | a4     | a4     | a5     |
| f003       | a2     | a2     | a2     |        |
| f004       | a1     | a1     | a1     | a1     |
| f002       | a9     |        |        |        |
+------------+--------+--------+--------+--------+

For example,

The first identifier should return "MisMatch",
The second identifier should return "MisMatch",
The third identifier should return "NoIssue",
The fourth identifier should return "Mismatch",
The fifth identifier should return "NoIssue",

Any help would be great, I'm stuck at this point.

Some lines have hundreds of columns, while others only have one. I'm hoping to be able to locate any line that contains any mismatch.

I'm using SQL Server 2008 R2.


texts:
Currently I have data like this,

CREATE TABLE foo (id, qty, unit_price, date, action) AS
    VALUES
    (1,  2000, 4.01235, '2015-10-10'::timestamp, 'in'),
    (2,   -30, NULL   , '2015-10-11'::timestamp, 'out'),
    (3,  1800, 4.9    , '2015-10-25'::timestamp, 'in'),
    (4, -1000, NULL   , '2015-11-12'::timestamp, 'out'),
    (5,  -980, NULL   , '2015-11-20'::timestamp, 'out');

I need to get calculated avg price for outgoing rows so that result look like this.

id |  qty  | unit_price |    date    | action
----------------------------------------------
1  |  2000 |    4.01235 | 2015-10-10 | in
2  |   -30 |    4.01235 | 2015-10-11 | out
3  |  1800 |        4.9 | 2015-10-25 | in
4  | -1000 |    4.01235 | 2015-11-12 | out
5  |  -980 |    4.02141 | 2015-11-20 | out

unit_price is calculated for outgoing in this way:

for id=2, since there's 2000 unused from only one income the result unit_price is the same as first income row id=1
for id=4, there's still 1970 unused and it's bigger than needed (-1000) - so result unit_price is still the same as first income row id=1
for id=5, there's left only 970 with the first in action, but theres more with in action id=3 1800. So basically i could use -970 with price 4.01235 and -10 with price 4.9. So as result i get the avg price for this row like this ((-970*4.01235)+(-10*4.9)) / -980 = 4.02141 when rounding to 5 decimals.

All the price calculations have to consider date column. Basically it is FIFO calculation logic.

Does anyone have idea how to do this kind of calculation?


texts: Move a database (from decomisioned server) to an existing server with heavy ongoing loads

texts:
We have a situation where one of our database approx. 60 GB in size with transactions running every 2-3 mins 24*7 has to be moved, since the server it is on is going to be decommissioned.

Now, we cannot upgrade the above server to a different environment as its application is compatible with that particular env.

Now, we have 3 similar env, where we thought of migrating this database to. But the challenge is all these 3 exiting DB servers are heavily used. They also have process like transactional replication and log shipping set on them as well. Those servers are all on windows 2003 with SQL 2K5 running on them. I know this should have been upgraded , but for now we want to stick is application does not support the upgrade.

Please suggest from you're experience what can be done here? Should we think to migrate that DB on those heavy servers, using some analysis as moving may require additional storage and CPU or RAM. Or will it be better to cancel the decommission and let that DB reside there?

Edit: I am just looking for recommendations on how to calculate/make a process to meet those parameters say, X amount of RAM/CPU/Storage will be needed if I am going with option of moving the database on the heavy loaded servers


texts: Building Dynamic SQL-Server Where Clause

texts:
Let us review this dba.exchange Oracle question for SQL-Server.

This is SaUce's code, after a little formatting:

CREATE PROCEDURE GetCustomer
    @FirstN nvarchar(20) = NULL,
    @LastN nvarchar(20) = NULL,
    @CUserName nvarchar(10) = NULL,
    @CID nvarchar(15) = NULL
as
begin
    DECLARE @sql nvarchar(4000)

    SELECT @sql = 'C_FirstName, C_LastName, C_UserName, C_UserID
FROM CUSTOMER
WHERE 1=1 '

    IF @FirstN IS NOT NULL
        SELECT @sql = @sql + ' AND C_FirstName like @FirstN '

    IF @LastN IS NOT NULL
        SELECT @sql = @sql + ' AND C_LastName like @LastN '

    IF @CUserName IS NOT NULL
        SELECT @sql = @sql + ' AND C_UserName like @CUserName '

    IF @CID IS NOT NULL
        SELECT @sql = @sql + ' AND C_UserID like @CID '

    EXEC sp_executesql @sql, N'@C_FirstName nvarchar(20), @C_LastName nvarchar(20), @CUserName nvarchar(10), @CID nvarchar(15)', @FirstN, @LastN, @CUserName, @CID
end
go

SaUce mentioned in his second note, that he had 1000 lines of code to prevent SQL-Injection. My feeling is that he needn't do this to shield his stored procedure.

My Question

is this SQL-Server Procedure immune
against SQL-Injection by itself
is it as satisfying solution with
respect to performance.


texts: In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

texts:
I understand the distinction between:

Scalar functions
Set-Returning-Functions (SRF)s
Internal functions
Window functions
Aggregate functions of all sorts
User-Implemented functions (which in PostgreSQL can be implement in any language)
Etc.

In SQL Server "Stored Procedures" are permitted through EXEC. What does that provide over any other function executed with SELECT that returns NULL?

When PostgreSQL gets Stored Procedures what will they bring me, and what is the formal distinction if any between a function and a stored procedure in the spec?

I read this question but it seems to predate the announcement of the implementation


texts: Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock?

texts:
We started an ALTER TABLE query hours ago and only recently realized (via pg_stat_activity) that it is waiting on a lock.  We discovered the other query that is holding a lock on the table we want to alter, and not letting it go.

Our query is a "simple" query (changing a column data type), but it is running on a massive table.

Rather than killing the process that is holding onto the lock, we've decided we'd rather kill the ALTER TABLE.

We did not wrap the ALTER TABLE in a transaction.

As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything.

Is this true?  Is it safe for us to outright cancel our ALTER TABLE query?  Or is it possible that the query has already modified something and cancelling it would leave our database in a halfway state of some kind?

PS: The plan is to cancel it using SELECT pg_cancel_backend(pid);.  If this is a bad idea, please let me know.


texts: SQL INSERT and UPDATE queries run far to slow

texts:
I have two queries that run far too slow and I have no idea why.

Both tables contain about 500000 rows. Each call needs ~20 minutes and the they have a really high CPU usage (~90% on Mac).

I have tested the queries with MAMP MySQL (InnoDB).

UPDATE foo_new as new
JOIN foo_active as active
ON active.id_3=new.id_3
SET active.norf=new.norf,
    active.time_left=new.time_left

-

INSERT INTO foo_active (id_a, id_b, id_c, norf, defg, quantity, time_left, date_saved)
SELECT id_a, id_b, id_c, norf, defg, quantity, time_left, date_saved
FROM foo_new new
WHERE NOT EXISTS (
    SELECT id_a, id_b, id_c, norf, defg, quantity, time_left, date_saved
    FROM foo_active active
    WHERE new.id_c = active.id_c
)

Both tables have the same structure:


texts: Why are correlated subqueries sometimes faster than joins in Postgres?

texts:
This relates to the dataset described in Postgres is performing sequential scan instead of index scan

I've started working on adapting the import logic to work with a more normalised schema – no surprises here it's faster and more compact – but I've hit a roadblock updating the existing data: adding and updating with relevant foreign keys is taking an age.

UPDATE pages
SET id_site = id
FROM sites
WHERE sites.url = pages."urlShort"
    AND "labelDate" = '2015-01-15'

NB pages."urlShort" and sites.url are text fields, both are indexed but currently have no explicit relationship.

There are around 500,000 rows for each date value and updates like this are taking around 2.5 hours. :-(

I looked at what the underlying query might look at:

select *
from pages
join sites on
sites.url = pages."urlShort"
where "labelDate" = '2015-01-01'

This takes around 6 minutes to run has query plan like this:

Hash Join  (cost=80226.81..934763.02 rows=493018 width=365)
  Hash Cond: ((pages."urlShort")::text = sites.url)
  ->  Bitmap Heap Scan on pages  (cost=13549.32..803595.26 rows=493018 width=315)
        Recheck Cond: ("labelDate" = '2015-01-01'::date)
        ->  Bitmap Index Scan on "pages_labelDate_idx  (cost=0.00..13426.07 rows=493018 width=0)
              Index Cond: ("labelDate" = '2015-01-01'::date)
  ->  Hash  (cost=30907.66..30907.66 rows=1606466 width=50)
        ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=50)

There are two things I'd like to know:

Can I adjust the update to run faster based on the above?
What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to find it out?

Here's the output from running EXPLAIN ANALYZE during the update:

Update on pages  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=48181951.584..48181951.584 rows=0 loops=1)
  ->  Hash Join  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=4075.973..1200902.835 rows=1909499 loops=1)
        Hash Cond: ((pages."urlShort")::text = sites.url)
        ->  Seq Scan on pages  (cost=0.00..1056057.95 rows=4037902 width=317) (actual time=0.025..456909.895 rows=2053904 loops=1)
              Filter: ((id_site IS NULL) AND ("labelDate" < '2015-09-01'::date))
              Rows Removed by Filter: 12105346
        ->  Hash  (cost=30907.66..30907.66 rows=1606466 width=41) (actual time=4061.106..4061.106 rows=1606489 loops=1)
              Buckets: 2097152  Batches: 2  Memory Usage: 74179kB
              ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=41) (actual time=0.024..869.068 rows=1606489 loops=1)
Planning time: 3.767 ms
Execution time: 48181966.394 ms

I've tried different memory settings (from 128MB to 1024MB shared_buffers) but they don't seem to make much difference.


texts: The best MySQL Settings for 32GB RAM on a Dedicated Server

texts:
I am worrying about my memory usage as it is reaching more than 90% if I will not restart php-fpm.

I have the following configuration on my MySQL:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 250
table_cache            = 4K
wait_timeout            = 1200

query_cache_limit       = 1M
query_cache_size        = 128M
join_buffer_size        = 2M

log_error = /var/log/mysql/error.log

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 1

expire_logs_days        = 10
max_binlog_size         = 100M

innodb_buffer_pool_size = 5G
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

I am running nginx & php-fpm.

Two of my sites with lots of traffic are configured with this settings:

PHP-FPM pm.max_children: 64
PHP-FPM pm.start_servers: 20
PHP-FPM pm.min_spare_servers: 15
PHP-FPM pm.max_spare_servers: 64
PHP-FPM pm.max_requests: 500

The rest of my sites are configured using the following settings:

PHP-FPM pm.max_children: 64
PHP-FPM pm.start_servers: 10
PHP-FPM pm.min_spare_servers: 5
PHP-FPM pm.max_spare_servers: 15
PHP-FPM pm.max_requests: 500

When I restart mysql server and php-fpm, the initial size of memory consumed is 85%.
I created a script to monitor the consumption of the memory and if it reaches 90% I automatically restarted php-fpm. I don't know if this is a good idea.

When the number of php-fpm server running increase, the memory usage also increased rapidly.

I used the following command to monitor the number of php-fpm server running:

smem -u -t -k

The example output are:

User     Count     Swap      USS      PSS      RSS
web8        22        0  1017.7M     1.0G     1.4G
web2        20        0     1.1G     1.1G     1.3G

The output above is good when the number of server/count does not increase. But when it increase up to 64, which is the highest value of PHP-FPM pm.max_spare_servers. There come's the problem. The memory increased rapidly.

BTW, here's the specs of my server:

Intel® Xeon® E3-1270 v2 Single Processor - Quad Core Dedicated Server
CPU Speed: 4 x 3.5 Ghz w/ 8MB Smart Cache
Motherboard: SuperMicro X9SCM-F
Total Cores: 4 Cores + 8 Threads
RAM: 32 GB DDR3 1333 ECC
Hard Drive: 120GB
Smart Cache: 8MB

Can anybody suggest the best settings for my server?


texts: Searching all Stored Procedures for an update of a specific column

texts:
I'm looking at an audit table created by a trigger, and we were smart enough to save the SUSER_NAME(); we have a column called LastUpdateRef, but not all old code has been updated to use it (it's a unique code to help trace updates back to the code that caused them).

So I know someone is updating, for example, TerminalId in the Transaction table. How would I answer this?

  Show me all the stored procs that have an update statement on the Transaction table that explicitly change the TerminalId

Seems like this would be beyond any RegEx that would be easy to write.

Some updates statements are in this format, where the table name is after FROM:

update a
set
    ArrivalDate = @ArrivalDateLocalTime,
    LastUpdate = SYSDATETIME(),
    LastUpdateRef = 'REFGFI168',
    LastUpdateBy = @UserID
from Flight a where etc...


texts: SQL Server 2008 R2 database mirroring - is it possible to have a secondary server for multiple primary servers?

texts:
Scenario is that I have two servers running SQL Server 2008 R2 (64-bit), let's call them DB1 and DB2. I have set up database mirroring for all the databases on these servers so that DB1 is the principal and DB2 is the secondary.
Now the question is, can I set up database mirroring on a new server DB3 running it as principal and have DB2 work as a secondary for both DB1 and DB3? Or will I need a separate new secondary server (DB4) for DB3? Would this configuration be possible using separate instances on DB2?


texts: Upgrading SQL Server 2019 from CTP to RTM version

texts:
A few days ago Microsoft announced RTM version of SQL Server 2019 finally and we are willing to install and use it on our business intelligence server.

We are already using Microsoft SQL Server 2019 Community Technology Preview 3.0 (CTP 3.0) so I gave it a shot to do in-place upgrade. However, Installation wizard detects CTP version and tries to install a new named instance. Obviously we don't want that.

I checked release notes but couldn't find anything about in-place upgrade support from CTP to RTM.

Questions:

Does anyone tried and succeed in-place upgrade from CTP to RTM version?
Is 'Uninstalling CTP and Installing RTM' the only way?

Update

I tried both Version Upgrade and Edition Upgrade using Installer but both didn't help.

It looks like uninstalling CTP components and then installing brand new Enterprise RTM is the only and correct way to do this.


texts:
I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"?

Thanks!


texts:
I need to use 2 order by in my procedure.

When I use only the second order by is working and not the first one.

What mistake am I doing?

Here is my procedure

BEGIN

SELECT
    sd.ShopID,
    @dist:= ( 6371 * acos( cos( radians(lat) ) * cos( radians( sd.Lat ) ) * cos( radians( sd.Lng ) - radians(lng) ) + sin( radians(lat) ) * sin( radians( sd.Lat ) ) ) ) AS distance
    FROM
        shop_details sd
    WHERE
        sd.Status = '1'
    Order by
        sd.Status Asc,
        distance Asc;
    END

(Don't care about the @dist formulae, as it will return the distance)

In the end I want to display the status as Ascending and distance as Ascending.

While I use

Order by
    sd.Status Asc,
    distance Asc

I got only distance as Asc and I got the sd.Status is not in ordered. I tried it vice versa but I got the same issue.

How can I do this without using a separate table like in this Question?

I tried this way.


texts: Any security related reasons to prefer Windows Authentication over Sql Server Authentication?

texts:
This is a rather short question:

When using Microsoft SQL Server (version 2005 and newer), are there any security related reasons to prefer Windows Authentication over SQL Server Authentication?

Just to point it out, I'm interested in security related concerns, not in administrative or any other differences between the two.

Update: If any difference leads to (or is) a security concern, then I'm surely interested in.


texts: What is the performance benefit when using a temporary table to update a regular table?

texts:
I'm trying to understand what the performance benefit between two different methods of updating multiple table rows.

For the first method we have an explicit update statement for each line to be altered.

i.e.

 update my_table set my_field='A' where id=1;
 update my_table set my_field='B' where id=2;
 update my_table set my_field='C' where id=3;
 update my_table set my_field='D' where id=4;
 update my_table set my_field='E' where id=5;

For the second method we use a temporary table.

i.e.

 create temporary table my_temp_table (id,val);

 insert into my_temp_table value (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');

 update my_table t, my_temp_table tt set t.val=tt.val where t.id=tt.id;

I've been told that the second method is more efficient and thus superior.  I just don't understand why this is so.

Sure we have 5 queries vs 3 queries but that last query is essentially still doing all those updates, albeit implicitly?  Plus it adds in a join too.

So I'm just curious as to why using a temporary table is better?  Is it simply 3 is less than 5, and as such, is better?

EDIT: We are using InnoDB and not explicitly using transactions.  This is just a one off batch update pushed into MYSQL via a file.


texts: SQL Server SSIS issue retrieve data when I enter into login of server side?

texts:
I am failing to retrieve data when I enter into login of server side.
Here is the error message
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click:
------------------------------
ADDITIONAL INFORMATION:

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

------------------------------

The server principal "dbkvsstaging" is not able to access the database "vssgis1" under the current security context. (Microsoft SQL Server, Error: 916)

For help, click:
------------------------------
BUTTONS:

OK
------------------------------

I tried to follow this article to solve this problem, which suggested:

Step 3: You will see a panel will open at the right hand side,
Step 4: Right click on the header part of that panel, and unselect all option, as shown in the image,

However, I didn't find any panel when I pressed F7. I found object explore detail only, but  didn't  find the listpanel on my pc at the right hand side, as mentioned in Step 4.


texts: Setting NLS_LENGTH_SEMANTICS for session via trigger

texts:
I have the following problem with my Oracle 12c database.

I have to install a third party application, which needs an Oracle database instance. The supplier demands to set the NLS_LENGTH_SEMANTICS parameter to CHAR. However, Oracle strongly recommends not to change it globally from BYTE to CHAR.

My solution was to create a trigger, that changes the parameter for the session. The trigger looks like follows:

create or replace TRIGGER SYS.ALTER_NLS_FOR_SAPIENT
AFTER LOGON on DATABASE
BEGIN
    IF ( user like 'SAPIENT%' ) THEN
        execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR"';
    END IF;
END;

Unfortunately, the trigger does not change the session parameter. I suspect the trigger doesn't fire up, so I ensured it by writing a file onto disk. It's been definitely triggered.

If I change the session parameter on e.g. SQL developer from client side, the parameter is set.

ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR";
select * from NLS_SESSION_PARAMETERS where parameter='NLS_LENGTH_SEMANTICS';

Does anyone know why it's not possible to set the NLS_LENGTH_SEMANTICS parameter from server side?

System information


texts: Normalization vs denormalization for these kind of data?

texts:
I'm using Firebase realtime database (NoSQL), And trying to figure out the best database design for my data.

My data is primarily about discounts, each affiliator introduces a number of discount offers on several stores.

So I have an affiliator_category which maybe banks, clubs or organisations. And each category contains a number of affiliators and each affiliator contains number of stores divided into store_category, each store has a number of offers.

And for the user side (the one who do the shopping). A users has a number of memberships in several affiliators, and a number of spendings he/she does.

I'm thinking of two ways to design the database.

The first one:

Each player is separate, has its own table (or node), for example, I have affiliators table that has a column for affiliator_category table row id. Basically I'm writing a normalized database.

The seconde way:

Is to denormalize the database. I have an affiliator_category node that's the parentof affiliators, which contains a number of stores. The data is lied out just like a normal JSON.

I'm searching for the advantage and disadvantage of both methods. The database is to be used for a mobile app.

So if I used the first method I'll have to query for the data on each node, and query for the related nodes by the unique id of the first node and so on.

If I used the second method (since i'm using a number of filters and search criteria on the app) I'll have to retrieve the parent node and do the searching by writing codes to iterate inside the returned object and accumulate results in an array and so on.

I don't know which is the best way to design the database, each method has its own advantage, the first one will result in less coding and more data consistency but lower performance, The second method will result in way more code, complicated object inspections and searching for values inside object fields. But i'll gain higher performance and scalability.


texts: How can I prove that the following T-SQL is bad for performance?

texts:
I have recently inherited a codebase with a large amount of stored procedures. The system they are supporting is encountering numerous performance problems which I am looking in to.

A number of the stored procedures have a pattern like this:

Create Temp table
Build up dynamic SQL query to insert a bunch of records, e.g.

DECLARE @sql VARCHAR(MAX)
SET @sql = 'INSERT INTO @tempTable
SELECT SomeColumn, SomeColumn2, SomeColumn3, etc FROM MyTable'

IF @someParam = [SomeValue]
    SET @sql = @sql + 'WHERE SomeColumn = [SomeValue]';

IF @someOtherParam = [SomeOtherValue]
    SET @sql = @sql + 'WHERE SomeOtherColum = [SomeOtherValue]';

Execute this dynamic sql

EXEC(@sql);

Select from the temp table and bring in a bunch of additional information to return to the client.

SELECT
   ...
FROM
   @tempTable
INNER JOIN ...

My immediate thoughts are:

There is dynamic SQL, so no cache plans, meaning plans generated every time.
There is an INSERT SELECT pattern, so table locking is more likely to be an issue.

I have re-written some of the stored procedures in this way instead:

SELECT
       ...
FROM
      MyTable
INNER JOIN ...

WHERE
    (@someParam != SomeValue
     OR
     SomeColumn = SomeValue)
    AND
    (@someOtherParam != SomeOtherValue
     OR
     SomeOtherColumn = SomeOtherValue)

From comparing execution plans and client statistics in SQL Server Management Studio, I have not sped the stored procedures up so I am apprehensive about suggesting wholesale re-writes of all stored procedures.

I am trying to set up some profiling of a live customer scenario, but as yet have been unable to prove my thoughts.

Can anyone offer any confirmation of the theory behind my thoughts, or any better ways of proving my suspicions?

The problem is I have read that dynamic SQL is not always a closed case - i.e. it depends on how it is used. My understanding of locking also falls down at the fact that nowhere can I get 100% confirmation of how this type of INSERT SELECT will lock tables.


texts:
I have two servers, SQL Server 2012.

I have snapshot replication set up. Pull subscription. Publisher, distributor, same box, subscription is a different box. Lately, I have been running into some issues with this. Usually, the replication finishes the entire process in 2 hours and completes the bulk copy on subscriber.

Lately, there have been delays and it seems that replication hangs and a message pops up on Replication monitor saying that it is waiting for a response from the server. It always hangs up for 3 hours, tries to get going, hangs up for another 2 hours and finally finishes up in the morning. Each time it has delayed, it follows the same pattern.

I ran whoisactive to find if any process blocks our snapshot repl but did not find any. Checked the processors and memory but they are nowhere near 100% when it is blocked. I have 16 processors and 32GB ram on the box. Would like to add that MADOP is 8 and cost threshold for parallelism is set to 50.

In my research, I have found out people saying that it hangs when bulk copying larger tables which is true but then again, why does it hangs only 10% of the times and runs fine the other 90%. Any insight is truly appreciated.


texts:
I want to reduce the size of a table.

I thought if I could do this with export / import, but when I check it
with the ESTIMATE_ONLY option, it doesn't seem to work.
I have no chance of using partitioning.
If I do it with CTAS (create table as select), the archive production
will peak and the disaster database will be affected.
How can I reduce the size of this table? If I do shrink, will UNDO be
enough and how much space will be enough to add UNDO tablespace to
overcome this problem? Do you have an estimate of how long it will
take if I shrink?
I have to be very careful as it will be done directly in the
production database.

Version: Oracle 11g Release 2 - Standard Edition
ASM option: yes
Cluster: yes
Partitioning option: no
Compress data pump option: no

Table size information:
SQL> select segment_name,sum(bytes)/1024/1024/1024 GB from dba_segments where owner='OWNER_NAME' and segment_type='TABLE' and segment_name=upper('TABLE_NAME') group by segment_name;

SEGMENT_NAME                                                                              GB
--------------------------------------------------------------------------------- ----------
TABLE_NAME                                                                        392.493164

Export information (It does not work in reducing the table size.
):
nohup expdp "'/ as sysdba'" directory=DP_DIR tables=OWNER_NAME.TABLE_NAME ESTIMATE_ONLY=Y &

Output:
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DP_DIR tables=OWNER_NAME.TABLE_NAME ESTIMATE_ONLY=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "OWNER_NAME"."TABLE_NAME"            392.4 GB
Total estimation using BLOCKS method: 392.4 GB

Best regards,


texts: upgrading sql server 2000 database to sql server 2012

texts:
Seeking some advice about an oudated database system we're working with.... hope this is an appropriate place to post this question.

I work with a city government arts program.  In 2004, we contracted with a local database developer to develop a system that allows us to track student participation in our after school art classes.  It tracks each student's address, demographics, what classes they enroll in, daily attendance, paperwork/documentation, class progress,  etc.   It was built on SQL Server 2000.

Because of issues with the city's technology systems back then we were not able to install it right away.  When they finally got around to supporting us (I think around 2006 or 07), they gave us SQL Server 2005.  We ended up installing an instance of SQL Server 2000 on SQL Server 2005 which itself runs on Windows Server 2003.

As of a few years ago, we are no longer under the city's technology systems and manage our own systems through independent contracted consultants.  Last year, we terminated our contract with the original database developer because he wasn't following through on a number of issues we needed addressed over the years.  Last year, we also went through a major overhaul of all our servers and desktops.

We've upgraded all  our other servers but we still have one server left that's still running SQL server 2000 and Windows 2003 just  because of this one outdated db system.  We'd like to upgrade both SQL Server to 2012 and Windows Server  to 2008R2 on this last server.

I'm trying to budget for how many hours it would take to upgrade a SQL Server 2000 database so it will run on SQL Server 2012. I realize you can't really tell much without actually seeing what we're working with, but all I'm looking for now is a ballpark figure (i.e., is it in the 20-30 hour range? 50 hours? 100 hours?).

If there is any other info I can provide to help get at an estimate feel free to post a comment....

Thanks for any help!


texts: How to create pg_trgm compound indexes with date columns

texts:
SELECT col1, max(date1) as max_date
FROM table
WHERE col1 ILIKE 'name'
GROUP BY col1

TYPES:Here col1 is varchar and date1 is timestamp with time zone data type. So created extension CREATE EXTENSION pg_trgm

Then tried the following indexes and got the errors:

1: Issue: ERROR:  operator class "gin_trgm_ops" does not accept data type timestamp with time zone

CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
ON table
USING gin (provider_id, date1 gin_trgm_ops);

2: Issue: ERROR:  operator class "text_pattern_ops" does not exist for access method "gin"

 CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
    ON table
    USING gin (provider_id, date1 text_pattern_ops);

How can I create an index for the above query for faster execution? Any help will be really appreciated!

EDIT:


texts: How to simplify schema for multi database / hundreds of millions of rows data set

texts:
We currently have a MySQL data set that was started about 10 years ago that aggregates a log of data, and accumulates millions of rows each month.  The people that developed our system started separating the data into months, however they did this by creating an entirely new database with that month name (e.g. jan2001, mar2009, aug2012), and one table in each database named "detail".  Each month a cronjob kicks off and spins up and entirely new database, with the table "detail", and the application starts writing the data to said table.

At this point the combined number of rows in all the "detail" tables is 448,231,722.  This data is only used for analytics purposes at this point, so the speed of the query returning the data does not need to be instantaneous.  One assumption we have is that the data will never be deleted.

I am wondering if you could suggest a better method of condensing or consolidating the data into a more manageable schema?


texts:
Given following query -

SELECT name, age FROM students WHERE age > 22

The result is -

name    age
----   ---
Kshitiz 23
Steve   22
Paul    24

How can I number the results sequentially? Like so -

serial  name    age
------  ----    ---
   1    Kshitiz 23
   2    Steve   22
   3    Paul    24

Please note that using surrogate key is not a solution. Because the surrogate id for record Kshitiz might be 9. I want whatever results are returned from whatever location to be numbered sequentially.


texts: Need to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2

texts:
My requirement is to create the automated email as ( A formatted HTML Email via Database Mail) The basic requirement has been completed where when the Job is triggered it inserts the data into a Table and that information has been scheduled to be sent as an Email,but now the client has requested in this format.

Current Format that I have done

 PO Date:2014-11-26 PO ID:PO1 SAP Ref:0001106102 GRN:1067 DealerID:045 Dealer Name:ABC(Pvt)Ltd. 2 Status:New

Format Requested By Client in a table like format

PO Date |PO ID| SAP Ref| GRN|DealerID|Dealer Name|Status

This is the SQL Query that I use to Insert the Data in to the Email_Table(_ERROR_MAIL) and this needs to be coveted according to the requirement

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ImpCancelledGRN')
BEGIN
    DROP PROCEDURE [dbo].[ImpCancelledGRN]
END
GO

CREATE PROCEDURE [dbo].[ImpCancelledGRN]
WITH ENCRYPTION
AS

SET NOCOUNT ON
BEGIN
BEGIN TRY
    -- Begin transaction
    BEGIN TRAN

            INSERT INTO _ERROR_MAIL(Recipients, Subject, CreationDate, IsNew, LastModifiedBy, Body)
            SELECT 'noreply-Email@Adress.com', 'SAP CANCELLED GRN', GETDATE(), 1, 'sapws',
                'PO Date:' + CONVERT(VARCHAR(10),P.Date,120) +
                ' PO ID:' + P.ID +
                ' SAP Ref:' + P.ID2 +
                ' GRN:' + G.ID +
                ' Dealer ID:' + D.ID +
                ' Dealer Name:' + D.Name +
                ' Status:' + CASE WHEN G.SubmittedDate IS NULL THEN 'New' ELSE 'Dealer Submitted' END
            FROM I_CancelledGRN I
                INNER JOIN TxnGRN G ON G.ID = I.ID
                INNER JOIN Distributor D ON D.UID = G.DistributorUID
                INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID AND P.UID = G.POTxnUID
            WHERE IsCancelled IS NULL;

            UPDATE TxnGRN
            SET ExpiryDate = GETDATE()
                , SAPCancellationDate = I.Date
                , SAPCancelledBy = 'SAP'
                , IsCancelled = 1
            FROM I_CancelledGRN I
                INNER JOIN TxnGRN G ON G.ID = I.ID
            WHERE IsCancelled IS NULL;

    -- Commit transaction
    COMMIT TRAN

END TRY
BEGIN CATCH
    -- Get error message, severity and satus information
    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),    @ErrorState = ERROR_STATE();
    -- Rollback transaction
    ROLLBACK TRAN;
    -- Log error message details
    INSERT INTO _ERROR_LOG(Module, SubModule, Text, Date)
    VALUES('SAP', '[ImpCancelledGRN]', @ErrorMessage, GETDATE());
    -- Raise error
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
RETURN 0;
END
SET NOCOUNT OFF
GO


texts: How to inquire about current value of MAX_EXECUTION_TIME

texts:
I understand that I can set the value of the global MAX_EXECUTION_TIME by running (from mysql client):
SET GLOBAL MAX_EXECUTION_TIME

But how do I just inquire its value?
GET instead of SET does not seem to accomplish that.


texts: Can I do a conditional JOIN or conditional SELECT based on the data in a table?

texts:
I think I have a problem where EAV is the best choice.

I am planning my tables as follows.

motherboard

motherboard_id | model_name
int            | varchar

Example:

motherboard_id | model_name
----------------------------
1              | Asus Rampage
2              | Intel 3X
3              | Gigabit 22P

attribute

attribute_id  | attribute_name | attribute_type
int           | varchar        | enum('str','int','float')

Example:

attribute_id   | attribute_name| attribute_type
-------------------------------------------------
1              | form_factor   | int
2              | max_ram       | int
3              | ram_type      | str
4              | weight        | float
5              | CPU_socket    | str
6              | PCH           | str

motherboard_attribute_int

attribute_id  | motherboard_id | value
int           | int            | int

Example:

attribute_id   | motherboard_id | value
-----------------------------------------
1              | 1              | 1
2              | 1              | 16384
1              | 2              | 1
2              | 2              | 32,768
1              | 3              | 2
2              | 3              | 8192

motherboard_attribute_str

attribute_id  | motherboard_id | value
int           | int            | varchar

Example:

attribute_id   | motherboard_id | value
--------------------------------------------
3              | 1              | DDR3
5              | 1              | LGA2011
6              | 1              | Intel P55
3              | 2              | DDR3
5              | 2              | LGA3011
3              | 3              | DDR2
5              | 3              | Socket 2

motherboard_attribute_float

attribute_id  | motherboard_id | value
int           | int            | float

Example:

attribute_id   | motherboard_id | value
------------------------------------------
4              | 1              | 250.2
4              | 2              | 250.2
4              | 2              | 110.5

I will have to query on all attributes (use WHERE clause). For example -

  Find all motherboards with weight >150g and max_ram>16M

So, I need to query the attribute table and discover that max_ram in an int and weight is a float. So, I have to join motherboard with motherboard_attribute_int and motherboard_attribute_float

Is there a way to do this?

Is there an alternate database design that would serve this kind of data better?


texts: user "sa" cannot connect to SQL Server Express 2008 R2

texts:
Goal: connect to SQL Server Express 2008 database using SQL Server authenticated account "sa", rather than my Windows authenticated account.

When installing SQL Server 2008 Express, I took all default options except when filling in the Service Accounts.  It did not allow me to enter "sa" as the Account Name for the "SQL Server Database Engine" service.  So I found my network user {NETBIOS DOMAIN\LOGIN} and entered that id along with my network password.  I then chose the default "Startup Type" of Automatic.  That allowed me to complete the installation.

Normally, on Windows XP or Windows 7, SQL Server Express installs fine.  This install was on Windows Server 2008.

When I created a new database, I noticed my network name showed up under the database properties.  Database Properties > General > Owner.  So I reset that user to "sa" for that one database.  Creating new databases defaults it to the value I initially set during install.

I used this command to reset that user.

EXEC sp_changedbowner 'sa'

The latest version of SQL Server Express (downloaded today) also defaulted the "sa" login to  Disabled (Database Server > Security > Logins > sa > Login Properties > Status > Login: Disabled).  So I enabled it.

http://sarangasl.blogspot.com/2011/06/sql-server-authentication-enabling.html

I still cannot log into the database server ({MACHINE NAME}\SQLEXPRESS) with account sa.  I event reset the password (Database Server > Security > Logins > sa > Login Properties > General > Password & Confirm Password).  What am I missing?


texts: Reporting Services will not connect to SQL Server

texts:
I have set up a new SQL Server 2014 default instance.  In SQL Server 2014 Configuration manager I see my 32 and 64 bit SQL Native Client 11.0 configurations and all protocols, pipes, tcp/ip,shared memory  are enabled for them all.  When I try to make a new data source to my SQL Server 2012 instance, everything is fine.  When I try to make a connection to my SQL Server 2008 R2 server, I get the generic:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have used Microsoft SQL Server driver with connection strings:

data source = "cha-sql-01"; initial catalog = Paymentbanc
data source = 'cha-sql-01'; initial catalog = Paymentbanc
data source = "cha-sql-01"; initial catalog = Paymentbanc;
data source = 'cha-sql-01'; initial catalog = Paymentbanc;

I checked the SQL Server 2008 R2 server and all protocols were enabled.  The browser was off, so I turned on the browser, made sure that sqlcmd -L saw the server, which it did, then tried and got the same results.

I then tried to connect via IP address:

data source =x.x.x.x,1433;Initial Catalog=xxxxx;User ID=xxxxx;Password=xxxxxxx;
Also tried a domain account

Next, I thought the issue was that SQL Server 11.0 would not be backwards compatible, so I loaded the SQL Client 10.0 client on the reporting server.  I went into ODBC sources, added the client 10.0 and configured it for my SQL Server 2008 R2 server.

In ODBC administrator, I can use both SQL Server and SQL Server Native Client 10.0 to connect to the server.  So, what am I missing to get the reporting service SQL Server client to connect?  No errors other than the teradata & SQLPDW

This is a brand new server on Windows 2012 R2 running SQL Server Standard Edition 2014 SP1.


texts: Mysql - Replication of multiple database

texts:
I'm trying to do a redudancy with Mysql.
I'm making master-master.
I'm very happy because it works with one database. When I add something in a table, it's added on the table of the second server. The 2 DB are NOT on the same server.
 but I want to add other database.

So, I add this to both server:

replicate-do-db = other
binlog-do-db = other

I restarted mysql, but the result of "show master status" is the same (specially "Binlog_Do_DB" (and the database isn't in both server).


texts: Oracle XE 12c won't configure - getting the "port 5,500 in use" error message

texts:
I have the infamous "port 5,500 in use" error message while trying to start/configure Oracle XE 12c.

I have searched for this problem and every solution I have found has been the same - make sure the hostname matches between /etc/hostname and /etc/hosts. I have done that, using grep -w to confirm the strings are the same, and they are, but the problem is not resolved.

/etc/hostname

iv4x.rdu.csb

/etc/hosts

127.0.0.1   iv4x.rdu.csb localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

The difference I see with the above is I didn't have a non-loopback IP address in /etc/hosts.  I attempted using those addresses reported by "ip a", but all of those failed as well, e.g.:

/etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.122.1   iv4x.rdu.csb

I should add that I have used netstat to search for anything using that port and I found nothing. I have also tried configuring Oracle to use different ports and received the same error message for any port that I tried.

I would appreciate any suggestions, thanks.


texts: Use array of composite type as function parameter and access it

texts:
I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table.

This is my type:

CREATE TYPE Books AS (
V_Book_ID NUMERIC,
V_Row_Num NUMERIC,
V_Book_OWNER TEXT,
V_Book_OWNER_ID TEXT
);

This is my function:

CREATE OR REPLACE FUNCTION Update_Table(row_book Books[]) RETURNS TEXT AS $$
DECLARE
   Status TEXT;
   I_Max integer := array_length(row_book, 1);
BEGIN
FOR I in 1..I_Max
  LOOP
   INSERT INTO books_table(Book_ID,
   Row_Num,
   Book_OWNER,
   Book_OWNER_ID)
   values
   (row_book[I].V_Book_ID,
   row_book[I].V_Row_Num,
   row_book[I].V_Book_OWNER,
   row_book[I].V_Book_OWNER_ID);
END LOOP;

   STATUS:='Saved';
exception when others then
   STATUS:='failure';
   RETURN STATUS;

END;
$$ language plpgsql;

How do I send data to the function or how should I call the function with data?


texts: Problem sending an attachment using dbo.sp_send_dbmail

texts:
I am trying to send an attachment using dbo.sp_send_dbmail.
I can't do this
I get the error
Attachment file \VMName\ListTEAM\LCPP_CP.pdf is invalid.

Just some quick things that I have checked.
I have tried various naming conventions.
I have shared the folder that the attachment is in.
I checked the Log On As in the Services MMC on the VM that the SQL database sits on.
The Log On As for SQL SERVER and SQL SERVER AGENT is SQLReportingAdmin@lan.domain.com
I have given this user SQLReportingAdmin@lan.domain.com access to the directory that the file that I want to send as an attachment is in.
I have triple checked the accuracy of the locations.
The user SQLReportingAdmin@lan.domain.com now has read and execute permissions on C$ on the relevant VM.

The file LCPP_CP.pdf is 72 KB.

The file LCPP_CP.pdf is not read only
The profile is public

I can send an email without an attachment.
The send_request user in dbo.sysmail_mailitems is domain\SQLReportingAgent

My questions are

Is there a stored procedure that I can use to check the SQL Engine logged on user?
Is it possible that msdb.dbo.sp_send_dbmail is not running under the SQL SERVER  account?
If so is there a stored procedure that I can use to check what user msdb.dbo.sp_send_dbmail is using
Is it possible that this isn't a permissions issue?

My code is below but it is really just boilerplate; the commented lines are the various permutations that I have tried.

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'valid@emailaddress.com',

@body= 'Test Email Body',

@subject = 'Test Email 18:01',

@profile_name = 'Team Meeting Profile'
--@file_attachments ='\\VMName\c$\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments =N'\\VMName\c$\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='\\C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments =N'\\C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments =N'C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='\\VMName\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='\\VMName\ListTEAM\LCPP_CP.pdf'


texts: Close all connections to postgres without having a connection

texts:
I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague  log in to run some manual queries and forget to log out, my script will fail

FATAL: too many connections for role

I need to run some command within either crontab or the script itself to close any connections for my user before attempting to run my script.

Something like

select pg_cancel_backend(pid)
from pg_stat_activity
where pid <> pg_backend_pid();

isn't helpful because to do so, my script would have to be able to make the connection.

Any option that doesn't require being logged in already or being an administrator of the db should work as the script should take priority over anything and the only users of this role are users that should defer to the script running on time.

Thanks.


texts: how to find oracle calculated/computed column list using data dictionary views

texts:
I would like to find computed column list in oracle database using Oracle Data Dictionary Views.

I would like to add more information. Suppose I have following computed/calculated column in database.

ALTER TABLE HR.EMPLOYEES
 ADD FULL_NAME AS ( FIRST_NAME || ' ' || LAST_NAME);

I can select this column value in Select Statements.

SELECT EMPLOYEE_ID,FULL_NAME FROM HR.EMPLOYEES;

Results

  ...
   174 Ellen Abel
  ...

I can see this column in TAB_COLUMNS view.

 select * from all_tab_columns   C
 WHERE
 1 = 1
 AND  C.table_name = 'EMPLOYEES'
 AND C.OWNER = 'HR'
 AND COLUMN_NAME = 'FULL_NAME'
 ;

I would like to find given schema, table and column find that if this column is calculated/computed?

Wrongly I thought that INDEXES view gives me this information. But following select returns no rows. This gives only functional indexes.

SELECT index_name,index_Type,I.*
FROM ALL_indexes I
WHERE
1 = 1
AND INDEX_TYPE LIKE 'FUNCTION-BASED%'
AND I.OWNER = 'HR'


texts: Exporting MySQL table data: "into outfile" fails, directory stays NULL after editing variables in cnf files

texts:
I'm pretty new to SQL, MySQL and Docker. I'm running a Docker MySQL container (I believe).

My problem:

In MySQL, the command

select * from [tablename] into outfile [csv-filename];

fails with the message

  The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

What I've tried:

select @@GLOBAL.secure_file_priv;

outputs a value of NULL.

Then, I changed/added the line

secure_file_priv= "/home/"

into both of the following files within the container:

/etc/mysql/my.cnf
/etc/mysql/conf.d/docker.cnf

Still, "select @@GLOBAL.secure_file_priv;" in MySQL returns NULL and "(...) into outfile (...);" fails as described above.

Any clues are much appreciated!


texts: Version store blowing up, but responsible sessions have no open transactions

texts:
Tempdb is growing and it's all version store data.  When I run:

select hostname,elapsed_time_seconds,session_id, transaction_id, is_snapshot, blocked, lastwaittype, cpu, physical_io,  open_tran, cmd
from sys.dm_tran_active_snapshot_database_transactions a
join master..sysprocesses b
on a.session_id=b.spid
order by a.elapsed_time_seconds desc

This shows two sessions that have elapsed_time_seconds nearly 400,000 (~4.5 days).  However, the open_tran column for these sessions is zero every time I check.  The application is using implicit transactions--not sure if that is relevant.

Also, the hostname that is reported for one of these sessions has changed (no, neither the IP addresses nor the host names have been changed) from this morning.  So it seems that maybe the client that had that session id this morning disconnected, and another client has that session id now.

Even though it shows no active transactions for these sessions most of the time, I find transactions that match transaction_id in sys.dm_tran_active_transactions with the following properties:

transaction_begin_time: [~4.5 days ago]
name: DTCXact
transaction_type: 4
transaction_state: 2
transaction_status: 12
transaction_status2: 386
dtc_state: 1
dtc_status: 0
dtc_isolation_level: 4096

Is there a way to explain what I'm seeing?  If there are no open transactions, why would they still have something active in the version store?

SQL Server 2014 SP2 12.0.5214.6


texts: Slide Partition from Rowstore to Columnstore

texts:
I have a rowstore table which contains ~10 billion records.  The table is partitioned on an INT field and has about 1,400 active partitions. I also have a clustered columnstore table built on the same Partition Scheme / Function. Can I switch an active partition from the Rowstore table to the Columnstore table?

ALTER TABLE [dbo].[RowstoreTable] SWITCH PARTITION 741 TO [dbo].[ColumnstoreTable] PARTITION 741

This database resides on SQL Server 2014 Enterprise Edition.


texts: How to sort strings with special characters in Hibernate?

texts:
For example, if the Order in criteria is ascending and if we have two names like follows:

_zname_9
1_aname_

I get an output (from hibernate result) like:

_zname_9
1_aname_

But the expected output (java sorting) is:

1_aname_
_zname_9

Am I missing something? Please note that I dont want to escape special characters while setting the criteria.

Can we mention regarding special characters while sorting?