texts: What's the difference between DBA and DA?

texts:
In categorization of users I have seen two different categorizations:

First one:

End user
DBA
DBP

and Second one:

End user
DBA
DA

What's the difference between DA and DBA ?


texts: relation between customer and product with review

texts:
I draw this ERD but I'm not sure if the relation in customer and product with review is correct. also, is it right that I made Rate, Date and time this way ?

this is my Description for review

Each  customer  can  write  as  many  reviews  of  the  products  he/she  bought  along  with  the  date and time of posting the review. The review will carry a unique ID. Also  the customer can give rating  from  1  to  5  which  are  illustrated  by  showing  glowing  gold  stars,  and  every  review  is welcomed and encouraged to be writing. So the website can be an open area to discuss the best option to buy. Also every customer can choose to writes as many reviews on the product or not.


texts:
In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate.  The script uses MYISAM tables and other than lack of support in the latest MYSQL versions, I have never had a problem with the way they work.
Changing to INNODB is something that would be time consuming and provide a zero benefit.
However, I am concerned that eventually there may come a time when either the Older MySQL version is not available or some other process forces the move.

My question is: Does anyone know of a specific reason to change from MyISAM to MyinnoDB just to keep up with MySQL.

If I can keep using what works for my own specific use, I see no reason to worry with testing and possible errors when there are more pressing issues that do need attention that have problems now.  MyISAM does not have a problem at this time.


texts: Mysql subquery not recognizing the subquery join table field

texts:
I have a three tables. The main table is the journeydata and next table is the alarm tables. The primary key for journeydata is journeydataID and its available as key in the alarm tables. Next in the alarm table there is a fenceID which is a primary key of another table that is fence table. Below I am trying to run a subquery example.What I am trying to do is join all the alarm for a particular journeydataID if it exist into a single string. But I keep getting error #1054 - Unknown column 'alarmgroup.journeydataID' in 'on clause'. Next my task I want to show is a alarm has a fence name value. Example if there is a relevant value of a alarmName with fenceName show as alarmName(fenceName).

SELECT journeydata.dateTime,
alarmDetails
FROM
journeydata
LEFT JOIN
    ( SELECT GROUP_CONCAT(alarmType.alarmName) AS alarmDetails
      FROM alarm
      JOIN alarmType
      ON alarm.alarmTypeID = alarmType.alarmTypeID
      LEFT JOIN fence
      ON alarm.fenceID=fence.fenceID
      WHERE alarm.associateID=6
      GROUP BY alarm.journeydataID
    ) AS alarmgroup
    ON journeydata.journeydataID=alarmgroup.journeydataID

WHERe journeydata.associateID=6


texts: MySQL: Find Extra Days From List Of Date Ranges

texts:
I have a following rows

start        end
2015-02-12   2015-02-16
2015-02-17   2015-02-18
2015-02-20   2015-02-20

Now I want to calculate number of days for a date range which are not in these ranges. So for example I have date range like this

2015-02-11 - 2015-02-19

so the result for this input should be 2 days. How? for a clear explanation

start        end
2015-02-12   2015-02-16   // 1 day which is 2015-02-11
2015-02-17   2015-02-18   // 1 day which is 2015-02-19 range
2015-02-20   2015-02-20  // 0 day because this row starting date is greater than input limit.

I want MySQL query for this.


texts: Separate license needed for Visual Studio 2012 SQL Server database projects?

texts:
We have SQL Server 2014 Standard Edition and would like to use Visual Studio 2012 Database Projects for version control.

Do we need to purchase a license to use VS database projects? Or is it included with our standard edition license; we just have to download it separately?


texts:
I'm running MySQL 5.7.15 on Ubuntu server 16.04.

After I've partitioned a table by range, select queries for some values are slow. I've checked that queries are slow if key is missing in older partition.

CREATE TABLE `table` (
  `id` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` bigint(20) unsigned NOT NULL DEFAULT '0',
  KEY `table_1` (`id`,`clock`),
  KEY `clock` (`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p2016_10_16 VALUES LESS THAN (1476655200) ENGINE = InnoDB,
 PARTITION p2016_10_17 VALUES LESS THAN (1476741600) ENGINE = InnoDB,
 PARTITION p2016_10_18 VALUES LESS THAN (1476828000) ENGINE = InnoDB,
 PARTITION p2016_10_19 VALUES LESS THAN (1476914400) ENGINE = InnoDB,
 PARTITION p2016_10_20 VALUES LESS THAN (1477000800) ENGINE = InnoDB,
 PARTITION p2016_10_21 VALUES LESS THAN (1477087200) ENGINE = InnoDB) */|

Executing the query:

select *
from table
where id='25885'
and clock>1476044179
order by clock desc
limit 3;

...takes over 1 second, maybe because there are only entries (15 tuples) in the last partition.

If I instead execute:

select *
from table
where id='24946'
and clock>1476044179
order by clock desc
limit 3;

...the query is faster (much faster) maybe because there are entries in all partitions (8k tuples).

What am I missing?


texts: MSSQL peer to peer replication what is the best practice to drop and create multiple table

texts:
I have a Transactional Replication server in peer to peer topology with 3 server node.
My development strategy was Agile, so that will be a frequency change on the database etc.
Scenario: I need to drop 20 tables out of 100 tables, and create another 30 tables at a time.
May I know what is the best/common practice to do it?
What I can think of was,

Drop all replication article and subscription for 3 server node.
Drop the 20 tables and create the 30 tables on each server node.
Resetup back the peer to peer topology replication to the 3 server node.

(P/S: I am new in this replication technology, do guide me if my step is wrong.)


texts:
I'm trying to learn some basic database design through open source projects.

And look into the WordPress database schema, the posts table for example, you'll find something like this

post_author
post_date
post_title
post_content
...
...
etc.

We're already in the posts table, why not just author, date, title, content? Is there any reason? Or just because WordPress is too old..?

(The same in other tables. In users table, will find user_login, user_pass, user_email, etc.)

I found many people say that using a column prefix is a bad practice, so, just title, content... will be a better design?


texts: Impdp over DB_LINK failing with ORA-31631

texts:
I am getting below error message while trying to execute impdp between our SIT and UAT environments:

ORA-31631: privileges are required

ORA-39149: cannot link privileged user to non-privileged user

The Roles for the source database are :

EXP_FULL_DATABASE

IMP_FULL_DATABASE

RUBY_FULL_ROLE

SELECT_CATALOG_ROLE

The roles for the target Database are:

CONNECT

EXP_FULL_DATABASE

IMP_FULL_DATABASE

RESOURCE

SELECT_CATALOG_ROLE

As we can see, both the source and target schemas have IMP_FULL_DATABASE and EXP_FULL_DATABASE roles.

Can someone please help me out with this.

Thanks for Help

Vishad


texts: automatically change value on the table mysql

texts: i have table invoice in my database that contain atributes 'due date', 'date' and 'status'. What i want is data on 'status' change automatically. Example, status (unpaid change to expired) when date past the due date. Does it make sense ?


texts:
i have created user and role in Cassandra and tracing is enabled.
but sessions and event table does not capture user info.
below tables gives information about what query has been executed and from which host, but i need user information as well. how we can find that ??

select * from system_traces.events;
 select * from system_traces.sessions;


texts: How to time SPARQL queries on an Oracle12c database?

texts:
I have an Oracle 12c instance setup with an Apache Fuseki endpoint sitting on top of it. I am able to query the database using the Fuseki endpoint. I want to be able to get the execution time of some queries without the network overhead. What is the best way to go about this?

EDIT: I also have access to the server but do not have DBA permissions. I can only query the data.


texts: Is it possible to replicate data changes of a database with triggers using binary logs?

texts:
I'm tasked to minimize the downtime of the database update. Unfortunately, I cannot use pt-online-schema-change toolkit because some tables in the current database has MySQL triggers that does further modification of data of other summary tables that may use past data.

I'm trying to research if this methodology is possible:

1.) Create a separate database for containing all new tables.
2.) Turn on binary logs
3.) Copy all data from old tables to new tables.
4.) Stop operation.
5.) Parse the binary logs and replicate the data changes to the new tables.
6.) Replace the old tables with the new tables
7.) Resume operation.

I tried enabling binary logs and set its format to STATEMENT. Unfortunately, it does not log the SQL statements executed by the triggers. Is there a way to log the SQL statements triggered in the binary logs?


texts: json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

texts:
I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the total count.

Instead the behaviour should be like if there's no array element in the particular json than it should still be counted in the total count value. Since I am using json_array_elemnts() in FROM clause and not WHERE clause.

Example:

SELECT count(*)
FROM incidents_incident
   , json_array_elements(incidents_incident.incident_data -> 'field_52ca447762ba2')
WHERE resort_id = 1
AND dt_created >= '2015-06-01 00:00:00'
AND dt_created <= '2015-08-10 00:00:00';


texts: How can I specify the last parameters in a function call and let the others default to the DEFAULT in the declaration?

texts:
I have a function with 5 parameters,

arguments p1, and p2 are mandatory
arguments p3, p4, and p5 are optional and have a DEFAULT set

The function looks like this,

CREATE FUNCTION test_func(
  p1 int,
  p2 int,
  p3 int DEFAULT 3,
  p4 int DEFAULT 4,
  p5 int DEFAULT 5
)
  RETURNS int AS
$$
  SELECT p5;
$$ LANGUAGE sql;

I can invoke it like this,

SELECT test_func(1,2,3,4,5);
 test_func
-----------
         5

But I would like to allow p3 and p4 to default and invoke it like this,

SELECT test_func(1,2,5);

Is there a way to specify the value for the last optional parameter (p5) without giving values to the preceding ones (p3, p4)? I don't want to hard-code default values for the p3, p4 in the call for test_func, because I may need to change their default values in the future.


texts: Merge 2 pairs of tables from 2 databases. Beginner

texts:
I have two MySql databases DB1 and DB2. Each of them has tables tbl1 and tbl2. These two tables have the same primary key (Column1). Tables in DB1 have N rows. I want to create new database and table, which will look like:

DB1.tbl1.Col1   | DB1.tbl1.Col2 | ... | DB1.tbl1.ColK | DB1.tbl2.Col2 | DB1.tbl2.ColZ
    ...         |    ...        | ... |     ...       |    ...        |      ...
