texts: Should I Denormalize Tables To Avoid Joins?

texts:
I'm in the process of developing a web application, and need some help with some basic db design.  Currently, I am unsure of the following three tables:
### User ###
 - Id
 - DetailId
 - etc... (About 6 more columns)

### Contact ###
 - Id
 - UserId
 - DetailId
 - etc... (About 6 more columns)

### Detail ###
 - Id
 - FirstName
 - LastName
 - etc... (About 10 more columns)

I am starting to wonder if it may be best to just eliminate the Details table and simply denormalize those columns back into the User and Contact tables.  Being that these tables will be read much more than they will be edited or updated, this denormalization should improve read performance by avoiding a table join. Is this a sound judgement or am I just falling for the root of all evil (Premature Optimization).  Either way, which would be considered a better practice?
Edit/Update
Thanks for the great feedback!  I realize I should have been a bit more descriptive, so here is some additional information on the use case for these tables.

The Details table contains mainly display information, like name, address, phone number, email, but this is NOT account information.  Instead it is more like print information, that will be used when the user prints quotes, invoices, etc...

Details table contains only columns that are duplicated in both a user and contact in terms of printing data.  So if I denormalize those columns back to the User and Contact table, any edits in column definition (or adding/removing columns) will have to be made twice.

The Users table may only have 100 records, but the Contacts and Details tables could potentially hold hundred of thousands, because each user can have hundreds of contacts.
But to be clear, a Detail "should" only belong to one Contact or User. However, a User can have many Contacts. So If there are 100 Users and each User has 100 Contacts, that's 10,000 Contacts, and 10,100 Details.


texts: Are there any potential problems with running distinct Oracle rman archivelog backups of the same database at the same time?

texts:
The scenario is that RMAN archivelog backups are scheduled to tape (TSM) every 2 hours. The scheduling ensures that such backups are run in a mutually exclusive fashion.
A separate crontab based mechanism has been put in place (by an unrelated team) with the result that "identical" RMAN archivelog backups are sometimes run at the same time.

Are there any potential problems with this, as regards to subsequent restore operations?


texts:
I am trying to install SQL Server 2008 x64 on Windows 2008 R2 and keep getting the following error:

  SQL Server Setup has encountered the following error: Element not
  found. (Exception from HRESULT: 0x80070490)

I have applied all required patches and there are no other instances of SQL Server on the machine.

Any clues as to what the cause might be?

Thanks.


texts: Need ODBC connection to postgresql to include materialized views

texts:
I have an Access front end user interface that links to tables in a postgreSQL database using the most recent ODBC driver. I can link to the postgreSQL tables and views but can't get to the materialized views.

This thread describes a patch to the driver to include materialized views. Does anyone know if this patch is included in the most recent driver? If not, how would I apply it? If it is included, which options need to be selected so that materialized views are accessible from MS Access?


texts: MongoDB not performing well when set Read from secondary

texts:
I have a mongoDB database, with 1 primary and 1 secondary in replicaset(and 1 arbiter). Both the servers are hosted on aws on c4.2xlarge instances(with 1.5 TB of SSD on each).
Mongo version of primary server is 3.4.9 and secondary is 3.4.10. This is the only difference between two instances(version diff).

The issue is whenever I start reading from the secondary server(i.e set READ PREDERENCE SECONDARY on my application), MongoDB starts working slow or inefficiently(queues start to increase in rabbitMQ and processes start taking time), and when I make my application to read-and-write both from primary only, it works perfectly.

What can be the issue or possible cause(with the given information)? Has anyone else too has faced the same or similar issue?
I know this sounds random, or vague, but I am 100% sure, the issue is with reading from secondary. I will try to get some more logs for the same. But currently I have only this much information only.(And there no lag between two, in any case,whether reading from secondary or not)

Thanks in adavance.


texts: SSD used for tablespace died. How can I recover data?

texts:
I have a postgres cluster with two databases

Database A uses the default tablespace. It holds important information but has very few writes per day (< 20) and only has a few tables with a few thousand rows of data.
Database B is on its own tablespace on a separate SSD. It has hundreds of GBs of data and adds millions of rows per day.  The data is for analytics and is not important.

Recently the SSD holding Database B's tablespace died.  Postgres will no longer start up.  My priority is dumping the data from Database A.

I was thinking that because Database A has few writes or deletes per day, it would be fairly safe to run pg_resetwal, then dump Database A.  After dumping Database A I would re-install postgres and load the dumped data from Database A.

Is there an alternative method to recover my data.  Are there any obvious problems with my plan?

