texts: Upgrade SQL Server 2005 to SQL Server 2012

texts:
I have a test environment set up with the following:

Virtual Machine (Hyper-V)
Windows Server 2008 R2 SP1(x64-bit)
Windows SQL Server 2005 Developer Edition SP4 (x64-bit)(default instance name)

1 Database with Merge Replication set up... 3 publications with 2 subscribers each.

We are testing the procedure for upgrading to SQL Server 2012 Developer Edition (sp1)...

I have downloaded the iso for 64bit SQL Server 2012 Dev. (sp1) from MSDN (full licensed copy) and have started going down the upgrade path. At one of the final stages, where it begins a check for different criteria, we run into a bizarre issue as outlined below:

Rule "Upgrade architecture mismatch" failed. The CPU architectures of upgrading feature(s) are different. To upgrade these features, Setup architecture must be the same as the features being installed.

Furthermore, in the upgrade log file, I found the following (more verbose) output:

(03) 2012-12-28 14:02:45 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.BlockMixedArchitecture
(03) 2012-12-28 14:02:45 Slp: Rule 'BlockMixedArchitectureUpgrade' detection result: IsMixedArchitectureInstall= True
(03) 2012-12-28 14:02:45 Slp: Evaluating rule        : BlockMixedArchitectureUpgrade
(03) 2012-12-28 14:02:45 Slp: Rule running on machine: REPLICATIONTEST
(03) 2012-12-28 14:02:45 Slp: Rule evaluation done   : Failed
(03) 2012-12-28 14:02:45 Slp: Rule evaluation message: The CPU architectures of upgrading feature(s) and this installation program are different. To upgrade these features, Setup architecture must be the same as the features being installed.

I have made sure that we are on an x64 system and not an ia64 and I see no reason why a mismatch would be occurring. Something to note: We cannot simply install SQL Server 2012, and reattach the databases to it as we have over 20 live databases (in production) with over 40 publications set up... this would be an absolute nightmare if we had to re-set up replication.

Here is the version info for the SQL Server 2005 setup:

Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

9.00.5000.00 indicated SP4.

I will be calling Microsoft support about this tomorrow.


texts: Find transactions that are filling up the version store

texts:
we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases.

Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be the culprit.

We monitor the TempDB usage through sys.dm_db_file_space_usage and once we see that the version store is increasing (as reported by  version_store_reserved_page_count) we would like to identity the transactions that are activily using the version store.

I'm using the following statement to find transactions that are using the version store:

