texts: How to Join Two Result sets to query on output came from Two statements

texts:
I Have two queries
Query 1:

SELECT
    e.eid, e.item as 'ITEM', SUM(s.qty) as 'TOTAL SOLD'
from
    tbl_sales s,
    tbl_matentry e
WHERE
    e.eid = s.item
GROUP By e.eid ;

Result 1:

+---------+-------------+---------------+
|     eid |    ITEM     |  TOTAL SOLD   |
+---------+-------------+---------------+
|       1 | rupa        |             5 |
|       2 | pan america |             3 |
|       3 | John Player |            10 |
|       4 | classmate   |            11 |
|       5 | lepakshi    |            55 |
|       6 | lee         |            14 |
|       7 | puma        |             9 |
+---------+-------------+---------------+

Query 2:

SELECT
    e.eid, e.item as 'ITEM', SUM(s.qty) as 'TOTAL STOCK'
from
    tbl_purchases p,
    tbl_matentry e
WHERE
     e.eid = p.item
GROUP By e.eid ;

Result 2:

+---------+-------------+--------------------+
|     eid |    ITEM     |        TOTAL STOCK |
+---------+-------------+--------------------+
|       1 | rupa        |                 41 |
|       2 | pan america |                 45 |
|       3 | John Player |                 32 |
|       4 | classmate   |                 75 |
|       5 | lepakshi    |                 56 |
|       6 | lee         |                 65 |
|       7 | puma        |                 50 |
+---------+-------------+--------------------+

When I combine both sql statements into single statement I am getting incorrect output

So Far i have tried this

SELECT
e.eid,
e.item as 'ITEM',
SUM(p.qty) as 'TOTAL STOCK',
SUM(s.qty) as ' TOATL SOLD',
(SUM(p.qty)-SUM(s.qty)) as 'BALANCE STOCK'
FROM
tbl_matentry e
    LEFT OUTER JOIN
tbl_purchases p ON p.item = e.eid
    LEFT OUTER JOIN
tbl_sales s ON s.item=e.eid
GROUP BY e.eid ORDER BY e.eid

+-----+-------------+-------+------+---------+
| eid |    ITEM     | STOCK | SOLD | BALANCE |
+-----+-------------+-------+------+---------+
|   1 | rupa        |   123 |   15 |     108 |
|   2 | pan america |    90 |    6 |      84 |
|   3 | John Player |    64 |   20 |      44 |
|   4 | classmate   |   150 |   22 |     128 |
|   5 | lepakshi    |   112 |  110 |       2 |
|   6 | lee         |   130 |   28 |     102 |
|   7 | puma        |   100 |   18 |      82 |
+-----+-------------+-------+------+---------+

How to do i get correct result? Where is mistake/error in my query.Please review my Query and help me to get correct data.

My Expected Output is

+---------+-------------+------------------+-----------------+------------------+
|     eid |    ITEM     |      TOTAL STOCK |   TOTAL SOLD    |  BALANCE STOCK   |
+---------+-------------+------------------+-----------------+------------------+
|       1 | rupa        |               41 |               5 |               36 |
|       2 | pan america |               45 |               3 |               42 |
|       3 | John Player |               32 |              10 |               22 |
|       4 | classmate   |               75 |              11 |               64 |
|       5 | lepakshi    |               56 |              55 |                1 |
|       6 | lee         |               65 |              14 |               51 |
|       7 | puma        |               50 |               9 |               41 |
+---------+-------------+------------------+-----------------+------------------+


texts: SQL Server 2008R2 - Stored Procedure with 2 updates causing deadlocks

texts:
I have a web api web service that calls an authentication stored procedure.
This stored procedure calls a different stored procedure that does 1 update:

UPDATE  AccountLicensingDeviceConnectionsPerPeriod
SET     DateLastConnected = GETDATE(),
        ConnectionPermitted = @IsValid
WHERE   AccountId = @AccountId
        AND UserId = @UserId
        AND Device = @Device

And then does a different update:

UPDATE  Users
SET     DateLastLoggedIn = GETDATE(),
        LastLoggedInDevice = @Device,
        InvalidLoginCount = 0
WHERE   Id = @UserId

These updates are always in the same order or at least they should be, is there any way to avoid 2 concurrent processes to deadlock?

The deadlock graph:

Deadlock graph data extracted

GetIsAccountLicenseConnectionValid SP

AuthenticateUser SP - h**ps://dl.dropboxusercontent.com/u/204180/AuthenticateUser.sql


texts: MySQL server installation file download for windows

texts:
I need to install latest MySQL community server 5.6.25 in windows box.

MySQL site is giving complete MySQL installer, but not particular MySQL server installation file.

Where can I get this particular MySQL server installation file.


texts: Mariadb always writes tmp disk to a file

texts:
I'm trying to understand why Mariadb (v10.4.12) is writing most of the temp tables to disk.

Created_tmp_disk_tables 196,8k
Created_tmp_tables 367.8k

There's 188G of RAM in the system with ~50% of it unused. So it's not like that should be a problem.

I enabled performance schema and I see that there are queries with small datasets with GROUP BY statements that have 100% rate on writing tmp tables to disk (events_statements_summary_by_digest.SUM_CREATED_TMP_DISK_TABLES === events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES)

For example - one of those queries have:
* 160 rows when grouped
* 2150 rows without grouping
* each row is about ~20 bytes - so it's quite tiny

I have no TEXT fields in the query only some integer fields that are indexed.

Query does have multiple INNER JOINs in it. But all the ON conditions etc are done on integer (PK) fields - no string used anywhere.
One joined table does have TEXT field in the schema but it's not used in the query itself. I can't see how that could influence this.

How could I get more info on why are these tmp tables written to disk?

There are lots more different queries that are behaving same way and I can't understand why is that so.

I'm not putting any schema / query examples here currenlty as I'm interested understanding how to debug this myself and I really don't want to publish any info about production systems schema etc.


texts: Slave Missing Data From Master, Manual Insertion Issues?

texts:
My transaction logs became corrupted and I had to skip 50 insert records from master. I've exported the data and am planning to update the slaves manually, with the slave stopped. The auto-increment value on the slave stayed in sync with master to my surprise so I think I can just re-populate the missing rows. Are there any potential issues with this plan?

So for example on master I run:

SELECT count(*) FROM `users` WHERE userid between 20 and 69;

and I get back 50. When I run it on slave I get "Empty set". So I did:

SELECT * FROM `users` WHERE userid between 20 and 69;

Then constructed the resulting CSV from PHPMyAdmin into an insert query:

insert ignore into users (userid, name, passwordhash, active)
values
(....,...,...,...)

Prior to insert on the slaves I will run:

stop slave;

and then after

start slave;

I can't think of issues with this may cause but I don't have a testing replication environment so can't test this thoroughly.


texts:
I have a table containing servers logs, and another table containing rules to match certain and drop them.

The logs tables contains around 1 million rows, and there are about 20-30 rules.

The query runs very slowly, I wonder is there any way I can make it run faster. I tried adding indexes to logs.message, but it does not help, I also read that you cannot index a "LIKE" column.

I am a total newbie to database, so please forgive me if I am missing any important concepts. Thanks in advance.