(I know that pg_barman should be used to prevent these problems, but my client has refused when I suggested pg_barman after a similar failure in the past. And, yes, RAID would obviously be better than a single SSD, but I don't get to make the hardware decisions)

Error log when attempting to start postgres:

2020-03-13 16:20:16.678 PDT [55834] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-03-13 16:20:16.686 PDT [55834] LOG:  could not open directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:16.688 PDT [57190] LOG:  database system was interrupted while in recovery at 2020-03-13 16:19:29 PDT
2020-03-13 16:20:16.688 PDT [57190] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2020-03-13 16:20:17.016 PDT [57190] LOG:  could not stat file "pg_tblspc/16394": No such file or directory
2020-03-13 16:20:17.134 PDT [57190] LOG:  could not open directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:17.135 PDT [57190] LOG:  database system was not properly shut down; automatic recovery in progress
2020-03-13 16:20:17.135 PDT [57190] LOG:  could not open directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:17.136 PDT [57190] LOG:  redo starts at 7C1/93EB0EA0
2020-03-13 16:20:17.136 PDT [57190] FATAL:  could not create directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:17.136 PDT [57190] CONTEXT:  WAL redo at 7C1/93EB0EA0 for Sequence/LOG: rel 16394/26819/26877
2020-03-13 16:20:17.136 PDT [55834] LOG:  startup process (PID 57190) exited with exit code 1
2020-03-13 16:20:17.136 PDT [55834] LOG:  aborting startup due to startup process failure
2020-03-13 16:20:17.139 PDT [55834] LOG:  database system is shut down

Posgresql version is 11.2


texts: PHPMyAdmin for multiple servers w/ single pma database

texts:
We have PHPMyAdmin setup for our various MySQL instances.  The annoying part is the current setup is requiring an individual pma database on each instance.  Because of this the severs that are slaves cannot run in readonly mode.  (PHPMyAdmin needs to write to the pma database).

Can someone offer advice or point me to an appropriate walk through on how to set this up?


texts:
How is this possible:

I installed mysql-5.6.11-osx10.7-x86.dmg which is supposed to be version Mac OS X ver. 10.7 (x86, 32-bit), DMG Archive (32 bit!)

But upon SHOW VARIABLES LIKE "%version%"; I get the following:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.11                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | osx10.7                      |
+-------------------------+------------------------------+
7 rows in set (0,00 sec)

Why x86_64 ?

Amendment:

Now it becomes weird:
I uninstalled MySQL with the help of https://stackoverflow.com/questions/1436425/how-do-you-uninstall-mysql-from-mac-os-x

Then I installed mysql-5.5.31-osx10.6-x86_64 from http://dev.mysql.com/downloads/mysql/5.5.html#downloads
It is clearly sitting in /usr/bin/local/mysql-5.5.31-osx10.6-x86_64/ (note the 64 at the end).

Now this:

mysqld  Ver 5.5.31 for osx10.6 on i386 (MySQL Community Server (GPL))

and:

mysql  Ver 14.14 Distrib 5.5.31, for osx10.6 (i386) using readline 5.1

and this:

mysql> SHOW VARIABLES LIKE "%version_compile%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| version_compile_machine | i386    |
| version_compile_os      | osx10.6 |
+-------------------------+---------+
2 rows in set (0.00 sec)

How can this be: 32 bit architecture after (obviously) downloading and installing 32 bit?
I am feeling lost.


texts: Having regex_matches exclude cases where both capturing groups match a 0-length string

texts:
I'll trying to write a regex pattern that will help me create an array of length 2 arrays such that each inner array is matched like so:
I'm looking to split sequences that begin with a positive or negative integer followed by a sequence of letters and periods that begins with a letter.  Neither the integer nor sequence of letters/periods is required, but at least one should be for it to match.
I don't know that that's a very clear description, so here's an example.  The string '9a.-16.y -y. -7hello world' should return {{9, a.},{-16,},{,y},{,y.},{-7,hello},{,world}}.
I've gotten pretty close with the following code
select array(
  select regexp_matches(
    '9a.-16.y -y. -7hello world',
    '(-\d+|\d*)([a-zA-Z][a-zA-Z.]*|)',
    'g'
));

But it's including cases where neither capturing group matches.
{{9,a.},{-16,},{,},{,y},{,},{,},{,y.},{,},{-7,hello},{,},{,world},{,}}


texts:
I am currently running into two problems. First, when viewing tables using MySQL Query Browser, it keeps displaying "Could not fetch columns".

Second, when attempting to go in an edit any of the fields, the "Edit" button is greyed out and won't allow me to modify anything in the table.

My first thought is that this is a permissions issue, so let me give some background on where I've come so far.  Overall goal, create a backup PrestaShop on another server for dev work.  Both servers are running CentOS 6.2 and MySQL Server 5.1.73.
Steps I've taken in this process (specifically in regards to the db):
1. Ran mysqldump -u root -p --databases shop > shop.sql
2. Copied shop.sql over to the new server
3. Made sure MySQL was installed and running on new server
4. Ran mysql -u root -p shop < shop.sql
5. GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY PASSWORD 'Whatever' WITH GRANT OPTION
6. I made sure to setup a user with the same permission as on the previous server as well to try to reduce problems and granted the same privileges to it.

So now I can view and run select statements on the data, but it is not allowing me to edit the fields from within query browser.  I am able to log in and run update statements from the command line on the local host through putty, however.  Any thoughts or ideas are greatly appreciated!


texts: How to set space usage alert for database using SQL Server 2012 with Amazon RDS as managed service?

texts:
I have a database whose initial size is set to 1000 GB.
Is there a way where i can set an alert when the max space is used and database size is nearing to 1000 GB, for eg when it reaches maybe 995 or 994 GB , so that we get to know about it and take actions for increasing the disk space before hand. without sudden downtime.

We are using SQL Server 2012 with Amazon RDS as managed service.

Thanks in advance.


texts: Do I need a database license for my application?

texts:
I am developing an application for cemetery management. It is my first "big" project, so I'm not familiar with the "corporative" side of this.

First, some info about the app:

Has 3 local database files, one for authorizations (Login and permission purposes), another for option storing (each of these two has only one table) and another for the system itself (this is the main one, and has 5 tables).
In general, the system stores all the info of a small cemetery. This info includes deceased, graves and payments (3 tables). The other 2 tables are one for crossed information and another for vacant graves.
There's storing and retrieval of info between the C# GUI and the SQL.
The application is for academic purposes for now, but it may be a serious thing in the future (own business maybe?), so I'm a little worried about legal problems with M$ in the future.

Main Question: Do I need to pay for a license? Either for SQL or Visual Studio or anything else.


texts: Configuring 2017 SQL Server SSISDB Server-wide Default Logging Level does not seem to work

texts:
Using SSMS, I have changed the SSIS Catalog Logging Level from Basic to Performance to a Custom Logging Level in a 2017 SQL Server Instance and then I run the report: "All Executions".  It doesn’t seem to matter what Logging Level I am at, the amount of data/detail that is included in the SSIS Catalog Report remains the same.  I would think the amount of detail would change with each Logging Level.
If the SSIS Catalog Reports do not change when the Logging Level changes, then how do I see the changes to the Logging Levels?  Otherwise, if the SSIS Catalog Reports are suppose change, can someone please point me in the correct path to get the SSIS Catalog Reports to change?


texts:
We need to move multiple tables using  Load from cursor. But the number columns in the source and destination tables are not the same.

Also the  tables structures can change so we need the same to be dynamic.

the statement would be something like below.

 declare cur1 cursor for select col1,col2, col3  from test1;
 load from cur1 of cursor insert into test2 (col1,col2, col3);

I need to call this from a procedure using execute immediate so that we can append the columns in the query. The list of columns is in a variable V_COL_LIST so the query i need to execute for procedure is like below.

 execute  immediate ('declare cur1 cursor for select '||V_COL_LIST ||'  from test1');
 execute  immediate ('load from cur1 of cursor insert into test2 ('||V_COL_LIST ||')');

But Execute Immediate does not work for the above queries.
Is there any work around to this ?


texts: Is attaching a database on the same server under a different database name possible?

texts:
I recently offered to help a friend with some development. She isn't a technical person and it turned out what she needed was more a DBA/SQL developer. She gave me the IP and password to this server.

There is no development server. Only production. So I have to be extra careful with everything I do.

I accidentally altered a stored procedure and now I'm trying to get it back. I had the old employees send me the lastest files. They were in rar format and after I unzipped the DB folder, there was only a file inside(no extension). Looking at the contents led me to believe it is a SQL Server .mdf file. This leads me to my question.

Can I attach this mdf file to a new database I just created? Without it trying to take down the other instance? It seems like I could but since this is production, I have to be EXTRA careful.

The server is Windows Server 2008 R2, with SQL Server Management Studio 2012 installed.


texts: How to compare strings as int in EAV model?

texts:
I have implemented an EAV db pattern as part of a PoC in Postgres. This is the following ER illustration:

entity:
id, primary key
firstName, varchar
lastName, varchar
tenantId, int, b-tree indexed

attribute:
id, primary key
label, varchar
type, varchar, (enumerated as string from java enum , NUMERIC, TEXT, DATE)

attribute_value:
id, primary key
attribute_id, fk reference attribute primary key
entity_id, fk reference entity primary key
value, varchar

Now the problem I am facing is comparing values in the joined tables. For example:

  How do I check whether an attribute with label = 'marks12' has value < 100

Comparing the value::int does not solve the problem as there are other attribute such as gender that cannot be cast into int value.
How do I perform such value-centric conditions in the above EAV design model?

PS: I am open to other database designs which allow to map/store dynamic attributes in an RDBMS.


texts: Do I always need boilerplate code to prevent nested transactions?

texts:
Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one?

It seems like a bit of boilerplate code that I need to repeat everywhere and it will make for worse readability.

I'm trying to future proof against this error:

  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Example of boilerplate code Exception handling and nested transactions


texts: Is an Oracle 10g instance dependent on the windows account authentication?

texts:
I have created an Oracle 10g (10.2.0.4) instance on a Windows 2003 Server with a domain user xxx having domain administrator level privileges.

Now I wish to change xxx's password. Will it impact the oracle instance functioning?


texts: How to use a select pivot query to update two different tables

texts:
I would like to update data in two different table however based on select query using pivot I'm unsure how to go about to do the update query.
See query I have below:
UPDATE [FuelManagementSystem].[dbo].[OpenCloseTank] SET OpenDip = 0.00, CloseDip = 0.00 WHERE WhenDate = GETDATE() AND TankID = 'BW-TK-DSL1'
UPDATE [FuelManagementSystem].[dbo].[OpenCloseNozzle] SET OpenReading = 0.00, CloseReading = 0.00 WHERE [Date] = GETDATE() AND TankID = 'BW-TK-DSL1' AND NozzleNo = 1
UPDATE [FuelManagementSystem].[dbo].[OpenCloseNozzle] SET OpenReading = 0.00, CloseReading = 0.00 WHERE [Date] = GETDATE() AND TankID = 'BW-TK-DSL1' AND NozzleNo = 2

SELECT QUERY:
SELECT
    [Date], T.TankID, [OpenDip], [CloseDip], [ORNozzle1], [CRNozzle1], [ORNozzle2], [CRNozzle2]
FROM [FuelManagementSystem].[dbo].[OpenCloseTank] T
INNER JOIN
(
    SELECT [TankID], [Date], SUM([1]) AS [ORNozzle1], SUM([2]) AS [ORNozzle2]
    FROM [FuelManagementSystem].[dbo].[OpenCloseNozzle]
    PIVOT
    (
        MAX (OpenReading)
        FOR [NozzleNo] IN ([1], [2])
    ) AS P
    WHERE CONVERT(DATE,[Date])='2019-11-09'
    GROUP BY [TankID], [Date]
) AS A
    ON [T].[TankID] = [A].[TankID]
INNER JOIN
(
    SELECT [TankID], SUM([1]) AS [CRNozzle1], SUM([2]) AS [CRNozzle2]
    FROM [FuelManagementSystem].[dbo].[OpenCloseNozzle]
    PIVOT
    (
        MAX (CloseReading)
        FOR [NozzleNo] IN ([1], [2])
    ) AS P
    WHERE CONVERT(DATE, [Date])='2019-11-09'
    GROUP BY [TankID]
) AS B
    ON [T].[TankID] = [B].[TankID]
WHERE CONVERT(DATE, Whendate) = '2019-11-09'

QUERY RESULTS:

TABLE DATA:


texts: Postgresql: when temporary tables are deleted in postgresql

texts:
I am inserting data into temp table and its working fine. below is the psedo sql code for the same

with cte as(

)
select *
into temp_table
from cte

with this approach data is inserted very fast into temp table. As per my knowleged temp table is deleted once the session is closed. but my temp table is not deleted even if closed my pgadmin connection.

My question is does temp table in postgresql are deleted automatically or they remains on disk space until we delete them.

Regards,

Sanjay Salunkhe


texts: Database design of Ledger Account and Balance sheet generation

texts: I am building an application which has deposit and loan accounts management,  for which i have to develop ledger accounts and then generate balance sheet. Being new to database design, i am having a hard time in designing the database model best suited. Is there any standard model for the database design of accounts(especially ledger) which can used?


texts:
I'd like to know what the general replacement is for a cursor.  The general implementation of a cursor I see out and about is

DECLARE @variable INT, @sqlstr NVARCHAR(MAX)

DECLARE cursor_name CURSOR
FOR select_statement --essentially to get an array for @variable
                     --usually it's a subset of unique ids for accounts, clients, parts, etc

OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @sqlstr = N'
     /* some query that uses '+ str(@variable) +' to do dirty work
     such as: go through all our accounts, if it''s some subset (possible new cursor),
     go through those accounts and connect this way,
     map those fields and add it to our big uniform table */
     '

     EXEC sp_executesql @sqlstr
FETCH NEXT FROM cursor_name INTO @variable
END

CLOSE cursor_name
DEALLOCATE cursor_name

Since so many people are anti-cursor (with a nod to SO: Why do people hate cursors) what is the general replacement for the general implementation (preferably SQL Server)?


texts: MySql group the difference of 2 timestamp columns in minutes


texts: SQL Server 2014 COUNT(DISTINCT x) ignores statistics density vector for column x

texts:
For a COUNT(DISTINCT) that has ~1 billion distinct values, I'm getting a query plan with a hash aggregate estimated to have only ~3 million rows.

Why is this happening? SQL Server 2012 produces a good estimate, so is this a bug in SQL Server 2014 that I should report on Connect?

The query and poor estimate

-- Actual rows: 1,011,719,166
-- SQL 2012 estimated rows: 1,079,130,000 (106% of actual)
-- SQL 2014 estimated rows: 2,980,240 (0.29% of actual)
SELECT COUNT(DISTINCT factCol5)
FROM BigFactTable
OPTION (RECOMPILE, QUERYTRACEON 9481) -- Include this line to use SQL 2012 CE

-- Stats for the factCol5 column show that there are ~1 billion distinct values
-- This is a good estimate, and it appears to be what the SQL 2012 CE uses
DBCC SHOW_STATISTICS (BigFactTable, _WA_Sys_00000005_24927208)
--All density   Average Length  Columns
--9.266754E-10  8               factCol5
SELECT 1 / 9.266754E-10
-- 1079126520.46229

The query plan

Full script

Here is a full repro of the situation using a stats only database.

What I've tried so far

I dug into the statistics for the relevant column and found that the density vector shows an estimated ~1.1 billion distinct values. SQL Server 2012 uses this estimate and produces a good plan. SQL Server 2014, surprisingly, appears to ignore the very accurate estimate provided by the statistics and instead uses a much lower estimate. This produces a much slower plan that does not reserve nearly enough memory and spills to tempdb.

I tried trace flag 4199, but that did not fix the situation. Lastly, I tried to dig in to optimizer information via a combination of trace flags (3604, 8606, 8607, 8608, 8612), as demonstrated in the second half of this article. However, I wasn't able to see any information explaining the bad estimate until it appeared in the final output tree.

Connect issue

Based on the answers to this question, I have also filed this as an issue in Connect


texts: How can I create a user who could only create tables and can't delete/drop them

texts:
There's my problem. I'm using PostgreSQL and tried so many things to create a user who can create tables but can't drop them, it's like create a table but assign it to another user. I've tried one trigger but it can't solve my problem.

I've used a trigger that I found on another site, just changed the role where the owner of the table need to be replaced but it didn't work. When I try to create a table with another user it returns me a message

  ERROR: must be a member of role "adm"

This is the trigger:

CREATE OR REPLACE FUNCTION trg_create_set_owner()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO adm', obj.object_identity);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_set_owner
 ON ddl_command_end
 WHEN tag IN ('CREATE TABLE')
 EXECUTE PROCEDURE trg_create_set_owner();


texts: SQL Server 2016 CDC (change data capture) missed data issue

texts:
I faced some interesting behaviour with CDC mechanism. I have a table (for example table 'A'), and i've turned on capture changes on it. So in result I have a CDC table 'cdc.dbo_A_CT' that contains captured data. Table contains a lot of columns but two of them are 'id' and 'timestamp', id is incremental (e.g. every new insert receive the next id), timestamp - is operation perfromed datetime. In a few days of table working, I decided to check size of CDC table and original. CDC has a few rows less than original table.
In original table I have this data:

  id          timestamp
OP1332    2018-08-06 12:32:28
OP1333    2018-08-06 12:38:42
OP1334    2018-08-06 12:47:12

In CDC table I have only information about inserting:

OP1332 and OP1334

and no data for id = OP1333.

This issue repeat for random table and random time, and have no idea whats going wrong with CDC mechanism.

Enviroment: Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64)


