I have five columns in a mysql table
-id
-field_1
-field_2
-field_3
-field_4
-field_5
Field_5 is the last column of the table i have named transactions. If i have a select statement like
select * from transactions where id = 4,how would i know the name of the column nearest the column field_5 that is null.
What I am looking to do here is two things:
I want to make every criteria in a query optional, and I want to sort the results of the query in descending order according to how many of the criteria matched? How can I do this?
For example, say my DB looks like this:
{ "_id" : ObjectId("547c127627cafb8f9838b06f"), "name" : "Things", "attributes" : { "a1" : "Foo", "a2" : "Foo", "a3" : "Foo" } }
{ "_id" : ObjectId("547c128227cafb8f9838b070"), "name" : "Stuff", "attributes" : { "a1" : "Foo", "a2" : "Foo", "a3" : "Bar" } }
{ "_id" : ObjectId("547c129427cafb8f9838b071"), "name" : "Blah", "attributes" : { "a1" : "Foo", "a2" : "Baz", "a3" : "Bar" } }
{ "_id" : ObjectId("547c12f627cafb8f9838b072"), "name" : "Other", "attributes" : { "a1" : "Quux", "a2" : "Baz", "a3" : "Bar" } }
If my query was something like {"attributes.a1": "Foo", "attributes.a2": "Foo", "attributes.a3": "Foo"} then I would want it to return, in order, the items with names Things, Stuff, and then Blah. How would I accomplish this?
After selecting the "log to table" option and running the job, when I come back to this screen and select View, I can see the output of the powershell script in a text file.
The only job step is a call to a powershell script that takes a string as input and prints it after performing a toUpper
However, where are these logs being stored?
I wonder what are the optimal Linux kernel parameters for oracle 11g db on 64-bit system. I didn't find any detailed, good or full recommendations or best practices for this issue. Just found one recommendation that on 64-bit system it's good to set shmax half of the RAM.
I have 3 tables. works,software,agent
it shows like that:
agent:
id name
1 agent1
2 agent2
3 agent3
4 agent4
works
id name date(TIMESTAMP type) agent_id
1 w1 2014/1/1 1
2 w2 2014/2/2 1
3 w3 2014/1/3 2
4 w4 2014/1/4 2
5 w5 2014/1/5 3
6 w6 2014/1/6 4
7 w7 2014/1/7 4
8 w8 2014/1/8 4
9 w9 2014/3/9 4
software
id name date(TIMESTAMP type) agent_id
1 s1 2014/1/1 1
2 s2 2014/2/2 1
3 s3 2014/1/3 2
4 s4 2014/1/4 2
5 s5 2014/1/5 3
6 s6 2014/1/6 4
7 s7 2014/1/7 4
8 s8 2014/1/8 4
9 s9 2014/3/9 4
and I want to get the result like that: get the every month of current year sum of works + software connect by agent_id
Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14
agent1 2 2 0 0 0 0 0 0 0 0 0 0
agent2 4 0 0 0 0 0 0 0 0 0 0 0
agent3 2 0 0 0 0 0 0 0 0 0 0 0
agent4 6 0 2 0 0 0 0 0 0 0 0 0
How to write the sql in MySql?
In a database design there are many fields with information that needs to be normalized. For instance for an address record countries can be normalized but also the address type (i.e. private address, billing address, business address). This leads to tables with information that, once implemented, only changes on a very occasional basis. What is the generic name to refer to these kind of tables?
I am about to start a project for SSRs report base on a USP. The procedure will have to use two cursors to compare orders v stock position (similar to a mrp run ): I am thinking of storing the data into a table that can be used In the ssrs report.
So the question is for a result set of no more than a few hundred lines what sort of table should I use. One in the production dB. A temp table in tempdb or can you use cte ?
The table will be dropped each run of the USP/ report
What comments are there
Thanks
My team and I have created a database project on wine for a class assignment. When we try to create a form from three tables nothing shows up in the Wine Search Form. It's just blank. We thought maybe it might be due to the three relationships of the three tables we tried to combine in the form, but we are not sure how to go about fixing it. I have tried switching the form to continuous form, but it did not work. Any help would be greatly appreciated as we are all database novices.
CREATE TABLE [dbo].[session](
[id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [int] NOT NULL,
[session_id] [uniqueidentifier] NOT NULL,
[supervisor_id] [int] NULL,
some columns ..
CONSTRAINT [PK_session] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tbl2](
[id] [int] IDENTITY(1,1) NOT NULL,
[close_date] [datetime] NULL,
[edit_date] [datetime] NULL,
[session_id] [uniqueidentifier] NOT NULL,
some columns ..
CONSTRAINT [PK_tbl2] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Have two tables which I join wtih sesssion_id , on each table is more than 20 000 values and join is too slow. Is good idea to create nonclustered index on session_id columns which is uniqueidentifier type ? Or there is another way to do the join faster ?
Edit: For example query is like :
SELECT some_columns...
FROM [dbo].[session] s
INNER JOIN [dbo].[tbl2] t2 on s.[session_id] = t2.[session_id]
WHERE t2.some_column = something
I recently offered to help a friend with some development. She isn't a technical person and it turned out what she needed was more a DBA/SQL developer. She gave me the IP and password to this server.
There is no development server. Only production. So I have to be extra careful with everything I do.
I accidentally altered a stored procedure and know I'm trying to get it back. I had the old employees send me the lastest files. They were in rar format and after I unzipped the DB folder, there was only a file inside(no extension). Looking at the contents led me to believe it is a SQL Server .mdf file. This leads me to my question.
Can I attach this mdf file to a new database I just created? Without it trying to take down the other instance? It seems like I could but since this is production, I have to be EXTRA careful.
The server is Windows Server 2008 R2, with SQL Server Management Studio 2012 installed.
In the process of cleaning up my harddrive, some files have been shuffled between OMFI MediaFile folders. Upon startup, Avid rescans the folders, however this time I am recieving an error message, and all projekts containing media files in the OMFI MediaFile folder which supposedly contains this corrupt 'msmMMOB.mdb' file appear to be offline. (but they still exist in the folder)
upon inspection of the OMFI MediaFile folder - I notice that there is no msmFMID.pmr
i've tried deleting the database files and rescanning, but no luck
I imagine I could move all the media files from the one drive's OMFI MediaFile folder to another drive's folder, and then Avid would see the files after rescanning etc...
any other ideas?
I am working on an application to store financial data and need to model the tables where the data for several financial statements will be stored. I came up with the following two designs but I can't decide on which one to use. Appreciate your input.
In the traditional example of a COMPANY database in database design textbooks, we are told that a DEPARTMENT can have only one manager hence Manager should be a single-valued attribute. However, shouldn't it be multivalued since one department can have different managers at different times? Shouldn't we create a table with managers and start and end dates?
Thanks in advance.
I know this sounds like a basic question but just for my knowledge Using this method (WAITFOR)Can we use to execute a SQL Server job multiple times with different parameters?,Hope someone can provide me an answer to this question with a simple example.
Thanks
I have a quite basic trigger defined as such:
CREATE TRIGGER dbo.foo
ON dbo.bar
FOR INSERT, UPDATE, DELETE
AS
...
I also have a stored procedure which at some point does something like this:
Insert into dbo.bar (a) select a from @bar
When @bar contains some data, my trigger is called as expected, but when it doesn't, the trigger is called even though dbo.bar obviously didn't change.
This is confirmed by both SQL profiler and the contents of sys.dm_exec_trigger_stats.
SQL Profiler reports a very low footprint (3 reads, 0 write), but since I have a lot of triggers, this happens a lot, and I end up with significant total_reads figures.
Any hints on why this is happening? Is there a way to change this behavior?
Question is about Oracle licensing terms.
I would like to learn more about Oracle Database administration. Unfortunatelly I am using 32 bit verison of LInux, so I can't install Oracle 11 XE (I can't find 32-bit edition though).
Is it OK, if I download and install Oracle Standard or Entrprise on my personal notebook, and use it with no additional costs and hidden license issuses, is it even possible? I won't be using it for any commerce, even open-source project - I will only be using this installation to improve my DBA skills.
I'm trying to add a shard set in mongos I got this error. Please help.
Background: I'm in the process of optimizing a Data Warehouse style query with big dimension tables. The optimizer does not like to pick the typical star schema join plan at the moment. When I force this plan (by using an index hint) I can see that this star join plan is just a little more expensive than the plan I'm using right now. I suspect that is because the dimensions do not fit into RAM on my development machine. The version is SQL Server 2014 but I hope that the question can be generalized to all recent versions.
Question: Is there a way to make the optimizer believe that it has a lot of RAM at its disposal? For testing purposes I would like to obtain the plan that it would generate if it had 1TB of RAM available (instead of 8GB at the moment).
I worked Merge Replication in SQL server 2008 R2 , in replication monitor i can't see Conflict table, i must to see conflict tables in "MSmerge_conflicts_info" , please help me, How to see Conflict tables in replication monitor? ,Thanks 
University student here. I've been assigned to write a paper on "Management methods of very large databases". What literature would be good to use as a resource for the topic?
I AM DESIGNING A PAGE FOR MY WEB SITE UNDER CONSTRUCTION.
I HAVE A DATABASE FILE CALLED "PASS.MDB" AT SERVER ROOT URL: \DB\PASS.MDB
MY PASS.DB FILE CONTAINS A TABLE CALLER 'pass' which contains "ID,CALLER,PW,AGE,REMARK" FIELDS IN IT WITH DATA FILLED.
WHEN I TRY TO TEST FOLLOWING SQL STATEMENTS 'SELECT', IT WORKED WELL. AND GOT THE RESULT.
BUT WHEN I TRY TO WORK WITH SQL STATEMENT 'INSER INTO' IT SHOWS ERROR MASSAGE.
AS I AM NOT WELL VERSED WITH DATABASE, PLEASE EXPLAIN WHERE THE MISTAKE HAPPENED AND ALSO THE CORRECT CODE THAT I HAVE TO USE.
THE ASP PAGE WITH SELECT STATEMENT THAT WORKED WELL:
<html>
<body>
<%
dim conn,DSN,rs,sql
Set conn = Server.CreateObject("adodb.connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=" & Server.Mappath("\db\pass.mdb")
conn.Open DSN
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT * FROM pass"
rs.Open sql, conn
%>
<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
rs.MoveNext%>
</tr>
<%loop
rs.close
conn.close
%>
</table>
</body>
</html>
------------------------------------------------------------------
THE asp page with INSERT STATEMENT THAT SHOWED ERROR:
-----------------------------------------------------------------
<html>
<body>
<%
dim conn,DSN,rs,sql
Set conn = Server.CreateObject("adodb.connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=" & Server.Mappath("\db\pass.mdb")
conn.Open DSN
set rs = Server.CreateObject("ADODB.recordset")
sql="INSERT INTO pass(CALLER,PW,REMARK) VALUE('BABY','AsDgF','WHITE')"
rs.Open sql, conn
rs.close
conn.close
%>
</body>
</html>
I am using maria galera cluster include three nodes: two cluster node and one garbd. I notice Threads_created and Innodb_master_thread_active_loops increase very quickly.
My information system:
Centos 6.6
linux kernel 2.6.32-431.el6.x86_64
Mariadb Server version 10.0.14
Using two SSD disk 480GB/disk Raid 1
32G Ram
16G Swap
This is my config of mysql server:
[galera]
bind_address=0.0.0.0
innodb_flush_log_at_trx_commit=1
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=<galera_user>:<galera_passwd>
wsrep_cluster_name=xxx
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_node_address='<ip of node 1>'
wsrep_node_name='node1'
wsrep_cluster_address=gcomm://<ip of node 2>
wsrep_slave_threads=24
wsrep_auto_increment_control=0
wsrep_provider_options = "gcache.size = 32768M;gcache.name = /data/galera/galera.cache;gcache.dir=/data/galera/;gcs.recv_q_hard_limit=32768M;gcs.fc_limit=16;gcs.fc_master_slave=YES;gcs.fc_factor=0.5"
wsrep_sst_receive_address=<ip of node 1>
wsrep_sst_donor=node1,node2
wsrep_notify_cmd=
wsrep_log_conflicts=ON
wsrep_certify_nonPK=ON
wsrep_recover=OFF
wsrep_max_ws_row=128000
wsrep_max_ws_size=1G
[mariadb]
### basic ###
user=mysql
datadir=/var/lib/mysql
log_error=/var/log/mysqld/mysql_error.log
socket=/var/lib/mysql/mysql.sock
port=3306
default_storage_engine=InnoDB
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
sync_binlog=1
skip_name_resolve=1
log_output=FILE
sort_buffer_size=32M
max_allowed_packet=64M
low_priority_updates = 1
### innodb ###
innodb_buffer_pool_size = 26000M
innodb_buffer_pool_instances = 64
innodb_file_per_table = 1
innodb_write_io_threads = 20
innodb_read_io_threads = 8
innodb_io_capacity = 1000
innodb_flush_method=O_DIRECT
innodb_log_buffer_size= 8M
innodb_open_files = 81920
innodb_thread_concurrency=50
### buffer ###
bulk_insert_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 524288
### query cache ###
query_cache_limit = 8M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
### table open cache ###
table_open_cache=8192
### network ###
back_log=75
### thread and connection ###
thread_stack = 256K
thread_cache_size = 64M
max_connections = 4096
### timeout ###
wait_timeout=30
interactive_timeout=30
low_priority_updates = 1
connect_timeout=60
net_read_timeout=60
net_write_timeout=60
### temporary tables ###
tmp_table_size=1024M
max_heap_table_size=1024M
tmpdir=/dev/shm
### slow query ###
slow_query_log=1
long_query_time = 2
slow_query_log_file = /var/log/mysqld/mysql_slow.log
### general log ###
# for debugging
general_log=0
general_log_file=/var/log/mysqld/mysql_general.log
### redo log ###
innodb_log_files_in_group=2
innodb_log_file_size=67108864
### replication ###
expire_logs_days=4
log_bin = /var/log/mysql/mysql-bin
log_bin_index = /var/log/mysql/mysql-bin.log.index
max_binlog_size = 100M
server_id=<server-id>
log_slave_updates=1
##security ###
max_connect_errors=10
###thread pool###
thread_handling=pool-of-threads
thread_pool_max_threads=200
thread_pool_idle_timeout=30
thread_pool_stall_limit=300
[xtrabackup]
datadir = /var/lib/mysql
streamfmt=xbstream
I hope someone help me in situation. I am getting stuck.
This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this.
SELECT SomeId, MyDate,
dbo.udfLastHitRecursive(param1, param2, MyDate) as 'Qualifying'
FROM T
How should it work
I send those three params into a UDF.
The UDF internally use params to fetch related <= 90 days older rows, from a view.
The UDF traverse 'MyDate' and return 1 if it should be included in a total calculation.
If it should not, then it return 0. Named here as "qualifying".
What the udf will do
List the rows in date order. Calculate the days between rows. First row in resultset defaults to Hit = 1. If the difference is up to 90, - then pass to next row until the sum of gaps is 90 days (90th day must pass) When reached, set Hit to 1 and reset gap to 0. It would also work to instead omit the row from result.
|(column by udf, which not work yet)
Date Calc_date MaxDiff | Qualifying
2014-01-01 11:00 2014-01-01 0 | 1
2014-01-03 10:00 2014-01-01 2 | 0
2014-01-04 09:30 2014-01-03 1 | 0
2014-04-01 10:00 2014-01-04 87 | 0
2014-05-01 11:00 2014-04-01 30 | 1
In the table above, MaxDiff column is the gap from date in previous line. The problem with my attempts so far is that I can't ignore second last row in the sample above.
[EDIT]
As per comment I add a tag and also paste the udf I have compiled just now. Though, is just a placeholder and won't give useful result.
;WITH cte (someid, otherkey, mydate, cost) AS
(
SELECT someid, otherkey, mydate, cost
FROM dbo.vGetVisits
WHERE someid = @someid AND VisitCode = 3 AND otherkey = @otherkey
AND CONVERT(Date,mydate) = @VisitDate
UNION ALL
SELECT top 1 e.someid, e.otherkey, e.mydate, e.cost
FROM dbo.vGetVisits AS E
WHERE CONVERT(date, e.mydate)
BETWEEN DateAdd(dd,-90,CONVERT(Date,@VisitDate)) AND CONVERT(Date,@VisitDate)
AND e.someid = @someid AND e.VisitCode = 3 AND e.otherkey = @otherkey
AND CONVERT(Date,e.mydate) = @VisitDate
order by e.mydate
)
I have another query which I define separately which is more close to what I need, but blocked with the fact I can't calculate on windowed columns. I also tried one similiar which give more or less same output just with a LAG() over MyDate, surrounded with a datediff.
SELECT
t.Mydate, t.VisitCode, t.Cost, t.SomeId, t.otherkey, t.MaxDiff, t.DateDiff
FROM
(
SELECT *,
MaxDiff = LAST_VALUE(Diff.Diff) OVER (
ORDER BY Diff.Mydate ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
(
SELECT *,
Diff = ISNULL(DATEDIFF(DAY, LAST_VALUE(r.Mydate) OVER (
ORDER BY r.Mydate ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
r.Mydate),0),
DateDiff = ISNULL(LAST_VALUE(r.Mydate) OVER (
ORDER BY r.Mydate ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
r.Mydate)
FROM dbo.vGetVisits AS r
WHERE r.VisitCode = 3 AND r.SomeId = @SomeID AND r.otherkey = @otherkey
) AS Diff
) AS t
WHERE t.VisitCode = 3 AND t.SomeId = @SomeId AND t.otherkey = @otherkey
AND t.Diff <= 90
ORDER BY
t.Mydate ASC;
I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well.
I am using PostgreSQL 9.3 64Bit on a windows 2012 server. I have tweaked the postgresql.conf using advice on the wiki. So it is as optimised as I can make it.
The table is large (>150million rows) which I need to add an extra column and populate it based on the contents of 3 other columns. The size of this table is 53Gb without indexes.
Having tested lots of approaches I am using a CASE statement. Two of the columns are arrays so I have used GIN indexes and a standard index.
A sample of my CASE statement looks like this, the rules go up to 38
UPDATE osmm.topographicarea
SET fcode = (CASE
--building CASE statements
WHEN (descriptivegroup @> '{Building}' and descriptiveterm @> '{Archway}') then 1
WHEN (descriptivegroup @> '{Building}') then 2
WHEN (descriptiveterm @> '{Step}') then 3
WHEN (descriptivegroup @> '{Glasshouse}') then 4
WHEN (descriptivegroup @> '{Historic Interest}') then 5
WHEN (descriptivegroup @> '{Inland Water}') then 6
ELSE 99
END);
COMMIT;
The process takes over 5hrs but adds a huge 180Gb to the table!!
Adding an integer column to this table surely should not do this?
How can I fix this please?
I need a Char Encoding Trick to Strip Hebrew Accent Marks.
Sample Before
בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ
Sample After
בראשית ברא אלהים את השמים ואת הארץ
I have this SQL:
CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);
INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}');
INSERT INTO test(data) VALUES('{"parent":1,"children":[4,5]}');
INSERT INTO test(data) VALUES('{"parent":1,"children":[]}');
INSERT INTO test(data) VALUES('{"parent":2,"children":[]}');
INSERT INTO test(data) VALUES('{"parent":2,"children":[]}');
that would give:
id | data
----+--------------------------------------
1 | {"parent": null, "children": [2, 3]}
2 | {"parent": 1, "children": [4, 5]}
3 | {"parent": 1, "children": []}
4 | {"parent": 2, "children": []}
5 | {"parent": 2, "children": []}
When doing normal one to many, it would show something like this:
SELECT *
FROM test x1
LEFT JOIN test x2
ON x1.id = (x2.data->>'parent')::INT;
id | data | id | data
----+--------------------------------------+----+-----------------------------------
1 | {"parent": null, "children": [2, 3]} | 2 | {"parent": 1, "children": [4, 5]}
1 | {"parent": null, "children": [2, 3]} | 3 | {"parent": 1, "children": []}
2 | {"parent": 1, "children": [4, 5]} | 4 | {"parent": 2, "children": []}
2 | {"parent": 1, "children": [4, 5]} | 5 | {"parent": 2, "children": []}
5 | {"parent": 2, "children": []} | |
4 | {"parent": 2, "children": []} | |
3 | {"parent": 1, "children": []} | |
how to join based on children (using LEFT JOIN or WHERE IN)? I've tried:
SELECT data->>'children' FROM test;
?column?
----------
[2, 3]
[4, 5]
[]
[]
[]
SELECT json_array_elements((data->>'children')::TEXT) FROM t...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT json_array_elements((data->>'children')::JSONB) FROM ...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT json_to_record((data->>'children')::JSON) FROM test;
ERROR: function returning record called in context that cannot accept type record
HINT: Try calling the function in the FROM clause using a column definition list.
SELECT * FROM json_to_record((test.data->>'children')::JSON);
ERROR: missing FROM-clause entry for table "test"
LINE 1: SELECT * FROM json_to_record((test.data->>'children')::JSON)...
^
What is a good beginner application for starting and running a website across servers? I have used html for experimental. When I save the file you can open on firefox, Ie or other software, but When it opens on the server it has no input output and the domain has my drive and personal info. How do I deal with this?
I need a big help.
I have two tables (SubCells and Codes)
and
Visually it looks like this (We can count an order)
And i need to get this final result (with SQL or even a C#)
How can i do that ? I think i have to add Order column to my SubCells table, but how can i iterate Codes and get groups and codegroups information ? I prepared test environment and i will glad for all help.
http://sqlfiddle.com/#!3/438a6
Thank you
I'm doing some digging into potential memory/configuration problems on a sql server 2012 SP2. I stumbled upon something that seems a bit strange in the performance counters as well as the dbcc memorystatus information. here's the info from the buffer pool section of dbcc memorystatus:
If i'm reading things correctly the value is the number of 8 KB pages. The server which is a VM has 16 gb of memory total and SQL server is assigned a min/max value of 12GB. The "Target" value of 32751616 gives me about 262GB which just so happens to be the total amount of memory in the VM host. If i check the "target pages" performance counter on the server i see the same number. I'm guessing this hints of the VM not being configured properly but what i'm really wondering is how the sql server will handle this. Will it impact performance / will the server try to commit more memory than is actually available?
Looking at the memory manager things seem to be looking more normal:
I've been searching the web for information about target buffer pool exceeding max memory but I haven't been able to come up with much so any input is much appreciated.
I am currently interested in large tape libraries. So far, I have found e.g. the StorageTek from Oracle (http://www.oracle.com/us/products/servers-storage/storage/tape-storage/sl8500-modular-library-system/overview/index.html). Is this the largest storage system that you can buy? What is Google doing? Do they just buy a bunch of this machines or are there larger systems? Or is there just no demand for such a huge capacity?
I have three regions: hsprd, hstst, and hstrn.
A request came in to restore hstst to hstrn, then to restore hsprd to hstst.
I don't run backups on my hstst or hstrn regions. So I manually ran a backup of hstst to restore into hstrn using this command:
db2 restore db hstst taken at XXXXXXXXXXXXXXXX to /dbs into hstrn newlogpath /dbs/log/hstrn
My users said the system 'looked good'.
I delete my hstst backup, run a manual backup of hsprd, and repeat my process to restore hsprd into hstst. Everything goes fine, but I was getting an error about a ROLL-FORWARD PENDING status. So then I run these commands:
db2 restore db hsprd logs logtarget /dbs/log/hstst
db2 rollforward db hstst to end of logs and complete
Now my users are saying the hstrn region is reporting the same ROLL-FORWARD PENDING status. I try to run the same commands as above, but on my hstrn region, however my database backup for hstst has been deleted. When trying to rollforward, I get this message:
(/home/hs/hstrn)> db2 rollforward db hstrn to end of logs and complete
SQL4970N Roll-forward recovery on database "HSTRN" cannot reach the specified
stop point (end-of-log or point-in-time) on database partition(s) "0".
Roll-forward recovery processing has halted on log file "S0006597.LOG".
Is there something I can do?
EDIT: I've tried to db2 rollforward db hstrn to end of logs and complete but receive this error:
SQL4970N Roll-forward recovery on database "HSTRN" cannot reach the specified
stop point (end-of-log or point-in-time) on database partition(s) "0".
Roll-forward recovery processing has halted on log file "S0006597.LOG"
I´ve got a problem with lots of mysql “Aborted_Connects”.
The mysqld also have a lot of CPU spikes.
The spikes appear random, when it´s low traffic and when it´s high traffic.
I have tried to run all website pages that I see people use when there is a CPU spike, on a separate server where I´m alone and can see exactly how much CPU in % the website takes. There are no website the user visited during the CPU spike that takes more than about 3 CPU %. The number of webpages loaded are low, no more than about 5-10 in the 10 seconds around the CPU spike.
I found some queries earlier that was high on CPU and changed all of them but it had no effect on the CPU spikes and no effect on the number of Aborted Connects.
If I ping my website there are “Timout” about 1-10 times every minute.
If I make a tracert there are problems with the “hop” (number 7) just before I arrive to my website (number 8). The 7 “hop” that timeout has the same name as my websites (hop 8) “hop” name. It gets timeout there about 3 of 5 times.
Can these timeout problems I see on Ping and Tracert be the cause of the mysql Aborted Connects?
I'm wondering if someone can help with a MongoDB/page-file issue we seem to experience as our database increases in size over time. Every now and then MongoDB will crash on a particular node in our replica set and it won't start again until we significantly increase (double is safe) our page file size. Currently, the page file is 42GB. We are running MongoDB as a 3 node replica set and each node is running on a Windows Server 2012 R2 virtual machine in Azure. Each server has 3.5 GB of memory. MongoDB is version 2.6.5.
I've seen the posts below, which are related, and I understand that MongoDB uses memory mapped files and presumably virtual memory helps with that when we haven't got lots of RAM. What I don't understand is:
Why is MongoDB requiring so much memory on start-up (>32GB page file for a 131GB database) and with a relatively small working set (~100MB)? Presumably it can swap out the files as it needs, especially with a page file this large, so why is MongoDB crashing?
Here are the posts I've found so far:
mongodb memory usage is going high even if only insertions are made
and this one
SERVER-10044 which explains why Mongo crashes and implies VM's are worse
Thanks in advance for any help.
To provide more context we are using MongoDB to log data, so most of the collections are written to but rarely read from, with the exception of a few small collections (100MB total) which are subject to constant reads and writes. The data is stored in a single Mongo DB database, the stats for which are shown below (db and collection names modified):
"db" : "MyDatabase",
"collections" : 854,
"objects" : 243025868,
"avgObjSize" : 541.2304596809423,
"dataSize" : 131533002252,
"storageSize" : 172592721920,
"numExtents" : 7268,
"indexes" : 1934,
"indexSize" : 27824138048,
"fileSize" : 210284576768,
"nsSizeMB" : 16,
"dataFileVersion" : {
"major" : 4,
"minor" : 5
},
"extentFreeList" : {
"num" : 3,
"totalSize" : 110592
},
"ok" : 1
The working set appears to be around the 100 MB mark, as illustrated below:
"workingSet" : {
"note" : "thisIsAnEstimate",
"pagesInMemory" : 20874,
"computationTimeMicros" : 26236,
"overSeconds" : 876
},
The log file output on the secondary that most recently failed is as follows (this occurred during start-up although the first time it failed was during normal operation):
2014-11-25T09:25:17.833+0000 [rsBackgroundSync] replSet syncing to: 10.1.6.71:27017 2014-11-25T09:25:17.833+0000 [rsBackgroundSync] replset setting syncSourceFeedback to 10.1.6.71:27017 2014-11-25T09:25:17.849+0000 [rsSync] replSet still syncing, not yet to minValid optime 54744561:c 2014-11-25T09:25:18.286+0000 [rsSync] replSet SECONDARY 2014-11-25T09:26:01.590+0000 [conn21] serverStatus was very slow: { after basic: 0, after asserts: 0, after backgroundFlushing: 0, after connections: 0, after cursors: 0, after dur: 0, after extra_info: 0, after globalLock: 0, after indexCounters: 0, after locks: 0, after network: 0, after opcounters: 0, after opcountersRepl: 0, after oplog: 10451, after recordStats: 10451, after repl: 10451, at end: 10451 } 2014-11-25T09:26:01.590+0000 [conn21] command admin.$cmd command: serverStatus { serverStatus: 1, oplog: 1 } keyUpdates:0 numYields:0 locks(micros) r:65 reslen:4028 16764ms 2014-11-25T09:26:31.155+0000 [DataFileSync] flushing mmaps took 15022ms for 115 files 2014-11-25T09:26:47.501+0000 [conn5] serverStatus was very slow: { after basic: 0, after asserts: 0, after backgroundFlushing: 0, after connections: 0, after cursors: 0, after dur: 0, after extra_info: 0, after globalLock: 0, after indexCounters: 0, after locks: 0, after network: 0, after opcounters: 0, after opcountersRepl: 0, after oplog: 4791, after recordStats: 4791, after repl: 4791, at end: 4791 } 2014-11-25T09:26:47.501+0000 [conn5] command admin.$cmd command: serverStatus { serverStatus: 1, oplog: 1 } keyUpdates:0 numYields:0 locks(micros) r:88 reslen:4028 7674ms 2014-11-25T09:27:06.350+0000 [repl writer worker 6] VirtualProtect for m:/mongodb/data/MyDatabase.72 chunk 21220 failed with errno:1455 The paging file is too small for this operation to complete. (chunk size is 67108864, address is 14b90000000) in mongo::makeChunkWritable, terminating 2014-11-25T09:27:06.350+0000 [repl writer worker 6] MyDatabase.RC_PUR_11_456754 Fatal Assertion 16362 2014-11-25T09:27:06.615+0000 [repl writer worker 6] Stack trace failed, SymInitialize failed with error 3765269347 2014-11-25T09:27:06.615+0000 [repl writer worker 6] MyDatabase.RC_PUR_11_456754 2014-11-25T09:27:06.615+0000 [repl writer worker 6]
***aborting after fassert() failure
Just like the title describe, I can't seem to login to MySQL through phpmyadmin after changing MySQL datadir from default "var/lib/mysql" to "home/mysql". The MySQL itself runs fine after changing the datadir, there is no problem with websites that connect to it and I can stil login to MySQL through console command and MySQL Workbench.
After checking the mysql folder there's a phpmyadmin folder in there, maybe this has something to do with it not being able to connect through phpmyadmin or are there some configuration I missed?
Thanks for the help.
I have three table A,B,C. Every table has 9, 10 columns.
My current query is very long and complicated because of column names.
I just want to replace column name with *(select all)
'Select col1, col2,.....col10 from table1
union all
select col1, col2,......,null as col10 from table2
union all
.......
'
How can i write simple query or with *;
I have a table in a postgresql database with a column of type bytea. I want to drop each binary entry as a file using the id as filename.
What would a SQL query look like for this?
I've got a query with, more or less, this structure:
SELECT
MAINTABLE_ID
FROM
MAINTABLE WITH (NOLOCK)
LEFT JOIN
TABLE_1 WITH (NOLOCK) ON TABLE_1_FK_MAINTABLE_ID = MAINTABLE_ID
LEFT JOIN
TABLE_2 WITH (NOLOCK) ON TABLE_2_FK_MAINTABLE_ID = MAINTABLE_ID
[...]
LEFT JOIN
TABLE_N WITH (NOLOCK) ON TABLE_N_FK_MAINTABLE_ID = MAINTABLE_ID
WHERE
TABLE_1_ID IS NULL
AND TABLE_2_ID IS NULL
[...]
AND TABLE_N_ID IS NULL
N is about 50. The table names are aliases to four part named tables. This query is pretty fast from SQL Server 2008 to SQL Server 2012. But in SQL Server 2014 (same database) it never ends. The query has been tested with different hardware configurations (these work with the others editions of SQL Server).
What's wrong with it?
Thank you.
INFO I'm following lessons on databases. We're learning about indices now.
Question
If big parts of a table are in the memory, would using a index still have benefits?
My side
I would assume indexing makes a search always faster, so yes it would have benefits. I hope this is not to much opinion based and maybe someone have a good explained reason why or why not it has benefits.
MySql has default setting and system configuration is I5(2.5GHz 8GB Ram)
Table has 18 mill rows and i trying to run this query
select ID ,count(*)
from table
where TYPE='good'
group by ID ,TYPE
limit 2 ;
would please explain how i can solve this problem
I have a table, with two foreign key defined on two columns of that table. The problem is that, when I insert the values from SQL server, it is giving me error as:-
"Column name or number of supplied values does not match table definition" Please see the images for the definition. Please help, as I am very weak in Relation of the table. Why the value is not getting inserted.
have sql server 2008 64bit Developer’s Edition Installed on my machine.
And Microsoft Office 2013 Professional 64bit.
I have been trying to import and export some Excel data from an Excel sheet into a sql server database. I have used the following query to do this :-
sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘Ad Hoc Distributed Queries’, 1; GO RECONFIGURE; GO
USE [ERPInventorySystem]; GO SELECT *FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′, ‘Excel 12.0;Database=C:\Users\pratiks\Downloads\contact.xls;’, ‘SELECT * FROM [Sheet1$]’)
GO But the error message says,
OLE DB provider ‘Microsoft.ACE.OLEDB.12.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I'm in charge of monitoring the performance of an industrial chemistry process. Every hour, a process gathers 50 million rows from various subsystems and feed into the central database. The whole thing takes about 30 minutes.
A database consultant did some analysis on the database and saw the log size spike to 50GB during those load operations. His recommendation was to break the 50 million rows insert into smaller batches. Make sense until I think about our database: it's set to Full Recovery Mode.
Assuming that I do break the operation into smaller batches, their total log sizes would still add up to 50GB. That 50GB will just sit there until the transaction log is backed up. In effect, there will be no difference between the smaller batches and the big batch approach.
Am I missing something here?
This question already has an answer here:
Most questions, including Why Does the Transaction Log Keep Growing or Run Out of Space? and other articles deal with people who have a transaction log file that keeps growing, usually due to them never backing it up. That is NOT the case with my problem. The transaction log file is no longer increasing in size, it is the regular large size of each of the transaction log backups. Each day is experiencing about 1GB of transaction log backups while at the end of each day the main DB backup is about 180MB that is much larger than I would expect the total daily transaction log backups should be in comparison to the size of the actual database.
How do I find what sql insert/update/delete/etc operations are causing so many / such large transactions?
We have an MS SQL Server 2012 database where we have
I realise the actual sizes listed below are not large in the scheme of things due to this DB being only a few months old, but if this trend continues as the DB grows, I'm expecting trouble on multiple fronts. Here is the last 2 week's worth of backup file sizes.
14/11/2014 11:05 PM 200,873,984 Production_backup_2014_11_14_230500_8220928.trn
14/11/2014 11:18 PM 173,499,904 Production_backup_2014_11_14_231753_3214232.bak
15/11/2014 06:00 AM 321,033,728 Production_backup_2014_11_15_060001_9915061.trn
15/11/2014 10:00 AM 47,616 Production_backup_2014_11_15_100001_1784634.trn
15/11/2014 02:00 PM 59,392 Production_backup_2014_11_15_140001_0461018.trn
15/11/2014 06:00 PM 62,976 Production_backup_2014_11_15_180001_5854303.trn
15/11/2014 11:05 PM 71,506,432 Production_backup_2014_11_15_230501_3937283.dif
16/11/2014 06:00 AM 12,906,496 Production_backup_2014_11_16_060009_1822596.trn
16/11/2014 10:00 AM 11,776 Production_backup_2014_11_16_100001_2205645.trn
16/11/2014 02:00 PM 12,288 Production_backup_2014_11_16_140000_7731183.trn
16/11/2014 06:00 PM 12,288 Production_backup_2014_11_16_180001_4459573.trn
16/11/2014 11:05 PM 73,329,664 Production_backup_2014_11_16_230501_1010172.dif
17/11/2014 06:00 AM 12,288 Production_backup_2014_11_17_060001_2761826.trn
17/11/2014 10:00 AM 41,998,848 Production_backup_2014_11_17_100004_9331208.trn
17/11/2014 02:00 PM 167,330,304 Production_backup_2014_11_17_140004_6334692.trn
17/11/2014 06:00 PM 161,651,712 Production_backup_2014_11_17_180007_0122447.trn
17/11/2014 11:05 PM 92,433,408 Production_backup_2014_11_17_230501_6387196.dif
18/11/2014 06:00 AM 333,844,480 Production_backup_2014_11_18_060001_5745763.trn
18/11/2014 10:00 AM 126,087,680 Production_backup_2014_11_18_100005_0370500.trn
18/11/2014 02:00 PM 166,028,288 Production_backup_2014_11_18_140005_3971911.trn
18/11/2014 06:00 PM 179,916,288 Production_backup_2014_11_18_180006_2898091.trn
18/11/2014 11:05 PM 96,823,296 Production_backup_2014_11_18_230501_5082355.dif
19/11/2014 06:00 AM 339,789,312 Production_backup_2014_11_19_060002_0077560.trn
19/11/2014 10:00 AM 127,332,352 Production_backup_2014_11_19_100006_4801078.trn
19/11/2014 02:00 PM 174,851,584 Production_backup_2014_11_19_140006_7360844.trn
19/11/2014 06:00 PM 170,896,384 Production_backup_2014_11_19_180006_2771851.trn
19/11/2014 11:05 PM 100,923,904 Production_backup_2014_11_19_230501_2174429.dif
20/11/2014 06:00 AM 341,084,160 Production_backup_2014_11_20_060000_7971034.trn
20/11/2014 10:00 AM 143,212,544 Production_backup_2014_11_20_100005_7464239.trn
20/11/2014 02:00 PM 168,745,472 Production_backup_2014_11_20_140007_3616365.trn
20/11/2014 06:00 PM 169,514,496 Production_backup_2014_11_20_180006_5721553.trn
20/11/2014 11:05 PM 104,629,248 Production_backup_2014_11_20_230501_0945522.dif
21/11/2014 06:00 AM 345,049,088 Production_backup_2014_11_21_060003_5997426.trn
21/11/2014 10:00 AM 131,839,488 Production_backup_2014_11_21_100004_0817301.trn
21/11/2014 02:00 PM 179,260,416 Production_backup_2014_11_21_140006_1959629.trn
21/11/2014 06:00 PM 185,633,280 Production_backup_2014_11_21_180006_0060725.trn
21/11/2014 11:05 PM 211,755,520 Production_backup_2014_11_21_230500_9492206.trn
21/11/2014 11:19 PM 184,960,512 Production_backup_2014_11_21_231900_1599462.bak
22/11/2014 06:00 AM 342,386,688 Production_backup_2014_11_22_060003_6139255.trn
22/11/2014 10:00 AM 114,176 Production_backup_2014_11_22_100003_8050593.trn
22/11/2014 02:00 PM 131,584 Production_backup_2014_11_22_140001_5545860.trn
22/11/2014 06:00 PM 131,584 Production_backup_2014_11_22_180004_6338691.trn
22/11/2014 11:05 PM 74,345,984 Production_backup_2014_11_22_230501_0706925.dif
23/11/2014 06:00 AM 13,621,760 Production_backup_2014_11_23_060005_1215593.trn
23/11/2014 10:00 AM 12,288 Production_backup_2014_11_23_100000_8636227.trn
23/11/2014 02:00 PM 148,992 Production_backup_2014_11_23_140001_5174270.trn
23/11/2014 06:00 PM 12,800 Production_backup_2014_11_23_180001_0560033.trn
23/11/2014 11:05 PM 76,475,904 Production_backup_2014_11_23_230500_6863628.dif
24/11/2014 06:00 AM 24,576 Production_backup_2014_11_24_060002_1207776.trn
24/11/2014 10:00 AM 99,911,168 Production_backup_2014_11_24_100001_6216355.trn
24/11/2014 02:00 PM 161,881,088 Production_backup_2014_11_24_140006_1306524.trn
24/11/2014 06:00 PM 163,761,664 Production_backup_2014_11_24_180001_1006240.trn
24/11/2014 11:05 PM 96,010,752 Production_backup_2014_11_24_230500_8739860.dif
25/11/2014 06:00 AM 333,105,664 Production_backup_2014_11_25_060001_1277451.trn
25/11/2014 10:00 AM 141,830,656 Production_backup_2014_11_25_100004_1112999.trn
25/11/2014 02:00 PM 164,597,248 Production_backup_2014_11_25_140001_6337935.trn
25/11/2014 06:00 PM 176,773,632 Production_backup_2014_11_25_180004_9749028.trn
25/11/2014 11:05 PM 102,029,312 Production_backup_2014_11_25_230500_9038297.dif
26/11/2014 06:00 AM 334,088,704 Production_backup_2014_11_26_060001_1548298.trn
26/11/2014 10:00 AM 127,079,424 Production_backup_2014_11_26_100001_7924674.trn
26/11/2014 02:00 PM 165,528,576 Production_backup_2014_11_26_140005_2202075.trn
26/11/2014 06:00 PM 165,344,256 Production_backup_2014_11_26_180000_9116237.trn
26/11/2014 11:05 PM 104,935,936 Production_backup_2014_11_26_230500_6289926.dif
27/11/2014 06:00 AM 336,645,120 Production_backup_2014_11_27_060000_8290121.trn
27/11/2014 10:00 AM 128,401,920 Production_backup_2014_11_27_100008_6395607.trn
27/11/2014 02:00 PM 163,676,160 Production_backup_2014_11_27_140004_6019843.trn
27/11/2014 06:00 PM 167,849,472 Production_backup_2014_11_27_180005_9136346.trn
27/11/2014 11:05 PM 108,699,136 Production_backup_2014_11_27_230500_8885145.dif
28/11/2014 06:00 AM 338,519,040 Production_backup_2014_11_28_060001_1061047.trn
28/11/2014 10:00 AM 129,424,896 Production_backup_2014_11_28_100005_8038388.trn
28/11/2014 02:00 PM 171,059,712 Production_backup_2014_11_28_140014_1188080.trn
The db application doesn't run between 3am sunday until around 8am on the the Monday, so the .trn files over the weekend are mostly small as I would expect. I think I've mucked up the sequence of the weekly DB shrink and re-index so the backups around that are larger than they should be, but each .trn file after that should be quite small.
These tables are not that big, but the time taken to process this query is long.
What can I do with the following queries to speed up the process taken to return results?
$msc=microtime(true);
$chat_alg = mysqli_query($con, "
SELECT sid, COUNT(*) as frequency
FROM plays
WHERE time > NOW() - INTERVAL 3 DAY && sid != ''
GROUP BY sid
ORDER BY COUNT(*) DESC
") or die(mysqli_error($con));
while($row_chat_alg = mysqli_fetch_array($chat_alg)) {
$chart_songs[] = $row_chat_alg['sid'];
}
$imploded_songs = implode($chart_songs, ',');
$projects = $dbh->query("
SELECT * FROM music
WHERE perms != 'c' && sid IN($imploded_songs)
ORDER BY FIELD(sid,$imploded_songs)
limit 50
");
$msc=microtime(true)-$msc;
echo ($msc*1000).' milliseconds';
...
Time taken: 9.5310001373291 milliseconds
This tables are growing, I'm worried that maintaining them will be a problem if this is already slow.
I'm trying to find related records based on 2 columns, however there must be a more elegant set based solution, or if not a set based solution, various ways of making this query better
The real world application for this query is that sometimes customers create multiple accounts with the same email address and/or have the same account with multiple emails, so I am trying to collate orders that are essentially from the same person, using either the userId or their email address
The example below basically stores the found orders ids, userIds and emails in to temporary tables, then researches for every new userId or email found
A working example
declare @UserId varchar(20) = '1'
declare @Email varchar(50)= 'blah1'
-- set test src tables
DECLARE @src TABLE
(Id int,UserId nvarchar(20),Email nvarchar(50),PostCode int,Country nvarchar(50))
-- inset test data
INSERT @src SELECT *
FROM (VALUES
(1, '1', 'blah1', 111, 'au'),
(2, '1', 'blah2', 111, 'au'),
(3, '1', 'blah3', 111, 'au'),
(4, '2', 'blah4', 111, 'au'),
(5, '2', 'blah3', 111, 'nz'),
(6, '4', 'blah4', 111, 'nz'),
(7, '5', 'blah4', 111, 'nz'),
(8, '5', 'blah6', 111, 'nz'),
(9, '7', 'blah7', 111, 'nz'),
(10, '8', 'blah8', 111, 'nz'),
(11, '9', 'blah9', 111, 'nz'),
(12, '10', 'blah10', 111, 'nz'),
(13, '1', 'blah11', 111, 'nz')
) src (Id, UserId, Email, PostCode, Country)
-- show the records, for debug purposes
SELECT *FROM @src
-- declare table variables
DECLARE @Ids TABLE (Id int)
DECLARE @Emails TABLE (Email varchar(50),Searched bit)
DECLARE @UserIds TABLE (UserId varchar(20),Searched bit)
DECLARE @Results TABLE (Id int,
UserId varchar(20),
Email varchar(50),
PostCode int,
Country nvarchar(50)
)
-- insert Initial UserId
IF (@UserId IS NOT NULL)
INSERT INTO @UserIds (UserId, Searched) VALUES (@UserId, 0);
-- insert Initial Email
IF (@Email IS NOT NULL)
INSERT INTO Emails(Email, Searched) VALUES (t.Email, 0);
-- while both variables have something in them, there are potentially more matches
WHILE (@UserId IS NOT NULL OR @Email IS NOT NULL)
BEGIN
-- clear results
DELETE FROM @Results
-- Main search query
INSERT INTO @Results SELECT Id,UserId,Email,PostCode,Country
FROM @src
WHERE (@UserId IS NOT NULL AND userId = @UserId)
OR (@Email IS NOT NULL AND Email = @Email)
-- if results are found merge
IF (@@ROWCOUNT > 0)
BEGIN
-- merge new ids
MERGE INTO @Ids i
USING (SELECT Id FROM @Results) t ON t.Id = i.Id
WHEN NOT MATCHED THEN
INSERT (Id) VALUES (t.Id);
-- merge new userIds
MERGE INTO @UserIds u
USING (SELECT UserId FROM @Results) t ON t.UserId = u.UserId
WHEN NOT MATCHED THEN
INSERT (UserId, Searched) VALUES (t.UserId, 0);
-- merge new emails
MERGE INTO @Emails u
USING (SELECT Email FROM @Results) t ON t.Email = u.Email
WHEN NOT MATCHED THEN
INSERT (Email, Searched) VALUES (t.Email, 0);
END
-- mark variables as searched in thier respective tables
UPDATE @UserIds SET Searched = 1 WHERE UserId = @UserId
UPDATE @Emails SET Searched = 1 WHERE Email = @Email
-- clear variables
SET @UserId = NULL;
SET @Email = NULL;
-- reset variables to the next unsearched value
SELECT TOP 1 @UserId = UserId
FROM @UserIds
WHERE Searched = 0;
SELECT TOP 1 @Email = Email
FROM @Emails
WHERE Searched = 0;
END
-- display related records
SELECT * FROM @Ids
I have my machine and a database server on the same Windows domain. I also have a staging machine that is not on the domain.
From my machine, I am able to perform any and all operations using FILESTREAM without trouble. From the staging machine, my application is able to use TSQL as it pleases, but the moment it tries to open a SqlFileStream, I receive 'access is denied.`
The staging machine, the database server, and my machine all have a local user set up with an identical name and password so that the application can use integrated security to connect to SQL Server. Since FILESTREAM is the only thing not working for the non-domain machine, I am wondering if I am missing something in the FILESTREAM documentation or if the documentation fails to specify that only Active Directory will suffice for integrated security, or of course if there is something I can do to make my situation work.
I'm getting a very weird MySQL replication error No. 1146 for a REPLACE INTO query on a slave host replicating all tables in all databases from a master, and I'm having a bit of a hard time understanding why.
Here's my scenario:
FLUSH TABLES WITH READ LOCK was issued on slave A and a dump of all db's was subsequently produced from it with mysqldump -v -h localhost -u root -p --all-databases --opt --single-transaction --hex-blob --no-autocommit > dump.sql. Lock was released only after the dump completed, and then the slave IO_THREAD was restarted; replication from master resumed without any problems, and continues to run smoothly to this day on slave A.mysql -h localhost -u root -p < dump.sql command, after confirming non of the target databases existed on this second host (and actually no db's other than the mysql db and information & performance schemas). I can also confirm integrity of the dump file after the transfer to slave B thanks to matching RMD160 checksums for it on both hosts.However, after about a day of smooth operation, slave B produced the following error in its SQL_THREAD:
Error 'Table 'knet.course_location_tracks' doesn't exist' on query. Default database: 'knet'. Query: 'REPLACE INTO `course_location_tracks` (`userid`,`courseid`,`lesson_location`,`datestamp`) VALUES (val1,val2,val3,val4)'
(actual row values have been redacted out)
I can't make much sense of this error because I can confirm not only that the knet.course_location_tracks table exists on slave B, but also that its definition is identical to that of the corresponding table on slave A. Slave A, as I pointed out above, continues to replicate from master just fine to this day, without any problems of any kind.
If replication started fine on slave B, leading me to believe that initial replication coordinates were calculated correctly for it off of the state of slave A at the point of the dump, why am I then getting this error for a table that does exists on the host? Moreover, why am I getting the error on slave B while slave A is still replicating smoothly?
Other than having mismatching MySQL versions across my three hosts (something I only noticed recently in trying to debug the problem, and that I will see to correct ASAP), what could I be doing wrong?
And, finally, once the problem is determined and corrected, how could I get slave B to resume replication at the correct point so that it is again fully in sync with master?
Thanks in advance for any help!
PS: If it matters, replication on slave A was initially set up by transferring all db's & tables in master to it in a similar fashion, i.e. by first flushing & blocking all tables on master with the read lock, dumping the data with mysqldump (same flags), and finally loading them into the slave with the same mysql command-line client call.
I've been giving the task of taking SQL DDL from data modeler and to transcribe it into mySQL for an internship. The only thing I cannot figure out is how to take
Created_By VARCHAR2(30) DEFAULT USER,
and turn that into sql my resolution was just to do a trigger but now the trigger doesn't work
/* Triggers for User Creation */
DELIMITER //
CREATE TRIGGER menu_creator_tr
BEFORE INSERT ON Menu
FOR EACH ROW BEGIN
NEW.Created_By = USER();
END
DELIMITER;
it states that NEW.Created_By is missing a colon. I am very confused now. Just started learning sql and so i am lost. Some help for both problems is what I am looking for to learn, but a help on either or will help me finish this. I really need to know this though for I have more columns that are similar.
I am attempting to convert stirngs to dates in mysql
Thu Oct 23 16:46:47 2014 +02:00
Im unsure how to handle the +02:00, The rest is simple enough
update logs
set date = str_to_date(date_raw, '%a %b %e %T %Y')
This returns
Truncated incorrect datetime value: 'Thu Oct 23 16:46:47 2014 +02:00'
Any ideas?
I have a simple table with two columns: id (autonumber) and name (char(255).
When I use C# to insert 100 records in a loop it takes about 30 milliseconds per insert. This is way too slow. So I browsed around on this site and found this setting:
set global innodb_flush_log_at_trx_commit = 2;
Now the inserts are as fast as SQL Server: .2 millisecond, or about 100 times faster than with the default setting.
But I've read that with setting innodb_flush_log_at_trx_commit to 2 you lose ACID compliance.
These are my measurements:
I have a few questions:
Update 1
This is the contenct of the my.ini file:
# Other default tuning values
# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by the MySQL Server Instance Configuration Wizard
#
#
# Installation Instructions
# ----------------------------------------------------------------------
#
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.
#
# On Windows you should keep this file in the installation directory
# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To
# make sure the server reads the config file use the startup option
# "--defaults-file".
#
# To run run the server from the command line, execute this in a
# command line shell, e.g.
# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# To install the server as a Windows service manually, execute this in a
# command line shell, e.g.
# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# And then execute this in a command line shell to start the server, e.g.
# net start MySQLXY
#
#
# Guildlines for editing this file
# ----------------------------------------------------------------------
#
# In this file, you can use all long options that the program supports.
# If you want to know the options a program supports, start the program
# with the "--help" option.
#
# More detailed information about the individual options can also be
# found in the manual.
#
# For advice on how to change settings please see
# http://ift.tt/ZQDJna
#
#
# CLIENT SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]
no-beep
# pipe
# socket=0.0
port=3306
[mysql]
default-character-set=utf8
# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
# server_type=3
[mysqld]
# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory
# shared-memory-base-name=MYSQL
# The Pipe the MySQL Server will use
# socket=MYSQL
# The TCP/IP Port the MySQL Server will listen on
port=3306
# Path to installation directory. All paths are usually resolved relative to this.
# basedir="C:/Program Files/MySQL/MySQL Server 5.6/"
# Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 5.6/Data
# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# Enable Windows Authentication
# plugin-load=authentication_windows.dll
# General and Slow logging.
log-output=FILE
general-log=0
general_log_file="HP-L-08.log"
slow-query-log=1
slow_query_log_file="HP-L-08-slow.log"
long_query_time=10
# Binary Logging.
# log-bin
# Error Logging.
log-error="HP-L-08.err"
# Server Id.
server-id=1
# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=151
# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=0
# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_open_cache=2000
# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=121M
# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before. This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=10
#*** MyISAM Specific options
# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G
# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method. This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=231M
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=8M
# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K
read_rnd_buffer_size=256K
# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=256K
#*** INNODB Specific options ***
# innodb_data_home_dir=0.0
# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
# skip-innodb
# Additional memory pool that is used by InnoDB to store metadata
# information. If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS. As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=17M
# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1
# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=9M
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=772M
# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=48M
# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=17
# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64
# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=8
# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000
# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time=1000
# It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
innodb_open_files=300
# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0
# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1
# Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm=0
# The number of outstanding connection requests MySQL can have.
# This option is useful when the main MySQL thread gets many connection requests in a very short time.
# It then takes some time (although very little) for the main thread to check the connection and start a new thread.
# The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily
# stops answering new requests.
# You need to increase this only if you expect a large number of connections in a short period of time.
back_log=80
# If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
# synchronize unflushed data to disk.
# This option is best used only on systems with minimal resources.
flush_time=0
# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
# indexes and thus perform full table scans.
join_buffer_size=256K
# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
# mysql_stmt_send_long_data() C API function.
max_allowed_packet=4M
# If more than this many successive connection requests from a host are interrupted without a successful connection,
# the server blocks that host from performing further connections.
max_connect_errors=100
# Changes the number of file descriptors available to mysqld.
# You should try increasing the value of this option if mysqld gives you the error "Too many open files".
open_files_limit=4161
# Set the query cache type. 0 for OFF, 1 for ON and 2 for DEMAND.
query_cache_type=0
# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
# sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
# or improved indexing.
sort_buffer_size=256K
# The number of table definitions (from .frm files) that can be stored in the definition cache.
# If you use a large number of tables, you can create a large table definition cache to speed up opening of tables.
# The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.
# The minimum and default values are both 400.
table_definition_cache=1400
# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K
# If the value of this variable is greater than 0, a replication slave synchronizes its master.info file to disk.
# (using fdatasync()) after every sync_master_info events.
sync_master_info=10000
# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000
# If the value of this variable is greater than 0, a replication slave synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000
transaction-isolation = READ-COMMITTED
Update 2
This is the C# code inserting 100 records:
var mySqlConnection = new MySqlConnection(connectionString);
mySqlConnection.Open();
for (int i = 0; i < 100; i++)
{
var guid = Guid.NewGuid();
var cmd = mySqlConnection.CreateCommand();
cmd.CommandText = "insert into speedtest (Name) values (@name);";
var name = new MySqlParameter
{
MySqlDbType = MySqlDbType.String,
ParameterName = "name",
Value = guid.ToString()
};
cmd.Parameters.Add(name);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
mySqlConnection.Close();
The code for the inserts into SQL Server is identical
Update 3
The C# code, MySql and Sql Server are all running on my laptop.
I came across the interesting article by Facebook's Engineering team where they were able to deploy GTID in MySQL at a large scale : http://ift.tt/1FCYOEJ
Their idea to enable GTID is to keep GTID off on the master, and on on all the slaves, and then later promote one of the slaves as the new master and then turning on GTID on the original master :
"We worked around this problem in fb-mysql by relaxing the constraint that gtid_mode must match between master and replicas. We permit a gtid_mode=ON replica to have a gtid_mode=OFF master, and we suppress assignment of GTIDs to transactions on servers with read_only=ON (i.e., replicas). This permits a high-availability deployment strategy as follows, for each replica set:
From what I understand about GTID in 5.6, either all the machines in your cluster need to have GTID on, or they need to have GTID off. So is Facebook able to have a GTID-ON master and GTID-OFF slaves because they have specially modified their MySQL to do so? This would otherwise not be supported under a normal MySQL 5.6 release right?
Thank you
I've got a database MSSQL2008R2Express DB running Kaspersky Security Center, and I have no idea under what circumstances the install happened, but the database appears to think that it's being replicated and will not free any space from the transaction log.
eg:
USE master;
SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases WHERE name = 'KAV';
SELECT DATABASEPROPERTYEX('KAV', 'IsPublished');
returns:
KAV,6,REPLICATION
0 [not published]
Also there's nothing listed in the Replication section in SSMS.
So far I've tried a couple statements gleaned from Google results:
USE KAV;
EXEC sp_repldone null, null, 0,0,1;
EXEC sp_removedbreplication KAV;
But I've had no luck in getting this DB to stop thinking its being replicated.
Full sys.databases info:
name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on is_cdc_enabled is_encrypted is_honor_broker_priority_on
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ ------------ ---------------- ----------------- ----- ------------------------------------------------------------ ------------- ------------------- ------------------------------- ------------------------ ------------------------------------------------------------ ----------------------------- -------------- ------------------------------------------------------------ ------------------ ------------------------------------------------------------ ----------------------- ----------------------- ----------------------------- ----------------------- ---------------- ------------------ ------------------- ---------------- ----------------------------- ------------------------ ----------------------- ------------------------ ---------------------------- ----------------------- ------------------- ----------------- ----------------- -------------------------- --------------------------------- ------------ ------------- ------------------ -------------- ------------------- ------------------------------------ ----------------- -------------- ------------------------------------------------------------ ---------------------- -------------- ------------ ---------------------------
KAV 5 NULL 0x0105000000000005150000004EB006B0C3554AB049CEA01BE8030000 2013-07-04 10:31:28.947 90 Latin1_General_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 1 1 FULL 2 CHECKSUM 1 1 0 1 1 1 1 1 1 0 1 0 0 1 1 0 0 0 0 0 0 0 0 0 19C05AF5-8686-4C27-BF7E-93E240DA953B 0 6 REPLICATION 0 0 0 0
I am using PostgreSQL.
I have a table with columns:
tutor_id
subject_id
I want to populate this table with the result of the following select:
select tutors.id, subject_id from courses_subjects
join courses on courses.id = courses_subjects.course_id
join tutors on tutors.id = courses.tutor_id;
The point is that the above select brings duplicate entries. This breaks the insert
insert into tutor_subjects (tutor_id, subject_id)
select tutors.id, subject_id
from ...;
because tutor_subjects does not allow duplicate entries. The correct data that I have to insert appear to be:
select distinct(tutors.id, subject_id) from ....;
But I cannot use this select in combination with insert.
Any help?