CREATE TABLE `logs` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `criticality` varchar(255) NOT NULL,
  `hostname` varchar(255) NOT NULL,
  `source` varchar(255) NOT NULL,
  `message` varchar(4096) NOT NULL,
  `record_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `record_by` varchar(255) NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `idx_message` (`message`(255))
) ENGINE=InnoDB AUTO_INCREMENT=233523 DEFAULT CHARSET=utf8$$

CREATE TABLE `rules` (
  `rule_id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) NOT NULL,
  `type` enum('MATCH','DROP') NOT NULL DEFAULT 'DROP',
  PRIMARY KEY (`rule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8$$

select hostname, criticality, source, message, record_date
from eventlog.logs l1
where not exists (
    SELECT l.message, r.rule_id
    FROM eventlog.logs l,
         eventlog.rules r
    where l.message like r.content
      and l.log_id = l1.log_id
      and r.type = 'DROP'
) and (criticality = 'High'
        or criticality = 'Medium')
  and record_date > sysdate() - Interval 2 Day
order by l1.message;

UPDATE 1
explain results, it took around 10 seconds to finish the query.

+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                |  rows   | Extra                       |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+
|  1 | PRIMARY            | l1    | ALL    | NULL          | NULL    | NULL    | NULL               | 2101642 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | r     | ALL    | NULL          | NULL    | NULL    | NULL               |      16 | Using where                 |
|  2 | DEPENDENT SUBQUERY | l     | eq_ref | PRIMARY       | PRIMARY | 8       | eventlog.l1.log_id |       1 | Using where                 |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+


texts: Access Denied error in CREATE DATABASE ... FOR ATTACH

texts:
I'm attempting to attach a copy of msdb to my workstation SQL Server 2012 Developer Edition on Windows 8.1 in an attempt to mimic the problem detailed in this question where the OP is attempting to recreate missing jobs out of a foreign msdb.

I performed the following actions:

use master;

backup database msdb to disk='C:\sqlserver\test.bak';
go
restore database msdbtest from disk='c:\sqlserver\test.bak'
    with move 'MSDBData' to 'C:\SQLServer\Data\msdbtest_data.mdf'
    , move 'MSDBLog' TO 'C:\SQLServer\Logs\msdbtest_log.ldf';
go

I then stopped the SQL Server Service, made a copy of C:\SQLServer\Data\msdbtest_data.mdf and C:\SQLServer\Logs\msdbtest_log.mdf, and restarted SQL Server.

I then performed the following:

drop database msdbtest;
go
create database msdbtest on (
    name='msdbtest_data'
    ,filename='c:\sqlserver\data\msdbtest_data.mdf'
) log on (
    name='msdbtest_log'
    , filename='c:\sqlserver\log\msdbtest_log.ldf'
) for attach;

This results in:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "c:\sqlserver\data\msdbtest_data.mdf".
Operating system error 5: "5(Access is denied.)".

SQL Server is configured with the default out-of-the box settings for the service account:

Security effective access for the SQL Server service account on c:\sqlserver\data\msdbtest_data.mdf is:

I am at a total loss; I think the error being returned is misleading me.  Perhaps I misunderstand the purpose of CREATE DATABASE ... FOR ATTACH?

As far as I can tell, SQL Server should certainly have access to the file.  The folder C:\SQLServer\Data is used by all the other user databases currently attached to the instance.

SELECT @@VERSION returns:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )


texts: Join one-to-many field in a single result without concatenation

texts:
I suffer from the same problem as in Concatenate one-to-many field in single result?, but I need a result where the 'many' fields occupy their own columns and aren't concatenated in one column. Any suggestions?

Sample data:

___ID      Mapped_Heading    Source_Value
1          English           pass
1          Math              pass
1          History           fail

___ID      Name              Age
1          Bob               12
2          Harry             12
3          Emily             14

And the End result would be:

___ID      Name      Age  English   Math   History
1          Bob       12   pass      pass   fail

I was hoping I wouldn't have to use PIVOT and PARTITION BY because I'm not familiar with those functions, but I keep bumping into them for this problem so think I might have to?


texts: Is an SQL Server trace 'considered harmful' in production?

texts:
Context: Careful DBAs

We've been bought out a few times and our latest overlords have DBAs who keep carefully guard their production database servers.

Overall this is good.

However, we have run into one point of contention between developers and DBAs: they will not run traces against production database servers.

Our experience as developers: SQL Server trace + RML Utilities rocks

In the past, when application instances ran sluggish, we would run a trace via an .sql script, zip up the output, and process it with Microsoft's RML Utilities.

Note that we do not run the GUI-based profiler tool as that dramatically slows down performance (the database needs to wait on updating the GUI). These traces simply "log activity" to the database server's local filesystem. Our scripts run a trace for a prescribed number of minutes at a time (for example, 10 minutes).

The combination of trace and RML Utilities works great: The report is easy to use. It shows hotspots (see below), the reports give helpful information, they allow drilling down to the actual execution plans, etc. It's much better than anything Oracle offers (or at least a few years ago).

(Hotspot: if a query takes 1/10 second, but executes 400,000 times, it wil appear higher in the report, higher than the once-executed query that takes 30 seconds.)

I've done a bit of performance work over the years. My experience is that capturing 'actual data' from 'real databases under real load' trumps programmers 'taking a guess at' and simulating the problem they imagine. This is why I think that the SQL Server trace is pretty much  'silver bullet' for killing the problem.

My Questions

What are the main, legitimate concerns against running SQL Server traces in production (filling up the disk, slowing down performance, etc.)?
Are these concerns valid? If so, how can we address them?
Taking a trace and profiling the "actual problem" seems like the clearest step toward understanding and solving the situation. What are the alternatives?


texts: Listener on database creation for PostgreSQL

texts:
I'm trying to achieve a set of functions/triggers to define database (catalog) level read-only role for every database created.

The way I thought of doing this is to create a listener (trigger/event trigger ?) on pg_database and when a new database is created It creates a new role, revoke all privileges (doing same for already created roles) on every other database and set the default read privileges for the newly created role.

Question: Is there a better approach to achieve this? If no, is there a way to run a function every time the command CREATE DATABASE run?


texts: Postgres 9.3: How to update row that has max value of a timestamp in addition to other selection criteria?

texts:
I want to allow users to cancel recurring subscriptions. The easiest way to do this given the other infrastructure I've got is to set the recurring field = 0 for the user's latest appointment (datewise). So I need to update the value of field recurring only in the row that matches a specific user's latest appointment.

Here's what I tried:

UPDATE appointments SET recurring = 0 WHERE id = 433 AND time = max(time);

ERROR:  aggregate functions are not allowed in WHERE
LINE 1: ...ments set recurring = 0 where id = 433 and time = MAX(time);

and

 UPDATE appointments SET recurring = 0 WHERE id = 433 AND time = SELECT MAX(time) FROM appointments WHERE id = 433;

Here's the relevant part of the appointments table:

                                           Table "public.appointments"
      Column      |            Type             |                           Modifiers
------------------+-----------------------------+----------------------------------------------------------------
 id               | integer                     |
 time             | timestamp with time zone    |
 recurring        | integer                     |

Where should I go from here? Is it necessary to make this into a two part call?


texts: Uninstall Postgres 10 on macOS – EnterpriseDB installer

texts:
Title says it all… How to completely remove a Postgres cluster from a macOS computer?

I used the installer app provided by EnterpriseDB as a courtesy to the community.

This uninstallation question has been asked multiple times on Stack Overflow. But those questions and answers are years out-of-date.


texts:
I am having a problem with my database it does not want to start :(

the server is running on linux centos 6.2

it was working until I emptied the logs I don't know if this have anything to do with my problem !

the log says the following :

131212 15:52:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131212 15:52:06  InnoDB: Initializing buffer pool, size = 8.0M
131212 15:52:06  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131212 15:52:06  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131212 15:52:06  InnoDB: Started; log sequence number 2 2565027428
131212 15:52:06 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find     file: 'db' (errno: 2)
131212 15:52:06 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Edit

here is my "my.cnf" :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

user=mysql
max_connections=1500
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

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

also this is the output of ls -l /var/lib/mysql/ :

-rwxr-xr-x 1 mysql mysql 1512046592 Dec 12 17:02 ibdata1
-rwxr-xr-x 1 mysql mysql    5242880 Dec 12 17:19 ib_logfile0
-rwxr-xr-x 1 mysql mysql    5242880 Dec 11 09:54 ib_logfile1
drwxr-xr-x 2 mysql mysql       4096 Dec 12 09:02 mysql
drwxr-xr-x 2 mysql mysql      12288 Dec 12 17:15 nagios
drwxr-xr-x 2 mysql mysql      20480 Dec  9 09:05 nagiosql
drwxr-xr-x 2 mysql mysql       4096 Oct 27 10:45 test

any suggestions?


texts: How to check the program/applicationname that triggered function?

texts: There are two applications that used the same function (trigger) in postgresql. I have to ignore notification by applicatonname. How do I identify the current program/application that called the function?


texts: In MySQL Workbench, insert a new column at the "top" of a table?

texts:
I must keep a table's columns as specified in a specification that changes quite often.

In MySQL Workbench, adding a new columns after all the others is easy, but I can't find a way to insert a new column where I want.
It is a problem, because I want to keep the columns ordered just like in the specification.

So I do it the painful way:

Create the new column at the bottom
Repeat Right-click-"Move up" tens of times to crawl my way up to the desired position

I am sure there is a more efficient way to achieve this, with the GUI rather than writing SQL code?
Even a shortcut to avoid the right-click would save me a lot of time.


texts: How to get SQLCMD to output errors and warnings only?

texts:
How can you get SQLCMD, when executing a SQL script file, to just output any errors or warnings it encounters?

I essentially dont want information based messages to be output.


texts: Find which session is holding which temporary table

texts:
We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session is holding which temp table? Ideally a query which would list temp tables used by each session.

Thanks,


texts: Getting error while importing large data through csv

texts:
I am new Postgres. I am getting constraint violation errors while importing a
large CSV file.

I want to disable all of the database constraints temporarily - I do not know how to do this.


texts: dump files maxing C drive with corrupt mdf

texts:
I have some databases with corrupt mdf and my C dive on the SQL server is getting maxed out with error dump files,

I am not asking how to troubleshoot why  these files are being created, I already know what the problem is, why they are being created and what I need to do to correct the underlying issue.

what I would like to know is how to prevent or limit the creation and retention of these dump files so they don't max out my C drive while I am doing repairs

thanks


texts: SQL Query From two Table satisfying a condition

texts:
Table 1 name: Student
Name      Department
Alex         CSE
Bob          EEE
Briyan       ME
John         CSE
James        ETE
Mike         CE

Table 2 name: Program
Department    Semester
   CSE         Summer
   CSE         Winter
   EEE         Summer
   ETE         Summer
   ME          Winter
   CE          Summer
   CE          Winter

What is the SQL query to print the names and the semester where each student's department have only 1 type of semester?

SQL Query Result should be like this:
Name     Semester
Bob       Summer
Briyan    Winter
James     Summer

Here, CSE and CE have both summer and winter semester. So Alex, John and Mike are not included.

I have tried this far:

SELECT Student.Name,
     Program.Semester
FROM Student
JOIN Program
    ON Program.Department = Student.Department
WHERE ....


texts: How do I limit the size of my Postgres log?

texts:
I’m using Postgres 9.5 on Ubuntu 14.04.  I notice I’m generating a lot of Postgres logs in

/var/log/postgresql/postgresql-9.5-main.log

.  I would like to limit the size of this file, and doing some time based rotation (e.g. daily) is not an option.  Is there any way I can tell Postgres to rotate its logs once they reach a certain size?


texts:
I have two tables - comments and votes :

comments(id, text, user_id, page_id)
votes(id, value)

There are 2,000,000 rows in the votes table.

I created the following query:

SELECT SUM(votes.value),
       comments.text,
       comments.comment_id
FROM comments, votes
WHERE comments.comment_id = votes.comment_id AND comments.page_id = $page_id
GROUP BY comment_id;

The query runs for about 12 seconds, is this normal?

SHOW PROCESSLIST says that its copying to a temp table. This seems slow, is it necessary to use a temp table?


texts: Solved Mysql connection lost with HAProxy

texts:
I have master-master replication with mysql. and haproxy configured front of my master-master replication.
When I connect my replication via HaProxy, mysql says that "MYSQL has gone away". But when I connect my replication via mysql1 or mysql2 servers no problem.

is there any haProxy config about mysql connection time out ?
UPDATE
I finally solved.
Problem is HaProxy config file's server and timeout values. You can increase this values according to your system and architecture.


texts: Mariadb json store with unicode \u sequences

texts:
I am using mariadb and I store json data with unicode encoded strings eg \u0395, in a medium text column name remoteData with collation utf8_general_ci.
My problem is where query this column like that
where remoteData like '%ΕΛΕΥΘΕΡΙΟΣ%'
I get an empty set because the value is stored as \uxxxx sequence like this
"patientFirstName":"\u0395\u039b\u0395\u03a5\u0398\u0395\u03a1\u0399\u039f\u03a3"

I have been suggested to use JSON_EXTRACT function like this
WHERE JSON_EXTRACT(`remoteData`, "$.patientFirstName")

but as far as I know this will ignore any index on the column. The table in two weeks has gone to almast 100000 rows and it is growing fast, so I want to be indexed.
What would be the best approach to handle such data?
Should I change from sql starage to no sql like mongo etc?
Thanks in advance.


texts:
We have got a requirement to provide login report from SQL Server for Sysadmin only - This would have log-in date and time when anyone logged into SQL server having sysadmin privilege.

Is there anyway we can get report from SQL server with details of log-ins of only those users who are Sysadmins on the database server.

I guess, in security tab we can enable both Success and Failed Logins however enabling this would add lot of overhead on server, currently we have enabled this only for failed logins. And I don't know if there is anyway to enable success login only for sysadmin and not for others.

Any help would be appreciated.


texts: Counting entries of a unique entry of another field and concatenate the result with the unique field in a new column

texts:
I want to create a query that will search the table and find the following:

If a POL_N entry has ENDO_N entries then the COUNT ENDO_N field will start counting the ENDO_N entries and will get the assigned name as shown below.

For example POL002 has two ENDO_N entries, hence the COUNT ENDO_N will be POL002-1 and POL002-2.

Do you have any suggestions?


texts: MySQL (phpmyadmin) update query from one table to another table

texts:
I have an issue with my SQL query as I have two tables named Student_Summary and Fees_Master.  In Student Summary I have a column named Admission_fees and in fees master we have Fees_Type.  For fees type we have multiple options like admission fees, hostel fees, late fees etc...

I need some help with how to update the student_summary admission fees amount from fees master.

I want update the value from fees master to student summary with matching admission fees column (please refer two tables in below images).


texts: Is a temporary table a good solution for importing temporary data into a database

texts:
i am facing the following scenario. I have a student database in which exam results are stored. Every now and then i get a list with the names of certain students for which i need to find out how well they performed in an exam. After i have finished with my queries i don't need the temporary student data anymore. I am trying to think of an elegant way of how to store this temporary student data in my database.  The only thing i found out  that in my opinion could be a solution to my problem is a temporary table, however i am not sure. My tables have the following structure.

Student
id(PK), matriculation_number, first_name, last_name, course_of_study, email

Exam
id(PK), student_id(FK), grade

Temporary student list
matriculation_number, first_name, last_name

All in all what i am trying to achieve here is import the temporary student list into a table, then compare it with the student table, using the matriculation number, in order to get the id of the student and finally use the student id to access all the  exam results for that particular student.

What do you think is a temporary table a good solution for the above problem? If not how would you go about it?

I am using MySQL as RDBMS.


texts: How to add another table to publisher and subscriber without reinitializing the subscriber?

texts:
Is there chance to add another table in SQL Server 2005 transactional replication without reinitializing subscriber.

Thanx.


texts:
This is getting the best of me!!

I created calendar table (table1) loaded with all days for a period of 5 years.  Among other things the table includes the date, the day type (weekend, holiday etc.)  Important fields here are table1.date and table1.type

I have a second table (table2) that records days worked.  Important fields here are table2.date and table2.employee

I want to identify which dates were not worked by an employee excluding any days that might be a weekend or holiday since those are not required work days.

table1
=======
2/10/20 Weekday
2/11/20 Weekday
2/12/20 Weekday
2/13/20 Weekday
2/14/20 HOLIDAY
2/15/20 Weekend
2/16/20 Weekend

table2
=======
2/10/20   john
2/10/20   jimmy
2/10/20   mary
2/11/20   john
2/11/20   jimmy
2/11/20   mary
2/12/20   john
2/12/20   mary
2/13/20   jimmy
2/13/20   mary
2/14/20   jimmy

Query result should show days that do not exist in table2.

Jimmy 2/12/20
John 2/13/20


texts: Login failure error log when querying Central Management Server group

texts:
I am querying a group under a Central Management Server, which results in the following being logged to the SQL Server Error Log.

Error: 18456, Severity: 14, State: 38. Login failed for user
'domain\account'. Reason: Failed to open the explicitly specified
database. [CLIENT: xx.xx.xx.xx]

This error is only appearing on this one particular SQL Server in this group. Running a Profiler Trace on this server and reproducing the login failure brings up the following User Error Message:

Cannot open database "<multiple>" requested by the login. The login failed.

My account belongs to an AD group which is apart of the sysadmin server level role. The AD group's default database is the master database. The SQL Server's Connection Properties, Connect to database: is set to <default>. Which is no different from any of the other SQL Servers in this group.

Any reason why this would occur?

The error appears in the trace once I click New Query and the window opens. Before any querying actually occurs.
Removed and re-added the server registration, error still appears.
I can query the server from the window successfully. There's no real issue or error experienced other than the fact that this error appears in the error log.
Server collations are not all the same.
The collation of the problematic server is Latin1_General_CI_AS.
Default user options are the same.
All databases are in the online state.


texts: Connect to sql server with windows authentication in different domains

texts:
I get we have two options while connecting to remote SQL Servers or other domains with Windows authentication:

runas /netonly method
adding windows credentials

But doesn't Windows authentication mean that the user who logged in to Windows has authentication to use SQL Server?

So since I am not an authorized login or user in remote servers, I mean I am not a Windows user on their machine, how could I connect to their servers?

So everyone who knows my domain name and instance name can connect to my machine.

What about security? Do we have to add other Windows users or machines to connect to our SQL Server?

I believe we must add other machines to our domain or we must add other domains as trusted to our domain.

Please help; what is the point I missed?


texts: How to restore database using old full backup and current log file

texts:
What is happened? My client has lost the hard disk. He has to restore database.

He has the full backup of database 15 days old.
He has ldf (log) file of this database.
He has NOT mdf file (it was located on that hard disk).

Does it have enough components to restore SQL Server 2008 database, if he used standard options in SQL Management Studio when he did full backup 15 days ago (still database structure has changed)?
Is it possible to restore at least database structure (it's possible to regenerate data from other source) that is was actual on crash moment?

Finally:

1) Is it possible to restore all database (data+structure)

2) Is it possible to restore database structure

Thank you.

ADDED:

LiteSpeed for SQL Server Enterprise (6.1 or 5.x) helped me.

1) The database was restored from full backup (15 days old backup)

2) Then LiteSpeed uses copy of mdf file (see previous step) and saved ldf file (log at crash moment) generates sql script of all changes (DDL/DML).

3) Then this script was applied to restored db in step 1)

Any way there were a lot of issues (bugs) of this application, but it helped to restore at least database structure changes.


texts:
I will to made a database diagram in my SQL Server Management Studio. But I have this warning that said:

  Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Can you help me whit this error? How can I give myself (hein_) the promision to continue?

@JeffersonBElias suggested me to execute this code:

select suser_sname(owner_sid) as usersname from sys.databases where name = 'FreeTimeDBSSA'

What me gives this:

+-----+-----------------------+
|     |       usersname       |
+-----+-----------------------+
|  1  | DESKTOP-3BMCMPC\hein_ |
+-----+-----------------------+

Here you have my Object Explorer:


texts:
This question is about strange behavior in SQL Server 2008 R2 SP3 Standard Edition x64.

I have read many questions about IDENTITY increments in SQL Server and strange gaps between them. But basically people say it is related to transactions or restarts. In my case (using a named instance) gaps are simply gigantic.

Application inserted 130 000 records in table, and "Arithmetic overflow IDENTITY conversion to int" error occurred. We truncate the table, and bulk insert 70 000 records. During bulk insert IDENTITY column maximal value is above 9 000 000!

As it was not normal load but mass insert - there was no rollback, restart nor any other issue during this operation.

So gaps are not in the size of 1000 but tenths of thousands.

As I do not control application code (it is commercial software bought from other company) I am simply afraid what can I do as Administrator of such database? Have I to truncate table once every 6 months? Monitor in real time ID column?

Gaps are not in the size of 1000 but tenths of thousands. Are there any pupils experiencing similar issue?


texts: CPU,Memory and disk I/O utilization for each query in mysql

texts: How can we identify the exact CPU,Memory and disk I/O, when a query is executed. I am using mysql 5.7.20 community version. I am able to get only cpu details for session level by setting SET profiling=1. How can we identify all these details in global level for all the sessions.


texts: MongoDB - changing data directory name changes data

texts:
I am witnessing a very bizzare behaviour of MongoDB.

I launch MongoD on a test system (Windows 10, 64bit) like this:

"C:\Program Files\MongoDB\Server\3.6\bin\mongod.exe" --dbpath "C:\MongoData"

I then shut down the server and replace the MongoData directory with a data set from a production server. I start mongod again, but I get a collection with 4 documents, which is what I used to have before I replaced my test data set with a real one.

That's not right, because I would expect to see loads of documents in a real data set. I then try dozens of things, including pointing MongoD to the data folder in its original location, which gives me all the documents from the real collection. Phew, the data is safe. But what is happening in my test system?

I have noticed that if I rename the folder in my test system to MongoData2 and lanuch mongod pointing at it then I get my proper production data set. but if I rename it back to MongoData and launch mongod pointing at this folder I get 4 documents again.

What is causing this?


texts:
My MongoDB 3.6.2 craches randomly.

It provides .mdmp files in the location where I installed MongoDB. The only problem is I don't know how to read these binary files.

mongod.2017-12-21T08-01-49.mdmp

Does anyone knows how to read these files?


texts: How to monitor mysql percentage disk utilization?

texts:
I am new to mysql world and not sure if there is a way to calculate mysql TableSpace/DiskSpace utilization in percentage. I am aware mysql does not provide upper limit or free space like Oracle DB, but just space consumed by Index tables and Data Tables. I have decided to add these two values and divide with total space available for mountpoint mentioned in "datadir" attribute of mysql. My queries are :

Is this right way of doing this ??
What if databases/tables are created on different mountpoints. Is there a way to monitor available space for mysql instance then ?


texts: Is it possible to create a SourceURI Column in SQL?

texts:
It is possible to create a UUID column in SQL:

create table table_name (
    uuid_column_name uuid,
);

Is it possible to create a SourceURI column in SQL as well? E.g.:

create table table_name (
    source_uri_column_name ?,
);

Note that a question mark has been added as internet research and several attempts were unsuccessful.

Discussion

Perhaps the definition of SourceURI is incorrect in our company? We define  a SourceURI like this:

prefix:UUID:UUID

In which a prefix is a String, e.g. "hello" and a UUID is a UUID, e.g. cd15b4ef-f3c4-49bd-92f4-10a93c041c75.

When I searched for SourceURI I did not find the same definition as for UUID. It seems we have another definition regarding sourceURI in the company or it is not valid at all?


texts:
I have very limited SQL knowledge and need to report on the top most current row of data within a table.  The table holds Effective Date, Information and a Renewal Date.

select DATA_KEY_ID,
TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE", DATA_KEY_REF, DATA_KEY_ID
from TABLENAME
where SCREEN_REF = 'TEST' and FIELD_REF = (select REF from REF_TABLE where SCREEN_REF = 'TEST' and ID = 'EFFDTE')

This brings the correct effective date, however it also brings out all the rows that have been entered.  I need to only bring out the top row.

Sorry if this is a bit random.


texts: SQLite3 - remove duplicates by multiple columns

texts:
I'm looking for the most efficient way to remove duplicates from a table where id's are unique but there are equal rows when you check other columns.

Example:

id   name   age   x
1   peter   25    II
2   peter   25    II

The table has tens of thousands of rows.


texts: Trigger creation for Sybase SQLAnywhere 16

texts:
I've been having the hardest time trying to create a trigger for a Sybase SAP SQLAnywhere 16 database following these docs.
Currently it's -131 syntax erroring on line 15/16 (the update statement), but I don't see any error in the syntax. What am I doing wrong here?
create trigger "DBA"."WKM_autoFillCL143" after insert order 2 on "DBA"."case_checklist"
when ((select top 1 "inserted"."code" from "inserted") in( '143' ) )
  begin
    declare @parentRef integer;
    declare @desc varchar(255);
    declare @desc1 varchar(255);
    declare @checkID integer;
    set @parentRef = (select top 1 "parent_ref" from "inserted");
    if (@parentRef <> '0')
      then
        set @desc = (select "description" from "case_checklist" where "checklist_id" = @parentRef);
        if (@desc is not null)
          then
            set @checkID = (select top 1 "checklist_id" from "inserted");
            update "WKM_RecordChecklistMapping" set "c143" = @checkID where "c142" = @parentRef;
            declare @tabid integer;
            set @tabid = (select top 1 "tab_id" from "WKM_recordChecklistMapping" where "c142" = @parentRef);
            set @tabid = (select top 1 "tab_id" from "user_tab2_data" where "tab_id" = @tabid);
            if (@tabid is not null)
              then
                declare @recProvider varchar(255),@recsRequested varchar(255),@dateFrom "datetime",@dateTo "datetime"
                set @recProvider = (select top 1 "Provider_Name" from "user_tab2_data" where "tab_id" = @tabid);
                set @recsRequested = (select top 1 "Records_Requested" from "user_tab2_data" where "tab_id" = @tabid);
                set @dateFrom = (select top 1 "For_Dates_From" from "user_tab2_data" where "tab_id" = @tabid);
                set @dateTo = (select top 1 "Through" from "user_tab2_data" where "tab_id" = @tabid);
                set @desc1 = 'Receipt '+@recProvider+' Records? '+@recsRequested+', dates '+"coalesce"(convert(varchar(255),@dateFrom,1),'00/00/00')+' to '+"coalesce"(convert(varchar(255),@dateTo,1),'00/00/00');
                set @checkID = (select top 1 "checklist_id" from "inserted");
                update "case_checklist" set "description" = @desc1,"staff_assigned" = 'ZKS',"due_date" = ("today"()+7) where "checklist_id" = @checkID
              end if;
          end if;
      end if;
  end;


texts: what's difference between sql_log_bin and log_bin in MySQL？

texts: can anybody tell me what is the difference between them ?


texts: MySQL query to find time difference based on a condition

texts:
Mysql version 5.7.28

I have a db with data in the below format

Id     ign     sp    time
1       1      25    10:00
2       1      22    10:01
3       1      0     10:02
4       1      0     10:03
5       1      0     10:04
6       1      0     10:05
7       1      0     10:06
8       1      8     10:07
9       1      25    10:08
10      1      22    10:09
11      1      0     10:10
12      1      0     10:11
13      1      0     10:12
14      1      0     10:13
15      1      8     10:14
16      1      10    10:15

I need to design a query that will give me the result based on the condition
Ign =1 and sp =0 and the time difference between time where sp is consecutively 0. In this case, sp=0 between time 10:02 to 10:06 and 10:10 to 10:13.

Desired output:

Sp Start Time  Sp Stop Time
10:02          10:06
10:10          10:13


texts: How can I filter first then show values in BI?

texts:
As my title, I'm in this trouble. When I create a BI publisher report, it's show all of value, I just can filter after that.

Can someone tell me know how can I filter values before show it in BI publisher report?

Like I call a Store Procedure and parse parameters, and then It show values

Ex: EXEC schema.SPStudents(001,Chris,Smalling,18,etc,...);

Now I'm using Oracle 12c.

Thanks!


texts:
How do you query something like

if parent = 0 then alias is network_id else alias is group_id

Is this possible with only queries?


texts: Storing many pandas DataFrames in SQLite with metadata

texts:
I need some help with designing a database. My aim is to persistently store a number of pandas DataFrames in a searchable way, and from what I've read SQLite is perfect for this task.

Each DataFrame contains about a million rows of particle movement data like this:

              z            y            x  frame  particle
0     49.724138    45.642857   813.035714      0         0
3789  14.345679  2820.537500  4245.162500      0         1
3788  10.692308  2819.210526  1646.842105      0         2
3787  34.100000  2817.700000  1375.300000      0         3
3786   8.244898  2819.729167  1047.375000      0         4

Using sqlalchemy I can already store each DataFrame as a table in a new DataBase:

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine("sqlite:////mnt/storage/test.db")

exp1.to_sql("exp1", engine, if_exists="replace")
exp2.to_sql("exp2", engine, if_exists="replace")
exp3.to_sql("exp3", engine, if_exists="replace")

But this is too basic. How can I store each DataFrame/experiment with a couple of metadata fields like Name, Date in such a way that later on it's possible to return all experiments conducted by a certain person, or on a specific date?

I will add more columns over time. Assuming each DataFrame/experiment has a column velocity, how could I retrieve all experiments where the mean temperature value is below or above an arbitrary threshold?


texts: Query execution time increased after changing server

texts:
I have changed the test database server from windows to synology. After this change, there is one query which takes 9-10 sec to get a result on synology. On the Windows server , it took 0.18sec to get data. When I exported data , indexes and triggers were also exported. So no issue with indexes.
I have also compared "Explain SQL" result on both server and It is same no difference.
select SUM(work_hours) as apt_hour, `employee_id` from `job_status` where `job_date` = '2020-10-23' and `status` != '0' and `job_status`.`deleted_at` is null group by `employee_id`

What is causing issue here? How to decrease the execution time ?


texts: Query Performance | Update Statistics | Ola Hallengren SPs

texts:
I recently read or listened to something Tara Kizer said about developers coming to her on timeouts or slow running queries, and the resolution was usually updating stats.  I think she went on to say something about eventually she worked with developers to update their code, but I can't find the article or podcast (it was something to the extent of her talking about early in her career, IIRC).

Anyway, I am experiencing the same issue after recently switching to using Ola Hallengren's scripts for maintenance. I started with a new company and I am working on moving everything over from maintenance plans (weekly index rebuilds) to using Ola Hallengren's stored procs with jobs (still doing weekly index/stats maintenance unless there is a reason to do it more frequently).  The specific code I am using for this is:

EXECUTE dbo.IndexOptimize
 @Databases = 'ALL_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y',
 @LogToTable = 'Y'

Oddly enough, after switching to this I am getting developers approaching me, exactly as Tara mentioned, and the fix for every single case has been updating stats (it has been 4 or 5 situations, and repeating periodically).  Without discussing specifics of each case, any idea why this would surface all of sudden?  Its too coincidental that I never saw any issues before, and it's only happened after switching to Ola's scripts.  I am sure I could dig into each case and find bad code behind all of them, but holistically, what would be different about the two situations (MPs vs. Ola's scripts)?  Maintenance plans are ugly, and I don't want to go back, but I am having a hard time expressing a business value for going to jobs with Ola's SPs, when we're having production issues since switching.

I appreciate any thoughts anyone has on this.


texts: How to use WHERE condition when using ON clause

texts:
I have looked at similar posts and have tried them but dint seem to work. In the below query on a MSSQL 2015 database, I need to filter out inactive users. That is, only return results where 'Active' = '1'. I tried:

ON Users.[Name] = Skills.[Name] where Users.[Active] = 1 ---- Incorrect syntax near the keyword 'LEFT'.

ON Users.[Name] = Skills.[Name] where Users.[Active] = '1' ---- Incorrect syntax near the keyword 'LEFT'.

ON Users.[Name] = Skills.[Name] AND Users.[Active] = '1' ---- Query gets executed but shows all results.

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
    ON Users.[Name] = Skills.[Name]
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
    ON Users.[Name] = WeekTimeslip.[employeename]
LEFT JOIN [master].[dbo].[Project] AS Project
    ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
    ON Users.[Name] = Utilisation.[employeename]
ORDER by Users.[Name]

Can you please let me know what I'm missing?


texts: How to create hs oracle to SQLSERVER with rac scan

texts:
hi i am trying to create an hs for oracle rac for a course but i am unable to configure the hs
How to configure the listener with scan_listener?
hs example:
odbc windows64 bits system dns:
odbcSQLSERVER

NOTE: db conexion using rac-scan configure in firewall "Untangle", disk share with "openfiler".
$GRID_HOME\network\admin\tnsnames.ora
SQLSERVERDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = SQLSERVERDB )
    )
  (HS=OK)
  )

$GRID_HOME\hs\admin\initSQLSERVERDB.ora
HS_FDS_CONNECT_INFO = odbcSQLSERVER
HS_FDS_TRACE_LEVEL = off

restart listener
lsnrctl stop
lsnrctl start

tnsping SQLSERVERDB
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) (CONNECT_DATA = (SERVER = ) (SERVICE_NAME = )))
OK (10 msec)

I don't understand how to configure this part since it is remote_scan.
is configured or not?
$GRID_HOME\network\admin\listener.ora
REMOTE_LISTENER_SCAN1= ...
REMOTE_LISTENER_SCAN2 = ...
REMOTE_LISTENER_SCAN3 = ...

SID_LIST_REMOTE_LISTENER_SCAN=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=SQLSERVERDB)
         (ORACLE_HOME=C:\oracle\app\grid)
         (PROGRAM=DG4ODBC)
      )
  )

create dblink: SQS
query:
select * from dbtest.table1@SQS;
ERROR:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from SQS


texts:
I need to add a condition on the WHERE clause such that when sevnum is not null then relate the tables by premnum and sevnum. However, the code below returns an error.

SELECT *
FROM prem c, tempTable t
WHERE
  CASE WHEN t.sevnum IS NOT NULL
     THEN t.premnum = c.premnum
     AND t.sevnum = c.sevnum
  END
;


texts: How to switch master server and warm standby (with streaming replication) in PostgreSQL?

texts:
I would like to know what is the way to switch the role of 2 servers when using warm standby?

I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with streaming replication.

At a later moment, I would like to switch S_2 become the master server and S_1 the warm standby.

What do I have to do to switch roles ?


texts: Speeding up COUNT(*) - WHERE clause slowing query

texts:
The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on 'messages_id'. The 'hidden' field is a bit field (not null) and 'message_type_id' is an int field (not null) and message_id is an int field (PK).

SELECT COUNT(*)
FROM messages
WHERE message_type_id = 1
AND (hidden = 0 OR message_id = @message_id)

Thanks in advance!


texts: How to represent the array of String in Entity Realtionship Diagram?

texts: I am creating a database using PostgreSQL. I am using the Entity Relationship (ER) diagram. I have a table named LiCLSituation as shown in the Figure below, where I am using Circumstance as array of string. Is this a correct way of representation or is it possible to represent arrays in the ER diagram?


texts: How to secure SQL Server being accessed from the DMZ

texts:
We have an internal stand alone SQL Server 2014 default instance that needs to be accessed from an application server residing in a DMZ.   The application team wants to open the default 1433 port so connections can be made to the SQL Server.

Is there a best practice for securing the SQL instance when it is going to be accessed from an application server within a DMZ?


texts: Using SQL Server Import\Export Wizard with Ms. Access as a source DB

texts:
I need to copy this Ms. Access database that we have to test it in SQL Server 2008 R2, is the Import\Export wizard will let me copy all tables, queries, forms... without affecting the live database?
I mean once I click "Finish" at the end of the wizard, is this just moving everything from origional data source or just copying it?

Any links that I should check on what kind of obstacles that I may face with large databases, much appreciated.


texts: Now my MySQL server running a REPAIR TABLE, table is almost 15gb size. Status - Repair with keycache. How I can restart REPAIR TABLE with normal (repair with sort) mode?


texts:
I am developing a database for an application. I have to create stored procedure to tables, so that programmers can simply call the stored procedure to implement the functions. I have a doubt to implement the following logic,

For example:
Business Type Table

Stored Procedure

As per my knowledge I thought, this procedure will work fine. While I call the procedure name as follows,

Call businesstype('insert', 1, 'Online Marketing', datetime(),'raj', datetime(), 'raj', 1);
Every time needs to call this procedure with all its parameters, but developers need to call the procedure with required fields, for example they need to call only the ID (PK) to perform delete function, but it required some values (NULL) for remaining parameters.
How to perform this action? I am just a starter, I am expecting some best practices to implement this, also I need to know whether the given table design and procedure will meet the industry expectations? is it good to implement in this way?

Thanks in advance.


texts: What is the best way to apply production changes (DML & DDL) in an environment with thousands of databases?

texts:
What is the best way to apply production changes (DML & DDL) in an environment with thousands of databases on SQL Server?
I have seen some products out there, but I'd like to know some expecience of you guys.

Thanks


texts: Get the records matching the combination of column values from a temporary table

texts:
I have a temp table with 4 columns

question_tbl:

TempTable -- combination to lookup

id      | Code1    | Code2       | code3
--------+----------+-------------+---------
111       | AA       | A1          | X
222       | BB       | B1          | Y
333       | CC       | C1          | Z
444       | DD       | D1          | ZZ

Code1 and Code2 are columns in tableA , Code3 is in table3
Table A has Records
DeptID      | Code1    | Code2       |
--------+----------+-------------+---------
A           | AA       | A1          |
B           | BC       | B1          |
C           | CC       | C1          |
D           | DC       | D1          |
E           | DD       | D1          |
Table B has Records
DeptID      | Code1
--------+----------+-------------+---------
A           | X
B           | XX
C           | Z
D           | YY
E           | Z Z

I have to use the temptable to lookup on both table A and B.
If I find the 3 combinations matching using both tables, then I append Id
value from Temp to DeptId in TableB
So my final result should be

DeptID      | Code1
--------+----------+-------------+---------
A111           | X
C333           | Z
E444           | ZZ

The query that I am using currently is as below. It is working fine. But gives
   me a performance issue because number of records in table A and B are more
   than  20000

SELECT B.DeptID + T.ID
FROM TableA                                 A
INNER JOIN #TMP                             T
    ON T.Code3  = A.Code3
INNER JOIN TableB                           B
    ON B.Code1  = T.Code1  AND B.Code2 =  T.Code2

is there any other alternative way to lookup the combinations on the table?
Please suggest. Thanks for the help!


texts:
I write some pivot query in oracle but I have some problem with order data :

   Select * FROM
(
SELECT
    ve_nazwisko PH,
    odbiorca,
    CONCAT(CONCAT(nazwa1,' '),nazwa2) FIRMA,
    segment2017,
    segment2018,
    symbol_kraju,
    nazwa_kraju,
    netto,
    zysk,
    CONCAT(CONCAT(miesiac,' '),ROK) Data
FROM
    v_eksport_arkusz_4_6
    WHERE ROK >= '2017'
)
PIVOT
(
SUM(netto) as netto,
SUM(zysk) as zysk
FOR Data
IN('01 Sty 2017','02 Lut 2017','03 Mar 2017','04 Kwi 2017','05 Maj 2017','06 Cze 2017','07 Lip 2017','08 Sie 2017','09 Wrz 2017','10 Paź 2017','11 Lis 2017','12 Gru 2017',
        '01 Sty 2018','02 Lut 2018','03 Mar 2018','04 Kwi 2018','05 Maj 2018','06 Cze 2018','07 Lip 2018','08 Sie 2018','09 Wrz 2018','10 Paź 2018','11 Lis 2018','12 Gru 2018',
        '01 Sty 2019','02 Lut 2019','03 Mar 2019','04 Kwi 2019','05 Maj 2019','06 Cze 2019','07 Lip 2019','08 Sie 2019','09 Wrz 2019','10 Paź 2019','11 Lis 2019','12 Gru 2019'
))

And displaying data they are not something I expected.

Now displaing this:

January 2017 - Netto,January 2017 Zysk,February 2017 Netto,February 2017 Zysk and something like this to 2019

I want display pivoting data in the following order :
- January 2017 Netto,February 2017 Netto,March 2018 Netto and to last 2017 months next January 2017 Zysk ,February 2017 Zysk,March 2018 Zysk and to last 2017

And this data loop to 2019

Somthing like in pivot table in Excel :


texts:
I have a big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action because the whole table is locked.

Is there a possibility or a workaround to bypass that table lock without getting a corrupt table? It seems that InnoDB is the right way (because of row locking), but InnoDB includes fulltext search only with a mysql version greater than 5.6 and this is not included in the current debian repository, even more the new debian wheezy has not mysql 5.6 included.

What can I do in my case?


texts:
I have to export some data from SQL Server 2012. I'm interested in flat file export (i.e.: .csv) but my database has about 500 tables.

Here is an example for you to better understand the situation:

Database: ETTEDB_list
Table1: list_20140101
Table2: list_20140102
.
..
...
TableN: list_20151706

All these tables have the same structure (number of columns, column names, data types).

Is there any way to automatically export all these tables into .csv files for later joining into one ore more BIG .csv files?


texts: SELECT * Returns 1 Record, But SELECT COUNT(*) Shows 3k+ Records

texts:
I imported a csv file that had more than 3k records into a database table in SQL Server using the Import Wizard.  I do not receive any errors, and the import wizard shows success, with 3217 records imported.  If I run the following statement, I get 3217 records:  SELECT COUNT(*) FROM tbl.  However, if I try SELECT * FROM tbl, only one record is returned.  What's going on here?

Using SQL Server 2014 Management Studio.


texts: Command Line Creation of a new 12g Oracle Database

texts:
I am new to creating an Oracle database using the command line. I am logged in as superuser; now I would like to create a new blank database, "mynewdatabase".

$ . oraenv

ORACLE_SID = [oracle] ? mynewdatabase

Is there a step-by-step example of how I can create a new database instance. Most of the examples I have found on the Internet show examples with a Windows environment and not Linux. Once I know how to create the new database environment, I can use ...

sqlplus / as sysdba

and begin working some database magic. Any assistance would be greatly appreciated!


texts: Dynamic script to alter a column type having an index

texts:
I am writing a script to update the datatype of a column in SQL Server on a list of tables, if the existing column are not yet of the desired type. The column should be converted from a VARCHAR to a NVARCHAR (same size, null, default... only the type changes)
The database is wether in local SQL Server, or in Azure database (so very close to SQL Server)

I have something like 20 tables to modify, in something like 10 database, not always the same tables depending of the database. The problem is always the same, and without the issue I will describe below it would have been very straighforward.

To make it reusable over different databases, I am attempting to make it automatical.

To list the tables that needs to be altered is very simple :

declare @tablesWithVarchar table ( [name] varchar(500) )

insert into @tablesWithVarchar
    select '[' + TABLE_SCHEMA + '].[' + Table_Name + ']'
    from
        (SELECT TABLE_SCHEMA, Table_Name, Column_Name, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
        COLUMN_NAME LIKE 'MyColumn'
        and DATA_TYPE = 'varchar') as t

To generate the related ALTER is easy too :

declare @sql nvarchar(max) = 'SET XACT_ABORT ON;'
select @sql = @sql +
'ALTER TABLE ' + [name] + '
ALTER COLUMN [MyColumn] NVARCHAR(500) NOT NULL;
'
from @tablesWithVarchar

It will output a bunch of "ALTER TABLE" for all the tables listed in first step.
I can run the result manually, or with exec sp_executesql @sql

And it works very well... As long as there is no Index on the column to alter.
If there's an index, the alter fails with :

The index 'IX_xxx' is dependent on column 'MyColumn'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN MyColumn failed because one or more objects access this column.

To find if there's one index on the column and to adapt the ALTER dynamically by doing things (?) on it seems to make the script pretty more complex.

Do you have a suggestion on how to make the ALTER TABLE achievable, despite of the index (by disabling it, or something) ? Always in an automated way, given that I don't want to hardcode the index names.

Thanks for reading


texts: implement geo / spatial / proximity search with php and mysql --- what have you done successfully

texts:
For my website's users, I want to implement a worldwide geo / spatial / proximity search with php and mysql.

I want to do this as simply and minimally as possible.

What is the minimum data I should capture (Country, State, City, Postal Code, etc.)?

There are dozens out-of-the-box and open-source solutions out there, with varying reviews. What have you tried that worked or did not work?

It's quite easy to implement such a thing for the U.S. (e.g., Google has some Zip code related proximity-solutions), but apparently if I want to go global, it's an order of magnitude more difficult. Have you tried (and was it worth-it) to code it from scratch?


texts: How should we handle rows which won't be queried once they are old in PostgreSQL?

texts:
We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.

Each row consists of:

ID
Foreign user ID
Date and time
Other data

The date and time aren't strictly monotonic with ID, however they are close.

When querying this table we are only interested in getting rows for a given foreign user ID, with a date and time in the last two weeks. Rows more than two weeks old will never be queried, however we'd like to keep them for archival purposes.

Given this special use case:

Should we have an index on the date and time column?
Is there any hint we can give that the date and time is (almost) monotonically increasing with ID.
Should we look at trying to remove rows older than two weeks from the table, is that likely to improve performance?


texts: What kind of data type to use for numbering in the Harmonized System?

texts:
The harmonized system uses a numbering that shows the hierarchy between different items. Similar to bookkeeping ledger numbers. Here is an example:

01  LIVE ANIMALS
0103  swine, live
010310  Purebred breeding animals

For each level there are two digits available, and this can go down in my case to up to eight digits in total (so four levels). I would like to catalog the harmonized system codes and their descriptions, but wonder what a good data type is to use in MySQL. These numbers are unique so I would like to make them my Primary Keys as well. Given the hierarchy, I should be able to do some SELECTs that allow me to find out the child records. What I am thinking of is using some sort of INT type, and then doing things like this:

SELECT * FROM accounts WHERE CAST(id AS TEXT) LIKE '01%'

Which should allow me to find the child accounts that fall under the '01' category. The problem here would be to work with the leading zeros. If I use zerofill, then 0103 will become 00000103, so that is not ideal. But if I do not use zerofill then this will be 103. Again not ideal.

Using a text data field instead would solve this, but this feels quite wrong as these are clearly numbers. Alternatively I could make several columns - each for the number group - but this again feels like a weird solution. Any advice is appreciated.


texts: Why is adding a FK constraint slow in MySQL?

texts:
I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand.

CREATE TABLE A (
    `pk` INT(11) NOT NULL,
    `fk` INT(11) NOT NULL,
    PRIMARY KEY `pk_A` (`pk`),
    KEY `idx_fk` (`fk`)
) ENGINE=INNODB;

SET foreign_key_checks=0;

ALTER TABLE `A` ADD FOREIGN KEY `idx_fk` (`fk`) REFERENCES `parent` (`pk`);

As far as I can tell, this should only add the constraint to the table meta-data for future data enforcement and ought to be an O(1) operation - we've turned off actually checking the constraint with "SET foreign_key_checks=0" and it should be using the idx_fk index (specified in the ADD FOREIGN KEY clause - possibly redundant) so it shouldn't have to create anything or iterate through the data in any way.

And yet that operation gets slower the larger the table when I run the ALTER TABLE.

What is it doing and why is that slow?


texts: How secure is an Azure database within Microsoft?

texts:
I am considering moving most of my Databases to Azure SQL server Database, but I am concerned that Microsoft will be able to snoop and look at the data inside the database, which is absolutely not acceptable.

Can Microsoft see any data in an Azure AQL DB?
What is the extent of Microsoft ability to influence a database in Azure?

Thanks for the help.


texts: Unable to connect to specific instance of Oracle RAC

texts:
I did several operations by connecting successfully to all 4 instances but suddenly during one of the jobs, it failed with a TNS error. I checked the connect from SQL and one of the nodes is giving an error.

This is puzzling as I have done more than 20 operations just before this by connecting to specific instances, and on the 21st run this suddenly occurred.

I never made any changes anywhere and this just occurred suddenly! Any ideas?

SQL> connect system/oracle@realDB_1 as sysdba;

ERROR:

ORA-12520: TNS:listener could not find available handler for requested type of server

SQL> connect system/oracle@realDB_2 as sysdba;

Connected.

SQL> connect system/oracle@realDB_3 as sysdba;

Connected.

SQL> connect system/oracle@realDB_4 as sysdba;

Connected.

SQL> connect system/oracle@realDB_1 as sysdba;

ERROR:

ORA-12520: TNS:listener could not find available handler for requested type of
server


texts: Is there anything wrong with creating a very large log file?

texts:
I've been given the task of finding out why our server is slow. First of all I did not set this server up.

Is there anything wrong with setting the initial log file size to 70GB? That's what ours is currently set to, and then 10MB after that.

And just so I understand- when a 70GB log file is created, it actually takes up that much space on the drive, correct? Because I initially thought the log file may be that big because it hadn't been backed up, but I backed it up and set it to truncate the log file. It backed it up, and created a new log file, but it was still 70GB.

From a performance standpoint, is there anything wrong with this?


texts: Different manifestations of the same logic yield very different execution times

texts:
Assume that the widgets_population table records every widget which has rolled off the assembly line, while the widgets_tested table is the subset that have been tested.

For every widget in the population, we want to determine the most recent test.

The two queries below show two different approaches to the solution.

However, the first query runs like lightning, whereas the second query seems to spin forever.  Why?  Is there something obviously inferior about the second query, or is this just one of those optimizer flukes?

We realize that having an inequality in the join predicate is not optimal because it can't take advantage of pre-ordering and merging, but it seems to us that the correlated subquery is just as taxing because it has to run the inner query for every row of the outer query.  So, to our minds, both queries should be equally inefficient.

SELECT   A.serial_ID,
         (SELECT MAX(B.serial_ID) FROM widgets_tested AS B
             WHERE A.serial_ID >= B.serial_ID) AS [ID of most recent test]
FROM     widgets_population AS A
;

SELECT   A.serial_ID,
         MAX(B.serial_ID) AS [ID of most recent test]
FROM     widgets_population AS A
         INNER JOIN
         widgets_tested AS B
         ON A.serial_ID >= B.serial_ID
GROUP BY A.serial_ID
;

Note:  It may not be possible to give more information about the DDL statements of the relevant tables and execution plans because the widgets tables are just sanitized stand-ins for the original tables. We just wanted to ask the community if there is something obviously wacky or inefficient about the logical structure of the second query.


texts: MySQL failover - Master to Master Replication

texts:
My company is trying to implement a MySQL failover mechanism, to achieve higher availability in our webservices tier - we commercialize a SaaS solution. To that end we have some low-end VMs scattered through different geographical locations, each containing a MySQL 5.5 server with several DBs, that for the time being are merely slave-replicating from the production server - the objective up until now was just checking the latency and general resilience of MySQL replication.

The plan however is to add a Master-Master replication environment between two servers in two separate locations, and these two instances would handle all the DB writes. The idea wouldn't necessarily imply concurrency; rather the intention is having a single one of the instances handling the writes, and upon a downtime situation using a DNS Failover service to direct the requests to the secondary server. After the primary comes back online, the b-log generated in the meantime in the secondary would be replicated back, and the DNS Failover restored the requests back to the first one.

I am not an experienced administrator, so I'm asking for your own thoughts and experiences. How wrong is this train of thought? What can obviously go wrong? Are there any much better alternatives? Bash away!

Thanks!


texts: Advice on deleting all rows and reclaiming space of very large table

texts:
I'm not a dba...let's start with that :) I'm an app developer. But our database has an archive table which is 252 million rows and 170GB, and I've been tasked to fix it (actually, there are multiple tables in this category that I need to clean up). But we've decided we do not need to keep any of this data. As this table is an archive table, by which I mean there is a primary transaction table for this data, but the application has a way to keep the transaction table smaller by archiving, or moving, older records to this archive table.

Being an archive table, it has no dependencies per SSMS (no indexes or foreign keys). This db is set to full recovery mode.

We have short maintenance windows, so need a process that is fast, and we have limited disk space, so we need to reclaim the disk space from this table.

We can truncate the table, but then how to reclaim the space? We don't want to do a db shrink, as it shrinks all tables and hence then we'd need to run index rebuilds...at least that is my understanding, which could be incorrect.

I could save a script from SSMS (Script Table as Create To...). Then drop the table, then recreate it... Is this risky? Will it reclaim the space?

Other ideas?


texts:
We have a process that currently uses the Oracle DBMS_SCHEDULER to execute. The process runs every time the backend receives data from an external device. The Oracle scheduler is set up such that a job is created on reception of external data, the job executes immediately, and the job is destroyed automatically when complete.

The issue here is that multiple devices could be sending up data at the same time. Because the process involves obtaining exclusive locks at different points throughout execution, occasionally one job will step on another that is already executing (it gets rejected).

I would like to somehow use the Oracle AQ (advanced queueing) mechansim in order to solve this issue. I envision a queue of jobs that are executed FIFO. Even if external device B sends up data while the process is currently processing data from external device A, the queue prevents processing of data from device B until processing for device A is complete.

There doesn't seem to be a specific section on this type of AQ processing in the Oracle documentation, but it seems like it would be a pretty common scenario. Has anyone else solved this type of issue with Oracle AQ?

More importantly, is Oracle AQ the most efficient way to solve this problem?


texts:
I have a table in Hive which looks like:

col1       col2
b           1
b           2
a           3
b           2
c           4
c           5

How do I, with hiveql, group up col1 elements together, sum them up, sort by the sum, as well as create a cumulative sum (csum) based on the sum?

id       sum_all     csum
a         3           3
b         5           8
c         9           17

I've only managed to come up with the grouping and sum, but am out of ideas regarding the cumulative sum. Correlated sub-queries are not supported in Hive

select col1 as id
      sum(col2) as sum_all
from t
group by col1
order by sum_all

With the following result:

id       sum_all
a         3
b         5
c         9


texts: Full text search results in a large amount of time spent in 'FULLTEXT initialization'

texts:
I'm currently trying to run some queries against a data dump of Stack Overflow's comments. Here's what the schema looks like:

CREATE TABLE `socomments` (
  `Id` int(11) NOT NULL,
  `PostId` int(11) NOT NULL,
  `Score` int(11) DEFAULT NULL,
  `Text` varchar(600) NOT NULL,
  `CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `UserId` int(11) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `idx_socomments_PostId` (`PostId`),
  KEY `CreationDate` (`CreationDate`),
  FULLTEXT KEY `Text` (`Text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I ran this query against the table, and it ran incredibly slow (It does have 29 million rows, but it has a Full-Text index):

SELECT *
FROM socomments
WHERE MATCH (Text) AGAINST ('"fixed the post"' IN BOOLEAN MODE)

So I profiled it, the results of which are:

|| Status                     || Duration ||
|| starting                   || 0.000058 ||
|| checking permissions       || 0.000006 ||
|| Opening tables             || 0.000014 ||
|| init                       || 0.000019 ||
|| System lock                || 0.000006 ||
|| optimizing                 || 0.000007 ||
|| statistics                 || 0.000013 ||
|| preparing                  || 0.000005 ||
|| FULLTEXT initialization    || 207.1112 ||
|| executing                  || 0.000009 ||
|| Sending data               || 0.000856 ||
|| end                        || 0.000004 ||
|| query end                  || 0.000004 ||
|| closing tables             || 0.000006 ||
|| freeing items              || 0.000059 ||
|| logging slow query         || 0.000037 ||
|| cleaning up                || 0.000046 ||

As you can see, it spends a long time in FULLTEXT initialization. Is this normal? If not, how would I fix it?


texts:
Is there any way to make SQL Server not full-text index numbers and currency values?

According to sys.dm_fts_index_keywords the index has a very large number of unique values such as $            123,456 and other numerical data all stored within a single NVARCHAR column. I am not interested in these numbers and would like the indexer to ignore them entirely.

Is there a way to tell a full text index to ignore numbers (possibly by a language setting)? Or is there any way to run a regular expression or CLR extension over the input text before being passed to the full text indexer? Or can fdhost be made to ignore numbers?


texts: Remote connection issue from windows machine

texts:
I have installed database 11g on machine A (Windows). Now I need to access that database from machine B (Windows), where my application is placed. I am able to ping B --> A but telnet is not working. Can someone help me on this. Some help will be really appreciated. Below are few files for reference.

Below are few things which I have already tried:

Disabled firewall.
Opened ports using inbound and outbound rules.
Started the services.

listener.ora

# listener.ora Network Configuration File: C:\app\rajhegde\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\rajhegde\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\rajhegde\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rajhegde-lap2)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = C:\app\rajhegde

tnsnames.ora

# tnsnames.ora Network Configuration File: C:\app\rajhegde\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rajhegde-lap2)(PORT = 1522))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rajhegde-lap2)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Output of lsnrctl status

C:\Users\rajhegde>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 09-SEP-2015 18:56
:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                09-SEP-2015 17:40:03
Uptime                    0 days 1 hr. 16 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\rajhegde\product\11.2.0\dbhome_1\network\admin\
listener.ora
Listener Log File         c:\app\rajhegde\diag\tnslsnr\RAJHEGDE-LAP2\listener\al
ert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=RAJHEGDE-LAP2.oradev.oraclecorp.com)
(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


texts: Find and Replace text in all table using mysql query

texts:
I have a database with name test having two tables

sample1(name, quote)
sample2(name, quote)

I need a replace function or procedure that does the following

Find columns with String data type (Text,varchar,char,etc) in all tables within test database
Look for a value in that column and replace it with a new value

I have found an stored procedure in this post but I don't understand how it works.

is there anyway to achieve this or adopt the stored procedure in the linked post for my use?


texts: Foreign Key - One Child Table to Multiple Parent Tables

texts:
Maybe I'm thinking of this problem in the wrong way, but we maintain an AccessControl table that is a child to several parent tables (e.g. CompanyItems, SystemFiles, etc.). I'm trying to make sure we don't create orphaned AccessControl recs, but I'm not sure how to properly set up this relationship. Any tips?

AccessControl
  ItemId
  AccessId (for user access)
  ItemType
  OwnerCd (1 for owner, 0 for read-only access)

CompanyItems
  CompanyId
  ItemId
  ItemType
  ItemName

SystemFiles
  FileId
  FileName
  FileType

Sample Data:
AccessControl: ItemId=1, AccessId=1 (UserId), ItemType=0(for a CompanyItems rec), OwnerCd=1 (owner)
AccessControl: ItemId=1, AccessId=2 (different UserId), ItemType=0, OwnerCd=0 (read-only access)
AccessControl: ItemId=10, AccessId=1, ItemType=1 (for SystemFiles entry), OwnerCd=0
CompanyItems: CompanyId={whatever company}, ItemId=0, ItemType=0, ItemName='Test Item'
SystemFiles: FileId=10, FileId='Test File', FileType='pdf', etc.

If my CompanyItems is deleted, the two corresponding AccessControl recs should also be deleted. If my SystemFiles rec is deleted, then its one corresponding AccessControl rec should be deleted.


texts: MariaDB: Error Number 126 On Table Only When Updating specific field

texts:
I have this problem on a database which seems to occur only when we try to update a specific field in a specific table.

This will trigger the error:

UPDATE post SET
     title = 'New Title here',
     iconid = 14
WHERE postid = 19486114;

MySQL Error   : Incorrect key file for table './johnforum/post.MYI'; try
to repair it
Error Number  : 126

While this will go through just fine:

UPDATE post SET
     iconid = 14
WHERE postid = 19486114;

In other words, only when we update the title field (a varchar) we get this error. We are running Server version: 5.5.66-MariaDB MariaDB Server running on 3.10.0-1062.9.1.el7.x86_64 #1 SMP Fri Dec 6 15:49:49 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

There is an enormous amount of disk space available, around 500GB. The table itself is around 20GB.

Thank you!


texts: migration sql 2003 to 2008, Sql side and php side

texts:
I have a system in php using MSSQL 2003. I need to change the database to MMSQL 2008.

I want to know if there's issue I should look at on

SQL Side (Query/SP/....)
PHP Side (Query, connection type, ...)

Answer will be usefull even if it is a "dont worry, using standard connection will work" or "there's not so much depricated function between 2003 and 2008".
If you goth link to other posts answering some of those interrogation or how to see it by myself.


texts: Linked Server ERROR while trying to insert some rows into remote table

texts:
I'm trying to insert some rows from local table, to a remote table.

begin distributed tran
begin try
    insert into [ali\servername].[dbname].[dbo].[mytable] select col1, col2 from mytable;
    commit
end try
begin catch
    print 'fail'
    rollback tran
end catch

But I get this error :

  OLE DB provider "SQLNCLI11" for linked server "ali\servername" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

I found this article but there is no way to fix problem in SSMS. Anyone can help?

Thanks in advance.


texts: How do I turn off MariaDB GTID to revert back to binlog replication?

texts: We have found concerns with GTID on MariaDB related to our multi-tenant MyISAM databases. It sounds like there are problems with temporary table replication so we'd like to revert away from GTID. How do I do this?


texts:
I am using SQL server. I have a view (VIEW1) in DB1.

Now, I have created another (VIEW2) in DB2:

Create VIEW2 as Select * from DB1.VIEW1 where id not in (select ID from DB2.Table2)

I want a user to be able to select from DB2.VIEW2, but not from DB1.VIEW1 directly.

In other words, this would work:

SELECT * FROM DB2.VIEW2

and this would not:

SELECT * FROM DB1.VIEW1

Is it possible? If yes how?


texts: Can I alter data within a mariadb db using mysql-workbench?

texts:
I am on Ubuntu, remote connecting to a point of sale. I want to update costs of ingredients on our menus.
Can I do this in mysql-workbench?


texts:
Here's the query:

set @_YEAR = 2014;
set @_MONTH = 1;
set @_PRODUCTS = '53';

set @_START = date(@_YEAR * 10000 + 1 * 100 + 1);
set @_END = date_add(date(@_YEAR * 10000 + 1 * 100 + 1), interval 1 year);

explain SELECT TG, TS, `MONTH`

FROM (

    SELECT SG.company, SUM(SG.Gain) as TG, SUM(SG.Spill) as TS, SG.`MONTH`

    FROM (

        (SELECT c.called as customer, c.calling as company, MONTH(c.`end`) as 'MONTH',
                @G := if(@prevComp <> c.calling AND @prevCust = c.called AND @prevDate = DATE_FORMAT(c.`end`,'%Y-%m-%d'), 1, 0) as Gain,
                @S := 0 as Spill,
                @prevCust := c.called as prevCust,
                @prevComp := c.calling as prevComp,
                @prevDate := DATE_FORMAT(c.`end`,'%Y-%m-%d') as prevDate
        FROM cdrdata_archive c CROSS JOIN
             (SELECT @prevComp := 0, @prevCust := 0, @prevDate := 0) prevVals
        where c.`end` >= @_START and c.`end` < @_END
        AND c.called != 'Anonymous'
        AND CAST(c.`duration` AS UNSIGNED) > 30
        ORDER BY c.called, c.`end` limit 5000000)

    UNION ALL

        (SELECT c.called as customer, c.calling as company, MONTH(c.`end`) as 'MONTH',
                @G := 0 as Gain,
                @S := if(@nextComp <> c.calling AND @nextCust = c.called AND @nextDate = DATE_FORMAT(c.`end`,'%Y-%m-%d'), 1, 0) as Spill,
                @nextCust := c.called as nextCust,
                @nextComp := c.calling as nextComp,
                @nextDate := DATE_FORMAT(c.`end`,'%Y-%m-%d') as nextDate
        FROM cdrdata_archive c CROSS JOIN
             (SELECT @nextComp := 0, @nextCust := 0, @nextDate := 0) nextVals
        where c.`end` >= @_START and c.`end` < @_END
        AND c.called != 'Anonymous'
        AND CAST(c.`duration` AS UNSIGNED) > 30
        ORDER BY c.called, c.`end` desc limit 5000000)
    ) SG

     GROUP BY SG.company, SG.`MONTH`

) SGgrouped

JOIN products p ON p.Number = SGgrouped.company
Where p.id IN (@_PRODUCTS)
ORDER BY SGgrouped.`MONTH`

Here's the input:

CREATE TABLE `cdrdata_archive` (
  `CustomerID` varchar(10) DEFAULT NULL,
  `callid` varchar(20) NOT NULL,
  `called` varchar(14) DEFAULT NULL,
  `description` varchar(20) DEFAULT NULL,
  `calling` varchar(16) DEFAULT NULL,
  `dtn` varchar(14) DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `answered` datetime DEFAULT NULL,
  `duration` varchar(5) DEFAULT NULL,
  `talktime` varchar(5) DEFAULT NULL,
  `tta` int(3) DEFAULT NULL,
  PRIMARY KEY (`callid`),
  UNIQUE KEY `callid_UNIQUE` (`callid`),
  KEY `called` (`called`,`start`,`end`),
  KEY `abandoned_from` (`calling`,`talktime`,`end`),
  KEY `called end` (`calling`,`end`),
  KEY `start` (`start`),
  KEY `calling` (`calling`),
  KEY `endcalling` (`end`,`calling`),
  KEY `productdur` (`calling`,`end`,`duration`),
  KEY `endcallingdur` (`end`,`calling`,`duration`),
  KEY `company` (`CustomerID`,`end`),
  KEY `calling end` (`end`,`called`,`calling`),
  KEY `end` (`end`) USING BTREE,
  KEY `call_id_desc` (`callid`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Here's the EXPLAIN statement:

Here's the output from SHOW VARIABLES LIKE '%buffer%';

Q: How can I optimize this query?


texts: Multi-Table Groupwise Max Problem where I need a second field along with the max

texts:
I have a query I'm having trouble with that involves 3 tables described below. There are more fields that are immaterial to this query.

users:
  id
  display_name
matches:
  id
  league_seasons_id
  date_played
  winner_id
  loser_id
players:
  id
  match_id
  player_id
  nth_match
  division
  result   # values are "Win" or "Loss"
  end_hcp

I'm trying to produce output like this:

  Name        Hcp   Wins   Losses   High Run    HR Pct of Hcp
  Alan        705    12       2       27            3.83%
  Bill        815    10       4       30            3.68%
  Charlie     611     9       5       12            1.96%
  Denise      788     8       6       15            1.90%
  ...

The query below mostly works, except for the Hcp column. In the players table there are 2 records for each match, one with the winner's info and one with the loser's. In each record is the start_hcp and the end_hcp, i.e. the handicap that player had before playing the match and the new one based on the match.

I need the end_hcp from the very last match that each player played up until now (the date/time of the query). I'm guessing there's going to be a subquery involved but I haven't figured out what it is.

SELECT
    p.division AS Division,
    u.display_name AS Name,
    p.end_hcp AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "Loss", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses


texts: Why SQL Server Service Account connects to the DAC?

texts:
We started getting Alerts:

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

With a help of SQL job and holding table, we managed to find out that it's the SQL Server Service Account that tries to connect, approx every 4 hours e.g. 12:00 16:00 and so on, with a program name: Net SqlClient Data Provider.
I've added the host_process_id column as per jadarnel27 suggestions, identified the PID, but there were no processes with the same PID in the task manager.
Oddly enough, there are no entries in the log: "Dedicated admin connection support was established", just the following ones:

16:00:05 Login succeeded for user 'sql service user'. Connection made using Windows authentication. [CLIENT: ip address of Windows Failover Cluster Virtual Adapter]
16:00:05 Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]
16:00:11 Dedicated administrator connection has been disconnected. This is an informational message only. No user action is required.

No clues in Windows Event Viewer logs or Windows Cluster logs.
This is only happening on an active node of AlwaysOn Availability  Group.
No jobs/port scanning configured to run at those times, nor any other tasks.
I have seen  How do we find who used Dedicated Admin Connection but we know what account has used DAC, just don't understand why.
How can we troubleshoot the issue further?
EDIT: I've found the cause of the problem, it was a powershell script that synchronises server objects for Availability Groups, it was set to run on port 1433 and I have no idea why it tried to connect to DAC, however simple reboot has solved the issue! Thanks ever so much to everyone who replied!


texts:
Recently I deploy my parser on server and sometime server start reboot from out of memory, i spectate for memory and find something, exactly after every using parser, (one time on day) ram memory decreases, and after few day server has been crashes. Why MySQL after every stop parser create more trash in RAM?
Screen from htop sorted by use memory

Screen from my.cnf

If i reboot the system, amount of process doesn't change, and
systemctl status mysql present that mysql have 28 tasks.


texts: Error "The publication '[Name]' does not exist." running sp_article_validation

texts:
DECLARE @p SYSNAME, @a SYSNAME
SET @p = N'[NAME]'
SET @a = N'[ARTICLE]'

EXEC sp_article_validation @p,@a

I'm using the exact name of the publication provided by the table MSpublications (even using NAME or QUOTENAME(N'Name') generates the same error), yet it's saying it doesn't exist.  Note that it doesn't work with any other publications either.  Yet this is what sp_article_validation (Transact-SQL) says.

SELECT publication
FROM MSpublications
ORDER BY publication


texts: Does anyone know how to add the series line between these columns in SSRS chart? Thanks!


texts:
How can find out which are the latest GA verions of MySQL Server & Workbench  supported under Windows 7?

On the 'MySQL Product Support EOL Announcements' page it states:

  Per the MySQL Support Lifecycle policy regarding ending support for
  OS versions that are obsolete or have reached end of life, we plan to
  discontinue building all MySQL binaries for Microsoft Windows 7 as of
  June 23, 2016

But I am unable to find information on which versions DO support Windows 7.

I tried using the installer:
MySQL Installer-Web seems to be the same for versions 5.6.45, 5.7.27 and 8.17.0 and each of these installers offers me the upgrade of my x64 system to 5.7.27 and my x86 system to 5.7.26.  I would usually take that at face value, and assume the installer was suggesting the latest GA version for my system BUT it also offers me MySQL Workbench 8.0.17 x64, which I know to be incorrect because

it results in an 'You are running an unsupported operating system' message
Supported Platforms: MySQL Workbench  clearly states that current Workbench only supports Windows 10


texts: group by left(col, x) text column - possible to index?

texts:
I'm looking for a possible index for the following:
select left(name,10), count(*)
from tab
group by left(name,10)

This is the table config:
create table tab(
id int(11),
name text,
primary key(id),
key name(name(10)))

The optimizer does not uses the index for the select. Any idea if that possible in MySQL?
In Oracle for example I can create index on functions, which is not supported in MySQL.


texts: Measuring latency between availability group nodes in real-time

texts:
We have a monitor in which we would like to display the replication latency between primary and secondary node in milliseconds in real-time.

We're using this query:

;WITH
    AG_Stats AS
            (
            SELECT AR.replica_server_name,
                   HARS.role_desc,
                   Db_name(DRS.database_id) [DBName],
                   DRS.last_commit_time
            FROM   sys.dm_hadr_database_replica_states DRS
            INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
            INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
                AND AR.replica_id = HARS.replica_id
            ),
    Pri_CommitTime AS
            (
            SELECT  replica_server_name
                    , DBName
                    , last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'PRIMARY'
            ),
    Sec_CommitTime AS
            (
            SELECT  replica_server_name
                    , DBName
                    , last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'SECONDARY'
            )
SELECT p.replica_server_name [primary_replica]
    , p.[DBName] AS [DatabaseName]
    , s.replica_server_name [secondary_replica]
    , s.last_commit_time
    , p.last_commit_time
    , DATEDIFF(ms,s.last_commit_time,p.last_commit_time) AS [Sync_Lag_MS]
FROM Pri_CommitTime p
LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]

Result:

primary_replica DatabaseName secondary_replica last_commit_time        last_commit_time        Sync_Lag_MS
--------------- ------------ ----------------- ----------------------- ----------------------- -----------
XXXXX       NextGenAG    ANGSQLD01         2018-06-08 13:18:08.853 2018-06-08 13:18:08.853           0

However the last_commit_time columns are not updated in real-time. They are updated every 5-10 minutes, which is not the case based on the OLTP workload which performs write operations every few milliseconds.


texts: MS SQL FileTable CreatedBy and LastModifiedBy

texts:
When using a SQL FileTable table, I would like to store additional metadata for the file such as CreatedBy and LastModifiedBy. By looking at SQL File Table Schema, this information is not available by default.

I am wondering if it's possible to determine such information by for example adding a trigger to the table and use current_user to determine current user and store it in a separate table?

I exploring the feasibility of SQL FileTable for a project and have never used this tech.


texts: good strategy for big tables and archiving in sql server 2012

texts:
I have a table with roughly ~500,000 rows a day getting added into it. The database supports an OLTP system. The make up of it is a bit like this:

create table Stuff
(StuffID int identity not null primary key,
ValueA decimal(18,4) not null,
ValueB decimal(18,4) not null,
ValueC decimal(18,4) not null,
ValueD decimal(18,4) not null,
StuffName varchar(10) not null,
Created datetime not null default(getdate())  )

I have a covering index on it because of the type of queries that run on it.

Generally, I'm only working with the current day's data. Very few of my queries are targetting the primary key. I've been considering moving the clustered index off of the primary key and onto the Created (datetime) column because so much of my querying is based on that, and I usually want the resultsets in chronological order.

I'd like to keep everything in one table because from a functional standpoint I have no reason to move it, but I'm worried that by not having an archive strategy of some type that eventually I'll have "too many" rows in there. In five years I should have one billion records, which maybe isn't a big deal considering the relatively small data size of my table.

Boiled down: for a small data sized table with 500K rows getting added per day, is there any particular strategy I should look towards? Will I encounter a general slowdown because of size, or is there really nothing to worry about?


texts: How to predict new node allocation in a Cassandra cluster?

texts:
I have a 5-node Cassandra cluster. Each node has disk space of 5TB. The real usage for each node is around 2.5TB depending on allocations and compactions (some are around 2TB some around 2.8).

I would like to add new nodes to the cluster with lower disk space (2TB for example).

How can I be sure that the new nodes will have enough disk space when Cassandra will redistribute data? How can I be sure that my new nodes are not too small?

I'm afraid that 2TB won't be enough, but what about 2.5TB?


texts: How do I use currval() in PostgreSQL to get the last inserted id?

texts:
I have a table:

CREATE TABLE names (id serial, name varchar(20))

I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don't understand how to use it.

I have tried with:

SELECT CURRVAL() AS id FROM names_id_seq
SELECT CURRVAL('names_id_seq')
SELECT CURRVAL('names_id_seq'::regclass)

but none of them work. How can I use currval() to get the last inserted id?