SELECT db_name(spu.database_id) as database_name,
       at.transaction_begin_time as begin_time,
       case
         when at.transaction_state in (0,1) then 'init'
         when at.transaction_state = 2 then 'active'
         when at.transaction_state = 3 then 'ended'
         when at.transaction_state = 4 then 'committing'
         when at.transaction_state = 6 then 'comitted'
         when at.transaction_state = 7 then 'rolling back'
         when at.transaction_state = 6 then 'rolled back'
         else 'other'
       end as transaction_state,
       ast.elapsed_time_seconds as elapsed_seconds,
       ses.program_name,
       ses.row_count,
       (spu.user_objects_alloc_page_count * 8) AS user_objects_kb,
       (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb,
       (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb,
       (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb
FROM sys.dm_tran_active_snapshot_database_transactions ast
  JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id
  JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
  JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
ORDER BY elapsed_time_seconds DESC
;

But this doesn't help me in identifying how much space each transaction is actually using in the version store.

Is there a way to get the information about the space usage in the version store per transaction (or even better: per statement)?

Edit: The "potential duplicate" (How to identify which query is filling up the tempdb transaction log?) does not take the version store into account (only temp tables, table variables, and space used for sort and hash operations).

In fact the accepted solution will not show anything for transactions that only use the version store (at least not for me)


texts:
So, I know that there are 3 modes of startup modes available for the instance which is NOMOUNT, MOUNT, and OPEN.
What I want to know is in NOMOUNT mode it is stated that the instance started but still not associated with the Database then Who activates the instance?  Is it the database itself or is it something else?

And If I use the command shutdown then is the instance only that got shutdown or is the database too got shutdown?

Thanks in advance!


texts:
I have a simple query:

SELECT a.name AS a_name,
       b.description AS b_description
    FROM a
    JOIN b ON a.id = b.a_id
    WHERE b.table_name IS NULL;

(Names changed to protect the innocent)

This usually takes ~3ms to run. Sometimes, this query hangs. It has currently been hanging for 3 days. Luckily, I'm in a position where I can try and debug this in its current state, for a while. It appears there's nothing blocking it (found by querying pg_locks and pg_stat_activity), though it is blocking other things.

Even with the other query in a hung state, I can run the same query again, and it completes successfully in the expected time period.

Things that are likely to be involved: we currently have two cron jobs (don't ask) that dump the database. I can find the exact commands being run if it's likely to be relevant.

Any suggestions on how to debug this gratefully received!


texts: Oracle: Decreasing PCTFREE on large table

texts:
Question about Oracle Database Enterprise Edition version 19.10.0.0.0 running as a standalone instance.
I have a table with billions of rows. The table has always had PCTFREE set to 10.
It turns out that we only ever perform inserts on this table, no updates, no deletions. I would like to save disk space by lowering PCTFREE to 0.
As I understand it, if I just do ALTER TABLE x PCTFREE 0 then in our case, only future allocated blocks are affected. But all the millions of existing blocks will never be filled above 90% because we never update or delete those rows.
Is there a way to get all the old blocks "re-evaluated" and put back on the freelist after I lower PCTFREE?
I would prefer not to move all of the data (e.g. use CTAS, or expdp+impdp, or delete+insert every row, or dbms_redefinition) because the table is so big that this would take forever and/or use enormous amounts of intermediate space. I just need the database to know that it's now okay to put more rows in the old blocks. Is this possible?


texts: Insert dependent records in multiple tables, MSSERVER 2012

texts:
I'm trying to migrate Data from a Excel file to a newly build Database.

I already inserted all data from the Excel file to the Database and now need to insert it in three newly dependable tables.

The old database contains all data in a single table, in the new Database I would like to split the Data in three different tables (Address, ContactData and Company).

I tried doing it like this:

INSERT INTO CRM_Beta.dbo.Adress(Street, City, ZIP, State, Country)
    (SELECT Adress, City, ZIP, State, Country
     FROM ACT_Data.dbo.ActData)

INSERT INTO CRM_Beta.dbo.Contactdata(Phonenumber, Mobilenumber, Email, Adress)
    (SELECT PhoneNumber, MobileNumber, [Privat-Email], IDENT_CURRENT('Adress')
     FROM ACT_Data.dbo.ActData)

INSERT INTO CRM_Beta.dbo.Company(Name, Website, Rating, Contactdata, Protocol, Documents)
    (SELECT Company, [Web-Site], '', IDENT_CURRENT('Contactdata'), @@ROWCOUNT, @@ROWCOUNT
     FROM ACT_Data.dbo.ActData WHERE Company != NULL)

But it will only inserts the last inserted ID from the Query before.

Lets say the first table contains two entries in the format:

| Address | City | ZIP | State | Country | PhoneNumber | MobileNumber | private_mail | Company

Now i need to split this into three tables:

All Address Data (Street, State ...)
All Contact Data (Phone, Email ...)
All Company Data (Name, ...)

while linking each ID to the corresponding Foreign Key ID.

The three new tables have the format:

Company PK: ID, Name, Website, Rating,  FK ContactData, FK Protocol, FK Documents
ContactData PK: ID, Phonenumber, Mobilenumber,Email, FK Address
Address PK: ID, Street, City, ZIP, State, Country

I can change the Database if necassary or redesign if you think im doing something stupid ;)

Thanks for the help.

Edit1: I have tried using OUTPUT but i didn't figure out how to work with it yet and therefore have problems applying it to my case.

The Source file is a simple txt file i imported with the SQL-Server Assistant, so i could easily add keys or indexes, or use custom imports.

Edit2: I managed to do what i tried to accomplish in a similiar way that
 @Jonathan Fite showed, it definitely lead me into the right direction.

I did not understand how this would have worked with the Identity_Insert mentioned by @David Spillett and therefore will give the right answer to @Jonathan Fite


texts: Unable to get any type of connection using MySQL on LAN

texts:
My question is rather simple, I have a Windows 7 machine that is hosting an Apache server and a MySQL server using the XAMPP control panel. Both of these have been installed as a service on the machine. I am trying to connect to the MySQL server from a CentOS machine that is on the same LAN. When issuing the command:

mysql -h *IPADD* -u *UNAME* -p*PWORD*

I get this error:

ERROR 2003 (HY000): Can't connect to MySQL server on '172.26.96.166' (110)

I also see no indication in any logs on the Windows machine that there was a connection attempt.

I have spent much time trying to figure out a cause for this and I cannot seem to figure out the issue. I have:

Checked the firewall and explicitly made an exception for mysql

Installed MySQL as a service on the machine

Used the netstat command to verify that the ports are open

Checked and re-checked the username and password being used

Checked the host associated with the used credentials (the host is set to the wild card (%)

After doing all of this, I cant seem to find anything wrong or that would be blocking it. To give some sort of context, I am using this on a medium sized network and my account on the Windows machine is not Admin. However, I have had extensive help from an admin who has been the one authenticating me to change the firewall and what have you. That being said, there still may be restrictions on his account due to some grouping policies or the way the computer is imaged.

It may also be important to note that I am not directly in front of the CentOS machine, I am using "Putty" on the Windows machine to ssh into the machine to issue the MySQL commands

Anything I may have missed or any advice would be much appreciated.


texts: snychronize multiple databases using database replication

texts:
As I show in the picture , I have three database servers (different town location) and client computers each one is running software.
Database schema for those three servers are same.

And now I have to synchronize those three servers.
So I upload the database to smarterasp.net.
I want to access this database from different local server and synchronize every Transaction ( Insert,Update,Delete ).

I found SQL Server Replication and there are three types of replication

Transactional replication
Merge replication
Snapshot replication

What approach should I use for this ?
Or any other way which is more suitable ?

Note: Sometime , the local server may be offline due to the condition of internet connection.


texts: Selecting an intersection with a fixed given percentage between two tables

texts:
I working with PostgreSQL and I have two tables:

Serieses

SeriesID
716
718
214
210

Viewers

ViewID    SeriesID
10          716
12          718
10          718
15          770
10          210
12          214
12          210

How can I create a query which for each viewer if it has series intersection more than 70% with table serieses I add it to the final result?

In our example we will get:

Result:

ViewerID
10
12


texts: Investigating Failures of a Subreport in SSRS 2016

texts:
I have a SSRS report, MainReport, that basically just calls a series of sub-reports to compile everything into a nice and tidy Excel Document (users love their Excel reports), with each sub-report occupying its own tab.  On occasion, one of the sub-reports, Subreport1, will fail displaying Error: Subreport could not be shown within the Excel tab and nothing else.  Digging through the various SSRS server and execution logs, I'm able to identify that the sub-report in question encountered an error, but the error description is not exactly useful:

reportrendering!WindowsService_1!1cb4!06/07/2018-01:55:13:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.RenderingObjectModelException: , Microsoft.ReportingServices.ReportProcessing.RenderingObjectModelException: Data retrieval failed for the subreport, 'Subreport1', located at: /Folder/Subreport1. Please check the log files for more information. ;
processing!WindowsService_1!1cb4!06/07/2018-01:55:13:: e ERROR: An error has occurred while processing a sub-report. Details: Data retrieval failed for the subreport, 'Subreport1', located at: /Folder/Subreport1. Please check the log files for more information.  Stack trace:
   at Microsoft.ReportingServices.OnDemandReportRendering.SubReport.FinalizeErrorMessageAndThrow()
   at Microsoft.ReportingServices.OnDemandReportRendering.SubReport.RetrieveSubreport()

I'm trying to figure out what the explicit error code in question is, and so far my efforts to do so have resulted in dead ends.

When reviewing the ExecutionLog3 view for the main report, it shows that it renders without issue:

ItemPath            RequestType     TimeStart               TimeEnd                 Format          Status
/Folder/MainReport  Subscription    2018-06-07 01:25:02.397 2018-06-07 01:55:13.537 EXCELOPENXML    rsSuccess

I would have expected to see an error code pop for the main report, but because it technically did render, I guess I can understand why a Success is being reported.

When I look for the execution record of the sub-report, there isn't a correlating run-time listed that matches up with the run-time of the main report.  The logged executions of the sub-report are no where near the rendering period of the main report, so I suspect SSRS is not logging each sub-report execution individually.  Here's all the available records that exist in my logs for the sub-report around the execution time of the main report:

ItemPath            RequestType TimeStart               TimeEnd                 Format          Status
/Folder/SubReport1  Interactive 2018-06-07 08:04:23.010 2018-06-07 08:08:14.540 RPL             rsProcessingAborted
/Folder/SubReport1  Interactive 2018-06-05 09:39:38.620 2018-06-05 09:39:39.557 EXCELOPENXML    rsSuccess

I know in this particular example I can infer that the sub-report exceeded the default timeout threshold of 30 minutes, but again, I cannot locate the explicit error that tells me so.  So my question is quite simply, is there another log or location I've not come across that would show more details pertaining to the error(s) of the sub-report, or are these just not captured?


texts:
I had to stop the slave server to test something. After I started the server again there is a problem with replication on MySQL server

On the problematic server

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: servera
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: servera-bin.000024
          Read_Master_Log_Pos: 808459481
               Relay_Log_File: serverb-relay-bin.000071
                Relay_Log_Pos: 4
        Relay_Master_Log_File: servera-bin.000024
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 808459481
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1129
                Last_IO_Error: error connecting to master 'replica@servera:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:

on the problematic server:

SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y';
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| root    | serverb   |
| root    | 127.0.0.1 |
| replica | servera   |
| replica | serverb   |
+---------+-----------+

on the main server:

SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y';
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| root    | servera   |
| root    | 127.0.0.1 |
| replica | servera   |
| replica | serverb   |
+---------+-----------+

according to what I've read, there is a need to execute the following command om the main server:

mysql> FLUSH HOSTS;

What will happen then? if there is any application connected to it - will it disconnect it also?


texts: Defragmenting SQL Server data and log files live with MoveFile API

texts:
My employer is looking at deploying NTFS defragmentation software that uses the Windows MoveFile API to defragment open files. This would get deployed to thousands of SQL Server servers running SQL versions from 2005-2012 and Windows versions from 2003 and 2008 R2. If it matters, the product I'm talking about is PerfectDisk, but I believe there are similar programs that work the same way.

Testing so far hasn't turned up many problems aside from occasional I/O performance issues, which is no surprise and can be worked around by rescheduling & tuning the defragmentation. However, I'm more concerned about the risk of data corruption.

Does anyone here have experience with running this type of software in production on database servers? Did you experience any data corruption?

I'm rather uneasy about this despite being unable to find any solid evidence that it poses a problem.

Thanks for any replies.

Edited to add: This horrible idea was thankfully just forgotten about, possibly in part due to some of the warnings I gave.


texts:
Full disclosure, new to postgres and new to linux. I am having trouble understanding the user management side of postgresql. I installed on a Ubuntu 19.04 based os using

sudo apt update
 sudo apt install postgresql postgresql-contrib

Ive been able to switch to the default user postgres and can successfully call psql amongst other commands. When trying to connect via pgadminIII i am prompted for a password when using the same user name. A couple searches reveal that you should be able to leave this blank. I get an error stating no password supplied.

Additionally, I would like to make my own user name an admin and use my linux password. I suppose this would be similiar to windows authentiaction on microsoft sql. After searching the internet it appeared that all i need to do is createuser *MyNameHere* and declare it a superuser. However when atempting to connect form command line it acts like there should also be a database named the same as my user. Secondly when using the pgadmin it actively rejects my credentials.

I guess my question is this:

Can someone explain to me the basic concepts of roles/usernames and how they relate to the linux user names. And secondly, what is going wrong in the above.


texts:
I want to enable an SSL connection to my database.  I've created SSL certificates and so on, however SSL remains disabled:

mysql> show variables like '%ssl%';
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| have_openssl  | DISABLED                           |
| have_ssl      | DISABLED                           |
| ssl_ca        | C:/Users/Krzysztof/ca-cert.pem     |
| ssl_capath    |                                    |
| ssl_cert      | C:/Users/Krzysztof/server-cert.pem |
| ssl_cipher    |                                    |
| ssl_key       | C:/Users/Krzysztof/server-key.pem  |
+---------------+------------------------------------+

What am I missing?

I'm using Windows 7.


texts: Is it possible to Install two (2) Firebird instances of different names & ports (gds_db port 3050 & fbsvc port 3051) running as service?

texts:
Is it possible to Install two (2) Firebird instances of different names & ports (gds_db port 3050 & fbsvc port 3051) running as service !?

I mean the two running as service not application

Thank you for your understanding


texts:
I've searched around all over and haven't found a conclusive answer to this question.

I need a script that can give ALL permissions for an associated role.

Any thoughts, or is it even possible?

This gets me CLOSE - but I can't seem to flip it around and give the summary for roles, rather than users.

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

 WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc,
    p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc,
   member_principal_id,user_name(member_principal_id) as member_principal_name,
   user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1


texts: How do I move a MySQL data directory to an external hard drive?

texts:
I tried moving MySQL data directory to an external Hard disk (which has been formatted using NTFS).
I followed the steps given in the answer to the question How to change MySQL data directory?

Stop MySQL
Copy MySQL data directory to new location
Edit the MySQL configuration file (update datadir variable).
Update the new path in apparmor
Reload apparmor
Start MySQL (/etc/init.d/mysql restart)

But at the 6th step, when I restart MySQL it's failing. When I look into /var/log/mysql/error.log I get the following message:

2015-05-17 12:07:05 4317 [Warning] Can't create test file /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/    neo.lower-test
/usr/sbin/mysqld: Can't change dir to '/media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/' (Errcode: 13 -     Permission denied)

What should I do to fix this problem?
I guessed that it is because the mysql user does not have permissions to write to the directory on the external hard drive. So I tried to change the ownership of the entire path (chown mysql:mysql -R /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/). But it didn't get reflected.
So I tried mounting that partition again with sudo mount -t ntfs -o rw,auto,user,fmask=0022,dmask=0000 /dev/sdb1 /media/prasanth/SeagateExpansionDrive/ with no luck.
Setup Details:
Host - Ubuntu 14.04
External Drive: NTFS FileSystem, 4TB single partition
MySQL: mysqld  Ver 5.6.19-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64


texts: Why is PostgreSQL doing a seq scan on column != column?

texts:
Why is PostgreSQL doing a table scan on column != column?

Isn't this query guaranteed to return no results? Isn't anything != anything guaranteed to be always false for any record on a not null column?

test=# CREATE TABLE toasters (id integer NOT NULL);
CREATE TABLE
test=# EXPLAIN SELECT * FROM toasters where id != id;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on toasters  (cost=0.00..40.00 rows=2388 width=4)
   Filter: (id <> id)
(2 rows)

test=# EXPLAIN ANALYZE SELECT * FROM toasters where id != id;
QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on toasters  (cost=0.00..40.00 rows=2388 width=4) (actual time=0.002..0.002 rows=0 loops=1)
   Filter: (id <> id)
 Total runtime: 3.082 ms
(3 rows)

This is exactly the same for a table with millions of records.

A bit more context here: this query is generated by the SQLAclchemy ORM. http://docs.sqlalchemy.org/en/rel_0_9/faq.html#why-does-col-in-produce-col-col-why-not-1-0


texts:
I have SQL Server 2008, I have 20 GB free on HD when I try to insert row in any table my HD was full before finished.

I try to insert about 200,000 rows each row with around 100 columns.

The operation consumed only space in my mdf file but not in my log file.

My destination table is empty, before today I don't have problems with this process.

Why is this happening?


texts: postgres filtering select based on combination of joins

texts:
I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship)

Please consider the following example.

CREATE TABLE sample_container
(
  id serial NOT NULL,
  owner_id integer NOT NULL,
  owner_type character varying(255) NOT NULL,
  CONSTRAINT sample_container_pkey PRIMARY KEY (id)
)

CREATE TABLE sample_link
(
  id serial NOT NULL,
  owner_type character varying(255) NOT NULL,
  label character varying(255),
  owner_id integer NOT NULL,
  sample_container_id integer NOT NULL,
  CONSTRAINT sample_link_pkey PRIMARY KEY (id),
  CONSTRAINT sample_link_sample_container_id FOREIGN KEY (sample_container_id)
      REFERENCES sample_container (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

sample_container
id  owner_type
1   glue_batch
2   glue_batch
3   glue_batch

sample_link
id  owner_type      owner_id    sample_container_id
1   project         1           1
2   raw_material    1           1
3   project         1           2
4   raw_material    2           2
5   project         2           3
6   raw_material    2           3

What I need to do is select every sample_container which "owns" a sample_link where project and material match.

So for example, if I search for project=1 && raw_material=2, I would want it to return container 2, but not container 1 or 3.

So far I can all the containers with:

SELECT DISTINCT sample_container.* FROM sample_container
    INNER JOIN sample_link ON sample_container.id = sample_link.sample_container_id
    WHERE sample_container.owner_type = 'glue_batch' AND (
        ((sample_link.owner_type = 'project' AND sample_link.owner_id = 1) OR
        (sample_link.owner_type = 'raw_material' AND sample_link.owner_id = 2) )
    )

Or no containers with:

SELECT DISTINCT sample_container.* FROM sample_container
    INNER JOIN sample_link ON sample_container.id = sample_link.sample_container_id
    WHERE sample_container.owner_type = 'glue_batch' AND (
        ((sample_link.owner_type = 'project' AND sample_link.owner_id = 1) AND
        (sample_link.owner_type = 'raw_material' AND sample_link.owner_id = 2) )
    )

(notice the change on the OR)

Any ideas on how I should go about this?

Cheers and thank you for your time!

PS: technically I am building the query dynamically from an ORM, but I should be able to do that just fine if I can get a working select statement to go from


texts: Azure Database for MySQL - mysqlimport command

texts:
So I'm trying to accomplish the goal of performing a mysqlimport on a Azure MySQL 8 database and I get the following error:
mysqlimport: Error: 1227 Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

I have created a sample schema: CREATE SCHEMA test;
I have created a sample table: CREATE TABLE test.test_import (Data VARCHAR(50));
I have a test csv (test_import.csv) with one column of text. Note that the name of the csv matches the test import table as well as the one field inside of the csv, so no issues with that part.
I have attempted to grant myself SESSION_VARIABLES_ADMIN like the error suggests doing, but have been unable to do so with these Azure databases:
GRANT SESSION_VARIABLES_ADMIN ON *.* TO tester@'%';

Error Code: 1227. Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation

Lastly, here is the mysqlimport command (don't worry about the credentials, they're not real).
.\mysqlimport.exe --host="testmysql8.mysql.database.azure.com" --user="tester@testmysql8" --password="test123!" test test_import.csv


texts:
I am creating a SSRS Report to produce some address labels. I have a SELECT statement as follows:

SELECT DISTINCT
                         AddressLink.intAddressID, Addresses.txtLabelSalutation, Addresses.txtAddress1, Addresses.txtAddress2, Addresses.txtAddress3, Addresses.txtTown,
                         Addresses.txtCounty, Addresses.txtCountry, Addresses.txtPostcode, Addresses.txtRelationType, Pupils.intNCYear, Pupils.txtSchoolID, Pupils.txtForename,
                         Pupils.txtSurname
FROM            TblPupilManagementAddresses AS Addresses INNER JOIN
                         TblPupilManagementAddressLink AS AddressLink ON Addresses.TblPupilManagementAddressesID = AddressLink.intAddressID LEFT OUTER JOIN
                         TblPupilManagementPupils AS Pupils ON AddressLink.txtSchoolID = Pupils.txtSchoolID
WHERE        (Pupils.intSystemStatus = 1) AND (Addresses.txtAddress1 <> '') AND (Addresses.txtRelationType <> 'OLD Billing') AND (Addresses.intJustContact <> 1)

And within the report itself I am using the following expression to produce the addresses:

=Fields!txtLabelSalutation.Value & vbCRLF &
Fields!txtAddress1.Value & vbCRLF &
IIF(
       Fields!txtAddress2.Value <> "", Fields!txtAddress2.Value, nothing) & " " &
       IIF (
                     Fields!txtAddress3.Value<> "", Fields!txtAddress3.Value, nothing) & vbCRLF & Fields!txtTown.Value
       & vbCRLF &
       IIF(
              Fields!txtCounty.Value <> "", Fields!txtCounty.Value, nothing) & vbCRLF &
              IIF(
                     Fields!txtCountry.Value <> "United Kingdom", Fields!txtCountry.Value, nothing) & vbCRLF & Fields!txtPostcode.Value

My issue is that this expression is producing blank lines for fields that have no data, so I am getting a format like:

Mr & Mrs Smith
22 Somewhere Street

Somewhere Town

SS48 9TQ

How can I concatenate rows together if there are blanks so the address appears in one clean block? I am sure I need to amend my expression, but I am not sure how. I need it to look like:

Mr & Mrs Smith
22 Somewhere Street
Somewhere Town
SS48 9TQ

I have already tried amending the SQL statement using a CAST, however this then removes the DISTINCT clause and this part is vital. I also tried by grouping with the cast but that also failed.


texts: Merge Replication API with SQL Server 2012 / 2014

texts:
We are using SQL Server 2008 with merge replication. Our business application uses the RMO API, which we have installed with SQL Server Feature Pack 2008 SP3 and there the package sql_rmo.msi.

Now we want use SQL Server 2012 or 2014. In SQL Server 2012 Feature Pack, I can not find such a package.

How can I install the RMO libs for SQL Server 2012 / 2014?


texts: Oracle 10.2 unable to dynamically execute DBA command

texts:
I would like to execute the analyze index command for every user index within a database in order to populate the index_stats view in order to detect fragmented indexes by (DEL_LF_ROWS/LF_ROWS).

The version of Oracle that the client is using is 10.2.0.3. This has made it even more challenging to find valid examples. I primarily work with the MSSQL database platform but the client has multiple queries that use Linked Server connections to the Oracle databases therefore the Oracle database performance problem has become my problem.

The code that I am working on is below.

DECLARE v_variable_tx VARCHAR2(32000);
BEGIN
  for item in (select owner, index_name
               from DBA_IND_STATISTICS
               where owner not in ('SYS','SYSTEM','DBSNMP'))
  LOOP
    v_variable_tx:= 'ANALYZE INDEX ' || item.owner || '.' || item.index_name || ' VALIDATE STRUCTURE OFFLINE;';
    --execute immediate v_variable_tx;
    DBMS_OUTPUT.PUT_LINE(v_variable_tx);
  END LOOP;
END;

The error that I am getting when executing the above loop is...

  RA-00911: invalid character ORA-06512: at line 8 00911. 00000 -  "invalid character"

When I run the put_line text the command executes successfully and I can see the index stats in the view. Why does it fail when it is executed using execute immediate?


texts:
I have a SQL Server 2012 database server with 64GB of RAM. A process called DataPumpService runs on the same box and loads around 400 million records from 2700 flat files.

I want to understand what process is consuming all the physical memory on the box, so I go to the Resource Manager (under Task Manager):

As you can see from the screenshot, the In Use memory is around 61GB, but the Committed Bytes and Private Bytes for the two most memory-intensive processes are around 3.3GB.

How can I find out what process contributes to such high In Use memory on this server?

Thanks.


texts: how to start sql server agent etl job after complete oracle process

texts: I have Oracle source and SQL Server. every day 12.00 AM Oracle data populate for oracle database tables, after that sql server agent jobs run manually by me after Oracle Data Extraction completed. for SQL Server databases, I want to automate this process. when oracle data population completed i need to run sql server agent jobs automatically, how can i do this.


texts: Is 3 minutes to join a 50 million table too much?

texts:
I apologize if this is the wrong place to ask, but I really need some opinion on whether or not my query is good or not.

Basically, I need to get a count of old and invalid entries per group, which is identified by a value of NULL or simply a month old. The selected data is going to be stored in a smaller table called "statistics". PHP is used for a cron script.

Now, the problem is I'm still a novice so I can't tell if the query is fast or slow. To finish the following query, it takes roughly 3 minutes, which according to my boss is slow and needs to be at least 50% faster, but I myself am not sure if anything can be done to speed it up. "cat_01" table has 50 mil row, the others have less then 2000.

I did add indexes to all used columns, which lowered the time from 6 to 3 minutes.

The query I'm using is as follows (I used pseudonyms for tables and columns, so don't mind the logic):

SELECT
'cat_01' as category,
m.shop_id,
o.name,
count(m.shop_id) as total,
tr2.traffic,
IF(mm.bought IS NULL,0,mm.bought) as bought
FROM cat_01 m
JOIN shops o
ON m.shop_id = o.id
JOIN (
    select
    mmm.shop_id,
    max(mmm.buy_date) as bought
    FROM cat_01 mmm
    GROUP BY mmm.shop_id
) mm
ON o.id = mm.shop_id
LEFT JOIN (
    SELECT tr.shop_id, sum(tr.sales) AS traffic
    FROM (
        SELECT
        mmmmm.shop_id,
        bs.sales,
        bs.order_id,
        bs.id
        FROM cat_01 mmmmm
        JOIN orders bs
        ON mmmmm.order = bs.order_id
    ) tr group by tr.shop_id
) tr2
ON tr2.shop_id = mm.shop_id
WHERE (m.buy_date IS NULL)
OR (m.buy_date <  UNIX_TIMESTAMP())
GROUP BY m.shop_id

Create table "cat01":

CREATE TABLE `cat_01` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `shop_id` int(11) DEFAULT '0',
 `buy_date` int(11) DEFAULT NULL,
 `order` int(11) DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `shop_id` (`shop_id`),
 KEY `buy_date` (`buy_date`),
 KEY `order` (`order`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Create table "shops":

CREATE TABLE `shops` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

create table "orders":

CREATE TABLE `orders` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `order_id` int(11) NOT NULL,
 `sales` int(11) NOT NULL DEFAULT '0',
 `status` tinyint(1) NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`),
 KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here is the explain (I don't have access to the live data from home, so I used dummy rows, but rest is the same)

EDIT: added UNIX_TIMESTAMP() as an example

EDIT2: I ended up removing the subqueries from the SELECT and used them seperately, then using a PHP loop, I created the appropriate INSERT query by mixing the results together.

P.S. I can't seem to find a direct example of how sql code formatting on sctackechange looks, so I apologize if it ends up garbled, I'll try to fix it shortly.

I do have an EXPLAIN result but not sure how to format that, might end up posting an image later.

If you need some particular info, feel free to ask, I myself am not sure what I should be looking at.


texts: MySQL - What to store in orders or product_order

texts:
I am building my first e-commerce website and this is my current (relevant) database schema.

I am very confused about what to store in the orders table or in the pivot table (order_product).

I read that you should store a copy of the single_price in the pivot table because the price may change at a later date. If this is correct should i also store copies of product-details in my order_product table ? Because those values may change like the price? . If a user changes their Shipping or Billing Address it should change for the next orders. This should work with the current setup or will it not?

Thanks in advance

Edit

My table names follow a naming convention from Laravel (5.2). This is the reason why my joining tables (pivot tables) have these names. The convention states that pivot tables should be the singular table names in alphabetical order.


texts:
I'm using SQL Server transactional replication and looking for ideas on reducing the amount of downtime caused by re-initializing subscriptions.

I have multiple publishers pushing data to a single subscriber across the WAN.  I recently had to recover the subscriber from backup.  Upon restoring the backup, the subscriber was out of sync with all of the publishers.  The only way I knew to get the subscriber back in sync with all of the publishers was to re-initialize all the subscriptions.

Since the publishers and subscriber are separated by a WAN connection, this process was extremely time consuming.  In some cases it takes 24+ hours to generate a snapshot.

Any ideas on how to reduce the downtime caused by this type of scenario?  The only idea I have at this point is to use Always On with the subscriber.


texts: In what order does postgresql handle queries which have conflicting locks?

texts:
Let's say that I have a long running query SELECT and this query holds a AccessShareLock on a table. While this query is being processed a new query which needs a AccessExclusiveLock on the table REINDEXDB is made. I suppose this query must wait the other query to finish, becuase they have conflicting locks. And while my REINDEXDB query is waiting for the SELECT query to finish, a second SELECT query is made. Now my question is

Which query will be serviced first? REINDEXDB or SELECT? Select query has been made later but it doesn't have a conflicting lock with the ongoing select query. So will they be processed in parallel? Or because REINDEXDB query has been made earlier will the second select query wait for REINDEXDB to finish?


texts: Why SQL Server Migration Assistant keeps crashing on the 'Migrate Data' step?

texts:
I'm trying to migrate a MySQL database to SQL Server using the SQL Server Migration Assistant for MySQL. Converting the schema and synchronizing with SQL Server works fine.

However, after clicking the Migrate Data button, SSMA crashes after a few seconds (SSMA has stopped working). What might be the cause of this problem?


texts: differences (if any) between Select count () from table vs select count (*) from table

texts:
I was looking at script in Sybase when I read something like

    select count() from table;

the result it's exactly the same to

    select count(*) from table;

I'm curious, and I was looking out for  more info in google but I didn't found anything.
I suppose that select count(*) from table it is ANSI and select count() from table not, and its just another way that sybase allow to do do it.

but anyway I would like to know if there is any difference or why are two different ways to do the same.


texts: How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

texts:
There is a third party app which generates a high-cost query that has a join on multiple tables.

I created a materialized view on some of the tables using the same join that is originated in the Application query and used rewrite hint. The hint is respected by the optimizer and the materialized view is used. What should I do to make optimizer perform a nested loop join between the materialized view and other tables?

Eg:

SELECT  /*+ rewrite */
       tab1.col1, tab1.col2, tab1.col3
  FROM tab1,
       tab2,
       tab3,
       tab4
 WHERE     tab1.col1 = tab2.col1
       AND tab2.col2 = tab3.col1
       AND tab4.col1 = tab3.col1
       AND tab4.col1 = :1;

I created a Materialized view as below:

CREATE MATERIALIZED VIEW tst_mv
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
    SELECT tab1.col1, tab1.col2, tab1.col3
      FROM tab1, tab2, tab3
     WHERE tab1.col1 = tab2.col1 AND tab2.col2 = tab3.col1;

I tried with the hint as /*+ rewrite use_nl(tst_mv tab4) */ but no luck. Any pointers?


texts: SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index

texts:
I'm running a 350GB database on my PC with ~40 million rows.

  SQL Server 2014, Win7, AMD 8350 @ 4.8GHZ, 16 GB of RAM, and a 500 GB
  SSD (database is hosted on it's own 500 GB SSD, with a throughput of
  500MB/500MB read/write).

The database is not being updated, I'm just analyzing/reading it. With the creation of a few indexes, any join, count(*) etc, takes less than 1 minute, which is ok for my purposes. I have been running some queries (after running a single join query, 40-50 times, it becomes slow) on the data, and now calls that took 1 minute, are still running 20 minutes later.

I keep a careful eye on system resources, and I can see the SSD kick in when the query starts, it reads for 20-30 seconds, then it reads at 121kB/second for the next 20 minutes. This is not a CPU problem, or disk problem. I am limited with my amount of RAM, however the calls run fine when I first loaded the database, now, nothing runs, 25 minutes later.

Effectively I cannot query the database anymore, any call takes excessively long even a basic SELECT statement. I have tried rebuilding the indexes and updating statistics, but no difference.

I don't have a lot of experience on this so it's entirely possible that my SQL query is incorrect, in which case I'd expect an error, or for it to finish executing with 0 results, but neither occurs.

What I am trying to do is count all instances of a 'TypeID', in the 5 seconds prior to a time based on the table ACALLS.

SELECT ACALLS.StartTime, ACALLS.Time, ACALLS.ServerIP, ACALLS.SRVR,   ACALLS.calls, ACALLS.TOKEN, COUNT(TypeID) as ExecRate
FROM ACALLS
INNER JOIN MAINVIEW ON
MainView.TimeStamp BETWEEN ACALLS.StartTime and DATEADD(ss,-5,ACALLS.StartTime)

WHERE DATEPART(hour,MainView.TimeStamp) BETWEEN 10 and 13 and
CAST(MainView.TimeStamp as date) = '2015-12-09' and
MainView.TypeID = '123456789'
GROUP BY Acalls.STartTime, ACALLs.TIME, ServerIp,SRVR, ACALLS.CALLS, ACALLS.TOKEN
ORDER BY Acalls.StartTime

After running "Who is Running",


texts: Connection pooling with 3 databases but 1 database as master for repmgr replication?

texts:
I'm using pgbouncer for connection pooling against 3 database on different servers, now I'm planning to use repmgr for replication, my question is, can I keep doing connection pooling on these 3 databases and have one of them as master replicating to the others, or I should have pgbouncer doing connection pooling on one database and have this as master replicating to the others?

My intention is have connections being distributed over databases and make use all database are up to date and returning updated data to users.


texts: Converting a SQL Server 2000 Database to SQL Server 2016

texts: I need to restore a SQL Server 2000 database to SQL Server 2016. Is there any possible way to do this without having to install a complete copy of SQL Server 2008, restore to 2008, change the compatibility level and then restore the 2008 backup to 2016? I know this would work but I really don't want to install 2008 R2 just for this single purpose unless I have no other choice.


texts: why should I join 2 tables with 1 table apart

texts:
I could not solve this problem on hackerrank , and had to look up the solution. I was joining the wrong tables.
There are 4 tables:

hackers
challenges
submissions made by hackers and their scores
difficulty table with levels.

the task description:

Write a query to print the respective hacker_id and name of hackers
who achieved full scores for more than one challenge.

Submissions table fields: submission_id, hacker_id, challenge_id, score
challenge table fields:   challenge_id, hacker_id, difficulty_level
The way i joined: hackers + challenges, challenges + difficulty , challenges + submissions.
select
hackers.hacker_id, name

from submissions inner join challenges on submissions.challenge_id = challenges.challenge_id
inner join difficulty on difficulty.difficulty_level = challenges.difficulty_level
inner join hackers on

challenges.hacker_id = hackers.hacker_id -- here is wrong part!

where difficulty.score = submissions.score and difficulty.difficulty_level = challenges.difficulty_level
group by hackers.hacker_id, name
having count( challenges.challenge_id)> 1
order by count( challenges.challenge_id) desc, hackers.hacker_id

However, the right way was almost same - except that i should have joined
submissions + hackers by hacker_id
vs
not submissions + challenges by challenge_id.
The correct way:
select
hackers.hacker_id,name

from submissions inner join challenges on submissions.challenge_id = challenges.challenge_id
inner join difficulty on difficulty.difficulty_level = challenges.difficulty_level
inner join hackers on submissions.hacker_id = hackers.hacker_id
where difficulty.score = submissions.score and difficulty.difficulty_level = challenges.difficulty_level
group by hackers.hacker_id, name
having count( challenges.challenge_id)> 1
order by count( challenges.challenge_id) desc, hackers.hacker_id

What's the logic behind joining submissions + challenges by challenge_id vs
submissions + hackers by hacker_id?
why does it produce different result? A hacker makes submissions , so it should not matter whether i join challenge+submission + hackers or
submission + hacker + challenge...


texts: How do I prove that the act of removing foreign keys doesn't corrupt existing data?

texts:
TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of the statement itself; consider data changes after the fact irrelevant. (By analogy: opening the stable door is the harmful action, not the horse bolting.)

My boss and I are having a difference of opinion about whether or not to use foreign keys in a MySQL/InnoDB database. I'm for using them for enforcing RI and taking advantage of ON DELETE CASCADE and ON UPDATE RESTRICT/SET NULL, while he's against (confident that he can enforce RI at the application level).

His argument is chiefly:

Drupal doesn't use them and gets along fine without them, so why should we?
They're too inflexible when you need to change data and/or structure.
He's removed them from existing tables to change things and it's caused data corruption that was only noticeable weeks or months later, on high-traffic/ high activity sites, so he'd rather not use them.

My arguments are chiefly:

Not all of the databases/ DB engines supported by Drupal 5-7 (MyISAM, SQLite 3) enforce FKs by default, so Drupal leaves them as documented only, whereas this might be different in D8.
Yes, they can be a pain to deal with, but perhaps the fault lies with poor planning/designing on the part of the developer, not the FKs.
Surely not enforcing RI with FKs at the DBMS level is more likely to cause data corruption than otherwise. (Case in point is D6's user reference module not restricting changing user status when existing content references a user that must have a certain status).
It's a waste of time and resources to make code do/attempt what the DBMS already does. How can he guarantee that his code will work as well as (or better than) the DBMS?

Essentially, he'll come over to my way of thinking if I can prove that the act of removing a foreign key itself (regardless of subsequent data inserts/updates) won't corrupt existing data. I can't see a way clear to prove their usefulness/advantage because I'm not sure how I would satisfactorily observe/document the effect a drop foreign key would have immediately after execution other than comparing data from before execution to data after execution.

So, how do I persuade my boss that we should use foreign keys by proving that the act of later removal/alteration of them won't corrupt existing data, whereas using them will not cause any issues? How would I best set up practical usage tests?

Note: I don't so much want to prove that I am right (from an egotistical point of view) to use FKs, but that it benefits the data and the application code more to use them at the DBMS level than at the application code level.


texts:
Since I switched from MyISAM to InnoDB I have a high I/O.

The writes is a flat line, it seems there is a limit for them. This is my current mysql configuration:

[mysqld]
bind-address = 127.0.0.1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

# CACHES AND LIMITS #
tmp_table_size                 = 512M
max_heap_table_size            = 512M
query_cache_size               = 16M
query_cache_limit              = 1M
thread_cache_size              = 8
open_files_limit               = 8292
key_buffer                     = 512M
table_cache                    = 512M
sort_buffer_size               = 4M
read_buffer_size               = 4M
read_rnd_buffer_size           = 16M
table_open_cache               = 512M

thread_concurrency=4
query_cache_size = 16M

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

#disable federated by default
skip-federated

innodb_file_per_table=1
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 128M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have 2 cpu's and 4GB RAM. My database is around 35GB. What can I do to optimize my configuration? Buy more resources?

Let me know if I need te add more information about my server or configuration.


texts: SQL datatype choice for flagging a read/ unread message?

texts:
I have a table where I want to track Boolean type operations. in addition, a business requirement is that I know the date of the action and not just the Boolean value.

for example if a message is read or deleted.

I would like to use a date time field so that I can just quantify all unread messages as having null or a date time value for read. I get Boolean operation and a timestamps.

My only qualm about this is how efficient will selecting all unread messages be?

Lets say in a table with a few million records and a non clustered index on a field such as read would a select perform way worse than if the field were a Boolean?


texts: Different collation between System and User databases in SQL Server

texts:
Recently, I have came across a situation where I found there is different SQL Collation is set for System databases and user databases on a sql instance in production.

Like: Danish_Norwegian_CI_AS for master, tempdb,model and msdb.
    Danish_Norwegian_CI_AI for USER_DB's.

Would there be any problem with this type of collation settings?


texts: MySQL:- Fetch records between two specific months and years

texts:
I need a Query to fetch records between two specific months and years also, Presently I am fetching only current year records like this

SELECT material_status, COUNT(*) c
FROM purchase_order
WHERE YEAR(date_created) = YEAR(CURDATE()) AND material_status='Closed';

But I want the records between Current year "April Month" to Upcoming year April Month" means 2016-April to 2017-April .


texts:
Does it make sense to think about a failover cluster configuration where less than 50% of all included nodes are standby?

For example instead of setting up 4 two-node clusters where one is active and one is on standby on each cluster - doesn't it make more sense to create 1 cluster with 4 active nodes and two on standby/passive for failover? That makes a saving of 2 nodes in total.

Sure, there is a risk in case multiple nodes fail unplanned and have to share one of the previous standby nodes, but in case you decide to take that risk over provisioning the standby hardware resources?

Looking for "multiple node failover clustering" I couldn't find more on this.


texts: What is a quick way of migrating 75G database to the cluster? I tried to test the backup and restore and it took more than 2 hrs. Business owners won't accept the long outage. Any idea how I can make this fast or how to accurately do it? This is on SQL server 2008 R2.


texts: How to UPDATE data without locking every record the search encounters?

texts:
We have the following database specifications:
version: 10.1.47-MariaDB-0ubuntu0.18.04.1
innodb_version: 5.6.49-89.0
tx_isolation: READ-COMMITTED
innodb_strict_mode: ON
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
autocommit: ON

We have the problem that we lock too many data entries in the table, which causes deadlocks. Here is our example.
The first query we run:
UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3  = 3;
We have single column indexes on all the columns. According to the docs: this will set an exclusive next-key lock on every record the search encounters.
So the next UPDATE statement will be waiting for the previous one, although the matching final result records are not overlapping. This is because the index on col1.
UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;
I would like to lock only the final result records and not "every record the search encounters". Splitting the query into a SELECT and an UPDATE by primary key gives me that result, but then a racing condition can happen between the SELECT and the UPDATE. Combining those in a transaction also doesn't help, as every SELECT is a consistent read.
How would you design a process like this, where I can run an UPDATE statement, without locking every record the search encounters?


texts: In the field of Probabilistic Databases, how can one compute the lineage and the safeness of a boolean query over a tuple-independent table?

texts: Suppose we have a tuple-independent database (TID) named D and we have to evaluate if a boolean query Q(D) is safe or not safe using the intensional query evaluation approach. I suppose we have to compute the lineage of Q and reason with that, but I am not quite sure of how to do it. How can one compute the lineage of a boolean query Q over a probabilistic database D and use that to evaluate the safeness of the query?


texts: Is mysql cluster a viable choice for real time data availability when it comes to data in high volumes (petabytes)?

texts:
I've been reading a lot of articles promoting MySql Cluster as a big data storage solution for real time applications, one of which is this one .

But as I went through the Mysql Cluster docs, I saw a specific phrase that stated :

  The maximum number of data nodes is 48
  and
  The total maximum number of nodes in a MySQL Cluster is 255.

This seems a pretty big limitation when it comes to big data analytics applications.
What I understand from the above is that Mysql Cluster cannot have more than 48 machines for data nodes. So the scale out stops at 48 and from there you go on scaling upwards?

Am I misunderstanding how Mysql Cluster works? Will Mysql Cluster support thousand of data nodes (using comodity hardware) like other big data storage solutions offer (like hadoop) ?


texts: MySQL or Oracle Associate Certification?

texts: I have 15+ years in software development and 7+ in web development, and I want to get a certification in another area, like DBA. I have 7+ years of experience like MySQL developer. Which is better for certification? MySQL or Oracle 11g? Which of the two is more hired and better paid?


texts: Exceptions not working from outer procedure

texts:
I created a MySQL trigger:

CREATE TRIGGER `check_purchase_date` BEFORE INSERT ON `purchases`
 FOR EACH ROW BEGIN
    DECLARE purchase_date_in_future CONDITION FOR SQLSTATE '45000';
    set time_zone='+5:30';
    IF date(NEW.purchase_date) > date(sysdate()) THEN
        SIGNAL purchase_date_in_future set message_text = 'Cannot purchase for future date.' ;
    END IF;
END

Then I created the below function:

CREATE FUNCTION `new_purchase`(`v_purchase_date` DATE, `v_voucher_number` VARCHAR(25), `v_notes` TEXT) RETURNS int(11)
    MODIFIES SQL DATA
BEGIN
    INSERT INTO PURCHASES (PURCHASE_DATE, VOUCHER_NUMNER, NOTES)
    VALUES(v_purchase_date, v_voucher_number, v_notes);
    return last_insert_id();
END

When I insert something into the purchases with a future date, it throws the exception as expected.

But when I try to insert using the new_purchase function, the row isn't inserted but the exception isn't thrown as I expected.

What I get is:

  #2014 - Commands out of sync; you can't run this command now

This is when I run:

SELECT new_purchase(
'2016-12-30', NULL , NULL
);

If I try like:

SET @p0 =  '2016-12-28';

SET @p1 =  '';

SET @p2 =  '';

SELECT  `new_purchase` (
@p0 , @p1 , @p2
) AS  `new_purchase` ;

The data doesn't get inserted but no error is thrown at all. All I get is:

  Your SQL query has been executed successfully

Doesn't exceptions cascade in MySQL? If yes, how can I make sure an exception is cascaded?

Any help is appreciated. I am stuck with this for the past 2 days. :(

DB Version: 5.5.31-MariaDB-log


texts: Loop inside of query in mysql procedure. Is it possible?

texts:
I have the following query:

INSERT INTO major (key_1, key_2, ... , key_N)
VALUES (v_1_1, v_1_2, v_1_3, ... , c_1_N)
       (v_2_1, v_2_2, v_2_3, ... , c_2_N)
       ...
       (v_K_1, v_K_2, v_K_3, ... , c_K_N)

where key_i is name of column, and v_i_j is random value from 1 to L. N,L and K are positive integers (our input).

I am going to write mysql procedure that create random (with values from 1 to L) matrices* (KxN) and place it to database using query presented above.

Now It is realised by code in php and doctrine but I want to achieve the same result in pure SQL procedure that as input gets (N,L,K). Theres is php code:

$conn = DriverManager::getConnection($connectionParams);
$conn->beginTransaction();
try {
    for ($i = 1; $i <= $k; $i++) {     // row in table major
        $content=[];
        for ($j = 1; $j <= $n; $j++) {   // foreign key of row
            $content['key_' . $j] = rand(1, $l);
        }
        $conn->insert('major', $content);
    }
    $conn->commit();
} catch(\Exception $e) {
    $conn->rollBack();
    throw $e;
}

*I wrote matrices but I know that without order of rows it is not matrix in strict sense.

So I do not want to make query inside loop, but loop inside query. I know it is bad practice, but I am thinking about concatenating and evaluating query inside of procedure? It is possible? Or is these better solution?


texts: Amazon RDS CPU is about 55-65% after optimizing some tables (Mysql)

texts:
I was having high RAM consumption and, looking for a solution, I executed the script mysqltunner to monitor possible optimizations in the Mysql database. Its first advice was I had to run

OPTIMIZE TABLE tablename;

So as, I had about 40 tables to optimize I ran it with the following php script:

<?php

    // connect your database here first

    $mysqli = mysqli_connect("localhost", "user", "password", "database");

    if($mysqli){
        echo "Connexion established";
    }else{
        echo "Imposible to establish a connexion to the database";
    }

    $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database'";

    $rs = mysqli_query($mysqli, $sql);

    while($row = mysqli_fetch_array($rs))
        {
            $tbl = $row[0];
            $sql = "OPTIMIZE TABLE `$tbl`";
            mysqli_query($mysqli, $sql);
        }

    mysqli_close($mysqli);

    echo "Optimization finished";

?>

And when it finished the execution I saw that 600Mb of RAM had been liberated. However, the CPU went from 5% of usage to 55%.

I connected to the database and ran

SHOW PROCESSLIST;

receiving this output

+-------+----------+---------------------+-------+---------+------+-------+------------------+
| Id    | User     | Host                | db    | Command | Time | State | Info             |
+-------+----------+---------------------+-------+---------+------+-------+------------------+
|  1020 | rdsadmin | localhost:39883     | mysql | Sleep   |    3 |       | NULL             |
| 60204 | user     | 172.31.11.223:40268 | NULL  | Query   |    0 | init  | show processlist |
+-------+----------+---------------------+-------+---------+------+-------+------------------+

The process 1020 of user rdsadmin is all the time there, I suppose it must be there. But apparently there are no threads to have so much CPU in use...

Do you have an idea of what can be happening and possible fixes?

Thank you!


texts: When would you use InnoDB PAGE compression over InnoDB TABLE compression?

texts:
From what I understand, InnoDB table level compression is "transparent" and appears to perform well on the surface.

In what circumstances would it be beneficial to choose InnoDB page level compression?


texts: query to find the count of each member in a column

texts:
Imagine there is a table with these columns

Table 1 : id
          Name
          Family

We want to find the count of each member in the column .
I wrote the query with CTE but I want to know if there are better solutions
in the picture below you can see the query and the result.


texts:
Currently I deleted one table from the database. But now I want some of the information from that table.

Please suggest how to restore or get back the table.

I am using SQL Server 2008 R2.


texts: TokuDB optimize table does not reclaim disk space

texts:
Was running an optimize table command against one of my tables about 11Gig in size, surprisingly used disk space grew by 2Gig immediately after optimization process finished.
I would expect the optimization to reclaim a space.

What's happening?

After the optimization finished MySQL CPU utilization 2-3 times higher than it used to be with load average 18-20 on 24 core machine. CPU and load average went down slightly after few hours but still higher than usual. Any had similar experience?
Thanks.


texts: how to select random and unique records from mysql (in php language)

texts:
I have a question table in my database which consists the following columns

question number which ranges from 1 to 150.
question text
text for 4 options as A,B,C,and D
answer option(either a,b,c or d)

Now i want to make a quiz where there will be 15 questions from these 150 questions and each question will be randomly selected from the pool of these 150 questions and also all these 15 questions must be unique i have used the followig approach but with this approach i am getting random question but there is a possibility of 2 same questions.

$n1=mt_rand(1,150);

$q=mysqli_query($mysqli,"select * from quiz WHERE qno = '".$n1."'");

I have also try to use DISTINCT but with this also I get two or more same questions:-

$n1=mt_rand(1,150);

$q=mysqli_query($mysqli,"select DISTINCT qno,ques,a,b,c,d,ans from quiz where qno='".$n1."' " );

i am using php language


texts: Identify which parameters were passed to a procedure (Oracle)

texts: Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values.  Is there a way from within the PL/SQL to determine whether a parameter is passed?  As implied this cannot be done by comparing the value with the default value.


texts: How to use MySQL Server 5.6 for GeoIP replication?

texts:
I have downloaded the new MySQL Community Server 5.6 and I dont quite understand how to use it properly for GeoIP based replication across 20 nodes. I want to have one central node, then three smaller ones, and then 8 sub-sub nodes so it's like a tree, and the writes are propagating in the cycle around the globe.

Have anyone has any link to point any theory on the above scenario, or what would be appropriate option without migrating to MySQL Cluster?


texts: How can I check if one specific login has any database users mapped on it?

texts:
I've create some logins and database users. So I would like to accomplish the following.
Drop user from database:
USE [dbname]
GO
IF EXISTS (SELECT 1 FROM sysusers WHERE NAME = 'username')
    DROP USER [username]

Here I would like to check if the login has any database users in other databases and if not drop login:
USE [master]
GO
DROP LOGIN [username]
GO


texts:
I currently have a MySQL database which I have added a new user (abbvs) to identified by a password.

The users I have:

mysql> select user,host from mysql.user;
+-------+-----------+
| user  | host      |
+-------+-----------+
| abbvs | %         |
| stnd  | %         |
| stnd  | 127.0.0.1 |
| root  | 127.0.0.1 |
| root  | ::1       |
|       | ATTD      |
| root  | ATTD      |
|       | localhost |
| stnd  | localhost |
| root  | localhost |
+-------+-----------+
10 rows in set (0.00 sec)

I have added SELECT for all databases, and ALL PRIVILEGES to another followed by flush_privileges;

SHOW GRANTS gives me the following:

mysql> show grants for 'abbvs'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for abbvs@%                                                                                |
+-------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'abbvs'@'%' IDENTIFIED BY PASSWORD '####' |
| GRANT ALL PRIVILEGES ON `vardb`.* TO 'abbvs'@'%'                                                         |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

However, when I attempt to login via the CLI with the new user I get the following:

[root@ATTD ~]# mysql -u abbvs -p vardb
Enter password:
ERROR 1045 (28000): Access denied for user 'abbvs'@'localhost' (using password: YES)

Is there anything that could be causing this? All other logins work as expected.


texts: Why isn't space deallocated after deleting all rows from a clustered table?

texts:
Today one of our queries has gone crazy. It was hanging for more than an hour before I killed the process.

UPDATE  tc
SET tc.Status   = 0
FROM    #RC_Task_Calc   AS tc
CROSS
APPLY   (   SELECT  TOP 1
            _tt.CalcStamp
        ,   _tt.TrDateInto
        FROM    dbo.TableName   AS _tt
        WHERE   _tt.TableId = tc.TableId
        AND _tt.EventKind   = tc.EventKind
        AND _tt.DayDate <= @DayDate
        AND _tt.Status  = 1
        ORDER
        BY  _tt.DayDate DESC
    )   AS tt
WHERE   tc.CalcStamp    = tt.CalcStamp
AND tc.TrDateInto   = tt.TrDateInto

It was expected to perform about 600k loops to dbo.TableName which had no rows. But something went wrong.

CREATE TABLE [dbo].[TableName](
    [Id] [dbo].[TBigInt] IDENTITY(1,1) NOT NULL,
    [TableId] [dbo].[TRowId] NOT NULL,
    [EventKind] [dbo].[TRowId] NOT NULL,
    [DayDate] [dbo].[TDate] NOT NULL,
    [CalcStamp] [varbinary](8) NULL,
    [TrDateInto] [dbo].[TDate] NULL,
    [Flag] [dbo].[TFlag] NOT NULL,
    [Status] [dbo].[TTinyInt] NOT NULL,
    [Stamp] [timestamp] NOT NULL,
    [CreatorId] [dbo].[TRowId] NOT NULL,
    [Created] [dbo].[TDateTime] NOT NULL,
    [ChangerId] [dbo].[TRowId] NOT NULL,
    [Changed] [dbo].[TDateTime] NOT NULL,
 CONSTRAINT [ITableNameId] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
),
 CONSTRAINT [ITableNameIdentity] UNIQUE CLUSTERED
(
    [TableId] ASC,
    [EventKind] ASC,
    [DayDate] ASC
)
)

I saw in properties of dbo.TableName (using SSMS) such information:

  Data space ~ 129 MB

  Index space ~ 49 MB

  Row count = 0

After rebuilding indexes on the table the query completed in less than a second

ALTER INDEX all ON [dbo].[TableName] REBUILD

I'm aware of removing all data from the table using a DELETE command yesterday. But why did I have to rebuild indexes so that the space was deallocated?

The table is clustered so it shouldn't have heaps' problems on deletion. All pages should have been empty after deletion because all rows was removed. I thought it could be linked to mixed extents, but is_mixed_page_allocation_on=0 for that database.

So could someone explain me what I missed here? Why those pages weren't deallocated? Or maybe I'm a victim of some misconception about the deletion from clustered table process.

  Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64)
    Nov  3 2018 00:01:54    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server
  2016 Standard 10.0  (Build 14393: )


texts: Exchange rate table and conversion on the fly in query

texts:
I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items.

I'm working with Java and my first thought was that I would use some kind of API to pull the rates and post process (loop) after I get the the items from the DB. But I'd rather not have to unnecessarily loop if I can directly get the right price form the query.

So I think I'm going to store all the exchanges rate in the DB and have my query do the conversation job here is my first draft:

 CREATE TABLE currency (
      code VARCHAR(3) NOT NULL,
      symbol VARCHAR(3) NOT NULL,
      display_name VARCHAR(4) NOT NULL,
      PRIMARY KEY (code),
      UNIQUE KEY uq_ccode (code)
    );

CREATE TABLE exchange_rate (
  currency_code_from VARCHAR(3) NOT NULL,
  currency_code_to VARCHAR(3) NOT NULL,
  rate numeric(15,4) NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (currency_code_from, currency_code_to),
  FOREIGN KEY (currency_code_from) REFERENCES currency(code) ON DELETE CASCADE,
  FOREIGN KEY (currency_code_to) REFERENCES currency(code) ON DELETE CASCADE
);

I would then write a query like:

select price as originalPrice, (price * rate) as TargetPrice FROM .....
JOIN exchange_rate er ON er.currency_code_from=(the currency of the item) AND er.currency_code_to=(the user currency)......

Note that on the above tables I would have to have every single combination

GBPEUR
EURGBP
GBPGBP (well i guess i can probably avoid this one if a slighty amend the query to detect this scenario)

Plus.. I would have to have some kind of cron job to daily update this table.

Now I'm far to be a banker nor a SQL genius so I would like some input from people that have already written such tables/query and that have expertise in this kind of stuff

Am I fare off with my design? Would I be better off post processing the results and do the conversation in java  with rates coming from an API?


texts: How to scale with MySQL (when not ready to scale properly)

texts:
We are using MySql.

My situation

I have a large number of tables with millions of rows each. Those tables are updated every second and are used both for adding info and retrieving info. Each table can be 5GB or 10GB or even more.
I have one table that I keep sums of information (something like a summary table of the information I need) but this is starting to get big in size as well.

My limitations

at the moment I cannot change database due to various reasons (mainly no knowledge, time and budget)
all the extra power that we add to the server goes to other resources needed so I cannot run very heavy queries

Temporary ways I have thought for scaling

Having these things in mind I am trying to think of ways to scale with what I have:

For the tables with millions of rows I have thought to keep to separate databases (could make my life easier for backups / exports / changes). Keep my main data in 1 database and all peripherals (huge tables) to other databases. Let’s say have a different database for a different need.
For the problem with the table that I really need regularly and is growing fast I was thinking into splitting it into XX tables. Could be 1 table per user (which might be too much) or 1 table per XXX users.

Are these ideas totally crazy and really bad DB design?

If yes..... any suggestions other than changing everything at once?


texts: Uninstalling 2 instances on 1 node of a 2 node SQL Server 2012 failover cluster instance

texts:
I need some pointers to uninstalling 2 instances on SQL Server 2012 cluster

We have a SQL Server 2012 Active -Active cluster with instance A on Node 1 and Instance B, Instance C and 3 services on Node 2. I need to uninstall Instance A and B from Node 2. The services could be removed/disabled.So I will have an Active-Active cluster with Instance C on Node 1 and no instance on Node2.

I have never uninstalled an instance on a cluster and we don’t have any test system so I have a couple of questions.

When there is no instance on Node 2, does it become an Active Passive cluster?
Do I have to take the node offline before the uninstall?
Can I get any links to uninstalling an instance? I have googled but have only come up with uninstalling nodes. I got This one that said about uninstalling an instance but at the end of it, uninstalled the node.
Am I right in understanding that you need the installation media to uninstall the instance? And that I cannot use uninstall programme method for this?
I have followed this link with a comment from Perry Whittle. So does it mean if I use the remove node option , it will remove only the instance BUT the node will still be there ?

I had initially disabled the 2 instances on the node ,thinking that would be a safer option but that gave rise to continuous alerts on our Nagios monitoring system and Operations got errors during the monthly patching.

Thanks


texts: I have delete database from mysql but storage is not freed

texts:
I have deleted multiple databases (schema) from mysql on amazon RDS but the storage is not freed, is there anything else I should do to free up the storage?

I have a mysql database that the total storage is 15GB. After adding multiple databases (copied from another mysql database) and deleting them the free storage available is 0.
I can not simply delete the RDS instance as there is another database on this instance that I need it. but I am 100% sure that the database is not using 15GB of storage.

I have read about this issue on other threads but most of the solutions where talking about deleting all the data and shutting down the server which I can't do as the database is being used all the time.


texts:
I'm using PostrgreSQL 9.1.9 on Ubuntu 13.04.

Using the following StackOverflow question, I tried to create a user/role in postgressql:

giladnaaman@giladnaaman-Lenovo-G550:~/Programming/Eclipse/Hephaestus$ createuser --version
createuser (PostgreSQL) 9.1.9

giladnaaman@giladnaaman-Lenovo-G550:~/Programming/Eclipse/Hephaestus$ createuser f4
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
createuser: could not connect to database postgres: FATAL:  role "giladnaaman" does not exist

How can I solve this problem?


texts:
I would like to get all the databases available on a db2 instance from a C# application.
The CLP command to be used is LIST DB DIRECTORY.

How can I fire this command from C#? Is there another select statement I can use to get all the databases on one db2 instance?


texts: Postgres - how much space is required to perform a VACUUM

texts:
I know that doing a VACUUM FULL on a table will free up disk space and return it to the OS.

There's been a couple of occasions where my OS (windows) has warned me that it's running low on disc space. My response has been to quickly shut down everything I can and run a VACUUM FULL on one of the more active tables.

I've noticed that VACUUM FULL appears to require a least a few hundred Mbytes free to actually work. If I attempt a VACCUM FULL on a 1Gbyte table when I've only got say 50 Mbytes of disk space then this quickly disappears and my computer grounds to a halt pretty quickly and the VACUUM FULL won't work.

How do I free OS space in a POSTGRES database when I know there is unused space available, but there's not enough space to run a VACUUM?

(I know the correct answer is - 'don't be stupid enough to let it happen in the first place', but sh1t happens occasionally!)


texts: MySQL Finding index size for compound unique indexes

texts:
I'm using following query to get index and data sizes for tables in database. But unfortunately it doesn't show index size for compound unique indexes. How can i get index sizes for compound unique indexes?

Here is my query;

SELECT TABLE_NAME AS "Table",
TABLE_ROWS AS "Rows",
CONCAT((FORMAT((DATA_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Data Size",
CONCAT((FORMAT((INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Index Size",
CONCAT((FORMAT((DATA_LENGTH+ INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Total Size",
TRIM(TRAILING ', ' FROM CONCAT_WS(', ', ENGINE, TABLE_COLLATION, CREATE_OPTIONS)) AS "Type"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = "my_database_name_here"


texts: What does a RED X on a database user mean?

texts:
I created two new AD groups and added them as Users of a database, but their icons show with a red X.
What does this mean?


texts: Why doesn't `vacuum full` release space immediately?

texts:
On Postgres 9.5 I had a table that grew to 30GB, but it was ultimately mostly dead rows. After cleaning it up with a vacuum full, postgres reports that the table size is now around 500MB. However, I see no space being released to the OS.

I found PostgreSQL - VACUUM FULL does not free space back to the OS which suggests the WAL files might be the culprit, but these are present on a different disk so it shouldn't affect my actual data disk at all.

I have confirmed that there are no other long running transactions or autovacuum's that might be locking the table.

What can I look at to figure out why my space isn't being released?


texts:
I had used --report-host option in one of my slaves, so that I could see that under SHOW SLAVE HOSTS. Now that server is no longer in use. I have uninstalled that server as well. Unfortunately I still see the details of that server under SHOW SLAVE HOSTS(When this is executed on the uninstalled server's master) which is incorrect.

What changes should I make such that SHOW SLAVE HOSTS gives me the correct output?


texts: Constraint on row value based on values in other rows

texts:
Is it possible to have a table with four columns:

CREATE TABLE accrual (
    add_date date NOT NULL,
    user_id integer NOT NULL,
    rate integer NOT NULL,
    amount numeric(7,3)
);

and then constrain it so that if I already have one entry like

('2016-04-01', 3, 120, 25.6)

an attempt to insert another entry with the same date and user_id but a different rate would fail?  i.e.

('2016-04-01', 3, 140, 15)

would fail, but

('2016-04-02', 3, 140, 15)

or

('2016-04-01', 4, 140, 15)

or

('2016-04-01', 3, 120, 15)

would be fine?

To be clear, the date and user_id are basically like an index, except duplicate indices are totally fine as long as the rates are the same.  The amount is not constrained.

I am working with a postrgesql 9.5 database, and I am using PHP.  My guess is that the easiest thing to do is to write some PHP code to perform the check.  The closest question I could find to this was this one:

trigger or check constraint insert values based upon existence of others

but it is about an Oracle database and is slightly different.

Thanks for any suggestions!!!


texts: How to resolve: Specified key was too long; max key length is 767 bytes

texts:
Our server is creating a lot of tables and one of them is the Lock_ table.

We are using MySQL 5.5 on Percona. Also we are using utf8_general_ci and utf8.

It throws the following error when creating the following index on Lock_ table:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Here is the create-table query:

create table Lock_ (
    uuid_ VARCHAR(75) null,
    lockId LONG not null primary key,
    companyId LONG,
    userId LONG,
    userName VARCHAR(75) null,
    createDate DATE null,
    className VARCHAR(75) null,
    key_ VARCHAR(200) null,
    owner VARCHAR(255) null,
    inheritable BOOLEAN,
    expirationDate DATE null
);

Index query:

create unique index IX_DD635956 on Lock_ (className, key_, owner);

This problem is happening on one of our prod servers (previously it was standalone mysql and then later moved to percona, but the problem was present in both the editions), but this is working fine on our dev environment.

So is there a way we can update the prod environment's index length or something so that it does not throw this error?

Thanks


texts:
I recently made changes to optimize performance for InnoDB engine-based DB and soon after when restarting I encounter into the following strange behavior:

I have some tables with data in them
I made a change to my.cnf - increased innodb_buffer_pool_size
I restart MySQL server
I do 'use database' and perform 'show tables' > all tables are shown
I do 'select * from some_table_shown_in_the_list' and I get 'Table does not exist'

Seriously... what the hell? I've no idea what's going on...
My my.cnf settings (I am using only InnoDB engine for my tables):
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

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

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

key_buffer              = 1M
max_allowed_packet      = 512K
thread_stack            = 128K
thread_cache_size       = 50

# InnoDB settings

innodb_buffer_pool_size=384M
innodb_log_file_size=32M
innodb_log_buffer_size=2M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_file_per_table

query_cache_limit       = 0M
#query_cache_size        = 16M

expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

EDIT
What I did was to follow the steps as described at https://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql
MySQL version - 5.1.61-0+squeeze1-log
Engines available - MyISAM, InnoDB, MRG_MYISAM, BLACKHOLE, CSV, MEMORY, FEDERATED, ARCHIVE


texts:
I ran into some trouble with my disk space on server because my DB size increase. Auto Growth is enable on my database, But file growth is in percentage and not in MB. I want to change in to MB.

But before changing that I want to know can we do this change in working hours or do we require down time for it.

I don't want to take any risk in working hours so I want to be sure before doing this.


texts:
I have a table that holds tests. For every test you have a generic one (TYPE field is NULL) but for some tests you can have also more specific tests (TYPE field has a value).

I need a join to this table on the test and type but so that if no type is specified I get the generic tests (That is no problem as both sides have NULL) but if a type is specified I get the test of that type if there is one, or the generic test when there exist no specific one.

    TestTable
    ... Test   Grp  Type  Description ...
    ... TestA  G1   NULL  TestA_Gen    ...
    ... TestA  G1   M     TestA_M     ...
    ... TestB  G1   NULL  TestB_Gen   ...
    ... TestB  G1   X     TestB_X     ...

    BaseTable
    ... Pers  Type ...
    ... P_A   Null ...
    ... P_B   M    ...
    ... P_C   X    ...

Select B.PERS, T.DESCRIPTION
from BaseTable B
Join Testtable T on ??? and T.Grp = 'G1'

should give
P_A TestA_Gen
P_A TestB_Gen
P_B TestA_M
P_B TestB_Gen
P_C TestA_Gen
P_C TestB_X

How do I do that in MySQL?

Many thanks in advance,


texts: Mysql auto insert record in primary table if not exist

texts:
Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key?

For example, assume these tables:
 - user(id, name, age)
 - topic(id, name)
 - post(userId, topicId, text, createdAt, updatedAt)

Now i am pulling posts from some source and saving the records in the post table. But sometimes the data that is being returned contains a userId or a topicId that is not yet in my database. So everytime i would have to check if the user and topic records exist then save if not. Only then my post record would be valid and saved.

I want to be able to save the post even if its related user or topic does not exist, and add an empty row with the in these tables having the ids that have been stored in the post table.

Example:

Current User Table

+----+------+-----+
| id | name | age |
+----+------+-----+
| 15 | Paul | 26  |
+----+------+-----+
| 56 | John | 31  |
+----+------+-----+

current Topic Table

+----+----------+
| id | name     |
+----+----------+
| 5  | Business |
+----+----------+
| 12 | General  |
+----+----------+

current Post Table:

+--------+---------+----------------+-------------+-------------+
| userId | topicId | text           | createdAt   | updatedAt   |
+--------+---------+----------------+-------------+-------------+
| 15     | 12      | blah blah blah | *timestamp* | *timestamp* |
+--------+---------+----------------+-------------+-------------+
| 56     | 5       | lorem ipsum... | *timestamp* | *timestamp* |
+--------+---------+----------------+-------------+-------------+

So then i fetch post from some sources an get a new 1 This is a new topic posted by a user with id 72 in a topic with id 2. The source only returns the id, and to obtain the rest of the details of the user, i should make another request to their api.

Post Table after:

+--------+---------+---------------------+-------------+-------------+
| userId | topicId | text                | createdAt   | updatedAt   |
+--------+---------+---------------------+-------------+-------------+
| 15     | 12      | blah blah blah      | *timestamp* | *timestamp* |
+--------+---------+---------------------+-------------+-------------+
| 56     | 5       | lorem ipsum...      | *timestamp* | *timestamp* |
+--------+---------+---------------------+-------------+-------------+
| 72     | 2       | This is a new topic | *timestamp* | *timestamp* |
+--------+---------+---------------------+-------------+-------------+

User Table After:

+----+------+-----+
| id | name | age |
+----+------+-----+
| 15 | Paul | 26  |
+----+------+-----+
| 56 | John | 31  |
+----+------+-----+
| 72 |      |     |
+----+------+-----+

Topic Table after

+----+------------+
| id | name       |
+----+------------+
| 2  |            |
+----+------------+
| 5  | Business   |
+----+------------+
| 12 | General    |
+----+------------+

So now that i have this, i can make my request to their api and look for data for user with id 72 and data for topic with id 2.


texts: Which is quicker: Select of existing row vs Update where no row exists?

texts:
Which would run the fastest?

Select from a table using the primary key (integer, clustered index, 1,000,000+ rows)
Attempt an update on a different table, where the row does not exist? (where clause on integer primary key, clustered index, 200,000+ rows)

Background

We currently have some procedures that need to maintain a filtered copy of their table.

Tables involved:

[MasterTable] that contains the filter criteria
[ChildTable] to be filtered
[ChildFilterTable] to hold the filtered records

This is currently being done by:

Select filter criteria
If filter criteria match then:

Attempt an update
If no records updated, insert a new record

Example SQL:

DECLARE @FilterValue INT

/* Get FilterValue to check */
SELECT @FilterValue = FilterValue FROM [MasterTable] WHERE ID = @IDFromChildTable

IF @FilterValue = 123
BEGIN
    /* Attempt update */
    UPDATE [ChildFilterTable] SET
        ...
    WHERE ChildID = @IDFromChildTable

    IF @@ROWCOUNT = 0
    BEGIN
        /* Row not there yet, insert it! */
        INSERT INTO [ChildFilterTable] (ChildID, ....) VALUES (@IDFromChildTable, ....)
    END
END

Proposed change

Change to:

Attempt an update
If no records updated, then:

Select filter criteria
If filter criteria match then: insert a new record

So:

DECLARE @FilterValue INT

/* Attempt update */
UPDATE [ChildFilterTable] SET
    ...
WHERE ChildID = @IDFromChildTable

IF @@ROWCOUNT = 0
BEGIN
    /* Get FilterValue to check */
    SELECT @FilterValue = FilterValue FROM [MasterTable] WHERE ID = @IDFromChildTable

    IF @FilterValue = 123
    BEGIN
        /* Row not there yet, insert it! */
        INSERT INTO [ChildFilterTable] (ChildID, ....) VALUES (@IDFromChildTable, ....)
    END
END

Note: Business rules confirm that the filter value will never change once setup on the [MasterTable] so we don't need to worry about updating a value which does not match the filter record (ie: if it's in the ChildFilterTable, we want to update it.


texts: Why is the lease locked for a some full sql backups to Azure Blob using Ola Hallengren backup scripts?

texts:
I'm using SQL Server 2012 and working on backing up to Azure Blob using Ola Hallengren backup scripts.

Some backups are lease locked (ex. model) while others are not (ex. msdb). It's inconsistent, it can be vice-versa, completely locked or unlocked.

Why are the backups being lease locked? How do I have them all unlocked?


texts: Pb connection from client to postgresql server

texts:
I've no problem to access to postgresql server from local machine with two methods (localhost or specific ip ).
But when i try to connect from client machine to the postgresql server (in other pc using network lan), i have this error : can't connect to the server.
These steps were verified in computer which include postgresql server:

listen_adresses='*'  ==> OK in postgresql.conf file
host all all 0.0.0.0/0 md5 => Ok in  pg_hba.conf file
_ postgresql rule is inserted in inbound windows firewall including these parameters : protocol type : TCP, local port: specific ports: 5432, action: allow connection, profiles: domain, private and public all checked
I've verified with ping  all it's ok
but when I try to connect from client machine to postgresql server the problem persist.
Thank you in advance for your answers!


texts: Insert Values Into Table, Putting Duplicates In Another Table

texts:
So I posted this question yesterday.  Some of the responses I got were helpful, however it seems my issue is a bit more complex than I originally thought.

After doing some looking the reason I was getting errors with my INSERT statement was because I was having columns like this:

part_number  |  description  | information
------------------------------------------------
331335A11       Desc1          Info1
331335A11       Desc2          Info1

Essentially, there are a number of entries that have the same value for the part_number field (which is suppose to be a UNIQUE column) but different vales for their other columns.  As such the query was trying to insert them into the database, and I have my problem.

So what I am trying to do, because I am unsure just how many records in my table have this problem, is to do the INSERT into my parts table, but every time I get a repeated part_number value, instead of inserting it into the parts table, it is instead inserted into a table called parts_duplicates which won't have the unique restriction for the part_number column (but still have all the same columns as the parts table.  From here I can analyze my incorrect data points and fix them (hopefully).

My only problem is...I have no idea where to even get started on tackling this.  In the question I posted above one of the responses suggested using MERGE and I am currently in the process of testing that, but I am wondering if there is a better way to go about this.


texts: Does SQL Server route connections to a secondary replica which has commit mode as synchronous in case of an AG with multiple secondary nodes?

texts:
If I have an AG with 4 different nodes as below:

Primary Node
Secondary Node 1 - Asynchronous Commit - Read Only Yes
Secondary Node 2 - Synchronous Commit - Read Only No
Secondary Node 3 - Synchronous Commit  - Read Only Yes

In this case, if a connection to secondary node is required from an application, will the SQL Server route it to Node 1 or Node 3 ? Node 2 is out of question because it's Read Only is set to NO, isn't it ?

Ideally it should be routed to Node 3 because it has synchronous commit and data will be more consistent with the primary. Is that's what will happen here?


texts:
Consider the following query to find the (spatial) nearest neighbor; the PostGIS specific components here should not be of importance for my question, or so I believe:

SELECT a.id,
       c.geom
FROM   table_a AS a
CROSS JOIN LATERAL (
  SELECT ST_MakeLine(a.geom, b.geom) AS geom
  FROM   table_b AS b
  ORDER BY
         a.geom <-> b.geom
  LIMIT  1
) AS c;

(<-> is a PostGIS specific distance operator optimized to use the (spatial) index when in ORDER BY)

I assume the the ST_MakeLine function executes once, after the LIMIT 1 filter has been applied, but I'd like to verify if I'm correct and/or what's the actual order of operations here?

I thought I have a profound idea of the core operation chain, but I fail to grasp if it would, in fact, be beneficial to move possibly costly functions, involving the reference value and its one nearest neighbor, into the outer (left hand) SELECT.

UPDATE:

So I did as suggested and benchmarked performance and function execution with a custom function wrapper:

SELECT a.id,
       c.geom
FROM   table_a AS a
CROSS JOIN LATERAL (
  SELECT wrapper_function(a.geom, b.geom) AS geom
  FROM   table_b AS b
  ORDER BY
         a.geom <-> b.geom
  LIMIT  1
) AS c;

and

SELECT a.id,
       wrapper_function(a.geom, c.geom) AS geom
FROM   table_a AS a
CROSS JOIN LATERAL (
  SELECT geom
  FROM   table_b AS b
  ORDER BY
         a.geom <-> b.geom
  LIMIT  1
) AS c;

produce the same query plan, and execute wrapper_function for every row in table_a. I expected that.

However, a minor thing I found was that after multiple benchmarks with 1000 runs on equal setups, the first syntax performed about 1% better than the second, on different table sizes. I cannot be sure, but the query planning time seems higher on the last query.

Still interested in the exact order of operations, and maybe to find out what could make the difference (random vs. reason)?


texts: Minimum Oracle table size making IOT advisable?

texts:
I have inherited an IOT that doesn't seem to merit it because:

only 25k rows
fairly pure join table, non-index fields rarely queried
total index width only ~125 bytes
total row width only ~160 bytes
used infrequently by small user base for queries returning three rows at most

Is the IOT gaining us anything?

Thanks,

Andrew