DB2.tbl1.Col1+N | DB2.tbl1.Col2 | ... | DB2.tbl1.ColK | DB2.tbl2.Col2 | DB2.tbl2.ColZ
    ...         |    ...        | ... |     ...       |    ...        |      ...

But, I could not use MERGE as I need indexes in DB2.tbl1.Col1 in new table to be DB3.tbl.Col1=DB1.tbl1.Col1, DB2.tbl1.Col1+N.

I now how to do it on the side of client (on Python), but is it possible to do it via some MySql script on server side?

Thank you.


texts: How to restore a .backup file from a single table on postgres?

texts: I have a .backup dump from a single table, but it didn't work restore like a normal backup from an entire db. I'm creating this backup file with the following batch script bin\pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE% --table <schema.table-name> <db-name> And i'm restoring it with the pgadmin restore option, i didn't get any error message, the restore run normally, but the table don't show on my db/schema.


texts:
Have a current AOAG setup with 1 database.  Trying to add a 2nd database.  It's in FULL recovery mode and I performed a FULL backup.  When I try to add the database, it says I need a FULL backup and the check box is "grayed" out so I cannot select it.

I have not found anything on Google with information on this so hoping someone who is a "guru" on AOAG can point me in the correct direction.


texts:
I have multiple tables, most of them have integer primary key. Is it a bad practice If I put all tables to identity(1,1) ?

I have thinking about this to be able to distinguish:

identity(1000,1) for the users table
identity(100000,1) for the employees
identity(20000,1) for leave_request
for leave_given_id (in leave_given table), It's not going to be automatic increment but I'll take the leave_request_id and add 2 to the left. (or 200000). In other words, I will have 220001 in leave_given_id If leave_request_id is 20001

and so on..

Does it make sense to do that?


texts: Creating a normalised Twitter database and foreign keys

texts:
Hello I am creating a normalized Twitter DB for a project I am working on and wanted a bit of guidance on foreign keys.

I am curious about what the difference would be if for hashtags, symbols, user_mentions, urls and media, I made another field in each table called tweet_ID and the relationship changes to the primary key id in the tweet table to each tables tweet_id? The way I've modelled it means the tweets table has 5 extra fields for each tweet object.

Currently I have the schema designed as follows:

Are there glaring issues with the way I have implemented the foreign keys in the model this way?

Thank you!


texts: How to Properly shutdown and disassemble SQL Database Replication

texts:
While testing SQL Server Replication we setup Replication on our ERP Test DB from SQL Server Enterprise to SQL Server Standard.

We have since got replication up-and-running on our Live database and we no longer need replication on the Test database...

We would like to PROPERLY remove the replication to free up resources and delete the Test Replicated DB.

I  have read online about issues with not properly 'disassembling' or shutting down Replication but I have not been able to find a step by step on how to do it.

I just have one test DB that is replicating between these servers that I need to shutdown and remove completely.

Specifics:

  SQL 2012, 1 DB, Transactional Replication.


texts: If i issue a rollback when recovery mode is bulk logged, will my bulk operations be rolled back?

texts: If my recovery model is BULK LOGGED, I'm in a transaction, and I've issued some statements that are minimally logged like SELECT * INTO, will a ROLLBACK undo those minimally logged statements?


texts: What are the advanvates and disadvantages of vacuum in postgresql?

texts:
I'm new to PostgreSQL, I know about the concept of vacuum in database.

But I want to know the major advantages and disadvantages of vacuuming process.
Also I want to know:

What happen if I access the table while vacuuming full database?
Whether it will affect the table, or cause data loss?


texts: Throttle DDL statement to avoid impacting performance of other queries

texts:
In MySQL, is there any way that I can make an ALTER TABLE or other DDL statement run more slowly, to mitigate its impact on the performance of other queries against the same database? I have found that a long-running DDL statement can cripple the performance of other queries against the database, and I'd like to be able to ALTER huge tables without suffering this impact.

To be clear, this question isn't about locks - online DDL solves most of our locking issues and allows us to run DDL commands on big tables without outright blocking writes to the table. It's purely about mitigating the performance impact that the heavy CPU and IO load imposed by running a DDL statement against a large table can have on the MySQL server. Nor am I interested in strategies that involve setting up a replicating slave on another computer and running the DDL statement there; I know that this is possible, but would prefer the more lightweight approach of just throttling the DDL if that is possible.

MySQL has the LOW_PRIORITY keyword for DML statements to mitigate their performance impact, and there's some discussion on Stack Overflow about the similar-in-principle problem of mitigating the performance impact of mysqldump, but I can't find anything similar for ALTER TABLE statements.


texts:
I'm working with a application and I have no control over the application source code, only SQL.

The entire process is this:

Row inserted via an application
Vendor trigger fires and calls an SP to insert the new row into my table. This new row is returned when I do a SELECT
I have an INSERT trigger on my table that selects the row and executes sp_send_dbmail to email the row just inserted (and will then truncate my table when live)

The issue is that I get the email (so the INSERT is picked up and the trigger executes) but it's blank.

If I then INSERT a second row, the trigger fires and I get an email containing the first row inserted.

My trigger:

ALTER TRIGGER [dbo].[SendEmail_INS]
ON [dbo].[tbl_UNIT_INS]
FOR INSERT
-- AFTER INSERT
AS

DECLARE @thequery NVARCHAR(MAX)

    SELECT @thequery =
    'SET NOCOUNT ON
     SELECT  contract,
         cont_line,
         site_ref,
         comp_id,
         cont_line as cont_line2,
         item,
         qty,
         ser_num,
         cust_num,
         description,
         unit_stat_code
    FROM tbl_UNIT_INS'

EXEC msdb.dbo.sp_send_dbmail
     @query_result_header = 0,
     @exclude_query_output = 1,
     @profile_name = 'Outbound',
     @recipients = 'me@email.com,
     @query = @thequery,
     @subject = 'New Item Inserted'

I've tried using AFTER INSERT as well - same result.

It seems the trigger is firing before the row is inserted but the fact I get an email means the insert has happened so the insert has completed?

SQL Server 2012.


texts: Can't add a foreign key constraint in a table on Postgresql

texts:
I'm trying to add a foreign key of "prefectures" table in "centresdevote" table.
But I'm having this error while trying to add a foreign key to "centresdevote" table.
I had no problem adding foreign keys to other tables in my database by the way, and I can't seem to figure out why this error pops up only for this one.
I'm working on Postgresql 9.2 and I've added the foreign keys for the other tables manually. I tried also using SQL code as follows :

ALTER TABLE centresdevote
ADD CONSTRAINT objectid
FOREIGN KEY (objectid) REFERENCES prefectures (objectid);

Here's the error

ERROR:  insert or update on table "centresdevote " violates foreign key constraint "objectid"
DETAIL:  Key (objectid)=(10) is not present in table "prefectures".

Here's the structure of the two tables :

"prefectures" table :

objectid integer NOT NULL,
nomprefecture character varying(70),
shape st_geometry,
respcommissionrecensement character varying(70),
CONSTRAINT prefectures_pkey PRIMARY KEY (objectid)

"centresdevote" table :

objectid integer NOT NULL,
arabicname character varying(70),
frenchname character varying(70),
shape st_point,
CONSTRAINT centresdevote_pkey PRIMARY KEY (objectid)

Thanks in advance!


texts:
Suppose a dump is provided from pgdumpall

pg_dumpall > outfile.bkp

How to verify if the outfile is a vaild database file for restoration. Just as a precautionary step before restoring it like

psql -f outfile.bkp postgres

For example: In MySQL we can head first few lines of the backup files produced from mysqldump for having SQL like syntax.