texts: How to implement a unique key for a long VARCHAR column in MySQL

texts:
I need to let MySQL to check the uniqueness of a VARCHAR column which is 5000 max length and is not the primary key. I can use an index of length 500 on the column to speed up query, but how to create an unique key that works?

An unique key of length 500 fails when trying to insert two records whose that column is the same for the first 500 characters and differs on somewhere after that.


texts: Map login to user in restored database in SYBASE ASE 16

texts:
After restoring a database from other environment is there a way to map a user in the database to a login in the server.  I can drop and recreate or alias but in MS SQL Server there is a way:
EXEC sp_change_users_login 'Auto_Fix', 'user'
I was looking for something similar in SYBASE ASE 16


texts:
I have two SQL tables: Users and clients.

Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field.

The users table has a username field which is unique.

The clients table has a user_id field AND clientname field that together are unique (i.e. clientnames can exist more than once, but must have different user_ids)

My question is: How do I add a rule that forces uniqueness between the username field in the users table and the clientname field in the clients table? Is it even possible?

i.e. no username can be the same as a clientname , and no clientname can be the same as a username.


texts: Having issue with window function COUNT counting duplicates

texts:
I have a query like so:

select
   distinct(line_items.email) as email,
   count(orders.order_id) over(partition by orders.order_id) as num_orders
from
   line_items
   inner join orders on
   line_items.order_id=orders.order_id

This is apart of a broader query where I need to be able to select from line_items vs. orders.

The problem here is that:

A user has multiple line items on a specific order (bought 2 different products)
The window function sees two rows and is still counting twice

I can't do count distinct because Redshift doesn't support count distinct as window function.

The records looks something like this:

line_item_id,order_id,email
123,1,bob@some.tld
124,1,bob@some.tld

The result of the above query looks like this:

email,num_orders
bob@some.tld,2

Is there a way I can get this to be:

email,num_orders
bob@some.tld,1

While retaining use of the window function?


texts: MSSQL and ALTER AUTHORIZATION on table has no affect?

texts:
I have a schema called [test] with an owner X.
I transferred an ownership of one table [test].[table] (ALTER AUTHORIZATION) to owner Y.
Now, if I understand correctly, even though the owner X owns the parent schema [test], he/she should have no access to [test].[table] since an owner of that table is Y, correct?
Or must I explicitly DENY permissions to X on that table?
I tested this and owner X still has an access to [test].[table].
And btw, X is not member of db_datareader or db_owner ;-)
So the question is, what is the purpose of ALTER AUTHORIZATION on an object/table if other users still have access to it (regardless if they are owners of parent schema)?
Best,
Mike


texts: How do I rename a table with minimal downtime in a production postgres database?

texts:
I understand that renaming a table in postgres boils down to a simple catalog update. However, it also requires an ACCESS EXCLUSIVE lock to ensure transactions are safe.

What would be the ideal way to do this on an active table in production? Wouldn't naively calling alter table foo rename to bar end up blocking newer transactions until the DDL can get its exclusive lock?

Would trying set lock_timeout TO '1s' and attempting the DDL multiple times until it can succeed be a better strategy? Or should I just bite the bullet and take my system offline for a minute or two and get it over with?


texts: insert data in multiple tables in one mysql query

texts:
Suppose there are two tables e1 and e2 with
e1
|id |time     | ca| cb|
|1  | 12:20:00| 5 | 10|
|2  | 12:25:00| 9 | 6 |

and:
e2
|id| time    | cc|
|1 | 12:20:00| 11|
|2 | 12:25:00| 50|

Now I want to insert third record ine1 and e2 having id:3 and store id,time in both table and ca,cb in e1 and cc in e2.it should be perform in single query.
e1
|id | time       |  ca|   cb  |
|1  |  12:20:00  |  5 |   10  |
|2  | 12:25:00   |  9 |   6   |
|**3|   12:28:00 |  11|    7**|

and:
e2
id| time      |  cc|
|1|  12:20:00 |  11|
|2|   12:25:00|  50|
|**3| 12:28:00|  23**|

whether its possible ?I am novice to MySQL


texts:
I am setting up new server and am a little confused in my research of HD configuration.  My main question is :

Is it recommended to have the Trans Logs and DB logs (ldf) on separate spindles or can they be on the same?
Do Trans Logs and ldf logs write concurrently or one after the other?
My plan is to configure it as follows:

  C:\ Raid 1,

  Data (Raid 10),

  Logs (Trans & .ldf) Raid 10,

  TempDB (Raid 1),

  Local Backups (Raid 10).

Does that make sense or should I separate the Trans & .ldf files onto separate raid configs ?


texts: Is it suitable innodb_buffer_pool_size for MySQL Master-Slave

texts:
I am wondering how to decide the value of innodb_buffer_pool_size for my Master-Slave style database.

I have a set of MySQL, 1 x master + 1 x slave.

As I know that the innodb_buffer_pool_size is generally for data storing in memory to speedup the data access (read). So can I set the different value between master & slave ?

For example, if I calculate the suitable innodb_buffer_pool_size value is 8G, I should set 8G in my slave but maybe 4G or less for my master ?

That's because the innodb_buffer_pool_size can benefit the read not the write process. Am I wrong ?

Thanks.


texts: Combine multiple similar queries into one

texts:
I need to calculate a weighted average using the following table. I have to calculate this number for metric_1 up to metric_10. All the metric columns have a value from 1 to 5.

------------------------------------------------------
id | var      | weight | metric_1 | metric_2 | week
------------------------------------------------------
1  | 9        | 0.56   | 4        | 2        | 8
2  | 3        | 1      | 3        | 1        | 7
3  | 7        | 0.98   | 3        | 5        | 8
------------------------------------------------------

Here's the query I'm using. I need same query for each metric, from 1 to 10.

SELECT
    weight, sample_size, detractor,
    (promoter * 100) / weight AS promoter_p,
    (promoter - detractor) * 100 / weight AS score
FROM
    (
        SELECT
            COUNT(surveys.id) AS sample_size,
            SUM(weight),
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN pond END) AS promoter,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN pond END) AS detractor,
            MAX(week)
        FROM surveys
        WHERE (var BETWEEN 0 AND 10) AND (metric_1 BETWEEN 1 AND 5)
    ) t

I there a way to combine those queries instead of having one query for each metric? The only difference is the WHERE clause:

        WHERE (...) AND (metric_1 BETWEEN 1 AND 5) /* metric_2, ...10 */


texts: Oracle shrinking / reclaiming free tablespace space

texts:
I have a Oracle 12c tablespace with the size of 90GB. Almost 40GB are free.

TB1 54 %    90880   41383   49497   46  98304   50 %    ONLINE  PERMANENT

The tablespace has 3 datafiles.

E:\ORACLE\ORADATA\xx\TB1.DBF    33554416    True    100
F:\ORACLE\ORADATA\xx\TB1_2.DBF  33554416    True    100
G:\ORACLE\ORADATA\xx\TB1_3.DBF  25344000    True    100

How can I shrink the tablespace to reclaim the free 40GB (datafiles on disk)? I can not have a downtime.


texts:
I have previously posted on stackoverflow, however, I have been directed here.

I'm getting to the stage where I need to analyse lots of flat CSVs:

Read-in around 500GB of CSVs (e.g. daily data split by month)
Group the data (e.g. by month or year)
Output the aggregated data as a small CSV

I wasn't sure whether sqlite was the right package for this (adding CSVs to it appears to take a long time and as in my link above after a certain size I can't access the database) and whether I should consider something else like PostgreSQL or Microsoft SQL Server?

I'm looking to invest in a hardware/software platform for this (e.g. SSD, RAID, Microsoft SQL Server) and was hoping for some information on where to begin.

Particularly, if PostgreSQL is a possibility - is there a similar way to quickly import CSVs like here:

https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

Edit (08/10/15):

I'm testing out uploading the data into a PostgreSQL database and am averaging 16 minutes per 10GB CSV. My issue is that some of my columns are very big so I would have to change them from bigint to varchar, etc. The data has 38 columns and around 50 mill rows per file so figuring out which categorical variables are strings or integers is a real pain.

At the moment I am using:
cur.copy_expert(sql="COPY %s FROM stdin DELIMITERS '~' CSV;", file=f), with my data-type mainly being varchar. I did this because the CSV is a bit messy and sometimes what I think is an integer turns out be alphanumeric and I have to re-upload.

Would it be much slower to import as a Pandas data-frame (so that panda takes care of column-type for me) and use that with sqlachemy to insert into Postgres? I'm guessing if PANDAs is a bit slower it will make up for it because the column types will be optimised (since it will decide on that for me).

E.g. something like this:

import pandas as pd
df = pd.read_csv('mypath.csv')

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')

df.to_sql("table", engine)

My main goal is to optimise this for the group-by command.

Second Edit:

Ahh, I guess best thing is to upload one CSV file using PANDAS and then copy the table structure into a new table which is then filled with the faster COPY command?


texts: MySQL error 1241: Operand should contain 1 column, INTERSECT two subqueries

texts:
I'm running this query in MYSQL and I'm getting the error 1241: Operand should contain 1 column(s).

I want to know the names and student id's of the students enrolled in Database Management Systems and Knowledge Discovery in Databases (both course titles in the CoursesOffer table)