texts:
What are possible causes for the following error?

  DB Error: connect failed [nativecode=08004 [unixODBC][Informix]
  [Informix ODBC Driver [Informix]Server rejected the connection.]

I have doubled the number of allowed connections thinking was that it was running out of connections in the pool but that has made no change to getting this error. It only happens when server is under load.


texts: Add linked server from SQL-Server to MySQL: "Data source name not found" (Error:7303)

texts:
I'm trying to link my SQL-Server 2005 on Windows Server 2008 R2 with MySQL 5.5.40 on a different computer. I have followed this guide:

http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

However, when i try to test the connection under "Linked Servers" as you can see in this screenshot:

i'm getting this error:

The ODBC data source was added as shown in above link as you can see here:

What can be the reason for this? Note that the Server2008 is a 64bit OS, if that matters.


texts: mysql: How do you determine the ideal 'open_files_limit' setting value?

texts:
I am facing this issue:
https://serverfault.com/questions/104618/out-of-resources-for-mysqldump

Questions

I know I need to increase open_files_limit, but to how much?
Can I call a command in mysql that tells me how much this should be?


texts: executing single query in loop increase time every time

texts:
Am executing one query which is the same as below. Now here the problem is as limit increasing its increasing execution of time.

I am adding limit using a PHP script. So anyone likes to suggest to stop increasing the time of execution by mysql?

There is total records are 12042993 which I have to copy from one table to another table. But when am executing the single query without limit that time it's not working.

So will you please suggest a better way to avoid this increasing time?

For now, I have added one index which is below. I know its wrong. But for me, I just have to concentrate only and only on this query. There is no any other query is running on the entire server/database.

alter table cdrs add index cdrs_data  (`callstart`,`uniqueid`,`accountid`,
    `type`,`callerid`,`sip_from_uri`,`callednum`,`billseconds`,trunk_id,disposition,
    debit,cost,provider_id,pricelist_id, package_id,pattern,notes,reseller_id,
    provider_call_cost, call_direction,calltype,progress_media_stamp,
    progress_mediamsec, is_recording,call_reward_amount);

insert into cdrs_data(uniqueid,accountid,type,callerid,sip_from_uri,callednum,
    billseconds,trunk_id,disposition,callstart,debit,cost,provider_id,pricelist_id,
    package_id,pattern,notes,reseller_id,provider_call_cost,call_direction,
    calltype,progress_media_stamp,progress_mediamsec,is_recording,call_reward_amount)
  select  uniqueid,accountid,type,callerid, sip_from_uri, callednum,billseconds,
       trunk_id,disposition, callstart,debit,cost, provider_id,pricelist_id,package_id,
       pattern,notes,reseller_id,provider_call_cost, call_direction,calltype,
       progress_media_stamp,progress_mediamsec, is_recording,call_reward_amount
    from callinfo
    where callstart >= '2017-01-30 00:00:00' and callstart < '2019-05-06 00:00:00'
    order by callstart asc limit 24000,3000

All suggestions are appriciated.


texts: How to add a PostgreSQL 10 identity column to an existing table?

texts:
I'm reading about the new Postgres 10 identity column, and saw how it is used to replace SERIAL columns when CREATE TABLE.

Is it also possible to add an identity column to an existing table?

For example, with the old SERIAL columns, I can do:

ALTER TABLE sourceTable ADD  COLUMN ogc_fid SERIAL;

With the identity columns, is there an equivalent of above? I tried:

ALTER TABLE sourceTable ADD  COLUMN ogc_fid GENERATED BY DEFAULT AS IDENTITY;

But got an error:

  ERROR:  syntax error at or near "BY" LINE 1: ...R TABLE sourceTable
  ADD  COLUMN ogc_fid GENERATED BY DEFAULT

I'm thinking about replacing the SERIAL columns with IDENTITY columns in my scripts, and just wanted to check if this is feasible.


texts: What is more efficient, a where clause or a join with million plus row tables?

texts:
We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows.

Sample structures:

MasterTable (Id, UserId, Created, Updated...) -- 15MM Rows
DetailsTable (Id, MasterId, SomeColumn...) -- 250MM Rows
UserTable (Id, Role, Created, UserName...) -- 12K Rows

We regularly have to do a few queries against all these tables. One is grabbing statistics for free users (~10k free users).

Select Count(1) from DetailsTable dt
join MasterTable mt on mt.Id = dt.MasterId
join UserTable ut on ut.Id = mt.UserId
where ut.Role is null and mt.created between @date1 and @date2

Problem is this query will some times run a long damn time due to the fact that the joins happens long before the where.

In this case would it be wiser to use wheres instead of joins or possibly where column in(...)?


texts: Auto incrementing a column based on another

texts:
I have an application which generates invoice_id's. These id's need to be unique. The issue is that a client can have more than one invoice depending on what plan they are on. It could be 2, 3 or 4. So I made a column with extra invoice_id's which I called invoice_idA and invoice_idB.

Now I do not know how to handle the incrementing of the invoice_id. It would be easier if it is one invoice so I can just use an auto_increment but I will have an issue because if there is more than one invoice_id in a column for one client, there could be duplicates.

Looking at the picture,

I need a way to make invoice_id increment to 1008 based on the value in invoice_idA of 1007.
If I insert a value in invoice_idB like 1008, how will I increment invoice_id to read from it because it could be 2,3 or 4 extra invoice numbers. I believe even if there was a way I may need to know the exact column to increment from but in this case, it could be invoice_idA or invoice_idB.


texts: mysql logging activity from specific user or ip

texts:
I have mysql server.

The server is accessed by my application, and by external auditor (person using mysql workbench).
The auditor has specific user and password and dedicated IP and it is granted only for select privileges.

I need to log the activities from the auditor. Is it possible to do it somehow ? If not can I log the packages that are coming from the network ?


texts: Would SQL Server Profile show triggers or other code which run as a side-effect of the query I'm profiling?

texts:
I've been using profiler to test a massive script I'm running in order to check for unintended side effects, e.g. triggers that I don't know about running because I've updated data in a certain table.

Having executed the script while the profiler is running, I can't see any additional code executing but I don't know whether that's because there is none or because profiler wouldn't show it for some reason!

I restricted the profile to these events:

  SQL:BatchCompleted
  SQL:StmtCompleted
  SP:Completed

Am I missing events which might show me that a trigger has fired or can I be confident that nothing is happening which I'm not expecting?


texts: 64bit SQL Server with total virtual address space of 4GB. How could this be?

texts:
My SQL Server 2012 Standard edition, 64bit has a total virtual address space of 4GB. Why is that?

it is a 64bit installation: print @@VERSION :

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (Intel X86)
    Dec 28 2012 19:06:41
    Copyright (c) Microsoft Corporation
    Standard Edition on Windows NT 6.2 <X64> (Build 9200: ) (WOW64)

virtual space of just 4GB

select total_virtual_address_space_kb from sys.dm_os_process_memory
4194176

(entire result of sys.dm_os_process_memory
http://screencast.com/t/2PN2PDQYpt )


texts: SQL Server 2008 Replication "between retries" or "worker thread no available"

texts:
All our existing servers are in SQL 2008 and I am in the process of upgrading to SQL 2014 and as well moving SAN to different SAN storage.

As part of it I have to upgrade my distributor.

We got about 7 production database servers and data from all that servers are replicated using a separate distributor to multiple subscribers. Totally got about 78 publications going to multiple subscribers for various reasons!!! Don’t ask why so many. It is what I inherited as part of new job.

So I started with creating a new VM with Windows 2012 and SQL 2014, made that as the distributor and started changing the distributor of each publisher to the new one and eventually once everything is moved the old distributor can be dropped.

We setup replication from first 2 servers (about 30 publications or assume x publications) to multiple subscribers and things worked fine. It is all Transaction replication with pull subscription. NO delays in replication things working great.

The moment I add a one more publication or a new article the new transaction replication goes into “between retries” or sql agent jobs complains about no worker threads to start. I tested by deleting one of the existing publication and the new one works fine. So I digged around and read that the number of worker threads on the server level and subsystem level needs to be high enough to run mulitiple SQL agents jobs continously.

So I updated the worker threads on server level setting, restarted server, and that didn’t help. I found the subsystem worker thread information in msdb.dbo.subsystems and updated the worker threads as defined here http://blogs.msdn.com/b/karthick_pk/archive/2011/02/01/sql-agent-maxworkerthreads-and-agent-subsystem.aspx and that doesn’t seem to help.

I also found about Desktop Heap issue and updated the Shared allocation of member in registry as specified @ https://support.microsoft.com/en-us/kb/949296

My distributor is a VM, 2 processors and 8GB ram on it. It is same as the existing SQL 2008 box. I do see that the existing 2008 has a high desktop heap and as well subsystem processor count updated. One issue in updating msdb.dbo.syssubsystems table is it gets reset whenever SQL server restarts by this proc: dbo.sp_verify_subsystems. So I tried adding a startup proc that updates the worker threads and that doesn’t help. This issue has been discussed @ SQL Server 2012 Replication Subsystem Increase Number of Working Threads

One thing that seems to work in the new server is, after I setup more replication if I restart the server then it works. But, the moment I add a new replication the new one doesn’t work. In my older server I could as much new replication I and want and doesn't require any restarts. So restart doesn’t sound right.

Any help is appreciated. Should I update the worker threads or other settings for Agent jobs? or Do I have to rely on restarts?

Thanks,
Nachi


texts: How to remove from a table using left join to get the id of the same table by using alias


texts: PostgreSQL - Grant privileges as superuser

texts:
I have a question about the rights in PG (version 10).

I did 2 tests.

The scenario is the following:

create a db with the superuser
create a schema owned by a user
create a read only role
grant privileges to the role
grant role to the read only user
create a table in the specific schema with the user that owns it

When I grant the privileges as a superuser, the "testuser" can't read the table: test KO.

When I grant the privileges as the owner of the schema, "testuser" can read the table: test OK

Why the superuser can't grant the privileges on a schema owned by another user ?

TEST KO

pg_ctl stop -D .
rm -rf *
pg_ctl init -D .
pg_ctl start -D .
psql -p 5432 -d postgres -c "create database testdb"
psql -p 5432 -d postgres -c "create user testuser"
psql -p 5432 -d postgres -c "create role role_read_testdb"

psql -p 5432 -d testdb -c "create schema authorization testuser"
psql -p 5432 -d testdb -c "GRANT SELECT ON ALL TABLES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -c "GRANT SELECT ON ALL SEQUENCES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -c "GRANT USAGE ON SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON TABLES to role_read_testdb"
psql -p 5432 -d testdb -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON SEQUENCES to role_read_testdb"

psql -p 5432 -d postgres -c "create user user_read"
psql -p 5432 -d postgres -c "grant role_read_testdb to user_read"

psql -p 5432 -d testdb -U testuser -c "create table t1(col1 int)"
psql -p 5432 -d testdb -U user_read -c "select count(1) from testuser.t1"
ERROR:  permission denied for relation t1

TEST OK

pg_ctl stop -D .
rm -rf *
pg_ctl init -D .
pg_ctl start -D .
psql -p 5432 -d postgres -c "create database testdb"
psql -p 5432 -d postgres -c "create user testuser"
psql -p 5432 -d postgres -c "create role role_read_testdb"

psql -p 5432 -d testdb -c "create schema authorization testuser"
psql -p 5432 -d testdb -U testuser -c "GRANT SELECT ON ALL TABLES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "GRANT SELECT ON ALL SEQUENCES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "GRANT USAGE ON SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON TABLES to role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON SEQUENCES to role_read_testdb"

psql -p 5432 -d postgres -c "create user user_read"
psql -p 5432 -d postgres -c "grant role_read_testdb to user_read"

psql -p 5432 -d testdb -U testuser -c "create table t1(col1 int)"
psql -p 5432 -d testdb -U user_read -c "select count(1) from testuser.t1"
 count
-------
     0
(1 row)

Thanks for your help !


texts: SQL Server Migration restore backup vs copy data and log files

texts:
I'm tasked with doing a SQL Server 2000 to 2005 migration. I will be doing a side-by-side migration. I've read from a few different sources that I can either:

Perform a backup of the database on SQL Server 2000, ship that backup to the new hardware and restore the database in SQL Server 2005; or
Detach the database on SQL Server 2000, copy the data and log files to the new hardware and attach the files in SQL Server 2005.

Is there a difference between these methods?
Which is the safest?

I've been through the pre-upgrade tasks of checking the database with Upgrade Advisior and I have no warnings to resolve before the migration.

Any comments would be greatly received.


texts: SQL Transactional Replication and Table level compression

texts:
At the moment, I have my production server with several large databases and I use a second SQL server for reporting purposes. I maintain the reporting copies of the databases using transactional replication.

As we've recently upgraded to EE, I want to enable data compression on several of the large tables on the reporting server to save on disk space (I'm currently not ready to enable it on the production server)

Has anyone tried this and if so are there any hidden pitfalls?

The only pitfalls I can see is if the table is re-snapshotted, the replicated table would lose its compression. As all of the additional reporting indexes would also be lost, its not something I would undertake lightly.


texts: What known strategies, if any, can I use to validate a Slowly Changing Dimension?

texts:
I have a customer dimension in our data warehouse (SSAS 2014 Multidimensional). It is set up as a Type II SCD, with just Start and End Date fields to track changes. (null end date = current).

As our daily load runs and I see the changes flowing in, I sometimes worry that it's creating new records when it shouldn't.

What kind of scripts can be run to compare each customer over it's respective history and make sure it's changing appropriately?

Also, and I think I'm touching on the gaps and islands problem, what strategies are there for verifying that the start dates and end dates are all contiguous?

I want to feel confident that the table is reporting correct data day to day without becoming a beast due to some null check causing a ton of records to be created each day.

Thanks for any help you can offer.


texts:
I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two columns in it - [tempId,n]. For the person table I have a primary key defined using 3 of its columns [tempId,n,sporder]

Using the sorting dictated by the clustered indexing on primary keys, I have generated a unique ID for each household [HHID] and each person [PERID] record (the snippet below is for generating PERID]:

 ALTER TABLE dbo.persons
 ADD PERID INT IDENTITY
 CONSTRAINT [UQ dbo.persons HHID] UNIQUE;

Now, my next step is to associate each person with the corresponding households ie; map a [PERID] to a [HHID]. The crosswalk between the two tables is based on the two columns[tempId,n]. For this I have the following inner join statement.

UPDATE t1
  SET t1.HHID = t2.HHID
  FROM dbo.persons AS t1
  INNER JOIN dbo.households AS t2
  ON t1.tempId = t2.tempId AND t1.n = t2.n;

I have a total of 1928783 household records and 5239842 person records. The execution time is currently very high.

Now, my questions:

Is it possible to optimize this query further? More generally, what
are the thumb rules for optimizing a join query?
Is there another query construct that can achieve the result I want with better
execution time?

I have uploaded the execution plan generated by SQL Server 2008 for the whole script to SQLPerformance.com


texts: Confusion about charset for connected tables from MS Access

texts:
I run 10.4.12-MariaDB on Windows10 and I use 64-bit ODBC driver to connect tables from MS Access to MariaDB database. I am simply glad that it works (!).
But going deeper and working more with connected tables (yes, this way to link mdb and open source db world is too complicated, but no other way for me...), I found some constraints.

Now I am confused with charsets.
My configuration of MariaDb is as follows:

version: 10.4.12-MariaDB on Windows10 64-bit
[mysqld]
...
character-set-server=utf8

My table connected to Access ?2003? mdb file:

CREATE TABLE pohoda.xfa (
  ID INT(10) NOT NULL,
 ... list of fields
)
ENGINE = CONNECT,
TABLE_TYPE = ODBC,
TABNAME = 'Fa',
BLOCK_SIZE = 10,
CHARACTER SET utf8,
COLLATE utf8_general_ci,
CONNECTION = 'DSN=pohoda_64b;DBQ=c:/path/to/mdb;';

and further connection details from information_schema.tables.CREATE_OPTIONS:

`TABLE_TYPE`='ODBC' `TABNAME`='DJjizdy' `DATA_CHARSET`='cp1250' `BLOCK_SIZE`=10

To summarize, when I created the connected tables, I tested that I should set DATA_CHARSET='cp1250' to display non-ascii characters right.

As for browsing the tables, it works great. But when I came to the query, a problem appeared.

Simple SELECT with non-ascii character in WHERE clause does not return appropriate and really existing rows:

MariaDB [pohoda]> SELECT  id, Firma FROM  xfa WHERE Firma LIKE 'Adam Vič%'
Empty set (0.120 sec)

MariaDB [pohoda]> SELECT  id, Firma FROM  xfa WHERE Firma LIKE 'Adam Vi%';
+-------+--------------+
| id    | Firma        |
+-------+--------------+
| 14443 | Adam Vička   |
| 14488 | Adam Vička   |

Thus, while  WHERE Firma LIKE 'Adam Vič%' returns an empty set, just removing the character "č" from where clause  Firma LIKE 'Adam Vi%' gives the right recordset.
I tested it from command and from HeidiSQL with same results.

Could you please help me to set a charset for connected table?


texts:
On a Windows server 2012 R2 with MSSQL 2012 SP4 Express, installed with all the default options, I do daily backups of a small database with a scheduled task running the backup command with default options. The database sees very little activity, maybe a dozen inserts a day in a specific table which has no indexes other than the PK. The recovery model is Full.

Yesterday's backup was 33mb and today's backup is 11mb. Auto-shrink is disabled, backup compression is disabled, and if I restore yesterday's backup then re-back it up, I get a 11mb file. I've verified we didn't lose any data and everything is still there. I also tried restoring and re-backing up on my PC running MSSQL2014 and I get the same result (33mb to 11mb).

I don't see any recent updates for MSSQL on the server nor on my machine, and I can't find any evidence of this behavior online. Is there some other process which could explain this? Not that I'm complaining mind you, I just find it weird and I'd rather understand what happenned.

Edit #1 -for LowlyDBA

The backup command is:

DECLARE @pathName NVARCHAR(512)
SET @pathName = 'D:\bkp\my_DB_' + REPLACE(Convert(nvarchar(20), GETDATE(), 120),':','-') + '.bak'
BACKUP DATABASE my_DB TO DISK = @pathName

results of sp_spaceused

backup:

database_name   database_size   unallocated space
my_DB           12.50 MB        0.13 MB

reserved    data     index_size unused
11136 KB    8584 KB  1936 KB    616 KB

current:

database_name   database_size   unallocated space
my_DB           13.50 MB        0.99 MB

reserved    data     index_size unused
11272 KB    8592 KB  1960 KB    720 KB


texts:
I have the following two tables with a foreign key on status_id:

mysql> describe usr_cookbook;
+-----------+-----------+------+-----+-------------------+-------+
| Field     | Type      | Null | Key | Default           | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| user_id   | int(11)   | NO   | PRI | NULL              |       |
| recipe_id | int(11)   | NO   | PRI | NULL              |       |
| status_id | int(11)   | NO   | MUL | NULL              |       |
| added_ts  | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-----------+-----------+------+-----+-------------------+-------+

mysql> describe usr_cookbook_status;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| status_id | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(45) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

There's two rows in usr_cookbook_status:

mysql> select * from usr_cookbook_status;
+-----------+-----------+
| status_id | name      |
+-----------+-----------+
|         1 | Try Soon  |
|         2 | Favorites |
+-----------+-----------+

So I should be good to insert into usr_cookbook right?

mysql> insert into usr_cookbook (user_id, recipe_id, status_id) values (3, 5, 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`tomato`.`usr_cookbook`, CONSTRAINT `fk_cookbook_status` FOREIGN KEY
(`status_id`)
REFERENCES `usr_cookbook_status` (`status_id`) ON DELETE NO ACTION ON UPDATE
NO ACTION)

Why the constraint violation?  The status_id I'm using (1) exists in the parent table.  I'm sure there's something simple I'm missing here...


texts:
I try to optimize MySQL with mysqltuner but it seems like my my.cnf was never upated. there are lots of depricated warnings in /var/log/syslog

How can I get back the default configuration file so I can start optimizing from scatch?


texts: In SQL Server Always On 2019 environment should SSRS/SSIS be on a separate server?

texts:
I am looking for some advice in setting up a new SQL Server 2019 Always On instance.

We have a 2 server cluster right now, moving to Always On with a similar two virtual server setup.

I want to know if that is recommended.

We have about 200 databases, maybe 40 used per day.

We have about 350 SSRS reports run on average per day, up to max of 1100 in the past couple months.  We have about 50 SSIS jobs (packages) that run, mostly every night.

Should SSRS and SSIS be on a separate server?  Or should the SSRS temp databases be on the SQL Always On servers but the SSRS install be on the separate server?


texts:
Using Postgres 9.6

I am trying to create an index on a type adt_flds with a field also named adt_flds

 create type adt_flds as
(
      created_at timestamp
    , updated_at timestamp
    , created_by text
    , updated_by text
    , created_by_client_addr inet
    , updated_by_client_addr inet
    , created_by_client_cmptr text
    , updated_by_client_cmptr text
);

My attempt is as follows for

schema = document

table = training_complete_history

field = adt_flds

field column = created_at

SQL

create index training_complete_history_adt_flds_index
on document.training_complete_history ((adt_flds).created_at);

Unfortunately, my attempt yields the following error.

  [42601] ERROR: syntax error at or near ")" Position: 114.

How would I properly index created_at inside adt_flds?


texts: Shrink a database below its initial size

texts:
I've got a SQL server 2005 dev database that is a 30GB copy of live. We have deleted some data that is not needed in dev, which brings the data file space used down to 20GB. So we have about 33% unused.

I need to reclaim the space, which will allow us to have a second dev DB on the server (based on the cut down version); however, I cannot reclaim the space, I have done the following:

The initial size of the file SMS2_Data is 30GB.

DBCC SHRINKFILE (N'SMS2_Data' , 0, TRUNCATEONLY)

followed by

DBCC SHRINKFILE (N'SMS2_Data' , 19500)

No joy. I have tried making a backup, creating a new DB with a low initial size then restoring, no joy as the initial size gets overwritten. Have also tried:

ALTER DATABASE SMS2HazSub MODIFY FILE (NAME = 'SMS2_Data', SIZE = 20000)

This errored, saying:

  MODIFY FILE failed. Specified size is less than current size.

I tried 20800 and then kept going up til 29000 (29GB) and it still will not let me change it.

Have done the shrink then changed the recovery mode from FULL to SIMPLE and back again. No joy.

I thought it was to do with some TEXT fields. We have about 6 across the system. So as a test I dropped them all and then did a shrink of the file and still no change.

The only option left is to reimport the data to another DB. This is not practical, as it would have to be done on the live DB, which carries too much risk. We semi-regularly grab a copy of the live DB and overwrite dev/test. We have something like 500 tables. I would like a way of doing it that would not have the risk of exporting data to a new DB.

I tried moving the data to another file, and it copied all but 5% of the data. This is what lead me to try and drop all the text columns.

The server is on compatibility mode 90, but is SP2. I've now done the following 3 times: reindex all tables, backup database, shrink file, shrink database. Still no joy.

EXECUTE sp_spaceused returns:

database_name   database_size   unallocated space
SMS2Tests       31453.94 MB     13903.16 MB

reserved     data         index_size   unused
16545568 KB  10602264 KB  4254360 KB   1688944 KB


texts: How to generate an error when backing up to an existing file

texts:
I'm using SQL Server's BACKUP command and I want the operation to generate an error if I attempt to write to an existing backup file.

By default, SQL Server seems to append to an existing file.  There are various media options like INIT,SKIP,FORMAT - but no combination of those seem to do what I'm looking for, unless I'm reading it wrong:
https://msdn.microsoft.com/en-us/library/ms186865.aspx


texts: Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?


texts: How do graph databases differ from older navigational (hierarchical/network) databases?

texts:
I'm trying to improve my knowledge of graph databases, in particular DGraph. Are graph DBMSs just a new generation of pre-relational hierarchical/network model DBMSs, or is there something new at a low level in the current generation of graph DBMSs (neo4j, etc.)? For example, the 60's-era CODASYL stored records in linked lists of free-form records. Isn't this just what current graph DB engines are doing?

My question isn't about things external to the fundamental way graph DBMSs function, like query language, replication, etc. I get that those areas have improved significantly.


texts: What is the behavior of MIN_ACTIVE_ROWVERSION() on replicas in SQL Server AlwaysOn?

texts:
MIN_ACTIVE_ROWVERSION() returns the lowest rowversion allocated within still-open transactions. This is important when using rowversion columns for reading data where you want to be sure that you wont subsequently miss rows that have been written with rowversions lower than the batch you have just read.

What is the behaviour of this function when called on synchronous and asynchronous replicas in an AlwaysOn Availability Group situation?


texts: How can I optimize a query with multiple left joins?

texts:
The working example that I have been provided with takes about 20 minutes to fully process.

http://paste2.org/G8BZvNHe

What are some basic strategies for making this more efficient?

Edit: I've been looking up some indexing solutions, however I don't believe them to work well in my case. The databases are serving as realtime DB and the data is not accessed often.


texts: I can not get Postgres to listen on anything but localhost

texts:
I have been on this for MANY hours and can not understand why Postgres will not listen on anything other than localhost. I an running on Ubuntu 18.04 clean install + Django, Postgres and nginx. All up and running fine. I am now trying to get external db access working. I can access the user and db from localhost by not the server's external IP. I have checked the listen_addresses and the host entries so many times I can't even remember. Restart Postgres, check. Restart server, check. Yes, there is a firewall running, but I can't even log in using psql and the server's IP. I can if I use localhost...

Anyone think of something I haven't tried yet??

csadmin@BulletinBoardServer:~$ netstat -anpt |  grep LISTEN
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      -
tcp6       0      0 :::80                   :::*                    LISTEN      -
tcp6       0      0 :::22                   :::*                    LISTEN      -
csadmin@BulletinBoardServer:~$ sudo tail -20 /etc/postgresql/10/main/pg_hba.conf
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all     all     0.0.0.0/0       md5
#host    all             all             127.0.0.1/32            md5
#host    cs250phpdata    cs250student    96.***.47.54/0     md5
#host   cs250phpdata    cs250student    new***cs.com/0      md5
# IPv6 local connections:
host    all     all     ::/0            md5
#host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
csadmin@BulletinBoardServer:~$ sudo cat /etc/postgresql/10/main/postgresql.conf |grep listen_
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'          # Added 8/31/18 to allow external access.
csadmin@BulletinBoardServer:~$ sudo ufw status verbose
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip

To                         Action      From
--                         ------      ----
22/tcp (OpenSSH)           ALLOW IN    Anywhere
80,443/tcp (Nginx Full)    ALLOW IN    Anywhere
5432                       ALLOW IN    Anywhere
22/tcp (OpenSSH (v6))      ALLOW IN    Anywhere (v6)
80,443/tcp (Nginx Full (v6)) ALLOW IN    Anywhere (v6)
5432 (v6)                  ALLOW IN    Anywhere (v6)

csadmin@BulletinBoardServer:~$


texts: Joining two tables in one has a subquery which is returning id count present in one table which is not presented on another field of a particular user

texts:
I have a two tables

My first table goes in at this way

CREATE TABLE `inventory` (
  `id` int(64) NOT NULL,
  `user_id` int(50) NOT NULL,
  `placed_date` date NOT NULL,
  `name_of_campaign` varchar(250) NOT NULL,
  `budget` int(64) NOT NULL,
  `start_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `platformType` int(64) NOT NULL,
  `titleOfTheVideo` varchar(250) NOT NULL,
  `videoUrl` varchar(250) NOT NULL,
  `redirection_url` varchar(250) NOT NULL,
  `photo_url` varchar(250) NOT NULL,
  `tags` varchar(250) NOT NULL,
  `language` int(64) NOT NULL,
  `country` int(11) DEFAULT NULL,
  `state` int(11) NOT NULL,
  `city` int(11) NOT NULL,
  `age` int(64) NOT NULL,
  `max_age` int(11) NOT NULL,
  `gender` int(64) NOT NULL,
  `publisher_catagory` int(64) NOT NULL,
  `publisher_name` int(64) NOT NULL,
  `status` int(11) NOT NULL,
  `paid` int(60) NOT NULL,
  `min_cpc` int(60) NOT NULL,
  `max_cpc` int(60) NOT NULL,
  `min_cpv` int(60) NOT NULL,
  `max_cpv` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

For this data

INSERT INTO `inventory` (`id`, `user_id`, `placed_date`, `name_of_campaign`, `budget`, `start_date`, `ending_date`, `platformType`, `titleOfTheVideo`, `videoUrl`, `redirection_url`, `photo_url`, `tags`, `language`, `country`, `state`, `city`, `age`, `max_age`, `gender`, `publisher_catagory`, `publisher_name`, `status`, `paid`, `min_cpc`, `max_cpc`, `min_cpv`, `max_cpv`) VALUES
(49, 113, '2018-05-02', 'Ads', 50000, '2018-05-25', '2018-05-28', 1, 'Some title', 'uploadvideos/arun vm.webm', 'http', 'screenimages/arun vm.png', 'videoads,videos,ads', 2, 96, 1639, 1103581, 25, 30, 2, 1, 109, 1, 1, 25, 30, 5, 10);

And my second table.

CREATE TABLE `ad_analytics` (
  `id` int(90) NOT NULL,
  `ad_id` int(90) NOT NULL,
  `advertiser_id` int(90) NOT NULL,
  `publisher_id` int(90) NOT NULL,
  `visitor_ip` varchar(250) NOT NULL,
  `type_ad` varchar(90) NOT NULL,
  `impression` int(90) NOT NULL,
  `view` int(90) NOT NULL,
  `clicks` int(90) NOT NULL,
  `date_event` date NOT NULL,
  `placed_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `cpc` int(60) NOT NULL,
  `cpv` int(60) NOT NULL,
  `cpi` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Data for the same.

INSERT INTO `ad_analytics` (`id`, `ad_id`, `advertiser_id`, `publisher_id`, `visitor_ip`, `type_ad`, `impression`, `view`, `clicks`, `date_event`, `placed_date`, `ending_date`, `cpc`, `cpv`, `cpi`) VALUES
    (1, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (2, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (3, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (4, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (5, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (6, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
(7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0);

This query

select id,
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
  publisher_id
from
  (select id,
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     publisher_id
  from ad_analytics
  group by ad_id, visitor_ip) t group by t.ad_id;

In at the second table gives me this answer.

Now i want to fetch this answer along with the ad name and budget from my first table i am not getting a proper way to join the tables how can i do the same?


texts: Can I use older MySQL workbench with newest MySQL server?

texts:
MySQL newbie here.

I'm using Ubuntu 16.04 (64-bit).
I've successfully installed MySQL Community Server 5.7 and MySQL Community Workbench 6.3.

Unable to find these answers in the MySQL forums. I've asked this on Stack Exchange and it was suggested that I ask here instead. I've searched on Ubuntu Forum and here also but did not find an answer.
One would think they are common questions but maybe not. Additionally, I cannot find a way to ask a new question on the MySQL Forum.
I've spent a ton of time reading/researching/installing/uninstalling and reinstalling MySQL and community workbench on Linux Ubuntu 16.04 (64-bit) and do not want to lose the momentum I've gained. I'm actually making forward progress after a week or so of trial and error working on this up to a few hours per day.
It appears that I cannot load MySQL Community Workbench 8.0 without Linux server installed. That seems to be one of the requirements if I understood correctly.

Can anyone tell me if MySQL Community Workbench 6.3 will work with MySQL Community Server 8.0 on Ubuntu 16.04 (64-bit)?
I would still like to upgrade and use MySQL community server 8.0 if I can.

Can I use MySQL Community Server 8.0 on Linux Ubuntu 16.04 32-bit?

I've spent many hours googling it and looking on MySQL Forums but couldn't find the answer.
Thanks much. ...Rick


texts: Join 3 tables of a one-to-many relationship

texts:
I have 3 files. 1 shapefile and 2 DBF files. For those of who are not a familiar with shapefiles (GIS data format), they are essentially database tables with rows and columns that have spatial geometry such as point, line, polygon etc., which can be displayed visually and used in calculations in a GIS software.

trucknetwork (shapefile) - 564 rows (column sri links to table1) - specific truck road network in NJ with no road names:

gid integer NOT NULL DEFAULT nextval('trucknetwork_gid_seq'::regclass),
id numeric(10,0),
sri character varying(20),
mp_start double precision,
mp_end double precision,
descriptio character varying(50),
road_type character varying(50),
geom geometry(MultiLineStringM),
CONSTRAINT trucknetwork_pkey PRIMARY KEY (gid)

table1 - 488715 rows (links to shapefile with sri column and table2 with seg_guid column) - start and end points of all NJ roads:

gid integer NOT NULL DEFAULT nextval('table1_gid_seq'::regclass),
objectid integer,
seg_guid character varying(38),
sri character varying(20),
route_type numeric,
sld_mp_st numeric,
sld_mp_end numeric,
CONSTRAINT table1_pkey PRIMARY KEY (gid)

table2 - 669557 rows (links to table1 with seg_guid column) - all NJ road names:

gid integer NOT NULL DEFAULT nextval('table2_gid_seq'::regclass),
objectid integer,
seg_guid character varying(38),
s_name character varying(254),
CONSTRAINT table2_pkey PRIMARY KEY (gid)

I want to join these 3 tables into 1 table - with the result being 564 rows of each road segment with its correct name. It is a one-to-many relationship.

I have posted this question on GIS stackexchange but I did not get a lot of feedback and I want to do this task in SQL not a GIS software.

I have tried left join, inner join - and I have tried the group by but for every query I keep getting over 100,000 + records

select shp.descriptio as descr, shp.road_type as road_type, shp.geom as geom, t2.s_name as street_name
      into networkEX
from trucknetwork as shp
   join table1 as t1 on shp.sri = t1.sri
   join table2 as t2 on t1.seg_guid = t2.seg_guid;

I am not sure what the logic should be. Subquery? Do I have to break this up into smaller steps?


texts:
How to use variable in IN Clause of mysql ? I know i can use find_in_set function but i don't want to use that as it validates all the rows.
Even i don't want to build query, prepare it and execute. Is there any other way i can do ? (In clause items are varchar not int)


texts: is there a way to run the same job multiple times without scheduling it?

texts:
I have to run a process, and it says:

    --first see what is the situation with the job
    EXEC msdb.dbo.sp_help_job @job_name=N'Create new gift vouchers'

    EXEC msdb.dbo.sp_start_job N'Create new gift vouchers' ;
-- RUN THIS JOB FOR AS MANY TIMES AS THERE ARE INSERTS ABOVE --> 2 IN THIS CASE
    GO

this time I need to run the same job (that executes a SSIS package and takes about 8 min on average to run) for 15 times!!!

is there a way to put a script together that will run this job 15 times?


texts: CLOB / VARCHAR(MAX) is causing slow select query

texts:
I have a transaction table where I am storing buy/sell transactions related information, and this information I am getting is from MQ as an XML and I am also storing XML String as VARCHAR(MAX) in SQL Server database.

The issue is that since I am storing XML String into this table, select query is running very slow.

Is there any way I can increase the performance? Any recommendation will be highly appreciated.

The solution which I can think of is to create another table of 1 -1 mapping with my transaction table to store XML String but not really sure if this is the best idea.


texts: GeoJSON MultiLineString import to MySQL with SRID/CRS 27700, 'out of range'

texts:
Version 8.0.20
I'm importing a single link of the OSM road network with the intention of creating a buffer in MySQL.
This is it:
select st_srid(st_geomfromgeojson('{ "type": "Feature", "properties": { "osm_id": "200020986" }, "geometry": { "type": "MultiLineString", "coordinates": [ [ [ 440417.0, 192697.0 ], [ 440386.0, 192596.0 ], [ 440361.0, 192416.0 ], [ 440353.0, 192277.0 ], [ 440331.0, 192121.0 ], [ 440327.0, 192040.0 ], [ 440330.0, 191981.0 ], [ 440341.0, 191949.0 ], [ 440433.0, 191817.0 ], [ 440494.0, 191703.0 ], [ 440506.0, 191688.0 ] ] ] } }'),27700)
into @foo

the easting and northings are within OSGB
the srid is being defined as 27700

and yet I get back

Error Code: 3616. Longitude 440417.000000 is out of range in function st_geomfromgeojson. It must be within (-180.000000, 180.000000].

It looks like its ignoring the 27700 and trying to encode into 4326/WGS84 and clearly failing.
Any ideas please?


texts: Database Inner join For fetching Records

texts:
I have two table in database. Basically these tables contains the question like subjective/objective. I want to fetch the data from these tables

table_1 :-
question_id        question_value  question-type
1                     test1          1
2                     test2          1
3                     test3          2

Table_2 :-
answer_auto_id   question_id  answer_value
1                   1             ans_sub
2                   2             ans_sub
3                   3             ans_obj_1
4                   3             ans_obj_2
5                   3             ans_obj_3
6                   3             ans_obj_4

result table

question_id     ans1_obj     ans2_obj    ans3_obj     ans4_obj  answer_subj
 1                NULL        NULL         NULL        NULL      ans_sub
 2                NULL        NULL         NULL        NULL      ans_sub
 3              ans_obj_1   ans_obj_2    ans_obj_3    ans_obj_4  NULL


texts: Normalizing data with multiple products to a single ID?

texts: How can I normalize this data to remove the redundant tuples in the salesorderline database? I already normalized the first one, but cant seem to figure out the second. Also, are my databases already in third normal form? I believe they are, however, would like extra confirmation from most knowledgeable people.


texts: Database logs growing during maintenance plan

texts:
I have read several threads and sites about SQL logs but I don't feel I have found the answer. Or if I did I did not understand it enough to address my issues. My issue: When I run my SQL maintenance plans my SQL logs grow during the backup and fill the drive completely.

What I am trying to understand is why this is occurring, and how to prevent it. Again the backup is filling the drive\logs and not user transactions.

I am not a DBA, but know something about SQL, but not enough and need some help.

My scheduled Maintenance Plans:

Maintenance Job 1: Daily Job - Tasks are to first remove old backup files, remove old log files that have expired. The remove old History. Now it re-orgs indexes followed by updating statistics. Now it backups up the DB (FULL, w overwrite existing, expires in 2) then backup the transaction log (append existing, expires 1 day).

During this process the DB log fills up. The thought is during any business day we can recover back to the top of the last hour in the event of corruption - From a DB restore perspective We should never need to go back more than an hour. Additionally, with sending the backups to tape we can still mount and recover the DB even after it expires, but it is lost from the chain - which should be fine as we can copy data from tables as\or if needed.

Maintenance Job 2: Hourly TLOG backup: During the day at the top of every hour I run a transaction log backup, so we can recover at the top of every hour.
Maintenance Job 3: End of week  Rebuild Indexes for DBs
Maintenance Job 4: Backup DBs (Expires 7 days; Full; Append Existing) runs 2x per month.


texts: Copy large object field into remote PostgreSQL DB

texts:
We have configured postgres_fdw to move data from a DB to remote DB. It works fine but the large object field's data in source db not getting copied to remote DB.

This is the statement used to copy:

INSERT INTO archive_remote.target_db(id, oid_field)
SELECT a.id, a.oid_field
FROM appl.source_db a
ON CONFLICT DO NOTHING;

Both databases are Postgres 9.6.

What is the right way to fix this?


texts: Column names - (created, modified) vs. (creation, modification)

texts:
I have a database schema where all the tables are required to have a column to hold the timestamp for the initial INSERT and another column that holds the timestamp of the last UPDATE.
Initially this column will have the same value as the one used for the INSERT timestamp.

I can't decide if it's more appropriate to call them created and modified or maybe creation and modification.

Suggestions for different names are also welcome.


texts:
I am performing index maintenance on a database.  I have run SQL script that uses DMVs to provides a list of indexes SQL Server believes should be created.  I am not creating indexes just because the index is listed as missing.  My question, Why would the primary key column be listed as a missing index? The table is arranged in primary key order, correct?  Why would SQL Server beleive a noclustered index should be created on the primary key?

Thanks in advance.


texts: Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)?

texts:
Recently I configured Log shipping between 2 different servers (all SQL Server 2008 R2), from primary to secondary (2 instances). Log shipping configured successfully and databases on secondary servers are Standby/Read only mode. I mean from primary to 2 secondary servers I added for Log shipping.

Today I observed Log backup is happening on primary but it's not restoring into secondary. Is the Full Backup on primary breaking Log shipping, or are there any other reasons for breaking Log shipping?

@ Kris Gruttemeyer, Transactional Log shipping status on secondary server seems like below:

Error Message for Restoring data in View Job History while Running Restore Job on Log shipping server (Secondary): "Could not find a log backup file that could be applied to secondary database" and “The restore operation completed with errors. Secondary ID: '807202d2-18a6-4236-85ad-ae3a51fba2eb'”

There is no answer for previous question as well: What does 'Could not find a log backup file that could be applied' means on log shipping?. Please help me on this?


texts: I have a SQL Server Integration Services 2012 package that copies data from SQL Server 2012 to Postgres 9.3.  I'm using the latest version of the psqlodbc driver (9.3.210) but I only can transfer around 1,500 rows per minute, whereas if I use the driver from PGNP (not free) I can get closer to 70,000 rows per minute.  Is there a way I can speed up the psqlodbc driver, or some other free driver that works better?


texts: Postgres cannot rename table because of AccessExcusiveLock

texts:
I'm trying to rename table. But it had error:

LOG:  sending cancel to blocking autovacuum PID 11911
Detail:  Process 10377 waits for AccessExclusiveLock on database 21819191 relation 24338310
Statement:  ALTER TABLE member_orderinfo2 RENAME TO member_orderinfo

Please tell me how to fix it ?
Thanks you !


texts: Can I read a .bak file without restoring it?

texts:
A few years ago I had used a 3rd party product to read a full backup file and open it in SSMS just as if it was online. The .bak could be queried like any online database. I need to do that again, but can't remember the company that offered the product.  Does anyone know of a tool that will do this?

The database backup is from a huge database that will take hours to restore, so I didn't want to do that unless absolutely necessary.


texts:
At the company I work for, we have encountered a problem where it seems that SQL Server 2008 R2 is not correctly calculating the free space in databases. As such databases on that server are growing rapidly in disk space usage when they should not be.

We have two database servers running SQL Server 2008 R2 10.50.1600.1 (RTM) on Windows Server 2008 R2 called Server A and Server Z. To our knowledge both servers are identical including the SQL Server configuration.

The databases held on each server are very similar though no two are identical.
All the databases are all set to Simple recovery mode.

Each night on the worst affected databases, a data import is run which deletes around 3 GB of data from the database and inserts around 3 GB of data, maybe a little more. Normally, the size of the database only grew if the inserted data was larger than the deleted data e.g. if 3GB was deleted and 3.1GB was inserted the database grew by 0.1GB.

On Server A, this has recently changed so that in the above example the database is growing by 3.1GB each time the data is imported. The old data is definitely still being deleted. It is the MDB file which is growing, not the transaction logs.

Most of this occurs in one table. When calculating the size of the data in that table it does not add up to the size reported by SQL Server when looking at the table Properties Data space value. The table has a Clustered Index, which we have both Rebuilt and Reorganised to no significant effect.

In the course of investigating this issue we took a backup of the database and restored it under a different name to Server A and Server Z using the same bak file. Server A reported 9% free space, Server Z reported 75% free space and we were able to shrink the databases accordingly.

Based on this it appears that there is an issue specifically with SQL Server on Server A as the same database is reporting a different amount of free space on  SQL Server on Server Z. We have now tried this with two of the affected databases with similar results.

We are however unaware of any differences between these servers, and are unaware of any changes made around this time this issue came to our attention.

Is anyone aware of what could be causing this?

The Fill Factor for the Index is 0 for that table on all the databases on both Server A and Server Z.
There are no active traces on the databases.
DBCC CHECKDB is reporting no errors.

We aim to be upgrading to SQL Server 2016 by the end of the year.  The issue we have is that this is causing us to run out of disk space so we cannot wait that long.  Cannot understand why this only just started happening, or why only one server is affected.  Will ask about patching SQL server.


texts: Split a row into two for attendance seet

texts:
I'm redesigning an attendance system used in my company. Originally, an attendance sheet was rendered on a Grid that was getting week-by-week data Pivoted. I've opted for a Scheduler control by DevExpress. A sign-in and a sign-out are tow be represented as two 'appointments' now.

The Scheduler requires a very specific data source for appointments. Here is my attendance table:

The DutyIn_Manual is what the employee reports and the DutyIn is the actual time captured. DutyIn_Manual is to be shown on the Scheduler. Similarly, DutyOut_Manual goes into view.

Now, I need to split one row into two: One for Duty In and one for Duty Out.

Any help greatly appreciated.

Update
The solution by @Mikael Eriksson worked perfectly.

Here's my updated query:

DECLARE @T TABLE
(
    Id INT IDENTITY PRIMARY KEY,
    AttendanceId INT,
    StaffNumber VARCHAR(50),
    DutyDate DATE,
    DutyIn TIME,
    DutyOut TIME,
    DutyInManual TIME,
    DutyOutManual TIME
);

INSERT INTO @T
SELECT [iSAID],
    [cStaffNo],
    [dtDutyDate],
    [dtDutyIN],
    [dtDutyOUT],
    [dtDutyIN_Manual],
    [dtDutyOUT_Manual]
FROM [Attendance].[dbo].[StaffAttendance];

SELECT T.Id,
    T.AttendanceId,
    T.StaffNumber,
    T.DutyDate,
    S.DutyAuto,
    S.DutyManual,
    S.DutyType
FROM @T AS T
    CROSS APPLY
    (
        VALUES
            (T  .DutyIn, T.DutyInManual, 'In'),
            (T  .DutyOut, T.DutyOutManual, 'Out')
    ) AS S (DutyAuto, DutyManual, DutyType);


texts:
For a purpose of reporting I need to present data from table:
table A (column1, column2, date1, date2, date3,...,dateN)

My query need to present all dates in one column separated with # .

(YYYY-MM-DD# YYYY-MM-DD#..)

But problem is that number of date columns is not fixed, because from product to product can be different number of dates.

Any idea ?


texts: How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')

texts:
Consider a simple query as

SELECT * FROM posts
JOIN tags USING(post_id)
JOIN tag_map USING(tag_id)
WHERE tags.tag IN('tag1', 'tag2', 'tag3')

tag_map is a many-to-many relationship between tables posts and tags.

In this query, mysql will find all posts tagged by any of these words, and order them by PRIMARY KEY.

How can we order the results by tag weight:

posts witch tagged with more words in the list (first posts with three tags from the list, then with two words, ...)
posts witch tagged with first words (first posts tagged with tag1 then posts tagged with tag2).


texts: What are the notable difference between versions of SSMS?

texts:
I've been updating all the software at my new workplace, and people are asking me not to break something on accident if it's working. You know how it goes. But I'm also curious myself about what is different between SSMS versions. I just installed 17.5 last week and now there's 17.6 available and not much info out there about what changed over the years.

I've found a page listing changes in some of the 2016 versions and 2017: https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms

Wikipedia doesn't really have a versions history entry for SSMS specifically, just for the SQL server.

Is there a page somewhere listing all the notable changes between SSMS versions over the years? If there isn't I suppose we could compile an answer with that information here.


texts: SQL Server: no search results caused by noise words

texts:
I've moved a database from SQL Server 2000 to SQL Server 2014 via an import/export through SQL Server 2008 to make it compatible to be imported in 2014. So far that has worked fine.

I found out that using words which are usually in a stop list (like articles) disrupts or breaks the retrieval in a FullText search and return no results. Using only the important keywords returns the result as usual. AFAIK in older versions of SQL Server a different system than stop list were used. Although I can not find anything relevant in the FTData folder because it is either empty or commented.

On inspecting the database via SQL Server Management Studio I see that no stop list is configured in the selected database (under Storage). I tried adding a new one based on the system stop list but it made no difference to the behavior.

What can cause this behavior?

UPDATE

The used query:

SELECT ProductID FROM SearchTable WHERE CONTAINS(*, '"word"') AND
CONTAINS(*, '"noiseword"')

The results fail because in the search query the words are connected with AND. Using a noise word apparently returns always false so no item is returned.

I tried to change that behavior but it is not prevented by this:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'transform noise words', 1;
RECONFIGURE;
GO

I will try a rebuild which may help based on this and this article, as suggested by wBob.


texts: MySQL memory usage slowly increasing then crash - what's causing this?

texts:
So I have a Server that's constantly receiving 30k clicks/day. However, in the meantime does certain insert/update/select cronjobs as well.

My server has 4CPUs, 8GM RAM. Using MySQL 5.7.18 + PHP.

I did some changes in my mysqld.cnf, however, it doesn't seem to help. Maybe the buffer pool size is too much?

Here's my configuration:

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

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# *
#InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
#max-connections                = 500
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 6G

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

Here are two images of the memory charts:

Not entirely sure what's causing this. But what I've gathered is that it's keep increasing until it crashes.

Any thoughts on this?

EDIT - After chnaging the buffer pool size to 4G - RAM usage is 85-86% now consistently


texts: Is it possible -- does a perfmon counter exist -- that would show the name of the end-user application that is connecting to SQL Server?


texts: Missing records in cdc capture instance - MSSQL Server

texts:
We have a Source table (MSSQL Server database) and cdc is enabled on it. Most of the time cdc captures all changes that happened in Source table correctly,
but time to time changed records are missing
from cdc shadow table. Did anybody see that strange behavior? Any suggestions how to resolve it is greatly appreciated.


texts:
I am trying to get the outer and prefix for uk postcodes i.e. NW3 6BX. A space divides the outer part of the post code for which extracting it can be easily achieved by doing:

SUBSTRING_INDEX(delivery_postcode, ' ', 1) AS outer_postcode

I would like to get also the prefix which is the the first one or two letters before the numbers. In this case NW. I would like to use the previous expression replacing  the ' '  with any number. How to do this ?

Thanks,


texts:
How to rollback all active transactions on Oracle DB?

I execute query and see 4 transcations in ACTIVE status.

SELECT *
FROM V$TRANSACTION


texts: Filter on join condition and performance

texts:
I have a query of the following type:
SELECT *
FROM CUSTOMER C
JOIN USERTABLE U on C.AccountmanagerID = US.ID

The user table also has external users which never can be the accountmanager. This table has about 30k records of which only about 500 users can be the accountmanager. I can filter them based on the length of their username. Which gives only 1k records back.
SELECT *
FROM CUSTOMER C
JOIN USERTABLE U on C.AccountmanagerID = US.ID
AND len(U.USERNAME) = 3

When i run the query plans the second one is way more efficient, which i can imagine, but I can't find any complete reasoning behind this. Can someone enlighten me and give me some background information. I am also wondering if this is always the case or is this just coincidence.
Link to the queryplan:
https://www.brentozar.com/pastetheplan/?id=HypDy3MnP
Thanks in advance


texts: How to determine Integer or String based SQL Injection?

texts:
Page 23 of this document said that Injection Type determines if you need a ' or not
https://defcon.org/images/defcon-17/dc-17-presentations/defcon-17-joseph_mccray-adv_sql_injection.pdf
Integer Injection:
http://[site]/page.asp?id=1 having 1=1--

Column '[COLUMN NAME]' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
String Injection:
http://[site]/page.asp?id=x' having 1=1--

Column '[COLUMN NAME]' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Determining this is what determines if you need a ' or not.
Let's test this theory on this site.
http://testphp.vulnweb.com/listproducts.php?cat=1

Please take note that this is not a real shop. This is an example PHP application, which is intentionally vulnerable to web attacks. It is intended to help you test Acunetix. It also helps you understand how developer errors and bad configuration may let someone break into your website. You can use it to test other tools and your manual hacking skills as well.
Test 1
http://testphp.vulnweb.com/listproducts.php?cat=1 having 1=1--

No Error, does that mean this is Integer Based SQL Injection?
If I use ' in the parameter, I'll be getting the following error. Does that this is not String Based SQL Injection?
Test 2
http://testphp.vulnweb.com/listproducts.php?cat=1' having 1=1--

Error
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' having 1=1--' at line 1 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/listproducts.php on line 74
Test 3
http://testphp.vulnweb.com/listproducts.php?cat=1' having 1=1--+

Error
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' having 1=1--' at line 1 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/listproducts.php on line 74
Test 4
http://testphp.vulnweb.com/listproducts.php?cat=1' having 1=1-- -

Error
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' having 1=1-- -' at line 1 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/listproducts.php on line 74


texts: What are the advantages of going with AWS aurora DB with Mysql backend over AWS aurora DB with Postgresql backend

texts:
What are the advantages of going with AWS Aurora DB with Mysql backend over AWS Aurora DB with Postgresql backend?
It's great if someone can share your experience or point out a document.


texts: Infix search on large table when majority of rows can be filtered out

texts:
We have a Postgres table, which looks like this:

+-----------+---------+---------+---------+
| CLIENT_ID | VALUE_1 | VALUE_2 | VALUE_3 |
+-----------+---------+---------+---------+
|     11234 | aabcdef |   mnfng |  lflgbm |
+-----------+---------+---------+---------+
|     11234 |   xdfef | nfnvnaz | fngnnva |
+-----------+---------+---------+---------+
|     84590 |  pflghh |   otngp | cignral |
+-----------+---------+---------+---------+
|    ...    |   ...   |   ...   |   ...   |

CLIENT_ID is an indexed column.

We need to run a query which will return all the rows that belong to a specific CLIENT_ID and will have a particular substring in some of the columns VALUE_X.

Naive query for client id 11234 and substring ng would look like:

SELECT * FROM tbl WHERE client_id = '11234' AND (value_1 LIKE '%ng%' OR
value_2 LIKE '%ng%' OR value_3 LIKE '%ng%')

The problem is that our table is large. It can possibly contain 10+ millions of rows. Rows can be first filtered by CLIENT_ID - then the subset of rows matching will only have about 100k+ rows.

Our tests show that when the table is relatively small, the database does a sequential scan of the whole table. It's not very fast, but it's reasonably fast (about 1s). When the table gets larger, the database first filters the rows with a specific CLIENT_ID (which is indexed) and then does sequential scan of the rest. This is super slow (about 30s+).

Is there any way how we can speed this up, only by using Postgres (we don't want to introduce Elasticsearch etc.)?

We are currently using Postgres 10.9 in production, but we can possibly upgrade to the latest version, if there is significant performance gain (our staging tests show there are not).

Thank you.

Note that there are already threads on speeding up infix searches, but this case is more specific - we can possibly filter many of the rows out otherwise.


texts: Database placement on abstract storage structure

texts:
Often times we all hear the same thing.  The recommendations on where to put OLAP/OLTP databases, where to put the tempdb, where to put transaction logs...etc.

But say you are in an environment where the physical definition of the storage is a mystery to you.  In other words, sure you can see logical drives, and you can give the system admin a call and ask for more space, but you never really know what is a different physical drive (say there's a SAN or a NAS behind the scenes).  If you don't know this for fact, how can you follow best practices by putting, say, the heavily hit tempdb database on a different physical drive?

What's the best practice on this one?


texts: How to perform same DML statements on two different servers (Oracle and SQL-Server) simultaneously

texts:
I have two servers, Oracle and SQL-Server.

I have same database on both the servers.

I want to perform DML (insert,update,delete).

How can I perform same DML statements on both the servers simultaneously ?

If I insert one statement in SQL-Server then the same statement should be updated in the Oracle database .


texts: "CREATE FILE encountered operating system error 32" when attempting to attach database

texts:
I am having trouble connecting to a database I created in SSMS. Keep in mind this is my first time working with databases. All I've done in this database is create some tables and add a few records. What I need to do is connect to this database in Visual Studio 2015. However, when I try to add a connection to a Microsoft SQL Server Database File to my windows forms application it says the file is in use and will not let me select it. When I select a different database file I created in SSMS and test the connection it tells me this database file is not compatible with the current instance of SQL Server. To resolve this issue, you must upgrade the database file by creating a new connection or modify the existing connection to this database file. Now in order to upgrade a database file, according to MS documentation, is to attach the database. But when I right click the "Database" node in SSMS and click attach and try to add either of the two database .mdf files from earlier, it gives me this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

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

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

CREATE FILE encountered operating system error 32(The process cannot access the file
because it is being used by another process.) while attempting to open or create the
physical file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Mestek Database.mdf'.
(Microsoft SQL Server, Error: 5123)

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

I ran this query on the database which I guess returns any process using the database.

SELECT * FROM sysfiles

But this just returns the file path and name of the .mdf file and log file.


texts: How can I make microsoft Access update fields that are linked in several tables?

texts:
I am working on a database for clinical observations in cancer mouse models. Usually, the animal care staff reports an observation that the vet checks. Sometimes, we have false positives that I need to track.

Thanks to the great support of this forum, here is my database structure that I made with Microsoft Access:

As you can see, Observations is my primary table. caseID (PK), trueCaseID(PK), and falseCaseID(PK) are all numbers. I was wondering: if I enter a trueCaseID (PK), will a caseID (PK) automatically be generated?

Thank you very much for your help!


texts: Joins between linked servers are dangerous, so can we just load the data into a local table before doing the join?

texts: I was just reading the following article which states that it's a worst practice to do JOINs between two linked servers.   The only alternative I can see is to get the necessary data from both linked servers and load them into temporary tables in an SQL Server instance and then do the join.  Is this advisable?  Or is there some other course of action?


texts: SQL Server - How to grant SELECT permission on View that access data in schemas from differents owners?

texts:
I need help to deal with a problem about permissions in my database.

This is the scenario:

1 Database

4 Schemas:

schemaA; owner dbo
schemaB; owner ownerX
schemaC; owner ownerX
schemaD; owner ownerX

In schemaD I have a view named viewABC, that gets information from tables and views allocated on schemas schemaA, schemaB e schemaC.

A user userX have SELECT permission for viewABC.

With the following command I grant permission:

GRANT SELECT ON schemaD.viewABC TO userX;

When userX try to execute a SELECT against the view, this way:

SELECT * FROM schemaD.viewABC;

We get the error:

  The SELECT permission was denied on object 'tableA', database
  'MyDatabase', schema 'schemaA'.

I do understand that the error happens because tableA is on a different schema, with a different owner (dbo instead ownerX), and SQLServer applies userX permissions to grant or deny access. Once userX don´t have explicit access to tableA the query execution returns error.

How to solve this without grant SELECT permission on tableA for userX?


texts:
In my web application there exists a web form with certain questions on it. Some of these questions will be pulled / retrieved from the database.

Here is some sample data:

My question is, is there much gain in moving the Type column (determines what sort of HTML input element to display on the form) into its own table and have a foreign key to the question type?

You might argue that instead of the words 'Text, Select ... etc' I'll have numbers .. but they'll still repeat! is there any gain in doing this?

Any clarification would be greatly appreciated.

Thank You.


texts: is there some way to do this sql script, in mysql?

texts:
This script was make in postgres, but I don't find their way to make it in mysql
DO
$do$
BEGIN
  IF EXISTS (SELECT FROM pg_database WHERE datname = 'example_work_db') THEN
    CREATE USER example_work_srv WITH ENCRYPTED PASSWORD 'P4ssw0rd.';
    GRANT CONNECT ON DATABASE example_work_db TO example_work_srv;
    GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO example_work_srv;
  END IF;
  IF EXISTS (SELECT FROM pg_database WHERE datname = 'example_test_db') THEN
    CREATE USER example_test_srv WITH ENCRYPTED PASSWORD 'P4ssw0rd.';
    GRANT CONNECT ON DATABASE example_test_db TO example_test_srv;
    GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO example_test_srv;
  END IF;
  IF EXISTS (SELECT FROM pg_database WHERE datname = 'example_live_db') THEN
    CREATE USER example_live_srv WITH ENCRYPTED PASSWORD 'P4ssw0rd.';
    GRANT CONNECT ON DATABASE example_live_db TO example_live_srv;
    GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO example_live_srv;
  END IF;
END
$do$;

Thank you in advance for the help


texts: Alter table add column with no default blocks

texts:
tl;dr

The following query blocks the postgres server when executed

ALTER TABLE "image_data" ADD COLUMN "user_status" varchar(30) NULL

engine version 10.6

# of rows 1.5 mils

Hi,

I have a somewhat odd problem with my production database server (I am unable to reproduce on my local server). As part of the deployment process I wanted to add a new column with a default value of null. When I ran the above query (which was produced by dajngo's migration process), It failed and in the process locked the database and blocked other queries (non-locking selects) from executing. Since it was a production environment, I rolled back after a few minutes (approximately 10). What I find most unusual is that this same query executes within less than a second on my local db replicate (which has the exact same data as the production).

Edit:
When I run this transaction, it blocks the access to the database for all other select queries - which I think (please correct me if I'm wrong) implies that the database is in fact blocked by this transaction.


texts: Is it possible to create a UNIQUE FULLTEXT index? I am using a mysql diff tool that is complaining that I am using duplicate indexes. I have a column that needs to be unique but also used for full text search.


texts: Should I not use camelCase in my column names?

texts:
I am creating a GraphQL API using Node.js, which foces me to return all the field names in camelCase.

In my PostgreSQL database, I currently have all my columns named following a camelCase convention, but I am thinking: is that the best idea?

Should I use snake_case in the database columns and convert them in the back-end?


texts: Is it possible to replicate a collection in MongoDB

texts:
I would like to replicate/synchronize a collection named "CollectionA" from a mongoDB "DataBaseA" to a mongoDB "DatabaseB" in real-time

DatabaseB is a normal database (not shared and not a replica)
The DatabaseB will have writes to it, but not for collection "CollectionA"
The synchronization/replication should be done in real-time with a delay defined based on network latency

I'm trying to achieve this without writing any custom code. Is this possible?


texts: The placement of database files in a shared folder

texts:
Is it a normal practice with MS SQL Server to place databases in remote shared folders on a fast local network rather than on the physical HDD where the server software is installed? What problems shall one fear that local placement avoids? For example, can a network glitch cause .mdf and .ldf to become inconsistent through successful modificaion of the one and failure to modify the other?

EDIT:
Whereas Microsoft recommends employment of Storage-Area Network (SAN) or SCSI instead of simple shared folders, could you please tell me the disadvantages of the latter?


texts: PostgreSQL: How to list the comments attached to a database

texts:
How do I display the comments associated with a database, not a table? I've tried \dd and \list , but can't seem to find this.
Thanks,


texts: backup history using availability groups

texts: When using availability groups it is hard to get backup history in situation of failover to secondary node. It would good to have msdb backup set data to be in-sync with all the servers involved in availability groups. can i create a procedure or script to have the backup history information for all the backups from different msdb databases to a central database ? What is the best way to do this ?