Students(stuID: String, stuName: String, gender: String, birthdate: Date, enterYear: Year, gpa: Float)
Departments(deptName: String, numPhDs: Integer)
ProfessorWorks(profID: String, profName: String, deptName: String)
CoursesOffer(cNo: String, cTitle: String, deptName: String)
Majors(deptName:  String, stuID: String, degreeProgram: String, attendYear: Year, attendSemester: String)
Sections(cNo: String, semester: String, sectNo: String, profID: String)
Enrolls(stuID: String, cNo: String, sectNo: String, semester: String, grade: String)

My query looks like

SELECT S.StuName, S.StuID
FROM Students S NATURAL JOIN CoursesOffer C NATURAL JOIN Enrolls E
WHERE cTitle='Database Management Systems'
IN (
SELECT S2.StuName, S2.StuID
FROM Students S2 NATURAL JOIN CoursesOffer C2 NATURAL JOIN Enrolls E2
WHERE cTitle='Knowledge Discovery in Databases');


texts: MS SQL Creating Tables and Adding Values by a Trigger

texts:
Table_A is an existing table which has 3 columns Id (PK), Name, Stock.
I would like to create a trigger to create a table.
For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10.
By using triggers I would like to create a new table called Table_B and when I write the following code "INSERT INTO Table_B VALUES(3,5)" a new table will be created and it will have 2 columns ProductId and Quanity. At the same time the value for Stock for Table_A will be decreased by 5. Every time I insert something to Table_B I would like them to get added to Table_B and the stock in Table_A to be decreased by the quantity of that item inserted with the related ProductId. Thanks!


texts: How long is "too long" for MySQL Connections to sleep?

texts:
I logged onto my server to check the status of the database and noticed on the SHOW PROCESSLIST; that there are a large number of connections sleeping that are very old.

What duration should the connection time limit be before the connection should be killed?

How do I set the time limit in MySQL 5.0.51a-3ubuntu5.4?

Note:

I am using PHP 5.2.x and symfony framework 1.2.


texts:
I have an error "ORA-01537" when I create a tablespace.

create tablespace "VOLUMT" datafile 'VOLUMT.dbf' size 250M autoextend on;

Then, it shows like following.

  ERROR at line 1: ORA-01537: cannot add file 'VOLUMT.dbf' - file
  already part of database

I just change the tablespace name of VOLUMT to TEST01, and it works..

  Tablespace created.

So, I get into the directory of db and searched the related tablespace..

${HOME}/oracle/db/oradata/ORAC12

However, I can't find any.. Coudl you help me to solve this problem? Also I need to use "VOLUMT" for tablespace creation.. not TEST01..

-- Additional --

select TABLESPACE_NAME from DBA_DATA_FILES;

TABLESPACE
----------
SYSTEM
SYSAUX
USERS
TEST01

I can see TEST01 Tablespace, but I can't find any datafiles in the directory.
Also I can't find any VOLLMT tablespace in it..

Thank you.


texts: Mongodb secondary reads data at 100% 24/7

texts:
We have a Mongodb 4.0 database, running a replica set with 3 members. Total database size is about 800GB. All of the members are running in docker containers. Docker shows that the master container has read total 60GB data and written 165GB in the past 5 days. One of the secondary members has read and written 33 + 33GB data in the past 5 days. The last (secondary) member has read 68.9TB (!!!) data since it was started. The average I/O transfer speed for that "rebellious" member is 200 MB/sec. Basically, it uses all available I/O bandwidth, 24/7.

The service log of that member is full of messages like this:

  NETWORK  [LogicalSessionCacheRefresh] Starting new replica set monitor
  for rs0/mongo-backup:27017,mongo-master:27017,mongo-replica:27017

This member is used for backup only, nothing is running queries on it.

How is that possible? Why is it reading at 100% speed all the time? What are these messages? Apart from these messages, we don't see anything useful.

UPDATE: If I restart the container, then it goes back normal for a while. But after running for some days, it starts this reading again, and the only way to fix it is to restart the container. All members in the replica set are running mongodb 4.0.6.

Some strange messages from the logs from yesterday:

2019-03-04T04:33:51.101+0100 I ASIO     [Replication] Ending connection to host mongo-replica:27017 due to bad connection status; 15 connections to that host remain open
2019-03-04T04:33:51.099+0100 I REPL_HB  [replexec-3980] Error in heartbeat (requestId: 4950505) to mongo-replica:27017, response status: NetworkInterfaceExceededTimeLimit: timed out

But otherwise, the replication is working fine.


texts: Read Committed Snapshot Isolation vs Read Committed - pros and cons?

texts:
We have an application from JD Edwards, for which the database runs on SQL Server 2005 or 2008R2.

They have a new application vendor and he suggested to change isolation level to Read Committed Snapshot Isolation (RCSI) instead of default locking Read Committed to resolve a blocking issue:

Long story short, client was complaining about getting error & we involve application team to look logs on app server & at that same time we see blocking on database so then we send the SPID & app team for rollback or kill that connection & issue will disappear so they have few API cursors & users searching for data every now & then. As a maintenance we are doing rebuild index & update stats on weekly basis.

We see hundreds of cursors running on server & I give the SPID of first blocking happened & then application team find out rest, but what i am seeing is below:

FETCH API_CURSOR0000000000A4804D
FETCH API_CURSOR0000000000A467E6
FETCH API_CURSOR00000000006AF7B0
Microsoft SQL Server JDBC Driver set transaction isolation level read committed

I told them by changing to RCSI, each update will hold a pointer if data is reading on that page or row, we also can get dirty reads and will use more tempdb in SQL Server. We have had a back and forth conversation and can't decide whether to make this change on the server.

Can anyone point out the pros and cons of using RCSI, or tell me if you have experience with the JD Edwards application running on SQL Server and best practices for the isolation level?


texts: Can this SQL be made more efficient and combined into one select?

texts:
I have the 4 SQL queries below that I combine into one result set at the end.

I am guessing that since I am operating on the same tables in each of the SQL sections, there is a cool way to get my results out of one unified SQL query.

Currently I am doing it like this:

select
[date] = CONVERT(DATE, M.crdate),
[tally sent to smc w/ attachment] = count(*) ,
[total MB size] =  sum(cast(AD.Size as decimal (10,2)) )/1024/1024
into #tmp_Attachments_Sent_To_smc
from AttachmentDetail AD
inner join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
inner join MessageRecipient MR on MA.MessageId = MR.MessageId
inner join Message M on MR.MessageId = M.id
where AD.isinline <>1 and MR.RecipientTypeId =2 and left(mr.EmailAddress,4)='smc+'
GROUP BY  CONVERT(DATE, M.crdate)
ORDER BY [date] DESC;
--select * from #tmp_Attachments_Sent_To_smc

select
[date] = CONVERT(DATE, M.crdate),
[tally sent from smc w/ attachment] = count(*) ,
[total MB size] =  sum(cast(AD.Size as decimal (10,2)) )/1024/1024
into #tmp_Attachments_Sent_from_smc
from AttachmentDetail AD
inner join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
inner join MessageRecipient MR on MA.MessageId = MR.MessageId
inner join Message M on MR.MessageId = M.id
where AD.isinline <>1 and MR.RecipientTypeId =1 and left(mr.EmailAddress,4)='smc+'
GROUP BY  CONVERT(DATE, M.crdate)
ORDER BY [date] DESC;

select
[date] = CONVERT(DATE, M.crdate),
[grand total sent to smc] = count(*) ,
[total MB size sent to smc] =  sum(cast(Size as decimal ) )/1024/1024
into #tmp_Sent_to_smc
from Message M
where MessageSourceId=2
GROUP BY  CONVERT(DATE, crdate)
ORDER BY [date] DESC;

select
[date] = CONVERT(DATE, M.crdate),
[grand total sent from smc] = count(*) ,
[total MB size sent from smc] =  sum(cast(Size as decimal ) )/1024/1024
into #tmp_Sent_from_smc
from Message M
where MessageSourceId=1
GROUP BY  CONVERT(DATE, crdate)
ORDER BY [date] DESC;

select   A.* ,
         B.[tally sent from smc w/ attachment] ,
         B.[total MB size],
         C.[grand total sent from smc] ,
         C.[total MB size sent from smc],
         D.[grand total sent to smc] ,
         D.[total MB size sent to smc]
from #tmp_Attachments_Sent_To_smc A
join #tmp_Attachments_Sent_from_smc B on A.date = b.date
join #tmp_Sent_from_smc C on A.date = C.date
join #tmp_Sent_to_smc D on A.date = D.date

drop table #tmp_Attachments_Sent_To_smc
drop table #tmp_Attachments_Sent_from_smc
drop table #tmp_Sent_from_smc
drop table #tmp_Sent_to_smc


texts:
I'm trying to restore a database log, however I am getting this message:

  Msg 4305, Level 16, State 1, Line 3
  The log in this backup set begins at LSN 76120000013549400001, which is too recent to apply to the database. An earlier log backup that includes LSN 75428000036693300001 can be restored.
  Msg 3013, Level 16, State 1, Line 3
  RESTORE LOG is terminating abnormally.

What is an LSN?

How can I determine which backup contains the correct LSN?


texts:
I have these tables:

pupils: (id_pupil, name, surname, email, user, pass, level, class)
incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester)
qualifications: (id_qualification, qualification, date, time, subject, id_pupil, id_user, id_trimester, type_qualification)

and i did this query:

SELECT
    pupils.id_pupil
  , name
  , surname
  , round(avg(qualifications.calificacion),2) as average
  , count(qualifications.calificacion) as count
  , COALESCE(sum(type_incident='miss' and level=1 and class='A' and id_trimester=1),0) as misses
  , COALESCE(sum(type_incident='delay' and level=1 and class='A' and id_trimester=1),0) as delays
  , COALESCE(sum(type_incident='attitude' and level=1 and class='A' and id_trimester=1),0) as attitude
  , COALESCE(sum(type_incident='miss_justif' and level=1 and class='A' and id_trimester=1),0) as misses_justificadas
FROM
    pupils
    left join incidents         ON incidents.id_pupil=pupils.id_pupil
    left join qualifications    ON qualifications.id_pupil=pupils.id_pupil
WHERE
    level=1
    and class='A'
    and id_trimester=1
    and type_qualification='class'
    and qualifications.id_trimester=incidents.id_trimester
GROUP BY id_pupil

But the problem is, the correct result i would have to get it would be:

And i get:

The problem is that multiplicate the number of class qualifications and the misses (17x6(2 misses, 1 delay, 1 attitude and 2 misses_just)=102)) and i don´t want that. I want the correct information showed in the first picture.

Would you like to help me, please?

Thanks!


texts:
I am having a very strange issue here with a web app that I have seen working fine in the past.

The server is a simple LAMP stack running MySQL 5.6.30.

SQLSTATE[HY000] [1045] Access denied for user 'my_user'@'localhost' (using password: YES)

The reason why this issue is causing me problems is because the user my_user has all privileges.

Here is the output of the SHOW GRANTS query on my MySQL server:

mysql> SHOW GRANTS FOR 'my_user'@'localhost';
+---------------------------------------------------------------
| Grants for my_user@localhost
+---------------------------------------------------------------
| GRANT USAGE ON *.* TO 'my_user'@'localhost' IDENTIFIED BY PASSWORD '******'
| GRANT ALL PRIVILEGES ON `db1`.* TO 'my_user'@'localhost'
| GRANT ALL PRIVILEGES ON `db2`.* TO 'my_user'@'localhost'
| GRANT ALL PRIVILEGES ON `db3`.* TO 'my_user'@'localhost'
| GRANT ALL PRIVILEGES ON `db4`.* TO 'my_user'@'localhost'
| GRANT ALL PRIVILEGES ON `my_db`.* TO 'my_user'@'localhost'
+---------------------------------------------------------------
6 rows in set (0.00 sec)

The database called my_db is the Database in question for this web app.

Does anyone have any suggestions on how I should go about diagnosing this issue?

I have confirmed that I am indeed looking at the right server with the correct user and what should be the correct password.

I am also unable to log in to MySQL CLI with this user.


texts: After issuing an Insert using NewId() can you get the Ids that were used in that Insert

texts: I would like to know if it is possible to get the last inserted ID's from an Insert with Select NewId()?


texts: My Mongos can't authenticate to my replica set

texts:
I have 3 config servers in a replica set and when I use the command: sudo mongos --configdb crs/the 3 config servers:port# --keyfile "Keyfile",
I keep getting this error:
[mongosMain] Unable to reach primary for set crs
[mongosMain] Cannot reach any nodes for set crs.
I have checked that all the config servers are online and functional, I have checked the firewall.
This is on AWS linux.


texts:
I am trying to implement Transperent Data Encryption.  I am done with setting up the wallet and creating the tablespace.

My doubt is how to specify tablespace so that every object(tables/functions/triggers/etc) is created inside that tablespace.

Can it be done by altering user as below so that every object created by user1 is automatically created inside the tablespace securespace? Assuming the following command does so.

ALTER USER "user1" DEFAULT TABLESPACE "securespace" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK ;

or do I have to specify securespace in every DDL statement?


texts: PostgreSQL pg_basebackup from several port number

texts:
I have one slave server which used as a replication server (let's called it slave1) from several master servers.

The `slave1' server is set up to receive replication from several postgresql server, and I set it at multiple ports. The settings is like this:

Master1 port 5432 replicated to slave1 port 5432
Master2 port 5432 replicated to slave1 port 5433
etc.

Those server above (master1, master2, & slave1) are hosted at cloud.

all server using Postgresql-11 on Ubuntu 18.04.1 LTS

Is it possible to replicate my slave1 to my on premise server at office (called slave2), so that all databases on slave1 on all port is replicated to my slave2 on single port (port 5432 which is the default port of postgresql) ??


texts: How do I echo data from two tables that are associated to one user in php


texts: Permissions using a TYPE as a Table Valued Parameter

texts:
I'm developing a .Net App that calls an stored procedure that have a table valued parameter declared as a TYPE READONLY.

First time I tried to call the SP I received the next error:

  Msg 229, Level 14, State 5, Line 1
  The EXECUTE permission was denied on the object 'TYPE_OBJ', database 'MY_DB', schema 'dbo'.

After granting EXECUTE permission to the user it works fine.

But I can't find in MS-DOCS neither in CREATE TYPE nor in Use table-valued parameters any reference to the permissions needed to use it as a parameter.

Where can I find information about the necessary permissions to use a TYPE as a parameter?


texts: Less restrictive query return less result due to simple removing one additional constraint

texts:
Look at this query

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
   AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

that's basically search all bizs whose TA.LatLong is in the box of 'MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)' and after that the box must contain kucing

This returns 22 rows.

Now compared that to this query

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
ORDER BY
  Distance
LIMIT
  0, 20

It's the same query but LESS restrictive. Now I only want to check whether the business is in the box or not. That is it. Yes there are businesses in those box. Most of which do not contain the word "kucing*"

There should be more queries. Yet there are less. The second query return null. The LESS restrictive query return LESS result.

Rolando answer is good with one catch.

The one with
    MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE) is the one that works properly.

I am asking why the one without is problematic. Rolando seems to try to correct the correct ones.

Note: (I wonder whether this should be another question). After listening to others I tried:
After removing distinct

SELECT
  TB.ID,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1939967464102 106.76400325359,-6.1760032535898 106.78199674641)'
            ),
            TA.Latlong)
AND TA.Prominent >15
ORDER BY
  Distance
LIMIT
  0, 20

returning 0 rows

Then I tried removing the order by and limit

SELECT
  TB.ID,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1939967464102 106.76400325359,-6.1760032535898 106.78199674641)'
            ),
            TA.Latlong)
AND TA.Prominent >15

Which also result in 0 rows finally I use the original query and make sure I only remove

    AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)

to turn results from some results to no results

Finally I make the query really simple

SELECT BusinessID, Prominent
FROM
    tableauxiliary as TA
WHERE
  MBRContains(GeomFromText ('MULTIPOINT(-6.2209869856406 106.73701301436,-6.1490130143594 106.80898698564)'),TA.Latlong)

That's it the most basic query ever.

Still return no result

Now here's a miracle. I add 2 characters and it works again. The characters I add is =1

SELECT BusinessID
FROM tableauxiliary AS TA
WHERE MBRContains( GeomFromText( 'MULTIPOINT(-6.2209869856406 106.73701301436,-6.1490130143594 106.80898698564)' ) , TA.Latlong )
 =1

I got result

I guess it's time to report bugs.


texts: Foreign key referencing table with composite primary key when I only need 1 column association

texts:
So lets say we have 2 tables on SQL Server database:

CREATE TABLE VersionedData (
    Id INT NOT NULL,
    Version INT NOT NULL

    CONSTRAINT [PK_VerData]
      PRIMARY KEY (Id, Version)
)

And:

CREATE TABLE PointingTable (
    Id INT PRIMARY KEY,
    RefId INT NOT NULL

    CONSTRAINT [FK_Pnt_Ver] FOREIGN KEY (RefId)
      REFERENCES VersionedData (Id)
)

My idea of this relationship is when joining these 2 particular tables I only care about latest version of VersionedData table (meaning I will ALWAYS match with maximum version for specific ID value giving me exactly 1 row that I need).

Of course the code above causes error that I must reference both PK columns from PointingTable. I could add Version column for PointingTable - however I'd be stuck to that specific version even when newer one will be inserted for VersionedTable.

I'm struggling to figure out how to redesign this relationship, I've thought of computed column for PointingTable that would utilize scalar function for getting max version, but I think that would be overkill, there must be a better way.


texts: Trouble backing up remote database from local server

texts:
I have 2 servers LOCAL and REMOTE. REMOTE has a database 'remotedb'.

From REMOTE the following statement backs up the database just fine.

BACKUP DATABASE [remotedb] TO  DISK = N'C:\Temp\remotedb.bak'

Now I want to issue the backup from LOCAL. From LOCAL I create a linked server to REMOTE.

exec sp_addlinkedserver 'REMOTE'

From LOCAL I issue the same statement...

BACKUP DATABASE [remotedb] TO  DISK = N'C:\Temp\remotedb.bak'

I get the following error message...

Msg 911, Level 16, State 11, Line 3
Database 'remotedb' does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

I am SYSADMIN on both SQL Servers. Can someone tell me why I am not able to issue a backup command from LOCAL for remotedb and what can I do to make this work?

Much thanks!


texts: Inheritance discriminator column better int or varchar or enum?

texts:
What is the best practice for storing a discriminator column ? Does it make any difference ?

Any pros/cons of using one over the other ?

I am using MySql with Entity Framework but the question goes beyond the underlying technology i think.


texts: Is there any SQL Database that will use UDP replication?

texts:
I have to find a database that will ensure the UDP replication intercontinental.

Do you know if there is any SQL database that will support UDP replication?
If not, can I use MySQL replication (TCP/IP protocol) intercontinental (from China to Switzerland)? Will this work fine? Won't be any meltdowns? How can I prevent them?
The master will be in China and the slave in Switzerland. If the slave will be down for a long period of time, and the logs used to update the replications will be too big or not enough, how can I handle the replication? What is the solution?

Thank you and kind regards,

Andreea


texts:
I am running a private MySQL instance on WebFaction

WebFaction is a shared host who provides both for their public MySQL instance and for your own private MySQL instance. The private instance is a 1-click install using their system which then provides a root user and port to you. The public instance is always just localhost, and the private instance is localhost:port.

What is the proper way to set up a user for that private instance?

CREATE USER 'user'@'localhost:port' IDENTIFIED BY 'password';
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'user'@'%' IDENTIFIED BY 'password';

Does it matter?

I'm running into an issue trying to set up a Simplified Machines forum instance. The SMF forum will connect to localhost just fine, and it seems to connect to localhost:port okay (at the point in code where it makes the connection to the DB, it is successful), but then will only read and write to localhost and not localhost:port.

I have successfully created a db user which can connect to, read, and write via localhost:port, but refuses to do so via SMF.

Any thoughts?


texts:
I have a report table with the following index:

providerid, date

The table is around 30M records and it grows about 100K rows per day.

I want to use in the index above as a cluster, but as I understand I need to run the cluster table command each time that I want to cluster the data, otherwise new data isn't clustered.

Is there a way to define cluster index like SQL Server so that new rows are also clustered.

I cannot stop all my process each day (cluster needs an exclusive lock).

The query is:

select date,providerid,sum(*)
  from report_table
    where data < x
    and date > x
    group by date, providerid


texts: Load balancing with Windows Clustering and AlwaysOn Availability Groups

texts:
I have some questions regarding Clustering and AlwaysON.

If I need to build a 3 node cluster - 2 active and one passive. Each node will be 16 CPU and 128 GB RAM. Does this mean that I will have double resources. Will I get the max thread number (704) for each node?
For the 2 nodes accessing the same storage, will this lead to more
blocking?
For AlwaysOn, can I set 2 primary servers instead of one so I can
get some kind on load balancing. If yes how will the data be
synchronized?


texts: Sql Server trace? Who is running it and how to stop it?

texts:
Can you please help me out in finding who is running SQL Server Trace(profiler) on Server machine.?

It looks like, some people are running trace on live system on SQL DB, which make application super slow.

We need to find out who is running that or who ran that in the past? any idea how to do this?

Thanks.


texts: Storing date as integer (numeric), what are the advantages

texts:
Question 1

I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example

20120101  -- 01 Jan 2012

Is there any advantage of keeping numeric date system and not using SQL Datetime?

Question 2

Now I am trying to loop through numeric date to find customers between two dates. If the start and enddate encompass two months, I get thousands of records instead of just 60. Example:

create table #temp1(day int,capacity int) /* just a temp table */

declare @start int
declare @end int

set @start=20111201
set @end = 20120131

while (@start <= @end)
Begin
    insert into #temp1  /* I am storing things in #temp table so data looks pretty */
    exec usp_GetDailyCap @date1= @start

    set @start = @start + 1;
end

select * from #temp1

This pulls 8931 records instead of 60. Is there a better way to improve the logic above so I pull only valid dates? I tried IsDate and sub-queries but that did not quite work in an efficient way.


texts: SQL Job is getting failed randomly while trying to send the alert email. need help

texts:
We have configured a alert which triggers the email using a job. But the job fails randomly during the day and we don't receive the alert related mails. If I clear the ExternalmailQueue it starts working again.

Below are the logs I got before the job gets failed.

Windows Event Logs

Database Engine Instance=NODMSSQL1;Mail PID=2240;Error Message:Exception of type 'System.OutOfMemoryException' was thrown.
Database Engine Instance=NODMSSQL1;Mail PID=2240;Error Message:The connection is not open.

Database Mail Log

08/30/2017 21:30:31,,Information,15826,DatabaseMail process is shutting down,2240,,,8/30/2017 9:30:31 PM,..\sqladmin
08/30/2017 21:30:31,,Information,15825,Database Mail shutting down. Mail queue has been stopped.,2240,,,8/30/2017 9:30:31 PM,..\sqladmin
08/30/2017 21:30:31,,Success,15824,Activation successful.,,,,8/30/2017 9:30:31 PM,sa
08/30/2017 21:30:20,,Success,15823,Mail successfully sent.
08/30/2017 21:30:19,,Success,15821,Mail successfully sent.
.
.
.

08/30/2017 21:30:10,,Error,15803,

The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2017-08-30T21:30:10). Exception Message: Cannot
send mails to mail server. (Exceeded storage allocation. The server response
was: 5.3.4 Message size exceeds fixed maximum message size).
),2240,19923,,8/30/2017 9:30:10 PM,sa

Sql Agent Log

08/30/2017 21:30:38,,Error,[440] SQLiMail send mail failure (reason: )
08/30/2017 21:30:38,,Error,[298] SQLServer Error: 14641 Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. [SQLSTATE 42000]

Question

What do we have to reconfigure to reduce the errors when sending mails?


texts: Is it possible to access column by index instead of it's name/alias in query?

texts:
I want do something like this:

SELECT *
FROM
(
  SELECT 1, 2, 3
) AS subquery
WHERE subquery[0] = 1 -- I want to reference cloumn by its index in row


texts: I am relatively new to postgresql. Is there a way to sort a table by a column value and then save the changes permanently? (in my case the value is an integer)


texts:
I am very much new to Oracle environment. I was trying to connect to my Oracle server from another machine on the same network. I found out that I need to configure Oracle Listener on the server that I did using the "Net Configuration Assistant" tool.

After saving the configuration file, I opened the listener.ora file and saw that in my new listener the hostname is the IP/machine name of the Oracle server itself rather than the client machine, what I was thinking that the host name in the listener should be IP/machine name of the client machine? Is that right?


texts: Catalog of LinkedServer2 from Server1 shows databases on Server1

texts:
I'm on Server1 and set up a linked server to LinkedServer2, but the catalog shows the databases on Server1!

I am sure I've done something bone-headed or am overlooking something horrendously obvious, but I need a nudge in the right direction.

Server1: Enterprise SQL 2012
LinkedServer2: Standard SQL 2008 R2


texts: Query optimization on a large table in PostgreSQL

texts:
I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a table of assessment responses (dbname.responses) which is about 14 million records. The query tries to obtain a district average for a particular assessment. So for instance average score on exam 1, for all the lastest responses from students taking that exam. The response table essentially contains all the responses for assessments. only the latest response from a student is factored into the score. Here it goes:

SELECT internal_scorable_key,
sum(score),
sum(case when response_type = 'NOT_SCORED_RESPONSE' then 1 else 0 end) AS "not_valid_responses",
sum(case when response_type = 'IS_SCORED_RESPONSE' then 1 else 0 end) AS "is_valid_responses"
FROM (
 SELECT DISTINCT ON (administration_key, internal_scorable_key)
 administration_key, internal_scorable_key, assesment_response_key, score, response_type
 FROM dbname.response
 JOIN dbname.assessment_admin USING (administration_key)
 WHERE
 is_disabled=false
 AND internal_school_yr_key=5
 AND internal_inst_key_of_assessment IN (4564)
 AND internal_assess_key = (SELECT internal_assess_key FROM dbname.internal_assess WHERE ext_id = '11300-2')
 ORDER BY administration_key, internal_scorable_key, assesment_response_key DESC
) all_the_things
GROUP BY internal_scorable_key

dbname.response Table...

response_key bigint NOT NULL,
administration_key bigint NOT NULL,
internal_scorable_key bigint NOT NULL,
score integer NOT NULL,
response_type character varying(255) NOT NULL,
client_create_date timestamp with time zone NOT NULL,
upload_date timestamp with time zone NOT NULL,
response_source character varying(255) NOT NULL,
print_id character varying(255) NOT NULL,
assessment_administration_page_key bigint,
constructed_response_key bigint,
answer_indexes text NOT NULL,

dbname.assessment_admin Table...

administration_key bigint NOT NULL,
internal_assess_key bigint NOT NULL,
internal_student_key bigint NOT NULL,
administration_id bigint NOT NULL,
internal_inst_key_of_assessment bigint,
account_key bigint,
internal_school_yr_key bigint NOT NULL,
is_disabled boolean NOT NULL,

dbname.internal_assess Table...

internal_assess_key bigint NOT NULL,
ext_id character varying(255) NOT NULL,
created_at timestamp with time zone NOT NULL DEFAULT now(),

here is the postgres explain image:

"HashAggregate  (cost=51.89..52.51 rows=62 width=28)"
"  ->  Unique  (cost=49.87..50.34 rows=62 width=44)"
"        InitPlan 1 (returns $0)"
"          ->  Seq Scan on internal_assess  (cost=0.00..1.77 rows=1 width=8)"
"                Filter: ((external_id)::text = '2568-1'::text)"
"        ->  Sort  (cost=48.10..48.25 rows=62 width=44)"
"              Sort Key: response.administration_key, response.internal_scorable_key, response.response_key"
"              ->  Nested Loop  (cost=4.37..46.25 rows=62 width=44)"
"                    ->  Bitmap Heap Scan on assessment_admin  (cost=4.37..18.34 rows=3 width=8)"
"                          Recheck Cond: (internal_assess_key = $0)"
"                          Filter: ((NOT is_disabled) AND (internal_school_yr_key = 1) AND (internal_inst_key_of_assessment = 1))"
"                          ->  Bitmap Index Scan on student_assessment_administration_id_key  (cost=0.00..4.37 rows=16 width=0)"
"                                Index Cond: (internal_assess_key = $0)"
"                    ->  Index Scan using idx_response_assessmentadministrationkey on response  (cost=0.00..9.03 rows=27 width=44)"
"                          Index Cond: (administration_key = assessment_admin.administration_key)"

the only indexes are on the responses table:

CREATE INDEX idx_response_assessmentadministrationkey
  ON response
  USING btree
  (administration_key);

and

CREATE INDEX idx_response_uploaddate
  ON response
  USING btree
  (upload_date);


texts: SQL Server AG in primary site with separate WSFC AG in secondary site

texts:
I would like some feedback from those experienced with SQL Server Availability Groups (AGs) in multiple sites.

We have a scenario where there are five SQL Server 2012 Enterprise Edition servers in a one-site AG under the same WSFC cluster.

We are looking to setup a secondary DR site and would like some guidance on whether the WSFC cluster should 'span' both sites or whether we should use Log Shipping to the remote DR site, targeting a separate AG in the DR site.

We are going to have a 1 Gbps pipe between the two sites with a very low < 15ms response time, but eventually we want to move to an alternate secondary site which will be ~70ms away and so I am uncertain whether we can make use of WSFC for such a latent connection.

This first DR site setup needs to be a 'template' for how we will be setting up the final DR site which is geographically disparate with the higher latency response time.  Therefore, please keep this in context in your response(s).

Some questions:

Can a separate AG be a 'recipient' (restore target) of Log Shipping?  In other words, can I have the primary server in a separate Availability Group be applying log backups (from log shipping) and when ready to cut over to this secondary AG, issue a RESTORE BACKUP WITH RECOVERY?
Ideally, it would be preferable to be able to more easily 'fail back' to the primary site.  If we had a WSFC spanning both sites and the same AG on said cluster, I gather this would be far easier to accomplish.  Network latency will be a concern.

Thanks for your advice.


texts: create sql server login for own domain account

texts:
I have a working SQL Server instance to administer.  I do not have a SQL Server login and do not know any other logins. I do have a Windows domain login to the server as a local admin.  How can I add my Windows account as a login to SQL Server when I don't know any existing login to the instance?

I can connect using PowerShell:

cd sql\uk-blahblah\default\logins

but cannot retrieve any detail:

gci

SQL Server PowerShell provider error: Failed to read child items. [Failed to connect to server uk-blahblah. --> Login failed for user 'AUTOdomain\admin-account'.]

I can stop the SQL Service and restart from an elevated command prompt:

sqlservr.exe -m -c

but trying to run sqlcmd still fails:

sqlcmd -S UK-blahblah
Login failed for user 'AUTOdomain\admin-account'

I'm sure many of you must have found a way of adding your login to a server where you don't know any other logins, but I'm struggling a little here.

I'm using SQL Server 2012 SP2 Enterprise Edition.


texts: Will the arrayFilter use an index if I created one matching the filter?

texts:
I am using updateOne to update some of the fields of an object embedded in an array property of a document.
The document itself has a valid _id, which is being used in the query of the updateOne call.  The array has multiple elements and arrayFilters are used to isolate the one I want to change.
explain() does not answer this question, the output itself only lists the stages for UPDATE and IDHACK.
db.getCollection('ColName').updateOne({_id: 'validId'}, {'$set': {'array.$[filter].field': 'edited'} },
    { 'upsert': false,
      'arrayFilters': [{
        "filter.F1" : "V1",
        "filter.F2" : "V2",
        "filter.F3" : "V3",
        "filter.F4" : "V4",
        "filter.F5" : "V5"
      }]
  })


texts: Identical query & instance but other execution plans & performance

texts:
I have 2 identical queries . One is executed with parameters, the other is executed with hardcoded values. Somehow sql server is creating 2 different execution plans for the 2 queries. The first one is almost instantaniously completed. The other one takes +- 2 seconds to complete. The application is creating the second query(which takes the longest) .

How do i make the parameterized query take the same execution plan as the other one? I already tried rebuilding the statistics & indexes but with no success.

The execution plans in the screenshot are scrolled to the right top position, showing a clear difference in approach.


texts: Extend a single week's win/loss ratio calculation to a whole season

texts:
I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project.

The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be something missing please point it out):

It's a database of a fantasy sports league, and we want to calculate an 'all-play record'.

My understanding is that the 'all-play record' is generated by taking every team in a given league's scores for a given week and assuming they all play each other.

I've written a query that does this for a given week:

WITH weekly_points_table (team_id, max_points)
AS
(
  SELECT teams.id, SUM(player_scores.points) as max_points
  FROM player_scores
  INNER JOIN weeks ON weeks.id = player_scores.week_id
  INNER JOIN teams ON teams.id = player_scores.team_id
  WHERE (team_id IN (?) AND weeks.number = ? and weeks.year = ?)
  GROUP BY teams.id
)
SELECT *
FROM weekly_points_table
WHERE max_points < (SELECT max_points
  FROM weekly_points_table
  WHERE team_id = ?
)

The list of team_ids, the given week number/year, and the final team_id are passed in.

Wins are calculated with the above query, losses are calculated by simply flipping the sign, and ties are just (count of all teams - 1) - (wins + losses) for a given week.

But now I'm at a loss at how to extend this to a given season.

A season is a collection of weeks where the year is identical, and week numbers go from 1 to 16.

Right now we're literally just taking the single-week query and running it in a for-loop. (Before that, we were using nested for loops to calculate the wins/losses/ties, and you can imagine how slow that can be.)

Pretty much, how do I take the calculated wins/losses for a given week and add them up for all 16 weeks?


texts: Hi disk I/O and crash when turning on sync_binlog in MySQL

texts:
I have a MySQL master server (MariaDB 10.0.17) that have a very odd behavior. It has one slave fetching data from it.

When I set global sync_binlog = 1, the server I/O goes very high and after some hours the server crashes. In the graph bellow, at 15:20 the server crashes. Then from 15:20 til ~15:40 it is recovering a MyISAM table I have with more then 120 million rows. Then at 15:40 it gets back to normal operation. The boot configuration for sync_binlog is 0

At 16:15, I set global sync_binlog = 1, and the I/O goes up again. At 16:45, I set global sync_binlog = 1 and the I/O goes down.

Why this is happening? Is there some way to avoid this high I/O and also the crash?


texts: Can expdp be piped directly into impdp, without intermediate files?

texts:
We often need to copy fairly large databases from one server to another. The current method uses expdp on the source, and then impdp on the destination. This gets pretty horrible because:

It takes up a lot of space to store the intermediary files.
It takes a lot more time than necessary because the writing and the reading happen in sequence rather than in parallel.

Is there a way to arrange things so that the stdout of expdp are piped directly into stdin of impdp? For example:

expdp / parfile=export.par | ssh remote impdp parfile=import.par

What should I put into these hypothetical export.par and import.par respectively?

If not straight stdout into stdin, can expdp write to and impdp -- read from -- TCP-socket(s)? I could have those plumbed with netcat...


texts:
In Oracle 10g I have a listener problem,

Here is tnsnames.ora :

MYTNS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
       (PROTOCOL = TCP)
       (HOST = testiold)
       (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mysid)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = IPC)
        (KEY = EXTPROC)
      )
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

and the listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mysid)
      (ORACLE_HOME = /oracle/10.2.4/db/OraHome_1)
      (SID_NAME = mysid)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = testiold)
        (PORT = 1521)
      )
     )
    )
  )

The proble is I cannot start the listener with this configs, when I try lsnrctl start I got this:

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production on 14-AUG-2012 22:11:44

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /oracle/10.2.4/db/OraHome_1/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
System parameter file is /oracle/10.2.4/db/OraHome_1/network/admin/listener.ora
Log messages written to /oracle/10.2.4/db/OraHome_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testiold)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testiold)(PORT=1521)))
TNS-12535: TNS:operation timed out
 TNS-12560: TNS:protocol adapter error
  TNS-00505: Operation timed out
   IBM/AIX RISC System/6000 Error: 78: Connection timed out

and whenever I try to connect to DB it says:

ORA-28547: connection to server failed, probable Oracle Net admin error

Can you please help me?


texts: INSERT to a table from a database to other (same SQL Server) using Dynamic SQL

texts:
I need to copy a record from a Database DBa, Table Tbl1 to Database DBx, Table Tbl1 and get the Scope Identity inside the same SQL Server 2005 server

I have already checked this:
How to Dynamically change the database using TSQL

but my issue is more complicated.

I do want just to execute something in the DBx being inside DBa.
I want to copy a record from one database to one of the other (same) databases in the same server and get the scope_identity back.

The Dynamic SQL is a string. What do we do when we want to copy variables of other datatypes as well?

Example code:

CREATE PROCEDURE dbo.pr_consolidation_copy_group @group_id numeric(10,0),@database_to varchar(100)
    AS

    DECLARE @group_name char(100),@arrival datetime,@departure datetime,@contact_id char(82)

    SELECT @group_name = group_name ,
    @arrival = arrival,
    @departure = departure,
    @contact_id = contact_id
    FROM grp
    WHERE group_id = @group_id

    DECLARE @exec nvarchar(max) = QUOTENAME(@database_to) + N'.sys.sp_executesql',
            @sql  nvarchar(max) = N'INSERT grp(group_name, arrival, departure, contact_id)
SELECT @group_name, @arrival, @departure, @contact_id;SELECT SCOPE_IDENTITY()'; //How do we handle the variables?

        EXEC @exec @sql;
        go

Get all variables to string and put CONVERT inside the Dynamic SQL?
I hope I can find another solution because my table is around 300 columns :(


texts:
I want to update my MySQL table with batches.

The table has 10000000+ records, so I want to update first 1000 rows, once it is done then update 1001 to 2000 rows. I need to make it as a loop.


texts: It's possible to avoid overload on a Mysql slave by slowing down replication?

texts:
I have a slave on a machine less powerful than the the master (8CPU/8GB on the master, 2CPU/4GB on the slave, MySQL 5.7).

Replication is binlog ROW based

As the db is increasing on the master, the slave started to overload the CPU slightly.

I wonder if it's possible to "slow down" the ingestion of binlogs on the slave in order to delay the unavoidable increase of power on the slave machine.

A small lag would not be a problem in my case.


texts: Is it possible to restore single PostgreSQL database to PITR?

texts:
I have PostgreSQL instance with n databases on it.
Is it possible to restore just a single database using PITR ?
# restore
tar xvfz /var/lib/postgresql/db_file_backup.tar.gz -C /var/lib/postgresql/10/main/

# add recovery.conf
nano /var/lib/postgresql/10/main/recovery.conf

  restore_command = 'cp /var/lib/postgresql/pg_log_archive/%f %p'
  recovery_target_time = '2018-02-22 15:20:00 EST'

# start DB
sudo systemctl start postgresql@10-main

I see that pg_restore contains dbname parameter. But it's logical. I need to restore to specific Point-In-Time.
pg_restore --help pg_restore restores a PostgreSQL database from an archive created by pg_dump. Usage:  pg_restore [OPTION]... [FILE] General options:  -d, --dbname=NAME        connect to database name


texts:
There is SQL Agent Job which is doing backup of database and after backup there are several steps for delete database objects, creating some files, etc... Job is pretty much generic one, but I need some kind of check if it's there some backup of database which is running and if it's running how to implement some kind of WHILE loop, to wait until backup is finished and then to start this one.

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DB_Backup',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Create Backup]    Script Date: 11/28/2016 1:41:01 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'USE [DB]
GO
DBCC SHRINKFILE (N''DB_log'' , 2)
GO
DBCC SHRINKFILE (N''DB_log2'' , 0)
go
DBCC SHRINKFILE (N''DB'' , 2)
GO
USE MASTER
GO

BACKUP DATABASE [DB] TO  DISK = ''C:\DBbackup''
GO
'


texts: How to update a point table when a polygon table is modified

texts:
I am trying to create a function that update table A (point) when table B (polygon) is modified. The reference to B must be changed in A. I can't get it to work. There are no overlaps in the polygons. It really is just to update the polygon ID in the points table. I was thinking of ST_Within to do it.

CREATE OR REPLACE FUNCTION maj_A()
    RETURNS trigger AS $foo$
BEGIN
    UPDATE public.Points AS A
    SET A.a = B.a,
        A.b = B.b
    FROM public.Polygons AS B
    WHERE ST_WITHIN(Points.geom, Polygons.geom) AND NEW.geom <> OLD.geom;
    RETURN NEW;
END;

$foo$ LANGUAGE plpgsql;

I am using postgre 10 and postgis 2.4. Any working function is welcome. The function is linked to a trigger.


texts: Visual studio 2015 closed when re-run SSIS pack

texts:
I recently installed visual studio 2015 and there is a really simple action that I can not make,
When I rerun (Ctr+Shift+F5) SSIS package (re-run, not run), it causes visual studio to throw this error:

I do not know if this is related, but the SSIS project is on the sql server 2014.

This is a routine operation that worked without problems at visual studio 2013.


texts: How does MySQL decide what partitions to load into memory?

texts:
I have a server where the MySQL indexes won't fit into memory (2TB total index, 512GB RAM on machine), and am wondering how MySQL decides what parts to put into memory and what not.

The relevant tables are range partitioned on ID, and I'm told (but can't find anything proving this) that the last 3 months is available in memory.

I'm hoping there's a more substantial answer available somewhere.


texts: SQL Full-text Filter Daemon Launcher failing to start with error "Access is Denied"

texts:
When attempting to start the SQL Full-text Filter Daemon Launcher service through the SQL Server Configuration Manager, it attempts to launch, but then fails.

In the event log we get the following error message: "The SQL Full-text Filter Daemon Launcher (instancename) service failed to start due to the following error:
Access is denied."

It is currently set in "Manual" start mode.  And uses the default service account generated at installation, "NT Service\MSSQLFDLauncher$instancename".  Named pipes is enabled.

This service was not installed as part of the original SQL Server installation, but done after.

This is a SQL Server 2016 Enterprise (13.0.5216.0) instance.


texts: How to make Oracle accept any password for login?

texts:
We have an old application which we need data and reports from. The application is configured to retrieve data from Oracle DB and the login username and password are hardcoded in the application. However, we do not have the source code as it was lost, we only have the exe.

We have recovered a database dump and were able to successfully point the app to the database, but we are getting wrong password errors and we cannot find out the old password to change the password to.

Is there anyway Oracle can accept login requests using any password provided so the application can log in successfully?


texts: How to prevent multiple queries in this case?

texts:
I am using Postgres. I have 2 tables.
PS : week starts from monday.
Time (id (bigInt), startOfWeek (Date), startOfMonth (Date), startOfQuarter(Date), day (Date) ...)

and
User (id (bigInt), timeId (FK --> Time), total (Int)).

Given 2 dates (start and end), a dimension (day / week /month / quarter), I need to find out the total for each day / week / month / quarter.
Example :
Time (id (1), startOfWeek ('07-13-2015'), startOfMonth ('07-01-2015'), startOfQuarter('07-01-2015'), day ('07-13-2015') ...)

Time (id (2), startOfWeek ('07-13-2015'), startOfMonth ('07-01-2015'), startOfQuarter('07-01-2015'), day ('07-14-2015') ...)

//for 22nd july.
Time (id (3), startOfWeek ('07-20-2015'), startOfMonth ('07-01-2015'), startOfQuarter('07-01-2015'), day ('07-22-2015') ...)

User (id (11), timeId (1), total (5)).
User (id (12), timeId (2), total (10)).
...

Given 2 date values start and end, and dimension 'd' (W /M /Q etc) how can I write a query to fetch values from User table like :
'07-13-2015' 100
'07-20-2015' 122 etc

The problem is the startOfWeek field will be same for 7 days. I need to get the value of only the first row in User table where startOfWeek changes.

I could do it using multiple queries :

get all startOfWeeks from Time table

for each startOfWeek, query User table and do first(..)

Is there a better / more efficient solution?


texts: Good example of MDX vs SQL for analytical queries

texts:
Can anybody show me a good example of MDX's advantages over regular SQL when doing analytical queries? I would like to compare an MDX query with an SQL query that gives similar results.

Wikipedia says:

  While it is possible to translate some of these into traditional SQL,
  it would frequently require the synthesis of clumsy SQL expressions
  even for very simple MDX expressions.

But there is neither a citation nor example. I am fully aware the underlying data must be organized differently, and OLAP will require more processing and storage per insert. (My proposal is to move from an Oracle RDBMS to Apache Kylin + Hadoop)

Context: I am trying to convince my company that we should be querying an OLAP database instead of an OLTP database. Most SIEM queries make heavy use of group-by, sort, and aggregation. Besides the performance boost, I think OLAP (MDX) queries would be more concise and easier to read/write than the equivalent OLTP SQL. A concrete example would drive the point home, but I am not an expert at SQL, much less MDX...

If it helps, here is a sample SIEM-related SQL query for firewall events that happened in the past week:

SELECT   'Seoul Average' AS term,
         Substr(To_char(idate, 'HH24:MI'), 0, 4)
                  || '0'        AS event_time ,
         Round(Avg(tot_accept)) AS cnt
FROM     (
                SELECT                     *
                FROM   st_event_100_#yyyymm-1m#
                WHERE  idate BETWEEN trunc(sysdate, 'iw')-7 AND trunc(sysdate, 'iw')-3 #stat_monitor_group_query#
                UNION ALL
                SELECT *
                FROM   st_event_100_#yyyymm#
                WHERE  idate BETWEEN trunc(sysdate, 'iw')-7 AND trunc(sysdate, 'iw')-3 #stat_monitor_group_query# ) pm
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4)
                  || '0'
UNION ALL
SELECT   'today' AS term ,
         substr(to_char(idate, 'HH24:MI'), 0, 4)
                  || '0'        AS event_time ,
         round(avg(tot_accept)) AS cnt
FROM     st_event_100_#yyyymm# cm
WHERE    idate >= trunc(sysdate) #stat_monitor_group_query#
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4)
                  || '0'
ORDER BY term DESC,
         event_time ASC


texts: POSTGRESQL : trigger running in background with listen and notify

texts:
I am working on a PostgreSQL database containing geographical information; in my OBS_TABLE I have:

the coordinates of each point (longitude, latitude)
the value of the ocean's temperature "TEMP" at this point
and a cell number "HGRID" where my point is (cell number defined by 4 geographical points)

I have a statistics table STATS_TABLE organized by cell number which I would like to update each time a point is modified. This table contains information such as the mean value of temperature in the cell, the minimum found in the cell...
I tried to use a trigger function, but for each modification I have to wait that the trigger finished it's computation before being able to do something else, I have a lot of data, and I need to find something more efficient.
I would like the trigger to be executed in background so it is not blocking me in my application.
It seems that notify and listen could be a solution but I don't understand where/how I should gave the order to compute the statistics on a cell when there is a modification.
Anyone to help me or who encountered a similar issue ?
Here is a simplified version of my original trigger function :
  # function update_stats
  CREATE OR REPLACE FUNCTION update_stats() RETURNS TRIGGER AS $update_stats$
     <<set_update>>
      DECLARE
        hgrid integer;
      BEGIN
        hgrid = NEW."hgrid";
      UPDATE STATS_TABLE SET
         "MIN" = (select min(O."TEMP") "TEMP MIN" from OBS_TABLE O WHERE O."HGRID"=hgrid),
         "MEAN" = (select avg(O."TEMP") "TEMP MEAN" from OBS_TABLE O WHERE O."HGRID"=hgrid)
      WHERE "HGRID"=hgrid;
      RETURN NULL;
    END;
  $update_stats$ LANGUAGE plpgsql ;

  # Creation of the trigger
  CREATE TRIGGER update_stats AFTER UPDATE ON OBS_TABLE FOR EACH ROW EXECUTE PROCEDURE update_stats();

How do I use listen and notify to resolve my issue? Or any other solution if you have. Thanks in advance, I've been stuck on these things for so long ...


texts: Can I use MariaDB as a replication slave for MySQL?

texts:
I've been a MySQL user/administrator for many years and I've been considering a switch to MariaDB. We have a statement-replicated setup with a MySQL slave and I've been thinking that one way to ease-into a MariaDB environment might be to swap the slave out for MariaDB until we're comfortable, then switch the master to MariaDB.

Is that something that is possible? I suspect it is, since the replication interface is fairly simple, especially for a statement-replicated setup.

Is that something that makes any sense?

UPDATE
I happen to be using MySQL 5.5 as the master and MySQL 5.6 as the replicated slave in my current environment, just in case that has any bearing on anything. But I'm curious if different versions might be better or worse for a mixed-vendor setup.


texts:
I noticed that when I right click manually on an index and choose "Rebuild" or "Reorganize", its not giving any indication that its doing so, then I click on properties again for the same index, I see the fragmentation level is still at the same percentage even after refreshing!

Am I missing something?

I'm using the SQL Server 2008 R2 Standard edition

Thanks in advance


texts: How can I get the actual SSAS error when processing a cube from a SQL Agent job?

texts:
I have a SASS cube on one server and a SSIS package on another server that triggers processing the cube. The SSIS package is executed via a scheduled SQL Agent job. I have enabled error reporting for the SSIS package via an OnError event handler that sends an email message with the MessageSource property set to @[System::ErrorDescription], but this has two problems:

The first problem is that I receive 4 emails for every failure, containing:

"Internal error: The operation terminated unsuccessfully."
"Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation."
"Errors in the OLAP storage engine: An error occurred while processing the 'XXX Fact' partition of the 'XXX Fact' measure group for the 'XXX' cube from the XXX database."
"Server: The current operation was cancelled because another operation in the transaction failed."

Only the third of these is useful to me, so is it possible to prevent the others from being sent?

The second problem is that even the third error message is only barely useful - if I process the affected cube manually on the SSAS server, I get the real error:

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_XXXFact', Column: 'XXXId', Value: 'YYY'. The attribute is 'XXX Id'.

and:

Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute XXX Id of Dimension: Dim XXX from Database: XXX, Cube: XXX, Measure Group: XXX Fact, Partition: XXX Fact, Record: YYY.

How can I have the OnError handler in my SSIS package retrieve these detailed error messages and send them via email instead of the vague "An error occurred" messages it currently sends?


texts: Powershell Invoke-Sqlcmd Implicit Transaction

texts:
Does Powershell, through invoke-sqlcmd or through running a query directly through a cmdlet, wrap a query in an implicit transaction? And if so, is there a way to work around this should my query require it?

For a trivial example of what I'm getting at:

$sqlcmd = "WHILE(1=1)
           BEGIN
               BEGIN TRANSACTION
                   SELECT @@TRANCOUNT;
               COMMIT TRANSACTION
               SELECT @@TRANCOUNT;
               IF(@@TRANCOUNT = 0)
                   BREAK;
           END"

Running this through Powershell should result in something that never ends, whereas running it in SSMS results in a query that ends in one iteration. Would immediately committing a transaction at the start work, or are there other ways to work around this in Powershell or through changing the query?

To rephrase the question, can we support proper transaction scoping using Powershell?


texts: how can I track down the source of "Internal connection fatal error"?

texts:
I'm working on a 6-700 line SQL script today. I'm running it from SSMS. Pointing at a SQL 11.0 db. About half the time I run it, I get:

An error occurred while executing batch. Error message is: Internal connection fatal error.

Googling for that didn't yield anything worthwhile. What could be causing this error? There aren't any RAISERRORs. It doesn't happen every time but it has happened about 20 times today. How can I track down the actual source of this error?